// Year-end close API — Task 6 of docs/superpowers/plans/2026-07-31-local-app.md.
// Three routes, all under /api/c/ (company scope from X-Company-Id, enforced
// by companyContextPlugin):
//
//   POST /api/c/close/compare  — upload the old software's Trial Balance xlsx +
//                                 year, diff it against trial_balance(Dec 31
//                                 of that year). Stateless: persists nothing.
//   POST /api/c/close/lock     — the SAME upload + year, re-parsed and
//                                 re-compared in this request (there is no
//                                 stored "last compare" to trust); locks the
//                                 year only on a zero-diff match.
//   GET  /api/c/close/status   — locked-through + per-year posted/locked
//                                 badges.
//
// CLAUDE.md §7 is binding here: "reconcile against QuickBooks' own trial
// balance for that year end. Zero differences, then lock the period." The
// zero-diff gate is enforced by re-running the comparison inside the SAME
// transaction that inserts the period_locks row (never by trusting a
// previous request's result) — see runCompare()/the lock handler below.
//
// Upload handling mirrors imports.ts's receiveXlsxUpload (same
// imports/uploads/ provenance convention, same randomUUID collision-proofing)
// but also reads the "year" text field carried alongside the file in the
// same multipart request. @fastify/multipart's docs recommend placing value
// fields BEFORE the file field in the multipart body so they're guaranteed
// parsed (via part.fields) by the time request.file() resolves — the test
// suite's multipart builder does this; a real browser <form> with the year
// input before the file input satisfies it too.
//
// Money handling: every comparison amount is converted to a BigInt count of
// ten-thousandths of a dollar (matching Postgres numeric(19,4)'s scale) at
// the boundary and converted back once at the end — never a float, per
// CLAUDE.md §2. This is the one file in the API that has to add two
// independently-sourced money values (ours from Postgres, theirs from a
// parsed xlsx cell), so unlike reports.ts (which only ever flips a sign)
// this needs real arithmetic, and BigInt integer arithmetic is the
// float-free way to do it.

import { mkdir, writeFile } from "node:fs/promises";
import path from "node:path";
import { fileURLToPath } from "node:url";
import { randomUUID } from "node:crypto";
import type { FastifyInstance, FastifyRequest } from "fastify";
import type { MultipartFile } from "@fastify/multipart";
import type { PoolClient } from "pg";
import { z } from "zod";
import type { Db } from "./db.js";
import { mapPgError } from "./errors.js";
import { readQbSheet } from "../../tools/qb-xlsx.js";
import { parseTrialBalanceRows, type TbRow } from "../../tools/tb-logic.js";
import { findOwnerId } from "../../tools/lib/journal-load.js";

const serverDir = path.dirname(fileURLToPath(import.meta.url));
const UPLOAD_DIR = path.resolve(serverDir, "../../imports/uploads");
const ZIP_MAGIC = Buffer.from([0x50, 0x4b, 0x03, 0x04]);

// ---------------------------------------------------------------------------
// upload receipt — mirrors imports.ts's receiveXlsxUpload, plus the "year"
// text field carried in the same multipart request.
// ---------------------------------------------------------------------------

interface TbUploadOk {
  filePath: string;
  originalFilename: string;
  yearRaw: string | undefined;
}
interface UploadErr {
  status: number;
  error: string;
}

function isUploadErr(u: TbUploadOk | UploadErr): u is UploadErr {
  return "error" in u;
}

function mapMultipartError(err: unknown): UploadErr | null {
  const code = (err as { code?: string } | undefined)?.code;
  if (code === "FST_REQ_FILE_TOO_LARGE") {
    return { status: 413, error: "That file is larger than the 25MB limit." };
  }
  if (code === "FST_INVALID_MULTIPART_CONTENT_TYPE") {
    return { status: 400, error: "Please upload the file as a multipart/form-data request." };
  }
  if (code === "FST_PARTS_LIMIT" || code === "FST_FILES_LIMIT" || code === "FST_FIELDS_LIMIT") {
    return { status: 400, error: "That upload had too many parts — please upload just the file and the year." };
  }
  return null;
}

function extractFieldValue(field: unknown): string | undefined {
  const f = Array.isArray(field) ? field[0] : field;
  const value = (f as { value?: unknown } | undefined)?.value;
  return typeof value === "string" ? value : undefined;
}

