import postgres from 'postgres';
import { drizzle, type PostgresJsDatabase } from 'drizzle-orm/postgres-js';
import * as schema from './schema/index.js';

export type Database = PostgresJsDatabase<typeof schema>;

let _sql: ReturnType<typeof postgres> | null = null;
let _db: Database | null = null;

export interface DbOptions {
  url?: string;
  max?: number;
}

/** Lazily created singleton; safe from Next.js server components, Fastify and workers alike. */
export function getSql(opts: DbOptions = {}) {
  if (!_sql) {
    const url = opts.url ?? process.env.DATABASE_URL ?? 'postgres://localhost:5432/cancerindex';
    _sql = postgres(url, {
      max: opts.max ?? Number(process.env.DB_POOL_MAX ?? 10),
      idle_timeout: 30,
      connect_timeout: 10,
      prepare: true,
      transform: { undefined: null },
      onnotice: () => {},
    });
  }
  return _sql;
}

export function getDb(opts: DbOptions = {}): Database {
  if (!_db) _db = drizzle(getSql(opts), { schema, casing: 'snake_case' });
  return _db;
}

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