import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import { getEnv } from "@/core/config/env";
import * as schema from "@/core/db/schema";

type DbClient = ReturnType<typeof drizzle<typeof schema>>;

const globalForDb = globalThis as unknown as {
  archiDb?: DbClient;
  archiSql?: ReturnType<typeof postgres>;
};

function createDb(): DbClient {
  const env = getEnv();
  const sql =
    globalForDb.archiSql ??
    postgres(env.DATABASE_URL, {
      max: env.NODE_ENV === "production" ? 20 : 5,
      prepare: false,
    });

  if (env.NODE_ENV !== "production") {
    globalForDb.archiSql = sql;
  }

  return drizzle(sql, { schema });
}

/**
 * Shared PostgreSQL client with Drizzle ORM.
 * All tenant-owned queries MUST include trusted tenant_id scoping.
 */
export function getDb(): DbClient {
  if (!globalForDb.archiDb) {
    globalForDb.archiDb = createDb();
  }
  return globalForDb.archiDb;
}

export type Database = DbClient;
