"""Add governance-tier columns to cmdb_cloud_roles.

Revision ID: 263
Revises: 262
Create Date: 2026-06-06

Part of the unified cloud-role governance framework. Each requestable
cloud role is classified into a governance tier
(normal/elevated/restricted/ungrantable — see
``app/integrations/cloud_role_governance.py``) that later drives
visibility / request / approval gating. This migration only adds the
storage columns; classification is computed by the driver classifier on
the next role-catalog sync (``cmdb.roles_sync`` daily cron, or admin
"立即同步"). No behavior depends on these columns yet.

Columns:
* ``governance_tier`` — one of the four tiers; defaults ``normal``.
* ``governance_reason`` — which classifier rule fired (UI + audit).
* ``governance_overridden_by_admin`` — once an admin manually sets a
  tier in the catalog UI, sync stops recomputing it (mirrors the
  existing ``is_promoted`` persistence rule).

Existing rows get ``normal`` / NULL / 0 via server defaults; their real
tiers fill in on the next sync. A role-catalog resync should be run
before any PR that *enforces* these tiers, so enforcement never reads a
stale ``normal``.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "263"
down_revision = "262"
branch_labels = None
depends_on = None

_TABLE = "cmdb_cloud_roles"


def _has_column(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 _has_column(bind, _TABLE, "governance_tier"):
        op.add_column(_TABLE, sa.Column(
            "governance_tier", sa.String(length=16),
            nullable=False, server_default="normal",
        ))
    if not _has_column(bind, _TABLE, "governance_reason"):
        op.add_column(_TABLE, sa.Column(
            "governance_reason", sa.String(length=255), nullable=True,
        ))
    if not _has_column(bind, _TABLE, "governance_overridden_by_admin"):
        op.add_column(_TABLE, sa.Column(
            "governance_overridden_by_admin", sa.Boolean(),
            nullable=False, server_default="0",
        ))


def downgrade() -> None:
    bind = op.get_bind()
    for col in (
        "governance_overridden_by_admin",
        "governance_reason",
        "governance_tier",
    ):
        if _has_column(bind, _TABLE, col):
            op.drop_column(_TABLE, col)