-- 丙村中学模拟数据SQL脚本
-- 适用于PostgreSQL数据库结构
-- 包含：地区、学校、年级、班级的完整数据

-- 清理现有测试数据（可选，谨慎使用）
-- DELETE FROM public.ak_classes WHERE id IN (SELECT c.id FROM public.ak_classes c JOIN public.ak_grades g ON c.grade_id = g.id JOIN public.ak_schools s ON g.school_id = s.id WHERE s.name = '丙村中学');
-- DELETE FROM public.ak_grades WHERE school_id IN (SELECT id FROM public.ak_schools WHERE name = '丙村中学');
-- DELETE FROM public.ak_schools WHERE name = '丙村中学';

-- Step 1: 创建地区数据（如果不存在）
-- 假设丙村中学位于广东省梅州市丰顺县
INSERT INTO public.ak_regions (id, name, parent_id, level, created_at) VALUES
('f47ac10b-58cc-4372-a567-0e02b2c3d479', '广东省', NULL, 1, now()),
('f47ac10b-58cc-4372-a567-0e02b2c3d480', '梅州市', 'f47ac10b-58cc-4372-a567-0e02b2c3d479', 2, now()),
('f47ac10b-58cc-4372-a567-0e02b2c3d481', '丰顺县', 'f47ac10b-58cc-4372-a567-0e02b2c3d480', 3, now()),
('f47ac10b-58cc-4372-a567-0e02b2c3d482', '丙村镇', 'f47ac10b-58cc-4372-a567-0e02b2c3d481', 4, now())
ON CONFLICT (id) DO NOTHING;

-- Step 2: 创建丙村中学学校数据
INSERT INTO public.ak_schools (id, name, region_id, type, created_at) VALUES
('bc123456-78cd-4ef0-a123-456789012345', '丙村中学', 'f47ac10b-58cc-4372-a567-0e02b2c3d482', '初级中学', now())
ON CONFLICT (id) DO NOTHING;

-- Step 3: 创建年级数据（初一到初三）
INSERT INTO public.ak_grades (id, school_id, name, created_at) VALUES
('bc111111-78cd-4ef0-a123-456789012345', 'bc123456-78cd-4ef0-a123-456789012345', '初一年级', now()),
('bc222222-78cd-4ef0-a123-456789012345', 'bc123456-78cd-4ef0-a123-456789012345', '初二年级', now()),
('bc333333-78cd-4ef0-a123-456789012345', 'bc123456-78cd-4ef0-a123-456789012345', '初三年级', now())
ON CONFLICT (id) DO NOTHING;

-- Step 4: 创建班级数据（每个年级6个班）
-- 初一年级6个班
INSERT INTO public.ak_classes (id, grade_id, name, created_at) VALUES
('bc111101-78cd-4ef0-a123-456789012345', 'bc111111-78cd-4ef0-a123-456789012345', '初一(1)班', now()),
('bc111102-78cd-4ef0-a123-456789012345', 'bc111111-78cd-4ef0-a123-456789012345', '初一(2)班', now()),
('bc111103-78cd-4ef0-a123-456789012345', 'bc111111-78cd-4ef0-a123-456789012345', '初一(3)班', now()),
('bc111104-78cd-4ef0-a123-456789012345', 'bc111111-78cd-4ef0-a123-456789012345', '初一(4)班', now()),
('bc111105-78cd-4ef0-a123-456789012345', 'bc111111-78cd-4ef0-a123-456789012345', '初一(5)班', now()),
('bc111106-78cd-4ef0-a123-456789012345', 'bc111111-78cd-4ef0-a123-456789012345', '初一(6)班', now()),

