// The bundled-PostgreSQL runtime driver — PG Task 2 of
// docs/todo/2026-08-08-bundled-postgres-decisions.md. This is the module that
// actually creates, starts and stops the database the app runs on once Docker is
// gone. Everything under server/src/localdb/ that ISN'T this file (or
// runtime-fetch.ts) is pure; this is where the filesystem, the network and child
// processes live.
//
//   ensureRuntime(layout)   the nine executables are present and checksum-verified,
//                           or they are fetched. (runtime-fetch.ts — re-exported here
//                           so a caller has one import.)
//   ensureCluster(layout)   initdb, exactly once, with a generated 0600 password;
//                           or validate the cluster that is already there.
//   start / stop / isRunning
//
// FOUR FINDINGS FROM THE 2026-08-08 SPIKE ARE BAKED IN HERE. They were paid for
// with real time on a real machine against real 16.10 binaries, so they are
// stated rather than rediscovered:
//
//  1. `pg_ctl start` HANGS THE NODE PARENT FOREVER. pg_ctl returns, but the
//     postmaster it forks inherits the parent's stdout handle, and the parent
//     then waits on a pipe that will never close. Ten minutes were lost to this.
//     The server is spawned as `postgres` DIRECTLY, detached, with stdio pointed
//     at the log file. pg_ctl is still exactly right for STOP, and is used for
//     it — the asymmetry is the finding, not an oversight.
//  2. READINESS TAKES ~6.3 SECONDS on a warm machine, so the budget is 30s and
//     not the 5s everyone reaches for. See READY_TIMEOUT_MS in runtime-facts.ts.
//  3. STARTING A SECOND POSTMASTER ON ONE DATA DIRECTORY IS SAFE AND LOUD: the
//     second exits with `FATAL: lock file "postmaster.pid" already exists` and a
//     HINT naming the live PID. It lands in the log file, which is why every
//     failure here ends by reading the tail of that log into the error message.
//     The answer is always in there.
//  4. A STALE postmaster.pid AFTER A HARD POWER-OFF IS ORDINARY. It is detected
//     and cleaned up. A business owner is never told to go and delete a file
//     they have never heard of.
//
// THE PASSWORD RULE, which overrides convenience everywhere in this file: the
// generated superuser password is written to `layout.secretFile` and read back
// from it, and that is the whole list of places it may exist. It is never
// logged, never interpolated into an error message, never put in .env. Any
// connection string that goes anywhere a human can read it goes through
// instance.ts's redactConnectionUrl() first.

import { spawn, type ChildProcess } from "node:child_process";
import { open, mkdir, readFile, rename, rm, writeFile, chmod } from "node:fs/promises";
import { randomUUID } from "node:crypto";
import net from "node:net";
import path from "node:path";

import { resolvePlatform, type PlatformId } from "./platform.js";
import { resolveLayout, binaryPath, type LocalDbLayout, type PathEnv } from "./paths.js";
import { SHIPPED_POSTGRES_VERSION } from "./manifest.js";
import {
  parseInstanceDocument, buildInstanceDocument, serializeInstanceDocument,
  adminConnectionUrl, redactConnectionUrl,
  APP_DATABASE, DEFAULT_PORT, LOOPBACK_HOST, SUPERUSER_ROLE,
  type InstanceDocument,
} from "./instance.js";
import { parsePostgresMajor } from "./version.js";
import { generateSecret, isWellFormedSecret } from "./secret.js";
import { getVersion } from "../version.js";
import {
  LocalDbError,
  READY_POLL_INTERVAL_MS, READY_TIMEOUT_MS, STOP_TIMEOUT_SECONDS,
  classifyProcessOwner, judgePostmasterLock, parsePostmasterPid,
  parsePsCommand, parseTasklistImageName, planCluster, summariseLogTail,
  type LockVerdict, type ProcessOwner,
} from "./runtime-facts.js";
import { consoleRuntimeLogger, type RuntimeLogger } from "./runtime-fetch.js";

// One import for the caller: PG Task 3 talks to this module, not to five.
export {
  ensureRuntime, isRuntimeInstalled, resolveTarExecutable, consoleRuntimeLogger,
  type EnsureRuntimeResult, type EnsureRuntimeOptions, type RuntimeLogger,
} from "./runtime-fetch.js";
export {
  LocalDbError, READY_TIMEOUT_MS,
  type LocalDbErrorCode, type LockVerdict,
} from "./runtime-facts.js";

// ---------------------------------------------------------------------------
// layout for THIS machine
// ---------------------------------------------------------------------------

export interface ResolveLayoutOptions {
  /** Defaults to process.env. Only LOCALAPPDATA and HOME are read. */
  env?: PathEnv;
  /** Defaults to process.platform / process.arch. Overridable so a test can ask
   *  for the macOS layout from a Windows runner — the same reason paths.ts and
   *  platform.ts take them as arguments in the first place. */
  nodePlatform?: string;
  nodeArch?: string;
  /** Defaults to the version this build ships. */
  postgresVersion?: string;
}

