cfc/cfc-backend/sql/cleanup_test_dirty_data.sql

-- ============================================================
-- 测试脏数据清理脚本（《角色权限与菜单归类整合方案》B-7）
-- 目标库: 192.168.16.251:3306/zxyj
-- 使用方式: 逐段执行。每段先 SELECT 预览，确认无误后再执行 DELETE/UPDATE。
-- 已排查修正: points_log 列名(task_id/amount)、自引用子查询 ERROR 1093、
--             LIKE 通配符、users.role NOT NULL、role 为 MySQL 8.0 保留字需加反引号
-- ============================================================

-- 重要: 本文件含中文匹配模式，必须确保连接字符集为 utf8mb4，
-- 否则 LIKE 匹配失效或报编码错误（命令行执行时: mysql --default-character-set=utf8mb4）
SET NAMES utf8mb4;

-- ------------------------------------------------------------
-- 第 1 步：清理 TEST-Energy-Grant 测试任务
-- 注意执行顺序: 先 1.2/1.4（依赖 tasks 关联），再 1.3 删除任务
-- ------------------------------------------------------------

-- 1.1 预览将被删除的任务
SELECT id, family_id, creator_id, title, status, created_at
FROM tasks
WHERE title LIKE 'TEST-Energy-Grant%';

-- 1.2 预览关联的积分流水（通过 task_id 关联，须先于 1.3 执行）
SELECT pl.id, pl.child_id, pl.task_id, pl.amount, pl.description, pl.created_at
FROM points_log pl
INNER JOIN tasks t ON pl.task_id = t.id
WHERE t.title LIKE 'TEST-Energy-Grant%';

-- 1.3 （可选）确认后删除关联积分流水（必须在 1.4 之前执行）
DELETE pl FROM points_log pl
INNER JOIN tasks t ON pl.task_id = t.id
WHERE t.title LIKE 'TEST-Energy-Grant%';

-- 1.4 确认后删除任务
DELETE FROM tasks WHERE title LIKE 'TEST-Energy-Grant%';

-- ------------------------------------------------------------
-- 第 2 步：清理乱码昵称/家庭名（UTF-8 按 latin1 误读产生的乱码，如"娴嬭瘯骞哥?瀹跺涵"）
-- 说明: MySQL LIKE 中 ? 不是通配符（通配符是 _ 和 %），乱码按实际片段匹配；
--       执行预览后如还有其他乱码片段，请自行补充 LIKE 条件
-- ------------------------------------------------------------

-- 2.1 预览乱码家庭
SELECT id, name, invite_code, created_at
FROM families
WHERE name LIKE '%娴嬭瘯%' OR name LIKE '%瀹跺涵%';

-- 2.2 预览乱码用户昵称
SELECT id, nickname, phone, `role`, family_id, created_at
FROM users
WHERE nickname LIKE '%娴嬭瘯%' OR nickname LIKE '%瀹跺涵%';

-- 2.3 确认后：清理乱码家庭
--     修正: MySQL 不允许 UPDATE users 时子查询 users 同表（ERROR 1093），
--     且 families 在 FROM 时不能作为 UPDATE 目标，改用多表 UPDATE JOIN
-- 2.3a 解除用户与乱码家庭的关联
UPDATE users u
INNER JOIN families f ON u.family_id = f.id
SET u.family_id = NULL
WHERE f.name LIKE '%娴嬭瘯%' OR f.name LIKE '%瀹跺涵%';

-- 2.3b 删除乱码家庭
DELETE FROM families WHERE name LIKE '%娴嬭瘯%' OR name LIKE '%瀹跺涵%';

-- 2.4 确认后：清理乱码昵称用户（软删或硬删二选一）
-- 软删（推荐，users 表 deleted 列已由迁移 98 创建）:
UPDATE users SET deleted = 1 WHERE nickname LIKE '%娴嬭瘯%' OR nickname LIKE '%瀹跺涵%';
-- 硬删（与软删二选一，默认注释）:
-- DELETE FROM users WHERE nickname LIKE '%娴嬭瘯%' OR nickname LIKE '%瀹跺涵%';

