-- ===================================================================
-- 消息系统测试数据生成脚本
-- 适用于运动训练监测与AI评估平台
-- 包含教师端和学生端用户的各种消息场景
-- ===================================================================

-- 测试用户ID
-- 教师端用户ID: 7bf7378e-a027-473e-97ac-3460ed3f170a
-- 学生端用户ID: eed3824b-bba1-4309-8048-19d17367c084

-- 清理可能存在的测试数据
DO $$
BEGIN
    -- 删除测试消息数据（按依赖关系顺序）
    DELETE FROM public.ak_message_recipients WHERE message_id IN (
        SELECT id FROM public.ak_messages WHERE metadata->>'test_data' = 'true'
    );
    DELETE FROM public.ak_messages WHERE metadata->>'test_data' = 'true';
    DELETE FROM public.ak_message_group_members WHERE group_id IN (
        SELECT id FROM public.ak_message_groups WHERE name LIKE '%测试%'
    );
    DELETE FROM public.ak_message_groups WHERE name LIKE '%测试%';
    
    RAISE NOTICE '已清理现有测试数据';
EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE '清理测试数据时出现错误，继续执行...';
END $$;

-- ===================================================================
-- 1. 创建测试用户消息偏好设置
-- ===================================================================

-- 为教师用户创建消息偏好设置
INSERT INTO public.ak_user_message_preferences (
    user_id, 
    global_enabled, 
    push_enabled, 
    email_enabled,
    type_preferences
) VALUES (
    '7bf7378e-a027-473e-97ac-3460ed3f170a', 
    true, 
    true, 
    true,
    '{
        "system": {"enabled": true, "push": true, "email": true},
        "device": {"enabled": true, "push": true, "email": false},
        "training": {"enabled": true, "push": true, "email": false},
        "social": {"enabled": true, "push": true, "email": false},
        "assignment": {"enabled": true, "push": true, "email": true},
        "achievement": {"enabled": true, "push": true, "email": false},
        "chat": {"enabled": true, "push": true, "email": false},
        "announcement": {"enabled": true, "push": true, "email": true},
        "reminder": {"enabled": true, "push": true, "email": true},
        "alert": {"enabled": true, "push": true, "email": true}
    }'::jsonb
) ON CONFLICT (user_id) DO UPDATE SET
    global_enabled = EXCLUDED.global_enabled,
    push_enabled = EXCLUDED.push_enabled,
    email_enabled = EXCLUDED.email_enabled,
    type_preferences = EXCLUDED.type_preferences;

-- 为学生用户创建消息偏好设置
INSERT INTO public.ak_user_message_preferences (
    user_id, 
    global_enabled, 
    push_enabled, 
    email_enabled,
    type_preferences
) VALUES (
    'eed3824b-bba1-4309-8048-19d17367c084', 
    true, 
    true, 
    false,
    '{
        "system": {"enabled": true, "push": true, "email": false},
        "device": {"enabled": true, "push": true, "email": false},
        "training": {"enabled": true, "push": true, "email": false},
        "social": {"enabled": true, "push": true, "email": false},
        "assignment": {"enabled": true, "push": true, "email": false},
        "achievement": {"enabled": true, "push": true, "email": false},
        "chat": {"enabled": true, "push": true, "email": false},
        "announcement": {"enabled": true, "push": true, "email": false},
        "reminder": {"enabled": true, "push": true, "email": false},
        "alert": {"enabled": true, "push": true, "email": false}
    }'::jsonb
) ON CONFLICT (user_id) DO UPDATE SET
    global_enabled = EXCLUDED.global_enabled,
    push_enabled = EXCLUDED.push_enabled,
    email_enabled = EXCLUDED.email_enabled,
    type_preferences = EXCLUDED.type_preferences;

-- ===================================================================
-- 2. 创建测试消息群组
-- ===================================================================

