all-project/test/create-test-data.sql

-- ==========================================
-- 物业报修管理测试数据脚本
-- 功能：插入测试数据到数据库
-- 时间：2025-06-30
-- 说明：为测试报修管理功能创建必要的数据
-- ==========================================

-- 1. 插入维修分类数据（如果不存在）
INSERT IGNORE INTO `pm_repair_category` (
  `id`, `tenant_id`, `category_name`, `category_code`, `category_description`,
  `parent_id`, `level_depth`, `required_skills`, `skill_level`, 
  `estimated_duration`, `icon_url`, `color_code`, `sort_order`, `status`,
  `create_time`, `create_by`
) VALUES
(1, '000000', '电路维修', 'ELECTRIC', '电路、照明、开关、插座等电气设备维修', 0, 1, '电工技能', '2', 60, '/icons/electric.svg', '#ff6b35', 1, '1', NOW(), 1),
(2, '000000', '水暖维修', 'PLUMBING', '水管、暖气、卫生间、厨房设备维修', 0, 1, '水暖技能', '2', 90, '/icons/plumbing.svg', '#1890ff', 2, '1', NOW(), 1),
(3, '000000', '土建维修', 'CONSTRUCTION', '墙面、地面、天花板、门窗等土建维修', 0, 1, '泥工技能,木工技能', '2', 120, '/icons/construction.svg', '#52c41a', 3, '1', NOW(), 1),
(4, '000000', '设备维修', 'EQUIPMENT', '空调、电梯、监控等设备维修', 0, 1, '设备维修技能', '3', 90, '/icons/equipment.svg', '#722ed1', 4, '1', NOW(), 1),
(5, '000000', '园林维修', 'GARDEN', '绿化、草坪、树木修剪等园林维护', 0, 1, '园林技能', '1', 45, '/icons/garden.svg', '#13c2c2', 5, '1', NOW(), 1),
(6, '000000', '清洁维护', 'CLEANING', '日常清洁、垃圾处理、环境维护', 0, 1, '清洁技能', '1', 30, '/icons/cleaning.svg', '#fa8c16', 6, '1', NOW(), 1);

-- 2. 插入测试报修工单数据
INSERT IGNORE INTO `pm_repair_order` (
  `id`, `tenant_id`, `order_no`, `title`, `description`, `repair_location`,
  `category_id`, `priority_level`, `expected_finish_time`,
  `reporter_id`, `reporter_name`, `reporter_phone`, `reporter_dept`,
  `order_status`, `create_time`, `create_by`
) VALUES
(1001, '000000', 'RO20250630001', '办公室空调不制冷', '302办公室空调制冷效果差，温度显示异常，需要检查维修', '行政楼3楼302办公室', 4, 2, DATE_ADD(NOW(), INTERVAL 1 DAY), 1, '张三', '13900001001', '行政办公室', '1', NOW(), 1),
(1002, '000000', 'RO20250630002', '教室投影仪故障', '多媒体教室投影仪无法正常显示，有色彩偏差', '教学楼A座201教室', 4, 3, DATE_ADD(NOW(), INTERVAL 2 DAY), 1, '李四', '13900001002', '教务处', '1', NOW(), 1),
(1003, '000000', 'RO20250630003', '宿舍楼水管漏水', '学生宿舍3楼卫生间水管接头漏水，影响正常使用', '学生宿舍楼3楼卫生间', 2, 1, DATE_ADD(NOW(), INTERVAL 3 HOUR), 1, '王五', '13900001003', '后勤处', '2', NOW(), 1),
(1004, '000000', 'RO20250630004', '图书馆照明灯具损坏', '图书馆二楼阅览区多个LED灯管损坏，照明不足', '图书馆二楼阅览区', 1, 3, DATE_ADD(NOW(), INTERVAL 1 WEEK), 1, '赵六', '13900001004', '图书馆', '1', NOW(), 1),
(1005, '000000', 'RO20250630005', '食堂地面瓷砖松动', '第二食堂入口处地面瓷砖松动，存在安全隐患', '第二食堂入口', 3, 2, DATE_ADD(NOW(), INTERVAL 2 DAY), 1, '孙七', '13900001005', '餐饮服务中心', '3', NOW(), 1);

-- 3. 为已分派的工单添加分派信息
UPDATE `pm_repair_order` SET 
  `assign_time` = NOW(),
  `assignee_id` = 2,
  `assignee_name` = '维修工A',
  `assign_remark` = '紧急维修，请尽快处理'
WHERE `id` = 1003;

-- 4. 为进行中的工单添加开始时间
UPDATE `pm_repair_order` SET 
  `start_time` = DATE_SUB(NOW(), INTERVAL 30 MINUTE)
WHERE `id` = 1005;

-- 5. 验证插入的数据
SELECT 
  '数据插入完成' AS message,
  (SELECT COUNT(*) FROM pm_repair_category WHERE tenant_id = '000000') AS category_count,
  (SELECT COUNT(*) FROM pm_repair_order WHERE tenant_id = '000000' AND id >= 1001) AS order_count;

-- 6. 显示插入的报修工单数据
SELECT 
  id,
  order_no,
  title,
  repair_location,
  priority_level,
  order_status,
  reporter_name,
  create_time
FROM pm_repair_order 
WHERE tenant_id = '000000' AND id >= 1001
ORDER BY create_time DESC;