// Transaction register API — Task 1 of
// docs/superpowers/plans/2026-08-02-switch-from-anywhere.md (spec Part A of
// docs/superpowers/specs/2026-08-02-switch-from-anywhere-design.md).
//
// Two read-only endpoints:
//   GET /api/c/register      — paged rows (keyset pagination, newest first)
//   GET /api/c/register.csv  — the ENTIRE filtered set as a CSV download
//
// Two modes:
//   - all (default): one row per posted journal entry, with a plain-English
//     type label derived from the entry's provenance (source column for
//     imports; document link tables for invoices/payments/bills/checks;
//     bank_statement_lines for bank-created entries; intercompany_postings;
//     reverses_entry_id for reversals; else a manual entry).
//   - account: one row per journal line of one account, signed in the
//     account's NATURAL direction (assets/expenses debit-positive,
//     liabilities/equity/income credit-positive — presentation-layer flip
//     done in the SQL SELECT; storage stays debit-positive per CLAUDE.md
//     §12), plus a RUNNING BALANCE column.
//
// The running balance is a window SUM computed at query time (§3.4: no
// stored balances, ever) over the account's COMPLETE posted history in
// (entry_date, entry_no, line id) order — date/type/search/amount filters
// are applied AFTER the window, so a date-filtered register still shows the
// true balance as of each row (the classic register-view bug, avoided by
// construction: the window runs in a CTE that no filter reaches).
//
// Money is NUMERIC(19,4) strings end to end — never a JS number (§2/§12).
// The only string surgery here is money2dp() for the CSV (display trim of
// trailing zeros, sign untouched, no arithmetic).

import type { FastifyInstance } from "fastify";
import type { PoolClient } from "pg";
import { z } from "zod";
import type { Db } from "./db.js";
import { mapPgError } from "./errors.js";

// ---------------------------------------------------------------------------
// types + labels
// ---------------------------------------------------------------------------

/** Stable type keys the `type` FILTER accepts (spec Part A, plus "entry" so
 * rows labeled plain 'Entry' — an unrecognized source — stay reachable). */
const TYPE_FILTER_KEYS = [
  "invoice", "payment", "bill", "check", "bill-payment",
  "bank", "imported", "reversal", "intercompany", "manual", "entry",
] as const;

/**
 * Plain-English labels per type key. "entry" is the derived key for a posted
 * entry with a `source` value this codebase doesn't recognize (none exist in
 * any database today — quickbooks_journal is the only source ever written,
 * adp_gl and universal:<slug> are the two planned ones — but an unknown
 * source must label honestly as just 'Entry' rather than guessing).
 */
export const TYPE_LABELS: Record<string, string> = {
  invoice: "Invoice",
  payment: "Payment received",
  bill: "Bill",
  check: "Check",
  "bill-payment": "Bill payment",
  bank: "Bank entry",
  imported: "Imported history",
  reversal: "Reversal",
  intercompany: "Intercompany",
  manual: "Manual entry",
  entry: "Entry",
};

export interface RegisterRouteOptions {
  /** CSV export refuses (400) when the filtered set exceeds this many rows. */
  csvMaxRows?: number;
}

const DEFAULT_CSV_MAX_ROWS = 100_000;
const DEFAULT_PAGE_SIZE = 50;
const MAX_PAGE_SIZE = 200;

const CSV_TOO_MANY_ROWS_ERROR =
  "That's too many rows for one file — narrow the date range.";
const BAD_CURSOR_ERROR =
  "That page marker isn't valid — start from the first page.";
const ACCOUNT_REQUIRED_ERROR =
  "Pick an account to view its register.";
const NOT_FOUND_ACCOUNT = { error: "We couldn't find that account." };

// ---------------------------------------------------------------------------
// query-param validation
// ---------------------------------------------------------------------------

/** Empty string (`?from=`) is treated the same as an absent param. */
function emptyAsUndefined(v: unknown): unknown {
  return v === "" || v === undefined ? undefined : v;
}

/**
 * True only for a YYYY-MM-DD string that names a REAL calendar date.
 * z.iso.date checks shape, not the calendar — it accepts 2024-02-30 and year
 * 0000, both of which Postgres then rejects at `::date` (SQLSTATE 22008),
 * which would surface as a 500. Round-trips through UTC date components
 * (setUTCFullYear, so years < 100 aren't misread as 19xx) and rejects year
 * 0000 (no year zero in the proleptic Gregorian calendar Postgres uses).
 */
