-- Baltique OS Core 0.69.0 — OR Live Command Wall

CREATE TABLE IF NOT EXISTS or_live_stage_definitions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  stage_code VARCHAR(80) NOT NULL,
  stage_label VARCHAR(190) NOT NULL,
  sort_order INT NOT NULL DEFAULT 100,
  color_class VARCHAR(40) NOT NULL DEFAULT 'neutral',
  target_room_role VARCHAR(80) 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_live_stage_code(stage_code),
  KEY idx_or_live_stage_active(active, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_live_patient_state (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  live_date DATE NOT NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  booking_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  current_stage VARCHAR(80) NOT NULL DEFAULT 'planned',
  current_room_id BIGINT UNSIGNED NULL,
  current_room_label VARCHAR(190) NULL,
  gate_status VARCHAR(40) NOT NULL DEFAULT 'UNKNOWN',
  readiness_status VARCHAR(40) NOT NULL DEFAULT 'UNKNOWN',
  delay_minutes INT NOT NULL DEFAULT 0,
  delay_status VARCHAR(40) NOT NULL DEFAULT 'on_time',
  live_alert_count INT UNSIGNED NOT NULL DEFAULT 0,
  communication_status VARCHAR(40) NOT NULL DEFAULT 'unknown',
  last_event_at DATETIME NULL,
  stage_started_at DATETIME NULL,
  expected_next_at DATETIME NULL,
  note TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_or_live_case_day(live_date, operative_case_id, booking_id),
  KEY idx_or_live_stage(live_date, current_stage),
  KEY idx_or_live_room(live_date, current_room_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_live_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  live_date DATE NOT NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  booking_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  event_type VARCHAR(80) NOT NULL,
  from_stage VARCHAR(80) NULL,
  to_stage VARCHAR(80) NULL,
  from_room_id BIGINT UNSIGNED NULL,
  to_room_id BIGINT UNSIGNED NULL,
  event_body TEXT NULL,
  metadata_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_or_live_event_day(live_date, created_at),
  KEY idx_or_live_event_case(operative_case_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_live_alerts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  live_date DATE NOT NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  booking_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  alert_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_live_alert_day(live_date, status, severity),
  KEY idx_or_live_alert_case(operative_case_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_live_room_status (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  live_date DATE NOT NULL,
  room_id BIGINT UNSIGNED NULL,
  room_code VARCHAR(120) NULL,
  room_label VARCHAR(190) NOT NULL,
  room_role VARCHAR(80) NOT NULL DEFAULT 'support',
  occupancy_status VARCHAR(40) NOT NULL DEFAULT 'free',
  current_case_id BIGINT UNSIGNED NULL,
  current_patient_id BIGINT UNSIGNED NULL,
  status_json JSON NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  UNIQUE KEY uq_or_live_room_day(live_date, room_code, room_label),
  KEY idx_or_live_room_role(live_date, room_role, occupancy_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_live_command_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  live_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_live_snap_day(live_date, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_live_reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_date DATE NOT NULL,
  total_cases INT UNSIGNED NOT NULL DEFAULT 0,
  active_cases INT UNSIGNED NOT NULL DEFAULT 0,
  completed_cases INT UNSIGNED NOT NULL DEFAULT 0,
  delayed_cases INT UNSIGNED NOT NULL DEFAULT 0,
  alert_count INT UNSIGNED NOT NULL DEFAULT 0,
  critical_alert_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_live_report_date(report_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
