"""知识竞速 (Knowledge Rush) trivia bank + session variant column.

* ``arcade_trivia_questions`` — the GENERAL-KNOWLEDGE question pool (常识/
  历史/科学/地理/中华文化/生活), deliberately separate from the security
  quiz bank so the daily-quiz earn loop and the game spend loop stay
  distinct. Seeded with a curated, fact-checked starter set.
* ``arcade_game_sessions.variant`` — the game sub-mode a round was played
  in (e.g. the trivia category, or 'mixed'); indexed with game_key + score
  so PR3's per-category leaderboards are cheap.

created_by FK matches identity_persons charset/collation (TiDB error-3780
guard). Idempotent: table/column/seed each guarded.
"""
from __future__ import annotations

import uuid

import sqlalchemy as sa

from alembic import op
from app.core.migration_helpers import id_type_for, table_kw_for

revision = "412"
down_revision = "411"
branch_labels = None
depends_on = None

TRIVIA = "arcade_trivia_questions"
SESSIONS = "arcade_game_sessions"


def _table_exists(bind, name: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.TABLES "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :n"
        ),
        {"n": name},
    ).scalar() is not None


def _column_exists(bind, table: str, column: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.COLUMNS "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t AND COLUMN_NAME = :c"
        ),
        {"t": table, "c": column},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()
    pid = id_type_for("identity_persons")
    kw = table_kw_for("identity_persons")

    seed_now = False
    if not _table_exists(bind, TRIVIA):
        op.create_table(
            TRIVIA,
            sa.Column("id", pid, primary_key=True),
            sa.Column("prompt", sa.Text(), nullable=False),
            sa.Column("options", sa.JSON(), nullable=False),
            sa.Column("correct_key", sa.String(8), nullable=False),
            sa.Column("explanation", sa.Text(), nullable=True),
            sa.Column("category", sa.String(32), nullable=False, server_default="common_sense"),
            sa.Column("difficulty", sa.String(16), nullable=False, server_default="normal"),
            sa.Column("status", sa.String(16), nullable=False, server_default="active"),
            sa.Column("source", sa.String(16), nullable=False, server_default="seed"),
            sa.Column("created_by", pid, nullable=True),
            sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
            sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
            sa.ForeignKeyConstraint(["created_by"], ["identity_persons.id"], name="fk_arcade_trivia_created_by"),
            **kw,
        )
        op.create_index("ix_arcade_trivia_status_cat", TRIVIA, ["status", "category"])
        seed_now = True

    # variant column on sessions (per-category leaderboard dimension).
    if not _column_exists(bind, SESSIONS, "variant"):
        op.add_column(SESSIONS, sa.Column("variant", sa.String(32), nullable=True))
        op.create_index("ix_arcade_sess_variant_score", SESSIONS, ["game_key", "variant", "score"])

    if seed_now:
        from app.domains.arcade.trivia_seed import TRIVIA_SEED

        rows = [
            {
                "id": str(uuid.uuid4()),
                "prompt": q["prompt"],
                "options": q["options"],
                "correct_key": q["correct_key"],
                "explanation": q["explanation"],
                "category": q["category"],
                "difficulty": "normal",
                "status": "active",
                "source": "seed",
            }
            for q in TRIVIA_SEED
        ]
        op.bulk_insert(
            sa.table(
                TRIVIA,
                sa.column("id", pid),
                sa.column("prompt", sa.Text()),
                sa.column("options", sa.JSON()),
                sa.column("correct_key", sa.String(8)),
                sa.column("explanation", sa.Text()),
                sa.column("category", sa.String(32)),
                sa.column("difficulty", sa.String(16)),
                sa.column("status", sa.String(16)),
                sa.column("source", sa.String(16)),
            ),
            rows,
        )


def downgrade() -> None:
    bind = op.get_bind()
    if _column_exists(bind, SESSIONS, "variant"):
        try:
            op.drop_index("ix_arcade_sess_variant_score", table_name=SESSIONS)
        except Exception:  # noqa: BLE001 — index may not exist on partial state
            pass
        op.drop_column(SESSIONS, "variant")
    if _table_exists(bind, TRIVIA):
        op.drop_table(TRIVIA)