<?php
/**
 * PayrollPeriods — monthly payroll lifecycle.
 *
 * States: draft → computed → approved → paid (any → void)
 *   * draft:     period exists but records not generated
 *   * computed:  records generated for all active drivers with statutory calc
 *   * approved:  accrual JE posted (Dr expense / Cr payable + net-pay)
 *   * paid:      payment JE posted (Dr net-pay-payable / Cr bank)
 *
 * Journal shape on APPROVE:
 *   Dr 7002 Basic Salaries              (sum(base + commission + other_earnings))
 *   Dr 7028 Subsistence Allowances      (sum(allowances))
 *   Dr 7016 NSSF Contribution           (sum(nssf_er))
 *   Dr 7026 Skills Development Levy     (sum(sdl))
 *   Dr 7031 Workers Compensation Fund   (sum(wcf))
 *   Cr 4005 Salary payable              (sum(net_pay))
 *   Cr 4003 NSSF - Payable              (sum(nssf_ee + nssf_er))
 *   Cr 4004 PAYE - Payable              (sum(paye))
 *   Cr 4006 SDL - Payable               (sum(sdl))
 *   Cr 4008 WCF - Payable               (sum(wcf))
 *   Cr 4001 Accrued Expenses            (sum(health_ins))
 *   Cr 4001 Accrued Expenses            (sum(other_deductions))    [if any]
 *
 * On PAY:
 *   Dr 4005 Salary payable              (sum(net_pay))
 *   Cr <bank/mobile per payment_method> (sum(net_pay))
 */
class PayrollPeriods extends Model
{
    private function s($v): string { return trim((string)$v); }

    /* ==================== reads ============================================ */

    public function listPaginated(int $page, int $perPage, array $filters = []): array
    {
        $where  = ['deleted = 0', 'tenant_id = :tenant'];
        $params = [':tenant' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1];
        if (!empty($filters['status'])) { $where[] = 'status = :st'; $params[':st'] = $this->s($filters['status']); }
        if (!empty($filters['year']))   { $where[] = 'YEAR(period_start) = :y'; $params[':y'] = (int)$filters['year']; }

        $whereSql = implode(' AND ', $where);
        $db = new DB();

        $countRs = $db->mySelect("SELECT COUNT(*) AS n FROM payroll_periods WHERE $whereSql", $params);
        $total = (int)($countRs[0]['n'] ?? 0);

        $page    = max(1, $page);
        $perPage = max(1, min(200, $perPage));
        $offset  = ($page - 1) * $perPage;

        $rows = $db->mySelect(
            "SELECT * FROM payroll_periods WHERE $whereSql
              ORDER BY period_start DESC LIMIT $perPage OFFSET $offset",
            $params
        ) ?: [];

        return ['rows' => $rows, 'total' => $total];
    }

    public function byId(int $id): array
    {
        $rs = (new DB())->mySelect("SELECT * FROM payroll_periods WHERE c0d = :id AND deleted = 0 LIMIT 1", [':id' => $id]);
        return $rs[0] ?? [];
    }

    public function recordsFor(int $periodId): array
    {
        return (new DB())->mySelect(
            "SELECT pr.*, d.full_name, d.employee_no, d.phone_1
               FROM payroll_records pr
               JOIN drivers d ON d.c0d = pr.driver_id
              WHERE pr.period_id = :p
           ORDER BY d.full_name ASC",
            [':p' => $periodId]
        ) ?: [];
    }

    public function recordById(int $recordId): array
    {
        $rs = (new DB())->mySelect(
            "SELECT pr.*, d.full_name, d.employee_no, d.phone_1, d.bank_account_no, d.mobile_money_msisdn,
                    p.period_no, p.period_start, p.period_end, p.status AS period_status
               FROM payroll_records pr
               JOIN drivers d          ON d.c0d = pr.driver_id
               JOIN payroll_periods p  ON p.c0d = pr.period_id
              WHERE pr.c0d = :id LIMIT 1",
            [':id' => $recordId]
        );
        return $rs[0] ?? [];
    }

    /* ==================== writes =========================================== */

