// Two-pool DB access for the server, per docs/superpowers/plans/2026-07-31-local-app.md
// Task 2 and docs/superpowers/specs/2026-07-31-local-app-design.md.
//
//  - appPool: the `app` role, RLS-bound. Every request that touches
//    tenant-scoped data goes through withCompany(), which sets
//    app.company_id for the duration of one transaction (CLAUDE.md §4).
//  - adminPool: the superuser connection (same one migrations/tests use).
//    ONLY for company creation/setup paths that must run before a company
//    (and therefore an RLS scope) exists at all.
//
// Both pools get a short connectionTimeoutMillis so a down/unreachable
// Postgres fails fast instead of hanging a request indefinitely.
//
// WHERE THE CONNECTION STRINGS COME FROM (PG Task 3 —
// docs/todo/2026-08-08-bundled-postgres-decisions.md). Until Docker was
// removed, both defaults were literals: `postgres://postgres:postgres@
// localhost:5432/accounting` and its `app:app` twin, matching what
// docker-compose.yml created. Both are now wrong in every particular — the
// bundled cluster listens on 54329, and its superuser password is generated
// per install and lives only in a 0600 file. So the defaults are resolved from
// the bundled instance instead, and resolveDatabaseUrls() below is the single
// place that decides.

import { Pool, type PoolClient } from "pg";

import { resolveCurrentLayout, readInstance, readSecret } from "./localdb/runtime.js";
import {
  adminConnectionUrl, appConnectionUrl, buildConnectionUrl,
  APP_DATABASE, APP_ROLE, DEFAULT_PORT, LOOPBACK_HOST, SUPERUSER_ROLE,
} from "./localdb/instance.js";

declare module "fastify" {
  interface FastifyInstance {
    db: Db;
  }
}

/**
 * The `app` role's password, as set by db/migrations/0001_companies_and_users.sql
 * (`CREATE ROLE app LOGIN PASSWORD 'app'`).
 *
 * It is NOT the generated per-install secret, and that is deliberate rather
 * than an oversight: the app role's password is written into a migration, which
 * means it is identical on every install and in git — so treating it as a
 * secret would be a lie. What actually protects this role is that the cluster
 * binds 127.0.0.1 with no Unix socket (see localdb/instance.ts's LOOPBACK_HOST
 * note): nothing off this machine can present it. Changing it is a migration
 * decision, not a connection-string one, and this constant exists so the two
 * spellings cannot drift.
 */
const APP_ROLE_PASSWORD = "app";

/**
 * How long a request may wait to get a client out of a pool before giving up.
 *
 * FIFTEEN SECONDS, MEASURED, AND IT USED TO BE THREE. The three-second figure
 * was written against the Docker container, where opening a PostgreSQL
 * connection on Linux costs single-digit milliseconds. On the bundled cluster
 * this product now ships it does not:
 *
 *     bundled cluster, Windows 11, 2026-08-08
 *       5 connections opened serially   2042 ms   (~408 ms each)
 *       20 connections opened at once   5732 ms
 *
 * ~400ms per connection is a Windows fact, not a bug in this code — the
 * postmaster forks a real `postgres.exe` per backend, the machine's antivirus
 * inspects each one, and scram-sha-256 then does its key derivation. A default
 * pg.Pool holds ten clients, so simply FILLING one from cold costs about three
 * seconds. At the old budget, a browser firing a handful of parallel requests
 * at a freshly started database got `Connection terminated due to connection
 * timeout` 500s — reproduced by tests/server/setup.test.ts's twenty concurrent
 * chart uploads, which passed against Docker, passed against the bundled
 * cluster in isolation, and failed against the bundled cluster under the load
 * of a full test run. A user opening the app for the first time each morning is
 * in exactly that state.
 *
 * Raising it costs nothing in the case this constant exists for. When Postgres
 * is DOWN, connect() fails immediately with ECONNREFUSED and never consults
 * this budget at all; it only governs "the server is there but slow", where
 * failing at three seconds was throwing away a request that was about to
 * succeed. Fifteen seconds is ~5x the measured cold pool fill and still bounds
 * a pathological wait well inside anyone's patience.
 */
