/**
 * PostgreSQL connection pool and transaction helpers.
 * Replaces ingestion/db.py — uses DATABASE_URL env var (set by devenv.nix).
 */

import pg from "pg";
import pgvector from "pgvector/pg";

const { Pool } = pg;

let pool: pg.Pool | null = null;

/** Get or create the shared connection pool. */
export function getPool(): pg.Pool {
  if (!pool) {
    pool = new Pool({
      connectionString: process.env.DATABASE_URL || "postgresql:///cultguard",
    });
    pool.on("error", (err) => {
      console.error("[db] Unexpected pool error:", err.message);
    });
  }
  return pool;
}

/** Register pgvector type on a client connection. */
async function registerVector(client: pg.PoolClient): Promise<void> {
  await pgvector.registerType(client);
}

/**
 * Run a function inside a transaction.
 * Automatically BEGINs, COMMITs on success, ROLLBACKs on error.
 */
export async function withTransaction<T>(
  fn: (client: pg.PoolClient) => Promise<T>,
): Promise<T> {
  const client = await getPool().connect();
  try {
    await registerVector(client);
    await client.query("BEGIN");
    const result = await fn(client);
    await client.query("COMMIT");
    return result;
  } catch (err) {
    try { await client.query("ROLLBACK"); } catch { /* ignore rollback errors to preserve original */ }
    throw err;
  } finally {
    client.release();
  }
}

/**
 * Run a single query (no explicit transaction — auto-commit).
 * Good for SELECTs and simple single-statement writes.
 */
export async function query<T extends pg.QueryResultRow = Record<string, unknown>>(
  text: string,
  values?: unknown[],
): Promise<pg.QueryResult<T>> {
  const client = await getPool().connect();
  try {
    await registerVector(client);
    return await client.query<T>(text, values);
  } finally {
    client.release();
  }
}

/** Ensure an investigation row exists (idempotent). */
export async function ensureInvestigation(
  client: pg.PoolClient,
  id: string,
): Promise<void> {
  await client.query(
    `INSERT INTO investigations (id, name) VALUES ($1, $1) ON CONFLICT (id) DO NOTHING`,
    [id],
  );
}

/** Gracefully close the pool (call on process exit). */
export async function closePool(): Promise<void> {
  if (pool) {
    await pool.end();
    pool = null;
  }
}

/** ISO timestamp for collected_at fields. */
export function now(): string {
  return new Date().toISOString();
}