<?php
/**
 * BankRec — bank reconciliation model.
 *
 * Statement lines come in via XLSX import (see BankRecController::importAj).
 * Auto-matching rule: for each unmatched statement line, find a journal_line
 *   on the same bank COA where
 *     – statement DEBIT (outflow)  = journal_line CREDIT
 *     – statement CREDIT (inflow)  = journal_line DEBIT
 *     – txn_date within +/- 3 days of the JE entry_date
 *   Highest-confidence match (date exact, amount exact, unmatched) wins.
 *   A journal_line already matched to another statement line cannot be reused.
 */
class BankRec extends Model
{
    /** All statements newest first, with counts + progress. */
    public function listAll(): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT bs.c0d, bs.statement_date, bs.opening_balance, bs.closing_balance, bs.currency,
                    bs.source_filename, bs.status, bs.imported_on, bs.imported_by_name,
                    coa.account_code, coa.account_name AS bank_name,
                    (SELECT COUNT(*) FROM bank_statement_lines WHERE statement_id = bs.c0d AND deleted = 0)                                          AS lines_total,
                    (SELECT COUNT(*) FROM bank_statement_lines WHERE statement_id = bs.c0d AND deleted = 0 AND match_status IN ('AUTO_MATCHED','MANUAL_MATCHED','IGNORED')) AS lines_done
               FROM bank_statements bs
               JOIN chart_of_accounts coa ON coa.c0d = bs.bank_account_id
              WHERE bs.deleted = 0
              ORDER BY bs.statement_date DESC, bs.c0d DESC",
            []
        ) ?: [];
    }

    /** Bank COAs available for import. */
    public function bankAccounts(): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, account_code, account_name FROM chart_of_accounts
              WHERE is_bank = 1 AND is_active = 1 ORDER BY account_code",
            []
        ) ?: [];
    }

    public function findStatement(int $id): ?array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT bs.*, coa.account_code, coa.account_name AS bank_name
               FROM bank_statements bs
               JOIN chart_of_accounts coa ON coa.c0d = bs.bank_account_id
              WHERE bs.c0d = :id AND bs.deleted = 0",
            [':id' => $id]
        );
        return $r[0] ?? null;
    }

    public function statementLines(int $statementId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT bsl.*, je.je_no, je.entry_date AS matched_je_date, je.narrative AS matched_je_narrative
               FROM bank_statement_lines bsl
          LEFT JOIN journal_lines   jl ON jl.c0d = bsl.matched_journal_line_id
          LEFT JOIN journal_entries je ON je.c0d = jl.journal_entry_id
              WHERE bsl.statement_id = :s AND bsl.deleted = 0
              ORDER BY bsl.txn_date, bsl.line_no",
            [':s' => $statementId]
        ) ?: [];
    }

    /** Book journal lines on the bank COA within the statement's date range, that aren't already matched. */
    public function unmatchedBookLines(int $statementId): array
    {
        $stmt = $this->findStatement($statementId);
        if (!$stmt) return [];

        $db = new DB();
        // Widen the search window by 15 days around statement date range to catch cross-period entries
        $from = date('Y-m-d', strtotime($stmt['statement_date'] . ' -60 days'));
        $to   = date('Y-m-d', strtotime($stmt['statement_date'] . ' +15 days'));

        return $db->mySelect(
            "SELECT jl.c0d, jl.debit, jl.credit, jl.narrative AS line_narrative,
                    je.je_no, je.entry_date, je.source_type, je.source_ref, je.narrative AS je_narrative
               FROM journal_lines jl
               JOIN journal_entries je ON je.c0d = jl.journal_entry_id AND je.deleted = 0 AND je.status = 'POSTED'
              WHERE jl.account_id = :aid
                AND je.entry_date BETWEEN :d1 AND :d2
                AND NOT EXISTS (SELECT 1 FROM bank_statement_lines bsl
                                 WHERE bsl.matched_journal_line_id = jl.c0d AND bsl.deleted = 0)
              ORDER BY je.entry_date, je.c0d",
            [':aid' => (int)$stmt['bank_account_id'], ':d1' => $from, ':d2' => $to]
        ) ?: [];
    }

    /**
     * Auto-match every unmatched statement line against unmatched journal_lines.
     * Returns count of new matches created.
     */
    public function autoMatch(int $statementId, int $userId): int
    {
        $lines = $this->statementLines($statementId);
        $book  = $this->unmatchedBookLines($statementId);
        if (empty($lines) || empty($book)) return 0;

        $db = new DB();
        $matched = 0;
        $usedBookIds = [];

        foreach ($lines as $ln) {
            if ($ln['match_status'] !== 'UNMATCHED') continue;
            $stDeb = (float)$ln['debit'];
            $stCrd = (float)$ln['credit'];
            $stDate = $ln['txn_date'];

            $bestId = null; $bestScore = -1;
            foreach ($book as $bl) {
                if (in_array((int)$bl['c0d'], $usedBookIds, true)) continue;
                $blDeb = (float)$bl['debit'];
                $blCrd = (float)$bl['credit'];

                // Bank statement DEBIT (outflow) matches journal_line CREDIT
                // Bank statement CREDIT (inflow) matches journal_line DEBIT
                $amountMatches = ($stDeb > 0 && abs($stDeb - $blCrd) < 0.01 && $blDeb < 0.01)
                              || ($stCrd > 0 && abs($stCrd - $blDeb) < 0.01 && $blCrd < 0.01);
                if (!$amountMatches) continue;

                $daysApart = abs((strtotime($stDate) - strtotime($bl['entry_date'])) / 86400);
                if ($daysApart > 3) continue;

                // Score: closer dates win. Exact-day = 100, 1 day = 80, 2 = 60, 3 = 40.
                $score = 100 - ((int)$daysApart) * 20;
                if ($score > $bestScore) { $bestScore = $score; $bestId = (int)$bl['c0d']; }
            }

            if ($bestId !== null) {
                $db->insertUpdate(
                    "UPDATE bank_statement_lines
                        SET matched_journal_line_id = :j,
                            match_status = 'AUTO_MATCHED',
                            matched_by = :u,
                            matched_on = NOW()
                      WHERE c0d = :id",
                    [':j' => $bestId, ':u' => $userId, ':id' => (int)$ln['c0d']]
                );
                $usedBookIds[] = $bestId;
                $matched++;
            }
        }

        if ($matched > 0) $this->refreshStatus($statementId);
        return $matched;
    }

    /** Manual match one statement line to one journal line. */
    public function manualMatch(int $statementLineId, int $journalLineId, int $userId): void
    {
        $db = new DB();
        // Guard: journal line must not already be matched elsewhere
        $exists = $db->mySelect(
            "SELECT c0d FROM bank_statement_lines
              WHERE matched_journal_line_id = :j AND c0d <> :s AND deleted = 0 LIMIT 1",
            [':j' => $journalLineId, ':s' => $statementLineId]
        );
        if (!empty($exists)) {
            throw new RuntimeException('That journal line is already matched to another statement line.');
        }
        $db->insertUpdate(
            "UPDATE bank_statement_lines
                SET matched_journal_line_id = :j,
                    match_status = 'MANUAL_MATCHED',
                    matched_by = :u,
                    matched_on = NOW()
              WHERE c0d = :s",
            [':j' => $journalLineId, ':u' => $userId, ':s' => $statementLineId]
        );

        // Get statementId for status refresh
        $r = $db->mySelect("SELECT statement_id FROM bank_statement_lines WHERE c0d = :s", [':s' => $statementLineId]);
        if (!empty($r)) $this->refreshStatus((int)$r[0]['statement_id']);
    }

    public function unmatch(int $statementLineId): void
    {
        $db = new DB();
        $db->insertUpdate(
            "UPDATE bank_statement_lines
                SET matched_journal_line_id = NULL,
                    match_status = 'UNMATCHED',
                    matched_by = NULL,
                    matched_on = NULL
              WHERE c0d = :s",
            [':s' => $statementLineId]
        );
        $r = $db->mySelect("SELECT statement_id FROM bank_statement_lines WHERE c0d = :s", [':s' => $statementLineId]);
        if (!empty($r)) $this->refreshStatus((int)$r[0]['statement_id']);
    }

    /** Ignore a statement line (bank fee, interest — no book counterpart needed). */
    public function ignore(int $statementLineId): void
    {
        $db = new DB();
        $db->insertUpdate(
            "UPDATE bank_statement_lines
                SET match_status = 'IGNORED', matched_journal_line_id = NULL,
                    matched_by = NULL, matched_on = NULL
              WHERE c0d = :s",
            [':s' => $statementLineId]
        );
        $r = $db->mySelect("SELECT statement_id FROM bank_statement_lines WHERE c0d = :s", [':s' => $statementLineId]);
        if (!empty($r)) $this->refreshStatus((int)$r[0]['statement_id']);
    }

    /** Mark statement RECONCILED (all lines must be matched or ignored). */
    public function markReconciled(int $statementId, int $userId, string $userName): void
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT COUNT(*) AS n FROM bank_statement_lines
              WHERE statement_id = :s AND deleted = 0 AND match_status = 'UNMATCHED'",
            [':s' => $statementId]
        );
        if ((int)($r[0]['n'] ?? 0) > 0) {
            throw new RuntimeException('Cannot reconcile: ' . (int)$r[0]['n'] . ' line(s) still unmatched.');
        }
        $db->insertUpdate(
            "UPDATE bank_statements
                SET status = 'RECONCILED', reconciled_by = :u, reconciled_by_name = :n, reconciled_on = NOW()
              WHERE c0d = :id",
            [':u' => $userId, ':n' => $userName, ':id' => $statementId]
        );
    }

    /** Roll status DRAFT → IN_PROGRESS once any match happens; back to DRAFT if all unmatched. */
    private function refreshStatus(int $statementId): void
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT
                SUM(CASE WHEN match_status = 'UNMATCHED' THEN 1 ELSE 0 END) AS unmatched,
                SUM(CASE WHEN match_status IN ('AUTO_MATCHED','MANUAL_MATCHED','IGNORED') THEN 1 ELSE 0 END) AS done
               FROM bank_statement_lines WHERE statement_id = :s AND deleted = 0",
            [':s' => $statementId]
        );
        $d = (int)($r[0]['done'] ?? 0);

        // Never move away from RECONCILED here — that's a terminal state.
        $cur = $db->mySelect("SELECT status FROM bank_statements WHERE c0d = :s", [':s' => $statementId]);
        if (!empty($cur) && $cur[0]['status'] === 'RECONCILED') return;

        $newStatus = ($d > 0) ? 'IN_PROGRESS' : 'DRAFT';
        $db->insertUpdate("UPDATE bank_statements SET status = :st WHERE c0d = :id", [':st' => $newStatus, ':id' => $statementId]);
    }

    /**
     * Create a new statement + lines in one shot.
     * $lines: array of ['txn_date','description','reference','debit','credit','running_balance']
     */
    public function importStatement(int $bankAccountId, string $statementDate, float $opening, float $closing, string $filename, array $lines, int $userId, string $userName): int
    {
        $db = new DB();
        $fb = $db->insertUpdate(
            "INSERT INTO bank_statements
                (bank_account_id, statement_date, opening_balance, closing_balance, currency,
                 source_filename, status, imported_by, imported_by_name, imported_on, created_by, created_on)
             VALUES (:aid, :d, :o, :c, 'TZS', :fn, 'DRAFT', :u1, :un, NOW(), :u2, NOW())",
            [':aid' => $bankAccountId, ':d' => $statementDate, ':o' => $opening, ':c' => $closing,
             ':fn' => $filename, ':u1' => $userId, ':u2' => $userId, ':un' => $userName]
        );
        $stmtId = (int)($fb['last_id'] ?? 0);

        $lineNo = 0;
        foreach ($lines as $ln) {
            $lineNo++;
            $db->insertUpdate(
                "INSERT INTO bank_statement_lines
                    (statement_id, line_no, txn_date, description, reference, debit, credit, running_balance, match_status, created_on)
                 VALUES (:s, :n, :d, :desc, :ref, :db, :cr, :rb, 'UNMATCHED', NOW())",
                [
                    ':s'    => $stmtId,
                    ':n'    => $lineNo,
                    ':d'    => (string)($ln['txn_date'] ?? date('Y-m-d')),
                    ':desc' => (string)($ln['description'] ?? ''),
                    ':ref'  => (string)($ln['reference'] ?? ''),
                    ':db'   => (float)($ln['debit'] ?? 0),
                    ':cr'   => (float)($ln['credit'] ?? 0),
                    ':rb'   => isset($ln['running_balance']) ? (float)$ln['running_balance'] : null,
                ]
            );
        }
        return $stmtId;
    }
}
