import sqlite3
import json
from werkzeug.security import generate_password_hash

DB_FILE = 'orchard_league.db'

def get_db():
    conn = sqlite3.connect(DB_FILE)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    conn = get_db()
    c = conn.cursor()

    # Drop existing tables
    c.execute('DROP TABLE IF EXISTS GameSubmissions')
    c.execute('DROP TABLE IF EXISTS Games')
    c.execute('DROP TABLE IF EXISTS SeasonTeams')
    c.execute('DROP TABLE IF EXISTS Seasons')
    c.execute('DROP TABLE IF EXISTS Users')

    # Users Table
    c.execute('''
        CREATE TABLE Users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password_hash TEXT NOT NULL,
            is_admin BOOLEAN NOT NULL DEFAULT 0,
            team_name TEXT,
            team_icon TEXT
        )
    ''')

    # Seasons Table
    c.execute('''
        CREATE TABLE Seasons (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            status TEXT NOT NULL DEFAULT 'Scheduled', -- 'Scheduled', 'Active', 'Completed'
            total_games INTEGER,
            games_per_week INTEGER,
            playoff_teams INTEGER,
            playoff_series_length INTEGER
        )
    ''')

    # SeasonTeams Junction Table
    c.execute('''
        CREATE TABLE SeasonTeams (
            season_id INTEGER,
            user_id INTEGER,
            status TEXT NOT NULL DEFAULT 'Pending', -- 'Pending', 'Approved'
            PRIMARY KEY (season_id, user_id),
            FOREIGN KEY (season_id) REFERENCES Seasons(id),
            FOREIGN KEY (user_id) REFERENCES Users(id)
        )
    ''')

    # Games Table
    c.execute('''
        CREATE TABLE Games (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            season_id INTEGER,
            away_team_id INTEGER,
            home_team_id INTEGER,
            scheduled_date TEXT,
            status TEXT NOT NULL DEFAULT 'Scheduled', -- 'Scheduled', 'Final', 'TBD'
            away_score INTEGER,
            home_score INTEGER,
            box_score_json TEXT,
            is_playoff BOOLEAN NOT NULL DEFAULT 0,
            series_id INTEGER,
            playoff_round INTEGER,
            playoff_game_num INTEGER,
            is_conditional BOOLEAN NOT NULL DEFAULT 0,
            FOREIGN KEY (season_id) REFERENCES Seasons(id),
            FOREIGN KEY (away_team_id) REFERENCES Users(id),
            FOREIGN KEY (home_team_id) REFERENCES Users(id)
        )
    ''')

    # GameSubmissions Table
    c.execute('''
        CREATE TABLE GameSubmissions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            game_id INTEGER,
            submitted_by_id INTEGER,
            away_score INTEGER,
            home_score INTEGER,
            box_score_json TEXT,
            proposed_date TEXT,
            FOREIGN KEY (game_id) REFERENCES Games(id),
            FOREIGN KEY (submitted_by_id) REFERENCES Users(id)
        )
    ''')

    # Create Initial Admin User
    admin_password = generate_password_hash('admin')
    c.execute('''
        INSERT INTO Users (username, password_hash, is_admin, team_name)
        VALUES (?, ?, 1, ?)
    ''', ('admin', admin_password, 'The Adminators'))

    # Create 8 Placeholder Teams
    teams = [
        "Yankees", "Giants", "Dodgers", "Cubs", 
        "Red Sox", "Cardinals", "White Sox", "Braves"
    ]
    test_password = generate_password_hash('test')
    for team in teams:
        username = f"test_{team.lower().replace(' ', '_')}"
        team_name = f"Test {team}"
        c.execute('''
            INSERT INTO Users (username, password_hash, is_admin, team_name)
            VALUES (?, ?, 0, ?)
        ''', (username, test_password, team_name))

    conn.commit()
    conn.close()
    print("Database initialized successfully.")

if __name__ == '__main__':
    init_db()