-- ============================================================================
-- migration_v3.sql
-- 提现迁移 + 佣金余额闭环修复
-- ============================================================================
--
-- 目的:
--   1. 把 ticket(提现工单) 表里 status=4 的 185 条记录全量重迁到 withdrawals 表
--      - 删除 5 条历史错迁的 status=3 (取消/拒绝) 单
--      - 补迁 8 条历史漏迁的 status=4 (已通过) 单
--   2. 修正 13 个 user.commission 字段,使其等于 SUM(全部 type=33 日志)
--   3. content 写入 '历史提现 #<ticket_id>',支持反查 ticket 源头
--
-- 执行方式(注意 --default-character-set 必须显式声明):
--   docker exec -i ppanel-mysql mysql --default-character-set=utf8mb4 \
--     -uroot -pppanel_dev ppanel < migration_v3.sql
--
-- 回滚方式(假设 COMMIT 之后想恢复):
--   TRUNCATE TABLE withdrawals;
--   INSERT INTO withdrawals SELECT * FROM withdrawals_backup_v3;
--   UPDATE user u JOIN user_commission_backup_v3 b ON b.id=u.id SET u.commission = b.commission;
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 阶段 0: 备份(在事务外执行,即使后续 ROLLBACK 备份也保留)
-- ----------------------------------------------------------------------------
DROP TABLE IF EXISTS withdrawals_backup_v3;
CREATE TABLE withdrawals_backup_v3 AS SELECT * FROM withdrawals;

DROP TABLE IF EXISTS user_commission_backup_v3;
CREATE TABLE user_commission_backup_v3 AS
  SELECT id, commission, NOW() AS backup_at FROM user WHERE commission <> 0;

SELECT 'backup_done' AS step,
  (SELECT COUNT(*) FROM withdrawals_backup_v3) AS withdrawals_rows,
  (SELECT COUNT(*) FROM user_commission_backup_v3) AS user_commission_rows;

-- ----------------------------------------------------------------------------
-- 阶段 1: 事务开始
-- ----------------------------------------------------------------------------
START TRANSACTION;

-- ----------------------------------------------------------------------------
-- 阶段 2: 清空 withdrawals 表
-- ----------------------------------------------------------------------------
TRUNCATE TABLE withdrawals;

-- ----------------------------------------------------------------------------
-- 阶段 3: 从 ticket 表全量重迁(严格只迁 status=4)
--   中文字面量统一 COLLATE utf8mb4_general_ci 跟 ticket 表一致
-- ----------------------------------------------------------------------------
INSERT INTO withdrawals (
  user_id, amount, content, status, reason,
  method, account, qr_code_url, created_at, updated_at
)
SELECT
  t.user_id,
  ROUND(CAST(TRIM(REPLACE(REPLACE(t.title, _utf8mb4'提现-' COLLATE utf8mb4_general_ci, ''),
                                     _utf8mb4'提现'  COLLATE utf8mb4_general_ci, '')) AS DECIMAL(18,2)) * 100) AS amount_cents,
  CONCAT(_utf8mb4'历史提现 #' COLLATE utf8mb4_0900_ai_ci, t.id) AS content,
  1 AS status,
  '' AS reason,
  CASE
    WHEN t.description LIKE _utf8mb4'支付宝%' COLLATE utf8mb4_general_ci THEN 1
    WHEN t.description LIKE _utf8mb4'微信%'   COLLATE utf8mb4_general_ci THEN 2
    WHEN UPPER(t.description) LIKE 'USDT%' OR UPPER(t.description) LIKE 'TRC20%' THEN 3
    ELSE 0
  END AS method,
  CASE
    WHEN UPPER(t.description) LIKE 'USDT(TRC20)-%'
         THEN TRIM(SUBSTRING(t.description, LOCATE('-', t.description) + 1))
    WHEN UPPER(t.description) LIKE 'USDT%' AND LOCATE('-', t.description) > 0
         THEN TRIM(SUBSTRING(t.description, LOCATE('-', t.description) + 1))
    WHEN t.description LIKE _utf8mb4'支付宝-data:image%' COLLATE utf8mb4_general_ci THEN _utf8mb4'支付宝收款码' COLLATE utf8mb4_0900_ai_ci
    WHEN t.description LIKE _utf8mb4'微信-data:image%'   COLLATE utf8mb4_general_ci THEN _utf8mb4'微信收款码'   COLLATE utf8mb4_0900_ai_ci
    WHEN CHAR_LENGTH(t.description) > 255 THEN LEFT(t.description, 255)
    ELSE t.description
  END AS account,
  '' AS qr_code_url,
  t.created_at,
  t.updated_at
