<?php
/**
 * FinancialReports — Trial Balance, Profit & Loss, and Balance Sheet.
 *
 * All three reports read the same posted-journal facts and shape the output
 * differently. Only POSTED (non-deleted) journal entries are included; drafts
 * and voided entries are ignored so the reports match the general ledger a
 * TRA auditor would see.
 *
 * Client's chart is 5-typed (ASSET / LIABILITY / EQUITY / REVENUE / EXPENSE)
 * with account_code prefix conventions:
 *
 *   1xxx  Contra assets (accumulated depreciation, natural credit balance)
 *   2xxx  Non-current assets
 *   3xxx  Current assets
 *   4xxx  Current liabilities
 *   5xxx  Non-current liabilities
 *   6xxx  Equity
 *   7xxx  Expenses
 *   8xxx  Revenue
 *
 * The Balance Sheet uses code prefix to split current vs non-current
 * sub-sections; the Trial Balance sorts by code alone.
 */
class FinancialReports
{
    private int $tenantId;

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

    /* ══════════════════════════════════════════════════════════════════
       Trial Balance — every account with a non-zero balance in the period.
       ══════════════════════════════════════════════════════════════════ */
    public function trialBalance(string $from, string $to): array
    {
        $rows = $this->balancesInPeriod($from, $to);
        $totalDebit = $totalCredit = 0.0;
        $out = [];
        foreach ($rows as $r) {
            $debit  = (float)$r['debit'];
            $credit = (float)$r['credit'];
            $net    = $debit - $credit;
            // Show natural-side balance: debit-natural accounts (ASSET/EXPENSE)
            // land in the debit column; credit-natural (LIABILITY/EQUITY/REVENUE)
            // land in the credit column. Contra amounts flip signs — a credit
            // balance on an asset account (e.g. accumulated depreciation)
            // still shows in the credit column.
            if (abs($net) < 0.005) continue;
            if ($net >= 0) {
                $out[] = ['acc' => $r, 'debit' => round($net, 2), 'credit' => 0.0];
                $totalDebit += $net;
            } else {
                $out[] = ['acc' => $r, 'debit' => 0.0, 'credit' => round(-$net, 2)];
                $totalCredit += -$net;
            }
        }
        return [
            'rows'         => $out,
            'total_debit'  => round($totalDebit, 2),
            'total_credit' => round($totalCredit, 2),
            'balanced'     => abs($totalDebit - $totalCredit) < 0.01,
            'from'         => $from,
            'to'           => $to,
        ];
    }

    /* ══════════════════════════════════════════════════════════════════
       Profit & Loss — Revenue − Expenses = Net Income for the period.
       ══════════════════════════════════════════════════════════════════ */
    public function pnl(string $from, string $to): array
    {
        $rows = $this->balancesInPeriod($from, $to);

        $revenue = $expense = [];
        $totalRevenue = $totalExpense = 0.0;

        foreach ($rows as $r) {
            $debit  = (float)$r['debit'];
            $credit = (float)$r['credit'];
            if ($r['account_type'] === 'REVENUE') {
                // Revenue accounts credit-natural → amount = credit − debit
                $amount = $credit - $debit;
                if (abs($amount) < 0.005) continue;
                $revenue[] = ['acc' => $r, 'amount' => round($amount, 2)];
                $totalRevenue += $amount;
            } elseif ($r['account_type'] === 'EXPENSE') {
                // Expense accounts debit-natural → amount = debit − credit
                $amount = $debit - $credit;
                if (abs($amount) < 0.005) continue;
                $expense[] = ['acc' => $r, 'amount' => round($amount, 2)];
                $totalExpense += $amount;
            }
        }

        return [
            'revenue_rows'   => $revenue,
            'expense_rows'   => $expense,
            'revenue_total'  => round($totalRevenue, 2),
            'expense_total'  => round($totalExpense, 2),
            'net_income'     => round($totalRevenue - $totalExpense, 2),
            'from'           => $from,
            'to'             => $to,
        ];
    }

