<?php
/**
 * JournalPosting · double-entry engine.
 *
 * Any module that moves money calls one of the post* methods here. Every
 * journal_entries row is enforced to be balanced (sum debit == sum credit)
 * before it's persisted; on imbalance nothing is written.
 *
 * post(array $entry) is the primitive; postForPayment() /postForReceipt() /
 * postForRevenue() are the domain-specific wrappers modules should prefer.
 */
class JournalPosting extends Model
{
    public function nextJeNo(): string
    {
        $db   = new DB();
        $year = (int) date('Y');
        $rs   = $db->mySelect(
            "SELECT je_no FROM journal_entries WHERE je_no LIKE :p ORDER BY c0d DESC LIMIT 1",
            [':p' => 'JE-' . $year . '-%']
        );
        $seq = (!empty($rs) && preg_match('/-(\d+)$/', $rs[0]['je_no'], $m)) ? (int)$m[1] + 1 : 1;
        return sprintf('JE-%d-%04d', $year, $seq);
    }

    /**
     * Primitive: post a balanced journal.
     * $entry = [
     *   'entry_date' => 'YYYY-MM-DD',
     *   'source_type' => 'PAYMENT|RECEIPT|INVOICE|MANUAL|OPENING',
     *   'source_id'   => int,
     *   'source_ref'  => 'PAY-2026-0001',
     *   'narrative'   => 'string',
     *   'currency'    => 'TZS',
     *   'fx_rate'     => 1.0,
     *   'posted_by'   => uid,
     *   'posted_by_name' => uname,
     *   'lines' => [
     *     ['account_code' => '6210', 'debit' => 250000, 'credit' => 0, 'narrative' => '...', 'project_id' => 1, 'lpo_id' => 1, 'payment_id' => 1, 'bank_account_id' => 0],
     *     ['account_code' => '1200', 'debit' => 0, 'credit' => 250000, ..., 'bank_account_id' => 1],
     *   ]
     * ]
     */
    public function post(array $entry): array
    {
        $lines = $entry['lines'] ?? [];
        if (count($lines) < 2) {
            return ['ok' => false, 'msg' => 'Journal must have at least 2 lines.', 'id' => 0];
        }
        // Resolve account codes → ids + denormalize name -----------------
        $db = new DB();
        $totDr = 0.0; $totCr = 0.0;
        $resolved = [];
        foreach ($lines as $i => $ln) {
            $code = (string)($ln['account_code'] ?? '');
            $id   = (int)   ($ln['account_id']   ?? 0);
            if ($code === '' && $id === 0) return ['ok' => false, 'msg' => "Line " . ($i+1) . ": account_code or account_id required."];
            if ($id === 0) {
                $rs = $db->mySelect("SELECT c0d, account_name FROM chart_of_accounts WHERE account_code = :c AND is_active = 1 LIMIT 1", [':c' => $code]);
                if (empty($rs)) return ['ok' => false, 'msg' => "Line " . ($i+1) . ": account_code '{$code}' not found."];
                $id   = (int)$rs[0]['c0d'];
                $name = (string)$rs[0]['account_name'];
            } else {
                $rs = $db->mySelect("SELECT account_code, account_name FROM chart_of_accounts WHERE c0d = :id LIMIT 1", [':id' => $id]);
                if (empty($rs)) return ['ok' => false, 'msg' => "Line " . ($i+1) . ": account_id {$id} not found."];
                $code = (string)$rs[0]['account_code'];
                $name = (string)$rs[0]['account_name'];
            }
            $dr = round((float)($ln['debit']  ?? 0), 2);
            $cr = round((float)($ln['credit'] ?? 0), 2);
            if ($dr < 0 || $cr < 0) return ['ok' => false, 'msg' => "Line " . ($i+1) . ": negative amounts not allowed."];
            if ($dr > 0 && $cr > 0) return ['ok' => false, 'msg' => "Line " . ($i+1) . ": a single line cannot be both debit and credit."];
            if ($dr === 0.0 && $cr === 0.0) return ['ok' => false, 'msg' => "Line " . ($i+1) . ": zero-value line."];
            $totDr += $dr; $totCr += $cr;
            $resolved[] = [
                'account_id'   => $id,
                'account_code' => $code,
                'account_name' => $name,
                'debit'        => $dr,
                'credit'       => $cr,
                'narrative'    => (string)($ln['narrative'] ?? ''),
                // rsflow legacy tags
                'project_id'   => (int)   ($ln['project_id'] ?? 0),
                'lpo_id'       => (int)   ($ln['lpo_id']     ?? 0),
                'payment_id'   => (int)   ($ln['payment_id'] ?? 0),
                'bank_account_id' => (int)($ln['bank_account_id'] ?? 0),
                // Pacific analytical tags — the reason ops + finance run on one dataset
                'truck_id'       => (int) ($ln['truck_id']       ?? 0) ?: null,
                'driver_id'      => (int) ($ln['driver_id']      ?? 0) ?: null,
                'trip_id'        => (int) ($ln['trip_id']        ?? 0) ?: null,
                'trip_request_id'=> (int) ($ln['trip_request_id']?? 0) ?: null,
                'customer_id'    => (int) ($ln['customer_id']    ?? 0) ?: null,
                'vendor_id'      => (int) ($ln['vendor_id']      ?? 0) ?: null,
                'expense_id'     => (int) ($ln['expense_id']     ?? 0) ?: null,
                'fuel_entry_id'  => (int) ($ln['fuel_entry_id']  ?? 0) ?: null,
                'work_order_id'  => (int) ($ln['work_order_id']  ?? 0) ?: null,
                'cost_centre_id' => (int) ($ln['cost_centre_id'] ?? 0) ?: null,
            ];
        }
        if (abs($totDr - $totCr) > 0.005) {
            return ['ok' => false, 'msg' => "Journal is out of balance: DR " . number_format($totDr,2) . " vs CR " . number_format($totCr,2)];
        }

        $jeNo = $this->nextJeNo();
        $r    = $db->insertUpdate(
            "INSERT INTO journal_entries
                (je_no, entry_date, source_type, source_id, source_ref, narrative,
                 currency, fx_rate, total_debit, total_credit, status,
                 posted_by, posted_by_name, posted_on, created_by, edited_by)
             VALUES
                (:jn, :ed, :st, :sid, :sr, :n, :cur, :fx, :td, :tc, 'POSTED',
                 :uid, :unm, NOW(), :uid, :uid)",
            [
                ':jn'  => $jeNo,
                ':ed'  => (string)($entry['entry_date'] ?? date('Y-m-d')),
                ':st'  => (string)($entry['source_type'] ?? 'MANUAL'),
                ':sid' => (int)   ($entry['source_id']   ?? 0),
                ':sr'  => (string)($entry['source_ref']  ?? ''),
                ':n'   => (string)($entry['narrative']   ?? ''),
                ':cur' => (string)($entry['currency']    ?? 'TZS'),
                ':fx'  => (float) ($entry['fx_rate']     ?? 1.0),
                ':td'  => $totDr,
                ':tc'  => $totCr,
                ':uid' => (int)   ($entry['posted_by']   ?? 0),
                ':unm' => (string)($entry['posted_by_name'] ?? ''),
            ]
        );
        $jeId = (int)$r['last_id'];
        if ($jeId <= 0) return ['ok' => false, 'msg' => 'Failed to persist journal header.'];

        $ln = 0;
        foreach ($resolved as $line) {
            $ln++;
            $db->insertUpdate(
                "INSERT INTO journal_lines
                    (journal_entry_id, line_no, account_id, account_code, account_name,
                     debit, credit, narrative,
                     project_id, lpo_id, payment_id, bank_account_id,
                     truck_id, driver_id, trip_id, trip_request_id,
                     customer_id, vendor_id, expense_id, fuel_entry_id, work_order_id, cost_centre_id)
                 VALUES
                    (:je, :ln, :aid, :ac, :an, :d, :c, :n,
                     :pid, :lid, :payid, :bid,
                     :tid, :did, :trip, :treq,
                     :cid, :vid, :eid, :fid, :wid, :ccid)",
                [
                    ':je' => $jeId, ':ln' => $ln,
                    ':aid'=> $line['account_id'], ':ac' => $line['account_code'], ':an' => $line['account_name'],
                    ':d'  => $line['debit'], ':c' => $line['credit'], ':n' => $line['narrative'],
                    ':pid'=> $line['project_id'], ':lid' => $line['lpo_id'],
                    ':payid'=> $line['payment_id'], ':bid' => $line['bank_account_id'],
                    ':tid' => $line['truck_id'],       ':did' => $line['driver_id'],
                    ':trip'=> $line['trip_id'],        ':treq'=> $line['trip_request_id'],
                    ':cid' => $line['customer_id'],    ':vid' => $line['vendor_id'],
                    ':eid' => $line['expense_id'],     ':fid' => $line['fuel_entry_id'],
                    ':wid' => $line['work_order_id'],  ':ccid'=> $line['cost_centre_id'],
                ]
            );
        }
        return ['ok' => true, 'msg' => 'Journal posted (' . $jeNo . ').', 'id' => $jeId, 'je_no' => $jeNo];
    }

