// Bill payments + AP aging + vendor open-bills picker — Task 3 of
// docs/superpowers/plans/2026-08-01-ap.md. Deliberate mirror of
// server/src/payments.ts (AR receive-payment/apply-credit/aging), adapted for
// the AP vocabulary and the one genuinely new piece: payment METHOD (check
// vs. electronic), which the AR side has no analogue for. 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/p_user is always the company's owner user (see
// resolveOwner below) — same pattern as every other route module: there's no
// login in v1.
//
// Documents sit ON TOP of the ledger (docs/superpowers/specs/2026-08-01-ap-design.md):
// pay_bills() (a SECURITY DEFINER function from db/migrations/0009_ap.sql)
// posts (A/P down / money down) and creates the payment + any applications
// atomically; apply_vendor_credit() applies an existing payment's unapplied
// remainder to more bills with no new posting (the cash already went out);
// void_bill_payment() reverses the posting entry (and, for a check payment,
// flips check_status to 'void' — the number is consumed, never reused).
// 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): pay_bills()/apply_vendor_credit()/void_bill_payment()'s own RAISEs
// (vendor/bill not found, wrong vendor, 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 pre-checked in this file (mirroring
// payments.ts's lookupAssetAccount) is the money account's type: pay_bills()'s
// own "money account must be an asset account" / "account % not found" RAISEs
// have no English mapping in errors.ts, so an unmapped one would surface as a
// raw 500 — lookupAssetAccount() below turns that into a friendly 422 before
// the function is ever called.
//
// Method validation (the one piece with no AR analogue) IS done here, in the
// zod schema below, rather than left entirely to pay_bills()'s own checks:
// the DB function only distinguishes "check_no supplied" (-> manual) from
// "not supplied" (-> to_print), and separately rejects a check_no on an
// electronic payment — but it has no opinion on "toPrint" as a concept (that
// flag exists only in this API, not in the database), so the exactly-one-of
// (checkNo, toPrint) rule for a check payment has to be enforced here.

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(),
);

const memoSchema = z.preprocess(blankToUndefined, z.string().max(1000).optional());

// Dollars-and-cents input, same shape as payments.ts's amountSchema.
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." });

// A hand-entered check number: bill_payments.check_no is a bigint, capped
// at 18 digits for the same "friendly 400, not a raw bigint overflow" reason
// as every other id in this codebase.
// Floor of 1 — a check number of 0 isn't a real check, same as every other
// counter/id schema in this codebase that draws the line at 1 (settings.ts's
// nextInvoiceNoSchema, checks.ts's settingsPutSchema's nextCheckNo). The
// regex alone (\d{1,18}) would accept "0"; the .refine below is what
// actually excludes it.
const checkNoSchema = z.preprocess(
  blankToUndefined,
  z.string()
    .regex(/^\d{1,18}$/, "Enter a whole check number.")
    .refine((v) => /[1-9]/.test(v), { message: "Enter a check number of 1 or more." })
    .optional(),
);

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

const paymentCreateSchema = z
  .object({
    vendorId: idSchema,
    paymentDate: isoDateSchema,
    amount: amountSchema,
    moneyAccountId: idSchema,
    method: z.enum(["check", "electronic"], {
      error: "Payment method must be 'check' or 'electronic'.",
    }),
    checkNo: checkNoSchema,
    toPrint: z.boolean().optional(),
    memo: memoSchema,
    applications: z.array(applicationSchema).default([]),
  })
  .superRefine((body, ctx) => {
    if (body.method === "check") {
      const hasCheckNo = body.checkNo !== undefined;
      const hasToPrint = body.toPrint === true;
      if (hasCheckNo && hasToPrint) {
        ctx.addIssue({
          code: "custom",
          message: "Choose either a check number already written or 'queue to print' — not both.",
        });
      } else if (!hasCheckNo && !hasToPrint) {
        ctx.addIssue({
          code: "custom",
          message: "For a check payment, enter the check number already written or choose to queue it for printing.",
        });
      }
    } else if (body.checkNo !== undefined || body.toPrint !== undefined) {
      ctx.addIssue({
        code: "custom",
        message: "An electronic payment can't have a check number or be queued to print.",
      });
    }
  });

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

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