/**
 * The one place `process.platform`, `process.arch` and `process.env` are read on
 * the way into the localdb module. Everything below this line takes a resolved
 * layout, which is what keeps the rest of it testable against a scratch
 * directory instead of the user's real application-data folder.
 *
 * Both failure modes — an OS we publish nothing for, and an environment that
 * won't say where application data goes — already have complete English
 * sentences written for them in platform.ts and paths.ts. They are passed
 * through verbatim rather than rewritten here; two wordings of the same refusal
 * is how one of them ends up stale.
 */
export function resolveCurrentLayout(options: ResolveLayoutOptions = {}): LocalDbLayout {
  const platformResult = resolvePlatform(
    options.nodePlatform ?? process.platform,
    options.nodeArch ?? process.arch,
  );
  if (!platformResult.ok) {
    throw new LocalDbError(
      "unsupported-platform",
      platformResult.message,
      `${platformResult.nodePlatform}/${platformResult.nodeArch}: ${platformResult.reason}`,
    );
  }

  const layoutResult = resolveLayout(
    platformResult.platform,
    options.env ?? process.env,
    options.postgresVersion ?? SHIPPED_POSTGRES_VERSION,
  );
  if (!layoutResult.ok) {
    throw new LocalDbError("no-app-data-root", layoutResult.message, layoutResult.reason);
  }
  return layoutResult.layout;
}

// ---------------------------------------------------------------------------
// running a bundled tool and waiting for it
// ---------------------------------------------------------------------------

interface ToolResult {
  code: number | null;
  stdout: string;
  stderr: string;
}

/**
 * Spawns one of the bundled executables and collects everything it said.
 *
 * Used for initdb, pg_ctl stop, pg_isready and the OS's process lister — every
 * child process in this file EXCEPT the postmaster itself, which is spawned by
 * hand in start() because it must outlive us. `shell: false` throughout: these
 * are real executables taking argv at absolute paths we chose, so there is no
 * `.cmd`-wrapper problem and no reason to hand any of these arguments to a
 * command interpreter.
 *
 * A process that cannot be started at all resolves as `code: null` with the
 * spawn error in stderr, rather than rejecting. Every caller here has a better
 * English sentence to write about "initdb wouldn't run" than a rethrown ENOENT,
 * and none of them want a try/catch around a shape they already have to handle.
 */
function runTool(exe: string, args: string[], env?: NodeJS.ProcessEnv): Promise<ToolResult> {
  return new Promise((resolve) => {
    let child;
    try {
      child = spawn(exe, args, { stdio: ["ignore", "pipe", "pipe"], windowsHide: true, env });
    } catch (err) {
      resolve({ code: null, stdout: "", stderr: (err as Error).message });
      return;
    }
    const out: Buffer[] = [];
    const errChunks: Buffer[] = [];
    child.stdout?.on("data", (c: Buffer) => out.push(c));
    child.stderr?.on("data", (c: Buffer) => errChunks.push(c));
    child.on("error", (err) => resolve({ code: null, stdout: "", stderr: err.message }));
    child.on("close", (code) => resolve({
      code,
      stdout: Buffer.concat(out).toString("utf8"),
      stderr: Buffer.concat(errChunks).toString("utf8"),
    }));
  });
}

function delay(ms: number): Promise<void> {
  return new Promise((resolve) => setTimeout(resolve, ms));
}

// ---------------------------------------------------------------------------
// is anything listening?
// ---------------------------------------------------------------------------

/**
 * pg_isready's exit code for `layout`'s loopback host on `port`.
 *
 *   0  accepting connections            — ready
 *   1  rejecting connections            — a server IS there (starting up, or in
 *                                         recovery, or shutting down)
 *   2  no response                      — nothing there
 *   3  no attempt made (bad arguments)
 *
 * The 0/1 distinction is load-bearing and is why this returns the code rather
 * than a boolean. For READINESS only 0 will do. For "is something already using
 * this data directory", 1 counts just as much as 0 — a server replaying WAL
 * after a crash rejects connections for as long as the replay takes, and
 * treating that as "nothing is running" is how you end up starting a second
 * postmaster over a recovering one.
 */
async function pgIsReadyCode(layout: LocalDbLayout, port: number): Promise<number | null> {
  const result = await runTool(binaryPath(layout, "pg_isready"), [
    "-h", LOOPBACK_HOST,
    "-p", String(port),
    "-q",
  ]);
  return result.code;
}

const isAccepting = (code: number | null): boolean => code === 0;
const isResponding = (code: number | null): boolean => code === 0 || code === 1;

/**
 * Is ANYTHING accepting TCP on this port? A cheap gate in front of pg_isready,
 * not a replacement for it: PostgreSQL binds its port early and then refuses
 * connections for the whole of startup and WAL replay, so an open socket means
 * "worth asking pg_isready", never "ready".
 *
 * WHY THIS EXISTS. Spawning a process is not free on Windows, and on a machine
 * whose antivirus is busy scanning 120 MB of freshly unpacked binaries it is not
 * even cheap: measured on this machine mid-scan, `pg_isready` took a median of
 * **1.4 seconds per spawn** — ten polls, fourteen seconds, none of it spent
 * waiting for PostgreSQL. The readiness loop was burning its entire budget on
 * the cost of asking, and reporting a timeout for a server that had logged
 * "ready to accept connections" five seconds in. A first launch on a fresh
 * Windows machine, with Defender scanning binaries that appeared thirty seconds
 * ago, is EXACTLY that machine — this is the common case, not the degraded one.
 *
 * A socket connect costs no process. So the loop probes the socket every time
 * and spawns pg_isready only once something is listening, which turns dozens of
 * spawns into one or two.
 *
 * Never rejects. A closed port, a refused connection and a timeout are all just
 * "not yet" to the caller.
 */
