Complete LinkedChildren integration and batch DTO optimizations

This commit integrates remaining performance changes:

- Add batch user data fetching in DtoService to reduce N+1 queries
- Add GetNextUpEpisodesBatch in TVSeriesManager for efficient batch retrieval
- Update Video/Movie/BoxSet to use LibraryManager for alternate versions
- Transition LinkedChild to use ItemId instead of Path (obsolete Path/LibraryItemId)
- Update providers and controllers for LinkedChildren-based references
- Add NextUpEpisodeBatchResult for batched episode queries
- Integrate IDescendantQueryProvider in SqliteDatabaseProvider

2 files changed, 110 insertions, 0 deletions

diff --git a/MediaBrowser.Controller/Persistence/IItemRepository.cs b/MediaBrowser.Controller/Persistence/IItemRepository.cs
index bf80b7d0a8..f7ed39730e 100644
--- a/MediaBrowser.Controller/Persistence/IItemRepository.cs
+++ b/MediaBrowser.Controller/Persistence/IItemRepository.cs
@@ -88,6 +88,21 @@ public interface IItemRepository
     IReadOnlyList<string> GetNextUpSeriesKeys(InternalItemsQuery filter, DateTime dateCutoff);
 
     /// <summary>
+    /// Gets next up episodes for multiple series in a single batched query.
+    /// Returns the last watched episode, next unwatched episode, specials, and next played episode for each series.
+    /// </summary>
+    /// <param name="filter">The query filter.</param>
+    /// <param name="seriesKeys">The series presentation unique keys to query.</param>
+    /// <param name="includeSpecials">Whether to include specials (ParentIndexNumber = 0) in the results.</param>
+    /// <param name="includeWatchedForRewatching">Whether to include watched episodes for rewatching mode.</param>
+    /// <returns>A dictionary mapping series key to batch result containing episodes needed for NextUp calculation.</returns>
+    IReadOnlyDictionary<string, NextUpEpisodeBatchResult> GetNextUpEpisodesBatch(
+        InternalItemsQuery filter,
+        IReadOnlyList<string> seriesKeys,
+        bool includeSpecials,
+        bool includeWatchedForRewatching);
+
+    /// <summary>
     /// Updates the inherited values.
     /// </summary>
     void UpdateInheritedValues();
@@ -133,9 +148,66 @@ public interface IItemRepository
     bool GetIsPlayed(User user, Guid id, bool recursive);
 
     /// <summary>
+    /// Gets the count of played items that are descendants of the specified ancestor.
+    /// Uses the AncestorIds table for efficient recursive lookup.
+    /// Applies user access filtering (library access, parental controls, tags).
+    /// </summary>
+    /// <param name="filter">The query filter containing user access settings.</param>
+    /// <param name="ancestorId">The ancestor item id.</param>
+    /// <returns>The count of played descendant items.</returns>
+    int GetPlayedCount(InternalItemsQuery filter, Guid ancestorId);
+
+    /// <summary>
+    /// Gets the total count of items that are descendants of the specified ancestor.
+    /// Uses the AncestorIds table for efficient recursive lookup.
+    /// Applies user access filtering (library access, parental controls, tags).
+    /// </summary>
+    /// <param name="filter">The query filter containing user access settings.</param>
+    /// <param name="ancestorId">The ancestor item id.</param>
+    /// <returns>The total count of descendant items.</returns>
+    int GetTotalCount(InternalItemsQuery filter, Guid ancestorId);
+
+    /// <summary>
+    /// Gets both the played count and total count of items that are descendants of the specified ancestor.
+    /// Uses the AncestorIds table for efficient recursive lookup.
+    /// Applies user access filtering (library access, parental controls, tags).
+    /// </summary>
+    /// <param name="filter">The query filter containing user access settings.</param>
+    /// <param name="ancestorId">The ancestor item id.</param>
+    /// <returns>A tuple containing (Played count, Total count).</returns>
+    (int Played, int Total) GetPlayedAndTotalCount(InternalItemsQuery filter, Guid ancestorId);
+
+    /// <summary>
+    /// Gets both the played count and total count of items that are linked children of the specified parent.
+    /// Uses the LinkedChildren table for BoxSets, Playlists, etc.
+    /// Applies user access filtering (library access, parental controls, tags).
+    /// </summary>
+    /// <param name="filter">The query filter containing user access settings.</param>
+    /// <param name="parentId">The parent item id (BoxSet, Playlist, etc.).</param>
+    /// <returns>A tuple containing (Played count, Total count).</returns>
+    (int Played, int Total) GetPlayedAndTotalCountFromLinkedChildren(InternalItemsQuery filter, Guid parentId);
+
+    /// <summary>
+    /// Gets the IDs of linked children for the specified parent.
+    /// </summary>
+    /// <param name="parentId">The parent item ID.</param>
+    /// <param name="childType">Optional child type filter (e.g., LocalAlternateVersion, LinkedAlternateVersion).</param>
+    /// <returns>List of child item IDs.</returns>
+    IReadOnlyList<Guid> GetLinkedChildrenIds(Guid parentId, int? childType = null);
+
+    /// <summary>
     /// Gets all artist matches from the db.
     /// </summary>
     /// <param name="artistNames">The names of the artists.</param>
     /// <returns>A map of the artist name and the potential matches.</returns>
     IReadOnlyDictionary<string, MusicArtist[]> FindArtists(IReadOnlyList<string> artistNames);
+
+    /// <summary>
+    /// Batch-fetches child counts for multiple parent folders.
+    /// Returns the count of immediate children (non-recursive) for each parent.
+    /// </summary>
+    /// <param name="parentIds">The list of parent folder IDs.</param>
+    /// <param name="userId">The user ID for access filtering.</param>
+    /// <returns>Dictionary mapping parent ID to child count.</returns>
+    Dictionary<Guid, int> GetChildCountBatch(IReadOnlyList<Guid> parentIds, Guid? userId);
 }
diff --git a/MediaBrowser.Controller/Persistence/NextUpEpisodeBatchResult.cs b/MediaBrowser.Controller/Persistence/NextUpEpisodeBatchResult.cs
new file mode 100644
index 0000000000..f5b09498b9
--- /dev/null
+++ b/MediaBrowser.Controller/Persistence/NextUpEpisodeBatchResult.cs
@@ -0,0 +1,38 @@
+using System.Collections.Generic;
+using MediaBrowser.Controller.Entities;
+
+namespace MediaBrowser.Controller.Persistence;
+
+/// <summary>
+/// Result of a batched NextUp query for a single series.
+/// </summary>
+public sealed class NextUpEpisodeBatchResult
+{
+    /// <summary>
+    /// Gets or sets the last watched episode (highest season/episode that is played).
+    /// </summary>
+    public BaseItem? LastWatched { get; set; }
+
+    /// <summary>
+    /// Gets or sets the next unwatched episode after the last watched position.
+    /// </summary>
+    public BaseItem? NextUp { get; set; }
+
+    /// <summary>
+    /// Gets or sets specials that may air between episodes.
+    /// Only populated when includeSpecials is true.
+    /// </summary>
+    public IReadOnlyList<BaseItem>? Specials { get; set; }
+
+    /// <summary>
+    /// Gets or sets the last watched episode for rewatching mode (most recently played).
+    /// Only populated when includeWatchedForRewatching is true.
+    /// </summary>
+    public BaseItem? LastWatchedForRewatching { get; set; }
+
+    /// <summary>
+    /// Gets or sets the next played episode for rewatching mode.
+    /// Only populated when includeWatchedForRewatching is true.
+    /// </summary>
+    public BaseItem? NextPlayedForRewatching { get; set; }
+}