// Backups — Task 7 of docs/superpowers/plans/2026-07-31-local-app.md, per
// docs/superpowers/specs/2026-07-31-local-app-design.md ("Backups").
//
// A whole-database gzipped pg_dump, written to a configurable directory
// (default %USERPROFILE%/Documents/Accounting Backups — outside the repo and
// outside the application-data folder the cluster lives in, so wiping either
// one does not take the backups with it), with a 30-file retention and a "run
// once a day on server start" policy. This module shares
// server/src/db.ts's connection RESOLUTION (resolveDatabaseUrls) but not its
// pools: pg_dump needs superuser-equivalent access to dump every table
// regardless of RLS, and it shells out to a different mechanism entirely (a
// subprocess, not `pg`), so there is nothing else to share.
//
// FINDING pg_dump USED TO BE A SEARCH. It no longer is. Before PG Task 3
// (docs/todo/2026-08-08-bundled-postgres-decisions.md) this machine had no
// pg_dump at all, so resolveDumpCommand() probed for `docker` on PATH and then
// for Docker Desktop's per-user install path and ran `docker exec accounting-db
// pg_dump`. We now ship pg_dump ourselves, at an absolute path we chose — which
// is one of the two reasons zonky.io's three-executable PostgreSQL build was
// rejected in the first place. So the bundled binary is preferred, and the only
// remaining fallback is a `pg_dump` on PATH, for the developer who has pointed
// DATABASE_URL at their own PostgreSQL and never installed our runtime.
//
// Never a partial file under the final name: the dump streams into
// `<name>.sql.gz.tmp` and is renamed to `<name>.sql.gz` only after pg_dump
// exits 0 AND the output is at least MIN_BACKUP_BYTES — any failure deletes
// the .tmp file. rename() on the same volume is atomic, so a reader of the
// backups directory never observes a half-written file at the final name.

import { spawn } from "node:child_process";
import { existsSync } from "node:fs";
import { mkdir, readdir, rename, rm, stat as fsStat } from "node:fs/promises";
import { createWriteStream } from "node:fs";
import { createGzip } from "node:zlib";
import { pipeline } from "node:stream/promises";
import { randomUUID } from "node:crypto";
import path from "node:path";
import os from "node:os";

import { resolveCurrentLayout } from "./localdb/runtime.js";
import { binaryPath } from "./localdb/paths.js";
import { resolveDatabaseUrls } from "./db.js";

// ---------------------------------------------------------------------------
// filename convention + retention (pure logic — unit-testable without I/O)
// ---------------------------------------------------------------------------

/** accounting-YYYYMMDD-HHMMSS.sql.gz — captures its own timestamp fields so
 *  parseBackupFilenameTimestamp() can recover "when this backup was really
 *  taken" straight from the name, independent of the file's on-disk mtime
 *  (which a copied-in file — e.g. restored from another machine, or moved
 *  onto this one — does NOT reliably preserve). */
const FILENAME_RE = /^accounting-(\d{4})(\d{2})(\d{2})-(\d{2})(\d{2})(\d{2})\.sql\.gz$/;

export const DEFAULT_RETENTION_KEEP = 30;
export const MIN_BACKUP_BYTES = 1024; // 1KB — anything smaller is treated as a failed/empty dump.
const DAILY_THRESHOLD_HOURS = 20;

export function isBackupFilename(name: string): boolean {
  return FILENAME_RE.test(name);
}

/**
 * Recovers the timestamp embedded in a backup filename (as local-time
 * milliseconds-since-epoch), or null if `name` doesn't match our naming
 * convention at all. This is the SOURCE OF TRUTH for "how old is this
 * backup" — not the file's mtime, which a file copied in from elsewhere
 * (another machine's backup folder, a restored-from-cloud-storage copy,
 * etc.) can carry a fresh "just now" value for even though the backup
 * itself is old. mtime is only ever used as a tiebreak between two backups
 * whose filenames encode the identical second.
 */
