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

export type Database = ReturnType<typeof createDatabase>["db"];

let sharedPool: pg.Pool | undefined;

/**
 * Skapar en databasklient. API och worker delar mönster men äger varsin pool.
 * DATABASE_URL pekar på appens separata databas med egen minimalprivilegie-användare
 * (minsta möjliga privilegier, spec §52).
 */
export function createDatabase(connectionString?: string) {
  const url =
    connectionString ??
    process.env.DATABASE_URL ??
    "postgres://app_user:app_dev_password@localhost:5432/app";

  const pool = new pg.Pool({
    connectionString: url,
    max: Number(process.env.DATABASE_POOL_MAX ?? 10),
    idleTimeoutMillis: 30_000,
    connectionTimeoutMillis: 10_000,
  });

  const db = drizzle(pool, { schema, casing: "snake_case" });
  return { db, pool };
}

/** Singleton för processer som bara behöver en anslutning. */
export function getDatabase() {
  if (!sharedPool) {
    const { db, pool } = createDatabase();
    sharedPool = pool;
    sharedDb = db;
  }
  return sharedDb!;
}
let sharedDb: Database | undefined;

export async function closeDatabase(): Promise<void> {
  if (sharedPool) {
    await sharedPool.end();
    sharedPool = undefined;
    sharedDb = undefined;
  }
}

export { schema };
