-- Baltique OS Core 0.41.0 — OS Unification / SITE Dedup / Multisite TheatreFlow

CREATE TABLE IF NOT EXISTS os_compatibility_reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_json JSON NOT NULL,
  action_summary_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_os_compat_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS theatreflow_unified_rooms (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source_kind VARCHAR(60) NOT NULL DEFAULT 'hospital_room',
  source_room_id BIGINT UNSIGNED NULL,
  source_site_id BIGINT UNSIGNED NULL,
  source_floor_id BIGINT UNSIGNED NULL,
  code VARCHAR(120) NOT NULL,
  name VARCHAR(190) NOT NULL,
  site_label VARCHAR(190) NULL,
  floor_label VARCHAR(190) NULL,
  room_type VARCHAR(80) NOT NULL DEFAULT 'or',
  capacity INT UNSIGNED NOT NULL DEFAULT 1,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  visible TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 100,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_tf_unified_room_code (code),
  KEY idx_tf_room_type (room_type, status, visible),
  KEY idx_tf_room_source (source_kind, source_room_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
