-- 0pt analytics domain model (BI-P002/BI-P003/BI-P004).
--
-- The nineteen canonical entities from BI-P002, one table each, named after
-- the entity (src/analytics/domain/entities.ts's ANALYTICS_ENTITY_TABLES maps
-- entity -> table, and tests/analytics/analytics-domain-postgres.realinfra.test.ts
-- asserts every entry exists here -- a rename or omission is a test failure,
-- not silent drift).
--
-- Every tenant-owned table carries `tenant_id` + `project_id`, and every one
-- of them is indexed leading with (tenant_id, project_id) so a tenant-scoped
-- read is an index range scan rather than a filter over everyone's rows.
--
-- IMMUTABILITY (BI-P002: "Use immutable versions for MetricVersion,
-- SemanticModel version, DashboardVersion, DatasetVersion") is enforced by
-- BEFORE UPDATE/DELETE triggers, not by convention and not by a TypeScript
-- `readonly`. A compiler opinion does not survive a psql session; a trigger
-- does. `opt_analytics_forbid_mutation()` raises, so an UPDATE against any of
-- the four version tables fails loudly.
--
-- Idempotent throughout (CREATE ... IF NOT EXISTS, CREATE OR REPLACE
-- FUNCTION, DROP TRIGGER IF EXISTS before CREATE TRIGGER) so it can be
-- replayed onto an already-migrated database -- the same contract
-- sql/002_source_registry.sql and sql/007_ingestion_plane.sql keep, and what
-- the restore drill (`psql -d opt < sql/001... < ... < sql/008...`) needs.
--
-- Ordering: standalone. It references no earlier table, so 001-007 are not a
-- prerequisite; it is numbered 008 to keep the replay order linear.

-- ── immutability enforcement ──────────────────────────────────────────────
CREATE OR REPLACE FUNCTION opt_analytics_forbid_mutation() RETURNS trigger AS $$
BEGIN
  RAISE EXCEPTION
    'BI-P002: % rows are immutable (attempted %); mint a new version instead',
    TG_TABLE_NAME, TG_OP
    USING ERRCODE = 'restrict_violation';
END;
$$ LANGUAGE plpgsql;

-- ── 1. AnalyticsDataset ───────────────────────────────────────────────────
-- BI-P003 DoD: tier/schema/owner/source/freshness/quality/retention/
-- classification are all NOT NULL. An optional declaration is not one.
CREATE TABLE IF NOT EXISTS opt_analytics_dataset (
  dataset_id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  name TEXT NOT NULL,
  tier TEXT NOT NULL CHECK (tier IN ('RAW','NORMALIZED','CURATED','SERVING')),
  schema_contract_id TEXT NOT NULL,
  schema_contract_version TEXT NOT NULL,
  owner_id TEXT NOT NULL,
  source_ref TEXT NOT NULL,
  freshness_slo_seconds INTEGER NOT NULL CHECK (freshness_slo_seconds > 0),
  quality_rule_ids TEXT[] NOT NULL DEFAULT '{}',
  retention_days INTEGER NOT NULL CHECK (retention_days >= 0),
  classification TEXT NOT NULL CHECK (classification IN ('none','pseudonymous','personal','sensitive')),
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','active','deprecated','retired')),
  raw_access_exception TEXT,
  description TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ,
  created_by TEXT
);
CREATE INDEX IF NOT EXISTS opt_analytics_dataset_tenant_idx
  ON opt_analytics_dataset (tenant_id, project_id, status);

-- ── 2. DatasetVersion (IMMUTABLE) ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS opt_analytics_dataset_version (
  dataset_version_id TEXT PRIMARY KEY,
  dataset_id TEXT NOT NULL REFERENCES opt_analytics_dataset (dataset_id),
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  version INTEGER NOT NULL CHECK (version >= 1),
  contract_hash TEXT NOT NULL,
  schema_contract_version TEXT NOT NULL,
  record_count BIGINT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by TEXT,
  UNIQUE (dataset_id, version)
);
CREATE INDEX IF NOT EXISTS opt_analytics_dataset_version_tenant_idx
  ON opt_analytics_dataset_version (tenant_id, project_id, dataset_id, version);
DROP TRIGGER IF EXISTS opt_analytics_dataset_version_immutable ON opt_analytics_dataset_version;
CREATE TRIGGER opt_analytics_dataset_version_immutable
  BEFORE UPDATE OR DELETE ON opt_analytics_dataset_version
  FOR EACH ROW EXECUTE FUNCTION opt_analytics_forbid_mutation();

-- ── 3. DatasetField ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS opt_analytics_dataset_field (
  dataset_field_id TEXT PRIMARY KEY,
  dataset_id TEXT NOT NULL REFERENCES opt_analytics_dataset (dataset_id),
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  dataset_version INTEGER NOT NULL,
  name TEXT NOT NULL,
  field_type TEXT NOT NULL CHECK (field_type IN ('string','int64','float64','bool','date','timestamp','decimal','json')),
  nullable BOOLEAN NOT NULL,
  role TEXT NOT NULL CHECK (role IN ('identifier','dimension','measure','attribute')),
  primary_identifier BOOLEAN NOT NULL DEFAULT false,
  classification TEXT NOT NULL DEFAULT 'none' CHECK (classification IN ('none','pseudonymous','personal','sensitive')),
  description TEXT,
  UNIQUE (dataset_id, dataset_version, name)
);
CREATE INDEX IF NOT EXISTS opt_analytics_dataset_field_tenant_idx
  ON opt_analytics_dataset_field (tenant_id, project_id, dataset_id);

-- ── 4. DatasetGrant ───────────────────────────────────────────────────────
-- allowed_purposes is NOT NULL and CHECKed non-empty: a grant without a
-- purpose is a GDPR purpose-limitation hole, so the database refuses it.
CREATE TABLE IF NOT EXISTS opt_analytics_dataset_grant (
  grant_id TEXT PRIMARY KEY,
  dataset_id TEXT NOT NULL REFERENCES opt_analytics_dataset (dataset_id),
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  subject_id TEXT NOT NULL,
  subject_type TEXT NOT NULL CHECK (subject_type IN ('user','role','service','bi_application')),
  level TEXT NOT NULL CHECK (level IN ('read','query','export','admin')),
  -- coalesce(...) is load-bearing: array_length('{}', 1) is NULL in Postgres,
  -- and a CHECK that evaluates to NULL PASSES. Written the obvious way, this
  -- constraint could never fail -- caught by
  -- tests/analytics/analytics-domain-postgres.realinfra.test.ts inserting an
  -- empty array and watching it succeed.
  allowed_purposes TEXT[] NOT NULL CONSTRAINT opt_analytics_dataset_grant_purposes_nonempty
    CHECK (coalesce(array_length(allowed_purposes, 1), 0) >= 1),
  expires_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by TEXT
);
CREATE INDEX IF NOT EXISTS opt_analytics_dataset_grant_tenant_idx
  ON opt_analytics_dataset_grant (tenant_id, project_id, dataset_id, subject_id);

-- ── 5. MetricDefinition ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS opt_analytics_metric_definition (
  metric_id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  name TEXT NOT NULL,
  dataset_id TEXT NOT NULL REFERENCES opt_analytics_dataset (dataset_id),
  aggregation TEXT NOT NULL CHECK (aggregation IN ('sum','avg','min','max','count','count_distinct','ratio','derived')),
  measure_field TEXT NOT NULL,
  denominator_field TEXT,
  allowed_dimension_ids TEXT[] NOT NULL DEFAULT '{}',
  current_version INTEGER NOT NULL DEFAULT 1,
  owner_id TEXT NOT NULL,
  description TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ,
  UNIQUE (tenant_id, name)
);
CREATE INDEX IF NOT EXISTS opt_analytics_metric_definition_tenant_idx
  ON opt_analytics_metric_definition (tenant_id, project_id, dataset_id);

-- ── 6. MetricVersion (IMMUTABLE) ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS opt_analytics_metric_version (
  metric_version_id TEXT PRIMARY KEY,
  metric_id TEXT NOT NULL REFERENCES opt_analytics_metric_definition (metric_id),
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  version INTEGER NOT NULL CHECK (version >= 1),
  definition_json TEXT NOT NULL,
  definition_hash TEXT NOT NULL,
  change_reason TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by TEXT,
  UNIQUE (metric_id, version),
  -- BI-P031 governance in the schema: every version after the first must say
  -- WHY the number changed meaning.
  CHECK (version = 1 OR (change_reason IS NOT NULL AND length(btrim(change_reason)) > 0))
);
CREATE INDEX IF NOT EXISTS opt_analytics_metric_version_tenant_idx
  ON opt_analytics_metric_version (tenant_id, project_id, metric_id, version);
DROP TRIGGER IF EXISTS opt_analytics_metric_version_immutable ON opt_analytics_metric_version;
CREATE TRIGGER opt_analytics_metric_version_immutable
  BEFORE UPDATE OR DELETE ON opt_analytics_metric_version
  FOR EACH ROW EXECUTE FUNCTION opt_analytics_forbid_mutation();

-- ── 7. DimensionDefinition ────────────────────────────────────────────────
-- The allow-list the query compiler resolves caller-supplied dimension names
-- through (BI-P011 "dimension identifier injection"). `field_name` is CHECKed
-- against a strict identifier pattern in the DATABASE as well as in the
-- compiler, because a hostile row here would otherwise be the injection.
CREATE TABLE IF NOT EXISTS opt_analytics_dimension_definition (
  dimension_id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  name TEXT NOT NULL,
  dataset_id TEXT NOT NULL REFERENCES opt_analytics_dataset (dataset_id),
  field_name TEXT NOT NULL CHECK (field_name ~ '^[A-Za-z_][A-Za-z0-9_]{0,62}$'),
  field_type TEXT NOT NULL CHECK (field_type IN ('string','int64','float64','bool','date','timestamp','decimal','json')),
  classification TEXT NOT NULL DEFAULT 'none' CHECK (classification IN ('none','pseudonymous','personal','sensitive')),
  description TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, dataset_id, name)
);
CREATE INDEX IF NOT EXISTS opt_analytics_dimension_definition_tenant_idx
  ON opt_analytics_dimension_definition (tenant_id, project_id, dataset_id);

