"""Add ``identity_persons.hris_absent_since`` — future-hire absence debounce.

Revision ID: 421
Revises: 420
Create Date: 2026-07-20

The daily ``hris.absent_person_check`` future-hire cross-check
(scheduled_jobs.handlers) soft-deleted a future hire the moment SF's
``get_users`` failed to return their userId on a SINGLE run — a lone absence
(the P10019 shape) passed straight through with no per-person debounce. SF
Onboarding pre-hires flicker in/out of ``get_users`` while their requisition
is in flux, so a transient one-tick absence tombstoned a REAL pre-hire
(P10019 陈建华: absent once → soft-deleted → SF returned him again the next
day). Destroying a row on a single failed poll is the anti-pattern.

This column records when a future hire was FIRST seen absent. The cross-check
now only soft-deletes after the absence has persisted past a grace window
(≥ 2 consecutive daily runs); a hire SF returns again clears the marker. No
backfill (NULL = "not currently in a grace window", the correct default for
every existing row).

Idempotent: guarded by an information_schema column-exists check so a re-run
(or a prod ``stamp``-then-``upgrade`` recovery) is safe.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "421"
down_revision = "420"
branch_labels = None
depends_on = None


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 LIMIT 1"
        ),
        {"t": table, "c": column},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()
    if not _column_exists(bind, "identity_persons", "hris_absent_since"):
        op.add_column(
            "identity_persons",
            sa.Column(
                "hris_absent_since",
                sa.DateTime(timezone=True),
                nullable=True,
            ),
        )


def downgrade() -> None:
    bind = op.get_bind()
    if _column_exists(bind, "identity_persons", "hris_absent_since"):
        op.drop_column("identity_persons", "hris_absent_since")