-- Baltique OS Core 0.43.0 — TheatreFlow v2 Planner / conflict engine

CREATE TABLE IF NOT EXISTS theatre_v2_conflicts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  conflict_type VARCHAR(80) NOT NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'warning',
  message TEXT NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  detected_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at DATETIME NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  metadata_json JSON NULL,
  PRIMARY KEY (id),
  KEY idx_tf2_conflict_booking (booking_id, status),
  KEY idx_tf2_conflict_case (operative_case_id, status),
  KEY idx_tf2_conflict_type (conflict_type, severity, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theatre_v2_planner_moves (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  booking_id BIGINT UNSIGNED NOT NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  from_room_id BIGINT UNSIGNED NULL,
  to_room_id BIGINT UNSIGNED NULL,
  old_starts_at DATETIME NULL,
  new_starts_at DATETIME NULL,
  old_ends_at DATETIME NULL,
  new_ends_at DATETIME NULL,
  conflict_count INT UNSIGNED NOT NULL DEFAULT 0,
  moved_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_tf2_moves_booking (booking_id, created_at),
  KEY idx_tf2_moves_case (operative_case_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theatre_v2_planner_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  planner_date DATE NOT NULL,
  view_mode VARCHAR(20) NOT NULL DEFAULT 'day',
  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_planner_snap_date (planner_date, view_mode, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- upgrade_0_43_0.php also adds optional columns to theatre_v2_bookings and theatre_v2_room_rules.
