-- ==============================================
-- Migration: Add League Standings Table
-- Version: 003
-- Date: 2026-01-17
-- Description: Store league standings for displaying team positions
-- ==============================================

-- ==============================================
-- LEAGUE STANDINGS TABLE
-- ==============================================
CREATE TABLE IF NOT EXISTS league_standings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    league_id INT NOT NULL,
    season INT NOT NULL,
    team_id INT NOT NULL,

    -- Position
    rank_position INT NOT NULL,
    group_name VARCHAR(50) DEFAULT NULL,  -- For group stage (Group A, B, etc.)

    -- Stats
    points INT DEFAULT 0,
    played INT DEFAULT 0,
    win INT DEFAULT 0,
    draw INT DEFAULT 0,
    lose INT DEFAULT 0,
    goals_for INT DEFAULT 0,
    goals_against INT DEFAULT 0,
    goal_diff INT DEFAULT 0,

    -- Form (last 5 matches: W, D, L)
    form VARCHAR(20) DEFAULT NULL,

    -- Description (Champions League, Relegation, etc.)
    description VARCHAR(255) DEFAULT NULL,

    -- Timestamps
    fetched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- Indexes
    INDEX idx_league_season (league_id, season),
    INDEX idx_team (team_id),
    INDEX idx_rank (rank_position),
    UNIQUE KEY unique_standing (league_id, season, team_id, group_name),

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

-- Add index for quick position lookup by team
CREATE INDEX IF NOT EXISTS idx_standings_lookup ON league_standings (team_id, league_id, season);
