-- Odds Prediction App Database Schema
-- Created for comprehensive football match prediction system

-- Leagues Table
CREATE TABLE IF NOT EXISTS leagues (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    country VARCHAR(50) NOT NULL,
    tier INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Teams Table
CREATE TABLE IF NOT EXISTS teams (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    league_id INT NOT NULL,
    country VARCHAR(50),
    founded_year INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (league_id) REFERENCES leagues(id),
    UNIQUE KEY unique_team_league (name, league_id)
);

-- Matches Table (Historical Data)
CREATE TABLE IF NOT EXISTS matches (
    id INT PRIMARY KEY AUTO_INCREMENT,
    league_id INT NOT NULL,
    home_team_id INT NOT NULL,
    away_team_id INT NOT NULL,
    match_date DATE NOT NULL,
    season VARCHAR(10) NOT NULL,
    home_goals INT,
    away_goals INT,
    result VARCHAR(1),
    home_shots INT,
    away_shots INT,
    home_shots_on_target INT,
    away_shots_on_target INT,
    home_possession DECIMAL(5,2),
    away_possession DECIMAL(5,2),
    home_xg DECIMAL(5,2),
    away_xg DECIMAL(5,2),
    odds_home DECIMAL(6,2),
    odds_draw DECIMAL(6,2),
    odds_away DECIMAL(6,2),
    bookmaker VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (league_id) REFERENCES leagues(id),
    FOREIGN KEY (home_team_id) REFERENCES teams(id),
    FOREIGN KEY (away_team_id) REFERENCES teams(id),
    INDEX idx_season (season),
    INDEX idx_match_date (match_date),
    INDEX idx_teams (home_team_id, away_team_id)
);

-- Team Statistics (Aggregated)
CREATE TABLE IF NOT EXISTS team_stats (
    id INT PRIMARY KEY AUTO_INCREMENT,
    team_id INT NOT NULL,
    season VARCHAR(10) NOT NULL,
    league_id INT NOT NULL,
    matches_played INT DEFAULT 0,
    wins INT DEFAULT 0,
    draws INT DEFAULT 0,
    losses INT DEFAULT 0,
    goals_for INT DEFAULT 0,
    goals_against INT DEFAULT 0,
    shots_for INT DEFAULT 0,
    shots_against INT DEFAULT 0,
    shots_on_target_for INT DEFAULT 0,
    shots_on_target_against INT DEFAULT 0,
    possession_avg DECIMAL(5,2),
    xg_for DECIMAL(8,2),
    xg_against DECIMAL(8,2),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (team_id) REFERENCES teams(id),
    FOREIGN KEY (league_id) REFERENCES leagues(id),
    UNIQUE KEY unique_team_season (team_id, season, league_id)
);

-- Head to Head Records
CREATE TABLE IF NOT EXISTS h2h_records (
    id INT PRIMARY KEY AUTO_INCREMENT,
    team1_id INT NOT NULL,
    team2_id INT NOT NULL,
    team1_wins INT DEFAULT 0,
    team2_wins INT DEFAULT 0,
    draws INT DEFAULT 0,
    last_match_date DATE,
    last_result VARCHAR(1),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (team1_id) REFERENCES teams(id),
    FOREIGN KEY (team2_id) REFERENCES teams(id),
    UNIQUE KEY unique_h2h (team1_id, team2_id)
);

-- Predictions Table (User Predictions)
CREATE TABLE IF NOT EXISTS predictions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_session_id VARCHAR(100),
    home_team_id INT NOT NULL,
    away_team_id INT NOT NULL,
    league_id INT NOT NULL,
    odds_home DECIMAL(6,2),
    odds_draw DECIMAL(6,2),
    odds_away DECIMAL(6,2),
    user_odds_home DECIMAL(6,2),
    user_odds_draw DECIMAL(6,2),
    user_odds_away DECIMAL(6,2),
    poisson_prediction VARCHAR(50),
    poisson_probability DECIMAL(5,2),
    win_percentage_prediction VARCHAR(50),
    win_percentage_probability DECIMAL(5,2),
    xg_prediction VARCHAR(50),
    xg_probability DECIMAL(5,2),
    form_prediction VARCHAR(50),
    form_probability DECIMAL(5,2),
    recommended_bet VARCHAR(50),
    confidence_level DECIMAL(5,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (home_team_id) REFERENCES teams(id),
    FOREIGN KEY (away_team_id) REFERENCES teams(id),
    FOREIGN KEY (league_id) REFERENCES leagues(id)
);

-- Data Range Configuration
CREATE TABLE IF NOT EXISTS data_config (
    id INT PRIMARY KEY AUTO_INCREMENT,
    config_key VARCHAR(100) NOT NULL UNIQUE,
    config_value VARCHAR(255),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Ad Configuration
CREATE TABLE IF NOT EXISTS ad_config (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ad_type ENUM('custom', 'network') NOT NULL,
    ad_name VARCHAR(100),
    ad_code LONGTEXT,
    position VARCHAR(50),
    enabled BOOLEAN DEFAULT 1,
    duration_seconds INT,
    url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- CSV Upload Log
CREATE TABLE IF NOT EXISTS csv_uploads (
    id INT PRIMARY KEY AUTO_INCREMENT,
    filename VARCHAR(255),
    upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    rows_processed INT,
    rows_successful INT,
    rows_failed INT,
    status VARCHAR(50),
    error_log LONGTEXT
);

-- Insert default leagues
INSERT INTO leagues (name, country, tier) VALUES
('Premier League', 'England', 1),
('Championship', 'England', 2),
('La Liga', 'Spain', 1),
('Segunda División', 'Spain', 2),
('Serie A', 'Italy', 1),
('Serie B', 'Italy', 2),
('Bundesliga', 'Germany', 1),
('2. Bundesliga', 'Germany', 2),
('Ligue 1', 'France', 1),
('Ligue 2', 'France', 2),
('Eredivisie', 'Netherlands', 1),
('Primeira Liga', 'Portugal', 1),
('Super Lig', 'Turkey', 1),
('Russian Premier League', 'Russia', 1),
('Super League Greece', 'Greece', 1),
('Belgian First Division', 'Belgium', 1),
('Danish Superliga', 'Denmark', 1),
('Swedish Allsvenskan', 'Sweden', 1),
('Norwegian Eliteserien', 'Norway', 1),
('Swiss Super League', 'Switzerland', 1),
('AFCON', 'Africa', 1),
('Copa Libertadores', 'South America', 1),
('MLS', 'USA', 1),
('J-League', 'Japan', 1),
('K-League', 'South Korea', 1);

-- Insert default data configuration
INSERT INTO data_config (config_key, config_value) VALUES
('min_season', '2022/23'),
('max_season', '2024/25'),
('recommended_range', '2022/23 to 2024/25'),
('last_data_update', '2025-12-26'),
('data_freshness_warning_days', '30');