-- 初二年级6个班
('bc222201-78cd-4ef0-a123-456789012345', 'bc222222-78cd-4ef0-a123-456789012345', '初二(1)班', now()),
('bc222202-78cd-4ef0-a123-456789012345', 'bc222222-78cd-4ef0-a123-456789012345', '初二(2)班', now()),
('bc222203-78cd-4ef0-a123-456789012345', 'bc222222-78cd-4ef0-a123-456789012345', '初二(3)班', now()),
('bc222204-78cd-4ef0-a123-456789012345', 'bc222222-78cd-4ef0-a123-456789012345', '初二(4)班', now()),
('bc222205-78cd-4ef0-a123-456789012345', 'bc222222-78cd-4ef0-a123-456789012345', '初二(5)班', now()),
('bc222206-78cd-4ef0-a123-456789012345', 'bc222222-78cd-4ef0-a123-456789012345', '初二(6)班', now()),

-- 初三年级6个班
('bc333301-78cd-4ef0-a123-456789012345', 'bc333333-78cd-4ef0-a123-456789012345', '初三(1)班', now()),
('bc333302-78cd-4ef0-a123-456789012345', 'bc333333-78cd-4ef0-a123-456789012345', '初三(2)班', now()),
('bc333303-78cd-4ef0-a123-456789012345', 'bc333333-78cd-4ef0-a123-456789012345', '初三(3)班', now()),
('bc333304-78cd-4ef0-a123-456789012345', 'bc333333-78cd-4ef0-a123-456789012345', '初三(4)班', now()),
('bc333305-78cd-4ef0-a123-456789012345', 'bc333333-78cd-4ef0-a123-456789012345', '初三(5)班', now()),
('bc333306-78cd-4ef0-a123-456789012345', 'bc333333-78cd-4ef0-a123-456789012345', '初三(6)班', now())
ON CONFLICT (id) DO NOTHING;

-- Step 5: 验证数据完整性
-- 查看创建的学校信息
SELECT 
    s.id as school_id,
    s.name as school_name,
    s.type as school_type,
    r.name as region_name
FROM public.ak_schools s
LEFT JOIN public.ak_regions r ON s.region_id = r.id
WHERE s.name = '丙村中学';

-- 查看年级和班级统计
SELECT 
    g.name as grade_name,
    COUNT(c.id) as class_count,
    array_agg(c.name ORDER BY c.name) as class_names
FROM public.ak_grades g
LEFT JOIN public.ak_classes c ON g.id = c.grade_id
JOIN public.ak_schools s ON g.school_id = s.id
WHERE s.name = '丙村中学'
GROUP BY g.id, g.name
ORDER BY g.name;

-- 查看完整的层级结构
SELECT 
    r.name as region,
    s.name as school,
    g.name as grade,
    c.name as class_name,
    c.id as class_id
FROM public.ak_regions r
JOIN public.ak_schools s ON r.id = s.region_id
JOIN public.ak_grades g ON s.id = g.school_id
JOIN public.ak_classes c ON g.id = c.grade_id
WHERE s.name = '丙村中学'
ORDER BY g.name, c.name;

-- Step 6: 创建一些示例教师用户（可选）
-- 创建学校管理员和年级主任
INSERT INTO public.ak_users (id, username, email, password_hash, role, school_id, created_at) VALUES
('550e8400-e29b-41d4-a716-446655440001', 'bingcun_admin', 'admin@bingcun.edu.cn', '$2a$10$dummy_hash_for_admin', 'school_admin', 'bc123456-78cd-4ef0-a123-456789012345', now()),
('550e8400-e29b-41d4-a716-446655440002', 'zhang_teacher', 'zhang@bingcun.edu.cn', '$2a$10$dummy_hash_for_teacher1', 'teacher', 'bc123456-78cd-4ef0-a123-456789012345', now()),
('550e8400-e29b-41d4-a716-446655440003', 'li_teacher', 'li@bingcun.edu.cn', '$2a$10$dummy_hash_for_teacher2', 'teacher', 'bc123456-78cd-4ef0-a123-456789012345', now()),
('550e8400-e29b-41d4-a716-446655440004', 'wang_teacher', 'wang@bingcun.edu.cn', '$2a$10$dummy_hash_for_teacher3', 'teacher', 'bc123456-78cd-4ef0-a123-456789012345', now())
ON CONFLICT (id) DO NOTHING;

