from app.database import engine
from sqlalchemy import text

def migrate():
    with engine.connect() as conn:
        # Questions table
        conn.execute(text("ALTER TABLE questions ADD COLUMN IF NOT EXISTS difficulty VARCHAR(20) DEFAULT 'medium'"))
        conn.execute(text("ALTER TABLE questions ADD COLUMN IF NOT EXISTS question_en TEXT NULL"))
        conn.execute(text("ALTER TABLE questions ADD COLUMN IF NOT EXISTS answer_en VARCHAR(255) NULL"))
        conn.execute(text("ALTER TABLE questions ADD COLUMN IF NOT EXISTS options_en TEXT NULL"))
        conn.execute(text("ALTER TABLE questions ADD COLUMN IF NOT EXISTS hint_en TEXT NULL"))

        # Quiz sessions table
        conn.execute(text("ALTER TABLE quiz_sessions ADD COLUMN IF NOT EXISTS lang VARCHAR(10) DEFAULT 'de'"))
        conn.execute(text("ALTER TABLE quiz_sessions ADD COLUMN IF NOT EXISTS difficulty VARCHAR(20) DEFAULT 'all'"))
        conn.commit()
    print("Migration v3 successful: added difficulty, multilingual columns, and session preferences.")

if __name__ == "__main__":
    migrate()