<?php
/**
 * BankReconciler — matching engine + reconciliation summary.
 *
 * Matching rule:
 *   A bank statement line with amount +X (money in) matches a journal line
 *   on the same bank account with debit = X and credit = 0.
 *   A bank statement line with amount -X (money out) matches a journal line
 *   with credit = X and debit = 0.
 *
 * Each match is 1:1 — a journal line already claimed by one statement line
 * can't be re-used. Auto-suggest returns non-conflicting candidates only.
 *
 * "Adjustment" is the escape hatch for statement lines that never existed
 * in the books (bank fees, interest income, direct deposits from customers
 * booked bank-side). The controller creates a balancing JE and links it.
 */
class BankReconciler
{
    /* ══════════════════════════════════════════════════════════════════
       Match / unmatch
       ══════════════════════════════════════════════════════════════════ */

    public function match(int $statementLineId, int $journalLineId, int $uid): array
    {
        // Guard: the journal line must actually offset this statement line
        // (same absolute amount, correct debit/credit side, hits the right
        // bank account). Anything else would create bogus recon state.
        $chk = $this->checkOffset($statementLineId, $journalLineId);
        if (!$chk['ok']) return $chk;

        // Guard: journal line can't already be claimed by a different
        // statement line.
        $db = new DB();
        $used = $db->mySelect(
            "SELECT c0d FROM bank_statement_lines
              WHERE matched_journal_line_id = :jl
                AND c0d != :sl
                AND deleted = 0
              LIMIT 1",
            [':jl' => $journalLineId, ':sl' => $statementLineId]
        );
        if (!empty($used)) return ['ok' => false, 'msg' => 'That journal line is already matched to another statement line.'];

        $db->insertUpdate(
            "UPDATE bank_statement_lines
                SET matched_journal_line_id = :jl,
                    matched_by              = :uid,
                    matched_on              = NOW()
              WHERE c0d = :sl",
            [':jl' => $journalLineId, ':uid' => $uid, ':sl' => $statementLineId]
        );
        $this->recomputeStatementCounts($chk['statement_id']);
        return ['ok' => true, 'msg' => 'Matched.'];
    }

