migrations/002_performance_indexes.sql

-- Performance optimization indexes for chat agent database
-- This migration adds indexes to improve query performance for common operations

-- Messages table indexes for performance optimization
-- Index for session-based queries (most common)
CREATE INDEX IF NOT EXISTS idx_messages_session_timestamp 
ON messages(session_id, timestamp DESC);

-- Index for user message queries across sessions
CREATE INDEX IF NOT EXISTS idx_messages_session_role 
ON messages(session_id, role);

-- Index for language-specific queries
CREATE INDEX IF NOT EXISTS idx_messages_language_timestamp 
ON messages(language, timestamp DESC);

-- Composite index for recent message queries
CREATE INDEX IF NOT EXISTS idx_messages_session_recent 
ON messages(session_id, timestamp DESC, role) 
WHERE timestamp > NOW() - INTERVAL '7 days';

-- Index for full-text search on message content (PostgreSQL specific)
-- This will be created conditionally based on database type
DO $$
BEGIN
    -- Check if we're using PostgreSQL
    IF EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_trgm') THEN
        -- Create GIN index for full-text search
        CREATE INDEX IF NOT EXISTS idx_messages_content_gin 
        ON messages USING gin(content gin_trgm_ops);
    ELSE
        -- Create regular index for LIKE queries
        CREATE INDEX IF NOT EXISTS idx_messages_content_text 
        ON messages(content);
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        -- Fallback to regular index if GIN is not available
        CREATE INDEX IF NOT EXISTS idx_messages_content_text 
        ON messages(content);
END $$;

-- Chat sessions table indexes
-- Index for user session queries
CREATE INDEX IF NOT EXISTS idx_chat_sessions_user_active 
ON chat_sessions(user_id, is_active, last_active DESC);

-- Index for session cleanup queries
CREATE INDEX IF NOT EXISTS idx_chat_sessions_last_active 
ON chat_sessions(last_active) 
WHERE is_active = true;

-- Index for language-based session queries
CREATE INDEX IF NOT EXISTS idx_chat_sessions_language 
ON chat_sessions(language, created_at DESC);

-- Language contexts table indexes (if exists)
-- Index for session context lookups
CREATE INDEX IF NOT EXISTS idx_language_contexts_session 
ON language_contexts(session_id, updated_at DESC);

-- Partial indexes for active sessions only (more efficient)
CREATE INDEX IF NOT EXISTS idx_chat_sessions_active_user 
ON chat_sessions(user_id, last_active DESC) 
WHERE is_active = true;

-- Index for message count aggregation
CREATE INDEX IF NOT EXISTS idx_messages_session_count 
ON messages(session_id) 
WHERE role IN ('user', 'assistant');

-- Performance optimization for timestamp range queries
CREATE INDEX IF NOT EXISTS idx_messages_timestamp_range 
ON messages(timestamp) 
WHERE timestamp > NOW() - INTERVAL '30 days';

-- Composite index for pagination queries
CREATE INDEX IF NOT EXISTS idx_messages_session_pagination 
ON messages(session_id, id, timestamp DESC);

-- Add database-specific optimizations
DO $$
BEGIN
    -- PostgreSQL specific optimizations
    IF (SELECT version() LIKE '%PostgreSQL%') THEN
        -- Enable auto-vacuum for better performance
        ALTER TABLE messages SET (autovacuum_vacuum_scale_factor = 0.1);
        ALTER TABLE chat_sessions SET (autovacuum_vacuum_scale_factor = 0.1);
        
        -- Set statistics target for better query planning
        ALTER TABLE messages ALTER COLUMN session_id SET STATISTICS 1000;
        ALTER TABLE messages ALTER COLUMN timestamp SET STATISTICS 1000;
        ALTER TABLE chat_sessions ALTER COLUMN user_id SET STATISTICS 1000;
        
        -- Create partial unique index for active sessions
        CREATE UNIQUE INDEX IF NOT EXISTS idx_chat_sessions_unique_active 
        ON chat_sessions(user_id, language) 
        WHERE is_active = true;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        -- Continue if PostgreSQL-specific features are not available
        NULL;
END $$;

-- Add comments for documentation
COMMENT ON INDEX idx_messages_session_timestamp IS 'Primary index for session message queries ordered by timestamp';
COMMENT ON INDEX idx_messages_session_role IS 'Index for filtering messages by role within sessions';
COMMENT ON INDEX idx_messages_language_timestamp IS 'Index for language-specific message queries';
COMMENT ON INDEX idx_chat_sessions_user_active IS 'Index for user session queries with activity filter';
COMMENT ON INDEX idx_chat_sessions_last_active IS 'Index for session cleanup and maintenance queries';

-- Create function for index usage monitoring (PostgreSQL)
DO $$
BEGIN
    IF (SELECT version() LIKE '%PostgreSQL%') THEN
        CREATE OR REPLACE FUNCTION get_index_usage_stats()
        RETURNS TABLE(
            schemaname text,
            tablename text,
            indexname text,
            idx_scan bigint,
            idx_tup_read bigint,
            idx_tup_fetch bigint
        ) AS $func$
        BEGIN
            RETURN QUERY
            SELECT 
                s.schemaname::text,
                s.relname::text,
                s.indexrelname::text,
                s.idx_scan,
                s.idx_tup_read,
                s.idx_tup_fetch
            FROM pg_stat_user_indexes s
            WHERE s.schemaname = 'public'
            AND (s.relname = 'messages' OR s.relname = 'chat_sessions' OR s.relname = 'language_contexts')
            ORDER BY s.idx_scan DESC;
        END;
        $func$ LANGUAGE plpgsql;
        
        COMMENT ON FUNCTION get_index_usage_stats() IS 'Function to monitor index usage statistics for performance tuning';
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        NULL;
END $$;