DROP TABLE IF EXISTS player_stats;
DROP TABLE IF EXISTS stat_definitions;
DROP TABLE IF EXISTS competitions;
DROP TABLE IF EXISTS players;
DROP TABLE IF EXISTS sports;

CREATE TABLE sports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sport_key VARCHAR(50) UNIQUE NOT NULL,
    sport_name VARCHAR(100) NOT NULL
);
INSERT INTO sports (sport_key, sport_name) VALUES
('cricket', 'Cricket'),
('football', 'Football'),
('basketball', 'Basketball');
CREATE TABLE players (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    country VARCHAR(50),
    birth_date DATE,
    birth_place VARCHAR(150),
    primary_role VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE competitions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sport_id INT NOT NULL,
    competition_key VARCHAR(50) NOT NULL,
    competition_name VARCHAR(100) NOT NULL,

    FOREIGN KEY (sport_id) REFERENCES sports(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_sport_competition (sport_id, competition_key)
);
-- Cricket competitions
INSERT INTO competitions (sport_id, competition_key, competition_name)
SELECT id, 'IPL', 'Indian Premier League' FROM sports WHERE sport_key='cricket';

INSERT INTO competitions (sport_id, competition_key, competition_name)
SELECT id, 'TEST', 'Test Cricket' FROM sports WHERE sport_key='cricket';

-- Football competition
INSERT INTO competitions (sport_id, competition_key, competition_name)
SELECT id, 'EPL', 'English Premier League' FROM sports WHERE sport_key='football';
CREATE TABLE stat_definitions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sport_id INT NOT NULL,

    stat_key VARCHAR(50) NOT NULL,
    stat_label VARCHAR(100) NOT NULL,
    value_type ENUM('int','decimal','text') NOT NULL,

    stat_group VARCHAR(50) DEFAULT 'general',
    display_order INT DEFAULT 0,

    FOREIGN KEY (sport_id) REFERENCES sports(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_sport_stat (sport_id, stat_key)
);
INSERT INTO stat_definitions (sport_id, stat_key, stat_label, value_type, stat_group, display_order)
SELECT id, 'matches', 'Matches', 'int', 'general', 1 FROM sports WHERE sport_key='cricket';

INSERT INTO stat_definitions (sport_id, stat_key, stat_label, value_type, stat_group, display_order)
SELECT id, 'runs', 'Runs', 'int', 'batting', 2 FROM sports WHERE sport_key='cricket';

INSERT INTO stat_definitions (sport_id, stat_key, stat_label, value_type, stat_group, display_order)
SELECT id, 'strike_rate', 'Strike Rate', 'decimal', 'batting', 3 FROM sports WHERE sport_key='cricket';
INSERT INTO stat_definitions (sport_id, stat_key, stat_label, value_type, stat_group, display_order)
SELECT id, 'matches', 'Matches', 'int', 'general', 1 FROM sports WHERE sport_key='football';

INSERT INTO stat_definitions (sport_id, stat_key, stat_label, value_type, stat_group, display_order)
SELECT id, 'goals', 'Goals', 'int', 'attacking', 2 FROM sports WHERE sport_key='football';

INSERT INTO stat_definitions (sport_id, stat_key, stat_label, value_type, stat_group, display_order)
SELECT id, 'assists', 'Assists', 'int', 'attacking', 3 FROM sports WHERE sport_key='football';
CREATE TABLE player_stats (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    player_id INT NOT NULL,
    competition_id INT NOT NULL,
    stat_id INT NOT NULL,

    season VARCHAR(20) DEFAULT 'career',

    value_int INT NULL,
    value_decimal DECIMAL(10,2) NULL,
    value_text VARCHAR(100) NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE,
    FOREIGN KEY (competition_id) REFERENCES competitions(id) ON DELETE CASCADE,
    FOREIGN KEY (stat_id) REFERENCES stat_definitions(id) ON DELETE CASCADE,

    UNIQUE KEY uniq_player_competition_stat_season
        (player_id, competition_id, stat_id, season),

    CHECK (
        (value_int IS NOT NULL AND value_decimal IS NULL AND value_text IS NULL) OR
        (value_int IS NULL AND value_decimal IS NOT NULL AND value_text IS NULL) OR
        (value_int IS NULL AND value_decimal IS NULL AND value_text IS NOT NULL)
    )
);
CREATE INDEX idx_player_competition
ON player_stats (player_id, competition_id, season);

CREATE INDEX idx_stat_lookup
ON player_stats (stat_id);
INSERT INTO players (full_name, country, primary_role)
VALUES ('N Jagadeesan', 'India', 'Wicketkeeper-Batsman');
-- Matches
INSERT INTO player_stats (player_id, competition_id, stat_id, value_int)
SELECT
    p.id,
    c.id,
    s.id,
    13
FROM players p
JOIN competitions c ON c.competition_key='IPL'
JOIN stat_definitions s ON s.stat_key='matches'
WHERE p.full_name='N Jagadeesan';

-- Runs
INSERT INTO player_stats (player_id, competition_id, stat_id, value_int)
SELECT
    p.id,
    c.id,
    s.id,
    162
FROM players p
JOIN competitions c ON c.competition_key='IPL'
JOIN stat_definitions s ON s.stat_key='runs'
WHERE p.full_name='N Jagadeesan';

-- Strike Rate
INSERT INTO player_stats (player_id, competition_id, stat_id, value_decimal)
SELECT
    p.id,
    c.id,
    s.id,
    110.21
FROM players p
JOIN competitions c ON c.competition_key='IPL'
JOIN stat_definitions s ON s.stat_key='strike_rate'
WHERE p.full_name='N Jagadeesan';