import { drizzle } from "drizzle-orm/postgres-js"
import postgres from "postgres"

import { env } from "@/lib/env"

/**
 * The one connection to the database.
 *
 * Held on globalThis because the development server reloads this module on
 * every edit, and a fresh pool each time exhausts Postgres' connection limit
 * within a morning's work. The production build imports it once, so the cache
 * is dead weight there and harmless.
 *
 * `max: 1` in development for the same reason: one editor, one person clicking,
 * no reason to hold ten sockets open. Production gets a real pool.
 */

declare global {
  var __advmfrSql: ReturnType<typeof postgres> | undefined
}

const production = process.env.NODE_ENV === "production"

const sql =
  globalThis.__advmfrSql ??
  postgres(env.DATABASE_URL, {
    max: production ? 10 : 1,
    // Dates are days on a wall calendar in Bangkok, carried as YYYY-MM-DD
    // strings from end to end. Left alone, the driver would hand back a
    // JavaScript Date built in the machine's timezone, which is the exact
    // mistake that dated every row a day early in the system this replaces.
    types: {
      date: {
        to: 1082,
        from: [1082],
        serialize: (value: string) => value,
        parse: (value: string) => value,
      },
    },
  })

if (!production) globalThis.__advmfrSql = sql

/**
 * `{ client: sql }` and not `drizzle(sql, ...)`. Drizzle 1.0 dropped the
 * positional form, and passing it anyway does not fail loudly: it treats the
 * arguments as a config it does not recognise and opens its own connection on
 * the defaults, which here meant a database named after the Unix user. The
 * error arrives as "database humperdoo does not exist" from a query that names
 * a table in a different database entirely.
 *
 * `schema` is gone from the config in 1.0 as well. It only ever fed the
 * relational query API, `db.query.people.findMany()`, which now wants relations
 * declared with `defineRelations`. Nothing selects that way yet, so there is
 * nothing to register.
 */
export const db = drizzle({ client: sql })
export { sql }