-- Baltique OS Core 0.47.0 — TheatreFlow v2 Communication Copilot

CREATE TABLE IF NOT EXISTS theatre_v2_comm_copilot_batches (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_date DATE NULL,
  strategy_id BIGINT UNSIGNED NULL,
  run_id BIGINT UNSIGNED NULL,
  booking_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  trigger_type VARCHAR(80) NOT NULL DEFAULT 'manual',
  status VARCHAR(40) NOT NULL DEFAULT 'draft',
  audience_count INT UNSIGNED NOT NULL DEFAULT 0,
  message_count INT UNSIGNED NOT NULL DEFAULT 0,
  summary_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),
  KEY idx_tf2_comm_batch_date (run_date, status, created_at),
  KEY idx_tf2_comm_batch_strategy (strategy_id),
  KEY idx_tf2_comm_batch_case (operative_case_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theatre_v2_comm_copilot_messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  batch_id BIGINT UNSIGNED NOT NULL,
  strategy_id BIGINT UNSIGNED NULL,
  booking_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  audience VARCHAR(80) NOT NULL,
  channel VARCHAR(40) NOT NULL DEFAULT 'email',
  recipient_label VARCHAR(190) NULL,
  recipient_email VARCHAR(190) NULL,
  recipient_phone VARCHAR(80) NULL,
  subject VARCHAR(255) NOT NULL,
  body MEDIUMTEXT NOT NULL,
  priority VARCHAR(40) NOT NULL DEFAULT 'normal',
  status VARCHAR(40) NOT NULL DEFAULT 'draft',
  queue_table VARCHAR(80) NULL,
  queue_id BIGINT UNSIGNED NULL,
  approved_by_user_id BIGINT UNSIGNED NULL,
  approved_at DATETIME NULL,
  queued_at DATETIME NULL,
  sent_at DATETIME NULL,
  error_message TEXT 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_tf2_comm_msg_batch (batch_id, status),
  KEY idx_tf2_comm_msg_case (operative_case_id, audience),
  KEY idx_tf2_comm_msg_patient (patient_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theatre_v2_comm_copilot_templates (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  audience VARCHAR(80) NOT NULL,
  strategy_type VARCHAR(80) NOT NULL DEFAULT '*',
  channel VARCHAR(40) NOT NULL DEFAULT 'email',
  subject_template VARCHAR(255) NOT NULL,
  body_template MEDIUMTEXT NOT 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_tf2_comm_template (audience, strategy_type, channel),
  KEY idx_tf2_comm_template_active (active, audience)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
