-- Payroll module: employees, payroll runs, payslips. Kenya PAYE, NSSF, NHIF, Housing Levy.
-- Run once: mysql -u root -p cmr_ecommerce < database/migrate_payroll.sql
-- Then ensure permission exists: INSERT IGNORE INTO permissions (name, description) VALUES ('payroll', 'Payroll and employees');
-- Grant to Admin: INSERT IGNORE INTO role_permissions (role_id, permission_id) SELECT r.id, p.id FROM roles r JOIN permissions p ON p.name = 'payroll' WHERE r.name = 'Admin';
-- Grant to Finance: INSERT IGNORE INTO role_permissions (role_id, permission_id) SELECT r.id, p.id FROM roles r JOIN permissions p ON p.name = 'payroll' WHERE r.name = 'Finance';

USE cmr_ecommerce;

-- Employees
CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    id_number VARCHAR(50) NULL COMMENT 'National ID',
    kra_pin VARCHAR(50) NULL,
    nssf_number VARCHAR(50) NULL,
    nhif_number VARCHAR(50) NULL,
    email VARCHAR(150) NULL,
    phone VARCHAR(50) NULL,
    department VARCHAR(100) NULL,
    job_title VARCHAR(100) NULL,
    employment_date DATE NULL,
    basic_salary DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    allowances DECIMAL(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Total monthly allowances (taxable)',
    bank_name VARCHAR(100) NULL,
    bank_account VARCHAR(100) NULL,
    user_id INT NULL COMMENT 'Link to users.id for sales commission',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Payroll run (e.g. March 2026)
CREATE TABLE IF NOT EXISTS payroll_runs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    period_month TINYINT NOT NULL,
    period_year SMALLINT NOT NULL,
    run_date DATE NULL,
    status ENUM('DRAFT','FINALIZED') NOT NULL DEFAULT 'DRAFT',
    notes TEXT NULL,
    created_by INT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_payroll_period (period_year, period_month),
    CONSTRAINT fk_payroll_user FOREIGN KEY (created_by) REFERENCES users(id)
);

-- Payslip per employee per run
CREATE TABLE IF NOT EXISTS payroll_payslips (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payroll_run_id INT NOT NULL,
    employee_id INT NOT NULL,
    gross_salary DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    taxable_income DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    paye DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    nssf_employee DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    nhif DECIMAL(15,2) NOT NULL DEFAULT 0.00 COMMENT 'SHIF contribution (2.75% of gross)',
    housing_levy DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    other_deductions DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    commission DECIMAL(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Sales commission (when employee linked to user)',
    net_pay DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ps_run FOREIGN KEY (payroll_run_id) REFERENCES payroll_runs(id) ON DELETE CASCADE,
    CONSTRAINT fk_ps_employee FOREIGN KEY (employee_id) REFERENCES employees(id),
    UNIQUE KEY uk_ps_run_emp (payroll_run_id, employee_id)
);

-- PAYE bands (monthly taxable income) - Kenya KRA rates; editable for future changes
CREATE TABLE IF NOT EXISTS paye_bands (
    id INT AUTO_INCREMENT PRIMARY KEY,
    band_order TINYINT NOT NULL,
    amount_from DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    amount_to DECIMAL(15,2) NOT NULL,
    rate_percent DECIMAL(5,2) NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);

-- Seed PAYE bands (first 24k @ 10%, next 8333 @ 25%, next 467667 @ 30%, next 300k @ 32.5%, above 800k @ 35%)
INSERT IGNORE INTO paye_bands (id, band_order, amount_from, amount_to, rate_percent) VALUES
(1, 1, 0, 24000, 10.00),
(2, 2, 24000, 32333, 25.00),
(3, 3, 32333, 500000, 30.00),
(4, 4, 500000, 800000, 32.50),
(5, 5, 800000, 999999999, 35.00);

-- SHIF uses 2.75% of gross (no bands). Legacy nhif_bands kept for reference only; calculation is in code.