function isRealIsoDate(s: string): boolean {
  const m = /^(\d{4})-(\d{2})-(\d{2})$/.exec(s);
  if (!m) return false;
  const year = Number(m[1]);
  const month = Number(m[2]);
  const day = Number(m[3]);
  if (year === 0) return false;
  const dt = new Date(0);
  dt.setUTCFullYear(year, month - 1, day);
  return dt.getUTCFullYear() === year
    && dt.getUTCMonth() === month - 1
    && dt.getUTCDate() === day;
}

const REAL_DATE_MESSAGE = "must be a real calendar date (like 2024-01-15)";

const optionalIsoDate = z.preprocess(
  emptyAsUndefined,
  z.iso.date({ error: "must be a date like 2024-01-15 (YYYY-MM-DD)" })
    .refine(isRealIsoDate, { message: REAL_DATE_MESSAGE })
    .optional(),
);

// Money as a string, never a JS number (§2). 1-15 whole digits keeps every
// accepted value well inside NUMERIC(19,4).
const optionalMoney = z.preprocess(
  emptyAsUndefined,
  z.string().regex(/^\d{1,15}(\.\d{1,4})?$/, "must be an amount like 125.00").optional(),
);

const registerQuerySchema = z.object({
  mode: z.preprocess(emptyAsUndefined, z.enum(["all", "account"]).optional()),
  accountId: z.preprocess(
    emptyAsUndefined,
    z.string().regex(/^\d{1,18}$/, "That account link isn't valid.").optional(),
  ),
  from: optionalIsoDate,
  to: optionalIsoDate,
  type: z.preprocess(emptyAsUndefined, z.enum(TYPE_FILTER_KEYS).optional()),
  q: z.preprocess(emptyAsUndefined, z.string().max(200).optional()),
  amountMin: optionalMoney,
  amountMax: optionalMoney,
  cursor: z.preprocess(emptyAsUndefined, z.string().max(500).optional()),
  limit: z.preprocess(
    emptyAsUndefined,
    z.coerce.number().int().min(1).max(MAX_PAGE_SIZE).optional(),
  ),
});

function zodMessage(issues: Array<{ message: string }>): string {
  return issues.map((i) => i.message).join("; ");
}

// ---------------------------------------------------------------------------
// cursor (keyset pagination)
// ---------------------------------------------------------------------------

/**
 * The cursor encodes the LAST row's position in the stable ordering:
 * (entry_date, entry_no) in all mode, plus the line id in account mode.
 * entry_no is unique per company, and line id is unique, so both orderings
 * are total — identical dates can never produce duplicated or skipped rows
 * across pages.
 */
interface Cursor {
  d: string; // entry_date, YYYY-MM-DD
  n: string; // entry_no (bigint as string)
  l?: string; // line id (account mode only)
}

function encodeCursor(c: Cursor): string {
  return Buffer.from(JSON.stringify(c), "utf8").toString("base64url");
}

function decodeCursor(raw: string, mode: "all" | "account"): Cursor | null {
  let parsed: unknown;
  try {
    parsed = JSON.parse(Buffer.from(raw, "base64url").toString("utf8"));
  } catch {
    return null;
  }
  if (typeof parsed !== "object" || parsed === null) return null;
  const c = parsed as Record<string, unknown>;
  // isRealIsoDate, not just the shape regex: a tampered cursor like
  // {d:"2024-02-30"} would otherwise pass here and blow up at `::date`.
  if (typeof c.d !== "string" || !isRealIsoDate(c.d)) return null;
  if (typeof c.n !== "string" || !/^\d{1,18}$/.test(c.n)) return null;
  if (mode === "account") {
    if (typeof c.l !== "string" || !/^\d{1,18}$/.test(c.l)) return null;
    return { d: c.d, n: c.n, l: c.l };
  }
  return { d: c.d, n: c.n };
}

// ---------------------------------------------------------------------------
// SQL construction
// ---------------------------------------------------------------------------

/** Escapes LIKE/ILIKE wildcards so a user's "%"/"_" match literally. */
function likePattern(q: string): string {
  return `%${q.replace(/[\\%_]/g, (m) => `\\${m}`)}%`;
}

/**
 * Provenance joins, shared by both modes. `eid` is the SQL expression for
 * the entry id (e.g. "e.id" or "a.entry_id") — internal constants, never
 * user input. Each lateral is a single index probe per row; together they
 * answer "which document (if any) posted this entry", which is what the
 * plain-English type label and the drill-to-source link derive from.
 */
