-- Baltique OS Core 0.48.0 — Communication Hub

CREATE TABLE IF NOT EXISTS communication_hub_threads (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  thread_key VARCHAR(190) NOT NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  subject VARCHAR(255) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  priority VARCHAR(40) NOT NULL DEFAULT 'normal',
  last_message_at DATETIME NULL,
  last_channel VARCHAR(40) NULL,
  unread_count INT UNSIGNED NOT NULL DEFAULT 0,
  metadata_json JSON 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_comm_hub_thread_key (thread_key),
  KEY idx_comm_hub_case (operative_case_id, status),
  KEY idx_comm_hub_patient (patient_id, status),
  KEY idx_comm_hub_status (status, priority, last_message_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_hub_messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  thread_id BIGINT UNSIGNED NOT NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  source_table VARCHAR(80) NULL,
  source_id BIGINT UNSIGNED NULL,
  channel VARCHAR(40) NOT NULL DEFAULT 'internal',
  direction VARCHAR(40) NOT NULL DEFAULT 'internal',
  sender_label VARCHAR(190) NULL,
  recipient_label VARCHAR(190) NULL,
  recipient_email VARCHAR(190) NULL,
  recipient_phone VARCHAR(80) NULL,
  subject VARCHAR(255) NULL,
  body MEDIUMTEXT NOT NULL,
  priority VARCHAR(40) NOT NULL DEFAULT 'normal',
  status VARCHAR(40) NOT NULL DEFAULT 'draft',
  read_status VARCHAR(40) NOT NULL DEFAULT 'unread',
  read_token_hash VARCHAR(128) NULL,
  queue_table VARCHAR(80) NULL,
  queue_id BIGINT UNSIGNED NULL,
  sent_at DATETIME NULL,
  read_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_comm_hub_source (source_table, source_id),
  KEY idx_comm_hub_msg_thread (thread_id, created_at),
  KEY idx_comm_hub_msg_case (operative_case_id, channel, status),
  KEY idx_comm_hub_msg_patient (patient_id, created_at),
  KEY idx_comm_hub_msg_read_token (read_token_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_hub_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  thread_id BIGINT UNSIGNED NULL,
  message_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_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_comm_hub_events_thread (thread_id, created_at),
  KEY idx_comm_hub_events_message (message_id, created_at),
  KEY idx_comm_hub_events_case (operative_case_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_hub_read_receipts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  message_id BIGINT UNSIGNED NOT NULL,
  thread_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  read_token_hash VARCHAR(128) NULL,
  reader_label VARCHAR(190) NULL,
  ip_address VARCHAR(80) NULL,
  user_agent TEXT NULL,
  read_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_comm_hub_receipt_msg (message_id, read_at),
  KEY idx_comm_hub_receipt_case (operative_case_id, read_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS communication_hub_sync_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source_name VARCHAR(80) NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'ok',
  imported_count INT UNSIGNED NOT NULL DEFAULT 0,
  skipped_count INT UNSIGNED NOT NULL DEFAULT 0,
  error_message TEXT NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_comm_hub_sync_source (source_name, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
