-- 为学生健康监测系统生成测试数据
-- 这个脚本将创建测试的教师、班级、学生和健康数据

-- 1. 创建测试班级
INSERT INTO ak_classes (id, name, description, created_at) VALUES
('class_001', '计算机科学1班', '计算机科学专业一年级1班', NOW()),
('class_002', '软件工程1班', '软件工程专业一年级1班', NOW()),
('class_003', '信息管理1班', '信息管理专业一年级1班', NOW())
ON CONFLICT (id) DO NOTHING;

-- 2. 创建测试教师
INSERT INTO ak_users (id, name, email, role, avatar, class_id, student_id, created_at) VALUES
('teacher_001', '张老师', 'zhang@school.com', 'teacher', 'https://via.placeholder.com/100x100?text=张', NULL, NULL, NOW())
ON CONFLICT (id) DO NOTHING;

-- 3. 设置教师负责的班级
INSERT INTO ak_teacher_roles (user_id, class_id, created_at) VALUES
('teacher_001', 'class_001', NOW()),
('teacher_001', 'class_002', NOW())
ON CONFLICT (user_id, class_id) DO NOTHING;

-- 4. 创建测试学生
INSERT INTO ak_users (id, name, email, role, avatar, class_id, student_id, created_at) VALUES
-- 计算机科学1班学生
('student_001', '王小明', 'wang@student.com', 'student', 'https://via.placeholder.com/100x100?text=王', 'class_001', '2024001', NOW()),
('student_002', '李小红', 'li@student.com', 'student', NULL, 'class_001', '2024002', NOW()),
('student_003', '张小华', 'zhang@student.com', 'student', 'https://via.placeholder.com/100x100?text=张', 'class_001', '2024003', NOW()),
('student_004', '刘小刚', 'liu@student.com', 'student', NULL, 'class_001', '2024004', NOW()),

-- 软件工程1班学生
('student_005', '陈小美', 'chen@student.com', 'student', 'https://via.placeholder.com/100x100?text=陈', 'class_002', '2024005', NOW()),
('student_006', '赵小强', 'zhao@student.com', 'student', NULL, 'class_002', '2024006', NOW()),
('student_007', '孙小丽', 'sun@student.com', 'student', 'https://via.placeholder.com/100x100?text=孙', 'class_002', '2024007', NOW()),

-- 信息管理1班学生（不属于张老师管理）
('student_008', '吴小亮', 'wu@student.com', 'student', NULL, 'class_003', '2024008', NOW()),
('student_009', '马小静', 'ma@student.com', 'student', 'https://via.placeholder.com/100x100?text=马', 'class_003', '2024009', NOW())
ON CONFLICT (id) DO NOTHING;

-- 5. 生成传感器主记录
INSERT INTO ss_sensor_measurements (id, user_id, device_id, measurement_time, created_at) VALUES
-- 最近的数据（1小时内）
('measurement_001', 'student_001', 'device_001', NOW() - INTERVAL '30 minutes', NOW()),
('measurement_002', 'student_002', 'device_002', NOW() - INTERVAL '45 minutes', NOW()),
('measurement_003', 'student_003', 'device_003', NOW() - INTERVAL '20 minutes', NOW()),
('measurement_004', 'student_004', 'device_004', NOW() - INTERVAL '15 minutes', NOW()),
('measurement_005', 'student_005', 'device_005', NOW() - INTERVAL '25 minutes', NOW()),
('measurement_006', 'student_006', 'device_006', NOW() - INTERVAL '35 minutes', NOW()),
('measurement_007', 'student_007', 'device_007', NOW() - INTERVAL '40 minutes', NOW()),

-- 较早的数据（1天前）
('measurement_101', 'student_001', 'device_001', NOW() - INTERVAL '1 day', NOW()),
('measurement_102', 'student_002', 'device_002', NOW() - INTERVAL '1 day', NOW()),
('measurement_103', 'student_003', 'device_003', NOW() - INTERVAL '1 day', NOW())
ON CONFLICT (id) DO NOTHING;

-- 6. 生成体温数据
INSERT INTO ss_temperature (measurement_id, temperature_c, created_at) VALUES
-- 正常体温
('measurement_001', 36.5, NOW()),
('measurement_003', 36.8, NOW()),
('measurement_005', 36.3, NOW()),
('measurement_007', 36.6, NOW()),

-- 异常体温（发烧）
('measurement_002', 38.2, NOW()),  -- 李小红发烧
('measurement_004', 37.8, NOW()),  -- 刘小刚体温偏高
('measurement_006', 35.5, NOW()),  -- 赵小强体温偏低

-- 历史数据
('measurement_101', 36.4, NOW()),
('measurement_102', 36.7, NOW()),
('measurement_103', 36.5, NOW())
ON CONFLICT (measurement_id) DO NOTHING;

-- 7. 生成心率数据
INSERT INTO ss_heart_rate (measurement_id, heart_rate_bpm, created_at) VALUES
-- 正常心率
('measurement_001', 75, NOW()),
('measurement_003', 82, NOW()),
('measurement_005', 68, NOW()),

-- 异常心率
('measurement_002', 110, NOW()),  -- 李小红心率过快（配合发烧）
('measurement_004', 95, NOW()),   -- 刘小刚心率偏快
('measurement_006', 45, NOW()),   -- 赵小强心率过慢
('measurement_007', 105, NOW()),  -- 孙小丽心率偏快

-- 历史数据
('measurement_101', 78, NOW()),
('measurement_102', 72, NOW()),
('measurement_103', 80, NOW())
ON CONFLICT (measurement_id) DO NOTHING;

