// Read-only reports API — Task 3 of docs/superpowers/plans/2026-07-31-local-app.md.
// Every route lives under /api/c/ (company scope from X-Company-Id, enforced
// by companyContextPlugin) and every query runs inside db.withCompany() so
// RLS is the actual guard (CLAUDE.md §4/§12), not just the WHERE clauses
// written here. Storage stays debit-positive everywhere (§12); the sign
// flips done in this file are presentation ONLY, and they operate on the
// NUMERIC(19,4) strings Postgres returns — never on a parsed JS number (§2:
// money is never a raw JS number, not even transiently). All summation and
// subtraction of money happens in SQL; the only string surgery done in JS is
// flipSign(), which never inspects magnitude, only the leading sign.
//
// Two report shapes on purpose (per Task 3 / design spec):
//  - trial-balance is the "accountant view": raw, debit-positive, verbatim
//    from trial_balance() — debit/credit columns shown as-is.
//  - dashboard/pnl/balance-sheet are presentation-signed: income, liability
//    and equity balances are sign-flipped so a bigger number always reads as
//    "more of this", per CLAUDE.md §9 (no debit/credit language in the UI).
//
// Balance-sheet accounts (asset/liability/equity) are inherently cumulative
// — there's no periodic close in this system, so trial_balance(asOf) IS the
// balance-sheet-as-of-that-date, and it also gives the running income/expense
// total needed for the "Accumulated profit" line (see registerReportRoutes
// balance-sheet handler for the identity this relies on). P&L is a PERIOD
// report, not cumulative, so it's a bespoke query filtered to [from, to]
// rather than a reuse of trial_balance().

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

// ---------------------------------------------------------------------------
// shared validation + helpers
// ---------------------------------------------------------------------------

/** Optional ISO date (YYYY-MM-DD) query param. An empty string (`?asOf=`) is
 * treated the same as an absent param, rather than a validation error. */
const optionalIsoDate = z.preprocess(
  (v) => (v === "" || v === undefined ? undefined : v),
  z.iso.date({ error: "must be a date like 2024-01-15 (YYYY-MM-DD)" }).optional(),
);

// Bounded to 1-18 digits: every id in this schema is a Postgres bigint
// (GENERATED ALWAYS AS IDENTITY), whose max value is 9223372036854775807 (19
// digits) — capping at 18 digits keeps every accepted value comfortably
// bigint-safe without needing a magnitude comparison against that max, and
// turns a too-long id into a friendly 400 instead of a raw Postgres
// "bigint out of range" 500.
const idParamSchema = z.object({
  id: z.string().regex(/^\d{1,18}$/, "must be a whole number id (no more than 18 digits)"),
});

const dateRangeQuerySchema = z.object({ from: optionalIsoDate, to: optionalIsoDate });
const asOfQuerySchema = z.object({ asOf: optionalIsoDate });
const accountEntriesQuerySchema = z.object({
  from: optionalIsoDate,
  to: optionalIsoDate,
  limit: z.coerce.number().int().min(1).max(500).optional(),
  offset: z.coerce.number().int().min(0).optional(),
});

const NOT_FOUND_ACCOUNT = { error: "We couldn't find that account." };
const NOT_FOUND_ENTRY = { error: "We couldn't find that entry." };

/**
 * Flips the presentation sign of a NUMERIC(19,4) string as returned by
 * Postgres (e.g. "-1234.5600" -> "1234.5600"), without ever parsing it into a
 * JS number. Storage is debit-positive everywhere (§12); this is the ONE
 * place that display sign is decided, and it's pure string surgery on the
 * leading "-", nothing more. Normalizes "-0.0000" to "0.0000" so a flipped
 * zero never displays with a stray minus.
 */
function flipSign(amount: string): string {
  const negative = amount.startsWith("-");
  const digits = negative ? amount.slice(1) : amount;
  if (/^0+(\.0+)?$/.test(digits)) return digits;
  return negative ? digits : `-${digits}`;
}

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

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

