import { drizzle, type NodePgDatabase } from "drizzle-orm/node-postgres";
import pg from "pg";
import { AsyncLocalStorage } from "node:async_hooks";
import * as schema from "@shared/schema";

const { Pool } = pg;

if (!process.env.DATABASE_URL) {
  throw new Error(
    "DATABASE_URL must be set. Did you forget to provision a database?",
  );
}

// T003 binding guardrail (architect Rule 9 PASS 2026-06-03 §0.2, Q2a):
// AEGIS_APP_DB_PASSWORD is required at module load. Without it the server
// cannot open the aegis_app restricted-role pool and RLS would not enforce.
// Fail-fast mirrors the DATABASE_URL check above.
if (!process.env.AEGIS_APP_DB_PASSWORD) {
  throw new Error(
    "AEGIS_APP_DB_PASSWORD must be set. Provision this Replit secret before " +
      "starting the server (password for the aegis_app restricted DB role, " +
      "required for Row-Level Security enforcement).",
  );
}

// Build the aegis_app connection string: same host / port / database as
// DATABASE_URL but with aegis_app credentials substituted. URL form required
// (key-value DSN is not supported). Special characters in the password are
// automatically percent-encoded by the URL API.
export function buildAppConnectionString(): string {
  try {
    const url = new URL(process.env.DATABASE_URL!);
    url.username = "aegis_app";
    url.password = process.env.AEGIS_APP_DB_PASSWORD!;
    return url.toString();
  } catch {
    throw new Error(
      "DATABASE_URL must be a valid PostgreSQL URL (e.g. postgres://...) " +
        "to support dual-pool aegis_app construction. Key-value DSN format " +
        "is not supported.",
    );
  }
}

// ── Dual-pool topology (T003 §0.2 amendment, architect Q1 binding) ───────────
//
// ddlPool — postgres superuser (DATABASE_URL). Used ONLY by:
//   1. initRlsPolicies() DDL bootstrap (creates its own local pool from DATABASE_URL;
//      the exported ddlPool here is for auditDb only — see below).
//   2. auditDb: independent audit write path to audit_chain_entries.
// Q1 binding guardrail: request-scoped queries must NEVER route through ddlPool.
// Exported for graceful shutdown (pool.end()) in server/index.ts.
export const ddlPool = new Pool({ connectionString: process.env.DATABASE_URL });

// appPool — aegis_app restricted role (no superuser, no BYPASSRLS). All
// request-scoped queries route through this pool; PostgreSQL RLS enforces on
// every query once T003 initRlsPolicies() has deployed the policies.
// Q1 binding guardrail: this is the ONLY pool for request handling.
export const appPool = new Pool({
  connectionString: buildAppConnectionString(),
});

// Backward-compat alias: callers that imported `pool` get the app pool.
// platform-health.ts SELECT-1 + regulator-pilot-tier-a.ts dedicated client
// checkout + index.ts graceful shutdown all use this name.
export const pool = appPool;

export type DrizzleDb = NodePgDatabase<typeof schema>;

// Pooled runner — appPool (aegis_app). RLS enforces on every query.
// Fallback for call sites not inside a request-scoped tenant transaction
// (background workers, boot-time system queries against non-(T) tables).
const pooledDb: DrizzleDb = drizzle(appPool, { schema });

// FU-053 Layer 1 (T001) — request-scoped tenant context.
//
// Option (c) strict variant (architect-approved 2026-05-29): the tenant GUC
// (`app.current_tenant_id`) is set LOCAL to a request transaction pinned to a
// single checked-out client, and a Drizzle runner bound to that client is
// stashed in this AsyncLocalStorage for the request's duration. The exported
// `db` proxy resolves the runner from ALS first, falling back to `pooledDb`
// only when no request context is active.
//
// DESIGN CONSTRAINT (Correction 2, "exemption is per-call-site, not
// per-module"): the pooled fallback is legitimate ONLY for a call site whose
// specific queries touch either (a) system catalogs or (b) application tables
// with no tenant dimension. A file's role never justifies the fallback; only
// the data the specific query touches does. T002's audit applies this test
// per-call-site across the full storage surface.
export interface TenantRunnerStore {
  runner: DrizzleDb;
  tenantId: string;
}

