-- ===================================================================
-- 消息系统测试数据生成脚本
-- PostgreSQL 15+ 兼容版本
-- 教师ID: 7bf7378e-a027-473e-97ac-3460ed3f170a
-- 学生ID: eed3824b-bba1-4309-8048-19d17367c084
-- ===================================================================

-- 清理现有测试数据
DELETE FROM ak_message_recipients 
WHERE message_id IN (
    SELECT id FROM ak_messages 
    WHERE metadata @> '{"test_data": "true"}'
);

DELETE FROM ak_messages 
WHERE metadata @> '{"test_data": "true"}';

DELETE FROM ak_message_group_members 
WHERE group_id IN (
    SELECT id FROM ak_message_groups 
    WHERE name LIKE '%测试%' OR name LIKE '%训练群%' OR name LIKE '%交流群%'
);

DELETE FROM ak_message_groups 
WHERE name LIKE '%测试%' OR name LIKE '%训练群%' OR name LIKE '%交流群%';

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

-- 教师用户消息偏好
INSERT INTO ak_user_message_preferences (
    user_id, 
    global_enabled, 
    push_enabled, 
    email_enabled,
    type_preferences
) VALUES (
    '7bf7378e-a027-473e-97ac-3460ed3f170a'::uuid, 
    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,
    updated_at = now();

-- 学生用户消息偏好
INSERT INTO ak_user_message_preferences (
    user_id, 
    global_enabled, 
    push_enabled, 
    email_enabled,
    type_preferences
) VALUES (
    'eed3824b-bba1-4309-8048-19d17367c084'::uuid, 
    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,
    updated_at = now();

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

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

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

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

-- ===================================================================
-- 3. 创建测试消息数据
-- ===================================================================

-- 获取消息类型ID的临时变量
WITH message_types AS (
    SELECT 
        id,
        code,
        ROW_NUMBER() OVER (ORDER BY code) as rn
    FROM ak_message_types
),
-- 3.1 系统欢迎消息
system_msg AS (
    INSERT INTO ak_messages (
        id, 
        message_type_id, 
        sender_type, 
        sender_name,
        receiver_type, 
        receiver_id, 
        title, 
        content,
        priority, 
        metadata, 
        created_at
    ) 
    SELECT 
        gen_random_uuid(),
        mt.id,
        'system',
        '系统',
        'user',
        'eed3824b-bba1-4309-8048-19d17367c084'::uuid,
        '欢迎使用运动训练平台！',
        '欢迎来到运动训练监测平台！您可以在这里记录训练数据、查看AI分析报告、与老师同学交流。祝您运动愉快！',
        100,
        '{"test_data": "true", "welcome_message": true}'::jsonb,
        now() - INTERVAL '2 days'
    FROM message_types mt WHERE mt.code = 'system'
    RETURNING id, receiver_id
),
system_recipient AS (
    INSERT INTO ak_message_recipients (
        message_id, 
        recipient_type, 
        recipient_id, 
        status, 
        delivered_at, 
        read_at
    )
    SELECT 
        sm.id,
        'user',
        sm.receiver_id,
        'read',
        now() - INTERVAL '2 days',
        now() - INTERVAL '1 day'
    FROM system_msg sm
    RETURNING message_id
),

-- 3.2 设备连接消息
device_msg AS (
    INSERT INTO ak_messages (
        id, 
        message_type_id, 
        sender_type, 
        sender_id, 
        sender_name,
        receiver_type, 
        receiver_id, 
        title, 
        content,
        priority, 
        device_data, 
        metadata, 
        created_at
    ) 
    SELECT 
        gen_random_uuid(),
        mt.id,
        'device',
        gen_random_uuid(),
        '智能手环Pro',
        'user',
        'eed3824b-bba1-4309-8048-19d17367c084'::uuid,
        '设备连接成功',
        '您的智能手环Pro已成功连接，电量85%，可以开始记录运动数据了。',
        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'
    FROM message_types mt WHERE mt.code = 'device'
    RETURNING id, receiver_id
),
device_recipient AS (
    INSERT INTO ak_message_recipients (
        message_id, 
        recipient_type, 
        recipient_id, 
        status, 
        delivered_at, 
        read_at
    )
    SELECT 
        dm.id,
        'user',
        dm.receiver_id,
        'read',
        now() - INTERVAL '1 day',
        now() - INTERVAL '20 hours'
    FROM device_msg dm
    RETURNING message_id
),

-- 3.3 设备低电量警告（未读）
device_alert AS (
    INSERT INTO ak_messages (
        id, 
        message_type_id, 
        sender_type, 
        sender_id, 
        sender_name,
        receiver_type, 
        receiver_id, 
        title, 
        content,
        priority, 
        is_urgent, 
        device_data, 
        metadata, 
        created_at
    ) 
    SELECT 
        gen_random_uuid(),
        mt.id,
        'device',
        gen_random_uuid(),
        '智能手环Pro',
        'user',
        'eed3824b-bba1-4309-8048-19d17367c084'::uuid,
        '⚠️ 设备电量不足',
        '您的智能手环Pro电量仅剩15%，请及时充电以确保正常使用。',
        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'
    FROM message_types mt WHERE mt.code = 'device'
    RETURNING id, receiver_id
),
device_alert_recipient AS (
    INSERT INTO ak_message_recipients (
        message_id, 
        recipient_type, 
        recipient_id, 
        status, 
        delivered_at
    )
    SELECT 
        da.id,
        'user',
        da.receiver_id,
        'delivered',
        now() - INTERVAL '2 hours'
    FROM device_alert da
    RETURNING message_id
),

-- 3.4 训练完成通知
training_msg AS (
    INSERT INTO ak_messages (
        id, 
        message_type_id, 
        sender_type, 
        sender_name,
        receiver_type, 
        receiver_id, 
        title, 
        content,
        priority, 
        metadata, 
        created_at
    ) 
    SELECT 
        gen_random_uuid(),
        mt.id,
        'system',
        '训练助手',
        'user',
        'eed3824b-bba1-4309-8048-19d17367c084'::uuid,
        '🎉 训练完成！',
        '恭喜完成跑步训练！用时25分钟，消耗180卡路里，步数3250步，平均心率145 bpm。您的表现很棒，继续保持！',
        70,
        '{"test_data": "true", "activity_type": "running", "duration": 25, "calories": 180, "steps": 3250, "avg_heart_rate": 145}'::jsonb,
        now() - INTERVAL '3 hours'
    FROM message_types mt WHERE mt.code = 'training'
    RETURNING id, receiver_id
),
training_recipient AS (
    INSERT INTO ak_message_recipients (
        message_id, 
        recipient_type, 
        recipient_id, 
        status, 
        delivered_at, 
        read_at
    )
    SELECT 
        tm.id,
        'user',
        tm.receiver_id,
        'read',
        now() - INTERVAL '3 hours',
        now() - INTERVAL '2 hours'
    FROM training_msg tm
    RETURNING message_id
),

-- 3.5 教师发布作业通知
assignment_msg AS (
    INSERT INTO ak_messages (
        id, 
        message_type_id, 
        sender_type, 
        sender_id, 
        sender_name,
        receiver_type, 
        receiver_id, 
        title, 
        content,
        priority, 
        expires_at, 
        metadata, 
        created_at
    ) 
    SELECT 
        gen_random_uuid(),
        mt.id,
        'user',
        '7bf7378e-a027-473e-97ac-3460ed3f170a'::uuid,
        '李老师',
        'user',
        'eed3824b-bba1-4309-8048-19d17367c084'::uuid,
        '新作业：每日运动打卡',
        '同学们好！本周的运动作业已发布：每天完成30分钟有氧运动，记录心率和步数数据。截止时间：本周日23:59。请按时完成并上传数据。',
        75,
        '2025-06-22 23:59:00'::timestamp with time zone,
        '{"test_data": "true", "assignment_id": "homework_001", "subject": "体育"}'::jsonb,
        now() - INTERVAL '1 day'
    FROM message_types mt WHERE mt.code = 'assignment'
    RETURNING id, receiver_id
),
assignment_recipient AS (
    INSERT INTO ak_message_recipients (
        message_id, 
        recipient_type, 
        recipient_id, 
        status, 
        delivered_at, 
        read_at
    )
    SELECT 
        am.id,
        'user',
        am.receiver_id,
        'read',
        now() - INTERVAL '1 day',
        now() - INTERVAL '18 hours'
    FROM assignment_msg am
    RETURNING message_id
),

-- 3.6 成就解锁通知（未读）
achievement_msg AS (
    INSERT INTO ak_messages (
        id, 
        message_type_id, 
        sender_type, 
        sender_name,
        receiver_type, 
        receiver_id, 
        title, 
        content,
        priority, 
        metadata, 
        created_at
    ) 
    SELECT 
        gen_random_uuid(),
        mt.id,
        'system',
        '成就系统',
        'user',
        'eed3824b-bba1-4309-8048-19d17367c084'::uuid,
        '🏆 恭喜解锁新成就！',
        '恭喜您解锁了"坚持不懈"成就！连续7天完成运动打卡，获得50积分奖励。继续努力，向更高目标前进！',
        50,
        '{"test_data": "true", "achievement_name": "坚持不懈", "points": 50, "days_streak": 7}'::jsonb,
        now() - INTERVAL '6 hours'
    FROM message_types mt WHERE mt.code = 'achievement'
    RETURNING id, receiver_id
),
achievement_recipient AS (
    INSERT INTO ak_message_recipients (
        message_id, 
        recipient_type, 
        recipient_id, 
        status, 
        delivered_at
    )
    SELECT 
        achm.id,
        'user',
        achm.receiver_id,
        'delivered',
        now() - INTERVAL '6 hours'
    FROM achievement_msg achm
    RETURNING message_id
)

-- 查询插入结果统计
SELECT 
    'CTE 批量插入完成' as status,
    (SELECT COUNT(*) FROM ak_messages WHERE metadata @> '{"test_data": "true"}') as total_messages,
    (SELECT COUNT(*) FROM ak_message_recipients mr 
     JOIN ak_messages m ON mr.message_id = m.id 
     WHERE m.metadata @> '{"test_data": "true"}') as total_recipients;

-- ===================================================================
-- 4. 创建群组消息
-- ===================================================================

-- 教师群组消息
WITH group_msg AS (
    INSERT INTO ak_messages (
        id, 
        message_type_id, 
        sender_type, 
        sender_id, 
        sender_name,
        receiver_type, 
        receiver_id, 
        content,
        priority, 
        conversation_id, 
        metadata, 
        created_at
    ) 
    SELECT 
        gen_random_uuid(),
        mt.id,
        'user',
        '7bf7378e-a027-473e-97ac-3460ed3f170a'::uuid,
        '李老师',
        'group',
        'f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid,
        '同学们，下周一我们将进行体能测试，请大家提前做好准备。测试项目包括：50米跑、立定跳远、引体向上。有问题随时问我。',
        90,
        'f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid,
        '{"test_data": "true", "group_message": true}'::jsonb,
        now() - INTERVAL '4 hours'
    FROM ak_message_types mt WHERE mt.code = 'chat'
    RETURNING id
),
group_recipients AS (
    INSERT INTO ak_message_recipients (
        message_id, 
        recipient_type, 
        recipient_id, 
        status, 
        delivered_at, 
        read_at
    )
    SELECT 
        gm.id,
        'user',
        gmem.user_id,
        CASE 
            WHEN gmem.user_id = 'eed3824b-bba1-4309-8048-19d17367c084'::uuid THEN 'read'
            ELSE 'read'
        END,
        now() - INTERVAL '4 hours',
        CASE 
            WHEN gmem.user_id = 'eed3824b-bba1-4309-8048-19d17367c084'::uuid THEN now() - INTERVAL '3 hours'
            ELSE now() - INTERVAL '4 hours'
        END
    FROM group_msg gm
    CROSS JOIN ak_message_group_members gmem
    WHERE gmem.group_id = 'f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid
    RETURNING message_id, recipient_id
)

-- 学生回复群组消息
INSERT INTO ak_messages (
    id, 
    message_type_id, 
    sender_type, 
    sender_id, 
    sender_name,
    receiver_type, 
    receiver_id, 
    content,
    priority, 
    conversation_id, 
    parent_message_id, 
    metadata, 
    created_at
) 
SELECT 
    gen_random_uuid(),
    mt.id,
    'user',
    'eed3824b-bba1-4309-8048-19d17367c084'::uuid,
    '张同学',
    'group',
    'f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid,
    '好的老师，我会认真准备的！请问引体向上有什么技巧吗？',
    90,
    'f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid,
    gm.id,
    '{"test_data": "true", "group_message": true, "reply": true}'::jsonb,
    now() - INTERVAL '3 hours'
FROM group_msg gm
CROSS JOIN ak_message_types mt 
WHERE mt.code = 'chat';

-- 为学生回复创建接收记录
WITH reply_msg AS (
    SELECT id, receiver_id 
    FROM ak_messages 
    WHERE content LIKE '%引体向上有什么技巧%' 
    AND metadata @> '{"test_data": "true"}'
    LIMIT 1
)
INSERT INTO ak_message_recipients (
    message_id, 
    recipient_type, 
    recipient_id, 
    status, 
    delivered_at, 
    read_at
)
SELECT 
    rm.id,
    'user',
    gmem.user_id,
    'read',
    now() - INTERVAL '3 hours',
    CASE 
        WHEN gmem.user_id = '7bf7378e-a027-473e-97ac-3460ed3f170a'::uuid THEN now() - INTERVAL '2 hours'
        ELSE now() - INTERVAL '3 hours'
    END
FROM reply_msg rm
CROSS JOIN ak_message_group_members gmem
WHERE gmem.group_id = 'f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid;

-- ===================================================================
-- 5. 创建更多测试消息
-- ===================================================================

-- 教师私信学生（未读）
WITH private_msg AS (
    INSERT INTO ak_messages (
        id, 
        message_type_id, 
        sender_type, 
        sender_id, 
        sender_name,
        receiver_type, 
        receiver_id, 
        content,
        priority, 
        metadata, 
        created_at
    ) 
    SELECT 
        gen_random_uuid(),
        mt.id,
        'user',
        '7bf7378e-a027-473e-97ac-3460ed3f170a'::uuid,
        '李老师',
        'user',
        'eed3824b-bba1-4309-8048-19d17367c084'::uuid,
        '张同学，我看到你最近的运动数据很不错，心率控制得很好。继续保持这个节奏，相信你的体能会有很大提升。',
        90,
        '{"test_data": "true", "private_message": true}'::jsonb,
        now() - INTERVAL '1 hour'
    FROM ak_message_types mt WHERE mt.code = 'chat'
    RETURNING id, receiver_id
)
INSERT INTO ak_message_recipients (
    message_id, 
    recipient_type, 
    recipient_id, 
    status, 
    delivered_at
)
SELECT 
    pm.id,
    'user',
    pm.receiver_id,
    'delivered',
    now() - INTERVAL '1 hour'
FROM private_msg pm;

-- 系统公告（广播消息）
WITH announcement_msg AS (
    INSERT INTO ak_messages (
        id, 
        message_type_id, 
        sender_type, 
        sender_name,
        receiver_type, 
        title, 
        content,
        priority, 
        is_broadcast, 
        metadata, 
        created_at
    ) 
    SELECT 
        gen_random_uuid(),
        mt.id,
        'system',
        '运动平台',
        'broadcast',
        '平台升级通知',
        '各位用户，运动训练平台将于本周六凌晨2:00-4:00进行系统升级，届时将暂停服务。升级后将新增AI运动分析功能，敬请期待！',
        95,
        true,
        '{"test_data": "true", "maintenance": true, "upgrade": true}'::jsonb,
        now() - INTERVAL '12 hours'
    FROM ak_message_types mt WHERE mt.code = 'announcement'
    RETURNING id
)
INSERT INTO ak_message_recipients (
    message_id, 
    recipient_type, 
    recipient_id, 
    status, 
    delivered_at, 
    read_at
)
SELECT 
    am.id,
    'user',
    users.user_id,
    'read',
    now() - INTERVAL '12 hours',
    now() - INTERVAL '10 hours'
FROM announcement_msg am
CROSS JOIN (
    VALUES 
    ('7bf7378e-a027-473e-97ac-3460ed3f170a'::uuid),
    ('eed3824b-bba1-4309-8048-19d17367c084'::uuid)
) AS users(user_id);

-- 运动提醒（未读）
WITH reminder_msg AS (
    INSERT INTO ak_messages (
        id, 
        message_type_id, 
        sender_type, 
        sender_name,
        receiver_type, 
        receiver_id, 
        title, 
        content,
        priority, 
        scheduled_at, 
        metadata, 
        created_at
    ) 
    SELECT 
        gen_random_uuid(),
        mt.id,
        'system',
        '运动助手',
        'user',
        'eed3824b-bba1-4309-8048-19d17367c084'::uuid,
        '⏰ 运动时间到了！',
        '根据您的运动计划，现在是跑步时间。建议运动30分钟，注意热身和拉伸。今天天气不错，适合户外运动！',
        65,
        now() - INTERVAL '30 minutes',
        '{"test_data": "true", "reminder_type": "exercise", "activity": "running"}'::jsonb,
        now() - INTERVAL '30 minutes'
    FROM ak_message_types mt WHERE mt.code = 'reminder'
    RETURNING id, receiver_id
)
INSERT INTO ak_message_recipients (
    message_id, 
    recipient_type, 
    recipient_id, 
    status, 
    delivered_at
)
SELECT 
    rm.id,
    'user',
    rm.receiver_id,
    'delivered',
    now() - INTERVAL '30 minutes'
FROM reminder_msg rm;

-- 心率异常警报
WITH alert_msg AS (
    INSERT INTO ak_messages (
        id, 
        message_type_id, 
        sender_type, 
        sender_id, 
        sender_name,
        receiver_type, 
        receiver_id, 
        title, 
        content,
        priority, 
        is_urgent, 
        device_data, 
        metadata, 
        created_at
    ) 
    SELECT 
        gen_random_uuid(),
        mt.id,
        'device',
        gen_random_uuid(),
        '智能手环Pro',
        'user',
        'eed3824b-bba1-4309-8048-19d17367c084'::uuid,
        '⚠️ 心率异常警报',
        '检测到您的心率持续偏高（185 bpm），已超过安全范围。建议立即停止运动，休息并观察。如有不适请及时就医。',
        99,
        true,
        '{"device_name": "智能手环Pro", "heart_rate": 185, "safe_range": "60-180"}'::jsonb,
        '{"test_data": "true", "alert_type": "heart_rate", "severity": "high"}'::jsonb,
        now() - INTERVAL '10 minutes'
    FROM ak_message_types mt WHERE mt.code = 'alert'
    RETURNING id, receiver_id
)
INSERT INTO ak_message_recipients (
    message_id, 
    recipient_type, 
    recipient_id, 
    status, 
    delivered_at, 
    read_at
)
SELECT 
    alm.id,
    'user',
    alm.receiver_id,
    'read',
    now() - INTERVAL '10 minutes',
    now() - INTERVAL '8 minutes'
FROM alert_msg alm;

-- 社交点赞消息
WITH social_msg AS (
    INSERT INTO ak_messages (
        id, 
        message_type_id, 
        sender_type, 
        sender_id, 
        sender_name,
        receiver_type, 
        receiver_id, 
        title, 
        content,
        priority, 
        metadata, 
        created_at
    ) 
    SELECT 
        gen_random_uuid(),
        mt.id,
        'user',
        '7bf7378e-a027-473e-97ac-3460ed3f170a'::uuid,
        '李老师',
        'user',
        'eed3824b-bba1-4309-8048-19d17367c084'::uuid,
        '👍 老师为您点赞了',
        '李老师为您的运动记录"今日跑步5公里"点赞了，并评论："坚持得很好，继续加油！"',
        60,
        '{"test_data": "true", "social_action": "like", "post_id": "post_001"}'::jsonb,
        now() - INTERVAL '5 hours'
    FROM ak_message_types mt WHERE mt.code = 'social'
    RETURNING id, receiver_id
)
INSERT INTO ak_message_recipients (
    message_id, 
    recipient_type, 
    recipient_id, 
    status, 
    delivered_at, 
    read_at
)
SELECT 
    sm.id,
    'user',
    sm.receiver_id,
    'read',
    now() - INTERVAL '5 hours',
    now() - INTERVAL '4 hours'
FROM social_msg sm;

-- ===================================================================
-- 6. 更新消息统计
-- ===================================================================

-- 更新所有测试消息的统计数据
UPDATE ak_messages 
SET 
    total_recipients = (
        SELECT COUNT(*) 
        FROM ak_message_recipients mr 
        WHERE mr.message_id = ak_messages.id
    ),
    delivered_count = (
        SELECT COUNT(*) 
        FROM ak_message_recipients mr 
        WHERE mr.message_id = ak_messages.id 
        AND mr.status IN ('delivered', 'read')
    ),
    read_count = (
        SELECT COUNT(*) 
        FROM ak_message_recipients mr 
        WHERE mr.message_id = ak_messages.id 
        AND mr.status = 'read'
    ),
    updated_at = now()
WHERE metadata @> '{"test_data": "true"}';

-- ===================================================================
-- 7. 创建一些历史统计数据
-- ===================================================================

INSERT INTO ak_message_stats (
    date_bucket, 
    hour_bucket, 
    message_type_id, 
    sender_type,
    total_sent, 
    total_delivered, 
    total_read, 
    total_replied
)
SELECT 
    date_series.date_bucket,
    hour_series.hour_bucket,
    mt.id,
    sender_types.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
) date_series
CROSS JOIN (
    SELECT generate_series(8, 22) as hour_bucket
) hour_series
CROSS JOIN (
    SELECT UNNEST(ARRAY['user', 'system', 'device']) as sender_type
) sender_types
CROSS JOIN ak_message_types mt
WHERE mt.is_active = true
  AND RANDOM() > 0.3  -- 随机生成70%的数据
ON CONFLICT (date_bucket, COALESCE(hour_bucket, -1), COALESCE(message_type_id::text, ''), COALESCE(sender_type, '')) DO NOTHING;

-- ===================================================================
-- 8. 输出测试数据统计
-- ===================================================================

-- 最终统计报告
WITH stats AS (
    SELECT 
        (SELECT COUNT(*) FROM ak_messages WHERE metadata @> '{"test_data": "true"}') as total_messages,
        (SELECT COUNT(*) FROM ak_message_recipients mr 
         JOIN ak_messages m ON mr.message_id = m.id 
         WHERE m.metadata @> '{"test_data": "true"}') as total_recipients,
        (SELECT COUNT(*) FROM ak_message_recipients mr 
         JOIN ak_messages m ON mr.message_id = m.id 
         WHERE m.metadata @> '{"test_data": "true"}' 
           AND mr.recipient_id = 'eed3824b-bba1-4309-8048-19d17367c084'::uuid
           AND mr.status != 'read') as student_unread_count,
        (SELECT COUNT(*) FROM ak_message_groups 
         WHERE name LIKE '%测试%' OR name LIKE '%群%') as test_groups,
        (SELECT COUNT(*) FROM ak_message_stats 
         WHERE created_at >= CURRENT_DATE - INTERVAL '1 day') as stats_records
)
SELECT 
    '====================================================================' as separator
UNION ALL
SELECT '✅ 测试数据生成完成！' as info
UNION ALL
SELECT '📊 数据统计：' as info
UNION ALL
SELECT '  - 总消息数：' || s.total_messages as info FROM stats s
UNION ALL
SELECT '  - 总接收记录数：' || s.total_recipients as info FROM stats s
UNION ALL
SELECT '  - 学生未读消息数：' || s.student_unread_count as info FROM stats s
UNION ALL
SELECT '  - 测试群组数：' || s.test_groups as info FROM stats s
UNION ALL
SELECT '  - 统计记录数：' || s.stats_records as info FROM stats s
UNION ALL
SELECT '====================================================================' as separator
UNION ALL
SELECT '👥 测试用户：' as info
UNION ALL
SELECT '  - 教师ID: 7bf7378e-a027-473e-97ac-3460ed3f170a' as info
UNION ALL
SELECT '  - 学生ID: eed3824b-bba1-4309-8048-19d17367c084' as info
UNION ALL
SELECT '====================================================================' as separator
UNION ALL
SELECT '🏠 测试群组：' as info
UNION ALL
SELECT '  - 班级群组ID: f47ac10b-58cc-4372-a567-0e02b2c3d479' as info
UNION ALL
SELECT '  - 师生群组ID: a1b2c3d4-e5f6-7890-abcd-ef1234567890' as info
UNION ALL
SELECT '====================================================================' as separator
UNION ALL
SELECT '🎯 包含的消息类型：' as info
UNION ALL
SELECT '  ✓ 系统欢迎消息（已读）' as info
UNION ALL
SELECT '  ✓ 设备连接成功（已读）' as info
UNION ALL
SELECT '  ⚠️ 设备低电量警告（未读）' as info
UNION ALL
SELECT '  ✓ 训练完成通知（已读）' as info
UNION ALL
SELECT '  ✓ 作业发布通知（已读）' as info
UNION ALL
SELECT '  🏆 成就解锁通知（未读）' as info
UNION ALL
SELECT '  💬 群组消息和回复（已读）' as info
UNION ALL
SELECT '  📩 教师私信（未读）' as info
UNION ALL
SELECT '  📢 系统公告（已读）' as info
UNION ALL
SELECT '  ⏰ 运动提醒（未读）' as info
UNION ALL
SELECT '  🚨 心率异常警报（已读）' as info
UNION ALL
SELECT '  👍 社交点赞消息（已读）' as info
UNION ALL
SELECT '====================================================================' as separator;

-- ===================================================================
-- 9. 测试查询示例（注释形式）
-- ===================================================================

/*
-- 查询学生的所有消息（按时间倒序）
SELECT 
    m.title,
    m.content,
    mt.name as message_type,
    m.sender_name,
    mr.status,
    m.created_at,
    m.is_urgent,
    m.priority
FROM ak_messages m
JOIN ak_message_types mt ON m.message_type_id = mt.id
JOIN ak_message_recipients mr ON m.id = mr.message_id
WHERE mr.recipient_id = 'eed3824b-bba1-4309-8048-19d17367c084'::uuid
  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.sender_name,
    m.created_at,
    m.is_urgent,
    m.priority
FROM ak_messages m
JOIN ak_message_types mt ON m.message_type_id = mt.id
JOIN ak_message_recipients mr ON m.id = mr.message_id
WHERE mr.recipient_id = 'eed3824b-bba1-4309-8048-19d17367c084'::uuid
  AND mr.recipient_type = 'user'
  AND mr.status != 'read'
  AND m.metadata @> '{"test_data": "true"}'
ORDER BY m.priority DESC, 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 ak_messages m
JOIN ak_message_types mt ON m.message_type_id = mt.id
WHERE m.sender_id = '7bf7378e-a027-473e-97ac-3460ed3f170a'::uuid
  AND m.metadata @> '{"test_data": "true"}'
ORDER BY m.created_at DESC;

-- 查询群组消息
SELECT 
    m.content,
    m.sender_name,
    mg.name as group_name,
    m.created_at,
    CASE WHEN m.parent_message_id IS NOT NULL THEN '回复' ELSE '原始消息' END as message_type
FROM ak_messages m
JOIN 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 
    m.title,
    m.content,
    m.device_data,
    m.is_urgent,
    mr.status,
    m.created_at
FROM ak_messages m
JOIN ak_message_recipients mr ON m.id = mr.message_id
WHERE m.sender_type = 'device'
  AND mr.recipient_id = 'eed3824b-bba1-4309-8048-19d17367c084'::uuid
  AND m.metadata @> '{"test_data": "true"}'
ORDER BY m.created_at DESC;

-- 查询未读消息统计
SELECT 
    mt.name as message_type,
    COUNT(*) as unread_count
FROM ak_message_recipients mr
JOIN ak_messages m ON mr.message_id = m.id
JOIN ak_message_types mt ON m.message_type_id = mt.id
WHERE mr.recipient_id = 'eed3824b-bba1-4309-8048-19d17367c084'::uuid
  AND mr.recipient_type = 'user'
  AND mr.status != 'read'
  AND m.metadata @> '{"test_data": "true"}'
GROUP BY mt.name, mt.priority
ORDER BY mt.priority DESC;

-- 使用系统函数查询未读消息统计
SELECT * FROM get_unread_message_count('eed3824b-bba1-4309-8048-19d17367c084'::uuid);

-- 标记消息为已读的示例
SELECT mark_message_read(
    (SELECT id FROM ak_messages WHERE title = '⚠️ 设备电量不足' AND metadata @> '{"test_data": "true"}' LIMIT 1),
    'eed3824b-bba1-4309-8048-19d17367c084'::uuid
);

-- 发送新消息的示例
SELECT send_message(
    'system',
    'system',
    NULL,
    'user',
    'eed3824b-bba1-4309-8048-19d17367c084'::uuid,
    '测试消息',
    '这是一条通过函数发送的测试消息',
    NULL,
    NULL,
    50,
    '{"test_data": "true", "manual_test": true}'::jsonb
);
*/