// Admin-side plumbing for the test-database lifecycle.
//
// Everything that issues CREATE DATABASE / DROP DATABASE lives here, behind a
// single naming guard, so there is exactly one place to audit. This module
// deliberately imports nothing from `vitest`: it runs both in the vitest main
// process (globalSetup) and inside test workers.
//
// The expensive part of a test database is replaying all migrations. We pay
// that once per run into a template database, then every per-file database is
// a `CREATE DATABASE ... TEMPLATE ...` file copy.

import { Client } from "pg";
import { randomUUID } from "node:crypto";
import { migrate } from "../../db/migrate.js";
import {
  LocalDbError, isRunning, readInstance, readSecret, resolveCurrentLayout,
} from "../../server/src/localdb/runtime.js";
import {
  adminConnectionUrl, buildConnectionUrl, redactConnectionUrl,
  APP_DATABASE, MAINTENANCE_DATABASE, SUPERUSER_ROLE,
} from "../../server/src/localdb/instance.js";
import "dotenv/config";

// ---------------------------------------------------------------------------
// WHERE THE SUPERUSER CONNECTION COMES FROM
// ---------------------------------------------------------------------------
//
// This used to be one line:
//
//   export const ADMIN_URL =
//     process.env.ADMIN_URL ?? "postgres://postgres:postgres@localhost:5432/postgres";
//
// That literal was the Docker container from docker-compose.yml, which no
// longer exists (docs/todo/2026-08-08-bundled-postgres-decisions.md). It kept
// working only because the container is still lying around on the one machine
// where this was noticed; the day Docker Desktop is uninstalled — which is the
// entire point of the milestone — every test in the suite would have failed
// with ECONNREFUSED on a port nothing in this product uses any more.
//
// The order is: whatever the environment says, then the bundled cluster.
// Explicit configuration wins so CI and any developer with their own PostgreSQL
// keep working EXACTLY as they did — nothing about the environment path has
// changed. Only the fallback moved, from a container that is gone to the
// cluster `npm run app` installs at %LOCALAPPDATA%\BalancedSum on 127.0.0.1:54329.
//
// ---------------------------------------------------------------------------
// WHY THE HARNESS DOES NOT START THE CLUSTER
// ---------------------------------------------------------------------------
//
// The obvious convenience would be to call localdb's start() (or worse,
// ensureCluster()) when nothing is listening. It is deliberately NOT done, for
// four reasons, in increasing order of how much they would hurt:
//
//  1. A TEST RUN THAT LEAVES A DATABASE SERVER RUNNING IS ITS OWN SMALL MESS.
//     `npm test` is run constantly. A command a developer runs fifty times a day
//     should not quietly leave a postmaster behind on the fiftieth, and it
//     should not be the thing that decides a machine-global service is up.
//  2. AND STOPPING IT AGAIN WOULD BE WORSE. The common case is a developer with
//     `npm run app` already running — the cluster IS up, and it is THEIRS. This
//     harness cannot reliably tell "I started this" from "it was already here"
//     (two concurrent test runs, or a run that crashed before its teardown), so
//     a stop-at-the-end would eventually shut the database out from under the
//     app the developer is using. Never disturbing a running cluster is the only
//     rule that is correct in every one of those cases, and it is what we do.
//  3. INITDB MUST NEVER BE A SIDE EFFECT OF `npm test`. Creating a cluster is
//     how this product creates the user's books. instance.ts's own header warns
//     about ending up with "a second, empty set of books alongside the real
//     ones"; a test harness that runs initdb because a port did not answer is
//     precisely the machinery for that. Cluster creation belongs to the
//     launcher, which asks and explains.
//  4. THE COST IS PAID ONCE, NOT PER RUN. Starting the cluster takes ~6.3s
//     (measured — see READY_TIMEOUT_MS) and then it stays up until the machine
//     is rebooted. Automating a once-a-day ten-second command is not worth
//     giving the test harness a second owner of the postmaster's lifecycle.
//
// So the harness REFUSES, and the whole burden of that choice is on the message:
// a developer with no database must get plain English telling them exactly what
// to run, never a raw ECONNREFUSED. That is explainAdminConnectFailure() below,
// and it is why every connection this file opens goes through one function.

/** Where the resolved superuser connection came from. Diagnostics and error
 *  wording only — nothing branches on it except the message a failure prints. */
export type AdminUrlSource = "environment" | "bundled";

