-- ===================================================================
-- 消息系统表结构验证和简单测试脚本
-- 用于验证修复后的 generate_message_data.sql 是否可以正常执行
-- ===================================================================

-- 检查消息类型表是否存在
SELECT 'ak_message_types表检查' as check_name, 
       CASE WHEN EXISTS (SELECT 1 FROM information_schema.tables 
                        WHERE table_name = 'ak_message_types') 
            THEN '✓ 表存在' 
            ELSE '✗ 表不存在' 
       END as result;

-- 检查消息主表是否存在
SELECT 'ak_messages表检查' as check_name,
       CASE WHEN EXISTS (SELECT 1 FROM information_schema.tables 
                        WHERE table_name = 'ak_messages') 
            THEN '✓ 表存在' 
            ELSE '✗ 表不存在' 
       END as result;

-- 检查消息接收者表是否存在
SELECT 'ak_message_recipients表检查' as check_name,
       CASE WHEN EXISTS (SELECT 1 FROM information_schema.tables 
                        WHERE table_name = 'ak_message_recipients') 
            THEN '✓ 表存在' 
            ELSE '✗ 表不存在' 
       END as result;

-- 检查消息类型数据
SELECT 'message_types数据检查' as check_name,
       CASE WHEN EXISTS (SELECT 1 FROM ak_message_types WHERE code = 'system') 
            THEN '✓ 系统消息类型存在' 
            ELSE '✗ 系统消息类型不存在' 
       END as result;

-- 显示现有消息类型
SELECT '现有消息类型' as info, code, name FROM ak_message_types ORDER BY priority DESC;

-- 检查ak_message_recipients表的列结构
SELECT '接收者表列结构' as info, column_name, data_type, is_nullable 
FROM information_schema.columns 
WHERE table_name = 'ak_message_recipients' 
  AND column_name IN ('id', 'message_id', 'recipient_id', 'recipient_type', 'status', 'read_duration_sec', 'created_at', 'updated_at')
ORDER BY ordinal_position;

-- 检查ak_messages表的列结构
SELECT '消息表列结构' as info, column_name, data_type, is_nullable 
FROM information_schema.columns 
WHERE table_name = 'ak_messages' 
  AND column_name IN ('id', 'message_type_id', 'sender_id', 'sender_type', 'title', 'content', 'priority', 'created_at', 'updated_at', 'metadata')
ORDER BY ordinal_position;

-- 简单插入测试
INSERT INTO ak_messages (id, message_type_id, sender_id, sender_type, title, content, priority, created_at, updated_at, metadata)
VALUES (
    gen_random_uuid(), 
    (SELECT id FROM ak_message_types WHERE code = 'system'), 
    '00000000-0000-0000-0000-000000000001'::uuid, 
    'system', 
    '测试消息', 
    '这是一条测试消息，用于验证表结构是否正确。', 
    90,
    now(), 
    now(), 
    '{"test": true}'::jsonb
) RETURNING id, title, created_at;

-- 显示插入的测试消息
SELECT '插入测试结果' as info, m.title, mt.code as message_type, m.sender_type, m.priority, m.created_at
FROM ak_messages m
JOIN ak_message_types mt ON m.message_type_id = mt.id
WHERE m.title = '测试消息';

-- 清理测试数据
DELETE FROM ak_messages WHERE title = '测试消息';