-- Dev Department — Human-Gate Approval Workflow (GAL-109)
-- Final consolidated Supabase migration, as applied to LSA_PRO on 2026-04-24.
-- Project: jiidzeympaalzljyqvjq (LSA Pro)
--
-- Applied in two passes:
--   1. Schema + table (pass #1, during scoping session) — see strategos breach notes
--   2. RLS + policies + indexes + comments + GRANTs + exposed-schemas (pass #2, end-to-end build)

-- ============================================================================
-- 1. Schema + table
-- ============================================================================

CREATE SCHEMA IF NOT EXISTS ops;

CREATE TABLE IF NOT EXISTS ops.approval_requests (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  idempotency_key text UNIQUE NOT NULL,
  agent text NOT NULL,
  gate_id text NOT NULL,
  description text NOT NULL,
  detail text,
  risk_level text NOT NULL CHECK (risk_level IN ('low', 'medium', 'high')),
  status text NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'approved', 'rejected', 'timeout', 'revoked')),
  approver text,
  requested_at timestamptz NOT NULL DEFAULT now(),
  resolved_at timestamptz,
  session_id text,
  is_dry_run boolean NOT NULL DEFAULT false
);

-- ============================================================================
-- 2. Row-level security + policies
-- ============================================================================

ALTER TABLE ops.approval_requests ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON ops.approval_requests;
CREATE POLICY "service_role_all" ON ops.approval_requests
  USING (true) WITH CHECK (true);

DROP POLICY IF EXISTS "authenticated_read" ON ops.approval_requests;
CREATE POLICY "authenticated_read" ON ops.approval_requests
  FOR SELECT USING (auth.role() = 'authenticated');

-- ============================================================================
-- 3. Indexes
-- ============================================================================

CREATE UNIQUE INDEX IF NOT EXISTS idx_approval_requests_idempotency
  ON ops.approval_requests (idempotency_key);

CREATE INDEX IF NOT EXISTS idx_approval_requests_status
  ON ops.approval_requests (status);

CREATE INDEX IF NOT EXISTS idx_approval_requests_agent_gate
  ON ops.approval_requests (agent, gate_id);

CREATE INDEX IF NOT EXISTS idx_approval_requests_requested_at
  ON ops.approval_requests (requested_at DESC);

-- ============================================================================
-- 4. Comments (audit trail discoverability)
-- ============================================================================

COMMENT ON TABLE ops.approval_requests IS
  'Dev department human-gate approval requests. Immutable audit trail — only status, resolved_at, and approver may update.';

COMMENT ON COLUMN ops.approval_requests.idempotency_key IS
  'sha256(agent + gate_id + description) — dedupe key enforcing 409 Conflict on duplicate pending and cached-outcome on duplicate resolved.';

COMMENT ON COLUMN ops.approval_requests.gate_id IS
  'One of the 10 gates from dev-department-v0 §7: deploy_billing_auth_payments, db_migration, dns_change, rls_policy_change, free_tier_exceed, new_third_party_service, regulated_content, incident_disclosure, partner_commitment, destructive_op.';

COMMENT ON COLUMN ops.approval_requests.status IS
  'pending | approved | rejected | timeout | revoked. Transitions from pending to any terminal state; terminal states are immutable.';

COMMENT ON COLUMN ops.approval_requests.is_dry_run IS
  'True when the request was issued with ?dry_run=true — workflow runs through insert + Telegram send but does not block on caller.';

-- ============================================================================
-- 5. GRANTs (applied 2026-04-24 to unblock n8n REST writes)
-- ============================================================================

GRANT USAGE ON SCHEMA ops TO service_role, authenticated, anon;
GRANT SELECT, INSERT, UPDATE ON ops.approval_requests TO service_role;
GRANT SELECT ON ops.approval_requests TO authenticated;

-- ============================================================================
-- 6. Expose ops schema via PostgREST (applied via Management API, not SQL)
-- ============================================================================

-- Equivalent of: PATCH /v1/projects/:ref/postgrest
--   body: {"db_schema":"public,graphql_public,ops"}
-- PostgREST re-reads its schema cache on db_schema update + NOTIFY pgrst, 'reload schema'.

NOTIFY pgrst, 'reload schema';
