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>
697 lines
27 KiB
Python
697 lines
27 KiB
Python
"""业务数据管理七表：业务/业务明细/状态留痕/发货单/发货明细/回款/评级等级配置（M2）
|
||
|
||
- businesses：业务编号自动生成（报表 2.4.1「合同编号」），五项汇总冗余列
|
||
由服务层事务内维护；毛利润相关值不落库（派生值，M3 台账才是固化点）
|
||
- business_items：行级 product_category 为折算锚点（对照表 #14），指导价
|
||
人工录入、T+ 同步不覆盖
|
||
- business_status_logs：append-only，创建时写首条（old=new=1）
|
||
- shipments/shipment_items：发货日为回款天数基准日（对照表 #1）
|
||
- receipts：手动指定发货批次（#18）；settlement_status 为 M3 核算防重锚点
|
||
- customer_rating_grades：等级映射可配置，种子四条随迁移初始化
|
||
|
||
Revision ID: 8efc189b131f
|
||
Revises: e1133a7c670a
|
||
Create Date: 2026-10-13 11:14:17.545320
|
||
|
||
"""
|
||
|
||
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 = "8efc189b131f"
|
||
down_revision: str | Sequence[str] | None = "e1133a7c670a"
|
||
branch_labels: str | Sequence[str] | None = None
|
||
depends_on: str | Sequence[str] | None = None
|
||
|
||
# 评级等级种子（与 init-dev.sql 双写一致；口径见模型 docstring）
|
||
_GRADE_SEED = [
|
||
{"grade_name": "A", "min_score": 90, "max_score": 100, "sort_order": 1, "remark": "优秀"},
|
||
{"grade_name": "B", "min_score": 75, "max_score": 89, "sort_order": 2, "remark": "良好"},
|
||
{"grade_name": "C", "min_score": 60, "max_score": 74, "sort_order": 3, "remark": "合格"},
|
||
{"grade_name": "D", "min_score": 0, "max_score": 59, "sort_order": 4, "remark": "待改进"},
|
||
]
|
||
|
||
|
||
def upgrade() -> None:
|
||
"""应用迁移。"""
|
||
# ### commands auto generated by Alembic - please adjust! ###
|
||
op.create_table(
|
||
"customer_rating_grades",
|
||
sa.Column(
|
||
"id", mysql.BIGINT(unsigned=True), autoincrement=True, nullable=False, comment="主键ID"
|
||
),
|
||
sa.Column(
|
||
"grade_name", sa.String(length=32), nullable=False, comment="等级名称（如 A/优秀）"
|
||
),
|
||
sa.Column(
|
||
"min_score",
|
||
sa.Numeric(precision=5, scale=1),
|
||
nullable=False,
|
||
comment="总分下限（含）；判定=按 sort_order 升序首个 min_score ≤ 总分",
|
||
),
|
||
sa.Column(
|
||
"max_score",
|
||
sa.Numeric(precision=5, scale=1),
|
||
nullable=False,
|
||
comment="总分上限（展示用）",
|
||
),
|
||
sa.Column(
|
||
"sort_order",
|
||
sa.Integer(),
|
||
server_default="0",
|
||
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.PrimaryKeyConstraint("id", name=op.f("pk_customer_rating_grades")),
|
||
sa.UniqueConstraint("grade_name", name=op.f("uk_customer_rating_grades_grade_name")),
|
||
comment="客户评级等级配置表（等级映射可配置；判定按 sort_order 升序首个 min_score≤总分）",
|
||
mysql_charset="utf8mb4",
|
||
mysql_collate="utf8mb4_general_ci",
|
||
mysql_engine="InnoDB",
|
||
)
|
||
op.create_table(
|
||
"business_status_logs",
|
||
sa.Column(
|
||
"id", mysql.BIGINT(unsigned=True), autoincrement=True, nullable=False, comment="主键ID"
|
||
),
|
||
sa.Column(
|
||
"business_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=False,
|
||
comment="业务ID（固化，无外键；删除保护由服务层承担）",
|
||
),
|
||
sa.Column(
|
||
"old_status", mysql.TINYINT(), nullable=False, comment="变更前状态（创建首条为 1）"
|
||
),
|
||
sa.Column("new_status", mysql.TINYINT(), nullable=False, comment="变更后状态"),
|
||
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_business_status_logs_operator_id"),
|
||
ondelete="SET NULL",
|
||
),
|
||
sa.PrimaryKeyConstraint("id", name=op.f("pk_business_status_logs")),
|
||
comment="业务状态变更记录表（append-only）",
|
||
mysql_charset="utf8mb4",
|
||
mysql_collate="utf8mb4_general_ci",
|
||
mysql_engine="InnoDB",
|
||
)
|
||
op.create_index(
|
||
"idx_business_status_logs_business_id",
|
||
"business_status_logs",
|
||
["business_id"],
|
||
unique=False,
|
||
)
|
||
op.create_index(
|
||
"idx_business_status_logs_create_time",
|
||
"business_status_logs",
|
||
["create_time"],
|
||
unique=False,
|
||
)
|
||
op.create_table(
|
||
"businesses",
|
||
sa.Column(
|
||
"id", mysql.BIGINT(unsigned=True), autoincrement=True, nullable=False, comment="主键ID"
|
||
),
|
||
sa.Column(
|
||
"business_no",
|
||
sa.String(length=32),
|
||
nullable=False,
|
||
comment="业务编号，自动生成 YW{YYYYMMDD}-{4位当日序号}（报表合同编号）",
|
||
),
|
||
sa.Column(
|
||
"customer_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=False,
|
||
comment="客户ID（customers.id）",
|
||
),
|
||
sa.Column("business_date", sa.Date(), nullable=True, comment="业务日期（T+ 同步口径）"),
|
||
sa.Column(
|
||
"status",
|
||
mysql.TINYINT(),
|
||
server_default="1",
|
||
nullable=False,
|
||
comment="业务状态：1执行中 2已完成 3已终止（终态不可变更；T+同步不覆盖）",
|
||
),
|
||
sa.Column(
|
||
"owner_personnel_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=True,
|
||
comment="负责营销人员ID（核算归属锚点；NULL=待指定，M3 核算前置校验非空；变更留痕不回溯）",
|
||
),
|
||
sa.Column(
|
||
"assistant_personnel_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=True,
|
||
comment="协助营销人员ID（一期核算不使用，对照表 #15；仅记录事实）",
|
||
),
|
||
sa.Column(
|
||
"is_special",
|
||
mysql.TINYINT(),
|
||
server_default="0",
|
||
nullable=False,
|
||
comment="特殊业务标记：1是 0否（公司参与等）",
|
||
),
|
||
sa.Column("special_note", sa.String(length=255), nullable=True, comment="特殊业务说明"),
|
||
sa.Column(
|
||
"total_sale_amount",
|
||
sa.Numeric(precision=14, scale=2),
|
||
server_default="0",
|
||
nullable=True,
|
||
comment="销售额合计（明细行汇总冗余，服务层事务内维护，禁手工直改；不含运费口径）",
|
||
),
|
||
sa.Column(
|
||
"total_cost_amount",
|
||
sa.Numeric(precision=14, scale=2),
|
||
server_default="0",
|
||
nullable=True,
|
||
comment="成本合计（明细行汇总冗余，服务层事务内维护，禁手工直改）",
|
||
),
|
||
sa.Column(
|
||
"total_ship_amount",
|
||
sa.Numeric(precision=14, scale=2),
|
||
server_default="0",
|
||
nullable=True,
|
||
comment="发货金额合计（发货明细汇总冗余，服务层事务内维护，禁手工直改）",
|
||
),
|
||
sa.Column(
|
||
"total_received_amount",
|
||
sa.Numeric(precision=14, scale=2),
|
||
server_default="0",
|
||
nullable=True,
|
||
comment="回款合计（回款记录汇总冗余，服务层事务内维护；M3 起付判定直接消费）",
|
||
),
|
||
sa.Column(
|
||
"total_freight_amount",
|
||
sa.Numeric(precision=14, scale=2),
|
||
server_default="0",
|
||
nullable=True,
|
||
comment="运费合计（发货单汇总冗余，仅记录展示；不计入毛利润口径，对照表 #2）",
|
||
),
|
||
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(
|
||
["assistant_personnel_id"],
|
||
["personnel.id"],
|
||
name=op.f("fk_businesses_assistant_personnel_id"),
|
||
ondelete="RESTRICT",
|
||
),
|
||
sa.ForeignKeyConstraint(
|
||
["customer_id"],
|
||
["customers.id"],
|
||
name=op.f("fk_businesses_customer_id"),
|
||
ondelete="RESTRICT",
|
||
),
|
||
sa.ForeignKeyConstraint(
|
||
["owner_personnel_id"],
|
||
["personnel.id"],
|
||
name=op.f("fk_businesses_owner_personnel_id"),
|
||
ondelete="RESTRICT",
|
||
),
|
||
sa.PrimaryKeyConstraint("id", name=op.f("pk_businesses")),
|
||
sa.UniqueConstraint("business_no", name=op.f("uk_businesses_business_no")),
|
||
comment="业务表（业务编号自动生成；T+同步不覆盖业务状态）",
|
||
mysql_charset="utf8mb4",
|
||
mysql_collate="utf8mb4_general_ci",
|
||
mysql_engine="InnoDB",
|
||
)
|
||
op.create_index("idx_businesses_business_date", "businesses", ["business_date"], unique=False)
|
||
op.create_index("idx_businesses_customer_id", "businesses", ["customer_id"], unique=False)
|
||
op.create_index(
|
||
"idx_businesses_owner_personnel_id", "businesses", ["owner_personnel_id"], unique=False
|
||
)
|
||
op.create_index(op.f("idx_businesses_source_no"), "businesses", ["source_no"], unique=False)
|
||
op.create_index("idx_businesses_status", "businesses", ["status"], unique=False)
|
||
op.create_table(
|
||
"business_items",
|
||
sa.Column(
|
||
"id", mysql.BIGINT(unsigned=True), autoincrement=True, nullable=False, comment="主键ID"
|
||
),
|
||
sa.Column(
|
||
"business_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=False,
|
||
comment="业务ID（businesses.id）",
|
||
),
|
||
sa.Column(
|
||
"product_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=True,
|
||
comment="产品ID（products.id；NULL=自由录入，未入产品库）",
|
||
),
|
||
sa.Column(
|
||
"product_name",
|
||
sa.String(length=128),
|
||
nullable=False,
|
||
comment="产品名称（引用产品时冗余固化）",
|
||
),
|
||
sa.Column(
|
||
"spec_model",
|
||
sa.String(length=128),
|
||
server_default="",
|
||
nullable=False,
|
||
comment="规格型号（冗余固化；空串对齐 products 组合唯一键口径）",
|
||
),
|
||
sa.Column("unit", sa.String(length=16), nullable=True, comment="计量单位"),
|
||
sa.Column(
|
||
"product_category",
|
||
sa.String(length=32),
|
||
nullable=False,
|
||
comment="产品类别（行级折算锚点，混合业务按产品逐条计算，对照表 #14；值域同 PRODUCT_CATEGORIES）",
|
||
),
|
||
sa.Column(
|
||
"quantity", sa.Numeric(precision=14, scale=2), nullable=False, comment="数量（>0）"
|
||
),
|
||
sa.Column(
|
||
"sale_price",
|
||
sa.Numeric(precision=14, scale=4),
|
||
nullable=True,
|
||
comment="销售单价（元；T+ 同步或人工）",
|
||
),
|
||
sa.Column(
|
||
"sale_amount",
|
||
sa.Numeric(precision=14, scale=2),
|
||
nullable=False,
|
||
comment="销售额 = 数量×销售单价（服务层计算固化；不含运费口径，毛利润率分母）",
|
||
),
|
||
sa.Column(
|
||
"cost_amount",
|
||
sa.Numeric(precision=14, scale=2),
|
||
nullable=True,
|
||
comment="成本额（T+ 同步或人工录入，来源待 T+ 确认；毛利润额 = 销售额 − 成本额）",
|
||
),
|
||
sa.Column(
|
||
"guided_price",
|
||
sa.Numeric(precision=14, scale=4),
|
||
nullable=True,
|
||
comment="指导价（人工录入；T+同步不覆盖，需规 2.2.1/2.2.4）",
|
||
),
|
||
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(
|
||
["business_id"],
|
||
["businesses.id"],
|
||
name=op.f("fk_business_items_business_id"),
|
||
ondelete="RESTRICT",
|
||
),
|
||
sa.ForeignKeyConstraint(
|
||
["product_id"],
|
||
["products.id"],
|
||
name=op.f("fk_business_items_product_id"),
|
||
ondelete="RESTRICT",
|
||
),
|
||
sa.PrimaryKeyConstraint("id", name=op.f("pk_business_items")),
|
||
comment="业务产品明细表（毛利润行级源数据；指导价 T+同步不覆盖）",
|
||
mysql_charset="utf8mb4",
|
||
mysql_collate="utf8mb4_general_ci",
|
||
mysql_engine="InnoDB",
|
||
)
|
||
op.create_index(
|
||
"idx_business_items_business_id", "business_items", ["business_id"], unique=False
|
||
)
|
||
op.create_index(
|
||
"idx_business_items_product_category", "business_items", ["product_category"], unique=False
|
||
)
|
||
op.create_index("idx_business_items_product_id", "business_items", ["product_id"], unique=False)
|
||
op.create_table(
|
||
"shipments",
|
||
sa.Column(
|
||
"id", mysql.BIGINT(unsigned=True), autoincrement=True, nullable=False, comment="主键ID"
|
||
),
|
||
sa.Column(
|
||
"shipment_no",
|
||
sa.String(length=32),
|
||
nullable=False,
|
||
comment="发货单号，自动生成 SH{YYYYMMDD}-{4位当日序号}（内部追溯用）",
|
||
),
|
||
sa.Column(
|
||
"business_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=False,
|
||
comment="业务ID（businesses.id；终态业务禁止新发货）",
|
||
),
|
||
sa.Column(
|
||
"ship_date",
|
||
sa.Date(),
|
||
nullable=False,
|
||
comment="发货日期（回款天数基准日，发货日首日=第 1 天）",
|
||
),
|
||
sa.Column(
|
||
"freight_amount",
|
||
sa.Numeric(precision=14, scale=2),
|
||
server_default="0",
|
||
nullable=False,
|
||
comment="运费金额（仅记录展示；不计入毛利润口径，对照表 #2）",
|
||
),
|
||
sa.Column(
|
||
"total_amount",
|
||
sa.Numeric(precision=14, scale=2),
|
||
server_default="0",
|
||
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.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_id"],
|
||
["businesses.id"],
|
||
name=op.f("fk_shipments_business_id"),
|
||
ondelete="RESTRICT",
|
||
),
|
||
sa.PrimaryKeyConstraint("id", name=op.f("pk_shipments")),
|
||
sa.UniqueConstraint("shipment_no", name=op.f("uk_shipments_shipment_no")),
|
||
comment="发货单表（发货日为回款天数基准日，发货日首日计第 1 天）",
|
||
mysql_charset="utf8mb4",
|
||
mysql_collate="utf8mb4_general_ci",
|
||
mysql_engine="InnoDB",
|
||
)
|
||
op.create_index("idx_shipments_business_id", "shipments", ["business_id"], unique=False)
|
||
op.create_index("idx_shipments_ship_date", "shipments", ["ship_date"], unique=False)
|
||
op.create_index(op.f("idx_shipments_source_no"), "shipments", ["source_no"], unique=False)
|
||
op.create_table(
|
||
"receipts",
|
||
sa.Column(
|
||
"id", mysql.BIGINT(unsigned=True), autoincrement=True, nullable=False, comment="主键ID"
|
||
),
|
||
sa.Column(
|
||
"receipt_batch_no",
|
||
sa.String(length=32),
|
||
nullable=False,
|
||
comment="回款批次号，自动生成 HK{YYYYMMDD}-{4位当日序号}（系统内唯一；T+ 同步回款以 source_no 幂等）",
|
||
),
|
||
sa.Column(
|
||
"business_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=False,
|
||
comment="业务ID（businesses.id）",
|
||
),
|
||
sa.Column(
|
||
"shipment_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=True,
|
||
comment="发货单ID（shipments.id；手动指定的回款对应批次，须与本行业务一致；NULL=暂未指定）",
|
||
),
|
||
sa.Column(
|
||
"receipt_date",
|
||
sa.Date(),
|
||
nullable=False,
|
||
comment="回款到账日（M3 按此日匹配参数版本，对照表 #11）",
|
||
),
|
||
sa.Column(
|
||
"amount",
|
||
sa.Numeric(precision=14, scale=2),
|
||
nullable=False,
|
||
comment="回款金额（≥0；一期禁负数/红字）",
|
||
),
|
||
sa.Column(
|
||
"is_barter",
|
||
mysql.TINYINT(),
|
||
server_default="0",
|
||
nullable=False,
|
||
comment="以物抵顶标记：1是 0否（M3 核算按应发绩效×barter.discount 折算，M2 仅标记）",
|
||
),
|
||
sa.Column(
|
||
"settlement_status",
|
||
mysql.TINYINT(),
|
||
server_default="0",
|
||
nullable=False,
|
||
comment="绩效核算状态：0未核算 1已核算（M3 核算防重锚点；已核算回款禁改禁删）",
|
||
),
|
||
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_id"],
|
||
["businesses.id"],
|
||
name=op.f("fk_receipts_business_id"),
|
||
ondelete="RESTRICT",
|
||
),
|
||
sa.ForeignKeyConstraint(
|
||
["shipment_id"],
|
||
["shipments.id"],
|
||
name=op.f("fk_receipts_shipment_id"),
|
||
ondelete="RESTRICT",
|
||
),
|
||
sa.PrimaryKeyConstraint("id", name=op.f("pk_receipts")),
|
||
sa.UniqueConstraint("receipt_batch_no", name=op.f("uk_receipts_receipt_batch_no")),
|
||
comment="回款表（手动指定发货批次；已核算回款禁改禁删）",
|
||
mysql_charset="utf8mb4",
|
||
mysql_collate="utf8mb4_general_ci",
|
||
mysql_engine="InnoDB",
|
||
)
|
||
op.create_index("idx_receipts_business_id", "receipts", ["business_id"], unique=False)
|
||
op.create_index("idx_receipts_receipt_date", "receipts", ["receipt_date"], unique=False)
|
||
op.create_index(
|
||
"idx_receipts_settlement_status", "receipts", ["settlement_status"], unique=False
|
||
)
|
||
op.create_index("idx_receipts_shipment_id", "receipts", ["shipment_id"], unique=False)
|
||
op.create_index(op.f("idx_receipts_source_no"), "receipts", ["source_no"], unique=False)
|
||
op.create_table(
|
||
"shipment_items",
|
||
sa.Column(
|
||
"id", mysql.BIGINT(unsigned=True), autoincrement=True, nullable=False, comment="主键ID"
|
||
),
|
||
sa.Column(
|
||
"shipment_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=False,
|
||
comment="发货单ID（shipments.id）",
|
||
),
|
||
sa.Column(
|
||
"product_id",
|
||
mysql.BIGINT(unsigned=True),
|
||
nullable=True,
|
||
comment="产品ID（products.id；NULL=自由录入）",
|
||
),
|
||
sa.Column(
|
||
"product_name",
|
||
sa.String(length=128),
|
||
nullable=False,
|
||
comment="产品名称（引用产品时冗余固化）",
|
||
),
|
||
sa.Column(
|
||
"spec_model",
|
||
sa.String(length=128),
|
||
server_default="",
|
||
nullable=False,
|
||
comment="规格型号（冗余固化；空串对齐 products 组合唯一键口径）",
|
||
),
|
||
sa.Column("unit", sa.String(length=16), nullable=True, comment="计量单位"),
|
||
sa.Column(
|
||
"quantity", sa.Numeric(precision=14, scale=2), nullable=False, comment="发货数量（>0）"
|
||
),
|
||
sa.Column(
|
||
"sale_price",
|
||
sa.Numeric(precision=14, scale=4),
|
||
nullable=True,
|
||
comment="发货单价（元；T+ 同步或人工，口径待 IT 确认）",
|
||
),
|
||
sa.Column(
|
||
"amount",
|
||
sa.Numeric(precision=14, scale=2),
|
||
nullable=False,
|
||
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(
|
||
["product_id"],
|
||
["products.id"],
|
||
name=op.f("fk_shipment_items_product_id"),
|
||
ondelete="RESTRICT",
|
||
),
|
||
sa.ForeignKeyConstraint(
|
||
["shipment_id"],
|
||
["shipments.id"],
|
||
name=op.f("fk_shipment_items_shipment_id"),
|
||
ondelete="RESTRICT",
|
||
),
|
||
sa.PrimaryKeyConstraint("id", name=op.f("pk_shipment_items")),
|
||
comment="发货产品明细表",
|
||
mysql_charset="utf8mb4",
|
||
mysql_collate="utf8mb4_general_ci",
|
||
mysql_engine="InnoDB",
|
||
)
|
||
op.create_index("idx_shipment_items_product_id", "shipment_items", ["product_id"], unique=False)
|
||
op.create_index(
|
||
"idx_shipment_items_shipment_id", "shipment_items", ["shipment_id"], unique=False
|
||
)
|
||
# ### end Alembic commands ###
|
||
# 评级等级种子（等级映射可配置；双轨：init-dev.sql 同步写入）
|
||
op.bulk_insert(
|
||
sa.table(
|
||
"customer_rating_grades",
|
||
sa.column("grade_name", sa.String),
|
||
sa.column("min_score", sa.Numeric),
|
||
sa.column("max_score", sa.Numeric),
|
||
sa.column("sort_order", sa.Integer),
|
||
sa.column("remark", sa.String),
|
||
),
|
||
_GRADE_SEED,
|
||
)
|
||
|
||
|
||
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 会连带删除索引与外键。
|
||
"""
|
||
# 整表回滚只保留 drop_table（DROP TABLE 连带删除索引与外键）；
|
||
# 逐条 drop_index 会先于 drop_table 摘掉外键支撑索引而报 MySQL 1553
|
||
op.drop_table("shipment_items")
|
||
op.drop_table("receipts")
|
||
op.drop_table("shipments")
|
||
op.drop_table("business_items")
|
||
op.drop_table("businesses")
|
||
op.drop_table("business_status_logs")
|
||
op.drop_table("customer_rating_grades")
|