// Universal import ("Restore from other software") — Task 4 of
// docs/superpowers/plans/2026-08-02-switch-from-anywhere.md; spec Part B of
// docs/superpowers/specs/2026-08-02-switch-from-anywhere-design.md.
//
// The pure engine (tools/universal-map-logic.ts + tools/universal-presets.ts)
// does every bit of parsing/mapping/grouping; this module is the plumbing:
//
//   POST /api/c/universal/upload            multipart (file + kind=chart|journal
//        + presetKey?, the source product the user picked in the wizard):
//        saves the file under imports/uploads/ (kept forever, CLAUDE.md §12),
//        parses it to rows, and returns headers + sample rows + preset guess
//        (+ date-format sniff for journals, distinct types + fuzzy type-map
//        prefill for charts). NOTHING is staged — the reply's uploadId is an
//        opaque token later calls hand back.
//
//        The user's PICK decides the column guesses (presetGuess.source ===
//        "picked"). Header scoring still runs, but only as an assist: when it
//        indicates a clearly different product than the one picked, that comes
//        back as `alternatePreset` for the wizard to OFFER — it never silently
//        overrides the pick. With no presetKey at all (API callers; the wizard
//        always sends one) header scoring is the fallback, and only then does
//        presetGuess.source read "detected". See the spec's Part B non-goals,
//        as-built amendment 2026-08-03.
//   POST /api/c/universal/chart/preview     {uploadId, mapping, typeValueMap}:
//        applyChartMapping only — accounts + row-numbered errors, no writes.
//   POST /api/c/universal/chart/commit      same inputs: creates accounts +
//        aliases via tools/lib/universal-chart-load.ts (the chart-load
//        conventions: same-name/different-type conflicts abort loudly,
//        nothing half-applied).
//   POST /api/c/universal/journal/preview   {uploadId, mapping}: FULL-file
//        applyJournalMapping — summary (entry/line counts, date range, year
//        breakdown) + first 50 errors, no staging.
//   POST /api/c/universal/journal/stage     {uploadId, mapping, sourceSlug?}:
//        engine errors -> 422 and nothing staged; clean -> one batch PER YEAR
//        (mirroring the QB pipeline exactly) through tools/lib/
//        journal-load.ts's loadJournalYears with source 'universal:<slug>' —
//        which runs the SAME resolve_batch/validate_batch machinery, keeps
//        the ORIGINAL parsed row as each staging row's raw jsonb, and
//        re-validates already-loaded years (the fix-a-missing-account loop).
//        A file whose every year is already posted/rolled back -> 409.
//   POST /api/c/universal/journal/post      {sourceSlug, years?}: thin wrap of
//        the QB post flow (loadExistingYearResults + postYears + per-year
//        batch_reconciliation), just filtered to the universal source.
//   GET  /api/c/universal/mappings[?kind=]  saved per-company mappings.
//   POST /api/c/universal/mappings          {kind, name, mapping}: save-by-name
//        (upsert), like bank_csv_maps but named (migration 0017).
//
// Upload tokens are held in a module-level in-memory registry (this is a
// single-process local app): the token is a random UUID, the server rebuilds
// the file path from its own registry entry — the client-supplied string is
// never used as a path component, so traversal is structurally impossible; a
// malformed token is a 400 before any lookup, an unknown token (or another
// company's token) is UPLOAD_EXPIRED. A server restart empties the registry;
// re-uploading is the recovery, and the message says exactly that.

import { mkdir, writeFile, readFile } 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 { z } from "zod";
import type { Db } from "./db.js";
import { mapPgError, UPLOAD_EXPIRED, UNIVERSAL_FILE_ALREADY_IMPORTED } from "./errors.js";
import {
  parseCsvRows,
  sniffHeaders,
  sniffDateFormat,
  collectDistinctTypes,
  fuzzyPrefillTypeMap,
  applyChartMapping,
  applyJournalMapping,
  type ChartMapping,
  type TypeValueMap,
  type UniversalJournalMapping,
  type RowError,
  type JournalEntry,
  type JournalLine,
} from "../../tools/universal-map-logic.js";
import {
  matchPreset,
  matchHints,
  UNIVERSAL_PRESETS,
  type UniversalPreset,
  type HintMatch,
} from "../../tools/universal-presets.js";
import { readUniversalSheet } from "../../tools/universal-xlsx.js";
import { groupEntriesByYear, sliceSha } from "../../tools/journal-logic.js";
import {
  findOwnerId,
  loadJournalYears,
  loadExistingYearResults,
  postYears,
  shouldRevalidate,
  type YearResult,
  type PostYearResult,
  type RawRowProvider,
} from "../../tools/lib/journal-load.js";
import { loadUniversalChart } from "../../tools/lib/universal-chart-load.js";

const serverDir = path.dirname(fileURLToPath(import.meta.url));
const UPLOAD_DIR = path.resolve(serverDir, "../../imports/uploads");

// A .xlsx file is a zip archive — same magic-byte check as imports.ts.
const ZIP_MAGIC = Buffer.from([0x50, 0x4b, 0x03, 0x04]);

// How many parsed data rows the upload reply shows the mapping UI.
const UPLOAD_SAMPLE_ROWS = 8;
// Row-numbered error lists are capped in replies (the total always rides
// along) — a 40,000-bad-row file must not produce a 40,000-item JSON body.
const MAX_ERRORS_IN_REPLY = 50;
// Upload registry cap: oldest entries are evicted past this. The FILES are
// never deleted (provenance, §12) — only the in-memory token mapping.
const MAX_UPLOAD_TOKENS = 200;

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