-- 创建班级群组
INSERT INTO public.ak_message_groups (
    id,
    name,
    description,
    group_type,
    owner_id,
    is_public,
    member_limit,
    settings
) VALUES (
    'f47ac10b-58cc-4372-a567-0e02b2c3d479',
    '高一(1)班运动训练群',
    '高一(1)班运动训练讨论群，用于发布训练任务和交流心得',
    'class',
    '7bf7378e-a027-473e-97ac-3460ed3f170a',
    false,
    50,
    '{"allow_file_upload": true, "allow_voice_message": true, "auto_archive_days": 30}'::jsonb
);

-- 创建师生群组
INSERT INTO public.ak_message_groups (
    id,
    name,
    description,
    group_type,
    owner_id,
    is_public,
    member_limit,
    settings
) VALUES (
    'a1b2c3d4-e5f6-7890-abcd-ef1234567890',
    '师生交流群',
    '教师与学生的日常交流群组',
    'normal',
    '7bf7378e-a027-473e-97ac-3460ed3f170a',
    false,
    100,
    '{"allow_file_upload": true, "allow_voice_message": true}'::jsonb
);

-- 添加群组成员
INSERT INTO public.ak_message_group_members (
    group_id,
    user_id,
    role,
    status,
    nickname,
    notification_enabled
) VALUES 
-- 班级群成员
('f47ac10b-58cc-4372-a567-0e02b2c3d479', '7bf7378e-a027-473e-97ac-3460ed3f170a', 'owner', 'active', '李老师', true),
('f47ac10b-58cc-4372-a567-0e02b2c3d479', 'eed3824b-bba1-4309-8048-19d17367c084', 'member', 'active', '张同学', true),
-- 师生交流群成员
('a1b2c3d4-e5f6-7890-abcd-ef1234567890', '7bf7378e-a027-473e-97ac-3460ed3f170a', 'owner', 'active', '李老师', true),
('a1b2c3d4-e5f6-7890-abcd-ef1234567890', 'eed3824b-bba1-4309-8048-19d17367c084', 'member', 'active', '张同学', true);

-- ===================================================================
-- 3. 创建各类型测试消息
-- ===================================================================

-- 变量声明
DO $$
DECLARE
    teacher_id uuid := '7bf7378e-a027-473e-97ac-3460ed3f170a';
    student_id uuid := 'eed3824b-bba1-4309-8048-19d17367c084';
    class_group_id uuid := 'f47ac10b-58cc-4372-a567-0e02b2c3d479';
    chat_group_id uuid := 'a1b2c3d4-e5f6-7890-abcd-ef1234567890';
    
    -- 消息类型ID
    system_type_id uuid;
    device_type_id uuid;
    training_type_id uuid;
    social_type_id uuid;
    assignment_type_id uuid;
    achievement_type_id uuid;
    chat_type_id uuid;
    announcement_type_id uuid;
    reminder_type_id uuid;
    alert_type_id uuid;
    
    -- 消息ID变量
    msg_id uuid;
    
