-- Apple经销商ERP系统 - 数据库认证测试脚本
-- 创建时间: 2025-01-27
-- 版本: v1.0

SET NAMES utf8mb4;
USE apple_erp;

-- =========================================
-- 1. 验证用户数据
-- =========================================
SELECT '=== 用户数据验证 ===' AS message;

SELECT 
    user_id AS '用户ID',
    username AS '用户名',
    real_name AS '真实姓名',
    phone AS '手机号',
    email AS '邮箱',
    status AS '状态',
    last_login_time AS '最后登录时间',
    last_login_ip AS '最后登录IP',
    create_time AS '创建时间'
FROM t_sys_user 
WHERE del_flag = '0'
ORDER BY user_id;

-- =========================================
-- 2. 验证用户角色关联
-- =========================================
SELECT '=== 用户角色关联验证 ===' AS message;

SELECT 
    u.username AS '用户名',
    u.real_name AS '真实姓名',
    r.role_code AS '角色编码',
    r.role_name AS '角色名称',
    r.status AS '角色状态'
FROM t_sys_user u
JOIN t_sys_user_role ur ON u.user_id = ur.user_id
JOIN t_sys_role r ON ur.role_id = r.role_id
WHERE u.del_flag = '0' AND r.del_flag = '0' AND ur.del_flag = '0'
ORDER BY u.username, r.role_code;

-- =========================================
-- 3. 验证用户权限
-- =========================================
SELECT '=== 用户权限验证 ===' AS message;

SELECT 
    u.username AS '用户名',
    r.role_name AS '角色名称',
    m.menu_name AS '菜单名称',
    m.perms AS '权限标识'
FROM t_sys_user u
JOIN t_sys_user_role ur ON u.user_id = ur.user_id
JOIN t_sys_role r ON ur.role_id = r.role_id
JOIN t_sys_role_menu rm ON r.role_id = rm.role_id
JOIN t_sys_menu m ON rm.menu_id = m.menu_id
WHERE u.del_flag = '0' 
  AND r.del_flag = '0' 
  AND ur.del_flag = '0'
  AND rm.del_flag = '0'
  AND m.del_flag = '0'
  AND r.status = 1
  AND m.status = 1
ORDER BY u.username, r.role_name, m.sort;

-- =========================================
-- 4. 测试用户查询（模拟认证过程）
-- =========================================
SELECT '=== 模拟认证查询测试 ===' AS message;

-- 测试admin用户查询
SELECT 
    'admin用户查询' AS test_case,
    u.user_id,
    u.username,
    u.real_name,
    u.password,
    u.status,
    COUNT(DISTINCT r.role_id) AS role_count,
    COUNT(DISTINCT m.menu_id) AS menu_count
FROM t_sys_user u
LEFT JOIN t_sys_user_role ur ON u.user_id = ur.user_id AND ur.del_flag = '0'
LEFT JOIN t_sys_role r ON ur.role_id = r.role_id AND r.del_flag = '0' AND r.status = 1
LEFT JOIN t_sys_role_menu rm ON r.role_id = rm.role_id AND rm.del_flag = '0'
LEFT JOIN t_sys_menu m ON rm.menu_id = m.menu_id AND m.del_flag = '0' AND m.status = 1
WHERE u.username = 'admin' AND u.del_flag = '0' AND u.status = 1
GROUP BY u.user_id, u.username, u.real_name, u.password, u.status;

-- 测试operator用户查询
SELECT 
    'operator用户查询' AS test_case,
    u.user_id,
    u.username,
    u.real_name,
    u.password,
    u.status,
    COUNT(DISTINCT r.role_id) AS role_count,
    COUNT(DISTINCT m.menu_id) AS menu_count
FROM t_sys_user u
LEFT JOIN t_sys_user_role ur ON u.user_id = ur.user_id AND ur.del_flag = '0'
LEFT JOIN t_sys_role r ON ur.role_id = r.role_id AND r.del_flag = '0' AND r.status = 1
LEFT JOIN t_sys_role_menu rm ON r.role_id = rm.role_id AND rm.del_flag = '0'
LEFT JOIN t_sys_menu m ON rm.menu_id = m.menu_id AND m.del_flag = '0' AND m.status = 1
WHERE u.username = 'operator' AND u.del_flag = '0' AND u.status = 1
GROUP BY u.user_id, u.username, u.real_name, u.password, u.status;

-- 测试auditor用户查询
SELECT 
    'auditor用户查询' AS test_case,
    u.user_id,
    u.username,
    u.real_name,
    u.password,
    u.status,
    COUNT(DISTINCT r.role_id) AS role_count,
    COUNT(DISTINCT m.menu_id) AS menu_count
FROM t_sys_user u
LEFT JOIN t_sys_user_role ur ON u.user_id = ur.user_id AND ur.del_flag = '0'
LEFT JOIN t_sys_role r ON ur.role_id = r.role_id AND r.del_flag = '0' AND r.status = 1
LEFT JOIN t_sys_role_menu rm ON r.role_id = rm.role_id AND rm.del_flag = '0'
LEFT JOIN t_sys_menu m ON rm.menu_id = m.menu_id AND m.del_flag = '0' AND m.status = 1
WHERE u.username = 'auditor' AND u.del_flag = '0' AND u.status = 1
GROUP BY u.user_id, u.username, u.real_name, u.password, u.status;

-- =========================================
-- 5. 密码验证测试
-- =========================================
SELECT '=== 密码验证测试 ===' AS message;

-- 检查密码格式（应该是BCrypt格式）
SELECT 
    username AS '用户名',
    password AS '密码哈希',
    CASE 
        WHEN password LIKE '$2a$%' THEN 'BCrypt格式'
        WHEN password LIKE '$2b$%' THEN 'BCrypt格式'
        WHEN password LIKE '$2y$%' THEN 'BCrypt格式'
        ELSE '非BCrypt格式'
    END AS '密码格式'
FROM t_sys_user 
WHERE del_flag = '0'
ORDER BY username;

-- =========================================
-- 6. 数据完整性检查
-- =========================================
SELECT '=== 数据完整性检查 ===' AS message;

-- 检查是否有用户没有角色
SELECT 
    '无角色用户' AS check_item,
    COUNT(*) AS count
FROM t_sys_user u
LEFT JOIN t_sys_user_role ur ON u.user_id = ur.user_id AND ur.del_flag = '0'
WHERE u.del_flag = '0' AND u.status = 1 AND ur.user_id IS NULL;

-- 检查是否有角色没有菜单权限
SELECT 
    '无菜单权限角色' AS check_item,
    COUNT(*) AS count
FROM t_sys_role r
LEFT JOIN t_sys_role_menu rm ON r.role_id = rm.role_id AND rm.del_flag = '0'
WHERE r.del_flag = '0' AND r.status = 1 AND rm.role_id IS NULL;

-- 检查是否有菜单没有权限标识
SELECT 
    '无权限标识菜单' AS check_item,
    COUNT(*) AS count
FROM t_sys_menu m
WHERE m.del_flag = '0' AND m.status = 1 AND (m.perms IS NULL OR m.perms = '');

SELECT '=== 数据库认证测试完成 ===' AS message;