-- ------------------------------------------------------------
-- 第 3 步：历史数据角色口径修复（B-2 配套：审核已通过但 roles 未回写的存量用户）
-- 说明: users.role 为 VARCHAR(20) NOT NULL（迁移 99），不会为 NULL；
--       role 是 MySQL 8.0 保留关键字，必须用反引号包裹
-- ------------------------------------------------------------

-- 3.1 预览：营养师已审核通过但 roles 中无 nutritionist
SELECT id, nickname, phone, `role`, roles, nutritionist_status
FROM users
WHERE nutritionist_status = 'approved'
  AND (roles IS NULL OR roles = '' OR roles NOT LIKE '%nutritionist%');

-- 3.2 预览：规划师已审核通过但 roles 中无 teacher
SELECT id, nickname, phone, `role`, roles, teacher_status
FROM users
WHERE teacher_status = 'approved'
  AND (roles IS NULL OR roles = '' OR roles NOT LIKE '%teacher%');

-- 3.3 预览：管家已认证但 roles 中无 butler
SELECT id, nickname, phone, `role`, roles, butler_status
FROM users
WHERE butler_status = 'approved'
  AND (roles IS NULL OR roles = '' OR roles NOT LIKE '%butler%');

-- 3.4 预览：供应商已审核但 roles 中无 supplier
SELECT id, nickname, phone, `role`, roles, vendor_status
FROM users
WHERE vendor_status = 'approved'
  AND (roles IS NULL OR roles = '' OR roles NOT LIKE '%supplier%');

-- 3.5 确认后回写 roles（JSON 数组格式；roles 为 NULL/空则新建，已有 JSON 数组则追加）
-- 营养师
UPDATE users
SET roles = CASE
        WHEN roles IS NULL OR roles = '' THEN CONCAT('["', `role`, '","nutritionist"]')
        WHEN roles LIKE '[%]' THEN REPLACE(roles, ']', ',"nutritionist"]')
        ELSE CONCAT(roles, ',nutritionist')
    END
WHERE nutritionist_status = 'approved'
  AND (roles IS NULL OR roles = '' OR roles NOT LIKE '%nutritionist%');

-- 规划师
UPDATE users
SET roles = CASE
        WHEN roles IS NULL OR roles = '' THEN CONCAT('["', `role`, '","teacher"]')
        WHEN roles LIKE '[%]' THEN REPLACE(roles, ']', ',"teacher"]')
        ELSE CONCAT(roles, ',teacher')
    END
WHERE teacher_status = 'approved'
  AND (roles IS NULL OR roles = '' OR roles NOT LIKE '%teacher%');

-- 管家
UPDATE users
SET roles = CASE
        WHEN roles IS NULL OR roles = '' THEN CONCAT('["', `role`, '","butler"]')
        WHEN roles LIKE '[%]' THEN REPLACE(roles, ']', ',"butler"]')
        ELSE CONCAT(roles, ',butler')
    END
WHERE butler_status = 'approved'
  AND (roles IS NULL OR roles = '' OR roles NOT LIKE '%butler%');

-- 供应商（含遗留 vendor 审核通过记录）
UPDATE users
SET roles = CASE
        WHEN roles IS NULL OR roles = '' THEN CONCAT('["', `role`, '","supplier"]')
        WHEN roles LIKE '[%]' THEN REPLACE(roles, ']', ',"supplier"]')
        ELSE CONCAT(roles, ',supplier')
    END
WHERE vendor_status = 'approved'
  AND (roles IS NULL OR roles = '' OR roles NOT LIKE '%supplier%');

-- ============================================================
-- 执行完成后，受影响的已登录用户需重新登录以获取新 token 中的 roles
-- ============================================================