import { sql } from 'drizzle-orm'
import type { PostgresTransaction, TransactionalDatabase } from '@platform-modules/db'
import { ScopeViolationError } from './index.js'

export type RunInTenantOptions = {
  gucName?: string
  role?: string
}

function quotedIdent(ident: string): string {
  if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(ident)) {
    throw new Error(`invalid SQL identifier: ${ident}`)
  }
  return `"${ident.replace(/"/g, '""')}"`
}

export async function runInTenant<S extends Record<string, unknown>, T>(
  db: TransactionalDatabase<S>,
  tenantId: string,
  fn: (scoped: PostgresTransaction<S>) => Promise<T>,
  opts?: RunInTenantOptions,
): Promise<T> {
  const gucName = opts?.gucName ?? 'app.current_tenant'

  return db.transaction(async (tx) => {
    await tx.execute(sql`select set_config(${gucName}, ${tenantId}, true)`)
    if (opts?.role) {
      await tx.execute(sql.raw(`SET LOCAL ROLE ${quotedIdent(opts.role)}`))
    }

    // SECURITY FLOOR (fail-loud, every call — no caching): RLS is SILENTLY bypassed
    // by a privileged role, so refuse to run `fn` under one. The check runs AFTER any
    // `SET LOCAL ROLE` (a correct non-priv switch clears it) and BEFORE `fn`.
    // `is_superuser` alone is insufficient — a non-superuser BYPASSRLS role reads
    // 'off' yet bypasses RLS, hence the combined `rolbypassrls` check.
    // Cross-driver result shape: pglite/neon return `{ rows }`, postgres-js is
    // array-like — read the boolean robustly across both.
    const res = (await tx.execute(
      sql`SELECT ((current_setting('is_superuser') = 'on') OR EXISTS (SELECT 1 FROM pg_roles WHERE rolname = current_user AND rolbypassrls)) AS can_bypass, current_user AS effective_role`,
    )) as unknown as
      | { rows?: Array<{ can_bypass?: boolean; effective_role?: string }> }
      | Array<{ can_bypass?: boolean; effective_role?: string }>
    const row = (Array.isArray(res) ? res : res.rows)?.[0]
    if (row?.can_bypass) {
      throw new ScopeViolationError(
        `tenancy: runInTenant requires a non-superuser, non-BYPASSRLS role — RLS is silently bypassed by privileged roles (current_user=${row?.effective_role ?? 'unknown'}). See the security floor in the boundary spec.`,
        tenantId,
      )
    }

    return fn(tx)
  })
}

export type TenantPolicyDDLOptions = {
  column?: string
  gucName?: string
  force?: boolean
}

export function tenantPolicyDDL(
  tableName: string,
  opts?: TenantPolicyDDLOptions,
): string[] {
  const column = opts?.column ?? 'tenant_id'
  const gucName = opts?.gucName ?? 'app.current_tenant'
  const force = opts?.force ?? true
  const table = quotedIdent(tableName)
  const col = quotedIdent(column)

  const statements = [`ALTER TABLE ${table} ENABLE ROW LEVEL SECURITY`]
  if (force) {
    statements.push(`ALTER TABLE ${table} FORCE ROW LEVEL SECURITY`)
  }
  statements.push(
    `CREATE POLICY tenant_isolation ON ${table} USING (${col}::text = current_setting('${gucName.replace(/'/g, "''")}', true))`,
  )
  return statements
}