"""`offboarding_account_assurance.system` → `identity_system`。

`SYSTEM` 从 MySQL 8.0.3 起是**保留字**。ORM 生成的 SQL 会自动加反引号,所以
应用侧一直是好的 —— 问题出在**手写 SQL**上:

    SELECT system FROM offboarding_account_assurance;   -- 1064 语法错误

这不是一个理论风险。本仓的运维路径明确写着「只读诊断直连生产 TiDB 跑一段
SQL」,而这张表恰恰是治理面要查的表 —— 第一个想看看「谁还能登录」的人,
写出来的第一条 SQL 就会炸,而报错信息(「near 'system FROM ...'」)完全不
提示原因是保留字。

发现方式:给界面验证造种子数据时,手写的 INSERT 直接 1064。

**现在改代价为零** —— 575 还没有发布到生产,这张表在生产上尚不存在;这条
迁移紧跟在建表之后、在一张空表上跑。晚一天改就要带着数据改。

Revision ID: 577
Revises: 576
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "577"
down_revision = "576"
branch_labels = None
depends_on = None

TABLE = "offboarding_account_assurance"


def _has_column(conn, table: str, column: str) -> bool:
    return conn.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:
    conn = op.get_bind()
    if not sa.inspect(conn).has_table(TABLE):
        return
    if not _has_column(conn, TABLE, "system"):
        return  # already renamed
    # ALTER ... CHANGE rather than RENAME COLUMN: TiDB accepts both, but
    # CHANGE restates the full definition, which keeps the column's type
    # and nullability explicit at the rename site instead of implied.
    op.execute(
        f"ALTER TABLE `{TABLE}` "
        "CHANGE COLUMN `system` `identity_system` VARCHAR(32) NOT NULL"
    )


def downgrade() -> None:
    conn = op.get_bind()
    if not sa.inspect(conn).has_table(TABLE):
        return
    if not _has_column(conn, TABLE, "identity_system"):
        return
    op.execute(
        f"ALTER TABLE `{TABLE}` "
        "CHANGE COLUMN `identity_system` `system` VARCHAR(32) NOT NULL"
    )