-- v28 Exit & Graduation Management
CREATE TABLE IF NOT EXISTS exit_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    beneficiary_id INT DEFAULT NULL,
    household_id INT DEFAULT NULL,
    exit_code VARCHAR(60) NOT NULL,
    exit_type VARCHAR(60) DEFAULT 'programme_exit',
    reason_category VARCHAR(80) DEFAULT 'graduation',
    effective_date DATE DEFAULT NULL,
    status VARCHAR(40) DEFAULT 'planned',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    approved_by INT DEFAULT NULL,
    approved_at DATETIME NULL,
    reversed_at DATETIME NULL,
    UNIQUE KEY uniq_exit_code (exit_code),
    INDEX idx_exit_records_tenant_status (tenant_id, status),
    INDEX idx_exit_records_programme (programme_id),
    INDEX idx_exit_records_beneficiary (beneficiary_id),
    INDEX idx_exit_records_household (household_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(73, 'exits', 'View exit and graduation workspace', 'exits.view'),
(74, 'exits', 'Manage exit and graduation workspace', 'exits.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 ('exits.view','exits.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 ('exits.view','exits.manage'))
    OR (r.slug IN ('programme_manager','field_officer','case_worker','me_officer','finance_officer') AND p.slug IN ('exits.view','exits.manage'))
    OR (r.slug = 'partner_user' AND p.slug IN ('exits.view'))
    OR (r.slug IN ('auditor','read_only_viewer') AND p.slug IN ('exits.view'))
);

INSERT INTO exit_records (tenant_id, programme_id, beneficiary_id, household_id, exit_code, exit_type, reason_category, effective_date, status, notes, created_by, created_at, approved_by, approved_at, reversed_at)
SELECT 1, 1, 1, 1, 'EXT-1001', 'graduation', 'graduation', CURDATE(), 'approved', 'Household has transitioned out after livelihoods support and income stabilization review.', 1, NOW(), 1, NOW(), NULL
WHERE NOT EXISTS (SELECT 1 FROM exit_records WHERE exit_code='EXT-1001');