"""
Migration: Add game_categories table and category_id to games.

Run from the backend/ directory:
    python migrate_game_categories.py
"""
import pymysql
from app.core.config import settings


def run_migration():
    conn = pymysql.connect(
        host=settings.DB_HOST,
        port=settings.DB_PORT,
        user=settings.DB_USER,
        password=settings.DB_PASSWORD,
        database=settings.DB_NAME,
        charset="utf8mb4",
    )
    cur = conn.cursor()

    # 1. Create game_categories table
    print("Creating game_categories table...")
    cur.execute("""
        CREATE TABLE IF NOT EXISTS game_categories (
            id          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
            name        VARCHAR(100) NOT NULL,
            slug        VARCHAR(100) NOT NULL,
            description TEXT,
            icon        VARCHAR(10),
            color_hex   VARCHAR(7) DEFAULT '#4F46E5',
            is_active   TINYINT(1) DEFAULT 1,
            created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            CONSTRAINT uk_gc_name UNIQUE (name),
            CONSTRAINT uk_gc_slug UNIQUE (slug)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """)
    print("   game_categories table ready")

    # 2. Seed default categories
    defaults = [
        ("Home",        "home",        "Home & household activities",  "home",    "#10B981"),
        ("School",      "school",      "School & classroom activities","school",  "#3B82F6"),
        ("Market",      "market",      "Shopping & market activities", "market",  "#F59E0B"),
        ("Community",   "community",   "Community & social activities","community","#8B5CF6"),
        ("Nature",      "nature",      "Nature & outdoor activities",  "nature",  "#22C55E"),
        ("Science",     "science",     "Science & experiments",        "science", "#EC4899"),
    ]
    for name, slug, desc, icon, color in defaults:
        cur.execute("""
            INSERT IGNORE INTO game_categories (name, slug, description, icon, color_hex)
            VALUES (%s, %s, %s, %s, %s)
        """, (name, slug, desc, icon, color))
    conn.commit()
    print("   Default categories seeded")

    # 3. Add category_id to games (if missing)
    print("Adding category_id column to games...")
    cur.execute("""
        SELECT COUNT(*) FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = %s AND TABLE_NAME = 'games' AND COLUMN_NAME = 'category_id'
    """, (settings.DB_NAME,))
    if cur.fetchone()[0] == 0:
        # Get the id of the "Home" category to use as default
        cur.execute("SELECT id FROM game_categories WHERE slug = 'home'")
        home_row = cur.fetchone()
        home_id = home_row[0] if home_row else None

        cur.execute("""
            ALTER TABLE games
            ADD COLUMN category_id INT NULL
                AFTER game_type_id,
            ADD CONSTRAINT fk_game_category
                FOREIGN KEY (category_id)
                REFERENCES game_categories(id)
                ON DELETE SET NULL;
        """)
        conn.commit()
        print("   category_id column added")

        # Set all existing games to "Home" category
        if home_id:
            cur.execute("UPDATE games SET category_id = %s WHERE category_id IS NULL", (home_id,))
            conn.commit()
            print(f"   Existing games assigned to 'Home' (id={home_id})")
    else:
        print("   category_id already exists - skipped")

    cur.close()
    conn.close()
    print("\nMigration complete!")


if __name__ == "__main__":
    run_migration()