export interface ResolvedAdminUrl {
  /** Hand this to `pg`. On the bundled path it carries the generated superuser
   *  password, so it must never be printed — print `redacted` instead. */
  url: string;
  source: AdminUrlSource;
  /** The same connection with the password replaced by `***`. Safe to log. */
  redacted: string;
  /** Which environment variable supplied it, when one did. */
  via?: "ADMIN_URL" | "DATABASE_URL";
}

/** Trims and treats "" as absent, so a variable someone blanked out in .env
 *  reads as "not set" rather than as an empty connection string. Same rule as
 *  server/src/db.ts's configured(). */
function configured(value: string | undefined): string | undefined {
  const trimmed = value?.trim();
  return trimmed ? trimmed : undefined;
}

/**
 * The maintenance-database URL implied by an application-database URL: same
 * host, same port, same credentials, database swapped to `postgres`.
 *
 * Only reached when DATABASE_URL is set and ADMIN_URL is not. CREATE DATABASE
 * cannot run from inside the database being created, so the harness always
 * needs the maintenance database; deriving it mirrors server/src/db.ts's
 * appUrlFrom(), and the rule there applies here too — the second connection
 * follows wherever the configured one points rather than silently landing on a
 * different server.
 */
function maintenanceUrlFrom(databaseUrl: string): string | null {
  try {
    const u = new URL(databaseUrl);
    return buildConnectionUrl({
      user: decodeURIComponent(u.username) || SUPERUSER_ROLE,
      password: decodeURIComponent(u.password),
      host: u.hostname,
      port: Number(u.port || "5432"),
      database: MAINTENANCE_DATABASE,
    });
  } catch {
    // A libpq keyword string ("host=... dbname=...") is the realistic case.
    // node-postgres accepts one; `new URL` cannot take it apart, and guessing is
    // not an option when the guess decides which server gets CREATE/DROP
    // DATABASE run against it. The caller refuses and names ADMIN_URL.
    return null;
  }
}

/**
 * The resolved superuser connection, computed once per process and cached.
 *
 * Cached as the PROMISE, not the value, so N concurrent callers in one worker
 * do one resolution — and so a failure is reported identically to every caller
 * instead of the first one getting the real error and the rest getting a
 * confusing second-order one.
 */
let adminUrlPromise: Promise<ResolvedAdminUrl> | null = null;

export function resolveAdminUrl(): Promise<ResolvedAdminUrl> {
  adminUrlPromise ??= resolveAdminUrlOnce();
  return adminUrlPromise;
}

async function resolveAdminUrlOnce(): Promise<ResolvedAdminUrl> {
  const explicit = configured(process.env.ADMIN_URL);
  if (explicit) {
    return {
      url: explicit, source: "environment", via: "ADMIN_URL",
      redacted: redactConnectionUrl(explicit),
    };
  }

  const appDatabase = configured(process.env.DATABASE_URL);
  if (appDatabase) {
    const maintenance = maintenanceUrlFrom(appDatabase);
    if (maintenance === null) {
      throw new Error(
        "The tests need a superuser connection to a maintenance database so they can create and drop " +
        "throwaway databases.\n\n" +
        "DATABASE_URL is set, but it isn't a postgres:// URL this can be derived from, so set ADMIN_URL " +
        "explicitly — for example ADMIN_URL=postgres://postgres:yourpassword@localhost:5432/postgres",
      );
    }
    return {
      url: maintenance, source: "environment", via: "DATABASE_URL",
      redacted: redactConnectionUrl(maintenance),
    };
  }

  // Nothing configured: the cluster this product installs. Reading it is two
  // small files — instance.json for the port, and the 0600 secret file for the
  // generated superuser password. Neither requires the server to be running,
  // which is why "installed but not started" is a distinguishable state below.
  let layout;
  try {
    layout = resolveCurrentLayout();
  } catch (err) {
    // Unsupported platform, or an environment that won't say where application
    // data lives. localdb already wrote a complete English sentence for both.
    throw new Error(`${describeLocalDbError(err)}\n\n${SET_ADMIN_URL_INSTEAD}`);
  }

  try {
    const instance = await readInstance(layout);
    const secret = await readSecret(layout);
    const url = adminConnectionUrl(instance, secret, MAINTENANCE_DATABASE);
    rememberResolvedUrl(url);
    return { url, source: "bundled", redacted: redactConnectionUrl(url) };
  } catch (err) {
    if (err instanceof LocalDbError && err.code === "not-initialised") {
      throw new Error(
        "The tests need a database, and this computer doesn't have one yet.\n\n" +
        "Run `npm run app` once. It installs PostgreSQL under your user account, creates the database and " +
        "leaves it running; then `npm test` will find it on its own. Nothing else needs setting up.\n\n" +
        `${SET_ADMIN_URL_INSTEAD}`,
      );
    }
    throw new Error(`${describeLocalDbError(err)}\n\n${SET_ADMIN_URL_INSTEAD}`);
  }
}