    /**
     * Domain wrapper: post the standard cash-out journal for a payments row.
     * - No supplier VAT captured: Dr Expense / Cr Bank (2 lines)
     * - Supplier EFD-VAT bill: Dr Expense (net) / Dr 8210 Input VAT / Cr Bank (3 lines)
     * Falls back to 6290 Other Operating Expenses / 1200 Bank & Cash if either missing.
     */
    public function postForPayment(int $payment_id): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT p.*, pv.pv_no, pv.payee_name, pv.notes AS pv_notes,
                    l.c0d AS lpo_id, l.lpo_no, l.lpo_type, l.project_id, l.purpose,
                    l.budget_category_id, l.currency AS l_currency,
                    l.supplier_receipt_type, l.supplier_efd_number, l.supplier_tin,
                    l.net_amount, l.vat_amount, l.vat_inclusive_flag,
                    bic.default_expense_coa_id,
                    coa_exp.account_code AS exp_code, coa_exp.account_name AS exp_name,
                    coa_bnk.c0d AS bank_coa_id, coa_bnk.account_code AS bank_code, coa_bnk.account_name AS bank_name
               FROM payments p
          LEFT JOIN payment_vouchers pv ON pv.c0d = p.payment_voucher_id
          LEFT JOIN lpos l              ON l.c0d = pv.lpo_id
          LEFT JOIN budget_item_categories bic ON bic.c0d = l.budget_category_id
          LEFT JOIN chart_of_accounts coa_exp  ON coa_exp.c0d = bic.default_expense_coa_id
          LEFT JOIN bank_accounts ba          ON ba.c0d = p.bank_account_id
          LEFT JOIN chart_of_accounts coa_bnk ON coa_bnk.c0d = ba.coa_id
              WHERE p.c0d = :id AND p.deleted = 0 LIMIT 1",
            [':id' => $payment_id]
        );
        $p = $rs[0] ?? null;
        if (!$p) return ['ok' => false, 'msg' => 'Payment not found.'];

        // Idempotency: don't double-post -----------------------------------
        $ex = $db->mySelect(
            "SELECT c0d FROM journal_entries
              WHERE source_type = 'PAYMENT' AND source_id = :id AND deleted = 0 AND status = 'POSTED'
              LIMIT 1",
            [':id' => $payment_id]
        );
        if (!empty($ex)) {
            return ['ok' => true, 'msg' => 'Already posted (JE #' . $ex[0]['c0d'] . ').', 'id' => (int)$ex[0]['c0d'], 'idempotent' => true];
        }

        $expCode = (string)($p['exp_code']  ?: '6290');
        $bnkCode = (string)($p['bank_code'] ?: '1200');
        $amt     = round((float)$p['amount'], 2);
        $vat     = round((float)($p['vat_amount'] ?? 0), 2);
        // Net = expense line. If vat_inclusive_flag = 1, LPO.amount is total incl VAT,
        // so net = amt - vat. If flag = 0 but vat > 0, LPO.amount was already net
        // and vat was added on top → still amt is what we paid, so net = amt - vat.
        $net     = ($vat > 0) ? round($amt - $vat, 2) : $amt;
        if ($net < 0) return ['ok' => false, 'msg' => 'VAT ('.$vat.') exceeds amount ('.$amt.') — cannot split.'];

        $narr = ($p['pv_no'] ? $p['pv_no'] . ' · ' : '') . ($p['lpo_no'] ?? '') . ' · ' . ($p['payee_name'] ?? '');
        if ($vat > 0) {
            $narr .= ' · Input VAT ' . number_format($vat) . ' (supplier EFD ' . ($p['supplier_efd_number'] ?: '(no ref)') . ')';
        }

        $lines = [
            [
                'account_code' => $expCode,
                'debit'  => $net,
                'credit' => 0,
                'narrative' => 'Expense (net) for ' . ($p['lpo_no'] ?? ''),
                'project_id' => (int)($p['project_id'] ?? 0),
                'lpo_id'     => (int)($p['lpo_id']     ?? 0),
                'payment_id' => (int)$p['c0d'],
            ],
        ];
        if ($vat > 0) {
            $lines[] = [
                'account_code' => '8210',
                'debit'  => $vat,
                'credit' => 0,
                'narrative' => 'Input VAT claimable — supplier ' . ($p['payee_name'] ?? '') . ' · EFD ' . ($p['supplier_efd_number'] ?: '(none)'),
                'project_id' => (int)($p['project_id'] ?? 0),
                'lpo_id'     => (int)($p['lpo_id']     ?? 0),
                'payment_id' => (int)$p['c0d'],
            ];
        }
        $lines[] = [
            'account_code' => $bnkCode,
            'debit'  => 0,
            'credit' => $amt,
            'narrative' => 'Bank payout to ' . ($p['payee_name'] ?? ''),
            'project_id' => (int)($p['project_id'] ?? 0),
            'lpo_id'     => (int)($p['lpo_id']     ?? 0),
            'payment_id' => (int)$p['c0d'],
            'bank_account_id' => (int)$p['bank_account_id'],
        ];

        return $this->post([
            'entry_date'  => (string)$p['payment_date'],
            'source_type' => 'PAYMENT',
            'source_id'   => (int)$p['c0d'],
            'source_ref'  => (string)$p['payment_no'],
            'narrative'   => 'Cash-out for ' . $narr . (($p['purpose'] ?? '') ? ' — ' . $p['purpose'] : ''),
            'currency'    => (string)($p['currency'] ?? 'TZS'),
            'posted_by'   => (int)   ($p['created_by'] ?? 0),
            'posted_by_name' => 'System (auto-post)',
            'lines' => $lines,
        ]);
    }

    /**
     * Domain wrapper: post the money-in journal for a customer receipt.
     * If the receipt is linked to a sale, we clear AR:
     *     Dr <bank_account.coa_id>  Cr 1121 Trade Receivables
     * If standalone (no sale — e.g. an advance), post to a customer advances
     * account or (fallback) to 1121 same way — real advance handling is a
     * later slice.
     * Idempotent — skips if a POSTED JE already exists for this receipt.
     */
    public function postForReceipt(int $receipt_id): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT r.*, ba.coa_id AS bank_coa_id, coa_bnk.account_code AS bank_code
               FROM receipts r
          LEFT JOIN bank_accounts ba          ON ba.c0d = r.bank_account_id
          LEFT JOIN chart_of_accounts coa_bnk ON coa_bnk.c0d = ba.coa_id
              WHERE r.c0d = :id AND r.deleted = 0 LIMIT 1",
            [':id' => $receipt_id]
        );
        $r = $rs[0] ?? null;
        if (!$r) return ['ok' => false, 'msg' => 'Receipt not found.'];

        // Idempotency
        $ex = $db->mySelect(
            "SELECT c0d FROM journal_entries
              WHERE source_type = 'RECEIPT' AND source_id = :id AND deleted = 0 AND status = 'POSTED'
              LIMIT 1",
            [':id' => $receipt_id]
        );
        if (!empty($ex)) {
            return ['ok' => true, 'msg' => 'Already posted (JE #' . $ex[0]['c0d'] . ').', 'id' => (int)$ex[0]['c0d'], 'idempotent' => true];
        }

        $bnkCode = (string)($r['bank_code'] ?: '1200');
        $amt     = round((float)$r['amount'], 2);
        if ($amt <= 0) return ['ok' => false, 'msg' => 'Receipt amount is zero — nothing to post.'];

        $ref  = (string)$r['receipt_no'];
        $narr = 'Cash-in · ' . $ref . ' · ' . ($r['customer_name'] ?: '(customer)')
              . (!empty($r['efd_receipt_number']) ? ' · EFD ' . $r['efd_receipt_number'] : '')
              . ' · type=' . $r['receipt_type'];

        $lines = [
            [
                'account_code' => $bnkCode,
                'debit'  => $amt,
                'credit' => 0,
                'narrative' => 'Bank receipt · ' . ($r['payment_method'] ?: 'BANK_TRANSFER') . ($r['bank_reference'] ? ' · ' . $r['bank_reference'] : ''),
                'project_id' => (int)($r['project_id'] ?? 0),
                'bank_account_id' => (int)$r['bank_account_id'],
            ],
            [
                'account_code' => '1121',
                'debit'  => 0,
                'credit' => $amt,
                'narrative' => 'AR cleared — ' . ($r['customer_name'] ?: '(customer)'),
                'project_id' => (int)($r['project_id'] ?? 0),
            ],
        ];

        return $this->post([
            'entry_date'  => (string)$r['receipt_date'],
            'source_type' => 'RECEIPT',
            'source_id'   => (int)$r['c0d'],
            'source_ref'  => $ref,
            'narrative'   => $narr,
            'currency'    => (string)($r['currency'] ?? 'TZS'),
            'posted_by'   => (int)   ($r['received_by'] ?? 0),
            'posted_by_name' => (string)($r['received_by_name'] ?? 'System (auto-post)'),
            'lines'       => $lines,
        ]);
    }

    /**
     * Domain wrapper: post the revenue journal for a customer invoice ack.
     * Dr 1121 Trade Receivables (total)
     * Cr 4110/4120/4130 Sales Revenue (net of VAT — routed by source_module)
     * Cr 8110 Output VAT (VAT portion, only if > 0)
     * Idempotent — skips if a POSTED JE already exists for this sale.
     */
    public function postForSaleAck(int $sale_id): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT s.*, p.project_no
               FROM sales s
          LEFT JOIN c_projects p ON p.c0d = s.project_id
              WHERE s.c0d = :id AND s.deleted = 0 LIMIT 1",
            [':id' => $sale_id]
        );
        $s = $rs[0] ?? null;
        if (!$s) return ['ok' => false, 'msg' => 'Sale not found.'];

        // Idempotency ------------------------------------------------------
        $ex = $db->mySelect(
            "SELECT c0d FROM journal_entries
              WHERE source_type = 'INVOICE' AND source_id = :id AND deleted = 0 AND status = 'POSTED'
              LIMIT 1",
            [':id' => $sale_id]
        );
        if (!empty($ex)) {
            return ['ok' => true, 'msg' => 'Already posted (JE #' . $ex[0]['c0d'] . ').', 'id' => (int)$ex[0]['c0d'], 'idempotent' => true];
        }

        // Route sales revenue by source_module ----------------------------
        $revMap  = ['goods' => '4110', 'services' => '4120', 'works' => '4130'];
        $revCode = $revMap[(string)$s['source_module']] ?? '4110';
        $subtotal = round((float)$s['subtotal'],   2);
        $vat      = round((float)$s['vat_amount'], 2);
        $total    = round((float)$s['total'],      2);
        if ($total <= 0) return ['ok' => false, 'msg' => 'Invoice total is zero — nothing to post.'];

        $ref  = $s['sale_no'] ?: $s['invoice_ref'];
        $narr = 'Revenue recognition · ' . $ref . ($s['customer_name'] ? ' · ' . $s['customer_name'] : '') . ($s['project_no'] ? ' · ' . $s['project_no'] : '');
        $lines = [
            [
                'account_code' => '1121',
                'debit'  => $total,
                'credit' => 0,
                'narrative' => 'AR: ' . ($s['customer_name'] ?: '(customer)'),
                'project_id' => (int)$s['project_id'],
            ],
            [
                'account_code' => $revCode,
                'debit'  => 0,
                'credit' => $subtotal,
                'narrative' => 'Sales revenue (' . $s['source_module'] . ')',
                'project_id' => (int)$s['project_id'],
            ],
        ];
        if ($vat > 0) {
            $lines[] = [
                'account_code' => '8110',
                'debit'  => 0,
                'credit' => $vat,
                'narrative' => 'Output VAT @ ' . number_format((float)$s['vat_rate'],2) . '%',
                'project_id' => (int)$s['project_id'],
            ];
        }

        return $this->post([
            'entry_date'  => (string)$s['invoice_date'],
            'source_type' => 'INVOICE',
            'source_id'   => $sale_id,
            'source_ref'  => $ref,
            'narrative'   => $narr,
            'currency'    => (string)$s['currency'],
            'posted_by'   => (int)($s['acknowledged_by'] ?: $s['drafted_by']),
            'posted_by_name' => (string)($s['acknowledged_by_name'] ?: 'System (auto-post)'),
            'lines'       => $lines,
        ]);
    }

    // ── Read helpers used by Journals / Cashbook / COA views ──────────────

    public function list(string $source_type = '', string $from = '', string $to = ''): array
    {
        $db = new DB();
        $where  = "WHERE je.deleted = 0";
        $params = [];
        if ($source_type !== '') { $where .= " AND je.source_type = :st"; $params[':st'] = $source_type; }
        if ($from !== '')        { $where .= " AND je.entry_date >= :f"; $params[':f']  = $from; }
        if ($to   !== '')        { $where .= " AND je.entry_date <= :t"; $params[':t']  = $to; }
        return $db->mySelect(
            "SELECT je.c0d, je.je_no, je.entry_date, je.source_type, je.source_ref,
                    je.narrative, je.currency, je.total_debit, je.total_credit, je.status,
                    je.posted_by_name
               FROM journal_entries je
               $where
              ORDER BY je.entry_date DESC, je.c0d DESC",
            $params
        ) ?: [];
    }

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

    public function linesForJe(int $je_id): array
    {
        $db = new DB();
        return $db->mySelect("SELECT * FROM journal_lines WHERE journal_entry_id = :id ORDER BY line_no ASC", [':id' => $je_id]) ?: [];
    }

    /** Cashbook: per-bank ledger with running balance (opening balance + Σ(debit − credit)). */
    public function cashbookForBank(int $bank_account_id, string $from = '', string $to = ''): array
    {
        $db = new DB();
        $where  = "WHERE jl.bank_account_id = :b AND je.deleted = 0 AND je.status = 'POSTED'";
        $params = [':b' => $bank_account_id];
        if ($from !== '') { $where .= " AND je.entry_date >= :f"; $params[':f'] = $from; }
        if ($to   !== '') { $where .= " AND je.entry_date <= :t"; $params[':t'] = $to; }
        return $db->mySelect(
            "SELECT je.c0d AS je_id, je.je_no, je.entry_date, je.source_type, je.source_ref, je.narrative AS je_narr,
                    jl.c0d AS line_id, jl.narrative AS line_narr, jl.debit, jl.credit,
                    jl.project_id, jl.lpo_id, jl.payment_id
               FROM journal_lines jl
               JOIN journal_entries je ON je.c0d = jl.journal_entry_id
               $where
              ORDER BY je.entry_date ASC, je.c0d ASC, jl.line_no ASC",
            $params
        ) ?: [];
    }

    /** COA activity: all lines that hit a given account, with running balance sign-aware. */
    public function activityForAccount(int $account_id, string $from = '', string $to = ''): array
    {
        $db = new DB();
        $where  = "WHERE jl.account_id = :a AND je.deleted = 0 AND je.status = 'POSTED'";
        $params = [':a' => $account_id];
        if ($from !== '') { $where .= " AND je.entry_date >= :f"; $params[':f'] = $from; }
        if ($to   !== '') { $where .= " AND je.entry_date <= :t"; $params[':t'] = $to; }
        return $db->mySelect(
            "SELECT je.c0d AS je_id, je.je_no, je.entry_date, je.source_type, je.source_ref, je.narrative AS je_narr,
                    jl.c0d AS line_id, jl.narrative AS line_narr, jl.debit, jl.credit,
                    jl.project_id, jl.lpo_id, jl.payment_id, jl.bank_account_id
               FROM journal_lines jl
               JOIN journal_entries je ON je.c0d = jl.journal_entry_id
               $where
              ORDER BY je.entry_date ASC, je.c0d ASC, jl.line_no ASC",
            $params
        ) ?: [];
    }

    /** COA balances: aggregate Σdebit, Σcredit, balance per account. Sign-aware per account_type. */
    public function balancesForAll(): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT coa.c0d, coa.account_code, coa.account_name, coa.account_type, coa.is_bank, coa.is_active,
                    IFNULL(SUM(jl.debit), 0)  AS total_debit,
                    IFNULL(SUM(jl.credit), 0) AS total_credit,
                    (IFNULL(SUM(jl.debit),0) - IFNULL(SUM(jl.credit),0)) AS raw_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.deleted = 0 AND je.status = 'POSTED'
              WHERE coa.is_active = 1
              GROUP BY coa.c0d
              ORDER BY coa.account_code ASC",
            []
        ) ?: [];
    }
}
