CREATE TABLE IF NOT EXISTS budget_allocations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    project_id INT DEFAULT NULL,
    donor_id INT DEFAULT NULL,
    budget_code VARCHAR(60) NOT NULL,
    fiscal_year VARCHAR(20) DEFAULT '',
    funding_source VARCHAR(100) DEFAULT '',
    line_item VARCHAR(180) NOT NULL,
    currency VARCHAR(10) DEFAULT 'NGN',
    allocated_amount DECIMAL(18,2) DEFAULT 0,
    committed_amount DECIMAL(18,2) DEFAULT 0,
    actual_amount DECIMAL(18,2) DEFAULT 0,
    variance_amount DECIMAL(18,2) DEFAULT 0,
    status VARCHAR(40) DEFAULT 'draft',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    approved_at DATETIME NULL,
    INDEX idx_budget_tenant_status (tenant_id, status),
    INDEX idx_budget_programme (programme_id),
    INDEX idx_budget_project (project_id),
    INDEX idx_budget_donor (donor_id),
    INDEX idx_budget_code (budget_code),
    INDEX idx_budget_fiscal_year (fiscal_year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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