-- 设置学生班级数据脚本
-- 为从 member_users 迁移到 ak_users 的学生数据补充学校、年级、班级信息

-- 步骤1: 创建基础数据（如果不存在）

-- 创建默认地区
INSERT INTO public.ak_regions (id, name, level) 
VALUES 
    ('11111111-1111-1111-1111-111111111111', '北京市', 1),
    ('22222222-2222-2222-2222-222222222222', '海淀区', 3)
ON CONFLICT (id) DO NOTHING;

-- 创建测试学校
INSERT INTO public.ak_schools (id, name, region_id, type) 
VALUES 
    ('33333333-3333-3333-3333-333333333333', '北京市第一中学', '22222222-2222-2222-2222-222222222222', '中学'),
    ('44444444-4444-4444-4444-444444444444', '北京市实验小学', '22222222-2222-2222-2222-222222222222', '小学')
ON CONFLICT (id) DO NOTHING;

-- 创建年级（中学）
INSERT INTO public.ak_grades (id, school_id, name) 
VALUES 
    ('55555555-5555-5555-5555-555555555555', '33333333-3333-3333-3333-333333333333', '高一'),
    ('66666666-6666-6666-6666-666666666666', '33333333-3333-3333-3333-333333333333', '高二'),
    ('77777777-7777-7777-7777-777777777777', '33333333-3333-3333-3333-333333333333', '高三')
ON CONFLICT (id) DO NOTHING;

-- 创建年级（小学）
INSERT INTO public.ak_grades (id, school_id, name) 
VALUES 
    ('88888888-8888-8888-8888-888888888888', '44444444-4444-4444-4444-444444444444', '一年级'),
    ('99999999-9999-9999-9999-999999999999', '44444444-4444-4444-4444-444444444444', '二年级'),
    ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '44444444-4444-4444-4444-444444444444', '三年级')
ON CONFLICT (id) DO NOTHING;

-- 创建班级（高一）
INSERT INTO public.ak_classes (id, grade_id, name) 
VALUES 
    ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', '55555555-5555-5555-5555-555555555555', '高一(1)班'),
    ('cccccccc-cccc-cccc-cccc-cccccccccccc', '55555555-5555-5555-5555-555555555555', '高一(2)班'),
    ('dddddddd-dddd-dddd-dddd-dddddddddddd', '55555555-5555-5555-5555-555555555555', '高一(3)班')
ON CONFLICT (id) DO NOTHING;

-- 创建班级（高二）
INSERT INTO public.ak_classes (id, grade_id, name) 
VALUES 
    ('eeeeeeee-eeee-eeee-eeee-eeeeeeeeeeee', '66666666-6666-6666-6666-666666666666', '高二(1)班'),
    ('ffffffff-ffff-ffff-ffff-ffffffffffff', '66666666-6666-6666-6666-666666666666', '高二(2)班')
ON CONFLICT (id) DO NOTHING;

-- 创建班级（小学）
INSERT INTO public.ak_classes (id, grade_id, name) 
VALUES 
    ('12121212-1212-1212-1212-121212121212', '88888888-8888-8888-8888-888888888888', '一年级(1)班'),
    ('13131313-1313-1313-1313-131313131313', '99999999-9999-9999-9999-999999999999', '二年级(1)班')
ON CONFLICT (id) DO NOTHING;

-- 步骤2: 查看当前学生数据状态
-- 查看有多少学生还没有设置班级信息
SELECT 
    role,
    COUNT(*) as user_count,
    COUNT(CASE WHEN school_id IS NULL THEN 1 END) as no_school_count,
    COUNT(CASE WHEN grade_id IS NULL THEN 1 END) as no_grade_count,
    COUNT(CASE WHEN class_id IS NULL THEN 1 END) as no_class_count
FROM public.ak_users 
WHERE role = 'student'
GROUP BY role;

-- 步骤3: 批量更新学生的学校、年级、班级信息
-- 这里提供几种方案，您可以根据实际情况选择：

-- 方案A: 将所有学生分配到默认的高一(1)班
-- 注意：这会将所有学生都分配到同一个班级
/*
UPDATE public.ak_users 
SET 
    school_id = '33333333-3333-3333-3333-333333333333',  -- 北京市第一中学
    grade_id = '55555555-5555-5555-5555-555555555555',   -- 高一
    class_id = 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb'    -- 高一(1)班
WHERE role = 'student' 
    AND (school_id IS NULL OR grade_id IS NULL OR class_id IS NULL);
*/

