-- Baltique OS Core 0.77.0 — OR Safe Auto-Apply + Digital Twin + Push/Readiness Hotfix
CREATE TABLE IF NOT EXISTS or_digital_twin_scenarios (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  simulation_date DATE NOT NULL,
  scenario_key VARCHAR(80) NOT NULL,
  scenario_name VARCHAR(190) NOT NULL,
  total_cases INT UNSIGNED NOT NULL DEFAULT 0,
  predicted_delay_minutes INT NOT NULL DEFAULT 0,
  predicted_overtime_minutes INT NOT NULL DEFAULT 0,
  predicted_pacu_conflicts INT NOT NULL DEFAULT 0,
  predicted_team_conflicts INT NOT NULL DEFAULT 0,
  predicted_pharmacy_conflicts INT NOT NULL DEFAULT 0,
  predicted_risk_score DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  predicted_satisfaction_score DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  predicted_cost_delta DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  optimizer_score DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  recommendation TEXT NULL,
  scenario_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id), KEY idx_or_dt_date_score(simulation_date, optimizer_score), KEY idx_or_dt_scenario(simulation_date, scenario_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS or_digital_twin_case_predictions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  scenario_id BIGINT UNSIGNED NOT NULL,
  simulation_date DATE NOT NULL,
  scenario_key VARCHAR(80) NOT NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  booking_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  patient_label VARCHAR(190) NULL,
  procedure_label VARCHAR(255) NULL,
  room_label VARCHAR(190) NULL,
  team_label VARCHAR(190) NULL,
  predicted_start_at DATETIME NULL,
  predicted_end_at DATETIME NULL,
  predicted_pacu_start_at DATETIME NULL,
  predicted_pacu_end_at DATETIME NULL,
  delay_minutes INT NOT NULL DEFAULT 0,
  risk_delta DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  satisfaction_delta DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  cost_delta DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  details_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id), KEY idx_or_dt_case_scenario(scenario_id, operative_case_id), KEY idx_or_dt_case_date(simulation_date, scenario_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS or_digital_twin_recommendations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  scenario_id BIGINT UNSIGNED NULL,
  simulation_date DATE NOT NULL,
  recommendation_type VARCHAR(80) NOT NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'info',
  title VARCHAR(255) NOT NULL,
  body TEXT NULL,
  baseline_value DECIMAL(12,2) NULL,
  recommended_value DECIMAL(12,2) NULL,
  impact_json JSON NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id), KEY idx_or_dt_rec_date(simulation_date, status, severity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS or_safe_apply_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  apply_date DATE NOT NULL,
  source_type VARCHAR(80) NOT NULL DEFAULT 'digital_twin',
  source_id BIGINT UNSIGNED NULL,
  scenario_id BIGINT UNSIGNED NULL,
  mode VARCHAR(40) NOT NULL DEFAULT 'dry_run',
  status VARCHAR(40) NOT NULL DEFAULT 'created',
  hard_blocker_count INT UNSIGNED NOT NULL DEFAULT 0,
  warning_count INT UNSIGNED NOT NULL DEFAULT 0,
  affected_cases INT UNSIGNED NOT NULL DEFAULT 0,
  summary TEXT NULL,
  preflight_json JSON NULL,
  plan_json JSON NULL,
  applied_json JSON NULL,
  rollback_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  applied_by_user_id BIGINT UNSIGNED NULL,
  applied_at DATETIME NULL,
  rolled_back_by_user_id BIGINT UNSIGNED NULL,
  rolled_back_at DATETIME NULL,
  PRIMARY KEY(id), KEY idx_or_safe_apply_date(apply_date,status,mode), KEY idx_or_safe_apply_scenario(scenario_id,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS or_safe_apply_actions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_id BIGINT UNSIGNED NOT NULL,
  action_type VARCHAR(80) NOT NULL,
  target_table VARCHAR(120) NULL,
  target_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  booking_id BIGINT UNSIGNED NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'planned',
  old_json JSON NULL,
  new_json JSON NULL,
  blocker_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  applied_at DATETIME NULL,
  rolled_back_at DATETIME NULL,
  PRIMARY KEY(id), KEY idx_or_safe_action_run(run_id,status), KEY idx_or_safe_action_case(operative_case_id,booking_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS or_safe_apply_locks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  lock_key VARCHAR(190) NOT NULL,
  run_id BIGINT UNSIGNED NULL,
  lock_type VARCHAR(80) NOT NULL,
  resource_key VARCHAR(190) NULL,
  locked_until DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id), UNIQUE KEY uq_or_safe_lock_key(lock_key), KEY idx_or_safe_lock_type(lock_type,resource_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS or_safe_apply_audit_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_id BIGINT UNSIGNED NULL,
  event_type VARCHAR(80) NOT NULL,
  body TEXT NULL,
  event_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id), KEY idx_or_safe_audit_run(run_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS or_mobile_push_subscriptions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NULL,
  role_slug VARCHAR(80) NULL,
  device_label VARCHAR(190) NULL,
  endpoint TEXT NULL,
  p256dh VARCHAR(255) NULL,
  auth_secret VARCHAR(255) NULL,
  user_agent TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  last_seen_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id), KEY idx_or_mobile_push_sub_user(user_id, active), KEY idx_or_mobile_push_sub_role(role_slug, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
