Merge branch 'master' into feature/season-provider-id-from-path

8 files changed, 81 insertions, 11 deletions

diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs
index 6fccfd976d..8556fb7bb3 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemConfiguration.cs
@@ -28,15 +28,17 @@ public class BaseItemConfiguration : IEntityTypeConfiguration<BaseItemEntity>
         builder.HasMany(e => e.Parents);
         builder.HasMany(e => e.Children);
         builder.HasMany(e => e.DirectChildren).WithOne(e => e.DirectParent).HasForeignKey(e => e.ParentId).OnDelete(DeleteBehavior.Cascade);
+        builder.HasMany(e => e.Extras).WithOne(e => e.Owner).HasForeignKey(e => e.OwnerId).OnDelete(DeleteBehavior.NoAction);
         builder.HasMany(e => e.LockedFields);
         builder.HasMany(e => e.TrailerTypes);
         builder.HasMany(e => e.Images);
 
         builder.HasIndex(e => e.Path);
         builder.HasIndex(e => e.ParentId);
+        builder.HasIndex(e => e.OwnerId);
+        builder.HasIndex(e => e.Name);
+        builder.HasIndex(e => new { e.ExtraType, e.OwnerId });
         builder.HasIndex(e => e.PresentationUniqueKey);
-        builder.HasIndex(e => new { e.Id, e.Type, e.IsFolder, e.IsVirtualItem });
-
         // covering index
         builder.HasIndex(e => new { e.TopParentId, e.Id });
         // series
@@ -53,14 +55,33 @@ public class BaseItemConfiguration : IEntityTypeConfiguration<BaseItemEntity>
         // latest items
         builder.HasIndex(e => new { e.Type, e.TopParentId, e.IsVirtualItem, e.PresentationUniqueKey, e.DateCreated });
         builder.HasIndex(e => new { e.IsFolder, e.TopParentId, e.IsVirtualItem, e.PresentationUniqueKey, e.DateCreated });
+        // latest items - optimized for sorting by DateCreated (no PresentationUniqueKey breaking the sort)
+        builder.HasIndex(e => new { e.TopParentId, e.Type, e.IsVirtualItem, e.DateCreated });
+        builder.HasIndex(e => new { e.TopParentId, e.IsFolder, e.IsVirtualItem, e.DateCreated });
+        builder.HasIndex(e => new { e.TopParentId, e.MediaType, e.IsVirtualItem, e.DateCreated });
         // resume
         builder.HasIndex(e => new { e.MediaType, e.TopParentId, e.IsVirtualItem, e.PresentationUniqueKey });
+        // sorted library queries (e.g., Series sorted by SortName)
+        builder.HasIndex(e => new { e.Type, e.TopParentId, e.SortName });
+        // NextUp: per-series episode ordering (index seek + range scan on season/episode)
+        builder.HasIndex(e => new { e.Type, e.SeriesPresentationUniqueKey, e.ParentIndexNumber, e.IndexNumber });
+        // ByName queries: WHERE Type = X AND CleanName IN (...)
+        builder.HasIndex(e => new { e.Type, e.CleanName });
+        // Latest TV: GROUP BY SeriesName
+        builder.HasIndex(e => e.SeriesName);
+        // Latest TV: episode count per season, season count per series
+        builder.HasIndex(e => e.SeasonId);
+        builder.HasIndex(e => e.SeriesId);
+
+        // Items/Counts: SELECT Type, COUNT(*) GROUP BY Type filtered by TopParentId.
+        builder.HasIndex(e => new { e.TopParentId, e.Type, e.IsVirtualItem })
+            .HasFilter("\"PrimaryVersionId\" IS NULL AND (\"OwnerId\" IS NULL OR \"ExtraType\" IS NOT NULL)");
 
         builder.HasData(new BaseItemEntity()
         {
             Id = Guid.Parse("00000000-0000-0000-0000-000000000001"),
             Type = "PLACEHOLDER",
-            Name = "This is a placeholder item for UserData that has been detacted from its original item",
+            Name = "This is a placeholder item for UserData that has been detached from its original item",
         });
     }
 }
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemImageInfoConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemImageInfoConfiguration.cs
new file mode 100644
index 0000000000..79262e4c8d
--- /dev/null
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemImageInfoConfiguration.cs
@@ -0,0 +1,21 @@
+using Jellyfin.Database.Implementations.Entities;
+using Microsoft.EntityFrameworkCore;
+using Microsoft.EntityFrameworkCore.Metadata.Builders;
+
+namespace Jellyfin.Database.Implementations.ModelConfiguration;
+
+/// <summary>
+/// FluentAPI configuration for the BaseItemImageInfo entity.
+/// </summary>
+public class BaseItemImageInfoConfiguration : IEntityTypeConfiguration<BaseItemImageInfo>
+{
+    /// <inheritdoc/>
+    public void Configure(EntityTypeBuilder<BaseItemImageInfo> builder)
+    {
+        builder.HasKey(e => e.Id);
+        builder.HasOne(e => e.Item).WithMany(e => e.Images).HasForeignKey(e => e.ItemId);
+
+        // Composite index for filtering by item and image type (also covers ItemId-only lookups)
+        builder.HasIndex(e => new { e.ItemId, e.ImageType });
+    }
+}
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemProviderConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemProviderConfiguration.cs
index dd28000ba6..0f1053a49b 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemProviderConfiguration.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/BaseItemProviderConfiguration.cs
@@ -14,6 +14,6 @@ public class BaseItemProviderConfiguration : IEntityTypeConfiguration<BaseItemPr
     {
         builder.HasKey(e => new { e.ItemId, e.ProviderId });
         builder.HasOne(e => e.Item);
-        builder.HasIndex(e => new { e.ProviderId, e.ProviderValue, e.ItemId });
+        builder.HasIndex(e => new { e.ProviderId, e.ItemId, e.ProviderValue });
     }
 }
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/DeviceConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/DeviceConfiguration.cs
index 3551f76863..93952e480b 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/DeviceConfiguration.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/DeviceConfiguration.cs
@@ -20,9 +20,6 @@ namespace Jellyfin.Database.Implementations.ModelConfiguration
 
             builder
                 .HasIndex(entity => new { entity.UserId, entity.DeviceId });
