-- Baltique OS Core 0.46.0 — Block Coordinator Copilot

CREATE TABLE IF NOT EXISTS theatre_v2_copilot_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_date DATE NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'draft',
  strategy_count INT UNSIGNED NOT NULL DEFAULT 0,
  best_strategy_id BIGINT UNSIGNED NULL,
  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_copilot_run_date (run_date, status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theatre_v2_copilot_strategies (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_id BIGINT UNSIGNED NOT NULL,
  run_date DATE NOT NULL,
  booking_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  strategy_type VARCHAR(80) NOT NULL,
  title VARCHAR(255) NOT NULL,
  body TEXT NULL,
  score DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  expected_delay_reduction_min INT NOT NULL DEFAULT 0,
  risk_after VARCHAR(40) NOT NULL DEFAULT 'unknown',
  implementation_cost VARCHAR(40) NOT NULL DEFAULT 'medium',
  proposed_action_json JSON NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'proposed',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_tf2_copilot_run (run_id, score),
  KEY idx_tf2_copilot_date (run_date, status, score),
  KEY idx_tf2_copilot_booking (booking_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theatre_v2_copilot_decisions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_id BIGINT UNSIGNED NULL,
  strategy_id BIGINT UNSIGNED NOT NULL,
  decision VARCHAR(40) NOT NULL DEFAULT 'accepted',
  decision_note TEXT NULL,
  applied_by_user_id BIGINT UNSIGNED NULL,
  applied_at DATETIME NULL,
  result_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_tf2_copilot_dec_strategy (strategy_id),
  KEY idx_tf2_copilot_dec_run (run_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
