"""Add last_verified_at to identity_sync_state + identity_sync_department_state.

Revision ID: 398
Revises: 397
Create Date: 2026-07-13

Enables the periodic drift re-verification of the outbound sync sweep.
Before this, an already-linked person/dept whose HRIS-rendered payload
hash was unchanged was hash-skipped WITHOUT any live vendor read — so a
field edited on the vendor side outside Helix (a manually-changed Feishu
email / name / dept) was never detected or corrected (Authing, the
predecessor, re-compared live vendor state every run). ``last_verified_at``
records the last time the runner ACTUALLY talked to the vendor for the
row; the sweep now re-pushes a row whose ``last_verified_at`` is older than
``_DRIFT_REVERIFY_INTERVAL`` even when the payload hash matches, so the
runner re-asserts the Helix-managed fields and corrects drift.

Nullable DateTime, no FK. Backfilled to ``last_synced_at`` so a freshly-synced
population isn't treated as never-verified (which would re-push everyone on the
first post-deploy sweep — a thundering herd). NOTE: the backfill alone does not
truly stagger a nightly-synced target (all rows share ~one ``last_synced_at``);
the actual spread comes from the per-row jitter in ``_drift_reverify_stale``
(``_DRIFT_REVERIFY_JITTER``), which fans each row's effective window across a
few days so re-verify is a rolling wave, not a weekly spike.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "398"
down_revision = "397"
branch_labels = None
depends_on = None

_TABLES = ("identity_sync_state", "identity_sync_department_state")


def _column_exists(bind, table: str, column: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.COLUMNS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "AND TABLE_NAME = :t AND COLUMN_NAME = :c",
        ),
        {"t": table, "c": column},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()
    for table in _TABLES:
        if not _column_exists(bind, table, "last_verified_at"):
            op.add_column(
                table,
                sa.Column("last_verified_at", sa.DateTime(timezone=True), nullable=True),
            )
        # Backfill = last_synced_at so recently-synced rows aren't treated
        # as stale on the first post-deploy sweep (staggers re-verify).
        bind.execute(
            sa.text(
                f"UPDATE {table} SET last_verified_at = last_synced_at "
                "WHERE last_verified_at IS NULL AND last_synced_at IS NOT NULL",
            ),
        )


def downgrade() -> None:
    bind = op.get_bind()
    for table in _TABLES:
        if _column_exists(bind, table, "last_verified_at"):
            op.drop_column(table, "last_verified_at")