function provenanceJoins(eid: string): string {
  return `
    LEFT JOIN LATERAL (
      SELECT i.id, c.name FROM invoices i
        JOIN customers c ON c.company_id = i.company_id AND c.id = i.customer_id
       WHERE i.company_id = $1 AND i.posted_entry_id = ${eid}
       LIMIT 1
    ) inv ON true
    LEFT JOIN LATERAL (
      SELECT p.id, c.name FROM payments p
        JOIN customers c ON c.company_id = p.company_id AND c.id = p.customer_id
       WHERE p.company_id = $1 AND p.posted_entry_id = ${eid}
       LIMIT 1
    ) pay ON true
    LEFT JOIN LATERAL (
      SELECT b.id, v.name FROM bills b
        JOIN vendors v ON v.company_id = b.company_id AND v.id = b.vendor_id
       WHERE b.company_id = $1 AND b.posted_entry_id = ${eid}
       LIMIT 1
    ) bil ON true
    LEFT JOIN LATERAL (
      SELECT bp.id, bp.check_no, v.name FROM bill_payments bp
        JOIN vendors v ON v.company_id = bp.company_id AND v.id = bp.vendor_id
       WHERE bp.company_id = $1 AND bp.posted_entry_id = ${eid}
       LIMIT 1
    ) bpay ON true
    LEFT JOIN LATERAL (
      SELECT bsl.id FROM bank_statement_lines bsl
       WHERE bsl.company_id = $1 AND bsl.matched_entry_id = ${eid}
         AND bsl.status = 'created'
       LIMIT 1
    ) bnk ON true
    LEFT JOIN LATERAL (
      SELECT ip.id FROM intercompany_postings ip
       WHERE (ip.from_company_id = $1 AND ip.from_entry_id = ${eid})
          OR (ip.to_company_id = $1 AND ip.to_entry_id = ${eid})
       LIMIT 1
    ) ic ON true
    LEFT JOIN LATERAL (
      SELECT min(nullif(btrim(sj.name), '')) AS staged_name FROM staging_journal sj
       WHERE sj.company_id = $1 AND sj.posted_entry_id = ${eid}
    ) stg ON true`;
}

/**
 * The DISPLAYED name — the "Name" half of the spec's Name/Memo column: the
 * customer, vendor or payee this entry is about. coalesce, not concat_ws:
 * exactly one document can post an entry, so the first non-null IS the name,
 * and an accidental "Acme Supply Bob's Diner" mash-up is impossible.
 * Provenance order matches sourceLinkFor()'s, so the name and the "Open
 * invoice/bill/…" link always describe the same document; the staged name
 * (imported history's own Name column, which has no document behind it)
 * comes first because when it exists nothing else does.
 *
 * The separate `names` column below stays a concat — it is the SEARCH
 * corpus, where matching any of the candidates is the whole point.
 */
const nameExpr =
  "nullif(btrim(coalesce(stg.staged_name, inv.name, pay.name, bil.name, bpay.name)), '')";

/**
 * The type-key CASE. Precedence: a reversal is a reversal no matter what it
 * reverses; anything with a `source` came from an import (external_ref pairs
 * with it by CHECK constraint); then document links; then bank-created;
 * then intercompany; else it was typed in by hand. `src`/`rev` are the SQL
 * expressions for the entry's source / reverses_entry_id columns.
 */
function typeKeyCase(src: string, rev: string): string {
  return `
       CASE
         WHEN ${rev} IS NOT NULL THEN 'reversal'
         WHEN ${src} IS NOT NULL THEN
           CASE WHEN ${src} IN ('quickbooks_journal', 'adp_gl')
                  OR ${src} LIKE 'universal:%'
                THEN 'imported' ELSE 'entry' END
         WHEN inv.id IS NOT NULL THEN 'invoice'
         WHEN pay.id IS NOT NULL THEN 'payment'
         WHEN bil.id IS NOT NULL THEN 'bill'
         WHEN bpay.id IS NOT NULL THEN
           CASE WHEN bpay.check_no IS NOT NULL THEN 'check' ELSE 'bill-payment' END
         WHEN bnk.id IS NOT NULL THEN 'bank'
         WHEN ic.id IS NOT NULL THEN 'intercompany'
         ELSE 'manual'
       END`;
}

/** Status: reversed-by-something wins over being a reversal (spec Part A). */
function statusCase(eid: string, rev: string): string {
  return `
       CASE
         WHEN EXISTS (SELECT 1 FROM journal_entries r
                       WHERE r.company_id = $1 AND r.reverses_entry_id = ${eid})
           THEN 'reversed'
         WHEN ${rev} IS NOT NULL THEN 'reversal'
         ELSE 'posted'
       END`;
}

