-- Baltique OS Core 0.49.0 — Active Inbound Communication Hub

CREATE TABLE IF NOT EXISTS communication_hub_inbound_sources (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source_key VARCHAR(80) NOT NULL,
  channel VARCHAR(40) NOT NULL,
  label VARCHAR(190) NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 0,
  config_json JSON NULL,
  last_checked_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_comm_in_source_key (source_key),
  KEY idx_comm_in_source_channel (channel, enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_hub_inbound_messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source_channel VARCHAR(40) NOT NULL,
  source_uid VARCHAR(190) NULL,
  source_from VARCHAR(190) NULL,
  source_to VARCHAR(190) NULL,
  subject VARCHAR(255) NULL,
  body MEDIUMTEXT NULL,
  raw_payload MEDIUMTEXT NULL,
  token_value VARCHAR(190) NULL,
  token_hash VARCHAR(128) NULL,
  detected_case_id BIGINT UNSIGNED NULL,
  detected_patient_id BIGINT UNSIGNED NULL,
  detected_thread_id BIGINT UNSIGNED NULL,
  hub_message_id BIGINT UNSIGNED NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'new',
  assignment_status VARCHAR(40) NOT NULL DEFAULT 'unassigned',
  task_id BIGINT UNSIGNED NULL,
  error_message TEXT NULL,
  received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  processed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_comm_in_source_uid (source_channel, source_uid),
  KEY idx_comm_in_status (status, assignment_status, received_at),
  KEY idx_comm_in_case (detected_case_id, received_at),
  KEY idx_comm_in_thread (detected_thread_id, received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_hub_inbound_tokens (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  token_hash VARCHAR(128) NOT NULL,
  raw_hint VARCHAR(80) NULL,
  message_id BIGINT UNSIGNED NULL,
  thread_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  purpose VARCHAR(80) NOT NULL DEFAULT 'reply',
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  expires_at DATETIME NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  used_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_comm_in_token (token_hash),
  KEY idx_comm_in_token_case (operative_case_id, patient_id, status),
  KEY idx_comm_in_token_thread (thread_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_hub_inbound_tasks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  inbound_message_id BIGINT UNSIGNED NULL,
  thread_id BIGINT UNSIGNED NULL,
  hub_message_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  task_type VARCHAR(80) NOT NULL DEFAULT 'inbound_reply',
  priority VARCHAR(40) NOT NULL DEFAULT 'normal',
  title VARCHAR(255) NOT NULL,
  body TEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  assigned_role VARCHAR(80) NOT NULL DEFAULT 'front_coordination',
  due_at DATETIME NULL,
  source_channel VARCHAR(40) NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_comm_in_task_status (status, priority, due_at),
  KEY idx_comm_in_task_case (operative_case_id, status),
  KEY idx_comm_in_task_thread (thread_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Columns added by upgrade_0_49_0.php / app self-healing:
-- communication_hub_messages.reply_token_hash
-- communication_hub_messages.reply_url
-- communication_hub_threads.inbound_unread_count
