19705e31bd
幂等 SQL 脚本，一键预置： - 张建国患者档案 + 3 条体征记录 - 2 份化验单（肌酐 88→102 趋势） - 25 个背景患者（仪表盘数据） - 随访模板 + 21 个随访任务 - 收缩压>=160 告警规则（场景5用） - 3 篇 CKD 科普文章
272 lines
12 KiB
PL/PgSQL
272 lines
12 KiB
PL/PgSQL
-- HMS V1 演示数据预置脚本
|
||
-- 用法: docker exec -i erp-postgres psql -U erp -d erp < scripts/demo-seed.sql
|
||
-- 幂等：使用 ON CONFLICT (id) DO NOTHING
|
||
-- 说明：预置张建国患者 + 化验单 + 背景患者 + 随访/告警 + 科普文章
|
||
|
||
-- 获取租户 ID（变量）
|
||
WITH t AS (SELECT id AS tid FROM tenants WHERE deleted_at IS NULL LIMIT 1)
|
||
SELECT 'tenant_id: ' || tid FROM t;
|
||
|
||
\set ON_ERROR_STOP on
|
||
|
||
BEGIN;
|
||
|
||
-- ============================================================
|
||
-- 1. 张建国患者档案
|
||
-- ============================================================
|
||
INSERT INTO patient (id, tenant_id, name, gender, birth_date, phone,
|
||
allergy_history, medical_history_summary,
|
||
emergency_contact_name, emergency_contact_phone,
|
||
status, verification_status, source,
|
||
created_at, updated_at, version)
|
||
SELECT
|
||
'a0000001-0001-7000-8000-000000000001'::uuid,
|
||
t.id,
|
||
'张建国', 'male', '1961-03-15', '13800138001',
|
||
'青霉素过敏', '慢性肾病3期，高血压病史5年',
|
||
'张小明', '13900139001',
|
||
'active', 'verified', 'manual',
|
||
NOW(), NOW(), 1
|
||
FROM tenants t WHERE t.deleted_at IS NULL
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- ============================================================
|
||
-- 2. 张建国历史体征数据（3 条，覆盖 3 个月）
|
||
-- ============================================================
|
||
-- 3 个月前：血压 132/82，心率 70
|
||
INSERT INTO vital_signs (id, tenant_id, patient_id, record_date,
|
||
systolic_bp_morning, diastolic_bp_morning, heart_rate,
|
||
source, created_at, updated_at, version)
|
||
SELECT
|
||
'b0000001-0001-7000-8000-000000000001'::uuid,
|
||
t.id,
|
||
'a0000001-0001-7000-8000-000000000001'::uuid,
|
||
CURRENT_DATE - INTERVAL '90 days',
|
||
132, 82, 70,
|
||
'manual', NOW(), NOW(), 1
|
||
FROM tenants t WHERE t.deleted_at IS NULL
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- 1 个月前：血压 138/86，心率 74，空腹血糖 5.6
|
||
INSERT INTO vital_signs (id, tenant_id, patient_id, record_date,
|
||
systolic_bp_morning, diastolic_bp_morning, heart_rate, blood_sugar,
|
||
blood_sugar_type, source, created_at, updated_at, version)
|
||
SELECT
|
||
'b0000001-0001-7000-8000-000000000002'::uuid,
|
||
t.id,
|
||
'a0000001-0001-7000-8000-000000000001'::uuid,
|
||
CURRENT_DATE - INTERVAL '30 days',
|
||
138, 86, 74, 5.6,
|
||
'fasting', 'manual', NOW(), NOW(), 1
|
||
FROM tenants t WHERE t.deleted_at IS NULL
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- 今天：血压 142/88，心率 72，空腹血糖 5.8
|
||
INSERT INTO vital_signs (id, tenant_id, patient_id, record_date,
|
||
systolic_bp_morning, diastolic_bp_morning, heart_rate, blood_sugar,
|
||
blood_sugar_type, source, created_at, updated_at, version)
|
||
SELECT
|
||
'b0000001-0001-7000-8000-000000000003'::uuid,
|
||
t.id,
|
||
'a0000001-0001-7000-8000-000000000001'::uuid,
|
||
CURRENT_DATE,
|
||
142, 88, 72, 5.8,
|
||
'fasting', 'manual', NOW(), NOW(), 1
|
||
FROM tenants t WHERE t.deleted_at IS NULL
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- ============================================================
|
||
-- 3. 化验报告（2 份，肌酐趋势 88→102）
|
||
-- ============================================================
|
||
-- 化验单 1：3 个月前，肌酐 88
|
||
INSERT INTO lab_report (id, tenant_id, patient_id, report_date, report_type,
|
||
source, items, status,
|
||
created_at, updated_at, version)
|
||
SELECT
|
||
'c0000001-0001-7000-8000-000000000001'::uuid,
|
||
t.id,
|
||
'a0000001-0001-7000-8000-000000000001'::uuid,
|
||
CURRENT_DATE - INTERVAL '90 days',
|
||
'kidney_function', 'manual_input',
|
||
'[{"name":"肌酐","value":"88","unit":"μmol/L","reference_low":"44","reference_high":"133","is_abnormal":false},
|
||
{"name":"尿素氮","value":"6.1","unit":"mmol/L","reference_low":"2.6","reference_high":"7.5","is_abnormal":false},
|
||
{"name":"eGFR","value":"75","unit":"mL/min/1.73m2","reference_low":"60","reference_high":"","is_abnormal":false}]'::jsonb,
|
||
'reviewed',
|
||
NOW(), NOW(), 1
|
||
FROM tenants t WHERE t.deleted_at IS NULL
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- 化验单 2：1 个月前，肌酐 102（偏高趋势）
|
||
INSERT INTO lab_report (id, tenant_id, patient_id, report_date, report_type,
|
||
source, items, status,
|
||
created_at, updated_at, version)
|
||
SELECT
|
||
'c0000001-0001-7000-8000-000000000002'::uuid,
|
||
t.id,
|
||
'a0000001-0001-7000-8000-000000000001'::uuid,
|
||
CURRENT_DATE - INTERVAL '30 days',
|
||
'kidney_function', 'manual_input',
|
||
'[{"name":"肌酐","value":"102","unit":"μmol/L","reference_low":"44","reference_high":"133","is_abnormal":false},
|
||
{"name":"尿素氮","value":"6.8","unit":"mmol/L","reference_low":"2.6","reference_high":"7.5","is_abnormal":false},
|
||
{"name":"eGFR","value":"72","unit":"mL/min/1.73m2","reference_low":"60","reference_high":"","is_abnormal":false}]'::jsonb,
|
||
'reviewed',
|
||
NOW(), NOW(), 1
|
||
FROM tenants t WHERE t.deleted_at IS NULL
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- ============================================================
|
||
-- 4. 背景患者（25 个，让仪表盘有数据）
|
||
-- ============================================================
|
||
INSERT INTO patient (id, tenant_id, name, gender, birth_date, phone,
|
||
status, verification_status, source,
|
||
created_at, updated_at, version)
|
||
SELECT
|
||
('d0000001-0001-7000-8000-0000000' || lpad(i::text, 6, '0'))::uuid,
|
||
t.id,
|
||
'测试患者' || i,
|
||
CASE WHEN i % 2 = 0 THEN 'male' ELSE 'female' END,
|
||
CURRENT_DATE - (30 + (i * 37) % 50) * INTERVAL '1 year',
|
||
'138' || lpad((13800000 + i)::text, 8, '0'),
|
||
'active', 'verified', 'manual',
|
||
NOW() - (i * INTERVAL '1 day'), NOW(), 1
|
||
FROM tenants t, generate_series(1, 25) AS i
|
||
WHERE t.deleted_at IS NULL
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- ============================================================
|
||
-- 5. 随访模板（慢性肾病定期随访）
|
||
-- ============================================================
|
||
INSERT INTO follow_up_template (id, tenant_id, name, description,
|
||
follow_up_type, applicable_scope, status,
|
||
created_at, updated_at, version)
|
||
SELECT
|
||
'e0000001-0001-7000-8000-000000000001'::uuid,
|
||
t.id,
|
||
'慢性肾病定期随访', 'CKD 3-4期患者标准随访计划',
|
||
'phone', 'chronic_kidney_disease', 'active',
|
||
NOW(), NOW(), 1
|
||
FROM tenants t WHERE t.deleted_at IS NULL
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- ============================================================
|
||
-- 6. 随访任务（张建国 + 若干背景患者）
|
||
-- ============================================================
|
||
-- 张建国的随访任务（pending 状态，演示场景 6 用）
|
||
INSERT INTO follow_up_task (id, tenant_id, patient_id,
|
||
follow_up_type, planned_date, status, content_template,
|
||
created_at, updated_at, version)
|
||
SELECT
|
||
'f0000001-0001-7000-8000-000000000001'::uuid,
|
||
t.id,
|
||
'a0000001-0001-7000-8000-000000000001'::uuid,
|
||
'phone', CURRENT_DATE + INTERVAL '7 days', 'pending',
|
||
'肾功能复查随访：询问近期症状、饮食依从性、用药情况',
|
||
NOW(), NOW(), 1
|
||
FROM tenants t WHERE t.deleted_at IS NULL
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- 背景患者的随访任务（混合状态）
|
||
INSERT INTO follow_up_task (id, tenant_id, patient_id,
|
||
follow_up_type, planned_date, status, content_template,
|
||
created_at, updated_at, version)
|
||
SELECT
|
||
('f0000002-0001-7000-8000-0000000' || lpad(i::text, 6, '0'))::uuid,
|
||
t.id,
|
||
('d0000001-0001-7000-8000-0000000' || lpad(i::text, 6, '0'))::uuid,
|
||
CASE WHEN i % 3 = 0 THEN 'phone' WHEN i % 3 = 1 THEN 'outpatient' ELSE 'wechat' END,
|
||
CURRENT_DATE - (i % 10) * INTERVAL '1 day',
|
||
CASE WHEN i <= 15 THEN 'completed' ELSE 'pending' END,
|
||
'定期健康随访',
|
||
NOW() - (i * INTERVAL '1 day'), NOW(), 1
|
||
FROM tenants t, generate_series(1, 20) AS i
|
||
WHERE t.deleted_at IS NULL
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- ============================================================
|
||
-- 7. 告警规则（收缩压 >=160，场景 5 用）
|
||
-- ============================================================
|
||
-- 注意：此规则用于演示场景 5，张大爷录入血压 168 时触发
|
||
-- seed 中的收缩压危急规则是 >=180，这里补充 >=160 的中等严重性规则
|
||
INSERT INTO alert_rules (id, tenant_id, name, description,
|
||
device_type, condition_type, condition_params, severity,
|
||
is_active, notify_roles, cooldown_minutes,
|
||
created_at, updated_at, version)
|
||
SELECT
|
||
'g0000001-0001-7000-8000-000000000001'::uuid,
|
||
t.id,
|
||
'收缩压偏高（演示用）', '收缩压 >= 160mmHg 触发中等严重性告警',
|
||
'blood_pressure', 'threshold',
|
||
'{"metric":"systolic_bp","operator":">=","threshold":160}'::jsonb,
|
||
'medium',
|
||
true,
|
||
'["nurse","health_manager","doctor"]'::jsonb,
|
||
30,
|
||
NOW(), NOW(), 1
|
||
FROM tenants t WHERE t.deleted_at IS NULL
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- ============================================================
|
||
-- 8. 科普文章（3 篇 CKD 相关）
|
||
-- ============================================================
|
||
INSERT INTO article (id, tenant_id, title, summary, content,
|
||
category, author, status, content_type,
|
||
view_count, sort_order,
|
||
published_at, created_at, updated_at, version)
|
||
SELECT
|
||
'h0000001-0001-7000-8000-000000000001'::uuid,
|
||
t.id,
|
||
'慢性肾病患者的饮食指南', '科学饮食延缓 CKD 进展',
|
||
'<h2>低盐低蛋白饮食原则</h2><p>CKD 3期患者每日蛋白质摄入控制在0.6-0.8g/kg，食盐不超过5g。</p><p>推荐食物：鸡蛋清、鱼肉、瘦肉（限量）、新鲜蔬菜。</p><p>避免：高钾食物（香蕉、土豆）、高磷食物（坚果、可乐）、加工食品。</p>',
|
||
'nutrition', 'HMS 健康管理团队', 'published', 'rich_text',
|
||
128, 1,
|
||
NOW() - INTERVAL '10 days', NOW() - INTERVAL '10 days', NOW(), 1
|
||
FROM tenants t WHERE t.deleted_at IS NULL
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
INSERT INTO article (id, tenant_id, title, summary, content,
|
||
category, author, status, content_type,
|
||
view_count, sort_order,
|
||
published_at, created_at, updated_at, version)
|
||
SELECT
|
||
'h0000001-0001-7000-8000-000000000002'::uuid,
|
||
t.id,
|
||
'CKD 患者运动建议', '安全运动改善生活质量',
|
||
'<h2>适度运动有益 CKD 管理</h2><p>推荐运动：散步（30分钟/天）、太极拳、瑜伽、游泳（低强度）。</p><p>运动频率：每周3-5次，每次20-40分钟。</p><p>注意：避免剧烈运动，运动前后监测血压，感觉不适立即停止。</p>',
|
||
'exercise', 'HMS 健康管理团队', 'published', 'rich_text',
|
||
85, 2,
|
||
NOW() - INTERVAL '7 days', NOW() - INTERVAL '7 days', NOW(), 1
|
||
FROM tenants t WHERE t.deleted_at IS NULL
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
INSERT INTO article (id, tenant_id, title, summary, content,
|
||
category, author, status, content_type,
|
||
view_count, sort_order,
|
||
published_at, created_at, updated_at, version)
|
||
SELECT
|
||
'h0000001-0001-7000-8000-000000000003'::uuid,
|
||
t.id,
|
||
'慢性肾病常用药物说明', '了解您正在服用的药物',
|
||
'<h2>常见 CKD 药物</h2><p><b>降压药（ACEI/ARB）</b>：保护肾功能，降低蛋白尿。需定期监测血钾。</p><p><b>碳酸氢钠</b>：纠正代谢性酸中毒。</p><p><b>铁剂/促红素</b>：改善肾性贫血。</p><p>重要提示：请勿自行停药或调整剂量，如有不适请及时联系医生。</p>',
|
||
'medication', 'HMS 健康管理团队', 'published', 'rich_text',
|
||
96, 3,
|
||
NOW() - INTERVAL '3 days', NOW() - INTERVAL '3 days', NOW(), 1
|
||
FROM tenants t WHERE t.deleted_at IS NULL
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
COMMIT;
|
||
|
||
-- ============================================================
|
||
-- 验证查询
|
||
-- ============================================================
|
||
SELECT 'patients' AS tbl, count(*) FROM patient WHERE deleted_at IS NULL
|
||
UNION ALL
|
||
SELECT 'vital_signs', count(*) FROM vital_signs WHERE deleted_at IS NULL
|
||
UNION ALL
|
||
SELECT 'lab_reports', count(*) FROM lab_report WHERE deleted_at IS NULL
|
||
UNION ALL
|
||
SELECT 'follow_up_tasks', count(*) FROM follow_up_task WHERE deleted_at IS NULL
|
||
UNION ALL
|
||
SELECT 'alert_rules', count(*) FROM alert_rules WHERE deleted_at IS NULL AND is_active = true
|
||
UNION ALL
|
||
SELECT 'articles', count(*) FROM article WHERE deleted_at IS NULL AND status = 'published';
|