interface RegisterFilters {
  from?: string;
  to?: string;
  type?: string;
  q?: string;
  amountMin?: string;
  amountMax?: string;
}

interface BuiltQuery {
  /** SELECT of the filtered, ordered rows (LIMIT included when limit given). */
  text: string;
  /** count(*) over the same filtered set (no ordering/limit). */
  countText: string;
  values: unknown[];
}

/**
 * All-activity mode: one row per posted journal entry. Filters that don't
 * depend on derived columns (dates) are pushed into the inner query; type/
 * search/amount filters apply to the derived subquery. The keyset predicate
 * uses SQL row comparison, which matches the DESC ordering exactly.
 */
function buildAllQuery(
  companyId: string,
  f: RegisterFilters,
  cursor: Cursor | null,
  limit: number | null,
): BuiltQuery {
  const values: unknown[] = [companyId];
  const innerWhere: string[] = ["e.company_id = $1", "e.status = 'posted'"];
  if (f.from) {
    values.push(f.from);
    innerWhere.push(`e.entry_date >= $${values.length}::date`);
  }
  if (f.to) {
    values.push(f.to);
    innerWhere.push(`e.entry_date <= $${values.length}::date`);
  }

  const inner = `
    SELECT e.id AS entry_id, e.entry_no, e.entry_date,
           coalesce(e.memo, lm.line_memo) AS memo,
           tot.total AS amount,
           coalesce(e.external_ref, bpay.check_no::text) AS ref,
           ${typeKeyCase("e.source", "e.reverses_entry_id")} AS type_key,
           ${statusCase("e.id", "e.reverses_entry_id")} AS status,
           inv.id AS invoice_id, pay.id AS payment_id, bil.id AS bill_id,
           bpay.id AS bill_payment_id, bnk.id AS statement_line_id,
           ${nameExpr} AS name,
           concat_ws(' ', stg.staged_name, inv.name, pay.name, bil.name, bpay.name) AS names
      FROM journal_entries e
      JOIN LATERAL (
        SELECT coalesce(sum(l.debit), 0)::numeric(19,4) AS total
          FROM journal_lines l
         WHERE l.company_id = $1 AND l.entry_id = e.id
      ) tot ON true
      LEFT JOIN LATERAL (
        SELECT min(l.memo) AS line_memo FROM journal_lines l
         WHERE l.company_id = $1 AND l.entry_id = e.id AND l.memo IS NOT NULL
      ) lm ON true
      ${provenanceJoins("e.id")}
     WHERE ${innerWhere.join(" AND ")}`;

  const outerWhere: string[] = [];
  applyDerivedFilters(f, values, outerWhere);
  if (cursor) {
    values.push(cursor.d, cursor.n);
    outerWhere.push(
      `(reg.entry_date, reg.entry_no) < ($${values.length - 1}::date, $${values.length}::bigint)`,
    );
  }

  const whereSql = outerWhere.length > 0 ? `WHERE ${outerWhere.join(" AND ")}` : "";
  const countText = `SELECT count(*)::bigint AS n FROM (${inner}) reg ${whereSql}`;
  let text = `
    SELECT reg.*, reg.entry_date::text AS date_text
      FROM (${inner}) reg
      ${whereSql}
     ORDER BY reg.entry_date DESC, reg.entry_no DESC`;
  if (limit !== null) {
    values.push(limit);
    text += `\n     LIMIT $${values.length}`;
  }
  return { text, countText, values };
}

/**
 * Account (register) mode: one row per posted journal line of one account.
 * The `acct` CTE computes the signed amount and the running balance over the
 * account's COMPLETE posted history — deliberately BEFORE any filter, so a
 * date-filtered page still shows the true balance as of each row (§3.4: the
 * balance is derived at query time from journal_lines, nothing else).
 * `debitNormal` picks the account's natural direction; the flip is pure
 * presentation in this SELECT — storage stays debit-positive (§12).
 */
