<?php
/**
 * TaxReturns — monthly statutory returns for TRA filing.
 *
 * All three reports use the same monthly window (year+month) and read
 * from POSTED, non-deleted journals. Nothing is persisted — every call
 * recomputes from the ledger, so retroactive edits to payroll or VAT
 * postings flow through automatically.
 *
 * Tanzania statutory rates (as at 2026-08):
 *   PAYE     — brackets, capped at 30%; see PayrollTax
 *   NSSF     — 10% ee + 10% er (capped at gross)
 *   SDL      — 3.5% er on gross salary bill
 *   WCF      — 0.5% er on gross salary bill (quarterly filing, monthly figures)
 *   VAT      — 18% standard rate (report only shows what's already posted)
 */
class TaxReturns
{
    private int $tenantId;

    /* COA codes we care about (validated in slice 29) */
    private const COA_VAT_RECOVERABLE = '3008';  // input VAT (debit-natural)
    private const COA_VAT_PAYABLE     = '4010';  // output VAT (credit-natural)
    private const COA_NSSF_PAYABLE    = '4003';
    private const COA_PAYE_PAYABLE    = '4004';
    private const COA_SDL_PAYABLE     = '4006';
    private const COA_WCF_PAYABLE     = '4008';

    public function __construct(?int $tenantId = null)
    {
        $this->tenantId = $tenantId ?? (defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1);
    }

    /* ══════════════════════════════════════════════════════════════════
       PAYE monthly return — one row per employee, plus totals.
       Format matches what the accountant needs to fill in on TRA's
       ITX215 employer return.
       ══════════════════════════════════════════════════════════════════ */
    public function paye(int $year, int $month): array
    {
        [$from, $to] = $this->monthBounds($year, $month);
        $db = new DB();

        $rows = $db->mySelect(
            "SELECT pr.c0d, pr.driver_id, pr.gross_pay, pr.paye, pr.base_salary,
                    pr.commission, pr.allowances, pr.other_earnings, pr.nssf_ee,
                    d.full_name, d.employee_no, d.id_no
               FROM payroll_records pr
               JOIN payroll_periods pp ON pp.c0d = pr.period_id
               JOIN drivers d          ON d.c0d = pr.driver_id
              WHERE pp.period_start = :from
                AND pp.period_end   = :to
                AND pp.status IN ('approved','paid')
                AND pp.tenant_id = :t
           ORDER BY d.full_name",
            [':from' => $from, ':to' => $to, ':t' => $this->tenantId]
        ) ?: [];

        // Also pull the PAYE-Payable ledger movement in the same window
        // so the accountant can reconcile the report figure against the JE.
        $ledger = $this->accountMovement(self::COA_PAYE_PAYABLE, $from, $to);

        $totalGross = $totalPaye = $totalNssfEe = 0.0;
        foreach ($rows as $r) {
            $totalGross  += (float)$r['gross_pay'];
            $totalPaye   += (float)$r['paye'];
            $totalNssfEe += (float)$r['nssf_ee'];
        }

        return [
            'year'        => $year,
            'month'       => $month,
            'from'        => $from,
            'to'          => $to,
            'employees'   => $rows,
            'total_gross'   => round($totalGross, 2),
            'total_paye'    => round($totalPaye, 2),
            'total_nssf_ee' => round($totalNssfEe, 2),
            'ledger_credit' => $ledger['credit'],
            'ledger_debit'  => $ledger['debit'],
            'ledger_net'    => round($ledger['credit'] - $ledger['debit'], 2),
            // Positive net = accrued but not yet paid to TRA
            'due_date'      => $this->tRADueDate($year, $month, 'paye'),
        ];
    }

    /* ══════════════════════════════════════════════════════════════════
       VAT monthly return — Output VAT (from sales) minus Input VAT
       (from purchases) → net payable / refundable.
       ══════════════════════════════════════════════════════════════════ */
    public function vat(int $year, int $month): array
    {
        [$from, $to] = $this->monthBounds($year, $month);

        // Output VAT: credits to 4010 during the month
        $out = $this->accountMovement(self::COA_VAT_PAYABLE, $from, $to);
        // Input VAT: debits to 3008 during the month
        $in  = $this->accountMovement(self::COA_VAT_RECOVERABLE, $from, $to);

        // Per-line details so the accountant can trace each figure back
        $outputLines = $this->accountLines(self::COA_VAT_PAYABLE, $from, $to);
        $inputLines  = $this->accountLines(self::COA_VAT_RECOVERABLE, $from, $to);

        // Estimated sales / purchase base = VAT / 0.18 (TRA standard rate)
        $outputVat = round($out['credit'] - $out['debit'], 2);   // net output
        $inputVat  = round($in['debit']  - $in['credit'], 2);    // net input
        $netVat    = round($outputVat - $inputVat, 2);           // payable if +, refundable if -

        return [
            'year'          => $year,
            'month'         => $month,
            'from'          => $from,
            'to'            => $to,
            'output_vat'    => $outputVat,
            'input_vat'     => $inputVat,
            'net_vat'       => $netVat,
            'sales_base_estimated'    => round($outputVat / 0.18, 2),
            'purchase_base_estimated' => round($inputVat  / 0.18, 2),
            'output_lines'  => $outputLines,
            'input_lines'   => $inputLines,
            'due_date'      => $this->tRADueDate($year, $month, 'vat'),
        ];
    }

