"""登录界面重置密码审批流（契约1.49）。

Revision ID: 0043_password_reset_request
Revises: 0042_matrix_top_category_kind
Create Date: 2026-09-22

变更要点（Python 版已废弃不做功能实现，仅保持迁移链同步）：
1. 新建 sys_password_reset_request 重置密码申请单表：登录前自助提交，
   按申请用户类型路由审批角色（首长→安全管理员、其他→系统管理员），
   任一审批人处理即定（先到先得）；
2. 审批通过=回首次登录态（密码清空、门户重新设密），由服务层执行。
"""

from __future__ import annotations

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op

revision: str = "0043_password_reset_request"
down_revision: str | None = "0042_matrix_top_category_kind"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.create_table(
        "sys_password_reset_request",
        sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False),
        sa.Column("user_id", sa.BigInteger(), nullable=False),
        sa.Column("username", sa.String(length=64), nullable=False),
        sa.Column("real_name", sa.String(length=100), nullable=False),
        sa.Column("user_type", sa.String(length=16), nullable=False),
        sa.Column("approver_role_name", sa.String(length=64), nullable=False),
        sa.Column("status", sa.String(length=16), nullable=False),
        sa.Column("request_ip", sa.String(length=64), nullable=True),
        sa.Column("requested_at", sa.DateTime(), nullable=False),
        sa.Column("handled_by", sa.BigInteger(), nullable=True),
        sa.Column("handled_by_name", sa.String(length=100), nullable=True),
        sa.Column("handled_at", sa.DateTime(), nullable=True),
        sa.Column("created_at", sa.DateTime(), nullable=False,
                  server_default=sa.text("CURRENT_TIMESTAMP")),
        sa.PrimaryKeyConstraint("id"),
        sa.CheckConstraint(
            "status IN ('PENDING', 'APPROVED', 'REJECTED')",
            name="ck_password_reset_request_status"),
    )
    op.create_index(
        "ix_password_reset_request_user",
        "sys_password_reset_request",
        ["user_id", "status"])


def downgrade() -> None:
    op.drop_index("ix_password_reset_request_user",
                  table_name="sys_password_reset_request")
    op.drop_table("sys_password_reset_request")