function portAccepting(port: number, timeoutMs = 1000): Promise<boolean> {
  return new Promise((resolve) => {
    const socket = new net.Socket();
    let settled = false;
    const finish = (open: boolean): void => {
      if (settled) return;
      settled = true;
      socket.destroy();
      resolve(open);
    };
    socket.setTimeout(timeoutMs);
    socket.once("connect", () => finish(true));
    socket.once("timeout", () => finish(false));
    socket.once("error", () => finish(false));
    socket.connect(port, LOOPBACK_HOST);
  });
}

// ---------------------------------------------------------------------------
// who owns that PID?
// ---------------------------------------------------------------------------

/**
 * Asks the operating system what, if anything, is running under `pid`.
 *
 * Two questions, and the second is the one that matters. `process.kill(pid, 0)`
 * answers "does a process with this id exist" — cheap, kernel-level, no spawn.
 * But Windows hands PIDs out of a small pool and recycles them within minutes,
 * so "a process exists" is emphatically NOT "our postmaster is still running",
 * and the difference decides whether a lock file gets deleted. So when something
 * IS alive, its image name is read out of the OS's own process lister.
 *
 * EPERM from kill(0) means the process exists but belongs to someone else —
 * alive, and we will not learn its name. That resolves to `unknown`, which
 * refuses. Never to `none`, which would delete a lock file.
 */
async function inspectProcessOwner(layout: LocalDbLayout, pid: number): Promise<ProcessOwner> {
  if (!Number.isSafeInteger(pid) || pid <= 0) return "none";

  let alive: boolean;
  try {
    process.kill(pid, 0);
    alive = true;
  } catch (err) {
    const code = (err as NodeJS.ErrnoException).code;
    if (code === "ESRCH") alive = false;
    else if (code === "EPERM") alive = true;
    // Anything else is a surprise; assume alive so the verdict lands on
    // "unknown" (refuse) rather than "none" (delete the lock).
    else alive = true;
  }
  if (!alive) return classifyProcessOwner(false, null);

  return classifyProcessOwner(true, await processImageName(layout.platform, pid));
}

/** The executable name behind `pid`, or null when the OS won't say. Absolute
 *  paths on Windows for the same reason bsdtar is resolved absolutely: a
 *  `tasklist` picked up from PATH is a stranger. */
async function processImageName(platform: PlatformId, pid: number): Promise<string | null> {
  if (platform === "win32-x64") {
    const tasklist = path.win32.join(process.env.SystemRoot ?? "C:\\Windows", "System32", "tasklist.exe");
    const result = await runTool(tasklist, ["/FI", `PID eq ${pid}`, "/NH", "/FO", "CSV"]);
    if (result.code !== 0) return null;
    return parseTasklistImageName(result.stdout);
  }
  const result = await runTool("/bin/ps", ["-o", "comm=", "-p", String(pid)]);
  if (result.code !== 0) return null;
  return parsePsCommand(result.stdout);
}

// ---------------------------------------------------------------------------
// instance.json + the secret
// ---------------------------------------------------------------------------

/** Reads and validates instance.json. A missing file and an unreadable one are
 *  different answers — see instance.ts's header for why that distinction is not
 *  pedantry. */
async function readInstanceFile(layout: LocalDbLayout) {
  const raw = await readFile(layout.instanceFile, "utf8").catch(() => null);
  return parseInstanceDocument(raw);
}

/** instance.json, or a LocalDbError. The public read: the launcher needs the
 *  port and the recorded version, and should not have to re-derive either. */
export async function readInstance(layout: LocalDbLayout): Promise<InstanceDocument> {
  const result = await readInstanceFile(layout);
  if (result.ok) return result.instance;
  throw new LocalDbError(
    result.reason === "absent" ? "not-initialised" : "instance-unreadable",
    result.reason === "absent"
      ? "No database has been set up on this computer yet."
      : result.message,
    result.detail,
  );
}

/** Written to a scratch name and renamed, so a power cut during this write can
 *  leave the OLD file or the NEW one but never half of either. instance.ts's
 *  parser is all-or-nothing precisely because a half-written file must not be
 *  readable as a document with some fields filled in; this is the other half of
 *  that bargain, on the writing side. */
async function writeInstanceFile(layout: LocalDbLayout, doc: InstanceDocument): Promise<void> {
  const tmp = `${layout.instanceFile}.${process.pid}.${randomUUID()}.tmp`;
  await writeFile(tmp, serializeInstanceDocument(doc), "utf8");
  await rename(tmp, layout.instanceFile);
}