export function parseBackupFilenameTimestamp(name: string): number | null {
  const m = FILENAME_RE.exec(name);
  if (!m) return null;
  const [, y, mo, d, h, mi, s] = m;
  return new Date(Number(y), Number(mo) - 1, Number(d), Number(h), Number(mi), Number(s)).getTime();
}

function pad(n: number, width = 2): string {
  return String(n).padStart(width, "0");
}

/** Builds the timestamped filename for `d` (local time — this is a
 *  single-machine, single-timezone local app; there is no cross-timezone
 *  audience for the filename). */
export function backupFilename(d: Date): string {
  const yyyy = d.getFullYear();
  const mm = pad(d.getMonth() + 1);
  const dd = pad(d.getDate());
  const HH = pad(d.getHours());
  const MM = pad(d.getMinutes());
  const SS = pad(d.getSeconds());
  return `accounting-${yyyy}${mm}${dd}-${HH}${MM}${SS}.sql.gz`;
}

export interface BackupFileStat {
  name: string;
  mtimeMs: number;
}

/** The timestamp retention/"last backup" ordering treats as authoritative:
 *  the one parsed from the filename, falling back to mtime only for a name
 *  that (somehow) doesn't match our convention at all — defensive, since
 *  every real caller already filters through isBackupFilename() first. */
function effectiveTimestamp(f: BackupFileStat): number {
  return parseBackupFilenameTimestamp(f.name) ?? f.mtimeMs;
}

/**
 * Pure retention logic: given every backup file's name + mtime and how many
 * to keep, returns the names to delete — the oldest ones beyond `keep`.
 * Ordered by the TIMESTAMP EMBEDDED IN THE FILENAME, not mtime — a backup
 * file copied in from elsewhere (a fresh mtime from the copy, but an old
 * filename date) must still be recognized as old, or retention would keep
 * the wrong files and delete genuinely-current ones. mtime is only a
 * tiebreak for two filenames encoding the identical second; filename
 * (lexicographic, which matches chronological for this naming scheme) is
 * the final tiebreak after that, for full determinism.
 */
export function selectFilesToDelete(files: BackupFileStat[], keep: number): string[] {
  const sorted = [...files].sort((a, b) =>
    effectiveTimestamp(a) - effectiveTimestamp(b) || a.mtimeMs - b.mtimeMs || a.name.localeCompare(b.name));
  const excess = sorted.length - keep;
  if (excess <= 0) return [];
  return sorted.slice(0, excess).map((f) => f.name);
}

// ---------------------------------------------------------------------------
// directory + listing helpers
// ---------------------------------------------------------------------------

/** Default: %USERPROFILE%/Documents/Accounting Backups. BACKUP_DIR overrides. */
export function defaultBackupDir(): string {
  const override = process.env.BACKUP_DIR;
  if (override && override.trim() !== "") return override;
  const home = process.env.USERPROFILE ?? os.homedir();
  return path.join(home, "Documents", "Accounting Backups");
}

async function listBackupFileStats(dir: string): Promise<BackupFileStat[]> {
  const entries = await readdir(dir).catch(() => [] as string[]);
  const stats: BackupFileStat[] = [];
  for (const name of entries) {
    if (!isBackupFilename(name)) continue;
    try {
      const s = await fsStat(path.join(dir, name));
      stats.push({ name, mtimeMs: s.mtimeMs });
    } catch {
      // Vanished between readdir and stat (e.g. a concurrent retention
      // sweep) — nothing meaningful to report, just skip it.
    }
  }
  return stats;
}

export interface LastBackupInfo {
  file: string;
  /** The backup's effective timestamp — parsed from its filename (falling
   *  back to mtime only for a non-conforming name), NOT necessarily the
   *  file's on-disk mtime. See effectiveTimestamp()'s note on why. */
  mtime: Date;
}

/** Newest matching file in `dir` (by filename timestamp, mtime as tiebreak
 *  — see selectFilesToDelete's note), or null if there are none (or the
 *  directory doesn't exist yet). */
