// Setup/migration upload endpoints — Task 4 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): upload the QuickBooks Account Listing, upload the
// QuickBooks Journal, and post the validated years. Every DB-flow step
// reuses the SAME tools/lib/{chart,journal}-load.ts functions the chart:load
// and qb:load CLIs use (one code path, two frontends — see the design spec).
//
// Uploaded files are saved to imports/uploads/ (gitignored) purely for
// provenance/debugging — the parse+load happens against the saved file, not
// the in-memory buffer, so a failure can always be replayed by hand with the
// CLI against the exact bytes that were uploaded.
//
// journal-load.ts's CONNECTION CONTRACT is binding here: loadJournalYears/
// postYears/loadExistingYearResults take db.appPool directly (never
// db.withCompany, which would hand them an already-open transaction) — they
// manage their own per-year sessions/transactions.

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 { z } from "zod";
import type { Db } from "./db.js";
import { mapPgError } from "./errors.js";
import { readQbSheet } from "../../tools/qb-xlsx.js";
import { parseListingRows, findDuplicateNames, planAccounts } from "../../tools/chart-logic.js";
import { parseJournalRows, groupEntriesByYear } from "../../tools/journal-logic.js";
import { loadChart } from "../../tools/lib/chart-load.js";
import {
  findOwnerId,
  loadJournalYears,
  loadExistingYearResults,
  postYears,
  type YearResult,
} from "../../tools/lib/journal-load.js";

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

// Task 4 (docs/superpowers/plans/2026-07-31-local-app.md): 25MB cap on
// uploaded QuickBooks xlsx exports. Exported so app.ts can pass the SAME
// number to @fastify/multipart's plugin-level limit (which enforces it
// during the stream read, before receiveXlsxUpload ever sees the bytes).
export const MAX_UPLOAD_BYTES = 25 * 1024 * 1024;
// A .xlsx file is a zip archive — every zip starts with this local-file-header
// signature. Checking it (not just the .xlsx extension) catches a renamed
// file of some other type before we ever hand it to ExcelJS.
const ZIP_MAGIC = Buffer.from([0x50, 0x4b, 0x03, 0x04]);

interface UploadOk {
  filePath: string;
  originalFilename: string;
}
interface UploadErr {
  status: number;
  error: string;
}

function isUploadErr(u: UploadOk | 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 one file." };
  }
  return null;
}

/**
 * Reads a single ".xlsx" file from a multipart request's "file" field,
 * validates it (extension + zip magic bytes), and saves it under
 * imports/uploads/<companyId>-<timestamp>-<uuid>-<suffix>.xlsx. Never
 * touches the database — purely file receipt + validation.
 *
 * Retention: these saved files are provenance, not a cache — CLAUDE.md §12
 * ("keep the raw source row for everything imported, forever") — so nothing
 * here ever deletes or overwrites an upload. They're kept indefinitely by
 * design; a retention/cleanup policy (if one is ever wanted) is a separate,
 * deliberate decision, not a side effect of this code.
 */
async function receiveXlsxUpload(
  request: FastifyRequest, companyId: string, suffix: "chart" | "journal",
): Promise<UploadOk | 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 });
  // A uuid is required here, not just decoration: two uploads for the same
  // company landing in the same millisecond (concurrent requests, or a fast
  // retry) would otherwise share a filename — the second writeFile() would
  // silently clobber the first mid-parse, corrupting BOTH requests' results.
  const filePath = path.join(UPLOAD_DIR, `${companyId}-${Date.now()}-${randomUUID()}-${suffix}.xlsx`);
  await writeFile(filePath, buffer);

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

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

async function resolveOwnerOrNull(
  db: Db, companyId: string, log: FastifyRequest["log"],
): Promise<string | null> {
  try {
    return await findOwnerId(db.adminPool, companyId);
  } catch (err) {
    // findOwnerId() throws both for "genuinely no owner" (the expected,
    // shouldn't-happen-but-handled case below) AND for any underlying DB
    // fault (connection drop, permissions, etc.) — those are very different
    // problems, and collapsing both to a silent `null` would make a real
    // outage indistinguishable from a data-integrity gap. Log the real
    // error here so it's diagnosable; the caller still gets a clean null.
    log.error({ err, companyId }, "resolveOwnerOrNull: findOwnerId failed");
    return null;
  }
}

function yearReport(r: YearResult) {
  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,
  };
}

const postBodySchema = z.object({
  years: z.array(z.number().int()).optional(),
});

