-- ===================================================================
-- 消息系统表结构更新脚本
-- 用于在表已存在的情况下，确保数据结构完整和数据正确
-- ===================================================================

-- 1. 更新消息类型数据（如果表已存在）
-- 使用 INSERT ... ON CONFLICT 来安全地插入或更新数据

INSERT INTO ak_message_types (code, name, description, icon, color, priority, is_system, auto_read_timeout, retention_days) 
VALUES
    ('system', '系统消息', '系统自动发送的重要通知', 'system', '#2196F3', 100, true, 0, 90),
    ('device', '设备消息', '设备发送的状态和数据消息', 'device', '#FF5722', 80, true, 300, 7),
    ('training', '训练提醒', '训练计划和完成情况提醒', 'training', '#4CAF50', 70, true, 0, 30),
    ('social', '社交消息', '用户间的社交互动消息', 'social', '#E91E63', 60, false, 0, 30),
    ('assignment', '作业通知', '作业发布、截止、批改通知', 'assignment', '#FF9800', 75, true, 0, 60),
    ('achievement', '成就通知', '积分、成就、奖励通知', 'achievement', '#FFC107', 50, true, 3600, 90),
    ('chat', '即时消息', '用户间的实时聊天消息', 'chat', '#9C27B0', 90, false, 0, 7),
    ('announcement', '公告通知', '系统公告和重要通知', 'announcement', '#3F51B5', 95, true, 0, 180),
    ('reminder', '提醒消息', '定时提醒和事件提醒', 'reminder', '#00BCD4', 65, true, 0, 14),
    ('alert', '警报消息', '紧急情况和异常状态警报', 'alert', '#F44336', 99, true, 0, 30)
ON CONFLICT (code) DO UPDATE SET
    name = EXCLUDED.name,
    description = EXCLUDED.description,
    icon = EXCLUDED.icon,
    color = EXCLUDED.color,
    priority = EXCLUDED.priority,
    is_system = EXCLUDED.is_system,
    auto_read_timeout = EXCLUDED.auto_read_timeout,
    retention_days = EXCLUDED.retention_days,
    updated_at = now();

-- 2. 检查并创建缺失的列（如果需要）
-- 注意：这些语句只在相应列不存在时执行

-- 为 ak_message_types 添加可能缺失的列
ALTER TABLE ak_message_types ADD COLUMN IF NOT EXISTS icon VARCHAR(64);
ALTER TABLE ak_message_types ADD COLUMN IF NOT EXISTS color VARCHAR(16);
ALTER TABLE ak_message_types ADD COLUMN IF NOT EXISTS priority INT DEFAULT 0;
ALTER TABLE ak_message_types ADD COLUMN IF NOT EXISTS auto_read_timeout INT;
ALTER TABLE ak_message_types ADD COLUMN IF NOT EXISTS retention_days INT DEFAULT 30;

-- 3. 创建缺失的索引（如果需要）
-- 使用 IF NOT EXISTS 避免重复创建

-- ak_messages 表的索引
CREATE INDEX IF NOT EXISTS idx_ak_messages_type ON ak_messages(message_type_id);
CREATE INDEX IF NOT EXISTS idx_ak_messages_sender ON ak_messages(sender_type, sender_id);
CREATE INDEX IF NOT EXISTS idx_ak_messages_receiver ON ak_messages(receiver_type, receiver_id);
CREATE INDEX IF NOT EXISTS idx_ak_messages_status ON ak_messages(status);
CREATE INDEX IF NOT EXISTS idx_ak_messages_created_at ON ak_messages(created_at);
CREATE INDEX IF NOT EXISTS idx_ak_messages_scheduled_at ON ak_messages(scheduled_at);
CREATE INDEX IF NOT EXISTS idx_ak_messages_urgent ON ak_messages(is_urgent) WHERE is_urgent = true;

-- ak_message_recipients 表的索引
CREATE INDEX IF NOT EXISTS idx_ak_message_recipients_message ON ak_message_recipients(message_id);
CREATE INDEX IF NOT EXISTS idx_ak_message_recipients_receiver ON ak_message_recipients(receiver_type, receiver_id);
CREATE INDEX IF NOT EXISTS idx_ak_message_recipients_status ON ak_message_recipients(status);
CREATE INDEX IF NOT EXISTS idx_ak_message_recipients_unread ON ak_message_recipients(receiver_type, receiver_id, status) WHERE status = 'unread';