export async function lastBackupInfo(dir: string): Promise<LastBackupInfo | null> {
  const stats = await listBackupFileStats(dir);
  if (stats.length === 0) return null;
  const newest = stats.reduce((a, b) => {
    const ta = effectiveTimestamp(a);
    const tb = effectiveTimestamp(b);
    if (tb !== ta) return tb > ta ? b : a;
    return b.mtimeMs > a.mtimeMs ? b : a;
  });
  return { file: newest.name, mtime: new Date(effectiveTimestamp(newest)) };
}

export async function countBackups(dir: string): Promise<number> {
  return (await listBackupFileStats(dir)).length;
}

async function applyRetention(dir: string, keep: number = DEFAULT_RETENTION_KEEP): Promise<void> {
  const stats = await listBackupFileStats(dir);
  const toDelete = selectFilesToDelete(stats, keep);
  for (const name of toDelete) {
    await rm(path.join(dir, name), { force: true }).catch(() => {
      // Best effort — a retention delete failing shouldn't fail the backup
      // that just succeeded.
    });
  }
}

// ---------------------------------------------------------------------------
// resolveDumpCommand — locates a pg_dump to run against this machine's
// Postgres. See the file header for why this is now two candidates and not
// three.
// ---------------------------------------------------------------------------

export type DumpCommand =
  /** The pg_dump inside the runtime we installed. The normal answer. */
  | { kind: "bundled"; executable: string }
  /** A pg_dump the developer already had. The only remaining fallback. */
  | { kind: "path"; executable: string };

const PG_DUMP_ON_PATH = "pg_dump";

/** The absolute path to the bundled pg_dump, or null when this machine can't
 *  even be asked where that would be — an unsupported platform, or a Windows
 *  session with no LOCALAPPDATA. resolveCurrentLayout() throws for both, and
 *  neither is a reason to fail a backup outright while a pg_dump on PATH might
 *  still work, so the answer is "no bundled candidate" rather than an error. */
function defaultBundledPgDumpPath(): string | null {
  try {
    return binaryPath(resolveCurrentLayout(), "pg_dump");
  } catch {
    return null;
  }
}

/** Spawns `cmd --version` and resolves true iff it started and exited 0.
 *  Any failure to start (ENOENT — not on PATH, not installed) resolves
 *  false rather than throwing; a non-zero exit also resolves false, since
 *  either way `cmd` isn't a usable dump mechanism. */
function checkExecutable(cmd: string): Promise<boolean> {
  return new Promise((resolve) => {
    let settled = false;
    const finish = (ok: boolean) => {
      if (settled) return;
      settled = true;
      resolve(ok);
    };
    let child;
    try {
      child = spawn(cmd, ["--version"], { stdio: "ignore", windowsHide: true });
    } catch {
      finish(false);
      return;
    }
    child.on("error", () => finish(false));
    child.on("close", (code) => finish(code === 0));
  });
}

export interface ResolveDumpCommandOptions {
  /** Injectable for tests — real callers never pass this. */
  checkExecutable?: (cmd: string) => Promise<boolean>;
  /** Injectable for tests — real callers never pass this. */
  existsSync?: (p: string) => boolean;
  /** Injectable for tests — real callers never pass this. `null` means "this
   *  machine has no bundled runtime path at all", which is a different
   *  situation from "the path exists but the file isn't there yet". */
  bundledPgDumpPath?: string | null;
}

/**
 * Prefers the bundled pg_dump, and falls back to one on PATH.
 *
 * The bundled binary is EXISTENCE-CHECKED, not run. Spawning it with
 * `--version` on every backup would cost a process launch to learn something we
 * already know: we put it there, we verified the archive's SHA-256 before
 * unpacking it, and ensureRuntime() re-checks all nine executables on every
 * launch. A pg_dump on PATH gets the opposite treatment — it IS run, because we
 * know nothing about it, and on Windows "a file called pg_dump.exe is on PATH"
 * and "it starts" are genuinely different claims.
 *
 * Bundled first, deliberately. A developer with their own PostgreSQL on PATH
 * still has ours installed, and ours is the version that matches the cluster
 * holding the books; pg_dump refuses outright to dump a server NEWER than
 * itself, so preferring an unknown, possibly older PATH binary would be a
 * failure waiting for the next major version.
 *
 * Throws a loud English error only when neither exists — callers (runBackup,
 * and therefore the /api/backup route) surface that message directly, so it
 * must already read as user-facing guidance, not a stack trace.
 */
