"""审批流程台账:「飞书后台还没改」的推送时间戳。

Helix can re-point a flow's owner. The Feishu approval console cannot be
re-pointed from Helix — the v4 definition surface is create / get /
subscribe, with **no update verb**, so 流程管理员 is editable only by a
human in the console. Every definition-level call in
``app/approval/feishu_client.py`` is a read; there is nothing to call.

That makes the half-year confirmation round a source of guaranteed
disagreement. An owner presses 「转交给 X」, the register updates, the
console still names the predecessor — and the console is the side that
decides who may edit the flow, read its instance data and receive its
admin notices. The successor holds accountability here and no rights
there, and nobody is told.

Drift itself is **not** stored. It is derived on every read from
``feishu_admins`` vs ``owner_person_id``
(:func:`serializers.feishu_admin_drift`), so the nightly sweep that
re-reads the console clears a fixed row with no write of ours — a
stored flag would need its own clearing path and would eventually
publish drift that was fixed weeks ago.

This one column is bookkeeping about the PUSH, not a claim about the
world: when IT was last told about this specific row. It exists so a
standing backlog is re-raised on a cadence (``DRIFT_RENAG_DAYS``, 14)
instead of the two failure modes either side of it — once-and-forget
becomes a card scrolled past in June, and daily trains IT to mute the
whole channel. The drift job nulls it again the moment a row stops
drifting, which is what makes a re-broken flow a new finding rather
than a silently suppressed one.

Add-column only, ``information_schema`` guarded on ``DATABASE()``,
unindexed — so the downgrade is a plain drop and TiDB has no index
rebuild to refuse.

Revision ID: 564
Revises: 563
"""
from __future__ import annotations

import json as _json
import uuid

import sqlalchemy as sa

from alembic import op

revision = "564"
down_revision = "563"
branch_labels = None
depends_on = None

TABLE = "registry_approval_flows"
COLUMN = "feishu_admin_drift_notified_at"
JOB_KEY = "approval_registry.feishu_admin_drift"

_JOB_DESC = (
    "每日 09:00。台账能改负责人,飞书审批后台不能 —— 审批 v4 的定义接口只有"
    "创建/读取/订阅,没有更新,流程管理员没有写接口。所以每次在 Helix 里换"
    "负责人(半年度确认里点「转交」、离职交接、IT 重新指派)都必然留下一处"
    "不一致:台账记新人,后台还点着原来那位 —— 而后台那份名单才决定谁能编辑"
    "流程、看实例数据、收管理员通知。这个任务修不了数据,它只保证这处不一致"
    "不是隐形的:给在职 global_admin 发一张汇总卡(不是每条一张),列出"
    "「台账负责人 vs 飞书流程管理员」并直接给后台入口。漂移每次读时算出来,"
    "不落库 —— 后台改好后当晚 03:30 的同步一读就自动消失。落库的只有上次"
    "告知时间,用来 14 天再提醒;不再漂移的行会清空它。"
)


def _has_column(conn: sa.engine.Connection, column: str) -> bool:
    # DATABASE()-qualified: an unqualified information_schema probe
    # matches a same-named table in ANY schema, and this box runs
    # hundreds of helix_test_* schemas side by side.
    return bool(
        conn.execute(
            sa.text(
                "SELECT COUNT(*) FROM information_schema.columns "
                "WHERE table_schema = DATABASE() "
                "AND table_name = :t AND column_name = :c",
            ),
            {"t": TABLE, "c": column},
        ).scalar()
    )


def upgrade() -> None:
    conn = op.get_bind()
    if not sa.inspect(conn).has_table(TABLE):
        return
    if not _has_column(conn, COLUMN):
        op.add_column(
            TABLE,
            sa.Column(
                COLUMN,
                sa.DateTime(timezone=True),
                nullable=True,
                comment=(
                    "Last time IT was told this row's owner and its "
                    "Feishu 流程管理员 disagree. Bookkeeping about the "
                    "push only — drift is derived from feishu_admins on "
                    "every read, so fixing the console clears it with no "
                    "write here."
                ),
            ),
        )

    # The daily digest, seeded ENABLED. The backlog it reports exists the
    # moment this ships — every hand-over made since the register opened
    # is already drifting — and a governance job nobody remembered to
    # switch on reports nothing. It cannot storm: at most one card per
    # operator, and a given flow is re-raised only every 14 days.
    #
    # Below the early return above on purpose: no register table means no
    # flows to compare, and a cron pointed at nothing is worse than an
    # absent one.
    conn.execute(
        sa.text(
            "INSERT INTO scheduled_jobs "
            "(id, name, display_name, description, handler_key, "
            " trigger_type, cron_expr, timezone, enabled, config, "
            " run_count, failure_count, created_at, updated_at) "
            "SELECT :id, :name, :display, :desc, :handler, 'cron', "
            ":cron, 'Asia/Shanghai', 1, :config, 0, 0, NOW(), NOW() "
            "FROM DUAL WHERE NOT EXISTS "
            "(SELECT 1 FROM scheduled_jobs WHERE handler_key = :handler)",
        ),
        {
            "id": str(uuid.uuid4()),
            "name": JOB_KEY,
            "display": "审批流程台账 · 飞书后台待同步提醒",
            "desc": _JOB_DESC,
            "handler": JOB_KEY,
            # 09:00, deliberately half an hour before the round's
            # escalation at 09:30: on a day both fire, IT reads 「后台
            # 还没改」 before 「有人还没回」, and the first is the one
            # the second's transfers created.
            "cron": "0 9 * * *",
            "config": _json.dumps({"renag_days": 14}),
        },
    )


def downgrade() -> None:
    conn = op.get_bind()
    conn.execute(
        sa.text("DELETE FROM scheduled_jobs WHERE handler_key = :k"),
        {"k": JOB_KEY},
    )
    if not sa.inspect(conn).has_table(TABLE):
        return
    if _has_column(conn, COLUMN):
        op.drop_column(TABLE, COLUMN)