-- 1. 修正余额 (依据核销事实)
-- 这一步首先将 balance_amount 修正为客观的使用事实
UPDATE user_coupons uc
JOIN system_coupons sc ON uc.coupon_id = sc.id
LEFT JOIN (
    SELECT user_coupon_id, SUM(applied_amount) as used_sum FROM order_coupons GROUP BY user_coupon_id
) oc_agg ON uc.id = oc_agg.user_coupon_id
LEFT JOIN (
    SELECT user_coupon_id, ABS(SUM(change_amount)) as used_sum FROM user_coupon_ledger WHERE action = 'apply' GROUP BY user_coupon_id
) l_agg ON uc.id = l_agg.user_coupon_id
SET uc.balance_amount = sc.discount_value - GREATEST(IFNULL(oc_agg.used_sum, 0), IFNULL(l_agg.used_sum, 0))
WHERE sc.discount_type = 1 -- 仅限余额券
  AND uc.balance_amount != (sc.discount_value - GREATEST(IFNULL(oc_agg.used_sum, 0), IFNULL(l_agg.used_sum, 0)));

-- 2. 逻辑 A: 如果余额已经扣完 (balance_amount = 0)，状态必须为 2 (已使用)
-- 优先级最高，无论是否到期，只要用完了就是"已使用”
UPDATE user_coupons uc
SET uc.status = 2
WHERE uc.balance_amount = 0 
  AND uc.status != 2;

-- 3. 逻辑 B: 如果余额 > 0，且已到期 (valid_end < NOW)，状态必须为 3 (已过期)
-- 包含：完全没用的到期了、用了一半的到期了
UPDATE user_coupons uc
SET uc.status = 3
WHERE uc.balance_amount > 0 
  AND uc.valid_end < NOW() 
  AND uc.status != 3;

-- 4. 逻辑 C: 如果余额 > 0，且未到期 (valid_end > NOW)，状态必须为 1 (未使用/进行中)
UPDATE user_coupons uc
SET uc.status = 1
WHERE uc.balance_amount > 0 
  AND uc.valid_end > NOW() 
  AND uc.status != 1;

-- 5. 补全 used_order_id：对于已用完的券，确保关联了最后一次使用的订单ID
UPDATE user_coupons uc
JOIN (
    SELECT user_coupon_id, MAX(order_id) as last_order_id
    FROM order_coupons
    GROUP BY user_coupon_id
) last_oc ON uc.id = last_oc.user_coupon_id
SET uc.used_order_id = last_oc.last_order_id
WHERE uc.balance_amount = 0 
  AND (uc.used_order_id IS NULL OR uc.used_order_id = 0);