"""Classify a cloud IAM user as human or service account.

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

An IAM user with no ``owner_person_id`` is treated as an orphan on every
compliance surface, and two very different things land in that bucket:

  * a colleague Helix failed to match — act on it;
  * a robot that will never match — stop asking.

Prod Tencent (2026-08-12) has both: ``chao.wu@pingcap.cn`` and
``huangbaoling@pingcap.cn`` in the first group, ``tiup-server`` and
``operator-v1-cos-upload`` in the second. With nowhere to record the
difference the orphan list can never reach zero — and a list that can
never reach zero stops being read, which is how the two departed
employees' live AdministratorAccess went unnoticed for months.

No backfill. Classification is a human statement about an account, and
inferring it from a name pattern here is exactly the kind of guess that
would then be displayed as if someone had checked. ``ConsoleLogin=0``
(now persisted by the Tencent provider) is offered as a *suggestion* in
the UI, never written on the user's behalf.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op
from app.core.migration_helpers import id_type_for

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

TABLE = "cmdb_iam_users"
COLUMNS = (
    "account_kind",
    "account_kind_note",
    "account_kind_set_by_person_id",
    "account_kind_set_at",
)


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 not _column_exists(bind, TABLE, "account_kind"):
        op.add_column(
            TABLE, sa.Column("account_kind", sa.String(16), nullable=True),
        )
    if not _column_exists(bind, TABLE, "account_kind_note"):
        op.add_column(
            TABLE, sa.Column("account_kind_note", sa.Text(), nullable=True),
        )
    if not _column_exists(bind, TABLE, "account_kind_set_by_person_id"):
        op.add_column(
            TABLE,
            sa.Column(
                "account_kind_set_by_person_id",
                id_type_for("identity_persons"),
                nullable=True,
            ),
        )
        op.create_foreign_key(
            "fk_iam_user_account_kind_actor",
            TABLE,
            "identity_persons",
            ["account_kind_set_by_person_id"],
            ["id"],
            ondelete="SET NULL",
        )
    if not _column_exists(bind, TABLE, "account_kind_set_at"):
        op.add_column(
            TABLE,
            sa.Column(
                "account_kind_set_at",
                sa.DateTime(timezone=True),
                nullable=True,
            ),
        )


def downgrade() -> None:
    bind = op.get_bind()
    if _column_exists(bind, TABLE, "account_kind_set_by_person_id"):
        op.drop_constraint(
            "fk_iam_user_account_kind_actor", TABLE, type_="foreignkey",
        )
    for column in COLUMNS:
        if _column_exists(bind, TABLE, column):
            op.drop_column(TABLE, column)