"""转岗复核差异引擎:每一条持有物记下「这次移动对它说了什么」,以及说不出来时的诚实沉默。

迁移 610 加了 ``baseline_source`` / ``delta_items`` 两列并把它们钉在
``('none', 0)`` —— 那是闸,不是引擎。这一条加的是引擎写下的**working**:

| 列 | 在哪张表 | 是什么 |
|---|---|---|
| ``delta_kind`` | items | 这条持有物的判定(见 ``mover_delta``):``policy_unexpected`` / ``provenance_stale`` 计入 delta;``policy_expected`` / ``provenance_current`` 是「查过了,没问题」;``peer_rare`` / ``peer_common`` 只排序不指控;``unknown`` 是「没有任何信号能就它说话」 |
| ``delta_evidence`` | items | 为什么是这个判定 —— 命中的策略、授予时这个人在哪个部门、新同侪里多少人也有。**一个对某人权限下判断却不给理由的界面不值得展示** |
| ``delta_summary`` | reviews | 引擎的全部账:逐 kind 计数、每个信号覆盖了包裹的多大比例、这次移动开出的开通缺口数 |
| ``delta_computed_at`` | reviews | 算的时间。答案取决于**算的那一刻**的策略表和组织树,两者都会动;读时重算等于悄悄改写历史 |

🔴 **`unknown` 与「没问题」必须分得开,这是加 `delta_kind` 而不是只加一个布尔的全部理由。**
生产上 ``provision_policies`` 是空表、``role_change`` 事件从 2026-07-13 才有,
所以今天绝大多数持有物只能落 ``unknown``。一个布尔会把它们全记成 false =
「不需要关注」,而正确的读法是「我们答不上来」—— 这两句话对着一份权限清单
说出来,分量完全不同。``coverage`` 存的就是这件事:delta=0 且 coverage=0.02
和 delta=0 且 coverage=1.0 是两个结论。

四列全可空 / 带默认值,旧代码不写它们照样 INSERT。索引只加一条
``(review_id, delta_kind)`` —— 包裹页要按判定分组,而 items 表在生产上
一份包裹就有几百行。
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "611"
down_revision = "610"
branch_labels = None
depends_on = None

REVIEWS = "hr_role_change_reviews"
ITEMS = "hr_role_change_review_items"


def _has_table(conn, name: str) -> bool:
    return sa.inspect(conn).has_table(name)


def _has_column(conn, table: str, column: str) -> bool:
    if not _has_table(conn, table):
        return False
    return any(c["name"] == column for c in sa.inspect(conn).get_columns(table))


def _has_index(conn, table: str, name: str) -> bool:
    if not _has_table(conn, table):
        return False
    return any(i["name"] == name for i in sa.inspect(conn).get_indexes(table))


def upgrade() -> None:
    conn = op.get_bind()

    if _has_table(conn, REVIEWS):
        if not _has_column(conn, REVIEWS, "delta_summary"):
            op.add_column(REVIEWS, sa.Column("delta_summary", sa.JSON))
        if not _has_column(conn, REVIEWS, "delta_computed_at"):
            op.add_column(
                REVIEWS,
                sa.Column("delta_computed_at", sa.DateTime(timezone=True)),
            )

    if _has_table(conn, ITEMS):
        if not _has_column(conn, ITEMS, "delta_kind"):
            op.add_column(ITEMS, sa.Column("delta_kind", sa.String(24)))
        if not _has_column(conn, ITEMS, "delta_evidence"):
            op.add_column(ITEMS, sa.Column("delta_evidence", sa.JSON))
        # The packet page groups by verdict, and one production packet is
        # 238 rows. Deliberately NOT a unique index and NOT covering
        # ``decision`` — the existing ``(review_id, decision)`` index
        # already serves the "what is left to answer" query.
        if not _has_index(conn, ITEMS, "ix_hr_rc_review_items_delta"):
            op.create_index(
                "ix_hr_rc_review_items_delta", ITEMS,
                ["review_id", "delta_kind"],
            )

    # Deliberately NO backfill.
    #
    # Every existing item would have to be classified against today's
    # policy table and today's org tree, and the answer for a move that
    # happened in July is not today's answer. Leaving them NULL is the
    # honest state: the page reads NULL as "not yet computed" and says
    # so, and a rescan (or the next packet build) fills it in with a
    # timestamp attached. Writing a confident ``unknown`` on 223
    # historical rows would look like a verdict.


def downgrade() -> None:
    conn = op.get_bind()
    if _has_index(conn, ITEMS, "ix_hr_rc_review_items_delta"):
        op.drop_index("ix_hr_rc_review_items_delta", table_name=ITEMS)
    if _has_column(conn, ITEMS, "delta_evidence"):
        op.drop_column(ITEMS, "delta_evidence")
    if _has_column(conn, ITEMS, "delta_kind"):
        op.drop_column(ITEMS, "delta_kind")
    if _has_column(conn, REVIEWS, "delta_computed_at"):
        op.drop_column(REVIEWS, "delta_computed_at")
    if _has_column(conn, REVIEWS, "delta_summary"):
        op.drop_column(REVIEWS, "delta_summary")