-- 修复「当前地区不在配送范围」
-- 说明：若 province_ids 已有其他省份但不含贵州，上一版 UPDATE 会 0 行；本脚本强制追加 520000
-- 测试地址：贵州省/贵阳市/南明区 → province_id=520000, city_id=520100, area_id=520102

SET @now = UNIX_TIMESTAMP();

-- 1) 查看当前运费规则（执行后可对照）
SELECT id, type, weigh, province_ids, city_ids, LEFT(area_ids, 80) AS area_ids
FROM `fa_shopro_dispatch_express`
WHERE `deletetime` IS NULL OR `deletetime` = 0;

-- 2) 空区域 → 设为贵州省
UPDATE `fa_shopro_dispatch_express`
SET `province_ids` = '520000', `updatetime` = @now
WHERE (`deletetime` IS NULL OR `deletetime` = 0)
  AND (`province_ids` IS NULL OR `province_ids` = '')
  AND (`city_ids` IS NULL OR `city_ids` = '')
  AND (`area_ids` IS NULL OR `area_ids` = '');

-- 3) 已有省份但未含贵州 → 追加 520000（解决「更新了 0 行」的情况）
UPDATE `fa_shopro_dispatch_express`
SET `province_ids` = CONCAT(`province_ids`, ',520000'), `updatetime` = @now
WHERE (`deletetime` IS NULL OR `deletetime` = 0)
  AND `province_ids` IS NOT NULL
  AND `province_ids` <> ''
  AND FIND_IN_SET('520000', `province_ids`) = 0;

-- 4) 若仍不匹配：仅配置了 city/area 且不含贵州 → 清空并改为贵州省（演示环境）
UPDATE `fa_shopro_dispatch_express`
SET `province_ids` = '520000', `city_ids` = NULL, `area_ids` = NULL, `updatetime` = @now
WHERE (`deletetime` IS NULL OR `deletetime` = 0)
  AND (`province_ids` IS NULL OR `province_ids` = '')
  AND (
    (`city_ids` IS NOT NULL AND `city_ids` <> '')
    OR (`area_ids` IS NOT NULL AND `area_ids` <> '')
  )
  AND (
    FIND_IN_SET('520100', IFNULL(`city_ids`, '')) = 0
    AND FIND_IN_SET('520102', IFNULL(`area_ids`, '')) = 0
  );

-- 5) 演示商品专用：雄戈演示包邮 关联的运费规则强制覆盖贵州
UPDATE `fa_shopro_dispatch_express` de
INNER JOIN `fa_shopro_dispatch` d ON d.`type` = 'express' AND FIND_IN_SET(de.`id`, d.`type_ids`)
SET de.`province_ids` = '520000', de.`city_ids` = NULL, de.`area_ids` = NULL, de.`updatetime` = @now
WHERE (de.`deletetime` IS NULL OR de.`deletetime` = 0)
  AND (d.`deletetime` IS NULL OR d.`deletetime` = 0)
  AND d.`name` LIKE '%雄戈%';

SELECT '运费模板已修复，请刷新小程序后重新下单' AS info;

-- ========== 可选：演示环境全国包邮（若你在省外真机测试，取消下面注释执行） ==========
-- UPDATE `fa_shopro_dispatch_express` de
-- SET de.`province_ids` = (SELECT GROUP_CONCAT(id) FROM `fa_shopro_area` WHERE `level` = 1),
--     de.`city_ids` = NULL, de.`area_ids` = NULL, de.`updatetime` = UNIX_TIMESTAMP()
-- WHERE de.`deletetime` IS NULL OR de.`deletetime` = 0;