-- Pacific Logistics · slice 24 · Driver Payroll (Iter 4 Turn 5)
--
-- Monthly payroll for drivers. Salary + per-trip commission + allowances,
-- less PAYE + NSSF + Health Insurance + other deductions = net pay.
-- Employer costs (NSSF-employer, SDL, WCF) hit the P&L as expenses,
-- statutory balances land in the client's 4003/4004/4006/4008 payable
-- accounts (all correctly mapped to Pacific's COA via Turn 1.5).
--
-- Two tables:
--   payroll_periods    — one row per month × tenant
--   payroll_records    — one row per driver × period (with statutory breakdown)
--
-- Plus 4 salary columns added to the drivers table:
--   monthly_salary, commission_pct, health_ins_amount, nssf_ee_pct
-- (Defaults: 0, 0, 0, 10 respectively.)
--
-- Statutory rates + PAYE brackets are hard-coded in the PayrollTax model
-- (see app/models/PayrollTax.php) because they change rarely; the model's
-- constants carry a comment explaining when TRA last updated them.


-- ═══════════════════════════════════════════════════════════════════════
-- Add salary columns to drivers (idempotent via stored proc)
-- ═══════════════════════════════════════════════════════════════════════
DELIMITER $$
DROP PROCEDURE IF EXISTS pl_add_col_if_missing_24 $$
CREATE PROCEDURE pl_add_col_if_missing_24(IN tbl VARCHAR(64), IN col VARCHAR(64), IN spec VARCHAR(500))
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.COLUMNS
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tbl AND COLUMN_NAME = col
    ) THEN
        SET @s = CONCAT('ALTER TABLE ', tbl, ' ADD COLUMN ', col, ' ', spec);
        PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
    END IF;
END $$
DELIMITER ;

CALL pl_add_col_if_missing_24('drivers', 'monthly_salary',    'DECIMAL(14,2) NOT NULL DEFAULT 0');
CALL pl_add_col_if_missing_24('drivers', 'commission_pct',    'DECIMAL(5,2) NOT NULL DEFAULT 0');   -- % of trip revenue
CALL pl_add_col_if_missing_24('drivers', 'health_ins_amount', 'DECIMAL(10,2) NOT NULL DEFAULT 0'); -- monthly premium (employee deduction)
CALL pl_add_col_if_missing_24('drivers', 'nssf_ee_pct',       'DECIMAL(5,2) NOT NULL DEFAULT 10'); -- employee NSSF % (standard 10)
CALL pl_add_col_if_missing_24('drivers', 'bank_account_no',   'VARCHAR(60) NULL');                  -- for bank-transfer payroll
CALL pl_add_col_if_missing_24('drivers', 'mobile_money_msisdn', 'VARCHAR(30) NULL');                -- for mobile-money payroll

DROP PROCEDURE pl_add_col_if_missing_24;


