CREATE TABLE IF NOT EXISTS neo_recommendation_history (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  recommendation_key VARCHAR(190) NOT NULL,
  match_id BIGINT UNSIGNED NULL,
  api_fixture_id BIGINT UNSIGNED NULL,
  match_label VARCHAR(190) NOT NULL,
  recommendation_type VARCHAR(120) NULL,
  title VARCHAR(190) NOT NULL,
  explanation TEXT NULL,
  confidence TINYINT UNSIGNED NULL,
  estimated_probability DECIMAL(5,2) NULL,
  odds DECIMAL(5,2) NULL,
  bookmaker VARCHAR(120) NULL,
  market VARCHAR(120) NULL,
  market_value VARCHAR(120) NULL,
  risk_level VARCHAR(40) NULL,
  result_status ENUM('pending', 'validated', 'lost', 'void', 'review_required') NOT NULL DEFAULT 'pending',
  result_checked_at DATETIME NULL,
  payload JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_neo_recommendation_key (recommendation_key),
  KEY idx_neo_recommendation_fixture (api_fixture_id),
  KEY idx_neo_recommendation_status (result_status),
  KEY idx_neo_recommendation_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;