all-project/test/fix-maintenance-worker-permissions.sql

-- ==========================================
-- 维修人员权限修复脚本
-- 功能：确保维修人员角色正确配置，能正确过滤只查看自己相关的订单
-- 时间：2025-06-30 18:45:00
-- 说明：使用UPSERT逻辑，支持重复执行
-- ==========================================

-- 第一步：创建维修人员和管理员角色（如果不存在）
INSERT INTO sys_role (
    role_id, role_name, role_key, role_sort, data_scope, 
    menu_check_strictly, dept_check_strictly, status, 
    del_flag, create_by, create_time, update_by, update_time, remark
) 
SELECT 
    5, '维修人员', 'maintenance_worker', 5, '5',
    1, 1, '0',
    '0', 1, now(), 1, now(), '负责具体维修工作的人员，只能查看和处理分配给自己的维修工单或自己创建的工单'
WHERE NOT EXISTS (SELECT 1 FROM sys_role WHERE role_id = 5);

INSERT INTO sys_role (
    role_id, role_name, role_key, role_sort, data_scope, 
    menu_check_strictly, dept_check_strictly, status, 
    del_flag, create_by, create_time, update_by, update_time, remark
) 
SELECT 
    6, '物业维修管理员', 'property_maintenance_admin', 6, '3',
    1, 1, '0',
    '0', 1, now(), 1, now(), '负责物业维修管理工作，可以管理所有维修工单、分配维修任务、查看维修统计'
WHERE NOT EXISTS (SELECT 1 FROM sys_role WHERE role_id = 6);

-- 第二步：确保维修人员角色有基础菜单权限
-- 为维修人员分配物业管理主菜单权限
INSERT IGNORE INTO sys_role_menu (role_id, menu_id) 
SELECT 5, menu_id FROM sys_menu WHERE menu_id = 2100; -- 物业管理主菜单

-- 为维修人员分配报修管理子菜单权限
INSERT IGNORE INTO sys_role_menu (role_id, menu_id) 
SELECT 5, menu_id FROM sys_menu WHERE menu_id = 2110; -- 报修管理菜单

-- 为维修人员分配基础功能权限
INSERT IGNORE INTO sys_role_menu (role_id, menu_id) VALUES
(5, 2111), -- 查询权限
(5, 2113), -- 修改权限
(5, 2117), -- 开始维修权限
(5, 2118), -- 完成维修权限
(5, 2120); -- 生成工单编号权限

-- 第三步：为物业维修管理员分配完整权限
INSERT IGNORE INTO sys_role_menu (role_id, menu_id) 
SELECT 6, menu_id FROM sys_menu WHERE menu_id BETWEEN 2100 AND 2199;

-- 第四步：创建测试用户（如果不存在）
-- 创建一个专门的维修人员测试账号
INSERT INTO sys_user (
    user_id, user_name, nick_name, email, phone, sex, avatar, password, status, 
    del_flag, login_ip, login_date, create_by, create_time, update_by, update_time, remark
) 
SELECT 
    999, 'maintenance_test', '测试维修员', 'maintenance@test.com', '13800000001', '0', '', 
    '$2a$10$7JB720yubVSOnlhHOY5aDuAEaG9MXOAG7Ap3CfvzDuR8YYnQbmLqK', -- 密码：admin123
    '0', '0', '', now(), 1, now(), 1, now(), '维修人员权限测试账号'
WHERE NOT EXISTS (SELECT 1 FROM sys_user WHERE user_id = 999);

-- 创建一个物业管理员测试账号
INSERT INTO sys_user (
    user_id, user_name, nick_name, email, phone, sex, avatar, password, status, 
    del_flag, login_ip, login_date, create_by, create_time, update_by, update_time, remark
) 
SELECT 
    998, 'property_admin_test', '测试物业管理员', 'property@test.com', '13800000002', '0', '', 
    '$2a$10$7JB720yubVSOnlhHOY5aDuAEaG9MXOAG7Ap3CfvzDuR8YYnQbmLqK', -- 密码：admin123
    '0', '0', '', now(), 1, now(), 1, now(), '物业管理员权限测试账号'
WHERE NOT EXISTS (SELECT 1 FROM sys_user WHERE user_id = 998);

-- 第五步：为测试用户分配角色
INSERT IGNORE INTO sys_user_role (user_id, role_id) VALUES
(999, 5),  -- 测试维修员 -> 维修人员角色
(998, 6);  -- 测试物业管理员 -> 物业维修管理员角色

-- 第六步：为现有总务处用户分配维修人员角色（如果用户存在）
INSERT IGNORE INTO sys_user_role (user_id, role_id) 
SELECT user_id, 5 FROM sys_user 
WHERE user_name IN ('zwc005', 'zwc007') AND user_id IS NOT NULL;