const CONNECTION_TIMEOUT_MILLIS = 15000;

export interface DbOptions {
  /** Overrides APP_DATABASE_URL / the bundled instance — used by tests to point
   *  at a throwaway DB. */
  appDatabaseUrl?: string;
  /** Overrides DATABASE_URL / the bundled instance — used by tests to point at
   *  a throwaway DB. */
  adminDatabaseUrl?: string;
}

/** Where each URL in a resolution came from. Diagnostics only — nothing
 *  branches on it; it exists so a caller can say "using the database you
 *  configured" versus "using the one Balanced Sum installed" without
 *  re-deriving the answer. */
export type DatabaseUrlSource = "environment" | "bundled" | "unconfigured";

export interface ResolvedDatabaseUrls {
  /** The RLS-bound `app` role. */
  app: string;
  /** The superuser. Carries the generated password — never log it; every
   *  message that shows a connection goes through redactConnectionUrl(). */
  admin: string;
  source: DatabaseUrlSource;
}

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

/**
 * The `app`-role URL implied by an admin URL: same host, same port, same
 * database, different role.
 *
 * This is what preserves the pre-Docker-removal behaviour exactly for a
 * developer who sets only DATABASE_URL. Before, an unset APP_DATABASE_URL fell
 * back to a hardcoded `app:app@localhost:5432/accounting` — which happened to
 * agree with the hardcoded admin default. Falling back to the BUNDLED app URL
 * instead would silently send the two pools of one server to two different
 * databases (their 5432, our 54329), and RLS-scoped reads returning an empty
 * but otherwise healthy database is about the most confusing failure this
 * codebase could produce. The rule is therefore: the app connection follows
 * wherever the admin connection points, unless it was named explicitly.
 *
 * `new URL` mutation rather than instance.ts's buildConnectionUrl(): the input
 * is an already-valid URL whose host may be IPv6 or otherwise awkward to take
 * apart and put back together, and the only components being replaced are a
 * role name and a password that are both fixed, git-committed ASCII literals —
 * none of the round-trip hazards instance.ts's header warns about apply.
 */
function appUrlFrom(adminUrl: string): string | undefined {
  try {
    const u = new URL(adminUrl);
    u.username = APP_ROLE;
    u.password = APP_ROLE_PASSWORD;
    return u.toString();
  } catch {
    // Not every string node-postgres accepts is a URL — a libpq keyword string
    // ("host=localhost dbname=accounting") is the realistic case. There is no
    // app URL to derive from one, and the caller has to decide what to do about
    // that; see resolveDatabaseUrls(), where the answer is emphatically not
    // "use the superuser connection then".
    return undefined;
  }
}

/** The bundled cluster's own URLs, or null when this machine has no cluster
 *  (a genuine first run) or cannot be asked (an unsupported platform, no
 *  LOCALAPPDATA). Never throws — see resolveDatabaseUrls(). */
async function bundledUrls(): Promise<{ app: string; admin: string } | null> {
  try {
    const layout = resolveCurrentLayout();
    const instance = await readInstance(layout);
    const secret = await readSecret(layout);
    return {
      app: appConnectionUrl(instance, APP_ROLE_PASSWORD, APP_DATABASE),
      admin: adminConnectionUrl(instance, secret, APP_DATABASE),
    };
  } catch (err) {
    // "No database has been set up on this computer yet" is an ordinary answer
    // here and stays quiet. Anything else — a damaged instance.json, a secret
    // file that has been edited — is worth saying out loud, because the user is
    // about to get a connection error whose real cause is this. It is still not
    // thrown: see resolveDatabaseUrls().
    const message = (err as Error).message;
    if (!/^No database has been set up/.test(message)) {
      console.error(`[db] couldn't read this computer's database settings: ${message}`);
    }
    return null;
  }
}

