-- 简化版学生数据查询脚本
-- 当健康数据视图不存在时的备用方案

-- 1. 创建一个简单的学生信息视图（不包含健康数据）
CREATE OR REPLACE VIEW public.v_student_basic_info AS
SELECT 
    u.id as user_id,
    u.username as name,
    COALESCE(u.email, u.username) as student_id,  -- 使用email或username作为学号
    u.avatar_url as avatar,
    u.class_id,
    c.name as class_name,
    g.name as grade_name,
    s.name as school_name,
    -- 默认健康数据字段（为了兼容前端）
    NULL::numeric as temperature_c,
    NULL::timestamp as temp_time,
    NULL::numeric as heart_rate_bpm,
    NULL::timestamp as heart_time,
    NULL::numeric as spo2_percent,
    NULL::timestamp as spo2_time,
    NULL::numeric as step_count,
    NULL::timestamp as steps_time
FROM public.ak_users u
LEFT JOIN public.ak_classes c ON u.class_id = c.id
LEFT JOIN public.ak_grades g ON c.grade_id = g.id
LEFT JOIN public.ak_schools s ON g.school_id = s.id
WHERE u.role = 'student';

-- 2. 检查系统中存在的表
SELECT 
    table_schema,
    table_name,
    table_type
FROM information_schema.tables 
WHERE table_schema = 'public' 
    AND (
        table_name LIKE '%student%' OR 
        table_name LIKE '%health%' OR 
        table_name LIKE '%sensor%' OR 
        table_name LIKE '%ss_%' OR
        table_name = 'ak_users'
    )
ORDER BY table_name;

-- 3. 查看ak_users表的结构
SELECT 
    column_name,
    data_type,
    is_nullable,
    column_default
FROM information_schema.columns 
WHERE table_schema = 'public' 
    AND table_name = 'ak_users'
ORDER BY ordinal_position;

-- 4. 测试基础查询：获取某个班级的学生
-- 这是前端当前使用的查询逻辑
SELECT 
    id,
    username as name,
    email as student_id,
    avatar_url as avatar,
    class_id,
    school_id,
    grade_id,
    role
FROM public.ak_users 
WHERE role = 'student' 
    AND class_id IS NOT NULL
ORDER BY username
LIMIT 10;

-- 5. 模拟前端查询：假设当前用户属于某个班级
WITH current_user_class AS (
    -- 模拟当前用户的class_id
    SELECT class_id 
    FROM public.ak_users 
    WHERE class_id IS NOT NULL 
    LIMIT 1
)
SELECT 
    u.id,
    u.username as name,
    u.email as student_id,
    u.avatar_url as avatar,
    u.class_id
FROM public.ak_users u
JOIN current_user_class cuc ON u.class_id = cuc.class_id
WHERE u.role = 'student'
ORDER BY u.username;