"""Bulk-expand the 知识竞速 trivia bank and the 安全每日一题 bank (~10×).

The starter seeds (migration 412 for trivia, 313/314 for the security quiz)
only run on table creation, so an existing environment never picks up new
curated questions. This migration inserts the full curated banks
idempotently — any question whose prompt is not already present is added,
so it is safe to re-run and safe on prod (the ~60 trivia / ~39 secquiz
already there are skipped).

Content lives as plain data modules:
  * trivia   → app/domains/arcade/trivia_seed.TRIVIA_SEED
  * security → app/domains/security_quiz/secquiz_seed.SECQUIZ_SEED
0 schema change — data only.
"""
from __future__ import annotations

import uuid

import sqlalchemy as sa

from alembic import op

revision = "417"
down_revision = "416"
branch_labels = None
depends_on = None

TRIVIA = "arcade_trivia_questions"
SECQUIZ = "secquiz_questions"


def _existing_prompts(bind, table: str) -> set[str]:
    rows = bind.execute(sa.text(f"SELECT prompt FROM {table}")).fetchall()  # noqa: S608 — table is a constant
    return {r[0] for r in rows}


def _insert_missing(bind, table: str, questions: list[dict], *, with_qtype: bool) -> int:
    existing = _existing_prompts(bind, table)
    cols = [
        sa.column("id", sa.String(36)),
        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)),
    ]
    if with_qtype:
        cols.insert(3, sa.column("qtype", sa.String(16)))

    rows = []
    seen: set[str] = set()
    for q in questions:
        prompt = q["prompt"]
        if prompt in existing or prompt in seen:
            continue
        seen.add(prompt)
        row = {
            "id": str(uuid.uuid4()),
            "prompt": prompt,
            "options": q["options"],
            "correct_key": q["correct_key"],
            "explanation": q.get("explanation"),
            "category": q["category"],
            "difficulty": q.get("difficulty", "normal"),
            "status": "active",
            "source": "seed",
        }
        if with_qtype:
            row["qtype"] = q.get("qtype", "single")
        rows.append(row)

    if rows:
        op.bulk_insert(sa.table(table, *cols), rows)
    return len(rows)


def upgrade() -> None:
    bind = op.get_bind()
    from app.domains.arcade.trivia_seed import TRIVIA_SEED
    from app.domains.security_quiz.secquiz_seed import SECQUIZ_SEED

    _insert_missing(bind, TRIVIA, TRIVIA_SEED, with_qtype=False)
    _insert_missing(bind, SECQUIZ, SECQUIZ_SEED, with_qtype=True)


def downgrade() -> None:
    # Data-only, additive, idempotent seed — nothing to reverse. Deleting
    # "seed" rows could remove questions admins have since curated/edited.
    pass