-- ═══════════════════════════════════════════════════════════════════════
-- payroll_periods — one per month
-- ═══════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS payroll_periods (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id        INT UNSIGNED NOT NULL DEFAULT 1,
    period_no        VARCHAR(20)  NOT NULL,             -- PAY-YYYY-MM
    period_start     DATE         NOT NULL,             -- always month start
    period_end       DATE         NOT NULL,             -- always month end
    status           VARCHAR(20)  NOT NULL DEFAULT 'draft',
                                                    -- draft | computed | approved | paid | void
    -- Rollups (recomputed on compute()):
    total_gross      DECIMAL(14,2) NOT NULL DEFAULT 0,
    total_deductions DECIMAL(14,2) NOT NULL DEFAULT 0,
    total_net        DECIMAL(14,2) NOT NULL DEFAULT 0,
    total_employer   DECIMAL(14,2) NOT NULL DEFAULT 0,  -- NSSF-er + SDL + WCF
    driver_count     INT UNSIGNED  NOT NULL DEFAULT 0,
    -- Workflow
    computed_by      INT UNSIGNED NULL,
    computed_at      DATETIME     NULL,
    approved_by      INT UNSIGNED NULL,
    approved_at      DATETIME     NULL,
    paid_by          INT UNSIGNED NULL,
    paid_at          DATETIME     NULL,
    accrual_je_id    INT UNSIGNED NULL,                 -- JE posted on approve
    payment_je_id    INT UNSIGNED NULL,                 -- JE posted on pay
    void_by          INT UNSIGNED NULL,
    void_at          DATETIME     NULL,
    void_reason      VARCHAR(255) NULL,
    notes            TEXT         NULL,
    created_by       INT UNSIGNED NULL,
    created_on       DATETIME     NULL,
    edited_by        INT UNSIGNED NULL,
    edited_on        DATETIME     NULL,
    deleted          TINYINT(1)   NOT NULL DEFAULT 0,
    PRIMARY KEY (c0d),
    UNIQUE KEY uq_pp_no (period_no),
    UNIQUE KEY uq_pp_month (tenant_id, period_start),
    KEY idx_pp_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- payroll_records — one per driver × period
-- ═══════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS payroll_records (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id        INT UNSIGNED NOT NULL DEFAULT 1,
    period_id        INT UNSIGNED NOT NULL,
    driver_id        INT UNSIGNED NOT NULL,
    -- Earnings
    base_salary      DECIMAL(14,2) NOT NULL DEFAULT 0,
    trip_count       INT UNSIGNED  NOT NULL DEFAULT 0,
    trip_revenue     DECIMAL(14,2) NOT NULL DEFAULT 0,   -- sum of invoice_amount for period's closed trips
    commission       DECIMAL(14,2) NOT NULL DEFAULT 0,
    allowances       DECIMAL(14,2) NOT NULL DEFAULT 0,   -- subsistence, etc.
    other_earnings   DECIMAL(14,2) NOT NULL DEFAULT 0,   -- bonuses, one-offs (manual)
    gross_pay        DECIMAL(14,2) NOT NULL DEFAULT 0,   -- = base + commission + allowances + other_earnings
    -- Statutory
    paye             DECIMAL(14,2) NOT NULL DEFAULT 0,
    nssf_ee          DECIMAL(14,2) NOT NULL DEFAULT 0,
    nssf_er          DECIMAL(14,2) NOT NULL DEFAULT 0,   -- employer side, kept for JE + reporting
    sdl              DECIMAL(14,2) NOT NULL DEFAULT 0,   -- employer only
    wcf              DECIMAL(14,2) NOT NULL DEFAULT 0,   -- employer only
    health_ins       DECIMAL(14,2) NOT NULL DEFAULT 0,
    -- Adjustments (manual overrides)
    other_deductions DECIMAL(14,2) NOT NULL DEFAULT 0,   -- loans, advances
    net_pay          DECIMAL(14,2) NOT NULL DEFAULT 0,   -- gross - paye - nssf_ee - health_ins - other_deductions
    -- Breakdown detail
    breakdown_json   JSON NULL,                          -- allowance items, deduction items with reasons
    -- Workflow status
    status           VARCHAR(20)  NOT NULL DEFAULT 'draft',
                                                    -- draft | approved | paid | void
    notes            VARCHAR(500) NULL,
    edited_by        INT UNSIGNED NULL,
    edited_on        DATETIME     NULL,
    PRIMARY KEY (c0d),
    UNIQUE KEY uq_pr (period_id, driver_id),
    KEY idx_pr_driver (driver_id, period_id),
    KEY idx_pr_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- Grants — Accounting/Admin scope. Fleet + Ops can view.
-- ═══════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
(10, 'Payroll', 'index',   1, NOW()), (10, 'Payroll', 'view',    1, NOW()), (10, 'Payroll', 'add',       1, NOW()),
(10, 'Payroll', 'save',    1, NOW()), (10, 'Payroll', 'compute', 1, NOW()), (10, 'Payroll', 'recompute', 1, NOW()),
(10, 'Payroll', 'approve', 1, NOW()), (10, 'Payroll', 'pay',     1, NOW()), (10, 'Payroll', 'voidRecord',1, NOW()),
(10, 'Payroll', 'voidPeriod', 1, NOW()), (10, 'Payroll', 'payslip', 1, NOW()), (10, 'Payroll', 'editRecord', 1, NOW()),
(10, 'Payroll', 'updateRecord', 1, NOW()),
(30, 'Payroll', 'index',   1, NOW()), (30, 'Payroll', 'view',    1, NOW()), (30, 'Payroll', 'payslip', 1, NOW()),
(20, 'Payroll', 'index',   1, NOW()), (20, 'Payroll', 'view',    1, NOW()), (20, 'Payroll', 'payslip', 1, NOW()),
(1,  'Payroll', 'index',   1, NOW()), (1,  'Payroll', 'view',    1, NOW());