/**
 * The generated superuser password, read back from its 0600 file.
 *
 * Trimmed before validating, then checked against the alphabet it was generated
 * from. A trailing newline picked up from an editor, a text-mode copy or a sync
 * client produces a password that is silently wrong, and "password
 * authentication failed for user postgres" is a maddening thing to debug when
 * the file looks perfectly fine. The charset check turns that into a specific
 * complaint.
 *
 * The value is returned and never logged. Callers hand it straight to `pg` or to
 * adminConnectionUrl(); nothing formats it into a string a human will see.
 */
export async function readSecret(layout: LocalDbLayout): Promise<string> {
  const raw = await readFile(layout.secretFile, "utf8").catch(() => null);
  if (raw === null) {
    throw new LocalDbError(
      "secret-unreadable",
      "Balanced Sum can't find the file that holds your database's password, so it can't open your books. " +
      "Restore from a backup, or get in touch — your data hasn't been changed.",
      `no readable file at ${layout.secretFile}`,
    );
  }
  const secret = raw.trim();
  if (!isWellFormedSecret(secret)) {
    throw new LocalDbError(
      "secret-unreadable",
      "The file that holds your database's password has been changed or damaged, so Balanced Sum can't open your " +
      "books. Restore from a backup, or get in touch — your data hasn't been changed.",
      // Length only. The contents of this file never appear in a message.
      `${layout.secretFile} holds ${secret.length} character(s) outside the generated alphabet`,
    );
  }
  return secret;
}

/** True when the secret file holds something that could have come out of
 *  generateSecret(). Used by the cluster plan, which must not throw. */
async function secretUsable(layout: LocalDbLayout): Promise<boolean> {
  const raw = await readFile(layout.secretFile, "utf8").catch(() => null);
  return raw !== null && isWellFormedSecret(raw.trim());
}

/**
 * Generates the password and writes it 0600, BEFORE initdb, because initdb
 * consumes it via `--pwfile`.
 *
 * The mode matters on macOS, where 0600 is the difference between "only this
 * user" and "anyone on the machine". On Windows the POSIX mode is largely
 * decorative — Node maps it to the read-only attribute and nothing else — and
 * the real protection is that %LOCALAPPDATA% is already ACL'd to this user
 * alone. It is still requested on both, because a mode that is correct on one
 * platform and inert on the other is better than one that is absent on both,
 * and because the day a Linux build appears this line will already be right.
 *
 * `mode` on writeFile only applies when the file is CREATED, so chmod runs after
 * — otherwise a leftover 0644 file from an interrupted earlier attempt would be
 * silently reused at the wrong permissions.
 */
async function writeSecretFile(layout: LocalDbLayout): Promise<string> {
  const secret = generateSecret();
  // No trailing newline: initdb --pwfile reads the FIRST LINE, and keeping the
  // file to exactly the password means the bytes on disk and the password are
  // the same thing with no parsing in between.
  await writeFile(layout.secretFile, secret, { mode: 0o600, encoding: "utf8" });
  await chmod(layout.secretFile, 0o600).catch(() => {
    // Windows: chmod is a near no-op and can fail on some filesystems. The
    // directory's ACL is the real control there, so this is not worth failing a
    // first run over.
  });
  return secret;
}

/** The cluster's own record of its major version. PostgreSQL writes PG_VERSION
 *  inside the data directory and checks it on every start; it is the ground
 *  truth about what is on disk, which instance.json — our file, about our
 *  intentions — is not. */
async function readClusterMajor(layout: LocalDbLayout): Promise<number | null> {
  const raw = await readFile(path.join(layout.dataDir, "PG_VERSION"), "utf8").catch(() => null);
  if (raw === null) return null;
  return parsePostgresMajor(raw.trim());
}

// ---------------------------------------------------------------------------
// ensureCluster
// ---------------------------------------------------------------------------

export type EnsureClusterResult =
  /** initdb ran. This machine now has books it did not have five seconds ago. */
  | { action: "created"; instance: InstanceDocument }
  /** A healthy cluster was already here and its record checks out. */
  | { action: "existing"; instance: InstanceDocument }
  /** A healthy cluster was here with no instance.json; it was restated from the
   *  evidence on disk. Logged as a warning, never silently. */
  | { action: "recorded"; instance: InstanceDocument };

export interface EnsureClusterOptions {
  log?: RuntimeLogger;
  /** The port to record for a NEW cluster. Defaults to 54329 (instance.ts's
   *  DEFAULT_PORT). An existing cluster's port comes from its instance.json and
   *  this is ignored — the port is a fact about an install, not a preference. */
  port?: number;
  /** Recorded in instance.json for diagnostics. Defaults to the product
   *  version from the root package.json. */
  appVersion?: string;
  /** Frozen "now" for a deterministic createdAt. Tests only. */
  now?: () => Date;
}