/**
 * Works out which database this process talks to. Explicit options win, then
 * the environment, then the bundled instance.
 *
 * NEVER THROWS, and that is a deliberate constraint rather than laziness.
 * buildApp() must be constructible on a machine with no database at all — the
 * update-feed tests build a real app without one, and /api/health's entire job
 * is to answer `db: false` rather than to crash. A pg.Pool does not connect
 * until it is used, so an unusable connection string produces a failed query
 * (which every caller already handles) instead of a failed startup. When
 * nothing can be resolved the result is therefore the bundled DEFAULTS —
 * correct host, correct port, no password — which fail at connect time with a
 * Postgres error, exactly as the old `localhost:5432` literals did when Docker
 * was not running.
 *
 * The one path that genuinely must not proceed without a database is
 * `npm run app`, and scripts/app.ts resolves its own connection through
 * localdb/runtime.ts's adminConnection() — which DOES throw, in complete
 * English sentences. Being loud is that launcher's job; being available is
 * this function's.
 */
export async function resolveDatabaseUrls(opts: DbOptions = {}): Promise<ResolvedDatabaseUrls> {
  const adminOverride = opts.adminDatabaseUrl ?? configured(process.env.DATABASE_URL);
  const appOverride = opts.appDatabaseUrl ?? configured(process.env.APP_DATABASE_URL);

  if (adminOverride) {
    let app = appOverride ?? appUrlFrom(adminOverride);
    if (app === undefined) {
      // DATABASE_URL is something `new URL()` can't take apart — a libpq
      // keyword string ("host=... dbname=..."), which node-postgres accepts
      // perfectly well, is the realistic case. The app URL cannot be derived
      // from it, and the ONE answer that must never be reached here is "use the
      // admin URL then": that would hand the superuser connection to the
      // RLS-bound pool, and a superuser bypasses every policy in CLAUDE.md §4
      // while looking, from the outside, like a working application. Fall back
      // to the unusable default instead — a connection error is recoverable,
      // silently serving one company's data to another is not.
      console.error(
        "[db] APP_DATABASE_URL is not set and DATABASE_URL isn't in a form the app connection can be derived from. " +
        "Set APP_DATABASE_URL explicitly. Refusing to fall back to the superuser connection, which would disable " +
        "row-level security.",
      );
      app = unconfiguredAppUrl();
    }
    return { admin: adminOverride, app, source: "environment" };
  }

  const local = await bundledUrls();
  if (local) {
    return { admin: local.admin, app: appOverride ?? local.app, source: "bundled" };
  }

  // Nothing configured and nothing installed. Point at where the bundled
  // cluster WOULD be, so the eventual error is "connection refused on 54329"
  // (a true statement about this product) rather than a connection to whatever
  // unrelated PostgreSQL a developer happens to have on 5432.
  return {
    admin: buildConnectionUrl({
      user: SUPERUSER_ROLE, password: "", host: LOOPBACK_HOST, port: DEFAULT_PORT, database: APP_DATABASE,
    }),
    app: appOverride ?? unconfiguredAppUrl(),
    source: "unconfigured",
  };
}

/** The `app`-role connection to a bundled cluster that may not exist. Used
 *  wherever the honest answer is "we don't know where the database is": it
 *  names the right host, port and role, so it either happens to work or fails
 *  with a Postgres error naming our own port. */
function unconfiguredAppUrl(): string {
  return buildConnectionUrl({
    user: APP_ROLE, password: APP_ROLE_PASSWORD, host: LOOPBACK_HOST, port: DEFAULT_PORT, database: APP_DATABASE,
  });
}

