142 lines
10 KiB
PL/PgSQL
142 lines
10 KiB
PL/PgSQL
-- Mock chat data for local development and demos
|
||
-- Uses deterministic UUIDs so the dataset can be reapplied safely
|
||
-- Assumes chat schema from doc_chat/create_chat_tables.sql is already deployed
|
||
-- Primary tester account (parent side): eed3824b-bba1-4309-8048-19d17367c084
|
||
-- Homeroom teacher account: e95b36ab-6cc8-4b69-8d23-286f98fe79ce
|
||
-- Demo student account: 0122a2e6-c412-4e9a-9ad5-403e0048333c
|
||
|
||
begin;
|
||
|
||
-- Optional cleanup so script is idempotent
|
||
delete from chat_notifications where conversation_id = any(array[
|
||
'0f2c8f30-9f0a-4a7b-8b75-9d41c4f20101'::uuid,
|
||
'0f2c8f30-9f0a-4a7b-8b75-9d41c4f20202'::uuid
|
||
]);
|
||
|
||
delete from chat_messages where conversation_id = any(array[
|
||
'0f2c8f30-9f0a-4a7b-8b75-9d41c4f20101'::uuid,
|
||
'0f2c8f30-9f0a-4a7b-8b75-9d41c4f20202'::uuid
|
||
]);
|
||
|
||
delete from chat_participants where conversation_id = any(array[
|
||
'0f2c8f30-9f0a-4a7b-8b75-9d41c4f20101'::uuid,
|
||
'0f2c8f30-9f0a-4a7b-8b75-9d41c4f20202'::uuid
|
||
]);
|
||
|
||
delete from chat_conversations where id = any(array[
|
||
'0f2c8f30-9f0a-4a7b-8b75-9d41c4f20101'::uuid,
|
||
'0f2c8f30-9f0a-4a7b-8b75-9d41c4f20202'::uuid
|
||
]);
|
||
|
||
delete from chat_mqtt_downlinks where conversation_id = any(array[
|
||
'0f2c8f30-9f0a-4a7b-8b75-9d41c4f20101'::uuid,
|
||
'0f2c8f30-9f0a-4a7b-8b75-9d41c4f20202'::uuid
|
||
]);
|
||
|
||
delete from chat_gateway_heartbeats where gateway_id = '7f8e2e9c-62c3-4fb5-9a4f-18aa0a118001';
|
||
delete from chat_gateway_nodes where id = '7f8e2e9c-62c3-4fb5-9a4f-18aa0a118001';
|
||
|
||
-- Conversations
|
||
insert into chat_conversations (id, title, is_group, owner_id, last_message_at, metadata, created_at, updated_at)
|
||
values
|
||
('0f2c8f30-9f0a-4a7b-8b75-9d41c4f20101', null, false, 'e95b36ab-6cc8-4b69-8d23-286f98fe79ce',
|
||
'2025-09-27 09:05:00+08'::timestamptz, '{"context":"direct_support"}'::jsonb,
|
||
'2025-09-26 08:55:00+08'::timestamptz, '2025-09-27 09:05:00+08'::timestamptz),
|
||
('0f2c8f30-9f0a-4a7b-8b75-9d41c4f20202', '体能训练筹备群', true, '05b40c9c-6b2d-4f1a-9d84-d1f4db1a0004',
|
||
'2025-09-28 08:10:45+08'::timestamptz, '{"sport":"basketball","tags":["training","weekly"]}'::jsonb,
|
||
'2025-09-26 12:10:00+08'::timestamptz, '2025-09-28 08:10:45+08'::timestamptz);
|
||
|
||
-- Participants
|
||
insert into chat_participants (id, conversation_id, user_id, role, joined_at, settings)
|
||
values
|
||
('8a111111-2222-4333-8444-555555555501', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20101', 'e95b36ab-6cc8-4b69-8d23-286f98fe79ce', 'owner',
|
||
'2025-09-26 08:55:00+08'::timestamptz, '{"notifications":"all"}'::jsonb),
|
||
('8a111111-2222-4333-8444-555555555502', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20101', 'eed3824b-bba1-4309-8048-19d17367c084', 'member',
|
||
'2025-09-26 08:55:30+08'::timestamptz, '{"notifications":"mentions"}'::jsonb),
|
||
('8a111111-2222-4333-8444-555555555503', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20202', '05b40c9c-6b2d-4f1a-9d84-d1f4db1a0004', 'owner',
|
||
'2025-09-26 12:10:00+08'::timestamptz, '{"pin":true}'::jsonb),
|
||
('8a111111-2222-4333-8444-555555555504', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20202', '0122a2e6-c412-4e9a-9ad5-403e0048333c', 'member',
|
||
'2025-09-26 12:10:30+08'::timestamptz, '{"color":"teal"}'::jsonb),
|
||
('8a111111-2222-4333-8444-555555555505', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20202', 'eed3824b-bba1-4309-8048-19d17367c084', 'admin',
|
||
'2025-09-26 12:11:00+08'::timestamptz, '{"notifications":"digest"}'::jsonb);
|
||
|
||
-- Messages (chronological order per conversation)
|
||
insert into chat_messages (id, conversation_id, sender_id, content, content_type, ingress_type, reply_to, metadata, created_at, updated_at)
|
||
values
|
||
('6c70f6a3-87f0-4685-b83f-99180a220101', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20101', 'e95b36ab-6cc8-4b69-8d23-286f98fe79ce',
|
||
'早上好 Bob，下午的体能课记得提前 10 分钟到场。', 'text', 'manual', null, '{"tone":"reminder"}'::jsonb,
|
||
'2025-09-27 09:02:00+08'::timestamptz, '2025-09-27 09:02:00+08'::timestamptz),
|
||
('6c70f6a3-87f0-4685-b83f-99180a220102', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20101', 'eed3824b-bba1-4309-8048-19d17367c084',
|
||
'收到！我会提前去热身，还需要带什么器材吗？', 'text', 'manual', '6c70f6a3-87f0-4685-b83f-99180a220101', null,
|
||
'2025-09-27 09:03:00+08'::timestamptz, '2025-09-27 09:03:00+08'::timestamptz),
|
||
('6c70f6a3-87f0-4685-b83f-99180a220103', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20101', 'e95b36ab-6cc8-4b69-8d23-286f98fe79ce',
|
||
'自备护腕就行，训练结束后我们复盘数据。', 'text', 'manual', '6c70f6a3-87f0-4685-b83f-99180a220102', '{"follow_up":true}'::jsonb,
|
||
'2025-09-27 09:05:00+08'::timestamptz, '2025-09-27 09:05:00+08'::timestamptz),
|
||
('6c70f6a3-87f0-4685-b83f-99180a220201', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20202', '05b40c9c-6b2d-4f1a-9d84-d1f4db1a0004',
|
||
'本周训练安排：周二体能、周四对抗、周六拉伸，附件里有详细指标。', 'markdown', 'manual', null,
|
||
'{"attachments":[{"type":"sheet","name":"week39-plan.xlsx"}]}'::jsonb,
|
||
'2025-09-28 07:30:00+08'::timestamptz, '2025-09-28 07:30:00+08'::timestamptz),
|
||
('6c70f6a3-87f0-4685-b83f-99180a220202', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20202', '0122a2e6-c412-4e9a-9ad5-403e0048333c',
|
||
'收到训练表，周二我带上心率带。', 'text', 'manual', '6c70f6a3-87f0-4685-b83f-99180a220201', '{"reaction":"thumbs_up"}'::jsonb,
|
||
'2025-09-28 08:00:00+08'::timestamptz, '2025-09-28 08:00:00+08'::timestamptz),
|
||
('6c70f6a3-87f0-4685-b83f-99180a220203', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20202', '0122a2e6-c412-4e9a-9ad5-403e0048333c',
|
||
'http://ak3.oulog.com:8000/storage/v1/object/public/chat-audio/voices/bluetooth/2025/09/28/voice-bt-080512.ogg', 'audio', 'bluetooth', null,
|
||
'{"storage_bucket":"chat-audio","object_path":"voices/bluetooth/2025/09/28/voice-bt-080512.ogg","public_url":"http://ak3.oulog.com:8000/storage/v1/object/public/chat-audio/voices/bluetooth/2025/09/28/voice-bt-080512.ogg","duration_sec":32,"codec":"opus"}'::jsonb,
|
||
'2025-09-28 08:05:12+08'::timestamptz, '2025-09-28 08:05:12+08'::timestamptz),
|
||
('6c70f6a3-87f0-4685-b83f-99180a220204', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20202', '0122a2e6-c412-4e9a-9ad5-403e0048333c',
|
||
'http://ak3.oulog.com:8000/storage/v1/object/public/chat-audio/voices/gateway/2025/09/28/voice-gw-081045.ogg', 'audio', 'school_gateway', null,
|
||
'{"gateway_id":"chat-gw-sports-a","storage_bucket":"chat-audio","object_path":"voices/gateway/2025/09/28/voice-gw-081045.ogg","public_url":"http://ak3.oulog.com:8000/storage/v1/object/public/chat-audio/voices/gateway/2025/09/28/voice-gw-081045.ogg","duration_sec":27,"codec":"aac"}'::jsonb,
|
||
'2025-09-28 08:10:45+08'::timestamptz, '2025-09-28 08:10:45+08'::timestamptz);
|
||
|
||
-- Notifications (unread + read samples)
|
||
insert into chat_notifications (id, user_id, conversation_id, message_id, type, is_read, created_at)
|
||
values
|
||
('4f3a0a10-1b84-4f7e-8ec3-111100000001', 'eed3824b-bba1-4309-8048-19d17367c084', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20101', '6c70f6a3-87f0-4685-b83f-99180a220101', 'message', true,
|
||
'2025-09-27 09:02:05+08'::timestamptz),
|
||
('4f3a0a10-1b84-4f7e-8ec3-111100000002', 'e95b36ab-6cc8-4b69-8d23-286f98fe79ce', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20101', '6c70f6a3-87f0-4685-b83f-99180a220102', 'message', true,
|
||
'2025-09-27 09:03:10+08'::timestamptz),
|
||
('4f3a0a10-1b84-4f7e-8ec3-111100000003', 'eed3824b-bba1-4309-8048-19d17367c084', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20101', '6c70f6a3-87f0-4685-b83f-99180a220103', 'message', false,
|
||
'2025-09-27 09:05:05+08'::timestamptz),
|
||
('4f3a0a10-1b84-4f7e-8ec3-111100000004', '0122a2e6-c412-4e9a-9ad5-403e0048333c', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20202', '6c70f6a3-87f0-4685-b83f-99180a220201', 'message', false,
|
||
'2025-09-28 07:30:15+08'::timestamptz),
|
||
('4f3a0a10-1b84-4f7e-8ec3-111100000005', 'eed3824b-bba1-4309-8048-19d17367c084', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20202', '6c70f6a3-87f0-4685-b83f-99180a220201', 'message', false,
|
||
'2025-09-28 07:30:15+08'::timestamptz),
|
||
('4f3a0a10-1b84-4f7e-8ec3-111100000006', '05b40c9c-6b2d-4f1a-9d84-d1f4db1a0004', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20202', '6c70f6a3-87f0-4685-b83f-99180a220202', 'message', true,
|
||
'2025-09-28 08:00:10+08'::timestamptz),
|
||
('4f3a0a10-1b84-4f7e-8ec3-111100000007', '05b40c9c-6b2d-4f1a-9d84-d1f4db1a0004', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20202', '6c70f6a3-87f0-4685-b83f-99180a220203', 'message', false,
|
||
'2025-09-28 08:05:15+08'::timestamptz),
|
||
('4f3a0a10-1b84-4f7e-8ec3-111100000008', 'eed3824b-bba1-4309-8048-19d17367c084', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20202', '6c70f6a3-87f0-4685-b83f-99180a220203', 'message', false,
|
||
'2025-09-28 08:05:15+08'::timestamptz),
|
||
('4f3a0a10-1b84-4f7e-8ec3-111100000009', '05b40c9c-6b2d-4f1a-9d84-d1f4db1a0004', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20202', '6c70f6a3-87f0-4685-b83f-99180a220204', 'message', false,
|
||
'2025-09-28 08:10:50+08'::timestamptz),
|
||
('4f3a0a10-1b84-4f7e-8ec3-11110000000a', 'eed3824b-bba1-4309-8048-19d17367c084', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20202', '6c70f6a3-87f0-4685-b83f-99180a220204', 'message', false,
|
||
'2025-09-28 08:10:50+08'::timestamptz);
|
||
|
||
-- Sample MQTT downlink tied to training reminder
|
||
insert into chat_mqtt_downlinks (id, conversation_id, target_user_id, topic, payload, payload_encoding, qos, retain, status,
|
||
scheduled_at, expires_at, created_by, created_at, updated_at, metadata)
|
||
values
|
||
('3d8e9956-1f35-4d34-a1f4-8d061df30001', '0f2c8f30-9f0a-4a7b-8b75-9d41c4f20202', 'eed3824b-bba1-4309-8048-19d17367c084',
|
||
'device/guardian-eed3824b/down', '{"type":"reminder","title":"周二体能课","start":"2025-09-30T14:00:00+08:00"}',
|
||
'json', 1, false, 'pending',
|
||
'2025-09-29 09:00:00+08'::timestamptz, '2025-09-30 12:00:00+08'::timestamptz,
|
||
'05b40c9c-6b2d-4f1a-9d84-d1f4db1a0004', '2025-09-28 08:05:00+08'::timestamptz, '2025-09-28 08:05:00+08'::timestamptz,
|
||
'{"priority":"high"}'::jsonb);
|
||
|
||
-- Gateway node + latest heartbeat for dashboard demos
|
||
insert into chat_gateway_nodes (id, name, mqtt_client_id, version, region, tags)
|
||
values
|
||
('7f8e2e9c-62c3-4fb5-9a4f-18aa0a118001', '体育馆边缘网关 A', 'chat-gw-sports-a', '1.4.2', 'cn-south', '{"rack":"gym-east","roles":["mqtt","kafka"]}'::jsonb);
|
||
|
||
insert into chat_gateway_heartbeats (id, gateway_id, created_at, uptime_sec, mem_rss_mb, heap_used_mb,
|
||
mqtt_connected, kafka_connected, redis_connected,
|
||
msgs_in, msgs_out, msgs_dropped, errors, acl_denied, kafka_produced, extra)
|
||
values
|
||
('9a3f5c12-2c6f-4b92-9e36-81f2f1010001', '7f8e2e9c-62c3-4fb5-9a4f-18aa0a118001',
|
||
'2025-09-28 08:02:00+08'::timestamptz, 86400, 612, 248,
|
||
true, true, true,
|
||
1280, 1264, 3, 1, 0, 1248,
|
||
'{"cpu_load":0.53,"latency_ms":42}'::jsonb);
|
||
|
||
commit;
|