    /* ══════════════════════════════════════════════════════════════════
       Statutory summary — NSSF, SDL, WCF for one month. All three come
       out of payroll_records; the ledger movements are shown alongside
       for reconciliation.
       ══════════════════════════════════════════════════════════════════ */
    public function statutory(int $year, int $month): array
    {
        [$from, $to] = $this->monthBounds($year, $month);
        $db = new DB();

        $agg = $db->mySelect(
            "SELECT COUNT(*) AS employees,
                    COALESCE(SUM(pr.gross_pay),   0) AS gross,
                    COALESCE(SUM(pr.nssf_ee),     0) AS nssf_ee,
                    COALESCE(SUM(pr.nssf_er),     0) AS nssf_er,
                    COALESCE(SUM(pr.sdl),         0) AS sdl,
                    COALESCE(SUM(pr.wcf),         0) AS wcf,
                    COALESCE(SUM(pr.paye),        0) AS paye
               FROM payroll_records pr
               JOIN payroll_periods pp ON pp.c0d = pr.period_id
              WHERE pp.period_start = :from
                AND pp.period_end   = :to
                AND pp.status IN ('approved','paid')
                AND pp.tenant_id = :t",
            [':from' => $from, ':to' => $to, ':t' => $this->tenantId]
        );
        $a = $agg[0] ?? ['employees' => 0, 'gross' => 0, 'nssf_ee' => 0, 'nssf_er' => 0, 'sdl' => 0, 'wcf' => 0, 'paye' => 0];

        // Employer share of NSSF is 10% of gross, ee share is 10% too, so
        // total NSSF remittance to the fund = ee + er
        $nssfTotal = (float)$a['nssf_ee'] + (float)$a['nssf_er'];

        return [
            'year'       => $year,
            'month'      => $month,
            'from'       => $from,
            'to'         => $to,
            'employees'  => (int)$a['employees'],
            'gross'      => round((float)$a['gross'],   2),
            'nssf_ee'    => round((float)$a['nssf_ee'], 2),
            'nssf_er'    => round((float)$a['nssf_er'], 2),
            'nssf_total' => round($nssfTotal,           2),
            'sdl'        => round((float)$a['sdl'],     2),
            'wcf'        => round((float)$a['wcf'],     2),
            'paye'       => round((float)$a['paye'],    2),
            'total_statutory' => round(
                (float)$a['paye'] + $nssfTotal + (float)$a['sdl'] + (float)$a['wcf'],
                2
            ),
            'ledger' => [
                'nssf' => $this->accountMovement(self::COA_NSSF_PAYABLE, $from, $to),
                'paye' => $this->accountMovement(self::COA_PAYE_PAYABLE, $from, $to),
                'sdl'  => $this->accountMovement(self::COA_SDL_PAYABLE,  $from, $to),
                'wcf'  => $this->accountMovement(self::COA_WCF_PAYABLE,  $from, $to),
            ],
            'due_dates' => [
                'nssf' => $this->tRADueDate($year, $month, 'nssf'),
                'paye' => $this->tRADueDate($year, $month, 'paye'),
                'sdl'  => $this->tRADueDate($year, $month, 'sdl'),
                'wcf'  => $this->tRADueDate($year, $month, 'wcf'),
            ],
        ];
    }

