-- Baltique OS Core 0.60.0 — Communication Consent Center

CREATE TABLE IF NOT EXISTS communication_consent_policies (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  policy_code VARCHAR(100) NOT NULL,
  purpose_code VARCHAR(80) NOT NULL,
  channel_code VARCHAR(40) NOT NULL,
  label VARCHAR(190) NOT NULL,
  description TEXT NULL,
  legal_basis VARCHAR(120) NOT NULL DEFAULT 'contract',
  requires_explicit TINYINT(1) NOT NULL DEFAULT 0,
  default_allowed TINYINT(1) NOT NULL DEFAULT 1,
  patient_visible TINYINT(1) NOT NULL DEFAULT 1,
  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_consent_policy (purpose_code, channel_code),
  KEY idx_consent_policy_active (active, purpose_code, channel_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_consent_records (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  purpose_code VARCHAR(80) NOT NULL,
  channel_code VARCHAR(40) NOT NULL,
  allowed TINYINT(1) NOT NULL DEFAULT 0,
  legal_basis VARCHAR(120) NOT NULL DEFAULT 'contract',
  source VARCHAR(80) NOT NULL DEFAULT 'staff',
  evidence_text TEXT NULL,
  consent_version VARCHAR(80) NULL,
  valid_from DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  valid_to DATETIME NULL,
  updated_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_consent_patient_purpose_channel (patient_id, purpose_code, channel_code),
  KEY idx_consent_patient (patient_id, allowed),
  KEY idx_consent_purpose_channel (purpose_code, channel_code, allowed)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_consent_history (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  purpose_code VARCHAR(80) NOT NULL,
  channel_code VARCHAR(40) NOT NULL,
  old_allowed TINYINT(1) NULL,
  new_allowed TINYINT(1) NOT NULL,
  old_legal_basis VARCHAR(120) NULL,
  new_legal_basis VARCHAR(120) NOT NULL,
  source VARCHAR(80) NOT NULL DEFAULT 'staff',
  evidence_text TEXT NULL,
  changed_by_user_id BIGINT UNSIGNED NULL,
  ip_address VARCHAR(80) NULL,
  user_agent TEXT NULL,
  changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_consent_hist_patient (patient_id, changed_at),
  KEY idx_consent_hist_channel (purpose_code, channel_code, changed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_consent_tokens (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  token_hash VARCHAR(128) NOT NULL,
  purpose VARCHAR(80) NOT NULL DEFAULT 'preference_update',
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  expires_at DATETIME NULL,
  last_used_at DATETIME NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_consent_token_hash (token_hash),
  KEY idx_consent_token_patient (patient_id, status, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_consent_blocks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  purpose_code VARCHAR(80) NULL,
  channel_code VARCHAR(40) NOT NULL,
  reason TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_consent_block_patient (patient_id, active),
  KEY idx_consent_block_channel (channel_code, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_consent_decision_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NULL,
  purpose_code VARCHAR(80) NULL,
  channel_code VARCHAR(40) NOT NULL,
  route_code VARCHAR(80) NULL,
  allowed TINYINT(1) NOT NULL DEFAULT 0,
  reason TEXT NULL,
  source VARCHAR(80) NULL,
  context_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_consent_decision_patient (patient_id, created_at),
  KEY idx_consent_decision_route (route_code, channel_code, allowed)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
