import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

type Sql = ReturnType<typeof postgres>;
const g = globalThis as unknown as { __toolsSql?: Sql; __toolsSqlPublic?: Sql; __toolsDrizzleSql?: Sql };
const opts = { max: 6, idle_timeout: 30, prepare: false, transform: { undefined: null }, onnotice: () => {} } as const;
function need(name: string) { const v = process.env[name]; if (!v) throw new Error(`${name} is not set`); return v; }

/** Full-privilege raw client (admin API, worker, scripts). Never import from public route modules. */
export const sql: Sql = g.__toolsSql ?? postgres(need("DATABASE_URL"), opts);
/** Read-only raw client for everything public: `tools_reader` role in production (SELECT on public views only). */
export const sqlPublic: Sql = g.__toolsSqlPublic ?? postgres(process.env.DATABASE_URL_PUBLIC ?? need("DATABASE_URL"), { ...opts, max: 12 });
// Drizzle's postgres-js driver rewires the client's date/json serializers for its own mapping, which breaks raw tagged-template
// queries that pass Date objects — so Drizzle gets its own dedicated connection pool.
const drizzleSql: Sql = g.__toolsDrizzleSql ?? postgres(need("DATABASE_URL"), { ...opts, max: 4 });
if (process.env.NODE_ENV !== "production") { g.__toolsSql = sql; g.__toolsSqlPublic = sqlPublic; g.__toolsDrizzleSql = drizzleSql; }

export const db = drizzle(drizzleSql, { schema, casing: "snake_case" });
export { schema };
export type DB = typeof db;
/** Close every pool (scripts/tests). */
export async function closeDb() { await Promise.all([sql.end(), sqlPublic.end(), drizzleSql.end()]); }