Merge branch 'master' into issue15137

4 files changed, 373 insertions, 183 deletions

diff --git a/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs b/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs
index eb88eac00a..5bb4494dd2 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemRepository.cs
@@ -250,7 +250,7 @@ public sealed class BaseItemRepository
     public QueryResult<BaseItemDto> GetItems(InternalItemsQuery filter)
     {
         ArgumentNullException.ThrowIfNull(filter);
-        if (!filter.EnableTotalRecordCount || (!filter.Limit.HasValue && (filter.StartIndex ?? 0) == 0))
+        if (!filter.EnableTotalRecordCount || ((filter.Limit ?? 0) == 0 && (filter.StartIndex ?? 0) == 0))
         {
             var returnList = GetItemList(filter);
             return new QueryResult<BaseItemDto>(
@@ -275,8 +275,9 @@ public sealed class BaseItemRepository
         }
 
         dbQuery = ApplyQueryPaging(dbQuery, filter);
+        dbQuery = ApplyNavigations(dbQuery, filter);
 
-        result.Items = dbQuery.AsEnumerable().Where(e => e is not null).Select(w => DeserializeBaseItem(w, filter.SkipDeserialization)).ToArray();
+        result.Items = dbQuery.AsEnumerable().Where(e => e is not null).Select(w => DeserializeBaseItem(w, filter.SkipDeserialization)).Where(dto => dto is not null).ToArray()!;
         result.StartIndex = filter.StartIndex ?? 0;
         return result;
     }
@@ -295,7 +296,27 @@ public sealed class BaseItemRepository
         dbQuery = ApplyGroupingFilter(context, dbQuery, filter);
         dbQuery = ApplyQueryPaging(dbQuery, filter);
 
-        return dbQuery.AsEnumerable().Where(e => e is not null).Select(w => DeserializeBaseItem(w, filter.SkipDeserialization)).ToArray();
+        var hasRandomSort = filter.OrderBy.Any(e => e.OrderBy == ItemSortBy.Random);
+        if (hasRandomSort)
+        {
+            var orderedIds = dbQuery.Select(e => e.Id).ToList();
+            if (orderedIds.Count == 0)
+            {
+                return Array.Empty<BaseItemDto>();
+            }
+
+            var itemsById = ApplyNavigations(context.BaseItems.Where(e => orderedIds.Contains(e.Id)), filter)
+                .AsEnumerable()
+                .Select(w => DeserializeBaseItem(w, filter.SkipDeserialization))
+                .Where(dto => dto is not null)
+                .ToDictionary(i => i!.Id);
+
+            return orderedIds.Where(itemsById.ContainsKey).Select(id => itemsById[id]).ToArray()!;
+        }
+
+        dbQuery = ApplyNavigations(dbQuery, filter);
+
+        return dbQuery.AsEnumerable().Where(e => e is not null).Select(w => DeserializeBaseItem(w, filter.SkipDeserialization)).Where(dto => dto is not null).ToArray()!;
     }
 
     /// <inheritdoc/>
@@ -324,7 +345,7 @@ public sealed class BaseItemRepository
             .OrderByDescending(g => g.MaxDateCreated)
             .Select(g => g);
 
-        if (filter.Limit.HasValue)
+        if (filter.Limit.HasValue && filter.Limit.Value > 0)
         {
             subqueryGrouped = subqueryGrouped.Take(filter.Limit.Value);
         }
@@ -337,7 +358,9 @@ public sealed class BaseItemRepository
         mainquery = ApplyGroupingFilter(context, mainquery, filter);
         mainquery = ApplyQueryPaging(mainquery, filter);
 
-        return mainquery.AsEnumerable().Where(e => e is not null).Select(w => DeserializeBaseItem(w, filter.SkipDeserialization)).ToArray();
+        mainquery = ApplyNavigations(mainquery, filter);
+
+        return mainquery.AsEnumerable().Where(e => e is not null).Select(w => DeserializeBaseItem(w, filter.SkipDeserialization)).Where(dto => dto is not null).ToArray()!;
     }
 
     /// <inheritdoc />
@@ -363,7 +386,7 @@ public sealed class BaseItemRepository
             .OrderByDescending(g => g.LastPlayedDate)
             .Select(g => g.Key!);
 
-        if (filter.Limit.HasValue)
+        if (filter.Limit.HasValue && filter.Limit.Value > 0)
         {
             query = query.Take(filter.Limit.Value);
         }
@@ -399,19 +422,32 @@ public sealed class BaseItemRepository
             dbQuery = dbQuery.Distinct();
         }
 
