CREATE TABLE IF NOT EXISTS match_analysis_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  analysis_date DATE NOT NULL,
  window_start DATETIME NOT NULL,
  window_end DATETIME NOT NULL,
  match_id BIGINT UNSIGNED NULL,
  api_fixture_id BIGINT UNSIGNED NOT NULL,
  status ENUM('pending', 'generating', 'ready', 'failed', 'locked') NOT NULL DEFAULT 'pending',
  match_snapshot JSON NULL,
  payload JSON NULL,
  error_message TEXT NULL,
  generated_at DATETIME NULL,
  locked_at DATETIME 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_match_analysis_snapshot_fixture_date (analysis_date, api_fixture_id),
  KEY idx_match_analysis_snapshot_match (match_id),
  KEY idx_match_analysis_snapshot_status (status),
  KEY idx_match_analysis_snapshot_window (window_start, window_end),
  CONSTRAINT fk_match_analysis_snapshot_match FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;