
-- Create the database
CREATE DATABASE IF NOT EXISTS betginous_db;
USE betginous_db;

-- Table to store teams
CREATE TABLE IF NOT EXISTS teams (
    id INT AUTO_INCREMENT PRIMARY KEY,
    team_name VARCHAR(255) NOT NULL,
    logo_url VARCHAR(255) NULL
);

-- Table to store leagues
CREATE TABLE IF NOT EXISTS leagues (
    id INT AUTO_INCREMENT PRIMARY KEY,
    league_name VARCHAR(255) NOT NULL,
    country VARCHAR(255) NULL,
    logo_url VARCHAR(255) NULL
);

-- Table to store fixtures
CREATE TABLE IF NOT EXISTS fixtures (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fixture_date DATE NOT NULL,
    home_team_id INT NOT NULL,
    away_team_id INT NOT NULL,
    league_id INT NOT NULL,
    venue VARCHAR(255) NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'scheduled',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE 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)
);

-- Table to store live scores
CREATE TABLE IF NOT EXISTS live_scores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fixture_id INT NOT NULL,
    home_score INT NOT NULL,
    away_score INT NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'in_progress',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (fixture_id) REFERENCES fixtures(id)
);

-- Table to store predictions
CREATE TABLE IF NOT EXISTS predictions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fixture_id INT NOT NULL,
    prediction VARCHAR(255) NOT NULL,
    confidence_level DECIMAL(5,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (fixture_id) REFERENCES fixtures(id)
);

-- Insert some sample data (optional, for testing purposes)
INSERT INTO teams (team_name, logo_url) VALUES ('Team A', 'http://example.com/logo_a.png');
INSERT INTO teams (team_name, logo_url) VALUES ('Team B', 'http://example.com/logo_b.png');

INSERT INTO leagues (league_name, country, logo_url) VALUES ('Premier League', 'England', 'http://example.com/league_logo.png');

INSERT INTO fixtures (fixture_date, home_team_id, away_team_id, league_id, venue, status) 
VALUES ('2024-08-19', 1, 2, 1, 'Example Stadium', 'scheduled');

INSERT INTO live_scores (fixture_id, home_score, away_score, status) VALUES (1, 2, 1, 'finished');

INSERT INTO predictions (fixture_id, prediction, confidence_level) VALUES (1, 'Team A wins', 75.00);
