Tolan/backend/migrations/backfill_users_stone_balances.sql

-- 将历史数据回填到 users.conversation_energy_stone_total / crystal_stone_total
-- 规则与 EnergyStoneService.get_stone_counts 对齐（对话侧）：
--   对话余额 = COUNT(conversation_energy_stones) + bonus - FLOOR(SUM(asset_purchase_logs.cost_energy))
-- 水晶余额 = COUNT(mission_energy_stones) - FLOOR(SUM(asset_purchase_logs.cost_crystal))
-- 若库中无 asset_purchase_logs 表，请先注释掉 spent 相关子查询，或仅执行第一节 ALTER 后用手工脚本。
--
-- 说明：若购物消耗仅记在 purchase_orders（无流水表），需单独按订单汇总后再 UPDATE，本脚本未合并订单表逻辑。

-- 1) 对话能量石余额
UPDATE `users` u
INNER JOIN (
  SELECT
    uu.user_id AS uid,
    GREATEST(
      0,
      COALESCE(ce.cnt, 0)
        + COALESCE(b.bonus_conversation_count, 0)
        - COALESCE(sp.energy_spent, 0)
    ) AS bal
  FROM `users` uu
  LEFT JOIN (
    SELECT user_id, COUNT(*) AS cnt
    FROM `conversation_energy_stones`
    GROUP BY user_id
  ) ce ON ce.user_id = uu.user_id
  LEFT JOIN `user_bonus_energy_stones` b ON b.user_id = uu.user_id
  LEFT JOIN (
    SELECT user_id, FLOOR(COALESCE(SUM(cost_energy), 0)) AS energy_spent
    FROM `asset_purchase_logs`
    GROUP BY user_id
  ) sp ON sp.user_id = uu.id
) x ON x.uid = u.user_id
SET u.conversation_energy_stone_total = x.bal;

-- 2) 水晶石余额
UPDATE `users` u
INNER JOIN (
  SELECT
    uu.user_id AS uid,
    GREATEST(
      0,
      COALESCE(ms.cnt, 0) - COALESCE(sp.crystal_spent, 0)
    ) AS bal
  FROM `users` uu
  LEFT JOIN (
    SELECT user_id, COUNT(*) AS cnt
    FROM `mission_energy_stones`
    GROUP BY user_id
  ) ms ON ms.user_id = uu.user_id
  LEFT JOIN (
    SELECT user_id, FLOOR(COALESCE(SUM(cost_crystal), 0)) AS crystal_spent
    FROM `asset_purchase_logs`
    GROUP BY user_id
  ) sp ON sp.user_id = uu.id
) x ON x.uid = u.user_id
SET u.crystal_stone_total = x.bal;