-        dbQuery = ApplyOrder(dbQuery, filter);
-
-        dbQuery = ApplyNavigations(dbQuery, filter);
+        dbQuery = ApplyOrder(dbQuery, filter, context);
 
         return dbQuery;
     }
 
     private static IQueryable<BaseItemEntity> ApplyNavigations(IQueryable<BaseItemEntity> dbQuery, InternalItemsQuery filter)
     {
-        dbQuery = dbQuery.Include(e => e.TrailerTypes)
-           .Include(e => e.Provider)
-           .Include(e => e.LockedFields)
-           .Include(e => e.UserData);
+        if (filter.TrailerTypes.Length > 0 || filter.IncludeItemTypes.Contains(BaseItemKind.Trailer))
+        {
+            dbQuery = dbQuery.Include(e => e.TrailerTypes);
+        }
+
+        if (filter.DtoOptions.ContainsField(ItemFields.ProviderIds))
+        {
+            dbQuery = dbQuery.Include(e => e.Provider);
+        }
+
+        if (filter.DtoOptions.ContainsField(ItemFields.Settings))
+        {
+            dbQuery = dbQuery.Include(e => e.LockedFields);
+        }
+
+        if (filter.DtoOptions.EnableUserData)
+        {
+            dbQuery = dbQuery.Include(e => e.UserData);
+        }
 
         if (filter.DtoOptions.EnableImages)
         {
@@ -423,19 +459,14 @@ public sealed class BaseItemRepository
 
     private IQueryable<BaseItemEntity> ApplyQueryPaging(IQueryable<BaseItemEntity> dbQuery, InternalItemsQuery filter)
     {
-        if (filter.Limit.HasValue || filter.StartIndex.HasValue)
+        if (filter.StartIndex.HasValue && filter.StartIndex.Value > 0)
         {
-            var offset = filter.StartIndex ?? 0;
-
-            if (offset > 0)
-            {
-                dbQuery = dbQuery.Skip(offset);
-            }
+            dbQuery = dbQuery.Skip(filter.StartIndex.Value);
+        }
 
-            if (filter.Limit.HasValue)
-            {
-                dbQuery = dbQuery.Take(filter.Limit.Value);
-            }
+        if (filter.Limit.HasValue && filter.Limit.Value > 0)
+        {
+            dbQuery = dbQuery.Take(filter.Limit.Value);
         }
 
         return dbQuery;
@@ -446,6 +477,7 @@ public sealed class BaseItemRepository
         dbQuery = TranslateQuery(dbQuery, context, filter);
         dbQuery = ApplyGroupingFilter(context, dbQuery, filter);
         dbQuery = ApplyQueryPaging(dbQuery, filter);
+        dbQuery = ApplyNavigations(dbQuery, filter);
         return dbQuery;
     }
 
@@ -549,22 +581,34 @@ public sealed class BaseItemRepository
     }
 
     /// <inheritdoc  />
-    public void SaveImages(BaseItemDto item)
+    public async Task SaveImagesAsync(BaseItemDto item, CancellationToken cancellationToken = default)
     {
         ArgumentNullException.ThrowIfNull(item);
 
-        var images = item.ImageInfos.Select(e => Map(item.Id, e));
-        using var context = _dbProvider.CreateDbContext();
+        var images = item.ImageInfos.Select(e => Map(item.Id, e)).ToArray();
 
-        if (!context.BaseItems.Any(bi => bi.Id == item.Id))
+        var context = await _dbProvider.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
+        await using (context.ConfigureAwait(false))
         {
-            _logger.LogWarning("Unable to save ImageInfo for non existing BaseItem");
-            return;
-        }
+            if (!await context.BaseItems
+                .AnyAsync(bi => bi.Id == item.Id, cancellationToken)
+                .ConfigureAwait(false))
+            {
+                _logger.LogWarning("Unable to save ImageInfo for non existing BaseItem");
+                return;
+            }
 
-        context.BaseItemImageInfos.Where(e => e.ItemId == item.Id).ExecuteDelete();
-        context.BaseItemImageInfos.AddRange(images);
-        context.SaveChanges();
+            await context.BaseItemImageInfos
+                .Where(e => e.ItemId == item.Id)
+                .ExecuteDeleteAsync(cancellationToken)
+                .ConfigureAwait(false);
+
+            await context.BaseItemImageInfos
+                .AddRangeAsync(images, cancellationToken)
+                .ConfigureAwait(false);
+
+            await context.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
+        }
     }
 
     /// <inheritdoc  />
@@ -599,7 +643,6 @@ public sealed class BaseItemRepository
 
         var ids = tuples.Select(f => f.Item.Id).ToArray();
         var existingItems = context.BaseItems.Where(e => ids.Contains(e.Id)).Select(f => f.Id).ToArray();
-        var newItems = tuples.Where(e => !existingItems.Contains(e.Item.Id)).ToArray();
 
         foreach (var item in tuples)
         {
@@ -614,25 +657,25 @@ public sealed class BaseItemRepository
             else
             {
                 context.BaseItemProviders.Where(e => e.ItemId == entity.Id).ExecuteDelete();
+                context.BaseItemImageInfos.Where(e => e.ItemId == entity.Id).ExecuteDelete();
+                context.BaseItemMetadataFields.Where(e => e.ItemId == entity.Id).ExecuteDelete();
+
+                if (entity.Images is { Count: > 0 })
+                {
+                    context.BaseItemImageInfos.AddRange(entity.Images);
+                }
+
+                if (entity.LockedFields is { Count: > 0 })
+                {
+                    context.BaseItemMetadataFields.AddRange(entity.LockedFields);
+                }
+
                 context.BaseItems.Attach(entity).State = EntityState.Modified;
             }
         }
 
         context.SaveChanges();
 
-        foreach (var item in newItems)
-        {
-            // reattach old userData entries
-            var userKeys = item.UserDataKey.ToArray();
-            var retentionDate = (DateTime?)null;
-            context.UserData
-                .Where(e => e.ItemId == PlaceholderId)
-                .Where(e => userKeys.Contains(e.CustomDataKey))
-                .ExecuteUpdate(e => e
-                    .SetProperty(f => f.ItemId, item.Item.Id)
-                    .SetProperty(f => f.RetentionDate, retentionDate));
-        }
-
         var itemValueMaps = tuples
             .Select(e => (e.Item, Values: GetItemValuesToSave(e.Item, e.InheritedTags)))
             .ToArray();
@@ -729,6 +772,43 @@ public sealed class BaseItemRepository
     }
 
     /// <inheritdoc  />
+    public async Task ReattachUserDataAsync(BaseItemDto item, CancellationToken cancellationToken)
+    {
+        ArgumentNullException.ThrowIfNull(item);
+        cancellationToken.ThrowIfCancellationRequested();
+
+        var dbContext = await _dbProvider.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
+
+        await using (dbContext.ConfigureAwait(false))
+        {
+            var transaction = await dbContext.Database.BeginTransactionAsync(cancellationToken).ConfigureAwait(false);
+            await using (transaction.ConfigureAwait(false))
+            {
+                var userKeys = item.GetUserDataKeys().ToArray();
+                var retentionDate = (DateTime?)null;
+
+                await dbContext.UserData
+                    .Where(e => e.ItemId == PlaceholderId)
+                    .Where(e => userKeys.Contains(e.CustomDataKey))
+                    .ExecuteUpdateAsync(
+                        e => e
+                            .SetProperty(f => f.ItemId, item.Id)
+                            .SetProperty(f => f.RetentionDate, retentionDate),
+                        cancellationToken).ConfigureAwait(false);
+
+                // Rehydrate the cached userdata
+                item.UserData = await dbContext.UserData
+                    .AsNoTracking()
+                    .Where(e => e.ItemId == item.Id)
+                    .ToArrayAsync(cancellationToken)
+                    .ConfigureAwait(false);
+
+                await transaction.CommitAsync(cancellationToken).ConfigureAwait(false);
+            }
+        }
+    }
+
+    /// <inheritdoc  />
     public BaseItemDto? RetrieveItem(Guid id)
     {
         if (id.IsEmpty())
@@ -835,7 +915,7 @@ public sealed class BaseItemRepository
         }
 
         dto.ExtraIds = string.IsNullOrWhiteSpace(entity.ExtraIds) ? [] : entity.ExtraIds.Split('|').Select(e => Guid.Parse(e)).ToArray();
