import postgres from "postgres";

export type Sql = ReturnType<typeof postgres>;
const g = globalThis as unknown as { __wpSql?: Sql; __wpSqlPublic?: 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 client (admin, worker, scripts). Never import from public route modules. */
export const sql: Sql = g.__wpSql ?? postgres(need("DATABASE_URL"), opts);
/** Read-only client for everything public: `wp_reader` role (SELECT on public views only, INSERT on events/search_log). */
export const sqlPublic: Sql = g.__wpSqlPublic ?? postgres(process.env.DATABASE_URL_PUBLIC ?? need("DATABASE_URL"), { ...opts, max: 12 });
if (process.env.NODE_ENV !== "production") { g.__wpSql = sql; g.__wpSqlPublic = sqlPublic; }

export async function closeDb() { await Promise.all([sql.end(), sqlPublic.end()]); }