-- Baltique OS Core 0.76.0 — OR Autopilot Recommendations Board
CREATE TABLE IF NOT EXISTS or_autopilot_recommendations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  recommendation_key VARCHAR(190) NOT NULL,
  recommendation_date DATE NOT NULL,
  source_type VARCHAR(80) NOT NULL DEFAULT 'digital_twin',
  source_scenario_id BIGINT UNSIGNED NULL,
  baseline_scenario_id BIGINT UNSIGNED NULL,
  recommendation_type VARCHAR(80) NOT NULL DEFAULT 'schedule_change',
  title VARCHAR(255) NOT NULL,
  rationale TEXT NULL,
  baseline_variant VARCHAR(190) NULL,
  recommended_variant VARCHAR(190) NULL,
  baseline_json JSON NULL,
  recommended_json JSON NULL,
  comparison_json JSON NULL,
  patient_impact_json JSON NULL,
  risk_json JSON NULL,
  predicted_delay_delta INT NULL,
  predicted_overtime_delta INT NULL,
  predicted_pacu_conflict_delta INT NULL,
  predicted_team_conflict_delta INT NULL,
  predicted_pharmacy_conflict_delta INT NULL,
  predicted_risk_delta DECIMAL(10,2) NULL,
  predicted_cost_delta DECIMAL(12,2) NULL,
  predicted_satisfaction_delta DECIMAL(10,2) NULL,
  autopilot_score DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  decision_note TEXT NULL,
  decided_by_user_id BIGINT UNSIGNED NULL,
  decided_at DATETIME NULL,
  implemented_by_user_id BIGINT UNSIGNED NULL,
  implemented_at DATETIME NULL,
  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_autopilot_key(recommendation_key),
  KEY idx_or_autopilot_date_status(recommendation_date,status,autopilot_score),
  KEY idx_or_autopilot_source(source_type,source_scenario_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS or_autopilot_decisions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  recommendation_id BIGINT UNSIGNED NOT NULL,
  decision VARCHAR(40) NOT NULL,
  decision_note TEXT NULL,
  previous_status VARCHAR(40) NULL,
  new_status VARCHAR(40) NULL,
  snapshot_json JSON NULL,
  decided_by_user_id BIGINT UNSIGNED NULL,
  decided_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_or_autopilot_decision_rec(recommendation_id,decided_at),
  KEY idx_or_autopilot_decision(decision,decided_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS or_autopilot_implementation_actions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  recommendation_id BIGINT UNSIGNED NOT NULL,
  action_type VARCHAR(80) NOT NULL DEFAULT 'manual_plan_update',
  action_title VARCHAR(255) NOT NULL,
  action_body TEXT NULL,
  target_table VARCHAR(120) NULL,
  target_id BIGINT UNSIGNED NULL,
  action_status VARCHAR(40) NOT NULL DEFAULT 'planned',
  before_json JSON NULL,
  after_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_or_autopilot_impl_rec(recommendation_id,action_status),
  KEY idx_or_autopilot_impl_target(target_table,target_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS or_autopilot_board_reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_date DATE NOT NULL,
  total_recommendations INT UNSIGNED NOT NULL DEFAULT 0,
  open_count INT UNSIGNED NOT NULL DEFAULT 0,
  accepted_count INT UNSIGNED NOT NULL DEFAULT 0,
  rejected_count INT UNSIGNED NOT NULL DEFAULT 0,
  implemented_count INT UNSIGNED NOT NULL DEFAULT 0,
  projected_delay_saving INT NULL,
  projected_cost_delta DECIMAL(12,2) NULL,
  projected_risk_delta DECIMAL(10,2) NULL,
  report_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_or_autopilot_report_date(report_date,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
