setup_database.py

#!/usr/bin/env python3
"""
Setup script to create the football predictions database schema and populate with sample data.
"""

import sqlite3
import os
from pathlib import Path


def setup_database():
    """Create database schema and populate with sample data."""

    # Ensure data directory exists
    data_dir = Path("D:/MatlaBz/data")
    data_dir.mkdir(exist_ok=True)

    db_path = data_dir / "football_predictions.db"

    # Remove existing database if it exists
    if db_path.exists():
        db_path.unlink()
        print(f"Removed existing database: {db_path}")

    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    # Create tables
    print("Creating database schema...")

    # Create matches table
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS matches (
            match_id TEXT PRIMARY KEY,
            home_team_id TEXT,
            away_team_id TEXT,
            home_team_name TEXT,
            away_team_name TEXT,
            match_date DATE,
            competition TEXT,
            kickoff_utc TEXT,
            status TEXT DEFAULT 'scheduled'
        )
    """)

    # Create predictions table
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS predictions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            match_id TEXT,
            home_team_id TEXT,
            away_team_id TEXT,
            home_win_prob REAL,
            draw_prob REAL,
            away_win_prob REAL,
            btts_prob REAL,
            over_1_5_prob REAL,
            over_2_5_prob REAL,
            expected_home_goals REAL,
            expected_away_goals REAL,
            expected_home_cards REAL,
            expected_away_cards REAL,
            home_defensive_rank INTEGER,
            away_defensive_rank INTEGER,
            home_offensive_rank INTEGER,
            away_offensive_rank INTEGER,
            source TEXT,
            generated_at TEXT,
            match_date DATE,
            FOREIGN KEY (match_id) REFERENCES matches(match_id)
        )
    """)

    # Create players table
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS players (
            player_id TEXT PRIMARY KEY,
            name TEXT,
            position TEXT,
            team_id TEXT,
            nationality TEXT
        )
    """)

    # Create player_predictions table
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS player_predictions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            match_id TEXT,
            player_id TEXT,
            team_id TEXT,
            expected_goals REAL,
            expected_assists REAL,
            expected_cards REAL,
            expected_saves REAL,
            expected_fouls REAL,
            fitness REAL,
            injury_status TEXT,
            source TEXT,
            generated_at TEXT,
            FOREIGN KEY (match_id) REFERENCES matches(match_id),
            FOREIGN KEY (player_id) REFERENCES players(player_id)
        )
    """)

    # Insert sample matches for today and tomorrow
    print("Inserting sample matches...")
    matches_data = [
        ('match_001', 'team_001', 'team_002', 'Manchester United',
         'Liverpool', '2024-11-09', 'Premier League', '15:00'),
        ('match_002', 'team_003', 'team_004', 'Arsenal',
         'Chelsea', '2024-11-09', 'Premier League', '17:30'),
        ('match_003', 'team_005', 'team_006', 'Real Madrid',
         'Barcelona', '2024-11-10', 'La Liga', '20:00'),
        ('match_004', 'team_007', 'team_008', 'Bayern Munich',
         'Borussia Dortmund', '2024-11-10', 'Bundesliga', '18:30')
    ]

    cursor.executemany("""
        INSERT OR REPLACE INTO matches (match_id, home_team_id, away_team_id, home_team_name, away_team_name, match_date, competition, kickoff_utc) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    """, matches_data)

    # Insert sample predictions
    print("Inserting sample predictions...")
    predictions_data = [
        ('match_001', 'team_001', 'team_002', 0.45, 0.25, 0.30, 0.75, 0.85, 0.60, 1.8,
         1.2, 2.5, 1.8, 8, 6, 4, 2, 'omnicore', '2024-11-09 08:00:00', '2024-11-09'),
        ('match_002', 'team_003', 'team_004', 0.40, 0.30, 0.30, 0.70, 0.80, 0.55, 1.5,
         1.5, 2.2, 2.0, 5, 7, 3, 4, 'omnicore', '2024-11-09 08:00:00', '2024-11-09'),
        ('match_003', 'team_005', 'team_006', 0.50, 0.25, 0.25, 0.80, 0.90, 0.70, 2.2,
         1.8, 1.8, 2.5, 2, 1, 1, 3, 'omnicore', '2024-11-09 08:00:00', '2024-11-10'),
        ('match_004', 'team_007', 'team_008', 0.60, 0.20, 0.20, 0.65, 0.75, 0.45, 2.0,
         1.0, 2.0, 1.5, 1, 3, 2, 8, 'omnicore', '2024-11-09 08:00:00', '2024-11-10')
    ]

    cursor.executemany("""
        INSERT OR REPLACE INTO predictions (
            match_id, home_team_id, away_team_id, home_win_prob, draw_prob, away_win_prob, 
            btts_prob, over_1_5_prob, over_2_5_prob, expected_home_goals, expected_away_goals,
            expected_home_cards, expected_away_cards, home_defensive_rank, away_defensive_rank,
            home_offensive_rank, away_offensive_rank, source, generated_at, match_date
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, predictions_data)

    # Insert sample players
    print("Inserting sample players...")
    players_data = [
        ('player_001', 'Bruno Fernandes', 'Midfielder', 'team_001', 'Portugal'),
        ('player_002', 'Mohamed Salah', 'Forward', 'team_002', 'Egypt'),
        ('player_003', 'Bukayo Saka', 'Forward', 'team_003', 'England'),
        ('player_004', 'Raheem Sterling', 'Forward', 'team_004', 'England'),
        ('player_005', 'Karim Benzema', 'Forward', 'team_005', 'France'),
        ('player_006', 'Robert Lewandowski', 'Forward', 'team_006', 'Poland'),
        ('player_007', 'Thomas Muller', 'Midfielder', 'team_007', 'Germany'),
        ('player_008', 'Jude Bellingham', 'Midfielder', 'team_008', 'England')
    ]

    cursor.executemany("""
        INSERT OR REPLACE INTO players (player_id, name, position, team_id, nationality) 
        VALUES (?, ?, ?, ?, ?)
    """, players_data)

    # Insert sample player predictions
    print("Inserting sample player predictions...")
    player_predictions_data = [
        ('match_001', 'player_001', 'team_001', 0.3, 0.4, 0.2,
         0.0, 0.8, 95, 'fit', 'omnicore', '2024-11-09 08:00:00'),
        ('match_001', 'player_002', 'team_002', 0.5, 0.3, 0.1,
         0.0, 0.6, 98, 'fit', 'omnicore', '2024-11-09 08:00:00'),
        ('match_002', 'player_003', 'team_003', 0.4, 0.5, 0.15,
         0.0, 0.7, 96, 'fit', 'omnicore', '2024-11-09 08:00:00'),
        ('match_002', 'player_004', 'team_004', 0.3, 0.2, 0.25,
         0.0, 0.9, 92, 'fit', 'omnicore', '2024-11-09 08:00:00'),
        ('match_003', 'player_005', 'team_005', 0.6, 0.2, 0.1,
         0.0, 0.5, 99, 'fit', 'omnicore', '2024-11-09 08:00:00'),
        ('match_003', 'player_006', 'team_006', 0.7, 0.3, 0.15,
         0.0, 0.4, 97, 'fit', 'omnicore', '2024-11-09 08:00:00'),
        ('match_004', 'player_007', 'team_007', 0.2, 0.6, 0.2,
         0.0, 0.6, 94, 'fit', 'omnicore', '2024-11-09 08:00:00'),
        ('match_004', 'player_008', 'team_008', 0.3, 0.4, 0.25,
         0.0, 0.8, 93, 'fit', 'omnicore', '2024-11-09 08:00:00')
    ]

    cursor.executemany("""
        INSERT OR REPLACE INTO player_predictions (
            match_id, player_id, team_id, expected_goals, expected_assists, expected_cards,
            expected_saves, expected_fouls, fitness, injury_status, source, generated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, player_predictions_data)

    # Commit changes
    conn.commit()

    # Verify data was inserted
    cursor.execute("SELECT COUNT(*) FROM matches")
    match_count = cursor.fetchone()[0]
    cursor.execute("SELECT COUNT(*) FROM predictions")
    prediction_count = cursor.fetchone()[0]
    cursor.execute("SELECT COUNT(*) FROM players")
    player_count = cursor.fetchone()[0]
    cursor.execute("SELECT COUNT(*) FROM player_predictions")
    player_pred_count = cursor.fetchone()[0]

    print(f"Database setup complete!")
    print(f"Matches: {match_count}")
    print(f"Predictions: {prediction_count}")
    print(f"Players: {player_count}")
    print(f"Player Predictions: {player_pred_count}")

    conn.close()
    print(f"Database created at: {db_path}")


if __name__ == "__main__":
    setup_database()