<?php
/**
 * Payroll engine — Ghana statutory calculations (SSNIT + PAYE) and table self-heal.
 *
 * SSNIT: employee 5.5% of basic, employer 13% of basic (Tier 1+2+3 total 18.5%).
 * PAYE:  progressive monthly bands (GRA). Taxable income = gross − employee SSNIT
 *        − reliefs. Bands are kept in one place so they can be updated when GRA
 *        revises them.
 */

if (!function_exists('payroll_ensure_tables')) {

    function payroll_ensure_tables(mysqli $conn): void
    {
        static $done = false;
        if ($done) return;
        $done = true;
        $conn->query("CREATE TABLE IF NOT EXISTS payroll_staff_pay (
            staff_id INT PRIMARY KEY,
            basic_salary DECIMAL(12,2) NOT NULL DEFAULT 0,
            transport_allowance DECIMAL(12,2) NOT NULL DEFAULT 0,
            housing_allowance DECIMAL(12,2) NOT NULL DEFAULT 0,
            other_allowance DECIMAL(12,2) NOT NULL DEFAULT 0,
            other_deductions DECIMAL(12,2) NOT NULL DEFAULT 0,
            tax_relief DECIMAL(12,2) NOT NULL DEFAULT 0,
            ssnit_exempt TINYINT(1) NOT NULL DEFAULT 0,
            active TINYINT(1) NOT NULL DEFAULT 1,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");
        $conn->query("CREATE TABLE IF NOT EXISTS payroll_runs (
            id INT AUTO_INCREMENT PRIMARY KEY,
            period VARCHAR(7) NOT NULL,
            status ENUM('draft','finalized') NOT NULL DEFAULT 'draft',
            note VARCHAR(200) NULL,
            created_by INT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            finalized_at DATETIME NULL,
            UNIQUE KEY uniq_period (period)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");
        $conn->query("CREATE TABLE IF NOT EXISTS payroll_items (
            id INT AUTO_INCREMENT PRIMARY KEY,
            run_id INT NOT NULL,
            staff_id INT NOT NULL,
            staff_name VARCHAR(120) NULL,
            basic DECIMAL(12,2) NOT NULL DEFAULT 0,
            allowances DECIMAL(12,2) NOT NULL DEFAULT 0,
            gross DECIMAL(12,2) NOT NULL DEFAULT 0,
            ssnit_employee DECIMAL(12,2) NOT NULL DEFAULT 0,
            ssnit_employer DECIMAL(12,2) NOT NULL DEFAULT 0,
            taxable DECIMAL(12,2) NOT NULL DEFAULT 0,
            paye DECIMAL(12,2) NOT NULL DEFAULT 0,
            other_deductions DECIMAL(12,2) NOT NULL DEFAULT 0,
            net DECIMAL(12,2) NOT NULL DEFAULT 0,
            bank_name VARCHAR(100) NULL,
            bank_account VARCHAR(50) NULL,
            ssnit_number VARCHAR(50) NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            KEY idx_run (run_id), KEY idx_staff (staff_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");
    }

    /** Ghana monthly PAYE bands: [width_or_null_for_top, rate]. Update when GRA revises. */
    function payroll_paye_bands(): array
    {
        return [
            [490.00,   0.00],
            [110.00,   0.05],
            [130.00,   0.10],
            [3166.67,  0.175],
            [16000.00, 0.25],
            [30520.00, 0.30],
            [null,     0.35],   // remainder
        ];
    }

    /** Progressive PAYE on a monthly taxable income. */
    function payroll_compute_paye(float $taxable): float
    {
        if ($taxable <= 0) return 0.0;
        $tax = 0.0; $left = $taxable;
        foreach (payroll_paye_bands() as [$width, $rate]) {
            if ($left <= 0) break;
            $slice = ($width === null) ? $left : min($left, $width);
            $tax  += $slice * $rate;
            $left -= $slice;
        }
        return round($tax, 2);
    }

    /**
     * Compute a full payroll breakdown from a pay row (payroll_staff_pay columns).
     * Returns basic/allowances/gross/ssnit_employee/ssnit_employer/taxable/paye/net.
     */
    function payroll_compute(array $pay): array
    {
        $basic      = (float) ($pay['basic_salary'] ?? 0);
        $allowances = (float) ($pay['transport_allowance'] ?? 0)
                    + (float) ($pay['housing_allowance'] ?? 0)
                    + (float) ($pay['other_allowance'] ?? 0);
        $gross      = $basic + $allowances;

        $ssnitEmp   = empty($pay['ssnit_exempt']) ? round($basic * 0.055, 2) : 0.0;
        $ssnitEmployer = empty($pay['ssnit_exempt']) ? round($basic * 0.13, 2) : 0.0;

        $relief     = (float) ($pay['tax_relief'] ?? 0);
        $taxable    = max(0.0, $gross - $ssnitEmp - $relief);
        $paye       = payroll_compute_paye($taxable);

        $otherDed   = (float) ($pay['other_deductions'] ?? 0);
        $net        = round($gross - $ssnitEmp - $paye - $otherDed, 2);

        return [
            'basic'          => round($basic, 2),
            'allowances'     => round($allowances, 2),
            'gross'          => round($gross, 2),
            'ssnit_employee' => $ssnitEmp,
            'ssnit_employer' => $ssnitEmployer,
            'taxable'        => round($taxable, 2),
            'paye'           => $paye,
            'other_deductions' => round($otherDed, 2),
            'net'            => $net,
        ];
    }

    function payroll_can_manage(string $role): bool
    {
        return in_array($role, ['admin', 'accountant', 'headteacher', 'director'], true);
    }
}
