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

17 files changed, 454 insertions, 15 deletions

diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs
new file mode 100644
index 0000000000..43e6a8bc00
--- /dev/null
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/DescendantQueryHelper.cs
@@ -0,0 +1,248 @@
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using Jellyfin.Database.Implementations.Entities;
+using Jellyfin.Database.Implementations.MatchCriteria;
+
+namespace Jellyfin.Database.Implementations;
+
+/// <summary>
+/// Provides methods for querying item hierarchies using iterative traversal.
+/// Uses AncestorIds and LinkedChildren tables for parent-child traversal.
+/// </summary>
+public static class DescendantQueryHelper
+{
+    /// <summary>
+    /// Gets a queryable of all descendant IDs for a parent item.
+    /// Traverses AncestorIds and LinkedChildren to find all descendants.
+    /// </summary>
+    /// <param name="context">Database context.</param>
+    /// <param name="parentId">Parent item ID.</param>
+    /// <returns>Queryable of descendant item IDs.</returns>
+    public static IQueryable<Guid> GetAllDescendantIds(JellyfinDbContext context, Guid parentId)
+    {
+        ArgumentNullException.ThrowIfNull(context);
+
+        var descendants = TraverseHierarchyDown(context, [parentId]);
+
+        descendants.Remove(parentId);
+
+        return descendants.AsQueryable();
+    }
+
+    /// <summary>
+    /// Gets a queryable of all owned descendant IDs for a parent item.
+    /// Traverses only AncestorIds (hierarchical ownership), NOT LinkedChildren (associations).
+    /// Use this for deletion to avoid destroying items that are merely linked (e.g. movies in a BoxSet).
+    /// </summary>
+    /// <param name="context">Database context.</param>
+    /// <param name="parentId">Parent item ID.</param>
+    /// <returns>Queryable of owned descendant item IDs.</returns>
+    public static IQueryable<Guid> GetOwnedDescendantIds(JellyfinDbContext context, Guid parentId)
+    {
+        ArgumentNullException.ThrowIfNull(context);
+
+        var descendants = TraverseHierarchyDownOwned(context, [parentId]);
+
+        descendants.Remove(parentId);
+
+        return descendants.AsQueryable();
+    }
+
+    /// <summary>
+    /// Gets all owned descendant IDs for multiple parent items in a single traversal.
+    /// More efficient than calling <see cref="GetOwnedDescendantIds"/> per parent because
+    /// it performs one traversal for all seeds instead of N separate traversals.
+    /// </summary>
+    /// <param name="context">Database context.</param>
+    /// <param name="parentIds">Parent item IDs.</param>
+    /// <returns>Set of all owned descendant item IDs (excluding the parent IDs themselves).</returns>
+    public static HashSet<Guid> GetOwnedDescendantIdsBatch(JellyfinDbContext context, IReadOnlyList<Guid> parentIds)
+    {
+        ArgumentNullException.ThrowIfNull(context);
+        ArgumentNullException.ThrowIfNull(parentIds);
+
+        if (parentIds.Count == 0)
+        {
+            return [];
+        }
+
+        var seedSet = new HashSet<Guid>(parentIds);
+        var descendants = TraverseHierarchyDownOwned(context, seedSet);
+
+        // Remove the seed IDs — callers want only descendants
+        descendants.ExceptWith(seedSet);
+
+        return descendants;
+    }
+
+    /// <summary>
+    /// Gets a queryable of all folder IDs that have any descendant matching the specified criteria.
+    /// Can be used in LINQ .Contains() expressions.
+    /// </summary>
+    /// <param name="context">Database context.</param>
+    /// <param name="criteria">The matching criteria to apply.</param>
+    /// <returns>Queryable of folder IDs.</returns>
+    public static IQueryable<Guid> GetFolderIdsMatching(JellyfinDbContext context, FolderMatchCriteria criteria)
+    {
+        ArgumentNullException.ThrowIfNull(context);
+        ArgumentNullException.ThrowIfNull(criteria);
+        var matchingItemIds = criteria switch
+        {
+            HasSubtitles => context.MediaStreamInfos
+                .Where(ms => ms.StreamType == MediaStreamTypeEntity.Subtitle)
+                .Select(ms => ms.ItemId)
+                .Distinct()
+                .ToHashSet(),
+            HasChapterImages => context.Chapters
+                .Where(c => c.ImagePath != null)
+                .Select(c => c.ItemId)
+                .Distinct()
+                .ToHashSet(),
+            HasMediaStreamType m => GetMatchingMediaStreamItemIds(context, m),
+            _ => throw new ArgumentOutOfRangeException(nameof(criteria), $"Unknown criteria type: {criteria.GetType().Name}")
+        };
+
+        var ancestors = TraverseHierarchyUp(context, matchingItemIds);
+
+        return ancestors.AsQueryable();
+    }
+
+    private static HashSet<Guid> GetMatchingMediaStreamItemIds(JellyfinDbContext context, HasMediaStreamType criteria)
+    {
+        var query = context.MediaStreamInfos
+            .Where(ms => ms.StreamType == criteria.StreamType && ms.Language == criteria.Language);
+
+        if (criteria.IsExternal.HasValue)
+        {
+            var isExternal = criteria.IsExternal.Value;
+            query = query.Where(ms => ms.IsExternal == isExternal);
+        }
+
+        return query.Select(ms => ms.ItemId).Distinct().ToHashSet();
+    }
+
+    /// <summary>
+    /// Traverses DOWN the hierarchy from parent folders to find all descendants.
+    /// </summary>
+    private static HashSet<Guid> TraverseHierarchyDown(JellyfinDbContext context, ICollection<Guid> startIds)
+    {
+        var visited = new HashSet<Guid>(startIds);
+        var folderStack = new HashSet<Guid>(startIds);
+
+        while (folderStack.Count != 0)
+        {
+            var currentFolders = folderStack.ToArray();
+            folderStack.Clear();
+
+            var directChildren = context.AncestorIds
+                .WhereOneOrMany(currentFolders, e => e.ParentItemId)
+                .Select(e => e.ItemId)
+                .ToArray();
+
+            var linkedChildren = context.LinkedChildren
+                .WhereOneOrMany(currentFolders, e => e.ParentId)
+                .Select(e => e.ChildId)
+                .ToArray();
+
+            var allChildren = directChildren.Concat(linkedChildren).Distinct().ToArray();
+
+            if (allChildren.Length == 0)
+            {
+                break;
+            }
+
+            var childFolders = context.BaseItems
+                .WhereOneOrMany(allChildren, e => e.Id)
+                .Where(e => e.IsFolder)
+                .Select(e => e.Id)
+                .ToHashSet();
+
+            foreach (var childId in allChildren)
+            {
+                if (visited.Add(childId) && childFolders.Contains(childId))
+                {
+                    folderStack.Add(childId);
+                }
+            }
+        }
+
+        return visited;
+    }
+
+    /// <summary>
+    /// Traverses DOWN the hierarchy using only AncestorIds (ownership), not LinkedChildren.
+    /// </summary>
+    private static HashSet<Guid> TraverseHierarchyDownOwned(JellyfinDbContext context, ICollection<Guid> startIds)
+    {
+        var visited = new HashSet<Guid>(startIds);
+        var folderStack = new HashSet<Guid>(startIds);
+
+        while (folderStack.Count != 0)
+        {
+            var currentFolders = folderStack.ToArray();
+            folderStack.Clear();
+
+            var directChildren = context.AncestorIds
+                .WhereOneOrMany(currentFolders, e => e.ParentItemId)
+                .Select(e => e.ItemId)
+                .ToArray();
+
+            if (directChildren.Length == 0)
+            {
+                break;
+            }
+
+            var childFolders = context.BaseItems
+                .WhereOneOrMany(directChildren, e => e.Id)
+                .Where(e => e.IsFolder)
+                .Select(e => e.Id)
+                .ToHashSet();
+
+            foreach (var childId in directChildren)
+            {
+                if (visited.Add(childId) && childFolders.Contains(childId))
+                {
+                    folderStack.Add(childId);
+                }
+            }
+        }
+
+        return visited;
+    }
+
+    /// <summary>
+    /// Traverses UP the hierarchy from items to find all ancestor folders.
+    /// </summary>
+    private static HashSet<Guid> TraverseHierarchyUp(JellyfinDbContext context, ICollection<Guid> startIds)
+    {
+        var ancestors = new HashSet<Guid>();
+        var itemStack = new HashSet<Guid>(startIds);
+
+        while (itemStack.Count != 0)
+        {
+            var currentItems = itemStack.ToArray();
+            itemStack.Clear();
+
+            var ancestorParents = context.AncestorIds
+                .WhereOneOrMany(currentItems, e => e.ItemId)
+                .Select(e => e.ParentItemId)
+                .ToArray();
+
+            var linkedParents = context.LinkedChildren
+                .WhereOneOrMany(currentItems, e => e.ChildId)
+                .Select(e => e.ParentId)
+                .ToArray();
+
+            foreach (var parentId in ancestorParents.Concat(linkedParents))
+            {
+                if (ancestors.Add(parentId))
+                {
+                    itemStack.Add(parentId);
+                }
+            }
+        }
+
+        return ancestors;
+    }
+}
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/BaseItemEntity.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/BaseItemEntity.cs
index d58466e5ca..76c847e5f0 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/BaseItemEntity.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/BaseItemEntity.cs
@@ -96,7 +96,7 @@ public class BaseItemEntity
 
     public string? OriginalTitle { get; set; }
 
