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

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

import { sql as _sql } from "drizzle-orm";
/** Postgres text[] literal (Drizzle spreads JS arrays into parameter lists, which breaks `= any(...)`). */
export function textArray(values: readonly string[]) {
  const lit = "{" + values.map((v) => '"' + String(v).replace(/\\/g, "\\\\").replace(/"/g, '\\"') + '"').join(",") + "}";
  return _sql.raw("'" + lit.replace(/'/g, "''") + "'::text[]");
}

export type Db = NodePgDatabase<typeof schema>;

let pool: pg.Pool | null = null;
let dbInstance: Db | null = null;

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

export function getDb(): Db {
  if (!dbInstance) dbInstance = drizzle(getPool(), { schema });
  return dbInstance;
}

export const db: Db = new Proxy({} as Db, {
  get(_t, prop) {
    return (getDb() as unknown as Record<string | symbol, unknown>)[prop];
  },
});

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