// src/lib/db/client.ts
import type { Logger } from "drizzle-orm";
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import { env } from "@/env";
import * as adminSchema from "./admin-schema";
import * as authSchema from "./auth-schema";
import * as domainSchema from "./domain-schema";
import * as schema from "./schema";

// D3 — auth-adjacent tables carry secrets in their params (session tokens,
// verification tokens/OTPs, OAuth access/refresh tokens, 2FA TOTP secret +
// backup codes): never let those reach stdout, which d3k persists to disk
// under ~/.d3k/. The query text itself stays (useful for debugging shape/
// perf), only params are redacted.
const SECRET_TABLE_RE = /\b(session|verification|account|two_factor)\b/i;

/** True if `query` references a table whose params may carry auth secrets. */
export function shouldMaskParams(query: string): boolean {
  return SECRET_TABLE_RE.test(query);
}

// Tagged SQL logger so each query lands on the Next dev-server stdout (which d3k
// aggregates into its timeline). Dev only — tests/scripts/prod stay quiet.
const devLogger: Logger = {
  logQuery(query, params) {
    const shown = shouldMaskParams(query)
      ? "[params masqués]"
      : params.length
        ? params
        : "";
    console.log("[drizzle]", query, shown);
  },
};

// postgres.js is lazy: importing this module does NOT open a connection;
// the first query does. Safe to import in code paths that never query.
// On serverless (Vercel) keep one connection per instance and disable prepared
// statements (transaction-mode poolers don't support them); full pool locally.
//
// Pool hygiene (all envs): idle_timeout closes idle connections after 20s and
// max_lifetime caps any connection at 30 min — without it postgres.js keeps
// connections open forever, which leaked under dev HMR (ELEVA-42).
//
// Dev singleton: Turbopack/HMR re-evaluates this module on every recompile;
// each evaluation would otherwise spawn a NEW pool (max≈10) while the old
// pools' connections stay open → Postgres hits max_connections within ~1h.
// Caching the client on globalThis makes HMR re-evaluations reuse the same
// pool. The drizzle `db` wrapper below is re-created per evaluation, but it's
// a lightweight wrapper around the cached client — only the pool owner matters.
// SEC-P1 — TLS: deliberately no `ssl` option here. postgres.js parses
// `?sslmode=` off the connection string itself (see its `parseOptions`)
// before applying the `ssl: false` default, so a prod DATABASE_URL with
// `?sslmode=verify-full` gets TLS with no code change, while the local
// loopback URL (no sslmode param) stays plaintext exactly as today. See
// docs/project/ops/db-least-privilege.md for the prod connection string.
const createClient = () =>
  postgres(env.DATABASE_URL, {
    ...(process.env.VERCEL ? { max: 1, prepare: false } : {}),
    idle_timeout: 20,
    max_lifetime: 60 * 30,
  });

const globalForPg = globalThis as unknown as {
  __pgClient?: ReturnType<typeof createClient>;
};

const devClient = () => {
  globalForPg.__pgClient ??= createClient();
  return globalForPg.__pgClient;
};

const client =
  process.env.NODE_ENV === "production" ? createClient() : devClient();

export const db = drizzle(client, {
  schema: { ...schema, ...authSchema, ...domainSchema, ...adminSchema },
  logger: process.env.NODE_ENV === "development" ? devLogger : false,
});
export { client };