-        dto.ProductionLocations = entity.ProductionLocations?.Split('|') ?? [];
+        dto.ProductionLocations = entity.ProductionLocations?.Split('|', StringSplitOptions.RemoveEmptyEntries) ?? [];
         dto.Studios = entity.Studios?.Split('|') ?? [];
         dto.Tags = string.IsNullOrWhiteSpace(entity.Tags) ? [] : entity.Tags.Split('|');
 
@@ -997,7 +1077,7 @@ public sealed class BaseItemRepository
         }
 
         entity.ExtraIds = dto.ExtraIds is not null ? string.Join('|', dto.ExtraIds) : null;
-        entity.ProductionLocations = dto.ProductionLocations is not null ? string.Join('|', dto.ProductionLocations) : null;
+        entity.ProductionLocations = dto.ProductionLocations is not null ? string.Join('|', dto.ProductionLocations.Where(p => !string.IsNullOrWhiteSpace(p))) : null;
         entity.Studios = dto.Studios is not null ? string.Join('|', dto.Studios) : null;
         entity.Tags = dto.Tags is not null ? string.Join('|', dto.Tags) : null;
         entity.LockedFields = dto.LockedFields is not null ? dto.LockedFields
@@ -1121,7 +1201,7 @@ public sealed class BaseItemRepository
         return type.GetCustomAttribute<RequiresSourceSerialisationAttribute>() == null;
     }
 
-    private BaseItemDto DeserializeBaseItem(BaseItemEntity baseItemEntity, bool skipDeserialization = false)
+    private BaseItemDto? DeserializeBaseItem(BaseItemEntity baseItemEntity, bool skipDeserialization = false)
     {
         ArgumentNullException.ThrowIfNull(baseItemEntity, nameof(baseItemEntity));
         if (_serverConfigurationManager?.Configuration is null)
@@ -1144,11 +1224,19 @@ public sealed class BaseItemRepository
     /// <param name="logger">Logger.</param>
     /// <param name="appHost">The application server Host.</param>
     /// <param name="skipDeserialization">If only mapping should be processed.</param>
-    /// <returns>A mapped BaseItem.</returns>
-    /// <exception cref="InvalidOperationException">Will be thrown if an invalid serialisation is requested.</exception>
-    public static BaseItemDto DeserializeBaseItem(BaseItemEntity baseItemEntity, ILogger logger, IServerApplicationHost? appHost, bool skipDeserialization = false)
+    /// <returns>A mapped BaseItem, or null if the item type is unknown.</returns>
+    public static BaseItemDto? DeserializeBaseItem(BaseItemEntity baseItemEntity, ILogger logger, IServerApplicationHost? appHost, bool skipDeserialization = false)
     {
-        var type = GetType(baseItemEntity.Type) ?? throw new InvalidOperationException("Cannot deserialize unknown type.");
+        var type = GetType(baseItemEntity.Type);
+        if (type is null)
+        {
+            logger.LogWarning(
+                "Skipping item {ItemId} with unknown type '{ItemType}'. This may indicate a removed plugin or database corruption.",
+                baseItemEntity.Id,
+                baseItemEntity.Type);
+            return null;
+        }
+
         BaseItemDto? dto = null;
         if (TypeRequiresDeserialization(type) && baseItemEntity.Data is not null && !skipDeserialization)
         {
@@ -1174,7 +1262,7 @@ public sealed class BaseItemRepository
     {
         ArgumentNullException.ThrowIfNull(filter);
 
-        if (!filter.Limit.HasValue)
+        if (!(filter.Limit.HasValue && filter.Limit.Value > 0))
         {
             filter.EnableTotalRecordCount = false;
         }
@@ -1245,7 +1333,7 @@ public sealed class BaseItemRepository
             .AsSingleQuery()
             .Where(e => masterQuery.Contains(e.Id));
 
-        query = ApplyOrder(query, filter);
+        query = ApplyOrder(query, filter, context);
 
         var result = new QueryResult<(BaseItemDto, ItemCounts?)>();
         if (filter.EnableTotalRecordCount)
@@ -1253,19 +1341,14 @@ public sealed class BaseItemRepository
             result.TotalRecordCount = query.Count();
         }
 
-        if (filter.Limit.HasValue || filter.StartIndex.HasValue)
+        if (filter.StartIndex.HasValue && filter.StartIndex.Value > 0)
         {
-            var offset = filter.StartIndex ?? 0;
-
-            if (offset > 0)
-            {
-                query = query.Skip(offset);
-            }
+            query = query.Skip(filter.StartIndex.Value);
+        }
 
-            if (filter.Limit.HasValue)
-            {
-                query = query.Take(filter.Limit.Value);
-            }
+        if (filter.Limit.HasValue && filter.Limit.Value > 0)
+        {
+            query = query.Take(filter.Limit.Value);
         }
 
         IQueryable<BaseItemEntity>? itemCountQuery = null;
@@ -1320,10 +1403,9 @@ public sealed class BaseItemRepository
                 .. resultQuery
                     .AsEnumerable()
                     .Where(e => e is not null)
-                    .Select(e =>
-                    {
-                        return (DeserializeBaseItem(e.item, filter.SkipDeserialization), e.itemCount);
-                    })
+                    .Select(e => (Item: DeserializeBaseItem(e.item, filter.SkipDeserialization), e.itemCount))
+                    .Where(e => e.Item is not null)
+                    .Select(e => (e.Item!, e.itemCount))
             ];
         }
         else
@@ -1334,10 +1416,9 @@ public sealed class BaseItemRepository
                 .. query
                     .AsEnumerable()
                     .Where(e => e is not null)
-                    .Select<BaseItemEntity, (BaseItemDto, ItemCounts?)>(e =>
-                    {
-                        return (DeserializeBaseItem(e, filter.SkipDeserialization), null);
-                    })
+                    .Select(e => (Item: DeserializeBaseItem(e, filter.SkipDeserialization), ItemCounts: (ItemCounts?)null))
+                    .Where(e => e.Item is not null)
+                    .Select(e => (e.Item!, e.ItemCounts))
             ];
         }
 