// The default import label. A CONSTANT on purpose: an earlier draft derived
// it from header sniffing per upload, which meant the same export re-uploaded
// with a tweaked header could land under a different source — and since
// source is part of the (company_id, source, external_ref) idempotency key,
// that opened a double-posting hole. The label is cosmetic; stability is not.
const DEFAULT_SLUG = "import";

// Content-only batch identity for universal imports: the per-year sha is
// salted with this constant instead of the uploaded FILENAME, so the same
// bytes under any name (or import label) collide with the existing batch
// instead of staging a second copy. See loadYear() in tools/lib/journal-load.ts.
const UNIVERSAL_SHA_BASENAME = "universal";

const RETRY_NOTE =
  "This file was imported and then rolled back — re-importing will post it again.";

// ---------------------------------------------------------------------------
// upload registry
// ---------------------------------------------------------------------------

type UploadKind = "chart" | "journal";
type UploadFormat = "csv" | "xlsx";

interface UploadRecord {
  companyId: string;
  kind: UploadKind;
  filePath: string;
  originalFilename: string;
  format: UploadFormat;
}

const uploads = new Map<string, UploadRecord>();

function registerUpload(record: UploadRecord): string {
  const uploadId = randomUUID();
  uploads.set(uploadId, record);
  while (uploads.size > MAX_UPLOAD_TOKENS) {
    const oldest = uploads.keys().next().value;
    if (oldest === undefined) break;
    uploads.delete(oldest);
  }
  return uploadId;
}

const BAD_ENCODING_ERROR =
  "This file's text encoding isn't one we can read — re-save it as CSV UTF-8.";

/**
 * Decodes an uploaded CSV's bytes to text. Windows accounting exports are
 * frequently UTF-16 (Excel's "Unicode Text", and several Sage/Peachtree
 * report exports), which read as mojibake under a blind utf8 decode — the
 * upload succeeds, the headers are garbage, and every column guess misses
 * for a reason nobody can see. So the BOM is honoured:
 *
 *   FF FE      UTF-16 LE  -> decoded as utf16le
 *   FE FF      UTF-16 BE  -> byte-swapped, then decoded as utf16le
 *   EF BB BF   UTF-8 BOM  -> stripped (Excel writes these constantly; left
 *                            in place it becomes part of the FIRST header's
 *                            text and quietly breaks preset matching)
 *
 * With no BOM we decode utf8 — but a BOM-less UTF-16 file is still readable
 * as bytes, so a text body carrying NULs (which valid UTF-8 spreadsheet text
 * never does) is refused in English rather than imported as mojibake.
 */
export function decodeCsvBuffer(buffer: Buffer): string {
  if (buffer.length >= 2 && buffer[0] === 0xff && buffer[1] === 0xfe) {
    return buffer.subarray(2).toString("utf16le");
  }
  if (buffer.length >= 2 && buffer[0] === 0xfe && buffer[1] === 0xff) {
    const body = Buffer.from(buffer.subarray(2)); // copy: never swap in place
    body.swap16();
    return body.toString("utf16le");
  }
  if (buffer.length >= 3 && buffer[0] === 0xef && buffer[1] === 0xbb && buffer[2] === 0xbf) {
    return buffer.subarray(3).toString("utf8");
  }
  // No BOM. Sample the head for NUL bytes — one is already impossible in
  // legitimate CSV text, and BOM-less UTF-16 is full of them.
  const head = buffer.subarray(0, 4096);
  if (head.includes(0x00)) throw new Error(BAD_ENCODING_ERROR);
  return buffer.toString("utf8");
}

async function readUploadRows(record: UploadRecord): Promise<string[][]> {
  if (record.format === "xlsx") return readUniversalSheet(record.filePath);
  return parseCsvRows(decodeCsvBuffer(await readFile(record.filePath)));
}

// ---------------------------------------------------------------------------
// multipart reading (file + fields, either order) — mirrors bank.ts's
// readMultipart (module-private there; ~40 lines, deliberately duplicated
// rather than exporting a route-module internal across modules).
// ---------------------------------------------------------------------------

interface MultipartRead {
  buffer: Buffer;
  filename: string;
  fields: Record<string, string>;
}
interface MultipartErr { status: number; error: string }

function isMultipartErr(u: MultipartRead | MultipartErr): u is MultipartErr {
  return "error" in u;
}

function mapMultipartError(err: unknown): MultipartErr | 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 one file." };
  }
  return null;
}

async function readMultipart(request: FastifyRequest): Promise<MultipartRead | MultipartErr> {
  let buffer: Buffer | null = null;
  let filename: string | null = null;
  const fields: Record<string, string> = {};
  try {
    for await (const part of request.parts()) {
      if (part.type === "file") {
        if (part.fieldname !== "file") {
          await part.toBuffer().catch(() => {});
          continue;
        }
        if (buffer !== null) {
          return { status: 400, error: "Please upload just one file." };
        }
        buffer = await part.toBuffer();
        filename = part.filename;
      } else {
        fields[part.fieldname] = typeof part.value === "string" ? part.value : String(part.value ?? "");
      }
    }
  } catch (err) {
    const mapped = mapMultipartError(err);
    if (mapped) return mapped;
    throw err;
  }
  if (buffer === null || !filename) {
    return { status: 400, error: "Please choose a file to upload." };
  }
  return { buffer, filename, fields };
}

// ---------------------------------------------------------------------------
// schemas
// ---------------------------------------------------------------------------

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

// Strict shape check BEFORE any registry lookup: a traversal-shaped or
// otherwise malformed token ("../../etc/passwd") is a clean 400 here. The
// token is never used to build a path anyway (see file header) — this just
// keeps garbage out of the registry lookup and the error honest.
const uploadIdSchema = z
  .string({ error: "uploadId is required — it comes from the upload step." })
  .regex(
    /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i,
    "uploadId must be the token returned by the upload step.",
  );

