-- Apple经销商ERP系统 - 系统表结构验证脚本
-- 创建时间: 2025-01-27
-- 版本: v1.0

SET NAMES utf8mb4;
USE apple_erp;

-- =========================================
-- 1. 验证表结构
-- =========================================
SELECT '=== 系统管理表结构验证 ===' AS message;

-- 检查表是否存在
SELECT 
    TABLE_NAME AS '表名',
    TABLE_COMMENT AS '表注释',
    TABLE_ROWS AS '记录数',
    CREATE_TIME AS '创建时间'
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'apple_erp' 
AND TABLE_NAME LIKE 't_sys_%'
ORDER BY TABLE_NAME;

-- =========================================
-- 2. 验证索引结构
-- =========================================
SELECT '=== 系统管理表索引验证 ===' AS message;

SELECT 
    TABLE_NAME AS '表名',
    INDEX_NAME AS '索引名',
    COLUMN_NAME AS '字段名',
    NON_UNIQUE AS '是否唯一',
    INDEX_TYPE AS '索引类型'
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = 'apple_erp' 
AND TABLE_NAME LIKE 't_sys_%'
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

-- =========================================
-- 3. 验证外键约束
-- =========================================
SELECT '=== 系统管理表外键约束验证 ===' AS message;

SELECT 
    TABLE_NAME AS '表名',
    CONSTRAINT_NAME AS '约束名',
    COLUMN_NAME AS '字段名',
    REFERENCED_TABLE_NAME AS '引用表名',
    REFERENCED_COLUMN_NAME AS '引用字段名'
FROM information_schema.KEY_COLUMN_USAGE 
WHERE TABLE_SCHEMA = 'apple_erp' 
AND TABLE_NAME LIKE 't_sys_%'
AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY TABLE_NAME, CONSTRAINT_NAME;

-- =========================================
-- 4. 验证基础数据
-- =========================================
SELECT '=== 系统基础数据验证 ===' AS message;

-- 用户数据验证
SELECT '用户数据:' AS info, COUNT(*) AS count FROM t_sys_user WHERE del_flag = '0';

-- 角色数据验证
SELECT '角色数据:' AS info, COUNT(*) AS count FROM t_sys_role WHERE del_flag = '0';

-- 菜单数据验证
SELECT '菜单数据:' AS info, COUNT(*) AS count FROM t_sys_menu WHERE del_flag = '0';

-- 字典类型数据验证
SELECT '字典类型数据:' AS info, COUNT(*) AS count FROM t_sys_dict_type WHERE del_flag = '0';

-- 字典项数据验证
SELECT '字典项数据:' AS info, COUNT(*) AS count FROM t_sys_dict_item WHERE del_flag = '0';

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

SELECT 
    u.username AS '用户名',
    r.role_name AS '角色名',
    ur.create_time 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_name;

-- =========================================
-- 6. 验证角色菜单关联
-- =========================================
SELECT '=== 角色菜单关联验证 ===' AS message;

SELECT 
    r.role_name AS '角色名',
    m.menu_name AS '菜单名',
    m.menu_type AS '菜单类型',
    rm.create_time AS '关联时间'
FROM t_sys_role r
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 r.del_flag = '0' AND m.del_flag = '0' AND rm.del_flag = '0'
ORDER BY r.role_name, m.sort;

-- =========================================
-- 7. 验证字典数据
-- =========================================
SELECT '=== 字典数据验证 ===' AS message;

SELECT 
    dt.dict_name AS '字典类型',
    di.dict_label AS '字典标签',
    di.dict_value AS '字典值',
    di.sort AS '排序'
FROM t_sys_dict_type dt
JOIN t_sys_dict_item di ON dt.dict_type_id = di.dict_type_id
WHERE dt.del_flag = '0' AND di.del_flag = '0'
ORDER BY dt.dict_type, di.sort;

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

-- 检查用户角色关联完整性
SELECT 
    '用户角色关联完整性' AS check_item,
    CASE 
        WHEN COUNT(*) = 0 THEN '通过'
        ELSE CONCAT('失败: ', COUNT(*), ' 条记录')
    END AS result
FROM t_sys_user_role ur
LEFT JOIN t_sys_user u ON ur.user_id = u.user_id
LEFT JOIN t_sys_role r ON ur.role_id = r.role_id
WHERE u.user_id IS NULL OR r.role_id IS NULL;

-- 检查角色菜单关联完整性
SELECT 
    '角色菜单关联完整性' AS check_item,
    CASE 
        WHEN COUNT(*) = 0 THEN '通过'
        ELSE CONCAT('失败: ', COUNT(*), ' 条记录')
    END AS result
FROM t_sys_role_menu rm
LEFT JOIN t_sys_role r ON rm.role_id = r.role_id
LEFT JOIN t_sys_menu m ON rm.menu_id = m.menu_id
WHERE r.role_id IS NULL OR m.menu_id IS NULL;

-- 检查字典项关联完整性
SELECT 
    '字典项关联完整性' AS check_item,
    CASE 
        WHEN COUNT(*) = 0 THEN '通过'
        ELSE CONCAT('失败: ', COUNT(*), ' 条记录')
    END AS result
FROM t_sys_dict_item di
LEFT JOIN t_sys_dict_type dt ON di.dict_type_id = dt.dict_type_id
WHERE dt.dict_type_id IS NULL;

SELECT '=== 系统表结构验证完成 ===' AS message;