sqxp/docs/sql/migration_20260414_order_client_supplier_settlement.sql

-- 订单客户/商家结算模型升级
-- 1) 订单增加客户/商家结算状态字段
-- 2) 新增客户结算主表/明细表及历史表
-- 3) 新增供应商结算主表/明细表及历史表

START TRANSACTION;

-- =========================
-- 1. 订单表扩展
-- =========================
ALTER TABLE biz_order
  ADD COLUMN client_settlement_status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT '客户结算状态: pending/settled' AFTER payment_method,
  ADD COLUMN supplier_settlement_status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT '供应商结算状态: pending/settled' AFTER client_settlement_status,
  ADD COLUMN client_settlement_at DATETIME NULL COMMENT '客户结算时间' AFTER supplier_settlement_status,
  ADD COLUMN supplier_settlement_at DATETIME NULL COMMENT '供应商结算时间' AFTER client_settlement_at;

ALTER TABLE biz_order_his
  ADD COLUMN client_settlement_status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT '客户结算状态: pending/settled' AFTER payment_method,
  ADD COLUMN supplier_settlement_status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT '供应商结算状态: pending/settled' AFTER client_settlement_status,
  ADD COLUMN client_settlement_at DATETIME NULL COMMENT '客户结算时间' AFTER supplier_settlement_status,
  ADD COLUMN supplier_settlement_at DATETIME NULL COMMENT '供应商结算时间' AFTER client_settlement_at;

CREATE INDEX idx_biz_order_client_settlement ON biz_order (is_del, client_settlement_status, payment_method, create_at);
CREATE INDEX idx_biz_order_supplier_settlement ON biz_order (is_del, supplier_settlement_status, create_at);

-- 线上支付订单默认客户已结算
UPDATE biz_order
SET client_settlement_status = 'settled',
    client_settlement_at = IFNULL(payment_time, update_at),
    update_at = NOW()
WHERE is_del = 0
  AND (payment_method = 'online' OR payment_method = 'wechat')
  AND client_settlement_status <> 'settled';

