"""``onboarding_processes.hard_ready_at`` — BLOCKER-dims-clear timestamp.

Review-r1 finding: the Day-1 readiness scorecard used ``ready_at``,
which the state machine stamps only when EVERY pending blocker —
including advisory dims like ``training_ack`` — first hits zero. A new
hire generally can't acknowledge 入职须知 before they have an account,
so one required ack item drags the whole metric to ~0% forever; the
metric measured "did the hire click confirm before Day-1", not IT
readiness.

``hard_ready_at`` is stamped (once) when the pending BLOCKER-severity
count first hits zero — the same hard-gap口径 as SLA / escalation
(#1027). Backfill: ``ready_at`` implies all dims (hence all hard dims)
were clear, so it is a valid conservative stamp for existing rows.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "280"
down_revision = "279"
branch_labels = None
depends_on = None

TABLE = "onboarding_processes"
COL = "hard_ready_at"


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()
    if not _column_exists(bind, TABLE, COL):
        op.add_column(
            TABLE,
            sa.Column(COL, sa.DateTime(timezone=True), nullable=True),
        )
        # Conservative backfill: ready_at ⇒ hard-ready (all dims clear
        # includes all hard dims). Later real stamps will be earlier or
        # equal for new rows; existing rows keep this upper bound.
        bind.execute(
            sa.text(
                f"UPDATE {TABLE} SET {COL} = ready_at "
                f"WHERE {COL} IS NULL AND ready_at IS NOT NULL"
            )
        )


def downgrade() -> None:
    bind = op.get_bind()
    if _column_exists(bind, TABLE, COL):
        op.drop_column(TABLE, COL)