import { sql } from 'drizzle-orm'
import type { Db } from './db.js'

/**
 * Least-privilege database roles (ARCHITECTURE.md §8). Roles are created once per cluster by
 * deploy/postgres-init; grants are (re)applied after every migration so new tables are covered.
 * Missing roles are skipped, so local development with a single superuser keeps working.
 */
export const DB_ROLES = {
  /** apps/sync — writes the catalog, its own bookkeeping, and owns the pg-boss schema. */
  sync: 'mcp_sync',
  /** apps/catalog-api — read-only catalog + sync status. */
  api: 'mcp_api',
  /** apps/mcp-server — audit log (insert-only) and OAuth session tables. Cannot read the catalog. */
  server: 'mcp_server',
} as const

const GRANTS: Record<keyof typeof DB_ROLES, string[]> = {
  sync: [
    'GRANT USAGE ON SCHEMA catalog, ops TO mcp_sync',
    'GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA catalog TO mcp_sync',
    'GRANT SELECT, INSERT, UPDATE ON ops.sync_state, ops.sync_runs TO mcp_sync',
    'GRANT USAGE ON SEQUENCE ops.sync_runs_id_seq TO mcp_sync',
    'CREATE SCHEMA IF NOT EXISTS pgboss AUTHORIZATION mcp_sync',
  ],
  api: [
    'GRANT USAGE ON SCHEMA catalog, ops TO mcp_api',
    'GRANT SELECT ON ALL TABLES IN SCHEMA catalog TO mcp_api',
    'GRANT SELECT ON ops.sync_state, ops.sync_runs TO mcp_api',
  ],
  server: [
    'GRANT USAGE ON SCHEMA ops TO mcp_server',
    'GRANT INSERT ON ops.audit_log TO mcp_server',
    'GRANT USAGE ON SEQUENCE ops.audit_log_id_seq TO mcp_server',
    'GRANT SELECT, INSERT, UPDATE, DELETE ON ops.oauth_clients, ops.oauth_codes, ops.oauth_tokens TO mcp_server',
  ],
}

/** Apply grants for every role that exists. Returns the roles granted. */
export async function applyGrants(db: Db): Promise<string[]> {
  const existing = await db.execute<{ rolname: string }>(
    sql`SELECT rolname FROM pg_roles WHERE rolname = ANY(${sql.param(Object.values(DB_ROLES))}::text[])`,
  )
  const present = new Set(existing.map(r => r.rolname))
  const applied: string[] = []
  for (const [key, role] of Object.entries(DB_ROLES) as Array<[keyof typeof DB_ROLES, string]>) {
    if (!present.has(role)) continue
    for (const stmt of GRANTS[key]) await db.execute(sql.raw(stmt))
    applied.push(role)
  }
  return applied
}