/**
 * Creates the cluster if there isn't one, and validates it if there is.
 *
 * The ordering rule that everything else here bends around: **instance.json is
 * written only AFTER initdb reports success.** A recorded instance whose cluster
 * does not exist is a lie the rest of the system will believe — the launcher
 * would read a port and a version, build a connection string, and produce a
 * connection error for a database that was never created. Nothing is recorded
 * until there is something to record.
 *
 * Refuses, in plain English and without touching anything, whenever the answer
 * is not obvious: a newer cluster than this build can read, an older one (no
 * tested migration path yet), a record that disagrees with the disk, a data
 * directory whose password file is gone. See planCluster() in runtime-facts.ts
 * for the full table and the reasoning.
 */
export async function ensureCluster(
  layout: LocalDbLayout, options: EnsureClusterOptions = {},
): Promise<EnsureClusterResult> {
  const log = options.log ?? consoleRuntimeLogger;

  const plan = planCluster({
    clusterMajor: await readClusterMajor(layout),
    instance: await readInstanceFile(layout),
    secretUsable: await secretUsable(layout),
    shippedMajor: layout.postgresMajor,
  });

  switch (plan.action) {
    case "refuse":
      throw new LocalDbError(plan.code, plan.message, plan.detail);

    case "use":
      return { action: "existing", instance: plan.instance };

    case "rebuild-instance": {
      // Narrow, evidenced repair — see planCluster()'s note. Everything written
      // here is something we can point at: the major came from PG_VERSION, the
      // platform from the runtime that is about to open it, the port from the
      // constant this build has always used. Announced as a warning because a
      // missing instance.json means something deleted a file, and the user
      // should know that happened even though it has been fixed.
      log.warn(
        "The small file that records your database's settings was missing, so Balanced Sum rebuilt it from the " +
        "database itself. Your books are unaffected.",
      );
      const doc = buildInstanceDocument({
        postgresVersion: layout.postgresVersion,
        platform: layout.platform,
        port: options.port ?? DEFAULT_PORT,
        createdAt: (options.now ?? (() => new Date()))(),
        appVersion: options.appVersion ?? safeAppVersion(),
      });
      await mkdir(layout.root, { recursive: true });
      await writeInstanceFile(layout, doc);
      return { action: "recorded", instance: doc };
    }

    case "initdb":
      return { action: "created", instance: await initCluster(layout, options, log) };
  }
}

/** The product version, or a placeholder. This is a diagnostics field in
 *  instance.json; a missing package.json must not be able to stop a first run
 *  over it. */
function safeAppVersion(): string {
  try {
    return getVersion();
  } catch {
    return "unknown";
  }
}

async function initCluster(
  layout: LocalDbLayout, options: EnsureClusterOptions, log: RuntimeLogger,
): Promise<InstanceDocument> {
  const port = options.port ?? DEFAULT_PORT;

  await mkdir(layout.root, { recursive: true });
  await mkdir(layout.dataRoot, { recursive: true });
  await mkdir(layout.logsDir, { recursive: true });

  log.info("Setting up your database for the first time...");

  // The password exists before the cluster does, because initdb is what applies
  // it. If initdb then fails, both are removed together (below) — a secret file
  // with no cluster is confusing, and a cluster with a secret file we did not
  // write is impossible.
  await writeSecretFile(layout);

  // The exact command from the decisions document. scram-sha-256 on BOTH local
  // and host connections, and UTF-8, and a password read from a file rather than
  // typed on a command line where every process on the machine could read it out
  // of the process table.
  const args = [
    "-D", layout.dataDir,
    "-U", SUPERUSER_ROLE,
    "--auth-local=scram-sha-256",
    "--auth-host=scram-sha-256",
    "-E", "UTF8",
    `--pwfile=${layout.secretFile}`,
  ];

  const result = await runTool(binaryPath(layout, "initdb"), args);
  if (result.code !== 0) {
    // Leave nothing half-made. A partially initialised data directory looks
    // exactly like a real one to the "is there a cluster here" check (it has a
    // PG_VERSION long before it has a working catalog), and would then be
    // refused forever as "a cluster we can't explain" instead of simply being
    // retried.
    await rm(layout.dataDir, { recursive: true, force: true }).catch(() => {});
    await rm(layout.secretFile, { force: true }).catch(() => {});

    const said = summariseLogTail(`${result.stdout}\n${result.stderr}`, 8);
    throw new LocalDbError(
      "initdb-failed",
      "Balanced Sum couldn't create the database for your books on this computer. Nothing was left behind, so " +
      "it's safe to try again. If it keeps happening, get in touch and include the details below." +
      (said ? `\n\n${said}` : ""),
      `initdb exited ${result.code}: ${result.stderr.trim() || result.stdout.trim()}`,
    );
  }

  // Only now. See this function's caller for why the ordering is the whole
  // point.
  const doc = buildInstanceDocument({
    postgresVersion: layout.postgresVersion,
    platform: layout.platform,
    port,
    createdAt: (options.now ?? (() => new Date()))(),
    appVersion: options.appVersion ?? safeAppVersion(),
  });
  await writeInstanceFile(layout, doc);

  log.info(`Your database is set up (PostgreSQL ${layout.postgresVersion}, port ${port}).`);
  return doc;
}

// ---------------------------------------------------------------------------
// isRunning
// ---------------------------------------------------------------------------