function buildAccountQuery(
  companyId: string,
  accountId: string,
  debitNormal: boolean,
  f: RegisterFilters,
  cursor: Cursor | null,
  limit: number | null,
): BuiltQuery {
  const values: unknown[] = [companyId, accountId, debitNormal];

  const inner = `
    WITH acct AS (
      SELECT l.id AS line_id, l.entry_id, l.debit, l.credit, l.memo AS line_memo,
             e.entry_no, e.entry_date, e.memo AS entry_memo,
             e.source, e.external_ref, e.reverses_entry_id,
             (CASE WHEN $3::boolean THEN l.debit - l.credit
                   ELSE l.credit - l.debit END)::numeric(19,4) AS signed_amount,
             (l.debit + l.credit)::numeric(19,4) AS abs_amount,
             sum(CASE WHEN $3::boolean THEN l.debit - l.credit
                      ELSE l.credit - l.debit END)
               OVER (ORDER BY e.entry_date, e.entry_no, l.id)
               ::numeric(19,4) AS running_balance
        FROM journal_lines l
        JOIN journal_entries e ON e.company_id = l.company_id AND e.id = l.entry_id
       WHERE l.company_id = $1 AND l.account_id = $2 AND e.status = 'posted'
    )
    SELECT a.line_id, a.entry_id, a.entry_no, a.entry_date,
           coalesce(a.entry_memo, a.line_memo) AS memo,
           a.debit, a.credit,
           a.signed_amount AS amount, a.abs_amount, a.running_balance,
           coalesce(a.external_ref, bpay.check_no::text) AS ref,
           ${typeKeyCase("a.source", "a.reverses_entry_id")} AS type_key,
           ${statusCase("a.entry_id", "a.reverses_entry_id")} AS status,
           inv.id AS invoice_id, pay.id AS payment_id, bil.id AS bill_id,
           bpay.id AS bill_payment_id, bnk.id AS statement_line_id,
           ${nameExpr} AS name,
           concat_ws(' ', stg.staged_name, inv.name, pay.name, bil.name, bpay.name) AS names
      FROM acct a
      ${provenanceJoins("a.entry_id")}`;

  const outerWhere: string[] = [];
  if (f.from) {
    values.push(f.from);
    outerWhere.push(`reg.entry_date >= $${values.length}::date`);
  }
  if (f.to) {
    values.push(f.to);
    outerWhere.push(`reg.entry_date <= $${values.length}::date`);
  }
  applyDerivedFilters(f, values, outerWhere, /* accountMode */ true);
  if (cursor) {
    values.push(cursor.d, cursor.n, cursor.l);
    outerWhere.push(
      `(reg.entry_date, reg.entry_no, reg.line_id) < ` +
      `($${values.length - 2}::date, $${values.length - 1}::bigint, $${values.length}::bigint)`,
    );
  }

  const whereSql = outerWhere.length > 0 ? `WHERE ${outerWhere.join(" AND ")}` : "";
  const countText = `SELECT count(*)::bigint AS n FROM (${inner}) reg ${whereSql}`;
  let text = `
    SELECT reg.*, reg.entry_date::text AS date_text
      FROM (${inner}) reg
      ${whereSql}
     ORDER BY reg.entry_date DESC, reg.entry_no DESC, reg.line_id DESC`;
  if (limit !== null) {
    values.push(limit);
    text += `\n     LIMIT $${values.length}`;
  }
  return { text, countText, values };
}

/**
 * Filters that reference derived columns (type, search corpus, amount) —
 * applied to the `reg` subquery in both modes. In all mode the amount filter
 * runs against the entry total (sum of debits); in account mode against the
 * line's absolute amount (exactly one of debit/credit is nonzero, so
 * debit + credit IS the magnitude).
 */
function applyDerivedFilters(
  f: RegisterFilters,
  values: unknown[],
  where: string[],
  accountMode = false,
): void {
  if (f.type) {
    values.push(f.type);
    where.push(`reg.type_key = $${values.length}`);
  }
  if (f.q) {
    values.push(likePattern(f.q));
    const p = `$${values.length}`;
    where.push(
      `(reg.memo ILIKE ${p} OR reg.ref ILIKE ${p} OR reg.names ILIKE ${p}
        OR EXISTS (SELECT 1 FROM journal_lines lq
                    WHERE lq.company_id = $1 AND lq.entry_id = reg.entry_id
                      AND lq.memo ILIKE ${p}))`,
    );
  }
  const amountCol = accountMode ? "reg.abs_amount" : "reg.amount";
  if (f.amountMin) {
    values.push(f.amountMin);
    where.push(`${amountCol} >= $${values.length}::numeric(19,4)`);
  }
  if (f.amountMax) {
    values.push(f.amountMax);
    where.push(`${amountCol} <= $${values.length}::numeric(19,4)`);
  }
}

// ---------------------------------------------------------------------------
// row shaping (shared by JSON and CSV)
// ---------------------------------------------------------------------------

