USE social_protection_platform;

CREATE TABLE IF NOT EXISTS indicators (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT NULL,
    name VARCHAR(180) NOT NULL,
    indicator_type VARCHAR(80) DEFAULT '',
    target_value DECIMAL(18,2) DEFAULT 0,
    actual_value DECIMAL(18,2) DEFAULT 0,
    unit VARCHAR(40) DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS monitoring_visits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT NULL,
    beneficiary_id INT NULL,
    visit_date DATE NULL,
    officer_name VARCHAR(150) DEFAULT '',
    notes TEXT NULL,
    status VARCHAR(40) DEFAULT 'scheduled'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(39, 'monitoring', 'View monitoring and MEL', 'monitoring.view'),
(40, 'monitoring', 'Manage monitoring and MEL', 'monitoring.manage');

INSERT IGNORE INTO role_permissions (role_id, permission_id) VALUES
(2,39),(2,40),
(3,39),(3,40),
(4,39),(4,40),
(7,39),(7,40),
(9,39),
(11,39);