const colSchema = z.number({ error: "column indexes must be numbers" }).int().min(0);
const optionalColSchema = colSchema.optional();

const chartMappingSchema = z.object({
  headerRows: colSchema,
  nameCol: colSchema,
  typeCol: colSchema,
  codeCol: optionalColSchema,
  parentCol: optionalColSchema,
  fullPathCol: optionalColSchema,
  pathSeparator: z.string().min(1).max(5).optional(),
});

const ledgerTypeSchema = z.enum(["asset", "liability", "equity", "income", "expense"]);
const typeValueMapSchema = z.record(z.string(), z.union([ledgerTypeSchema, z.null()]));

const dateFormatSchema = z.enum(
  ["MM/DD/YYYY", "M/D/YY", "YYYY-MM-DD", "DD/MM/YYYY", "MMM D YYYY", "D-MMM-YY", "MM-DD-YYYY"],
  { error: "dateFormat must be one of the supported date formats." },
);

const journalMappingSchema = z.object({
  headerRows: colSchema,
  dateCol: colSchema,
  dateFormat: dateFormatSchema,
  accountCol: colSchema,
  amountShape: z.discriminatedUnion("kind", [
    z.object({ kind: z.literal("debit-credit"), debitCol: colSchema, creditCol: colSchema }),
    z.object({
      kind: z.literal("signed"),
      amountCol: colSchema,
      positiveIs: z.enum(["debit", "credit"]),
    }),
  ]),
  grouping: z.discriminatedUnion("kind", [
    z.object({ kind: z.literal("key-column"), groupKeyCol: colSchema }),
    z.object({ kind: z.literal("consecutive-balanced") }),
  ]),
  memoCol: optionalColSchema,
  nameCol: optionalColSchema,
  refCol: optionalColSchema,
});

const chartBodySchema = z.object({
  uploadId: uploadIdSchema,
  mapping: chartMappingSchema,
  typeValueMap: typeValueMapSchema,
});

const journalPreviewBodySchema = z.object({
  uploadId: uploadIdSchema,
  mapping: journalMappingSchema,
});

const journalStageBodySchema = z.object({
  uploadId: uploadIdSchema,
  mapping: journalMappingSchema,
  sourceSlug: z.string().max(80).optional(),
});

const journalPostBodySchema = z.object({
  // Optional, and resolved through the SAME sanitize-with-default as the
  // stage route — a slug that sanitizes to nothing means the same batches
  // in both places, never two different defaults.
  sourceSlug: z.string().max(80).optional(),
  years: z.array(z.number().int()).optional(),
});

const mappingKindSchema = z.enum(["chart", "journal"], {
  error: 'kind must be "chart" or "journal".',
});

const savedMappingBodySchema = z.object({
  kind: mappingKindSchema,
  name: z.string().trim().min(1, "Give this saved mapping a name.").max(80, "That name is too long — keep it under 80 characters."),
  mapping: z.record(z.string(), z.unknown()),
});

const savedMappingQuerySchema = z.object({
  kind: mappingKindSchema.optional(),
});

// ---------------------------------------------------------------------------
// helpers
// ---------------------------------------------------------------------------

/** 'universal:<slug>' — slug sanitized to [a-z0-9-], defaulting to the
 *  DEFAULT_SLUG constant (see its comment: the default must be stable, never
 *  derived from file contents). The slug is part of the entries' idempotency
 *  identity ((company_id, source, external_ref)), so stage and post both
 *  resolve it through this one function. */
function sanitizeSlug(raw: string | undefined): string {
  const cleaned = (raw ?? "")
    .toLowerCase()
    .replace(/^universal:/, "")
    .replace(/[^a-z0-9]+/g, "-")
    .replace(/^-+|-+$/g, "")
    .slice(0, 40);
  return cleaned !== "" ? cleaned : DEFAULT_SLUG;
}

// ---------------------------------------------------------------------------
// preset resolution (the user's pick decides; header scoring only assists)
// ---------------------------------------------------------------------------

/**
 * How strong the header signal must be before we'll even MENTION a different
 * product than the one the user picked: at least two columns named EXACTLY
 * the way that product names them. Exact matches only — a fuzzy hit on a word
 * as common as "date" or "account" says nothing about which software wrote
 * the file, and piping up on that against a choice the user made deliberately
 * would be noise. (Score alone won't do: matchHints gives 2 per exact and 1
 * per fuzzy, so four fuzzy hits on generic words would clear a score floor of
 * 4 while proving nothing.) Matches the spec's as-built amendment, 2026-08-03.
 */
const MIN_ALTERNATE_EXACT_MATCHES = 2;

function exactMatchCount(match: HintMatch): number {
  return match.guesses.filter((g) => g.confidence === "exact").length;
}

interface PresetPayload {
  key: string;
  label: string;
  description: string;
  likelyKind: "chart" | "journal";
  score: number;
  guesses: HintMatch["guesses"];
}

function presetPayload(
  preset: UniversalPreset, kind: UploadKind, chart: HintMatch, journal: HintMatch,
): PresetPayload {
  const mine = kind === "chart" ? chart : journal;
  return {
    key: preset.key,
    label: preset.label,
    description: preset.description,
    likelyKind: journal.score >= chart.score ? "journal" : "chart",
    score: mine.score,
    guesses: mine.guesses,
  };
}

/**
 * The column guesses this upload starts from, plus (when it exists) the ONE
 * honest second opinion.
 *
 * `presetGuess.source` is the whole point: "picked" means these guesses came
 * from the product the user named, so nothing may claim the file was
 * identified; "detected" means nobody named a product and header scoring
 * chose, which is the only case where "this file looks like a X export" is a
 * statement of fact rather than a guess dressed up as one.
 */
