-- ==========================================
-- OPTIONAL: Schema improvements for FUDMA alignment
-- Run this only if you want the extra columns and FK.
-- Backup your database first.
-- ==========================================

USE university_payroll;

-- 1) FUDMA audit columns on payroll_records (for reporting: annual gross, relief, chargeable, annual tax)
ALTER TABLE payroll_records
    ADD COLUMN annual_gross DECIMAL(15,2) NULL AFTER net_salary,
    ADD COLUMN total_relief DECIMAL(15,2) NULL AFTER annual_gross,
    ADD COLUMN chargeable_income DECIMAL(15,2) NULL AFTER total_relief,
    ADD COLUMN annual_tax DECIMAL(15,2) NULL AFTER chargeable_income;

-- 2) Referential integrity: approved_by should reference system_users
-- If this fails (e.g. approved_by values not in system_users), run first:
--   UPDATE payroll_approvals SET approved_by = NULL WHERE approved_by NOT IN (SELECT id FROM system_users);
ALTER TABLE payroll_approvals
    ADD CONSTRAINT fk_payroll_approvals_approved_by
    FOREIGN KEY (approved_by) REFERENCES system_users(id)
    ON DELETE SET NULL ON UPDATE CASCADE;

-- 3) Explicit monthly/annual for staff deductions (FUDMA uses monthly for Pension, NHF, Union)
ALTER TABLE staff_deductions
    ADD COLUMN amount_basis ENUM('monthly','annual') NOT NULL DEFAULT 'monthly' AFTER amount;

-- 4) Optional: salary basis per level (if you want some scales annual, others monthly)
ALTER TABLE salary_levels
    ADD COLUMN salary_basis ENUM('annual','monthly') NOT NULL DEFAULT 'annual' AFTER basic_salary;
