"""Separate "the provider said no" from "the provider could not answer" on MFA rows.

Revision ID: 517
Revises: 516
Create Date: 2026-08-18

``auditor_mfa_status.is_enrolled`` / ``is_enforced`` are NOT NULL booleans, so
every provider that cannot answer had to write ``False`` — indistinguishable
from an observed non-compliance. In production all 528 Atlassian rows carry
``raw_payload.policy_status='policy_absent'`` (Atlassian exposes no per-user 2FA
attribute and the org authentication policy is unreadable), and that unknown was
consumed as failure by six separate surfaces: the health metric
(``0.0%`` → 30/100), the findings worklist (412 unactionable items), the
per-identity risk score (528 of 570 people penalised), the ``mfa_coverage`` ISMS
control (``fail — 0/412 enrolled``), the insights headline, and the ISMS
assessment evidence table.

Two nullable flags rather than a NOT NULL default: NULL is the honest value for
rows written before this migration, and every consumer reads NULL as "not
observed" — which drops the row out of coverage denominators instead of turning
it into a false accusation. **No backfill on purpose.** The daily
``auditor.mfa_sync_daily`` run restates every row with what the upstream API
actually answered, so deriving today's values from ``raw_payload`` here would
only duplicate that, and would encode one snapshot's provider mix into the
schema history.

Nullable-add on an existing table is TiDB-safe (no default rewrite, no
generated-column dependency, no FK).
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op

revision = "517"
down_revision = "516"
branch_labels = None
depends_on = None

_TABLE = "auditor_mfa_status"
_COLUMNS = ("enrollment_observed", "enforcement_observed")


def _existing() -> set[str]:
    bind = op.get_bind()
    return {
        row[0]
        for row in bind.execute(
            sa.text(
                # Schema-qualified via DATABASE(): an unqualified
                # information_schema guard matches same-named tables in
                # every other schema on the instance (test DBs included).
                "SELECT column_name FROM information_schema.columns "
                "WHERE table_schema = DATABASE() AND table_name = :t"
            ),
            {"t": _TABLE},
        )
    }


def upgrade() -> None:
    present = _existing()
    for name in _COLUMNS:
        if name in present:
            continue
        op.add_column(_TABLE, sa.Column(name, sa.Boolean(), nullable=True))


def downgrade() -> None:
    present = _existing()
    for name in _COLUMNS:
        if name in present:
            op.drop_column(_TABLE, name)