-- 学生健康数据展示用的简化传感器数据表
-- 为了简化演示，我们创建一个统一的传感器数据表

-- 创建简化的传感器数据表
CREATE TABLE IF NOT EXISTS public.ss_sensor_data (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE,
    sensor_type TEXT NOT NULL, -- 'temperature', 'heart_rate', 'oxygen_level', 'steps'
    value NUMERIC NOT NULL,
    unit TEXT, -- '°C', 'bpm', '%', 'steps'
    recorded_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 创建索引以提高查询性能
CREATE INDEX IF NOT EXISTS idx_ss_sensor_data_user_type_time 
ON public.ss_sensor_data(user_id, sensor_type, recorded_at DESC);

CREATE INDEX IF NOT EXISTS idx_ss_sensor_data_type_time 
ON public.ss_sensor_data(sensor_type, recorded_at DESC);

-- 插入示例数据（为演示用）
-- 假设有一个学生用户ID
DO $$
DECLARE
    demo_user_id UUID;
    current_time TIMESTAMPTZ;
BEGIN
    -- 查找第一个学生用户ID
    SELECT id INTO demo_user_id 
    FROM public.ak_users 
    WHERE role = 'student' 
    LIMIT 1;
    
    -- 如果找到学生用户，插入示例数据
    IF demo_user_id IS NOT NULL THEN
        current_time := NOW();
        
        -- 插入体温数据（最近7天，每小时一条）
        INSERT INTO public.ss_sensor_data (user_id, sensor_type, value, unit, recorded_at)
        SELECT 
            demo_user_id,
            'temperature',
            36.0 + (random() * 1.5), -- 36.0-37.5°C
            '°C',
            current_time - (interval '1 hour' * generate_series(0, 167))
        FROM generate_series(0, 167) AS gs;
        
        -- 插入心率数据（最近7天，每30分钟一条）
        INSERT INTO public.ss_sensor_data (user_id, sensor_type, value, unit, recorded_at)
        SELECT 
            demo_user_id,
            'heart_rate',
            60 + (random() * 40), -- 60-100 bpm
            'bpm',
            current_time - (interval '30 minutes' * generate_series(0, 335))
        FROM generate_series(0, 335) AS gs;
        
        -- 插入血氧数据（最近7天，每2小时一条）
        INSERT INTO public.ss_sensor_data (user_id, sensor_type, value, unit, recorded_at)
        SELECT 
            demo_user_id,
            'oxygen_level',
            95 + (random() * 5), -- 95-100%
            '%',
            current_time - (interval '2 hours' * generate_series(0, 83))
        FROM generate_series(0, 83) AS gs;
        
        -- 插入步数数据（最近7天，每天一条）
        INSERT INTO public.ss_sensor_data (user_id, sensor_type, value, unit, recorded_at)
        SELECT 
            demo_user_id,
            'steps',
            5000 + (random() * 10000), -- 5000-15000 steps
            'steps',
            current_time - (interval '1 day' * generate_series(0, 6))
        FROM generate_series(0, 6) AS gs;
        
        RAISE NOTICE '成功为用户 % 插入示例传感器数据', demo_user_id;
    ELSE
        RAISE NOTICE '未找到学生用户，跳过示例数据插入';
    END IF;
END $$;

-- 创建视图以便查询最新的传感器数据
CREATE OR REPLACE VIEW public.v_latest_sensor_data AS
WITH latest_readings AS (
    SELECT 
        user_id,
        sensor_type,
        value,
        unit,
        recorded_at,
        ROW_NUMBER() OVER (PARTITION BY user_id, sensor_type ORDER BY recorded_at DESC) as rn
    FROM public.ss_sensor_data
)
SELECT 
    user_id,
    sensor_type,
    value,
    unit,
    recorded_at
FROM latest_readings 
WHERE rn = 1;

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

COMMENT ON TABLE public.ss_sensor_data IS '简化的传感器数据表，用于存储学生的健康监测数据';
COMMENT ON VIEW public.v_latest_sensor_data IS '获取每个用户每种传感器类型的最新数据';