export const tenantContext = new AsyncLocalStorage<TenantRunnerStore>();

// FU-053 Layer 1 (T001) — streaming-route carve-out guard (architect-approved
// Shape 3, 2026-05-29). A small set of long-lived streaming routes (SSE chat,
// file-pipe downloads, the pilot-pack ZIP) cannot hold the per-request tenant
// transaction open for their whole lifetime without pinning a pool client and
// starving the pool. `tenantMiddleware` therefore runs those routes OUTSIDE the
// request transaction, inside this context. While it is active, every DB access
// MUST go through a bounded `withTenantDbPhase(...)` (which opens its own pinned
// tenant tx and activates `tenantContext`); raw `db` use is FORBIDDEN so a
// carved-out handler cannot silently query the pooled connection with no tenant
// GUC set (which, once T003 RLS lands, would fail-open before policies exist /
// return empty after — either way an unverified path). The fail mode is a loud
// throw, not a silent fallback.
export const streamingBypassContext = new AsyncLocalStorage<{ bypassed: true }>();

/**
 * Resolve the active runner: request-scoped tx runner if present, else pool.
 *
 * Throws if accessed from within a streaming-bypass request that is NOT inside a
 * `withTenantDbPhase` bounded phase — raw `db` is forbidden in carved-out
 * handlers (see `streamingBypassContext`).
 */
export function resolveRunner(): DrizzleDb {
  const tenantRunner = tenantContext.getStore()?.runner;
  if (tenantRunner) return tenantRunner;
  if (streamingBypassContext.getStore()?.bypassed) {
    throw new Error(
      "FU-053 T001: raw `db` access inside a streaming-bypass handler is " +
        "forbidden. Wrap tenant-scoped DB work in `withTenantDbPhase(req, ...)` " +
        "so the tenant GUC is set on the querying connection (RLS-ready).",
    );
  }
  return pooledDb;
}

/** Current request's tenantId if a tenant context is active, else undefined. */
export function getActiveTenantId(): string | undefined {
  return tenantContext.getStore()?.tenantId;
}

/** Build a Drizzle runner bound to a specific checked-out client. */
export function makeClientRunner(client: pg.PoolClient): DrizzleDb {
  return drizzle(client, { schema });
}

// Transparent proxy. Every `db.select()/insert()/update()/delete()/transaction()`
// call site automatically routes through the request-scoped transaction client
// when one is active, and through the pooled runner otherwise — with zero
// changes to the ~62 existing `db` call sites (this is why the architect
// rejected option (a)'s 64-method IStorage churn).
export const db: DrizzleDb = new Proxy({} as DrizzleDb, {
  get(_target, prop, receiver) {
    const runner = resolveRunner();
    const value = Reflect.get(runner as object, prop, receiver);
    return typeof value === "function" ? value.bind(runner) : value;
  },
});

