-- ==============================================
-- Migration: Add H2H table and improve match details storage
-- Date: 2026-01-17
-- ==============================================

-- ==============================================
-- HEAD TO HEAD TABLE
-- Stores historical matches between two teams
-- ==============================================
CREATE TABLE IF NOT EXISTS head_to_head (
    id INT AUTO_INCREMENT PRIMARY KEY,
    team1_id INT NOT NULL,              -- Lower team ID (for consistent ordering)
    team2_id INT NOT NULL,              -- Higher team ID
    fixture_id INT NOT NULL,            -- Reference to the historical fixture
    match_date DATETIME NOT NULL,
    league_id INT,
    league_name VARCHAR(255),
    league_logo VARCHAR(500),
    home_team_id INT NOT NULL,
    away_team_id INT NOT NULL,
    home_team_name VARCHAR(255),
    away_team_name VARCHAR(255),
    home_team_logo VARCHAR(500),
    away_team_logo VARCHAR(500),
    goals_home INT DEFAULT 0,
    goals_away INT DEFAULT 0,
    status_short VARCHAR(10),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY unique_h2h_fixture (team1_id, team2_id, fixture_id),
    INDEX idx_teams (team1_id, team2_id),
    INDEX idx_fixture (fixture_id),
    INDEX idx_match_date (match_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================
-- MATCH DETAILS CACHE TABLE
-- Tracks when match details were last fetched
-- ==============================================
CREATE TABLE IF NOT EXISTS match_details_cache (
    fixture_id INT PRIMARY KEY,
    has_events TINYINT(1) DEFAULT 0,
    has_statistics TINYINT(1) DEFAULT 0,
    has_lineups TINYINT(1) DEFAULT 0,
    events_count INT DEFAULT 0,
    statistics_count INT DEFAULT 0,
    lineups_count INT DEFAULT 0,
    last_fetched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (fixture_id) REFERENCES fixtures(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================
-- ADD COACH INFO TO LINEUPS
-- ==============================================
ALTER TABLE fixture_lineups
ADD COLUMN coach_id INT DEFAULT NULL AFTER is_substitute,
ADD COLUMN coach_name VARCHAR(255) DEFAULT NULL AFTER coach_id,
ADD COLUMN coach_photo VARCHAR(500) DEFAULT NULL AFTER coach_name;

-- ==============================================
-- ADD PLAYER PHOTO TO LINEUPS
-- ==============================================
ALTER TABLE fixture_lineups
ADD COLUMN player_photo VARCHAR(500) DEFAULT NULL AFTER player_grid;

-- ==============================================
-- INDEXES FOR BETTER QUERY PERFORMANCE
-- ==============================================

-- Index for checking if match details exist
CREATE INDEX IF NOT EXISTS idx_events_fixture_count
ON fixture_events (fixture_id);

CREATE INDEX IF NOT EXISTS idx_lineups_fixture_team
ON fixture_lineups (fixture_id, team_id, is_substitute);

CREATE INDEX IF NOT EXISTS idx_stats_fixture_team
ON fixture_statistics (fixture_id, team_id);