const listQuerySchema = z.object({
  vendorId: 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_VENDOR = { error: "We couldn't find that vendor." };
const NOT_ASSET_ACCOUNT = {
  status: 422,
  error: "Pick a money account for where the payment came from.",
};

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.",
};

interface PgErrorLike { code?: string; constraint?: string }

/**
 * Duplicate manual check number on the same money account: a friendly 409
 * naming the ACTUAL number the caller submitted (when known) — mirrors
 * bills.ts's mapDuplicateRefNo exactly (same rationale: Postgres suppresses
 * the DETAIL field for a non-owner, RLS-subject role like `app`, even though
 * the row is this company's own and fully visible to it under RLS, so
 * recovering the number from e.detail can't be relied on; this uses the
 * value the caller already gave us instead). errors.ts's own mapping for
 * bill_payments_check_no_unique is kept as a generic (no number) fallback for
 * any caller that hits this constraint without a submitted number on hand —
 * exactly the case in checks.ts's print-run race path below, where the
 * colliding number was assigned automatically rather than typed by hand, so
 * there's nothing specific to name (CLAUDE.md §12 — the DB's constraint is
 * still the one source of truth; this is just a friendlier restatement of it
 * when we happen to know the number). */
export function mapDuplicateCheckNo(err: unknown, checkNo: string | null): { status: number; error: string } | null {
  const e = (err ?? {}) as PgErrorLike;
  if (e.code === "23505" && e.constraint === "bill_payments_check_no_unique") {
    return {
      status: 409,
      error: checkNo
        ? `Check #${checkNo} has already been used on this account — pick another number or update the next check number in Settings.`
        : "That check number has already been used on this account — pick another number or update the next check number in Settings.",
    };
  }
  return null;
}

/** Mirrors payments.ts/bills.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, same as
 *  payments.ts's joinEnglishList. */
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]}`;
}

/**
 * Builds the pay-bills summary sentence (CLAUDE.md §9 — plain English, never
 * "debit"/"credit"), incl. the payment method — the one thing with no AR
 * analogue. Three method shapes, all built on the same "Paid $X to Vendor"
 * prefix (per the plan's own examples):
 *  - manual check:  "...by check #1024 from Chase Business Checking."
 *  - queued check:  "...by a check queued to print."
 *  - electronic:    "...electronically."
 * plus, if anything is left unapplied, a trailing " $R kept as vendor
 * credit." note (mirrors payments.ts's buildReceiveSummary's remainder note).
 */
function buildPaySummary(
  amount: string, vendorName: string, method: "check" | "electronic",
  checkNo: string | null, moneyAccountName: string, remainder: string,
): string {
  let sentence = `Paid ${formatUsd(amount)} to ${vendorName}`;
  if (method === "check") {
    sentence += checkNo !== null
      ? ` by check #${checkNo} from ${moneyAccountName}`
      : " by a check queued to print";
  } else {
    sentence += " electronically";
  }
  sentence += ".";
  if (remainder !== "0.0000") {
    sentence += ` ${formatUsd(remainder)} kept as vendor credit.`;
  }
  return sentence;
}

interface AppliedBill { refNo: string | null; billId: string; amount: string }

/** Builds the apply-credit summary sentence: "Applied credit to bill(s)
 *  #.. ($..)." plus, if any credit remains unapplied, a trailing note.
 *  Mirrors payments.ts's buildApplySummary. A bill with no ref_no is
 *  identified by its internal id instead (bills have no assigned number —
 *  see bills.ts's header for why). */
