-- Running upgrade e1133a7c670a -> 8efc189b131f

CREATE TABLE customer_rating_grades (
    id BIGINT UNSIGNED NOT NULL COMMENT '主键ID' AUTO_INCREMENT, 
    grade_name VARCHAR(32) NOT NULL COMMENT '等级名称（如 A/优秀）', 
    min_score NUMERIC(5, 1) NOT NULL COMMENT '总分下限（含）；判定=按 sort_order 升序首个 min_score ≤ 总分', 
    max_score NUMERIC(5, 1) NOT NULL COMMENT '总分上限（展示用）', 
    sort_order INTEGER NOT NULL COMMENT '排序（判定顺序，升序）' DEFAULT '0', 
    remark VARCHAR(255) COMMENT '说明', 
    create_time DATETIME NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP, 
    update_time DATETIME NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    CONSTRAINT pk_customer_rating_grades PRIMARY KEY (id), 
    CONSTRAINT uk_customer_rating_grades_grade_name UNIQUE (grade_name)
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='客户评级等级配置表（等级映射可配置；判定按 sort_order 升序首个 min_score≤总分）' COLLATE utf8mb4_general_ci;

CREATE TABLE business_status_logs (
    id BIGINT UNSIGNED NOT NULL COMMENT '主键ID' AUTO_INCREMENT, 
    business_id BIGINT UNSIGNED NOT NULL COMMENT '业务ID（固化，无外键；删除保护由服务层承担）', 
    old_status TINYINT NOT NULL COMMENT '变更前状态（创建首条为 1）', 
    new_status TINYINT NOT NULL COMMENT '变更后状态', 
    change_reason VARCHAR(255) COMMENT '变更原因（终止时必填）', 
    operator_id BIGINT UNSIGNED COMMENT '操作人ID（账号删除后置空）', 
    operator_name VARCHAR(64) NOT NULL COMMENT '操作人用户名（冗余固化）', 
    create_time DATETIME NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP, 
    CONSTRAINT pk_business_status_logs PRIMARY KEY (id), 
    CONSTRAINT fk_business_status_logs_operator_id FOREIGN KEY(operator_id) REFERENCES users (id) ON DELETE SET NULL
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='业务状态变更记录表（append-only）' COLLATE utf8mb4_general_ci;

CREATE INDEX idx_business_status_logs_business_id ON business_status_logs (business_id);

CREATE INDEX idx_business_status_logs_create_time ON business_status_logs (create_time);

CREATE TABLE businesses (
    id BIGINT UNSIGNED NOT NULL COMMENT '主键ID' AUTO_INCREMENT, 
    business_no VARCHAR(32) NOT NULL COMMENT '业务编号，自动生成 YW{YYYYMMDD}-{4位当日序号}（报表合同编号）', 
    customer_id BIGINT UNSIGNED NOT NULL COMMENT '客户ID（customers.id）', 
    business_date DATE COMMENT '业务日期（T+ 同步口径）', 
    status TINYINT NOT NULL COMMENT '业务状态：1执行中 2已完成 3已终止（终态不可变更；T+同步不覆盖）' DEFAULT '1', 
    owner_personnel_id BIGINT UNSIGNED COMMENT '负责营销人员ID（核算归属锚点；NULL=待指定，M3 核算前置校验非空；变更留痕不回溯）', 
    assistant_personnel_id BIGINT UNSIGNED COMMENT '协助营销人员ID（一期核算不使用，对照表 #15；仅记录事实）', 
    is_special TINYINT NOT NULL COMMENT '特殊业务标记：1是 0否（公司参与等）' DEFAULT '0', 
    special_note VARCHAR(255) COMMENT '特殊业务说明', 
    total_sale_amount NUMERIC(14, 2) COMMENT '销售额合计（明细行汇总冗余，服务层事务内维护，禁手工直改；不含运费口径）' DEFAULT '0', 
    total_cost_amount NUMERIC(14, 2) COMMENT '成本合计（明细行汇总冗余，服务层事务内维护，禁手工直改）' DEFAULT '0', 
    total_ship_amount NUMERIC(14, 2) COMMENT '发货金额合计（发货明细汇总冗余，服务层事务内维护，禁手工直改）' DEFAULT '0', 
    total_received_amount NUMERIC(14, 2) COMMENT '回款合计（回款记录汇总冗余，服务层事务内维护；M3 起付判定直接消费）' DEFAULT '0', 
    total_freight_amount NUMERIC(14, 2) COMMENT '运费合计（发货单汇总冗余，仅记录展示；不计入毛利润口径，对照表 #2）' DEFAULT '0', 
    remark VARCHAR(255) COMMENT '备注', 
    create_time DATETIME NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP, 
    update_time DATETIME NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    source_no VARCHAR(64) COMMENT 'T+ 原始单据号（同步幂等去重锚点；唯一键待 IT 定键后升级）', 
    t_plus_id VARCHAR(64) COMMENT 'T+ 系统内部标识', 
    sync_batch_no VARCHAR(32) COMMENT '同步批次号（同步模块写入）', 
    synced_at DATETIME COMMENT '最近同步时间', 
    CONSTRAINT pk_businesses PRIMARY KEY (id), 
    CONSTRAINT fk_businesses_assistant_personnel_id FOREIGN KEY(assistant_personnel_id) REFERENCES personnel (id) ON DELETE RESTRICT, 
    CONSTRAINT fk_businesses_customer_id FOREIGN KEY(customer_id) REFERENCES customers (id) ON DELETE RESTRICT, 
    CONSTRAINT fk_businesses_owner_personnel_id FOREIGN KEY(owner_personnel_id) REFERENCES personnel (id) ON DELETE RESTRICT, 
    CONSTRAINT uk_businesses_business_no UNIQUE (business_no)
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='业务表（业务编号自动生成；T+同步不覆盖业务状态）' COLLATE utf8mb4_general_ci;

CREATE INDEX idx_businesses_business_date ON businesses (business_date);

CREATE INDEX idx_businesses_customer_id ON businesses (customer_id);

CREATE INDEX idx_businesses_owner_personnel_id ON businesses (owner_personnel_id);

CREATE INDEX idx_businesses_source_no ON businesses (source_no);

CREATE INDEX idx_businesses_status ON businesses (status);

CREATE TABLE business_items (
    id BIGINT UNSIGNED NOT NULL COMMENT '主键ID' AUTO_INCREMENT, 
    business_id BIGINT UNSIGNED NOT NULL COMMENT '业务ID（businesses.id）', 
    product_id BIGINT UNSIGNED COMMENT '产品ID（products.id；NULL=自由录入，未入产品库）', 
    product_name VARCHAR(128) NOT NULL COMMENT '产品名称（引用产品时冗余固化）', 
    spec_model VARCHAR(128) NOT NULL COMMENT '规格型号（冗余固化；空串对齐 products 组合唯一键口径）' DEFAULT '', 
    unit VARCHAR(16) COMMENT '计量单位', 
    product_category VARCHAR(32) NOT NULL COMMENT '产品类别（行级折算锚点，混合业务按产品逐条计算，对照表 #14；值域同 PRODUCT_CATEGORIES）', 
    quantity NUMERIC(14, 2) NOT NULL COMMENT '数量（>0）', 
    sale_price NUMERIC(14, 4) COMMENT '销售单价（元；T+ 同步或人工）', 
    sale_amount NUMERIC(14, 2) NOT NULL COMMENT '销售额 = 数量×销售单价（服务层计算固化；不含运费口径，毛利润率分母）', 
    cost_amount NUMERIC(14, 2) COMMENT '成本额（T+ 同步或人工录入，来源待 T+ 确认；毛利润额 = 销售额 − 成本额）', 
    guided_price NUMERIC(14, 4) COMMENT '指导价（人工录入；T+同步不覆盖，需规 2.2.1/2.2.4）', 
    remark VARCHAR(255) COMMENT '备注', 
    create_time DATETIME NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP, 
    update_time DATETIME NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    CONSTRAINT pk_business_items PRIMARY KEY (id), 
    CONSTRAINT fk_business_items_business_id FOREIGN KEY(business_id) REFERENCES businesses (id) ON DELETE RESTRICT, 
    CONSTRAINT fk_business_items_product_id FOREIGN KEY(product_id) REFERENCES products (id) ON DELETE RESTRICT
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='业务产品明细表（毛利润行级源数据；指导价 T+同步不覆盖）' COLLATE utf8mb4_general_ci;

CREATE INDEX idx_business_items_business_id ON business_items (business_id);

CREATE INDEX idx_business_items_product_category ON business_items (product_category);

CREATE INDEX idx_business_items_product_id ON business_items (product_id);

CREATE TABLE shipments (
    id BIGINT UNSIGNED NOT NULL COMMENT '主键ID' AUTO_INCREMENT, 
    shipment_no VARCHAR(32) NOT NULL COMMENT '发货单号，自动生成 SH{YYYYMMDD}-{4位当日序号}（内部追溯用）', 
    business_id BIGINT UNSIGNED NOT NULL COMMENT '业务ID（businesses.id；终态业务禁止新发货）', 
    ship_date DATE NOT NULL COMMENT '发货日期（回款天数基准日，发货日首日=第 1 天）', 
    freight_amount NUMERIC(14, 2) NOT NULL COMMENT '运费金额（仅记录展示；不计入毛利润口径，对照表 #2）' DEFAULT '0', 
    total_amount NUMERIC(14, 2) NOT NULL COMMENT '发货总金额（明细行汇总冗余，服务层事务内维护，禁手工直改）' DEFAULT '0', 
    remark VARCHAR(255) COMMENT '备注', 
    create_time DATETIME NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP, 
    update_time DATETIME NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    source_no VARCHAR(64) COMMENT 'T+ 原始单据号（同步幂等去重锚点；唯一键待 IT 定键后升级）', 
    t_plus_id VARCHAR(64) COMMENT 'T+ 系统内部标识', 
    sync_batch_no VARCHAR(32) COMMENT '同步批次号（同步模块写入）', 
    synced_at DATETIME COMMENT '最近同步时间', 
    CONSTRAINT pk_shipments PRIMARY KEY (id), 
    CONSTRAINT fk_shipments_business_id FOREIGN KEY(business_id) REFERENCES businesses (id) ON DELETE RESTRICT, 
    CONSTRAINT uk_shipments_shipment_no UNIQUE (shipment_no)
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='发货单表（发货日为回款天数基准日，发货日首日计第 1 天）' COLLATE utf8mb4_general_ci;

CREATE INDEX idx_shipments_business_id ON shipments (business_id);

CREATE INDEX idx_shipments_ship_date ON shipments (ship_date);

CREATE INDEX idx_shipments_source_no ON shipments (source_no);

CREATE TABLE receipts (
    id BIGINT UNSIGNED NOT NULL COMMENT '主键ID' AUTO_INCREMENT, 
    receipt_batch_no VARCHAR(32) NOT NULL COMMENT '回款批次号，自动生成 HK{YYYYMMDD}-{4位当日序号}（系统内唯一；T+ 同步回款以 source_no 幂等）', 
    business_id BIGINT UNSIGNED NOT NULL COMMENT '业务ID（businesses.id）', 
    shipment_id BIGINT UNSIGNED COMMENT '发货单ID（shipments.id；手动指定的回款对应批次，须与本行业务一致；NULL=暂未指定）', 
    receipt_date DATE NOT NULL COMMENT '回款到账日（M3 按此日匹配参数版本，对照表 #11）', 
    amount NUMERIC(14, 2) NOT NULL COMMENT '回款金额（≥0；一期禁负数/红字）', 
    is_barter TINYINT NOT NULL COMMENT '以物抵顶标记：1是 0否（M3 核算按应发绩效×barter.discount 折算，M2 仅标记）' DEFAULT '0', 
    settlement_status TINYINT NOT NULL COMMENT '绩效核算状态：0未核算 1已核算（M3 核算防重锚点；已核算回款禁改禁删）' DEFAULT '0', 
    remark VARCHAR(255) COMMENT '备注', 
    create_time DATETIME NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP, 
    update_time DATETIME NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    source_no VARCHAR(64) COMMENT 'T+ 原始单据号（同步幂等去重锚点；唯一键待 IT 定键后升级）', 
    t_plus_id VARCHAR(64) COMMENT 'T+ 系统内部标识', 
    sync_batch_no VARCHAR(32) COMMENT '同步批次号（同步模块写入）', 
    synced_at DATETIME COMMENT '最近同步时间', 
    CONSTRAINT pk_receipts PRIMARY KEY (id), 
    CONSTRAINT fk_receipts_business_id FOREIGN KEY(business_id) REFERENCES businesses (id) ON DELETE RESTRICT, 
    CONSTRAINT fk_receipts_shipment_id FOREIGN KEY(shipment_id) REFERENCES shipments (id) ON DELETE RESTRICT, 
    CONSTRAINT uk_receipts_receipt_batch_no UNIQUE (receipt_batch_no)
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='回款表（手动指定发货批次；已核算回款禁改禁删）' COLLATE utf8mb4_general_ci;

CREATE INDEX idx_receipts_business_id ON receipts (business_id);

CREATE INDEX idx_receipts_receipt_date ON receipts (receipt_date);

CREATE INDEX idx_receipts_settlement_status ON receipts (settlement_status);

CREATE INDEX idx_receipts_shipment_id ON receipts (shipment_id);

CREATE INDEX idx_receipts_source_no ON receipts (source_no);

CREATE TABLE shipment_items (
    id BIGINT UNSIGNED NOT NULL COMMENT '主键ID' AUTO_INCREMENT, 
    shipment_id BIGINT UNSIGNED NOT NULL COMMENT '发货单ID（shipments.id）', 
    product_id BIGINT UNSIGNED COMMENT '产品ID（products.id；NULL=自由录入）', 
    product_name VARCHAR(128) NOT NULL COMMENT '产品名称（引用产品时冗余固化）', 
    spec_model VARCHAR(128) NOT NULL COMMENT '规格型号（冗余固化；空串对齐 products 组合唯一键口径）' DEFAULT '', 
    unit VARCHAR(16) COMMENT '计量单位', 
    quantity NUMERIC(14, 2) NOT NULL COMMENT '发货数量（>0）', 
    sale_price NUMERIC(14, 4) COMMENT '发货单价（元；T+ 同步或人工，口径待 IT 确认）', 
    amount NUMERIC(14, 2) NOT NULL COMMENT '行金额 = 数量×单价（服务层计算固化，不信任前端传值）', 
    create_time DATETIME NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP, 
    update_time DATETIME NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    CONSTRAINT pk_shipment_items PRIMARY KEY (id), 
    CONSTRAINT fk_shipment_items_product_id FOREIGN KEY(product_id) REFERENCES products (id) ON DELETE RESTRICT, 
    CONSTRAINT fk_shipment_items_shipment_id FOREIGN KEY(shipment_id) REFERENCES shipments (id) ON DELETE RESTRICT
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='发货产品明细表' COLLATE utf8mb4_general_ci;

CREATE INDEX idx_shipment_items_product_id ON shipment_items (product_id);

CREATE INDEX idx_shipment_items_shipment_id ON shipment_items (shipment_id);

INSERT INTO customer_rating_grades (grade_name, min_score, max_score, sort_order, remark) VALUES ('A', 90, 100, 1, '优秀');

INSERT INTO customer_rating_grades (grade_name, min_score, max_score, sort_order, remark) VALUES ('B', 75, 89, 2, '良好');

INSERT INTO customer_rating_grades (grade_name, min_score, max_score, sort_order, remark) VALUES ('C', 60, 74, 3, '合格');

INSERT INTO customer_rating_grades (grade_name, min_score, max_score, sort_order, remark) VALUES ('D', 0, 59, 4, '待改进');

UPDATE alembic_version SET version_num='8efc189b131f' WHERE alembic_version.version_num = 'e1133a7c670a';