-- Performance Indexes for BaseItems Table
-- These indexes improve performance of grouping queries in BaseItemRepository
-- Run on PostgreSQL database
\pset pager off

-- Connect to your Jellyfin database first:
-- \c jellyfin_testdata

-- Index for PresentationUniqueKey grouping
-- Used by queries that group by PresentationUniqueKey
CREATE INDEX CONCURRENTLY IF NOT EXISTS "IX_BaseItems_PresentationUniqueKey" 
ON library."BaseItems" ("PresentationUniqueKey") 
WHERE "PresentationUniqueKey" IS NOT NULL;

-- Index for SeriesPresentationUniqueKey grouping  
-- Used by queries that group by SeriesPresentationUniqueKey
CREATE INDEX CONCURRENTLY IF NOT EXISTS "IX_BaseItems_SeriesPresentationUniqueKey" 
ON library."BaseItems" ("SeriesPresentationUniqueKey")
WHERE "SeriesPresentationUniqueKey" IS NOT NULL;

-- Composite index for queries using both keys
-- Covers the case where both grouping keys are used together
CREATE INDEX CONCURRENTLY IF NOT EXISTS "IX_BaseItems_PresentationKeys_Composite" 
ON library."BaseItems" ("PresentationUniqueKey", "SeriesPresentationUniqueKey", "Id")
WHERE "PresentationUniqueKey" IS NOT NULL 
   OR "SeriesPresentationUniqueKey" IS NOT NULL;

-- Index for Type + TopParentId (common filter combination)
-- Improves WHERE clause filtering before grouping
CREATE INDEX CONCURRENTLY IF NOT EXISTS "IX_BaseItems_Type_TopParentId" 
ON library."BaseItems" ("Type", "TopParentId")
WHERE "TopParentId" IS NOT NULL;

-- Index for Type + TopParentId + PresentationUniqueKey (covering index)
-- Covers the entire query pattern for TV Series queries
CREATE INDEX CONCURRENTLY IF NOT EXISTS "IX_BaseItems_Series_Grouping" 
ON library."BaseItems" ("Type", "TopParentId", "PresentationUniqueKey", "Id")
WHERE "Type" = 'MediaBrowser.Controller.Entities.TV.Series'
  AND "TopParentId" IS NOT NULL;

-- ANALYZE to update statistics after creating indexes
ANALYZE library."BaseItems";

-- Verify indexes were created
SELECT 
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'BaseItems'
  AND schemaname = 'library'
ORDER BY indexname;

-- Check index sizes
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE tablename = 'BaseItems'
  AND schemaname = 'library'
ORDER BY pg_relation_size(indexrelid) DESC;