-- Add performance indexes for chat module
-- This script adds missing indexes to improve query performance

-- Index on messages.sender_id for efficient user message queries
CREATE INDEX IF NOT EXISTS idx_messages_sender_id ON messages(sender_id);

-- Index on messages.created_at for time-based queries
CREATE INDEX IF NOT EXISTS idx_messages_created_at ON messages(created_at);

-- Index on conversation_participants.last_read_at for unread count queries
CREATE INDEX IF NOT EXISTS idx_participants_last_read ON conversation_participants(last_read_at);

-- Verify indexes were created
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.STATISTICS
WHERE 
    TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME IN ('messages', 'conversation_participants')
    AND INDEX_NAME LIKE 'idx_%'
ORDER BY 
    TABLE_NAME, INDEX_NAME;