BEGIN
    -- 获取消息类型ID
    SELECT id INTO system_type_id FROM public.ak_message_types WHERE code = 'system';
    SELECT id INTO device_type_id FROM public.ak_message_types WHERE code = 'device';
    SELECT id INTO training_type_id FROM public.ak_message_types WHERE code = 'training';
    SELECT id INTO social_type_id FROM public.ak_message_types WHERE code = 'social';
    SELECT id INTO assignment_type_id FROM public.ak_message_types WHERE code = 'assignment';
    SELECT id INTO achievement_type_id FROM public.ak_message_types WHERE code = 'achievement';
    SELECT id INTO chat_type_id FROM public.ak_message_types WHERE code = 'chat';
    SELECT id INTO announcement_type_id FROM public.ak_message_types WHERE code = 'announcement';
    SELECT id INTO reminder_type_id FROM public.ak_message_types WHERE code = 'reminder';
    SELECT id INTO alert_type_id FROM public.ak_message_types WHERE code = 'alert';
    
    -- 3.1 系统消息
    INSERT INTO public.ak_messages (
        id, message_type_id, sender_type, sender_id, sender_name,
        receiver_type, receiver_id, title, content, content_type,
        priority, is_broadcast, metadata, created_at
    ) VALUES (
        gen_random_uuid(), system_type_id, 'system', null, '系统',
        'user', student_id, '欢迎使用运动训练平台！',
        '欢迎来到运动训练监测平台！您可以在这里记录训练数据、查看AI分析报告、与老师同学交流。祝您运动愉快！',
        'text', 100, false,
        '{"test_data": "true", "welcome_message": true}'::jsonb,
        now() - interval '2 days'
    ) RETURNING id INTO msg_id;
    
    -- 创建接收记录
    INSERT INTO public.ak_message_recipients (
        message_id, recipient_type, recipient_id, status, delivered_at, read_at
    ) VALUES (
        msg_id, 'user', student_id, 'read', now() - interval '2 days', now() - interval '1 day'
    );
    
    -- 3.2 设备消息 - 设备连接成功
    INSERT INTO public.ak_messages (
        id, message_type_id, sender_type, sender_id, sender_name,
        receiver_type, receiver_id, title, content, content_type,
        priority, device_data, metadata, created_at
    ) VALUES (
        gen_random_uuid(), device_type_id, 'device', gen_random_uuid(), '智能手环Pro',
        'user', student_id, '设备连接成功',
        '您的智能手环Pro已成功连接，电量85%，可以开始记录运动数据了。',
        'text', 80,
        '{"device_name": "智能手环Pro", "battery_level": 85, "firmware_version": "2.1.3", "connection_type": "bluetooth"}'::jsonb,
        '{"test_data": "true", "device_type": "wearable"}'::jsonb,
        now() - interval '1 day'
    ) RETURNING id INTO msg_id;
    
    INSERT INTO public.ak_message_recipients (
        message_id, recipient_type, recipient_id, status, delivered_at, read_at
    ) VALUES (
        msg_id, 'user', student_id, 'read', now() - interval '1 day', now() - interval '20 hours'
    );
    
    -- 3.3 设备消息 - 低电量警告
    INSERT INTO public.ak_messages (
        id, message_type_id, sender_type, sender_id, sender_name,
        receiver_type, receiver_id, title, content, content_type,
        priority, is_urgent, device_data, metadata, created_at
    ) VALUES (
        gen_random_uuid(), device_type_id, 'device', gen_random_uuid(), '智能手环Pro',
        'user', student_id, '设备电量不足',
        '您的智能手环Pro电量仅剩15%，请及时充电以确保正常使用。',
        'text', 85, true,
        '{"device_name": "智能手环Pro", "battery_level": 15, "estimated_time_left": "2小时"}'::jsonb,
        '{"test_data": "true", "alert_type": "low_battery"}'::jsonb,
        now() - interval '2 hours'
    ) RETURNING id INTO msg_id;
    
    INSERT INTO public.ak_message_recipients (
        message_id, recipient_type, recipient_id, status, delivered_at
    ) VALUES (
        msg_id, 'user', student_id, 'delivered', now() - interval '2 hours'
    );
    
    -- 3.4 训练消息 - 训练完成通知
    INSERT INTO public.ak_messages (
        id, message_type_id, sender_type, sender_id, sender_name,
        receiver_type, receiver_id, title, content, content_type,
        priority, metadata, created_at
    ) VALUES (
        gen_random_uuid(), training_type_id, 'system', null, '训练助手',
        'user', student_id, '🎉 训练完成！',
        '恭喜完成跑步训练！用时25分钟，消耗180卡路里，步数3250步，平均心率145 bpm。您的表现很棒，继续保持！',
        'text', 70,
        '{"test_data": "true", "activity_type": "running", "duration": 25, "calories": 180, "steps": 3250, "avg_heart_rate": 145}'::jsonb,
        now() - interval '3 hours'
    ) RETURNING id INTO msg_id;
    
    INSERT INTO public.ak_message_recipients (
        message_id, recipient_type, recipient_id, status, delivered_at, read_at
    ) VALUES (
        msg_id, 'user', student_id, 'read', now() - interval '3 hours', now() - interval '2 hours'
    );
    
    -- 3.5 作业通知 - 教师发布作业
    INSERT INTO public.ak_messages (
        id, message_type_id, sender_type, sender_id, sender_name,
        receiver_type, receiver_id, title, content, content_type,
        priority, metadata, expires_at, created_at
    ) VALUES (
        gen_random_uuid(), assignment_type_id, 'user', teacher_id, '李老师',
        'user', student_id, '新作业：每日运动打卡',
        '同学们好！本周的运动作业已发布：每天完成30分钟有氧运动，记录心率和步数数据。截止时间：本周日23:59。请按时完成并上传数据。',
        'text', 75,
        '{"test_data": "true", "assignment_id": "homework_001", "due_date": "2025-06-22 23:59:00", "subject": "体育"}'::jsonb,
        '2025-06-22 23:59:00'::timestamp,
        now() - interval '1 day'
    ) RETURNING id INTO msg_id;
    
    INSERT INTO public.ak_message_recipients (
        message_id, recipient_type, recipient_id, status, delivered_at, read_at
    ) VALUES (
        msg_id, 'user', student_id, 'read', now() - interval '1 day', now() - interval '18 hours'
    );
    
    -- 3.6 成就通知
    INSERT INTO public.ak_messages (
        id, message_type_id, sender_type, sender_id, sender_name,
        receiver_type, receiver_id, title, content, content_type,
        priority, metadata, created_at
    ) VALUES (
        gen_random_uuid(), achievement_type_id, 'system', null, '成就系统',
        'user', student_id, '🏆 恭喜解锁新成就！',
        '恭喜您解锁了"坚持不懈"成就！连续7天完成运动打卡，获得50积分奖励。继续努力，向更高目标前进！',
        'text', 50,
        '{"test_data": "true", "achievement_name": "坚持不懈", "points": 50, "days_streak": 7}'::jsonb,
        now() - interval '6 hours'
    ) RETURNING id INTO msg_id;
    
    INSERT INTO public.ak_message_recipients (
        message_id, recipient_type, recipient_id, status, delivered_at
    ) VALUES (
        msg_id, 'user', student_id, 'delivered', now() - interval '6 hours'
    );
    
    -- 3.7 群组聊天消息 - 教师在班级群发消息
    INSERT INTO public.ak_messages (
        id, message_type_id, sender_type, sender_id, sender_name,
        receiver_type, receiver_id, title, content, content_type,
        priority, conversation_id, metadata, created_at
    ) VALUES (
        gen_random_uuid(), chat_type_id, 'user', teacher_id, '李老师',
        'group', class_group_id, null,
        '同学们，下周一我们将进行体能测试，请大家提前做好准备。测试项目包括：50米跑、立定跳远、引体向上。有问题随时问我。',
        'text', 90, class_group_id,
        '{"test_data": "true", "group_message": true}'::jsonb,
        now() - interval '4 hours'
    ) RETURNING id INTO msg_id;
    
    -- 为群组成员创建接收记录
    INSERT INTO public.ak_message_recipients (
        message_id, recipient_type, recipient_id, status, delivered_at, read_at
    ) VALUES 
    (msg_id, 'user', student_id, 'read', now() - interval '4 hours', now() - interval '3 hours'),
    (msg_id, 'user', teacher_id, 'read', now() - interval '4 hours', now() - interval '4 hours');
    
    -- 3.8 学生回复群组消息
    INSERT INTO public.ak_messages (
        id, message_type_id, sender_type, sender_id, sender_name,
        receiver_type, receiver_id, title, content, content_type,
        priority, conversation_id, parent_message_id, metadata, created_at
    ) VALUES (
        gen_random_uuid(), chat_type_id, 'user', student_id, '张同学',
        'group', class_group_id, null,
        '好的老师，我会认真准备的！请问引体向上有什么技巧吗？',
        'text', 90, class_group_id, msg_id,
        '{"test_data": "true", "group_message": true, "reply": true}'::jsonb,
        now() - interval '3 hours'
    ) RETURNING id INTO msg_id;
    
    INSERT INTO public.ak_message_recipients (
        message_id, recipient_type, recipient_id, status, delivered_at, read_at
    ) VALUES 
    (msg_id, 'user', teacher_id, 'read', now() - interval '3 hours', now() - interval '2 hours'),
    (msg_id, 'user', student_id, 'read', now() - interval '3 hours', now() - interval '3 hours');
    
    -- 3.9 私聊消息 - 教师私信学生
    INSERT INTO public.ak_messages (
        id, message_type_id, sender_type, sender_id, sender_name,
        receiver_type, receiver_id, title, content, content_type,
        priority, metadata, created_at
    ) VALUES (
        gen_random_uuid(), chat_type_id, 'user', teacher_id, '李老师',
        'user', student_id, null,
        '张同学，我看到你最近的运动数据很不错，心率控制得很好。继续保持这个节奏，相信你的体能会有很大提升。',
        'text', 90,
        '{"test_data": "true", "private_message": true}'::jsonb,
        now() - interval '1 hour'
    ) RETURNING id INTO msg_id;
    
    INSERT INTO public.ak_message_recipients (
        message_id, recipient_type, recipient_id, status, delivered_at
    ) VALUES (
        msg_id, 'user', student_id, 'delivered', now() - interval '1 hour'
    );
    
    -- 3.10 公告消息
    INSERT INTO public.ak_messages (
        id, message_type_id, sender_type, sender_id, sender_name,
        receiver_type, receiver_id, title, content, content_type,
        priority, is_broadcast, metadata, created_at
    ) VALUES (
        gen_random_uuid(), announcement_type_id, 'system', null, '运动平台',
        'broadcast', null, '平台升级通知',
        '各位用户，运动训练平台将于本周六凌晨2:00-4:00进行系统升级，届时将暂停服务。升级后将新增AI运动分析功能，敬请期待！',
        'text', 95, true,
        '{"test_data": "true", "maintenance": true, "upgrade": true}'::jsonb,
        now() - interval '12 hours'
    ) RETURNING id INTO msg_id;
    
    -- 为所有用户创建接收记录（这里只为测试用户创建）
    INSERT INTO public.ak_message_recipients (
        message_id, recipient_type, recipient_id, status, delivered_at, read_at
    ) VALUES 
    (msg_id, 'user', teacher_id, 'read', now() - interval '12 hours', now() - interval '11 hours'),
    (msg_id, 'user', student_id, 'read', now() - interval '12 hours', now() - interval '10 hours');
    
    -- 3.11 提醒消息 - 运动提醒
    INSERT INTO public.ak_messages (
        id, message_type_id, sender_type, sender_id, sender_name,
        receiver_type, receiver_id, title, content, content_type,
        priority, metadata, scheduled_at, created_at
    ) VALUES (
        gen_random_uuid(), reminder_type_id, 'system', null, '运动助手',
        'user', student_id, '⏰ 运动时间到了！',
        '根据您的运动计划，现在是跑步时间。建议运动30分钟，注意热身和拉伸。今天天气不错，适合户外运动！',
        'text', 65,
        '{"test_data": "true", "reminder_type": "exercise", "activity": "running", "duration": 30}'::jsonb,
        now() - interval '30 minutes',
        now() - interval '30 minutes'
    ) RETURNING id INTO msg_id;
    
    INSERT INTO public.ak_message_recipients (
        message_id, recipient_type, recipient_id, status, delivered_at
    ) VALUES (
        msg_id, 'user', student_id, 'delivered', now() - interval '30 minutes'
    );
    
    -- 3.12 警报消息 - 异常心率警报
    INSERT INTO public.ak_messages (
        id, message_type_id, sender_type, sender_id, sender_name,
        receiver_type, receiver_id, title, content, content_type,
        priority, is_urgent, device_data, metadata, created_at
    ) VALUES (
        gen_random_uuid(), alert_type_id, 'device', gen_random_uuid(), '智能手环Pro',
        'user', student_id, '⚠️ 心率异常警报',
        '检测到您的心率持续偏高（185 bpm），已超过安全范围。建议立即停止运动，休息并观察。如有不适请及时就医。',
        'text', 99, true,
        '{"device_name": "智能手环Pro", "heart_rate": 185, "safe_range": "60-180", "duration": "5分钟"}'::jsonb,
        '{"test_data": "true", "alert_type": "heart_rate", "severity": "high"}'::jsonb,
        now() - interval '10 minutes'
    ) RETURNING id INTO msg_id;
    
    INSERT INTO public.ak_message_recipients (
        message_id, recipient_type, recipient_id, status, delivered_at, read_at
    ) VALUES (
        msg_id, 'user', student_id, 'read', now() - interval '10 minutes', now() - interval '8 minutes'
    );
    
    -- 3.13 社交消息 - 点赞通知
    INSERT INTO public.ak_messages (
        id, message_type_id, sender_type, sender_id, sender_name,
        receiver_type, receiver_id, title, content, content_type,
        priority, metadata, created_at
    ) VALUES (
        gen_random_uuid(), social_type_id, 'user', teacher_id, '李老师',
        'user', student_id, '👍 老师为您点赞了',
        '李老师为您的运动记录"今日跑步5公里"点赞了，并评论："坚持得很好，继续加油！"',
        'text', 60,
        '{"test_data": "true", "social_action": "like", "post_id": "post_001", "comment": "坚持得很好，继续加油！"}'::jsonb,
        now() - interval '5 hours'
    ) RETURNING id INTO msg_id;
    
    INSERT INTO public.ak_message_recipients (
        message_id, recipient_type, recipient_id, status, delivered_at, read_at
    ) VALUES (
        msg_id, 'user', student_id, 'read', now() - interval '5 hours', now() - interval '4 hours'
    );
    
    -- 更新消息统计
    UPDATE public.ak_messages 
    SET 
        total_recipients = (SELECT COUNT(*) FROM public.ak_message_recipients WHERE message_id = public.ak_messages.id),
        delivered_count = (SELECT COUNT(*) FROM public.ak_message_recipients WHERE message_id = public.ak_messages.id AND status IN ('delivered', 'read')),
        read_count = (SELECT COUNT(*) FROM public.ak_message_recipients WHERE message_id = public.ak_messages.id AND status = 'read')
    WHERE metadata->>'test_data' = 'true';
    
    RAISE NOTICE '测试消息数据创建完成！';