export function registerImportRoutes(fastify: FastifyInstance, db: Db): void {
  // -------------------------------------------------------------------------
  // POST /api/c/import/chart — QuickBooks Account Listing xlsx
  // -------------------------------------------------------------------------
  fastify.post("/api/c/import/chart", async (request, reply) => {
    const companyId = request.companyId!;

    const upload = await receiveXlsxUpload(request, companyId, "chart");
    if (isUploadErr(upload)) {
      return reply.code(upload.status).send({ error: upload.error });
    }

    let listingRows;
    try {
      const sheetRows = await readQbSheet(upload.filePath);
      listingRows = parseListingRows(sheetRows);
    } catch (err) {
      return reply.code(422).send({ error: (err as Error).message });
    }
    if (listingRows.length === 0) {
      return reply.code(422).send({ error: "That file has no account rows in it — nothing was imported." });
    }

    const duplicateNames = findDuplicateNames(listingRows);
    if (duplicateNames.length > 0) {
      return reply.code(422).send({
        error: `${duplicateNames.length} account name(s) appear more than once in the file — nothing was imported.`,
        duplicateNames,
      });
    }

    // Validation-only pass, same as the CLI: tells us whether every
    // QuickBooks type in the file is recognized, before any database work.
    const preliminaryPlan = planAccounts(listingRows);
    if (preliminaryPlan.unknownTypes.length > 0) {
      return reply.code(422).send({
        error: `${preliminaryPlan.unknownTypes.length} account(s) have a QuickBooks type we don't recognize — nothing was imported.`,
        unknownTypes: preliminaryPlan.unknownTypes,
      });
    }

    const client = await db.adminPool.connect();
    try {
      await client.query("BEGIN");
      // Advisory lock pattern copied from ensureCompanyAndOwner
      // (tools/lib/chart-load.ts) — serializes concurrent chart loads for
      // the same company so two simultaneous uploads can't race each other's
      // code assignment.
      await client.query(
        "SELECT pg_advisory_xact_lock(hashtext('chart:load:' || $1))", [companyId],
      );

      const chart = await loadChart(client, companyId, listingRows);
      if (chart.conflicts.length > 0) {
        await client.query("ROLLBACK");
        return reply.code(422).send({
          error: `${chart.conflicts.length} account(s) already exist with a different type than this file expects — nothing was imported.`,
          conflicts: chart.conflicts,
        });
      }

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

  // -------------------------------------------------------------------------
  // POST /api/c/import/journal — QuickBooks Journal xlsx
  // -------------------------------------------------------------------------
  fastify.post("/api/c/import/journal", async (request, reply) => {
    const companyId = request.companyId!;

    const upload = await receiveXlsxUpload(request, companyId, "journal");
    if (isUploadErr(upload)) {
      return reply.code(upload.status).send({ error: upload.error });
    }

    let entries;
    try {
      const sheetRows = await readQbSheet(upload.filePath);
      entries = parseJournalRows(sheetRows);
    } catch (err) {
      return reply.code(422).send({ error: (err as Error).message });
    }
    if (entries.length === 0) {
      return reply.code(422).send({ error: "No entries were found in that file — nothing was imported." });
    }

    const ownerId = await resolveOwnerOrNull(db, companyId, request.log);
    if (ownerId === null) {
      // The underlying cause (genuinely no owner vs. a DB fault) is already
      // logged at .error level inside resolveOwnerOrNull.
      return reply.code(NO_OWNER_ERROR.status).send({ error: NO_OWNER_ERROR.error });
    }

    const entriesByYear = groupEntriesByYear(entries);
    try {
      const results = await loadJournalYears(db.appPool, {
        companyId, ownerId, filename: upload.originalFilename, entriesByYear,
      });
      // 200 even with per-row validation errors present — the report IS the
      // product; the UI (or a human) decides what to do about dirty years.
      return reply.send({ years: results.map(yearReport) });
    } catch (err) {
      const mapped = mapPgError(err);
      if (mapped.status === 500) request.log.error({ err }, "journal import failed");
      return reply.code(mapped.status).send({ error: mapped.message });
    }
  });

  // -------------------------------------------------------------------------
  // POST /api/c/import/post {years?} — the --post path
  // -------------------------------------------------------------------------
  fastify.post("/api/c/import/post", async (request, reply) => {
    const companyId = request.companyId!;
    const parsed = postBodySchema.safeParse(request.body ?? {});
    if (!parsed.success) {
      return reply.code(400).send({ error: parsed.error.issues.map((i) => i.message).join("; ") });
    }
    const years = parsed.data.years;

    const ownerId = await resolveOwnerOrNull(db, companyId, request.log);
    if (ownerId === null) {
      // The underlying cause (genuinely no owner vs. a DB fault) is already
      // logged at .error level inside resolveOwnerOrNull.
      return reply.code(NO_OWNER_ERROR.status).send({ error: NO_OWNER_ERROR.error });
    }

    let results: YearResult[];
    try {
      results = await loadExistingYearResults(db.appPool, companyId, years);
    } catch (err) {
      return reply.code(404).send({ error: (err as Error).message });
    }

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

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

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