-- 分配教师到对应年级（示例）
INSERT INTO public.ak_teacher_roles (id, user_id, school_id, class_id, role, created_at) VALUES
-- 张老师负责初一年级
('550e8400-e29b-41d4-a716-446655440011', '550e8400-e29b-41d4-a716-446655440002', 'bc123456-78cd-4ef0-a123-456789012345', 'bc111101-78cd-4ef0-a123-456789012345', 'class_teacher', now()),
('550e8400-e29b-41d4-a716-446655440012', '550e8400-e29b-41d4-a716-446655440002', 'bc123456-78cd-4ef0-a123-456789012345', 'bc111102-78cd-4ef0-a123-456789012345', 'teacher', now()),

-- 李老师负责初二年级
('550e8400-e29b-41d4-a716-446655440013', '550e8400-e29b-41d4-a716-446655440003', 'bc123456-78cd-4ef0-a123-456789012345', 'bc222201-78cd-4ef0-a123-456789012345', 'class_teacher', now()),
('550e8400-e29b-41d4-a716-446655440014', '550e8400-e29b-41d4-a716-446655440003', 'bc123456-78cd-4ef0-a123-456789012345', 'bc222202-78cd-4ef0-a123-456789012345', 'teacher', now()),

-- 王老师负责初三年级
('550e8400-e29b-41d4-a716-446655440015', '550e8400-e29b-41d4-a716-446655440004', 'bc123456-78cd-4ef0-a123-456789012345', 'bc333301-78cd-4ef0-a123-456789012345', 'class_teacher', now()),
('550e8400-e29b-41d4-a716-446655440016', '550e8400-e29b-41d4-a716-446655440004', 'bc123456-78cd-4ef0-a123-456789012345', 'bc333302-78cd-4ef0-a123-456789012345', 'teacher', now())
ON CONFLICT (id) DO NOTHING;

-- Step 7: 创建一些示例学生用户（每班部分学生）
-- 初一(1)班学生示例
INSERT INTO public.ak_users (id, username, email, password_hash, role, school_id, grade_id, class_id, created_at) VALUES
('550e8400-e29b-41d4-a716-446655440021', 'zhang_xiaoming', 'zhang.xiaoming@student.bingcun.edu.cn', '$2a$10$dummy_hash_student', 'student', 'bc123456-78cd-4ef0-a123-456789012345', 'bc111111-78cd-4ef0-a123-456789012345', 'bc111101-78cd-4ef0-a123-456789012345', now()),
('550e8400-e29b-41d4-a716-446655440022', 'li_xiaohong', 'li.xiaohong@student.bingcun.edu.cn', '$2a$10$dummy_hash_student', 'student', 'bc123456-78cd-4ef0-a123-456789012345', 'bc111111-78cd-4ef0-a123-456789012345', 'bc111101-78cd-4ef0-a123-456789012345', now()),
('550e8400-e29b-41d4-a716-446655440023', 'wang_xiaogang', 'wang.xiaogang@student.bingcun.edu.cn', '$2a$10$dummy_hash_student', 'student', 'bc123456-78cd-4ef0-a123-456789012345', 'bc111111-78cd-4ef0-a123-456789012345', 'bc111101-78cd-4ef0-a123-456789012345', now()),

-- 初二(1)班学生示例
('550e8400-e29b-41d4-a716-446655440024', 'chen_xiaoli', 'chen.xiaoli@student.bingcun.edu.cn', '$2a$10$dummy_hash_student', 'student', 'bc123456-78cd-4ef0-a123-456789012345', 'bc222222-78cd-4ef0-a123-456789012345', 'bc222201-78cd-4ef0-a123-456789012345', now()),
('550e8400-e29b-41d4-a716-446655440025', 'liu_xiaowei', 'liu.xiaowei@student.bingcun.edu.cn', '$2a$10$dummy_hash_student', 'student', 'bc123456-78cd-4ef0-a123-456789012345', 'bc222222-78cd-4ef0-a123-456789012345', 'bc222201-78cd-4ef0-a123-456789012345', now()),

