164 lines
16 KiB
SQL
164 lines
16 KiB
SQL
-- ================================================
|
||
-- 养老管理系统模拟数据插入脚本 (PostgreSQL版本) - 修复版
|
||
-- ElderCare Management System Mock Data Insert - Fixed Version
|
||
-- 按角色分段插入模拟数据，包含冲突处理
|
||
-- ================================================
|
||
|
||
-- 重要说明：所有INSERT语句都包含冲突处理 ON CONFLICT (id) DO NOTHING
|
||
-- 建议：首次运行前可以启用下面的清理语句
|
||
|
||
-- 清理现有测试数据（可选，谨慎使用）
|
||
-- DELETE FROM public.ec_notification_logs;
|
||
-- DELETE FROM public.ec_health_alerts;
|
||
-- DELETE FROM public.ec_vital_signs;
|
||
-- DELETE FROM public.ec_care_records;
|
||
-- DELETE FROM public.ec_care_tasks;
|
||
-- DELETE FROM public.ec_medication_logs;
|
||
-- DELETE FROM public.ec_medications;
|
||
-- DELETE FROM public.ec_medical_records;
|
||
-- DELETE FROM public.ec_meal_records;
|
||
-- DELETE FROM public.ec_activity_participations;
|
||
-- DELETE FROM public.ec_activities;
|
||
-- DELETE FROM public.ec_care_plans;
|
||
-- DELETE FROM public.ec_elders;
|
||
-- DELETE FROM public.ak_user_roles;
|
||
-- DELETE FROM public.ak_users;
|
||
|
||
-- ================================================
|
||
-- 第一部分：插入区域和设施数据
|
||
-- ================================================
|
||
|
||
-- 插入区域数据
|
||
INSERT INTO public.ak_regions (id, name, level, parent_id, status) VALUES
|
||
('11000000-0000-0000-0000-000000000000', '北京市', 1, NULL, 'active'),
|
||
('11010000-0000-0000-0000-000000000000', '北京市市辖区', 2, '11000000-0000-0000-0000-000000000000', 'active'),
|
||
('11010100-0000-0000-0000-000000000000', '东城区', 3, '11010000-0000-0000-0000-000000000000', 'active'),
|
||
('32000000-0000-0000-0000-000000000000', '江苏省', 1, NULL, 'active'),
|
||
('32010000-0000-0000-0000-000000000000', '南京市', 2, '32000000-0000-0000-0000-000000000000', 'active'),
|
||
('32010100-0000-0000-0000-000000000000', '玄武区', 3, '32010000-0000-0000-0000-000000000000', 'active'),
|
||
('33000000-0000-0000-0000-000000000000', '浙江省', 1, NULL, 'active'),
|
||
('33010000-0000-0000-0000-000000000000', '杭州市', 2, '33000000-0000-0000-0000-000000000000', 'active'),
|
||
('33010100-0000-0000-0000-000000000000', '上城区', 3, '33010000-0000-0000-0000-000000000000', 'active')
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- ================================================
|
||
-- 第二部分：插入所有用户账号（去重版本）
|
||
-- ================================================
|
||
|
||
-- 插入所有用户账号（老人、家属、护工、护士、医生、管理员）
|
||
INSERT INTO public.ak_users (id, username, email, phone, real_name, avatar_url, status, created_at) VALUES
|
||
-- 老人用户 (16人)
|
||
('10000001-0001-4001-8001-000000000001', 'elder_zhang_wei', 'zhang.wei@eldercare.com', '13800000001', '张伟', 'https://example.com/avatars/zhang_wei.jpg', 'active', '2024-01-15 09:00:00+08'),
|
||
('10000002-0002-4002-8002-000000000002', 'elder_wang_li', 'wang.li@eldercare.com', '13800000002', '王丽', 'https://example.com/avatars/wang_li.jpg', 'active', '2024-01-16 10:30:00+08'),
|
||
('10000003-0003-4003-8003-000000000003', 'elder_li_ming', 'li.ming@eldercare.com', '13800000003', '李明', 'https://example.com/avatars/li_ming.jpg', 'active', '2024-01-17 14:20:00+08'),
|
||
('10000004-0004-4004-8004-000000000004', 'elder_zhao_mei', 'zhao.mei@eldercare.com', '13800000004', '赵美', 'https://example.com/avatars/zhao_mei.jpg', 'active', '2024-01-18 11:15:00+08'),
|
||
('10000005-0005-4005-8005-000000000005', 'elder_chen_gang', 'chen.gang@eldercare.com', '13800000005', '陈刚', 'https://example.com/avatars/chen_gang.jpg', 'active', '2024-01-19 08:45:00+08'),
|
||
('20000001-0001-4001-8001-000000000011', 'elder_liu_xia', 'liu.xia@eldercare.com', '13800000011', '刘霞', 'https://example.com/avatars/liu_xia.jpg', 'active', '2024-02-01 09:30:00+08'),
|
||
('20000002-0002-4002-8002-000000000012', 'elder_huang_jun', 'huang.jun@eldercare.com', '13800000012', '黄军', 'https://example.com/avatars/huang_jun.jpg', 'active', '2024-02-02 10:45:00+08'),
|
||
('20000003-0003-4003-8003-000000000013', 'elder_sun_hua', 'sun.hua@eldercare.com', '13800000013', '孙华', 'https://example.com/avatars/sun_hua.jpg', 'active', '2024-02-03 14:10:00+08'),
|
||
('20000004-0004-4004-8004-000000000014', 'elder_wu_qing', 'wu.qing@eldercare.com', '13800000014', '吴青', 'https://example.com/avatars/wu_qing.jpg', 'active', '2024-02-04 16:20:00+08'),
|
||
('20000005-0005-4005-8005-000000000015', 'elder_zhou_feng', 'zhou.feng@eldercare.com', '13800000015', '周峰', 'https://example.com/avatars/zhou_feng.jpg', 'active', '2024-02-05 11:30:00+08'),
|
||
('30000001-0001-4001-8001-000000000021', 'elder_wu_bin', 'wu.bin@eldercare.com', '13800000021', '吴斌', 'https://example.com/avatars/wu_bin.jpg', 'active', '2024-03-01 08:00:00+08'),
|
||
('30000002-0002-4002-8002-000000000022', 'elder_zheng_yan', 'zheng.yan@eldercare.com', '13800000022', '郑燕', 'https://example.com/avatars/zheng_yan.jpg', 'active', '2024-03-02 09:15:00+08'),
|
||
('30000003-0003-4003-8003-000000000023', 'elder_he_qiang', 'he.qiang@eldercare.com', '13800000023', '何强', 'https://example.com/avatars/he_qiang.jpg', 'active', '2024-03-03 10:30:00+08'),
|
||
('40000001-0001-4001-8001-000000000031', 'elder_ma_xia', 'ma.xia@eldercare.com', '13800000031', '马霞', 'https://example.com/avatars/ma_xia.jpg', 'active', '2024-04-01 13:20:00+08'),
|
||
('40000002-0002-4002-8002-000000000032', 'elder_guo_lei', 'guo.lei@eldercare.com', '13800000032', '郭磊', 'https://example.com/avatars/guo_lei.jpg', 'active', '2024-04-02 15:45:00+08'),
|
||
('50000001-0001-4001-8001-000000000041', 'elder_luo_jing', 'luo.jing@eldercare.com', '13800000041', '罗静', 'https://example.com/avatars/luo_jing.jpg', 'active', '2024-05-01 10:10:00+08'),
|
||
|
||
-- 家属用户 (10人)
|
||
('11111111-1111-4111-8111-111111111111', 'family_zhang_li', 'zhang.li@family.com', '13900000001', '张莉', 'https://example.com/avatars/zhang_li.jpg', 'active', '2024-01-20 09:00:00+08'),
|
||
('22222222-2222-4222-8222-222222222222', 'family_wang_ming', 'wang.ming@family.com', '13900000002', '王明', 'https://example.com/avatars/wang_ming.jpg', 'active', '2024-01-21 10:30:00+08'),
|
||
('33333333-3333-4333-8333-333333333333', 'family_li_na', 'li.na@family.com', '13900000003', '李娜', 'https://example.com/avatars/li_na.jpg', 'active', '2024-01-22 11:45:00+08'),
|
||
('44444444-4444-4444-8444-444444444444', 'family_zhao_lei', 'zhao.lei@family.com', '13900000004', '赵雷', 'https://example.com/avatars/zhao_lei.jpg', 'active', '2024-01-23 08:20:00+08'),
|
||
('55555555-5555-4555-8555-555555555555', 'family_chen_mei', 'chen.mei@family.com', '13900000005', '陈梅', 'https://example.com/avatars/chen_mei.jpg', 'active', '2024-01-24 14:10:00+08'),
|
||
('66666666-6666-4666-8666-666666666666', 'family_liu_qiang', 'liu.qiang@family.com', '13900000006', '刘强', 'https://example.com/avatars/liu_qiang.jpg', 'active', '2024-02-10 09:30:00+08'),
|
||
('77777777-7777-4777-8777-777777777777', 'family_huang_yan', 'huang.yan@family.com', '13900000007', '黄燕', 'https://example.com/avatars/huang_yan.jpg', 'active', '2024-02-11 10:45:00+08'),
|
||
('88888888-8888-4888-8888-888888888888', 'family_sun_bin', 'sun.bin@family.com', '13900000008', '孙斌', 'https://example.com/avatars/sun_bin.jpg', 'active', '2024-02-12 13:20:00+08'),
|
||
('99999999-9999-4999-8999-999999999999', 'family_wu_hong', 'wu.hong@family.com', '13900000009', '吴红', 'https://example.com/avatars/wu_hong.jpg', 'active', '2024-03-15 15:30:00+08'),
|
||
('00000000-0000-4000-8000-000000000000', 'family_zhou_jun', 'zhou.jun@family.com', '13900000010', '周军', 'https://example.com/avatars/zhou_jun.jpg', 'active', '2024-03-16 16:45:00+08'),
|
||
|
||
-- 护工用户 (14人)
|
||
('61000001-0001-4001-8001-000000000001', 'caregiver_wang_fang', 'wang.fang@caregiver.com', '13700000001', '王芳', 'https://example.com/avatars/wang_fang.jpg', 'active', '2024-01-10 08:00:00+08'),
|
||
('61000002-0002-4002-8002-000000000002', 'caregiver_li_jie', 'li.jie@caregiver.com', '13700000002', '李洁', 'https://example.com/avatars/li_jie.jpg', 'active', '2024-01-11 08:30:00+08'),
|
||
('61000003-0003-4003-8003-000000000003', 'caregiver_zhao_ping', 'zhao.ping@caregiver.com', '13700000003', '赵萍', 'https://example.com/avatars/zhao_ping.jpg', 'active', '2024-01-12 09:00:00+08'),
|
||
('61000004-0004-4004-8004-000000000004', 'caregiver_chen_lu', 'chen.lu@caregiver.com', '13700000004', '陈露', 'https://example.com/avatars/chen_lu.jpg', 'active', '2024-01-13 09:30:00+08'),
|
||
('61000005-0005-4005-8005-000000000005', 'caregiver_liu_mei', 'liu.mei@caregiver.com', '13700000005', '刘美', 'https://example.com/avatars/liu_mei.jpg', 'active', '2024-01-14 10:00:00+08'),
|
||
('62000001-0001-4001-8001-000000000011', 'caregiver_huang_xin', 'huang.xin@caregiver.com', '13700000011', '黄欣', 'https://example.com/avatars/huang_xin.jpg', 'active', '2024-02-01 08:15:00+08'),
|
||
('62000002-0002-4002-8002-000000000012', 'caregiver_sun_li', 'sun.li@caregiver.com', '13700000012', '孙丽', 'https://example.com/avatars/sun_li.jpg', 'active', '2024-02-02 08:45:00+08'),
|
||
('62000003-0003-4003-8003-000000000013', 'caregiver_wu_yu', 'wu.yu@caregiver.com', '13700000013', '吴雨', 'https://example.com/avatars/wu_yu.jpg', 'active', '2024-02-03 09:15:00+08'),
|
||
('62000004-0004-4004-8004-000000000014', 'caregiver_zhou_xia', 'zhou.xia@caregiver.com', '13700000014', '周霞', 'https://example.com/avatars/zhou_xia.jpg', 'active', '2024-02-04 09:45:00+08'),
|
||
('63000001-0001-4001-8001-000000000021', 'caregiver_zheng_qin', 'zheng.qin@caregiver.com', '13700000021', '郑琴', 'https://example.com/avatars/zheng_qin.jpg', 'active', '2024-03-01 08:00:00+08'),
|
||
('63000002-0002-4002-8002-000000000022', 'caregiver_he_min', 'he.min@caregiver.com', '13700000022', '何敏', 'https://example.com/avatars/he_min.jpg', 'active', '2024-03-02 08:30:00+08'),
|
||
('64000001-0001-4001-8001-000000000031', 'caregiver_ma_juan', 'ma.juan@caregiver.com', '13700000031', '马娟', 'https://example.com/avatars/ma_juan.jpg', 'active', '2024-04-01 08:00:00+08'),
|
||
('64000002-0002-4002-8002-000000000032', 'caregiver_guo_ting', 'guo.ting@caregiver.com', '13700000032', '郭婷', 'https://example.com/avatars/guo_ting.jpg', 'active', '2024-04-02 08:30:00+08'),
|
||
('65000001-0001-4001-8001-000000000041', 'caregiver_luo_lan', 'luo.lan@caregiver.com', '13700000041', '罗兰', 'https://example.com/avatars/luo_lan.jpg', 'active', '2024-05-01 08:00:00+08'),
|
||
|
||
-- 护士用户 (10人)
|
||
('1000001a-0001-4001-8001-000000000001', 'nurse_zhang_hua', 'zhang.hua@nurse.com', '13600000001', '张华', 'https://example.com/avatars/zhang_hua.jpg', 'active', '2024-01-08 07:30:00+08'),
|
||
('1000002a-0002-4002-8002-000000000002', 'nurse_wang_jun', 'wang.jun@nurse.com', '13600000002', '王军', 'https://example.com/avatars/wang_jun.jpg', 'active', '2024-01-09 08:00:00+08'),
|
||
('2000001a-0001-4001-8001-000000000011', 'nurse_li_yan', 'li.yan@nurse.com', '13600000011', '李燕', 'https://example.com/avatars/li_yan.jpg', 'active', '2024-02-01 07:45:00+08'),
|
||
('2000002a-0002-4002-8002-000000000012', 'nurse_zhao_rui', 'zhao.rui@nurse.com', '13600000012', '赵瑞', 'https://example.com/avatars/zhao_rui.jpg', 'active', '2024-02-02 08:15:00+08'),
|
||
('3000001a-0001-4001-8001-000000000021', 'nurse_chen_bo', 'chen.bo@nurse.com', '13600000021', '陈波', 'https://example.com/avatars/chen_bo.jpg', 'active', '2024-03-01 07:30:00+08'),
|
||
('3000002a-0002-4002-8002-000000000022', 'nurse_liu_ning', 'liu.ning@nurse.com', '13600000022', '刘宁', 'https://example.com/avatars/liu_ning.jpg', 'active', '2024-03-02 08:00:00+08'),
|
||
('4000001a-0001-4001-8001-000000000031', 'nurse_huang_tao', 'huang.tao@nurse.com', '13600000031', '黄涛', 'https://example.com/avatars/huang_tao.jpg', 'active', '2024-04-01 07:45:00+08'),
|
||
('4000002a-0002-4002-8002-000000000032', 'nurse_sun_gang', 'sun.gang@nurse.com', '13600000032', '孙刚', 'https://example.com/avatars/sun_gang.jpg', 'active', '2024-04-02 08:15:00+08'),
|
||
('5000001a-0001-4001-8001-000000000041', 'nurse_wu_lei', 'wu.lei@nurse.com', '13600000041', '吴雷', 'https://example.com/avatars/wu_lei.jpg', 'active', '2024-05-01 07:30:00+08'),
|
||
('5000002a-0002-4002-8002-000000000042', 'nurse_zhou_min', 'zhou.min@nurse.com', '13600000042', '周敏', 'https://example.com/avatars/zhou_min.jpg', 'active', '2024-05-02 08:00:00+08'),
|
||
|
||
-- 医生用户 (7人)
|
||
('71111111-1111-4111-8111-111111111111', 'doctor_zhang_wei', 'zhang.wei@doctor.com', '13500000001', '张伟医生', 'https://example.com/avatars/doctor_zhang_wei.jpg', 'active', '2024-01-05 07:00:00+08'),
|
||
('71111112-1111-4111-8111-111111111112', 'doctor_wang_li', 'wang.li@doctor.com', '13500000002', '王丽医生', 'https://example.com/avatars/doctor_wang_li.jpg', 'active', '2024-01-06 07:30:00+08'),
|
||
('71111113-1111-4111-8111-111111111113', 'doctor_li_ming', 'li.ming@doctor.com', '13500000003', '李明医生', 'https://example.com/avatars/doctor_li_ming.jpg', 'active', '2024-01-07 08:00:00+08'),
|
||
('71111114-1111-4111-8111-111111111114', 'doctor_zhao_mei', 'zhao.mei@doctor.com', '13500000004', '赵美医生', 'https://example.com/avatars/doctor_zhao_mei.jpg', 'active', '2024-02-01 07:15:00+08'),
|
||
('71111115-1111-4111-8111-111111111115', 'doctor_chen_gang', 'chen.gang@doctor.com', '13500000005', '陈刚医生', 'https://example.com/avatars/doctor_chen_gang.jpg', 'active', '2024-03-01 07:45:00+08'),
|
||
('71111116-1111-4111-8111-111111111116', 'doctor_liu_xia', 'liu.xia@doctor.com', '13500000006', '刘霞医生', 'https://example.com/avatars/doctor_liu_xia.jpg', 'active', '2024-04-01 08:15:00+08'),
|
||
('71111117-1111-4111-8111-111111111117', 'doctor_huang_jun', 'huang.jun@doctor.com', '13500000007', '黄军医生', 'https://example.com/avatars/doctor_huang_jun.jpg', 'active', '2024-05-01 07:30:00+08'),
|
||
|
||
-- 管理员用户 (7人)
|
||
('81111111-1111-4111-8111-111111111111', 'admin_system', 'admin@eldercare.com', '13400000001', '系统管理员', 'https://example.com/avatars/admin_system.jpg', 'active', '2024-01-01 00:00:00+08'),
|
||
('82222222-2222-4222-8222-222222222222', 'admin_facility_bj', 'admin.bj@eldercare.com', '13400000002', '北京院长', 'https://example.com/avatars/admin_bj.jpg', 'active', '2024-01-02 08:00:00+08'),
|
||
('83333333-3333-4333-8333-333333333333', 'admin_facility_sh', 'admin.sh@eldercare.com', '13400000003', '上海院长', 'https://example.com/avatars/admin_sh.jpg', 'active', '2024-01-03 08:00:00+08'),
|
||
('84444444-4444-4444-8444-444444444444', 'admin_facility_nj', 'admin.nj@eldercare.com', '13400000004', '南京院长', 'https://example.com/avatars/admin_nj.jpg', 'active', '2024-01-04 08:00:00+08'),
|
||
('85555555-5555-4555-8555-555555555555', 'admin_facility_hz', 'admin.hz@eldercare.com', '13400000005', '杭州院长', 'https://example.com/avatars/admin_hz.jpg', 'active', '2024-01-05 08:00:00+08'),
|
||
('86666666-6666-4666-8666-666666666666', 'admin_nurse_head', 'nurse.head@eldercare.com', '13400000006', '护理主管', 'https://example.com/avatars/nurse_head.jpg', 'active', '2024-01-06 08:00:00+08'),
|
||
('87777777-7777-4777-8777-777777777777', 'admin_care_head', 'care.head@eldercare.com', '13400000007', '护理部主任', 'https://example.com/avatars/care_head.jpg', 'active', '2024-01-07 08:00:00+08')
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- ================================================
|
||
-- 第三部分：用户角色分配
|
||
-- ================================================
|
||
|
||
-- 为用户分配角色（使用NOT EXISTS避免重复插入）
|
||
INSERT INTO public.ak_user_roles (user_id, role_id)
|
||
SELECT u.id, r.id
|
||
FROM public.ak_users u
|
||
CROSS JOIN public.ak_roles r
|
||
WHERE ((u.username LIKE 'elder_%' AND r.name = 'elder')
|
||
OR (u.username LIKE 'family_%' AND r.name = 'family')
|
||
OR (u.username LIKE 'caregiver_%' AND r.name = 'caregiver')
|
||
OR (u.username LIKE 'nurse_%' AND r.name = 'nurse')
|
||
OR (u.username LIKE 'doctor_%' AND r.name = 'doctor')
|
||
OR (u.username LIKE 'admin_%' AND r.name = 'admin'))
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM public.ak_user_roles ur
|
||
WHERE ur.user_id = u.id AND ur.role_id = r.id
|
||
);
|
||
|
||
-- ================================================
|
||
-- 脚本执行完成
|
||
-- ================================================
|
||
|
||
-- 验证插入结果
|
||
SELECT
|
||
'用户统计' as type,
|
||
COUNT(*) as total_count,
|
||
COUNT(CASE WHEN username LIKE 'elder_%' THEN 1 END) as elder_count,
|
||
COUNT(CASE WHEN username LIKE 'family_%' THEN 1 END) as family_count,
|
||
COUNT(CASE WHEN username LIKE 'caregiver_%' THEN 1 END) as caregiver_count,
|
||
COUNT(CASE WHEN username LIKE 'nurse_%' THEN 1 END) as nurse_count,
|
||
COUNT(CASE WHEN username LIKE 'doctor_%' THEN 1 END) as doctor_count,
|
||
COUNT(CASE WHEN username LIKE 'admin_%' THEN 1 END) as admin_count
|
||
FROM public.ak_users;
|
||
|
||
SELECT '插入完成' as message, NOW() as completed_at;
|