-- Baltique OS Core 0.64.0 — Clinical Content Governance

CREATE TABLE IF NOT EXISTS clinical_content_governance_roles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  role_key VARCHAR(80) NOT NULL,
  role_name VARCHAR(190) NOT NULL,
  description TEXT NULL,
  role_scope_json JSON NULL,
  required_for_json JSON NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_clin_gov_role_key(role_key),
  KEY idx_clin_gov_role_active(active, role_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clinical_content_governance_workflows (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  workflow_key VARCHAR(120) NOT NULL,
  block_id BIGINT UNSIGNED NOT NULL,
  block_code VARCHAR(120) NOT NULL,
  block_version VARCHAR(40) NOT NULL,
  workflow_type VARCHAR(80) NOT NULL DEFAULT 'content_approval',
  status VARCHAR(40) NOT NULL DEFAULT 'draft',
  required_roles_json JSON NULL,
  approved_roles_json JSON NULL,
  rejected_roles_json JSON NULL,
  effective_from DATE NULL,
  effective_to DATE NULL,
  recertification_interval_days INT UNSIGNED NOT NULL DEFAULT 365,
  recertification_due_at DATE NULL,
  owner_user_id BIGINT UNSIGNED NULL,
  started_by_user_id BIGINT UNSIGNED NULL,
  started_at DATETIME NULL,
  completed_at DATETIME NULL,
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_clin_gov_workflow_key(workflow_key),
  KEY idx_clin_gov_workflow_block(block_id, status),
  KEY idx_clin_gov_workflow_due(recertification_due_at, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clinical_content_governance_approvals (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  workflow_id BIGINT UNSIGNED NOT NULL,
  block_id BIGINT UNSIGNED NOT NULL,
  role_key VARCHAR(80) NOT NULL,
  decision VARCHAR(40) NOT NULL DEFAULT 'pending',
  signer_user_id BIGINT UNSIGNED NULL,
  signer_label VARCHAR(190) NULL,
  signature_data_url LONGTEXT NULL,
  signature_sha256 VARCHAR(128) NULL,
  decision_note TEXT NULL,
  decided_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_clin_gov_approval_role(workflow_id, role_key),
  KEY idx_clin_gov_approval_block(block_id, decision)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clinical_content_governance_recertifications (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  workflow_id BIGINT UNSIGNED NULL,
  block_id BIGINT UNSIGNED NOT NULL,
  due_at DATE NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'due',
  trigger_reason VARCHAR(190) NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_clin_gov_recert_due(due_at,status),
  KEY idx_clin_gov_recert_block(block_id,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clinical_content_governance_audit_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  workflow_id BIGINT UNSIGNED NULL,
  block_id BIGINT UNSIGNED NULL,
  event_type VARCHAR(80) NOT NULL,
  event_body TEXT NULL,
  metadata_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_clin_gov_audit_workflow(workflow_id, created_at),
  KEY idx_clin_gov_audit_block(block_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
