-- Baltique OS Core 0.59.0 — Smart Multi-Channel Routing / Delete Mail Provider

CREATE TABLE IF NOT EXISTS communication_channel_routes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  route_code VARCHAR(80) NOT NULL,
  route_label VARCHAR(190) NOT NULL,
  description TEXT NULL,
  urgency_rank INT NOT NULL DEFAULT 100,
  selection_policy VARCHAR(80) NOT NULL DEFAULT 'best_score',
  require_human_confirmation TINYINT(1) NOT NULL DEFAULT 1,
  default_sla_minutes INT UNSIGNED NOT NULL DEFAULT 240,
  active TINYINT(1) NOT NULL DEFAULT 1,
  metadata_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_comm_channel_route_code (route_code),
  KEY idx_comm_channel_route_active (active, urgency_rank)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_channel_route_options (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  route_id BIGINT UNSIGNED NOT NULL,
  channel_code VARCHAR(40) NOT NULL,
  rank_order INT NOT NULL DEFAULT 100,
  weight DECIMAL(8,2) NOT NULL DEFAULT 1.00,
  min_delivery_score DECIMAL(8,2) NULL,
  require_consent TINYINT(1) NOT NULL DEFAULT 1,
  allow_if_no_contact TINYINT(1) NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  note TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_comm_channel_opt_route (route_id, active, rank_order),
  KEY idx_comm_channel_opt_channel (channel_code, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_channel_decisions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  decision_uuid VARCHAR(80) NOT NULL,
  route_code VARCHAR(80) NOT NULL,
  detected_by VARCHAR(80) NOT NULL DEFAULT 'auto',
  patient_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  recipient_email VARCHAR(190) NULL,
  recipient_phone VARCHAR(80) NULL,
  subject VARCHAR(255) NULL,
  urgency VARCHAR(40) NOT NULL DEFAULT 'normal',
  selected_channel VARCHAR(40) NULL,
  selected_reason TEXT NULL,
  candidates_json JSON NULL,
  context_json JSON NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'selected',
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_comm_channel_decision_uuid (decision_uuid),
  KEY idx_comm_channel_decision_route (route_code, created_at),
  KEY idx_comm_channel_decision_case (operative_case_id, created_at),
  KEY idx_comm_channel_decision_patient (patient_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_channel_learning (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  score_month CHAR(7) NOT NULL,
  route_code VARCHAR(80) NOT NULL,
  channel_code VARCHAR(40) NOT NULL,
  attempted INT UNSIGNED NOT NULL DEFAULT 0,
  delivered INT UNSIGNED NOT NULL DEFAULT 0,
  read_count INT UNSIGNED NOT NULL DEFAULT 0,
  response_count INT UNSIGNED NOT NULL DEFAULT 0,
  failed INT UNSIGNED NOT NULL DEFAULT 0,
  sla_missed INT UNSIGNED NOT NULL DEFAULT 0,
  delivery_rate DECIMAL(8,4) NOT NULL DEFAULT 0,
  response_rate DECIMAL(8,4) NOT NULL DEFAULT 0,
  smart_score DECIMAL(10,2) NOT NULL DEFAULT 0,
  recommendation TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_comm_channel_learning (score_month, route_code, channel_code),
  KEY idx_comm_channel_learning_score (score_month, smart_score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS patient_channel_preferences (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NOT NULL,
  email_allowed TINYINT(1) NOT NULL DEFAULT 1,
  sms_allowed TINYINT(1) NOT NULL DEFAULT 1,
  portal_allowed TINYINT(1) NOT NULL DEFAULT 1,
  phone_allowed TINYINT(1) NOT NULL DEFAULT 1,
  marketing_email_allowed TINYINT(1) NOT NULL DEFAULT 0,
  marketing_sms_allowed TINYINT(1) NOT NULL DEFAULT 0,
  preferred_channel VARCHAR(40) NULL,
  note TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_patient_channel_pref (patient_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
