-- SportsPulse History & Archive Tables Migration
-- Adds historical tracking for matches, standings, and player statistics
-- Created: 2026-03-19
-- Migration Version: 1.0

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================
-- RAW API ARCHIVE
-- ============================================

-- Store raw API responses for debugging, replay, and audit
CREATE TABLE IF NOT EXISTS `api_raw_archive` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `provider` VARCHAR(50) NOT NULL,
    `endpoint` VARCHAR(255) NOT NULL,
    `request_method` VARCHAR(20) NOT NULL DEFAULT 'GET',
    `request_params` JSON DEFAULT NULL,
    `request_headers` JSON DEFAULT NULL,
    `response_json` LONGTEXT NOT NULL,
    `status_code` INT NOT NULL,
    `response_size_bytes` INT UNSIGNED DEFAULT NULL,
    `execution_time_ms` INT UNSIGNED DEFAULT NULL,
    `fetched_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `processed` TINYINT(1) NOT NULL DEFAULT 0,
    `notes` VARCHAR(500) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_api_raw_provider` (`provider`, `endpoint`),
    KEY `idx_api_raw_fetched` (`fetched_at`),
    KEY `idx_api_raw_processed` (`processed`),
    KEY `idx_api_raw_endpoint` (`endpoint`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- MATCH HISTORY TRACKING
-- ============================================

-- Track match score changes over time (snapshots)
CREATE TABLE IF NOT EXISTS `match_score_snapshots` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `match_id` INT UNSIGNED NOT NULL,
    `home_score` INT DEFAULT NULL,
    `away_score` INT DEFAULT NULL,
    `home_ht_score` INT DEFAULT NULL,
    `away_ht_score` INT DEFAULT NULL,
    `home_ft_score` INT DEFAULT NULL,
    `away_ft_score` INT DEFAULT NULL,
    `status` VARCHAR(50) NOT NULL,
    `minute` VARCHAR(10) DEFAULT NULL,
    `snapshot_reason` VARCHAR(50) NOT NULL DEFAULT 'update',
    `snapshot_source` VARCHAR(50) NOT NULL DEFAULT 'sync',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_match_snapshots_match` (`match_id`),
    KEY `idx_match_snapshots_created` (`created_at`),
    CONSTRAINT `fk_match_snapshots_match` FOREIGN KEY (`match_id`) REFERENCES `matches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Track match events with immutable history
CREATE TABLE IF NOT EXISTS `match_events_history` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `match_event_id` INT UNSIGNED NOT NULL,
    `match_id` INT UNSIGNED NOT NULL,
    `event_type` VARCHAR(50) NOT NULL,
    `minute` INT NOT NULL,
    `extra_minute` INT DEFAULT NULL,
    `player_name` VARCHAR(200) DEFAULT NULL,
    `team_name` VARCHAR(200) DEFAULT NULL,
    `detail` VARCHAR(100) DEFAULT NULL,
    `comments` TEXT DEFAULT NULL,
    `archived_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `reason` VARCHAR(50) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_match_events_hist_match` (`match_id`),
    KEY `idx_match_events_hist_event` (`match_event_id`),
    KEY `idx_match_events_hist_archived` (`archived_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- STANDINGS HISTORY TRACKING
-- ============================================

-- Historical standings snapshots by date
CREATE TABLE IF NOT EXISTS `standings_snapshots` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `league_id` INT UNSIGNED NOT NULL,
    `season_id` INT UNSIGNED NOT NULL,
    `snapshot_date` DATE NOT NULL,
    `team_id` INT UNSIGNED NOT NULL,
    `group_name` VARCHAR(50) DEFAULT NULL,
    `position` INT NOT NULL,
    `played` INT NOT NULL DEFAULT 0,
    `won` INT NOT NULL DEFAULT 0,
    `drawn` INT NOT NULL DEFAULT 0,
    `lost` INT NOT NULL DEFAULT 0,
    `goals_for` INT NOT NULL DEFAULT 0,
    `goals_against` INT NOT NULL DEFAULT 0,
    `goal_diff` INT NOT NULL DEFAULT 0,
    `points` INT NOT NULL DEFAULT 0,
    `form` VARCHAR(20) DEFAULT NULL,
    `description` VARCHAR(100) DEFAULT NULL,
    `snapshot_created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_standings_snapshots` (`league_id`, `season_id`, `snapshot_date`, `team_id`, `group_name`),
    KEY `idx_standings_snapshots_date` (`snapshot_date`),
    KEY `idx_standings_snapshots_team` (`team_id`),
    KEY `idx_standings_snapshots_position` (`position`),
    CONSTRAINT `fk_standings_snapshots_league` FOREIGN KEY (`league_id`) REFERENCES `leagues` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_standings_snapshots_season` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_standings_snapshots_team` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- PLAYER STATISTICS HISTORY
-- ============================================

-- Historical player statistics snapshots
CREATE TABLE IF NOT EXISTS `player_statistics_snapshots` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `player_id` INT UNSIGNED NOT NULL,
    `team_id` INT UNSIGNED DEFAULT NULL,
    `league_id` INT UNSIGNED DEFAULT NULL,
    `season_id` INT UNSIGNED DEFAULT NULL,
    `snapshot_date` DATE NOT NULL,
    `appearances` INT NOT NULL DEFAULT 0,
    `goals` INT NOT NULL DEFAULT 0,
    `assists` INT NOT NULL DEFAULT 0,
    `yellow_cards` INT NOT NULL DEFAULT 0,
    `red_cards` INT NOT NULL DEFAULT 0,
    `minutes_played` INT NOT NULL DEFAULT 0,
    `rating` DECIMAL(3,1) DEFAULT NULL,
    `penalties_scored` INT NOT NULL DEFAULT 0,
    `additional_stats` JSON DEFAULT NULL,
    `snapshot_created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_player_stats_snapshots` (`player_id`, `snapshot_date`, `league_id`),
    KEY `idx_player_stats_snapshots_player` (`player_id`),
    KEY `idx_player_stats_snapshots_date` (`snapshot_date`),
    KEY `idx_player_stats_snapshots_goals` (`goals`),
    CONSTRAINT `fk_player_stats_snapshots_player` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_player_stats_snapshots_team` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_player_stats_snapshots_league` FOREIGN KEY (`league_id`) REFERENCES `leagues` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_player_stats_snapshots_season` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TOP SCORERS HISTORY
-- ============================================

-- Historical top scorers tracking
CREATE TABLE IF NOT EXISTS `top_scorers_snapshots` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `league_id` INT UNSIGNED NOT NULL,
    `season_id` INT UNSIGNED NOT NULL,
    `snapshot_date` DATE NOT NULL,
    `player_id` INT UNSIGNED NOT NULL,
    `team_id` INT UNSIGNED DEFAULT NULL,
    `goals` INT NOT NULL DEFAULT 0,
    `assists` INT NOT NULL DEFAULT 0,
    `penalties` INT NOT NULL DEFAULT 0,
    `appearances` INT NOT NULL DEFAULT 0,
    `position` INT NOT NULL DEFAULT 1,
    `snapshot_created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_top_scorers_snapshots` (`league_id`, `season_id`, `snapshot_date`, `player_id`),
    KEY `idx_top_scorers_snapshots_date` (`snapshot_date`),
    KEY `idx_top_scorers_snapshots_goals` (`goals` DESC),
    CONSTRAINT `fk_top_scorers_snapshots_league` FOREIGN KEY (`league_id`) REFERENCES `leagues` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_top_scorers_snapshots_season` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_top_scorers_snapshots_player` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_top_scorers_snapshots_team` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- SEARCH INDEX UPDATES
-- ============================================

-- Log search index updates for monitoring
CREATE TABLE IF NOT EXISTS `search_index_log` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `entity_type` VARCHAR(50) NOT NULL,
    `entity_id` INT UNSIGNED NOT NULL,
    `action` ENUM('created', 'updated', 'deleted') NOT NULL,
    `old_title` VARCHAR(255) DEFAULT NULL,
    `new_title` VARCHAR(255) DEFAULT NULL,
    `popularity_change` INT DEFAULT 0,
    `changed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_search_log_entity` (`entity_type`, `entity_id`),
    KEY `idx_search_log_changed` (`changed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- ASSET DOWNLOAD HISTORY
-- ============================================

-- Track asset download attempts to avoid duplicates
CREATE TABLE IF NOT EXISTS `asset_download_log` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `asset_id` INT UNSIGNED DEFAULT NULL,
    `entity_type` VARCHAR(50) NOT NULL,
    `entity_id` INT UNSIGNED NOT NULL,
    `asset_type` VARCHAR(50) NOT NULL,
    `remote_url` VARCHAR(500) NOT NULL,
    `local_path` VARCHAR(255) DEFAULT NULL,
    `download_status` ENUM('success', 'failed', 'skipped', 'duplicate') NOT NULL,
    `file_size_bytes` INT UNSIGNED DEFAULT NULL,
    `error_message` VARCHAR(500) DEFAULT NULL,
    `downloaded_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_asset_log_entity` (`entity_type`, `entity_id`, `asset_type`),
    KEY `idx_asset_log_status` (`download_status`),
    KEY `idx_asset_log_downloaded` (`downloaded_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================
-- MIGRATION NOTES
-- ============================================

/*
This migration adds comprehensive history tracking to SportsPulse:

1. api_raw_archive
   - Stores all raw API responses
   - Enables debugging, replay, and audit trails
   - Tracks execution time and response size

2. match_score_snapshots
   - Captures score changes during live matches
   - Tracks reason for snapshot (goal, card, update, etc.)
   - Immutable history of match progression

3. match_events_history
   - Archives match events when updated/deleted
   - Preserves original event data
   - Useful for correcting errors

4. standings_snapshots
   - Daily/weekly standings position tracking
   - Shows team progression through season
   - Enables "on this day" features

5. player_statistics_snapshots
   - Track player performance over time
   - Season-by-season statistics
   - Form tracking

6. top_scorers_snapshots
   - Historical top scorer rankings
   - Race tracking throughout season

7. search_index_log
   - Monitor search index changes
   - Track popularity trends
   - Debug search issues

8. asset_download_log
   - Prevent duplicate downloads
   - Track download failures
   - Monitor storage usage

USAGE PATTERNS:

- Before updating a match, create a snapshot
- Before replacing standings, archive current state
- After fetching from API, store raw response
- On entity update, log search index changes
- Before downloading asset, check download log

*/