akmon/generate_sensor_demo_data.sql

-- 根据现有ss_sensor_data_schema.sql创建示例数据
-- 为学生用户生成传感器数据以供演示

-- 首先确保表结构存在
-- 如果还没有运行过ss_sensor_data_schema.sql，请先运行

-- 生成示例数据
DO $$
DECLARE
    demo_user_id UUID;
    demo_device_id UUID;
    measurement_id UUID;
    current_time TIMESTAMPTZ;
    i INTEGER;
BEGIN
    -- 查找第一个学生用户ID
    SELECT id INTO demo_user_id 
    FROM public.ak_users 
    WHERE role = 'student' 
    LIMIT 1;
    
    -- 创建或查找一个示例设备
    SELECT id INTO demo_device_id 
    FROM public.ak_devices 
    LIMIT 1;
    
    -- 如果没有设备，创建一个示例设备
    IF demo_device_id IS NULL THEN
        INSERT INTO public.ak_devices (id, name, type, model, created_at)
        VALUES (
            uuid_generate_v4(),
            '健康监测设备',
            'health_monitor',
            'HealthTracker Pro',
            NOW()
        )
        RETURNING id INTO demo_device_id;
        
        RAISE NOTICE '创建了示例设备: %', demo_device_id;
    END IF;
    
    -- 如果找到学生用户，插入示例数据
    IF demo_user_id IS NOT NULL AND demo_device_id IS NOT NULL THEN
        current_time := NOW();
        
        -- 生成最近7天的体温数据（每4小时一条）
        FOR i IN 0..41 LOOP
            -- 插入到主表
            INSERT INTO public.ss_sensor_measurements (
                id, device_id, user_id, measurement_type, measured_at, unit, raw_data
            ) VALUES (
                uuid_generate_v4(),
                demo_device_id,
                demo_user_id,
                'temp',
                current_time - (interval '4 hours' * i),
                '°C',
                jsonb_build_object('temperature_c', 36.0 + (random() * 1.5))
            )
            RETURNING id INTO measurement_id;
            
            -- 插入到专项表
            INSERT INTO public.ss_temperature (measurement_id, temperature_c)
            SELECT measurement_id, (raw_data->>'temperature_c')::FLOAT
            FROM public.ss_sensor_measurements
            WHERE id = measurement_id;
        END LOOP;
        
        -- 生成最近7天的心率数据（每2小时一条）
        FOR i IN 0..83 LOOP
            -- 插入到主表
            INSERT INTO public.ss_sensor_measurements (
                id, device_id, user_id, measurement_type, measured_at, unit, raw_data
            ) VALUES (
                uuid_generate_v4(),
                demo_device_id,
                demo_user_id,
                'heart_rate',
                current_time - (interval '2 hours' * i),
                'bpm',
                jsonb_build_object('heart_rate_bpm', 60 + (random() * 40)::INT)
            )
            RETURNING id INTO measurement_id;
            
            -- 插入到专项表
            INSERT INTO public.ss_heart_rate (measurement_id, heart_rate_bpm)
            SELECT measurement_id, (raw_data->>'heart_rate_bpm')::INT
            FROM public.ss_sensor_measurements
            WHERE id = measurement_id;
        END LOOP;
        
        -- 生成最近7天的血氧数据（每6小时一条）
        FOR i IN 0..27 LOOP
            -- 插入到主表
            INSERT INTO public.ss_sensor_measurements (
                id, device_id, user_id, measurement_type, measured_at, unit, raw_data
            ) VALUES (
                uuid_generate_v4(),
                demo_device_id,
                demo_user_id,
                'spo2',
                current_time - (interval '6 hours' * i),
                '%',
                jsonb_build_object('spo2_percent', 95 + (random() * 5)::INT)
            )
            RETURNING id INTO measurement_id;
            
            -- 插入到专项表
            INSERT INTO public.ss_spo2 (measurement_id, spo2_percent)
            SELECT measurement_id, (raw_data->>'spo2_percent')::INT
            FROM public.ss_sensor_measurements
            WHERE id = measurement_id;
        END LOOP;
        
        -- 生成最近7天的步数数据（每天一条）
        FOR i IN 0..6 LOOP
            -- 插入到主表
            INSERT INTO public.ss_sensor_measurements (
                id, device_id, user_id, measurement_type, measured_at, unit, raw_data
            ) VALUES (
                uuid_generate_v4(),
                demo_device_id,
                demo_user_id,
                'steps',
                current_time - (interval '1 day' * i),
                'count',
                jsonb_build_object('step_count', 5000 + (random() * 10000)::BIGINT)
            )
            RETURNING id INTO measurement_id;
            
            -- 插入到专项表
            INSERT INTO public.ss_steps (measurement_id, step_count)
            SELECT measurement_id, (raw_data->>'step_count')::BIGINT
            FROM public.ss_sensor_measurements
            WHERE id = measurement_id;
        END LOOP;
        
        RAISE NOTICE '成功为用户 % 生成传感器数据', demo_user_id;
        RAISE NOTICE '使用设备ID: %', demo_device_id;
        RAISE NOTICE '生成数据: 体温42条, 心率84条, 血氧28条, 步数7条';
    ELSE
        RAISE NOTICE '未找到学生用户或设备，跳过示例数据生成';
        RAISE NOTICE '学生用户ID: %, 设备ID: %', demo_user_id, demo_device_id;
    END IF;