export interface RunningStatus {
  running: boolean;
  /** The postmaster's process id when we could establish one. */
  pid: number | null;
  /** The port it is on — from postmaster.pid when there is one, otherwise from
   *  instance.json. */
  port: number | null;
  /** The full verdict, including the "we can't tell" case. `running: false` with
   *  a verdict of `unknown` is NOT permission to start. */
  verdict: LockVerdict;
}

/**
 * Whether a postmaster owns this data directory right now.
 *
 * Three independent sources of evidence, because none of them is sufficient
 * alone: the lock file (which survives a power cut), the process table (which
 * recycles PIDs), and the port (which another PostgreSQL could be holding). The
 * pure combination rule is judgePostmasterLock(); this function's whole job is
 * gathering the facts honestly and not deciding anything itself.
 */
export async function isRunning(layout: LocalDbLayout): Promise<RunningStatus> {
  const raw = await readFile(layout.postmasterPidFile, "utf8").catch(() => null);
  const pidFile = parsePostmasterPid(raw);

  // Which port to probe: the running server's own claim first, our record
  // second, the built-in default last. A postmaster.pid that names a DIFFERENT
  // port than instance.json is exactly the case where believing our own file
  // would report "nothing is running" about a live server.
  const recorded = await readInstanceFile(layout);
  const port = pidFile.ok
    ? pidFile.info.port
    : (recorded.ok ? recorded.instance.port : DEFAULT_PORT);

  const owner: ProcessOwner = pidFile.ok
    ? await inspectProcessOwner(layout, pidFile.info.pid)
    : "unknown";

  const verdict = judgePostmasterLock({
    pidFile,
    owner,
    portAnswering: isResponding(await pgIsReadyCode(layout, port)),
  });

  return {
    running: verdict.state === "running",
    pid: verdict.state === "running" ? verdict.pid : null,
    port: verdict.state === "running" ? (verdict.port ?? port) : port,
    verdict,
  };
}

// ---------------------------------------------------------------------------
// start
// ---------------------------------------------------------------------------

export type StartResult =
  | { action: "started"; pid: number; port: number; durationMs: number }
  | { action: "already-running"; pid: number | null; port: number };

export interface StartOptions {
  log?: RuntimeLogger;
  /** Overrides the readiness budget. Tests only — the default is measured, see
   *  READY_TIMEOUT_MS. */
  readyTimeoutMs?: number;
}

/**
 * Starts the bundled postmaster and waits until it is accepting connections.
 *
 * NOT `pg_ctl start`. Finding #1 from the spike, restated because it is the
 * single most expensive thing in this file to rediscover: pg_ctl returns
 * promptly, but the postmaster it forks inherits the Node parent's stdout
 * handle, and the parent then blocks forever on a pipe nothing will ever close.
 * `postgres` is spawned directly instead, `detached`, with stdout AND stderr
 * pointed at the log file — no inherited pipe exists to wait on — and then
 * `unref`ed so this process can exit while the database keeps running. That last
 * part is verified behaviour, not hope: the spike confirmed the server survived
 * its parent exiting.
 *
 * The two `-c` flags are the entire network posture of a local-first product.
 * `listen_addresses=127.0.0.1` means nothing outside this machine can reach the
 * database; an empty `unix_socket_directories` means there is no filesystem
 * socket either, so loopback TCP with scram-sha-256 is the only way in.
 */
