-- v27 Actions & Follow-up Tracker
CREATE TABLE IF NOT EXISTS action_tracker (
    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,
    compliance_review_id INT DEFAULT NULL,
    risk_id INT DEFAULT NULL,
    action_code VARCHAR(60) NOT NULL,
    title VARCHAR(180) NOT NULL,
    action_type VARCHAR(80) DEFAULT 'corrective_action',
    action_owner VARCHAR(150) DEFAULT '',
    due_date DATE DEFAULT NULL,
    priority VARCHAR(30) DEFAULT 'medium',
    progress_percent INT DEFAULT 0,
    status VARCHAR(40) DEFAULT 'open',
    evidence_path VARCHAR(255) DEFAULT '',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    completed_at DATETIME NULL,
    INDEX idx_action_tracker_tenant_status (tenant_id, status),
    INDEX idx_action_tracker_code (action_code),
    INDEX idx_action_tracker_programme (programme_id),
    INDEX idx_action_tracker_project (project_id),
    INDEX idx_action_tracker_agreement (agreement_id),
    INDEX idx_action_tracker_review (compliance_review_id),
    INDEX idx_action_tracker_risk (risk_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(71, 'actions', 'View action tracker workspace', 'actions.view'),
(72, 'actions', 'Manage action tracker workspace', 'actions.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 ('actions.view','actions.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') AND p.slug IN ('actions.view','actions.manage'))
    OR (r.slug IN ('programme_manager','field_officer','case_worker','me_officer','finance_officer') AND p.slug IN ('actions.view','actions.manage'))
    OR (r.slug = 'partner_user' AND p.slug IN ('actions.view'))
    OR (r.slug IN ('auditor','read_only_viewer') AND p.slug IN ('actions.view'))
);