-- ── 8. SemanticModel (versions IMMUTABLE) ─────────────────────────────────
CREATE TABLE IF NOT EXISTS opt_analytics_semantic_model (
  semantic_model_id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  name TEXT NOT NULL,
  version INTEGER NOT NULL CHECK (version >= 1),
  model_json TEXT NOT NULL,
  model_hash TEXT NOT NULL,
  dataset_ids TEXT[] NOT NULL DEFAULT '{}',
  owner_id TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by TEXT,
  UNIQUE (tenant_id, name, version)
);
CREATE INDEX IF NOT EXISTS opt_analytics_semantic_model_tenant_idx
  ON opt_analytics_semantic_model (tenant_id, project_id, name, version);
DROP TRIGGER IF EXISTS opt_analytics_semantic_model_immutable ON opt_analytics_semantic_model;
CREATE TRIGGER opt_analytics_semantic_model_immutable
  BEFORE UPDATE OR DELETE ON opt_analytics_semantic_model
  FOR EACH ROW EXECUTE FUNCTION opt_analytics_forbid_mutation();

-- ── 9. AnalyticsQuery ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS opt_analytics_query (
  query_id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  name TEXT NOT NULL,
  surface TEXT NOT NULL CHECK (surface IN ('metric','sql','projection')),
  dataset_id TEXT NOT NULL REFERENCES opt_analytics_dataset (dataset_id),
  metric_id TEXT REFERENCES opt_analytics_metric_definition (metric_id),
  request_json TEXT NOT NULL,
  owner_id TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS opt_analytics_query_tenant_idx
  ON opt_analytics_query (tenant_id, project_id, dataset_id);

-- ── 10. QueryExecution ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS opt_analytics_query_execution (
  execution_id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  query_id TEXT REFERENCES opt_analytics_query (query_id),
  engine_query_id TEXT NOT NULL,
  state TEXT NOT NULL CHECK (state IN ('queued','running','succeeded','failed','cancelled','rejected')),
  provider TEXT NOT NULL,
  submitted_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  started_at TIMESTAMPTZ,
  finished_at TIMESTAMPTZ,
  duration_ms INTEGER,
  rows_read BIGINT,
  bytes_read BIGINT,
  result_rows BIGINT,
  error_code TEXT,
  correlation_id TEXT
);
CREATE INDEX IF NOT EXISTS opt_analytics_query_execution_tenant_idx
  ON opt_analytics_query_execution (tenant_id, project_id, submitted_at DESC);
CREATE INDEX IF NOT EXISTS opt_analytics_query_execution_engine_idx
  ON opt_analytics_query_execution (engine_query_id);

-- ── 11. QueryResult ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS opt_analytics_query_result (
  result_id TEXT PRIMARY KEY,
  execution_id TEXT NOT NULL REFERENCES opt_analytics_query_execution (execution_id),
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  encoding TEXT NOT NULL CHECK (encoding IN ('json','arrow_ipc')),
  row_count BIGINT NOT NULL,
  artifact_ref TEXT,
  truncated BOOLEAN NOT NULL DEFAULT false,
  expires_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS opt_analytics_query_result_tenant_idx
  ON opt_analytics_query_result (tenant_id, project_id, execution_id);

-- ── 12. DashboardDefinition ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS opt_analytics_dashboard_definition (
  dashboard_id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  name TEXT NOT NULL,
  current_version INTEGER NOT NULL DEFAULT 1,
  owner_id TEXT NOT NULL,
  dataset_ids TEXT[] NOT NULL DEFAULT '{}',
  description TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS opt_analytics_dashboard_definition_tenant_idx
  ON opt_analytics_dashboard_definition (tenant_id, project_id);

-- ── 13. DashboardVersion (IMMUTABLE) ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS opt_analytics_dashboard_version (
  dashboard_version_id TEXT PRIMARY KEY,
  dashboard_id TEXT NOT NULL REFERENCES opt_analytics_dashboard_definition (dashboard_id),
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  version INTEGER NOT NULL CHECK (version >= 1),
  layout_json TEXT NOT NULL,
  layout_hash TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by TEXT,
  UNIQUE (dashboard_id, version)
);
CREATE INDEX IF NOT EXISTS opt_analytics_dashboard_version_tenant_idx
  ON opt_analytics_dashboard_version (tenant_id, project_id, dashboard_id, version);
DROP TRIGGER IF EXISTS opt_analytics_dashboard_version_immutable ON opt_analytics_dashboard_version;
CREATE TRIGGER opt_analytics_dashboard_version_immutable
  BEFORE UPDATE OR DELETE ON opt_analytics_dashboard_version
  FOR EACH ROW EXECUTE FUNCTION opt_analytics_forbid_mutation();

-- ── 14. ReportDefinition ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS opt_analytics_report_definition (
  report_id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  name TEXT NOT NULL,
  dashboard_id TEXT REFERENCES opt_analytics_dashboard_definition (dashboard_id),
  query_id TEXT REFERENCES opt_analytics_query (query_id),
  format TEXT NOT NULL CHECK (format IN ('pdf','csv','xlsx','parquet','arrow_ipc')),
  schedule_cron TEXT,
  owner_id TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ,
  CHECK (dashboard_id IS NOT NULL OR query_id IS NOT NULL)
);
CREATE INDEX IF NOT EXISTS opt_analytics_report_definition_tenant_idx
  ON opt_analytics_report_definition (tenant_id, project_id);

-- ── 15. ReportRun ─────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS opt_analytics_report_run (
  report_run_id TEXT PRIMARY KEY,
  report_id TEXT NOT NULL REFERENCES opt_analytics_report_definition (report_id),
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  state TEXT NOT NULL CHECK (state IN ('queued','running','succeeded','failed','cancelled','rejected')),
  started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  finished_at TIMESTAMPTZ,
  artifact_ref TEXT,
  error_code TEXT,
  correlation_id TEXT
);
CREATE INDEX IF NOT EXISTS opt_analytics_report_run_tenant_idx
  ON opt_analytics_report_run (tenant_id, project_id, report_id, started_at DESC);

-- ── 16. DataQualityRule ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS opt_analytics_data_quality_rule (
  rule_id TEXT PRIMARY KEY,
  dataset_id TEXT NOT NULL REFERENCES opt_analytics_dataset (dataset_id),
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  kind TEXT NOT NULL CHECK (kind IN ('not_null','unique','accepted_values','range','freshness','row_count','referential','custom')),
  field_name TEXT,
  params_json TEXT NOT NULL DEFAULT '{}',
  severity TEXT NOT NULL CHECK (severity IN ('warn','block')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by TEXT
);
CREATE INDEX IF NOT EXISTS opt_analytics_data_quality_rule_tenant_idx
  ON opt_analytics_data_quality_rule (tenant_id, project_id, dataset_id);

-- ── 17. DataQualityRun ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS opt_analytics_data_quality_run (
  quality_run_id TEXT PRIMARY KEY,
  rule_id TEXT NOT NULL REFERENCES opt_analytics_data_quality_rule (rule_id),
  dataset_id TEXT NOT NULL REFERENCES opt_analytics_dataset (dataset_id),
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  dataset_version INTEGER,
  passed BOOLEAN NOT NULL,
  violation_count BIGINT NOT NULL DEFAULT 0,
  evaluated_rows BIGINT NOT NULL DEFAULT 0,
  started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  finished_at TIMESTAMPTZ,
  correlation_id TEXT
);
CREATE INDEX IF NOT EXISTS opt_analytics_data_quality_run_tenant_idx
  ON opt_analytics_data_quality_run (tenant_id, project_id, dataset_id, started_at DESC);

-- ── 18. LineageReference ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS opt_analytics_lineage_reference (
  lineage_reference_id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  run_id TEXT NOT NULL,
  job_name TEXT NOT NULL,
  direction TEXT NOT NULL CHECK (direction IN ('input','output')),
  entity_type TEXT NOT NULL CHECK (entity_type IN ('dataset','metric','dashboard','report')),
  entity_id TEXT NOT NULL,
  counterpart_ref TEXT NOT NULL,
  observed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS opt_analytics_lineage_reference_tenant_idx
  ON opt_analytics_lineage_reference (tenant_id, project_id, entity_type, entity_id);

-- ── 19. BIApplicationBinding ──────────────────────────────────────────────
-- NORMATIVE: there is NO credential column here, and there must never be one.
-- SIAX/0pt owns the analytics engine; a bound BI application authenticates to
-- the 0pt Analytics API with a CL0UD identity and receives compiled,
-- tenant-forced results. It never learns a ClickHouse DSN, user, or password.
CREATE TABLE IF NOT EXISTS opt_analytics_bi_application_binding (
  binding_id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  project_id TEXT NOT NULL,
  kind TEXT NOT NULL CHECK (kind IN ('superset','evidence','des1gn','notebook','custom')),
  name TEXT NOT NULL,
  principal_id TEXT NOT NULL,
  dataset_ids TEXT[] NOT NULL DEFAULT '{}',
  metric_ids TEXT[] NOT NULL DEFAULT '{}',
  -- Same NULL-CHECK trap as opt_analytics_dataset_grant; see the note there.
  allowed_purposes TEXT[] NOT NULL CONSTRAINT opt_analytics_bi_app_binding_purposes_nonempty
    CHECK (coalesce(array_length(allowed_purposes, 1), 0) >= 1),
  enabled BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by TEXT,
  UNIQUE (tenant_id, name)
);
CREATE INDEX IF NOT EXISTS opt_analytics_bi_application_binding_tenant_idx
  ON opt_analytics_bi_application_binding (tenant_id, project_id, principal_id);

-- ── constraint upgrade path ───────────────────────────────────────────────
-- CREATE TABLE IF NOT EXISTS does nothing to a table that already exists, so
-- a database that received an earlier copy of this file would keep the old
-- (NULL-passing) purpose checks. These statements bring it forward and are
-- no-ops on a fresh database.
ALTER TABLE opt_analytics_dataset_grant
  DROP CONSTRAINT IF EXISTS opt_analytics_dataset_grant_purposes_nonempty;
ALTER TABLE opt_analytics_dataset_grant
  ADD CONSTRAINT opt_analytics_dataset_grant_purposes_nonempty
  CHECK (coalesce(array_length(allowed_purposes, 1), 0) >= 1);

ALTER TABLE opt_analytics_bi_application_binding
  DROP CONSTRAINT IF EXISTS opt_analytics_bi_app_binding_purposes_nonempty;
ALTER TABLE opt_analytics_bi_application_binding
  ADD CONSTRAINT opt_analytics_bi_app_binding_purposes_nonempty
  CHECK (coalesce(array_length(allowed_purposes, 1), 0) >= 1);