    /** Create a fresh draft period for a given month. Idempotent by (tenant, month). */
    public function createPeriod(int $year, int $month, int $uid): array
    {
        if ($year < 2020 || $year > 2100 || $month < 1 || $month > 12) return ['ok' => false, 'msg' => 'Invalid year/month.'];
        $start = sprintf('%04d-%02d-01', $year, $month);
        $end   = date('Y-m-t', strtotime($start));
        $no    = 'PAY-' . $year . '-' . sprintf('%02d', $month);

        $db = new DB();
        $ex = $db->mySelect("SELECT c0d FROM payroll_periods WHERE period_start = :s AND deleted = 0", [':s' => $start]);
        if (!empty($ex)) return ['ok' => false, 'msg' => 'A period already exists for ' . date('F Y', strtotime($start)) . '.', 'id' => (int)$ex[0]['c0d']];

        $r = $db->insertUpdate(
            "INSERT INTO payroll_periods (tenant_id, period_no, period_start, period_end, status, created_by, created_on)
             VALUES (:tenant, :no, :s, :e, 'draft', :uid, NOW())",
            [':tenant' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1, ':no' => $no, ':s' => $start, ':e' => $end, ':uid' => $uid]
        );
        return ['ok' => $r['success'] > 0, 'msg' => 'Period created.', 'id' => (int)$r['last_id']];
    }

    /**
     * Compute records for every active driver: pull base salary, per-trip
     * commission, apply statutory. Overwrites existing draft records.
     */
    public function compute(int $periodId, int $uid): array
    {
        $p = $this->byId($periodId);
        if (!$p)                                    return ['ok' => false, 'msg' => 'Period not found.'];
        if (in_array($p['status'], ['approved','paid','void'], true)) return ['ok' => false, 'msg' => 'Period is ' . $p['status'] . ' — cannot recompute.'];

        $db = new DB();
        $db->insertUpdate("DELETE FROM payroll_records WHERE period_id = :p", [':p' => $periodId]);

        // Load all active drivers with salary + commission config
        $drivers = $db->mySelect(
            "SELECT c0d, full_name, monthly_salary, commission_pct, health_ins_amount, nssf_ee_pct
               FROM drivers
              WHERE deleted = 0 AND status != 'terminated'
                AND monthly_salary > 0", []
        ) ?: [];

        $totGross = 0; $totDed = 0; $totNet = 0; $totEr = 0; $count = 0;

        foreach ($drivers as $d) {
            // Trip commission = sum(invoice_amount) for closed trips in period × commission_pct
            $tripAgg = $db->mySelect(
                "SELECT COUNT(*) trips, COALESCE(SUM(invoice_amount), 0) revenue
                   FROM trips
                  WHERE driver_id = :did AND deleted = 0 AND status = 'closed'
                    AND actual_end BETWEEN :s AND :e",
                [':did' => (int)$d['c0d'], ':s' => $p['period_start'], ':e' => $p['period_end']]
            );
            $tripCount   = (int)($tripAgg[0]['trips']   ?? 0);
            $tripRevenue = (float)($tripAgg[0]['revenue'] ?? 0);
            $commission  = round($tripRevenue * (float)$d['commission_pct'] / 100, 2);

            $base       = (float)$d['monthly_salary'];
            $allowances = 0;  // MVP: no automatic allowances; manual add on editRecord
            $otherEarn  = 0;
            $gross      = round($base + $commission + $allowances + $otherEarn, 2);

            $tax = PayrollTax::computeAll($gross, [
                'nssf_ee_pct' => (float)$d['nssf_ee_pct'],
                'health_ins'  => (float)$d['health_ins_amount'],
            ]);
            $otherDed = 0;
            $net      = round($tax['net_before_other'] - $otherDed, 2);

            $db->insertUpdate(
                "INSERT INTO payroll_records
                    (tenant_id, period_id, driver_id,
                     base_salary, trip_count, trip_revenue, commission, allowances, other_earnings, gross_pay,
                     paye, nssf_ee, nssf_er, sdl, wcf, health_ins, other_deductions, net_pay,
                     status, edited_by, edited_on)
                 VALUES
                    (:tenant, :p, :d,
                     :base, :tc, :tr, :com, :al, :oe, :gross,
                     :paye, :ne, :nr, :sdl, :wcf, :hi, :od, :net,
                     'draft', :uid, NOW())",
                [
                    ':tenant' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1,
                    ':p' => $periodId, ':d' => (int)$d['c0d'],
                    ':base' => $base, ':tc' => $tripCount, ':tr' => $tripRevenue, ':com' => $commission,
                    ':al' => $allowances, ':oe' => $otherEarn, ':gross' => $gross,
                    ':paye' => $tax['paye'], ':ne' => $tax['nssf_ee'], ':nr' => $tax['nssf_er'],
                    ':sdl' => $tax['sdl'], ':wcf' => $tax['wcf'], ':hi' => $tax['health_ins'],
                    ':od' => $otherDed, ':net' => $net,
                    ':uid' => $uid,
                ]
            );

            $totGross += $gross;
            $totDed   += $tax['paye'] + $tax['nssf_ee'] + $tax['health_ins'] + $otherDed;
            $totNet   += $net;
            $totEr    += $tax['nssf_er'] + $tax['sdl'] + $tax['wcf'];
            $count++;
        }

        $db->insertUpdate(
            "UPDATE payroll_periods
                SET status = 'computed', computed_by = :uid, computed_at = NOW(),
                    total_gross = :g, total_deductions = :d, total_net = :n, total_employer = :e, driver_count = :c
              WHERE c0d = :id",
            [':uid' => $uid, ':g' => $totGross, ':d' => $totDed, ':n' => $totNet, ':e' => $totEr, ':c' => $count, ':id' => $periodId]
        );

        return ['ok' => true, 'msg' => "Computed {$count} driver record(s). Total net: TZS " . number_format($totNet, 0) . '.'];
    }

