-- ============================================================
-- 企油通对接 · 一单多次加油（7.10 consume_amount 累计）
-- 生成：2026-09-15
-- ------------------------------------------------------------
-- 7.10 每条推送是一次加油，consume_amount = 本次占用运单金额（分）。
-- 一张订单可以分多次加油，因此：
--   · 订单累计已加油金额 used_amount / 次数 used_num
--   · 退款金额 = 订单金额 − 已加油金额（剩余未加油金额），不能超过它
--   · 每笔加油都通知下单平台（amount = 本次占用金额），逐笔记录是否已通知
--
-- 执行顺序：... -> qyt_migration_refund_invoice_driver.sql -> 本文件
-- ============================================================

-- ------------------------------------------------------------
-- 1. 订单：已加油金额 / 次数
--    use_status 语义不变：0 未加油 / 1 有加油记录（可能只加了一部分）
-- ------------------------------------------------------------
ALTER TABLE `yoshop_partners_order`
  ADD COLUMN `used_amount` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '已加油占用金额(元)，7.10 consume_amount 累计' AFTER `use_status`,
  ADD COLUMN `used_num`    int(11)       NOT NULL DEFAULT 0    COMMENT '加油次数' AFTER `used_amount`;

-- ------------------------------------------------------------
-- 2. 消费流水：去重键 + 逐笔通知状态
--    原唯一键 (waybill_no_code, order_code) 有缺陷：文档里 order_code 是选填，
--    不传时同一运单的第二笔加油会被当成重复推送丢掉，已加油金额就少记了。
--    改为 (waybill_no_code, consume_key)：有 order_code 用它，没有就用本笔消费特征字段的指纹。
-- ------------------------------------------------------------
ALTER TABLE `yoshop_qyt_consume`
  ADD COLUMN `consume_key`   varchar(64) NOT NULL DEFAULT '' COMMENT '去重键：order_code，缺失时为消费特征指纹(FP+md5)' AFTER `order_code`,
  ADD COLUMN `notify_status` tinyint(1)  NOT NULL DEFAULT 0  COMMENT '是否已通知下单平台 0否 1是' AFTER `raw`,
  ADD COLUMN `notify_time`   int(11)     NOT NULL DEFAULT 0  COMMENT '通知成功时间' AFTER `notify_status`,
  ADD INDEX `idx_order_notify` (`order_id`, `notify_status`);

-- 2.1 回填去重键。指纹口径与 CallbackQueue 一致：
--     md5(quota_id|pay_time|consume_amount|pay_fee|station_code|litter)，取值来自回调原文 raw
UPDATE `yoshop_qyt_consume`
SET `consume_key` = `order_code`
WHERE `consume_key` = '' AND `order_code` <> '';

UPDATE `yoshop_qyt_consume`
SET `consume_key` = CONCAT('FP', MD5(CONCAT_WS('|',
      IFNULL(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(`raw`, '$.quota_id')), 'null'), ''),
      IFNULL(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(`raw`, '$.pay_time')), 'null'), ''),
      IFNULL(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(`raw`, '$.consume_amount')), 'null'), ''),
      IFNULL(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(`raw`, '$.pay_fee')), 'null'), ''),
      IFNULL(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(`raw`, '$.station_code')), 'null'), ''),
      IFNULL(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(`raw`, '$.litter')), 'null'), '')
    )))
WHERE `consume_key` = '' AND `raw` IS NOT NULL AND JSON_VALID(`raw`);

-- 2.2 兜底：原文不可解析的历史行用主键，保证唯一键能建起来
UPDATE `yoshop_qyt_consume` SET `consume_key` = CONCAT('ID', `id`) WHERE `consume_key` = '';

-- 2.3 更换唯一键
--     ⚠️ 执行前可先检查是否有冲突：
--     SELECT waybill_no_code, consume_key, COUNT(*) c FROM yoshop_qyt_consume GROUP BY waybill_no_code, consume_key HAVING c > 1;
ALTER TABLE `yoshop_qyt_consume`
  DROP INDEX `uk_consume`,
  ADD UNIQUE KEY `uk_consume` (`waybill_no_code`, `consume_key`);

-- ------------------------------------------------------------
-- 3. 回填订单已加油金额 / 次数
-- ------------------------------------------------------------
UPDATE `yoshop_partners_order` o
JOIN (
  SELECT `order_id`, SUM(`consume_amount`) AS fen, COUNT(*) AS n
  FROM `yoshop_qyt_consume` GROUP BY `order_id`
) c ON c.`order_id` = o.`order_id`
SET o.`used_amount` = ROUND(c.fen / 100, 2),
    o.`used_num`    = c.n;

-- 3.1 存量：订单已回调过加油结果的，其消费流水视为已通知，避免上线后被重推
UPDATE `yoshop_qyt_consume` c
JOIN `yoshop_partners_order` o ON o.`order_id` = c.`order_id`
SET c.`notify_status` = 1,
    c.`notify_time`   = o.`use_callback_time`
WHERE o.`use_callback` = 1;

-- ============================================================
-- 回滚：
-- ALTER TABLE `yoshop_qyt_consume` DROP INDEX `uk_consume`, ADD UNIQUE KEY `uk_consume` (`waybill_no_code`, `order_code`);
-- ALTER TABLE `yoshop_qyt_consume` DROP COLUMN `consume_key`, DROP COLUMN `notify_status`, DROP COLUMN `notify_time`, DROP INDEX `idx_order_notify`;
-- ALTER TABLE `yoshop_partners_order` DROP COLUMN `used_amount`, DROP COLUMN `used_num`;
-- ============================================================