@@ -1346,7 +1427,7 @@ public sealed class BaseItemRepository
 
     private static void PrepareFilterQuery(InternalItemsQuery query)
     {
-        if (query.Limit.HasValue && query.EnableGroupByMetadataKey)
+        if (query.Limit.HasValue && query.Limit.Value > 0 && query.EnableGroupByMetadataKey)
         {
             query.Limit = query.Limit.Value + 4;
         }
@@ -1357,14 +1438,54 @@ public sealed class BaseItemRepository
         }
     }
 
-    private string GetCleanValue(string value)
+    /// <summary>
+    /// Gets the clean value for search and sorting purposes.
+    /// </summary>
+    /// <param name="value">The value to clean.</param>
+    /// <returns>The cleaned value.</returns>
+    public static string GetCleanValue(string value)
     {
         if (string.IsNullOrWhiteSpace(value))
         {
             return value;
         }
 
-        return value.RemoveDiacritics().ToLowerInvariant();
+        var noDiacritics = value.RemoveDiacritics();
+
+        // Build a string where any punctuation or symbol is treated as a separator (space).
+        var sb = new StringBuilder(noDiacritics.Length);
+        var previousWasSpace = false;
+        foreach (var ch in noDiacritics)
+        {
+            char outCh;
+            if (char.IsLetterOrDigit(ch) || char.IsWhiteSpace(ch))
+            {
+                outCh = ch;
+            }
+            else
+            {
+                outCh = ' ';
+            }
+
+            // normalize any whitespace character to a single ASCII space.
+            if (char.IsWhiteSpace(outCh))
+            {
+                if (!previousWasSpace)
+                {
+                    sb.Append(' ');
+                    previousWasSpace = true;
+                }
+            }
+            else
+            {
+                sb.Append(outCh);
+                previousWasSpace = false;
+            }
+        }
+
+        // trim leading/trailing spaces that may have been added.
+        var collapsed = sb.ToString().Trim();
+        return collapsed.ToLowerInvariant();
     }
 
     private List<(ItemValueType MagicNumber, string Value)> GetItemValuesToSave(BaseItemDto item, List<string> inheritedTags)
@@ -1511,51 +1632,58 @@ public sealed class BaseItemRepository
             || query.IncludeItemTypes.Contains(BaseItemKind.Season);
     }
 
