// Database client — branches on environment.
//
// Production path: `@raredays/postgres-neon` (HTTP, edge-runtime
// compatible). Set `DATABASE_URL` and the lazy Proxy below resolves to a
// Neon client on first property access. To swap providers (Supabase /
// RDS / self-hosted via `@raredays/postgres-js`, or any other
// `@raredays/postgres-*` driver):
//
//   1. Edit `site.spec.ts` and set `deploy.database.provider` accordingly.
//   2. `pnpm remove @raredays/postgres-neon && pnpm add @raredays/postgres-js`
//   3. Replace the `@raredays/postgres-neon` static import below.
//
// Dev path (NODE_ENV !== "production" AND no DATABASE_URL): a fresh
// in-process `@raredays/postgres-pglite` is booted eagerly so
// Drizzle-backed providers (state-machine, engagement, audit, future
// cms-drizzle) can register their tables at composition root without
// provisioning Postgres. PGlite resets every time the dev server
// restarts; set `DATABASE_URL` to opt into a persistent database in dev.
//
// Why eager in dev and lazy in prod? Drizzle providers materialize
// tables at `registerMachine` / `registerTable` time — i.e. during
// composition root eval. The dev runtime needs the db handle ready
// before `createRaredaysApp` is called. The prod runtime can stay
// lazy: the Neon client is cheap to construct, and lazy construction
// means a build that never queries (e.g. SSG-only marketing routes)
// doesn't require DATABASE_URL.
import { assertIdColumns } from "@raredays/core/schema";
import { createDb, type RaredaysDatabase as NeonDb } from "@raredays/postgres-neon";
import { createPglite, type RaredaysDatabase as PgliteDb } from "@raredays/postgres-pglite";
import siteSpec from "../site.spec.ts";
import * as appSchema from "./schema.ts";

export type Db = NeonDb<typeof appSchema> | PgliteDb<typeof appSchema>;

// Fail at startup, naming the columns, when a table's user or organization id
// column disagrees with the idStrategy in site.spec.ts (framework ADR-0028),
// instead of at the first insert with Postgres 22P02.
assertIdColumns(siteSpec.idStrategy, appSchema);

const isProduction = process.env.NODE_ENV === "production";
const hasDatabaseUrl = Boolean(process.env.DATABASE_URL);

// Dev fallback: bootstrap PGlite eagerly so providers can register
// tables at composition-root eval. In prod, or when DATABASE_URL is
// set, leave this null and fall through to the lazy Proxy below.
const eagerPglite: Db | null = !isProduction && !hasDatabaseUrl ? await bootstrapPglite() : null;

let cached: Db | undefined = eagerPglite ?? undefined;

function buildNeon(): Db {
  const connectionString = process.env.DATABASE_URL;
  if (!connectionString) {
    throw new Error(
      "DATABASE_URL is not set. Provision a Postgres database (Neon, Supabase, etc.) and add it to the environment before running queries.",
    );
  }
  return createDb(connectionString, { schema: appSchema }) as Db;
}

async function bootstrapPglite(): Promise<Db> {
  const handle = await createPglite({ schema: appSchema });
  // Minimal CREATE TABLE statements mirroring the substrate tables in
  // `db/schema.ts` and `@raredays/audit`, with organization_id typed from
  // idStrategy in site.spec.ts like theirs (framework ADR-0028). Kept tight on
  // purpose: Drizzle-provider tests exercise the real conformance suite; this
  // is purely so a fresh `pnpm dev` doesn't crash on first query against
  // substrate tables. If you need the full migration set in dev, point
  // DATABASE_URL at a local Postgres and run `pnpm db:migrate`.
  const orgId = siteSpec.idStrategy === "text" ? "text" : "uuid";
  const statements: readonly string[] = [
    // Audit substrate — `audit_entries` is the ONE audit table (S2a / D3). The
    // old `audit_events` table was retired: `@raredays/audit-drizzle` persists
    // `audit_entries`, which matches the core `AuditProvider` port. If you hold
    // legacy `audit_events` rows, backfill them into `audit_entries` before
    // dropping (see the @raredays/postgres CHANGELOG / migration M8-backfill).
    `DO $$ BEGIN
       CREATE TYPE audit_actor_type AS ENUM ('user', 'system', 'api-key');
     EXCEPTION WHEN duplicate_object THEN null; END $$`,
    `CREATE TABLE IF NOT EXISTS audit_entries (
       id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
       organization_id ${orgId} NOT NULL,
       actor_id text NOT NULL,
       actor_type audit_actor_type NOT NULL,
       action varchar(255) NOT NULL,
       target_type varchar(255) NOT NULL,
       target_id text NOT NULL,
       metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
       ip_address varchar(45),
       user_agent text,
       trace_id text,
       acting_client_id text,
       timestamp timestamptz NOT NULL DEFAULT now(),
       created_at timestamptz NOT NULL DEFAULT now()
     )`,
    `CREATE TABLE IF NOT EXISTS webhook_deliveries (
       id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
       organization_id ${orgId} NOT NULL,
       created_at timestamptz NOT NULL DEFAULT now(),
       updated_at timestamptz NOT NULL DEFAULT now(),
       provider varchar(32) NOT NULL,
       external_id varchar(128) NOT NULL,
       event_type varchar(64) NOT NULL,
       status varchar(16) NOT NULL,
       payload jsonb NOT NULL,
       error text,
       CONSTRAINT webhook_deliveries_provider_external_id_idx UNIQUE (provider, external_id)
     )`,
    // Jobs substrate — append-only log + tenant-scoped dedup markers (S2a / D2).
    // `job_executions` carries NO unique index (every status transition is an
    // insert); dedup-of-record lives in `job_dedup_markers`.
    `DO $$ BEGIN
       CREATE TYPE job_execution_status AS ENUM
         ('enqueued', 'running', 'succeeded', 'failed', 'skipped');
     EXCEPTION WHEN duplicate_object THEN null; END $$`,
    `CREATE TABLE IF NOT EXISTS job_executions (
       id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
       organization_id ${orgId} NOT NULL,
       created_at timestamptz NOT NULL DEFAULT now(),
       updated_at timestamptz NOT NULL DEFAULT now(),
       job_name varchar(64) NOT NULL,
       idempotency_key varchar(128),
       status job_execution_status NOT NULL,
       attempts integer NOT NULL DEFAULT 0,
       payload jsonb NOT NULL,
       metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
       reason text,
       error text
     )`,
    `CREATE TABLE IF NOT EXISTS job_dedup_markers (
       id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
       organization_id ${orgId} NOT NULL,
       created_at timestamptz NOT NULL DEFAULT now(),
       updated_at timestamptz NOT NULL DEFAULT now(),
       job_name varchar(64) NOT NULL,
       idempotency_key varchar(128) NOT NULL,
       CONSTRAINT job_dedup_markers_key_idx UNIQUE (organization_id, job_name, idempotency_key)
     )`,
  ];
  for (const stmt of statements) {
    await handle.client.query(stmt);
  }
  return handle.db as Db;
}

export const db: Db = new Proxy({} as Db, {
  get(_target, prop) {
    if (!cached) cached = buildNeon();
    return Reflect.get(cached, prop);
  },
}) as Db;