#!/usr/bin/env python3
"""
Apply SQL migration 003 to existing database
Migration 003: Add cpu_score_single and cpu_score_multi columns to benchmarks table
Usage: python apply_migration_003.py
"""

import os
import sqlite3
from typing import Dict, List, Tuple

# Database path
DB_PATH = os.path.join(os.path.dirname(__file__), "data", "data.db")
MIGRATION_PATH = os.path.join(
    os.path.dirname(__file__), "migrations", "003_add_cpu_scores.sql"
)

# (column_name, human description)
COLUMNS_TO_ADD: List[Tuple[str, str]] = [
    ("cpu_score_single", "Score CPU monocœur"),
    ("cpu_score_multi", "Score CPU multicœur"),
]


def _load_statements() -> Dict[str, str]:
    """Load SQL statements mapped by column name from the migration file."""
    with open(MIGRATION_PATH, "r", encoding="utf-8") as f:
        raw_sql = f.read()

    # Remove comments and blank lines for easier parsing
    filtered_lines = []
    for line in raw_sql.splitlines():
        stripped = line.strip()
        if not stripped or stripped.startswith("--"):
            continue
        filtered_lines.append(line)

    statements = {}
    for statement in "\n".join(filtered_lines).split(";"):
        stmt = statement.strip()
        if not stmt:
            continue
        for column_name, _ in COLUMNS_TO_ADD:
            if column_name in stmt:
                statements[column_name] = stmt
                break

    return statements


def apply_migration():
    """Apply the SQL migration 003."""

    if not os.path.exists(DB_PATH):
        print(f"❌ Database not found at {DB_PATH}")
        print("   The database will be created automatically on first run.")
        return

    if not os.path.exists(MIGRATION_PATH):
        print(f"❌ Migration file not found at {MIGRATION_PATH}")
        return

    print(f"📂 Database: {DB_PATH}")
    print(f"📄 Migration: {MIGRATION_PATH}")
    print()

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

    try:
        cursor.execute("PRAGMA table_info(benchmarks)")
        existing_columns = {row[1] for row in cursor.fetchall()}

        missing_columns = [
            col for col, _ in COLUMNS_TO_ADD if col not in existing_columns
        ]
        if not missing_columns:
            print("⚠️  Migration 003 already applied (CPU score columns exist)")
            print("✅ Database is up to date")
            return

        statements = _load_statements()

        print("🔄 Applying migration 003...")
        for column_name, description in COLUMNS_TO_ADD:
            if column_name not in missing_columns:
                print(f"⏩ Column {column_name} already present, skipping")
                continue

            statement = statements.get(column_name)
            if not statement:
                raise RuntimeError(
                    f"No SQL statement found for column '{column_name}' in migration file"
                )

            print(f"➕ Adding {description} ({column_name})...")
            cursor.execute(statement)

        conn.commit()

        print("✅ Migration 003 applied successfully!")
        print("New columns added to benchmarks table:")
        for column_name, description in COLUMNS_TO_ADD:
            if column_name in missing_columns:
                print(f"  - {column_name}: {description}")

    except (sqlite3.Error, RuntimeError) as e:
        print(f"❌ Error applying migration: {e}")
        conn.rollback()
    finally:
        conn.close()


if __name__ == "__main__":
    apply_migration()