Tolan/backend/migrations/seed_special_users_ab_groups.sql

/*
  预置 80 个特殊实验账号（尚未完成首次验证码登录）
  - A 组：A001@lyrria.com ~ A040@lyrria.com
  - B 组：B001@lyrria.com ~ B040@lyrria.com

  前置条件：
  1. 已执行 add_special_user_fields.sql
  2. users 表已存在（含 user_segment、is_test_user 等字段）

  账号状态说明：
  - user_type = guest，is_verified = 0，registered_at = NULL
  - 邮箱已占用，防止被其他用户注册
  - 首次验证码登录时，后端需识别「guest + is_special_user」并完成注册化
    （后续改造 verify_and_auth / register_with_email 时处理）

  幂等：按 email 冲突时仅更新特殊用户标记，不覆盖已注册用户数据
*/

SET NAMES utf8mb4;

-- A 组 A001 ~ A030
INSERT INTO `users` (
  `user_id`,
  `username`,
  `user_type`,
  `user_segment`,
  `is_test_user`,
  `is_special_user`,
  `special_group`,
  `email`,
  `is_verified`,
  `user_level`,
  `current_background_image_id`
)
SELECT
  UUID(),
  CONCAT('A', LPAD(n, 3, '0')),
  'guest',
  'individual',
  0,
  1,
  'A',
  CONCAT('A', LPAD(n, 3, '0'), '@lyrria.com'),
  0,
  0,
  'default'
FROM (
  SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
  UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
  UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
  UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
  UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25
  UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30
) AS seq
ON DUPLICATE KEY UPDATE
  `is_special_user` = IF(`user_type` = 'registered', `is_special_user`, VALUES(`is_special_user`)),
  `special_group` = IF(`user_type` = 'registered', `special_group`, VALUES(`special_group`));

-- A 组 A031 ~ A040（追加 10 人）
INSERT INTO `users` (
  `user_id`,
  `username`,
  `user_type`,
  `user_segment`,
  `is_test_user`,
  `is_special_user`,
  `special_group`,
  `email`,
  `is_verified`,
  `user_level`,
  `current_background_image_id`
)
SELECT
  UUID(),
  CONCAT('A', LPAD(n, 3, '0')),
  'guest',
  'individual',
  0,
  1,
  'A',
  CONCAT('A', LPAD(n, 3, '0'), '@lyrria.com'),
  0,
  0,
  'default'
FROM (
  SELECT 31 AS n UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34 UNION ALL SELECT 35
  UNION ALL SELECT 36 UNION ALL SELECT 37 UNION ALL SELECT 38 UNION ALL SELECT 39 UNION ALL SELECT 40
) AS seq
ON DUPLICATE KEY UPDATE
  `is_special_user` = IF(`user_type` = 'registered', `is_special_user`, VALUES(`is_special_user`)),
  `special_group` = IF(`user_type` = 'registered', `special_group`, VALUES(`special_group`));

-- B 组 B001 ~ B030
INSERT INTO `users` (
  `user_id`,
  `username`,
  `user_type`,
  `user_segment`,
  `is_test_user`,
  `is_special_user`,
  `special_group`,
  `email`,
  `is_verified`,
  `user_level`,
  `current_background_image_id`
)
SELECT
  UUID(),
  CONCAT('B', LPAD(n, 3, '0')),
  'guest',
  'individual',
  0,
  1,
  'B',
  CONCAT('B', LPAD(n, 3, '0'), '@lyrria.com'),
  0,
  0,
  'default'
FROM (
  SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
  UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
  UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
  UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
  UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25
  UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30
) AS seq
ON DUPLICATE KEY UPDATE
  `is_special_user` = IF(`user_type` = 'registered', `is_special_user`, VALUES(`is_special_user`)),
  `special_group` = IF(`user_type` = 'registered', `special_group`, VALUES(`special_group`));

-- B 组 B031 ~ B040（追加 10 人）
INSERT INTO `users` (
  `user_id`,
  `username`,
  `user_type`,
  `user_segment`,
  `is_test_user`,
  `is_special_user`,
  `special_group`,
  `email`,
  `is_verified`,
  `user_level`,
  `current_background_image_id`
)
SELECT
  UUID(),
  CONCAT('B', LPAD(n, 3, '0')),
  'guest',
  'individual',
  0,
  1,
  'B',
  CONCAT('B', LPAD(n, 3, '0'), '@lyrria.com'),
  0,
  0,
  'default'
FROM (
  SELECT 31 AS n UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34 UNION ALL SELECT 35
  UNION ALL SELECT 36 UNION ALL SELECT 37 UNION ALL SELECT 38 UNION ALL SELECT 39 UNION ALL SELECT 40
) AS seq
ON DUPLICATE KEY UPDATE
  `is_special_user` = IF(`user_type` = 'registered', `is_special_user`, VALUES(`is_special_user`)),
  `special_group` = IF(`user_type` = 'registered', `special_group`, VALUES(`special_group`));

-- 校验
SELECT
  `special_group`,
  COUNT(*) AS cnt
FROM `users`
WHERE `is_special_user` = 1
  AND `email` LIKE '%@lyrria.com'
  AND (`email` LIKE 'A%@lyrria.com' OR `email` LIKE 'B%@lyrria.com')
GROUP BY `special_group`
ORDER BY `special_group`;