function buildApplySummary(applications: AppliedBill[], remainder: string): string {
  const label = applications.length === 1 ? "bill" : "bills";
  const parts = applications.map((a) => `#${a.refNo ?? a.billId} (${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 — mirrors payments.ts's
 *  lookupAssetAccount, fixed to type 'asset' since that's the only kind a
 *  money account may be. See the file header for why this is pre-checked
 *  here rather than left to pay_bills()'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 registerBillPaymentRoutes(fastify: FastifyInstance, db: Db): void {
  // -------------------------------------------------------------------------
  // POST /api/c/bill-payments
  // -------------------------------------------------------------------------
  fastify.post("/api/c/bill-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 moneyAccount = await lookupAssetAccount(client, companyId, body.moneyAccountId);
        if (!moneyAccount) return { ok: false as const, ...NOT_ASSET_ACCOUNT };

        const spec = {
          vendor_id: body.vendorId,
          payment_date: body.paymentDate,
          amount: body.amount,
          money_account_id: body.moneyAccountId,
          method: body.method,
          check_no: body.checkNo ?? null,
          memo: body.memo ?? null,
          applications: body.applications.map((a) => ({ bill_id: a.billId, amount: a.amount })),
        };

        // Raises (vendor/bill not found, bill not open, wrong vendor) 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<{ pay_bills: string }>(
          "SELECT pay_bills($1, $2::jsonb, $3) AS pay_bills",
          [companyId, JSON.stringify(spec), ownerId],
        );
        const paymentId = payRes.rows[0]!.pay_bills;

        const vendorRes = await client.query<{ name: string }>(
          "SELECT name FROM vendors WHERE company_id = $1 AND id = $2",
          [companyId, body.vendorId],
        );
        const vendorName = vendorRes.rows[0]?.name ?? "this vendor";

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

        // What check_no ended up on the payment: the caller's own manual
        // number, or still NULL for a to_print queue entry — read back
        // rather than trusted from the request, since pay_bills() is the
        // one source of truth for what actually got stored.
        const checkNoRes = await client.query<{ check_no: string | null }>(
          "SELECT check_no::text AS check_no FROM bill_payments WHERE company_id = $1 AND id = $2",
          [companyId, paymentId],
        );
        const storedCheckNo = checkNoRes.rows[0]?.check_no ?? null;

        const summary = buildPaySummary(
          body.amount, vendorName, body.method, storedCheckNo, moneyAccount.name, 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 dup = mapDuplicateCheckNo(err, body.checkNo ?? null);
      if (dup) return reply.code(dup.status).send({ error: dup.error });
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "pay bills failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // GET /api/c/bill-payments?vendorId=&limit=&offset=
  // -------------------------------------------------------------------------
  fastify.get("/api/c/bill-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 { vendorId, limit, offset } = parsedQuery.data;

    try {
      return await db.withCompany(companyId, async (client) => {
        const rowsRes = await client.query(
          `WITH totals AS (
             SELECT p.id, p.vendor_id, v.name AS vendor_name,
                    p.payment_date::text AS payment_date, p.amount, p.status,
                    p.method, p.check_no::text AS check_no, p.check_status, p.printed_on::text AS printed_on,
                    p.money_account_id, ma.name AS money_account_name,
                    p.posted_entry_id, p.void_entry_id, p.memo,
                    coalesce((SELECT sum(amount) FROM bill_payment_applications bpa
                                WHERE bpa.company_id = p.company_id AND bpa.payment_id = p.id), 0)
                      ::numeric(19,4) AS applied
               FROM bill_payments p
               JOIN vendors v ON v.company_id = p.company_id AND v.id = p.vendor_id
               JOIN accounts ma ON ma.company_id = p.company_id AND ma.id = p.money_account_id
              WHERE p.company_id = $1
                AND ($2::bigint IS NULL OR p.vendor_id = $2)
           )
           SELECT id, vendor_id, vendor_name, payment_date, amount, status,
                  method, check_no, check_status, printed_on, money_account_id, money_account_name,
                  posted_entry_id, void_entry_id, memo, 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, vendorId ?? null, limit ?? 50, offset ?? 0],
        );

        const rows = rowsRes.rows as Array<{
          id: string; vendor_id: string; vendor_name: string; payment_date: string;
          amount: string; status: string; method: string; check_no: string | null;
          check_status: string | null; printed_on: string | null;
          money_account_id: string; money_account_name: string;
          posted_entry_id: string; void_entry_id: string | null; memo: 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,
            vendorId: r.vendor_id,
            vendorName: r.vendor_name,
            amount: r.amount,
            method: r.method,
            checkNo: r.check_no,
            checkStatus: r.check_status,
            printedOn: r.printed_on,
            moneyAccountId: r.money_account_id,
            moneyAccountName: r.money_account_name,
            applied: r.applied,
            unapplied: r.unapplied,
            status: r.status,
            memo: r.memo,
            postedEntryId: r.posted_entry_id,
            voidEntryId: r.void_entry_id,
          })),
        };
      });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "bill payments list failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // GET /api/c/bill-payments/:id  (detail)
  // -------------------------------------------------------------------------
  fastify.get("/api/c/bill-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; vendor_id: string; vendor_name: string; vendor_email: string | null;
          payment_date: string; amount: string; status: string; memo: string | null;
          method: string; check_no: string | null; check_status: string | null; printed_on: string | null;
          money_account_id: string; money_account_name: string;
          posted_entry_id: string; void_entry_id: string | null;
        }>(
          `SELECT p.id, p.vendor_id, v.name AS vendor_name, v.email AS vendor_email,
                  p.payment_date::text AS payment_date, p.amount, p.status, p.memo,
                  p.method, p.check_no::text AS check_no, p.check_status, p.printed_on::text AS printed_on,
                  p.money_account_id, ma.name AS money_account_name,
                  p.posted_entry_id, p.void_entry_id
             FROM bill_payments p
             JOIN vendors v ON v.company_id = p.company_id AND v.id = p.vendor_id
             JOIN accounts ma ON ma.company_id = p.company_id AND ma.id = p.money_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; bill_id: string; ref_no: string | null; bill_date: string;
          due_date: string; amount: string;
        }>(
          `SELECT bpa.id, bpa.bill_id, b.ref_no, b.bill_date::text AS bill_date,
                  b.due_date::text AS due_date, bpa.amount
             FROM bill_payment_applications bpa
             JOIN bills b ON b.company_id = bpa.company_id AND b.id = bpa.bill_id
            WHERE bpa.company_id = $1 AND bpa.payment_id = $2
            ORDER BY bpa.id`,
          [companyId, paymentId],
        );

        const totalsRes = await client.query<{ applied: string; unapplied: string }>(
          `SELECT coalesce((SELECT sum(amount) FROM bill_payment_applications
                              WHERE company_id = $1 AND payment_id = $2), 0)::numeric(19,4) AS applied,
                  (p.amount - coalesce((SELECT sum(amount) FROM bill_payment_applications
                                          WHERE company_id = $1 AND payment_id = $2), 0))
                    ::numeric(19,4) AS unapplied
             FROM bill_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,
          vendorId: header.vendor_id,
          vendorName: header.vendor_name,
          vendorEmail: header.vendor_email,
          amount: header.amount,
          applied: totals.applied,
          unapplied: totals.unapplied,
          status: header.status,
          memo: header.memo,
          method: header.method,
          checkNo: header.check_no,
          checkStatus: header.check_status,
          printedOn: header.printed_on,
          moneyAccountId: header.money_account_id,
          moneyAccountName: header.money_account_name,
          postedEntryId: header.posted_entry_id,
          voidEntryId: header.void_entry_id,
          applications: appsRes.rows.map((a) => ({
            id: a.id,
            billId: a.bill_id,
            refNo: a.ref_no,
            billDate: a.bill_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 }, "bill payment detail failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // POST /api/c/bill-payments/:id/void
  // -------------------------------------------------------------------------
  fastify.post("/api/c/bill-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; method: string; check_no: string | null;
        }>(
          "SELECT id, method, check_no::text AS check_no FROM bill_payments WHERE company_id = $1 AND id = $2",
          [companyId, paymentId],
        );
        const existing = existingRes.rows[0];
        if (!existing) return { found: false as const };

        const appCountRes = await client.query<{ count: string }>(
          "SELECT count(*) AS count FROM bill_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_bill_payment($1, $2, coalesce($3::date, current_date), $4)",
          [companyId, paymentId, voidDate, ownerId],
        );

        return {
          found: true as const, hadApplications,
          method: existing.method, checkNo: existing.check_no,
        };
      });

      if (!result.found) {
        return reply.code(404).send(NOT_FOUND_PAYMENT);
      }
      let summary = result.hadApplications
        ? "Payment voided — the bills it covered are open again."
        : "Payment voided — the vendor credit it created no longer exists.";
      // A check that already carried a number (manual, or a queued one that
      // had already been printed) is consumed for good — the design spec's
      // "v1: void + re-pay" reprint rule (docs/superpowers/specs/
      // 2026-08-01-ap-design.md's check-printing section).
      if (result.method === "check" && result.checkNo !== null) {
        summary += ` Check #${result.checkNo} is now void and won't be reused.`;
      }
      return reply.send({ paymentId, summary });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "void bill payment failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // POST /api/c/bill-payments/:id/apply
  // -------------------------------------------------------------------------
  fastify.post("/api/c/bill-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 bill_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) => ({ bill_id: a.billId, amount: a.amount }));

        // Raises (payment void, bill not found/not open/wrong vendor,
        // 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_vendor_credit($1, $2, $3::jsonb, $4)",
          [companyId, paymentId, JSON.stringify(apps), ownerId],
        );

        const billIds = body.applications.map((a) => a.billId);
        const refNoRes = await client.query<{ id: string; ref_no: string | null }>(
          "SELECT id, ref_no FROM bills WHERE company_id = $1 AND id = ANY($2::bigint[])",
          [companyId, billIds],
        );
        const refNoById = new Map(refNoRes.rows.map((r) => [r.id, r.ref_no]));

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

        const applied = body.applications.map((a) => ({
          billId: a.billId,
          refNo: refNoById.get(a.billId) ?? null,
          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 vendor credit failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // GET /api/c/vendors/:id/open-bills  (pay-bills picker)
  // -------------------------------------------------------------------------
  fastify.get("/api/c/vendors/:id/open-bills", 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 vendorId = parsedParams.data.id;

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

        // open>0, non-void, issued (status='open' already excludes draft and
        // void) — includes document_only bills, same as ap_aging()/the
        // vendors.ts list route.
        const billRes = await client.query<{
          id: string; ref_no: string | null; bill_date: string; due_date: string;
          total: string; open: string; overdue: boolean;
        }>(
          `SELECT b.id, b.ref_no, b.bill_date::text AS bill_date, b.due_date::text AS due_date,
                  coalesce(sum(bl.amount), 0)::numeric(19,4) AS total,
                  bill_open_balance($1, b.id) AS open,
                  (b.due_date < current_date) AS overdue
             FROM bills b
             LEFT JOIN bill_lines bl ON bl.company_id = b.company_id AND bl.bill_id = b.id
            WHERE b.company_id = $1 AND b.vendor_id = $2 AND b.status = 'open'
            GROUP BY b.id, b.ref_no, b.bill_date, b.due_date
           HAVING bill_open_balance($1, b.id) > 0
            ORDER BY b.due_date, b.id`,
          [companyId, vendorId],
        );

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

        return {
          found: true as const,
          bills: billRes.rows.map((r) => ({
            billId: r.id,
            refNo: r.ref_no,
            billDate: r.bill_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_VENDOR);
      }
      return reply.send({ bills: result.bills, unappliedCredit: result.unappliedCredit });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "open bills lookup failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // GET /api/c/reports/ap-aging?asOf=  (default: today, per the database's clock)
  // -------------------------------------------------------------------------
  fastify.get("/api/c/reports/ap-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) => {
        // ap_aging() (db/migrations/0009_ap.sql) already does the actual
        // bucketing (by due_date, including document_only bills) and the
        // per-vendor unapplied-credit rows; this just re-shapes its rows
        // (row_type/bucket) into the same {buckets, credits, total} shape
        // payments.ts's AR aging report uses — entirely in SQL (CLAUDE.md
        // §2 — no money math in JS). row_type here is 'bill'/'credit' (not
        // AR's 'invoice'/'credit' — see ap_aging()'s own header comment).
        const perVendorRes = await client.query<{
          vendor_id: string; vendor_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 ap_aging($1, coalesce($2::date, current_date)))
           SELECT vendor_id, vendor_name,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'bill' AND bucket = 'current'), 0)
                    ::numeric(19,4) AS current,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'bill' AND bucket = '1-30'), 0)
                    ::numeric(19,4) AS d1_30,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'bill' AND bucket = '31-60'), 0)
                    ::numeric(19,4) AS d31_60,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'bill' AND bucket = '61-90'), 0)
                    ::numeric(19,4) AS d61_90,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'bill' 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 = 'bill'), 0)::numeric(19,4) AS total
             FROM aging
            GROUP BY vendor_id, vendor_name
            ORDER BY vendor_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 ap_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 = 'bill' AND bucket = 'current'), 0)
                    ::numeric(19,4) AS current,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'bill' AND bucket = '1-30'), 0)
                    ::numeric(19,4) AS d1_30,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'bill' AND bucket = '31-60'), 0)
                    ::numeric(19,4) AS d31_60,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'bill' AND bucket = '61-90'), 0)
                    ::numeric(19,4) AS d61_90,
                  coalesce(sum(open_amount) FILTER (WHERE row_type = 'bill' 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 = 'bill'), 0)::numeric(19,4) AS grand
             FROM aging`,
          [companyId, asOf],
        );
        const t = totalsRes.rows[0]!;

        return {
          asOf: t.as_of,
          vendors: perVendorRes.rows.map((r) => ({
            vendorId: r.vendor_id,
            name: r.vendor_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 }, "ap aging report failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });
}