// Independent audit write path — postgres superuser pool (ddlPool).
//
// T003 Option B (architect Q3/Q4 ruling — REQUIRED, not optional):
// aegis_app has SELECT-only on audit_chain_entries (REVOKE INSERT/UPDATE/DELETE
// applied in initRlsPolicies). Chain writes are exclusively through this path,
// converting tamper-evidence from a documented invariant to a structural
// PostgreSQL access-control boundary. Any path — including SQL injection on
// the application pool — cannot INSERT/UPDATE/DELETE the audit chain.
//
// Audit-chain writes COMMIT independently of the request transaction (no shared
// client), so they survive request rollback. audit_chain_entries is system-level
// cross-tenant (no RLS policy); ddlPool superuser disposition is coupled to the
// Option B carve-out — if auditDb ever touches a (T) table, new Rule 9 review
// is required.
//
// Rule 9 review authorises TWO (T)-table writes through this path:
//   1. (VF-S5, 2026-06-10) api_usage_metrics observability inserts
//      (server/lib/api-analytics.ts).
//   2. (FU-107, 2026-06-21) notification_logs side-writes
//      (server/lib/notification-service.ts writeNotificationLog).
// Same rationale for both: the write must survive request rollback AND must
// never poison the caller's request transaction. On the request `db` proxy a
// fire-and-forget insert hit RLS WITH CHECK with a NULL/foreign tenant_id
// (42501), aborting the per-request tx and silently rolling back the request's
// durable writes (FU-107: a DSAR create vanished silently). Routing through
// auditDb (BYPASSRLS skips WITH CHECK) decouples it; the caller stamps tenant_id
// (fail-closed: explicit ?? request GUC ?? null) so RLS-scoped reads stay
// tenant-correct. A genuinely-absent context writes a null-tenant row
// (attribution gap) rather than poisoning — and reads still go through
// aegis_app/RLS, so null/foreign rows are not tenant-visible. Any THIRD
// (T)-table write through this path needs a new Rule 9 review.
export const auditDb: DrizzleDb = drizzle(ddlPool, { schema });