-- ak_message_groups 表的索引（如果表存在）
CREATE INDEX IF NOT EXISTS idx_ak_message_groups_active ON ak_message_groups(is_active);
CREATE INDEX IF NOT EXISTS idx_ak_message_groups_type ON ak_message_groups(group_type);

-- ak_message_group_members 表的索引（如果表存在）
CREATE INDEX IF NOT EXISTS idx_ak_message_group_members_group ON ak_message_group_members(group_id);
CREATE INDEX IF NOT EXISTS idx_ak_message_group_members_user ON ak_message_group_members(user_id);
CREATE INDEX IF NOT EXISTS idx_ak_message_group_members_active ON ak_message_group_members(group_id, is_active);

-- 4. 创建或替换有用的视图和函数

-- 消息统计视图
CREATE OR REPLACE VIEW v_message_stats AS
SELECT 
    receiver_type,
    receiver_id,
    COUNT(*) as total_messages,
    COUNT(CASE WHEN mr.status = 'unread' THEN 1 END) as unread_messages,
    COUNT(CASE WHEN m.is_urgent = true AND mr.status = 'unread' THEN 1 END) as urgent_unread,
    MAX(m.created_at) as last_message_time
FROM ak_messages m
JOIN ak_message_recipients mr ON m.id = mr.message_id
WHERE m.is_deleted = false AND mr.is_deleted = false
GROUP BY receiver_type, receiver_id;

-- 更新时间触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 获取用户消息统计的函数
CREATE OR REPLACE FUNCTION get_user_message_stats(user_id_param TEXT)
RETURNS TABLE(
    total_messages BIGINT,
    unread_messages BIGINT,
    urgent_unread BIGINT,
    read_messages BIGINT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        COALESCE(vs.total_messages, 0) as total_messages,
        COALESCE(vs.unread_messages, 0) as unread_messages,
        COALESCE(vs.urgent_unread, 0) as urgent_unread,
        COALESCE(vs.total_messages - vs.unread_messages, 0) as read_messages
    FROM v_message_stats vs
    WHERE vs.receiver_type = 'user' AND vs.receiver_id = user_id_param
    UNION ALL
    SELECT 0::BIGINT, 0::BIGINT, 0::BIGINT, 0::BIGINT
    WHERE NOT EXISTS (
        SELECT 1 FROM v_message_stats vs 
        WHERE vs.receiver_type = 'user' AND vs.receiver_id = user_id_param
    )
    LIMIT 1;
END;
$$ LANGUAGE plpgsql;

-- 批量标记消息为已读的函数
CREATE OR REPLACE FUNCTION mark_messages_as_read(
    message_ids_param UUID[],
    user_id_param TEXT
)
RETURNS INTEGER AS $$
DECLARE
    affected_count INTEGER;
BEGIN
    UPDATE ak_message_recipients 
    SET 
        status = 'read',
        read_at = now(),
        updated_at = now()
    WHERE 
        message_id = ANY(message_ids_param)
        AND receiver_type = 'user'
        AND receiver_id = user_id_param
        AND status = 'unread';
    
    GET DIAGNOSTICS affected_count = ROW_COUNT;
    RETURN affected_count;
END;
$$ LANGUAGE plpgsql;

-- 批量删除消息的函数
CREATE OR REPLACE FUNCTION delete_messages_for_user(
    message_ids_param UUID[],
    user_id_param TEXT
)
RETURNS INTEGER AS $$
DECLARE
    affected_count INTEGER;
BEGIN
    UPDATE ak_message_recipients 
    SET 
        status = 'deleted',
        deleted_at = now(),
        is_deleted = true,
        updated_at = now()
    WHERE 
        message_id = ANY(message_ids_param)
        AND receiver_type = 'user'
        AND receiver_id = user_id_param
        AND is_deleted = false;
    
    GET DIAGNOSTICS affected_count = ROW_COUNT;
    RETURN affected_count;
END;
$$ LANGUAGE plpgsql;

-- 5. 验证数据完整性
-- 显示最终的消息类型配置
SELECT 
    code, 
    name, 
    priority, 
    is_system, 
    is_active,
    retention_days,
    icon,
    color
FROM ak_message_types 
ORDER BY priority DESC, code;