-- Baltique OS Core 0.45.1 — TheatreFlow v2 Day Simulation

CREATE TABLE IF NOT EXISTS theatre_v2_simulation_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  sim_date DATE NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'paused',
  `current_time` DATETIME NOT NULL,
  tick_minutes INT UNSIGNED NOT NULL DEFAULT 15,
  speed_seconds DECIMAL(8,2) NOT NULL DEFAULT 2.00,
  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_sim_run_date (sim_date),
  KEY idx_sim_run_status (status, sim_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theatre_v2_simulation_entities (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_id BIGINT UNSIGNED NOT NULL,
  entity_key VARCHAR(190) NOT NULL,
  entity_type VARCHAR(50) NOT NULL,
  label VARCHAR(190) NOT NULL,
  subtitle VARCHAR(190) NULL,
  booking_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  team_id BIGINT UNSIGNED NULL,
  bed_id BIGINT UNSIGNED NULL,
  theatre_room_id BIGINT UNSIGNED NULL,
  hospital_room_id BIGINT UNSIGNED NULL,
  site_id BIGINT UNSIGNED NULL,
  floor_id BIGINT UNSIGNED NULL,
  x DECIMAL(10,2) NOT NULL DEFAULT 100.00,
  y DECIMAL(10,2) NOT NULL DEFAULT 100.00,
  status VARCHAR(40) NOT NULL DEFAULT 'waiting',
  payload_json JSON NULL,
  last_event_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_sim_entity_key (run_id, entity_key),
  KEY idx_sim_entity_type (run_id, entity_type, status),
  KEY idx_sim_entity_case (operative_case_id),
  KEY idx_sim_entity_floor (floor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theatre_v2_simulation_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_id BIGINT UNSIGNED NOT NULL,
  scheduled_at DATETIME NOT NULL,
  executed_at DATETIME NULL,
  booking_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  entity_key VARCHAR(190) NOT NULL,
  entity_type VARCHAR(50) NOT NULL,
  event_code VARCHAR(90) NOT NULL,
  event_label VARCHAR(255) NOT NULL,
  from_room_id BIGINT UNSIGNED NULL,
  to_room_id BIGINT UNSIGNED NULL,
  target_room_type VARCHAR(80) NULL,
  target_status VARCHAR(60) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'pending',
  metadata_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_sim_event_run_time (run_id, scheduled_at, status),
  KEY idx_sim_event_case (operative_case_id, scheduled_at),
  KEY idx_sim_event_entity (run_id, entity_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theatre_v2_simulation_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_id BIGINT UNSIGNED NULL,
  log_type VARCHAR(80) NOT NULL,
  message TEXT NOT NULL,
  payload_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_sim_log_run (run_id, created_at),
  KEY idx_sim_log_type (log_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theatre_v2_simulation_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_id BIGINT UNSIGNED NOT NULL,
  sim_date DATE NOT NULL,
  `current_time` DATETIME 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_sim_snap_run (run_id, created_at),
  KEY idx_sim_snap_date (sim_date, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- upgrade_0_44_0.php also adds optional simulation columns to hospital_live_entities and theatre_v2_bookings.