function resolvePresets(headers: string[], kind: UploadKind, presetKey: string | undefined): {
  presetGuess: (PresetPayload & { source: "picked" | "detected" }) | null;
  alternatePreset: PresetPayload | null;
} {
  const detected = matchPreset(headers);

  const picked = presetKey === undefined
    ? undefined
    : UNIVERSAL_PRESETS.find((p) => p.key === presetKey);

  if (picked === undefined) {
    if (detected === null) return { presetGuess: null, alternatePreset: null };
    return {
      presetGuess: {
        ...presetPayload(detected.preset, kind, detected.chart, detected.journal),
        source: "detected",
      },
      alternatePreset: null,
    };
  }

  const chart = matchHints(headers, picked.chartHints);
  const journal = matchHints(headers, picked.journalHints);
  const presetGuess = { ...presetPayload(picked, kind, chart, journal), source: "picked" as const };

  let alternatePreset: PresetPayload | null = null;
  if (detected !== null && detected.preset.key !== picked.key) {
    const theirs = kind === "chart" ? detected.chart : detected.journal;
    if (
      theirs.score > presetGuess.score &&
      exactMatchCount(theirs) >= MIN_ALTERNATE_EXACT_MATCHES
    ) {
      alternatePreset = presetPayload(
        detected.preset, kind, detected.chart, detected.journal,
      );
    }
  }
  return { presetGuess, alternatePreset };
}

function capErrors(errors: RowError[]): { errors: RowError[]; errorTotal: number } {
  return { errors: errors.slice(0, MAX_ERRORS_IN_REPLY), errorTotal: errors.length };
}

/** "1 row" / "3 rows" — developer pluralization ("row(s)") is not English,
 *  and CLAUDE.md §9 wants these read like a person wrote them. */
function count(n: number, singular: string, plural: string): string {
  return `${n} ${n === 1 ? singular : plural}`;
}

function yearReport(r: YearResult, note?: string) {
  return {
    year: r.year,
    batchId: r.batchId,
    status: r.status,
    isNew: r.isNew,
    entries: r.entryCount,
    rows: r.rowCount,
    errors: r.errorCount,
    errorGroups: r.errorGroups,
    voidCount: r.voidCount,
    fullyVoidEntryCount: r.fullyVoidEntryCount,
    ...(note !== undefined ? { note } : {}),
  };
}

/** A posted year's failure, in English: map the ORIGINAL thrown error when
 *  we have it (postYears keeps it as errorCause), never the raw PG text. */
function mappedYearError(y: PostYearResult): string | undefined {
  if (y.errorCause !== undefined) return mapPgError(y.errorCause).message;
  return y.error;
}

async function resolveOwnerOrNull(
  db: Db, companyId: string, log: FastifyRequest["log"],
): Promise<string | null> {
  try {
    return await findOwnerId(db.adminPool, companyId);
  } catch (err) {
    log.error({ err, companyId }, "universal import: findOwnerId failed");
    return null;
  }
}

function isEmptyRow(row: string[]): boolean {
  return row.every((c) => c.trim() === "");
}

/**
 * Builds the staging raw-jsonb provider: each line's raw is the ORIGINAL
 * parsed spreadsheet row ({row: <1-indexed row number>, cells: [...]}) —
 * CLAUDE.md §12, the raw source row kept forever (the QB pipeline stores a
 * reconstruction because its parser consumes the sheet structurally; here
 * the original row is cheaply recoverable, so we keep the real thing).
 *
 * Alignment is pure bookkeeping, not a re-implementation of the engine's
 * grouping: this is only ever called on a file the engine mapped with ZERO
 * errors (stage refuses otherwise), in which case every non-blank data row
 * became exactly one line — key-column groups take their key's rows in file
 * order; consecutive-balanced groups start at the row their entryKey names
 * ("row-N") and run over consecutive non-blank rows. Any mismatch is a bug
 * and throws (aborting that year's transaction) rather than mis-attributing
 * provenance.
 */
function buildRawProvider(
  rows: string[][],
  mapping: UniversalJournalMapping,
  entries: JournalEntry[],
): RawRowProvider {
  interface DataRow { rowNum: number; cells: string[] }
  const dataRows: DataRow[] = [];
  for (let i = mapping.headerRows; i < rows.length; i++) {
    const row = rows[i] ?? [];
    if (isEmptyRow(row)) continue;
    dataRows.push({ rowNum: i + 1, cells: row });
  }

  const rawsByEntry = new Map<JournalEntry, DataRow[]>();
  if (mapping.grouping.kind === "key-column") {
    const keyCol = mapping.grouping.groupKeyCol;
    const byKey = new Map<string, DataRow[]>();
    for (const dr of dataRows) {
      const key = (dr.cells[keyCol] ?? "").trim();
      const list = byKey.get(key) ?? [];
      list.push(dr);
      byKey.set(key, list);
    }
    for (const entry of entries) {
      rawsByEntry.set(entry, byKey.get(entry.entryKey) ?? []);
    }
  } else {
    const indexByRowNum = new Map<number, number>();
    dataRows.forEach((dr, idx) => indexByRowNum.set(dr.rowNum, idx));
    for (const entry of entries) {
      const m = /^row-(\d+)$/.exec(entry.entryKey);
      const start = m ? indexByRowNum.get(Number(m[1])) : undefined;
      rawsByEntry.set(
        entry,
        start === undefined ? [] : dataRows.slice(start, start + entry.lines.length),
      );
    }
  }

  const cursor = new Map<JournalEntry, number>();
  return (entry: JournalEntry, _line: JournalLine): unknown => {
    const idx = cursor.get(entry) ?? 0;
    cursor.set(entry, idx + 1);
    const dr = rawsByEntry.get(entry)?.[idx];
    if (dr === undefined) {
      throw new Error(
        `raw-row provenance mismatch for entry "${entry.entryKey}" (line ${idx + 1}) — this is a bug.`,
      );
    }
    return { row: dr.rowNum, cells: dr.cells };
  };
}