export interface Db {
  /** The `app` role, RLS-bound. Prefer withCompany() over using this directly. */
  appPool: Pool;
  /** Superuser connection. Only for company creation/setup paths. */
  adminPool: Pool;
  /**
   * Runs `fn` with a checked-out `app`-role client, inside a transaction that
   * has set `app.company_id` (transaction-local) to `companyId` — every
   * query `fn` issues is RLS-scoped to that one company. Commits on success,
   * rolls back and rethrows on error, always releases the client back to the
   * pool.
   */
  withCompany<T>(companyId: string, fn: (client: PoolClient) => Promise<T>): Promise<T>;
  /** Closes both pools. Safe to call once, e.g. from a Fastify onClose hook. */
  close(): Promise<void>;
}

/**
 * Builds both pools.
 *
 * Async only because resolveDatabaseUrls() has to read two small files to find
 * the bundled cluster; no connection is opened here, and this still returns
 * before Postgres has been spoken to. It is awaited in exactly one place
 * (buildApp), and it cannot reject — see resolveDatabaseUrls().
 */
export async function createDb(opts: DbOptions = {}): Promise<Db> {
  const urls = await resolveDatabaseUrls(opts);

  const appPool = new Pool({
    connectionString: urls.app,
    connectionTimeoutMillis: CONNECTION_TIMEOUT_MILLIS,
  });
  const adminPool = new Pool({
    connectionString: urls.admin,
    connectionTimeoutMillis: CONNECTION_TIMEOUT_MILLIS,
  });

  // -------------------------------------------------------------------------
  // Why these two listeners are not optional.
  // -------------------------------------------------------------------------
  //
  // A pg.Pool is an EventEmitter, and it emits 'error' when an IDLE pooled
  // client's connection dies with nobody awaiting it — Postgres restarting, the
  // bundled cluster being shut down by a second copy of the launcher, an
  // idle-timeout kill, a dropped socket. EventEmitter's contract is that an
  // 'error' with NO listener is rethrown as an uncaught exception, so the
  // default behaviour here is "a Postgres blip takes down the whole API
  // process".
  //
  // Under vitest it is worse than a crash, because it is SILENT. The uncaught
  // exception kills the fork worker AFTER its tests have already passed, so
  // that file's results are simply lost from the run and its throwaway
  // database is orphaned — the totals come back short and nothing says why.
  // This was reproduced during review: 32 tests vanished (accounts-api.test.ts)
  // and the same run passed with these listeners attached. A gate that can be
  // silently voided isn't a gate.
  //
  // Log and swallow is the CORRECT handling, not a shortcut: node-postgres has
  // already removed the dead client from the pool by the time this fires, and
  // the next connect() gets a fresh one. There is no state to repair here and
  // nothing in flight to fail — an idle client is by definition not serving a
  // request. A request that IS in flight when its own connection dies rejects
  // through withCompany()'s try/catch as it always did; this path is only ever
  // about the ones that weren't.
  const swallowIdleClientError = (label: string) => (err: Error) => {
    // console, not the Fastify logger: createDb() runs before (and outside)
    // any Fastify instance, and this must never be able to throw itself.
    console.error(`[db] idle ${label} client error (pool recovers, request path unaffected):`, err.message);
  };
  appPool.on("error", swallowIdleClientError("app"));
  adminPool.on("error", swallowIdleClientError("admin"));

  async function withCompany<T>(
    companyId: string, fn: (client: PoolClient) => Promise<T>,
  ): Promise<T> {
    const client = await appPool.connect();
    try {
      await client.query("BEGIN");
      // `true` = transaction-local: reverts automatically at COMMIT/ROLLBACK,
      // so a pooled connection can never leak one request's company scope
      // into the next request that borrows it.
      await client.query("SELECT set_config('app.company_id', $1, true)", [companyId]);
      const result = await fn(client);
      await client.query("COMMIT");
      return result;
    } catch (err) {
      await client.query("ROLLBACK").catch(() => {
        // best effort — the connection may already be broken
      });
      throw err;
    } finally {
      client.release();
    }
  }

  async function close(): Promise<void> {
    await Promise.all([appPool.end(), adminPool.end()]);
  }

  return { appPool, adminPool, withCompany, close };
}
