-- 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';