CREATE TABLE IF NOT EXISTS daily_featured_analyses (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  analysis_date DATE NOT NULL,
  match_id BIGINT UNSIGNED NULL,
  api_fixture_id BIGINT UNSIGNED NULL,
  title VARCHAR(220) NOT NULL,
  description TEXT NOT NULL,
  confidence TINYINT UNSIGNED 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_daily_featured_date (analysis_date),
  KEY idx_daily_featured_match (match_id),
  KEY idx_daily_featured_api_fixture (api_fixture_id),
  CONSTRAINT fk_daily_featured_match FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;