const SET_ADMIN_URL_INSTEAD =
  "If you'd rather run the tests against a PostgreSQL you already have, set ADMIN_URL to a superuser " +
  "connection to its maintenance database (e.g. postgres://postgres:yourpassword@localhost:5432/postgres) " +
  "and the tests will use that instead. Either way, the tests only ever create and drop their own " +
  "accounting_test_* databases.";

/** localdb's own English sentence when it has one, otherwise the raw message.
 *  LocalDbError.message is already written for a non-technical reader; there is
 *  nothing to improve on and rewording it here is how two copies of the same
 *  explanation drift apart. */
function describeLocalDbError(err: unknown): string {
  return err instanceof Error ? err.message : String(err);
}

/**
 * The last successfully resolved admin URL, mirrored synchronously so
 * protectedDatabaseNames() can see it.
 *
 * protectedDatabaseNames() is called from isDisposableTestDb(), which is a
 * synchronous type guard used on the drop path, and making that async would
 * turn a one-line safety check into an await at every call site. It does not
 * need to be async: nothing can be dropped before something has been connected
 * to, so by the time any of this matters the resolution has already happened.
 * On the very first call it is simply absent, and the static names plus the two
 * environment variables still cover every database this harness could reach.
 */
let resolvedAdminUrlMirror: string | null = null;
function rememberResolvedUrl(url: string): void {
  resolvedAdminUrlMirror = url;
}

/**
 * The developer's real books live in `accounting`. Nothing in the test harness
 * may ever drop it. This list is the belt; the regexes below are the braces.
 *
 * APP_DATABASE is used rather than a second spelling of "accounting" so this
 * cannot drift from the name the product actually creates.
 */