/**
 * Cross-label overlap guard: refuses staging when any of this file's entry
 * keys is already POSTED under a DIFFERENT 'universal:%' source for this
 * company. The content sha catches byte-identical re-uploads; this catches
 * the re-export that was tweaked just enough (a header, a memo) to change
 * the sha while still being the same books — the other way a second import
 * label could double-post. Entries that have been REVERSED are excluded:
 * a rolled-back batch is exactly the case where re-importing elsewhere is
 * legitimate.
 */
async function findOverlappingSources(
  db: Db, companyId: string, source: string, entryKeys: string[],
): Promise<string[]> {
  return db.withCompany(companyId, async (client) => {
    const r = await client.query<{ source: string }>(
      `SELECT DISTINCT e.source FROM journal_entries e
        WHERE e.company_id = $1
          AND e.status = 'posted'
          AND e.source LIKE 'universal:%'
          AND e.source <> $2
          AND e.external_ref = ANY($3::text[])
          AND NOT EXISTS (
            SELECT 1 FROM journal_entries rev
             WHERE rev.company_id = e.company_id AND rev.reverses_entry_id = e.id)
        ORDER BY e.source`,
      [companyId, source, entryKeys]);
    return r.rows.map((row) => row.source);
  });
}

/**
 * Rollback-retry support: when a year's content sha matches a batch of this
 * source that was ROLLED BACK, the re-import is legitimate (rollback exists
 * precisely so a corrected re-import can happen) — but the rolled-back
 * batch still owns both the sha and the posted (now-reversed) entries'
 * external_refs. So the retry year gets a deterministic entry-key suffix
 * ("~r<rolled-back batch id>"), which changes both the keys and (since the
 * sha is computed over entry content) the sha. Loops so a second rollback
 * chains a second suffix. MUTATES the entries in place; call it AFTER the
 * raw-row provider is built (alignment uses the original keys). Returns the
 * per-year note to surface in the stage reply.
 */
async function applyRollbackRetrySalting(
  db: Db, companyId: string, source: string, entriesByYear: Map<number, JournalEntry[]>,
): Promise<Map<number, string>> {
  const notes = new Map<number, string>();
  await db.withCompany(companyId, async (client) => {
    for (const [year, entriesForYear] of entriesByYear) {
      for (;;) {
        const sha = sliceSha(UNIVERSAL_SHA_BASENAME, year, entriesForYear);
        const r = await client.query<{ id: string; status: string }>(
          `SELECT id, status FROM import_batches
            WHERE company_id = $1 AND file_sha256 = $2 AND source = $3`,
          [companyId, sha, source]);
        if (r.rowCount !== 1 || r.rows[0]!.status !== "rolled_back") break;
        const suffix = `~r${r.rows[0]!.id}`;
        for (const entry of entriesForYear) {
          entry.entryKey += suffix;
          for (const line of entry.lines) line.entryKey += suffix;
        }
        notes.set(year, RETRY_NOTE);
      }
    }
  });
  return notes;
}

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

