"""门户跳转用户预置：sys_user.external_id + 软删唯一约束。

Revision ID: 0016_portal_user_provision
Revises: 0015_login_ip_restriction
Create Date: 2026-08-23

变更要点：
1. sys_user 新增 external_id（对端门户系统的用户唯一标识，NULL=本地原生用户）；
2. 新增 MySQL 生成列 active_marker（未删=1，已删=NULL）与
   唯一约束 uq_sys_user_external_id_active，防止同一门户用户重复落库；
3. 降级：存在门户用户时拒绝有损降级。
"""

from __future__ import annotations

from collections.abc import Sequence

from alembic import op
import sqlalchemy as sa

revision: str = "0016_portal_user_provision"
down_revision: str | None = "0015_login_ip_restriction"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.add_column(
        "sys_user",
        sa.Column("external_id", sa.String(length=128), nullable=True),
    )
    op.add_column(
        "sys_user",
        sa.Column(
            "active_marker",
            sa.Integer(),
            sa.Computed("IF(is_deleted = 0, 1, NULL)", persisted=False),
            nullable=True,
        ),
    )
    op.create_unique_constraint(
        "uq_sys_user_external_id_active",
        "sys_user",
        ["external_id", "active_marker"],
    )
    op.create_index(
        "ix_sys_user_external_active",
        "sys_user",
        ["external_id", "is_deleted"],
    )


def downgrade() -> None:
    bind = op.get_bind()
    portal_users = bind.scalar(
        sa.text("SELECT COUNT(*) FROM sys_user WHERE external_id IS NOT NULL")
    )
    if portal_users:
        raise RuntimeError(
            "0016已存在门户用户（external_id非空），拒绝有损降级；"
            "请先备份并按数据治理流程处理"
        )
    op.drop_index("ix_sys_user_external_active", table_name="sys_user")
    op.drop_constraint(
        "uq_sys_user_external_id_active", "sys_user", type_="unique"
    )
    op.drop_column("sys_user", "active_marker")
    op.drop_column("sys_user", "external_id")