/**
 * Reads a single ".xlsx" file from a multipart request's "file" field (plus
 * the "year" text field carried alongside it — see part.fields, populated by
 * @fastify/multipart as fields ahead of the file part in the stream are
 * consumed), validates the file (extension + zip magic bytes), and saves it
 * under imports/uploads/<companyId>-<timestamp>-<uuid>-tb.xlsx for
 * provenance. Never touches the database.
 */
async function receiveTbUpload(
  request: FastifyRequest, companyId: string,
): Promise<TbUploadOk | UploadErr> {
  let part: MultipartFile | undefined;
  try {
    part = await request.file();
  } catch (err) {
    const mapped = mapMultipartError(err);
    if (mapped) return mapped;
    throw err;
  }
  if (!part) {
    return { status: 400, error: "Please choose a file to upload." };
  }
  if (part.fieldname !== "file") {
    return { status: 400, error: 'Please upload the file under a field named "file".' };
  }
  if (!part.filename || !part.filename.toLowerCase().endsWith(".xlsx")) {
    return { status: 400, error: "Please upload an Excel (.xlsx) file." };
  }

  let buffer: Buffer;
  try {
    buffer = await part.toBuffer();
  } catch (err) {
    const mapped = mapMultipartError(err);
    if (mapped) return mapped;
    throw err;
  }

  if (buffer.length < 4 || buffer.subarray(0, 4).compare(ZIP_MAGIC) !== 0) {
    return { status: 400, error: "That doesn't look like a valid Excel (.xlsx) file." };
  }

  await mkdir(UPLOAD_DIR, { recursive: true });
  const filePath = path.join(UPLOAD_DIR, `${companyId}-${Date.now()}-${randomUUID()}-tb.xlsx`);
  await writeFile(filePath, buffer);

  const yearRaw = extractFieldValue((part.fields as Record<string, unknown> | undefined)?.["year"]);

  return { filePath, originalFilename: part.filename, yearRaw };
}

// ---------------------------------------------------------------------------
// year validation
// ---------------------------------------------------------------------------

const yearFieldSchema = z.coerce
  .number({ error: "year must be a whole number like 2011" })
  .int("year must be a whole number like 2011")
  .min(2000, "year must be between 2000 and 2100")
  .max(2100, "year must be between 2000 and 2100");

type YearResult = { ok: true; year: number } | { ok: false; error: string };

function parseYear(yearRaw: string | undefined): YearResult {
  if (yearRaw === undefined || yearRaw.trim() === "") {
    return { ok: false, error: "Please include a year (e.g. 2011)." };
  }
  const parsed = yearFieldSchema.safeParse(yearRaw);
  if (!parsed.success) {
    return { ok: false, error: parsed.error.issues.map((i) => i.message).join("; ") };
  }
  return { ok: true, year: parsed.data };
}

// ---------------------------------------------------------------------------
// float-free money helpers — see the file header note on BigInt vs floats.
// ---------------------------------------------------------------------------

/** Parses a decimal money string (optional leading '-', up to 4 decimal
 *  places — matching Postgres numeric(19,4)) into a BigInt count of
 *  ten-thousandths of a dollar, without ever going through a JS float. */
function toMicros(amount: string): bigint {
  const negative = amount.startsWith("-");
  const unsigned = negative ? amount.slice(1) : amount;
  const [wholeRaw, fracRaw = ""] = unsigned.split(".");
  const frac = (fracRaw + "0000").slice(0, 4);
  const micros = BigInt(wholeRaw || "0") * 10000n + BigInt(frac || "0");
  return negative ? -micros : micros;
}

/** Inverse of toMicros — always renders 4 decimal places, matching how
 *  Postgres numeric(19,4) values are shown everywhere else in this API. */
function fromMicros(micros: bigint): string {
  const negative = micros < 0n;
  const abs = negative ? -micros : micros;
  const whole = abs / 10000n;
  const frac = (abs % 10000n).toString().padStart(4, "0");
  return `${negative ? "-" : ""}${whole}.${frac}`;
}

// ---------------------------------------------------------------------------
// comparison — the core of both /compare and /lock
// ---------------------------------------------------------------------------

type RowStatus = "match" | "different" | "only-ours" | "only-theirs";

interface CompareRow {
  account: string;
  ours: string | null;
  theirs: string | null;
  diff: string;
  status: RowStatus;
}

interface CompareResult {
  matches: boolean;
  rows: CompareRow[];
  totals: { ours: string; theirs: string };
}