END $$;

-- ===================================================================
-- 4. 创建一些历史消息数据（模拟长期使用）
-- ===================================================================

-- 创建过去一个月的消息统计数据
INSERT INTO public.ak_message_stats (
    date_bucket, hour_bucket, message_type_id, sender_type,
    total_sent, total_delivered, total_read, total_replied
)
SELECT 
    date_bucket,
    hour_bucket,
    mt.id,
    sender_type,
    FLOOR(RANDOM() * 50 + 10)::INT as total_sent,
    FLOOR(RANDOM() * 45 + 8)::INT as total_delivered,
    FLOOR(RANDOM() * 40 + 5)::INT as total_read,
    FLOOR(RANDOM() * 10 + 1)::INT as total_replied
FROM (
    SELECT generate_series(
        CURRENT_DATE - INTERVAL '30 days',
        CURRENT_DATE - INTERVAL '1 day',
        INTERVAL '1 day'
    )::DATE as date_bucket,
    generate_series(8, 22) as hour_bucket
) dates
CROSS JOIN (
    SELECT UNNEST(ARRAY['user', 'system', 'device']) as sender_type
) senders
CROSS JOIN public.ak_message_types mt
WHERE mt.is_active = true
  AND RANDOM() > 0.3; -- 随机生成70%的数据

