-- V021 Full Tenant Isolation and Legacy Migration
CREATE TABLE IF NOT EXISTS tenant_resource_scopes_v021 (
 id VARCHAR(36) PRIMARY KEY, tenant_id VARCHAR(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE,
 resource_type VARCHAR(120) NOT NULL, resource_id VARCHAR(120) NOT NULL, source_table VARCHAR(180) NOT NULL DEFAULT '',
 isolation_mode VARCHAR(30) NOT NULL DEFAULT 'physical', assigned_by VARCHAR(36) NULL REFERENCES users(id) ON DELETE SET NULL,
 assigned_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT uq_tenant_resource_scope_v021 UNIQUE(resource_type,resource_id)
);
CREATE INDEX IF NOT EXISTS ix_trs_tenant_type_v021 ON tenant_resource_scopes_v021(tenant_id,resource_type);
CREATE TABLE IF NOT EXISTS tenant_isolation_violations_v021 (
 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, resource_type VARCHAR(120) NOT NULL,
 resource_id VARCHAR(120) NOT NULL DEFAULT '', action VARCHAR(80) NOT NULL DEFAULT 'read', reason VARCHAR(300) NOT NULL,
 request_id VARCHAR(120) NOT NULL DEFAULT '', details JSONB NOT NULL DEFAULT '{}'::jsonb,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS ix_tiv_tenant_created_v021 ON tenant_isolation_violations_v021(tenant_id,created_at DESC);
CREATE TABLE IF NOT EXISTS tenant_migration_batches_v021 (
 id VARCHAR(36) PRIMARY KEY, tenant_id VARCHAR(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE,
 resource_type VARCHAR(120) NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'queued', strategy VARCHAR(40) NOT NULL DEFAULT 'manual',
 scanned_count INTEGER NOT NULL DEFAULT 0, assigned_count INTEGER NOT NULL DEFAULT 0, skipped_count INTEGER NOT NULL DEFAULT 0,
 error_count INTEGER NOT NULL DEFAULT 0, checkpoint JSONB NOT NULL DEFAULT '{}'::jsonb, dry_run BOOLEAN NOT NULL DEFAULT TRUE,
 requested_by VARCHAR(36) NOT NULL REFERENCES users(id), started_at TIMESTAMPTZ NULL, finished_at TIMESTAMPTZ NULL,
 created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS ix_tmb_tenant_status_v021 ON tenant_migration_batches_v021(tenant_id,status);
CREATE TABLE IF NOT EXISTS tenant_isolation_policies_v021 (
 id VARCHAR(36) PRIMARY KEY, tenant_id VARCHAR(36) NOT NULL UNIQUE REFERENCES tenants_v020(id) ON DELETE CASCADE,
 enforcement_mode VARCHAR(30) NOT NULL DEFAULT 'strict', require_tenant_header BOOLEAN NOT NULL DEFAULT TRUE,
 block_unscoped_resources BOOLEAN NOT NULL DEFAULT TRUE, audit_denied_access BOOLEAN NOT NULL DEFAULT TRUE,
 allow_platform_override BOOLEAN NOT NULL DEFAULT FALSE, legacy_migration_complete BOOLEAN NOT NULL DEFAULT FALSE,
 updated_by VARCHAR(36) NULL REFERENCES users(id) ON DELETE SET NULL, updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Physical tenant columns for highest-risk shared-schema resources. Existing rows remain NULL until assigned by a controlled V021 batch.
ALTER TABLE data_sources ADD COLUMN IF NOT EXISTS tenant_id VARCHAR(36) NULL REFERENCES tenants_v020(id) ON DELETE CASCADE;
ALTER TABLE datasets ADD COLUMN IF NOT EXISTS tenant_id VARCHAR(36) NULL REFERENCES tenants_v020(id) ON DELETE CASCADE;
ALTER TABLE dashboards ADD COLUMN IF NOT EXISTS tenant_id VARCHAR(36) NULL REFERENCES tenants_v020(id) ON DELETE CASCADE;
ALTER TABLE reports ADD COLUMN IF NOT EXISTS tenant_id VARCHAR(36) NULL REFERENCES tenants_v020(id) ON DELETE CASCADE;
ALTER TABLE automations ADD COLUMN IF NOT EXISTS tenant_id VARCHAR(36) NULL REFERENCES tenants_v020(id) ON DELETE CASCADE;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS tenant_id VARCHAR(36) NULL REFERENCES tenants_v020(id) ON DELETE CASCADE;
ALTER TABLE api_definitions ADD COLUMN IF NOT EXISTS tenant_id VARCHAR(36) NULL REFERENCES tenants_v020(id) ON DELETE CASCADE;
ALTER TABLE semantic_models_v015 ADD COLUMN IF NOT EXISTS tenant_id VARCHAR(36) NULL REFERENCES tenants_v020(id) ON DELETE CASCADE;
ALTER TABLE semantic_executions_v016 ADD COLUMN IF NOT EXISTS tenant_id VARCHAR(36) NULL REFERENCES tenants_v020(id) ON DELETE CASCADE;
ALTER TABLE federated_query_jobs_v017 ADD COLUMN IF NOT EXISTS tenant_id VARCHAR(36) NULL REFERENCES tenants_v020(id) ON DELETE CASCADE;
ALTER TABLE query_exports_v018 ADD COLUMN IF NOT EXISTS tenant_id VARCHAR(36) NULL REFERENCES tenants_v020(id) ON DELETE CASCADE;
ALTER TABLE object_storage_artifacts_v019 ADD COLUMN IF NOT EXISTS tenant_id VARCHAR(36) NULL REFERENCES tenants_v020(id) ON DELETE CASCADE;
CREATE INDEX IF NOT EXISTS ix_data_sources_tenant_v021 ON data_sources(tenant_id);
CREATE INDEX IF NOT EXISTS ix_datasets_tenant_v021 ON datasets(tenant_id);
CREATE INDEX IF NOT EXISTS ix_dashboards_tenant_v021 ON dashboards(tenant_id);
CREATE INDEX IF NOT EXISTS ix_reports_tenant_v021 ON reports(tenant_id);
CREATE INDEX IF NOT EXISTS ix_semantic_models_tenant_v021 ON semantic_models_v015(tenant_id);
CREATE INDEX IF NOT EXISTS ix_federated_jobs_tenant_v021 ON federated_query_jobs_v017(tenant_id);