// <copyright file="20260226170000_AddBasePerformanceIndexes.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 AddBasePerformanceIndexes : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            // These are the base performance indexes that should have been in InitialCreate
            // but were missing. They're from the original schema dump.

            // BaseItems performance indexes
            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS baseitems_communityrating_idx 
                ON library.""BaseItems"" (""CommunityRating"" DESC);
            ");

            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS baseitems_datecreated_idx 
                ON library.""BaseItems"" (""DateCreated"" DESC);
            ");

            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS baseitems_datemodified_idx 
                ON library.""BaseItems"" (""DateModified"" DESC);
            ");

            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS baseitems_parentid_idx 
                ON library.""BaseItems"" (""ParentId"", ""Type"");
            ");

            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS baseitems_premieredate_idx 
                ON library.""BaseItems"" (""PremiereDate"" DESC);
            ");

            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS baseitems_productionyear_idx 
                ON library.""BaseItems"" (""ProductionYear"" DESC);
            ");

            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS baseitems_seriespresentationuniquekey_idx 
                ON library.""BaseItems"" (""SeriesPresentationUniqueKey"", ""IndexNumber"", ""ParentIndexNumber"");
            ");

            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS baseitems_sortname_idx 
                ON library.""BaseItems"" (""SortName"");
            ");

            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS baseitems_topparentid_idx 
                ON library.""BaseItems"" (""TopParentId"", ""Type"");
            ");

            // BaseItemProviders performance indexes
            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS baseitemproviders_providerid_idx 
                ON library.""BaseItemProviders"" (""ProviderId"", ""ItemId"");
            ");

            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS baseitemproviders_providervalue_idx 
                ON library.""BaseItemProviders"" (""ProviderValue"", ""ProviderId"");
            ");

            // MediaStreamInfos performance indexes
            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS mediastreaminfos_codec_idx 
                ON library.""MediaStreamInfos"" (""Codec"");
            ");

            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS mediastreaminfos_itemid_idx 
                ON library.""MediaStreamInfos"" (""ItemId"", ""StreamType"");
            ");

            // PeopleBaseItemMap performance indexes
            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS peoplebaseitemmap_itemid_idx 
                ON library.""PeopleBaseItemMap"" (""ItemId"", ""PeopleId"");
            ");

            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS peoplebaseitemmap_peopleid_idx 
                ON library.""PeopleBaseItemMap"" (""PeopleId"", ""ItemId"");
            ");

            // UserData performance indexes
            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS userdata_lastplayeddate_idx 
                ON library.""UserData"" (""LastPlayedDate"" DESC);
            ");

            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS userdata_userid_isfavorite_idx 
                ON library.""UserData"" (""UserId"", ""IsFavorite"");
            ");

            migrationBuilder.Sql(@"
                CREATE INDEX IF NOT EXISTS userdata_userid_played_idx 
                ON library.""UserData"" (""UserId"", ""Played"");
            ");
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            // Drop all the base performance indexes
            migrationBuilder.Sql(@"DROP INDEX IF EXISTS library.baseitems_communityrating_idx;");
            migrationBuilder.Sql(@"DROP INDEX IF EXISTS library.baseitems_datecreated_idx;");
            migrationBuilder.Sql(@"DROP INDEX IF EXISTS library.baseitems_datemodified_idx;");
            migrationBuilder.Sql(@"DROP INDEX IF EXISTS library.baseitems_parentid_idx;");
            migrationBuilder.Sql(@"DROP INDEX IF EXISTS library.baseitems_premieredate_idx;");
            migrationBuilder.Sql(@"DROP INDEX IF EXISTS library.baseitems_productionyear_idx;");
            migrationBuilder.Sql(@"DROP INDEX IF EXISTS library.baseitems_seriespresentationuniquekey_idx;");
            migrationBuilder.Sql(@"DROP INDEX IF EXISTS library.baseitems_sortname_idx;");
            migrationBuilder.Sql(@"DROP INDEX IF EXISTS library.baseitems_topparentid_idx;");
            migrationBuilder.Sql(@"DROP INDEX IF EXISTS library.baseitemproviders_providerid_idx;");
            migrationBuilder.Sql(@"DROP INDEX IF EXISTS library.baseitemproviders_providervalue_idx;");
            migrationBuilder.Sql(@"DROP INDEX IF EXISTS library.mediastreaminfos_codec_idx;");
            migrationBuilder.Sql(@"DROP INDEX IF EXISTS library.mediastreaminfos_itemid_idx;");
            migrationBuilder.Sql(@"DROP INDEX IF EXISTS library.peoplebaseitemmap_itemid_idx;");
            migrationBuilder.Sql(@"DROP INDEX IF EXISTS library.peoplebaseitemmap_peopleid_idx;");
            migrationBuilder.Sql(@"DROP INDEX IF EXISTS library.userdata_lastplayeddate_idx;");
            migrationBuilder.Sql(@"DROP INDEX IF EXISTS library.userdata_userid_isfavorite_idx;");
            migrationBuilder.Sql(@"DROP INDEX IF EXISTS library.userdata_userid_played_idx;");
        }
    }
}