// ── withBypassRls() — tightly-scoped RLS bypass ──────────────────────────────
//
// Checks out a client from appPool, begins a transaction, elevates to
// aegis_rls_bypass role (BYPASSRLS) via SET LOCAL ROLE (scoped to this
// transaction only — NOT ambient on the connection), executes the callback,
// then commits. Rolls back on error and re-throws.
//
// Authorised call sites — DR-T003 bypass register + D-B=B-3 api_keys:
//   DR-T003-06:   pilot-readiness.ts runTenantIsolationTest (cross-tenant count)
//   DR-T003-07:   pilot-readiness.ts runBackupRestoreTest (user_tenant_roles count)
//   DR-T003-08:   pilot-readiness.ts runBackupRestoreTest (user_tenant_roles copy)
//   D-B=B-3:      apiKeyAuth pre-tenant metadata path on api_keys —
//                 storage.getApiKeyByHash (key-hash lookup) AND
//                 storage.updateApiKeyLastUsed (last-used-at write by id). Both
//                 run as the first /api middleware, BEFORE tenantMiddleware sets
//                 the GUC, so the plain RLS db sees a null tenant and (lookup)
//                 returns no row / (update) matches no row. Scoped to exact
//                 hash / primary-key access only.
//   BACKUP-SYS:   backup-service.ts all backup_records DML + backup-scheduler.ts
//                 shouldFireBackup + findVerifyTarget SELECTs. backup_records is
//                 SYSTEM-EXEMPT (F-RP-07, 2026-06-20): removed from TENANT_TABLES,
//                 RLS DISABLED; tenant_id NULL by construction. The bypass is
//                 retained as the deliberate system-scope path (no GUC in
//                 background) — harmless with RLS off, correct if re-enabled.
//   SBOM-SYS:     pilot-readiness-extras-2.ts generateSbom sbomSnapshots INSERT
//                 (system-level SBOM; no per-tenant owner)
//   PILOT-COUNT:  pilot-pack-scheduler.ts tableExists + countRows loop
//                 (cross-tenant aggregate; pilot-pack ZIP covers all tenants)
//   DSAR-LEGACY:  dsar-purge-worker.ts processClaimedJob legacy bootstrap SELECT
//                 on dsar_requests (null-tenantId jobs pre-T003, time-bounded)
//   LOGIN-UTR:    routes.ts login handler primary-tenant lookup on
//                 user_tenant_roles (pre-session; GUC="default" at login time;
//                 chicken-and-egg — tenant cannot be known before the lookup).
//   SEED-UTR:     pilot-readiness-extras.ts seedUserTenantRoles (boot seed;
//                 no GUC available outside request context; same class as
//                 BACKUP-SYS/SBOM-SYS; idempotent system-level seed).
//   EXAMINER-KYC-UTR: routes.ts examiner KYC-evidence handlers (Feature 4)
//                 issuer-tenant resolution on users + user_tenant_roles. The
//                 examiner Bearer carries no session, so the request GUC is
//                 "default"; the effective tenant must be derived from the
//                 issuing admin's primary tenant BEFORE any tenant-scoped read
//                 — same pre-session chicken-and-egg as LOGIN-UTR. The KYC DATA
//                 reads themselves run under runWithTenantContext (RLS-enforced),
//                 NOT under this bypass; the bypass is for tenant resolution only.
//   APIKEY-ADMIN: routes.ts api_keys admin DML — POST /api/api-keys (create) and
//                 DELETE /api/api-keys/:id (deactivate). api_keys is an RLS (T)
//                 table but is admin-managed CROSS-TENANT (the GET takes an
//                 optional tenantId; admins manage keys for any tenant), and the
//                 D-B=B-3 directive (rls-init.ts) routes api_keys admin DML through
//                 this bypass. Added for the revocation-race class fix (Shelf-Sweep
//                 AS/PLATFORM/2026/003): withBypassRls COMMITs before returning, so
//                 a created key is durable before plainKey is handed back and a
//                 revoked key is durably inactive before the 200 — closing the
//                 res.finish-commit race on this authentication surface.
//   PORTAL-PUBLIC: pilot-readiness-extras-4.ts publicTrustPortalSnapshot
//                 (AS/CYBER/CLIENTDATA-ISOLATION Batch 1b + Batch 2-3 Pack 1).
//                 The public trust portal is a NO-AUTH surface (empty tenant
//                 GUC). erasure_drills and consent_ledger became RLS-enrolled in
//                 Batch 1b, and ai_appeals became RLS-enrolled in Batch 2-3
//                 Pack 1, so a request-tenant scope here would silently zero
//                 these platform-wide figures. Read-only: latest erasure_drills
//                 row (only triggeredAt/passed surfaced in the response — no
//                 customer_ref/receipt_hash/detailsJson reaches the payload), a
//                 consent_ledger COUNT(*), and an ai_appeals COUNT(*). No
//                 row-level cross-tenant detail is surfaced; no cross-tenant
//                 write. Architect Rule 9 PASS 2026-06-20 (erasure/consent) and
//                 2026-06-21 (ai_appeals count). (Least-privilege column
//                 projection on the erasure read deferred — the response field
//                 accessors reference pre-existing mismatched names, a separate
//                 latent issue outside scope.)
//   SCIM-BEARER:  scim.ts validateBearerToken pre-tenant credential scan on
//                 scim_configs. SCIM routes carry NO session/tenant middleware, so
//                 the presented bearer token must be matched across ALL tenants'
//                 active configs BEFORE tenant identity is known. Under the plain
//                 RLS db the GUC is null -> 0 rows -> every SCIM endpoint 401s under
//                 aegis_app (the module-wide RLS-blindness fix). Same pre-tenant
//                 chicken-and-egg as LOGIN-UTR / D-B=B-3. Read-only; projects only
//                 tenantId + encryptedBearerToken; scoped to isActive configs. The
//                 tenant-scoped SCIM data reads/writes run under withTenantRls
//                 (SCIM-TENANT), NOT this bypass. Architect Rule 9 PASS (plan)
//                 2026-06-21; diff review to follow.
//
// Any NEW call site requires architect Rule 9 review before addition.
export async function withBypassRls<T>(
  fn: (bypassDb: DrizzleDb) => Promise<T>,
): Promise<T> {
  const client = await appPool.connect();
  try {
    await client.query("BEGIN");
    // SET LOCAL ROLE: scoped to this transaction only (not the connection).
    // NOINHERIT on aegis_app means bypass is not ambient — requires this
    // explicit invocation to activate BYPASSRLS.
    await client.query("SET LOCAL ROLE aegis_rls_bypass");
    const bypassDb = drizzle(client, { schema }) as DrizzleDb;
    const result = await fn(bypassDb);
    await client.query("COMMIT");
    return result;
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

// ── withTenantRls() — bounded tenant-scoped phase for background workers ──────
//
// Background workers (DSAR purge worker) run outside the request-scoped
// tenant middleware. This helper provides equivalent GUC-setting behaviour:
// checks out a client from appPool, begins a transaction, sets the GUC
// `app.current_tenant_id` to the given tenantId via set_config (LOCAL =
// transaction-scoped, reverted on COMMIT/ROLLBACK), executes the callback
// with a Drizzle runner bound to that client, then commits. RLS policies
// evaluate against the GUC exactly as they do in the request middleware path.
//
// Authorised call sites — DR-T003 bypass register:
//   DSAR-TENANT: dsar-purge-worker.ts processClaimedJob Shape 2a
//     (dsar_requests SELECT + downstream erasure scoped to claim.tenantId)
//   UBO-ERASURE-TENANT: ubo-erasure.ts withUboTenantCtx
//     (FU-056 — processCorporateErasure + flagUboForIndividualErasure per-storage-call
//     GUC threading; D-2-B per architect Rule 9 PASS 2026-06-05; each of the 7 UBO
//     storage calls runs in its own withTenantRls transaction — no shared-transaction
//     audit-erasure inconsistency)
//   THREAT-INTEL-TENANT (AS/PLATFORM/2026/008 Chunk B + C): threat-intelligence-db.ts
//     saveFeed/saveIndicator + threat-intel-sync.ts upsertIndicator/syncFeedToDb
//     (threat_feeds / threat_indicators / threat_feed_sync_log tenant-scoped writes;
//     a dedicated tx isolates the in-mem service's fire-and-forget saves from the
//     request transaction — see the request-tx-poison hazard).
//     Chunk C adds threat-intelligence-db.ts recordThreatMatch (threat_matches INSERT
//     + the follow-on threat_indicators match-count bump in the SAME tenant tx;
//     tenant_id stamped from current_tenant_id(), fail-closed if no tenant context).
//   BIOMETRIC-TENANT (AS/PLATFORM/2026/008 Chunk B): wave-final-services.ts
//     saveBiometricProfile (biometric_profiles upsert; tenant = the actor's home
//     tenant resolved from user_tenant_roles is_primary).
//   SOAR-COMPLETION-TENANT (AS/CYBER/READPATH-FIX/SOAR, F-RP-02): soar-playbook.ts
//     runPlaybookAsync detached completion (soar_executions completion persist +
//     soar_playbooks updatePlaybookStats). runPlaybookAsync runs AFTER the response
//     when the request tx + ambient tenant GUC are gone, so it re-enters via
//     withTenantRls(<tenant captured at executePlaybook time>); fail-closed if the
//     captured tenant is missing/"default". Architect Rule 9 PASS 2026-06-20.
//   SCIM-TENANT (SCIM RLS-blindness fix): scim.ts registerScimRoutes post-auth
//     handlers on scim_provisions + scim_configs — GET list, GET by id, POST
//     (createScimProvision insert + config-count update incl. the nested count
//     SELECT), PUT, PATCH, DELETE. validateBearerToken (SCIM-BEARER bypass) returns
//     the tenant; the handler's RLS-table work then runs under that tenant's GUC via
//     the runner (every query uses tdb, never module db). users-table sync stays on
//     module db (non-RLS, platform-key), OUTSIDE the tenant tx. Architect Rule 9
//     PASS (plan) 2026-06-21; diff review to follow.
//
// Any NEW call site requires architect Rule 9 review before addition.
export async function withTenantRls<T>(
  tenantId: string,
  fn: (tenantDb: DrizzleDb) => Promise<T>,
): Promise<T> {
  const client = await appPool.connect();
  try {
    await client.query("BEGIN");
    // set_config with third arg `true` = LOCAL (transaction-scoped, reverts on COMMIT/ROLLBACK).
    await client.query("SELECT set_config('app.current_tenant_id', $1, true)", [tenantId]);
    const tenantDb = drizzle(client, { schema }) as DrizzleDb;
    const result = await fn(tenantDb);
    await client.query("COMMIT");
    return result;
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}