CREATE TABLE IF NOT EXISTS compliance_reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    project_id INT DEFAULT NULL,
    agreement_id INT DEFAULT NULL,
    review_code VARCHAR(60) NOT NULL,
    review_type VARCHAR(80) DEFAULT 'compliance_review',
    title VARCHAR(180) NOT NULL,
    review_period VARCHAR(100) DEFAULT '',
    lead_reviewer VARCHAR(150) DEFAULT '',
    due_date DATE DEFAULT NULL,
    risk_rating VARCHAR(20) DEFAULT 'medium',
    findings_count INT DEFAULT 0,
    open_actions_count INT DEFAULT 0,
    status VARCHAR(40) DEFAULT 'planned',
    summary TEXT NULL,
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    closed_at DATETIME NULL,
    INDEX idx_compliance_reviews_tenant_status (tenant_id, status),
    INDEX idx_compliance_reviews_code (review_code),
    INDEX idx_compliance_reviews_programme (programme_id),
    INDEX idx_compliance_reviews_project (project_id),
    INDEX idx_compliance_reviews_agreement (agreement_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(63, 'compliance', 'View compliance review register', 'compliance.view'),
(64, 'compliance', 'Manage compliance review register', 'compliance.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 ('compliance.view', 'compliance.manage')
WHERE (
    (r.slug IN ('super_admin', 'tenant_admin', 'programme_manager', 'field_officer') AND p.slug IN ('compliance.view', 'compliance.manage'))
    OR (r.slug IN ('auditor', 'read_only_viewer', 'partner_user', 'me_officer') AND p.slug = 'compliance.view')
    OR (r.slug = 'finance_officer' AND p.slug IN ('compliance.view', 'compliance.manage'))
);