    /* ══════════════════════════════════════════════════════════════════
       Balance Sheet — everything from inception through $asOf.
       Groups assets and liabilities by "current" (3xxx / 4xxx) vs
       "non-current" (1xxx, 2xxx / 5xxx). Includes current-period net income
       under Equity (unclosed books convention).
       ══════════════════════════════════════════════════════════════════ */
    public function balanceSheet(string $asOf): array
    {
        // Inception through as-of, so use a very early "from"
        $rows = $this->balancesInPeriod('1970-01-01', $asOf);

        $nonCurrentAssets = $currentAssets = [];
        $currentLiab      = $nonCurrentLiab = [];
        $equity           = [];
        $totalNCA = $totalCA = $totalCL = $totalNCL = $totalEq = 0.0;
        $totalRevenue = $totalExpense = 0.0;

        foreach ($rows as $r) {
            $debit  = (float)$r['debit'];
            $credit = (float)$r['credit'];
            $code   = $r['account_code'];
            switch ($r['account_type']) {
                case 'ASSET':
                    $bal = $debit - $credit;
                    if (abs($bal) < 0.005) break;
                    // Codes 1xxx (contra) and 2xxx (non-current) group under
                    // Non-current; 3xxx groups under Current.
                    if ($code[0] === '3') { $currentAssets[] = ['acc' => $r, 'amount' => round($bal, 2)]; $totalCA += $bal; }
                    else                  { $nonCurrentAssets[] = ['acc' => $r, 'amount' => round($bal, 2)]; $totalNCA += $bal; }
                    break;
                case 'LIABILITY':
                    $bal = $credit - $debit;
                    if (abs($bal) < 0.005) break;
                    if ($code[0] === '4') { $currentLiab[] = ['acc' => $r, 'amount' => round($bal, 2)]; $totalCL += $bal; }
                    else                  { $nonCurrentLiab[] = ['acc' => $r, 'amount' => round($bal, 2)]; $totalNCL += $bal; }
                    break;
                case 'EQUITY':
                    $bal = $credit - $debit;
                    if (abs($bal) < 0.005) break;
                    $equity[] = ['acc' => $r, 'amount' => round($bal, 2)];
                    $totalEq += $bal;
                    break;
                case 'REVENUE':
                    $totalRevenue += ($credit - $debit);
                    break;
                case 'EXPENSE':
                    $totalExpense += ($debit - $credit);
                    break;
            }
        }

        $netIncome  = $totalRevenue - $totalExpense;
        $totalAssets      = round($totalNCA + $totalCA, 2);
        $totalLiabilities = round($totalCL + $totalNCL, 2);
        $totalEquity      = round($totalEq + $netIncome, 2);
        $totalLE          = round($totalLiabilities + $totalEquity, 2);

        return [
            'non_current_assets' => $nonCurrentAssets,
            'current_assets'     => $currentAssets,
            'current_liab'       => $currentLiab,
            'non_current_liab'   => $nonCurrentLiab,
            'equity'             => $equity,
            'total_nca'          => round($totalNCA, 2),
            'total_ca'           => round($totalCA, 2),
            'total_cl'           => round($totalCL, 2),
            'total_ncl'          => round($totalNCL, 2),
            'total_eq'           => round($totalEq, 2),
            'net_income'         => round($netIncome, 2),
            'total_assets'       => $totalAssets,
            'total_liab'         => $totalLiabilities,
            'total_equity'       => $totalEquity,
            'total_liab_eq'      => $totalLE,
            'balanced'           => abs($totalAssets - $totalLE) < 0.01,
            'as_of'              => $asOf,
        ];
    }