export async function resolveDumpCommand(opts: ResolveDumpCommandOptions = {}): Promise<DumpCommand> {
  const check = opts.checkExecutable ?? checkExecutable;
  const exists = opts.existsSync ?? existsSync;
  const bundled = opts.bundledPgDumpPath !== undefined
    ? opts.bundledPgDumpPath
    : defaultBundledPgDumpPath();

  if (bundled !== null && exists(bundled)) {
    return { kind: "bundled", executable: bundled };
  }
  if (await check(PG_DUMP_ON_PATH)) {
    return { kind: "path", executable: PG_DUMP_ON_PATH };
  }

  throw new Error(
    "Can't back up your books: the copy of PostgreSQL that Balanced Sum installed is missing its backup tool " +
    "(pg_dump), and there isn't another one on this computer. Close Balanced Sum and open it again — it will " +
    "reinstall what's missing. If that doesn't help, get in touch.",
  );
}

// ---------------------------------------------------------------------------
// runBackup
// ---------------------------------------------------------------------------

/**
 * The command line for one dump. Both kinds of DumpCommand take the identical
 * form, and that is the whole shape of what Docker removal bought here: a
 * container needed `docker exec <name> pg_dump -U <user> <dbname>`, with the
 * user and database picked back out of the URL and the password left to
 * whatever the container's environment happened to be. A real pg_dump accepts
 * the connection URI as its dbname argument, which carries host, port, user,
 * password and database in one string — nothing to take apart, no PGPASSWORD to
 * plumb, and no second place for the generated superuser password to end up.
 *
 * Exported for testing only. The URI is passed as an argv element to a spawn
 * with no shell, so the password in it is never handed to a command
 * interpreter; it is still visible in this machine's process list for the life
 * of the dump, which is the same exposure `psql <uri>` has always had and is
 * acceptable on a single-user local install.
 */
export function buildDumpArgs(
  dumpCommand: DumpCommand, databaseUrl: string,
): { command: string; args: string[] } {
  return { command: dumpCommand.executable, args: [databaseUrl] };
}

/** Builds a collision-proof `.tmp` path for `finalPath`: includes this
 *  process's pid AND a random UUID, so two backups racing for the SAME
 *  final filename (e.g. two started within the same second, from two
 *  separate `npm run app` instances, or a CLI run alongside the server —
 *  the in-process runBackup() lock below only protects against the
 *  SAME-process case) never write to the same `.tmp` file. Before this,
 *  two concurrent dumps to an identical deterministic `<final>.tmp` path
 *  could interleave their writes into one corrupt file that still passed
 *  the size check and got renamed into place as if it had succeeded. */
export function makeTmpPath(finalPath: string): string {
  return `${finalPath}.${process.pid}.${randomUUID()}.tmp`;
}

/** Windows reports this exact value (0xFFFFFFFF as an unsigned 32-bit exit
 *  code) when a child process was killed or crashed rather than exiting
 *  normally — "pg_dump exited with code 4294967295" reads as meaningless
 *  noise to a user, so it gets its own, plainer message. */
const ABNORMAL_EXIT_CODE = 4294967295;

/** Pure — the exact wording for a failed (non-zero-exit) pg_dump run.
 *  Exported for direct unit testing of the ABNORMAL_EXIT_CODE mapping
 *  without needing to actually crash a child process. */