-- 方案B: 根据用户ID的哈希值随机分配到不同班级
-- 这样可以让学生相对均匀地分布在不同班级中
WITH class_assignment AS (
    SELECT 
        id,
        CASE 
            WHEN (hashtext(id::text) % 5) = 0 THEN 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb'  -- 高一(1)班
            WHEN (hashtext(id::text) % 5) = 1 THEN 'cccccccc-cccc-cccc-cccc-cccccccccccc'  -- 高一(2)班
            WHEN (hashtext(id::text) % 5) = 2 THEN 'dddddddd-dddd-dddd-dddd-dddddddddddd'  -- 高一(3)班
            WHEN (hashtext(id::text) % 5) = 3 THEN 'eeeeeeee-eeee-eeee-eeee-eeeeeeeeeeee'  -- 高二(1)班
            ELSE 'ffffffff-ffff-ffff-ffff-ffffffffffff'  -- 高二(2)班
        END as assigned_class_id
    FROM public.ak_users 
    WHERE role = 'student' 
        AND (school_id IS NULL OR grade_id IS NULL OR class_id IS NULL)
)
UPDATE public.ak_users 
SET 
    school_id = '33333333-3333-3333-3333-333333333333',  -- 北京市第一中学
    grade_id = CASE 
        WHEN ca.assigned_class_id IN ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'cccccccc-cccc-cccc-cccc-cccccccccccc', 'dddddddd-dddd-dddd-dddd-dddddddddddd') 
        THEN '55555555-5555-5555-5555-555555555555'  -- 高一
        ELSE '66666666-6666-6666-6666-666666666666'  -- 高二
    END,
    class_id = ca.assigned_class_id
FROM class_assignment ca
WHERE public.ak_users.id = ca.id;

-- 步骤4: 创建教师账户并分配班级管理权限
-- 创建测试教师（如果不存在）
INSERT INTO public.ak_users (
    id, 
    username, 
    email, 
    password_hash, 
    role,
    school_id,
    grade_id
) 
VALUES 
    ('teacher01-1111-1111-1111-111111111111', '张老师', 'zhang.teacher@school.edu', '$2b$10$dummy.hash.for.testing', 'teacher', '33333333-3333-3333-3333-333333333333', '55555555-5555-5555-5555-555555555555'),
    ('teacher02-2222-2222-2222-222222222222', '李老师', 'li.teacher@school.edu', '$2b$10$dummy.hash.for.testing', 'teacher', '33333333-3333-3333-3333-333333333333', '66666666-6666-6666-6666-666666666666')
ON CONFLICT (id) DO NOTHING;

-- 创建教师-班级关系（如果表结构存在）
-- 张老师负责高一的3个班级
INSERT INTO public.ak_teacher_roles (id, user_id, class_id)
VALUES 
    (gen_random_uuid(), 'teacher01-1111-1111-1111-111111111111', 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb'),
    (gen_random_uuid(), 'teacher01-1111-1111-1111-111111111111', 'cccccccc-cccc-cccc-cccc-cccccccccccc'),
    (gen_random_uuid(), 'teacher01-1111-1111-1111-111111111111', 'dddddddd-dddd-dddd-dddd-dddddddddddd')
ON CONFLICT (user_id, class_id) DO NOTHING;

-- 李老师负责高二的2个班级
INSERT INTO public.ak_teacher_roles (id, user_id, class_id)
VALUES 
    (gen_random_uuid(), 'teacher02-2222-2222-2222-222222222222', 'eeeeeeee-eeee-eeee-eeee-eeeeeeeeeeee'),
    (gen_random_uuid(), 'teacher02-2222-2222-2222-222222222222', 'ffffffff-ffff-ffff-ffff-ffffffffffff')
ON CONFLICT (user_id, class_id) DO NOTHING;

-- 步骤5: 验证数据设置结果
-- 查看更新后的学生分布情况
SELECT 
    s.name as school_name,
    g.name as grade_name,
    c.name as class_name,
    COUNT(u.id) as student_count
FROM public.ak_users u
LEFT JOIN public.ak_schools s ON u.school_id = s.id
LEFT JOIN public.ak_grades g ON u.grade_id = g.id  
LEFT JOIN public.ak_classes c ON u.class_id = c.id
WHERE u.role = 'student'
GROUP BY s.name, g.name, c.name
ORDER BY s.name, g.name, c.name;

-- 查看教师负责的班级情况
SELECT 
    u.username as teacher_name,
    s.name as school_name,
    g.name as grade_name,
    c.name as class_name,
    (SELECT COUNT(*) FROM public.ak_users WHERE class_id = c.id AND role = 'student') as student_count
FROM public.ak_teacher_roles tr
JOIN public.ak_users u ON tr.user_id = u.id
JOIN public.ak_classes c ON tr.class_id = c.id
JOIN public.ak_grades g ON c.grade_id = g.id
JOIN public.ak_schools s ON g.school_id = s.id
ORDER BY u.username, s.name, g.name, c.name;

-- 检查是否还有未分配班级的学生
SELECT 
    COUNT(*) as unassigned_students,
    array_agg(username) as usernames
FROM public.ak_users 
WHERE role = 'student' 
    AND (school_id IS NULL OR grade_id IS NULL OR class_id IS NULL);