"""Retro-mark stale late-ingest onboarding processes as ``backfilled``.

Revision ID: 396
Revises: 395
Create Date: 2026-07-08

Companion to the code fix that wires the long-dormant
``onboarding_processes.backfilled`` flag onto the live path (it had only
ever been set by the one-time migration-229 historical seed). The bug:
when an existing / long-tenured employee is first synced into Helix long
after their start date (HRIS-late detection / bulk backfill), their genuine
hire_date flows in years in the past, and — with no "too far past" gate —
the onboarding pipeline treated it like a same-day new hire. That paged
every IT admin「已入职 N 天仍未配齐」and auto-minted a fresh Google mailbox
the person already had (the 2026-07-08 roger.zhou03 incident: 杨懿梅 Julia,
SF P01572, hire_date 2021-07-21, first synced 2026-07-08 → days_past 1813 →
instant SLA breach + a welcome-failed→suspended roger.zhou03 mailbox).

Going forward ``start_process`` marks such rows ``backfilled`` at birth and
``hr_sync_coordinator`` skips their provisioning. This migration applies the
same normalization to rows that already exist: any OPEN process whose row
was CREATED more than 90 days after its hire_date is, by definition, a
late-ingest backfill — not a slow-but-real onboarding (whose process is
created near the hire date). Marking it ``backfilled`` removes it from the
escalation dispatchers, T+7 compliance verify, and analytics, while keeping
it visible in the cockpit as a 补录 row.

The created_at−hire_date gap (not hire_date−today) is deliberate: it
distinguishes "ingested late" from "onboarding genuinely stalled for
months", so a real hire from a few months ago whose onboarding is still
open and overdue keeps paging IT.

Idempotent — only flips rows currently ``backfilled = 0``.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "396"
down_revision = "395"
branch_labels = None
depends_on = None

# Mirror of app.core.working_hours.STALE_HIRE_BACKFILL_DAYS. Kept as a
# literal here so the migration is self-contained (no app import at
# migration time), but the two must stay in step.
STALE_HIRE_BACKFILL_DAYS = 90

_OPEN_STATUSES = ("scanning", "preparing", "ready", "live")


def _table_exists(bind, table: str) -> bool:
    row = bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.TABLES "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t"
        ),
        {"t": table},
    ).fetchone()
    return row is not None


def upgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind, "onboarding_processes"):
        return
    bind.execute(
        sa.text(
            "UPDATE onboarding_processes "
            "SET backfilled = 1, updated_at = NOW() "
            "WHERE backfilled = 0 "
            "  AND status IN :open_statuses "
            "  AND hire_date IS NOT NULL "
            "  AND DATEDIFF(created_at, hire_date) > :max_age"
        ).bindparams(
            sa.bindparam("open_statuses", expanding=True),
        ),
        {
            "open_statuses": list(_OPEN_STATUSES),
            "max_age": STALE_HIRE_BACKFILL_DAYS,
        },
    )


def downgrade() -> None:
    # Not reversible: the pre-migration ``backfilled`` value of the touched
    # rows is not recorded (all were 0 by the WHERE clause, but re-clearing
    # them would also clear any set by the new live path after deploy). This
    # is a data-normalization backfill, so downgrade is a no-op.
    pass