// <copyright file="20260227000000_AddSupplementaryIndexes.cs" company="PlaceholderCompany">
// Copyright (c) PlaceholderCompany. All rights reserved.
// </copyright>

using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace Jellyfin.Database.Providers.Postgres.Migrations
{
    /// <inheritdoc />
    public partial class AddSupplementaryIndexes : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            // 1. Composite index for filtered library browsing (type + virtual + parent)
            // Use case: Browse items by type in a specific library, excluding virtual items
            migrationBuilder.Sql(@"
                CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_baseitems_type_isvirtualitem_topparentid
                ON library.""BaseItems"" (""Type"", ""IsVirtualItem"", ""TopParentId"")
                WHERE ""IsVirtualItem"" = false;
            ");

            // 2. Index for folder hierarchy queries
            // Use case: Navigate folder structures efficiently
            migrationBuilder.Sql(@"
                CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_baseitems_topparentid_isfolder
                ON library.""BaseItems"" (""TopParentId"", ""IsFolder"", ""IsVirtualItem"")
                WHERE ""TopParentId"" IS NOT NULL;
            ");

            // 3. Index for recently added content with filters
            // Use case: ""Recently Added"" view across all libraries
            migrationBuilder.Sql(@"
                CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_baseitems_datecreated_filtered
                ON library.""BaseItems"" (""DateCreated"" DESC, ""Type"", ""IsVirtualItem"")
                WHERE ""DateCreated"" IS NOT NULL AND ""IsVirtualItem"" = false;
            ");

            // 4. Index for reverse lookup (find items by genre/tag)
            // Use case: ""Show all items with genre 'Action'""
            migrationBuilder.Sql(@"
                CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_itemvaluesmap_itemvalueid_itemid
                ON library.""ItemValuesMap"" (""ItemValueId"", ""ItemId"");
            ");

            // 5. Index for user-specific activity queries
            // Use case: ""Show activity for user X""
            migrationBuilder.Sql(@"
                DO $$
                BEGIN
                    IF EXISTS (
                        SELECT 1 FROM information_schema.tables 
                        WHERE table_schema = 'activitylog' 
                        AND table_name = 'ActivityLogs'
                    ) THEN
                        CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_activitylogs_userid_datecreated
                        ON activitylog.""ActivityLogs"" (""UserId"", ""DateCreated"" DESC)
                        WHERE ""UserId"" IS NOT NULL;
                    END IF;
                END $$;
            ");

            // 6. Index for episode deduplication by PresentationUniqueKey
            // Use case: Group and deduplicate episodes (used in GetItems queries)
            migrationBuilder.Sql(@"
                CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_baseitems_presentationuniquekey_episodes
                ON library.""BaseItems"" (""PresentationUniqueKey"", ""TopParentId"", ""IsVirtualItem"")
                WHERE ""Type"" = 'MediaBrowser.Controller.Entities.TV.Episode' AND ""PresentationUniqueKey"" IS NOT NULL;
            ");
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            // Drop the supplementary indexes
            migrationBuilder.Sql(@"DROP INDEX CONCURRENTLY IF EXISTS library.idx_baseitems_type_isvirtualitem_topparentid;");
            migrationBuilder.Sql(@"DROP INDEX CONCURRENTLY IF EXISTS library.idx_baseitems_topparentid_isfolder;");
            migrationBuilder.Sql(@"DROP INDEX CONCURRENTLY IF EXISTS library.idx_baseitems_datecreated_filtered;");
            migrationBuilder.Sql(@"DROP INDEX CONCURRENTLY IF EXISTS library.idx_itemvaluesmap_itemvalueid_itemid;");
            migrationBuilder.Sql(@"DROP INDEX CONCURRENTLY IF EXISTS activitylog.idx_activitylogs_userid_datecreated;");
            migrationBuilder.Sql(@"DROP INDEX CONCURRENTLY IF EXISTS library.idx_baseitems_presentationuniquekey_episodes;");
        }
    }
}