-    public string? PrimaryVersionId { get; set; }
+    public Guid? PrimaryVersionId { get; set; }
 
     public DateTime? DateLastMediaAdded { get; set; }
 
@@ -118,8 +118,6 @@ public class BaseItemEntity
 
     public string? ProductionLocations { get; set; }
 
-    public string? ExtraIds { get; set; }
-
     public int? TotalBitrate { get; set; }
 
     public BaseItemExtraType? ExtraType { get; set; }
@@ -134,7 +132,17 @@ public class BaseItemEntity
 
     public string? ShowId { get; set; }
 
-    public string? OwnerId { get; set; }
+    public Guid? OwnerId { get; set; }
+
+    /// <summary>
+    /// Gets or sets the owner item (for extras like trailers, theme songs, etc.).
+    /// </summary>
+    public BaseItemEntity? Owner { get; set; }
+
+    /// <summary>
+    /// Gets or sets the extras owned by this item (trailers, theme songs, behind the scenes, etc.).
+    /// </summary>
+    public ICollection<BaseItemEntity>? Extras { get; set; }
 
     public int? Width { get; set; }
 
@@ -178,6 +186,16 @@ public class BaseItemEntity
 
     public ICollection<BaseItemImageInfo>? Images { get; set; }
 
+    /// <summary>
+    /// Gets or sets the linked children (for BoxSets, Playlists, etc.).
+    /// </summary>
+    public ICollection<LinkedChildEntity>? LinkedChildEntities { get; set; }
+
+    /// <summary>
+    /// Gets or sets the items this entity is linked to as a child.
+    /// </summary>
+    public ICollection<LinkedChildEntity>? LinkedChildOfEntities { get; set; }
+
     // those are references to __LOCAL__ ids not DB ids ... TODO: Bring the whole folder structure into the DB
     // public ICollection<BaseItemEntity>? SeriesEpisodes { get; set; }
     // public BaseItemEntity? Series { get; set; }
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/LinkedChildEntity.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/LinkedChildEntity.cs
new file mode 100644
index 0000000000..7361775711
--- /dev/null
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/LinkedChildEntity.cs
@@ -0,0 +1,39 @@
+using System;
+
+namespace Jellyfin.Database.Implementations.Entities;
+
+/// <summary>
+/// Represents a linked child relationship between items (e.g., BoxSet to Movies, Playlist to tracks).
+/// </summary>
+public class LinkedChildEntity
+{
+    /// <summary>
+    /// Gets or sets the parent item ID (BoxSet, Playlist, etc.).
+    /// </summary>
+    public required Guid ParentId { get; set; }
+
+    /// <summary>
+    /// Gets or sets the child item ID.
+    /// </summary>
+    public required Guid ChildId { get; set; }
+
+    /// <summary>
+    /// Gets or sets the type of linked child (Manual or Shortcut).
+    /// </summary>
+    public required LinkedChildType ChildType { get; set; }
+
+    /// <summary>
+    /// Gets or sets the sort order.
+    /// </summary>
+    public int? SortOrder { get; set; }
+
+    /// <summary>
+    /// Gets or sets the parent item navigation property.
+    /// </summary>
+    public BaseItemEntity? Parent { get; set; }
+
+    /// <summary>
+    /// Gets or sets the child item navigation property.
+    /// </summary>
+    public BaseItemEntity? Child { get; set; }
+}
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/LinkedChildType.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/LinkedChildType.cs
new file mode 100644
index 0000000000..09b4b84aba
--- /dev/null
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/Entities/LinkedChildType.cs
@@ -0,0 +1,27 @@
+namespace Jellyfin.Database.Implementations.Entities;
+
+/// <summary>
+/// The linked child type.
+/// </summary>
+public enum LinkedChildType
+{
+    /// <summary>
+    /// Manually linked child.
+    /// </summary>
+    Manual = 0,
+
+    /// <summary>
+    /// Shortcut linked child.
+    /// </summary>
+    Shortcut = 1,
+
+    /// <summary>
+    /// Local alternate version (same item, different file path).
+    /// </summary>
+    LocalAlternateVersion = 2,
+
+    /// <summary>
+    /// Linked alternate version (different item ID).
+    /// </summary>
+    LinkedAlternateVersion = 3
+}
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinDbContext.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinDbContext.cs
index 5163bff8b6..f6fce7279a 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinDbContext.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/JellyfinDbContext.cs
@@ -144,6 +144,11 @@ public class JellyfinDbContext(DbContextOptions<JellyfinDbContext> options, ILog
     public DbSet<PeopleBaseItemMap> PeopleBaseItemMap => Set<PeopleBaseItemMap>();
 
     /// <summary>
+    /// Gets the <see cref="DbSet{TEntity}"/> containing linked children relationships.
+    /// </summary>
+    public DbSet<LinkedChildEntity> LinkedChildren => Set<LinkedChildEntity>();
+
+    /// <summary>
     /// Gets the <see cref="DbSet{TEntity}"/> containing the referenced Providers with ids.
     /// </summary>
     public DbSet<BaseItemProvider> BaseItemProviders => Set<BaseItemProvider>();
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/MatchCriteria/FolderMatchCriteria.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/MatchCriteria/FolderMatchCriteria.cs
new file mode 100644
index 0000000000..d9f2d91806
--- /dev/null
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/MatchCriteria/FolderMatchCriteria.cs
@@ -0,0 +1,6 @@
+namespace Jellyfin.Database.Implementations.MatchCriteria;
+
+/// <summary>
+/// Base type for folder matching criteria using discriminated union pattern.
+/// </summary>
+public abstract record FolderMatchCriteria;
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/MatchCriteria/HasChapterImages.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/MatchCriteria/HasChapterImages.cs
new file mode 100644
index 0000000000..3dd84bbd27
--- /dev/null
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/MatchCriteria/HasChapterImages.cs
@@ -0,0 +1,6 @@
+namespace Jellyfin.Database.Implementations.MatchCriteria;
+
+/// <summary>
+/// Matches folders containing descendants with chapter images.
+/// </summary>
+public sealed record HasChapterImages : FolderMatchCriteria;
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/MatchCriteria/HasMediaStreamType.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/MatchCriteria/HasMediaStreamType.cs
new file mode 100644
index 0000000000..68f2ca2786
--- /dev/null
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/MatchCriteria/HasMediaStreamType.cs
@@ -0,0 +1,14 @@
+using Jellyfin.Database.Implementations.Entities;
+
+namespace Jellyfin.Database.Implementations.MatchCriteria;
+
+/// <summary>
+/// Matches folders containing descendants with a specific media stream type and language.
+/// </summary>
+/// <param name="StreamType">The type of media stream to match (Audio, Subtitle, etc.).</param>
+/// <param name="Language">The language to match.</param>
+/// <param name="IsExternal">If not null, filters by internal (false) or external (true) streams. Only applicable to subtitles.</param>
+public sealed record HasMediaStreamType(
+    MediaStreamTypeEntity StreamType,
+    string Language,
+    bool? IsExternal = null) : FolderMatchCriteria;
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/MatchCriteria/HasSubtitles.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/MatchCriteria/HasSubtitles.cs
new file mode 100644
index 0000000000..e50b9f3e12
--- /dev/null
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/MatchCriteria/HasSubtitles.cs
@@ -0,0 +1,6 @@
+namespace Jellyfin.Database.Implementations.MatchCriteria;
+
+/// <summary>
+/// Matches folders containing descendants with subtitles.
+/// </summary>
+public sealed record HasSubtitles : FolderMatchCriteria;
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);
     }
 }