    /**
     * Approve the period: post the accrual JE using client's COA codes.
     */
    public function approve(int $periodId, int $uid, string $uname): array
    {
        $p = $this->byId($periodId);
        if (!$p)                              return ['ok' => false, 'msg' => 'Period not found.'];
        if ($p['status'] !== 'computed')      return ['ok' => false, 'msg' => 'Only computed periods can be approved.'];

        $records = $this->recordsFor($periodId);
        if (empty($records)) return ['ok' => false, 'msg' => 'No records to approve.'];

        // Aggregate for the JE
        $sums = ['base'=>0,'commission'=>0,'other_earn'=>0,'allowances'=>0,
                 'paye'=>0,'nssf_ee'=>0,'nssf_er'=>0,'sdl'=>0,'wcf'=>0,
                 'health_ins'=>0,'other_ded'=>0,'net'=>0];
        foreach ($records as $r) {
            $sums['base']       += (float)$r['base_salary'];
            $sums['commission'] += (float)$r['commission'];
            $sums['other_earn'] += (float)$r['other_earnings'];
            $sums['allowances'] += (float)$r['allowances'];
            $sums['paye']       += (float)$r['paye'];
            $sums['nssf_ee']    += (float)$r['nssf_ee'];
            $sums['nssf_er']    += (float)$r['nssf_er'];
            $sums['sdl']        += (float)$r['sdl'];
            $sums['wcf']        += (float)$r['wcf'];
            $sums['health_ins'] += (float)$r['health_ins'];
            $sums['other_ded']  += (float)$r['other_deductions'];
            $sums['net']        += (float)$r['net_pay'];
        }

        $lines = [];
        // Debits — expense side
        $salaryDr = round($sums['base'] + $sums['commission'] + $sums['other_earn'], 2);
        if ($salaryDr > 0) $lines[] = ['account_code' => '7002', 'debit' => $salaryDr, 'narrative' => "Basic salaries & commission · {$p['period_no']}"];
        if ($sums['allowances'] > 0) $lines[] = ['account_code' => '7028', 'debit' => round($sums['allowances'], 2), 'narrative' => "Subsistence allowances · {$p['period_no']}"];
        if ($sums['nssf_er']    > 0) $lines[] = ['account_code' => '7016', 'debit' => round($sums['nssf_er'], 2),    'narrative' => "NSSF contribution (employer) · {$p['period_no']}"];
        if ($sums['sdl']        > 0) $lines[] = ['account_code' => '7026', 'debit' => round($sums['sdl'], 2),        'narrative' => "Skills Development Levy · {$p['period_no']}"];
        if ($sums['wcf']        > 0) $lines[] = ['account_code' => '7031', 'debit' => round($sums['wcf'], 2),        'narrative' => "Workers Compensation Fund · {$p['period_no']}"];

        // Credits — payables & net-pay
        if ($sums['net']        > 0) $lines[] = ['account_code' => '4005', 'credit' => round($sums['net'], 2),        'narrative' => "Net salary payable · {$p['period_no']}"];
        $nssfTotal = round($sums['nssf_ee'] + $sums['nssf_er'], 2);
        if ($nssfTotal          > 0) $lines[] = ['account_code' => '4003', 'credit' => $nssfTotal,                    'narrative' => "NSSF payable (ee + er) · {$p['period_no']}"];
        if ($sums['paye']       > 0) $lines[] = ['account_code' => '4004', 'credit' => round($sums['paye'], 2),       'narrative' => "PAYE payable · {$p['period_no']}"];
        if ($sums['sdl']        > 0) $lines[] = ['account_code' => '4006', 'credit' => round($sums['sdl'], 2),        'narrative' => "SDL payable · {$p['period_no']}"];
        if ($sums['wcf']        > 0) $lines[] = ['account_code' => '4008', 'credit' => round($sums['wcf'], 2),        'narrative' => "WCF payable · {$p['period_no']}"];
        $accruedCr = round($sums['health_ins'] + $sums['other_ded'], 2);
        if ($accruedCr          > 0) $lines[] = ['account_code' => '4001', 'credit' => $accruedCr,                    'narrative' => "Health insurance & other deductions · {$p['period_no']}"];

        $jp  = new JournalPosting();
        $res = $jp->post([
            'entry_date'     => (string)$p['period_end'],
            'source_type'    => 'PAYROLL_ACCRUAL',
            'source_id'      => $periodId,
            'source_ref'     => $p['period_no'],
            'narrative'      => 'Payroll accrual · ' . $p['period_no'],
            'currency'       => 'TZS',
            'posted_by'      => $uid,
            'posted_by_name' => $uname,
            'lines'          => $lines,
        ]);
        if (!$res['ok']) return ['ok' => false, 'msg' => 'JE failed: ' . $res['msg']];

        (new DB())->insertUpdate(
            "UPDATE payroll_periods SET status = 'approved', approved_by = :uid, approved_at = NOW(), accrual_je_id = :je WHERE c0d = :id",
            [':uid' => $uid, ':je' => $res['id'], ':id' => $periodId]
        );
        (new DB())->insertUpdate(
            "UPDATE payroll_records SET status = 'approved' WHERE period_id = :p AND status = 'draft'",
            [':p' => $periodId]
        );

        return ['ok' => true, 'msg' => "Approved · JE {$res['je_no']} posted."];
    }