    /* ══════════════════════════════════════════════════════════════════
       Cash Flow — direct method.

       Follows every journal line that hits a cash / cash-equivalent
       account (3001 CRDB, 3002 NMB, 3003 Cash, 3006 Mobile Money Float,
       3009 Fuel Card Account) and categorises the movement by the parent
       journal's source_type.

       Categories (matching how PostingLine builds source_type):
         Operating in  — RECEIPT_APPLY, SALE
         Operating out — SUPPLIER_PAYMENT, FUEL, EXPENSE, WORK_ORDER
         Payroll out   — PAYROLL_PAYMENT
         Tax out       — TAX_REMIT
         Investing     — FIXED_ASSET
         Financing     — LOAN, EQUITY, DRAWING
         Bank adj      — BANK_ADJ
         Other         — everything else, surfaced so the accountant sees it
       ══════════════════════════════════════════════════════════════════ */
    public function cashFlow(string $from, string $to): array
    {
        $db = new DB();

        // 1. Every cash-side journal line in the window
        $lines = $db->mySelect(
            "SELECT jl.debit, jl.credit,
                    je.entry_date, je.source_type, je.narrative
               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.is_bank = 1
                 OR coa.account_code IN ('3003','3006','3009')",
            [':from' => $from, ':to' => $to]
        ) ?: [];

        // 2. Opening balance = every cash-side movement strictly before $from
        $rs = $db->mySelect(
            "SELECT COALESCE(SUM(jl.debit - jl.credit), 0) AS bal
               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 < :from
               JOIN chart_of_accounts  coa ON coa.c0d = jl.account_id
              WHERE coa.is_bank = 1
                 OR coa.account_code IN ('3003','3006','3009')",
            [':from' => $from]
        );
        $opening = (float)($rs[0]['bal'] ?? 0);

        // 3. Categorise
        $cat = [
            'operating_in'  => 0.0,
            'operating_out' => 0.0,
            'payroll_out'   => 0.0,
            'tax_out'       => 0.0,
            'investing_in'  => 0.0,
            'investing_out' => 0.0,
            'financing_in'  => 0.0,
            'financing_out' => 0.0,
            'bank_adj_in'   => 0.0,
            'bank_adj_out'  => 0.0,
            'other_in'      => 0.0,
            'other_out'     => 0.0,
        ];
        $bySource = [];   // source_type => ['in', 'out', 'count', 'category']

        foreach ($lines as $l) {
            $inflow  = (float)$l['debit'];    // cash coming in = debit to cash account
            $outflow = (float)$l['credit'];   // cash going out = credit
            [$catIn, $catOut] = $this->cashFlowBuckets($l['source_type']);
            $cat[$catIn]  += $inflow;
            $cat[$catOut] += $outflow;

            $src = $l['source_type'] ?: 'UNKNOWN';
            if (!isset($bySource[$src])) {
                $bySource[$src] = [
                    'source_type' => $src,
                    'in'          => 0.0,
                    'out'         => 0.0,
                    'count'       => 0,
                    'category'    => $this->cashFlowCategory($src),
                ];
            }
            $bySource[$src]['in']    += $inflow;
            $bySource[$src]['out']   += $outflow;
            $bySource[$src]['count']++;
        }

        // 4. Section totals
        $operating = round(
            $cat['operating_in'] - $cat['operating_out']
            - $cat['payroll_out'] - $cat['tax_out']
            + $cat['bank_adj_in'] - $cat['bank_adj_out'],
            2
        );
        $investing = round($cat['investing_in'] - $cat['investing_out'], 2);
        $financing = round($cat['financing_in'] - $cat['financing_out'], 2);
        $other     = round($cat['other_in']     - $cat['other_out'],     2);

        $netChange = round($operating + $investing + $financing + $other, 2);
        $closing   = round($opening + $netChange, 2);

        // 5. Cross-check against the actual cash balance at end of window
        $rs = $db->mySelect(
            "SELECT COALESCE(SUM(jl.debit - jl.credit), 0) AS bal
               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 <= :to
               JOIN chart_of_accounts  coa ON coa.c0d = jl.account_id
              WHERE coa.is_bank = 1
                 OR coa.account_code IN ('3003','3006','3009')",
            [':to' => $to]
        );
        $actualClosing = (float)($rs[0]['bal'] ?? 0);

        // Sort bySource descending on absolute movement so the biggest
        // buckets sit at the top of the drill-down table.
        uasort($bySource, fn($a, $b) => (abs($b['in']) + abs($b['out'])) <=> (abs($a['in']) + abs($a['out'])));

        return [
            'from'                => $from,
            'to'                  => $to,
            'opening_cash'        => round($opening, 2),
            'closing_cash'        => $closing,
            'actual_closing'      => round($actualClosing, 2),
            'reconciles'          => abs($closing - $actualClosing) < 0.01,
            'net_change'          => $netChange,
            'operating'           => $operating,
            'investing'           => $investing,
            'financing'           => $financing,
            'other'               => $other,
            'buckets'             => array_map(fn($v) => round($v, 2), $cat),
            'by_source'           => array_values($bySource),
            'cash_accounts'       => $this->cashAccountsBreakdown($to),
        ];
    }

