"""
Migration: Add game_topics table and game_topic_id column to game_levels.

Run from the backend/ directory:
    python migrate_game_topics.py
"""
import asyncio
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()

    print("▶ Creating game_topics table...")
    cur.execute("""
        CREATE TABLE IF NOT EXISTS game_topics (
            id              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
            game_id         INT NOT NULL,
            name            VARCHAR(255) NOT NULL,
            slug            VARCHAR(255) NOT NULL,
            description     TEXT,
            location_name   VARCHAR(100),
            icon_url        VARCHAR(500),
            background_url  VARCHAR(500),
            color_hex       VARCHAR(7) DEFAULT '#4F46E5',
            sequence_order  INT DEFAULT 1,
            is_active       TINYINT(1) DEFAULT 1,
            created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            CONSTRAINT fk_gt_game FOREIGN KEY (game_id)
                REFERENCES games(id) ON DELETE CASCADE,
            CONSTRAINT uk_game_topic_slug UNIQUE (game_id, slug)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    """)
    print("   ✅ game_topics table ready")

    print("▶ Adding game_topic_id column to game_levels (if missing)...")
    cur.execute("""
        SELECT COUNT(*) FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = %s
          AND TABLE_NAME = 'game_levels'
          AND COLUMN_NAME = 'game_topic_id'
    """, (settings.DB_NAME,))
    if cur.fetchone()[0] == 0:
        cur.execute("""
            ALTER TABLE game_levels
            ADD COLUMN game_topic_id INT NULL
                AFTER game_id,
            ADD CONSTRAINT fk_gl_game_topic
                FOREIGN KEY (game_topic_id)
                REFERENCES game_topics(id)
                ON DELETE SET NULL;
        """)
        print("   ✅ game_topic_id column added")
    else:
        print("   ℹ️  game_topic_id column already exists — skipped")

    print("▶ Making game_levels.topic_level_id nullable (if not already)...")
    cur.execute("""
        SELECT IS_NULLABLE FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = %s
          AND TABLE_NAME = 'game_levels'
          AND COLUMN_NAME = 'topic_level_id'
    """, (settings.DB_NAME,))
    row = cur.fetchone()
    if row and row[0] == 'NO':
        cur.execute("""
            ALTER TABLE game_levels
            MODIFY COLUMN topic_level_id INT NULL;
        """)
        print("   ✅ topic_level_id is now nullable")
    else:
        print("   ℹ️  topic_level_id already nullable — skipped")

    conn.commit()
    conn.close()
    print("\n✅ Migration complete!")


if __name__ == "__main__":
    run_migration()