-- 创建学生最新健康数据视图
-- 如果视图已存在则先删除
DROP VIEW IF EXISTS public.v_latest_student_health;

-- 创建视图：获取每个学生的最新健康数据
CREATE OR REPLACE VIEW public.v_latest_student_health AS
WITH latest_measurements AS (
    -- 获取每个用户每种传感器类型的最新记录
    SELECT 
        user_id,
        sensor_type,
        timestamp_utc,
        value_numeric,
        ROW_NUMBER() OVER (
            PARTITION BY user_id, sensor_type 
            ORDER BY timestamp_utc DESC
        ) as rn
    FROM public.ss_sensor_measurements
    WHERE value_numeric IS NOT NULL
),
pivoted_data AS (
    -- 将不同传感器类型的数据透视为列
    SELECT 
        user_id,
        MAX(CASE WHEN sensor_type = 'temperature' AND rn = 1 THEN value_numeric END) as temperature_c,
        MAX(CASE WHEN sensor_type = 'temperature' AND rn = 1 THEN timestamp_utc END) as temp_time,
        MAX(CASE WHEN sensor_type = 'heart_rate' AND rn = 1 THEN value_numeric END) as heart_rate_bpm,
        MAX(CASE WHEN sensor_type = 'heart_rate' AND rn = 1 THEN timestamp_utc END) as heart_time,
        MAX(CASE WHEN sensor_type = 'spo2' AND rn = 1 THEN value_numeric END) as spo2_percent,
        MAX(CASE WHEN sensor_type = 'spo2' AND rn = 1 THEN timestamp_utc END) as spo2_time,
        MAX(CASE WHEN sensor_type = 'steps' AND rn = 1 THEN value_numeric END) as step_count,
        MAX(CASE WHEN sensor_type = 'steps' AND rn = 1 THEN timestamp_utc END) as steps_time
    FROM latest_measurements
    GROUP BY user_id
)
-- 关联用户基本信息
SELECT 
    u.id as user_id,
    u.username as name,
    u.email as student_id,  -- 临时使用email作为student_id，您可以根据实际情况调整
    u.avatar_url as avatar,
    pd.temperature_c,
    pd.temp_time,
    pd.heart_rate_bpm,
    pd.heart_time,
    pd.spo2_percent,
    pd.spo2_time,
    pd.step_count,
    pd.steps_time
FROM public.ak_users u
LEFT JOIN pivoted_data pd ON u.id = pd.user_id
WHERE u.role = 'student';

-- 给视图添加注释
COMMENT ON VIEW public.v_latest_student_health IS '学生最新健康数据视图 - 包含每个学生的最新体温、心率、血氧、步数等数据';

-- 如果ss_sensor_measurements表不存在，创建替代方案
-- 使用专门的传感器数据表
CREATE OR REPLACE VIEW public.v_latest_student_health_alt AS
WITH latest_health_data AS (
    SELECT 
        user_id,
        'temperature' as data_type,
        temperature_celsius as value,
        recorded_at as recorded_time
    FROM public.ss_temperature 
    WHERE user_id IS NOT NULL AND temperature_celsius IS NOT NULL
    
    UNION ALL
    
    SELECT 
        user_id,
        'heart_rate' as data_type,
        heart_rate_bpm as value,
        recorded_at as recorded_time
    FROM public.ss_heart_rate 
    WHERE user_id IS NOT NULL AND heart_rate_bpm IS NOT NULL
    
    UNION ALL
    
    SELECT 
        user_id,
        'spo2' as data_type,
        spo2_percentage as value,
        recorded_at as recorded_time
    FROM public.ss_spo2 
    WHERE user_id IS NOT NULL AND spo2_percentage IS NOT NULL
    
    UNION ALL
    
    SELECT 
        user_id,
        'steps' as data_type,
        step_count as value,
        recorded_at as recorded_time
    FROM public.ss_steps 
    WHERE user_id IS NOT NULL AND step_count IS NOT NULL
),
latest_by_type AS (
    SELECT 
        user_id,
        data_type,
        value,
        recorded_time,
        ROW_NUMBER() OVER (
            PARTITION BY user_id, data_type 
            ORDER BY recorded_time DESC
        ) as rn
    FROM latest_health_data
),
pivoted_alt AS (
    SELECT 
        user_id,
        MAX(CASE WHEN data_type = 'temperature' AND rn = 1 THEN value END) as temperature_c,
        MAX(CASE WHEN data_type = 'temperature' AND rn = 1 THEN recorded_time END) as temp_time,
        MAX(CASE WHEN data_type = 'heart_rate' AND rn = 1 THEN value END) as heart_rate_bpm,
        MAX(CASE WHEN data_type = 'heart_rate' AND rn = 1 THEN recorded_time END) as heart_time,
        MAX(CASE WHEN data_type = 'spo2' AND rn = 1 THEN value END) as spo2_percent,
        MAX(CASE WHEN data_type = 'spo2' AND rn = 1 THEN recorded_time END) as spo2_time,
        MAX(CASE WHEN data_type = 'steps' AND rn = 1 THEN value END) as step_count,
        MAX(CASE WHEN data_type = 'steps' AND rn = 1 THEN recorded_time END) as steps_time
    FROM latest_by_type
    GROUP BY user_id
)
SELECT 
    u.id as user_id,
    u.username as name,
    u.email as student_id,
    u.avatar_url as avatar,
    pa.temperature_c,
    pa.temp_time,
    pa.heart_rate_bpm,
    pa.heart_time,
    pa.spo2_percent,
    pa.spo2_time,
    pa.step_count,
    pa.steps_time
FROM public.ak_users u
LEFT JOIN pivoted_alt pa ON u.id = pa.user_id
WHERE u.role = 'student';

-- 查看当前系统中存在哪些传感器相关的表
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public' 
    AND (table_name LIKE '%sensor%' OR table_name LIKE '%ss_%')
ORDER BY table_name;