FROM ticket t
WHERE t.title LIKE _utf8mb4'提现%' COLLATE utf8mb4_general_ci
  AND t.status = 4
  AND TRIM(REPLACE(REPLACE(t.title, _utf8mb4'提现-' COLLATE utf8mb4_general_ci, ''),
                                     _utf8mb4'提现'  COLLATE utf8mb4_general_ci, '')) REGEXP '^[0-9]+([.][0-9]+)?$'
  AND ROUND(CAST(TRIM(REPLACE(REPLACE(t.title, _utf8mb4'提现-' COLLATE utf8mb4_general_ci, ''),
                                                _utf8mb4'提现'  COLLATE utf8mb4_general_ci, '')) AS DECIMAL(18,2)) * 100) > 0
ORDER BY t.id;

-- ----------------------------------------------------------------------------
-- 阶段 4: 修正用户的 commission 字段,使其等于 SUM(type=33 日志)
-- ----------------------------------------------------------------------------
UPDATE user u
JOIN (
  SELECT object_id, SUM(CAST(JSON_EXTRACT(content,'$.amount') AS SIGNED)) AS log_sum
  FROM system_logs
  WHERE type = 33
  GROUP BY object_id
) s ON s.object_id = u.id
SET u.commission = s.log_sum
WHERE u.commission <> s.log_sum;

-- ----------------------------------------------------------------------------
-- 阶段 4b: 处理"前日志时代"账号 — 有 commission 余额但 system_logs 完全没记录
--   补一条 type=335 admin_adjust 日志记录历史基准余额,让账目闭环
--   content 标记 source=pre_log_baseline 便于追溯
-- ----------------------------------------------------------------------------
INSERT INTO system_logs (type, date, object_id, content, created_at)
SELECT 33,
       DATE_FORMAT(u.created_at, '%Y-%m-%d'),
       u.id,
       JSON_OBJECT(
         'type', 335,
         'amount', u.commission,
         'order_no', '',
         'timestamp', UNIX_TIMESTAMP(u.created_at)*1000,
         'source', 'pre_log_baseline'
       ),
       u.created_at
FROM user u
WHERE u.commission <> 0
  AND NOT EXISTS (
    SELECT 1 FROM system_logs s WHERE s.type=33 AND s.object_id=u.id
  );

-- ----------------------------------------------------------------------------
-- 阶段 5: 验证(任何一项 verdict=FAIL 都应 ROLLBACK)
-- ----------------------------------------------------------------------------

-- 5a. withdrawals 总数 = ticket status=4 数,且都 > 0
SELECT
  '5a_count_match' AS check_name,
  (SELECT COUNT(*) FROM ticket WHERE title LIKE _utf8mb4'提现%' COLLATE utf8mb4_general_ci AND status=4
    AND TRIM(REPLACE(REPLACE(title, _utf8mb4'提现-' COLLATE utf8mb4_general_ci, ''),
                              _utf8mb4'提现'  COLLATE utf8mb4_general_ci, '')) REGEXP '^[0-9]+([.][0-9]+)?$') AS expected,
  (SELECT COUNT(*) FROM withdrawals) AS actual,
  IF(
    (SELECT COUNT(*) FROM withdrawals) > 0
    AND (SELECT COUNT(*) FROM ticket WHERE title LIKE _utf8mb4'提现%' COLLATE utf8mb4_general_ci AND status=4
          AND TRIM(REPLACE(REPLACE(title, _utf8mb4'提现-' COLLATE utf8mb4_general_ci, ''),
                                    _utf8mb4'提现'  COLLATE utf8mb4_general_ci, '')) REGEXP '^[0-9]+([.][0-9]+)?$')
        = (SELECT COUNT(*) FROM withdrawals),
    'PASS', 'FAIL'
  ) AS verdict;

