-- ==============================================
-- X2SCORE - Database Schema
-- Real-time Football Live Score System
-- ==============================================

CREATE DATABASE IF NOT EXISTS x2score CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE x2score;

-- ==============================================
-- LEAGUES TABLE
-- ==============================================
CREATE TABLE IF NOT EXISTS leagues (
    id INT PRIMARY KEY,                    -- API League ID
    name VARCHAR(255) NOT NULL,
    logo VARCHAR(500),
    country VARCHAR(100),
    country_code VARCHAR(10),
    country_flag VARCHAR(500),
    season INT,                            -- Current season year
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_country (country),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================
-- TEAMS TABLE
-- ==============================================
CREATE TABLE IF NOT EXISTS teams (
    id INT PRIMARY KEY,                    -- API Team ID
    name VARCHAR(255) NOT NULL,
    code VARCHAR(10),
    logo VARCHAR(500),
    country VARCHAR(100),
    founded INT,
    venue_name VARCHAR(255),
    venue_city VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name),
    INDEX idx_country (country)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================
-- FIXTURES TABLE (Core Live Score Data)
-- ==============================================
CREATE TABLE IF NOT EXISTS fixtures (
    id INT PRIMARY KEY,                    -- API Fixture ID
    league_id INT NOT NULL,
    home_team_id INT NOT NULL,
    away_team_id INT NOT NULL,
    match_date DATETIME NOT NULL,
    timestamp_utc INT,                     -- Unix timestamp for sorting
    venue_name VARCHAR(255),
    venue_city VARCHAR(100),
    referee VARCHAR(100),

    -- Match Status
    status_long VARCHAR(50),               -- Match Finished, First Half, etc.
    status_short VARCHAR(10),              -- FT, 1H, 2H, HT, NS, etc.
    elapsed INT DEFAULT NULL,              -- Current minute
    extra_time INT DEFAULT NULL,           -- Extra time minutes

    -- Score
    goals_home INT DEFAULT 0,
    goals_away INT DEFAULT 0,
    halftime_home INT DEFAULT NULL,
    halftime_away INT DEFAULT NULL,
    fulltime_home INT DEFAULT NULL,
    fulltime_away INT DEFAULT NULL,
    extratime_home INT DEFAULT NULL,
    extratime_away INT DEFAULT NULL,
    penalty_home INT DEFAULT NULL,
    penalty_away INT DEFAULT NULL,

    -- Metadata
    is_live TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_match_date (match_date),
    INDEX idx_league (league_id),
    INDEX idx_home_team (home_team_id),
    INDEX idx_away_team (away_team_id),
    INDEX idx_status (status_short),
    INDEX idx_live (is_live),
    INDEX idx_timestamp (timestamp_utc),

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

-- ==============================================
-- ODDS TABLE (Pre-match Betting Odds)
-- ==============================================
CREATE TABLE IF NOT EXISTS odds (
    fixture_id INT PRIMARY KEY,
    bookmaker_id INT,
    bookmaker_name VARCHAR(100),

    -- 1X2 Odds (Match Winner)
    home_odd DECIMAL(6,2),
    draw_odd DECIMAL(6,2),
    away_odd DECIMAL(6,2),

    -- Asian Handicap (Optional)
    handicap_home DECIMAL(6,2) DEFAULT NULL,
    handicap_away DECIMAL(6,2) DEFAULT NULL,
    handicap_value VARCHAR(10) DEFAULT NULL,

    -- Over/Under 2.5 (Optional)
    over_25 DECIMAL(6,2) DEFAULT NULL,
    under_25 DECIMAL(6,2) DEFAULT NULL,

    -- Tip/Prediction (ทรรศนะฟุตบอล/ทีเด็ดบอล)
    tip VARCHAR(100) DEFAULT NULL,

    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 tip column if table already exists
-- ALTER TABLE odds ADD COLUMN tip VARCHAR(100) DEFAULT NULL AFTER under_25;

-- ==============================================
-- EVENTS TABLE (Goals, Cards, Substitutions)
-- ==============================================
CREATE TABLE IF NOT EXISTS fixture_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fixture_id INT NOT NULL,
    time_elapsed INT,
    time_extra INT DEFAULT NULL,
    team_id INT,
    player_id INT DEFAULT NULL,
    player_name VARCHAR(255),
    assist_id INT DEFAULT NULL,
    assist_name VARCHAR(255),
    event_type VARCHAR(50),                -- Goal, Card, Subst, Var
    event_detail VARCHAR(100),             -- Normal Goal, Yellow Card, Red Card, etc.
    comments VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_fixture (fixture_id),
    INDEX idx_type (event_type),
    INDEX idx_time (time_elapsed),

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

-- ==============================================
-- LINEUP TABLE
-- ==============================================
CREATE TABLE IF NOT EXISTS fixture_lineups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fixture_id INT NOT NULL,
    team_id INT NOT NULL,
    formation VARCHAR(20),
    player_id INT,
    player_name VARCHAR(255),
    player_number INT,
    player_pos VARCHAR(5),                 -- G, D, M, F
    player_grid VARCHAR(10),               -- Position on field grid
    is_substitute TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_fixture (fixture_id),
    INDEX idx_team (team_id),

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

-- ==============================================
-- STATISTICS TABLE
-- ==============================================
CREATE TABLE IF NOT EXISTS fixture_statistics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fixture_id INT NOT NULL,
    team_id INT NOT NULL,
    stat_type VARCHAR(50),                 -- Shots on Goal, Possession, etc.
    stat_value VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_fixture (fixture_id),
    INDEX idx_team (team_id),
    UNIQUE KEY unique_stat (fixture_id, team_id, stat_type),

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

-- ==============================================
-- API QUOTA TRACKING TABLE
-- ==============================================
CREATE TABLE IF NOT EXISTS api_quota_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    endpoint VARCHAR(100) NOT NULL,
    request_count INT DEFAULT 1,
    date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY unique_endpoint_date (endpoint, date),
    INDEX idx_date (date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================
-- INITIAL DATA / VIEWS
-- ==============================================

-- View for live fixtures with team and league names
CREATE OR REPLACE VIEW v_live_fixtures AS
SELECT
    f.id,
    f.match_date,
    f.status_short,
    f.status_long,
    f.elapsed,
    f.goals_home,
    f.goals_away,
    l.id AS league_id,
    l.name AS league_name,
    l.logo AS league_logo,
    l.country AS league_country,
    l.country_flag,
    ht.id AS home_team_id,
    ht.name AS home_team_name,
    ht.logo AS home_team_logo,
    at.id AS away_team_id,
    at.name AS away_team_name,
    at.logo AS away_team_logo,
    o.home_odd,
    o.draw_odd,
    o.away_odd
FROM fixtures f
JOIN leagues l ON f.league_id = l.id
JOIN teams ht ON f.home_team_id = ht.id
JOIN teams at ON f.away_team_id = at.id
LEFT JOIN odds o ON f.id = o.fixture_id
WHERE f.is_live = 1
ORDER BY l.country, l.name, f.match_date;

-- View for today's fixtures
CREATE OR REPLACE VIEW v_today_fixtures AS
SELECT
    f.id,
    f.match_date,
    f.status_short,
    f.status_long,
    f.elapsed,
    f.goals_home,
    f.goals_away,
    f.is_live,
    l.id AS league_id,
    l.name AS league_name,
    l.logo AS league_logo,
    l.country AS league_country,
    l.country_flag,
    ht.id AS home_team_id,
    ht.name AS home_team_name,
    ht.logo AS home_team_logo,
    at.id AS away_team_id,
    at.name AS away_team_name,
    at.logo AS away_team_logo,
    o.home_odd,
    o.draw_odd,
    o.away_odd
FROM fixtures f
JOIN leagues l ON f.league_id = l.id
JOIN teams ht ON f.home_team_id = ht.id
JOIN teams at ON f.away_team_id = at.id
LEFT JOIN odds o ON f.id = o.fixture_id
WHERE DATE(f.match_date) = CURDATE()
ORDER BY f.is_live DESC, l.country, l.name, f.match_date;