END $$;

-- 创建一个用于获取最新传感器数据的视图
CREATE OR REPLACE VIEW public.v_latest_student_health AS
WITH latest_temp AS (
    SELECT DISTINCT ON (sm.user_id) 
        sm.user_id,
        st.temperature_c,
        sm.measured_at as temp_time
    FROM public.ss_sensor_measurements sm
    JOIN public.ss_temperature st ON st.measurement_id = sm.id
    WHERE sm.measurement_type = 'temp'
    ORDER BY sm.user_id, sm.measured_at DESC
),
latest_heart AS (
    SELECT DISTINCT ON (sm.user_id) 
        sm.user_id,
        sh.heart_rate_bpm,
        sm.measured_at as heart_time
    FROM public.ss_sensor_measurements sm
    JOIN public.ss_heart_rate sh ON sh.measurement_id = sm.id
    WHERE sm.measurement_type = 'heart_rate'
    ORDER BY sm.user_id, sm.measured_at DESC
),
latest_spo2 AS (
    SELECT DISTINCT ON (sm.user_id) 
        sm.user_id,
        ss.spo2_percent,
        sm.measured_at as spo2_time
    FROM public.ss_sensor_measurements sm
    JOIN public.ss_spo2 ss ON ss.measurement_id = sm.id
    WHERE sm.measurement_type = 'spo2'
    ORDER BY sm.user_id, sm.measured_at DESC
),
latest_steps AS (
    SELECT DISTINCT ON (sm.user_id) 
        sm.user_id,
        sst.step_count,
        sm.measured_at as steps_time
    FROM public.ss_sensor_measurements sm
    JOIN public.ss_steps sst ON sst.measurement_id = sm.id
    WHERE sm.measurement_type = 'steps'
    ORDER BY sm.user_id, sm.measured_at DESC
)
SELECT 
    u.id as user_id,
    u.name,
    u.student_id,
    u.avatar,
    lt.temperature_c,
    lt.temp_time,
    lh.heart_rate_bpm,
    lh.heart_time,
    ls.spo2_percent,
    ls.spo2_time,
    lst.step_count,
    lst.steps_time
FROM public.ak_users u
LEFT JOIN latest_temp lt ON lt.user_id = u.id
LEFT JOIN latest_heart lh ON lh.user_id = u.id
LEFT JOIN latest_spo2 ls ON ls.user_id = u.id
LEFT JOIN latest_steps lst ON lst.user_id = u.id
WHERE u.role = 'student';

-- 授予权限
GRANT SELECT ON public.v_latest_student_health TO authenticated;

COMMENT ON VIEW public.v_latest_student_health IS '获取所有学生的最新健康数据，包括体温、心率、血氧、步数';

-- 显示统计信息
SELECT 
    measurement_type,
    COUNT(*) as record_count,
    MIN(measured_at) as earliest_record,
    MAX(measured_at) as latest_record
FROM public.ss_sensor_measurements 
GROUP BY measurement_type
ORDER BY measurement_type;