import pg from "pg";
import { config } from "../config.js";

const globalForDb = globalThis as unknown as { bouslaPool?: pg.Pool };

export const pool: pg.Pool =
  globalForDb.bouslaPool ??
  new pg.Pool({
    host: config.db.host,
    port: config.db.port,
    database: config.db.database,
    user: config.db.user,
    password: config.db.password,
    max: 10,
    idleTimeoutMillis: 30_000,
    connectionTimeoutMillis: 10_000,
  });

// Reference arrays from queries come back as Postgres arrays; ensure numerics parse.
pg.types.setTypeParser(pg.types.builtins.NUMERIC, (val) => (val === null ? null : Number(val)));
// Return BIGINT ids as JS numbers (our ids never approach 2^53)
pg.types.setTypeParser(pg.types.builtins.INT8, (val) => (val === null ? null : Number.parseInt(val, 10)));

globalForDb.bouslaPool = pool;

export async function query<T extends pg.QueryResultRow = pg.QueryResultRow>(
  text: string,
  params: readonly unknown[] = [],
): Promise<pg.QueryResult<T>> {
  return pool.query<T>(text, params as unknown[]);
}

export async function one<T extends pg.QueryResultRow = pg.QueryResultRow>(
  text: string,
  params: readonly unknown[] = [],
): Promise<T | null> {
  const result = await query<T>(text, params);
  return result.rows[0] ?? null;
}

export async function transaction<T>(fn: (client: pg.PoolClient) => Promise<T>): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const result = await fn(client);
    await client.query("COMMIT");
    return result;
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}