CREATE TABLE IF NOT EXISTS agreements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    project_id INT DEFAULT NULL,
    procurement_request_id INT DEFAULT NULL,
    vendor_id INT DEFAULT NULL,
    partner_id INT DEFAULT NULL,
    donor_id INT DEFAULT NULL,
    agreement_code VARCHAR(60) NOT NULL,
    title VARCHAR(180) NOT NULL,
    agreement_type VARCHAR(80) DEFAULT 'service_contract',
    counterparty_type VARCHAR(40) DEFAULT 'vendor',
    start_date DATE DEFAULT NULL,
    end_date DATE DEFAULT NULL,
    amount DECIMAL(18,2) DEFAULT 0,
    currency VARCHAR(10) DEFAULT 'NGN',
    status VARCHAR(40) DEFAULT 'draft',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    signed_at DATETIME NULL,
    closed_at DATETIME NULL,
    INDEX idx_agreements_tenant_status (tenant_id, status),
    INDEX idx_agreements_code (agreement_code),
    INDEX idx_agreements_programme (programme_id),
    INDEX idx_agreements_project (project_id),
    INDEX idx_agreements_procurement (procurement_request_id),
    INDEX idx_agreements_vendor (vendor_id),
    INDEX idx_agreements_partner (partner_id),
    INDEX idx_agreements_donor (donor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(59, 'agreements', 'View agreements and contracts workspace', 'agreements.view'),
(60, 'agreements', 'Manage agreements and contracts workspace', 'agreements.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 ('agreements.view','agreements.manage')
WHERE (
    (r.slug IN ('super_admin','tenant_admin') AND p.slug IN ('agreements.view','agreements.manage'))
    OR (r.slug IN ('programme_manager','finance_officer','field_officer') AND p.slug IN ('agreements.view','agreements.manage'))
    OR (r.slug IN ('partner_user','auditor','read_only_viewer','me_officer') AND p.slug='agreements.view')
);