import pg from "pg";
import { createPgD1, type PgD1Database } from "./pgD1";

/**
 * PostgreSQL connection pool and the D1-shaped handle bound to `env.DB`.
 */

/**
 * `pg` parses INT8 (BIGINT) into a JS string by default, because 64-bit
 * integers can exceed Number.MAX_SAFE_INTEGER. Every BIGINT in this schema is
 * a token count, a cost in micro-USD, or a GitHub id — all far below 2^53 —
 * and the code treats them as numbers (`meta.changes > 0`, token arithmetic).
 * Returning strings would silently turn addition into concatenation.
 */
pg.types.setTypeParser(pg.types.builtins.INT8, (value: string) => Number.parseInt(value, 10));

let pool: pg.Pool | null = null;
let database: PgD1Database | null = null;

export interface PgConfig {
  connectionString?: string;
  max?: number;
  ssl?: boolean;
}

function resolveConfig(config: PgConfig = {}): pg.PoolConfig {
  const connectionString = config.connectionString ?? process.env.DATABASE_URL ?? "postgresql://postgres:postgres@localhost:5432/agent_kanban";

  const ssl = config.ssl ?? process.env.DATABASE_SSL === "true";

  return {
    connectionString,
    // Cap connections so a burst of SSE streams plus the cron sweep can't
    // exhaust the server's max_connections.
    max: config.max ?? Number.parseInt(process.env.DATABASE_POOL_MAX ?? "10", 10),
    idleTimeoutMillis: 30_000,
    connectionTimeoutMillis: 10_000,
    // Self-hosted PostgreSQL commonly uses a self-signed certificate.
    ssl: ssl ? { rejectUnauthorized: false } : undefined,
  };
}

export function getPool(config?: PgConfig): pg.Pool {
  if (!pool) {
    pool = new pg.Pool(resolveConfig(config));
    // An idle client erroring (server restart, network drop) emits on the pool
    // rather than on any one query. Without a listener Node treats it as an
    // unhandled 'error' event and kills the process.
    pool.on("error", (err) => {
      console.error("[pg] idle client error:", err.message);
    });
  }
  return pool;
}

/** The handle to bind to `env.DB`. Supports both `.prepare()` and `.query()`. */
export function getDatabase(config?: PgConfig): PgD1Database {
  if (!database) database = createPgD1(getPool(config));
  return database;
}

/** Verify connectivity and that migrations have run. Called at boot. */
export async function assertDatabaseReady(): Promise<void> {
  const result = await getPool().query<{ ok: boolean }>("SELECT to_regclass('public.session_events') IS NOT NULL AS ok");
  if (!result.rows[0]?.ok) {
    throw new Error("Database schema is missing (table 'session_events' not found). Run `pnpm db:pg:migrate` before starting the server.");
  }
}

export async function closePool(): Promise<void> {
  if (pool) {
    await pool.end();
    pool = null;
    database = null;
  }
}

/** Escape hatch for scripts that need raw SQL without the D1 facade. */
export async function query<T extends pg.QueryResultRow = pg.QueryResultRow>(text: string, params?: unknown[]): Promise<pg.QueryResult<T>> {
  return getPool().query<T>(text, params);
}