// Matching engine for bank statement lines — Task 3 of
// docs/superpowers/plans/2026-08-01-bank-rec.md. Server-side TypeScript (not
// SQL) per docs/superpowers/specs/2026-08-01-bank-rec-design.md's "Matching
// engine" section, since it needs a ±7 day heuristic window that doesn't
// belong as a stored procedure. The one hard rule, quoted from the spec:
// "Exactly one candidate → status 'suggested'... Multiple or zero → never
// guess." This module never calls match_statement_line() — only
// suggest_statement_line() (db/migrations/0011_bank_rec.sql) — so a
// suggestion can never be mistaken for a confirmed match; confirming is
// always a separate user action (server/src/bank.ts's /confirm,
// /confirm-all, or /match routes call match_statement_line() themselves).
//
// A candidate entry must: be posted; carry EXACTLY ONE line on the
// statement's money account (mirrors match_statement_line()'s own
// cardinality check, so a "candidate" here is never one that function would
// go on to reject); have that single line's sign-adjusted amount
// (debit - credit — the same "bank positive = debit to the money account"
// convention bank_statement_lines.amount already uses, see 0011's header)
// equal to the statement line's amount exactly; not already be settled by
// ANY line on this account (the settle-uniqueness partial unique index's
// own scope, re-expressed here as a NOT EXISTS so the engine never proposes
// an entry that's already spoken for); and NOT ITSELF HAVE BEEN REVERSED
// (review finding — see below).
//
// runMatchingEngine() additionally excludes, within one run, any entry
// already suggested to an EARLIER line of the same statement — walking lines
// in a fixed order and accumulating a "used" set — so one entry is never
// proposed for two lines at once (the plan's "never double-suggesting one
// entry to two lines of this statement"). It always resets every
// unmatched/suggested line to 'unmatched' first (via
// suggest_statement_line(..., NULL), which 0011 already supports as a
// no-migration-needed reset path — see that function's own header comment)
// before recomputing, so a stale suggestion whose entry got claimed
// elsewhere since the last run reverts cleanly instead of sticking around.
// This is what backs POST .../statements/:id/rematch as well as the
// suggestion pass run automatically at import time.
//
// Reversed entries (review finding on the milestone: "suggestion
// staleness"): match_statement_line() itself (0011_bank_rec.sql) has no
// concept of "this entry was later reversed" — a REVERSED entry's own line
// is untouched (CLAUDE.md §3.2, posted entries are immutable forever), so
// manually /match-ing or /confirm-ing a suggestion against one still
// SUCCEEDS today, matching a real historical bank event to a real
// historical posting even after it's been corrected. That is intentionally
// left alone here (changing match_statement_line()'s own enforcement is a
// bigger, separate decision — a new migration — not this fix). What IS
// fixed here, in the engine's own candidate selection: an entry that has
// SINCE been reversed is excluded from ever being freshly SUGGESTED (an
// automatic proposal the user never explicitly chose, which should stop
// pointing at a posting the books themselves have already walked back) —
// via a NOT EXISTS against journal_entries_reversal_once's own
// (company_id, reverses_entry_id) index. Consequence: POST
// .../statements/:id/rematch run after the suggested entry gets reversed
// reliably CLEARS the stale suggestion (the line reverts to 'unmatched'
// rather than being re-suggested the same now-reversed entry), and a line
// suggested that entry BEFORE the reversal but never confirmed will do the
// same the next time rematch runs (or the next import, since fresh imports
// call this same function). GET .../lines/:id/candidates (the manual-pick
// list) shares this same exclusion, for the same reason — it would be
// inconsistent to auto-suggest never, yet still surface it as a one-click
// manual pick.

import type { PoolClient } from "pg";

export interface CandidateEntry {
  entryId: string;
  entryNo: string | null;
  date: string;
  memo: string | null;
  amount: string;
}

interface FindCandidatesOptions {
  /** Entries to exclude outright (already claimed by another line this run). */
  excludeEntryIds?: string[];
  /** When set, restrict to entries dated within +/- this many days of postedDate. */
  withinDays?: number;
  /** When set, LIMIT the result to this many rows. */
  limit?: number;
  /** When true, order by |entry_date - postedDate| (nearest first) rather than by id. */
  orderByProximity?: boolean;
}

/**
 * Finds posted journal entries that are legal match_statement_line()
 * candidates for one statement line's (moneyAccountId, postedDate, amount).
 * Pure read — never mutates statement lines. See the file header for the
 * exact candidate definition.
 */
