-- Fix column name in update_chat_metadata function
-- The function was referencing 'message_count' but the column is actually 'total_messages'

CREATE OR REPLACE FUNCTION update_chat_metadata()
RETURNS TRIGGER 
SECURITY DEFINER
SET search_path = public, pg_catalog, pg_temp
AS $$
BEGIN
    -- Update metadata when a message is added/updated/deleted
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        UPDATE chats SET
            last_message_at = NOW(),
            total_messages = (
                SELECT COUNT(*) 
                FROM messages 
                WHERE chat_id = NEW.chat_id
            ),
            updated_at = NOW()
        WHERE id = NEW.chat_id;
        
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE chats SET
            total_messages = (
                SELECT COUNT(*) 
                FROM messages 
                WHERE chat_id = OLD.chat_id
            ),
            updated_at = NOW()
        WHERE id = OLD.chat_id;
        
        RETURN OLD;
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;