/**
 * Diffs a parsed Trial Balance export against trial_balance(company, Dec 31
 * of `year`). Matching rule (CLAUDE.md §7 / the design spec): an account in
 * their file resolves to one of ours by EXACT name match, or — since the QB
 * Trial Balance shows full "Parent:Sub" paths but a leaf-only name is also
 * common — by account_aliases (mirrors resolve_batch()'s own account
 * resolution in db/migrations/0003_import_layer.sql). An ambiguous exact
 * name match (more than one of our accounts sharing that name) is treated
 * as unresolved by name, same as resolve_batch, so it falls through to the
 * alias lookup rather than picking one arbitrarily.
 *
 * Every account that has EITHER a nonzero balance on our side OR a resolved
 * entry on theirs is included as a row — a zero-balance account that their
 * file nonetheless lists (or vice versa isn't possible, since a resolved
 * "theirs" always ties to a real account) still shows up as a genuine
 * "different" row rather than being silently dropped.
 *
 * `client` must already be scoped to `companyId` (i.e. called from inside
 * db.withCompany) — every query here is RLS-scoped, not just filtered by a
 * WHERE clause (CLAUDE.md §4/§12).
 */
async function runCompare(
  client: PoolClient, companyId: string, year: number, tbRows: TbRow[],
): Promise<CompareResult> {
  const asOf = `${year}-12-31`;

  const oursRes = await client.query<{ account_id: string; name: string; balance: string }>(
    `SELECT account_id, name, balance FROM trial_balance($1, $2)`,
    [companyId, asOf],
  );

  // Sum duplicate account names within their own file (defensive — a
  // well-formed TB shouldn't repeat an account, but summing rather than
  // overwriting never silently drops a real amount).
  const theirsSigned = new Map<string, bigint>();
  for (const r of tbRows) {
    const signed = toMicros(r.amount) * (r.side === "credit" ? -1n : 1n);
    theirsSigned.set(r.account, (theirsSigned.get(r.account) ?? 0n) + signed);
  }
  const theirNames = [...theirsSigned.keys()];

  const resolvedRes = theirNames.length > 0
    ? await client.query<{ their_name: string; account_id: string | null }>(
      `SELECT t.their_name,
              coalesce(
                (SELECT min(a.id) FROM accounts a
                  WHERE a.company_id = $1 AND a.name = t.their_name
                 HAVING count(*) = 1),
                (SELECT al.account_id FROM account_aliases al
                  WHERE al.company_id = $1 AND al.alias = t.their_name)
              ) AS account_id
         FROM unnest($2::text[]) AS t(their_name)`,
      [companyId, theirNames],
    )
    : { rows: [] as Array<{ their_name: string; account_id: string | null }> };

  const theirsByAccountId = new Map<string, bigint>();
  const unmatchedTheirs: Array<{ name: string; micros: bigint }> = [];
  for (const row of resolvedRes.rows) {
    const micros = theirsSigned.get(row.their_name)!;
    if (row.account_id === null) {
      unmatchedTheirs.push({ name: row.their_name, micros });
    } else {
      theirsByAccountId.set(row.account_id, (theirsByAccountId.get(row.account_id) ?? 0n) + micros);
    }
  }

  const oursByAccountId = new Map<string, { name: string; balanceMicros: bigint }>();
  // The "totals" footing below mirrors what a printed trial balance actually
  // shows: every account's NET balance lands in EITHER the debit column
  // (positive balance) OR the credit column (negative balance), and the two
  // columns' totals tie. Summing gross debit_total (every debit ever posted
  // to the account, across its whole history) would NOT tie to their file's
  // debit-column total even in a perfect match — a debit-normal account with
  // any activity on its credit side nets those out in the printed balance,
  // but gross debit_total doesn't. balanceMicros (debit_total - credit_total)
  // is the right quantity to foot.
  let oursDebitFootingMicros = 0n;
  for (const row of oursRes.rows) {
    const balanceMicros = toMicros(row.balance);
    oursByAccountId.set(row.account_id, { name: row.name, balanceMicros });
    if (balanceMicros > 0n) oursDebitFootingMicros += balanceMicros;
  }

  const relevantAccountIds = new Set<string>();
  for (const [id, entry] of oursByAccountId) {
    if (entry.balanceMicros !== 0n) relevantAccountIds.add(id);
  }
  for (const id of theirsByAccountId.keys()) relevantAccountIds.add(id);

  const rows: CompareRow[] = [];
  for (const id of relevantAccountIds) {
    const ours = oursByAccountId.get(id)!; // always present: id came from oursByAccountId itself, or from a
    // resolved (therefore company-scoped, therefore trial_balance-covered) account.
    const theirsMicros = theirsByAccountId.get(id);
    const diffMicros = ours.balanceMicros - (theirsMicros ?? 0n);
    const status: RowStatus = theirsMicros === undefined
      ? "only-ours"
      : diffMicros === 0n ? "match" : "different";
    rows.push({
      account: ours.name,
      ours: fromMicros(ours.balanceMicros),
      theirs: theirsMicros !== undefined ? fromMicros(theirsMicros) : null,
      diff: fromMicros(diffMicros),
      status,
    });
  }
  for (const { name, micros } of unmatchedTheirs) {
    rows.push({
      account: name, ours: null, theirs: fromMicros(micros), diff: fromMicros(-micros), status: "only-theirs",
    });
  }
  rows.sort((a, b) => a.account.localeCompare(b.account));

  let theirsDebitFootingMicros = 0n;
  for (const r of tbRows) {
    if (r.side === "debit") theirsDebitFootingMicros += toMicros(r.amount);
  }

  return {
    matches: rows.every((r) => r.status === "match"),
    rows,
    totals: { ours: fromMicros(oursDebitFootingMicros), theirs: fromMicros(theirsDebitFootingMicros) },
  };
}

