"""安全宣贯:素材挂小测题 + 答题记录。

## 「点了确认」和「答对了题」不是同一个证据

前四个 PR 里,完成的最强档是「点了我已阅读并理解」。那已经是审计一般会认的
那一条,但它只能证明**点过**,证不了**理解**。给素材挂两三道题、答对才算完
成,是唯一能把后者也写下来的做法 —— 而这恰好也是员工最不反感的一档:它把
「你读了吗」这个无法回答的问题,换成了一件几十秒就能做完、而且有反馈的事。

## 为什么是链接表,不是在题目上加一列 material_id

一道题可以既是某份素材的配套题,也是每日一题题库里的一员;`material_id` 一列
会把它锁死在一个素材上。链接表还顺带给了**顺序**(`sort_order`)和「同一道题
被几份素材复用」这两件事。

## `attempt_no` 不从 `created_at` 推

`created_at` 是秒级的 DATETIME,而这里最有价值的那个序列 —— 答错、看解释、马上
重来 —— 整个发生在一秒之内,按时间排根本分不出第一次和第二次。而「每人**第一次**
答错了哪几道题」正是这张表存在的理由(后面的尝试已经看过解释,统计进去会让每道题
都显得很简单)。所以序号显式存一列,在插入时按 (person, material) 现有行数 +1。

## `secaw_quiz_attempts` 记的是每一次尝试,不是最终状态

答错也是一条记录。**只留「最后通过了」会把这个域最有价值的一个信号丢掉** ——
哪几道题大量的人第一次答错,正是「这份材料这一段没讲清楚」的直接证据,而那
是安全团队下一次改稿的输入。`passed` 冗余存在行上而不是每次现算:一次尝试的
判定要用**当时**的正确答案,而题目之后会被编辑。

Revision ID: 600
Revises: 599

⚠️ 迁移号要对着 `origin/main` 抢,而且每次 push 之前重新抢一次。
"""
from __future__ import annotations

import sqlalchemy as sa

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

revision = "600"
down_revision = "599"
branch_labels = None
depends_on = None

LINKS = "secaw_material_questions"
ATTEMPTS = "secaw_quiz_attempts"
MATERIALS = "secaw_materials"
QUESTIONS = "secquiz_questions"


def _has_table(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 upgrade() -> None:
    bind = op.get_bind()
    person_fk = id_type_for("identity_persons")

    if not _has_table(bind, LINKS) and _has_table(bind, QUESTIONS):
        op.create_table(
            LINKS,
            sa.Column("id", sa.String(36), primary_key=True),
            sa.Column(
                "material_id", id_type_for(MATERIALS),
                sa.ForeignKey(f"{MATERIALS}.id", ondelete="CASCADE"),
                nullable=False,
            ),
            # CASCADE 也在这一侧:一道题被删除,它和素材的**关联**跟着消失
            # 是对的 —— 而已经发生过的**尝试**不受影响(见下),因为证据
            # 存的是当时的判定结果,不是一个指回题目的实时查询。
            sa.Column(
                "question_id", id_type_for(QUESTIONS),
                sa.ForeignKey(f"{QUESTIONS}.id", ondelete="CASCADE"),
                nullable=False,
            ),
            sa.Column(
                "sort_order", sa.Integer(), nullable=False, server_default="0",
            ),
            sa.Column(
                "created_at", sa.DateTime(timezone=True),
                nullable=False, server_default=sa.func.now(),
            ),
            sa.Column(
                "created_by_person_id", person_fk,
                sa.ForeignKey("identity_persons.id", ondelete="SET NULL"),
            ),
            sa.UniqueConstraint(
                "material_id", "question_id",
                name="uq_secaw_material_question",
            ),
        )
        op.create_index(
            "ix_secaw_material_questions_material", LINKS,
            ["material_id", "sort_order"],
        )

    if not _has_table(bind, ATTEMPTS):
        op.create_table(
            ATTEMPTS,
            sa.Column("id", sa.String(36), primary_key=True),
            sa.Column(
                "person_id", person_fk,
                sa.ForeignKey("identity_persons.id", ondelete="CASCADE"),
                nullable=False,
            ),
            sa.Column(
                "material_id", id_type_for(MATERIALS),
                sa.ForeignKey(f"{MATERIALS}.id"),
                nullable=False,
            ),
            # 这一次尝试时素材是第几版。素材之后会被编辑,而「他当时读的是
            # 哪一版、据此答的题」是争议的全部内容。
            sa.Column(
                "material_version", sa.Integer(), nullable=False,
                server_default="1",
            ),
            # (person, material) 内的第 N 次尝试。**刻意不从 created_at
            # 推**:那是秒级的 DATETIME,而这里最有价值的那个序列 ——
            # 答错、看解释、马上重来 —— 整个发生在一秒之内,按时间排根本
            # 分不出第一次和第二次。而「每人第一次答错了哪几道题」正是这
            # 张表存在的理由。
            sa.Column(
                "attempt_no", sa.Integer(), nullable=False, server_default="1",
            ),
            sa.Column("correct_count", sa.Integer(), nullable=False),
            sa.Column("total_count", sa.Integer(), nullable=False),
            # 冗余存,不现算:一次尝试的判定要用**当时**的正确答案,而题目
            # 之后会被编辑。
            sa.Column(
                "passed", sa.Boolean(), nullable=False, server_default="0",
            ),
            # 逐题明细 [{question_id, selected, correct, is_correct}] ——
            # 「哪几道题大量的人第一次答错」是「这份材料这一段没讲清楚」的
            # 直接证据,也是安全团队下一次改稿的输入。
            sa.Column("answers", sa.JSON()),
            sa.Column(
                "created_at", sa.DateTime(timezone=True),
                nullable=False, server_default=sa.func.now(),
            ),
        )
        # 「我通过了没有」的逐人读,以及参与看板的逐素材汇总。
        op.create_index(
            "ix_secaw_attempts_person_material", ATTEMPTS,
            ["person_id", "material_id", "passed"],
        )
        op.create_index(
            "ix_secaw_attempts_material", ATTEMPTS,
            ["material_id", "created_at"],
        )


def downgrade() -> None:
    bind = op.get_bind()
    if _has_table(bind, ATTEMPTS):
        op.drop_table(ATTEMPTS)
    if _has_table(bind, LINKS):
        op.drop_table(LINKS)