huahuibao/sql/forward_schema_a.sql

-- 方案A：冲销凭证双表 + 原单汇总缓存 + 售后关联（ssh 冲销单功能）
-- 说明：不创建负销售单/负采购单；GHS/HD 各有凭证主表+明细，互挂 id

-- ========== GHS 冲销凭证 ==========
CREATE TABLE IF NOT EXISTS `xhGhsForward` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `forwardSn` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '冲销凭证号',
  `mainId` INT(11) NOT NULL DEFAULT 0 COMMENT '供货商主体id',
  `shopId` INT(11) NOT NULL DEFAULT 0,
  `sjId` INT(11) NOT NULL DEFAULT 0,
  `customId` INT(11) NOT NULL DEFAULT 0,
  `ghsId` INT(11) NOT NULL DEFAULT 0 COMMENT '对应 xhGhs.id（花店侧镜像客户）',
  `orderId` INT(11) NOT NULL DEFAULT 0 COMMENT '原销售单id，0=自由冲销',
  `orderSn` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '原销售单号',
  `amount` DECIMAL(9,2) NOT NULL DEFAULT 0.00 COMMENT '本次冲销金额(正数)',
  `payWay` TINYINT(4) NOT NULL DEFAULT 0 COMMENT '原单/记账支付方式',
  `fundType` TINYINT(4) NOT NULL DEFAULT 2 COMMENT '1原路退 2返余额 3仅记账',
  `forwardStock` TINYINT(4) NOT NULL DEFAULT 1 COMMENT '0回库存 1不回库存',
  `cgForwardId` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '对应 xhCgForward.id',
  `refundId` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '来源 xhRefund.id，0=非售后转',
  `thirdRefundNo` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '网关退款流水号',
  `status` TINYINT(4) NOT NULL DEFAULT 0 COMMENT '0处理中 1成功 2失败',
  `remark` VARCHAR(255) NOT NULL DEFAULT '',
  `shopAdminId` INT(11) NOT NULL DEFAULT 0,
  `shopAdminName` VARCHAR(64) NOT NULL DEFAULT '',
  `addTime` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_forwardSn` (`forwardSn`),
  KEY `idx_orderId` (`orderId`),
  KEY `idx_customId` (`customId`),
  KEY `idx_mainId_addTime` (`mainId`,`addTime`),
  KEY `idx_cgForwardId` (`cgForwardId`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='供货商冲销凭证';

CREATE TABLE IF NOT EXISTS `xhGhsForwardItem` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `forwardId` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  `forwardSn` VARCHAR(64) NOT NULL DEFAULT '',
  `mainId` INT(11) NOT NULL DEFAULT 0,
  `customId` INT(11) NOT NULL DEFAULT 0,
  `productId` INT(11) NOT NULL DEFAULT 0,
  `orderItemId` INT(11) NOT NULL DEFAULT 0 COMMENT '原单明细id，可0',
  `name` VARCHAR(128) NOT NULL DEFAULT '',
  `num` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '冲销数量(正数，小单位合计)',
  `bigNum` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `smallNum` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `unitPrice` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '单价(大单位或提交价)',
  `amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '行金额(正数)',
  `addTime` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_forwardId` (`forwardId`),
  KEY `idx_productId` (`productId`),
  KEY `idx_mainId_addTime` (`mainId`,`addTime`),
  KEY `idx_customId` (`customId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='供货商冲销凭证明细';

-- ========== HD 冲销凭证（镜像） ==========
CREATE TABLE IF NOT EXISTS `xhCgForward` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `forwardSn` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '花店侧冲销凭证号',
  `mainId` INT(11) NOT NULL DEFAULT 0 COMMENT '花店主体id',
  `shopId` INT(11) NOT NULL DEFAULT 0,
  `sjId` INT(11) NOT NULL DEFAULT 0,
  `customId` INT(11) NOT NULL DEFAULT 0 COMMENT '供货商视角客户id',
  `ghsId` INT(11) NOT NULL DEFAULT 0,
  `cgId` INT(11) NOT NULL DEFAULT 0 COMMENT '原采购单id，0=自由冲销',
  `cgSn` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '原采购单号',
  `ghsForwardId` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '对应 xhGhsForward.id',
  `amount` DECIMAL(9,2) NOT NULL DEFAULT 0.00,
  `payWay` TINYINT(4) NOT NULL DEFAULT 0,
  `fundType` TINYINT(4) NOT NULL DEFAULT 2,
  `forwardStock` TINYINT(4) NOT NULL DEFAULT 1,
  `cgRefundId` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '来源 xhCgRefund.id',
  `status` TINYINT(4) NOT NULL DEFAULT 0 COMMENT '0处理中 1成功 2失败',
  `remark` VARCHAR(255) NOT NULL DEFAULT '',
  `addTime` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_forwardSn` (`forwardSn`),
  KEY `idx_cgId` (`cgId`),
  KEY `idx_ghsForwardId` (`ghsForwardId`),
  KEY `idx_ghsId_addTime` (`ghsId`,`addTime`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='花店冲销凭证';

CREATE TABLE IF NOT EXISTS `xhCgForwardItem` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `forwardId` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'xhCgForward.id',
  `forwardSn` VARCHAR(64) NOT NULL DEFAULT '',
  `ghsId` INT(11) NOT NULL DEFAULT 0,
  `customId` INT(11) NOT NULL DEFAULT 0,
  `productId` INT(11) NOT NULL DEFAULT 0,
  `cgItemId` INT(11) NOT NULL DEFAULT 0,
  `name` VARCHAR(128) NOT NULL DEFAULT '',
  `num` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `bigNum` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `smallNum` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `unitPrice` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `addTime` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_forwardId` (`forwardId`),
  KEY `idx_productId` (`productId`),
  KEY `idx_ghsId` (`ghsId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='花店冲销凭证明细';

-- ========== 原单汇总缓存 ==========
ALTER TABLE `xhGhsOrder`
  ADD COLUMN `hasForward` TINYINT(4) NOT NULL DEFAULT 0 COMMENT '0无冲销 1有冲销' AFTER `tkPrice`,
  ADD COLUMN `forwardPrice` DECIMAL(9,2) NOT NULL DEFAULT 0.00 COMMENT '累计冲销金额' AFTER `hasForward`;

ALTER TABLE `xhCg`
  ADD COLUMN `hasForward` TINYINT(4) NOT NULL DEFAULT 0 COMMENT '0无冲销 1有冲销' AFTER `tkPrice`,
  ADD COLUMN `forwardPrice` DECIMAL(9,2) NOT NULL DEFAULT 0.00 COMMENT '累计冲销金额' AFTER `hasForward`;

ALTER TABLE `xhGhsOrderItem`
  ADD COLUMN `forwardNum` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '已冲销数量';

ALTER TABLE `xhCgItem`
  ADD COLUMN `forwardNum` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '已冲销数量';

-- ========== 售后指向本侧凭证 ==========
ALTER TABLE `xhRefund`
  ADD COLUMN `forwardId` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '转冲销后xhGhsForward.id，0=未转' AFTER `cgRefundId`;

ALTER TABLE `xhCgRefund`
  ADD COLUMN `forwardId` INT(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '转冲销后xhCgForward.id，0=未转' AFTER `saleRefundId`;