export function formatDumpFailureMessage(exitCode: number | null, stderr: string): string {
  const codeMessage = exitCode === ABNORMAL_EXIT_CODE
    ? `pg_dump was interrupted before finishing (exit code ${exitCode}).`
    : `pg_dump exited with code ${exitCode ?? "unknown"}.`;
  const details = stderr.trim() ? ` Details: ${stderr.trim()}.` : "";
  return `Backup failed: ${codeMessage}${details} Nothing was saved.`;
}

interface SpawnDumpResult {
  exitCode: number | null;
  stderr: string;
}

/** Spawns pg_dump, streaming its stdout through gzip straight to `tmpPath`.
 *  Resolves once both the process has exited AND the gzip/write pipeline
 *  has fully flushed — never just one or the other, since either "process
 *  exited but stream still draining" or "stream done but exit code unread"
 *  would let a caller act on an incomplete picture. */
async function spawnDump(command: string, args: string[], tmpPath: string): Promise<SpawnDumpResult> {
  const child = spawn(command, args, { stdio: ["ignore", "pipe", "pipe"] });

  const stderrChunks: Buffer[] = [];
  child.stderr?.on("data", (chunk: Buffer) => stderrChunks.push(chunk));

  const gzip = createGzip();
  const outStream = createWriteStream(tmpPath);
  const pipelinePromise = pipeline(child.stdout!, gzip, outStream);

  const exitPromise = new Promise<number | null>((resolve, reject) => {
    child.on("error", reject);
    child.on("close", (code) => resolve(code));
  });

  const [exitCode] = await Promise.all([exitPromise, pipelinePromise]);
  return { exitCode, stderr: Buffer.concat(stderrChunks).toString("utf8") };
}

export interface RunBackupOptions {
  /** Directory to write into. Defaults to defaultBackupDir(). */
  dir?: string;
  /** Overrides DATABASE_URL — used by tests. */
  databaseUrl?: string;
  /** Overrides "now" for a deterministic filename — used by tests. */
  now?: () => Date;
  /** How many backups to keep after this one succeeds. */
  retentionKeep?: number;
  /** Test-only: bypasses resolveDumpCommand()/buildDumpArgs entirely and
   *  spawns this command+args directly. Lets tests exercise runBackup's
   *  concurrency guard, tmp-file handling, and retention against a fast,
   *  deterministic fake "pg_dump" instead of shelling out to a real one
   *  against a real Postgres. Real callers (the API route, maybeDailyBackup)
   *  never set this. */
  commandOverride?: { command: string; args: string[] };
}

export interface RunBackupResult {
  file: string;
  bytes: number;
  durationMs: number;
}

export const BACKUP_ALREADY_RUNNING_MESSAGE = "A backup is already running — wait for it to finish.";

/** Set only while a backup is actually in progress IN THIS PROCESS. Checked
 *  and set synchronously (no `await` between the check and the assignment
 *  below) so two calls arriving back-to-back — e.g. `Promise.all([runBackup(),
 *  runBackup()])`, or a manual "back up now" click landing while
 *  maybeDailyBackup() is still running — can never both pass the check:
 *  JS runs each call synchronously up to its first `await`, and the guard
 *  is set before this function's first `await`. This is a same-process
 *  guard only; makeTmpPath()'s pid+uuid suffix is what protects against
 *  two DIFFERENT processes (e.g. two `npm run app` instances) racing on
 *  the same tmp filename. */
let inFlightBackup: Promise<RunBackupResult> | null = null;

/**
 * Runs one full backup: resolves a dump mechanism, streams pg_dump's output
 * through gzip into a `.tmp` file, and only on success (exit 0 AND output
 * >= MIN_BACKUP_BYTES) renames it to the final timestamped name. Any
 * failure — pg_dump missing, a non-zero exit, or suspiciously small output
 * (which almost always means pg_dump failed after already writing some
 * bytes, e.g. an auth error mid-stream) — deletes the `.tmp` file and
 * throws a loud English error. No partial file is ever left under the
 * final name.
 *
 * Rejects immediately (before touching the filesystem or spawning
 * anything) if another runBackup() call is already in flight in this
 * process — see inFlightBackup's note.
 */