-- ===================================================================
-- 5. 创建一些测试查询和验证
-- ===================================================================

-- 显示测试数据统计
DO $$
DECLARE
    msg_count INTEGER;
    recipient_count INTEGER;
    unread_count INTEGER;
BEGIN
    -- 统计消息数量
    SELECT COUNT(*) INTO msg_count FROM public.ak_messages WHERE metadata->>'test_data' = 'true';
    SELECT COUNT(*) INTO recipient_count FROM public.ak_message_recipients mr 
    JOIN public.ak_messages m ON mr.message_id = m.id 
    WHERE m.metadata->>'test_data' = 'true';
    
    -- 统计学生未读消息
    SELECT COUNT(*) INTO unread_count FROM public.ak_message_recipients mr 
    JOIN public.ak_messages m ON mr.message_id = m.id 
    WHERE m.metadata->>'test_data' = 'true' 
      AND mr.recipient_id = 'eed3824b-bba1-4309-8048-19d17367c084'
      AND mr.status != 'read';
    
    RAISE NOTICE '===================================================================';
    RAISE NOTICE '测试数据统计：';
    RAISE NOTICE '- 总消息数：%', msg_count;
    RAISE NOTICE '- 总接收记录数：%', recipient_count;
    RAISE NOTICE '- 学生未读消息数：%', unread_count;
    RAISE NOTICE '===================================================================';
