-- Baltique OS Core 0.75.0 — OR Digital Twin Optimizer + Push Safe Registration Hotfix

CREATE TABLE IF NOT EXISTS or_digital_twin_scenarios (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  scenario_date DATE NOT NULL,
  scenario_code VARCHAR(120) NOT NULL,
  scenario_name VARCHAR(255) NOT NULL,
  scenario_type VARCHAR(80) NOT NULL DEFAULT 'custom',
  baseline_json JSON NULL,
  scenario_json JSON NULL,
  result_json JSON 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 UNSIGNED NOT NULL DEFAULT 0,
  predicted_team_conflicts INT UNSIGNED NOT NULL DEFAULT 0,
  predicted_pharmacy_conflicts INT UNSIGNED NOT NULL DEFAULT 0,
  predicted_risk_score DECIMAL(10,2) NULL,
  predicted_satisfaction_score DECIMAL(10,2) NULL,
  predicted_cost_delta DECIMAL(12,2) NULL,
  optimizer_score DECIMAL(10,2) NULL,
  recommendation TEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'draft',
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_or_dt_scenario_code (scenario_code),
  KEY idx_or_dt_scenario_date (scenario_date, scenario_type, optimizer_score)
) 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,
  scenario_date DATE NOT NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  booking_id BIGINT UNSIGNED NULL,
  patient_label VARCHAR(190) NULL,
  procedure_label VARCHAR(255) NULL,
  room_label VARCHAR(190) NULL,
  team_label VARCHAR(190) NULL,
  planned_start_at DATETIME NULL,
  predicted_start_at DATETIME NULL,
  predicted_end_at DATETIME NULL,
  predicted_delay_minutes INT NOT NULL DEFAULT 0,
  risk_score DECIMAL(10,2) NULL,
  satisfaction_score DECIMAL(10,2) NULL,
  flags_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_or_dt_case_scenario (scenario_id, predicted_start_at),
  KEY idx_or_dt_case_date (scenario_date, operative_case_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_digital_twin_constraints (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  constraint_code VARCHAR(120) NOT NULL,
  constraint_name VARCHAR(255) NOT NULL,
  constraint_type VARCHAR(80) NOT NULL,
  weight DECIMAL(10,2) NOT NULL DEFAULT 1.00,
  active TINYINT(1) NOT NULL DEFAULT 1,
  settings_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_or_dt_constraint_code (constraint_code),
  KEY idx_or_dt_constraint_type (constraint_type, active)
) 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,
  scenario_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,
  expected_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 (scenario_date, status, severity)
) 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;
