import os
from sqlalchemy import create_engine, text
from dotenv import load_dotenv

load_dotenv()

DB_USER = os.getenv("DB_USER", "v2_usr_quiz")
DB_PASS = os.getenv("DB_PASS", "Quiz_Pass_2026_Secure!")
DB_HOST = os.getenv("DB_HOST", "db.waiswuaschd.de")
DB_PORT = os.getenv("DB_PORT", "3306")
DB_NAME = os.getenv("DB_NAME", "v2_db_quiz")

DATABASE_URL = f"mysql+pymysql://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
engine = create_engine(DATABASE_URL)

def run_migration():
    with engine.connect() as conn:
        print("Running migrate_v4...")

        # 1. Add duration_seconds to quiz_sessions
        res = conn.execute(text("SHOW COLUMNS FROM quiz_sessions LIKE 'duration_seconds'")).fetchall()
        if not res:
            print("Adding duration_seconds to quiz_sessions...")
            conn.execute(text("ALTER TABLE quiz_sessions ADD COLUMN duration_seconds INT NULL"))
            conn.commit()
            print("Added duration_seconds column.")
        else:
            print("Column duration_seconds already exists.")

        # 2. Create leaderboard_entries table
        print("Ensuring leaderboard_entries table exists...")
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS leaderboard_entries (
                id INT AUTO_INCREMENT PRIMARY KEY,
                nickname VARCHAR(50) NOT NULL,
                score INT NOT NULL,
                total_questions INT NOT NULL DEFAULT 10,
                time_seconds INT NOT NULL,
                difficulty VARCHAR(20) NOT NULL DEFAULT 'all',
                lang VARCHAR(10) NOT NULL DEFAULT 'de',
                session_id VARCHAR(36) NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_leaderboard_score_time (score DESC, time_seconds ASC),
                INDEX idx_leaderboard_diff (difficulty),
                INDEX idx_leaderboard_created (created_at DESC)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """))
        conn.commit()
        print("leaderboard_entries table ready.")

if __name__ == "__main__":
    run_migration()