-- Performance-enhancing indexes for the SRS database

-- User progress indexes
CREATE INDEX IF NOT EXISTS idx_user_progress_next_review ON user_progress(user_id, next_review_date);
CREATE INDEX IF NOT EXISTS idx_user_progress_mastered ON user_progress(user_id, is_mastered);

-- Expressions related indexes
CREATE INDEX IF NOT EXISTS idx_expressions_combined ON expressions(lang, type, frequency);
CREATE INDEX IF NOT EXISTS idx_expressions_spelling_lang ON expressions(spelling, lang);

-- Cards and lessons indexes
CREATE INDEX IF NOT EXISTS idx_cards_lessons_lesson ON cards_lessons(lesson_id);
CREATE INDEX IF NOT EXISTS idx_cards_lessons_card ON cards_lessons(card_id);
CREATE INDEX IF NOT EXISTS idx_cards_expressions_expression ON cards_expressions(expression_id);

-- Bookmarks index
CREATE INDEX IF NOT EXISTS idx_bookmarks_user ON bookmarks(user_id);

-- Lessons indexes
CREATE INDEX IF NOT EXISTS idx_lang_lessons_lang ON lang_lessons(lang);

-- Attempts indexes
CREATE INDEX IF NOT EXISTS idx_attempts_timestamp ON attempts(timestamp);

-- Combined index for senses parent lookup
CREATE INDEX IF NOT EXISTS idx_senses_parent_combined ON senses(parent_id, pos);