// Payments + aging + receive-payment picker — Task 3 of
// docs/superpowers/plans/2026-07-31-ar.md. Same route-module pattern as
// customers.ts/invoices.ts: every route lives under /api/c/ (company scope
// from X-Company-Id, enforced by companyContextPlugin) and every query/insert
// runs inside db.withCompany() so RLS is the real guard (CLAUDE.md §4/§12),
// never just the WHERE clauses written here. created_by is always the
// company's owner user (see resolveOwner below) — same pattern as
// entries.ts/invoices.ts: there's no login in v1.
//
// Documents sit ON TOP of the ledger (docs/superpowers/specs/2026-07-31-ar-design.md):
// receive_payment() (a SECURITY DEFINER function from db/migrations/0005_ar.sql,
// hardened in 0006_ar_fixes.sql) posts (cash up / receivable down) and creates
// the payment + any applications atomically; apply_credit() applies an
// existing payment's unapplied remainder to more invoices with no new posting
// (the cash already arrived); void_payment() reverses the posting entry.
// Every derived amount here (applied, unapplied, aging buckets) is computed in
// SQL at query time — CLAUDE.md §3.4, never stored, never cached.
//
// Error-mapping split (CLAUDE.md §9/§12 — errors in English, DB is the real
// guard): receive_payment()/apply_credit()/void_payment()'s own RAISEs for
// customer/invoice not-found, wrong customer, already-void, over-application,
// etc. are already mapped to friendly English in errors.ts — those are
// deliberately NOT re-validated here, so the DB's own guard stays the one
// source of truth. The ONE thing that's pre-checked in this file (mirroring
// entries.ts's resolveEntry / invoices.ts's resolveLines) is the deposit
// account's type: receive_payment()'s own "deposit account must be an asset
// account" / "account % not found" RAISEs have no English mapping in
// errors.ts (unlike the AR-specific messages, which do), so an unmapped one
// would surface as a raw 500 — lookupAssetAccount() below turns that into a
// friendly 422 before the function is ever called, exactly like the existing
// pattern elsewhere in this codebase.

import type { FastifyInstance } from "fastify";
import { z } from "zod";
import type { Db } from "./db.js";
import type { PoolClient } from "pg";
import { mapPgError } from "./errors.js";
import { findOwnerId } from "../../tools/lib/journal-load.js";
import { blankToUndefined } from "./lib/textNormalize.js";

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

const idSchema = z.string().regex(/^\d{1,18}$/, "must be a whole number id (no more than 18 digits)");
const idParamSchema = z.object({ id: idSchema });

const isoDateSchema = z.iso.date({ error: "must be a date like 2024-01-15 (YYYY-MM-DD)" });
const optionalIsoDate = z.preprocess(
  (v) => (v === "" || v === undefined ? undefined : v),
  isoDateSchema.optional(),
);

// blankToUndefined trims BEFORE checking for blankness, so a whitespace-only
// input ("   ") collapses to undefined/NULL rather than surviving as a
// stored empty string (sweep fix — see lib/textNormalize.ts's header for the
// full history: this exact bug was already fixed in bills.ts/customers.ts/
// vendors.ts; it existed here too, unfixed, until this sweep).
const memoSchema = z.preprocess(blankToUndefined, z.string().max(1000).optional());

// Dollars-and-cents input, same shape as invoices.ts's unitPriceSchema — up to
// 13 integer digits (comfortably under numeric(19,4)'s 15-digit integer-part
// limit), 2 decimal places, no leading zeros, deliberately a regex rather than
// Number()/parseFloat() (CLAUDE.md §2: money is never a raw JS number).
const amountSchema = z
  .string()
  .regex(
    /^(0|[1-9]\d{0,12})(\.\d{1,2})?$/,
    "Enter an amount like 120 or 120.50 (no more than 2 decimal places, no leading zeros, no more than 13 digits before the decimal point).",
  )
  .refine((v) => /[1-9]/.test(v), { message: "Enter an amount greater than zero." });

const applicationSchema = z.object({ invoiceId: idSchema, amount: amountSchema });

