CREATE TABLE IF NOT EXISTS outreach_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    campaign_id INT DEFAULT NULL,
    service_point_id INT DEFAULT NULL,
    session_code VARCHAR(60) NOT NULL,
    session_type VARCHAR(80) DEFAULT 'sensitization',
    title VARCHAR(180) NOT NULL,
    state VARCHAR(120) DEFAULT '',
    lga VARCHAR(120) DEFAULT '',
    community VARCHAR(150) DEFAULT '',
    session_date DATETIME DEFAULT NULL,
    facilitator_name VARCHAR(150) DEFAULT '',
    expected_attendance INT DEFAULT 0,
    actual_attendance INT DEFAULT 0,
    status VARCHAR(40) DEFAULT 'planned',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    completed_at DATETIME NULL,
    INDEX idx_outreach_tenant_status (tenant_id, status),
    INDEX idx_outreach_programme (programme_id),
    INDEX idx_outreach_campaign (campaign_id),
    INDEX idx_outreach_service_point (service_point_id),
    INDEX idx_outreach_session_date (session_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
    (89, 'outreach', 'View outreach sessions workspace', 'outreach.view'),
    (90, 'outreach', 'Manage outreach sessions workspace', 'outreach.manage');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
JOIN permissions p ON p.slug IN ('outreach.view','outreach.manage')
LEFT JOIN role_permissions rp ON rp.role_id=r.id AND rp.permission_id=p.id
WHERE rp.id IS NULL AND (
    (r.slug IN ('super_admin','tenant_admin','programme_manager','me_officer','field_officer') AND p.slug IN ('outreach.view','outreach.manage'))
    OR (r.slug IN ('case_worker','finance_officer','partner_user') AND p.slug='outreach.view')
    OR (r.slug IN ('auditor','read_only_viewer') AND p.slug='outreach.view')
);