-- Baltique OS Core 0.45.0 — TheatreFlow v2 Predictive Delays / Repair Suggestions

CREATE TABLE IF NOT EXISTS theatre_v2_delay_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id BIGINT UNSIGNED NOT NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  delay_minutes INT NOT NULL DEFAULT 0,
  event_type VARCHAR(80) NOT NULL DEFAULT 'manual_delay',
  reason VARCHAR(190) NULL,
  note TEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_tf2_delay_booking (booking_id, status, created_at),
  KEY idx_tf2_delay_case (operative_case_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theatre_v2_delay_predictions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  prediction_date DATE NOT NULL,
  booking_id BIGINT UNSIGNED NOT NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  planned_starts_at DATETIME NULL,
  planned_ends_at DATETIME NULL,
  predicted_starts_at DATETIME NULL,
  predicted_ends_at DATETIME NULL,
  delay_minutes INT NOT NULL DEFAULT 0,
  delay_source VARCHAR(120) NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'ok',
  impact_json JSON NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_tf2_pred_date_booking (prediction_date, booking_id),
  KEY idx_tf2_pred_severity (prediction_date, severity),
  KEY idx_tf2_pred_case (operative_case_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theatre_v2_repair_suggestions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  prediction_date DATE NOT NULL,
  booking_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  suggestion_type VARCHAR(80) NOT NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'info',
  title VARCHAR(255) NOT NULL,
  body TEXT NULL,
  proposed_action_json JSON NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  applied_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_tf2_sugg_date (prediction_date, status, severity),
  KEY idx_tf2_sugg_booking (booking_id),
  KEY idx_tf2_sugg_case (operative_case_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theatre_v2_prediction_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  prediction_date DATE NOT NULL,
  snapshot_json JSON NOT NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_tf2_pred_snapshot_date (prediction_date, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