export function registerUniversalImportRoutes(fastify: FastifyInstance, db: Db): void {
  /** Body-validated uploadId -> registry record, or null after replying.
   *  A token for another company (or the other kind, or none at all) is the
   *  same UPLOAD_EXPIRED to the caller — nothing about other tenants leaks. */
  function lookupUpload(
    uploadId: string, companyId: string, kind: UploadKind,
  ): UploadRecord | null {
    const record = uploads.get(uploadId);
    if (!record || record.companyId !== companyId || record.kind !== kind) return null;
    return record;
  }

  // -------------------------------------------------------------------------
  // POST /api/c/universal/upload — multipart: file + kind=chart|journal
  // -------------------------------------------------------------------------
  fastify.post("/api/c/universal/upload", async (request, reply) => {
    const companyId = request.companyId!;

    const read = await readMultipart(request);
    if (isMultipartErr(read)) return reply.code(read.status).send({ error: read.error });

    const kindParsed = mappingKindSchema.safeParse(read.fields.kind);
    if (!kindParsed.success) {
      return reply.code(400).send({
        error: 'Tell us what this file is: send kind="chart" (chart of accounts) or kind="journal" (general ledger).',
      });
    }
    const kind = kindParsed.data;

    // The source product the user picked in the wizard. Optional (an API
    // caller may have nothing to say), but a value we don't recognise is a
    // caller mistake worth naming rather than quietly ignoring — silently
    // falling back to header scoring is exactly the failure this field fixes.
    const rawPresetKey = read.fields.presetKey?.trim();
    const presetKey = rawPresetKey === undefined || rawPresetKey === "" ? undefined : rawPresetKey;
    if (presetKey !== undefined && !UNIVERSAL_PRESETS.some((p) => p.key === presetKey)) {
      return reply.code(400).send({
        error: "We don't know that software — pick one of the options in the wizard.",
      });
    }

    const lower = read.filename.toLowerCase();
    const format: UploadFormat | null =
      lower.endsWith(".csv") ? "csv" : lower.endsWith(".xlsx") ? "xlsx" : null;
    if (format === null) {
      return reply.code(400).send({ error: "Please upload a CSV (.csv) or Excel (.xlsx) file." });
    }
    if (format === "xlsx" &&
        (read.buffer.length < 4 || read.buffer.subarray(0, 4).compare(ZIP_MAGIC) !== 0)) {
      return reply.code(400).send({ error: "That doesn't look like a valid Excel (.xlsx) file." });
    }

    // Save first, parse second — the file is provenance either way (§12),
    // and a parse failure can then always be replayed against the exact
    // bytes. uuid in the name: same concurrency rationale as imports.ts.
    await mkdir(UPLOAD_DIR, { recursive: true });
    const savedId = randomUUID();
    const filePath = path.join(
      UPLOAD_DIR, `${companyId}-${Date.now()}-${savedId}-universal-${kind}.${format}`,
    );
    await writeFile(filePath, read.buffer);

    const record: UploadRecord = {
      companyId, kind, filePath, originalFilename: read.filename, format,
    };

    let rows: string[][];
    try {
      rows = await readUploadRows(record);
    } catch (err) {
      return reply.code(422).send({ error: (err as Error).message });
    }

    const sniff = sniffHeaders(rows, true);
    if (rows.length === 0 || sniff.headers.every((h) => h === "")) {
      return reply.code(422).send({
        error: "That file looks empty — there are no rows to read.",
      });
    }

    const uploadId = registerUpload(record);
    const { presetGuess, alternatePreset } = resolvePresets(sniff.headers, kind, presetKey);

    const base = {
      uploadId,
      kind,
      filename: read.filename,
      headers: sniff.headers,
      columnCount: sniff.columnCount,
      dataStartIndex: sniff.dataStartIndex,
      sampleRows: rows.slice(sniff.dataStartIndex, sniff.dataStartIndex + UPLOAD_SAMPLE_ROWS),
      presetGuess,
      alternatePreset,
    };

    if (kind === "journal") {
      // Sniff the date format on the guessed date column, when there is one.
      const dateGuess = presetGuess?.guesses.find((g) => g.field === "date");
      let dateSniff = null;
      if (dateGuess !== undefined) {
        const values = rows
          .slice(sniff.dataStartIndex, sniff.dataStartIndex + 200)
          .map((r) => r[dateGuess.col] ?? "");
        dateSniff = { column: dateGuess.col, ...sniffDateFormat(values) };
      }
      return reply.send({ ...base, dateSniff });
    }

    // Chart: distinct source-type strings + fuzzy prefill, when a type
    // column was guessed (guesses only — the UI always confirms).
    const typeGuess = presetGuess?.guesses.find((g) => g.field === "type");
    let distinctTypes: string[] = [];
    let typeMapSuggestions: TypeValueMap = {};
    if (typeGuess !== undefined) {
      distinctTypes = collectDistinctTypes(rows, {
        headerRows: sniff.dataStartIndex,
        nameCol: typeGuess.col,
        typeCol: typeGuess.col,
      });
      // Spread onto a plain object: fuzzyPrefillTypeMap returns a
      // null-prototype table, which fastify's JSON serializer handles fine,
      // but a plain object keeps the reply shape unsurprising.
      typeMapSuggestions = { ...fuzzyPrefillTypeMap(distinctTypes) };
    }
    return reply.send({ ...base, distinctTypes, typeMapSuggestions });
  });

  // -------------------------------------------------------------------------
  // POST /api/c/universal/chart/preview — mapping only, no writes
  // -------------------------------------------------------------------------
  fastify.post("/api/c/universal/chart/preview", async (request, reply) => {
    const companyId = request.companyId!;
    const parsed = chartBodySchema.safeParse(request.body ?? {});
    if (!parsed.success) return reply.code(400).send({ error: zodMessage(parsed.error.issues) });

    const record = lookupUpload(parsed.data.uploadId, companyId, "chart");
    if (!record) return reply.code(UPLOAD_EXPIRED.status).send({ error: UPLOAD_EXPIRED.message });

    let rows: string[][];
    try {
      rows = await readUploadRows(record);
    } catch (err) {
      return reply.code(422).send({ error: (err as Error).message });
    }

    try {
      const { accounts, errors } = applyChartMapping(
        rows, parsed.data.mapping as ChartMapping, parsed.data.typeValueMap as TypeValueMap,
      );
      return reply.send({ accounts, ...capErrors(errors) });
    } catch (err) {
      // A structurally invalid mapping (bad column index, parent+fullPath
      // both set) throws — that's a caller mistake, not file data.
      return reply.code(400).send({ error: (err as Error).message });
    }
  });

  // -------------------------------------------------------------------------
  // POST /api/c/universal/chart/commit — accounts + aliases, transactional
  // -------------------------------------------------------------------------
  fastify.post("/api/c/universal/chart/commit", async (request, reply) => {
    const companyId = request.companyId!;
    const parsed = chartBodySchema.safeParse(request.body ?? {});
    if (!parsed.success) return reply.code(400).send({ error: zodMessage(parsed.error.issues) });

    const record = lookupUpload(parsed.data.uploadId, companyId, "chart");
    if (!record) return reply.code(UPLOAD_EXPIRED.status).send({ error: UPLOAD_EXPIRED.message });

    let rows: string[][];
    try {
      rows = await readUploadRows(record);
    } catch (err) {
      return reply.code(422).send({ error: (err as Error).message });
    }

    let accounts;
    let errors: RowError[];
    try {
      ({ accounts, errors } = applyChartMapping(
        rows, parsed.data.mapping as ChartMapping, parsed.data.typeValueMap as TypeValueMap,
      ));
    } catch (err) {
      return reply.code(400).send({ error: (err as Error).message });
    }
    if (errors.length > 0) {
      return reply.code(422).send({
        error: `${count(errors.length, "row", "rows")} in that file ` +
          `${errors.length === 1 ? "has a problem" : "have problems"} — fix them (or the mapping) ` +
          `and try again; nothing was imported.`,
        ...capErrors(errors),
      });
    }
    if (accounts.length === 0) {
      return reply.code(422).send({
        error: "That file has no account rows in it — nothing was imported.",
      });
    }

    const client = await db.adminPool.connect();
    try {
      await client.query("BEGIN");
      // Same advisory lock as the QB chart route: serializes concurrent
      // chart loads for one company so code assignment can't race.
      await client.query(
        "SELECT pg_advisory_xact_lock(hashtext('chart:load:' || $1))", [companyId],
      );

      const result = await loadUniversalChart(client, companyId, accounts);
      if (result.conflicts.length > 0) {
        await client.query("ROLLBACK");
        return reply.code(422).send({
          error: `${count(result.conflicts.length, "account", "accounts")} already ` +
            `${result.conflicts.length === 1 ? "exists" : "exist"} with a different type than ` +
            `this file expects — nothing was imported.`,
          conflicts: result.conflicts,
        });
      }
      if (result.codeProblems.length > 0) {
        await client.query("ROLLBACK");
        return reply.code(422).send({
          error: `${count(result.codeProblems.length, "account number", "account numbers")} ` +
            `in that file ${result.codeProblems.length === 1 ? "clashes" : "clash"} — ` +
            `nothing was imported.`,
          codeProblems: result.codeProblems,
        });
      }

      await client.query("COMMIT");
      return reply.send({
        totalInFile: result.totalInFile,
        accountsCreated: result.accountsCreated,
        accountsSkipped: result.accountsSkipped,
        aliasesCreated: result.aliases.created,
        aliasesSkipped: result.aliases.skipped,
      });
    } catch (err) {
      await client.query("ROLLBACK").catch(() => {});
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "universal chart commit failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    } finally {
      client.release();
    }
  });

  // -------------------------------------------------------------------------
  // POST /api/c/universal/journal/preview — full-file mapping, no staging
  // -------------------------------------------------------------------------
  fastify.post("/api/c/universal/journal/preview", async (request, reply) => {
    const companyId = request.companyId!;
    const parsed = journalPreviewBodySchema.safeParse(request.body ?? {});
    if (!parsed.success) return reply.code(400).send({ error: zodMessage(parsed.error.issues) });

    const record = lookupUpload(parsed.data.uploadId, companyId, "journal");
    if (!record) return reply.code(UPLOAD_EXPIRED.status).send({ error: UPLOAD_EXPIRED.message });

    let rows: string[][];
    try {
      rows = await readUploadRows(record);
    } catch (err) {
      return reply.code(422).send({ error: (err as Error).message });
    }

    let entries: JournalEntry[];
    let errors: RowError[];
    try {
      ({ entries, errors } = applyJournalMapping(rows, parsed.data.mapping as UniversalJournalMapping));
    } catch (err) {
      return reply.code(400).send({ error: (err as Error).message });
    }

    let lineCount = 0;
    let minDate: string | null = null;
    let maxDate: string | null = null;
    for (const entry of entries) {
      lineCount += entry.lines.length;
      if (minDate === null || entry.dateIso < minDate) minDate = entry.dateIso;
      if (maxDate === null || entry.dateIso > maxDate) maxDate = entry.dateIso;
    }
    const yearBreakdown = [...groupEntriesByYear(entries).entries()]
      .map(([year, list]) => ({
        year,
        entries: list.length,
        lines: list.reduce((n, e) => n + e.lines.length, 0),
      }))
      .sort((a, b) => a.year - b.year);

    return reply.send({
      entryCount: entries.length,
      lineCount,
      dateRange: minDate === null ? null : { from: minDate, to: maxDate },
      yearBreakdown,
      ...capErrors(errors),
    });
  });

  // -------------------------------------------------------------------------
  // POST /api/c/universal/journal/stage — per-year batches into the pipeline
  // -------------------------------------------------------------------------
  fastify.post("/api/c/universal/journal/stage", async (request, reply) => {
    const companyId = request.companyId!;
    const parsed = journalStageBodySchema.safeParse(request.body ?? {});
    if (!parsed.success) return reply.code(400).send({ error: zodMessage(parsed.error.issues) });

    const record = lookupUpload(parsed.data.uploadId, companyId, "journal");
    if (!record) return reply.code(UPLOAD_EXPIRED.status).send({ error: UPLOAD_EXPIRED.message });

    let rows: string[][];
    try {
      rows = await readUploadRows(record);
    } catch (err) {
      return reply.code(422).send({ error: (err as Error).message });
    }

    const mapping = parsed.data.mapping as UniversalJournalMapping;
    let entries: JournalEntry[];
    let errors: RowError[];
    try {
      ({ entries, errors } = applyJournalMapping(rows, mapping));
    } catch (err) {
      return reply.code(400).send({ error: (err as Error).message });
    }
    if (errors.length > 0) {
      return reply.code(422).send({
        error: `${count(errors.length, "row", "rows")} in that file ` +
          `${errors.length === 1 ? "has a problem" : "have problems"} — nothing was staged.`,
        ...capErrors(errors),
      });
    }
    if (entries.length === 0) {
      return reply.code(422).send({
        error: "No entries were found in that file — nothing was staged.",
      });
    }

    const slug = sanitizeSlug(parsed.data.sourceSlug);
    const source = `universal:${slug}`;

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

    try {
      // Cross-label double-post guard (uses the ORIGINAL entry keys).
      const overlapping = await findOverlappingSources(
        db, companyId, source, entries.map((e) => e.entryKey));
      if (overlapping.length > 0) {
        return reply.code(409).send({
          error: `Some of these entries were already imported under a different import name ` +
            `(${overlapping.join(", ")}) — if this is a re-import, roll back that batch first.`,
        });
      }

      // Raw-row alignment FIRST (it matches on the original keys), then the
      // rollback-retry salting, which may rewrite keys in place.
      const entriesByYear = groupEntriesByYear(entries);
      const rawForLine = buildRawProvider(rows, mapping, entries);
      const retryNotes = await applyRollbackRetrySalting(db, companyId, source, entriesByYear);

      const results = await loadJournalYears(db.appPool, {
        companyId,
        ownerId,
        filename: record.originalFilename,
        entriesByYear,
        source,
        rawForLine,
        shaBasename: UNIVERSAL_SHA_BASENAME,
      });

      // Every year already loaded AND none of them re-validatable (all
      // posted/rolled back): re-staging this exact file has nothing to do.
      // Any re-validatable year keeps the QB fix loop working — add the
      // missing account/alias, re-stage the same file, watch it go clean.
      if (results.every((r) => !r.isNew && !shouldRevalidate(r.status))) {
        return reply
          .code(UNIVERSAL_FILE_ALREADY_IMPORTED.status)
          .send({ error: UNIVERSAL_FILE_ALREADY_IMPORTED.message });
      }

      return reply.send({
        source,
        years: results.map((r) => yearReport(r, retryNotes.get(r.year))),
      });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "universal journal stage failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // POST /api/c/universal/journal/post {sourceSlug, years?} — the post gate
  // -------------------------------------------------------------------------
  fastify.post("/api/c/universal/journal/post", async (request, reply) => {
    const companyId = request.companyId!;
    const parsed = journalPostBodySchema.safeParse(request.body ?? {});
    if (!parsed.success) return reply.code(400).send({ error: zodMessage(parsed.error.issues) });

    const slug = sanitizeSlug(parsed.data.sourceSlug);
    const source = `universal:${slug}`;

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

    let results: YearResult[];
    try {
      results = await loadExistingYearResults(db.appPool, companyId, parsed.data.years, source);
    } catch (err) {
      return reply.code(404).send({ error: (err as Error).message });
    }
    if (results.length === 0) {
      return reply.code(404).send({
        error: "No staged batches were found for that source — stage the journal first.",
      });
    }

    try {
      const postResult = await postYears(db.appPool, companyId, ownerId, results);

      if (!postResult.ok && postResult.dirtyYears.length > 0) {
        return reply.code(409).send({
          error: `${count(postResult.dirtyYears.length, "year", "years")} still ` +
            `${postResult.dirtyYears.length === 1 ? "has" : "have"} validation errors and can't ` +
            `be posted: ${postResult.dirtyYears.join(", ")}.`,
          dirtyYears: postResult.dirtyYears,
        });
      }

      // A year whose post_batch itself threw: map the ORIGINAL error to
      // English (e.g. the (company_id, source, external_ref) uniqueness —
      // same entries re-staged from a different file — maps to a 409).
      const failed = postResult.years.find((y) => y.errorCause !== undefined);
      if (failed !== undefined) {
        const mapped = mapPgError(failed.errorCause);
        if (mapped.status === 500) {
          request.log.error({ err: failed.errorCause }, "universal journal post failed");
        }
        return reply.code(mapped.status).send({
          error: mapped.message,
          years: postResult.years.map((y) => ({
            year: y.year, batchId: y.batchId, posted: y.posted,
            alreadyPosted: y.alreadyPosted, error: mappedYearError(y),
          })),
        });
      }

      return reply.send({
        years: postResult.years.map((y) => ({
          year: y.year,
          batchId: y.batchId,
          posted: y.posted,
          alreadyPosted: y.alreadyPosted,
          reconciliation: y.reconciliation,
        })),
      });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "universal journal post failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // GET /api/c/universal/mappings[?kind=chart|journal] — list (load = pick)
  // -------------------------------------------------------------------------
  fastify.get("/api/c/universal/mappings", async (request, reply) => {
    const companyId = request.companyId!;
    const parsed = savedMappingQuerySchema.safeParse(request.query ?? {});
    if (!parsed.success) return reply.code(400).send({ error: zodMessage(parsed.error.issues) });
    const kind = parsed.data.kind;

    try {
      const mappings = await db.withCompany(companyId, async (client) => {
        const res = kind === undefined
          ? await client.query(
            `SELECT id, kind, name, mapping, updated_at FROM universal_mappings
              WHERE company_id = $1 ORDER BY kind, name`,
            [companyId])
          : await client.query(
            `SELECT id, kind, name, mapping, updated_at FROM universal_mappings
              WHERE company_id = $1 AND kind = $2 ORDER BY name`,
            [companyId, kind]);
        return res.rows.map((r) => ({
          id: Number(r.id),
          kind: r.kind as string,
          name: r.name as string,
          mapping: r.mapping as Record<string, unknown>,
          updatedAt: r.updated_at as Date,
        }));
      });
      return reply.send({ mappings });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "universal mappings list failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // POST /api/c/universal/mappings {kind, name, mapping} — save by name
  // -------------------------------------------------------------------------
  fastify.post("/api/c/universal/mappings", async (request, reply) => {
    const companyId = request.companyId!;
    const parsed = savedMappingBodySchema.safeParse(request.body ?? {});
    if (!parsed.success) return reply.code(400).send({ error: zodMessage(parsed.error.issues) });
    const { kind, name, mapping } = parsed.data;

    const serialized = JSON.stringify(mapping);
    if (serialized.length > 20_000) {
      return reply.code(400).send({ error: "That mapping is too large to save." });
    }

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

    try {
      await db.withCompany(companyId, async (client) => {
        await client.query(
          `INSERT INTO universal_mappings (company_id, kind, name, mapping, updated_by)
           VALUES ($1, $2, $3, $4::jsonb, $5)
           ON CONFLICT (company_id, kind, name)
           DO UPDATE SET mapping = EXCLUDED.mapping, updated_by = EXCLUDED.updated_by,
                         updated_at = now()`,
          [companyId, kind, name, serialized, ownerId],
        );
      });
      return reply.send({ kind, name, mapping });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "universal mapping save failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });
}
