"""Record when each access-drift control became measurable.

Revision ID: 511
Revises: 510
Create Date: 2026-08-17

Two of the access-drift detectors were, on production data, reporting a
past they could not have observed:

* ``privileged_role_without_grant_record`` — 617 would-be findings, and
  the detector's own docstring already said why: "either the grant
  predates migration 484 (**expected, and harmless**) or something wrote
  outside the ORM". The ledger deliberately does not backfill (see 484:
  reconstructing intervals from 829 unstructured action shapes would be
  manufacturing evidence), so on day one EVERY holding lacks an interval.
  The detector named the false positive and then opened a ticket for it.
* ``offboarding_without_evidence`` — 70 would-be findings over a 90-day
  lookback, while the auto-archive path shipped today. Every completion
  in that window predates the feature and has no archive by
  construction.

A control cannot judge what happened before it existed. These two keys
record the instant each one became measurable; the detectors skip
anything older and, when a key is missing, judge nothing at all rather
than fall back to judging everything.

Deliberately NOT derived from the data:
``min(auth_user_role_cycles.created_at)`` would work for the ledger but
is exactly wrong for the archive — zero bundles is the broken state the
detector exists to catch, so an empty-table epoch would silence it
forever. A recorded deploy instant is the honest source for both.

Seeded to ``NOW()`` at upgrade time rather than a hardcoded date: this
migration runs when the code that populates both paths goes live, which
is the definition being recorded. Slightly conservative for the role
ledger (484 landed a few hours earlier), and erring toward "not yet
measurable" only ever drops findings that were false by construction.
"""
from __future__ import annotations

import json

import sqlalchemy as sa

from alembic import op

revision = "511"
down_revision = "510"
branch_labels = None
depends_on = None


_SETTINGS = (
    (
        "access_drift.role_ledger_epoch",
        "auth_user_role_cycles 开始记账的时刻。此前的授权没有区间是设计使然"
        "(见迁移 484:不回填),不是漂移 —— 探测器只判这个时刻之后的授权。",
    ),
    (
        "access_drift.offboarding_archive_epoch",
        "离职自动归档上线的时刻。此前完成的离职没有归档是结构使然,"
        "不是归档失败 —— 探测器只判这个时刻之后完成的离职。",
    ),
)


def upgrade() -> None:
    conn = op.get_bind()
    # The instant this migration runs IS the instant both paths become
    # authoritative, so read it from the database rather than the app
    # clock — a worker in another timezone must not shift the boundary.
    #
    # ``UTC_TIMESTAMP`` rather than ``NOW``: the reader labels this value
    # UTC, and ``NOW()`` follows the server's timezone. Production is
    # currently UTC (verified: ``NOW(6) == UTC_TIMESTAMP(6)``, both
    # matching the app clock), so today the two are identical — which is
    # exactly why the dependency is worth making explicit instead of
    # leaving a boundary that silently shifts if that ever changes.
    now = conn.execute(sa.text("SELECT UTC_TIMESTAMP(6)")).scalar()
    stamp = now.isoformat() if hasattr(now, "isoformat") else str(now)
    for key, description in _SETTINGS:
        existing = conn.execute(
            sa.text("SELECT 1 FROM platform_settings WHERE `key` = :k"),
            {"k": key},
        ).first()
        if existing:
            # Re-running must not move an epoch that has already been
            # in force: that would re-open every finding it had already
            # correctly excluded.
            continue
        conn.execute(
            sa.text(
                "INSERT INTO platform_settings (`key`, value, description) "
                "VALUES (:k, :v, :d)"
            ),
            {"k": key, "v": json.dumps(stamp, ensure_ascii=False), "d": description},
        )


def downgrade() -> None:
    conn = op.get_bind()
    for key, _description in _SETTINGS:
        conn.execute(
            sa.text("DELETE FROM platform_settings WHERE `key` = :k"),
            {"k": key},
        )