    public function unmatch(int $statementLineId, int $uid): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT statement_id FROM bank_statement_lines WHERE c0d = :id",
            [':id' => $statementLineId]
        );
        if (empty($rs)) return ['ok' => false, 'msg' => 'Statement line not found.'];

        $db->insertUpdate(
            "UPDATE bank_statement_lines
                SET matched_journal_line_id = 0,
                    matched_by              = 0,
                    matched_on              = NULL
              WHERE c0d = :id",
            [':id' => $statementLineId]
        );
        $this->recomputeStatementCounts((int)$rs[0]['statement_id']);
        return ['ok' => true, 'msg' => 'Unmatched.'];
    }

    /**
     * Score every unmatched (statement line, journal line) pair and return
     * the top candidate per statement line. A candidate is only returned if
     * the amount matches exactly and the date is within ±$maxDayGap days.
     * Called by the "Auto-match" button in the recon UI.
     */
    public function autoSuggest(int $statementId, int $maxDayGap = 7): array
    {
        $bs = new BankStatements();
        $lines = $bs->linesFor($statementId);
        $book  = $bs->bookLinesFor($statementId);

        $bookByAmt = [];
        foreach ($book as $b) {
            if ((int)$b['matched_stmt_line_id'] !== 0) continue;
            $signed = (float)$b['debit'] - (float)$b['credit']; // + = money in
            $key = number_format($signed, 2, '.', '');
            $bookByAmt[$key][] = $b;
        }

        $used = [];
        $suggestions = [];
        foreach ($lines as $l) {
            if ((int)$l['matched_journal_line_id'] !== 0) continue;
            $key = number_format((float)$l['amount'], 2, '.', '');
            if (empty($bookByAmt[$key])) continue;

            $bestGap = PHP_INT_MAX;
            $best    = null;
            foreach ($bookByAmt[$key] as $b) {
                if (isset($used[$b['c0d']])) continue;
                $gap = abs((strtotime($l['txn_date']) - strtotime($b['entry_date'])) / 86400);
                if ($gap <= $maxDayGap && $gap < $bestGap) {
                    $bestGap = $gap;
                    $best = $b;
                }
            }
            if ($best) {
                $suggestions[] = [
                    'stmt_line_id' => (int)$l['c0d'],
                    'journal_line' => $best,
                    'gap_days'     => (int)$bestGap,
                ];
                $used[$best['c0d']] = true;
            }
        }
        return $suggestions;
    }

    /**
     * Apply every auto-suggestion in one shot. Returns count of matches
     * created.
     */
    public function applyAutoSuggestions(int $statementId, int $uid): int
    {
        $suggestions = $this->autoSuggest($statementId);
        $n = 0;
        foreach ($suggestions as $s) {
            $r = $this->match((int)$s['stmt_line_id'], (int)$s['journal_line']['c0d'], $uid);
            if ($r['ok']) $n++;
        }
        return $n;
    }

    /* ══════════════════════════════════════════════════════════════════
       Adjustment — create a balancing JE for a bank-only item and match it
       ══════════════════════════════════════════════════════════════════ */

    /**
     * Post a small adjustment JE:
     *   money OUT (bank fee, charge)     → Dr contra_account, Cr bank
     *   money IN  (interest, ext deposit)→ Dr bank,          Cr contra_account
     * then match the newly-created journal line to $statementLineId.
     */
    public function adjust(int $statementLineId, int $contraAccountId, string $narrative, int $uid, string $userName = ''): array
    {
        $db = new DB();
        $line = $db->mySelect(
            "SELECT bsl.*, bs.bank_account_id, bs.tenant_id
               FROM bank_statement_lines bsl
               JOIN bank_statements bs ON bs.c0d = bsl.statement_id
              WHERE bsl.c0d = :id AND bsl.deleted = 0 LIMIT 1",
            [':id' => $statementLineId]
        );
        if (empty($line)) return ['ok' => false, 'msg' => 'Statement line not found.'];
        $l = $line[0];
        if ((int)$l['matched_journal_line_id'] !== 0) return ['ok' => false, 'msg' => 'Line is already matched — unmatch first.'];

        $amount = abs((float)$l['amount']);
        if ($amount < 0.005) return ['ok' => false, 'msg' => 'Zero amount.'];

        $bankId = (int)$l['bank_account_id'];
        $moneyIn = ((float)$l['amount']) > 0;

        // Fetch the two account rows so we can inline their names on the JE
        $accs = $db->mySelect(
            "SELECT c0d, account_code, account_name FROM chart_of_accounts WHERE c0d IN (:a, :b)",
            [':a' => $bankId, ':b' => $contraAccountId]
        );
        $accMap = [];
        foreach ($accs as $a) $accMap[(int)$a['c0d']] = $a;
        if (!isset($accMap[$bankId]) || !isset($accMap[$contraAccountId])) {
            return ['ok' => false, 'msg' => 'Account not found.'];
        }

        if (!class_exists('JournalPosting')) {
            require_once ROOT . '/app/models/JournalPosting.php';
        }

        $lines = $moneyIn
            ? [
                ['account_id' => $bankId,          'debit' => $amount, 'credit' => 0.0,     'narrative' => $narrative],
                ['account_id' => $contraAccountId, 'debit' => 0.0,     'credit' => $amount, 'narrative' => $narrative],
            ]
            : [
                ['account_id' => $contraAccountId, 'debit' => $amount, 'credit' => 0.0,     'narrative' => $narrative],
                ['account_id' => $bankId,          'debit' => 0.0,     'credit' => $amount, 'narrative' => $narrative],
            ];

        $post = (new JournalPosting())->post([
            'tenant_id'   => (int)$l['tenant_id'],
            'entry_date'  => $l['txn_date'],
            'source_type' => 'BANK_ADJ',
            'source_id'   => (int)$l['c0d'],
            'source_ref'  => 'stmt-line-' . $l['c0d'],
            'narrative'   => $narrative ?: 'Bank reconciliation adjustment',
            'posted_by'   => $uid,
            'posted_by_name' => $userName,
            'lines'       => $lines,
        ]);
        if (!$post['ok']) return ['ok' => false, 'msg' => 'JE post failed: ' . $post['msg']];

        // Locate the bank-side journal line we just created and match it.
        $jeId = (int)$post['id'];
        $bankLine = $db->mySelect(
            "SELECT c0d FROM journal_lines
              WHERE journal_entry_id = :je AND account_id = :bank LIMIT 1",
            [':je' => $jeId, ':bank' => $bankId]
        );
        if (empty($bankLine)) return ['ok' => false, 'msg' => 'JE posted but bank line not found.'];

        $r = $this->match($statementLineId, (int)$bankLine[0]['c0d'], $uid);
        if (!$r['ok']) return ['ok' => false, 'msg' => 'JE posted but match failed: ' . $r['msg']];

        return ['ok' => true, 'msg' => 'Adjustment posted.', 'je_id' => $jeId];
    }

    /* ══════════════════════════════════════════════════════════════════
       Summary — the numbers the reconciler needs to see at the top of the
       screen and the closing check the accountant signs off.
       ══════════════════════════════════════════════════════════════════ */

    public function summary(int $statementId): array
    {
        $bs = new BankStatements();
        $s  = $bs->byId($statementId);
        if (!$s) return [];

        $lines = $bs->linesFor($statementId);
        $book  = $bs->bookLinesFor($statementId);

        // Book balance = every posting to this bank account up to statement_to
        $db = new DB();
        $bal = $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
              WHERE jl.account_id = :bank
                AND je.entry_date <= :to",
            [':bank' => (int)$s['bank_account_id'], ':to' => $s['statement_to']]
        );
        $bookBalance = (float)($bal[0]['bal'] ?? 0);

        $stmtIn = $stmtOut = 0.0;
        $matchedCount = $unmatchedCount = 0;
        foreach ($lines as $l) {
            if ((float)$l['amount'] > 0) $stmtIn  += (float)$l['amount'];
            else                          $stmtOut += -1 * (float)$l['amount'];
            if ((int)$l['matched_journal_line_id'] !== 0) $matchedCount++; else $unmatchedCount++;
        }

        // Uncleared book items = book lines with no matching statement line
        $unclearedInBooks = 0.0;
        $unclearedCount   = 0;
        foreach ($book as $b) {
            if ((int)$b['matched_stmt_line_id'] !== 0) continue;
            $unclearedInBooks += ((float)$b['debit'] - (float)$b['credit']);
            $unclearedCount++;
        }

        // Unmatched STATEMENT items would show as unposted adjustments —
        // they'll modify the book balance once posted via adjust().
        $unpostedFromStmt = 0.0;
        foreach ($lines as $l) {
            if ((int)$l['matched_journal_line_id'] === 0) {
                $unpostedFromStmt += (float)$l['amount'];
            }
        }

        return [
            'statement'          => $s,
            'stmt_in'            => round($stmtIn, 2),
            'stmt_out'           => round($stmtOut, 2),
            'stmt_matched'       => $matchedCount,
            'stmt_unmatched'     => $unmatchedCount,
            'stmt_count'         => count($lines),
            'book_balance'       => round($bookBalance, 2),
            'uncleared_books'    => round($unclearedInBooks, 2),
            'uncleared_count'    => $unclearedCount,
            'unposted_from_stmt' => round($unpostedFromStmt, 2),
            // Reconciled book balance = current book bal + not-yet-posted items
            'reconciled_book'    => round($bookBalance + $unpostedFromStmt, 2),
            // Reconciled bank balance = statement closing - not-yet-cleared items
            // (an outstanding cheque still on the books but not on statement
            //  reduces the effective bank position)
            'reconciled_bank'    => round((float)$s['closing_balance'] - $unclearedInBooks, 2),
            'difference'         => round(
                ($bookBalance + $unpostedFromStmt) - ((float)$s['closing_balance'] - $unclearedInBooks),
                2
            ),
        ];
    }

    public function complete(int $statementId, int $uid): array
    {
        $sum = $this->summary($statementId);
        if (empty($sum)) return ['ok' => false, 'msg' => 'Statement not found.'];
        if ($sum['stmt_unmatched'] > 0) {
            return ['ok' => false, 'msg' => 'Still ' . $sum['stmt_unmatched'] . ' unmatched statement line(s). Match or adjust before completing.'];
        }
        if (abs($sum['difference']) >= 0.01) {
            return ['ok' => false, 'msg' => 'Reconciled book vs bank differ by ' . number_format($sum['difference'], 2) . '. Investigate uncleared items before completing.'];
        }

        $db = new DB();
        $db->insertUpdate(
            "UPDATE bank_statements
                SET status = 'RECONCILED',
                    reconciled_by = :uid,
                    reconciled_on = NOW(),
                    edited_by = :uid,
                    edited_on = NOW()
              WHERE c0d = :id",
            [':uid' => $uid, ':id' => $statementId]
        );
        return ['ok' => true, 'msg' => 'Statement reconciled.'];
    }

    /* ── internals ─────────────────────────────────────────────────── */

    /** Confirm the JL offsets the SL (same absolute amount + correct side + right bank account). */
    private function checkOffset(int $statementLineId, int $journalLineId): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT bsl.c0d      AS sl_id,
                    bsl.amount   AS sl_amount,
                    bsl.statement_id,
                    bs.bank_account_id,
                    jl.debit, jl.credit, jl.account_id
               FROM bank_statement_lines bsl
               JOIN bank_statements bs ON bs.c0d = bsl.statement_id
               JOIN journal_lines jl   ON jl.c0d = :jl
              WHERE bsl.c0d = :sl AND bsl.deleted = 0
              LIMIT 1",
            [':sl' => $statementLineId, ':jl' => $journalLineId]
        );
        if (empty($rs)) return ['ok' => false, 'msg' => 'Statement or journal line not found.'];
        $r = $rs[0];
        if ((int)$r['account_id'] !== (int)$r['bank_account_id']) {
            return ['ok' => false, 'msg' => 'Journal line does not hit this bank account.'];
        }
        $signed = (float)$r['debit'] - (float)$r['credit'];
        if (abs($signed - (float)$r['sl_amount']) >= 0.01) {
            return ['ok' => false, 'msg' => 'Amounts do not offset (statement ' . number_format((float)$r['sl_amount'], 2) . ' vs journal ' . number_format($signed, 2) . ').'];
        }
        return ['ok' => true, 'statement_id' => (int)$r['statement_id']];
    }

    private function recomputeStatementCounts(int $statementId): void
    {
        $db = new DB();
        $db->insertUpdate(
            "UPDATE bank_statements
                SET matched_count = (
                    SELECT COUNT(*) FROM bank_statement_lines
                     WHERE statement_id = :sid AND deleted = 0 AND matched_journal_line_id != 0
                )
              WHERE c0d = :sid",
            [':sid' => $statementId]
        );
    }
}