interface RegisterDbRow {
  entry_id: string;
  entry_no: string;
  date_text: string;
  name: string | null;
  memo: string | null;
  amount: string;
  ref: string | null;
  type_key: string;
  status: string;
  invoice_id: string | null;
  payment_id: string | null;
  bill_id: string | null;
  bill_payment_id: string | null;
  statement_line_id: string | null;
  // account mode only:
  line_id?: string;
  debit?: string;
  credit?: string;
  running_balance?: string;
}

interface SourceLink {
  kind: "invoice" | "payment" | "bill" | "bill-payment" | "statement-line";
  id: string;
}

function sourceLinkFor(r: RegisterDbRow): SourceLink | undefined {
  if (r.invoice_id) return { kind: "invoice", id: r.invoice_id };
  if (r.payment_id) return { kind: "payment", id: r.payment_id };
  if (r.bill_id) return { kind: "bill", id: r.bill_id };
  if (r.bill_payment_id) return { kind: "bill-payment", id: r.bill_payment_id };
  if (r.statement_line_id) return { kind: "statement-line", id: r.statement_line_id };
  return undefined;
}

function toAllRow(r: RegisterDbRow) {
  return {
    id: r.entry_id,
    date: r.date_text,
    entryNo: r.entry_no,
    type: r.type_key,
    typeLabel: TYPE_LABELS[r.type_key] ?? TYPE_LABELS.entry,
    name: r.name,
    memo: r.memo,
    ref: r.ref,
    amount: r.amount,
    status: r.status,
    sourceLink: sourceLinkFor(r),
  };
}

function toAccountRow(r: RegisterDbRow) {
  // Exactly one of debit/credit is nonzero per line (CHECK constraint in
  // 0002); the zero side is omitted so the Dr/Cr toggle can trust presence.
  const debitSet = r.debit !== undefined && r.debit !== "0.0000";
  return {
    entryId: r.entry_id,
    date: r.date_text,
    entryNo: r.entry_no,
    type: r.type_key,
    typeLabel: TYPE_LABELS[r.type_key] ?? TYPE_LABELS.entry,
    name: r.name,
    memo: r.memo,
    ref: r.ref,
    amount: r.amount,
    debit: debitSet ? r.debit : undefined,
    credit: debitSet ? undefined : r.credit,
    runningBalance: r.running_balance!,
    // The register mode an accountant actually reconciles in must show a
    // reversed line as reversed — the SQL has always computed it here, it
    // just never left the server.
    status: r.status,
    sourceLink: sourceLinkFor(r),
  };
}

// ---------------------------------------------------------------------------
// CSV helpers
// ---------------------------------------------------------------------------

/**
 * NUMERIC(19,4) string -> plain display string: trailing zeros trimmed down
 * to (at least) 2 decimals. Pure string surgery — sign kept, no parsing to a
 * JS number, no rounding (a genuine sub-cent 4th decimal is kept, honestly).
 */
export function money2dp(raw: string): string {
  const m = /^(-?)(\d+)\.(\d+)$/.exec(raw);
  if (!m) return raw;
  const [, sign, whole, frac] = m;
  const trimmed = frac.replace(/0+$/, "");
  const decimals = trimmed.length <= 2 ? (trimmed + "00").slice(0, 2) : trimmed;
  return `${sign}${whole}.${decimals}`;
}

/**
 * Account-mode CSV filename: the account's NAME (slugified — lowercase,
 * every run of anything but a-z0-9 collapsed to one '-') plus the date
 * range when one is applied, e.g.
 * "chase-business-checking-2024-01-01-to-2024-12-31.csv". The slug can
 * only contain [a-z0-9-], so it is inert inside the quoted
 * Content-Disposition filename (no quotes/CRLF/8-bit to escape); a name
 * with no usable characters at all falls back to "account".
 */
export function accountCsvFilename(name: string, from?: string, to?: string): string {
  const slug =
    name.toLowerCase().replace(/[^a-z0-9]+/g, "-").replace(/^-+|-+$/g, "") || "account";
  let range = "";
  if (from && to) range = `-${from}-to-${to}`;
  else if (from) range = `-from-${from}`;
  else if (to) range = `-through-${to}`;
  return `${slug}${range}.csv`;
}

