20fcf06f51
- businesses 五项汇总冗余列服务层事务内维护；毛利润不落库（派生值，M3 台账固化） - 行级 product_category 折算锚点（#14）；回款 settlement_status 预置为 M3 防重锚点 - 评级等级种子四条（迁移+init-dev 双写）；编号前缀常量 YW/SH/HK - 双轨 SQL 同步（V4/V5 重导，schema_sync+test_models 164 例全绿） Co-Authored-By: Claude Code <noreply@anthropic.com>
491 lines
18 KiB
Python
491 lines
18 KiB
Python
"""基础数据管理六表：人员/辐射区域/拓展计划/产品/客户/归属变更留痕（M2）
|
||
|
||
- personnel / personnel_regions / personnel_plans：人员档案及其 1:N 子表
|
||
- products：产品主数据（类别锚定折算阈值参数）
|
||
- customers：客户档案（公司名称+负责人为业务唯一标识）
|
||
- ownership_change_logs：归属变更留痕（append-only，不回溯历史）
|
||
- 六张表的 T+ 同步预留列（source_no 等）全部可空、不设唯一键（对照表 #26）
|
||
|
||
Revision ID: e1133a7c670a
|
||
Revises: bfbda9c3191d
|
||
Create Date: 2026-10-13 09:36:02.219248
|
||
|
||
"""
|
||
|
||
from collections.abc import Sequence
|
||
|
||
from alembic import op
|
||
import sqlalchemy as sa
|
||
from sqlalchemy.dialects import mysql
|
||
|
||
# revision identifiers, used by Alembic.
|
||
revision: str = "e1133a7c670a"
|
||
down_revision: str | Sequence[str] | None = "bfbda9c3191d"
|
||
branch_labels: str | Sequence[str] | None = None
|
||
depends_on: str | Sequence[str] | None = None
|
||
|
||
|
||
def upgrade() -> None:
|
||
"""应用迁移。"""
|
||
# ### commands auto generated by Alembic - please adjust! ###
|
||
op.create_table(
|
||
"products",
|
||
sa.Column(
|
||
"id", mysql.BIGINT(unsigned=True), autoincrement=True, nullable=False, comment="主键ID"
|
||
),
|
||
sa.Column("product_name", sa.String(length=128), nullable=False, comment="产品名称"),
|
||
sa.Column(
|
||
"spec_model",
|
||
sa.String(length=128),
|
||
server_default="",
|
||
nullable=False,
|
||
comment="规格型号（空串而非 NULL：参与组合唯一键，MySQL 组合索引对 NULL 不去重）",
|
||
),
|
||
sa.Column("unit", sa.String(length=16), nullable=True, comment="计量单位"),
|
||
sa.Column(
|
||
"stock_quantity",
|
||
sa.Numeric(precision=14, scale=2),
|
||
nullable=True,
|
||
comment="数量（T+ 同步原样存储，语义待 IT 确认）",
|
||
),
|
||
sa.Column(
|
||
"sale_price",
|
||
sa.Numeric(precision=14, scale=4),
|
||
nullable=True,
|
||
comment="销售单价（元，T+ 同步）",
|
||
),
|
||
sa.Column(
|
||
"product_category",
|
||
sa.String(length=32),
|
||
nullable=False,
|
||
comment="产品类别：municipal_water市政水利 rail_transit轨道交通（决定折算阈值 30%/20%；T+同步不覆盖）",
|
||
),
|
||
sa.Column(
|
||
"status",
|
||
mysql.TINYINT(),
|
||
server_default="1",
|
||
nullable=False,
|
||
comment="状态：1启用 0停用",
|
||
),
|
||
sa.Column("remark", sa.String(length=255), nullable=True, comment="备注"),
|
||
sa.Column(
|
||
"create_time",
|
||
sa.DateTime(),
|
||
server_default=sa.text("CURRENT_TIMESTAMP"),
|
||
nullable=False,
|
||
comment="创建时间",
|
||
),
|
||
sa.Column(
|
||
"update_time",
|
||
sa.DateTime(),
|
||
server_default=sa.text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"),
|
||
nullable=False,
|
||
comment="更新时间",
|
||
),
|
||
sa.Column(
|
||
"source_no",
|
||
sa.String(length=64),
|
||
nullable=True,
|
||
comment="T+ 原始单据号（同步幂等去重锚点；唯一键待 IT 定键后升级）",
|
||
),
|
||
sa.Column("t_plus_id", sa.String(length=64), nullable=True, comment="T+ 系统内部标识"),
|
||
sa.Column(
|
||
"sync_batch_no",
|
||
sa.String(length=32),
|
||
nullable=True,
|
||
comment="同步批次号（同步模块写入）",
|
||
),
|
||
sa.Column("synced_at", sa.DateTime(), nullable=True, comment="最近同步时间"),
|
||
sa.PrimaryKeyConstraint("id", name=op.f("pk_products")),
|
||
sa.UniqueConstraint("product_name", "spec_model", name="uk_products_name_spec"),
|
||
comment="产品表（类别锚定折算阈值参数；T+同步不覆盖产品类别）",
|
||
mysql_charset="utf8mb4",
|
||
mysql_collate="utf8mb4_general_ci",
|
||
mysql_engine="InnoDB",
|
||
)
|
||
op.create_index(op.f("idx_products_source_no"), "products", ["source_no"], unique=False)
|
||
op.create_table(
|
||
"ownership_change_logs",
|
||
sa.Column(
|
||
"id", mysql.BIGINT(unsigned=True), autoincrement=True, nullable=False, comment="主键ID"
|
||
),
|
||
sa.Column(
|
||
"owner_type", mysql.TINYINT(), nullable=False, comment="归属类型：1客户归属 2业务归属"
|
||
),
|
||
sa.Column(
|
||
"customer_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=True,
|
||
comment="客户ID（固化，owner_type=1 时非空）",
|
||
),
|
||
sa.Column(
|
||
"business_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=True,
|
||
comment="业务ID（固化，owner_type=2 时非空）",
|
||
),
|
||
sa.Column(
|
||
"old_owner_personnel_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=True,
|
||
comment="变更前负责营销人员ID（固化）",
|
||
),
|
||
sa.Column(
|
||
"new_owner_personnel_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=True,
|
||
comment="变更后负责营销人员ID（固化）",
|
||
),
|
||
sa.Column(
|
||
"old_assistant_personnel_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=True,
|
||
comment="变更前协助营销人员ID（固化，仅业务归属使用）",
|
||
),
|
||
sa.Column(
|
||
"new_assistant_personnel_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=True,
|
||
comment="变更后协助营销人员ID（固化，仅业务归属使用）",
|
||
),
|
||
sa.Column("change_reason", sa.String(length=255), nullable=True, comment="变更原因"),
|
||
sa.Column(
|
||
"operator_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=True,
|
||
comment="操作人ID（账号删除后置空）",
|
||
),
|
||
sa.Column(
|
||
"operator_name",
|
||
sa.String(length=64),
|
||
nullable=False,
|
||
comment="操作人用户名（冗余固化）",
|
||
),
|
||
sa.Column(
|
||
"create_time",
|
||
sa.DateTime(),
|
||
server_default=sa.text("CURRENT_TIMESTAMP"),
|
||
nullable=False,
|
||
comment="创建时间",
|
||
),
|
||
sa.ForeignKeyConstraint(
|
||
["operator_id"],
|
||
["users.id"],
|
||
name=op.f("fk_ownership_change_logs_operator_id"),
|
||
ondelete="SET NULL",
|
||
),
|
||
sa.PrimaryKeyConstraint("id", name=op.f("pk_ownership_change_logs")),
|
||
comment="归属变更记录表（append-only；客户/业务归属变更留痕，不回溯历史）",
|
||
mysql_charset="utf8mb4",
|
||
mysql_collate="utf8mb4_general_ci",
|
||
mysql_engine="InnoDB",
|
||
)
|
||
op.create_index(
|
||
op.f("idx_ownership_change_logs_customer_id"),
|
||
"ownership_change_logs",
|
||
["customer_id"],
|
||
unique=False,
|
||
)
|
||
op.create_index(
|
||
op.f("idx_ownership_change_logs_business_id"),
|
||
"ownership_change_logs",
|
||
["business_id"],
|
||
unique=False,
|
||
)
|
||
op.create_index(
|
||
op.f("idx_ownership_change_logs_create_time"),
|
||
"ownership_change_logs",
|
||
["create_time"],
|
||
unique=False,
|
||
)
|
||
op.create_table(
|
||
"personnel",
|
||
sa.Column(
|
||
"id", mysql.BIGINT(unsigned=True), autoincrement=True, nullable=False, comment="主键ID"
|
||
),
|
||
sa.Column("personnel_name", sa.String(length=64), nullable=False, comment="姓名"),
|
||
sa.Column(
|
||
"business_role_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=False,
|
||
comment="岗位角色ID（决定计提公式模板；T+同步不覆盖）",
|
||
),
|
||
sa.Column(
|
||
"manager_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=True,
|
||
comment="直属营销经理ID（NULL=经理本人或未分配；团队成员关系锚点）",
|
||
),
|
||
sa.Column("phone", sa.String(length=20), nullable=True, comment="联系电话"),
|
||
sa.Column("hire_date", sa.Date(), nullable=True, comment="入职日期"),
|
||
sa.Column(
|
||
"is_probation",
|
||
mysql.TINYINT(),
|
||
server_default="0",
|
||
nullable=False,
|
||
comment="是否试用期：1是 0否（试用期绩效照算不发放）",
|
||
),
|
||
sa.Column(
|
||
"salary_standard",
|
||
sa.Numeric(precision=12, scale=2),
|
||
nullable=True,
|
||
comment="月工资标准（元；T+同步不覆盖）",
|
||
),
|
||
sa.Column(
|
||
"status",
|
||
mysql.TINYINT(),
|
||
server_default="1",
|
||
nullable=False,
|
||
comment="状态：1在职 0离职（离职人员不可再被业务/客户归属引用）",
|
||
),
|
||
sa.Column("remark", sa.String(length=255), nullable=True, comment="备注"),
|
||
sa.Column(
|
||
"create_time",
|
||
sa.DateTime(),
|
||
server_default=sa.text("CURRENT_TIMESTAMP"),
|
||
nullable=False,
|
||
comment="创建时间",
|
||
),
|
||
sa.Column(
|
||
"update_time",
|
||
sa.DateTime(),
|
||
server_default=sa.text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"),
|
||
nullable=False,
|
||
comment="更新时间",
|
||
),
|
||
sa.Column(
|
||
"source_no",
|
||
sa.String(length=64),
|
||
nullable=True,
|
||
comment="T+ 原始单据号（同步幂等去重锚点；唯一键待 IT 定键后升级）",
|
||
),
|
||
sa.Column("t_plus_id", sa.String(length=64), nullable=True, comment="T+ 系统内部标识"),
|
||
sa.Column(
|
||
"sync_batch_no",
|
||
sa.String(length=32),
|
||
nullable=True,
|
||
comment="同步批次号（同步模块写入）",
|
||
),
|
||
sa.Column("synced_at", sa.DateTime(), nullable=True, comment="最近同步时间"),
|
||
sa.ForeignKeyConstraint(
|
||
["business_role_id"],
|
||
["business_roles.id"],
|
||
name=op.f("fk_personnel_business_role_id"),
|
||
ondelete="RESTRICT",
|
||
),
|
||
sa.ForeignKeyConstraint(
|
||
["manager_id"],
|
||
["personnel.id"],
|
||
name=op.f("fk_personnel_manager_id"),
|
||
ondelete="RESTRICT",
|
||
),
|
||
sa.PrimaryKeyConstraint("id", name=op.f("pk_personnel")),
|
||
comment="营销人员表（T+同步不覆盖岗位与工资标准）",
|
||
mysql_charset="utf8mb4",
|
||
mysql_collate="utf8mb4_general_ci",
|
||
mysql_engine="InnoDB",
|
||
)
|
||
op.create_index(
|
||
op.f("idx_personnel_business_role_id"), "personnel", ["business_role_id"], unique=False
|
||
)
|
||
op.create_index(op.f("idx_personnel_manager_id"), "personnel", ["manager_id"], unique=False)
|
||
op.create_index(op.f("idx_personnel_source_no"), "personnel", ["source_no"], unique=False)
|
||
op.create_table(
|
||
"customers",
|
||
sa.Column(
|
||
"id", mysql.BIGINT(unsigned=True), autoincrement=True, nullable=False, comment="主键ID"
|
||
),
|
||
sa.Column("company_name", sa.String(length=128), nullable=False, comment="公司名称"),
|
||
sa.Column(
|
||
"owner_name",
|
||
sa.String(length=64),
|
||
nullable=False,
|
||
comment="客户负责人姓名（客户公司负责人的人名，非系统人员）",
|
||
),
|
||
sa.Column("contact_person", sa.String(length=64), nullable=True, comment="联系人"),
|
||
sa.Column("contact_phone", sa.String(length=20), nullable=True, comment="联系电话"),
|
||
sa.Column("address", sa.String(length=255), nullable=True, comment="地址"),
|
||
sa.Column("industry", sa.String(length=64), nullable=True, comment="所属行业（自由文本）"),
|
||
sa.Column(
|
||
"customer_type",
|
||
sa.String(length=32),
|
||
server_default="personal",
|
||
nullable=False,
|
||
comment="客户分类：personal个人户 manager_personal经理个人户 enterprise企业户（计提规则匹配锚点；T+同步不覆盖）",
|
||
),
|
||
sa.Column(
|
||
"owner_personnel_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=True,
|
||
comment="归属负责营销人员ID（手动修改不回溯历史，变更留痕见 ownership_change_logs）",
|
||
),
|
||
sa.Column("remark", sa.String(length=255), nullable=True, comment="备注"),
|
||
sa.Column(
|
||
"create_time",
|
||
sa.DateTime(),
|
||
server_default=sa.text("CURRENT_TIMESTAMP"),
|
||
nullable=False,
|
||
comment="创建时间",
|
||
),
|
||
sa.Column(
|
||
"update_time",
|
||
sa.DateTime(),
|
||
server_default=sa.text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"),
|
||
nullable=False,
|
||
comment="更新时间",
|
||
),
|
||
sa.Column(
|
||
"source_no",
|
||
sa.String(length=64),
|
||
nullable=True,
|
||
comment="T+ 原始单据号（同步幂等去重锚点；唯一键待 IT 定键后升级）",
|
||
),
|
||
sa.Column("t_plus_id", sa.String(length=64), nullable=True, comment="T+ 系统内部标识"),
|
||
sa.Column(
|
||
"sync_batch_no",
|
||
sa.String(length=32),
|
||
nullable=True,
|
||
comment="同步批次号（同步模块写入）",
|
||
),
|
||
sa.Column("synced_at", sa.DateTime(), nullable=True, comment="最近同步时间"),
|
||
sa.ForeignKeyConstraint(
|
||
["owner_personnel_id"],
|
||
["personnel.id"],
|
||
name=op.f("fk_customers_owner_personnel_id"),
|
||
ondelete="RESTRICT",
|
||
),
|
||
sa.PrimaryKeyConstraint("id", name=op.f("pk_customers")),
|
||
sa.UniqueConstraint("company_name", "owner_name", name="uk_customers_company_owner"),
|
||
comment="客户表（公司名称+负责人为业务唯一标识；T+同步不覆盖客户分类）",
|
||
mysql_charset="utf8mb4",
|
||
mysql_collate="utf8mb4_general_ci",
|
||
mysql_engine="InnoDB",
|
||
)
|
||
op.create_index(
|
||
op.f("idx_customers_customer_type"), "customers", ["customer_type"], unique=False
|
||
)
|
||
op.create_index(
|
||
op.f("idx_customers_owner_personnel_id"), "customers", ["owner_personnel_id"], unique=False
|
||
)
|
||
op.create_index(op.f("idx_customers_source_no"), "customers", ["source_no"], unique=False)
|
||
op.create_table(
|
||
"personnel_plans",
|
||
sa.Column(
|
||
"id", mysql.BIGINT(unsigned=True), autoincrement=True, nullable=False, comment="主键ID"
|
||
),
|
||
sa.Column(
|
||
"personnel_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=False,
|
||
comment="人员ID（personnel.id）",
|
||
),
|
||
sa.Column(
|
||
"plan_period",
|
||
sa.String(length=32),
|
||
nullable=False,
|
||
comment="计划期间（如 2026Q1、2026-05）",
|
||
),
|
||
sa.Column("plan_content", sa.String(length=500), nullable=False, comment="计划内容"),
|
||
sa.Column(
|
||
"target_desc", sa.String(length=255), nullable=True, comment="指标说明（文字描述）"
|
||
),
|
||
sa.Column(
|
||
"source_no", sa.String(length=64), nullable=True, comment="T+ 原始条目号（预留）"
|
||
),
|
||
sa.Column(
|
||
"create_time",
|
||
sa.DateTime(),
|
||
server_default=sa.text("CURRENT_TIMESTAMP"),
|
||
nullable=False,
|
||
comment="创建时间",
|
||
),
|
||
sa.Column(
|
||
"update_time",
|
||
sa.DateTime(),
|
||
server_default=sa.text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"),
|
||
nullable=False,
|
||
comment="更新时间",
|
||
),
|
||
sa.ForeignKeyConstraint(
|
||
["personnel_id"],
|
||
["personnel.id"],
|
||
name=op.f("fk_personnel_plans_personnel_id"),
|
||
ondelete="RESTRICT",
|
||
),
|
||
sa.PrimaryKeyConstraint("id", name=op.f("pk_personnel_plans")),
|
||
comment="市场拓展计划表",
|
||
mysql_charset="utf8mb4",
|
||
mysql_collate="utf8mb4_general_ci",
|
||
mysql_engine="InnoDB",
|
||
)
|
||
op.create_index(
|
||
op.f("idx_personnel_plans_personnel_id"), "personnel_plans", ["personnel_id"], unique=False
|
||
)
|
||
op.create_table(
|
||
"personnel_regions",
|
||
sa.Column(
|
||
"id", mysql.BIGINT(unsigned=True), autoincrement=True, nullable=False, comment="主键ID"
|
||
),
|
||
sa.Column(
|
||
"personnel_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=False,
|
||
comment="人员ID（personnel.id）",
|
||
),
|
||
sa.Column(
|
||
"region_name",
|
||
sa.String(length=128),
|
||
nullable=False,
|
||
comment="区域名称（产品辐射地理范围）",
|
||
),
|
||
sa.Column("remark", sa.String(length=255), nullable=True, comment="备注"),
|
||
sa.Column(
|
||
"create_time",
|
||
sa.DateTime(),
|
||
server_default=sa.text("CURRENT_TIMESTAMP"),
|
||
nullable=False,
|
||
comment="创建时间",
|
||
),
|
||
sa.Column(
|
||
"update_time",
|
||
sa.DateTime(),
|
||
server_default=sa.text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"),
|
||
nullable=False,
|
||
comment="更新时间",
|
||
),
|
||
sa.ForeignKeyConstraint(
|
||
["personnel_id"],
|
||
["personnel.id"],
|
||
name=op.f("fk_personnel_regions_personnel_id"),
|
||
ondelete="RESTRICT",
|
||
),
|
||
sa.PrimaryKeyConstraint("id", name=op.f("pk_personnel_regions")),
|
||
comment="产品辐射区域表",
|
||
mysql_charset="utf8mb4",
|
||
mysql_collate="utf8mb4_general_ci",
|
||
mysql_engine="InnoDB",
|
||
)
|
||
op.create_index(
|
||
op.f("idx_personnel_regions_personnel_id"),
|
||
"personnel_regions",
|
||
["personnel_id"],
|
||
unique=False,
|
||
)
|
||
# ### end Alembic commands ###
|
||
|
||
|
||
def downgrade() -> None:
|
||
"""回滚迁移。
|
||
|
||
**注意（autogenerate 不会替你处理）**：生成的 `op.drop_index` 与
|
||
`op.drop_table` 顺序需人工检查 —— 若某索引是外键的唯一支撑索引，
|
||
单独 drop_index 会报 MySQL 1553（needed in a foreign key constraint）。
|
||
**整表回滚应只保留 `op.drop_table`**，DROP TABLE 会连带删除索引与外键。
|
||
"""
|
||
op.drop_table("personnel_regions")
|
||
op.drop_table("personnel_plans")
|
||
op.drop_table("customers")
|
||
op.drop_table("personnel")
|
||
op.drop_table("ownership_change_logs")
|
||
op.drop_table("products")
|