export function registerReportRoutes(fastify: FastifyInstance, db: Db): void {
  // -------------------------------------------------------------------------
  // GET /api/c/reports/dashboard
  // -------------------------------------------------------------------------
  fastify.get("/api/c/reports/dashboard", async (request, reply) => {
    const companyId = request.companyId!;
    try {
      return await db.withCompany(companyId, async (client) => {
        // Derived in SQL, not new Date().getFullYear(): the server process
        // and the database may not agree on local timezone/clock, and "today"
        // for a books-and-records app should mean the database's clock. No
        // behavior change today (both agree), but this removes that future
        // timezone hazard.
        const currentYearRes = await client.query<{ year: number }>(
          "SELECT extract(year FROM current_date)::int AS year",
        );
        const currentYear = currentYearRes.rows[0]!.year;

        const healthRes = await client.query<{ out_of_balance: string }>(
          "SELECT ledger_out_of_balance($1, NULL) AS out_of_balance", [companyId],
        );
        const outOfBalance = healthRes.rows[0]!.out_of_balance;

        // Inventory's ledger_out_of_balance(). The inventory design spec's
        // decision 7 calls inventory_out_of_tie() "the single number for the
        // health panel and for tests" — the tests and the valuation report
        // used it from day one, the health panel did not, so until now the one
        // screen the spec named for it never read it. 0.0000 for a company
        // with no items (check_inventory_ties() returns no rows), so this
        // costs a company that keeps no stock nothing and says nothing.
        //
        // Summed as ABSOLUTE differences, so two inventory accounts wrong in
        // opposite directions cannot cancel into a clean bill of health — and
        // for the same reason this number has no direction, which is why the
        // panel's wording says "off by", never "over" or "short".
        const inventoryTieRes = await client.query<{ out_of_tie: string }>(
          "SELECT inventory_out_of_tie($1, NULL) AS out_of_tie", [companyId],
        );
        const inventoryOutOfTie = inventoryTieRes.rows[0]!.out_of_tie;

        // Cumulative (all-history) totals, straight from trial_balance() —
        // one query, reused for both the tiles and the balance-sheet section.
        const totalsRes = await client.query<{
          assets_total: string; liabilities_total: string; equity_total: string; net_worth: string;
        }>(
          `SELECT
             coalesce(sum(balance) FILTER (WHERE type = 'asset'), 0)::numeric(19,4) AS assets_total,
             coalesce(sum(balance) FILTER (WHERE type = 'liability'), 0)::numeric(19,4) AS liabilities_total,
             coalesce(sum(balance) FILTER (WHERE type = 'equity'), 0)::numeric(19,4) AS equity_total,
             -- Net worth = "you have" minus "you owe" = assets_total - flipSign(liabilities_total).
             -- flipSign negates the raw (debit-positive) string, so this is just
             -- assets_total + liabilities_total — done here in SQL, in exact
             -- NUMERIC arithmetic, so the client never has to subtract two money
             -- strings itself (CLAUDE.md §2/§12: no float math on money, anywhere).
             (coalesce(sum(balance) FILTER (WHERE type = 'asset'), 0)
               + coalesce(sum(balance) FILTER (WHERE type = 'liability'), 0))::numeric(19,4)
               AS net_worth
           FROM trial_balance($1, NULL)`,
          [companyId],
        );
        const totals = totalsRes.rows[0]!;

        const balanceRowsRes = await client.query<{
          account_id: string; name: string; type: string; balance: string;
        }>(
          `SELECT account_id, name, type, balance FROM trial_balance($1, NULL)
            WHERE type IN ('asset', 'liability') AND balance <> 0`,
          [companyId],
        );

        // Task 12 health panel item: accounts whose balance sign contradicts
        // their normal side — an asset/expense account should never carry a
        // net CREDIT balance (raw balance < 0), and a liability/equity/income
        // account should never carry a net DEBIT balance (raw balance > 0).
        // Storage stays debit-positive (§12); flipSign() below is the same
        // presentation-only sign flip used everywhere else in this file —
        // applying it here means every wrong-sign row's DISPLAYED balance is
        // always negative (a debit-normal account that's wrongly negative
        // stays negative unflipped; a credit-normal account that's wrongly
        // positive gets flipped to negative), which is what makes "negative"
        // read as "this is the one that's off" consistently in the UI,
        // regardless of which side the account is normally on.
        const wrongSignRes = await client.query<{
          account_id: string; name: string; type: string; balance: string;
        }>(
          `SELECT account_id, name, type, balance FROM trial_balance($1, NULL)
            WHERE (type IN ('asset', 'expense') AND balance < 0)
               OR (type IN ('liability', 'equity', 'income') AND balance > 0)
            ORDER BY name`,
          [companyId],
        );

        // Period activity by calendar year for income/expense accounts.
        // profit's line-level contribution is (credit - debit) for BOTH
        // income and expense: for income that's the normal-side increase;
        // for expense, -(debit-credit) [an expense increase should REDUCE
        // profit] simplifies to the same (credit - debit) expression. So one
        // sum(l.credit - l.debit) over both types gives the period profit
        // directly, entirely in SQL.
        const yearsRes = await client.query<{
          year: number; money_in: string; money_out: string; profit: string;
        }>(
          `SELECT extract(year FROM e.entry_date)::int AS year,
                  coalesce(sum(l.credit - l.debit) FILTER (WHERE a.type = 'income'), 0)::numeric(19,4)
                    AS money_in,
                  coalesce(sum(l.debit - l.credit) FILTER (WHERE a.type = 'expense'), 0)::numeric(19,4)
                    AS money_out,
                  coalesce(sum(l.credit - l.debit), 0)::numeric(19,4) AS profit
             FROM journal_lines l
             JOIN journal_entries e ON e.company_id = l.company_id AND e.id = l.entry_id
             JOIN accounts a ON a.company_id = l.company_id AND a.id = l.account_id
            WHERE l.company_id = $1 AND e.status = 'posted' AND a.type IN ('income', 'expense')
            GROUP BY 1
            ORDER BY 1`,
          [companyId],
        );
        const currentYearRow = yearsRes.rows.find((r) => r.year === currentYear);

        // Task 3 (docs/superpowers/plans/2026-07-31-ar.md) health panel item:
        // the single oldest open receivable, by due_date, among issued
        // (non-draft, non-void) invoices that still have money owed —
        // includes document_only invoices, same as ar_aging()/the
        // open-invoices picker (server/src/payments.ts). daysOverdue is
        // clamped to 0 for an invoice that isn't overdue yet (a not-yet-due
        // invoice can still be "the oldest open one"), computed against the
        // database's own clock (current_date), not a JS Date, for the same
        // reason every other "today" in this file is (see the file header).
        const oldestReceivableRes = await client.query<{
          id: string; invoice_no: string; customer_name: string; due_date: string;
          days_overdue: number; open: string;
        }>(
          `SELECT i.id, i.invoice_no, c.name AS customer_name, i.due_date::text AS due_date,
                  GREATEST((current_date - i.due_date), 0)::int AS days_overdue,
                  invoice_open_balance($1, i.id) AS open
             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.status = 'open'
              AND invoice_open_balance($1, i.id) > 0
            ORDER BY i.due_date ASC, i.id ASC
            LIMIT 1`,
          [companyId],
        );
        const oldestReceivableRow = oldestReceivableRes.rows[0];
        const oldestReceivable = oldestReceivableRow
          ? {
              invoiceId: oldestReceivableRow.id,
              invoiceNo: oldestReceivableRow.invoice_no,
              customerName: oldestReceivableRow.customer_name,
              dueDate: oldestReceivableRow.due_date,
              daysOverdue: oldestReceivableRow.days_overdue,
              open: oldestReceivableRow.open,
            }
          : null;

        // Task 3 (docs/superpowers/plans/2026-08-01-ap.md) health panel items:
        // overdueBills (open, non-void, issued bills past due_date) and
        // billsDueSoon (open, non-void, issued bills due within the next 7
        // days but NOT yet overdue) — both counted and summed by OPEN
        // balance (bill_open_balance()), never the bill's full total, and
        // both derived entirely in SQL against the database's own clock
        // (current_date), same rationale as every other "today" in this
        // file. issued_on IS NOT NULL excludes drafts forever (a draft never
        // gets one — mirrors ap_aging()'s own point-in-time gate); status <>
        // 'void' excludes voided bills; the open>0 filter excludes paid ones.
        const billHealthRes = await client.query<{
          overdue_count: string; overdue_total: string;
          due_soon_count: string; due_soon_total: string;
        }>(
          `SELECT
             count(*) FILTER (WHERE b.due_date < current_date) AS overdue_count,
             coalesce(sum(bill_open_balance($1, b.id))
               FILTER (WHERE b.due_date < current_date), 0)::numeric(19,4) AS overdue_total,
             count(*) FILTER (WHERE b.due_date >= current_date
               AND b.due_date <= current_date + 7) AS due_soon_count,
             coalesce(sum(bill_open_balance($1, b.id))
               FILTER (WHERE b.due_date >= current_date AND b.due_date <= current_date + 7), 0)
               ::numeric(19,4) AS due_soon_total
             FROM bills b
            WHERE b.company_id = $1 AND b.status = 'open'
              AND bill_open_balance($1, b.id) > 0`,
          [companyId],
        );
        const billHealth = billHealthRes.rows[0]!;

        // Bank Rec Task 5 health panel items: accounts that have never been
        // reconciled, and accounts reconciled but 30+ days stale. Same
        // "money account" universe as GET /api/c/bank/overview (every
        // active asset account — bank_statements_account_is_asset already
        // enforces asset-only at the schema level, so this mirrors that,
        // not an arbitrary new filter). effective_reconciled_through()
        // (0011_bank_rec.sql) is the same "latest append-only row" function
        // the reconcile guard itself uses — no separate/divergent notion of
        // "reconciled through" is invented here. neverReconciled only
        // includes accounts that actually HAVE posted activity (an unused
        // asset account with zero entries has nothing to reconcile yet, so
        // flagging it would just be noise); staleReconciliation only
        // requires that a reconciliation exist and be 30+ days behind
        // today's date (current_date, not a JS Date — see this file's own
        // header for why every "today" here comes from the database).
        const bankHealthRes = await client.query<{
          account_id: string; name: string; reconciled_through: string | null;
          days_since_reconciled: number | null; has_entries: boolean;
        }>(
          `SELECT a.id AS account_id, a.name,
                  rt.reconciled_through::text AS reconciled_through,
                  (current_date - rt.reconciled_through) AS days_since_reconciled,
                  EXISTS (
                    SELECT 1 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 = a.company_id AND l.account_id = a.id AND e.status = 'posted'
                  ) AS has_entries
             FROM accounts a
             CROSS JOIN LATERAL (
               SELECT effective_reconciled_through(a.company_id, a.id) AS reconciled_through
             ) rt
            WHERE a.company_id = $1 AND a.type = 'asset' AND a.active = true
            ORDER BY a.code`,
          [companyId],
        );
        const STALE_RECONCILIATION_DAYS = 30;
        const neverReconciled = bankHealthRes.rows
          .filter((r) => r.has_entries && r.reconciled_through === null)
          .map((r) => ({ accountId: r.account_id, name: r.name }));
        const staleReconciliation = bankHealthRes.rows
          .filter((r) =>
            r.reconciled_through !== null &&
            (r.days_since_reconciled ?? 0) >= STALE_RECONCILIATION_DAYS,
          )
          .map((r) => ({
            accountId: r.account_id, name: r.name, reconciledThrough: r.reconciled_through as string,
          }));

        const activityRes = await client.query<{
          id: string; entry_no: string; entry_date: string; memo: string | null;
          amount: string; reversed: boolean; who: string | null;
        }>(
          `SELECT e.id, e.entry_no, e.entry_date::text AS entry_date, e.memo,
                  (SELECT coalesce(sum(l.debit), 0) FROM journal_lines l
                    WHERE l.company_id = e.company_id AND l.entry_id = e.id)::numeric(19,4) AS amount,
                  EXISTS (
                    SELECT 1 FROM journal_entries r
                     WHERE r.company_id = e.company_id AND r.reverses_entry_id = e.id
                  ) AS reversed,
                  (SELECT min(nullif(btrim(sj.name), '')) FROM staging_journal sj
                    WHERE sj.company_id = e.company_id AND sj.posted_entry_id = e.id) AS who
             FROM journal_entries e
            WHERE e.company_id = $1 AND e.status = 'posted'
            ORDER BY e.entry_date DESC, e.entry_no DESC
            LIMIT 10`,
          [companyId],
        );

        return {
          health: {
            outOfBalance,
            balanced: outOfBalance === "0.0000",
            wrongSign: wrongSignRes.rows.map((r) => ({
              accountId: r.account_id,
              name: r.name,
              balance: r.type === "asset" || r.type === "expense" ? r.balance : flipSign(r.balance),
            })),
            oldestReceivable,
            overdueBills: { count: Number(billHealth.overdue_count), total: billHealth.overdue_total },
            billsDueSoon: { count: Number(billHealth.due_soon_count), total: billHealth.due_soon_total },
            neverReconciled,
            staleReconciliation,
            inventoryOutOfTie,
            inventoryTies: inventoryOutOfTie === "0.0000",
          },
          tiles: {
            youHave: totals.assets_total,
            youOwe: flipSign(totals.liabilities_total),
            yearMoneyIn: currentYearRow?.money_in ?? "0.0000",
            yearProfit: currentYearRow?.profit ?? "0.0000",
            netWorth: totals.net_worth,
          },
          years: yearsRes.rows.map((r) => ({
            year: r.year, moneyIn: r.money_in, moneyOut: r.money_out,
          })),
          balanceSheet: {
            assets: balanceRowsRes.rows
              .filter((r) => r.type === "asset")
              .map((r) => ({ accountId: r.account_id, name: r.name, balance: r.balance })),
            liabilities: balanceRowsRes.rows
              .filter((r) => r.type === "liability")
              .map((r) => ({ accountId: r.account_id, name: r.name, balance: flipSign(r.balance) })),
            equityTotal: flipSign(totals.equity_total),
          },
          activity: activityRes.rows.map((r) => ({
            entryId: r.id, entryNo: r.entry_no, date: r.entry_date, who: r.who,
            memo: r.memo, amount: r.amount, reversed: r.reversed,
          })),
        };
      });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "dashboard report failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // GET /api/c/reports/trial-balance?asOf=
  // -------------------------------------------------------------------------
  fastify.get("/api/c/reports/trial-balance", async (request, reply) => {
    const companyId = request.companyId!;
    const parsedQuery = asOfQuerySchema.safeParse(request.query);
    if (!parsedQuery.success) {
      return reply.code(400).send({ error: zodMessage(parsedQuery.error.issues) });
    }
    const asOf = parsedQuery.data.asOf ?? null;

    try {
      return await db.withCompany(companyId, async (client) => {
        const rowsRes = await client.query<{
          account_id: string; code: string; name: string; type: string;
          debit_total: string; credit_total: string; balance: string;
        }>(
          `SELECT account_id, code, name, type, debit_total, credit_total, balance
             FROM trial_balance($1, $2)`,
          [companyId, asOf],
        );
        const totalsRes = await client.query<{ debit: string; credit: string }>(
          `SELECT coalesce(sum(debit_total), 0)::numeric(19,4) AS debit,
                  coalesce(sum(credit_total), 0)::numeric(19,4) AS credit
             FROM trial_balance($1, $2)`,
          [companyId, asOf],
        );

        return {
          asOf,
          rows: rowsRes.rows.map((r) => ({
            accountId: r.account_id, code: r.code, name: r.name, type: r.type,
            debit: r.debit_total, credit: r.credit_total, balance: r.balance,
          })),
          totals: totalsRes.rows[0]!,
        };
      });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "trial balance report failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // GET /api/c/reports/pnl?from=&to=
  // -------------------------------------------------------------------------
  fastify.get("/api/c/reports/pnl", async (request, reply) => {
    const companyId = request.companyId!;
    const parsedQuery = dateRangeQuerySchema.safeParse(request.query);
    if (!parsedQuery.success) {
      return reply.code(400).send({ error: zodMessage(parsedQuery.error.issues) });
    }
    const from = parsedQuery.data.from ?? null;
    const to = parsedQuery.data.to ?? null;

    try {
      return await db.withCompany(companyId, async (client) => {
        // Period activity, NOT cumulative — unlike balance-sheet accounts,
        // income/expense have no meaning "as of" a date without a range.
        const rowsRes = await client.query<{
          account_id: string; code: string; name: string; type: string; amount: string;
        }>(
          `SELECT a.id AS account_id, a.code, a.name, a.type,
                  (CASE WHEN a.type = 'income' THEN sum(l.credit - l.debit)
                        ELSE sum(l.debit - l.credit) END)::numeric(19,4) AS amount
             FROM journal_lines l
             JOIN journal_entries e ON e.company_id = l.company_id AND e.id = l.entry_id
             JOIN accounts a ON a.company_id = l.company_id AND a.id = l.account_id
            WHERE l.company_id = $1 AND e.status = 'posted' AND a.type IN ('income', 'expense')
              AND e.entry_date >= coalesce($2::date, '-infinity')
              AND e.entry_date <= coalesce($3::date, 'infinity')
            GROUP BY a.id, a.code, a.name, a.type
           HAVING sum(l.debit) <> 0 OR sum(l.credit) <> 0
            ORDER BY a.type, a.code`,
          [companyId, from, to],
        );

        const totalsRes = await client.query<{
          income_total: string; expense_total: string; net: string;
        }>(
          `SELECT
             coalesce(sum(CASE WHEN a.type = 'income' THEN l.credit - l.debit ELSE 0 END), 0)::numeric(19,4)
               AS income_total,
             coalesce(sum(CASE WHEN a.type = 'expense' THEN l.debit - l.credit ELSE 0 END), 0)::numeric(19,4)
               AS expense_total,
             coalesce(sum(l.credit - l.debit), 0)::numeric(19,4) AS net
             FROM journal_lines l
             JOIN journal_entries e ON e.company_id = l.company_id AND e.id = l.entry_id
             JOIN accounts a ON a.company_id = l.company_id AND a.id = l.account_id
            WHERE l.company_id = $1 AND e.status = 'posted' AND a.type IN ('income', 'expense')
              AND e.entry_date >= coalesce($2::date, '-infinity')
              AND e.entry_date <= coalesce($3::date, 'infinity')`,
          [companyId, from, to],
        );
        const t = totalsRes.rows[0]!;

        return {
          from, to,
          income: rowsRes.rows
            .filter((r) => r.type === "income")
            .map((r) => ({ accountId: r.account_id, code: r.code, name: r.name, amount: r.amount })),
          incomeTotal: t.income_total,
          expense: rowsRes.rows
            .filter((r) => r.type === "expense")
            .map((r) => ({ accountId: r.account_id, code: r.code, name: r.name, amount: r.amount })),
          expenseTotal: t.expense_total,
          net: t.net,
        };
      });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "P&L report failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // GET /api/c/reports/balance-sheet?asOf=
  // -------------------------------------------------------------------------
  fastify.get("/api/c/reports/balance-sheet", async (request, reply) => {
    const companyId = request.companyId!;
    const parsedQuery = asOfQuerySchema.safeParse(request.query);
    if (!parsedQuery.success) {
      return reply.code(400).send({ error: zodMessage(parsedQuery.error.issues) });
    }
    const asOf = parsedQuery.data.asOf ?? null;

    try {
      return await db.withCompany(companyId, async (client) => {
        const rowsRes = await client.query<{
          account_id: string; code: string; name: string; type: string; balance: string;
        }>(
          `SELECT account_id, code, name, type, balance FROM trial_balance($1, $2)
            WHERE type IN ('asset', 'liability', 'equity') AND balance <> 0`,
          [companyId, asOf],
        );

        // Every total below is one more FILTERed sum over the SAME
        // trial_balance() call, so each is a single flipSign() away from its
        // presented value — no JS-side addition of money strings anywhere.
        // The accounting identity (assets = liabilities + equity, always,
        // because every posted entry balances) falls out for free: raw
        // assets_total + liabilities_total + equity_and_profit_total = 0,
        // so flipSign(liab_equity_profit_total) always equals assets_total.
        const totalsRes = await client.query<{
          assets_total: string;
          liabilities_total: string;
          income_expense_total: string;
          equity_and_profit_total: string;
          liab_equity_profit_total: string;
        }>(
          `SELECT
             coalesce(sum(balance) FILTER (WHERE type = 'asset'), 0)::numeric(19,4)
               AS assets_total,
             coalesce(sum(balance) FILTER (WHERE type = 'liability'), 0)::numeric(19,4)
               AS liabilities_total,
             coalesce(sum(balance) FILTER (WHERE type IN ('income', 'expense')), 0)::numeric(19,4)
               AS income_expense_total,
             coalesce(sum(balance) FILTER (WHERE type IN ('equity', 'income', 'expense')), 0)::numeric(19,4)
               AS equity_and_profit_total,
             coalesce(sum(balance) FILTER (WHERE type IN ('liability', 'equity', 'income', 'expense')), 0)
               ::numeric(19,4) AS liab_equity_profit_total
           FROM trial_balance($1, $2)`,
          [companyId, asOf],
        );
        const t = totalsRes.rows[0]!;

        const assets = rowsRes.rows
          .filter((r) => r.type === "asset")
          .map((r) => ({ accountId: r.account_id, code: r.code, name: r.name, balance: r.balance }));
        const liabilities = rowsRes.rows
          .filter((r) => r.type === "liability")
          .map((r) => ({ accountId: r.account_id, code: r.code, name: r.name, balance: flipSign(r.balance) }));
        const equityAccounts = rowsRes.rows
          .filter((r) => r.type === "equity")
          .map((r) => ({ accountId: r.account_id, code: r.code, name: r.name, balance: flipSign(r.balance) }));

        // Do NOT invent accounting: this is not a plug or a balancing entry,
        // it's the running income-minus-expense total through asOf, which
        // this system already tracks (there is no year-end close that zeros
        // income/expense into retained earnings) — trial_balance(asOf) IS
        // that running total for those two account types.
        const accumulatedProfit = flipSign(t.income_expense_total);
        const equity = [
          ...equityAccounts,
          { accountId: null, code: null, name: "Accumulated profit", balance: accumulatedProfit },
        ];

        return {
          asOf,
          assets,
          assetsTotal: t.assets_total,
          liabilities,
          liabilitiesTotal: flipSign(t.liabilities_total),
          equity,
          equityTotal: flipSign(t.equity_and_profit_total),
          totalLiabilitiesAndEquity: flipSign(t.liab_equity_profit_total),
        };
      });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "balance sheet report failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // GET /api/c/reports/inventory-valuation?asOf=
  // -------------------------------------------------------------------------
  //
  // Two independent derivations of the same number, side by side, with their
  // difference in between — the milestone's headline invariant, shown on the
  // face of the report rather than asserted only in a test suite (spec: "the
  // tie shown on the report itself"). PER ACCOUNT, not one total, because a
  // single aggregate can be fooled by an error that moves value BETWEEN two
  // inventory accounts: 1300 short by 20 and 1310 over by 20 sum to a serene
  // zero while both are individually wrong (0018 decision 7).
  //
  // Nothing here is stored: inventory_valuation() sums inventory_movements and
  // check_inventory_ties() compares that against journal_lines, both at query
  // time (CLAUDE.md §3.4).
  fastify.get("/api/c/reports/inventory-valuation", async (request, reply) => {
    const companyId = request.companyId!;
    const parsedQuery = asOfQuerySchema.safeParse(request.query);
    if (!parsedQuery.success) {
      return reply.code(400).send({ error: zodMessage(parsedQuery.error.issues) });
    }
    const asOf = parsedQuery.data.asOf ?? null;

    try {
      return await db.withCompany(companyId, async (client) => {
        const itemsRes = await client.query<{
          item_id: string; sku: string | null; name: string;
          qty_on_hand: string; average_cost: string; value: string;
          inventory_account_id: string | null; inventory_account_code: string | null;
          inventory_account_name: string | null;
        }>(
          `SELECT v.item_id, v.sku, v.name, v.qty_on_hand, v.average_cost, v.value,
                  i.inventory_account_id, a.code AS inventory_account_code,
                  a.name AS inventory_account_name
             FROM inventory_valuation($1, $2::date) v
             JOIN items i ON i.company_id = $1 AND i.id = v.item_id
             LEFT JOIN accounts a ON a.company_id = $1 AND a.id = i.inventory_account_id
            ORDER BY a.code NULLS LAST, v.name`,
          [companyId, asOf],
        );

        const tiesRes = await client.query<{
          account_id: string; code: string; name: string;
          ledger_value: string; movement_value: string; difference: string;
        }>(
          "SELECT account_id, code, name, ledger_value, movement_value, difference FROM check_inventory_ties($1, $2::date)",
          [companyId, asOf],
        );

        const totalsRes = await client.query<{ total_value: string; out_of_tie: string }>(
          `SELECT coalesce((SELECT sum(value) FROM inventory_valuation($1, $2::date)), 0)::numeric(19,4)
                    AS total_value,
                  inventory_out_of_tie($1, $2::date) AS out_of_tie`,
          [companyId, asOf],
        );
        const totals = totalsRes.rows[0]!;

        return {
          asOf,
          items: itemsRes.rows.map((r) => ({
            itemId: r.item_id,
            sku: r.sku,
            name: r.name,
            qtyOnHand: r.qty_on_hand,
            averageCost: r.average_cost,
            value: r.value,
            inventoryAccountId: r.inventory_account_id,
            inventoryAccountCode: r.inventory_account_code,
            inventoryAccountName: r.inventory_account_name,
          })),
          accounts: tiesRes.rows.map((r) => ({
            accountId: r.account_id,
            code: r.code,
            name: r.name,
            // "Inventory account: $41,207.18 · Items on hand: $41,207.18 ·
            // Difference: $0.00" — the two derivations and the gap between them.
            ledgerValue: r.ledger_value,
            movementValue: r.movement_value,
            difference: r.difference,
          })),
          totalValue: totals.total_value,
          // 0.0000 exactly when every inventory account ties. Sums ABSOLUTE
          // differences, so two accounts wrong in opposite directions can never
          // cancel into a clean bill of health.
          outOfTie: totals.out_of_tie,
          ties: totals.out_of_tie === "0.0000",
        };
      });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "inventory valuation report failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // GET /api/c/reports/item-profitability?from=&to=
  // -------------------------------------------------------------------------
  //
  // Units sold, revenue, cost of sales and margin per item over a date range —
  // every figure derived at query time by item_profitability()
  // (db/migrations/0023_inventory_selling_builds.sql), never stored. Cost comes
  // from the SALE MOVEMENTS the invoice lines actually produced, so it is the
  // number the ledger was posted rather than a re-multiplication of an average.
  fastify.get("/api/c/reports/item-profitability", async (request, reply) => {
    const companyId = request.companyId!;
    const parsedQuery = dateRangeQuerySchema.safeParse(request.query);
    if (!parsedQuery.success) {
      return reply.code(400).send({ error: zodMessage(parsedQuery.error.issues) });
    }
    const from = parsedQuery.data.from ?? null;
    const to = parsedQuery.data.to ?? null;

    try {
      return await db.withCompany(companyId, async (client) => {
        const rowsRes = await client.query<{
          item_id: string; sku: string | null; name: string; kind: string;
          units_sold: string; revenue: string; cogs: string; margin: string;
        }>(
          "SELECT * FROM item_profitability($1, $2::date, $3::date)",
          [companyId, from, to],
        );

        // Totalled in SQL, in NUMERIC — never by adding money strings in JS.
        const totalsRes = await client.query<{
          revenue: string; cogs: string; margin: string;
        }>(
          `SELECT coalesce(sum(revenue), 0)::numeric(19,4) AS revenue,
                  coalesce(sum(cogs), 0)::numeric(19,4) AS cogs,
                  coalesce(sum(margin), 0)::numeric(19,4) AS margin
             FROM item_profitability($1, $2::date, $3::date)`,
          [companyId, from, to],
        );
        const totals = totalsRes.rows[0]!;

        return {
          from,
          to,
          items: rowsRes.rows.map((r) => ({
            itemId: r.item_id,
            sku: r.sku,
            name: r.name,
            kind: r.kind,
            unitsSold: r.units_sold,
            revenue: r.revenue,
            cogs: r.cogs,
            margin: r.margin,
          })),
          revenueTotal: totals.revenue,
          cogsTotal: totals.cogs,
          marginTotal: totals.margin,
        };
      });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "item profitability report failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // GET /api/c/accounts/:id/entries?from=&to=&limit=&offset=  (drill-through)
  // -------------------------------------------------------------------------
  fastify.get("/api/c/accounts/:id/entries", async (request, reply) => {
    const companyId = request.companyId!;
    const parsedParams = idParamSchema.safeParse(request.params);
    if (!parsedParams.success) {
      return reply.code(400).send({ error: zodMessage(parsedParams.error.issues) });
    }
    const parsedQuery = accountEntriesQuerySchema.safeParse(request.query);
    if (!parsedQuery.success) {
      return reply.code(400).send({ error: zodMessage(parsedQuery.error.issues) });
    }
    const accountId = parsedParams.data.id;
    const from = parsedQuery.data.from ?? null;
    const to = parsedQuery.data.to ?? null;
    const limit = parsedQuery.data.limit ?? 50;
    const offset = parsedQuery.data.offset ?? 0;

    try {
      // Never call reply.send() from inside db.withCompany()'s callback (see
      // the identical note in entries.ts's POST /api/c/entries): Fastify
      // considers the response complete the moment reply.send() runs, before
      // the wrapper's own `await client.query("COMMIT")` (which runs AFTER
      // this callback resolves) has had a chance to execute — a double-send
      // hazard if that COMMIT ever throws. Returning a plain discriminated
      // result and replying only once db.withCompany() has fully resolved
      // (below) removes the hazard even though this route only reads.
      const result = await db.withCompany(companyId, async (client) => {
        const accountRes = await client.query<{ id: string; type: string; name: string }>(
          "SELECT id, type, name FROM accounts WHERE company_id = $1 AND id = $2",
          [companyId, accountId],
        );
        const account = accountRes.rows[0];
        // 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 { found: false as const };
        }
        const debitNormal = account.type === "asset" || account.type === "expense";

        const countRes = await client.query<{ total: string }>(
          `SELECT count(DISTINCT l.entry_id) AS total
             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'
              AND e.entry_date >= coalesce($3::date, '-infinity')
              AND e.entry_date <= coalesce($4::date, 'infinity')`,
          [companyId, accountId, from, to],
        );

        const rowsRes = await client.query<{
          entry_id: string; entry_no: string; entry_date: string;
          memo: string | null; amount_into_account: string; who: string | null;
        }>(
          `SELECT e.id AS entry_id, e.entry_no, e.entry_date::text AS entry_date,
                  coalesce(e.memo, min(l.memo)) AS memo,
                  sum(CASE WHEN $5::boolean THEN l.debit - l.credit ELSE l.credit - l.debit END)
                    ::numeric(19,4) AS amount_into_account,
                  (SELECT min(nullif(btrim(sj.name), '')) FROM staging_journal sj
                    WHERE sj.company_id = e.company_id AND sj.posted_entry_id = e.id) AS who
             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'
              AND e.entry_date >= coalesce($3::date, '-infinity')
              AND e.entry_date <= coalesce($4::date, 'infinity')
            GROUP BY e.id, e.entry_no, e.entry_date, e.memo
            ORDER BY e.entry_date DESC, e.entry_no DESC
            LIMIT $6 OFFSET $7`,
          [companyId, accountId, from, to, debitNormal, limit, offset],
        );

        return {
          found: true as const,
          account: { accountId: account.id, name: account.name, type: account.type },
          total: Number(countRes.rows[0]!.total), // a row count, not money — safe to convert
          entries: rowsRes.rows.map((r) => ({
            entryId: r.entry_id, entryNo: r.entry_no, date: r.entry_date,
            who: r.who, memo: r.memo, amountIntoAccount: r.amount_into_account,
          })),
        };
      });

      if (!result.found) {
        return reply.code(404).send(NOT_FOUND_ACCOUNT);
      }
      return reply.send({
        account: result.account, total: result.total, entries: result.entries,
      });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "account drill-through failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // GET /api/c/entries/:id  (entry detail)
  // -------------------------------------------------------------------------
  fastify.get("/api/c/entries/:id", async (request, reply) => {
    const companyId = request.companyId!;
    const parsedParams = idParamSchema.safeParse(request.params);
    if (!parsedParams.success) {
      return reply.code(400).send({ error: zodMessage(parsedParams.error.issues) });
    }
    const entryId = parsedParams.data.id;

    try {
      // Never call reply.send() from inside db.withCompany()'s callback (see
      // the identical note in entries.ts's POST /api/c/entries): Fastify
      // considers the response complete the moment reply.send() runs, before
      // the wrapper's own `await client.query("COMMIT")` (which runs AFTER
      // this callback resolves) has had a chance to execute — a double-send
      // hazard if that COMMIT ever throws. Returning a plain discriminated
      // result and replying only once db.withCompany() has fully resolved
      // (below) removes the hazard even though this route only reads.
      const result = await db.withCompany(companyId, async (client) => {
        const entryRes = await client.query<{
          id: string; entry_no: string | null; entry_date: string; memo: string | null;
          status: string; source: string | null; external_ref: string | null;
          reverses_entry_id: string | null;
        }>(
          `SELECT id, entry_no, entry_date::text AS entry_date, memo, status, source,
                  external_ref, reverses_entry_id
             FROM journal_entries WHERE company_id = $1 AND id = $2`,
          [companyId, entryId],
        );
        const entry = entryRes.rows[0];
        if (!entry) {
          return { found: false as const };
        }

        const reversedByRes = await client.query<{ id: string }>(
          "SELECT id FROM journal_entries WHERE company_id = $1 AND reverses_entry_id = $2",
          [companyId, entryId],
        );

        const batchRes = await client.query<{ filename: string }>(
          `SELECT ib.filename FROM staging_journal sj
             JOIN import_batches ib ON ib.company_id = sj.company_id AND ib.id = sj.batch_id
            WHERE sj.company_id = $1 AND sj.posted_entry_id = $2
            LIMIT 1`,
          [companyId, entryId],
        );

        const linesRes = await client.query<{
          account_id: string; code: string; account_name: string; type: string;
          debit: string; credit: string; memo: string | null;
        }>(
          `SELECT l.account_id, a.code, a.name AS account_name, a.type, l.debit, l.credit, l.memo
             FROM journal_lines l
             JOIN accounts a ON a.company_id = l.company_id AND a.id = l.account_id
            WHERE l.company_id = $1 AND l.entry_id = $2
            ORDER BY l.id`,
          [companyId, entryId],
        );

        const DEBIT_NORMAL_TYPES = new Set(["asset", "expense"]);
        const lines = linesRes.rows.map((r) => {
          const debitNormal = DEBIT_NORMAL_TYPES.has(r.type);
          const debitSet = r.debit !== "0.0000"; // exactly one of debit/credit is nonzero, by CHECK constraint
          const side: "in" | "out" = debitNormal === debitSet ? "in" : "out";
          const amount = debitSet ? r.debit : r.credit;
          return {
            accountId: r.account_id, code: r.code, accountName: r.account_name,
            side, amount, debit: r.debit, credit: r.credit, memo: r.memo,
          };
        });

        return {
          found: true as const,
          entryId: entry.id,
          entryNo: entry.entry_no,
          date: entry.entry_date,
          memo: entry.memo,
          status: entry.status,
          source: entry.source,
          externalRef: entry.external_ref,
          importBatch: batchRes.rows[0] ? { filename: batchRes.rows[0].filename } : null,
          reversesEntryId: entry.reverses_entry_id,
          reversedByEntryId: reversedByRes.rows[0]?.id ?? null,
          lines,
        };
      });

      if (!result.found) {
        return reply.code(404).send(NOT_FOUND_ENTRY);
      }
      return reply.send({
        entryId: result.entryId,
        entryNo: result.entryNo,
        date: result.date,
        memo: result.memo,
        status: result.status,
        source: result.source,
        externalRef: result.externalRef,
        importBatch: result.importBatch,
        reversesEntryId: result.reversesEntryId,
        reversedByEntryId: result.reversedByEntryId,
        lines: result.lines,
      });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "entry detail failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });
}