export async function runBackup(options: RunBackupOptions = {}): Promise<RunBackupResult> {
  if (inFlightBackup) {
    throw new Error(BACKUP_ALREADY_RUNNING_MESSAGE);
  }
  const run = runBackupExclusive(options);
  inFlightBackup = run;
  try {
    return await run;
  } finally {
    inFlightBackup = null;
  }
}

async function runBackupExclusive(options: RunBackupOptions): Promise<RunBackupResult> {
  const dir = options.dir ?? defaultBackupDir();
  await mkdir(dir, { recursive: true });

  // Same resolution the server's own pools use (server/src/db.ts): DATABASE_URL
  // wins, then the bundled instance's superuser connection. Resolved through
  // that one function rather than re-read here, so a backup can never be taken
  // of a different database than the one being served.
  const databaseUrl = options.databaseUrl ?? (await resolveDatabaseUrls()).admin;
  const { command, args } = options.commandOverride
    ?? buildDumpArgs(await resolveDumpCommand(), databaseUrl);

  const now = (options.now ?? (() => new Date()))();
  const finalPath = path.join(dir, backupFilename(now));
  // Collision-proof even if another process picks the identical finalPath
  // in the identical second — see makeTmpPath's note.
  const tmpPath = makeTmpPath(finalPath);

  const start = Date.now();
  let exitCode: number | null;
  let stderr: string;
  try {
    ({ exitCode, stderr } = await spawnDump(command, args, tmpPath));
  } catch (err) {
    await rm(tmpPath, { force: true }).catch(() => {});
    throw new Error(`Backup failed: couldn't run pg_dump (${(err as Error).message}). Nothing was saved.`);
  }
  const durationMs = Date.now() - start;

  if (exitCode !== 0) {
    await rm(tmpPath, { force: true }).catch(() => {});
    throw new Error(formatDumpFailureMessage(exitCode, stderr));
  }

  const info = await fsStat(tmpPath).catch(() => null);
  if (!info || info.size < MIN_BACKUP_BYTES) {
    await rm(tmpPath, { force: true }).catch(() => {});
    throw new Error(
      `Backup failed: the output was empty or suspiciously small (${info?.size ?? 0} bytes). Nothing was saved.`,
    );
  }

  await rename(tmpPath, finalPath);
  await applyRetention(dir, options.retentionKeep ?? DEFAULT_RETENTION_KEEP);

  return { file: finalPath, bytes: info.size, durationMs };
}

// ---------------------------------------------------------------------------
// maybeDailyBackup — called once from index.ts, after listen() succeeds,
// non-blocking. Never throws: a backup failure is logged loudly but must
// never crash (or even delay) the server.
// ---------------------------------------------------------------------------

export interface BackupLogger {
  info: (msg: string) => void;
  error: (msg: string) => void;
}

export interface MaybeDailyBackupOptions {
  dir?: string;
  log?: BackupLogger;
}

/** Runs a backup if the newest one in `dir` is missing or more than
 *  DAILY_THRESHOLD_HOURS old. Swallows and logs every error — this is
 *  called fire-and-forget from server startup and must never take the
 *  server down with it. */
export async function maybeDailyBackup(options: MaybeDailyBackupOptions = {}): Promise<void> {
  const dir = options.dir ?? defaultBackupDir();
  const log = options.log ?? { info: (m: string) => console.log(m), error: (m: string) => console.error(m) };

  try {
    const last = await lastBackupInfo(dir);
    const ageHours = last ? (Date.now() - last.mtime.getTime()) / 3_600_000 : Infinity;
    if (ageHours < DAILY_THRESHOLD_HOURS) {
      log.info(`Daily backup: skipped — last backup (${last!.file}) is ${ageHours.toFixed(1)}h old.`);
      return;
    }
    const result = await runBackup({ dir });
    log.info(`Daily backup: done — ${result.file} (${result.bytes} bytes, ${result.durationMs}ms).`);
  } catch (err) {
    log.error(`Daily backup: failed — ${(err as Error).message}`);
  }
}