-- 8. 生成血氧数据
INSERT INTO ss_spo2 (measurement_id, spo2_percent, created_at) VALUES
-- 正常血氧
('measurement_001', 98, NOW()),
('measurement_003', 97, NOW()),
('measurement_005', 99, NOW()),
('measurement_007', 96, NOW()),

-- 异常血氧
('measurement_002', 93, NOW()),   -- 李小红血氧偏低（配合发烧）
('measurement_004', 94, NOW()),   -- 刘小刚血氧偏低
('measurement_006', 92, NOW()),   -- 赵小强血氧偏低

-- 历史数据
('measurement_101', 98, NOW()),
('measurement_102', 97, NOW()),
('measurement_103', 96, NOW())
ON CONFLICT (measurement_id) DO NOTHING;

-- 9. 生成步数数据
INSERT INTO ss_steps (measurement_id, step_count, created_at) VALUES
-- 正常步数
('measurement_001', 8500, NOW()),
('measurement_002', 6200, NOW()),
('measurement_003', 9200, NOW()),
('measurement_004', 7800, NOW()),
('measurement_005', 10200, NOW()),
('measurement_006', 5500, NOW()),
('measurement_007', 8800, NOW()),

-- 历史数据
('measurement_101', 7200, NOW()),
('measurement_102', 8900, NOW()),
('measurement_103', 6800, NOW())
ON CONFLICT (measurement_id) DO NOTHING;

-- 10. 创建或更新健康数据视图
CREATE OR REPLACE VIEW v_latest_student_health AS
WITH latest_measurements AS (
    SELECT 
        user_id,
        MAX(measurement_time) as latest_time
    FROM ss_sensor_measurements
    WHERE user_id IN (
        SELECT id FROM ak_users WHERE role = 'student'
    )
    GROUP BY user_id
),
student_health_data AS (
    SELECT 
        u.id as user_id,
        u.name,
        u.student_id,
        u.avatar,
        u.class_id,
        sm.measurement_time,
        sm.id as measurement_id
    FROM ak_users u
    LEFT JOIN latest_measurements lm ON u.id = lm.user_id
    LEFT JOIN ss_sensor_measurements sm ON u.id = sm.user_id AND sm.measurement_time = lm.latest_time
    WHERE u.role = 'student'
)
SELECT 
    shd.user_id,
    shd.name,
    shd.student_id,
    shd.avatar,
    shd.class_id,
    shd.measurement_time as latest_measurement_time,
    -- 体温数据
    st.temperature_c,
    st.created_at as temp_time,
    -- 心率数据
    shr.heart_rate_bpm,
    shr.created_at as heart_time,
    -- 血氧数据
    ssp.spo2_percent,
    ssp.created_at as spo2_time,
    -- 步数数据
    sst.step_count,
    sst.created_at as steps_time
FROM student_health_data shd
LEFT JOIN ss_temperature st ON shd.measurement_id = st.measurement_id
LEFT JOIN ss_heart_rate shr ON shd.measurement_id = shr.measurement_id
LEFT JOIN ss_spo2 ssp ON shd.measurement_id = ssp.measurement_id
LEFT JOIN ss_steps sst ON shd.measurement_id = sst.measurement_id;

-- 测试查询：验证数据是否正确生成
-- 这将显示张老师负责的学生健康数据
SELECT 
    vlsh.*,
    -- 异常判断
    CASE 
        WHEN temperature_c < 36.0 OR temperature_c > 37.5 THEN 'TEMP_ABNORMAL'
        ELSE 'TEMP_NORMAL'
    END as temp_status,
    CASE 
        WHEN heart_rate_bpm < 60 OR heart_rate_bpm > 100 THEN 'HR_ABNORMAL'
        ELSE 'HR_NORMAL'
    END as hr_status,
    CASE 
        WHEN spo2_percent < 95 THEN 'SPO2_ABNORMAL'
        ELSE 'SPO2_NORMAL'
    END as spo2_status
FROM v_latest_student_health vlsh
WHERE user_id IN (
    SELECT u.id 
    FROM ak_users u
    WHERE u.role = 'student' 
    AND u.class_id IN (
        SELECT class_id 
        FROM ak_teacher_roles 
        WHERE user_id = 'teacher_001'
    )
)
ORDER BY 
    -- 异常优先排序
    CASE 
        WHEN (temperature_c < 36.0 OR temperature_c > 37.5 OR 
              heart_rate_bpm < 60 OR heart_rate_bpm > 100 OR 
              spo2_percent < 95) 
        THEN 0 
        ELSE 1 
    END,
    name;

-- 显示创建的数据统计
SELECT 
    '教师数量' as type, COUNT(*) as count 
FROM ak_users WHERE role = 'teacher'
UNION ALL
SELECT 
    '学生数量' as type, COUNT(*) as count 
FROM ak_users WHERE role = 'student'
UNION ALL
SELECT 
    '张老师负责的学生' as type, COUNT(*) as count 
FROM ak_users u 
WHERE u.role = 'student' 
AND u.class_id IN (
    SELECT class_id FROM ak_teacher_roles WHERE user_id = 'teacher_001'
)
UNION ALL
SELECT 
    '有健康数据的学生' as type, COUNT(DISTINCT user_id) as count 
FROM v_latest_student_health 
WHERE temperature_c IS NOT NULL OR heart_rate_bpm IS NOT NULL OR spo2_percent IS NOT NULL
UNION ALL
SELECT 
    '异常健康指标的学生' as type, COUNT(*) as count 
FROM v_latest_student_health 
WHERE temperature_c < 36.0 OR temperature_c > 37.5 OR 
      heart_rate_bpm < 60 OR heart_rate_bpm > 100 OR 
      spo2_percent < 95;