-- Add performance indexes for better query performance
-- This migration adds strategic indexes based on query pattern analysis

-- Activities table indexes
-- Used for: date filtering, ordering by date
CREATE INDEX IF NOT EXISTS idx_activities_date ON activities(date);
CREATE INDEX IF NOT EXISTS idx_activities_created_at ON activities(created_at);

-- Blog posts table indexes  
-- Used for: date ordering, created_at ordering
CREATE INDEX IF NOT EXISTS idx_blog_posts_date ON blog_posts(date);
CREATE INDEX IF NOT EXISTS idx_blog_posts_created_at ON blog_posts(created_at);

-- Blog images table indexes
-- Used for: blog_post_id lookups, sort_order ordering
CREATE INDEX IF NOT EXISTS idx_blog_images_blog_post_id ON blog_images(blog_post_id);
CREATE INDEX IF NOT EXISTS idx_blog_images_blog_post_id_sort ON blog_images(blog_post_id, sort_order);

-- Blog videos table indexes
-- Used for: blog_post_id lookups
CREATE INDEX IF NOT EXISTS idx_blog_videos_blog_post_id ON blog_videos(blog_post_id);

-- Blog post grades table indexes
-- Used for: blog_post_id lookups, grade filtering
CREATE INDEX IF NOT EXISTS idx_blog_post_grades_blog_post_id ON blog_post_grades(blog_post_id);
CREATE INDEX IF NOT EXISTS idx_blog_post_grades_grade ON blog_post_grades(grade);

-- News table indexes (some already exist, adding missing ones)
-- Used for: created_at ordering, title searching
CREATE INDEX IF NOT EXISTS idx_news_created_at ON news(created_at);
CREATE INDEX IF NOT EXISTS idx_news_title ON news(title);

-- Users table indexes
-- Used for: username lookups, email lookups, admin filtering
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_is_admin ON users(is_admin);

-- Documents table indexes
-- Used for: sort_order ordering, uploaded_at ordering
CREATE INDEX IF NOT EXISTS idx_documents_sort_order ON documents(sort_order);
CREATE INDEX IF NOT EXISTS idx_documents_uploaded_at ON documents(uploaded_at);

-- Lost items table indexes
-- Used for: date_found ordering, status filtering
CREATE INDEX IF NOT EXISTS idx_lost_items_date_found ON lost_items(date_found);
CREATE INDEX IF NOT EXISTS idx_lost_items_is_claimed ON lost_items(is_claimed);

-- Summer reading table indexes
-- Used for: created_at ordering
CREATE INDEX IF NOT EXISTS idx_summer_reading_created_at ON summer_reading(created_at);

-- Varia table indexes
-- Used for: month/year filtering, year/month ordering
CREATE INDEX IF NOT EXISTS idx_varia_month_year ON varia(month, year);
CREATE INDEX IF NOT EXISTS idx_varia_year_month ON varia(year, month);

-- Settings table indexes
-- Used for: name lookups (already has unique index, but adding for completeness)
-- Note: settings.name already has a unique index, so no additional index needed

-- Schema version table indexes
-- Used for: version ordering
CREATE INDEX IF NOT EXISTS idx_schema_version_version ON schema_version(version);
