admin/sql/admin1008.sql

-- =============================================================================
-- admin.sql
-- 存量库增量迁移合集（由 sql/ 下各 migrate_*.sql 合并，可重复执行）
-- 新环境请直接执行仓库根目录 db.sql，勿在有生产数据的库上 DROP 重建。
-- =============================================================================

-- =============================================================================
-- migrate_member_level_rolling_points.sql
-- 旧会员体系 → V0~V5（可重复执行）
-- old: normal/silver/gold/diamond
-- new: V0 普通 / V1 铜卡(新增) / V2 银卡 / V3 金卡 / V4 钻石 / V5 皇冠(新增)
-- =============================================================================
SET @db := DATABASE();

SET @col := (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 't_member_level'
    AND COLUMN_NAME = 'CanDowngrade'
);
SET @sql := IF(
  @col = 0,
  'ALTER TABLE `t_member_level`
    ADD COLUMN `CanDowngrade` TINYINT(1) NOT NULL DEFAULT 0 COMMENT ''V4/V5 允许逐级降级'' AFTER `ExtraDiscountRate`',
  'SELECT ''CanDowngrade already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ---------- 把旧行升级成新编码（仅当目标 V* 还不存在）----------
UPDATE `t_member_level`
SET
  `LevelCode` = 'V0',
  `LevelName` = '普通会员',
  `MinPoints` = 0,
  `ExtraDiscountRate` = 0.00,
  `CanDowngrade` = 0,
  `Sort` = 0,
  `BenefitDesc` = '新注册 / 小单试单卖家',
  `Status` = 2,
  `UpdateTime` = NOW()
WHERE `IsDeleted` = 0
  AND LOWER(`LevelCode`) = 'normal'
  AND NOT EXISTS (SELECT 1 FROM (SELECT 1 FROM `t_member_level` x WHERE x.`IsDeleted` = 0 AND x.`LevelCode` = 'V0') t);

UPDATE `t_member_level`
SET
  `LevelCode` = 'V2',
  `LevelName` = '银卡会员',
  `MinPoints` = 60000,
  `ExtraDiscountRate` = 0.02,
  `CanDowngrade` = 0,
  `Sort` = 2,
  `BenefitDesc` = '主力中小卖家，基础运费 98 折',
  `Status` = 2,
  `UpdateTime` = NOW()
WHERE `IsDeleted` = 0
  AND LOWER(`LevelCode`) = 'silver'
  AND NOT EXISTS (SELECT 1 FROM (SELECT 1 FROM `t_member_level` x WHERE x.`IsDeleted` = 0 AND x.`LevelCode` = 'V2') t);

UPDATE `t_member_level`
SET
  `LevelCode` = 'V3',
  `LevelName` = '金卡会员',
  `MinPoints` = 120000,
  `ExtraDiscountRate` = 0.03,
  `CanDowngrade` = 0,
  `Sort` = 3,
  `BenefitDesc` = '优质中型卖家，基础运费 97 折；此级及以下只升不降',
  `Status` = 2,
  `UpdateTime` = NOW()
WHERE `IsDeleted` = 0
  AND LOWER(`LevelCode`) = 'gold'
  AND NOT EXISTS (SELECT 1 FROM (SELECT 1 FROM `t_member_level` x WHERE x.`IsDeleted` = 0 AND x.`LevelCode` = 'V3') t);

UPDATE `t_member_level`
SET
  `LevelCode` = 'V4',
  `LevelName` = '钻石会员',
  `MinPoints` = 240000,
  `ExtraDiscountRate` = 0.04,
  `CanDowngrade` = 1,
  `Sort` = 4,
  `BenefitDesc` = '中大卖家，基础运费 96 折；滚动积分不足可降到金卡',
  `Status` = 2,
  `UpdateTime` = NOW()
WHERE `IsDeleted` = 0
  AND LOWER(`LevelCode`) = 'diamond'
  AND NOT EXISTS (SELECT 1 FROM (SELECT 1 FROM `t_member_level` x WHERE x.`IsDeleted` = 0 AND x.`LevelCode` = 'V4') t);

-- ---------- 缺档则插入（铜卡、皇冠，以及未被旧行带过来的档）----------
INSERT INTO `t_member_level`
  (`LevelCode`, `LevelName`, `MinPoints`, `ExtraDiscountRate`, `CanDowngrade`, `Sort`, `BenefitDesc`, `Status`, `IsDeleted`, `CreateTime`)
SELECT 'V0', '普通会员', 0, 0.00, 0, 0, '新注册 / 小单试单卖家', 2, 0, NOW()
WHERE NOT EXISTS (SELECT 1 FROM `t_member_level` WHERE `LevelCode` = 'V0' AND `IsDeleted` = 0);

INSERT INTO `t_member_level`
  (`LevelCode`, `LevelName`, `MinPoints`, `ExtraDiscountRate`, `CanDowngrade`, `Sort`, `BenefitDesc`, `Status`, `IsDeleted`, `CreateTime`)
SELECT 'V1', '铜卡会员', 24000, 0.01, 0, 1, '起步稳定发货卖家，基础运费 99 折', 2, 0, NOW()
WHERE NOT EXISTS (SELECT 1 FROM `t_member_level` WHERE `LevelCode` = 'V1' AND `IsDeleted` = 0);

INSERT INTO `t_member_level`
  (`LevelCode`, `LevelName`, `MinPoints`, `ExtraDiscountRate`, `CanDowngrade`, `Sort`, `BenefitDesc`, `Status`, `IsDeleted`, `CreateTime`)
SELECT 'V2', '银卡会员', 60000, 0.02, 0, 2, '主力中小卖家，基础运费 98 折', 2, 0, NOW()
WHERE NOT EXISTS (SELECT 1 FROM `t_member_level` WHERE `LevelCode` = 'V2' AND `IsDeleted` = 0);

INSERT INTO `t_member_level`
  (`LevelCode`, `LevelName`, `MinPoints`, `ExtraDiscountRate`, `CanDowngrade`, `Sort`, `BenefitDesc`, `Status`, `IsDeleted`, `CreateTime`)
SELECT 'V3', '金卡会员', 120000, 0.03, 0, 3, '优质中型卖家，基础运费 97 折；此级及以下只升不降', 2, 0, NOW()
WHERE NOT EXISTS (SELECT 1 FROM `t_member_level` WHERE `LevelCode` = 'V3' AND `IsDeleted` = 0);

INSERT INTO `t_member_level`
  (`LevelCode`, `LevelName`, `MinPoints`, `ExtraDiscountRate`, `CanDowngrade`, `Sort`, `BenefitDesc`, `Status`, `IsDeleted`, `CreateTime`)
SELECT 'V4', '钻石会员', 240000, 0.04, 1, 4, '中大卖家，基础运费 96 折；滚动积分不足可降到金卡', 2, 0, NOW()
WHERE NOT EXISTS (SELECT 1 FROM `t_member_level` WHERE `LevelCode` = 'V4' AND `IsDeleted` = 0);

INSERT INTO `t_member_level`
  (`LevelCode`, `LevelName`, `MinPoints`, `ExtraDiscountRate`, `CanDowngrade`, `Sort`, `BenefitDesc`, `Status`, `IsDeleted`, `CreateTime`)
SELECT 'V5', '皇冠会员', 600000, 0.05, 1, 5, '顶级头部卖家，基础运费 95 折封顶；滚动积分不足可降到钻石', 2, 0, NOW()
WHERE NOT EXISTS (SELECT 1 FROM `t_member_level` WHERE `LevelCode` = 'V5' AND `IsDeleted` = 0);

-- ---------- 旧编码与新档并存时：客户挂到新档，旧行停用 ----------
UPDATE `t_customer` c
INNER JOIN `t_member_level` old ON old.`Id` = c.`MemberLevelId` AND old.`IsDeleted` = 0
INNER JOIN `t_member_level` canon ON canon.`IsDeleted` = 0 AND canon.`LevelCode` = CASE LOWER(old.`LevelCode`)
  WHEN 'normal' THEN 'V0'
  WHEN 'silver' THEN 'V2'
  WHEN 'gold' THEN 'V3'
  WHEN 'diamond' THEN 'V4'
  ELSE NULL
END
SET c.`MemberLevelId` = canon.`Id`, c.`UpdateTime` = NOW()
WHERE LOWER(old.`LevelCode`) IN ('normal', 'silver', 'gold', 'diamond')
  AND old.`Id` <> canon.`Id`;

UPDATE `t_member_level` old
INNER JOIN `t_member_level` canon ON canon.`IsDeleted` = 0 AND canon.`LevelCode` = CASE LOWER(old.`LevelCode`)
  WHEN 'normal' THEN 'V0'
  WHEN 'silver' THEN 'V2'
  WHEN 'gold' THEN 'V3'
  WHEN 'diamond' THEN 'V4'
  ELSE NULL
END
SET
  old.`IsDeleted` = 1,
  old.`Status` = 1,
  old.`UpdateTime` = NOW()
WHERE old.`IsDeleted` = 0
  AND LOWER(old.`LevelCode`) IN ('normal', 'silver', 'gold', 'diamond')
  AND old.`Id` <> canon.`Id`;

-- ---------- 校正 V0~V5 门槛/折扣（已占用客户的行 Id 不变）----------
UPDATE `t_member_level`
SET
  `LevelName` = '普通会员',
  `MinPoints` = 0,
  `ExtraDiscountRate` = 0.00,
  `CanDowngrade` = 0,
  `Sort` = 0,
  `BenefitDesc` = '新注册 / 小单试单卖家',
  `Status` = 2,
  `UpdateTime` = NOW()
WHERE `IsDeleted` = 0 AND `LevelCode` = 'V0';

UPDATE `t_member_level`
SET
  `LevelName` = '铜卡会员',
  `MinPoints` = 24000,
  `ExtraDiscountRate` = 0.01,
  `CanDowngrade` = 0,
  `Sort` = 1,
  `BenefitDesc` = '起步稳定发货卖家，基础运费 99 折',
  `Status` = 2,
  `UpdateTime` = NOW()
WHERE `IsDeleted` = 0 AND `LevelCode` = 'V1';

UPDATE `t_member_level`
SET
  `LevelName` = '银卡会员',
  `MinPoints` = 60000,
  `ExtraDiscountRate` = 0.02,
  `CanDowngrade` = 0,
  `Sort` = 2,
  `BenefitDesc` = '主力中小卖家，基础运费 98 折',
  `Status` = 2,
  `UpdateTime` = NOW()
WHERE `IsDeleted` = 0 AND `LevelCode` = 'V2';

UPDATE `t_member_level`
SET
  `LevelName` = '金卡会员',
  `MinPoints` = 120000,
  `ExtraDiscountRate` = 0.03,
  `CanDowngrade` = 0,
  `Sort` = 3,
  `BenefitDesc` = '优质中型卖家，基础运费 97 折；此级及以下只升不降',
  `Status` = 2,
  `UpdateTime` = NOW()
WHERE `IsDeleted` = 0 AND `LevelCode` = 'V3';

UPDATE `t_member_level`
SET
  `LevelName` = '钻石会员',
  `MinPoints` = 240000,
  `ExtraDiscountRate` = 0.04,
  `CanDowngrade` = 1,
  `Sort` = 4,
  `BenefitDesc` = '中大卖家，基础运费 96 折；滚动积分不足可降到金卡',
  `Status` = 2,
  `UpdateTime` = NOW()
WHERE `IsDeleted` = 0 AND `LevelCode` = 'V4';

UPDATE `t_member_level`
SET
  `LevelName` = '皇冠会员',
  `MinPoints` = 600000,
  `ExtraDiscountRate` = 0.05,
  `CanDowngrade` = 1,
  `Sort` = 5,
  `BenefitDesc` = '顶级头部卖家，基础运费 95 折封顶；滚动积分不足可降到钻石',
  `Status` = 2,
  `UpdateTime` = NOW()
WHERE `IsDeleted` = 0 AND `LevelCode` = 'V5';

-- =============================================================================
-- migrate_coupon_grant_system.sql
-- 优惠券发放体系：未解锁状态、到期提醒标记、新人/裂变券模板（可重复执行）
-- =============================================================================
SET @db := DATABASE();

SET @col := (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 't_coupon_instance'
    AND COLUMN_NAME = 'ExpiryNoticeSent'
);
SET @sql := IF(
  @col = 0,
  'ALTER TABLE `t_coupon_instance`
    ADD COLUMN `ExpiryNoticeSent` TINYINT(1) NOT NULL DEFAULT 0 COMMENT ''到期前3天已提醒'' AFTER `LockExpireTime`',
  'SELECT ''ExpiryNoticeSent already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 新人首单券 15 元，领取后 15 天
INSERT INTO `t_coupon_template` (
  `TemplateCode`, `TemplateName`, `CouponType`, `FaceValue`, `DiscountRate`, `MaxDiscountAmount`,
  `MinOrderAmount`, `TotalQuantity`, `IssuedQuantity`, `PerCustomerLimit`,
  `ValidType`, `ValidStartTime`, `ValidEndTime`, `ValidDaysAfterReceive`,
  `ApplyScopeJson`, `GrantChannel`, `Status`, `Remark`,
  `IsDeleted`, `CreateUserId`, `CreateTime`, `UpdateUserId`, `UpdateTime`
)
SELECT
  'NEWCOMER_FIRST', '新人首单券', 'fixed_off', 15.00, NULL, NULL,
  0, 0, 0, 1,
  2, NULL, NULL, 15,
  NULL, 'newcomer', 1, '注册自动下发，仅抵基础运费，退款不退回',
  0, 0, NOW(), 0, NOW()
WHERE NOT EXISTS (
  SELECT 1 FROM `t_coupon_template` x WHERE x.`IsDeleted` = 0 AND x.`TemplateCode` = 'NEWCOMER_FIRST'
);

-- 新人第二单券 15 元，满 150 基础运费，解锁后 30 天
INSERT INTO `t_coupon_template` (
  `TemplateCode`, `TemplateName`, `CouponType`, `FaceValue`, `DiscountRate`, `MaxDiscountAmount`,
  `MinOrderAmount`, `TotalQuantity`, `IssuedQuantity`, `PerCustomerLimit`,
  `ValidType`, `ValidStartTime`, `ValidEndTime`, `ValidDaysAfterReceive`,
  `ApplyScopeJson`, `GrantChannel`, `Status`, `Remark`,
  `IsDeleted`, `CreateUserId`, `CreateTime`, `UpdateUserId`, `UpdateTime`
)
SELECT
  'NEWCOMER_SECOND', '新人第二单券', 'fixed_off', 15.00, NULL, NULL,
  150, 0, 0, 1,
  3, NULL, NULL, 30,
  NULL, 'newcomer', 1, '首单派送成功后解锁，实付基础运费满 150',
  0, 0, NOW(), 0, NOW()
WHERE NOT EXISTS (
  SELECT 1 FROM `t_coupon_template` x WHERE x.`IsDeleted` = 0 AND x.`TemplateCode` = 'NEWCOMER_SECOND'
);

-- 新人备货券 20 元，满 200，注册后 60 天
INSERT INTO `t_coupon_template` (
  `TemplateCode`, `TemplateName`, `CouponType`, `FaceValue`, `DiscountRate`, `MaxDiscountAmount`,
  `MinOrderAmount`, `TotalQuantity`, `IssuedQuantity`, `PerCustomerLimit`,
  `ValidType`, `ValidStartTime`, `ValidEndTime`, `ValidDaysAfterReceive`,
  `ApplyScopeJson`, `GrantChannel`, `Status`, `Remark`,
  `IsDeleted`, `CreateUserId`, `CreateTime`, `UpdateUserId`, `UpdateTime`
)
SELECT
  'NEWCOMER_STOCK', '新人备货券', 'fixed_off', 20.00, NULL, NULL,
  200, 0, 0, 1,
  2, NULL, NULL, 60,
  NULL, 'newcomer', 1, '注册自动下发，实付基础运费满 200',
  0, 0, NOW(), 0, NOW()
WHERE NOT EXISTS (
  SELECT 1 FROM `t_coupon_template` x WHERE x.`IsDeleted` = 0 AND x.`TemplateCode` = 'NEWCOMER_STOCK'
);

-- 裂变：老客 20 元无门槛
INSERT INTO `t_coupon_template` (
  `TemplateCode`, `TemplateName`, `CouponType`, `FaceValue`, `DiscountRate`, `MaxDiscountAmount`,
  `MinOrderAmount`, `TotalQuantity`, `IssuedQuantity`, `PerCustomerLimit`,
  `ValidType`, `ValidStartTime`, `ValidEndTime`, `ValidDaysAfterReceive`,
  `ApplyScopeJson`, `GrantChannel`, `Status`, `Remark`,
  `IsDeleted`, `CreateUserId`, `CreateTime`, `UpdateUserId`, `UpdateTime`
)
SELECT
  'REFERRAL_INVITER', '邀请有礼券', 'fixed_off', 20.00, NULL, NULL,
  0, 0, 0, 9999,
  2, NULL, NULL, 30,
  NULL, 'referral', 1, '被邀请人首单派送成功后发给邀请人（每位被邀请人最多触发一次）',
  0, 0, NOW(), 0, NOW()
WHERE NOT EXISTS (
  SELECT 1 FROM `t_coupon_template` x WHERE x.`IsDeleted` = 0 AND x.`TemplateCode` = 'REFERRAL_INVITER'
);

-- 已存在的邀请有礼券：放宽每人限领，避免仅首个被邀请人能给邀请人发券
UPDATE `t_coupon_template`
SET `PerCustomerLimit` = 9999,
    `Remark` = '被邀请人首单派送成功后发给邀请人（每位被邀请人最多触发一次）',
    `UpdateTime` = NOW()
WHERE `IsDeleted` = 0
  AND `TemplateCode` = 'REFERRAL_INVITER'
  AND `PerCustomerLimit` < 9999;

-- 裂变：新客额外 5 元
INSERT INTO `t_coupon_template` (
  `TemplateCode`, `TemplateName`, `CouponType`, `FaceValue`, `DiscountRate`, `MaxDiscountAmount`,
  `MinOrderAmount`, `TotalQuantity`, `IssuedQuantity`, `PerCustomerLimit`,
  `ValidType`, `ValidStartTime`, `ValidEndTime`, `ValidDaysAfterReceive`,
  `ApplyScopeJson`, `GrantChannel`, `Status`, `Remark`,
  `IsDeleted`, `CreateUserId`, `CreateTime`, `UpdateUserId`, `UpdateTime`
)
SELECT
  'REFERRAL_INVITEE', '邀请注册加赠券', 'fixed_off', 5.00, NULL, NULL,
  0, 0, 0, 1,
  2, NULL, NULL, 30,
  NULL, 'referral', 1, '被邀请人注册即发 5 元券',
  0, 0, NOW(), 0, NOW()
WHERE NOT EXISTS (
  SELECT 1 FROM `t_coupon_template` x WHERE x.`IsDeleted` = 0 AND x.`TemplateCode` = 'REFERRAL_INVITEE'
);

-- =============================================================================
-- migrate_referral_invite_reward.sql
-- 邀请奖励：按实付基础运费百分比、T+N 工作日入运费余额、退款标记（可重复执行）
-- =============================================================================
SET @db := DATABASE();

-- ---------- t_order.IsRefunded ----------
SET @col := (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 't_order'
    AND COLUMN_NAME = 'IsRefunded'
);
SET @sql := IF(
  @col = 0,
  'ALTER TABLE `t_order`
    ADD COLUMN `IsRefunded` TINYINT(1) NOT NULL DEFAULT 0 COMMENT ''是否已退款（分成计次排除）'' AFTER `IsPaid`',
  'SELECT ''t_order.IsRefunded already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ---------- t_referral_commission_rule 新字段 ----------
SET @col := (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 't_referral_commission_rule'
    AND COLUMN_NAME = 'MinInviteLevelCode'
);
SET @sql := IF(
  @col = 0,
  'ALTER TABLE `t_referral_commission_rule`
    ADD COLUMN `MinInviteLevelCode` VARCHAR(16) NOT NULL DEFAULT ''V1'' COMMENT ''邀请资格最低等级'' AFTER `MonthlyCap`,
    ADD COLUMN `MinPaidBaseFreight` DECIMAL(18,2) NOT NULL DEFAULT 50.00 COMMENT ''计奖最低实付基础运费'' AFTER `MinInviteLevelCode`,
    ADD COLUMN `FirstOrderRate` DECIMAL(8,4) NOT NULL DEFAULT 0.0500 COMMENT ''首单奖励比例'' AFTER `MinPaidBaseFreight`,
    ADD COLUMN `FirstOrderCap` DECIMAL(18,2) NOT NULL DEFAULT 50.00 COMMENT ''首单奖励封顶'' AFTER `FirstOrderRate`,
    ADD COLUMN `FollowOrderRate` DECIMAL(8,4) NOT NULL DEFAULT 0.0300 COMMENT ''后续单奖励比例'' AFTER `FirstOrderCap`,
    ADD COLUMN `FollowOrderCap` DECIMAL(18,2) NOT NULL DEFAULT 30.00 COMMENT ''后续单奖励封顶'' AFTER `FollowOrderRate`,
    ADD COLUMN `FollowWindowDays` INT NOT NULL DEFAULT 90 COMMENT ''后续单注册后天数窗口'' AFTER `FollowOrderCap`,
    ADD COLUMN `MaxRewardOrders` INT NOT NULL DEFAULT 6 COMMENT ''最多计奖订单数'' AFTER `FollowWindowDays`,
    ADD COLUMN `SettleBusinessDays` INT NOT NULL DEFAULT 3 COMMENT ''派送成功后结算工作日'' AFTER `MaxRewardOrders`',
  'SELECT ''t_referral_commission_rule reward columns already exist'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UPDATE `t_referral_commission_rule`
SET
  `CommissionMode` = 'percent_of_paid_base_freight',
  `SettleTriggerOrderStatus` = 'deliverySuccess',
  `SettleBusinessDays` = CASE WHEN `SettleBusinessDays` > 0 THEN `SettleBusinessDays` ELSE 3 END,
  `FreezeDays` = CASE WHEN `SettleBusinessDays` > 0 THEN `SettleBusinessDays` ELSE 3 END,
  `UpdateTime` = NOW()
WHERE `IsDeleted` = 0;

-- ---------- t_referral_commission_detail ----------
SET @col := (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 't_referral_commission_detail'
    AND COLUMN_NAME = 'SettleDueTime'
);
SET @sql := IF(
  @col = 0,
  'ALTER TABLE `t_referral_commission_detail`
    ADD COLUMN `SettleDueTime` DATETIME NULL COMMENT ''计划结算时间'' AFTER `EligibleTime`',
  'SELECT ''SettleDueTime already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col := (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 't_referral_commission_detail'
    AND COLUMN_NAME = 'RewardOrderIndex'
);
SET @sql := IF(
  @col = 0,
  'ALTER TABLE `t_referral_commission_detail`
    ADD COLUMN `RewardOrderIndex` INT NOT NULL DEFAULT 0 COMMENT ''被邀请人第几单'' AFTER `SettleDueTime`',
  'SELECT ''RewardOrderIndex already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UPDATE `t_coupon_template`
SET
  `Remark` = '被邀请人注册即发 5 元券',
  `UpdateTime` = NOW()
WHERE `IsDeleted` = 0
  AND `TemplateCode` = 'REFERRAL_INVITEE';

-- =============================================================================
-- migrate_kpi_area_info_match.sql
-- 存量库：分区明细增加匹配维度/方式/前缀长度（可重复执行）
-- =============================================================================
SET @db := DATABASE();

SET @col := (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 't_kpi_area_info'
    AND COLUMN_NAME = 'MatchDim'
);
SET @sql := IF(
  @col = 0,
  'ALTER TABLE `t_kpi_area_info`
    ADD COLUMN `MatchDim` VARCHAR(16) NOT NULL DEFAULT ''country'' COMMENT ''匹配维度：zip/state/city/country'' AFTER `RightPostcode`,
    ADD COLUMN `MatchMode` VARCHAR(16) NOT NULL DEFAULT ''all'' COMMENT ''匹配方式：prefix/exact/range/all'' AFTER `MatchDim`,
    ADD COLUMN `PrefixLen` INT NOT NULL DEFAULT 0 COMMENT ''邮编前缀长度（zip+prefix）'' AFTER `MatchMode`',
  'SELECT ''kpi area match columns already exist'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @idx := (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 't_kpi_area_info'
    AND INDEX_NAME = 'idx_kpi_info_area_dim'
);
SET @sql := IF(
  @idx = 0,
  'ALTER TABLE `t_kpi_area_info` ADD KEY `idx_kpi_info_area_dim` (`AreaId`, `CountryCode`, `MatchDim`)',
  'SELECT ''idx_kpi_info_area_dim already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UPDATE `t_kpi_area_info`
SET
  `MatchDim` = CASE
    WHEN IFNULL(`LeftPostcode`, '') <> '' OR IFNULL(`RightPostcode`, '') <> '' THEN 'zip'
    WHEN IFNULL(`City`, '') <> '' THEN 'city'
    WHEN IFNULL(`Province`, '') <> '' THEN 'state'
    ELSE 'country'
  END,
  `MatchMode` = CASE
    WHEN IFNULL(`LeftPostcode`, '') <> '' AND IFNULL(`RightPostcode`, '') = '' THEN 'prefix'
    WHEN IFNULL(`LeftPostcode`, '') <> '' AND `LeftPostcode` = `RightPostcode` THEN 'exact'
    WHEN IFNULL(`LeftPostcode`, '') <> '' AND IFNULL(`RightPostcode`, '') <> '' THEN 'range'
    WHEN IFNULL(`City`, '') <> '' OR IFNULL(`Province`, '') <> '' THEN 'exact'
    ELSE 'all'
  END,
  `PrefixLen` = CASE
    WHEN IFNULL(`LeftPostcode`, '') <> '' AND IFNULL(`RightPostcode`, '') = '' THEN CHAR_LENGTH(`LeftPostcode`)
    WHEN IFNULL(`LeftPostcode`, '') <> '' AND `LeftPostcode` = `RightPostcode` THEN CHAR_LENGTH(`LeftPostcode`)
    WHEN IFNULL(`LeftPostcode`, '') <> '' AND IFNULL(`RightPostcode`, '') <> '' THEN LEAST(CHAR_LENGTH(`LeftPostcode`), CHAR_LENGTH(`RightPostcode`))
    ELSE 0
  END
WHERE IFNULL(`MatchDim`, '') IN ('', 'country')
  AND (
    IFNULL(`LeftPostcode`, '') <> ''
    OR IFNULL(`RightPostcode`, '') <> ''
    OR IFNULL(`Province`, '') <> ''
    OR IFNULL(`City`, '') <> ''
  );

-- =============================================================================
-- migrate_kpi_area_info_area_id_index.sql
-- 分区明细按 AreaId 全量/分页查询时避免 filesort 打满 sort_buffer（查价 LIVE 路径易踩）
-- =============================================================================
SET @db := DATABASE();

SET @idx := (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 't_kpi_area_info'
    AND INDEX_NAME = 'idx_kpi_info_area_id'
);
SET @sql := IF(
  @idx = 0,
  'ALTER TABLE `t_kpi_area_info` ADD KEY `idx_kpi_info_area_id` (`AreaId`, `Id`)',
  'SELECT ''idx_kpi_info_area_id already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- =============================================================================
-- migrate_kpi_area_publish_record.sql
-- 分区包发布表（价区 / 黑名单 / 偏远）：卖价/规则发布钉包，计费按 version 加载
-- =============================================================================
SET @db := DATABASE();

SET @tbl := (
  SELECT COUNT(1)
  FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 't_kpi_area_publish_record'
);
SET @sql := IF(
  @tbl = 0,
  'CREATE TABLE `t_kpi_area_publish_record` (
    `Id` INT NOT NULL AUTO_INCREMENT COMMENT ''主键'',
    `AreaId` INT NOT NULL COMMENT ''所属分区表 t_kpi_area.Id'',
    `AreaType` VARCHAR(32) NOT NULL COMMENT ''分区类型：price=价格分区，blacklist=黑名单，remote=偏远'',
    `Version` VARCHAR(32) NOT NULL COMMENT ''包版本号（yyyyMMddHHmmssfff）'',
    `Status` TINYINT NOT NULL DEFAULT 1 COMMENT ''状态：1=生效 Active，2=失效 Inactive'',
    `ContentHash` VARCHAR(64) NOT NULL COMMENT ''规范化分区明细 Content 的 SHA256（hex），同 AreaId+Hash 去重复用'',
    `Content` JSON NULL COMMENT ''分区明细快照 JSON（KpiAreaSnapshotRuleDto 数组）'',
    `PublishUser` VARCHAR(64) NOT NULL DEFAULT '''' COMMENT ''发布人'',
    `PublishTime` DATETIME NOT NULL COMMENT ''发布时间'',
    `Description` VARCHAR(512) NULL COMMENT ''发布说明'',
    `IsDeleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT ''软删除：0=否，1=是'',
    `CreateUserId` INT NOT NULL DEFAULT 0 COMMENT ''创建人'',
    `CreateTime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ''创建时间'',
    `UpdateUserId` INT NULL COMMENT ''更新人'',
    `UpdateTime` DATETIME NULL COMMENT ''更新时间'',
    PRIMARY KEY (`Id`),
    UNIQUE KEY `uk_kpi_pack_area_hash` (`AreaId`, `ContentHash`),
    KEY `idx_kpi_pack_area_ver` (`AreaId`, `Version`),
    KEY `idx_kpi_pack_area_type_time` (`AreaId`, `AreaType`, `PublishTime`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=''KPI分区发布包（价区/黑名单/偏远明细不可变快照）''',
  'SELECT ''t_kpi_area_publish_record already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 表已存在时回填表/字段注释（可重复执行；不包进 PREPARE）
ALTER TABLE `t_kpi_area_publish_record`
  COMMENT = 'KPI分区发布包（价区/黑名单/偏远明细不可变快照）',
  MODIFY COLUMN `Id` INT NOT NULL AUTO_INCREMENT COMMENT '主键',
  MODIFY COLUMN `AreaId` INT NOT NULL COMMENT '所属分区表 t_kpi_area.Id',
  MODIFY COLUMN `AreaType` VARCHAR(32) NOT NULL COMMENT '分区类型：price=价格分区，blacklist=黑名单，remote=偏远',
  MODIFY COLUMN `Version` VARCHAR(32) NOT NULL COMMENT '包版本号（yyyyMMddHHmmssfff）',
  MODIFY COLUMN `Status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：1=生效 Active，2=失效 Inactive',
  MODIFY COLUMN `ContentHash` VARCHAR(64) NOT NULL COMMENT '规范化分区明细 Content 的 SHA256（hex），同 AreaId+Hash 去重复用',
  MODIFY COLUMN `Content` JSON NULL COMMENT '分区明细快照 JSON（KpiAreaSnapshotRuleDto 数组）',
  MODIFY COLUMN `PublishUser` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '发布人',
  MODIFY COLUMN `PublishTime` DATETIME NOT NULL COMMENT '发布时间',
  MODIFY COLUMN `Description` VARCHAR(512) NULL COMMENT '发布说明',
  MODIFY COLUMN `IsDeleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除：0=否，1=是',
  MODIFY COLUMN `CreateUserId` INT NOT NULL DEFAULT 0 COMMENT '创建人',
  MODIFY COLUMN `CreateTime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  MODIFY COLUMN `UpdateUserId` INT NULL COMMENT '更新人',
  MODIFY COLUMN `UpdateTime` DATETIME NULL COMMENT '更新时间';

-- =============================================================================
-- migrate_customer_export_qualification_comment.sql
-- 存量库：补 t_customer_export_qualification 表注释与字段注释（可重复执行）
-- 列类型、空值、默认值与 db.sql 一致，只回填 COMMENT。
-- 请直接执行本段，不要包进 PREPARE。
-- =============================================================================
ALTER TABLE `t_customer_export_qualification`
  COMMENT = '客户出口资质备案',
  MODIFY COLUMN `Id` INT NOT NULL AUTO_INCREMENT COMMENT '主键',
  MODIFY COLUMN `CustomerId` INT NOT NULL COMMENT '归属客户 t_customer.Id',
  MODIFY COLUMN `CompanyName` VARCHAR(200) NOT NULL COMMENT '企业名称',
  MODIFY COLUMN `SocialCreditCode` VARCHAR(64) NOT NULL COMMENT '企业社会信用代码',
  MODIFY COLUMN `DomesticConsignor` VARCHAR(200) NOT NULL COMMENT '境内发货人',
  MODIFY COLUMN `DomesticConsignorCustomsCode` VARCHAR(64) NULL COMMENT '境内发货人海关代码',
  MODIFY COLUMN `ProducerName` VARCHAR(200) NOT NULL COMMENT '生产销售单位',
  MODIFY COLUMN `ProducerCustomsCode` VARCHAR(64) NULL COMMENT '生产销售单位海关代码',
  MODIFY COLUMN `CustomsPort` VARCHAR(100) NULL COMMENT '备案海关港口地',
  MODIFY COLUMN `IsDefault` TINYINT NOT NULL DEFAULT 0 COMMENT '是否默认：0-否，1-是（同客户仅一条）',
  MODIFY COLUMN `Remark` VARCHAR(512) NULL COMMENT '备注',
  MODIFY COLUMN `IsDeleted` TINYINT NOT NULL DEFAULT 0 COMMENT '软删除：0-否，1-是',
  MODIFY COLUMN `ConcurrencyStamp` CHAR(36) NOT NULL DEFAULT (UUID()) COMMENT '并发戳',
  MODIFY COLUMN `CreateUserId` INT NOT NULL DEFAULT 0 COMMENT '创建人',
  MODIFY COLUMN `CreateTime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  MODIFY COLUMN `UpdateUserId` INT NULL COMMENT '更新人',
  MODIFY COLUMN `UpdateTime` DATETIME NULL COMMENT '更新时间';

-- =============================================================================
-- migrate_warehouse_address_sort_remark.sql
-- 仓库地址：展示排序（选填<E98089><E5A1AB>? 备注（选填<E98089><E5A1AB>?
-- =============================================================================
SET @db := DATABASE();

SET @col := (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 't_warehouse_address'
    AND COLUMN_NAME = 'Sort'
);
SET @sql := IF(
  @col = 0,
  'ALTER TABLE `t_warehouse_address`
    ADD COLUMN `Sort` INT NULL DEFAULT NULL COMMENT ''展示排序，越小越靠前'' AFTER `ShippingAddress`',
  'SELECT ''Sort already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col := (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db
    AND TABLE_NAME = 't_warehouse_address'
    AND COLUMN_NAME = 'Remark'
);
SET @sql := IF(
  @col = 0,
  'ALTER TABLE `t_warehouse_address`
    ADD COLUMN `Remark` VARCHAR(512) NULL DEFAULT NULL COMMENT ''备注'' AFTER `Sort`',
  'SELECT ''Remark already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;