huahuibao/sql/20260706_redesign.sql

-- ouyang 2026-7-17 10:11:34
ALTER TABLE xhHd
    add COLUMN `homeRule` tinyint(4) NOT NULL DEFAULT '0' COMMENT '上门遵循规则 0遵循总设置 1遵循自定义' after `updateTime`;
ALTER TABLE xhCustom
    add COLUMN `homeRule` tinyint(4) NOT NULL DEFAULT '0' COMMENT '上门遵循规则 0遵循总设置 1遵循自定义' after `updateTime`;

-- ouyang 2026-7-17 11:31:52 用户地址列表
-- 注意现有的用户收货地址要同步过来
CREATE TABLE `xhUserAddress` (
    `id` bigint(11) NOT NULL AUTO_INCREMENT,
    `userId` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
    `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '收货人',
    `phone` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '手机号',
    `lat` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '纬度',
    `long` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '经度',
    `province` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '省',
    `city` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '市',
    `dist` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '区县',
    `address` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '街道地址',
    `floor` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '楼号门牌号',
    `fullAddress` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '完整地址',
    `showAddress` varchar(900) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '地图接口显示的完整地址',
    `default` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否默认地址',
    `tag` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '标签',
    `addTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
    `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`) USING BTREE,
    KEY `idx_userId` (`userId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户地址列表';

-- ouyang 2026-7-20 15:27:39
CREATE TABLE `xhPsExplain` (
   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
   `methodId` int(11) NOT NULL COMMENT '关联 PsMethod.id',
   `explain` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '说明文字，最多50字',
   `color` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '颜色:1黑 2红 3蓝',
   `fontWeight` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '字重:1常规 2加粗',
   `sort` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '显示顺序，越小越靠前',
   `addTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
   PRIMARY KEY (`id`),
   KEY `idx_method_sort` (`methodId`,`sort`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='花店配送方式说明条目';


CREATE TABLE `xhPsMethod` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `shopId` int(11) NOT NULL DEFAULT '0' COMMENT '门店ID',
  `mainId` int(11) NOT NULL DEFAULT '0' COMMENT 'mainId',
  `style` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'sendType 0送货 1自取 2跑腿 3物流 4快递 transType 0德邦 1顺丰 2冷链 3航空 4送货 5自取',
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '名称',
  `originName` char(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '原名',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '状态:1启用 0禁用',
  `sort` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '显示顺序',
  `minAmount` decimal(10,2) DEFAULT '0.00' COMMENT '最低消费金额，0表示没有要求',
  `minNum` smallint(5) DEFAULT '0' COMMENT '最低消费数量，0表示没有要求',
  `unMeet` tinyint(3) unsigned DEFAULT '0' COMMENT '不满条件，0收运费 1收包装费 2不能下单',
  `unMeetFee` decimal(10,2) DEFAULT '0.00' COMMENT '运费(送货) / 包装费(快递)',
  `startKm` decimal(8,2) DEFAULT '0.00' COMMENT '起步公里数',
  `startPrice` decimal(10,2) DEFAULT '0.00' COMMENT '起步公里内价格(元)',
  `perKmPrice` decimal(10,2) DEFAULT '0.00' COMMENT '超出起步每公里加价(元)',
  `changeRate` decimal(6,2) DEFAULT '0.00' COMMENT '临时涨价百分比，负数为降价',
  `addTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_main_type` (`mainId`,`style`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='花店送货方式配置表';


CREATE TABLE `xhPsReduceRule` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `methodId` int(11) NOT NULL COMMENT '关联 PsMethod.id',
  `meetNum` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '满数量',
  `meetAmount` decimal(10,2) NOT NULL COMMENT '满金额',
  `freeKm` decimal(8,2) NOT NULL COMMENT 'X公里内免配送费',
  `sort` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '规则排序，越小越靠前',
  `addTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_method_sort` (`methodId`,`sort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='花店送货方式，跑腿，花材，满减规则';


-- ouyang 2026-7-22 17:13:06
-- =============================================================================
-- 分销功能 — 数据库建表脚本（hdApp）
-- =============================================================================

SET NAMES utf8mb4;
SET SESSION lock_wait_timeout = 10;


-- -----------------------------------------------------------------------------
-- 1. 分销用户表（id = xhCustom.id，非自增）
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `xhDistributionUser` (
    `id` bigint(20) unsigned NOT NULL COMMENT '主键，与 xhCustom.id 一致',
    `customId` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '客户ID， xhCustom id',
    `shopId` int(11) NOT NULL DEFAULT '0' COMMENT '门店ID',
    `inviterId` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '邀请人 xhCustom.id，0无',
    `inviteTime` datetime DEFAULT NULL COMMENT '邀请时间',
    `inviteCount` int(11) NOT NULL DEFAULT '0' COMMENT '拉新数量',
    `orderCount` int(11) NOT NULL DEFAULT '0' COMMENT '本人获佣订单数',
    `orderAmount` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '本人获佣订单金额（计佣基数合计）',
    `pendingCommission` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '待结算提成（已付款未分佣）',
    `settledCommission` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '已结算提成（已分佣）',
    `totalCommission` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '累计提成（待结算+已结算）',
    `totalDeposit` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '累计存入金额',
    `availDeposit` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '可存入分红余额（已结算未存入）',
    `upOrderCount` int(11) NOT NULL DEFAULT '0' COMMENT '为上级贡献订单数（本人下单产生）',
    `upOrderAmount` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '为上级贡献订单金额（计佣基数合计）',
    `upCommissionAmount` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '为上级贡献提成金额';
    `status` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '1正常 0禁用',
    `addTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    KEY `idx_shop` (`shopId`),
    KEY `idx_inviter` (`shopId`, `inviterId`),
    KEY `idx_customId` (`customId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='分销用户统计表';

-- -----------------------------------------------------------------------------
-- 1.1 客户与分销员绑定（按时间递减计佣必填；inviteTime 是邀请成为分销员，bindTime 是客户绑定关系）
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `xhDistributionCustomerBind` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
    `shopId` int(11) NOT NULL DEFAULT '0' COMMENT '门店ID',
    `hdId` int(11) NOT NULL DEFAULT '0' COMMENT '花店ID',
    `buyerId` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '下单客户 xhCustom.id',
    `distId` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '绑定分销员 xhCustom.id',
    `bindTime` datetime NOT NULL COMMENT '绑定时间（递减计佣起点）',
    `bindSource` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '1邀请 2扫码 3首单 4后台',
    `status` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '1有效 0失效',
    `addTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_shop_buyer` (`shopId`, `buyerId`),
    KEY `idx_dist` (`shopId`, `distId`, `bindTime`),
    KEY `idx_buyer` (`buyerId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='客户与分销员绑定';


-- -----------------------------------------------------------------------------
-- 2. 分销规则（图2 固定比例 / 图3 按时间递减）
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `xhDistributionRule` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    `shopId` int(11) NOT NULL DEFAULT '0' COMMENT '门店ID',
    `mainId` int(11) NOT NULL DEFAULT '0' COMMENT 'mainId',
    `status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '分销功能 0关 1开',
    `settleDays` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '订单完成后 N 天结算',
    `bonusType` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '1固定比例 2按时间递减',
    `bonusRate` decimal(6,2) NOT NULL DEFAULT '0.00' COMMENT '固定比例%（bonusType=1）',
    `durationType` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '分红时长 1不限 2限制',
    `durationDays` int(11) NOT NULL DEFAULT '0' COMMENT '限制天数（durationType=2）',
    `timeStartType` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '计时起点 1绑定时间',
    `productScope` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '1全部 2指定商品 3指定分类',
    `addTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_shop` (`shopId`),
    KEY `idx_mainId` (`mainId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='分销规则';


-- -----------------------------------------------------------------------------
-- 2.1 时间递减阶梯
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `xhDistributionRuleTier` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    `ruleId` int(11) NOT NULL DEFAULT '0' COMMENT 'xhDistributionRule.id',
    `minDays` int(11) NOT NULL DEFAULT '0' COMMENT '起始天数（含）',
    `maxDays` int(11) NOT NULL DEFAULT '0' COMMENT '结束天数（含）；0=及以上',
    `bonusRate` decimal(6,2) NOT NULL DEFAULT '0.00' COMMENT '佣金比例%',
    `sort` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
    `addTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `idx_rule_sort` (`ruleId`, `sort`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='分销规则阶梯';


-- -----------------------------------------------------------------------------
-- 2.2 指定商品/分类（productScope=2/3 时使用，若仅做「全部商品」可暂不建）
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `xhDistributionRuleScope` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    `ruleId` int(11) NOT NULL DEFAULT '0' COMMENT 'xhDistributionRule.id',
    `scopeType` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '1商品 2分类',
    `targetId` int(11) NOT NULL DEFAULT '0' COMMENT 'goodsId 或 categoryId',
    `addTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_rule_target` (`ruleId`, `scopeType`, `targetId`),
    KEY `idx_rule` (`ruleId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='分销规则商品范围';


-- -----------------------------------------------------------------------------
-- 3. 订单分销记录（图4/5）
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `xhDistributionOrder` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
    `shopId` int(11) NOT NULL DEFAULT '0' COMMENT '门店ID',
    `hdId` int(11) NOT NULL DEFAULT '0' COMMENT '花店ID',
    `orderId` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '订单ID',
    `orderSn` varchar(64) NOT NULL DEFAULT '' COMMENT '订单号',
    `buyerId` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '下单客户 xhCustom.id',
    `buyerName` varchar(80) NOT NULL DEFAULT '' COMMENT '下单客户名（快照）',
    `distId` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '获佣分销员 xhCustom.id',
    `distName` varchar(80) NOT NULL DEFAULT '' COMMENT '获佣人名（快照）',
    `orderAmount` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '订单商品金额',
    `payAmount` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '实付（含运费）',
    `sendCost` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '运费',
    `commissionBase` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '计佣基数=实付-运费',
    `commissionRate` decimal(6,2) NOT NULL DEFAULT '0.00' COMMENT '佣金比例%（快照）',
    `commissionAmount` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '佣金金额',
    `bonusType` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '1固定 2递减（快照）',
    `bindTime` datetime DEFAULT NULL COMMENT '绑定时间（快照）',
    `bindDays` int(11) NOT NULL DEFAULT '0' COMMENT '下单距绑定天数（快照）',
    `settleStatus` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '0待结算 1已结算 2已失效',
    `orderTime` datetime DEFAULT NULL COMMENT '下单时间',
    `payTime` datetime DEFAULT NULL COMMENT '付款时间',
    `finishTime` datetime DEFAULT NULL COMMENT '订单完成时间',
    `settleTime` datetime DEFAULT NULL COMMENT '分佣时间',
    `invalidTime` datetime DEFAULT NULL COMMENT '失效时间',
    `invalidReason` varchar(255) NOT NULL DEFAULT '' COMMENT '失效原因',
    `addTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_order_dist` (`orderId`, `distId`),
    KEY `idx_shop_settle` (`shopId`, `settleStatus`, `orderTime`),
    KEY `idx_dist_settle` (`distId`, `settleStatus`, `settleTime`),
    KEY `idx_orderSn` (`orderSn`),
    KEY `idx_buyer` (`buyerId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单分销记录';


-- -----------------------------------------------------------------------------
-- 4. 分销流水（图6）
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `xhDistributionFlow` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
    `shopId` int(11) NOT NULL DEFAULT '0' COMMENT '门店ID',
    `hdId` int(11) NOT NULL DEFAULT '0' COMMENT '花店ID',
    `xhCustomId` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '分销员 xhCustom.id',
    `flowType` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '1订单分红 2分红存入 3退款扣回',
    `amount` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '变动金额；分红+ 存入/扣回-',
    `availAfter` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '变动后可存分红余额',
    `refType` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '1分销订单 2存入日志',
    `refId` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '关联业务ID',
    `refSn` varchar(64) NOT NULL DEFAULT '' COMMENT '订单号/存入编号',
    `buyerName` varchar(80) NOT NULL DEFAULT '' COMMENT '下单客户（flowType=1）',
    `flowStatus` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '1待结算 2已结算 3存入成功 4已扣回',
    `flowTime` datetime NOT NULL COMMENT '业务时间',
    `remark` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
    `addTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `idx_custom_time` (`xhCustomId`, `flowTime`),
    KEY `idx_shop_type_time` (`shopId`, `flowType`, `flowTime`),
    KEY `idx_ref` (`refType`, `refId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='分销流水';


-- -----------------------------------------------------------------------------
-- 5. 分红存入日志（图7）
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `xhDistributionDepositLog` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
    `shopId` int(11) NOT NULL DEFAULT '0' COMMENT '门店ID',
    `hdId` int(11) NOT NULL DEFAULT '0' COMMENT '花店ID',
    `xhCustomId` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '客户 xhCustom.id',
    `depositSn` varchar(32) NOT NULL DEFAULT '' COMMENT '存入编号',
    `depositAmount` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '存入金额',
    `beforeAvail` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '存入前可存分红',
    `afterAvail` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '存入后可存分红',
    `balanceChangeId` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'xhCustomBalanceChange.id',
    `remark` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
    `addTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '存入时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_depositSn` (`depositSn`),
    KEY `idx_custom_time` (`xhCustomId`, `addTime`),
    KEY `idx_shop_time` (`shopId`, `addTime`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='分红存入日志';

-- =============================================================================
-- 分销功能脚本结束
-- =============================================================================

-- 新增订单完成时间 ouyang 2026-7-24 18:18:01
ALTER TABLE `xhOrder`
    ADD COLUMN `successTime` datetime NULL DEFAULT '0000-00-00 00:00:00' COMMENT '订单完成时间' AFTER `settleTime`;

-- # 分销延迟结算定时任务 ouyang 2026-7-24 18:27:52
-- 0 * * * * cd /path/to/huahuibao && php yii distribution/settle-due --limit=100



-- -----------------------------------------------------------------------------
-- 1. xhMain 增加钱包余额
-- -----------------------------------------------------------------------------
ALTER TABLE `xhMain`
    ADD COLUMN `walletBalance` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '钱包余额' AFTER `balance`;


-- -----------------------------------------------------------------------------
-- 2. 中央钱包变动记录
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `xhMainWalletChange` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `relateId` int(11) NOT NULL DEFAULT '0' COMMENT '关联订单id',
    `ptStyle` tinyint(4) NOT NULL DEFAULT '1' COMMENT '平台类型 查看dict.php表',
    `capitalType` tinyint(4) NOT NULL DEFAULT '0' COMMENT '流水类型',
    `amount` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '变动金额',
    `balance` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '当前钱包余额',
    `io` tinyint(4) NOT NULL DEFAULT '0' COMMENT '类型 0减少 1增加',
    `payWay` tinyint(4) NOT NULL DEFAULT '0' COMMENT '支付方式',
    `fromType` tinyint(4) NOT NULL DEFAULT '1' COMMENT '来源 查看dict.php',
    `event` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '事项',
    `mainId` int(11) NOT NULL DEFAULT '0' COMMENT '中央id',
    `remark` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '备注',
    `addTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
    `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `idx_mainId_addTime` (`mainId`, `addTime`) USING BTREE,
    KEY `idx_relateId` (`relateId`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='中央钱包变动记录';

-- -----------------------------------------------------------------------------
-- 门店邀请（批发 / 花店）
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `xhMainInvite` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    `mainId` int(11) NOT NULL DEFAULT '0' COMMENT '本店中央 id（xhMain.id）',
    `inviteCode` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '本店唯一邀请码',
    `totalCommission` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '累计佣金（本店作为邀请人）',
    `ableCommission` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '可提现佣金',
    `inviteCount` int(11) NOT NULL DEFAULT '0' COMMENT '累计邀请数量（本店作为邀请人）',
    `commissionAmount` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '分佣金额（规则配置）',
    `hdDiscountAmount` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '花店优惠金额（规则配置）',
    `inviterId` int(11) NOT NULL DEFAULT '0' COMMENT '邀请人 xhMain.id，0 表示无',
    `inviteTime` datetime DEFAULT NULL COMMENT '本店被邀请绑定时间',
    `addTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY `uk_inviteCode` (`inviteCode`) USING BTREE,
    UNIQUE KEY `uk_mainId` (`mainId`) USING BTREE,
    KEY `idx_inviterId` (`inviterId`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='门店邀请（批发/花店）';

-- -----------------------------------------------------------------------------
-- 门店邀请佣金分成明细
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `xhMainInviteCommission` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
    `commissionSn` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '分佣单号（如 FY202607210001）',
    `inviterMainId` int(11) NOT NULL DEFAULT '0' COMMENT '获佣人中央 id（批发商 xhMain.id）',
    `pfShopId` int(11) NOT NULL DEFAULT '0' COMMENT '批发店shopId',
    `pfShopName` varchar(100) NOT NULL DEFAULT '0' COMMENT '批发店名称',
    `lsShopId` int(11) NOT NULL DEFAULT '0' COMMENT '零售店shopId',
    `lsShopName` varchar(100) NOT NULL DEFAULT '0' COMMENT '批发店名称',
    `inviterShopName` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '获佣人门店名（快照）',
    `inviteeMainId` int(11) NOT NULL DEFAULT '0' COMMENT '受邀花店中央 id（xhMain.id）',
    `inviteeShopName` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '受邀花店名（快照）',
    `inviteeMobile` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '受邀人手机号（快照）',
    `inviteCode` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '邀请码（快照）',
    `memberType` tinyint(4) NOT NULL DEFAULT '0' COMMENT '会员类型，见 dict.php',
    `memberTypeName` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '会员类型名（快照，如年度会员）',
    `amount` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '原价',
    `memberPayAmount` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '实付金额',
    `commissionAmount` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '分成金额（获佣人）',
    `hdDiscountAmount` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '优惠金额',
    `settleStatus` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '结算状态 0待结算 1已结算 2已失效',
    `relateOrderId` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '关联会员/订单 id（业务定稿后写入）',
    `payWay` tinyint(4) NOT NULL DEFAULT '0' COMMENT '支付方式 0微信 1支付宝 2余额',
    `payTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '付款时间，确认时间',
    `settleTime` datetime DEFAULT NULL COMMENT '结算时间',
    `invalidTime` datetime DEFAULT NULL COMMENT '失效时间',
    `invalidReason` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '失效原因',
    `addTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY `uk_commissionSn` (`commissionSn`) USING BTREE,
    KEY `idx_inviter_settle_occur` (`inviterMainId`, `settleStatus`, `occurTime`) USING BTREE,
    KEY `idx_invitee_occur` (`inviteeShopId`, `occurTime`) USING BTREE,
    KEY `idx_inviteCode` (`inviteCode`) USING BTREE,
    KEY `idx_relateOrderId` (`relateOrderId`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='门店邀请记录';

CREATE TABLE IF NOT EXISTS `xhMainInviteFlow` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `mainId` int(11) NOT NULL DEFAULT 0 COMMENT '邀请人 xhMain.id',
    `flowType` tinyint(3) unsigned NOT NULL DEFAULT 1 COMMENT '1邀请获佣 2存入余额 3退款扣回',
    `amount` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT '变动金额：获佣为正，存入/扣回为负',
    `ableAfter` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT '变动后可提现佣金',
    `refType` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '1=xhMainInviteCommission 2=xhMainWalletChange',
    `refId` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT '关联业务 id',
    `refSn` varchar(32) NOT NULL DEFAULT '' COMMENT '分佣单号 FY… / 存入编号 CR…',
    `inviteeShopName` varchar(80) NOT NULL DEFAULT '' COMMENT '受邀花店名快照 flowType=1',
    `inviteeMobile` varchar(20) NOT NULL DEFAULT '' COMMENT '受邀手机号快照',
    `lsShopId` int(11) NOT NULL DEFAULT 0 COMMENT '零售店 shopId',
    `baseAmount` decimal(15,2) NOT NULL DEFAULT 0.00 COMMENT '会员实付或存余额金额',
    `flowTime` datetime NOT NULL COMMENT '业务时间',
    `remark` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
    `addTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_main_flowTime` (`mainId`, `flowTime`),
    KEY `idx_ref` (`refType`, `refId`)
    ) COMMENT='邀请佣金变动流水';
-- 花店注册付费开通：xhApply 扩展字段
ALTER TABLE `xhApply`
  ADD COLUMN `inviteCode` varchar(32) NOT NULL DEFAULT '' COMMENT '注册时填写的邀请码' AFTER `status`,
  ADD COLUMN `hdDiscountAmount` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '邀请优惠金额' AFTER `inviteCode`,
  ADD COLUMN `inviteCommissionAmount` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '邀请人佣金快照' AFTER `hdDiscountAmount`;
ALTER TABLE `xhRenew`
    ADD COLUMN `ptStyle` tinyint(4) NOT NULL DEFAULT '2' COMMENT '所属平台 1花店 2供货商 请查看dict.php';

-- xhShopExt 表添加客服电话与微信二维码
ALTER TABLE xhShopExt
  ADD COLUMN serviceMobile varchar(20) NOT NULL DEFAULT '' COMMENT '客服电话' AFTER rechargeRemark,
  ADD COLUMN serviceWx varchar(300) NOT NULL DEFAULT '' COMMENT '客服微信二维码' AFTER serviceMobile;

CREATE TABLE IF NOT EXISTS `xhMainWalletRecharge` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `orderSn` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '充值单号',
    `mainId` int(11) NOT NULL DEFAULT '0' COMMENT '中央 id',
    `shopId` int(11) NOT NULL DEFAULT '0' COMMENT '门店 id',
    `amount` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '充值金额',
    `payStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT '支付状态 0未付 1已付',
    `payWay` tinyint(4) NOT NULL DEFAULT '0' COMMENT '支付方式',
    `returnCode` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '微信 transaction_id',
    `remark` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '备注',
    `addTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
    `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY `uk_orderSn` (`orderSn`) USING BTREE,
    KEY `idx_mainId_addTime` (`mainId`, `addTime`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='中央钱包微信充值订单';