-- V022 Tenant Runtime Enforcement, PostgreSQL RLS, domains, namespaces and quota reservations
CREATE TABLE IF NOT EXISTS tenant_domains_v022 (
 id varchar(36) PRIMARY KEY, tenant_id varchar(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE,
 hostname varchar(255) NOT NULL UNIQUE, verified boolean NOT NULL DEFAULT false, verification_token varchar(128) NOT NULL DEFAULT '',
 is_primary boolean NOT NULL DEFAULT false, tls_status varchar(30) NOT NULL DEFAULT 'pending', created_at timestamptz NOT NULL DEFAULT now(), verified_at timestamptz NULL);
CREATE INDEX IF NOT EXISTS ix_tenant_domains_v022_tenant ON tenant_domains_v022(tenant_id,verified);
CREATE UNIQUE INDEX IF NOT EXISTS uq_tenant_primary_domain_v022 ON tenant_domains_v022(tenant_id) WHERE is_primary=true;
CREATE TABLE IF NOT EXISTS tenant_runtime_namespaces_v022 (
 id varchar(36) PRIMARY KEY, tenant_id varchar(36) NOT NULL UNIQUE REFERENCES tenants_v020(id) ON DELETE CASCADE,
 redis_prefix varchar(180) NOT NULL UNIQUE, celery_queue varchar(180) NOT NULL UNIQUE, object_prefix varchar(500) NOT NULL UNIQUE,
 schema_name varchar(120) NOT NULL DEFAULT 'public', database_alias varchar(120) NOT NULL DEFAULT 'metadata',
 isolation_mode varchar(30) NOT NULL DEFAULT 'shared_schema', enabled boolean NOT NULL DEFAULT true, updated_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS tenant_quota_reservations_v022 (
 id varchar(36) PRIMARY KEY, tenant_id varchar(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE,
 metric_code varchar(100) NOT NULL, quantity integer NOT NULL CHECK(quantity>0), status varchar(30) NOT NULL DEFAULT 'reserved',
 idempotency_key varchar(160) NOT NULL UNIQUE, reference_type varchar(80) NOT NULL DEFAULT '', reference_id varchar(120) NOT NULL DEFAULT '',
 expires_at timestamptz NULL, created_by varchar(36) NULL REFERENCES users(id) ON DELETE SET NULL, created_at timestamptz NOT NULL DEFAULT now(), finalized_at timestamptz NULL);
CREATE INDEX IF NOT EXISTS ix_tenant_quota_v022_active ON tenant_quota_reservations_v022(tenant_id,metric_code,status);
CREATE TABLE IF NOT EXISTS tenant_runtime_audits_v022 (
 id varchar(36) PRIMARY KEY, tenant_id varchar(36) NULL REFERENCES tenants_v020(id) ON DELETE SET NULL,
 user_id varchar(36) NULL REFERENCES users(id) ON DELETE SET NULL, event_type varchar(100) NOT NULL, request_id varchar(120) NOT NULL DEFAULT '',
 hostname varchar(255) NOT NULL DEFAULT '', route varchar(500) NOT NULL DEFAULT '', outcome varchar(30) NOT NULL DEFAULT 'success', details jsonb NOT NULL DEFAULT '{}'::jsonb,
 created_at timestamptz NOT NULL DEFAULT now());

-- Physical tenant columns for dependent high-volume tables.
ALTER TABLE ingestion_runs ADD COLUMN IF NOT EXISTS tenant_id varchar(36) REFERENCES tenants_v020(id) ON DELETE CASCADE;
ALTER TABLE dashboard_widgets ADD COLUMN IF NOT EXISTS tenant_id varchar(36) REFERENCES tenants_v020(id) ON DELETE CASCADE;
ALTER TABLE query_usage_ledger_v017 ADD COLUMN IF NOT EXISTS tenant_id varchar(36) REFERENCES tenants_v020(id) ON DELETE CASCADE;
ALTER TABLE federated_worker_runs_v018 ADD COLUMN IF NOT EXISTS tenant_id varchar(36) REFERENCES tenants_v020(id) ON DELETE CASCADE;
ALTER TABLE query_spill_segments_v018 ADD COLUMN IF NOT EXISTS tenant_id varchar(36) REFERENCES tenants_v020(id) ON DELETE CASCADE;
CREATE INDEX IF NOT EXISTS ix_ingestion_runs_tenant_v022 ON ingestion_runs(tenant_id,created_at);
CREATE INDEX IF NOT EXISTS ix_dashboard_widgets_tenant_v022 ON dashboard_widgets(tenant_id,dashboard_id);

-- RLS uses SET LOCAL app.current_tenant_id performed by V022 runtime middleware/service.
DO $$
DECLARE t text;
BEGIN
 FOREACH t IN ARRAY ARRAY['data_sources','datasets','dashboards','reports','automations','messages','api_definitions','semantic_models_v015','semantic_executions_v016','federated_query_jobs_v017','query_exports_v018','object_storage_artifacts_v019','ingestion_runs','dashboard_widgets','query_usage_ledger_v017','federated_worker_runs_v018','query_spill_segments_v018']
 LOOP
  EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY',t);
  EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY',t);
  EXECUTE format('DROP POLICY IF EXISTS tenant_isolation_v022 ON %I',t);
  EXECUTE format('CREATE POLICY tenant_isolation_v022 ON %I USING (tenant_id::text = current_setting(''app.current_tenant_id'', true)) WITH CHECK (tenant_id::text = current_setting(''app.current_tenant_id'', true))',t);
 END LOOP;
END $$;