import { drizzle, type NodePgDatabase } from "drizzle-orm/node-postgres";
import pg from "pg";
import * as schema from "./schema";

export * from "./schema";
export * as schema from "./schema";
export { sql, eq, and, or, desc, asc, gte, lte, lt, gt, ne, inArray, isNull, isNotNull, count, sum, avg, max, like, ilike, between, notInArray } from "drizzle-orm";

export type Db = NodePgDatabase<typeof schema>;
export type Tx = Parameters<Parameters<Db["transaction"]>[0]>[0];

let _pool: pg.Pool | null = null;
let _db: Db | null = null;

// BIGINT → number (we never exceed 2^53 credits), NUMERIC → string (handled per use).
pg.types.setTypeParser(20, (v) => Number(v));

export function getPool(): pg.Pool {
  if (!_pool) {
    const connectionString = process.env.DATABASE_URL ?? "postgres://localhost:5432/spinza";
    _pool = new pg.Pool({
      connectionString,
      max: Number(process.env.DB_POOL_MAX ?? 10),
      idleTimeoutMillis: 30_000,
      connectionTimeoutMillis: 10_000,
    });
    _pool.on("error", (err) => console.error("[db] pool error", err.message));
  }
  return _pool;
}

export function getDb(): Db {
  if (!_db) _db = drizzle(getPool(), { schema, casing: "snake_case" });
  return _db;
}

/** Lazily-initialised shared database handle. */
export const db: Db = new Proxy({} as Db, {
  get(_t, prop) {
    return (getDb() as unknown as Record<PropertyKey, unknown>)[prop];
  },
});

export async function closeDb(): Promise<void> {
  if (_pool) {
    await _pool.end();
    _pool = null;
    _db = null;
  }
}

export async function pingDb(): Promise<number> {
  const t = Date.now();
  await getPool().query("select 1");
  return Date.now() - t;
}