-- ==============================================
-- Migration 004: Add Predictions, Injuries, Top Scorers, Coaches
-- ==============================================

-- ==============================================
-- PREDICTIONS TABLE (AI Match Predictions)
-- ==============================================
CREATE TABLE IF NOT EXISTS match_predictions (
    fixture_id INT PRIMARY KEY,
    winner_id INT DEFAULT NULL,              -- Predicted winner team ID (null = draw)
    winner_name VARCHAR(255),
    winner_comment VARCHAR(500),             -- e.g., "Win or Draw"

    -- Win Probabilities (percentages)
    percent_home DECIMAL(5,2),               -- e.g., 45.50
    percent_draw DECIMAL(5,2),               -- e.g., 30.00
    percent_away DECIMAL(5,2),               -- e.g., 24.50

    -- Advice
    advice VARCHAR(500),                      -- e.g., "Double chance : Manchester United or draw"

    -- Goals Predictions
    goals_home VARCHAR(10),                  -- e.g., "-2.5" or "1.5"
    goals_away VARCHAR(10),

    -- Team Comparison Stats (percentage based)
    comparison_form_home DECIMAL(5,2),
    comparison_form_away DECIMAL(5,2),
    comparison_att_home DECIMAL(5,2),
    comparison_att_away DECIMAL(5,2),
    comparison_def_home DECIMAL(5,2),
    comparison_def_away DECIMAL(5,2),
    comparison_h2h_home DECIMAL(5,2),
    comparison_h2h_away DECIMAL(5,2),
    comparison_poisson_home DECIMAL(5,2),
    comparison_poisson_away DECIMAL(5,2),
    comparison_total_home DECIMAL(5,2),
    comparison_total_away DECIMAL(5,2),

    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;

-- ==============================================
-- INJURIES TABLE (Player Injuries/Suspensions)
-- ==============================================
CREATE TABLE IF NOT EXISTS match_injuries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fixture_id INT NOT NULL,
    team_id INT NOT NULL,
    player_id INT NOT NULL,
    player_name VARCHAR(255) NOT NULL,
    player_photo VARCHAR(500),
    player_type VARCHAR(50),                 -- "Missing Fixture" or "Questionable"
    reason VARCHAR(255),                     -- "Injury", "Suspended", "International Duty"
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_fixture (fixture_id),
    INDEX idx_team (team_id),
    UNIQUE KEY unique_injury (fixture_id, player_id),

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

-- ==============================================
-- TOP SCORERS TABLE (League Top Scorers)
-- ==============================================
CREATE TABLE IF NOT EXISTS league_top_scorers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    league_id INT NOT NULL,
    season INT NOT NULL,
    rank_position INT NOT NULL,

    -- Player Info
    player_id INT NOT NULL,
    player_name VARCHAR(255) NOT NULL,
    player_firstname VARCHAR(100),
    player_lastname VARCHAR(100),
    player_photo VARCHAR(500),
    player_nationality VARCHAR(100),
    player_age INT,

    -- Team Info
    team_id INT NOT NULL,
    team_name VARCHAR(255),
    team_logo VARCHAR(500),

    -- Stats
    appearances INT DEFAULT 0,               -- Games played
    minutes_played INT DEFAULT 0,
    goals INT DEFAULT 0,
    assists INT DEFAULT 0,
    penalties_scored INT DEFAULT 0,
    penalties_missed INT DEFAULT 0,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_league_season (league_id, season),
    INDEX idx_goals (goals DESC),
    UNIQUE KEY unique_scorer (league_id, season, player_id),

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

-- ==============================================
-- TOP ASSISTS TABLE (League Top Assist Providers)
-- ==============================================
CREATE TABLE IF NOT EXISTS league_top_assists (
    id INT AUTO_INCREMENT PRIMARY KEY,
    league_id INT NOT NULL,
    season INT NOT NULL,
    rank_position INT NOT NULL,

    -- Player Info
    player_id INT NOT NULL,
    player_name VARCHAR(255) NOT NULL,
    player_firstname VARCHAR(100),
    player_lastname VARCHAR(100),
    player_photo VARCHAR(500),
    player_nationality VARCHAR(100),
    player_age INT,

    -- Team Info
    team_id INT NOT NULL,
    team_name VARCHAR(255),
    team_logo VARCHAR(500),

    -- Stats
    appearances INT DEFAULT 0,
    minutes_played INT DEFAULT 0,
    goals INT DEFAULT 0,
    assists INT DEFAULT 0,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_league_season (league_id, season),
    INDEX idx_assists (assists DESC),
    UNIQUE KEY unique_assist (league_id, season, player_id),

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

-- ==============================================
-- COACHES TABLE (Team Coaches)
-- ==============================================
CREATE TABLE IF NOT EXISTS coaches (
    id INT PRIMARY KEY,                      -- API Coach ID
    name VARCHAR(255) NOT NULL,
    firstname VARCHAR(100),
    lastname VARCHAR(100),
    photo VARCHAR(500),
    nationality VARCHAR(100),
    age INT,
    birth_date DATE,
    birth_place VARCHAR(100),
    birth_country VARCHAR(100),

    -- Current Team (can be null)
    team_id INT DEFAULT NULL,
    team_name VARCHAR(255),

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_team (team_id),
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================
-- COACH CAREER HISTORY TABLE
-- ==============================================
CREATE TABLE IF NOT EXISTS coach_career (
    id INT AUTO_INCREMENT PRIMARY KEY,
    coach_id INT NOT NULL,
    team_id INT NOT NULL,
    team_name VARCHAR(255),
    team_logo VARCHAR(500),
    start_date DATE,
    end_date DATE,

    INDEX idx_coach (coach_id),

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

-- ==============================================
-- INDEXES for Performance
-- ==============================================
-- Add index for predictions lookup by fixture
CREATE INDEX IF NOT EXISTS idx_predictions_fixture ON match_predictions(fixture_id);

-- Add composite index for injuries lookup
CREATE INDEX IF NOT EXISTS idx_injuries_fixture_team ON match_injuries(fixture_id, team_id);
