-- Baltique OS Core 0.50.0 — Inbound Triage AI / Mail Queue Reliability

CREATE TABLE IF NOT EXISTS communication_inbound_triage_results (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  inbound_message_id BIGINT UNSIGNED NULL,
  hub_message_id BIGINT UNSIGNED NULL,
  thread_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  source_channel VARCHAR(40) NULL,
  category VARCHAR(80) NOT NULL DEFAULT 'other',
  severity VARCHAR(40) NOT NULL DEFAULT 'normal',
  sla_minutes INT UNSIGNED NOT NULL DEFAULT 240,
  sla_due_at DATETIME NULL,
  alarm_score INT NOT NULL DEFAULT 0,
  confidence DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  detected_terms TEXT NULL,
  summary TEXT NULL,
  recommended_action TEXT NULL,
  escalation_role VARCHAR(80) NULL,
  task_id BIGINT UNSIGNED NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  triaged_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reviewed_by_user_id BIGINT UNSIGNED NULL,
  reviewed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_triage_inbound (inbound_message_id),
  KEY idx_triage_status (status, severity, sla_due_at),
  KEY idx_triage_case (operative_case_id, status),
  KEY idx_triage_thread (thread_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_inbound_triage_suggestions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  triage_id BIGINT UNSIGNED NOT NULL,
  inbound_message_id BIGINT UNSIGNED NULL,
  hub_message_id BIGINT UNSIGNED NULL,
  thread_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  suggestion_type VARCHAR(80) NOT NULL DEFAULT 'reply',
  audience VARCHAR(80) NOT NULL DEFAULT 'front_coordination',
  subject VARCHAR(255) NULL,
  body MEDIUMTEXT NOT NULL,
  priority VARCHAR(40) NOT NULL DEFAULT 'normal',
  status VARCHAR(40) NOT NULL DEFAULT 'draft',
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_triage_sugg_triage (triage_id, status),
  KEY idx_triage_sugg_case (operative_case_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_inbound_sla_policies (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  severity VARCHAR(40) NOT NULL,
  category VARCHAR(80) NOT NULL DEFAULT '*',
  sla_minutes INT UNSIGNED NOT NULL DEFAULT 240,
  escalation_role VARCHAR(80) 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_sla_policy (severity, category),
  KEY idx_sla_active (active, severity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_inbound_triage_rules (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  rule_key VARCHAR(120) NOT NULL,
  category VARCHAR(80) NOT NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'normal',
  terms TEXT NOT NULL,
  alarm_score INT NOT NULL DEFAULT 0,
  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_triage_rule_key (rule_key),
  KEY idx_triage_rule_active (active, category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mail_queue_worker_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_key VARCHAR(80) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'running',
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  heartbeat_at DATETIME NULL,
  finished_at DATETIME NULL,
  processed_count INT UNSIGNED NOT NULL DEFAULT 0,
  sent_count INT UNSIGNED NOT NULL DEFAULT 0,
  failed_count INT UNSIGNED NOT NULL DEFAULT 0,
  skipped_count INT UNSIGNED NOT NULL DEFAULT 0,
  error_message TEXT NULL,
  pid VARCHAR(80) NULL,
  metadata_json JSON NULL,
  PRIMARY KEY (id),
  KEY idx_mail_worker_runs_status (status, started_at),
  KEY idx_mail_worker_runs_key (run_key, started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mail_queue_worker_locks (
  lock_key VARCHAR(80) NOT NULL,
  run_id BIGINT UNSIGNED NULL,
  locked_by VARCHAR(190) NULL,
  expires_at DATETIME NOT NULL,
  heartbeat_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (lock_key),
  KEY idx_mail_worker_lock_exp (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mail_queue_send_attempts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  mail_queue_id BIGINT UNSIGNED NULL,
  run_id BIGINT UNSIGNED NULL,
  recipient_email VARCHAR(190) NULL,
  subject VARCHAR(255) NULL,
  status VARCHAR(40) NOT NULL,
  error_message TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_mail_attempt_queue (mail_queue_id, created_at),
  KEY idx_mail_attempt_run (run_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mail_queue_dead_letters (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  mail_queue_id BIGINT UNSIGNED NULL,
  recipient_email VARCHAR(190) NULL,
  subject VARCHAR(255) NULL,
  body MEDIUMTEXT NULL,
  reason TEXT NULL,
  attempts INT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_mail_dead_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