-- 初三(1)班学生示例
('550e8400-e29b-41d4-a716-446655440026', 'zhao_xiaojun', 'zhao.xiaojun@student.bingcun.edu.cn', '$2a$10$dummy_hash_student', 'student', 'bc123456-78cd-4ef0-a123-456789012345', 'bc333333-78cd-4ef0-a123-456789012345', 'bc333301-78cd-4ef0-a123-456789012345', now()),
('550e8400-e29b-41d4-a716-446655440027', 'sun_xiaofang', 'sun.xiaofang@student.bingcun.edu.cn', '$2a$10$dummy_hash_student', 'student', 'bc123456-78cd-4ef0-a123-456789012345', 'bc333333-78cd-4ef0-a123-456789012345', 'bc333301-78cd-4ef0-a123-456789012345', now())
ON CONFLICT (id) DO NOTHING;

-- Step 8: 最终数据验证和统计
-- 显示完整的数据统计
SELECT '=== 丙村中学数据创建完成 ===' as status;

SELECT 
    '学校信息' as info_type,
    s.name as name,
    s.type as type,
    r.name as location
FROM public.ak_schools s
LEFT JOIN public.ak_regions r ON s.region_id = r.id
WHERE s.name = '丙村中学'

UNION ALL

SELECT 
    '年级统计' as info_type,
    CONCAT(COUNT(DISTINCT g.id), ' 个年级') as name,
    '' as type,
    string_agg(DISTINCT g.name, ', ' ORDER BY g.name) as location
FROM public.ak_grades g
JOIN public.ak_schools s ON g.school_id = s.id
WHERE s.name = '丙村中学'

UNION ALL

SELECT 
    '班级统计' as info_type,
    CONCAT(COUNT(c.id), ' 个班级') as name,
    '' as type,
    '每个年级6个班' as location
FROM public.ak_classes c
JOIN public.ak_grades g ON c.grade_id = g.id
JOIN public.ak_schools s ON g.school_id = s.id
WHERE s.name = '丙村中学'

UNION ALL

SELECT 
    '用户统计' as info_type,
    CONCAT(COUNT(u.id), ' 个用户') as name,
    CONCAT(
        SUM(CASE WHEN u.role = 'student' THEN 1 ELSE 0 END), ' 学生, ',
        SUM(CASE WHEN u.role = 'teacher' THEN 1 ELSE 0 END), ' 教师, ',
        SUM(CASE WHEN u.role = 'school_admin' THEN 1 ELSE 0 END), ' 管理员'
    ) as type,
    '' as location
FROM public.ak_users u
WHERE u.school_id = 'bc123456-78cd-4ef0-a123-456789012345';

-- 显示详细的班级列表供参考
SELECT 
    g.name as grade_name,
    c.name as class_name,
    c.id as class_id,
    '可用于作业分配' as note
FROM public.ak_grades g
JOIN public.ak_classes c ON g.id = c.grade_id
JOIN public.ak_schools s ON g.school_id = s.id
WHERE s.name = '丙村中学'
ORDER BY g.name, c.name;

/*
使用说明：
1. 此脚本创建了完整的丙村中学数据结构
2. 包含地区(丙村镇) -> 学校(丙村中学) -> 年级(初一到初三) -> 班级(每个年级6个班)
3. 创建了示例的教师和学生用户
4. 使用了固定的UUID以确保数据的一致性
5. 包含了ON CONFLICT DO NOTHING以避免重复插入

数据结构：
- 1个学校：丙村中学
- 3个年级：初一、初二、初三年级
- 18个班级：每个年级6个班
- 示例用户：4个教师 + 7个学生 + 1个管理员

这些班级ID可以直接用于作业分配和教师管理功能的测试。
*/