/** RFC 4180 field quoting: quote when needed, double any embedded quotes. */
export function csvField(v: string | null | undefined): string {
  const s = v ?? "";
  if (/[",\r\n]/.test(s)) {
    return `"${s.replaceAll('"', '""')}"`;
  }
  return s;
}

// Name gets its own column in both exports (spec Part A's Name/Memo): the
// screen can stack them in one cell, a spreadsheet cannot sort by half of one.
// Status rides along in account mode too — a reversed line looks exactly like
// a live one otherwise, which is precisely the reconciliation the export is
// downloaded for.
const CSV_ALL_HEADER = ["Date", "No.", "Type", "Name", "Memo", "Ref", "Amount", "Status"];
const CSV_ACCOUNT_HEADER = [
  "Date", "No.", "Type", "Name", "Memo", "Ref", "Amount", "Debit", "Credit",
  "Running balance", "Status",
];

function csvLineAll(r: RegisterDbRow): string {
  return [
    r.date_text,
    r.entry_no,
    TYPE_LABELS[r.type_key] ?? TYPE_LABELS.entry,
    r.name ?? "",
    r.memo ?? "",
    r.ref ?? "",
    money2dp(r.amount),
    r.status,
  ].map(csvField).join(",");
}

function csvLineAccount(r: RegisterDbRow): string {
  const debitSet = r.debit !== undefined && r.debit !== "0.0000";
  return [
    r.date_text,
    r.entry_no,
    TYPE_LABELS[r.type_key] ?? TYPE_LABELS.entry,
    r.name ?? "",
    r.memo ?? "",
    r.ref ?? "",
    money2dp(r.amount),
    debitSet ? money2dp(r.debit!) : "",
    debitSet ? "" : money2dp(r.credit!),
    money2dp(r.running_balance!),
    r.status,
  ].map(csvField).join(",");
}

// ---------------------------------------------------------------------------
// shared per-request plumbing
// ---------------------------------------------------------------------------

interface AccountInfo {
  id: string;
  name: string;
  type: string;
  debitNormal: boolean;
}

async function lookupAccount(
  client: PoolClient, companyId: string, accountId: string,
): Promise<AccountInfo | null> {
  const res = await client.query<{ id: string; name: string; type: string }>(
    "SELECT id, name, type FROM accounts WHERE company_id = $1 AND id = $2",
    [companyId, accountId],
  );
  const row = res.rows[0];
  if (!row) return null;
  return {
    id: row.id,
    name: row.name,
    type: row.type,
    debitNormal: row.type === "asset" || row.type === "expense",
  };
}

/**
 * Belt and braces on top of isRealIsoDate(): if an impossible date ever DOES
 * reach Postgres (a future param, a missed path), SQLSTATE 22007 (invalid
 * datetime format) / 22008 (datetime field overflow) is the user's mistake,
 * not a server fault — map it to a 400 in English instead of letting
 * mapPgError's generic 500 claim something broke on our end.
 */
const INVALID_DATETIME_FORMAT = "22007";
const DATETIME_FIELD_OVERFLOW = "22008";

function mapRegisterError(err: unknown): { status: number; message: string } {
  const code = (err as { code?: string } | undefined)?.code;
  if (code === INVALID_DATETIME_FORMAT || code === DATETIME_FIELD_OVERFLOW) {
    return {
      status: 400,
      message: "That isn't a real calendar date — check the dates and try again.",
    };
  }
  return mapPgError(err);
}

type ParsedRegisterQuery = z.infer<typeof registerQuerySchema>;

function filtersOf(p: ParsedRegisterQuery): RegisterFilters {
  return {
    from: p.from,
    to: p.to,
    type: p.type,
    q: p.q,
    amountMin: p.amountMin,
    amountMax: p.amountMax,
  };
}

// ---------------------------------------------------------------------------
// route registration
// ---------------------------------------------------------------------------

export function registerRegisterRoutes(
  fastify: FastifyInstance, db: Db, opts: RegisterRouteOptions = {},
): void {
  const csvMaxRows = opts.csvMaxRows ?? DEFAULT_CSV_MAX_ROWS;

  // -------------------------------------------------------------------------
  // GET /api/c/register
  // -------------------------------------------------------------------------
  fastify.get("/api/c/register", async (request, reply) => {
    const companyId = request.companyId!;
    const parsed = registerQuerySchema.safeParse(request.query);
    if (!parsed.success) {
      return reply.code(400).send({ error: zodMessage(parsed.error.issues) });
    }
    const p = parsed.data;
    const mode = p.mode ?? "all";
    const limit = p.limit ?? DEFAULT_PAGE_SIZE;

    if (mode === "account" && !p.accountId) {
      return reply.code(400).send({ error: ACCOUNT_REQUIRED_ERROR });
    }
    let cursor: Cursor | null = null;
    if (p.cursor !== undefined) {
      cursor = decodeCursor(p.cursor, mode);
      if (cursor === null) {
        return reply.code(400).send({ error: BAD_CURSOR_ERROR });
      }
    }

    try {
      // Never reply.send() inside db.withCompany() (double-send hazard if
      // COMMIT throws — see reports.ts for the full note): return a
      // discriminated result and reply after the transaction resolves.
      const result = await db.withCompany(companyId, async (client) => {
        if (mode === "account") {
          const account = await lookupAccount(client, companyId, p.accountId!);
          // RLS makes another company's account id look exactly like an
          // unknown one — zero rows either way, both correctly 404 (§4/§9).
          if (!account) return { notFound: true as const };
          const q = buildAccountQuery(
            companyId, account.id, account.debitNormal, filtersOf(p), cursor, limit + 1,
          );
          const rows = (await client.query<RegisterDbRow>(q.text, q.values)).rows;
          const page = rows.slice(0, limit);
          const last = page[page.length - 1];
          const nextCursor = rows.length > limit && last
            ? encodeCursor({ d: last.date_text, n: last.entry_no, l: last.line_id! })
            : null;
          return {
            notFound: false as const,
            body: {
              mode,
              account: {
                id: account.id, name: account.name, type: account.type,
                debitNormal: account.debitNormal,
              },
              rows: page.map(toAccountRow),
              nextCursor,
            },
          };
        }

        const q = buildAllQuery(companyId, filtersOf(p), cursor, limit + 1);
        const rows = (await client.query<RegisterDbRow>(q.text, q.values)).rows;
        const page = rows.slice(0, limit);
        const last = page[page.length - 1];
        const nextCursor = rows.length > limit && last
          ? encodeCursor({ d: last.date_text, n: last.entry_no })
          : null;
        return {
          notFound: false as const,
          body: { mode, rows: page.map(toAllRow), nextCursor },
        };
      });

      if (result.notFound) {
        return reply.code(404).send(NOT_FOUND_ACCOUNT);
      }
      return reply.send(result.body);
    } catch (err) {
      const mapped = mapRegisterError(err);
      if (mapped.status === 500) request.log.error({ err }, "register query failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // GET /api/c/register.csv — the ENTIRE filtered set (no cursor/limit)
  // -------------------------------------------------------------------------
  fastify.get("/api/c/register.csv", async (request, reply) => {
    const companyId = request.companyId!;
    const parsed = registerQuerySchema.safeParse(request.query);
    if (!parsed.success) {
      return reply.code(400).send({ error: zodMessage(parsed.error.issues) });
    }
    const p = parsed.data;
    const mode = p.mode ?? "all";

    if (mode === "account" && !p.accountId) {
      return reply.code(400).send({ error: ACCOUNT_REQUIRED_ERROR });
    }

    try {
      const result = await db.withCompany(companyId, async (client) => {
        let q: BuiltQuery;
        let filename: string;
        let header: string[];
        let toLine: (r: RegisterDbRow) => string;

        if (mode === "account") {
          const account = await lookupAccount(client, companyId, p.accountId!);
          if (!account) return { kind: "notFound" as const };
          q = buildAccountQuery(
            companyId, account.id, account.debitNormal, filtersOf(p), null, null,
          );
          filename = accountCsvFilename(account.name, p.from, p.to);
          header = CSV_ACCOUNT_HEADER;
          toLine = csvLineAccount;
        } else {
          q = buildAllQuery(companyId, filtersOf(p), null, null);
          filename = "transactions.csv";
          header = CSV_ALL_HEADER;
          toLine = csvLineAll;
        }

        // Cap safety: count BEFORE materializing anything (spec Part A).
        const countRes = await client.query<{ n: string }>(q.countText, q.values);
        const n = BigInt(countRes.rows[0]!.n);
        if (n > BigInt(csvMaxRows)) {
          return { kind: "tooMany" as const };
        }

        const rows = (await client.query<RegisterDbRow>(q.text, q.values)).rows;
        const lines = [header.map(csvField).join(",")];
        for (const r of rows) lines.push(toLine(r));
        return { kind: "csv" as const, filename, csv: lines.join("\r\n") + "\r\n" };
      });

      if (result.kind === "notFound") {
        return reply.code(404).send(NOT_FOUND_ACCOUNT);
      }
      if (result.kind === "tooMany") {
        return reply.code(400).send({ error: CSV_TOO_MANY_ROWS_ERROR });
      }
      return reply
        .header("content-type", "text/csv; charset=utf-8")
        .header("content-disposition", `attachment; filename="${result.filename}"`)
        .send(result.csv);
    } catch (err) {
      const mapped = mapRegisterError(err);
      if (mapped.status === 500) request.log.error({ err }, "register csv failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });
}
