PayrollManagementSystem/backend/app/models/customer.py

"""客户模型：客户档案与归属变更留痕。

- 「公司名称 + 负责人」是业务唯一标识（需规 2.1.3）；负责人是客户公司的
  负责人**人名**（字符串），不是系统内的营销人员 —— 系统内归属另用
  owner_personnel_id 外键表达，两者语义不同
- 归属变更只改主表当前值 + 插一条留痕，**不回溯历史**（对照表 #19）：
  历史核算结果在 M3 台账已固化，无需 effective_date 时点化设计
- 留痕表（OwnershipChangeLog）对客户/业务/人员 ID 均为**无外键的冗余固化**：
  留痕是历史记录，被引用方被删除后记录仍须完整可读；RESTRICT 外键反而会
  把「删除客户」逼成 500。业务实体删除保护由服务层检查承担
"""

from datetime import datetime

from sqlalchemy import DateTime, ForeignKey, Index, String, UniqueConstraint, text
from sqlalchemy.orm import Mapped, mapped_column

from app.models.base import (
    BIGINT_UNSIGNED,
    TINYINT_TYPE,
    Base,
    TimestampMixin,
    TPlusSyncMixin,
    mysql_table_args,
)

# 归属类型（ownership_change_logs.owner_type）
OWNER_TYPE_CUSTOMER = 1
OWNER_TYPE_BUSINESS = 2


class Customer(Base, TimestampMixin, TPlusSyncMixin):
    """客户表（需求 2.1.3）。customer_type 是计提规则匹配锚点（M3 消费）。"""

    __tablename__ = "customers"
    __table_args__ = mysql_table_args(
        "客户表（公司名称+负责人为业务唯一标识；T+同步不覆盖客户分类）",
        UniqueConstraint("company_name", "owner_name", name="uk_customers_company_owner"),
    )

    id: Mapped[int] = mapped_column(
        BIGINT_UNSIGNED, primary_key=True, autoincrement=True, comment="主键ID"
    )
    company_name: Mapped[str] = mapped_column(String(128), nullable=False, comment="公司名称")
    owner_name: Mapped[str] = mapped_column(
        String(64), nullable=False, comment="客户负责人姓名（客户公司负责人的人名，非系统人员）"
    )
    contact_person: Mapped[str | None] = mapped_column(String(64), comment="联系人")
    contact_phone: Mapped[str | None] = mapped_column(String(20), comment="联系电话")
    address: Mapped[str | None] = mapped_column(String(255), comment="地址")
    industry: Mapped[str | None] = mapped_column(String(64), comment="所属行业（自由文本）")
    customer_type: Mapped[str | None] = mapped_column(
        String(32),
        nullable=True,
        index=True,
        comment="客户分类：personal个人户 manager_personal经理个人户 enterprise企业户（计提规则匹配锚点；T+同步不覆盖）",
    )
    owner_personnel_id: Mapped[int | None] = mapped_column(
        BIGINT_UNSIGNED,
        ForeignKey("personnel.id", ondelete="RESTRICT"),
        index=True,
        comment="归属负责营销人员ID（手动修改不回溯历史，变更留痕见 ownership_change_logs）",
    )
    remark: Mapped[str | None] = mapped_column(String(255), comment="备注")
    rating_score: Mapped[int | None] = mapped_column(
        TINYINT_TYPE,
        comment="评级总分（0-100，评级计算器保存；null=未评分；人工维护字段，T+ 同步不覆盖）",
    )
    rating_time: Mapped[datetime | None] = mapped_column(
        DateTime,
        comment="最近评级时间（评级计算器保存评分时刷新；人工维护字段）",
    )


class OwnershipChangeLog(Base):
    """归属变更记录表（append-only，需求 2.1.3 + 对照表 #19）。

    owner_type 区分客户归属/业务归属：1 → customer_id 非空，2 → business_id
    非空，由应用层校验（无外键，见模块 docstring 的固化论证）。
    前后值冗余固化（无外键）：人员被删除后留痕仍可读。
    """

    __tablename__ = "ownership_change_logs"
    __table_args__ = mysql_table_args(
        "归属变更记录表（append-only；客户/业务归属变更留痕，不回溯历史）",
        Index("idx_ownership_change_logs_customer_id", "customer_id"),
        Index("idx_ownership_change_logs_business_id", "business_id"),
        Index("idx_ownership_change_logs_create_time", "create_time"),
    )

    id: Mapped[int] = mapped_column(
        BIGINT_UNSIGNED, primary_key=True, autoincrement=True, comment="主键ID"
    )
    owner_type: Mapped[int] = mapped_column(
        TINYINT_TYPE, nullable=False, comment="归属类型：1客户归属 2业务归属"
    )
    customer_id: Mapped[int | None] = mapped_column(
        BIGINT_UNSIGNED,
        comment="客户ID（固化，owner_type=1 时非空）",
    )
    business_id: Mapped[int | None] = mapped_column(
        BIGINT_UNSIGNED,
        comment="业务ID（固化，owner_type=2 时非空）",
    )
    old_owner_personnel_id: Mapped[int | None] = mapped_column(
        BIGINT_UNSIGNED, comment="变更前负责营销人员ID（固化）"
    )
    new_owner_personnel_id: Mapped[int | None] = mapped_column(
        BIGINT_UNSIGNED, comment="变更后负责营销人员ID（固化）"
    )
    old_assistant_personnel_id: Mapped[int | None] = mapped_column(
        BIGINT_UNSIGNED, comment="变更前协助营销人员ID（固化，仅业务归属使用）"
    )
    new_assistant_personnel_id: Mapped[int | None] = mapped_column(
        BIGINT_UNSIGNED, comment="变更后协助营销人员ID（固化，仅业务归属使用）"
    )
    change_reason: Mapped[str | None] = mapped_column(String(255), comment="变更原因")
    operator_id: Mapped[int | None] = mapped_column(
        BIGINT_UNSIGNED,
        ForeignKey("users.id", ondelete="SET NULL"),
        comment="操作人ID（账号删除后置空）",
    )
    operator_name: Mapped[str] = mapped_column(
        String(64), nullable=False, comment="操作人用户名（冗余固化）"
    )
    create_time: Mapped[datetime] = mapped_column(
        DateTime, nullable=False, server_default=text("CURRENT_TIMESTAMP"), comment="创建时间"
    )