/** Single shared pg Pool + small query helpers. */
import pg from 'pg';
import { env } from '../config/env.js';
import { logger } from '../utils/logger.js';
import { describeError } from '../utils/describeError.js';

// Return BIGINT (message.seq) as a JS number rather than a string. Safe: seq
// would need ~9 quadrillion messages to exceed Number.MAX_SAFE_INTEGER.
pg.types.setTypeParser(20, (v) => (v === null ? null : Number(v)));

/**
 * Managed providers put `sslmode=require` in the connection string, but
 * node-postgres also takes an explicit `ssl` object. Supplying both makes pg
 * emit a deprecation warning, because `sslmode=require` currently implies
 * full verification and will switch to weaker libpq semantics in pg v9.
 *
 * So: read the URL's intent, strip the parameter, and make `sslConfig()` the
 * single source of truth. Behaviour then stays identical across that upgrade.
 */
function splitSslFromUrl(raw: string): { url: string; urlWantsSsl: boolean } {
  const wants = (mode: string | null) => Boolean(mode && /^(require|verify-ca|verify-full)$/i.test(mode));
  try {
    const parsed = new URL(raw);
    const urlWantsSsl = wants(parsed.searchParams.get('sslmode'));
    parsed.searchParams.delete('sslmode');
    return { url: parsed.toString(), urlWantsSsl };
  } catch {
    // Not a URL (a libpq "key=value" DSN) - leave it exactly as given.
    return { url: raw, urlWantsSsl: /(^|[?&\s])sslmode=(require|verify-ca|verify-full)/i.test(raw) };
  }
}

const { url: CONNECTION_STRING, urlWantsSsl } = splitSslFromUrl(env.DATABASE_URL);

/**
 * TLS for managed Postgres. Every hosted provider requires it, and connecting
 * without it fails with a confusing "Connection terminated unexpectedly"
 * rather than a TLS error - so honour the URL as well as the PGSSL flag.
 */
function sslConfig(): pg.PoolConfig['ssl'] {
  if (!env.PGSSL && !urlWantsSsl) return undefined;
  return { rejectUnauthorized: env.PGSSL_REJECT_UNAUTHORIZED };
}

export const pool = new pg.Pool({
  connectionString: CONNECTION_STRING,
  ssl: sslConfig(),
  // Serverless Postgres (Neon) suspends idle compute, so the first query after
  // a quiet spell pays a cold-start. Keep the pool modest and the timeout
  // generous rather than the other way round.
  max: env.PG_POOL_MAX,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 15_000,
});

pool.on('error', (err) => logger.error('pg pool error', { err: describeError(err) }));

export async function query<T extends pg.QueryResultRow = any>(
  text: string,
  params: unknown[] = [],
): Promise<pg.QueryResult<T>> {
  const started = Date.now();
  const res = await pool.query<T>(text, params as any[]);
  const ms = Date.now() - started;
  if (ms > 200) logger.warn('slow query', { ms, text: text.slice(0, 120) });
  return res;
}

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

/** All rows. */
export async function many<T extends pg.QueryResultRow = any>(
  text: string,
  params: unknown[] = [],
): Promise<T[]> {
  const { rows } = await query<T>(text, params);
  return rows;
}

/** Run `fn` inside a transaction, rolling back on any throw. */
export async function tx<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 (err) {
    await client.query('ROLLBACK').catch(() => undefined);
    throw err;
  } finally {
    client.release();
  }
}