function protectedDatabaseNames(): Set<string> {
  const names = new Set([APP_DATABASE, MAINTENANCE_DATABASE, "template0", "template1"]);
  for (const url of [process.env.DATABASE_URL, process.env.ADMIN_URL, resolvedAdminUrlMirror]) {
    if (!url) continue;
    try {
      const name = decodeURIComponent(new URL(url).pathname.replace(/^\//, ""));
      if (name) names.add(name);
    } catch {
      /* an unparseable URL cannot name a database to protect */
    }
  }
  return names;
}

/** Per-test-file throwaway database: accounting_test_<32 hex>. */
const TEST_DB_RE = /^accounting_test_[0-9a-f]{32}$/;
/** Per-run migrated template: accounting_test_template_<32 hex>. */
const TEMPLATE_DB_RE = /^accounting_test_template_[0-9a-f]{32}$/;

/**
 * True only for a database this harness created and may destroy.
 *
 * Fails closed on every axis: wrong type, protected name, or anything that is
 * not an exact full-string match against one of the two generated shapes. In
 * particular `accounting` cannot match — it is in the protected set AND is far
 * too short for either pattern.
 */
export function isDisposableTestDb(name: unknown): name is string {
  if (typeof name !== "string") return false;
  if (protectedDatabaseNames().has(name)) return false;
  return TEST_DB_RE.test(name) || TEMPLATE_DB_RE.test(name);
}

/** Throw unless `name` is a database this harness is allowed to destroy. */
export function assertDisposable(name: unknown): asserts name is string {
  if (!isDisposableTestDb(name)) {
    throw new Error(
      `refusing to operate on database ${JSON.stringify(name)}: ` +
      "not a disposable test database (must match accounting_test_<32 hex> " +
      "or accounting_test_template_<32 hex>, and must not be a protected name)",
    );
  }
}

/**
 * Quote a database identifier. Only ever called on names that already passed
 * `assertDisposable`, so this is redundancy, not the primary defence.
 */
function ident(name: string): string {
  if (!/^[a-z0-9_]+$/.test(name)) throw new Error(`unsafe identifier: ${name}`);
  return `"${name}"`;
}

/**
 * A connection string for `dbName` on whichever server the harness resolved.
 *
 * Async now, and it has to be: the bundled cluster's port lives in
 * instance.json and its superuser password in a 0600 file, so there is no
 * synchronous answer to "where is the database". Every call site is already
 * inside an async function, so this costs nothing but an `await` — and after
 * the first call the resolution is cached, so it is not even a file read.
 *
 * With no `user`, you get the resolved superuser — `postgres` plus the
 * generated password on the bundled path, or whatever ADMIN_URL/DATABASE_URL
 * supplied on the environment path. Pass `"app", "app"` for the RLS-bound role
 * (its password is a git-committed literal in migration 0001; see the long note
 * on APP_ROLE_PASSWORD in server/src/db.ts for why that is not a secret).
 *
 * Built with instance.ts's buildConnectionUrl() rather than string
 * concatenation, so a password containing a URL metacharacter cannot silently
 * re-point the connection at a different host. The generated secret's alphabet
 * makes that impossible today; an ADMIN_URL a developer typed by hand does not.
 */
export async function dbUrl(dbName: string, user?: string, password?: string): Promise<string> {
  const resolved = await resolveAdminUrl();
  let u: URL;
  try {
    u = new URL(resolved.url);
  } catch {
    throw new Error(
      `The superuser connection the tests resolved (${resolved.redacted}) isn't a postgres:// URL, so a ` +
      "connection to a throwaway test database can't be derived from it. Set ADMIN_URL to a postgres:// URL.",
    );
  }
  return buildConnectionUrl({
    user: user ?? (decodeURIComponent(u.username) || SUPERUSER_ROLE),
    password: user !== undefined ? (password ?? "") : decodeURIComponent(u.password),
    host: u.hostname,
    port: Number(u.port || "5432"),
    database: dbName,
  });
}

export function newTestDbName(): string {
  return `accounting_test_${randomUUID().replaceAll("-", "")}`;
}

function newTemplateDbName(): string {
  return `accounting_test_template_${randomUUID().replaceAll("-", "")}`;
}

/**
 * The ONE place this file opens a superuser connection, which is what makes it
 * the one place that has to explain a failure to open one.
 *
 * Every CREATE/DROP DATABASE in the harness goes through here, so a developer
 * with no database gets explainAdminConnectFailure()'s plain English no matter
 * which entry point they happened to reach first — the stale-database sweep,
 * the template build, or a per-file teardown.
 */
async function withMaintenanceClient<T>(fn: (c: Client) => Promise<T>): Promise<T> {
  const resolved = await resolveAdminUrl();
  const c = new Client({ connectionString: resolved.url });
  c.on("error", () => { /* an unlistened 'error' event would kill the worker */ });
  try {
    await c.connect();
  } catch (err) {
    await c.end().catch(() => { /* never connected; nothing to close */ });
    throw new Error(await explainAdminConnectFailure(resolved, err), { cause: err });
  }
  try {
    return await fn(c);
  } finally {
    await c.end().catch(() => { /* best effort */ });
  }
}

/**
 * Turns a failed superuser connection into an instruction.
 *
 * A raw `ECONNREFUSED 127.0.0.1:54329` is a true statement that tells a
 * developer nothing about what to do next, and it is the single most likely
 * thing anyone will see the first time they clone this repo. So the three
 * states that actually occur are separated, and each one ends with a command:
 *
 *   - the environment named a server and it isn't answering  -> your ADMIN_URL
 *   - the bundled cluster is installed but not started       -> `npm run app`
 *   - the bundled cluster is answering but rejected us       -> the secret file
 *
 * `isRunning()` is asked only on this path (it spawns pg_isready from the
 * bundled runtime), because the answer is worth a process spawn exactly when
 * something has already gone wrong.
 */
async function explainAdminConnectFailure(
  resolved: ResolvedAdminUrl, err: unknown,
): Promise<string> {
  const detail = err instanceof Error ? err.message : String(err);
  // 28P01 invalid_password / 28000 invalid_authorization_specification.
  const sqlState = (err as { code?: string } | null)?.code;
  const authFailed = sqlState === "28P01" || sqlState === "28000";

  if (resolved.source === "environment") {
    return (
      `The tests couldn't connect to the PostgreSQL your environment points at (${resolved.redacted}, from ` +
      `${resolved.via}).\n\n` +
      (authFailed
        ? "The server answered but refused the credentials in that URL. Check the user and password.\n\n"
        : "Nothing answered there. Start that server, or correct the URL.\n\n") +
      "To use the database Balanced Sum installs instead, unset ADMIN_URL and DATABASE_URL (this repo's .env " +
      "sets both) and run `npm run app` once.\n\n" +
      `PostgreSQL said: ${detail}`
    );
  }

  if (authFailed) {
    return (
      "The database on this computer is running, but it refused the password Balanced Sum has on file for it.\n\n" +
      "That usually means the password file and the database no longer belong together — most often because one " +
      "of them was restored from a backup without the other. Get in touch before doing anything else; your books " +
      "have not been changed.\n\n" +
      `PostgreSQL said: ${detail}`
    );
  }

  // Not an auth problem, so: is it even up? Asked of localdb rather than
  // inferred from the socket error, because "installed but stopped" and "never
  // installed" need different sentences and only localdb can tell them apart.
  let running = false;
  try {
    running = (await isRunning(resolveCurrentLayout())).running;
  } catch {
    /* if we can't ask, the message below is still correct */
  }
  if (running) {
    return (
      "The database on this computer says it's running, but the tests couldn't connect to it. It may still be " +
      "starting up — that can take a while after the computer was switched off without shutting down. Wait a " +
      "moment and run `npm test` again.\n\n" +
      `Tried: ${resolved.redacted}\nPostgreSQL said: ${detail}`
    );
  }
  return (
    "The tests need a database, and the one on this computer isn't running.\n\n" +
    "Run `npm run app` and leave it running, then run `npm test` again. The tests deliberately do NOT start the " +
    "database themselves: it's a machine-wide service, and a test run should not start one behind your back or " +
    "shut down one you're already using.\n\n" +
    `${SET_ADMIN_URL_INSTEAD}\n\n` +
    `Tried: ${resolved.redacted}\nPostgreSQL said: ${detail}`
  );
}

/**
 * Stamp a database we just created with its provenance and birth time. This is
 * not the safety guard (the name pattern is) — it is a second, independent
 * signal that the sweep can use, and it makes a stray database explain itself
 * to anyone who finds one with `\l+`.
 */
const STAMP_PREFIX = "accounting-test-harness created=";

/**
 * Provenance written onto every database this harness creates.
 *
 * `created=` is the birth time the stale sweep reads. `run=` and `file=` are
 * newer and exist for one reason: WHEN A WORKER DIES, ITS FILE'S RESULTS
 * DISAPPEAR AND VITEST NAMES NO FILE.
 *
 * The run comes back with "Worker exited unexpectedly", a total that is short
 * by however many tests that file held, and not one word about which file it
 * was — the person reading it cannot even begin. But a worker that dies mid-file
 * never reaches its afterAll, so its throwaway database is still sitting there
 * when the run ends. That database is the fingerprint of the file that vanished,
 * and with the file name stamped on it, reportLeakedDatabases() in globalSetup's
 * teardown can say so by name.
 *
 * `run=` is this run's template database name, which is a fresh uuid per run.
 * That is what makes the leak report exact rather than a guess: a second test
 * run happening at the same time has a different template, so its databases are
 * visibly not ours and are left completely alone.
 *
 * Quoting: `name` has already passed assertDisposable (generated hex), the
 * timestamp and run id are machine-generated, and `file` is the only value with
 * an outside origin — it is a path from vitest, so `'` is doubled the way
 * PostgreSQL wants and the length is capped. A COMMENT is not worth an injection
 * hole, however implausible.
 */
interface DatabaseStamp {
  /** This run's template database name — the run id. */
  run?: string;
  /** The test file the database belongs to, when the caller knows it. */
  file?: string;
}

function quoteLiteral(value: string): string {
  return value.replaceAll("'", "''");
}

function buildStamp(stamp: DatabaseStamp): string {
  let text = `${STAMP_PREFIX}${new Date().toISOString()}`;
  if (stamp.run) text += ` run=${stamp.run}`;
  if (stamp.file) text += ` file=${stamp.file.slice(0, 300)}`;
  return quoteLiteral(text);
}

async function stampOn(c: Client, name: string, stamp: DatabaseStamp = {}): Promise<void> {
  assertDisposable(name);
  await c.query(
    `COMMENT ON DATABASE ${ident(name)} IS '${buildStamp(stamp)}'`,
  ).catch(() => { /* provenance is a nicety, never a hard failure */ });
}

/** Pulls `file=` back out of a stamp. Last field, so it may contain spaces. */
function fileFromStamp(comment: string | null): string | null {
  const match = /(?:^| )file=(.+)$/.exec(comment ?? "");
  return match ? match[1] : null;
}

/** True when `comment` is a stamp this run wrote. */
function stampBelongsToRun(comment: string | null, runId: string): boolean {
  return comment !== null
    && comment.startsWith(STAMP_PREFIX)
    && new RegExp(`(?:^| )run=${runId}(?: |$)`).test(comment);
}

/**
 * Give a database's remaining backends a moment to disconnect on their own.
 *
 * `DROP DATABASE ... WITH (FORCE)` severs live sockets. A `pg.Pool` that is
 * still finishing `app.close()` reacts to a severed idle socket by emitting
 * 'error'; a pool with no 'error' listener turns that into an unhandled error
 * that kills the vitest worker *after* its tests have already passed — which
 * also skips the rest of teardown and leaks the database. Waiting for
 * quiescence removes the race; FORCE stays as backstop.
 *
 * server/src/db.ts now attaches those listeners, which is the actual fix (the
 * same crash shape takes down the API process in production when Postgres
 * restarts). This stays as belt-and-braces for pools this harness does not own.
 *
 * Hitting the deadline is NOT a normal outcome and is no longer silent. The
 * original returned quietly, which meant the one condition worth knowing about
 * — backends still attached when we are about to sever them — looked exactly
 * like the healthy path. That is how a harness hides a flake from the person
 * chasing it. Warn, name the database and the count, and carry on to FORCE.
 */
async function waitForQuiescence(
  c: Client, name: string, timeoutMs = 5000,
): Promise<void> {
  const deadline = Date.now() + timeoutMs;
  for (;;) {
    const r = await c.query<{ n: number }>(
      `SELECT count(*)::int AS n FROM pg_stat_activity
        WHERE datname = $1 AND pid <> pg_backend_pid()`, [name]);
    const remaining = r.rows[0]?.n ?? 0;
    if (remaining === 0) return;
    if (Date.now() >= deadline) {
      console.warn(
        `[test-db] ${name}: ${remaining} backend(s) still connected after ${timeoutMs}ms — ` +
        `dropping WITH (FORCE) anyway. If a test file's results went missing from this run, ` +
        `start here.`,
      );
      return;
    }
    await new Promise((resolve) => setTimeout(resolve, 25));
  }
}

async function dropOn(c: Client, name: string): Promise<void> {
  assertDisposable(name);
  // A template has connections disallowed; re-allow before dropping so
  // WITH (FORCE) can still evict any straggler.
  await c.query(`ALTER DATABASE ${ident(name)} WITH ALLOW_CONNECTIONS true`)
    .catch(() => { /* already gone, or never disallowed */ });
  await waitForQuiescence(c, name);
  await c.query(`DROP DATABASE IF EXISTS ${ident(name)} WITH (FORCE)`);
}

/** Drop a throwaway test database. Refuses anything that is not one. */
export async function dropTestDatabase(name: string): Promise<void> {
  assertDisposable(name);
  await withMaintenanceClient((c) => dropOn(c, name));
}

const CHART: Array<[code: string, name: string, type: string]> = [
  ["1000", "Cash", "asset"],
  ["1100", "Accounts Receivable", "asset"],
  ["1200", "Due from Affiliate", "asset"],
  ["2000", "Accounts Payable", "liability"],
  ["2200", "Due to Affiliate", "liability"],
  ["3000", "Opening Equity", "equity"],
  ["4000", "Revenue", "income"],
  ["6000", "Operating Expense", "expense"],
];

/**
 * The standard fixture, baked into the template so every per-file database
 * inherits it for free: two companies, two owners, one identical chart each.
 * Historically this ran per test database inside createTestDb().
 */
async function seedFixture(admin: Client): Promise<void> {
  const comps = await admin.query(
    `INSERT INTO companies (name) VALUES ('Alpha LLC'), ('Beta LLC') RETURNING id`);
  const companyA: string = comps.rows[0].id;
  const companyB: string = comps.rows[1].id;

  const us = await admin.query(
    `INSERT INTO users (email, display_name)
     VALUES ('a@example.com', 'User A'), ('b@example.com', 'User B') RETURNING id`);
  const userA: string = us.rows[0].id;
  const userB: string = us.rows[1].id;

  await admin.query(
    `INSERT INTO user_companies (user_id, company_id, role)
     VALUES ($1, $3, 'owner'), ($2, $4, 'owner')`,
    [userA, userB, companyA, companyB]);

  for (const [code, name, type] of CHART) {
    await admin.query(
      `INSERT INTO accounts (company_id, code, name, type)
       VALUES ($1, $3, $4, $5), ($2, $3, $4, $5)`,
      [companyA, companyB, code, name, type]);
  }
}

export interface Fixture {
  companyA: string;
  companyB: string;
  userA: string;
  userB: string;
  accounts: Record<string, { A: number; B: number }>;
}

/**
 * Read back the seeded fixture ids from a database created off the template.
 * Ordering is by natural key (company name, user email) so the A/B mapping is
 * identical to what the old insert-and-capture code produced.
 */
export async function readFixture(admin: Client): Promise<Fixture> {
  const comps = await admin.query(
    "SELECT id, name FROM companies WHERE name IN ('Alpha LLC','Beta LLC') ORDER BY name");
  const us = await admin.query(
    "SELECT id, email FROM users WHERE email IN ('a@example.com','b@example.com') ORDER BY email");
  if (comps.rows.length !== 2 || us.rows.length !== 2) {
    throw new Error("test template is missing the standard fixture");
  }
  const companyA: string = comps.rows[0].id;
  const companyB: string = comps.rows[1].id;
  const userA: string = us.rows[0].id;
  const userB: string = us.rows[1].id;

  const accs = await admin.query(
    "SELECT company_id, code, id FROM accounts WHERE company_id IN ($1, $2)",
    [companyA, companyB]);
  const accounts: Record<string, { A: number; B: number }> = {};
  for (const [code] of CHART) {
    const a = accs.rows.find((r) => r.code === code && r.company_id === companyA);
    const b = accs.rows.find((r) => r.code === code && r.company_id === companyB);
    if (!a || !b) throw new Error(`test template is missing seeded account ${code}`);
    accounts[code] = { A: Number(a.id), B: Number(b.id) };
  }
  return { companyA, companyB, userA, userB, accounts };
}

/**
 * Build the per-run template: create, migrate, seed, then disconnect and lock
 * it down. Postgres refuses `CREATE DATABASE ... TEMPLATE t` while any session
 * is connected to `t`, so we disallow connections (the same mechanism that
 * protects template0) and terminate anything still attached. From here on the
 * template is copy-only.
 */
export async function buildTemplateDatabase(): Promise<string> {
  const name = newTemplateDbName();
  assertDisposable(name);
  await withMaintenanceClient(async (c) => {
    await c.query(`CREATE DATABASE ${ident(name)}`);
    await stampOn(c, name);
  });

  const url = await dbUrl(name);
  try {
    await migrate(url); // opens and closes its own client
    const seed = new Client({ connectionString: url });
    // Same reason as withMaintenanceClient's listener: a pg Client is an
    // EventEmitter, and an 'error' with no listener is rethrown as an uncaught
    // exception. This one runs in the vitest MAIN process during globalSetup,
    // where an uncaught exception takes down the entire run before a single
    // test has started — a worse outcome than the worker deaths that motivated
    // the other listeners, not a lesser one.
    seed.on("error", () => { /* the catch below owns the failure path */ });
    await seed.connect();
    try {
      await seedFixture(seed);
    } finally {
      await seed.end();
    }
  } catch (err) {
    await dropTestDatabase(name).catch(() => { /* best effort */ });
    throw err;
  }

  await withMaintenanceClient(async (c) => {
    await c.query(`ALTER DATABASE ${ident(name)} WITH ALLOW_CONNECTIONS false`);
    await c.query(
      `SELECT pg_terminate_backend(pid) FROM pg_stat_activity
       WHERE datname = $1 AND pid <> pg_backend_pid()`, [name]);
  });
  return name;
}

/**
 * Copy the template into a fresh throwaway database. Concurrent copies of the
 * same template are serialized by Postgres and can transiently collide, so
 * retry a couple of times before giving up.
 */
export async function createTestDatabaseFromTemplate(
  template: string, testFile?: string,
): Promise<string> {
  assertDisposable(template);
  const name = newTestDbName();
  assertDisposable(name);
  let lastErr: unknown;
  for (let attempt = 0; attempt < 4; attempt++) {
    try {
      await withMaintenanceClient(async (c) => {
        await c.query(`CREATE DATABASE ${ident(name)} TEMPLATE ${ident(template)}`);
        // The template name doubles as this run's id — see DatabaseStamp.
        await stampOn(c, name, { run: template, file: testFile });
      });
      return name;
    } catch (err) {
      lastErr = err;
      await new Promise((r) => setTimeout(r, 150 * (attempt + 1)));
    }
  }
  throw new Error(
    `could not copy test template ${template}: ${(lastErr as Error)?.message}`,
    { cause: lastErr },
  );
}

/**
 * Names the test files whose databases were never dropped, and drops them.
 *
 * Called from globalSetup's teardown, after every worker has finished. In a
 * healthy run this finds nothing: each file's afterAll drops its own database.
 * A database still standing means the worker that owned it did not get to its
 * teardown — it died. That is the failure this whole exercise is about, and
 * until now vitest's report of it was "Worker exited unexpectedly", a test total
 * short by an unexplained amount, and NO FILE NAME. This turns it into a line
 * that says which file.
 *
 * Scoped by `run=<template>` (a per-run uuid), so a test run happening
 * concurrently in another checkout is not merely unlikely to be touched — its
 * databases carry a different run id and are not even considered. The
 * age-and-connections heuristics in sweepStaleTestDatabases() are for
 * databases whose run is long gone; this one is exact.
 *
 * Returns the file names so the caller decides how loud to be. Never throws:
 * a diagnostic that can fail a passing run is worse than no diagnostic.
 */
export async function reportLeakedDatabases(
  runId: string,
): Promise<Array<{ database: string; file: string | null }>> {
  const leaked: Array<{ database: string; file: string | null }> = [];
  try {
    await withMaintenanceClient(async (c) => {
      const all = await c.query<{ datname: string; comment: string | null }>(
        `SELECT d.datname, shobj_description(d.oid, 'pg_database') AS comment
           FROM pg_database d`);
      for (const row of all.rows) {
        if (!isDisposableTestDb(row.datname)) continue;
        if (row.datname === runId) continue; // the template; dropped separately
        if (!stampBelongsToRun(row.comment, runId)) continue;
        leaked.push({ database: row.datname, file: fileFromStamp(row.comment) });
      }
      for (const { database } of leaked) {
        await dropOn(c, database).catch(() => { /* the report is the point */ });
      }
    });
  } catch {
    /* nothing to report if we cannot even connect; the run already said so */
  }
  return leaked;
}

/**
 * Drop leftover test databases from crashed runs so they stop accumulating
 * against max_connections and disk.
 *
 * Every check below is a reason NOT to drop; a database has to clear all of
 * them. A concurrent test run is protected on three counts: its databases have
 * live connections, a fresh directory mtime, and a fresh creation stamp.
 */
export async function sweepStaleTestDatabases(
  maxAgeMs = 60 * 60 * 1000,
): Promise<string[]> {
  const dropped: string[] = [];
  const now = Date.now();

  await withMaintenanceClient(async (c) => {
    const all = await c.query<{
      datname: string; oid: string; comment: string | null; conns: string;
    }>(`SELECT d.datname,
               d.oid::text AS oid,
               shobj_description(d.oid, 'pg_database') AS comment,
               (SELECT count(*) FROM pg_stat_activity a
                 WHERE a.datname = d.datname)::text AS conns
          FROM pg_database d`);

    for (const row of all.rows) {
      // Guard first, and it is the only thing that decides eligibility:
      // a name this harness could have generated, and never a protected one.
      if (!isDisposableTestDb(row.datname)) continue;

      // In use right now by someone -> not stale.
      if (Number(row.conns) > 0) continue;

      // Newest available filesystem signal. base/<oid> is touched by ongoing
      // activity; PG_VERSION is written at creation and never again. Taking
      // the newer of the two makes the age estimate conservative.
      const times: number[] = [];
      for (const path of [`base/${row.oid}`, `base/${row.oid}/PG_VERSION`]) {
        try {
          const st = await c.query<{ m: Date | null }>(
            "SELECT (pg_stat_file($1, true)).modification AS m", [path]);
          const m = st.rows[0]?.m;
          if (m instanceof Date) times.push(m.getTime());
        } catch {
          /* unreadable -> contributes no evidence */
        }
      }
      // No evidence of age at all -> leave it alone.
      if (times.length === 0) continue;
      if (now - Math.max(...times) <= maxAgeMs) continue;

      // If it carries our creation stamp, that must be old too.
      if (row.comment?.startsWith(STAMP_PREFIX)) {
        const created = Date.parse(row.comment.slice(STAMP_PREFIX.length));
        if (!Number.isNaN(created) && now - created <= maxAgeMs) continue;
      }

      try {
        await dropOn(c, row.datname); // re-asserts the name guard
        dropped.push(row.datname);
      } catch {
        /* raced with someone; next run will pick it up */
      }
    }
  });
  return dropped;
}