    /* ══════════════════════════════════════════════════════════════════
       P9 annual certificate — Jan-Dec breakdown for one employee.

       Matches the shape TRA's employer withholding certificate expects:
       for each month the employee has a payroll record, we surface the
       usual grid — Base | Allowances | Gross | NSSF Ee | Taxable | PAYE —
       plus annual totals for the summary panel.

       Returns null if the driver has no approved/paid records for that
       year — no P9 to render.
       ══════════════════════════════════════════════════════════════════ */
    public function p9(int $driverId, int $year): ?array
    {
        $db = new DB();
        $driver = $db->mySelect(
            "SELECT c0d, full_name, employee_no, id_no, hire_date, phone_1
               FROM drivers WHERE c0d = :id AND deleted = 0 LIMIT 1",
            [':id' => $driverId]
        );
        if (empty($driver)) return null;

        $rows = $db->mySelect(
            "SELECT MONTH(pp.period_start) AS mo,
                    pr.base_salary, pr.commission, pr.allowances, pr.other_earnings,
                    pr.gross_pay, pr.paye, pr.nssf_ee, pr.health_ins, pr.other_deductions,
                    pr.net_pay
               FROM payroll_records pr
               JOIN payroll_periods pp ON pp.c0d = pr.period_id
              WHERE pr.driver_id = :did
                AND YEAR(pp.period_start) = :yr
                AND pp.status IN ('approved','paid')
                AND pp.deleted = 0
                AND pp.tenant_id = :t
           ORDER BY pp.period_start",
            [':did' => $driverId, ':yr' => $year, ':t' => $this->tenantId]
        ) ?: [];

        if (empty($rows)) return null;

        // Zero-filled 12-month grid; a driver hired mid-year just has blanks
        // in the early months, which is the correct thing to show TRA.
        $months = [];
        $monthNames = [1=>'Jan',2=>'Feb',3=>'Mar',4=>'Apr',5=>'May',6=>'Jun',7=>'Jul',8=>'Aug',9=>'Sep',10=>'Oct',11=>'Nov',12=>'Dec'];
        for ($m = 1; $m <= 12; $m++) {
            $months[$m] = [
                'mo'        => $m, 'name' => $monthNames[$m],
                'base'      => 0.0, 'commission' => 0.0, 'allowances' => 0.0, 'other_e' => 0.0,
                'gross'     => 0.0, 'nssf_ee'    => 0.0,
                'taxable'   => 0.0, 'paye' => 0.0,
                'health_ins'=> 0.0, 'other_d'    => 0.0, 'net' => 0.0,
                'has_data'  => false,
            ];
        }
        $total = [
            'base' => 0.0, 'commission' => 0.0, 'allowances' => 0.0, 'other_e' => 0.0,
            'gross' => 0.0, 'nssf_ee' => 0.0, 'taxable' => 0.0, 'paye' => 0.0,
            'health_ins' => 0.0, 'other_d' => 0.0, 'net' => 0.0,
        ];

        foreach ($rows as $r) {
            $m       = (int)$r['mo'];
            $gross   = (float)$r['gross_pay'];
            $nssfEe  = (float)$r['nssf_ee'];
            $taxable = round($gross - $nssfEe, 2);   // Taxable = Gross − NSSF employee share

            $months[$m] = array_merge($months[$m], [
                'base'       => (float)$r['base_salary'],
                'commission' => (float)$r['commission'],
                'allowances' => (float)$r['allowances'],
                'other_e'    => (float)$r['other_earnings'],
                'gross'      => $gross,
                'nssf_ee'    => $nssfEe,
                'taxable'    => $taxable,
                'paye'       => (float)$r['paye'],
                'health_ins' => (float)$r['health_ins'],
                'other_d'    => (float)$r['other_deductions'],
                'net'        => (float)$r['net_pay'],
                'has_data'   => true,
            ]);
            $total['base']       += (float)$r['base_salary'];
            $total['commission'] += (float)$r['commission'];
            $total['allowances'] += (float)$r['allowances'];
            $total['other_e']    += (float)$r['other_earnings'];
            $total['gross']      += $gross;
            $total['nssf_ee']    += $nssfEe;
            $total['taxable']    += $taxable;
            $total['paye']       += (float)$r['paye'];
            $total['health_ins'] += (float)$r['health_ins'];
            $total['other_d']    += (float)$r['other_deductions'];
            $total['net']        += (float)$r['net_pay'];
        }
        foreach ($total as $k => $v) $total[$k] = round($v, 2);

        return [
            'year'      => $year,
            'driver'    => $driver[0],
            'months'    => array_values($months),
            'totals'    => $total,
            'months_covered' => count($rows),
        ];
    }

