-- Baltique OS Core 0.68.0 — OR Readiness Control Tower
CREATE TABLE IF NOT EXISTS or_readiness_daily_status (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  readiness_date DATE NOT NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  booking_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  procedure_id BIGINT UNSIGNED NULL,
  site_id BIGINT UNSIGNED NULL,
  room_id BIGINT UNSIGNED NULL,
  room_label VARCHAR(190) NULL,
  team_label VARCHAR(190) NULL,
  bed_label VARCHAR(190) NULL,
  patient_label VARCHAR(190) NULL,
  procedure_label VARCHAR(255) NULL,
  start_at DATETIME NULL,
  end_at DATETIME NULL,
  gate_status VARCHAR(40) NOT NULL DEFAULT 'UNKNOWN',
  readiness_status VARCHAR(40) NOT NULL DEFAULT 'UNKNOWN',
  labs_status VARCHAR(40) NOT NULL DEFAULT 'unknown',
  implants_status VARCHAR(40) NOT NULL DEFAULT 'unknown',
  documentation_status VARCHAR(40) NOT NULL DEFAULT 'unknown',
  payments_status VARCHAR(40) NOT NULL DEFAULT 'unknown',
  pacu_status VARCHAR(40) NOT NULL DEFAULT 'unknown',
  communication_status VARCHAR(40) NOT NULL DEFAULT 'unknown',
  blocker_count INT UNSIGNED NOT NULL DEFAULT 0,
  warning_count INT UNSIGNED NOT NULL DEFAULT 0,
  summary_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_or_ready_date_case_booking (readiness_date, operative_case_id, booking_id),
  KEY idx_or_ready_day_status (readiness_date, readiness_status),
  KEY idx_or_ready_room (readiness_date, room_id, start_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS or_readiness_gap_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  readiness_date DATE NOT NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  booking_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  domain VARCHAR(80) NOT NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'warning',
  title VARCHAR(255) NOT NULL,
  body TEXT NULL,
  responsible_role VARCHAR(80) NULL,
  recommended_action TEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  source_table VARCHAR(80) NULL,
  source_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_or_gap_day (readiness_date, status, severity),
  KEY idx_or_gap_case (operative_case_id, status),
  KEY idx_or_gap_domain (domain, severity, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS or_readiness_room_catalog (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_key VARCHAR(120) NOT NULL,
  floor_key VARCHAR(80) NULL,
  room_code VARCHAR(120) NOT NULL,
  room_label VARCHAR(190) NOT NULL,
  room_type VARCHAR(80) NOT NULL DEFAULT 'other',
  readiness_role VARCHAR(80) NOT NULL DEFAULT 'support',
  capacity INT UNSIGNED NOT NULL DEFAULT 1,
  source_label VARCHAR(190) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_or_room_catalog (site_key, floor_key, room_code),
  KEY idx_or_room_role (readiness_role, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS or_readiness_day_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  readiness_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_or_snap_day (readiness_date, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS or_readiness_reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_date DATE NOT NULL,
  total_cases INT UNSIGNED NOT NULL DEFAULT 0,
  go_count INT UNSIGNED NOT NULL DEFAULT 0,
  hold_count INT UNSIGNED NOT NULL DEFAULT 0,
  no_go_count INT UNSIGNED NOT NULL DEFAULT 0,
  blocker_count INT UNSIGNED NOT NULL DEFAULT 0,
  warning_count INT UNSIGNED NOT NULL DEFAULT 0,
  report_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  UNIQUE KEY uq_or_report_date (report_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