// ---------------------------------------------------------------------------
// owner resolution — same pattern as entries.ts/imports.ts (no login in v1;
// every write is attributed to the company's owner user).
// ---------------------------------------------------------------------------

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

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;
  }
}

// ---------------------------------------------------------------------------
// lock guard outcome — a discriminated union so the route handler never
// needs a non-null assertion to tell the three outcomes apart.
// ---------------------------------------------------------------------------

type LockOutcome =
  | { guard: "already-locked" }
  | { guard: "out-of-order"; earliestYear: number }
  | { guard: null; locked: false; compare: CompareResult }
  | { guard: null; locked: true; lockedThrough: string };

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

export function registerCloseRoutes(fastify: FastifyInstance, db: Db): void {
  // -------------------------------------------------------------------------
  // POST /api/c/close/compare
  // -------------------------------------------------------------------------
  fastify.post("/api/c/close/compare", async (request, reply) => {
    const companyId = request.companyId!;

    const upload = await receiveTbUpload(request, companyId);
    if (isUploadErr(upload)) {
      return reply.code(upload.status).send({ error: upload.error });
    }
    const yearResult = parseYear(upload.yearRaw);
    if (!yearResult.ok) {
      return reply.code(400).send({ error: yearResult.error });
    }
    const { year } = yearResult;

    let tbRows: TbRow[];
    try {
      const sheetRows = await readQbSheet(upload.filePath);
      tbRows = parseTrialBalanceRows(sheetRows);
    } catch (err) {
      return reply.code(422).send({ error: (err as Error).message });
    }

    try {
      const result = await db.withCompany(
        companyId, (client) => runCompare(client, companyId, year, tbRows),
      );
      return reply.send({ year, matches: result.matches, rows: result.rows, totals: result.totals });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "close compare failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // POST /api/c/close/lock — re-parses + re-compares in THIS request (see
  // file header): there is no persisted "last compare" to trust, and the
  // zero-diff gate has to be checked against fresh input, atomically with
  // the INSERT that actually locks the year.
  // -------------------------------------------------------------------------
  fastify.post("/api/c/close/lock", async (request, reply) => {
    const companyId = request.companyId!;

    const upload = await receiveTbUpload(request, companyId);
    if (isUploadErr(upload)) {
      return reply.code(upload.status).send({ error: upload.error });
    }
    const yearResult = parseYear(upload.yearRaw);
    if (!yearResult.ok) {
      return reply.code(400).send({ error: yearResult.error });
    }
    const { year } = yearResult;

    let tbRows: TbRow[];
    try {
      const sheetRows = await readQbSheet(upload.filePath);
      tbRows = parseTrialBalanceRows(sheetRows);
    } catch (err) {
      return reply.code(422).send({ error: (err as Error).message });
    }

    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: Fastify
      // treats the response as complete the instant send() runs, before the
      // wrapper's own COMMIT (which runs after this callback resolves) has
      // executed. Returning a plain discriminated result and replying only
      // once withCompany() has fully resolved keeps the INSERT guaranteed
      // committed before the client can observe { locked: true }.
      const result: LockOutcome = await db.withCompany(companyId, async (client) => {
        // Serializes concurrent lock requests for the SAME company — mirrors
        // the advisory-lock pattern in tools/lib/chart-load.ts's
        // ensureCompanyAndOwner (used by the chart-import route in
        // imports.ts). Without this, two racing requests (e.g. a double-
        // click) could both pass the guard checks below before either
        // commits, and both insert a period_locks row for what must be one
        // atomic "lock this year" decision. The pg_advisory_xact_lock is
        // released automatically at COMMIT/ROLLBACK of this transaction.
        await client.query(
          "SELECT pg_advisory_xact_lock(hashtext('close:lock:' || $1))", [companyId],
        );

        const askedThrough = `${year}-12-31`;

        // §7: import/lock oldest year first, "after each one reconciles" —
        // the guard is NOT merely "is year-1 locked" (that misses gap years:
        // a company with posted entries in 2013 and 2015 but none in 2014
        // could otherwise lock 2015 directly, silently sweeping unreconciled
        // 2013 under a lock that, once past, can never be un-passed). Instead
        // find the EARLIEST year with any posted entry not yet covered by
        // the current effective lock — that year must be <= the one being
        // requested.
        const guardRes = await client.query<{
          locked_through: string | null; earliest_unlocked_year: number | null;
        }>(
          `WITH lock AS (SELECT effective_locked_through($1) AS locked_through)
           SELECT lock.locked_through::text AS locked_through,
                  min(extract(year FROM e.entry_date))::int AS earliest_unlocked_year
             FROM lock
             LEFT JOIN journal_entries e
               ON e.company_id = $1 AND e.status = 'posted'
              AND (lock.locked_through IS NULL OR e.entry_date > lock.locked_through)
            GROUP BY lock.locked_through`,
          [companyId],
        );
        const guard = guardRes.rows[0]!;

        // §3.3 / §7: the effective lock is the latest period_locks row.
        // ISO 'YYYY-MM-DD' strings compare correctly lexicographically —
        // no date-object parsing needed for this comparison.
        if (guard.locked_through !== null && guard.locked_through >= askedThrough) {
          return { guard: "already-locked" as const };
        }
        if (guard.earliest_unlocked_year !== null && guard.earliest_unlocked_year < year) {
          return { guard: "out-of-order" as const, earliestYear: guard.earliest_unlocked_year };
        }

        const compare = await runCompare(client, companyId, year, tbRows);
        if (!compare.matches) {
          return { guard: null, locked: false as const, compare };
        }

        await client.query(
          `INSERT INTO period_locks (company_id, locked_through, created_by) VALUES ($1, $2, $3)`,
          [companyId, askedThrough, ownerId],
        );
        return { guard: null, locked: true as const, lockedThrough: askedThrough };
      });

      if (result.guard === "already-locked") {
        return reply.code(409).send({ error: `${year} is already locked.` });
      }
      if (result.guard === "out-of-order") {
        return reply.code(409).send({
          error: `Lock ${result.earliestYear} first — years must be locked oldest-first after each one reconciles.`,
        });
      }
      if (!result.locked) {
        return reply.code(409).send({
          error: `${year} doesn't tie out yet — fix the differences below before locking.`,
          matches: false,
          rows: result.compare.rows,
          totals: result.compare.totals,
        });
      }
      return reply.code(201).send({
        locked: true,
        lockedThrough: result.lockedThrough,
        summary: `${year} is locked. Nothing can be posted into it; corrections must go into open years.`,
      });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "close lock failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // GET /api/c/close/status
  // -------------------------------------------------------------------------
  fastify.get("/api/c/close/status", async (request, reply) => {
    const companyId = request.companyId!;
    try {
      const result = await db.withCompany(companyId, async (client) => {
        const lockRes = await client.query<{ locked_through: string | null }>(
          `SELECT effective_locked_through($1)::text AS locked_through`, [companyId],
        );
        const lockedThrough = lockRes.rows[0]!.locked_through;

        const yearsRes = await client.query<{ year: number; n: string }>(
          `SELECT extract(year FROM entry_date)::int AS year, count(*)::int AS n
             FROM journal_entries
            WHERE company_id = $1 AND status = 'posted'
            GROUP BY 1 ORDER BY 1`,
          [companyId],
        );

        return {
          lockedThrough,
          years: yearsRes.rows.map((r) => ({
            year: r.year,
            postedEntries: Number(r.n),
            locked: lockedThrough !== null && lockedThrough >= `${r.year}-12-31`,
          })),
        };
      });
      return reply.send(result);
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "close status failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });
}