    /**
     * Pay the period: post the payment JE Dr 4005 / Cr <bank per method>.
     * $method = 'bank_transfer' (default → 3001 CRDB) | 'mobile_money' (→ 3006) | 'cash' (→ 3003).
     */
    public function pay(int $periodId, string $method, int $uid, string $uname): array
    {
        $p = $this->byId($periodId);
        if (!$p)                              return ['ok' => false, 'msg' => 'Period not found.'];
        if ($p['status'] !== 'approved')      return ['ok' => false, 'msg' => 'Only approved periods can be paid.'];

        $creditMap = [
            'cash'          => '3003',
            'mobile_money'  => '3006',
            'bank_transfer' => '3001',
            'cheque'        => '3001',
        ];
        $creditCode = $creditMap[$method] ?? '3001';
        $amount     = round((float)$p['total_net'], 2);
        if ($amount <= 0) return ['ok' => false, 'msg' => 'Nothing to pay (total_net is zero).'];

        $lines = [
            ['account_code' => '4005',       'debit'  => $amount, 'narrative' => "Settle salary payable · {$p['period_no']}"],
            ['account_code' => $creditCode,  'credit' => $amount, 'narrative' => "Payroll paid via {$method} · {$p['period_no']}"],
        ];
        $jp  = new JournalPosting();
        $res = $jp->post([
            'entry_date'     => date('Y-m-d'),
            'source_type'    => 'PAYROLL_PAYMENT',
            'source_id'      => $periodId,
            'source_ref'     => $p['period_no'],
            'narrative'      => 'Payroll payment · ' . $p['period_no'],
            'currency'       => 'TZS',
            'posted_by'      => $uid,
            'posted_by_name' => $uname,
            'lines'          => $lines,
        ]);
        if (!$res['ok']) return ['ok' => false, 'msg' => 'JE failed: ' . $res['msg']];

        (new DB())->insertUpdate(
            "UPDATE payroll_periods SET status = 'paid', paid_by = :uid, paid_at = NOW(), payment_je_id = :je WHERE c0d = :id",
            [':uid' => $uid, ':je' => $res['id'], ':id' => $periodId]
        );
        (new DB())->insertUpdate(
            "UPDATE payroll_records SET status = 'paid' WHERE period_id = :p AND status = 'approved'",
            [':p' => $periodId]
        );
        return ['ok' => true, 'msg' => "Paid · JE {$res['je_no']} posted."];
    }

