"""
Migration: Add subject_id column to game_topics table.

Run from the backend/ directory:
    python migrate_game_topic_subject.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()

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

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


if __name__ == "__main__":
    run_migration()