document-management/backend/dms/models/user.py

"""系统用户模型。"""

from __future__ import annotations

from datetime import datetime

from sqlalchemy import (
    BigInteger,
    CheckConstraint,
    Computed,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    String,
    UniqueConstraint,
    text,
)
from sqlalchemy.dialects.mysql import DATETIME
from sqlalchemy.orm import Mapped, mapped_column

from dms.database.base import BusinessTableMixin
from dms.extensions import db


class User(BusinessTableMixin, db.Model):
    __tablename__ = "sys_user"
    __table_args__ = (
        UniqueConstraint(
            "external_id",
            "active_marker",
            name="uq_sys_user_external_id_active",
        ),
        Index(
            "ix_sys_user_external_active",
            "external_id",
            "is_deleted",
        ),
        CheckConstraint(
            "role_code IN ('USER', 'ADMIN')",
            name="user_role_code",
        ),
        CheckConstraint(
            "security_level IN "
            "('PUBLIC', 'INTERNAL', 'SECRET', 'CONFIDENTIAL', 'TOP_SECRET')",
            name="user_security_level",
        ),
        CheckConstraint(
            "status IN ('ENABLED', 'DISABLED')",
            name="user_status",
        ),
        CheckConstraint(
            "ui_theme IN ('blue', 'red', 'green')",
            name="user_ui_theme",
        ),
        Index(
            "ix_sys_user_organization_active_status",
            "organization_id",
            "is_deleted",
            "status",
        ),
        Index(
            "ix_sys_user_role_status_active",
            "role_code",
            "status",
            "is_deleted",
        ),
        Index(
            "ix_sys_user_role_active",
            "role_id",
            "is_deleted",
        ),
    )

    username: Mapped[str] = mapped_column(String(64), nullable=False, unique=True)
    password_hash: Mapped[str] = mapped_column(String(255), nullable=False)
    real_name: Mapped[str] = mapped_column(String(64), nullable=False)
    organization_id: Mapped[int | None] = mapped_column(
        BigInteger,
        ForeignKey("sys_organization.id", ondelete="RESTRICT"),
        nullable=True,
    )
    organization_name: Mapped[str | None] = mapped_column(String(128), nullable=True)
    role_code: Mapped[str] = mapped_column(
        String(32),
        nullable=False,
        server_default=text("'USER'"),
    )
    role_id: Mapped[int | None] = mapped_column(
        BigInteger,
        ForeignKey("sys_role.id", ondelete="RESTRICT"),
        nullable=True,
    )
    security_level: Mapped[str] = mapped_column(
        String(32),
        nullable=False,
        server_default=text("'INTERNAL'"),
    )
    status: Mapped[str] = mapped_column(
        String(20),
        nullable=False,
        server_default=text("'ENABLED'"),
    )
    auth_version: Mapped[int] = mapped_column(
        Integer,
        nullable=False,
        server_default=text("0"),
    )
    last_login_at: Mapped[datetime | None] = mapped_column(
        DATETIME(fsp=3),
        nullable=True,
    )
    last_login_ip: Mapped[str | None] = mapped_column(String(64), nullable=True)
    # 登录失败锁定：连续密码错误计数与锁定截止时间（UTC，NULL=未锁定）
    failed_login_count: Mapped[int] = mapped_column(
        Integer,
        nullable=False,
        server_default=text("0"),
    )
    locked_until: Mapped[datetime | None] = mapped_column(
        DateTime(),
        nullable=True,
    )
    external_id: Mapped[str | None] = mapped_column(String(128), nullable=True)
    # 界面主题偏好（账号级漫游，契约1.20）：blue/red/green，存量与缺省为 blue
    ui_theme: Mapped[str] = mapped_column(
        String(12),
        nullable=False,
        server_default=text("'blue'"),
    )
    # 拖动排序号（契约1.27）：拖拽重排按步长10全量重编号，排序号输入已废止
    sort_no: Mapped[int] = mapped_column(nullable=False, server_default=text("0"))
    # 用户级管理范围（契约1.30）：NULL=全局、0=跟随本人所在机构（SELF 哨兵）、
    # 机构ID=固定锚点；仅角色锚点为 SELF 时生效（实际范围以角色设置为准）
    managed_org_id: Mapped[int | None] = mapped_column(
        BigInteger,
        nullable=True,
        server_default=text("0"),
    )
    active_marker: Mapped[int | None] = mapped_column(
        Integer(),
        Computed("IF(is_deleted = 0, 1, NULL)", persisted=False),
        nullable=True,
    )