-- ============================================
-- 验证 user_roles 表数据
-- ============================================

-- 1. 检查表是否创建成功
SHOW TABLES LIKE 'user_roles';

-- 2. 查看表结构
DESC user_roles;

-- 3. 统计数据
SELECT 
  '总记录数' as item,
  COUNT(*) as count
FROM user_roles
UNION ALL
SELECT 
  '用户数',
  COUNT(DISTINCT user_id)
FROM user_roles
UNION ALL
SELECT 
  '主身份数',
  COUNT(*)
FROM user_roles
WHERE is_primary = 1;

-- 4. 查看各角色分布
SELECT 
  role_type,
  COUNT(*) as count,
  COUNT(IF(is_primary = 1, 1, NULL)) as primary_count
FROM user_roles
GROUP BY role_type
ORDER BY count DESC;

-- 5. 查看前20条数据（包含用户信息）
SELECT 
  u.id,
  u.phone,
  u.role as user_table_role,
  ur.role_type as user_roles_table_role,
  IF(ur.is_primary = 1, '主身份', '附加身份') as role_status,
  ur.create_time
FROM user u
INNER JOIN user_roles ur ON u.id = ur.user_id
ORDER BY u.id
LIMIT 20;

-- 6. 检查数据一致性（user.role 应该等于 user_roles 中的主身份）
SELECT 
  '数据一致性检查' as check_item,
  COUNT(*) as inconsistent_count
FROM user u
LEFT JOIN user_roles ur ON u.id = ur.user_id AND ur.is_primary = 1
WHERE u.role IS NOT NULL 
  AND u.role != '' 
  AND (ur.role_type IS NULL OR u.role != ur.role_type);