const paymentCreateSchema = z.object({
  customerId: idSchema,
  paymentDate: isoDateSchema,
  amount: amountSchema,
  depositAccountId: idSchema,
  memo: memoSchema,
  applications: z.array(applicationSchema).default([]),
});

const applyBodySchema = z.object({
  applications: z.array(applicationSchema).min(1, "Add at least one invoice to apply this credit to."),
});

const voidBodySchema = z.object({ date: optionalIsoDate });

const listQuerySchema = z.object({
  customerId: idSchema.optional(),
  limit: z.coerce.number().int().min(1).max(500).optional(),
  offset: z.coerce.number().int().min(0).optional(),
});

const agingQuerySchema = z.object({ asOf: optionalIsoDate });

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

const NOT_FOUND_PAYMENT = { error: "We couldn't find that payment." };
const NOT_FOUND_CUSTOMER = { error: "We couldn't find that customer." };
const NOT_ASSET_ACCOUNT = {
  status: 422,
  error: "Pick a money account for where the payment landed.",
};

const NO_OWNER_ERROR = {
  status: 500,
  error: "This company doesn't have an owner user on file, so payments can't be attributed. This shouldn't happen.",
};

/** Mirrors entries.ts/invoices.ts's resolveOwner — every payment/void/apply is
 *  attributed to the company's owner user (no login in v1). */
async function resolveOwner(
  db: Db, companyId: string, log: { error: (obj: unknown, msg: string) => void },
): Promise<string | null> {
  try {
    return await findOwnerId(db.adminPool, companyId);
  } catch (err) {
    log.error({ err, companyId }, "resolveOwner: findOwnerId failed");
    return null;
  }
}

/** Formats a NUMERIC(19,4) string from Postgres (e.g. "1234.0000") as
 *  "$1,234.00" — pure string surgery, never a parsed JS number (CLAUDE.md §2). */
function formatUsd(amount: string): string {
  const negative = amount.startsWith("-");
  const unsigned = negative ? amount.slice(1) : amount;
  const [wholeRaw, fracRaw = ""] = unsigned.split(".");
  const whole = wholeRaw!.replace(/\B(?=(\d{3})+(?!\d))/g, ",");
  const frac = (fracRaw + "00").slice(0, 2);
  return `${negative ? "-" : ""}$${whole}.${frac}`;
}

/** "A" | "A and B" | "A, B, and C" — the plain-English list join used by both
 *  summary builders below. */
function joinEnglishList(parts: string[]): string {
  if (parts.length <= 1) return parts[0] ?? "";
  if (parts.length === 2) return `${parts[0]} and ${parts[1]}`;
  return `${parts.slice(0, -1).join(", ")}, and ${parts[parts.length - 1]}`;
}

interface AppliedInvoice { invoiceNo: string; amount: string }

/**
 * Builds the receive-payment summary sentence (CLAUDE.md §9 — plain English,
 * never "debit"/"credit"). Three shapes:
 *  - fully applied: "Received $X from Name — applied to invoice(s) #.. ($..)."
 *  - partially applied: same, plus ". $R kept as customer credit."
 *  - fully unapplied (no applications at all): "Received $X from Name —
 *    kept as customer credit."
 */
function buildReceiveSummary(
  amount: string, customerName: string, applications: AppliedInvoice[], remainder: string,
): string {
  const amountStr = formatUsd(amount);
  if (applications.length === 0) {
    return `Received ${amountStr} from ${customerName} — kept as customer credit.`;
  }
  const label = applications.length === 1 ? "invoice" : "invoices";
  const parts = applications.map((a) => `#${a.invoiceNo} (${formatUsd(a.amount)})`);
  let sentence = `Received ${amountStr} from ${customerName} — applied to ${label} ${joinEnglishList(parts)}.`;
  if (remainder !== "0.0000") {
    sentence += ` ${formatUsd(remainder)} kept as customer credit.`;
  }
  return sentence;
}

/** Builds the apply-credit summary sentence: "Applied credit to invoice(s)
 *  #.. ($..)." plus, if any credit remains unapplied, a trailing note. */
