"""Tri-state MFA on cmdb_iam_users.

Revision ID: 464
Revises: 463
Create Date: 2026-08-12

``mfa_enabled`` is a NOT NULL bool, so a provider that cannot read a
user's MFA state had exactly one way to say so: ``False`` — which the
compliance view renders identically to "this user has no MFA". The
Tencent provider hard-coded ``False`` for every CAM user on a comment
claiming per-user MFA is only reachable through unstable helpers. It is
not: ``DescribeSafeAuthFlagColl(SubUin=…)`` returns it directly
(verified against prod 2026-08-12 — 冯帅 and 李琳 have login+action MFA
on, 左武辉 login-only, the ``tiup-server`` service account none).

So every one of those 25 rows was asserting a fact nobody had asked the
provider for. ``mfa_state`` lets a provider say ``unknown``, which is a
different remediation from ``disabled``.

Backfill is deliberately conservative: existing rows get ``unknown``
rather than being mapped from the bool they carry, because that bool is
exactly the value we no longer trust. The next IAM sync overwrites it
with a real answer per provider.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "464"
down_revision = "463"
branch_labels = None
depends_on = None

TABLE = "cmdb_iam_users"
COLUMN = "mfa_state"


def _column_exists(bind, table: str, column: str) -> bool:
    return bool(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())


def upgrade() -> None:
    bind = op.get_bind()
    if _column_exists(bind, TABLE, COLUMN):
        return
    op.add_column(
        TABLE, sa.Column(COLUMN, sa.String(16), nullable=True),
    )
    # Not derived from mfa_enabled — that bool is the thing being
    # distrusted. One IAM sync per integration replaces this.
    bind.execute(
        sa.text(f"UPDATE {TABLE} SET {COLUMN} = 'unknown' "
                f"WHERE {COLUMN} IS NULL AND deleted_at IS NULL"),
    )


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