"""Self-healing repair for the 知识竞速 trivia bank + session variant column.

WHY THIS EXISTS: on at least one deployed environment migration ``412``
was stamped in ``alembic_version`` while its body had NOT taken effect —
``arcade_trivia_questions`` was absent and ``arcade_game_sessions.variant``
was missing, even though ``alembic current`` reported a revision past 412
(413's ``correct_count`` and 414's phishing/spot tables WERE present). A
stamped-but-unapplied revision never re-runs, so the gap cannot be closed
by editing 412 — the arcade home 500'd on the missing ``variant`` column
and 知识竞速 could not build a round (no trivia bank).

This migration re-asserts exactly 412's schema effects, each guarded by an
existence check, so it is a no-op on every environment where 412 applied
cleanly (local, fresh installs) and a repair on any where it didn't. It
also re-seeds the trivia bank only when the table is created empty.

Idempotent: table / column / index / seed each guarded. ``created_by`` FK
matches identity_persons charset/collation via ``id_type_for`` (TiDB
error-3780 guard), same as 412.
"""
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 = "415"
down_revision = "414"
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 _index_exists(bind, table: str, index: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.STATISTICS "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t AND INDEX_NAME = :i"
        ),
        {"t": table, "i": index},
    ).scalar() is not None


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

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

    # (2) trivia bank table (+ seed when freshly created).
    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,
        )
        seed_now = True
    if not _index_exists(bind, TRIVIA, "ix_arcade_trivia_status_cat"):
        op.create_index("ix_arcade_trivia_status_cat", TRIVIA, ["status", "category"])

    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:
    # This is a forward-only repair; it deliberately does NOT drop the
    # trivia bank or variant column on downgrade (412 owns that lifecycle).
    pass