    private function cashFlowCategory(string $sourceType): string
    {
        $map = [
            'RECEIPT_APPLY'     => 'Operating',
            'SALE'              => 'Operating',
            'INVOICE'           => 'Operating',
            'SUPPLIER_PAYMENT'  => 'Operating',
            'FUEL'              => 'Operating',
            'EXPENSE'           => 'Operating',
            'WORK_ORDER'        => 'Operating',
            'BILL'              => 'Operating',
            'PAYROLL_PAYMENT'   => 'Payroll',
            'PAYROLL_ACCRUAL'   => 'Payroll',
            'TAX_REMIT'         => 'Tax',
            'BANK_ADJ'          => 'Bank adjustments',
            'FIXED_ASSET'       => 'Investing',
            'ASSET_DISPOSAL'    => 'Investing',
            'LOAN'              => 'Financing',
            'LOAN_REPAYMENT'    => 'Financing',
            'EQUITY'            => 'Financing',
            'DRAWING'           => 'Financing',
        ];
        return $map[$sourceType] ?? 'Other';
    }

    /** Returns [inflow-bucket-key, outflow-bucket-key] for a given source_type. */
    private function cashFlowBuckets(string $sourceType): array
    {
        switch ($sourceType) {
            case 'RECEIPT_APPLY': case 'SALE': case 'INVOICE':
                return ['operating_in', 'operating_out'];
            case 'SUPPLIER_PAYMENT': case 'FUEL': case 'EXPENSE':
            case 'WORK_ORDER': case 'BILL':
                return ['operating_in', 'operating_out'];
            case 'PAYROLL_PAYMENT': case 'PAYROLL_ACCRUAL':
                return ['operating_in', 'payroll_out'];
            case 'TAX_REMIT':
                return ['operating_in', 'tax_out'];
            case 'BANK_ADJ':
                return ['bank_adj_in', 'bank_adj_out'];
            case 'FIXED_ASSET': case 'ASSET_DISPOSAL':
                return ['investing_in', 'investing_out'];
            case 'LOAN': case 'EQUITY':
                return ['financing_in', 'financing_out'];
            case 'LOAN_REPAYMENT': case 'DRAWING':
                return ['financing_in', 'financing_out'];
            default:
                return ['other_in', 'other_out'];
        }
    }

    /** Per-cash-account closing balances so the Cash Flow view can reconcile visually. */
    private function cashAccountsBreakdown(string $asOf): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT coa.c0d, coa.account_code, coa.account_name,
                    COALESCE(SUM(jl.debit - jl.credit), 0) AS balance
               FROM chart_of_accounts coa
          LEFT JOIN journal_lines jl   ON jl.account_id = coa.c0d
          LEFT JOIN journal_entries je ON je.c0d = jl.journal_entry_id
                                     AND je.status = 'POSTED'
                                     AND je.deleted = 0
                                     AND je.entry_date <= :to
              WHERE coa.is_active = 1
                AND (coa.is_bank = 1 OR coa.account_code IN ('3003','3006','3009'))
           GROUP BY coa.c0d, coa.account_code, coa.account_name
           ORDER BY coa.account_code",
            [':to' => $asOf]
        ) ?: [];
    }

    /* ══════════════════════════════════════════════════════════════════
       Shared query — per-account debit + credit sums over a date window.
       Returns rows for accounts that have ANY movement in the window; the
       caller decides what to do with zero-net rows.
       ══════════════════════════════════════════════════════════════════ */
    private function balancesInPeriod(string $from, string $to): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT coa.c0d, coa.account_code, coa.account_name, coa.account_type,
                    COALESCE(SUM(jl.debit),  0) AS debit,
                    COALESCE(SUM(jl.credit), 0) AS credit
               FROM chart_of_accounts coa
               JOIN journal_lines jl   ON jl.account_id = coa.c0d
               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
              WHERE coa.is_active = 1
           GROUP BY coa.c0d, coa.account_code, coa.account_name, coa.account_type
           ORDER BY coa.account_code",
            [':from' => $from, ':to' => $to]
        );
        return $rs ?: [];
    }

    /**
     * Ledger drill-down — every journal line hitting a single account
     * inside the window, newest first. Used by report views that link the
     * account name for the reader to trace a balance back to its sources.
     */
    public function ledger(int $accountId, string $from, string $to): array
    {
        $db = new DB();
        $rs = $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.truck_id, jl.driver_id, jl.trip_id, jl.customer_id
               FROM journal_lines jl
               JOIN journal_entries je ON je.c0d = jl.journal_entry_id
                                     AND je.status = 'POSTED'
                                     AND je.deleted = 0
              WHERE jl.account_id = :acc
                AND je.entry_date BETWEEN :from AND :to
           ORDER BY je.entry_date DESC, je.c0d DESC, jl.line_no",
            [':acc' => $accountId, ':from' => $from, ':to' => $to]
        );
        return $rs ?: [];
    }
}
