"""Lightweight, idempotent schema upgrades for tables created by an older release.

create_all() only creates *missing tables*; it never alters existing ones.
Every statement here is safe to run on every start. When the schema grows
more complex, move to Alembic.
"""
import logging

from sqlalchemy.ext.asyncio import AsyncEngine

log = logging.getLogger(__name__)

POSTGRES_UPGRADES = [
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS streak INTEGER NOT NULL DEFAULT 0",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS best_streak INTEGER NOT NULL DEFAULT 0",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS last_active_on DATE",
    "ALTER TABLE materials ADD COLUMN IF NOT EXISTS file_id VARCHAR(512)",
    "ALTER TABLE materials ADD COLUMN IF NOT EXISTS file_type VARCHAR(16) NOT NULL DEFAULT 'document'",
    "ALTER TABLE materials ADD COLUMN IF NOT EXISTS downloads INTEGER NOT NULL DEFAULT 0",
    "ALTER TABLE materials ADD COLUMN IF NOT EXISTS uploader_id BIGINT",
    "ALTER TABLE materials ALTER COLUMN library_chat_id DROP NOT NULL",
    "ALTER TABLE materials ALTER COLUMN library_message_id DROP NOT NULL",
    "CREATE INDEX IF NOT EXISTS ix_materials_course_id ON materials (course_id)",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS active_days INTEGER NOT NULL DEFAULT 0",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS week_key VARCHAR(8)",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS week_xp INTEGER NOT NULL DEFAULT 0",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS day_key VARCHAR(10)",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS day_xp INTEGER NOT NULL DEFAULT 0",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS trust INTEGER NOT NULL DEFAULT 100",
    "ALTER TABLE materials ADD COLUMN IF NOT EXISTS content_hash VARCHAR(64)",
    "ALTER TABLE materials ADD COLUMN IF NOT EXISTS text_sig BIGINT",
    "ALTER TABLE materials ADD COLUMN IF NOT EXISTS pages INTEGER",
    "ALTER TABLE materials ADD COLUMN IF NOT EXISTS size INTEGER",
    "ALTER TABLE materials ADD COLUMN IF NOT EXISTS source_name VARCHAR(256)",
    "ALTER TABLE materials ADD COLUMN IF NOT EXISTS status VARCHAR(8) NOT NULL DEFAULT 'ok'",
    "ALTER TABLE materials ADD COLUMN IF NOT EXISTS orig_file_id VARCHAR(512)",
    "ALTER TABLE materials ADD COLUMN IF NOT EXISTS watermarked BOOLEAN NOT NULL DEFAULT FALSE",
    "CREATE INDEX IF NOT EXISTS ix_materials_content_hash ON materials (content_hash)",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS source VARCHAR(24)",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS pending_link VARCHAR(16)",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS share_prompt_day VARCHAR(10)",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS credit_name BOOLEAN NOT NULL DEFAULT FALSE",
    "ALTER TABLE materials ADD COLUMN IF NOT EXISTS credit VARCHAR(40)",
    "UPDATE users SET gate_passed = TRUE WHERE gate_passed = FALSE AND year IS NOT NULL",
    "ALTER TABLE users ADD COLUMN IF NOT EXISTS gate_missing VARCHAR(80)",
    "ALTER TABLE referrals ADD COLUMN IF NOT EXISTS qualified BOOLEAN NOT NULL DEFAULT FALSE",
]


async def run_migrations(engine: AsyncEngine) -> None:
    if engine.dialect.name != "postgresql":
        return  # local sqlite dev DB is created fresh by create_all
    for stmt in POSTGRES_UPGRADES:
        try:
            async with engine.begin() as conn:
                await conn.exec_driver_sql(stmt)
        except Exception:
            log.exception("Migration step failed: %s", stmt)