export async function findCandidateEntries(
  client: PoolClient,
  companyId: string,
  moneyAccountId: string,
  postedDate: string,
  amount: string,
  opts: FindCandidatesOptions = {},
): Promise<CandidateEntry[]> {
  const params: unknown[] = [];
  function p(value: unknown): string {
    params.push(value);
    return `$${params.length}`;
  }

  const companyParam = p(companyId);
  const accountParam = p(moneyAccountId);

  let dateFilter = "";
  if (opts.withinDays !== undefined) {
    const dateParam = p(postedDate);
    const daysParam = p(opts.withinDays);
    dateFilter = `AND e.entry_date BETWEEN ${dateParam}::date - ${daysParam}::int
                                        AND ${dateParam}::date + ${daysParam}::int`;
  }

  let excludeFilter = "";
  if (opts.excludeEntryIds && opts.excludeEntryIds.length > 0) {
    const excludeParam = p(opts.excludeEntryIds);
    excludeFilter = `AND e.id <> ALL(${excludeParam}::bigint[])`;
  }

  const amountParam = p(amount);

  let orderBy = "e.id";
  if (opts.orderByProximity) {
    const proximityParam = p(postedDate);
    orderBy = `abs(e.entry_date - ${proximityParam}::date), e.entry_date, e.id`;
  }

  let limitClause = "";
  if (opts.limit !== undefined) {
    const limitParam = p(opts.limit);
    limitClause = `LIMIT ${limitParam}`;
  }

  const sql = `
    SELECT e.id AS entry_id, e.entry_no, e.entry_date::text AS entry_date, e.memo,
           sum(l.debit - l.credit)::numeric(19,4) AS amount
      FROM journal_entries e
      JOIN journal_lines l
        ON l.company_id = e.company_id AND l.entry_id = e.id AND l.account_id = ${accountParam}
     WHERE e.company_id = ${companyParam} AND e.status = 'posted'
       ${dateFilter}
       ${excludeFilter}
       AND NOT EXISTS (
         SELECT 1 FROM bank_statement_lines bsl
          WHERE bsl.company_id = e.company_id AND bsl.money_account_id = ${accountParam}
            AND bsl.matched_entry_id = e.id
       )
       -- An entry that has SINCE been reversed is never a fresh candidate —
       -- see the file header's "Reversed entries" section. Indexed via
       -- journal_entries_reversal_once (company_id, reverses_entry_id).
       AND NOT EXISTS (
         SELECT 1 FROM journal_entries r
          WHERE r.company_id = e.company_id AND r.reverses_entry_id = e.id
       )
     GROUP BY e.id, e.entry_no, e.entry_date, e.memo
    HAVING count(*) = 1 AND sum(l.debit - l.credit) = ${amountParam}::numeric(19,4)
     ORDER BY ${orderBy}
     ${limitClause}`;

  const res = await client.query<{
    entry_id: string; entry_no: string | null; entry_date: string; memo: string | null; amount: string;
  }>(sql, params);

  return res.rows.map((r) => ({
    entryId: r.entry_id, entryNo: r.entry_no, date: r.entry_date, memo: r.memo, amount: r.amount,
  }));
}

export interface EngineResult {
  suggested: number;
  unmatched: number;
}

/**
 * Runs the matching engine for one statement: every line currently
 * 'unmatched' or 'suggested' is reset, then re-evaluated in a fixed order
 * (line_no, id) against findCandidateEntries with a ±7 day window; exactly
 * one candidate promotes the line to 'suggested', anything else leaves it
 * 'unmatched'. Lines already 'matched', 'created', or 'excluded' are left
 * untouched entirely (not even re-read) — they're the user's decision, not
 * the engine's business anymore. Safe to call repeatedly (idempotent given
 * an unchanged ledger) and is exactly what both the post-import pass and the
 * on-demand POST .../statements/:id/rematch route call.
 */
export async function runMatchingEngine(
  client: PoolClient, companyId: string, statementId: string,
): Promise<EngineResult> {
  const stmtRes = await client.query<{ money_account_id: string }>(
    "SELECT money_account_id FROM bank_statements WHERE company_id = $1 AND id = $2",
    [companyId, statementId],
  );
  const stmt = stmtRes.rows[0];
  if (!stmt) {
    return { suggested: 0, unmatched: 0 };
  }

  const linesRes = await client.query<{ id: string; posted_date: string; amount: string }>(
    `SELECT id, posted_date::text AS posted_date, amount
       FROM bank_statement_lines
      WHERE company_id = $1 AND statement_id = $2 AND status IN ('unmatched', 'suggested')
      ORDER BY line_no, id`,
    [companyId, statementId],
  );

  const usedEntryIds: string[] = [];
  for (const line of linesRes.rows) {
    // Reset first — see the file header for why: a clean slate before
    // recomputation so a now-stale suggestion reverts rather than sticking.
    await client.query("SELECT suggest_statement_line($1, $2, NULL)", [companyId, line.id]);

    const candidates = await findCandidateEntries(
      client, companyId, stmt.money_account_id, line.posted_date, line.amount,
      { withinDays: 7, excludeEntryIds: usedEntryIds },
    );
    if (candidates.length === 1) {
      const entryId = candidates[0]!.entryId;
      await client.query("SELECT suggest_statement_line($1, $2, $3)", [companyId, line.id, entryId]);
      usedEntryIds.push(entryId);
    }
  }

  const countsRes = await client.query<{ status: string; cnt: string }>(
    `SELECT status, count(*) AS cnt FROM bank_statement_lines
      WHERE company_id = $1 AND statement_id = $2 AND status IN ('unmatched', 'suggested')
      GROUP BY status`,
    [companyId, statementId],
  );
  let suggested = 0;
  let unmatched = 0;
  for (const r of countsRes.rows) {
    if (r.status === "suggested") suggested = Number(r.cnt);
    else if (r.status === "unmatched") unmatched = Number(r.cnt);
  }
  return { suggested, unmatched };
}
