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

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

export type Db = PostgresJsDatabase<typeof schema>;

let _client: ReturnType<typeof postgres> | null = null;
let _db: Db | null = null;

export function getSql(): ReturnType<typeof postgres> {
  if (!_client) {
    const url = process.env.DATABASE_URL ?? "postgres://dci:dci@127.0.0.1:5432/dci";
    _client = postgres(url, { max: Number(process.env.PG_POOL_MAX ?? 16), idle_timeout: 30, connect_timeout: 15, prepare: true, onnotice: () => {} });
  }
  return _client;
}

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

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

/** Postgres `text[]` literal helper — drizzle `sql\`${arr}\`` would spread arrays into tuples. */
export function textArray(values: string[]): string {
  return `{${values.map((v) => `"${v.replace(/(["\\])/g, "\\$1")}"`).join(",")}}`;
}