-- 为现有总务处用户分配物业管理员角色（如果用户存在）
INSERT IGNORE INTO sys_user_role (user_id, role_id) 
SELECT user_id, 6 FROM sys_user 
WHERE user_name IN ('zwc003', 'zwc004') AND user_id IS NOT NULL;

-- 第七步：创建测试数据 - 创建一些报修订单用于测试权限
-- 为测试维修员创建的报修订单
INSERT INTO pm_repair_order (
    id, order_no, title, description, repair_location, category_id, priority_level,
    reporter_id, reporter_name, reporter_phone, order_status, assignee_id, assignee_name,
    attachment_count, progress_count, create_by, create_time, update_by, update_time,
    tenant_id, create_dept, deleted
) 
SELECT 
    9901, 'RO20251230001', '测试报修-维修员自己创建', '这是维修员自己创建的报修订单，用于测试权限', 
    '测试楼101室', 1, 2, 999, '测试维修员', '13800000001', '1', NULL, NULL,
    0, 0, 999, now(), 999, now(), '000000', 103, 0
WHERE NOT EXISTS (SELECT 1 FROM pm_repair_order WHERE id = 9901);

-- 分配给测试维修员的报修订单
INSERT INTO pm_repair_order (
    id, order_no, title, description, repair_location, category_id, priority_level,
    reporter_id, reporter_name, reporter_phone, order_status, assignee_id, assignee_name,
    attachment_count, progress_count, create_by, create_time, update_by, update_time,
    tenant_id, create_dept, deleted
) 
SELECT 
    9902, 'RO20251230002', '测试报修-分配给维修员', '这是分配给维修员的报修订单，用于测试权限', 
    '测试楼102室', 1, 3, 1, '管理员', '13900000000', '2', 999, '测试维修员',
    0, 0, 1, now(), 1, now(), '000000', 103, 0
WHERE NOT EXISTS (SELECT 1 FROM pm_repair_order WHERE id = 9902);

-- 其他用户创建的报修订单（维修员不应该看到）
INSERT INTO pm_repair_order (
    id, order_no, title, description, repair_location, category_id, priority_level,
    reporter_id, reporter_name, reporter_phone, order_status, assignee_id, assignee_name,
    attachment_count, progress_count, create_by, create_time, update_by, update_time,
    tenant_id, create_dept, deleted
) 
SELECT 
    9903, 'RO20251230003', '测试报修-其他用户创建', '这是其他用户创建的报修订单，维修员不应该看到', 
    '测试楼103室', 1, 1, 998, '测试物业管理员', '13800000002', '1', NULL, NULL,
    0, 0, 998, now(), 998, now(), '000000', 103, 0
WHERE NOT EXISTS (SELECT 1 FROM pm_repair_order WHERE id = 9903);

-- 第八步：验证配置结果
SELECT '=== 角色配置验证 ===' as section;
SELECT 
    role_id, role_name, role_key, status, remark
FROM sys_role 
WHERE role_id IN (5, 6)
ORDER BY role_id;

SELECT '=== 用户角色分配验证 ===' as section;
SELECT 
    u.user_id, u.user_name, u.nick_name, r.role_name, r.role_key
FROM sys_user_role ur
JOIN sys_user u ON ur.user_id = u.user_id
JOIN sys_role r ON ur.role_id = r.role_id
WHERE ur.role_id IN (5, 6)
ORDER BY ur.role_id, u.user_id;

SELECT '=== 维修人员权限验证 ===' as section;
SELECT 
    COUNT(*) as menu_permission_count
FROM sys_role_menu 
WHERE role_id = 5;

SELECT '=== 测试数据验证 ===' as section;
SELECT 
    id, order_no, title, reporter_id, reporter_name, assignee_id, assignee_name, order_status
FROM pm_repair_order 
WHERE id BETWEEN 9901 AND 9903
ORDER BY id;

-- 第九步：权限测试说明
SELECT '=== 权限测试说明 ===' as section;
SELECT 
    '1. 使用maintenance_test账号登录，应该只能看到自己创建(9901)和分配给自己(9902)的工单' as test_case_1,
    '2. 使用property_admin_test账号登录，应该能看到所有工单(9901,9902,9903)' as test_case_2,
    '3. 使用admin账号登录，作为超级管理员应该能看到所有工单' as test_case_3;

-- 第十步：完成提示
SELECT 
    '权限修复完成' as status,
    'maintenance_test / admin123 - 维修人员测试账号' as test_account_1,
    'property_admin_test / admin123 - 物业管理员测试账号' as test_account_2,
    '请使用这些账号测试权限是否正确' as next_step;