export async function start(layout: LocalDbLayout, options: StartOptions = {}): Promise<StartResult> {
  const log = options.log ?? consoleRuntimeLogger;
  const instance = await readInstance(layout);
  const port = instance.port;

  const status = await isRunning(layout);
  if (status.verdict.state === "running") {
    // `pid: null` means judgePostmasterLock found NO postmaster.pid in our data
    // directory and yet something is answering on our port. That is not our
    // cluster — it is someone else's PostgreSQL, or a second Balanced Sum
    // installation with its own data directory, holding the port we want.
    //
    // Treating it as "already running" is how you end up pointing the
    // application at a stranger's database. The first clean-machine install of
    // 2.0.1 did exactly that: it initdb'd its own cluster, saw port 54329
    // answering, announced "the database is already running", and then tried to
    // log in to the OTHER installation with its own generated password. It got
    // an authentication failure, which was lucky. Two Balanced Sum installs
    // share a superuser NAME, and the only reason this stopped at a confusing
    // error rather than a stranger's books is that the passwords are random.
    //
    // So: our own live postmaster is "already running". Anything else on our
    // port is a conflict, and conflicts get refused by name.
    if (status.pid === null) {
      throw new LocalDbError(
        "port-in-use",
        `Something else on this computer is already using port ${port}, which is the port Balanced Sum keeps ` +
        "your books on. That is usually another copy of Balanced Sum running from a different folder — close " +
        "it and try again. If you run PostgreSQL yourself for other work, it may be that instead; Balanced " +
        "Sum will not touch a database it did not create.",
        `port ${port} is answering but ${layout.postmasterPidFile} does not exist — not our cluster`,
      );
    }
    log.info("The database is already running.");
    return { action: "already-running", pid: status.pid, port: status.port ?? port };
  }
  if (status.verdict.state === "unknown") {
    throw new LocalDbError("lock-undecidable", status.verdict.message, "postmaster.pid could not be adjudicated");
  }
  if (status.verdict.state === "stale") {
    // Trap #2 in the decisions document: ordinary, not exceptional. Cleaned up
    // and explained in one sentence, and the user is never asked to go and find
    // a file called postmaster.pid.
    log.warn(status.verdict.why);
    await rm(layout.postmasterPidFile, { force: true });
  }

  await mkdir(layout.logsDir, { recursive: true });

  // A dated banner before every start. When something goes wrong, the log is
  // where the answer is (finding #3), and a log with no boundaries between runs
  // is one where the FATAL from three weeks ago reads as today's.
  const banner = `\n===== Balanced Sum starting PostgreSQL ${layout.postgresVersion} on port ${port} at ${new Date().toISOString()} =====\n`;
  await writeFile(layout.logFile, banner, { flag: "a", encoding: "utf8" });

  // The child needs a real file descriptor for stdout/stderr — this is the
  // fix for finding #1 and it is why the handle is opened by hand rather than
  // using stdio: "ignore" (which would throw the postmaster's own explanation of
  // any failure into the void) or "inherit" (which is the hang).
  const logHandle = await open(layout.logFile, "a");

  const started = Date.now();
  let child: ChildProcess;
  try {
    child = spawn(
      binaryPath(layout, "postgres"),
      [
        "-D", layout.dataDir,
        "-p", String(port),
        "-c", `listen_addresses=${LOOPBACK_HOST}`,
        "-c", "unix_socket_directories=",
      ],
      { detached: true, stdio: ["ignore", logHandle.fd, logHandle.fd], windowsHide: true },
    );
  } catch (err) {
    await logHandle.close().catch(() => {});
    throw new LocalDbError(
      "start-failed",
      "Balanced Sum couldn't start the database on this computer. Try again, and if it keeps happening, get in " +
      "touch.",
      `spawning postgres failed: ${(err as Error).message}`,
    );
  }
  // The child got its own duplicate of the descriptor at spawn time, so the
  // parent's copy is dead weight — and on Windows an open handle to the log
  // would stop anything else from rotating or deleting it.
  await logHandle.close().catch(() => {});

  // Held on an object rather than in three `let`s: every field is written from
  // an event callback and read from the polling loop below, and a plain `let`
  // assigned only inside a closure is exactly the shape TypeScript's flow
  // analysis reasons about least usefully.
  const state: { exited: boolean; exitCode: number | null; spawnError: Error | null } =
    { exited: false, exitCode: null, spawnError: null };
  child.on("exit", (code) => { state.exited = true; state.exitCode = code; });
  child.on("error", (err) => { state.spawnError = err; });
  // Detach from this process's event loop so the server outlives us. Events
  // registered above still fire for as long as we are alive, which is the whole
  // window we care about.
  child.unref();

  const budget = options.readyTimeoutMs ?? READY_TIMEOUT_MS;
  const deadline = started + budget;

  log.info("Starting the database...");
  for (;;) {
    if (state.spawnError) {
      throw new LocalDbError(
        "start-failed",
        "Balanced Sum couldn't start the database on this computer. Try again, and if it keeps happening, get in " +
        "touch.",
        `spawning postgres failed: ${state.spawnError.message}`,
      );
    }

    // Checked BEFORE pg_isready, deliberately. A postmaster that dies on startup
    // — the port is taken, the data directory is locked, PostgreSQL refuses to
    // run under an administrator account — is the common failure, and its
    // explanation is already sitting in the log. Polling a dead process for
    // thirty seconds and then reporting a timeout would bury it.
    if (state.exited) {
      const tail = summariseLogTail(await tailLogFile(layout), 8);
      throw new LocalDbError(
        "start-failed",
        "The database stopped as soon as it started. This usually means it's already running, or another program " +
        "is using the same port on this computer." +
        (tail ? `\n\nWhat PostgreSQL said:\n${tail}` : "") +
        `\n\nThe full log is at ${layout.logFile}`,
        `postgres exited ${state.exitCode} after ${Date.now() - started}ms`,
      );
    }

    // Socket first, pg_isready only if something answered. See portAccepting():
    // on a machine whose antivirus is scanning the binaries we just unpacked —
    // i.e. every first launch — spawning pg_isready costs more than a second,
    // and polling with it alone spends the whole readiness budget on the asking
    // rather than on the waiting.
    if (await portAccepting(port) && isAccepting(await pgIsReadyCode(layout, port))) {
      const durationMs = Date.now() - started;
      // Host and port only. The connection STRING is never logged from here,
      // because building one requires the password — see adminConnection(),
      // which is the only function that touches it and which hands back a
      // pre-redacted form so no caller has to remember.
      log.info(`The database is ready on ${LOOPBACK_HOST}:${port} (${(durationMs / 1000).toFixed(1)}s).`);
      return { action: "started", pid: child.pid ?? 0, port, durationMs };
    }

    if (Date.now() >= deadline) {
      // NOT killed. A postmaster this slow is almost always replaying WAL after
      // an unclean shutdown, and killing it mid-recovery turns a slow start into
      // a damaged cluster. It is left alone, running, and the user is told the
      // truth: it is still starting.
      const tail = summariseLogTail(await tailLogFile(layout), 8);
      throw new LocalDbError(
        "start-failed",
        `The database didn't finish starting within ${Math.round(budget / 1000)} seconds. It may still be ` +
        "recovering from a computer that was switched off without shutting down — that can take a while on a " +
        "large set of books. Wait a minute and open Balanced Sum again." +
        (tail ? `\n\nWhat PostgreSQL said:\n${tail}` : "") +
        `\n\nThe full log is at ${layout.logFile}`,
        `no accepting connection on ${LOOPBACK_HOST}:${port} within ${budget}ms`,
      );
    }

    await delay(READY_POLL_INTERVAL_MS);
  }
}