-
-            builder
-                .HasIndex(entity => entity.DeviceId);
         }
     }
 }
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/LinkedChildConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/LinkedChildConfiguration.cs
new file mode 100644
index 0000000000..2abccd41f0
--- /dev/null
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/LinkedChildConfiguration.cs
@@ -0,0 +1,31 @@
+using Jellyfin.Database.Implementations.Entities;
+using Microsoft.EntityFrameworkCore;
+using Microsoft.EntityFrameworkCore.Metadata.Builders;
+
+namespace Jellyfin.Database.Implementations.ModelConfiguration;
+
+/// <summary>
+/// LinkedChildEntity configuration.
+/// </summary>
+public class LinkedChildConfiguration : IEntityTypeConfiguration<LinkedChildEntity>
+{
+    /// <inheritdoc/>
+    public void Configure(EntityTypeBuilder<LinkedChildEntity> builder)
+    {
+        builder.ToTable("LinkedChildren");
+        builder.HasKey(e => new { e.ParentId, e.ChildId });
+        builder.HasIndex(e => new { e.ParentId, e.SortOrder });
+        builder.HasIndex(e => new { e.ParentId, e.ChildType });
+        builder.HasIndex(e => new { e.ChildId, e.ChildType });
+
+        builder.HasOne(e => e.Parent)
+            .WithMany(e => e.LinkedChildEntities)
+            .HasForeignKey(e => e.ParentId)
+            .OnDelete(DeleteBehavior.NoAction);
+
+        builder.HasOne(e => e.Child)
+            .WithMany(e => e.LinkedChildOfEntities)
+            .HasForeignKey(e => e.ChildId)
+            .OnDelete(DeleteBehavior.NoAction);
+    }
+}
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/MediaStreamInfoConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/MediaStreamInfoConfiguration.cs
index 075af2c053..afa9eee363 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/MediaStreamInfoConfiguration.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/MediaStreamInfoConfiguration.cs
@@ -13,9 +13,5 @@ public class MediaStreamInfoConfiguration : IEntityTypeConfiguration<MediaStream
     public void Configure(EntityTypeBuilder<MediaStreamInfo> builder)
     {
         builder.HasKey(e => new { e.ItemId, e.StreamIndex });
-        builder.HasIndex(e => e.StreamIndex);
-        builder.HasIndex(e => e.StreamType);
-        builder.HasIndex(e => new { e.StreamIndex, e.StreamType });
-        builder.HasIndex(e => new { e.StreamIndex, e.StreamType, e.Language });
     }
 }
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/PeopleBaseItemMapConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/PeopleBaseItemMapConfiguration.cs
index f7694aeda0..32ede86c96 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/PeopleBaseItemMapConfiguration.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/PeopleBaseItemMapConfiguration.cs
@@ -15,6 +15,7 @@ public class PeopleBaseItemMapConfiguration : IEntityTypeConfiguration<PeopleBas
         builder.HasKey(e => new { e.ItemId, e.PeopleId, e.Role });
         builder.HasIndex(e => new { e.ItemId, e.SortOrder });
         builder.HasIndex(e => new { e.ItemId, e.ListOrder });
+        builder.HasIndex(e => e.PeopleId);
         builder.HasOne(e => e.Item);
         builder.HasOne(e => e.People);
     }
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/UserDataConfiguration.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/UserDataConfiguration.cs
index e7b436293e..42848c6c4e 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/UserDataConfiguration.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/ModelConfiguration/UserDataConfiguration.cs
@@ -17,6 +17,9 @@ public class UserDataConfiguration : IEntityTypeConfiguration<UserData>
         builder.HasIndex(d => new { d.ItemId, d.UserId, d.PlaybackPositionTicks });
         builder.HasIndex(d => new { d.ItemId, d.UserId, d.IsFavorite });
         builder.HasIndex(d => new { d.ItemId, d.UserId, d.LastPlayedDate });
+        builder.HasIndex(d => new { d.UserId, d.ItemId, d.LastPlayedDate });
+        builder.HasIndex(d => new { d.UserId, d.Played, d.ItemId });
+        builder.HasIndex(d => new { d.UserId, d.IsFavorite, d.ItemId });
         builder.HasOne(e => e.Item).WithMany(e => e.UserData);
     }
 }