-- 5b. 没有 amount<=0
SELECT '5b_amount_positive' AS check_name,
  0 AS expected,
  (SELECT COUNT(*) FROM withdrawals WHERE amount <= 0) AS actual,
  IF((SELECT COUNT(*) FROM withdrawals WHERE amount <= 0) = 0, 'PASS', 'FAIL') AS verdict;

-- 5c. content 必须形如 '历史提现 #<digits>'
SELECT '5c_content_format' AS check_name,
  0 AS expected,
  (SELECT COUNT(*) FROM withdrawals
   WHERE content NOT REGEXP CONCAT('^', _utf8mb4'历史提现 #' COLLATE utf8mb4_0900_ai_ci, '[0-9]+$')) AS actual,
  IF((SELECT COUNT(*) FROM withdrawals
      WHERE content NOT REGEXP CONCAT('^', _utf8mb4'历史提现 #' COLLATE utf8mb4_0900_ai_ci, '[0-9]+$')) = 0,
     'PASS', 'FAIL') AS verdict;

-- 5d. user.commission == SUM(type=33 logs) 全用户闭环
SELECT '5d_balance_closure' AS check_name,
  0 AS expected,
  (SELECT COUNT(*) FROM user u
   LEFT JOIN (SELECT object_id, SUM(CAST(JSON_EXTRACT(content,'$.amount') AS SIGNED)) amt
              FROM system_logs WHERE type=33 GROUP BY object_id) s ON s.object_id=u.id
   WHERE u.commission <> COALESCE(s.amt, 0)) AS actual,
  IF((SELECT COUNT(*) FROM user u
      LEFT JOIN (SELECT object_id, SUM(CAST(JSON_EXTRACT(content,'$.amount') AS SIGNED)) amt
                 FROM system_logs WHERE type=33 GROUP BY object_id) s ON s.object_id=u.id
      WHERE u.commission <> COALESCE(s.amt, 0)) = 0, 'PASS', 'FAIL') AS verdict;

-- 5e. 31742 闭环具体值核对
SELECT '5e_user_31742' AS check_name,
  (SELECT COALESCE(SUM(CAST(JSON_EXTRACT(content,'$.amount') AS SIGNED)),0)
     FROM system_logs WHERE type=33 AND object_id=31742) AS expected_from_logs,
  (SELECT commission FROM user WHERE id=31742) AS actual_balance,
  (SELECT COUNT(*) FROM withdrawals WHERE user_id=31742) AS withdrawals_count;

-- ----------------------------------------------------------------------------
-- 阶段 6: 提交事务
-- 检查上面所有 verdict 是 PASS 后才 COMMIT,否则 ROLLBACK
-- ----------------------------------------------------------------------------
COMMIT;

-- ----------------------------------------------------------------------------
-- 阶段 7: 事后摘要
-- ----------------------------------------------------------------------------
SELECT 'final_summary' AS section,
  (SELECT COUNT(*) FROM withdrawals) AS withdrawals_count,
  (SELECT SUM(amount) FROM withdrawals WHERE status=1) AS total_amount_cents,
  (SELECT COUNT(*) FROM user u
   LEFT JOIN (SELECT object_id, SUM(CAST(JSON_EXTRACT(content,'$.amount') AS SIGNED)) amt
              FROM system_logs WHERE type=33 GROUP BY object_id) s ON s.object_id=u.id
   WHERE u.commission <> COALESCE(s.amt, 0)) AS imbalanced_users_after_fix;