/** The last few KB of the PostgreSQL log. Capped because this text ends up
 *  inside an error message: the answer to "why didn't it start" is always in the
 *  final handful of lines, and a log that has been running for months must not
 *  be able to turn one failure into a screenful. */
async function tailLogFile(layout: LocalDbLayout, maxBytes = 8192): Promise<string> {
  const handle = await open(layout.logFile, "r").catch(() => null);
  if (!handle) return "";
  try {
    const { size } = await handle.stat();
    const start = Math.max(0, size - maxBytes);
    const buffer = Buffer.alloc(Math.min(maxBytes, size));
    if (buffer.length === 0) return "";
    await handle.read(buffer, 0, buffer.length, start);
    return buffer.toString("utf8");
  } catch {
    return "";
  } finally {
    await handle.close().catch(() => {});
  }
}

// ---------------------------------------------------------------------------
// stop
// ---------------------------------------------------------------------------

export type StopResult =
  | { action: "stopped"; durationMs: number }
  | { action: "not-running" };

export interface StopOptions {
  log?: RuntimeLogger;
  /** Seconds pg_ctl waits for shutdown. Tests only. */
  timeoutSeconds?: number;
}

/**
 * Stops the bundled postmaster with `pg_ctl stop -m fast -w`.
 *
 * pg_ctl IS the right tool here, and the asymmetry with start() is deliberate,
 * not an inconsistency: the hang in finding #1 comes from the long-lived
 * postmaster pg_ctl FORKS at start time inheriting our stdout. A stop forks
 * nothing — pg_ctl signals the running server, waits, and exits, verified at
 * exit 0 in the spike. Reimplementing "signal the postmaster, then poll for the
 * lock file to disappear" by hand would be strictly more code doing strictly
 * less (pg_ctl also handles the modes, the timeout, and the shutdown checkpoint
 * wait) for no gain.
 *
 * `-m fast` rather than `smart`: fast disconnects clients and shuts down
 * cleanly with a checkpoint. `smart` waits for every client to disconnect
 * voluntarily, which on an app that just crashed means waiting forever.
 */
export async function stop(layout: LocalDbLayout, options: StopOptions = {}): Promise<StopResult> {
  const log = options.log ?? consoleRuntimeLogger;

  const status = await isRunning(layout);
  if (!status.running) return { action: "not-running" };

  const started = Date.now();
  log.info("Shutting the database down...");
  const result = await runTool(binaryPath(layout, "pg_ctl"), [
    "-D", layout.dataDir,
    "stop",
    "-m", "fast",
    "-w",
    "-t", String(options.timeoutSeconds ?? STOP_TIMEOUT_SECONDS),
  ]);

  if (result.code !== 0) {
    const said = summariseLogTail(`${result.stdout}\n${result.stderr}`, 6);
    throw new LocalDbError(
      "stop-failed",
      "Balanced Sum couldn't shut the database down cleanly. It's safe to leave it running — your books are not " +
      "at risk — but if you're about to switch this computer off, close Balanced Sum and try again first." +
      (said ? `\n\n${said}` : ""),
      `pg_ctl stop exited ${result.code}`,
    );
  }

  const durationMs = Date.now() - started;
  log.info(`The database has shut down (${(durationMs / 1000).toFixed(1)}s).`);
  return { action: "stopped", durationMs };
}

// ---------------------------------------------------------------------------
// connecting
// ---------------------------------------------------------------------------

export interface AdminConnection {
  instance: InstanceDocument;
  /** Hand this to `pg`. Never print it. */
  url: string;
  /** Print THIS. Password replaced with ***. */
  redactedUrl: string;
}

/**
 * The superuser connection string for the local cluster — migrations, company
 * creation, pg_dump.
 *
 * Returns the safe-to-log form beside the real one so a caller never has to
 * remember to redact. That is not a nicety: everything else about the password
 * is enforced by where it lives, and the one remaining way for it to escape is
 * somebody logging a connection string they built themselves.
 */
export async function adminConnection(
  layout: LocalDbLayout, database: string = APP_DATABASE,
): Promise<AdminConnection> {
  const instance = await readInstance(layout);
  const password = await readSecret(layout);
  const url = adminConnectionUrl(instance, password, database);
  return { instance, url, redactedUrl: redactConnectionUrl(url) };
}