-- =========================
-- 2. 客户结算表
-- =========================
CREATE TABLE IF NOT EXISTS ord_client_settlement (
  id VARCHAR(32) PRIMARY KEY COMMENT '主键ID',
  settlement_no VARCHAR(50) NOT NULL UNIQUE COMMENT '结算单号',
  client_id VARCHAR(32) NOT NULL COMMENT '客户ID',
  client_name VARCHAR(100) NULL COMMENT '客户名称快照',
  order_count INT NOT NULL DEFAULT 0 COMMENT '订单数量',
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT '结算总金额',
  voucher_urls TEXT NULL COMMENT '结算凭证URL(JSON数组)',
  remark VARCHAR(500) NULL COMMENT '结算备注',
  settlement_at DATETIME NOT NULL COMMENT '结算时间',
  status VARCHAR(20) NOT NULL DEFAULT 'done' COMMENT '结算单状态',
  is_del TINYINT NOT NULL DEFAULT 0 COMMENT '是否删除',
  create_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  create_by VARCHAR(32) NULL COMMENT '创建人ID',
  create_name VARCHAR(100) NULL COMMENT '创建人姓名',
  update_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  update_by VARCHAR(32) NULL COMMENT '修改人ID',
  KEY idx_ocs_client (client_id),
  KEY idx_ocs_settlement_at (settlement_at),
  KEY idx_ocs_create_at (create_at),
  KEY idx_ocs_is_del (is_del)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户结算主表';

CREATE TABLE IF NOT EXISTS ord_client_settlement_item (
  id VARCHAR(32) PRIMARY KEY COMMENT '主键ID',
  settlement_id VARCHAR(32) NOT NULL COMMENT '结算主表ID',
  order_id VARCHAR(32) NOT NULL COMMENT '订单ID',
  order_no VARCHAR(50) NOT NULL COMMENT '订单号',
  client_id VARCHAR(32) NOT NULL COMMENT '客户ID',
  order_amount DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT '订单结算金额',
  is_del TINYINT NOT NULL DEFAULT 0 COMMENT '是否删除',
  create_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  create_by VARCHAR(32) NULL COMMENT '创建人ID',
  create_name VARCHAR(100) NULL COMMENT '创建人姓名',
  update_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  update_by VARCHAR(32) NULL COMMENT '修改人ID',
  KEY idx_ocsi_settlement_id (settlement_id),
  KEY idx_ocsi_order_id (order_id),
  KEY idx_ocsi_client_id (client_id),
  KEY idx_ocsi_is_del (is_del)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户结算明细表';

CREATE TABLE IF NOT EXISTS ord_client_settlement_his (
  id VARCHAR(32) NOT NULL COMMENT '主键ID',
  settlement_no VARCHAR(50) NOT NULL COMMENT '结算单号',
  client_id VARCHAR(32) NOT NULL COMMENT '客户ID',
  client_name VARCHAR(100) NULL COMMENT '客户名称快照',
  order_count INT NOT NULL DEFAULT 0 COMMENT '订单数量',
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT '结算总金额',
  voucher_urls TEXT NULL COMMENT '结算凭证URL(JSON数组)',
  remark VARCHAR(500) NULL COMMENT '结算备注',
  settlement_at DATETIME NOT NULL COMMENT '结算时间',
  status VARCHAR(20) NOT NULL DEFAULT 'done' COMMENT '结算单状态',
  create_at DATETIME NOT NULL COMMENT '创建时间',
  create_by VARCHAR(32) NULL COMMENT '创建人ID',
  create_name VARCHAR(100) NULL COMMENT '创建人姓名',
  update_at DATETIME NULL COMMENT '修改时间',
  update_by VARCHAR(32) NULL COMMENT '修改人ID',
  his_create_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户结算主表历史';

CREATE TABLE IF NOT EXISTS ord_client_settlement_item_his (
  id VARCHAR(32) NOT NULL COMMENT '主键ID',
  settlement_id VARCHAR(32) NOT NULL COMMENT '结算主表ID',
  order_id VARCHAR(32) NOT NULL COMMENT '订单ID',
  order_no VARCHAR(50) NOT NULL COMMENT '订单号',
  client_id VARCHAR(32) NOT NULL COMMENT '客户ID',
  order_amount DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT '订单结算金额',
  create_at DATETIME NOT NULL COMMENT '创建时间',
  create_by VARCHAR(32) NULL COMMENT '创建人ID',
  create_name VARCHAR(100) NULL COMMENT '创建人姓名',
  update_at DATETIME NULL COMMENT '修改时间',
  update_by VARCHAR(32) NULL COMMENT '修改人ID',
  his_create_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户结算明细历史表';

-- =========================
-- 3. 供应商结算表
-- =========================
CREATE TABLE IF NOT EXISTS ord_supplier_settlement (
  id VARCHAR(32) PRIMARY KEY COMMENT '主键ID',
  settlement_no VARCHAR(50) NOT NULL UNIQUE COMMENT '结算单号',
  supplier_id VARCHAR(32) NOT NULL COMMENT '供应商ID',
  supplier_name VARCHAR(100) NULL COMMENT '供应商名称快照',
  order_count INT NOT NULL DEFAULT 0 COMMENT '订单数量',
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT '结算总金额',
  voucher_urls TEXT NULL COMMENT '结算凭证URL(JSON数组)',
  remark VARCHAR(500) NULL COMMENT '结算备注',
  settlement_at DATETIME NOT NULL COMMENT '结算时间',
  status VARCHAR(20) NOT NULL DEFAULT 'done' COMMENT '结算单状态',
  is_del TINYINT NOT NULL DEFAULT 0 COMMENT '是否删除',
  create_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  create_by VARCHAR(32) NULL COMMENT '创建人ID',
  create_name VARCHAR(100) NULL COMMENT '创建人姓名',
  update_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  update_by VARCHAR(32) NULL COMMENT '修改人ID',
  KEY idx_oss_supplier (supplier_id),
  KEY idx_oss_settlement_at (settlement_at),
  KEY idx_oss_create_at (create_at),
  KEY idx_oss_is_del (is_del)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='供应商结算主表';

CREATE TABLE IF NOT EXISTS ord_supplier_settlement_item (
  id VARCHAR(32) PRIMARY KEY COMMENT '主键ID',
  settlement_id VARCHAR(32) NOT NULL COMMENT '结算主表ID',
  order_id VARCHAR(32) NOT NULL COMMENT '订单ID',
  order_no VARCHAR(50) NOT NULL COMMENT '订单号',
  supplier_id VARCHAR(32) NOT NULL COMMENT '供应商ID',
  order_amount DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT '订单结算金额',
  is_del TINYINT NOT NULL DEFAULT 0 COMMENT '是否删除',
  create_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  create_by VARCHAR(32) NULL COMMENT '创建人ID',
  create_name VARCHAR(100) NULL COMMENT '创建人姓名',
  update_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  update_by VARCHAR(32) NULL COMMENT '修改人ID',
  KEY idx_ossi_settlement_id (settlement_id),
  KEY idx_ossi_order_id (order_id),
  KEY idx_ossi_supplier_id (supplier_id),
  KEY idx_ossi_is_del (is_del)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='供应商结算明细表';

CREATE TABLE IF NOT EXISTS ord_supplier_settlement_his (
  id VARCHAR(32) NOT NULL COMMENT '主键ID',
  settlement_no VARCHAR(50) NOT NULL COMMENT '结算单号',
  supplier_id VARCHAR(32) NOT NULL COMMENT '供应商ID',
  supplier_name VARCHAR(100) NULL COMMENT '供应商名称快照',
  order_count INT NOT NULL DEFAULT 0 COMMENT '订单数量',
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT '结算总金额',
  voucher_urls TEXT NULL COMMENT '结算凭证URL(JSON数组)',
  remark VARCHAR(500) NULL COMMENT '结算备注',
  settlement_at DATETIME NOT NULL COMMENT '结算时间',
  status VARCHAR(20) NOT NULL DEFAULT 'done' COMMENT '结算单状态',
  create_at DATETIME NOT NULL COMMENT '创建时间',
  create_by VARCHAR(32) NULL COMMENT '创建人ID',
  create_name VARCHAR(100) NULL COMMENT '创建人姓名',
  update_at DATETIME NULL COMMENT '修改时间',
  update_by VARCHAR(32) NULL COMMENT '修改人ID',
  his_create_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='供应商结算主表历史';

CREATE TABLE IF NOT EXISTS ord_supplier_settlement_item_his (
  id VARCHAR(32) NOT NULL COMMENT '主键ID',
  settlement_id VARCHAR(32) NOT NULL COMMENT '结算主表ID',
  order_id VARCHAR(32) NOT NULL COMMENT '订单ID',
  order_no VARCHAR(50) NOT NULL COMMENT '订单号',
  supplier_id VARCHAR(32) NOT NULL COMMENT '供应商ID',
  order_amount DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT '订单结算金额',
  create_at DATETIME NOT NULL COMMENT '创建时间',
  create_by VARCHAR(32) NULL COMMENT '创建人ID',
  create_name VARCHAR(100) NULL COMMENT '创建人姓名',
  update_at DATETIME NULL COMMENT '修改时间',
  update_by VARCHAR(32) NULL COMMENT '修改人ID',
  his_create_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '历史记录创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='供应商结算明细历史表';

COMMIT;