migrations/002_performance_indexes_sqlite.sql

-- Performance optimization indexes for SQLite
-- 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);

-- Index for full-text search on message content
CREATE INDEX IF NOT EXISTS idx_messages_content_text 
ON messages(content);

-- 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);

-- 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);

-- Index for message count aggregation
CREATE INDEX IF NOT EXISTS idx_messages_session_count 
ON messages(session_id);

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