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

const { Pool, types } = pg;
// timestamptz stays a Date (pg default → ISO in JSON); numeric/int8 as numbers where safe.
types.setTypeParser(20, (v) => Number(v));
types.setTypeParser(1700, (v) => Number(v));

export const pool = new Pool({
  connectionString: config.databaseUrl,
  max: config.role === "api" ? 8 : 16,
  idleTimeoutMillis: 30_000,
  statement_timeout: 60_000,
  options: "-c lock_timeout=15000", // never let a lock wait starve the pool (health checks must keep answering)
  application_name: `market-atlas-${config.role}`,
});

pool.on("error", (err) => logger.error({ err }, "pg pool error"));

export type Queryable = pg.Pool | pg.PoolClient;

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

export async function withTransaction<T>(fn: (client: pg.PoolClient) => Promise<T>): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("begin");
    const out = await fn(client);
    await client.query("commit");
    return out;
  } catch (err) {
    await client.query("rollback").catch(() => {});
    throw err;
  } finally {
    client.release();
  }
}

export const tsToMs = (v: unknown): number | null => {
  if (v == null) return null;
  if (v instanceof Date) return v.getTime();
  const ms = Date.parse(String(v));
  return Number.isFinite(ms) ? ms : null;
};

export const msToTs = (ms: number | null | undefined): string | null => (ms == null ? null : new Date(ms).toISOString());