    /* ══════════════════════════════════════════════════════════════════
       PAYE CSV export — one row per employee, matching the column order
       TRA's PAYE-return upload template expects (as at 2026).
       ══════════════════════════════════════════════════════════════════ */
    public function payeCsv(int $year, int $month): string
    {
        $r = $this->paye($year, $month);
        $fh = fopen('php://temp', 'r+');

        $period = sprintf('%04d-%02d', $year, $month);
        fputcsv($fh, ['TRA PAYE Return — ' . $period]);
        fputcsv($fh, ['Employer', defined('COMPANY_NAME') ? COMPANY_NAME : 'Pacific Group']);
        fputcsv($fh, ['Period', $r['from'] . ' to ' . $r['to']]);
        fputcsv($fh, []);
        fputcsv($fh, [
            'Employee No', 'Full Name', 'TIN / NIDA',
            'Base Salary', 'Commission', 'Allowances', 'Other Earnings',
            'Gross Pay', 'NSSF Ee', 'PAYE',
        ]);

        foreach ($r['employees'] as $e) {
            fputcsv($fh, [
                $e['employee_no'] ?: '',
                $e['full_name'],
                $e['id_no'] ?: '',
                number_format((float)$e['base_salary'],     2, '.', ''),
                number_format((float)$e['commission'],      2, '.', ''),
                number_format((float)$e['allowances'],      2, '.', ''),
                number_format((float)$e['other_earnings'],  2, '.', ''),
                number_format((float)$e['gross_pay'],       2, '.', ''),
                number_format((float)$e['nssf_ee'],         2, '.', ''),
                number_format((float)$e['paye'],            2, '.', ''),
            ]);
        }

        fputcsv($fh, []);
        fputcsv($fh, [
            'TOTALS', '', '', '', '', '', '',
            number_format($r['total_gross'],   2, '.', ''),
            number_format($r['total_nssf_ee'], 2, '.', ''),
            number_format($r['total_paye'],    2, '.', ''),
        ]);

        rewind($fh);
        $csv = stream_get_contents($fh);
        fclose($fh);
        return $csv;
    }

    /* ── helpers ───────────────────────────────────────────────────── */

    /** [YYYY-MM-01, YYYY-MM-{last}] for the given year+month. */
    private function monthBounds(int $year, int $month): array
    {
        $month = max(1, min(12, $month));
        $from = sprintf('%04d-%02d-01', $year, $month);
        $to   = date('Y-m-t', strtotime($from));
        return [$from, $to];
    }

    private function accountMovement(string $accountCode, string $from, string $to): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT COALESCE(SUM(jl.debit),  0) AS debit,
                    COALESCE(SUM(jl.credit), 0) AS credit
               FROM journal_lines jl
               JOIN journal_entries je    ON je.c0d = jl.journal_entry_id
                                         AND je.status = 'POSTED'
                                         AND je.deleted = 0
                                         AND je.entry_date BETWEEN :from AND :to
               JOIN chart_of_accounts coa ON coa.c0d = jl.account_id
              WHERE coa.account_code = :ac",
            [':ac' => $accountCode, ':from' => $from, ':to' => $to]
        );
        return [
            'debit'  => round((float)($rs[0]['debit']  ?? 0), 2),
            'credit' => round((float)($rs[0]['credit'] ?? 0), 2),
        ];
    }

    private function accountLines(string $accountCode, string $from, string $to): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT je.je_no, je.entry_date, je.source_type, je.narrative AS je_narrative,
                    jl.debit, jl.credit, jl.narrative AS line_narrative,
                    jl.customer_id, jl.vendor_id
               FROM journal_lines jl
               JOIN journal_entries je    ON je.c0d = jl.journal_entry_id
                                         AND je.status = 'POSTED'
                                         AND je.deleted = 0
                                         AND je.entry_date BETWEEN :from AND :to
               JOIN chart_of_accounts coa ON coa.c0d = jl.account_id
              WHERE coa.account_code = :ac
           ORDER BY je.entry_date, je.c0d",
            [':ac' => $accountCode, ':from' => $from, ':to' => $to]
        ) ?: [];
    }

    /**
     * TRA filing deadlines. Static rules for now — a calendar table would
     * be overkill until we handle prior-year rate changes and public
     * holidays that push a due date to the next business day.
     *   PAYE / SDL / NSSF  →  7th of following month
     *   WCF                →  30th of month after quarter end
     *   VAT                →  20th of following month
     */
    private function tRADueDate(int $year, int $month, string $tax): string
    {
        $next = strtotime(sprintf('%04d-%02d-01 +1 month', $year, $month));
        $y = (int)date('Y', $next);
        $m = (int)date('m', $next);
        switch (strtolower($tax)) {
            case 'vat':  return sprintf('%04d-%02d-20', $y, $m);
            case 'wcf':
                // 30th of month after the quarter closes
                $qEnd = $month <= 3 ? [$year, 3] : ($month <= 6 ? [$year, 6] : ($month <= 9 ? [$year, 9] : [$year, 12]));
                $due = strtotime(sprintf('%04d-%02d-01 +1 month', $qEnd[0], $qEnd[1]));
                return date('Y-m-d', strtotime('+29 days', $due));
            case 'paye':
            case 'sdl':
            case 'nssf':
            default:
                return sprintf('%04d-%02d-07', $y, $m);
        }
    }
}