END $$;

-- ===================================================================
-- 6. 常用测试查询示例
-- ===================================================================

-- 查询学生的所有消息（按时间倒序）
/*
SELECT 
    m.title,
    m.content,
    mt.name as message_type,
    m.sender_name,
    mr.status,
    m.created_at,
    m.is_urgent,
    m.priority
FROM public.ak_messages m
JOIN public.ak_message_types mt ON m.message_type_id = mt.id
JOIN public.ak_message_recipients mr ON m.id = mr.message_id
WHERE mr.recipient_id = 'eed3824b-bba1-4309-8048-19d17367c084'
  AND mr.recipient_type = 'user'
  AND m.metadata->>'test_data' = 'true'
ORDER BY m.created_at DESC;
*/

-- 查询教师发送的所有消息
/*
SELECT 
    m.title,
    m.content,
    mt.name as message_type,
    m.receiver_type,
    m.created_at,
    m.total_recipients,
    m.read_count
FROM public.ak_messages m
JOIN public.ak_message_types mt ON m.message_type_id = mt.id
WHERE m.sender_id = '7bf7378e-a027-473e-97ac-3460ed3f170a'
  AND m.metadata->>'test_data' = 'true'
ORDER BY m.created_at DESC;
*/

-- 查询群组消息
/*
SELECT 
    m.title,
    m.content,
    m.sender_name,
    mg.name as group_name,
    m.created_at
FROM public.ak_messages m
JOIN public.ak_message_groups mg ON m.receiver_id = mg.id
WHERE m.receiver_type = 'group'
  AND m.metadata->>'test_data' = 'true'
ORDER BY m.created_at DESC;
*/

-- 查询未读消息统计
/*
SELECT * FROM public.get_unread_message_count('eed3824b-bba1-4309-8048-19d17367c084');
*/

RAISE NOTICE '===================================================================';
RAISE NOTICE '测试数据生成完成！';
RAISE NOTICE '教师ID: 7bf7378e-a027-473e-97ac-3460ed3f170a';
RAISE NOTICE '学生ID: eed3824b-bba1-4309-8048-19d17367c084';
RAISE NOTICE '可以使用注释中的查询语句测试各种消息场景';
RAISE NOTICE '===================================================================';