    /** Manual edit to one record: allowances / other_earnings / other_deductions with recompute. */
    public function updateRecord(int $recordId, array $d, int $uid): array
    {
        $rec = $this->recordById($recordId);
        if (!$rec)                                             return ['ok' => false, 'msg' => 'Record not found.'];
        if (in_array($rec['period_status'], ['approved','paid','void'], true)) return ['ok' => false, 'msg' => 'Period is ' . $rec['period_status'] . ' — cannot edit records.'];

        $allowances = isset($d['allowances'])        ? round((float)$d['allowances'], 2)        : (float)$rec['allowances'];
        $otherEarn  = isset($d['other_earnings'])    ? round((float)$d['other_earnings'], 2)    : (float)$rec['other_earnings'];
        $otherDed   = isset($d['other_deductions']) ? round((float)$d['other_deductions'], 2)  : (float)$rec['other_deductions'];

        $base       = (float)$rec['base_salary'];
        $commission = (float)$rec['commission'];
        $gross      = round($base + $commission + $allowances + $otherEarn, 2);
        $config     = (new DB())->mySelect("SELECT nssf_ee_pct, health_ins_amount FROM drivers WHERE c0d = :id", [':id' => (int)$rec['driver_id']])[0] ?? [];
        $tax        = PayrollTax::computeAll($gross, [
            'nssf_ee_pct' => (float)($config['nssf_ee_pct']       ?? PayrollTax::DEFAULT_NSSF_EE_PCT),
            'health_ins'  => (float)($config['health_ins_amount'] ?? 0),
        ]);
        $net        = round($tax['net_before_other'] - $otherDed, 2);

        (new DB())->insertUpdate(
            "UPDATE payroll_records SET
                allowances = :al, other_earnings = :oe, other_deductions = :od, gross_pay = :g,
                paye = :paye, nssf_ee = :ne, nssf_er = :nr, sdl = :sdl, wcf = :wcf, health_ins = :hi,
                net_pay = :net, notes = :n, edited_by = :uid, edited_on = NOW()
              WHERE c0d = :id",
            [':al' => $allowances, ':oe' => $otherEarn, ':od' => $otherDed, ':g' => $gross,
             ':paye' => $tax['paye'], ':ne' => $tax['nssf_ee'], ':nr' => $tax['nssf_er'],
             ':sdl' => $tax['sdl'], ':wcf' => $tax['wcf'], ':hi' => $tax['health_ins'],
             ':net' => $net, ':n' => trim((string)($d['notes'] ?? '')), ':uid' => $uid, ':id' => $recordId]
        );

        // Rollup the period totals
        $this->refreshPeriodTotals((int)$rec['period_id']);
        return ['ok' => true, 'msg' => 'Record updated.'];
    }

    private function refreshPeriodTotals(int $periodId): void
    {
        (new DB())->insertUpdate(
            "UPDATE payroll_periods p
                SET total_gross = (SELECT COALESCE(SUM(gross_pay), 0) FROM payroll_records WHERE period_id = p.c0d),
                    total_deductions = (SELECT COALESCE(SUM(paye + nssf_ee + health_ins + other_deductions), 0) FROM payroll_records WHERE period_id = p.c0d),
                    total_net = (SELECT COALESCE(SUM(net_pay), 0) FROM payroll_records WHERE period_id = p.c0d),
                    total_employer = (SELECT COALESCE(SUM(nssf_er + sdl + wcf), 0) FROM payroll_records WHERE period_id = p.c0d),
                    driver_count = (SELECT COUNT(*) FROM payroll_records WHERE period_id = p.c0d)
              WHERE c0d = :id",
            [':id' => $periodId]
        );
    }

    public function voidPeriod(int $id, string $reason, int $uid): array
    {
        $p = $this->byId($id);
        if (!$p)                     return ['ok' => false, 'msg' => 'Period not found.'];
        if ($p['status'] === 'paid') return ['ok' => false, 'msg' => 'Paid periods cannot be voided from the UI. Contact your accountant to reverse the JE.'];
        (new DB())->insertUpdate(
            "UPDATE payroll_periods SET status = 'void', void_by = :uid, void_at = NOW(), void_reason = :r WHERE c0d = :id",
            [':uid' => $uid, ':r' => trim($reason), ':id' => $id]
        );
        return ['ok' => true, 'msg' => 'Period voided.'];
    }
}