function buildApplySummary(applications: AppliedInvoice[], remainder: string): string {
  const label = applications.length === 1 ? "invoice" : "invoices";
  const parts = applications.map((a) => `#${a.invoiceNo} (${formatUsd(a.amount)})`);
  let sentence = `Applied credit to ${label} ${joinEnglishList(parts)}.`;
  if (remainder !== "0.0000") {
    sentence += ` ${formatUsd(remainder)} credit still available.`;
  }
  return sentence;
}

interface AssetAccountRow { id: string; name: string }

/** RLS-scoped lookup of one active ASSET account by id, within the caller's
 *  transaction — mirrors entries.ts's lookupAccount, but fixed to type
 *  'asset' since that's the only kind a deposit account may be. See the file
 *  header for why this is pre-checked here rather than left to
 *  receive_payment()'s own (unmapped) RAISE. */
async function lookupAssetAccount(
  client: PoolClient, companyId: string, accountId: string,
): Promise<AssetAccountRow | null> {
  const res = await client.query<AssetAccountRow>(
    "SELECT id, name FROM accounts WHERE company_id = $1 AND id = $2 AND type = 'asset' AND active = true",
    [companyId, accountId],
  );
  return res.rows[0] ?? null;
}

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

export function registerPaymentRoutes(fastify: FastifyInstance, db: Db): void {
  // -------------------------------------------------------------------------
  // POST /api/c/payments
  // -------------------------------------------------------------------------
  fastify.post("/api/c/payments", async (request, reply) => {
    const companyId = request.companyId!;
    const parsed = paymentCreateSchema.safeParse(request.body);
    if (!parsed.success) {
      return reply.code(400).send({ error: zodMessage(parsed.error.issues) });
    }
    const body = parsed.data;

    const ownerId = await resolveOwner(db, companyId, request.log);
    if (ownerId === null) {
      return reply.code(NO_OWNER_ERROR.status).send({ error: NO_OWNER_ERROR.error });
    }

    try {
      // Never call reply.send() from inside db.withCompany()'s callback (see
      // the identical note in entries.ts's POST /api/c/entries) — a plain
      // discriminated result is returned and replied to only once the
      // transaction has fully committed.
      const result = await db.withCompany(companyId, async (client) => {
        const depositAccount = await lookupAssetAccount(client, companyId, body.depositAccountId);
        if (!depositAccount) return { ok: false as const, ...NOT_ASSET_ACCOUNT };

        const spec = {
          customer_id: body.customerId,
          payment_date: body.paymentDate,
          amount: body.amount,
          deposit_account_id: body.depositAccountId,
          memo: body.memo ?? null,
          applications: body.applications.map((a) => ({ invoice_id: a.invoiceId, amount: a.amount })),
        };

        // Raises (customer/invoice not found, invoice not open, wrong
        // customer) flow through to mapPgError below — see the file header
        // for why those are deliberately left to the DB's own English.
        const payRes = await client.query<{ receive_payment: string }>(
          "SELECT receive_payment($1, $2::jsonb, $3) AS receive_payment",
          [companyId, JSON.stringify(spec), ownerId],
        );
        const paymentId = payRes.rows[0]!.receive_payment;

        const customerRes = await client.query<{ name: string }>(
          "SELECT name FROM customers WHERE company_id = $1 AND id = $2",
          [companyId, body.customerId],
        );
        const customerName = customerRes.rows[0]?.name ?? "this customer";

        const appsRes = await client.query<{ invoice_no: string; amount: string }>(
          `SELECT i.invoice_no, pa.amount
             FROM payment_applications pa
             JOIN invoices i ON i.company_id = pa.company_id AND i.id = pa.invoice_id
            WHERE pa.company_id = $1 AND pa.payment_id = $2
            ORDER BY pa.id`,
          [companyId, paymentId],
        );

        const remainderRes = await client.query<{ remainder: string }>(
          "SELECT payment_unapplied_balance($1, $2) AS remainder",
          [companyId, paymentId],
        );
        const remainder = remainderRes.rows[0]!.remainder;

        const summary = buildReceiveSummary(
          body.amount, customerName,
          appsRes.rows.map((r) => ({ invoiceNo: r.invoice_no, amount: r.amount })),
          remainder,
        );

        return { ok: true as const, paymentId, summary };
      });

      if (!result.ok) {
        return reply.code(result.status).send({ error: result.error });
      }
      return reply.code(201).send({ paymentId: result.paymentId, summary: result.summary });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "receive payment failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // GET /api/c/payments?customerId=&limit=&offset=
  // -------------------------------------------------------------------------
  fastify.get("/api/c/payments", async (request, reply) => {
    const companyId = request.companyId!;
    const parsedQuery = listQuerySchema.safeParse(request.query);
    if (!parsedQuery.success) {
      return reply.code(400).send({ error: zodMessage(parsedQuery.error.issues) });
    }
    const { customerId, limit, offset } = parsedQuery.data;

    try {
      return await db.withCompany(companyId, async (client) => {
        // "applied" here is the historical sum of this payment's own
        // application rows (they're immutable — void_payment() never deletes
        // them, it just makes them stop counting toward any INVOICE's open
        // balance, via payments.status filters elsewhere). "unapplied" is
        // simply amount - applied, so the two always add up to the payment's
        // full amount regardless of its own status.
        const rowsRes = await client.query(
          `WITH totals AS (
             SELECT p.id, p.customer_id, c.name AS customer_name,
                    p.payment_date::text AS payment_date, p.amount, p.status,
                    p.posted_entry_id, p.void_entry_id,
                    coalesce((SELECT sum(amount) FROM payment_applications pa
                                WHERE pa.company_id = p.company_id AND pa.payment_id = p.id), 0)
                      ::numeric(19,4) AS applied
               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 ($2::bigint IS NULL OR p.customer_id = $2)
           )
           SELECT id, customer_id, customer_name, payment_date, amount, status,
                  posted_entry_id, void_entry_id, applied,
                  (amount - applied)::numeric(19,4) AS unapplied,
                  count(*) OVER() AS full_count
             FROM totals
            ORDER BY payment_date DESC, id DESC
            LIMIT $3 OFFSET $4`,
          [companyId, customerId ?? null, limit ?? 50, offset ?? 0],
        );

        const rows = rowsRes.rows as Array<{
          id: string; customer_id: string; customer_name: string; payment_date: string;
          amount: string; status: string; posted_entry_id: string; void_entry_id: string | null;
          applied: string; unapplied: string; full_count: string;
        }>;

        return {
          total: rows[0] ? Number(rows[0].full_count) : 0,
          payments: rows.map((r) => ({
            paymentId: r.id,
            date: r.payment_date,
            customerId: r.customer_id,
            customerName: r.customer_name,
            amount: r.amount,
            applied: r.applied,
            unapplied: r.unapplied,
            status: r.status,
            postedEntryId: r.posted_entry_id,
            voidEntryId: r.void_entry_id,
          })),
        };
      });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "payments list failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // GET /api/c/payments/:id  (detail)
  // -------------------------------------------------------------------------
  fastify.get("/api/c/payments/: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 paymentId = parsedParams.data.id;

    try {
      const result = await db.withCompany(companyId, async (client) => {
        const headerRes = await client.query<{
          id: string; customer_id: string; customer_name: string; customer_email: string | null;
          payment_date: string; amount: string; status: string; memo: string | null;
          deposit_account_id: string; deposit_account_name: string;
          posted_entry_id: string; void_entry_id: string | null;
        }>(
          `SELECT p.id, p.customer_id, c.name AS customer_name, c.email AS customer_email,
                  p.payment_date::text AS payment_date, p.amount, p.status, p.memo,
                  p.deposit_account_id, da.name AS deposit_account_name,
                  p.posted_entry_id, p.void_entry_id
             FROM payments p
             JOIN customers c ON c.company_id = p.company_id AND c.id = p.customer_id
             JOIN accounts da ON da.company_id = p.company_id AND da.id = p.deposit_account_id
            WHERE p.company_id = $1 AND p.id = $2`,
          [companyId, paymentId],
        );
        const header = headerRes.rows[0];
        if (!header) return { found: false as const };

        const appsRes = await client.query<{
          id: string; invoice_id: string; invoice_no: string; invoice_date: string;
          due_date: string; amount: string;
        }>(
          `SELECT pa.id, pa.invoice_id, i.invoice_no, i.invoice_date::text AS invoice_date,
                  i.due_date::text AS due_date, pa.amount
             FROM payment_applications pa
             JOIN invoices i ON i.company_id = pa.company_id AND i.id = pa.invoice_id
            WHERE pa.company_id = $1 AND pa.payment_id = $2
            ORDER BY pa.id`,
          [companyId, paymentId],
        );

        const totalsRes = await client.query<{ applied: string; unapplied: string }>(
          `SELECT coalesce((SELECT sum(amount) FROM payment_applications
                              WHERE company_id = $1 AND payment_id = $2), 0)::numeric(19,4) AS applied,
                  (p.amount - coalesce((SELECT sum(amount) FROM payment_applications
                                          WHERE company_id = $1 AND payment_id = $2), 0))
                    ::numeric(19,4) AS unapplied
             FROM payments p WHERE p.company_id = $1 AND p.id = $2`,
          [companyId, paymentId],
        );
        const totals = totalsRes.rows[0]!;

        return {
          found: true as const,
          paymentId: header.id,
          date: header.payment_date,
          customerId: header.customer_id,
          customerName: header.customer_name,
          customerEmail: header.customer_email,
          amount: header.amount,
          applied: totals.applied,
          unapplied: totals.unapplied,
          status: header.status,
          memo: header.memo,
          depositAccountId: header.deposit_account_id,
          depositAccountName: header.deposit_account_name,
          postedEntryId: header.posted_entry_id,
          voidEntryId: header.void_entry_id,
          applications: appsRes.rows.map((a) => ({
            id: a.id,
            invoiceId: a.invoice_id,
            invoiceNo: a.invoice_no,
            invoiceDate: a.invoice_date,
            dueDate: a.due_date,
            amount: a.amount,
          })),
        };
      });

      if (!result.found) {
        return reply.code(404).send(NOT_FOUND_PAYMENT);
      }
      const { found: _found, ...body } = result;
      return reply.send(body);
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "payment detail failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // POST /api/c/payments/:id/void
  // -------------------------------------------------------------------------
  fastify.post("/api/c/payments/:id/void", 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 parsedBody = voidBodySchema.safeParse(request.body ?? {});
    if (!parsedBody.success) {
      return reply.code(400).send({ error: zodMessage(parsedBody.error.issues) });
    }
    const paymentId = parsedParams.data.id;
    const voidDate = parsedBody.data.date ?? null;

    const ownerId = await resolveOwner(db, companyId, request.log);
    if (ownerId === null) {
      return reply.code(NO_OWNER_ERROR.status).send({ error: NO_OWNER_ERROR.error });
    }

    try {
      const result = await db.withCompany(companyId, async (client) => {
        const existingRes = await client.query<{ id: string }>(
          "SELECT id FROM payments WHERE company_id = $1 AND id = $2",
          [companyId, paymentId],
        );
        if (!existingRes.rows[0]) return { found: false as const };

        const appCountRes = await client.query<{ count: string }>(
          "SELECT count(*) AS count FROM payment_applications WHERE company_id = $1 AND payment_id = $2",
          [companyId, paymentId],
        );
        const hadApplications = Number(appCountRes.rows[0]!.count) > 0;

        await client.query(
          "SELECT void_payment($1, $2, coalesce($3::date, current_date), $4)",
          [companyId, paymentId, voidDate, ownerId],
        );

        return { found: true as const, hadApplications };
      });

      if (!result.found) {
        return reply.code(404).send(NOT_FOUND_PAYMENT);
      }
      const summary = result.hadApplications
        ? "Payment voided — the invoices it covered are open again."
        : "Payment voided — the customer credit it created no longer exists.";
      return reply.send({ paymentId, summary });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "void payment failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // POST /api/c/payments/:id/apply
  // -------------------------------------------------------------------------
  fastify.post("/api/c/payments/:id/apply", 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 parsedBody = applyBodySchema.safeParse(request.body);
    if (!parsedBody.success) {
      return reply.code(400).send({ error: zodMessage(parsedBody.error.issues) });
    }
    const paymentId = parsedParams.data.id;
    const body = parsedBody.data;

    const ownerId = await resolveOwner(db, companyId, request.log);
    if (ownerId === null) {
      return reply.code(NO_OWNER_ERROR.status).send({ error: NO_OWNER_ERROR.error });
    }

    try {
      const result = await db.withCompany(companyId, async (client) => {
        const existingRes = await client.query<{ id: string }>(
          "SELECT id FROM payments WHERE company_id = $1 AND id = $2",
          [companyId, paymentId],
        );
        if (!existingRes.rows[0]) return { found: false as const };

        const apps = body.applications.map((a) => ({ invoice_id: a.invoiceId, amount: a.amount }));

        // Raises (payment void, invoice not found/not open/wrong customer,
        // over-application) flow through to mapPgError below — see the file
        // header for why those are deliberately left to the DB's own English.
        await client.query(
          "SELECT apply_credit($1, $2, $3::jsonb, $4)",
          [companyId, paymentId, JSON.stringify(apps), ownerId],
        );

        const invoiceIds = body.applications.map((a) => a.invoiceId);
        const invNoRes = await client.query<{ id: string; invoice_no: string }>(
          "SELECT id, invoice_no FROM invoices WHERE company_id = $1 AND id = ANY($2::bigint[])",
          [companyId, invoiceIds],
        );
        const noById = new Map(invNoRes.rows.map((r) => [r.id, r.invoice_no]));

        const remainderRes = await client.query<{ remainder: string }>(
          "SELECT payment_unapplied_balance($1, $2) AS remainder",
          [companyId, paymentId],
        );
        const remainder = remainderRes.rows[0]!.remainder;

        const applied = body.applications.map((a) => ({
          invoiceNo: noById.get(a.invoiceId) ?? a.invoiceId,
          amount: a.amount,
        }));

        return { found: true as const, summary: buildApplySummary(applied, remainder) };
      });

      if (!result.found) {
        return reply.code(404).send(NOT_FOUND_PAYMENT);
      }
      return reply.send({ paymentId, summary: result.summary });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "apply credit failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // GET /api/c/customers/:id/open-invoices  (receive-payment picker)
  // -------------------------------------------------------------------------
  fastify.get("/api/c/customers/:id/open-invoices", 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 customerId = parsedParams.data.id;

    try {
      const result = await db.withCompany(companyId, async (client) => {
        const customerRes = await client.query<{ id: string }>(
          "SELECT id FROM customers WHERE company_id = $1 AND id = $2",
          [companyId, customerId],
        );
        if (!customerRes.rows[0]) return { found: false as const };

        // open>0, non-void, issued (status='open' already excludes draft and
        // void) — includes document_only invoices, same as ar_aging().
        const invRes = await client.query<{
          id: string; invoice_no: string; invoice_date: string; due_date: string;
          total: string; open: string; overdue: boolean;
        }>(
          // total is the GRAND total (lines + tax_amount, 0 on every pre-tax
          // document) — the carry-over fix from the sales tax Task 1 review:
          // the picker's total must match invoice_open_balance()'s
          // tax-inclusive notion of what's owed.
          `SELECT i.id, i.invoice_no, i.invoice_date::text AS invoice_date,
                  i.due_date::text AS due_date,
                  (coalesce(sum(il.amount), 0) + i.tax_amount)::numeric(19,4) AS total,
                  invoice_open_balance($1, i.id) AS open,
                  (i.due_date < current_date) AS overdue
             FROM invoices i
             LEFT JOIN invoice_lines il ON il.company_id = i.company_id AND il.invoice_id = i.id
            WHERE i.company_id = $1 AND i.customer_id = $2 AND i.status = 'open'
            GROUP BY i.id, i.invoice_no, i.invoice_date, i.due_date, i.tax_amount
           HAVING invoice_open_balance($1, i.id) > 0
            ORDER BY i.due_date, i.invoice_no`,
          [companyId, customerId],
        );

        const creditRes = await client.query<{ credit: string }>(
          `SELECT coalesce(sum(payment_unapplied_balance($1, p.id)), 0)::numeric(19,4) AS credit
             FROM payments p
            WHERE p.company_id = $1 AND p.customer_id = $2 AND p.status = 'active'`,
          [companyId, customerId],
        );

        return {
          found: true as const,
          invoices: invRes.rows.map((r) => ({
            invoiceId: r.id,
            invoiceNo: r.invoice_no,
            invoiceDate: r.invoice_date,
            dueDate: r.due_date,
            total: r.total,
            open: r.open,
            overdue: r.overdue,
          })),
          unappliedCredit: creditRes.rows[0]!.credit,
        };
      });

      if (!result.found) {
        return reply.code(404).send(NOT_FOUND_CUSTOMER);
      }
      return reply.send({ invoices: result.invoices, unappliedCredit: result.unappliedCredit });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "open invoices lookup failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // GET /api/c/reports/aging?asOf=  (default: today, per the database's clock)
  // -------------------------------------------------------------------------
  fastify.get("/api/c/reports/aging", async (request, reply) => {
    const companyId = request.companyId!;
    const parsedQuery = agingQuerySchema.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) => {
        // ar_aging() (db/migrations/0005_ar.sql) already does the actual
        // bucketing (by due_date, including document_only invoices) and the
        // per-customer unapplied-credit rows; this just re-shapes its rows
        // (row_type/bucket) into the design spec's {buckets, credits, total}
        // shape, entirely in SQL (CLAUDE.md §2 — no money math in JS).
        const perCustomerRes = await client.query<{
          customer_id: string; customer_name: string;
          current: string; d1_30: string; d31_60: string; d61_90: string; d90_plus: string;
          credits: string; total: string;
        }>(
          `WITH aging AS (SELECT * FROM ar_aging($1, coalesce($2::date, current_date)))
           SELECT customer_id, customer_name,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'invoice' AND bucket = 'current'), 0)
                    ::numeric(19,4) AS current,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'invoice' AND bucket = '1-30'), 0)
                    ::numeric(19,4) AS d1_30,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'invoice' AND bucket = '31-60'), 0)
                    ::numeric(19,4) AS d31_60,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'invoice' AND bucket = '61-90'), 0)
                    ::numeric(19,4) AS d61_90,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'invoice' AND bucket = '90+'), 0)
                    ::numeric(19,4) AS d90_plus,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'credit'), 0)::numeric(19,4) AS credits,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'invoice'), 0)::numeric(19,4) AS total
             FROM aging
            GROUP BY customer_id, customer_name
            ORDER BY customer_name`,
          [companyId, asOf],
        );

        const totalsRes = await client.query<{
          as_of: string;
          current: string; d1_30: string; d31_60: string; d61_90: string; d90_plus: string;
          credits: string; grand: string;
        }>(
          `WITH aging AS (SELECT * FROM ar_aging($1, coalesce($2::date, current_date)))
           SELECT coalesce($2::date, current_date)::text AS as_of,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'invoice' AND bucket = 'current'), 0)
                    ::numeric(19,4) AS current,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'invoice' AND bucket = '1-30'), 0)
                    ::numeric(19,4) AS d1_30,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'invoice' AND bucket = '31-60'), 0)
                    ::numeric(19,4) AS d31_60,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'invoice' AND bucket = '61-90'), 0)
                    ::numeric(19,4) AS d61_90,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'invoice' AND bucket = '90+'), 0)
                    ::numeric(19,4) AS d90_plus,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'credit'), 0)::numeric(19,4) AS credits,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'invoice'), 0)::numeric(19,4) AS grand
             FROM aging`,
          [companyId, asOf],
        );
        const t = totalsRes.rows[0]!;

        return {
          asOf: t.as_of,
          customers: perCustomerRes.rows.map((r) => ({
            customerId: r.customer_id,
            name: r.customer_name,
            buckets: {
              current: r.current, d1_30: r.d1_30, d31_60: r.d31_60, d61_90: r.d61_90, d90_plus: r.d90_plus,
            },
            credits: r.credits,
            total: r.total,
          })),
          totals: {
            current: t.current, d1_30: t.d1_30, d31_60: t.d31_60, d61_90: t.d61_90, d90_plus: t.d90_plus,
            credits: t.credits, grand: t.grand,
          },
        };
      });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "aging report failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });
}