-    private IQueryable<BaseItemEntity> ApplyOrder(IQueryable<BaseItemEntity> query, InternalItemsQuery filter)
+    private IQueryable<BaseItemEntity> ApplyOrder(IQueryable<BaseItemEntity> query, InternalItemsQuery filter, JellyfinDbContext context)
     {
-        var orderBy = filter.OrderBy;
+        var orderBy = filter.OrderBy.Where(e => e.OrderBy != ItemSortBy.Default).ToArray();
         var hasSearch = !string.IsNullOrEmpty(filter.SearchTerm);
 
         if (hasSearch)
         {
-            orderBy = filter.OrderBy = [(ItemSortBy.SortName, SortOrder.Ascending), .. orderBy];
+            orderBy = [(ItemSortBy.SortName, SortOrder.Ascending), .. orderBy];
         }
-        else if (orderBy.Count == 0)
+        else if (orderBy.Length == 0)
         {
             return query.OrderBy(e => e.SortName);
         }
 
         IOrderedQueryable<BaseItemEntity>? orderedQuery = null;
 
+        // When searching, prioritize by match quality: exact match > prefix match > contains
+        if (hasSearch)
+        {
+            orderedQuery = query.OrderBy(OrderMapper.MapSearchRelevanceOrder(filter.SearchTerm!));
+        }
+
         var firstOrdering = orderBy.FirstOrDefault();
         if (firstOrdering != default)
         {
-            var expression = OrderMapper.MapOrderByField(firstOrdering.OrderBy, filter);
-            if (firstOrdering.SortOrder == SortOrder.Ascending)
+            var expression = OrderMapper.MapOrderByField(firstOrdering.OrderBy, filter, context);
+            if (orderedQuery is null)
             {
-                orderedQuery = query.OrderBy(expression);
+                // No search relevance ordering, start fresh
+                orderedQuery = firstOrdering.SortOrder == SortOrder.Ascending
+                    ? query.OrderBy(expression)
+                    : query.OrderByDescending(expression);
             }
             else
             {
-                orderedQuery = query.OrderByDescending(expression);
+                // Search relevance ordering already applied, chain with ThenBy
+                orderedQuery = firstOrdering.SortOrder == SortOrder.Ascending
+                    ? orderedQuery.ThenBy(expression)
+                    : orderedQuery.ThenByDescending(expression);
             }
 
             if (firstOrdering.OrderBy is ItemSortBy.Default or ItemSortBy.SortName)
             {
-                if (firstOrdering.SortOrder is SortOrder.Ascending)
-                {
-                    orderedQuery = orderedQuery.ThenBy(e => e.Name);
-                }
-                else
-                {
-                    orderedQuery = orderedQuery.ThenByDescending(e => e.Name);
-                }
+                orderedQuery = firstOrdering.SortOrder is SortOrder.Ascending
+                    ? orderedQuery.ThenBy(e => e.Name)
+                    : orderedQuery.ThenByDescending(e => e.Name);
             }
         }
 
         foreach (var item in orderBy.Skip(1))
         {
-            var expression = OrderMapper.MapOrderByField(item.OrderBy, filter);
+            var expression = OrderMapper.MapOrderByField(item.OrderBy, filter, context);
             if (item.SortOrder == SortOrder.Ascending)
             {
                 orderedQuery = orderedQuery!.ThenBy(expression);
@@ -1637,19 +1765,18 @@ public sealed class BaseItemRepository
         var tags = filter.Tags.ToList();
         var excludeTags = filter.ExcludeTags.ToList();
 
-        if (filter.IsMovie == true)
+        if (filter.IsMovie.HasValue)
         {
-            if (filter.IncludeItemTypes.Length == 0
-                || filter.IncludeItemTypes.Contains(BaseItemKind.Movie)
-                || filter.IncludeItemTypes.Contains(BaseItemKind.Trailer))
+            var shouldIncludeAllMovieTypes = filter.IsMovie.Value
+                && (filter.IncludeItemTypes.Length == 0
+                    || filter.IncludeItemTypes.Contains(BaseItemKind.Movie)
+                    || filter.IncludeItemTypes.Contains(BaseItemKind.Trailer));
+
+            if (!shouldIncludeAllMovieTypes)
             {
-                baseQuery = baseQuery.Where(e => e.IsMovie);
+                baseQuery = baseQuery.Where(e => e.IsMovie == filter.IsMovie.Value);
             }
         }
-        else if (filter.IsMovie.HasValue)
-        {
-            baseQuery = baseQuery.Where(e => e.IsMovie == filter.IsMovie);
-        }
 
         if (filter.IsSeries.HasValue)
         {
@@ -1694,15 +1821,16 @@ public sealed class BaseItemRepository
 
         if (!string.IsNullOrEmpty(filter.SearchTerm))
         {
-            var searchTerm = filter.SearchTerm.ToLower();
-            if (SearchWildcardTerms.Any(f => searchTerm.Contains(f)))
+            var cleanedSearchTerm = GetCleanValue(filter.SearchTerm);
+            var originalSearchTerm = filter.SearchTerm.ToLower();
+            if (SearchWildcardTerms.Any(f => cleanedSearchTerm.Contains(f)))
             {
-                searchTerm = $"%{searchTerm.Trim('%')}%";
-                baseQuery = baseQuery.Where(e => EF.Functions.Like(e.CleanName!.ToLower(), searchTerm) || (e.OriginalTitle != null && EF.Functions.Like(e.OriginalTitle.ToLower(), searchTerm)));
+                cleanedSearchTerm = $"%{cleanedSearchTerm.Trim('%')}%";
+                baseQuery = baseQuery.Where(e => EF.Functions.Like(e.CleanName!, cleanedSearchTerm) || (e.OriginalTitle != null && EF.Functions.Like(e.OriginalTitle.ToLower(), originalSearchTerm)));
             }
             else
             {
-                baseQuery = baseQuery.Where(e => e.CleanName!.ToLower().Contains(searchTerm) || (e.OriginalTitle != null && e.OriginalTitle.ToLower().Contains(searchTerm)));
+                baseQuery = baseQuery.Where(e => e.CleanName!.Contains(cleanedSearchTerm) || (e.OriginalTitle != null && e.OriginalTitle.ToLower().Contains(originalSearchTerm)));
             }
         }
 
@@ -1756,7 +1884,8 @@ public sealed class BaseItemRepository
 
         if (!string.IsNullOrWhiteSpace(filter.Path))
         {
-            baseQuery = baseQuery.Where(e => e.Path == filter.Path);
+            var pathToQuery = GetPathToSave(filter.Path);
+            baseQuery = baseQuery.Where(e => e.Path == pathToQuery);
         }
 
         if (!string.IsNullOrWhiteSpace(filter.PresentationUniqueKey))
@@ -1913,8 +2042,15 @@ public sealed class BaseItemRepository
 
         if (!string.IsNullOrWhiteSpace(filter.Name))
         {
-            var cleanName = GetCleanValue(filter.Name);
-            baseQuery = baseQuery.Where(e => e.CleanName == cleanName);
+            if (filter.UseRawName == true)
+            {
+                baseQuery = baseQuery.Where(e => e.Name == filter.Name);
+            }
+            else
+            {
+                var cleanName = GetCleanValue(filter.Name);
+                baseQuery = baseQuery.Where(e => e.CleanName == cleanName);
+            }
         }
 
         // These are the same, for now
@@ -1936,19 +2072,20 @@ public sealed class BaseItemRepository
 
         if (!string.IsNullOrWhiteSpace(filter.NameStartsWith))
         {
-            baseQuery = baseQuery.Where(e => e.SortName!.StartsWith(filter.NameStartsWith));
+            var startsWithLower = filter.NameStartsWith.ToLowerInvariant();
+            baseQuery = baseQuery.Where(e => e.SortName!.StartsWith(startsWithLower));
         }
 
         if (!string.IsNullOrWhiteSpace(filter.NameStartsWithOrGreater))
         {
-            // i hate this
-            baseQuery = baseQuery.Where(e => e.SortName!.FirstOrDefault() > filter.NameStartsWithOrGreater[0] || e.Name!.FirstOrDefault() > filter.NameStartsWithOrGreater[0]);
+            var startsOrGreaterLower = filter.NameStartsWithOrGreater.ToLowerInvariant();
+            baseQuery = baseQuery.Where(e => e.SortName!.CompareTo(startsOrGreaterLower) >= 0);
         }
 
         if (!string.IsNullOrWhiteSpace(filter.NameLessThan))
         {
-            // i hate this
-            baseQuery = baseQuery.Where(e => e.SortName!.FirstOrDefault() < filter.NameLessThan[0] || e.Name!.FirstOrDefault() < filter.NameLessThan[0]);
+            var lessThanLower = filter.NameLessThan.ToLowerInvariant();
+            baseQuery = baseQuery.Where(e => e.SortName!.CompareTo(lessThanLower ) < 0);
         }
 
         if (filter.ImageTypes.Length > 0)
@@ -2046,7 +2183,7 @@ public sealed class BaseItemRepository
 
         if (filter.ExcludeArtistIds.Length > 0)
         {
-            baseQuery = baseQuery.WhereReferencedItem(context, ItemValueType.Artist, filter.ExcludeArtistIds, true);
+            baseQuery = baseQuery.WhereReferencedItemMultipleTypes(context, [ItemValueType.Artist, ItemValueType.AlbumArtist], filter.ExcludeArtistIds, true);
         }
 
         if (filter.GenreIds.Count > 0)
@@ -2353,17 +2490,23 @@ public sealed class BaseItemRepository
 
         if (filter.HasImdbId.HasValue)
         {
-            baseQuery = baseQuery.Where(e => e.Provider!.Any(f => f.ProviderId == "imdb"));
+            baseQuery = filter.HasImdbId.Value
+                ? baseQuery.Where(e => e.Provider!.Any(f => f.ProviderId.ToLower() == MetadataProvider.Imdb.ToString().ToLower()))
+                : baseQuery.Where(e => e.Provider!.All(f => f.ProviderId.ToLower() != MetadataProvider.Imdb.ToString().ToLower()));
         }
 
         if (filter.HasTmdbId.HasValue)
         {
-            baseQuery = baseQuery.Where(e => e.Provider!.Any(f => f.ProviderId == "tmdb"));
+            baseQuery = filter.HasTmdbId.Value
+                ? baseQuery.Where(e => e.Provider!.Any(f => f.ProviderId.ToLower() == MetadataProvider.Tmdb.ToString().ToLower()))
+                : baseQuery.Where(e => e.Provider!.All(f => f.ProviderId.ToLower() != MetadataProvider.Tmdb.ToString().ToLower()));
         }
 
         if (filter.HasTvdbId.HasValue)
         {
-            baseQuery = baseQuery.Where(e => e.Provider!.Any(f => f.ProviderId == "tvdb"));
+            baseQuery = filter.HasTvdbId.Value
+                ? baseQuery.Where(e => e.Provider!.Any(f => f.ProviderId.ToLower() == MetadataProvider.Tvdb.ToString().ToLower()))
+                : baseQuery.Where(e => e.Provider!.All(f => f.ProviderId.ToLower() != MetadataProvider.Tvdb.ToString().ToLower()));
         }
 
         var queryTopParentIds = filter.TopParentIds;
@@ -2401,40 +2544,24 @@ public sealed class BaseItemRepository
 
         if (filter.ExcludeInheritedTags.Length > 0)
         {
-            baseQuery = baseQuery
-                .Where(e => !e.ItemValues!.Where(w => w.ItemValue.Type == ItemValueType.InheritedTags || w.ItemValue.Type == ItemValueType.Tags)
-                .Any(f => filter.ExcludeInheritedTags.Contains(f.ItemValue.CleanValue)));
+            var excludedTags = filter.ExcludeInheritedTags;
+            baseQuery = baseQuery.Where(e =>
+                !e.ItemValues!.Any(f => f.ItemValue.Type == ItemValueType.Tags && excludedTags.Contains(f.ItemValue.CleanValue))
+                && (!e.SeriesId.HasValue || !context.ItemValuesMap.Any(f => f.ItemId == e.SeriesId.Value && f.ItemValue.Type == ItemValueType.Tags && excludedTags.Contains(f.ItemValue.CleanValue))));
         }
 
         if (filter.IncludeInheritedTags.Length > 0)
         {
-            // Episodes do not store inherit tags from their parents in the database, and the tag may be still required by the client.
-            // In addition to the tags for the episodes themselves, we need to manually query its parent (the season)'s tags as well.
-            if (includeTypes.Length == 1 && includeTypes.FirstOrDefault() is BaseItemKind.Episode)
-            {
-                baseQuery = baseQuery
-                    .Where(e => e.ItemValues!.Where(f => f.ItemValue.Type == ItemValueType.InheritedTags || f.ItemValue.Type == ItemValueType.Tags)
-                        .Any(f => filter.IncludeInheritedTags.Contains(f.ItemValue.CleanValue))
-                        ||
-                        (e.ParentId.HasValue && context.ItemValuesMap.Where(w => w.ItemId == e.ParentId.Value && (w.ItemValue.Type == ItemValueType.InheritedTags || w.ItemValue.Type == ItemValueType.Tags))
-                        .Any(f => filter.IncludeInheritedTags.Contains(f.ItemValue.CleanValue))));
-            }
+            var includeTags = filter.IncludeInheritedTags;
+            var isPlaylistOnlyQuery = includeTypes.Length == 1 && includeTypes.FirstOrDefault() == BaseItemKind.Playlist;
+            baseQuery = baseQuery.Where(e =>
+                e.ItemValues!.Any(f => f.ItemValue.Type == ItemValueType.Tags && includeTags.Contains(f.ItemValue.CleanValue))
 
-            // A playlist should be accessible to its owner regardless of allowed tags.
-            else if (includeTypes.Length == 1 && includeTypes.FirstOrDefault() is BaseItemKind.Playlist)
-            {
-                baseQuery = baseQuery
-                    .Where(e => e.ItemValues!.Where(f => f.ItemValue.Type == ItemValueType.InheritedTags || f.ItemValue.Type == ItemValueType.Tags)
-                        .Any(f => filter.IncludeInheritedTags.Contains(f.ItemValue.CleanValue))
-                        || e.Data!.Contains($"OwnerUserId\":\"{filter.User!.Id:N}\""));
-                // d        ^^ this is stupid it hate this.
-            }
-            else
-            {
-                baseQuery = baseQuery
-                    .Where(e => e.ItemValues!.Where(f => f.ItemValue.Type == ItemValueType.InheritedTags || f.ItemValue.Type == ItemValueType.Tags)
-                        .Any(f => filter.IncludeInheritedTags.Contains(f.ItemValue.CleanValue)));
-            }
+                // For seasons and episodes, we also need to check the parent series' tags.
+                || (e.SeriesId.HasValue && context.ItemValuesMap.Any(f => f.ItemId == e.SeriesId.Value && f.ItemValue.Type == ItemValueType.Tags && includeTags.Contains(f.ItemValue.CleanValue)))
+
+                // A playlist should be accessible to its owner regardless of allowed tags
+                || (isPlaylistOnlyQuery && e.Data!.Contains($"OwnerUserId\":\"{filter.User!.Id:N}\"")));
         }
 
         if (filter.SeriesStatuses.Length > 0)
@@ -2588,6 +2715,21 @@ public sealed class BaseItemRepository
             .Where(e => artistNames.Contains(e.Name))
             .ToArray();
 
-        return artists.GroupBy(e => e.Name).ToDictionary(e => e.Key!, e => e.Select(f => DeserializeBaseItem(f)).Cast<MusicArtist>().ToArray());
+        var lookup = artists
+            .GroupBy(e => e.Name!)
+            .ToDictionary(
+                g => g.Key,
+                g => g.Select(f => DeserializeBaseItem(f)).Where(dto => dto is not null).Cast<MusicArtist>().ToArray());
+
+        var result = new Dictionary<string, MusicArtist[]>(artistNames.Count);
+        foreach (var name in artistNames)
+        {
+            if (lookup.TryGetValue(name, out var artistArray))
+            {
+                result[name] = artistArray;
+            }
+        }
+
+        return result;
     }
 }
diff --git a/Jellyfin.Server.Implementations/Item/MediaStreamRepository.cs b/Jellyfin.Server.Implementations/Item/MediaStreamRepository.cs
index 7eb13b7408..64874ccad7 100644
--- a/Jellyfin.Server.Implementations/Item/MediaStreamRepository.cs
+++ b/Jellyfin.Server.Implementations/Item/MediaStreamRepository.cs
@@ -158,6 +158,12 @@ public class MediaStreamRepository : IMediaStreamRepository
             dto.LocalizedDefault = _localization.GetLocalizedString("Default");
             dto.LocalizedExternal = _localization.GetLocalizedString("External");
 
+            if (!string.IsNullOrEmpty(dto.Language))
+            {
+                var culture = _localization.FindLanguageInfo(dto.Language);
+                dto.LocalizedLanguage = culture?.DisplayName;
+            }
+
             if (dto.Type is MediaStreamType.Subtitle)
             {
                 dto.LocalizedUndefined = _localization.GetLocalizedString("Undefined");
diff --git a/Jellyfin.Server.Implementations/Item/OrderMapper.cs b/Jellyfin.Server.Implementations/Item/OrderMapper.cs
index a0c1270311..1ae7cc6c4a 100644
--- a/Jellyfin.Server.Implementations/Item/OrderMapper.cs
+++ b/Jellyfin.Server.Implementations/Item/OrderMapper.cs
@@ -1,8 +1,12 @@
+#pragma warning disable RS0030 // Do not use banned APIs
+
 using System;
 using System.Linq;
 using System.Linq.Expressions;
 using Jellyfin.Data.Enums;
+using Jellyfin.Database.Implementations;
 using Jellyfin.Database.Implementations.Entities;
+using Jellyfin.Extensions;
 using MediaBrowser.Controller.Entities;
 using Microsoft.EntityFrameworkCore;
 
@@ -18,40 +22,77 @@ public static class OrderMapper
     /// </summary>
     /// <param name="sortBy">Item property to sort by.</param>
     /// <param name="query">Context Query.</param>
+    /// <param name="jellyfinDbContext">Context.</param>
     /// <returns>Func to be executed later for sorting query.</returns>
-    public static Expression<Func<BaseItemEntity, object?>> MapOrderByField(ItemSortBy sortBy, InternalItemsQuery query)
+    public static Expression<Func<BaseItemEntity, object?>> MapOrderByField(ItemSortBy sortBy, InternalItemsQuery query, JellyfinDbContext jellyfinDbContext)
     {
-        return sortBy switch
+        return (sortBy, query.User) switch
         {
-            ItemSortBy.AirTime => e => e.SortName, // TODO
-            ItemSortBy.Runtime => e => e.RunTimeTicks,
-            ItemSortBy.Random => e => EF.Functions.Random(),
-            ItemSortBy.DatePlayed => e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id))!.LastPlayedDate,
-            ItemSortBy.PlayCount => e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id))!.PlayCount,
-            ItemSortBy.IsFavoriteOrLiked => e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id))!.IsFavorite,
-            ItemSortBy.IsFolder => e => e.IsFolder,
-            ItemSortBy.IsPlayed => e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id))!.Played,
-            ItemSortBy.IsUnplayed => e => !e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id))!.Played,
-            ItemSortBy.DateLastContentAdded => e => e.DateLastMediaAdded,
-            ItemSortBy.Artist => e => e.ItemValues!.Where(f => f.ItemValue.Type == ItemValueType.Artist).Select(f => f.ItemValue.CleanValue).FirstOrDefault(),
-            ItemSortBy.AlbumArtist => e => e.ItemValues!.Where(f => f.ItemValue.Type == ItemValueType.AlbumArtist).Select(f => f.ItemValue.CleanValue).FirstOrDefault(),
-            ItemSortBy.Studio => e => e.ItemValues!.Where(f => f.ItemValue.Type == ItemValueType.Studios).Select(f => f.ItemValue.CleanValue).FirstOrDefault(),
-            ItemSortBy.OfficialRating => e => e.InheritedParentalRatingValue,
-            // ItemSortBy.SeriesDatePlayed => "(Select MAX(LastPlayedDate) from TypedBaseItems B" + GetJoinUserDataText(query) + " where Played=1 and B.SeriesPresentationUniqueKey=A.PresentationUniqueKey)",
-            ItemSortBy.SeriesSortName => e => e.SeriesName,
+            (ItemSortBy.AirTime, _) => e => e.SortName, // TODO
+            (ItemSortBy.Runtime, _) => e => e.RunTimeTicks,
+            (ItemSortBy.Random, _) => e => EF.Functions.Random(),
+            (ItemSortBy.DatePlayed, _) => e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id))!.LastPlayedDate,
+            (ItemSortBy.PlayCount, _) => e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id))!.PlayCount,
+            (ItemSortBy.IsFavoriteOrLiked, _) => e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id))!.IsFavorite,
+            (ItemSortBy.IsFolder, _) => e => e.IsFolder,
+            (ItemSortBy.IsPlayed, _) => e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id))!.Played,
+            (ItemSortBy.IsUnplayed, _) => e => !e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id))!.Played,
+            (ItemSortBy.DateLastContentAdded, _) => e => e.DateLastMediaAdded,
+            (ItemSortBy.Artist, _) => e => e.ItemValues!.Where(f => f.ItemValue.Type == ItemValueType.Artist).Select(f => f.ItemValue.CleanValue).FirstOrDefault(),
+            (ItemSortBy.AlbumArtist, _) => e => e.ItemValues!.Where(f => f.ItemValue.Type == ItemValueType.AlbumArtist).Select(f => f.ItemValue.CleanValue).FirstOrDefault(),
+            (ItemSortBy.Studio, _) => e => e.ItemValues!.Where(f => f.ItemValue.Type == ItemValueType.Studios).Select(f => f.ItemValue.CleanValue).FirstOrDefault(),
+            (ItemSortBy.OfficialRating, _) => e => e.InheritedParentalRatingValue,
+            (ItemSortBy.SeriesSortName, _) => e => e.SeriesName,
+            (ItemSortBy.Album, _) => e => e.Album,
+            (ItemSortBy.DateCreated, _) => e => e.DateCreated,
+            (ItemSortBy.PremiereDate, _) => e => (e.PremiereDate ?? (e.ProductionYear.HasValue ? DateTime.MinValue.AddYears(e.ProductionYear.Value - 1) : null)),
+            (ItemSortBy.StartDate, _) => e => e.StartDate,
+            (ItemSortBy.Name, _) => e => e.CleanName,
+            (ItemSortBy.CommunityRating, _) => e => e.CommunityRating,
+            (ItemSortBy.ProductionYear, _) => e => e.ProductionYear,
+            (ItemSortBy.CriticRating, _) => e => e.CriticRating,
+            (ItemSortBy.VideoBitRate, _) => e => e.TotalBitrate,
+            (ItemSortBy.ParentIndexNumber, _) => e => e.ParentIndexNumber,
+            (ItemSortBy.IndexNumber, _) => e => e.IndexNumber,
+            (ItemSortBy.SeriesDatePlayed, not null) => e =>
+                            jellyfinDbContext.BaseItems
+                                .Where(w => w.SeriesPresentationUniqueKey == e.PresentationUniqueKey)
+                                .Join(jellyfinDbContext.UserData.Where(w => w.UserId == query.User.Id && w.Played), f => f.Id, f => f.ItemId, (item, userData) => userData.LastPlayedDate)
+                                .Max(f => f),
+            (ItemSortBy.SeriesDatePlayed, null) => e => jellyfinDbContext.BaseItems.Where(w => w.SeriesPresentationUniqueKey == e.PresentationUniqueKey)
+                                .Join(jellyfinDbContext.UserData.Where(w => w.Played), f => f.Id, f => f.ItemId, (item, userData) => userData.LastPlayedDate)
+                                .Max(f => f),
+            // ItemSortBy.SeriesDatePlayed => e => jellyfinDbContext.UserData
+            //     .Where(u => u.Item!.SeriesPresentationUniqueKey == e.PresentationUniqueKey && u.Played)
+            //     .Max(f => f.LastPlayedDate),
             // ItemSortBy.AiredEpisodeOrder => "AiredEpisodeOrder",
-            ItemSortBy.Album => e => e.Album,
-            ItemSortBy.DateCreated => e => e.DateCreated,
-            ItemSortBy.PremiereDate => e => (e.PremiereDate ?? (e.ProductionYear.HasValue ? DateTime.MinValue.AddYears(e.ProductionYear.Value - 1) : null)),
-            ItemSortBy.StartDate => e => e.StartDate,
-            ItemSortBy.Name => e => e.CleanName,
-            ItemSortBy.CommunityRating => e => e.CommunityRating,
-            ItemSortBy.ProductionYear => e => e.ProductionYear,
-            ItemSortBy.CriticRating => e => e.CriticRating,
-            ItemSortBy.VideoBitRate => e => e.TotalBitrate,
-            ItemSortBy.ParentIndexNumber => e => e.ParentIndexNumber,
-            ItemSortBy.IndexNumber => e => e.IndexNumber,
             _ => e => e.SortName
         };
     }
+
+    /// <summary>
+    /// Creates an expression to order search results by match quality.
+    /// Prioritizes: exact match (0) > prefix match with word boundary (1) > prefix match (2) > contains (3).
+    /// </summary>
+    /// <param name="searchTerm">The search term to match against.</param>
+    /// <returns>An expression that returns an integer representing match quality (lower is better).</returns>
+    public static Expression<Func<BaseItemEntity, int>> MapSearchRelevanceOrder(string searchTerm)
+    {
+        var cleanSearchTerm = GetCleanValue(searchTerm);
+        var searchPrefix = cleanSearchTerm + " ";
+        return e =>
+            e.CleanName == cleanSearchTerm ? 0 :
+            e.CleanName!.StartsWith(searchPrefix) ? 1 :
+            e.CleanName!.StartsWith(cleanSearchTerm) ? 2 : 3;
+    }
+
+    private static string GetCleanValue(string value)
+    {
+        if (string.IsNullOrWhiteSpace(value))
+        {
+            return value;
+        }
+
+        return value.RemoveDiacritics().ToLowerInvariant();
+    }
 }
diff --git a/Jellyfin.Server.Implementations/Item/PeopleRepository.cs b/Jellyfin.Server.Implementations/Item/PeopleRepository.cs
index 355ed64797..e2569241d2 100644
--- a/Jellyfin.Server.Implementations/Item/PeopleRepository.cs
+++ b/Jellyfin.Server.Implementations/Item/PeopleRepository.cs
@@ -74,9 +74,10 @@ public class PeopleRepository(IDbContextFactory<JellyfinDbContext> dbProvider, I
     /// <inheritdoc />
     public void UpdatePeople(Guid itemId, IReadOnlyList<PersonInfo> people)
     {
-        foreach (var item in people.Where(e => e.Role is null))
+        foreach (var person in people)
         {
-            item.Role = string.Empty;
+            person.Name = person.Name.Trim();
+            person.Role = person.Role?.Trim() ?? string.Empty;
         }
 
         // multiple metadata providers can provide the _same_ person