WIP BaseItem search refactoring

9 files changed, 1408 insertions, 1250 deletions

diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs
index a2aeaf0fc..94a5eba81 100644
--- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs
+++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs
@@ -59,119 +59,6 @@ namespace Emby.Server.Implementations.Data
         private readonly TypeMapper _typeMapper;
         private readonly JsonSerializerOptions _jsonOptions;
 
-        private readonly ItemFields[] _allItemFields = Enum.GetValues<ItemFields>();
-
-        private static readonly string _mediaStreamSaveColumnsInsertQuery =
-            $"insert into mediastreams ({string.Join(',', _mediaStreamSaveColumns)}) values ";
-
-        private static readonly string _mediaStreamSaveColumnsSelectQuery =
-            $"select {string.Join(',', _mediaStreamSaveColumns)} from mediastreams where ItemId=@ItemId";
-
-        private static readonly string[] _mediaAttachmentSaveColumns =
-        {
-            "ItemId",
-            "AttachmentIndex",
-            "Codec",
-            "CodecTag",
-            "Comment",
-            "Filename",
-            "MIMEType"
-        };
-
-        private static readonly string _mediaAttachmentSaveColumnsSelectQuery =
-            $"select {string.Join(',', _mediaAttachmentSaveColumns)} from mediaattachments where ItemId=@ItemId";
-
-        private static readonly string _mediaAttachmentInsertPrefix = BuildMediaAttachmentInsertPrefix();
-
-        private static readonly BaseItemKind[] _programTypes = new[]
-        {
-            BaseItemKind.Program,
-            BaseItemKind.TvChannel,
-            BaseItemKind.LiveTvProgram,
-            BaseItemKind.LiveTvChannel
-        };
-
-        private static readonly BaseItemKind[] _programExcludeParentTypes = new[]
-        {
-            BaseItemKind.Series,
-            BaseItemKind.Season,
-            BaseItemKind.MusicAlbum,
-            BaseItemKind.MusicArtist,
-            BaseItemKind.PhotoAlbum
-        };
-
-        private static readonly BaseItemKind[] _serviceTypes = new[]
-        {
-            BaseItemKind.TvChannel,
-            BaseItemKind.LiveTvChannel
-        };
-
-        private static readonly BaseItemKind[] _startDateTypes = new[]
-        {
-            BaseItemKind.Program,
-            BaseItemKind.LiveTvProgram
-        };
-
-        private static readonly BaseItemKind[] _seriesTypes = new[]
-        {
-            BaseItemKind.Book,
-            BaseItemKind.AudioBook,
-            BaseItemKind.Episode,
-            BaseItemKind.Season
-        };
-
-        private static readonly BaseItemKind[] _artistExcludeParentTypes = new[]
-        {
-            BaseItemKind.Series,
-            BaseItemKind.Season,
-            BaseItemKind.PhotoAlbum
-        };
-
-        private static readonly BaseItemKind[] _artistsTypes = new[]
-        {
-            BaseItemKind.Audio,
-            BaseItemKind.MusicAlbum,
-            BaseItemKind.MusicVideo,
-            BaseItemKind.AudioBook
-        };
-
-        private static readonly Dictionary<BaseItemKind, string> _baseItemKindNames = new()
-        {
-            { BaseItemKind.AggregateFolder, typeof(AggregateFolder).FullName },
-            { BaseItemKind.Audio, typeof(Audio).FullName },
-            { BaseItemKind.AudioBook, typeof(AudioBook).FullName },
-            { BaseItemKind.BasePluginFolder, typeof(BasePluginFolder).FullName },
-            { BaseItemKind.Book, typeof(Book).FullName },
-            { BaseItemKind.BoxSet, typeof(BoxSet).FullName },
-            { BaseItemKind.Channel, typeof(Channel).FullName },
-            { BaseItemKind.CollectionFolder, typeof(CollectionFolder).FullName },
-            { BaseItemKind.Episode, typeof(Episode).FullName },
-            { BaseItemKind.Folder, typeof(Folder).FullName },
-            { BaseItemKind.Genre, typeof(Genre).FullName },
-            { BaseItemKind.Movie, typeof(Movie).FullName },
-            { BaseItemKind.LiveTvChannel, typeof(LiveTvChannel).FullName },
-            { BaseItemKind.LiveTvProgram, typeof(LiveTvProgram).FullName },
-            { BaseItemKind.MusicAlbum, typeof(MusicAlbum).FullName },
-            { BaseItemKind.MusicArtist, typeof(MusicArtist).FullName },
-            { BaseItemKind.MusicGenre, typeof(MusicGenre).FullName },
-            { BaseItemKind.MusicVideo, typeof(MusicVideo).FullName },
-            { BaseItemKind.Person, typeof(Person).FullName },
-            { BaseItemKind.Photo, typeof(Photo).FullName },
-            { BaseItemKind.PhotoAlbum, typeof(PhotoAlbum).FullName },
-            { BaseItemKind.Playlist, typeof(Playlist).FullName },
-            { BaseItemKind.PlaylistsFolder, typeof(PlaylistsFolder).FullName },
-            { BaseItemKind.Season, typeof(Season).FullName },
-            { BaseItemKind.Series, typeof(Series).FullName },
-            { BaseItemKind.Studio, typeof(Studio).FullName },
-            { BaseItemKind.Trailer, typeof(Trailer).FullName },
-            { BaseItemKind.TvChannel, typeof(LiveTvChannel).FullName },
-            { BaseItemKind.TvProgram, typeof(LiveTvProgram).FullName },
-            { BaseItemKind.UserRootFolder, typeof(UserRootFolder).FullName },
-            { BaseItemKind.UserView, typeof(UserView).FullName },
-            { BaseItemKind.Video, typeof(Video).FullName },
-            { BaseItemKind.Year, typeof(Year).FullName }
-        };
-
         /// <summary>
         /// Initializes a new instance of the <see cref="SqliteItemRepository"/> class.
         /// </summary>
@@ -210,948 +97,6 @@ namespace Emby.Server.Implementations.Data
         /// <inheritdoc />
         protected override TempStoreMode TempStore => TempStoreMode.Memory;
 
-        /// <summary>
-        /// Opens the connection to the database.
-        /// </summary>
-        public override void Initialize()
-        {
-            base.Initialize();
-
-            const string CreateMediaStreamsTableCommand
-                    = "create table if not exists mediastreams (ItemId GUID, StreamIndex INT, StreamType TEXT, Codec TEXT, Language TEXT, ChannelLayout TEXT, Profile TEXT, AspectRatio TEXT, Path TEXT, IsInterlaced BIT, BitRate INT NULL, Channels INT NULL, SampleRate INT NULL, IsDefault BIT, IsForced BIT, IsExternal BIT, Height INT NULL, Width INT NULL, AverageFrameRate FLOAT NULL, RealFrameRate FLOAT NULL, Level FLOAT NULL, PixelFormat TEXT, BitDepth INT NULL, IsAnamorphic BIT NULL, RefFrames INT NULL, CodecTag TEXT NULL, Comment TEXT NULL, NalLengthSize TEXT NULL, IsAvc BIT NULL, Title TEXT NULL, TimeBase TEXT NULL, CodecTimeBase TEXT NULL, ColorPrimaries TEXT NULL, ColorSpace TEXT NULL, ColorTransfer TEXT NULL, DvVersionMajor INT NULL, DvVersionMinor INT NULL, DvProfile INT NULL, DvLevel INT NULL, RpuPresentFlag INT NULL, ElPresentFlag INT NULL, BlPresentFlag INT NULL, DvBlSignalCompatibilityId INT NULL, IsHearingImpaired BIT NULL, Rotation INT NULL, PRIMARY KEY (ItemId, StreamIndex))";
-
-            const string CreateMediaAttachmentsTableCommand
-                    = "create table if not exists mediaattachments (ItemId GUID, AttachmentIndex INT, Codec TEXT, CodecTag TEXT NULL, Comment TEXT NULL, Filename TEXT NULL, MIMEType TEXT NULL, PRIMARY KEY (ItemId, AttachmentIndex))";
-
-            string[] queries =
-            {
-                "create table if not exists TypedBaseItems (guid GUID primary key NOT NULL, type TEXT NOT NULL, data BLOB NULL, ParentId GUID NULL, Path TEXT NULL)",
-
-                "create table if not exists AncestorIds (ItemId GUID NOT NULL, AncestorId GUID NOT NULL, AncestorIdText TEXT NOT NULL, PRIMARY KEY (ItemId, AncestorId))",
-                "create index if not exists idx_AncestorIds1 on AncestorIds(AncestorId)",
-                "create index if not exists idx_AncestorIds5 on AncestorIds(AncestorIdText,ItemId)",
-
-                "create table if not exists ItemValues (ItemId GUID NOT NULL, Type INT NOT NULL, Value TEXT NOT NULL, CleanValue TEXT NOT NULL)",
-
-                "create table if not exists People (ItemId GUID, Name TEXT NOT NULL, Role TEXT, PersonType TEXT, SortOrder int, ListOrder int)",
-
-                "drop index if exists idxPeopleItemId",
-                "create index if not exists idxPeopleItemId1 on People(ItemId,ListOrder)",
-                "create index if not exists idxPeopleName on People(Name)",
-
-                "create table if not exists " + ChaptersTableName + " (ItemId GUID, ChapterIndex INT NOT NULL, StartPositionTicks BIGINT NOT NULL, Name TEXT, ImagePath TEXT, PRIMARY KEY (ItemId, ChapterIndex))",
-
-                CreateMediaStreamsTableCommand,
-                CreateMediaAttachmentsTableCommand,
-
-                "pragma shrink_memory"
-            };
-
-            string[] postQueries =
-            {
-                "create index if not exists idx_PathTypedBaseItems on TypedBaseItems(Path)",
-                "create index if not exists idx_ParentIdTypedBaseItems on TypedBaseItems(ParentId)",
-
-                "create index if not exists idx_PresentationUniqueKey on TypedBaseItems(PresentationUniqueKey)",
-                "create index if not exists idx_GuidTypeIsFolderIsVirtualItem on TypedBaseItems(Guid,Type,IsFolder,IsVirtualItem)",
-                "create index if not exists idx_CleanNameType on TypedBaseItems(CleanName,Type)",
-
-                // covering index
-                "create index if not exists idx_TopParentIdGuid on TypedBaseItems(TopParentId,Guid)",
-
-                // series
-                "create index if not exists idx_TypeSeriesPresentationUniqueKey1 on TypedBaseItems(Type,SeriesPresentationUniqueKey,PresentationUniqueKey,SortName)",
-
-                // series counts
-                // seriesdateplayed sort order
-                "create index if not exists idx_TypeSeriesPresentationUniqueKey3 on TypedBaseItems(SeriesPresentationUniqueKey,Type,IsFolder,IsVirtualItem)",
-
-                // live tv programs
-                "create index if not exists idx_TypeTopParentIdStartDate on TypedBaseItems(Type,TopParentId,StartDate)",
-
-                // covering index for getitemvalues
-                "create index if not exists idx_TypeTopParentIdGuid on TypedBaseItems(Type,TopParentId,Guid)",
-
-                // used by movie suggestions
-                "create index if not exists idx_TypeTopParentIdGroup on TypedBaseItems(Type,TopParentId,PresentationUniqueKey)",
-                "create index if not exists idx_TypeTopParentId5 on TypedBaseItems(TopParentId,IsVirtualItem)",
-
-                // latest items
-                "create index if not exists idx_TypeTopParentId9 on TypedBaseItems(TopParentId,Type,IsVirtualItem,PresentationUniqueKey,DateCreated)",
-                "create index if not exists idx_TypeTopParentId8 on TypedBaseItems(TopParentId,IsFolder,IsVirtualItem,PresentationUniqueKey,DateCreated)",
-
-                // resume
-                "create index if not exists idx_TypeTopParentId7 on TypedBaseItems(TopParentId,MediaType,IsVirtualItem,PresentationUniqueKey)",
-
-                // items by name
-                "create index if not exists idx_ItemValues6 on ItemValues(ItemId,Type,CleanValue)",
-                "create index if not exists idx_ItemValues7 on ItemValues(Type,CleanValue,ItemId)",
-
-                // Used to update inherited tags
-                "create index if not exists idx_ItemValues8 on ItemValues(Type, ItemId, Value)",
-
-                "CREATE INDEX IF NOT EXISTS idx_TypedBaseItemsUserDataKeyType ON TypedBaseItems(UserDataKey, Type)",
-                "CREATE INDEX IF NOT EXISTS idx_PeopleNameListOrder ON People(Name, ListOrder)"
-            };
-
-            using (var connection = GetConnection())
-            using (var transaction = connection.BeginTransaction())
-            {
-                connection.Execute(string.Join(';', queries));
-
-                var existingColumnNames = GetColumnNames(connection, "AncestorIds");
-                AddColumn(connection, "AncestorIds", "AncestorIdText", "Text", existingColumnNames);
-
-                existingColumnNames = GetColumnNames(connection, "TypedBaseItems");
-
-                AddColumn(connection, "TypedBaseItems", "Path", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "StartDate", "DATETIME", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "EndDate", "DATETIME", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "ChannelId", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "IsMovie", "BIT", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "CommunityRating", "Float", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "CustomRating", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "IndexNumber", "INT", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "IsLocked", "BIT", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "Name", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "OfficialRating", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "MediaType", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "Overview", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "ParentIndexNumber", "INT", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "PremiereDate", "DATETIME", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "ProductionYear", "INT", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "ParentId", "GUID", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "Genres", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "SortName", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "ForcedSortName", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "RunTimeTicks", "BIGINT", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "DateCreated", "DATETIME", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "DateModified", "DATETIME", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "IsSeries", "BIT", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "EpisodeTitle", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "IsRepeat", "BIT", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "PreferredMetadataLanguage", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "PreferredMetadataCountryCode", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "DateLastRefreshed", "DATETIME", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "DateLastSaved", "DATETIME", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "IsInMixedFolder", "BIT", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "LockedFields", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "Studios", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "Audio", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "ExternalServiceId", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "Tags", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "IsFolder", "BIT", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "InheritedParentalRatingValue", "INT", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "UnratedType", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "TopParentId", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "TrailerTypes", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "CriticRating", "Float", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "CleanName", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "PresentationUniqueKey", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "OriginalTitle", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "PrimaryVersionId", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "DateLastMediaAdded", "DATETIME", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "Album", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "LUFS", "Float", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "NormalizationGain", "Float", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "IsVirtualItem", "BIT", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "SeriesName", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "UserDataKey", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "SeasonName", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "SeasonId", "GUID", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "SeriesId", "GUID", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "ExternalSeriesId", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "Tagline", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "ProviderIds", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "Images", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "ProductionLocations", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "ExtraIds", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "TotalBitrate", "INT", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "ExtraType", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "Artists", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "AlbumArtists", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "ExternalId", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "SeriesPresentationUniqueKey", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "ShowId", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "OwnerId", "Text", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "Width", "INT", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "Height", "INT", existingColumnNames);
-                AddColumn(connection, "TypedBaseItems", "Size", "BIGINT", existingColumnNames);
-
-                existingColumnNames = GetColumnNames(connection, "ItemValues");
-                AddColumn(connection, "ItemValues", "CleanValue", "Text", existingColumnNames);
-
-                existingColumnNames = GetColumnNames(connection, ChaptersTableName);
-                AddColumn(connection, ChaptersTableName, "ImageDateModified", "DATETIME", existingColumnNames);
-
-                existingColumnNames = GetColumnNames(connection, "MediaStreams");
-                AddColumn(connection, "MediaStreams", "IsAvc", "BIT", existingColumnNames);
-                AddColumn(connection, "MediaStreams", "TimeBase", "TEXT", existingColumnNames);
-                AddColumn(connection, "MediaStreams", "CodecTimeBase", "TEXT", existingColumnNames);
-                AddColumn(connection, "MediaStreams", "Title", "TEXT", existingColumnNames);
-                AddColumn(connection, "MediaStreams", "NalLengthSize", "TEXT", existingColumnNames);
-                AddColumn(connection, "MediaStreams", "Comment", "TEXT", existingColumnNames);
-                AddColumn(connection, "MediaStreams", "CodecTag", "TEXT", existingColumnNames);
-                AddColumn(connection, "MediaStreams", "PixelFormat", "TEXT", existingColumnNames);
-                AddColumn(connection, "MediaStreams", "BitDepth", "INT", existingColumnNames);
-                AddColumn(connection, "MediaStreams", "RefFrames", "INT", existingColumnNames);
-                AddColumn(connection, "MediaStreams", "KeyFrames", "TEXT", existingColumnNames);
-                AddColumn(connection, "MediaStreams", "IsAnamorphic", "BIT", existingColumnNames);
-
-                AddColumn(connection, "MediaStreams", "ColorPrimaries", "TEXT", existingColumnNames);
-                AddColumn(connection, "MediaStreams", "ColorSpace", "TEXT", existingColumnNames);
-                AddColumn(connection, "MediaStreams", "ColorTransfer", "TEXT", existingColumnNames);
-
-                AddColumn(connection, "MediaStreams", "DvVersionMajor", "INT", existingColumnNames);
-                AddColumn(connection, "MediaStreams", "DvVersionMinor", "INT", existingColumnNames);
-                AddColumn(connection, "MediaStreams", "DvProfile", "INT", existingColumnNames);
-                AddColumn(connection, "MediaStreams", "DvLevel", "INT", existingColumnNames);
-                AddColumn(connection, "MediaStreams", "RpuPresentFlag", "INT", existingColumnNames);
-                AddColumn(connection, "MediaStreams", "ElPresentFlag", "INT", existingColumnNames);
-                AddColumn(connection, "MediaStreams", "BlPresentFlag", "INT", existingColumnNames);
-                AddColumn(connection, "MediaStreams", "DvBlSignalCompatibilityId", "INT", existingColumnNames);
-
-                AddColumn(connection, "MediaStreams", "IsHearingImpaired", "BIT", existingColumnNames);
-
-                AddColumn(connection, "MediaStreams", "Rotation", "INT", existingColumnNames);
-
-                connection.Execute(string.Join(';', postQueries));
-
-                transaction.Commit();
-            }
-        }
-
-        /// <inheritdoc />
-        public void SaveImages(BaseItem item)
-        {
-            ArgumentNullException.ThrowIfNull(item);
-
-            CheckDisposed();
-
-            var images = SerializeImages(item.ImageInfos);
-            using var connection = GetConnection();
-            using var transaction = connection.BeginTransaction();
-            using var saveImagesStatement = PrepareStatement(connection, "Update TypedBaseItems set Images=@Images where guid=@Id");
-            saveImagesStatement.TryBind("@Id", item.Id);
-            saveImagesStatement.TryBind("@Images", images);
-
-            saveImagesStatement.ExecuteNonQuery();
-            transaction.Commit();
-        }
-
-        /// <summary>
-        /// Saves the items.
-        /// </summary>
-        /// <param name="items">The items.</param>
-        /// <param name="cancellationToken">The cancellation token.</param>
-        /// <exception cref="ArgumentNullException">
-        /// <paramref name="items"/> or <paramref name="cancellationToken"/> is <c>null</c>.
-        /// </exception>
-        public void SaveItems(IReadOnlyList<BaseItem> items, CancellationToken cancellationToken)
-        {
-            ArgumentNullException.ThrowIfNull(items);
-
-            cancellationToken.ThrowIfCancellationRequested();
-
-            CheckDisposed();
-
-            var itemsLen = items.Count;
-            var tuples = new ValueTuple<BaseItem, List<Guid>, BaseItem, string, List<string>>[itemsLen];
-            for (int i = 0; i < itemsLen; i++)
-            {
-                var item = items[i];
-                var ancestorIds = item.SupportsAncestors ?
-                    item.GetAncestorIds().Distinct().ToList() :
-                    null;
-
-                var topParent = item.GetTopParent();
-
-                var userdataKey = item.GetUserDataKeys().FirstOrDefault();
-                var inheritedTags = item.GetInheritedTags();
-
-                tuples[i] = (item, ancestorIds, topParent, userdataKey, inheritedTags);
-            }
-
-            using var connection = GetConnection();
-            using var transaction = connection.BeginTransaction();
-            SaveItemsInTransaction(connection, tuples);
-            transaction.Commit();
-        }
-
-        private void SaveItemsInTransaction(ManagedConnection db, IEnumerable<(BaseItem Item, List<Guid> AncestorIds, BaseItem TopParent, string UserDataKey, List<string> InheritedTags)> tuples)
-        {
-            using (var saveItemStatement = PrepareStatement(db, SaveItemCommandText))
-            using (var deleteAncestorsStatement = PrepareStatement(db, "delete from AncestorIds where ItemId=@ItemId"))
-            {
-                var requiresReset = false;
-                foreach (var tuple in tuples)
-                {
-                    if (requiresReset)
-                    {
-                        saveItemStatement.Parameters.Clear();
-                        deleteAncestorsStatement.Parameters.Clear();
-                    }
-
-                    var item = tuple.Item;
-                    var topParent = tuple.TopParent;
-                    var userDataKey = tuple.UserDataKey;
-
-                    SaveItem(item, topParent, userDataKey, saveItemStatement);
-
-                    var inheritedTags = tuple.InheritedTags;
-
-                    if (item.SupportsAncestors)
-                    {
-                        UpdateAncestors(item.Id, tuple.AncestorIds, db, deleteAncestorsStatement);
-                    }
-
-                    UpdateItemValues(item.Id, GetItemValuesToSave(item, inheritedTags), db);
-
-                    requiresReset = true;
-                }
-            }
-        }
-
-        private string GetPathToSave(string path)
-        {
-            if (path is null)
-            {
-                return null;
-            }
-
-            return _appHost.ReverseVirtualPath(path);
-        }
-
-        private string RestorePath(string path)
-        {
-            return _appHost.ExpandVirtualPath(path);
-        }
-
-        private void SaveItem(BaseItem item, BaseItem topParent, string userDataKey, SqliteCommand saveItemStatement)
-        {
-            Type type = item.GetType();
-
-            saveItemStatement.TryBind("@guid", item.Id);
-            saveItemStatement.TryBind("@type", type.FullName);
-
-            if (TypeRequiresDeserialization(type))
-            {
-                saveItemStatement.TryBind("@data", JsonSerializer.SerializeToUtf8Bytes(item, type, _jsonOptions), true);
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@data");
-            }
-
-            saveItemStatement.TryBind("@Path", GetPathToSave(item.Path));
-
-            if (item is IHasStartDate hasStartDate)
-            {
-                saveItemStatement.TryBind("@StartDate", hasStartDate.StartDate);
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@StartDate");
-            }
-
-            if (item.EndDate.HasValue)
-            {
-                saveItemStatement.TryBind("@EndDate", item.EndDate.Value);
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@EndDate");
-            }
-
-            saveItemStatement.TryBind("@ChannelId", item.ChannelId.IsEmpty() ? null : item.ChannelId.ToString("N", CultureInfo.InvariantCulture));
-
-            if (item is IHasProgramAttributes hasProgramAttributes)
-            {
-                saveItemStatement.TryBind("@IsMovie", hasProgramAttributes.IsMovie);
-                saveItemStatement.TryBind("@IsSeries", hasProgramAttributes.IsSeries);
-                saveItemStatement.TryBind("@EpisodeTitle", hasProgramAttributes.EpisodeTitle);
-                saveItemStatement.TryBind("@IsRepeat", hasProgramAttributes.IsRepeat);
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@IsMovie");
-                saveItemStatement.TryBindNull("@IsSeries");
-                saveItemStatement.TryBindNull("@EpisodeTitle");
-                saveItemStatement.TryBindNull("@IsRepeat");
-            }
-
-            saveItemStatement.TryBind("@CommunityRating", item.CommunityRating);
-            saveItemStatement.TryBind("@CustomRating", item.CustomRating);
-            saveItemStatement.TryBind("@IndexNumber", item.IndexNumber);
-            saveItemStatement.TryBind("@IsLocked", item.IsLocked);
-            saveItemStatement.TryBind("@Name", item.Name);
-            saveItemStatement.TryBind("@OfficialRating", item.OfficialRating);
-            saveItemStatement.TryBind("@MediaType", item.MediaType.ToString());
-            saveItemStatement.TryBind("@Overview", item.Overview);
-            saveItemStatement.TryBind("@ParentIndexNumber", item.ParentIndexNumber);
-            saveItemStatement.TryBind("@PremiereDate", item.PremiereDate);
-            saveItemStatement.TryBind("@ProductionYear", item.ProductionYear);
-
-            var parentId = item.ParentId;
-            if (parentId.IsEmpty())
-            {
-                saveItemStatement.TryBindNull("@ParentId");
-            }
-            else
-            {
-                saveItemStatement.TryBind("@ParentId", parentId);
-            }
-
-            if (item.Genres.Length > 0)
-            {
-                saveItemStatement.TryBind("@Genres", string.Join('|', item.Genres));
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@Genres");
-            }
-
-            saveItemStatement.TryBind("@InheritedParentalRatingValue", item.InheritedParentalRatingValue);
-
-            saveItemStatement.TryBind("@SortName", item.SortName);
-
-            saveItemStatement.TryBind("@ForcedSortName", item.ForcedSortName);
-
-            saveItemStatement.TryBind("@RunTimeTicks", item.RunTimeTicks);
-            saveItemStatement.TryBind("@Size", item.Size);
-
-            saveItemStatement.TryBind("@DateCreated", item.DateCreated);
-            saveItemStatement.TryBind("@DateModified", item.DateModified);
-
-            saveItemStatement.TryBind("@PreferredMetadataLanguage", item.PreferredMetadataLanguage);
-            saveItemStatement.TryBind("@PreferredMetadataCountryCode", item.PreferredMetadataCountryCode);
-
-            if (item.Width > 0)
-            {
-                saveItemStatement.TryBind("@Width", item.Width);
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@Width");
-            }
-
-            if (item.Height > 0)
-            {
-                saveItemStatement.TryBind("@Height", item.Height);
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@Height");
-            }
-
-            if (item.DateLastRefreshed != default(DateTime))
-            {
-                saveItemStatement.TryBind("@DateLastRefreshed", item.DateLastRefreshed);
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@DateLastRefreshed");
-            }
-
-            if (item.DateLastSaved != default(DateTime))
-            {
-                saveItemStatement.TryBind("@DateLastSaved", item.DateLastSaved);
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@DateLastSaved");
-            }
-
-            saveItemStatement.TryBind("@IsInMixedFolder", item.IsInMixedFolder);
-
-            if (item.LockedFields.Length > 0)
-            {
-                saveItemStatement.TryBind("@LockedFields", string.Join('|', item.LockedFields));
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@LockedFields");
-            }
-
-            if (item.Studios.Length > 0)
-            {
-                saveItemStatement.TryBind("@Studios", string.Join('|', item.Studios));
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@Studios");
-            }
-
-            if (item.Audio.HasValue)
-            {
-                saveItemStatement.TryBind("@Audio", item.Audio.Value.ToString());
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@Audio");
-            }
-
-            if (item is LiveTvChannel liveTvChannel)
-            {
-                saveItemStatement.TryBind("@ExternalServiceId", liveTvChannel.ServiceName);
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@ExternalServiceId");
-            }
-
-            if (item.Tags.Length > 0)
-            {
-                saveItemStatement.TryBind("@Tags", string.Join('|', item.Tags));
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@Tags");
-            }
-
-            saveItemStatement.TryBind("@IsFolder", item.IsFolder);
-
-            saveItemStatement.TryBind("@UnratedType", item.GetBlockUnratedType().ToString());
-
-            if (topParent is null)
-            {
-                saveItemStatement.TryBindNull("@TopParentId");
-            }
-            else
-            {
-                saveItemStatement.TryBind("@TopParentId", topParent.Id.ToString("N", CultureInfo.InvariantCulture));
-            }
-
-            if (item is Trailer trailer && trailer.TrailerTypes.Length > 0)
-            {
-                saveItemStatement.TryBind("@TrailerTypes", string.Join('|', trailer.TrailerTypes));
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@TrailerTypes");
-            }
-
-            saveItemStatement.TryBind("@CriticRating", item.CriticRating);
-
-            if (string.IsNullOrWhiteSpace(item.Name))
-            {
-                saveItemStatement.TryBindNull("@CleanName");
-            }
-            else
-            {
-                saveItemStatement.TryBind("@CleanName", GetCleanValue(item.Name));
-            }
-
-            saveItemStatement.TryBind("@PresentationUniqueKey", item.PresentationUniqueKey);
-            saveItemStatement.TryBind("@OriginalTitle", item.OriginalTitle);
-
-            if (item is Video video)
-            {
-                saveItemStatement.TryBind("@PrimaryVersionId", video.PrimaryVersionId);
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@PrimaryVersionId");
-            }
-
-            if (item is Folder folder && folder.DateLastMediaAdded.HasValue)
-            {
-                saveItemStatement.TryBind("@DateLastMediaAdded", folder.DateLastMediaAdded.Value);
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@DateLastMediaAdded");
-            }
-
-            saveItemStatement.TryBind("@Album", item.Album);
-            saveItemStatement.TryBind("@LUFS", item.LUFS);
-            saveItemStatement.TryBind("@NormalizationGain", item.NormalizationGain);
-            saveItemStatement.TryBind("@IsVirtualItem", item.IsVirtualItem);
-
-            if (item is IHasSeries hasSeriesName)
-            {
-                saveItemStatement.TryBind("@SeriesName", hasSeriesName.SeriesName);
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@SeriesName");
-            }
-
-            if (string.IsNullOrWhiteSpace(userDataKey))
-            {
-                saveItemStatement.TryBindNull("@UserDataKey");
-            }
-            else
-            {
-                saveItemStatement.TryBind("@UserDataKey", userDataKey);
-            }
-
-            if (item is Episode episode)
-            {
-                saveItemStatement.TryBind("@SeasonName", episode.SeasonName);
-
-                var nullableSeasonId = episode.SeasonId.IsEmpty() ? (Guid?)null : episode.SeasonId;
-
-                saveItemStatement.TryBind("@SeasonId", nullableSeasonId);
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@SeasonName");
-                saveItemStatement.TryBindNull("@SeasonId");
-            }
-
-            if (item is IHasSeries hasSeries)
-            {
-                var nullableSeriesId = hasSeries.SeriesId.IsEmpty() ? (Guid?)null : hasSeries.SeriesId;
-
-                saveItemStatement.TryBind("@SeriesId", nullableSeriesId);
-                saveItemStatement.TryBind("@SeriesPresentationUniqueKey", hasSeries.SeriesPresentationUniqueKey);
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@SeriesId");
-                saveItemStatement.TryBindNull("@SeriesPresentationUniqueKey");
-            }
-
-            saveItemStatement.TryBind("@ExternalSeriesId", item.ExternalSeriesId);
-            saveItemStatement.TryBind("@Tagline", item.Tagline);
-
-            saveItemStatement.TryBind("@ProviderIds", SerializeProviderIds(item.ProviderIds));
-            saveItemStatement.TryBind("@Images", SerializeImages(item.ImageInfos));
-
-            if (item.ProductionLocations.Length > 0)
-            {
-                saveItemStatement.TryBind("@ProductionLocations", string.Join('|', item.ProductionLocations));
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@ProductionLocations");
-            }
-
-            if (item.ExtraIds.Length > 0)
-            {
-                saveItemStatement.TryBind("@ExtraIds", string.Join('|', item.ExtraIds));
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@ExtraIds");
-            }
-
-            saveItemStatement.TryBind("@TotalBitrate", item.TotalBitrate);
-            if (item.ExtraType.HasValue)
-            {
-                saveItemStatement.TryBind("@ExtraType", item.ExtraType.Value.ToString());
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@ExtraType");
-            }
-
-            string artists = null;
-            if (item is IHasArtist hasArtists && hasArtists.Artists.Count > 0)
-            {
-                artists = string.Join('|', hasArtists.Artists);
-            }
-
-            saveItemStatement.TryBind("@Artists", artists);
-
-            string albumArtists = null;
-            if (item is IHasAlbumArtist hasAlbumArtists
-                && hasAlbumArtists.AlbumArtists.Count > 0)
-            {
-                albumArtists = string.Join('|', hasAlbumArtists.AlbumArtists);
-            }
-
-            saveItemStatement.TryBind("@AlbumArtists", albumArtists);
-            saveItemStatement.TryBind("@ExternalId", item.ExternalId);
-
-            if (item is LiveTvProgram program)
-            {
-                saveItemStatement.TryBind("@ShowId", program.ShowId);
-            }
-            else
-            {
-                saveItemStatement.TryBindNull("@ShowId");
-            }
-
-            Guid ownerId = item.OwnerId;
-            if (ownerId.IsEmpty())
-            {
-                saveItemStatement.TryBindNull("@OwnerId");
-            }
-            else
-            {
-                saveItemStatement.TryBind("@OwnerId", ownerId);
-            }
-
-            saveItemStatement.ExecuteNonQuery();
-        }
-
-        internal static string SerializeProviderIds(Dictionary<string, string> providerIds)
-        {
-            StringBuilder str = new StringBuilder();
-            foreach (var i in providerIds)
-            {
-                // Ideally we shouldn't need this IsNullOrWhiteSpace check,
-                // but we're seeing some cases of bad data slip through
-                if (string.IsNullOrWhiteSpace(i.Value))
-                {
-                    continue;
-                }
-
-                str.Append(i.Key)
-                    .Append('=')
-                    .Append(i.Value)
-                    .Append('|');
-            }
-
-            if (str.Length == 0)
-            {
-                return null;
-            }
-
-            str.Length -= 1; // Remove last |
-            return str.ToString();
-        }
-
-        internal static void DeserializeProviderIds(string value, IHasProviderIds item)
-        {
-            if (string.IsNullOrWhiteSpace(value))
-            {
-                return;
-            }
-
-            foreach (var part in value.SpanSplit('|'))
-            {
-                var providerDelimiterIndex = part.IndexOf('=');
-                // Don't let empty values through
-                if (providerDelimiterIndex != -1 && part.Length != providerDelimiterIndex + 1)
-                {
-                    item.SetProviderId(part[..providerDelimiterIndex].ToString(), part[(providerDelimiterIndex + 1)..].ToString());
-                }
-            }
-        }
-
-        internal string SerializeImages(ItemImageInfo[] images)
-        {
-            if (images.Length == 0)
-            {
-                return null;
-            }
-
-            StringBuilder str = new StringBuilder();
-            foreach (var i in images)
-            {
-                if (string.IsNullOrWhiteSpace(i.Path))
-                {
-                    continue;
-                }
-
-                AppendItemImageInfo(str, i);
-                str.Append('|');
-            }
-
-            str.Length -= 1; // Remove last |
-            return str.ToString();
-        }
-
-        internal ItemImageInfo[] DeserializeImages(string value)
-        {
-            if (string.IsNullOrWhiteSpace(value))
-            {
-                return Array.Empty<ItemImageInfo>();
-            }
-
-            // TODO The following is an ugly performance optimization, but it's extremely unlikely that the data in the database would be malformed
-            var valueSpan = value.AsSpan();
-            var count = valueSpan.Count('|') + 1;
-
-            var position = 0;
-            var result = new ItemImageInfo[count];
-            foreach (var part in valueSpan.Split('|'))
-            {
-                var image = ItemImageInfoFromValueString(part);
-
-                if (image is not null)
-                {
-                    result[position++] = image;
-                }
-            }
-
-            if (position == count)
-            {
-                return result;
-            }
-
-            if (position == 0)
-            {
-                return Array.Empty<ItemImageInfo>();
-            }
-
-            // Extremely unlikely, but somehow one or more of the image strings were malformed. Cut the array.
-            return result[..position];
-        }
-
-        private void AppendItemImageInfo(StringBuilder bldr, ItemImageInfo image)
-        {
-            const char Delimiter = '*';
-
-            var path = image.Path ?? string.Empty;
-
-            bldr.Append(GetPathToSave(path))
-                .Append(Delimiter)
-                .Append(image.DateModified.Ticks)
-                .Append(Delimiter)
-                .Append(image.Type)
-                .Append(Delimiter)
-                .Append(image.Width)
-                .Append(Delimiter)
-                .Append(image.Height);
-
-            var hash = image.BlurHash;
-            if (!string.IsNullOrEmpty(hash))
-            {
-                bldr.Append(Delimiter)
-                    // Replace delimiters with other characters.
-                    // This can be removed when we migrate to a proper DB.
-                    .Append(hash.Replace(Delimiter, '/').Replace('|', '\\'));
-            }
-        }
-
-        internal ItemImageInfo ItemImageInfoFromValueString(ReadOnlySpan<char> value)
-        {
-            const char Delimiter = '*';
-
-            var nextSegment = value.IndexOf(Delimiter);
-            if (nextSegment == -1)
-            {
-                return null;
-            }
-
-            ReadOnlySpan<char> path = value[..nextSegment];
-            value = value[(nextSegment + 1)..];
-            nextSegment = value.IndexOf(Delimiter);
-            if (nextSegment == -1)
-            {
-                return null;
-            }
-
-            ReadOnlySpan<char> dateModified = value[..nextSegment];
-            value = value[(nextSegment + 1)..];
-            nextSegment = value.IndexOf(Delimiter);
-            if (nextSegment == -1)
-            {
-                nextSegment = value.Length;
-            }
-
-            ReadOnlySpan<char> imageType = value[..nextSegment];
-
-            var image = new ItemImageInfo
-            {
-                Path = RestorePath(path.ToString())
-            };
-
-            if (long.TryParse(dateModified, CultureInfo.InvariantCulture, out var ticks)
-                && ticks >= DateTime.MinValue.Ticks
-                && ticks <= DateTime.MaxValue.Ticks)
-            {
-                image.DateModified = new DateTime(ticks, DateTimeKind.Utc);
-            }
-            else
-            {
-                return null;
-            }
-
-            if (Enum.TryParse(imageType, true, out ImageType type))
-            {
-                image.Type = type;
-            }
-            else
-            {
-                return null;
-            }
-
-            // Optional parameters: width*height*blurhash
-            if (nextSegment + 1 < value.Length - 1)
-            {
-                value = value[(nextSegment + 1)..];
-                nextSegment = value.IndexOf(Delimiter);
-                if (nextSegment == -1 || nextSegment == value.Length)
-                {
-                    return image;
-                }
-
-                ReadOnlySpan<char> widthSpan = value[..nextSegment];
-
-                value = value[(nextSegment + 1)..];
-                nextSegment = value.IndexOf(Delimiter);
-                if (nextSegment == -1)
-                {
-                    nextSegment = value.Length;
-                }
-
-                ReadOnlySpan<char> heightSpan = value[..nextSegment];
-
-                if (int.TryParse(widthSpan, NumberStyles.Integer, CultureInfo.InvariantCulture, out var width)
-                    && int.TryParse(heightSpan, NumberStyles.Integer, CultureInfo.InvariantCulture, out var height))
-                {
-                    image.Width = width;
-                    image.Height = height;
-                }
-
-                if (nextSegment < value.Length - 1)
-                {
-                    value = value[(nextSegment + 1)..];
-                    var length = value.Length;
-
-                    Span<char> blurHashSpan = stackalloc char[length];
-                    for (int i = 0; i < length; i++)
-                    {
-                        var c = value[i];
-                        blurHashSpan[i] = c switch
-                        {
-                            '/' => Delimiter,
-                            '\\' => '|',
-                            _ => c
-                        };
-                    }
-
-                    image.BlurHash = new string(blurHashSpan);
-                }
-            }
-
-            return image;
-        }
-
-        /// <summary>
-        /// Internal retrieve from items or users table.
-        /// </summary>
-        /// <param name="id">The id.</param>
-        /// <returns>BaseItem.</returns>
-        /// <exception cref="ArgumentNullException"><paramref name="id"/> is <c>null</c>.</exception>
-        /// <exception cref="ArgumentException"><paramr name="id"/> is <seealso cref="Guid.Empty"/>.</exception>
-        public BaseItem RetrieveItem(Guid id)
-        {
-            if (id.IsEmpty())
-            {
-                throw new ArgumentException("Guid can't be empty", nameof(id));
-            }
-
-            CheckDisposed();
-
-            using (var connection = GetConnection(true))
-            using (var statement = PrepareStatement(connection, _retrieveItemColumnsSelectQuery))
-            {
-                statement.TryBind("@guid", id);
-
-                foreach (var row in statement.ExecuteQuery())
-                {
-                    return GetItem(row, new InternalItemsQuery());
-                }
-            }
-
-            return null;
-        }
 
         private bool TypeRequiresDeserialization(Type type)
         {
@@ -1179,152 +124,6 @@ namespace Emby.Server.Implementations.Data
                 && type != typeof(MusicAlbum);
         }
 
-        /// <inheritdoc />
-        public List<ChapterInfo> GetChapters(BaseItem item)
-        {
-            CheckDisposed();
-
-            var chapters = new List<ChapterInfo>();
-            using (var connection = GetConnection(true))
-            using (var statement = PrepareStatement(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId order by ChapterIndex asc"))
-            {
-                statement.TryBind("@ItemId", item.Id);
-
-                foreach (var row in statement.ExecuteQuery())
-                {
-                    chapters.Add(GetChapter(row, item));
-                }
-            }
-
-            return chapters;
-        }
-
-        /// <inheritdoc />
-        public ChapterInfo GetChapter(BaseItem item, int index)
-        {
-            CheckDisposed();
-
-            using (var connection = GetConnection(true))
-            using (var statement = PrepareStatement(connection, "select StartPositionTicks,Name,ImagePath,ImageDateModified from " + ChaptersTableName + " where ItemId = @ItemId and ChapterIndex=@ChapterIndex"))
-            {
-                statement.TryBind("@ItemId", item.Id);
-                statement.TryBind("@ChapterIndex", index);
-
-                foreach (var row in statement.ExecuteQuery())
-                {
-                    return GetChapter(row, item);
-                }
-            }
-
-            return null;
-        }
-
-        /// <summary>
-        /// Gets the chapter.
-        /// </summary>
-        /// <param name="reader">The reader.</param>
-        /// <param name="item">The item.</param>
-        /// <returns>ChapterInfo.</returns>
-        private ChapterInfo GetChapter(SqliteDataReader reader, BaseItem item)
-        {
-            var chapter = new ChapterInfo
-            {
-                StartPositionTicks = reader.GetInt64(0)
-            };
-
-            if (reader.TryGetString(1, out var chapterName))
-            {
-                chapter.Name = chapterName;
-            }
-
-            if (reader.TryGetString(2, out var imagePath))
-            {
-                chapter.ImagePath = imagePath;
-                chapter.ImageTag = _imageProcessor.GetImageCacheTag(item, chapter);
-            }
-
-            if (reader.TryReadDateTime(3, out var imageDateModified))
-            {
-                chapter.ImageDateModified = imageDateModified;
-            }
-
-            return chapter;
-        }
-
-        /// <summary>
-        /// Saves the chapters.
-        /// </summary>
-        /// <param name="id">The item id.</param>
-        /// <param name="chapters">The chapters.</param>
-        public void SaveChapters(Guid id, IReadOnlyList<ChapterInfo> chapters)
-        {
-            CheckDisposed();
-
-            if (id.IsEmpty())
-            {
-                throw new ArgumentNullException(nameof(id));
-            }
-
-            ArgumentNullException.ThrowIfNull(chapters);
-
-            using var connection = GetConnection();
-            using var transaction = connection.BeginTransaction();
-            // First delete chapters
-            using var command = connection.PrepareStatement($"delete from {ChaptersTableName} where ItemId=@ItemId");
-            command.TryBind("@ItemId", id);
-            command.ExecuteNonQuery();
-
-            InsertChapters(id, chapters, connection);
-            transaction.Commit();
-        }
-
-        private void InsertChapters(Guid idBlob, IReadOnlyList<ChapterInfo> chapters, ManagedConnection db)
-        {
-            var startIndex = 0;
-            var limit = 100;
-            var chapterIndex = 0;
-
-            const string StartInsertText = "insert into " + ChaptersTableName + " (ItemId, ChapterIndex, StartPositionTicks, Name, ImagePath, ImageDateModified) values ";
-            var insertText = new StringBuilder(StartInsertText, 256);
-
-            while (startIndex < chapters.Count)
-            {
-                var endIndex = Math.Min(chapters.Count, startIndex + limit);
-
-                for (var i = startIndex; i < endIndex; i++)
-                {
-                    insertText.AppendFormat(CultureInfo.InvariantCulture, "(@ItemId, @ChapterIndex{0}, @StartPositionTicks{0}, @Name{0}, @ImagePath{0}, @ImageDateModified{0}),", i.ToString(CultureInfo.InvariantCulture));
-                }
-
-                insertText.Length -= 1; // Remove trailing comma
-
-                using (var statement = PrepareStatement(db, insertText.ToString()))
-                {
-                    statement.TryBind("@ItemId", idBlob);
-
-                    for (var i = startIndex; i < endIndex; i++)
-                    {
-                        var index = i.ToString(CultureInfo.InvariantCulture);
-
-                        var chapter = chapters[i];
-
-                        statement.TryBind("@ChapterIndex" + index, chapterIndex);
-                        statement.TryBind("@StartPositionTicks" + index, chapter.StartPositionTicks);
-                        statement.TryBind("@Name" + index, chapter.Name);
-                        statement.TryBind("@ImagePath" + index, chapter.ImagePath);
-                        statement.TryBind("@ImageDateModified" + index, chapter.ImageDateModified);
-
-                        chapterIndex++;
-                    }
-
-                    statement.ExecuteNonQuery();
-                }
-
-                startIndex += limit;
-                insertText.Length = StartInsertText.Length;
-            }
-        }
-
         private static bool EnableJoinUserData(InternalItemsQuery query)
         {
             if (query.User is null)
diff --git a/Jellyfin.Data/Entities/BaseItem.cs b/Jellyfin.Data/Entities/BaseItem.cs
index c0c88b2e6..18166f7c1 100644
--- a/Jellyfin.Data/Entities/BaseItem.cs
+++ b/Jellyfin.Data/Entities/BaseItem.cs
@@ -161,4 +161,10 @@ public class BaseItem
     public int? Height { get; set; }
 
     public long? Size { get; set; }
+
+    public ICollection<People>? Peoples { get; set; }
+
+    public ICollection<UserData>? UserData { get; set; }
+
+    public ICollection<ItemValue>? ItemValues { get; set; }
 }
diff --git a/Jellyfin.Data/Entities/People.cs b/Jellyfin.Data/Entities/People.cs
index 72c39699b..014a0f1c9 100644
--- a/Jellyfin.Data/Entities/People.cs
+++ b/Jellyfin.Data/Entities/People.cs
@@ -7,6 +7,7 @@ namespace Jellyfin.Data.Entities;
 public class People
 {
     public Guid ItemId { get; set; }
+    public BaseItem Item { get; set; }
 
     public required string Name { get; set; }
     public string? Role { get; set; }
diff --git a/Jellyfin.Server.Implementations/Item/BaseItemManager.cs b/Jellyfin.Server.Implementations/Item/BaseItemManager.cs
index 4ad842e0b..85dc98e09 100644
--- a/Jellyfin.Server.Implementations/Item/BaseItemManager.cs
+++ b/Jellyfin.Server.Implementations/Item/BaseItemManager.cs
@@ -3,16 +3,24 @@ using System.Collections.Concurrent;
 using System.Collections.Generic;
 using System.Globalization;
 using System.Linq;
+using System.Linq.Expressions;
+using System.Security.Cryptography.X509Certificates;
 using System.Text;
 using System.Threading;
+using System.Threading.Channels;
+using Jellyfin.Data.Enums;
 using Jellyfin.Extensions;
 using MediaBrowser.Controller;
 using MediaBrowser.Controller.Entities;
 using MediaBrowser.Controller.Entities.Audio;
+using MediaBrowser.Controller.Entities.Movies;
 using MediaBrowser.Controller.Entities.TV;
 using MediaBrowser.Controller.LiveTv;
+using MediaBrowser.Controller.Persistence;
+using MediaBrowser.Controller.Playlists;
 using MediaBrowser.Model.Entities;
 using MediaBrowser.Model.LiveTv;
+using MediaBrowser.Model.Querying;
 using Microsoft.EntityFrameworkCore;
 using BaseItemDto = MediaBrowser.Controller.Entities.BaseItem;
 using BaseItemEntity = Jellyfin.Data.Entities.BaseItem;
@@ -22,12 +30,105 @@ namespace Jellyfin.Server.Implementations.Item;
 /// <summary>
 /// Handles all storage logic for BaseItems.
 /// </summary>
-public class BaseItemManager
+public class BaseItemManager : IItemRepository
 {
     private readonly IDbContextFactory<JellyfinDbContext> _dbProvider;
     private readonly IServerApplicationHost _appHost;
 
-     /// <summary>
+
+
+    private readonly ItemFields[] _allItemFields = Enum.GetValues<ItemFields>();
+
+    private static readonly BaseItemKind[] _programTypes = new[]
+    {
+            BaseItemKind.Program,
+            BaseItemKind.TvChannel,
+            BaseItemKind.LiveTvProgram,
+            BaseItemKind.LiveTvChannel
+    };
+
+    private static readonly BaseItemKind[] _programExcludeParentTypes = new[]
+    {
+            BaseItemKind.Series,
+            BaseItemKind.Season,
+            BaseItemKind.MusicAlbum,
+            BaseItemKind.MusicArtist,
+            BaseItemKind.PhotoAlbum
+    };
+
+    private static readonly BaseItemKind[] _serviceTypes = new[]
+    {
+            BaseItemKind.TvChannel,
+            BaseItemKind.LiveTvChannel
+    };
+
+    private static readonly BaseItemKind[] _startDateTypes = new[]
+    {
+            BaseItemKind.Program,
+            BaseItemKind.LiveTvProgram
+    };
+
+    private static readonly BaseItemKind[] _seriesTypes = new[]
+    {
+            BaseItemKind.Book,
+            BaseItemKind.AudioBook,
+            BaseItemKind.Episode,
+            BaseItemKind.Season
+    };
+
+    private static readonly BaseItemKind[] _artistExcludeParentTypes = new[]
+    {
+            BaseItemKind.Series,
+            BaseItemKind.Season,
+            BaseItemKind.PhotoAlbum
+    };
+
+    private static readonly BaseItemKind[] _artistsTypes = new[]
+    {
+            BaseItemKind.Audio,
+            BaseItemKind.MusicAlbum,
+            BaseItemKind.MusicVideo,
+            BaseItemKind.AudioBook
+    };
+
+    private static readonly Dictionary<BaseItemKind, string?> _baseItemKindNames = new()
+        {
+            { BaseItemKind.AggregateFolder, typeof(AggregateFolder).FullName },
+            { BaseItemKind.Audio, typeof(Audio).FullName },
+            { BaseItemKind.AudioBook, typeof(AudioBook).FullName },
+            { BaseItemKind.BasePluginFolder, typeof(BasePluginFolder).FullName },
+            { BaseItemKind.Book, typeof(Book).FullName },
+            { BaseItemKind.BoxSet, typeof(BoxSet).FullName },
+            { BaseItemKind.Channel, typeof(Channel).FullName },
+            { BaseItemKind.CollectionFolder, typeof(CollectionFolder).FullName },
+            { BaseItemKind.Episode, typeof(Episode).FullName },
+            { BaseItemKind.Folder, typeof(Folder).FullName },
+            { BaseItemKind.Genre, typeof(Genre).FullName },
+            { BaseItemKind.Movie, typeof(Movie).FullName },
+            { BaseItemKind.LiveTvChannel, typeof(LiveTvChannel).FullName },
+            { BaseItemKind.LiveTvProgram, typeof(LiveTvProgram).FullName },
+            { BaseItemKind.MusicAlbum, typeof(MusicAlbum).FullName },
+            { BaseItemKind.MusicArtist, typeof(MusicArtist).FullName },
+            { BaseItemKind.MusicGenre, typeof(MusicGenre).FullName },
+            { BaseItemKind.MusicVideo, typeof(MusicVideo).FullName },
+            { BaseItemKind.Person, typeof(Person).FullName },
+            { BaseItemKind.Photo, typeof(Photo).FullName },
+            { BaseItemKind.PhotoAlbum, typeof(PhotoAlbum).FullName },
+            { BaseItemKind.Playlist, typeof(Playlist).FullName },
+            { BaseItemKind.PlaylistsFolder, typeof(PlaylistsFolder).FullName },
+            { BaseItemKind.Season, typeof(Season).FullName },
+            { BaseItemKind.Series, typeof(Series).FullName },
+            { BaseItemKind.Studio, typeof(Studio).FullName },
+            { BaseItemKind.Trailer, typeof(Trailer).FullName },
+            { BaseItemKind.TvChannel, typeof(LiveTvChannel).FullName },
+            { BaseItemKind.TvProgram, typeof(LiveTvProgram).FullName },
+            { BaseItemKind.UserRootFolder, typeof(UserRootFolder).FullName },
+            { BaseItemKind.UserView, typeof(UserView).FullName },
+            { BaseItemKind.Video, typeof(Video).FullName },
+            { BaseItemKind.Year, typeof(Year).FullName }
+        };
+
+    /// <summary>
     /// This holds all the types in the running assemblies
     /// so that we can de-serialize properly when we don't have strong types.
     /// </summary>
@@ -37,12 +138,1210 @@ public class BaseItemManager
     /// Initializes a new instance of the <see cref="BaseItemManager"/> class.
     /// </summary>
     /// <param name="dbProvider">The db factory.</param>
+    /// <param name="appHost">The Application host.</param>
     public BaseItemManager(IDbContextFactory<JellyfinDbContext> dbProvider, IServerApplicationHost appHost)
     {
         _dbProvider = dbProvider;
         _appHost = appHost;
     }
 
+    public int GetCount(InternalItemsQuery query)
+    {
+        ArgumentNullException.ThrowIfNull(query);
+        // Hack for right now since we currently don't support filtering out these duplicates within a query
+        if (query.Limit.HasValue && query.EnableGroupByMetadataKey)
+        {
+            query.Limit = query.Limit.Value + 4;
+        }
+
+        if (query.IsResumable ?? false)
+        {
+            query.IsVirtualItem = false;
+        }
+
+
+
+    }
+
+    private IQueryable<BaseItemEntity> TranslateQuery(
+        IQueryable<BaseItemEntity> baseQuery,
+        JellyfinDbContext context,
+        InternalItemsQuery query)
+    {
+        var minWidth = query.MinWidth;
+        var maxWidth = query.MaxWidth;
+        var now = DateTime.UtcNow;
+
+        if (query.IsHD.HasValue)
+        {
+            const int Threshold = 1200;
+            if (query.IsHD.Value)
+            {
+                minWidth = Threshold;
+            }
+            else
+            {
+                maxWidth = Threshold - 1;
+            }
+        }
+
+        if (query.Is4K.HasValue)
+        {
+            const int Threshold = 3800;
+            if (query.Is4K.Value)
+            {
+                minWidth = Threshold;
+            }
+            else
+            {
+                maxWidth = Threshold - 1;
+            }
+        }
+
+        if (minWidth.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.Width >= minWidth);
+        }
+
+        if (query.MinHeight.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.Height >= query.MinHeight);
+        }
+
+        if (maxWidth.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.Width >= maxWidth);
+        }
+
+        if (query.MaxHeight.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.Height <= query.MaxHeight);
+        }
+
+        if (query.IsLocked.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.IsLocked == query.IsLocked);
+        }
+
+        var tags = query.Tags.ToList();
+        var excludeTags = query.ExcludeTags.ToList();
+
+        if (query.IsMovie == true)
+        {
+            if (query.IncludeItemTypes.Length == 0
+                || query.IncludeItemTypes.Contains(BaseItemKind.Movie)
+                || query.IncludeItemTypes.Contains(BaseItemKind.Trailer))
+            {
+                baseQuery = baseQuery.Where(e => e.IsMovie);
+            }
+        }
+        else if (query.IsMovie.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.IsMovie == query.IsMovie);
+        }
+
+        if (query.IsSeries.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.IsSeries == query.IsSeries);
+        }
+
+        if (query.IsSports.HasValue)
+        {
+            if (query.IsSports.Value)
+            {
+                tags.Add("Sports");
+            }
+            else
+            {
+                excludeTags.Add("Sports");
+            }
+        }
+
+        if (query.IsNews.HasValue)
+        {
+            if (query.IsNews.Value)
+            {
+                tags.Add("News");
+            }
+            else
+            {
+                excludeTags.Add("News");
+            }
+        }
+
+        if (query.IsKids.HasValue)
+        {
+            if (query.IsKids.Value)
+            {
+                tags.Add("Kids");
+            }
+            else
+            {
+                excludeTags.Add("Kids");
+            }
+        }
+
+        if (query.SimilarTo is not null && query.MinSimilarityScore > 0)
+        {
+            // TODO support similarty score via CTE
+            baseQuery = baseQuery.Where(e => e.Sim == query.IsSeries);
+            whereClauses.Add("SimilarityScore > " + (query.MinSimilarityScore - 1).ToString(CultureInfo.InvariantCulture));
+        }
+
+        if (!string.IsNullOrEmpty(query.SearchTerm))
+        {
+            whereClauses.Add("SearchScore > 0");
+        }
+
+        if (query.IsFolder.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.IsFolder == query.IsFolder);
+        }
+
+        var includeTypes = query.IncludeItemTypes;
+        // Only specify excluded types if no included types are specified
+        if (query.IncludeItemTypes.Length == 0)
+        {
+            var excludeTypes = query.ExcludeItemTypes;
+            if (excludeTypes.Length == 1)
+            {
+                if (_baseItemKindNames.TryGetValue(excludeTypes[0], out var excludeTypeName))
+                {
+                    baseQuery = baseQuery.Where(e => e.Type != excludeTypeName);
+                }
+            }
+            else if (excludeTypes.Length > 1)
+            {
+                var excludeTypeName = new List<string>();
+                foreach (var excludeType in excludeTypes)
+                {
+                    if (_baseItemKindNames.TryGetValue(excludeType, out var baseItemKindName))
+                    {
+                        excludeTypeName.Add(baseItemKindName!);
+                    }
+                }
+
+                baseQuery = baseQuery.Where(e => !excludeTypeName.Contains(e.Type));
+            }
+        }
+        else if (includeTypes.Length == 1)
+        {
+            if (_baseItemKindNames.TryGetValue(includeTypes[0], out var includeTypeName))
+            {
+                baseQuery = baseQuery.Where(e => e.Type == includeTypeName);
+            }
+        }
+        else if (includeTypes.Length > 1)
+        {
+            var includeTypeName = new List<string>();
+            foreach (var includeType in includeTypes)
+            {
+                if (_baseItemKindNames.TryGetValue(includeType, out var baseItemKindName))
+                {
+                    includeTypeName.Add(baseItemKindName!);
+                }
+            }
+
+            baseQuery = baseQuery.Where(e => includeTypeName.Contains(e.Type));
+        }
+
+        if (query.ChannelIds.Count == 1)
+        {
+            baseQuery = baseQuery.Where(e => e.ChannelId == query.ChannelIds[0].ToString("N", CultureInfo.InvariantCulture));
+        }
+        else if (query.ChannelIds.Count > 1)
+        {
+            baseQuery = baseQuery.Where(e => query.ChannelIds.Select(f => f.ToString("N", CultureInfo.InvariantCulture)).Contains(e.ChannelId));
+        }
+
+        if (!query.ParentId.IsEmpty())
+        {
+            baseQuery = baseQuery.Where(e => e.ParentId.Equals(query.ParentId));
+        }
+
+        if (!string.IsNullOrWhiteSpace(query.Path))
+        {
+            baseQuery = baseQuery.Where(e => e.Path == query.Path);
+        }
+
+        if (!string.IsNullOrWhiteSpace(query.PresentationUniqueKey))
+        {
+            baseQuery = baseQuery.Where(e => e.PresentationUniqueKey == query.PresentationUniqueKey);
+        }
+
+        if (query.MinCommunityRating.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.CommunityRating >= query.MinCommunityRating);
+        }
+
+        if (query.MinIndexNumber.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.IndexNumber >= query.MinIndexNumber);
+        }
+
+        if (query.MinParentAndIndexNumber.HasValue)
+        {
+            baseQuery = baseQuery
+                .Where(e => (e.ParentIndexNumber == query.MinParentAndIndexNumber.Value.ParentIndexNumber && e.IndexNumber >= query.MinParentAndIndexNumber.Value.IndexNumber) || e.ParentIndexNumber > query.MinParentAndIndexNumber.Value.ParentIndexNumber);
+        }
+
+        if (query.MinDateCreated.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.DateCreated >= query.MinDateCreated);
+        }
+
+        if (query.MinDateLastSaved.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.DateLastSaved != null && e.DateLastSaved >= query.MinDateLastSaved.Value);
+        }
+
+        if (query.MinDateLastSavedForUser.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.DateLastSaved != null && e.DateLastSaved >= query.MinDateLastSavedForUser.Value);
+        }
+
+        if (query.IndexNumber.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.IndexNumber == query.IndexNumber.Value);
+        }
+
+        if (query.ParentIndexNumber.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.ParentIndexNumber == query.ParentIndexNumber.Value);
+        }
+
+        if (query.ParentIndexNumberNotEquals.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.ParentIndexNumber != query.ParentIndexNumberNotEquals.Value || e.ParentIndexNumber == null);
+        }
+
+        var minEndDate = query.MinEndDate;
+        var maxEndDate = query.MaxEndDate;
+
+        if (query.HasAired.HasValue)
+        {
+            if (query.HasAired.Value)
+            {
+                maxEndDate = DateTime.UtcNow;
+            }
+            else
+            {
+                minEndDate = DateTime.UtcNow;
+            }
+        }
+
+        if (minEndDate.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.EndDate >= minEndDate);
+        }
+
+        if (maxEndDate.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.EndDate <= maxEndDate);
+        }
+
+        if (query.MinStartDate.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.StartDate >= query.MinStartDate.Value);
+        }
+
+        if (query.MaxStartDate.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.StartDate <= query.MaxStartDate.Value);
+        }
+
+        if (query.MinPremiereDate.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.PremiereDate <= query.MinPremiereDate.Value);
+        }
+
+        if (query.MaxPremiereDate.HasValue)
+        {
+            baseQuery = baseQuery.Where(e => e.PremiereDate <= query.MaxPremiereDate.Value);
+        }
+
+        if (query.TrailerTypes.Length > 0)
+        {
+            baseQuery = baseQuery.Where(e => query.TrailerTypes.Any(f => e.TrailerTypes!.Contains(f.ToString(), StringComparison.OrdinalIgnoreCase)));
+        }
+
+        if (query.IsAiring.HasValue)
+        {
+            if (query.IsAiring.Value)
+            {
+                baseQuery = baseQuery.Where(e => e.StartDate <= now && e.EndDate >= now);
+            }
+            else
+            {
+                baseQuery = baseQuery.Where(e => e.StartDate > now && e.EndDate < now);
+            }
+        }
+
+        if (query.PersonIds.Length > 0)
+        {
+            baseQuery = baseQuery
+                .Where(e =>
+                    context.Peoples.Where(w => context.BaseItems.Where(w => query.PersonIds.Contains(w.Id)).Any(f => f.Name == w.Name))
+                        .Any(f => f.ItemId.Equals(e.Id)));
+        }
+
+        if (!string.IsNullOrWhiteSpace(query.Person))
+        {
+            baseQuery = baseQuery.Where(e => e.Peoples!.Any(f => f.Name == query.Person));
+        }
+
+        if (!string.IsNullOrWhiteSpace(query.MinSortName))
+        {
+            // this does not makes sense.
+            // baseQuery = baseQuery.Where(e => e.SortName >= query.MinSortName);
+            // whereClauses.Add("SortName>=@MinSortName");
+            // statement?.TryBind("@MinSortName", query.MinSortName);
+        }
+
+        if (!string.IsNullOrWhiteSpace(query.ExternalSeriesId))
+        {
+            baseQuery = baseQuery.Where(e => e.ExternalSeriesId == query.ExternalSeriesId);
+        }
+
+        if (!string.IsNullOrWhiteSpace(query.ExternalId))
+        {
+            baseQuery = baseQuery.Where(e => e.ExternalId == query.ExternalId);
+        }
+
+        if (!string.IsNullOrWhiteSpace(query.Name))
+        {
+            var cleanName = GetCleanValue(query.Name);
+            baseQuery = baseQuery.Where(e => e.CleanName == cleanName);
+        }
+
+        // These are the same, for now
+        var nameContains = query.NameContains;
+        if (!string.IsNullOrWhiteSpace(nameContains))
+        {
+            baseQuery = baseQuery.Where(e =>
+                e.CleanName == query.NameContains
+                || e.OriginalTitle!.Contains(query.NameContains!, StringComparison.Ordinal));
+        }
+
+        if (!string.IsNullOrWhiteSpace(query.NameStartsWith))
+        {
+            baseQuery = baseQuery.Where(e => e.SortName!.Contains(query.NameStartsWith, StringComparison.OrdinalIgnoreCase));
+        }
+
+        if (!string.IsNullOrWhiteSpace(query.NameStartsWithOrGreater))
+        {
+            // i hate this
+            baseQuery = baseQuery.Where(e => e.SortName![0] > query.NameStartsWithOrGreater[0]);
+        }
+
+        if (!string.IsNullOrWhiteSpace(query.NameLessThan))
+        {
+            // i hate this
+            baseQuery = baseQuery.Where(e => e.SortName![0] < query.NameLessThan[0]);
+        }
+
+        if (query.ImageTypes.Length > 0)
+        {
+            baseQuery = baseQuery.Where(e => query.ImageTypes.Any(f => e.Images!.Contains(f.ToString(), StringComparison.InvariantCulture)));
+        }
+
+        if (query.IsLiked.HasValue)
+        {
+            baseQuery = baseQuery
+                .Where(e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id) && f.Key == e.UserDataKey)!.Rating >= UserItemData.MinLikeValue);
+        }
+
+        if (query.IsFavoriteOrLiked.HasValue)
+        {
+            baseQuery = baseQuery
+                .Where(e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id) && f.Key == e.UserDataKey)!.IsFavorite == query.IsFavoriteOrLiked);
+        }
+
+        if (query.IsFavorite.HasValue)
+        {
+            baseQuery = baseQuery
+                .Where(e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id) && f.Key == e.UserDataKey)!.IsFavorite == query.IsFavorite);
+        }
+
+        if (query.IsPlayed.HasValue)
+        {
+            baseQuery = baseQuery
+                   .Where(e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id) && f.Key == e.UserDataKey)!.Played == query.IsPlayed.Value);
+        }
+
+        if (query.IsResumable.HasValue)
+        {
+            if (query.IsResumable.Value)
+            {
+                baseQuery = baseQuery
+                       .Where(e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id) && f.Key == e.UserDataKey)!.PlaybackPositionTicks > 0);
+            }
+            else
+            {
+                baseQuery = baseQuery
+                       .Where(e => e.UserData!.FirstOrDefault(f => f.UserId.Equals(query.User!.Id) && f.Key == e.UserDataKey)!.PlaybackPositionTicks == 0);
+            }
+        }
+
+        if (query.ArtistIds.Length > 0)
+        {
+            baseQuery = baseQuery
+                   .Where(e => e.ItemValues!.Any(f => f.Type <= 1 && context.BaseItems.Where(w => query.ArtistIds.Contains(w.Id)).Any(w => w.CleanName == f.CleanValue)));
+        }
+
+        if (query.AlbumArtistIds.Length > 0)
+        {
+            baseQuery = baseQuery
+               .Where(e => e.ItemValues!.Any(f => f.Type == 1 && context.BaseItems.Where(w => query.ArtistIds.Contains(w.Id)).Any(w => w.CleanName == f.CleanValue)));
+        }
+
+        if (query.ContributingArtistIds.Length > 0)
+        {
+            var contributingArtists = context.BaseItems.Where(e => query.ContributingArtistIds.Contains(e.Id));
+            baseQuery = baseQuery.Where(e => e.ItemValues!.Any(f => f.Type == 0 && contributingArtists.Any(w => w.CleanName == f.CleanValue)));
+
+            clauseBuilder.Append('(');
+            for (var i = 0; i < query.ContributingArtistIds.Length; i++)
+            {
+                clauseBuilder.Append("((select CleanName from TypedBaseItems where guid=@ArtistIds")
+                    .Append(i)
+                    .Append(") in (select CleanValue from ItemValues where ItemId=Guid and Type=0) AND (select CleanName from TypedBaseItems where guid=@ArtistIds")
+                    .Append(i)
+                    .Append(") not in (select CleanValue from ItemValues where ItemId=Guid and Type=1)) OR ");
+                statement?.TryBind("@ArtistIds" + i, query.ContributingArtistIds[i]);
+            }
+
+            clauseBuilder.Length -= Or.Length;
+            whereClauses.Add(clauseBuilder.Append(')').ToString());
+            clauseBuilder.Length = 0;
+        }
+
+        if (query.AlbumIds.Length > 0)
+        {
+            clauseBuilder.Append('(');
+            for (var i = 0; i < query.AlbumIds.Length; i++)
+            {
+                clauseBuilder.Append("Album in (select Name from typedbaseitems where guid=@AlbumIds")
+                    .Append(i)
+                    .Append(") OR ");
+                statement?.TryBind("@AlbumIds" + i, query.AlbumIds[i]);
+            }
+
+            clauseBuilder.Length -= Or.Length;
+            whereClauses.Add(clauseBuilder.Append(')').ToString());
+            clauseBuilder.Length = 0;
+        }
+
+        if (query.ExcludeArtistIds.Length > 0)
+        {
+            clauseBuilder.Append('(');
+            for (var i = 0; i < query.ExcludeArtistIds.Length; i++)
+            {
+                clauseBuilder.Append("(guid not in (select itemid from ItemValues where CleanValue = (select CleanName from TypedBaseItems where guid=@ExcludeArtistId")
+                    .Append(i)
+                    .Append(") and Type<=1)) OR ");
+                statement?.TryBind("@ExcludeArtistId" + i, query.ExcludeArtistIds[i]);
+            }
+
+            clauseBuilder.Length -= Or.Length;
+            whereClauses.Add(clauseBuilder.Append(')').ToString());
+            clauseBuilder.Length = 0;
+        }
+
+        if (query.GenreIds.Count > 0)
+        {
+            clauseBuilder.Append('(');
+            for (var i = 0; i < query.GenreIds.Count; i++)
+            {
+                clauseBuilder.Append("(guid in (select itemid from ItemValues where CleanValue = (select CleanName from TypedBaseItems where guid=@GenreId")
+                    .Append(i)
+                    .Append(") and Type=2)) OR ");
+                statement?.TryBind("@GenreId" + i, query.GenreIds[i]);
+            }
+
+            clauseBuilder.Length -= Or.Length;
+            whereClauses.Add(clauseBuilder.Append(')').ToString());
+            clauseBuilder.Length = 0;
+        }
+
+        if (query.Genres.Count > 0)
+        {
+            clauseBuilder.Append('(');
+            for (var i = 0; i < query.Genres.Count; i++)
+            {
+                clauseBuilder.Append("@Genre")
+                    .Append(i)
+                    .Append(" in (select CleanValue from ItemValues where ItemId=Guid and Type=2) OR ");
+                statement?.TryBind("@Genre" + i, GetCleanValue(query.Genres[i]));
+            }
+
+            clauseBuilder.Length -= Or.Length;
+            whereClauses.Add(clauseBuilder.Append(')').ToString());
+            clauseBuilder.Length = 0;
+        }
+
+        if (tags.Count > 0)
+        {
+            clauseBuilder.Append('(');
+            for (var i = 0; i < tags.Count; i++)
+            {
+                clauseBuilder.Append("@Tag")
+                    .Append(i)
+                    .Append(" in (select CleanValue from ItemValues where ItemId=Guid and Type=4) OR ");
+                statement?.TryBind("@Tag" + i, GetCleanValue(tags[i]));
+            }
+
+            clauseBuilder.Length -= Or.Length;
+            whereClauses.Add(clauseBuilder.Append(')').ToString());
+            clauseBuilder.Length = 0;
+        }
+
+        if (excludeTags.Count > 0)
+        {
+            clauseBuilder.Append('(');
+            for (var i = 0; i < excludeTags.Count; i++)
+            {
+                clauseBuilder.Append("@ExcludeTag")
+                    .Append(i)
+                    .Append(" not in (select CleanValue from ItemValues where ItemId=Guid and Type=4) OR ");
+                statement?.TryBind("@ExcludeTag" + i, GetCleanValue(excludeTags[i]));
+            }
+
+            clauseBuilder.Length -= Or.Length;
+            whereClauses.Add(clauseBuilder.Append(')').ToString());
+            clauseBuilder.Length = 0;
+        }
+
+        if (query.StudioIds.Length > 0)
+        {
+            clauseBuilder.Append('(');
+            for (var i = 0; i < query.StudioIds.Length; i++)
+            {
+                clauseBuilder.Append("(guid in (select itemid from ItemValues where CleanValue = (select CleanName from TypedBaseItems where guid=@StudioId")
+                    .Append(i)
+                    .Append(") and Type=3)) OR ");
+                statement?.TryBind("@StudioId" + i, query.StudioIds[i]);
+            }
+
+            clauseBuilder.Length -= Or.Length;
+            whereClauses.Add(clauseBuilder.Append(')').ToString());
+            clauseBuilder.Length = 0;
+        }
+
+        if (query.OfficialRatings.Length > 0)
+        {
+            clauseBuilder.Append('(');
+            for (var i = 0; i < query.OfficialRatings.Length; i++)
+            {
+                clauseBuilder.Append("OfficialRating=@OfficialRating").Append(i).Append(Or);
+                statement?.TryBind("@OfficialRating" + i, query.OfficialRatings[i]);
+            }
+
+            clauseBuilder.Length -= Or.Length;
+            whereClauses.Add(clauseBuilder.Append(')').ToString());
+            clauseBuilder.Length = 0;
+        }
+
+        clauseBuilder.Append('(');
+        if (query.HasParentalRating ?? false)
+        {
+            clauseBuilder.Append("InheritedParentalRatingValue not null");
+            if (query.MinParentalRating.HasValue)
+            {
+                clauseBuilder.Append(" AND InheritedParentalRatingValue >= @MinParentalRating");
+                statement?.TryBind("@MinParentalRating", query.MinParentalRating.Value);
+            }
+
+            if (query.MaxParentalRating.HasValue)
+            {
+                clauseBuilder.Append(" AND InheritedParentalRatingValue <= @MaxParentalRating");
+                statement?.TryBind("@MaxParentalRating", query.MaxParentalRating.Value);
+            }
+        }
+        else if (query.BlockUnratedItems.Length > 0)
+        {
+            const string ParamName = "@UnratedType";
+            clauseBuilder.Append("(InheritedParentalRatingValue is null AND UnratedType not in (");
+
+            for (int i = 0; i < query.BlockUnratedItems.Length; i++)
+            {
+                clauseBuilder.Append(ParamName).Append(i).Append(',');
+                statement?.TryBind(ParamName + i, query.BlockUnratedItems[i].ToString());
+            }
+
+            // Remove trailing comma
+            clauseBuilder.Length--;
+            clauseBuilder.Append("))");
+
+            if (query.MinParentalRating.HasValue || query.MaxParentalRating.HasValue)
+            {
+                clauseBuilder.Append(" OR (");
+            }
+
+            if (query.MinParentalRating.HasValue)
+            {
+                clauseBuilder.Append("InheritedParentalRatingValue >= @MinParentalRating");
+                statement?.TryBind("@MinParentalRating", query.MinParentalRating.Value);
+            }
+
+            if (query.MaxParentalRating.HasValue)
+            {
+                if (query.MinParentalRating.HasValue)
+                {
+                    clauseBuilder.Append(" AND ");
+                }
+
+                clauseBuilder.Append("InheritedParentalRatingValue <= @MaxParentalRating");
+                statement?.TryBind("@MaxParentalRating", query.MaxParentalRating.Value);
+            }
+
+            if (query.MinParentalRating.HasValue || query.MaxParentalRating.HasValue)
+            {
+                clauseBuilder.Append(')');
+            }
+
+            if (!(query.MinParentalRating.HasValue || query.MaxParentalRating.HasValue))
+            {
+                clauseBuilder.Append(" OR InheritedParentalRatingValue not null");
+            }
+        }
+        else if (query.MinParentalRating.HasValue)
+        {
+            clauseBuilder.Append("InheritedParentalRatingValue is null OR (InheritedParentalRatingValue >= @MinParentalRating");
+            statement?.TryBind("@MinParentalRating", query.MinParentalRating.Value);
+
+            if (query.MaxParentalRating.HasValue)
+            {
+                clauseBuilder.Append(" AND InheritedParentalRatingValue <= @MaxParentalRating");
+                statement?.TryBind("@MaxParentalRating", query.MaxParentalRating.Value);
+            }
+
+            clauseBuilder.Append(')');
+        }
+        else if (query.MaxParentalRating.HasValue)
+        {
+            clauseBuilder.Append("InheritedParentalRatingValue is null OR InheritedParentalRatingValue <= @MaxParentalRating");
+            statement?.TryBind("@MaxParentalRating", query.MaxParentalRating.Value);
+        }
+        else if (!query.HasParentalRating ?? false)
+        {
+            clauseBuilder.Append("InheritedParentalRatingValue is null");
+        }
+
+        if (clauseBuilder.Length > 1)
+        {
+            whereClauses.Add(clauseBuilder.Append(')').ToString());
+            clauseBuilder.Length = 0;
+        }
+
+        if (query.HasOfficialRating.HasValue)
+        {
+            if (query.HasOfficialRating.Value)
+            {
+                whereClauses.Add("(OfficialRating not null AND OfficialRating<>'')");
+            }
+            else
+            {
+                whereClauses.Add("(OfficialRating is null OR OfficialRating='')");
+            }
+        }
+
+        if (query.HasOverview.HasValue)
+        {
+            if (query.HasOverview.Value)
+            {
+                whereClauses.Add("(Overview not null AND Overview<>'')");
+            }
+            else
+            {
+                whereClauses.Add("(Overview is null OR Overview='')");
+            }
+        }
+
+        if (query.HasOwnerId.HasValue)
+        {
+            if (query.HasOwnerId.Value)
+            {
+                whereClauses.Add("OwnerId not null");
+            }
+            else
+            {
+                whereClauses.Add("OwnerId is null");
+            }
+        }
+
+        if (!string.IsNullOrWhiteSpace(query.HasNoAudioTrackWithLanguage))
+        {
+            whereClauses.Add("((select language from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.StreamType='Audio' and MediaStreams.Language=@HasNoAudioTrackWithLanguage limit 1) is null)");
+            statement?.TryBind("@HasNoAudioTrackWithLanguage", query.HasNoAudioTrackWithLanguage);
+        }
+
+        if (!string.IsNullOrWhiteSpace(query.HasNoInternalSubtitleTrackWithLanguage))
+        {
+            whereClauses.Add("((select language from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.StreamType='Subtitle' and MediaStreams.IsExternal=0 and MediaStreams.Language=@HasNoInternalSubtitleTrackWithLanguage limit 1) is null)");
+            statement?.TryBind("@HasNoInternalSubtitleTrackWithLanguage", query.HasNoInternalSubtitleTrackWithLanguage);
+        }
+
+        if (!string.IsNullOrWhiteSpace(query.HasNoExternalSubtitleTrackWithLanguage))
+        {
+            whereClauses.Add("((select language from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.StreamType='Subtitle' and MediaStreams.IsExternal=1 and MediaStreams.Language=@HasNoExternalSubtitleTrackWithLanguage limit 1) is null)");
+            statement?.TryBind("@HasNoExternalSubtitleTrackWithLanguage", query.HasNoExternalSubtitleTrackWithLanguage);
+        }
+
+        if (!string.IsNullOrWhiteSpace(query.HasNoSubtitleTrackWithLanguage))
+        {
+            whereClauses.Add("((select language from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.StreamType='Subtitle' and MediaStreams.Language=@HasNoSubtitleTrackWithLanguage limit 1) is null)");
+            statement?.TryBind("@HasNoSubtitleTrackWithLanguage", query.HasNoSubtitleTrackWithLanguage);
+        }
+
+        if (query.HasSubtitles.HasValue)
+        {
+            if (query.HasSubtitles.Value)
+            {
+                whereClauses.Add("((select type from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.StreamType='Subtitle' limit 1) not null)");
+            }
+            else
+            {
+                whereClauses.Add("((select type from MediaStreams where MediaStreams.ItemId=A.Guid and MediaStreams.StreamType='Subtitle' limit 1) is null)");
+            }
+        }
+
+        if (query.HasChapterImages.HasValue)
+        {
+            if (query.HasChapterImages.Value)
+            {
+                whereClauses.Add("((select imagepath from Chapters2 where Chapters2.ItemId=A.Guid and imagepath not null limit 1) not null)");
+            }
+            else
+            {
+                whereClauses.Add("((select imagepath from Chapters2 where Chapters2.ItemId=A.Guid and imagepath not null limit 1) is null)");
+            }
+        }
+
+        if (query.HasDeadParentId.HasValue && query.HasDeadParentId.Value)
+        {
+            whereClauses.Add("ParentId NOT NULL AND ParentId NOT IN (select guid from TypedBaseItems)");
+        }
+
+        if (query.IsDeadArtist.HasValue && query.IsDeadArtist.Value)
+        {
+            whereClauses.Add("CleanName not in (Select CleanValue From ItemValues where Type in (0,1))");
+        }
+
+        if (query.IsDeadStudio.HasValue && query.IsDeadStudio.Value)
+        {
+            whereClauses.Add("CleanName not in (Select CleanValue From ItemValues where Type = 3)");
+        }
+
+        if (query.IsDeadPerson.HasValue && query.IsDeadPerson.Value)
+        {
+            whereClauses.Add("Name not in (Select Name From People)");
+        }
+
+        if (query.Years.Length == 1)
+        {
+            whereClauses.Add("ProductionYear=@Years");
+            statement?.TryBind("@Years", query.Years[0].ToString(CultureInfo.InvariantCulture));
+        }
+        else if (query.Years.Length > 1)
+        {
+            var val = string.Join(',', query.Years);
+            whereClauses.Add("ProductionYear in (" + val + ")");
+        }
+
+        var isVirtualItem = query.IsVirtualItem ?? query.IsMissing;
+        if (isVirtualItem.HasValue)
+        {
+            whereClauses.Add("IsVirtualItem=@IsVirtualItem");
+            statement?.TryBind("@IsVirtualItem", isVirtualItem.Value);
+        }
+
+        if (query.IsSpecialSeason.HasValue)
+        {
+            if (query.IsSpecialSeason.Value)
+            {
+                whereClauses.Add("IndexNumber = 0");
+            }
+            else
+            {
+                whereClauses.Add("IndexNumber <> 0");
+            }
+        }
+
+        if (query.IsUnaired.HasValue)
+        {
+            if (query.IsUnaired.Value)
+            {
+                whereClauses.Add("PremiereDate >= DATETIME('now')");
+            }
+            else
+            {
+                whereClauses.Add("PremiereDate < DATETIME('now')");
+            }
+        }
+
+        if (query.MediaTypes.Length == 1)
+        {
+            whereClauses.Add("MediaType=@MediaTypes");
+            statement?.TryBind("@MediaTypes", query.MediaTypes[0].ToString());
+        }
+        else if (query.MediaTypes.Length > 1)
+        {
+            var val = string.Join(',', query.MediaTypes.Select(i => $"'{i}'"));
+            whereClauses.Add("MediaType in (" + val + ")");
+        }
+
+        if (query.ItemIds.Length > 0)
+        {
+            var includeIds = new List<string>();
+            var index = 0;
+            foreach (var id in query.ItemIds)
+            {
+                includeIds.Add("Guid = @IncludeId" + index);
+                statement?.TryBind("@IncludeId" + index, id);
+                index++;
+            }
+
+            whereClauses.Add("(" + string.Join(" OR ", includeIds) + ")");
+        }
+
+        if (query.ExcludeItemIds.Length > 0)
+        {
+            var excludeIds = new List<string>();
+            var index = 0;
+            foreach (var id in query.ExcludeItemIds)
+            {
+                excludeIds.Add("Guid <> @ExcludeId" + index);
+                statement?.TryBind("@ExcludeId" + index, id);
+                index++;
+            }
+
+            whereClauses.Add(string.Join(" AND ", excludeIds));
+        }
+
+        if (query.ExcludeProviderIds is not null && query.ExcludeProviderIds.Count > 0)
+        {
+            var excludeIds = new List<string>();
+
+            var index = 0;
+            foreach (var pair in query.ExcludeProviderIds)
+            {
+                if (string.Equals(pair.Key, nameof(MetadataProvider.TmdbCollection), StringComparison.OrdinalIgnoreCase))
+                {
+                    continue;
+                }
+
+                var paramName = "@ExcludeProviderId" + index;
+                excludeIds.Add("(ProviderIds is null or ProviderIds not like " + paramName + ")");
+                statement?.TryBind(paramName, "%" + pair.Key + "=" + pair.Value + "%");
+                index++;
+
+                break;
+            }
+
+            if (excludeIds.Count > 0)
+            {
+                whereClauses.Add(string.Join(" AND ", excludeIds));
+            }
+        }
+
+        if (query.HasAnyProviderId is not null && query.HasAnyProviderId.Count > 0)
+        {
+            var hasProviderIds = new List<string>();
+
+            var index = 0;
+            foreach (var pair in query.HasAnyProviderId)
+            {
+                if (string.Equals(pair.Key, nameof(MetadataProvider.TmdbCollection), StringComparison.OrdinalIgnoreCase))
+                {
+                    continue;
+                }
+
+                // TODO this seems to be an idea for a better schema where ProviderIds are their own table
+                //      but this is not implemented
+                // hasProviderIds.Add("(COALESCE((select value from ProviderIds where ItemId=Guid and Name = '" + pair.Key + "'), '') <> " + paramName + ")");
+
+                // TODO this is a really BAD way to do it since the pair:
+                //      Tmdb, 1234 matches Tmdb=1234 but also Tmdb=1234567
+                //      and maybe even NotTmdb=1234.
+
+                // this is a placeholder for this specific pair to correlate it in the bigger query
+                var paramName = "@HasAnyProviderId" + index;
+
+                // this is a search for the placeholder
+                hasProviderIds.Add("ProviderIds like " + paramName);
+
+                // this replaces the placeholder with a value, here: %key=val%
+                statement?.TryBind(paramName, "%" + pair.Key + "=" + pair.Value + "%");
+                index++;
+
+                break;
+            }
+
+            if (hasProviderIds.Count > 0)
+            {
+                whereClauses.Add("(" + string.Join(" OR ", hasProviderIds) + ")");
+            }
+        }
+
+        if (query.HasImdbId.HasValue)
+        {
+            whereClauses.Add(GetProviderIdClause(query.HasImdbId.Value, "imdb"));
+        }
+
+        if (query.HasTmdbId.HasValue)
+        {
+            whereClauses.Add(GetProviderIdClause(query.HasTmdbId.Value, "tmdb"));
+        }
+
+        if (query.HasTvdbId.HasValue)
+        {
+            whereClauses.Add(GetProviderIdClause(query.HasTvdbId.Value, "tvdb"));
+        }
+
+        var queryTopParentIds = query.TopParentIds;
+
+        if (queryTopParentIds.Length > 0)
+        {
+            var includedItemByNameTypes = GetItemByNameTypesInQuery(query);
+            var enableItemsByName = (query.IncludeItemsByName ?? false) && includedItemByNameTypes.Count > 0;
+
+            if (queryTopParentIds.Length == 1)
+            {
+                if (enableItemsByName && includedItemByNameTypes.Count == 1)
+                {
+                    whereClauses.Add("(TopParentId=@TopParentId or Type=@IncludedItemByNameType)");
+                    statement?.TryBind("@IncludedItemByNameType", includedItemByNameTypes[0]);
+                }
+                else if (enableItemsByName && includedItemByNameTypes.Count > 1)
+                {
+                    var itemByNameTypeVal = string.Join(',', includedItemByNameTypes.Select(i => "'" + i + "'"));
+                    whereClauses.Add("(TopParentId=@TopParentId or Type in (" + itemByNameTypeVal + "))");
+                }
+                else
+                {
+                    whereClauses.Add("(TopParentId=@TopParentId)");
+                }
+
+                statement?.TryBind("@TopParentId", queryTopParentIds[0].ToString("N", CultureInfo.InvariantCulture));
+            }
+            else if (queryTopParentIds.Length > 1)
+            {
+                var val = string.Join(',', queryTopParentIds.Select(i => "'" + i.ToString("N", CultureInfo.InvariantCulture) + "'"));
+
+                if (enableItemsByName && includedItemByNameTypes.Count == 1)
+                {
+                    whereClauses.Add("(Type=@IncludedItemByNameType or TopParentId in (" + val + "))");
+                    statement?.TryBind("@IncludedItemByNameType", includedItemByNameTypes[0]);
+                }
+                else if (enableItemsByName && includedItemByNameTypes.Count > 1)
+                {
+                    var itemByNameTypeVal = string.Join(',', includedItemByNameTypes.Select(i => "'" + i + "'"));
+                    whereClauses.Add("(Type in (" + itemByNameTypeVal + ") or TopParentId in (" + val + "))");
+                }
+                else
+                {
+                    whereClauses.Add("TopParentId in (" + val + ")");
+                }
+            }
+        }
+
+        if (query.AncestorIds.Length == 1)
+        {
+            whereClauses.Add("Guid in (select itemId from AncestorIds where AncestorId=@AncestorId)");
+            statement?.TryBind("@AncestorId", query.AncestorIds[0]);
+        }
+
+        if (query.AncestorIds.Length > 1)
+        {
+            var inClause = string.Join(',', query.AncestorIds.Select(i => "'" + i.ToString("N", CultureInfo.InvariantCulture) + "'"));
+            whereClauses.Add(string.Format(CultureInfo.InvariantCulture, "Guid in (select itemId from AncestorIds where AncestorIdText in ({0}))", inClause));
+        }
+
+        if (!string.IsNullOrWhiteSpace(query.AncestorWithPresentationUniqueKey))
+        {
+            var inClause = "select guid from TypedBaseItems where PresentationUniqueKey=@AncestorWithPresentationUniqueKey";
+            whereClauses.Add(string.Format(CultureInfo.InvariantCulture, "Guid in (select itemId from AncestorIds where AncestorId in ({0}))", inClause));
+            statement?.TryBind("@AncestorWithPresentationUniqueKey", query.AncestorWithPresentationUniqueKey);
+        }
+
+        if (!string.IsNullOrWhiteSpace(query.SeriesPresentationUniqueKey))
+        {
+            whereClauses.Add("SeriesPresentationUniqueKey=@SeriesPresentationUniqueKey");
+            statement?.TryBind("@SeriesPresentationUniqueKey", query.SeriesPresentationUniqueKey);
+        }
+
+        if (query.ExcludeInheritedTags.Length > 0)
+        {
+            var paramName = "@ExcludeInheritedTags";
+            if (statement is null)
+            {
+                int index = 0;
+                string excludedTags = string.Join(',', query.ExcludeInheritedTags.Select(_ => paramName + index++));
+                whereClauses.Add("((select CleanValue from ItemValues where ItemId=Guid and Type=6 and cleanvalue in (" + excludedTags + ")) is null)");
+            }
+            else
+            {
+                for (int index = 0; index < query.ExcludeInheritedTags.Length; index++)
+                {
+                    statement.TryBind(paramName + index, GetCleanValue(query.ExcludeInheritedTags[index]));
+                }
+            }
+        }
+
+        if (query.IncludeInheritedTags.Length > 0)
+        {
+            var paramName = "@IncludeInheritedTags";
+            if (statement is null)
+            {
+                int index = 0;
+                string includedTags = string.Join(',', query.IncludeInheritedTags.Select(_ => paramName + index++));
+                // Episodes do not store inherit tags from their parents in the database, and the tag may be still required by the client.
+                // In addtion 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)
+                {
+                    whereClauses.Add($"""
+                                          ((select CleanValue from ItemValues where ItemId=Guid and Type=6 and CleanValue in ({includedTags})) is not null
+                                          OR (select CleanValue from ItemValues where ItemId=ParentId and Type=6 and CleanValue in ({includedTags})) is not null)
+                                          """);
+                }
+
+                // A playlist should be accessible to its owner regardless of allowed tags.
+                else if (includeTypes.Length == 1 && includeTypes.FirstOrDefault() is BaseItemKind.Playlist)
+                {
+                    whereClauses.Add($"""
+                                          ((select CleanValue from ItemValues where ItemId=Guid and Type=6 and CleanValue in ({includedTags})) is not null
+                                          OR data like @PlaylistOwnerUserId)
+                                          """);
+                }
+                else
+                {
+                    whereClauses.Add("((select CleanValue from ItemValues where ItemId=Guid and Type=6 and cleanvalue in (" + includedTags + ")) is not null)");
+                }
+            }
+            else
+            {
+                for (int index = 0; index < query.IncludeInheritedTags.Length; index++)
+                {
+                    statement.TryBind(paramName + index, GetCleanValue(query.IncludeInheritedTags[index]));
+                }
+
+                if (query.User is not null)
+                {
+                    statement.TryBind("@PlaylistOwnerUserId", $"""%"OwnerUserId":"{query.User.Id.ToString("N")}"%""");
+                }
+            }
+        }
+
+        if (query.SeriesStatuses.Length > 0)
+        {
+            var statuses = new List<string>();
+
+            foreach (var seriesStatus in query.SeriesStatuses)
+            {
+                statuses.Add("data like  '%" + seriesStatus + "%'");
+            }
+
+            whereClauses.Add("(" + string.Join(" OR ", statuses) + ")");
+        }
+
+        if (query.BoxSetLibraryFolders.Length > 0)
+        {
+            var folderIdQueries = new List<string>();
+
+            foreach (var folderId in query.BoxSetLibraryFolders)
+            {
+                folderIdQueries.Add("data like '%" + folderId.ToString("N", CultureInfo.InvariantCulture) + "%'");
+            }
+
+            whereClauses.Add("(" + string.Join(" OR ", folderIdQueries) + ")");
+        }
+
+        if (query.VideoTypes.Length > 0)
+        {
+            var videoTypes = new List<string>();
+
+            foreach (var videoType in query.VideoTypes)
+            {
+                videoTypes.Add("data like '%\"VideoType\":\"" + videoType + "\"%'");
+            }
+
+            whereClauses.Add("(" + string.Join(" OR ", videoTypes) + ")");
+        }
+
+        if (query.Is3D.HasValue)
+        {
+            if (query.Is3D.Value)
+            {
+                whereClauses.Add("data like '%Video3DFormat%'");
+            }
+            else
+            {
+                whereClauses.Add("data not like '%Video3DFormat%'");
+            }
+        }
+
+        if (query.IsPlaceHolder.HasValue)
+        {
+            if (query.IsPlaceHolder.Value)
+            {
+                whereClauses.Add("data like '%\"IsPlaceHolder\":true%'");
+            }
+            else
+            {
+                whereClauses.Add("(data is null or data not like '%\"IsPlaceHolder\":true%')");
+            }
+        }
+
+        if (query.HasSpecialFeature.HasValue)
+        {
+            if (query.HasSpecialFeature.Value)
+            {
+                whereClauses.Add("ExtraIds not null");
+            }
+            else
+            {
+                whereClauses.Add("ExtraIds is null");
+            }
+        }
+
+        if (query.HasTrailer.HasValue)
+        {
+            if (query.HasTrailer.Value)
+            {
+                whereClauses.Add("ExtraIds not null");
+            }
+            else
+            {
+                whereClauses.Add("ExtraIds is null");
+            }
+        }
+
+        if (query.HasThemeSong.HasValue)
+        {
+            if (query.HasThemeSong.Value)
+            {
+                whereClauses.Add("ExtraIds not null");
+            }
+            else
+            {
+                whereClauses.Add("ExtraIds is null");
+            }
+        }
+
+        if (query.HasThemeVideo.HasValue)
+        {
+            if (query.HasThemeVideo.Value)
+            {
+                whereClauses.Add("ExtraIds not null");
+            }
+            else
+            {
+                whereClauses.Add("ExtraIds is null");
+            }
+        }
+    }
+
     /// <summary>
     /// Gets the type.
     /// </summary>
@@ -58,14 +1357,26 @@ public class BaseItemManager
             .FirstOrDefault(t => t is not null));
     }
 
-    /// <summary>
-    /// Saves the items.
-    /// </summary>
-    /// <param name="items">The items.</param>
-    /// <param name="cancellationToken">The cancellation token.</param>
-    /// <exception cref="ArgumentNullException">
-    /// <paramref name="items"/> or <paramref name="cancellationToken"/> is <c>null</c>.
-    /// </exception>
+    /// <inheritdoc cref="IItemRepository" />
+    public void SaveImages(BaseItem item)
+    {
+        ArgumentNullException.ThrowIfNull(item);
+
+        var images = SerializeImages(item.ImageInfos);
+        using var db = _dbProvider.CreateDbContext();
+
+        db.BaseItems
+            .Where(e => e.Id.Equals(item.Id))
+            .ExecuteUpdate(e => e.SetProperty(f => f.Images, images));
+    }
+
+    /// <inheritdoc cref="IItemRepository" />
+    public void SaveItems(IReadOnlyList<BaseItemDto> items, CancellationToken cancellationToken)
+    {
+        UpdateOrInsertItems(items, cancellationToken);
+    }
+
+    /// <inheritdoc cref="IItemRepository" />
     public void UpdateOrInsertItems(IReadOnlyList<BaseItemDto> items, CancellationToken cancellationToken)
     {
         ArgumentNullException.ThrowIfNull(items);
@@ -124,7 +1435,8 @@ public class BaseItemManager
         context.SaveChanges(true);
     }
 
-    public BaseItemDto? GetSingle(Guid id)
+    /// <inheritdoc cref="IItemRepository" />
+    public BaseItemDto? RetrieveItem(Guid id)
     {
         if (id.IsEmpty())
         {
@@ -141,13 +1453,6 @@ public class BaseItemManager
         return DeserialiseBaseItem(item);
     }
 
-    private BaseItemDto DeserialiseBaseItem(BaseItemEntity baseItemEntity)
-    {
-        var type = GetType(baseItemEntity.Type) ?? throw new InvalidOperationException("Cannot deserialise unkown type.");
-        var dto = Activator.CreateInstance(type) as BaseItemDto ?? throw new InvalidOperationException("Cannot deserialise unkown type.");;
-        return Map(baseItemEntity, dto);
-    }
-
     /// <summary>
     /// Maps a Entity to the DTO.
     /// </summary>
@@ -462,6 +1767,13 @@ public class BaseItemManager
         return entity;
     }
 
+    private BaseItemDto DeserialiseBaseItem(BaseItemEntity baseItemEntity)
+    {
+        var type = GetType(baseItemEntity.Type) ?? throw new InvalidOperationException("Cannot deserialise unkown type.");
+        var dto = Activator.CreateInstance(type) as BaseItemDto ?? throw new InvalidOperationException("Cannot deserialise unkown type."); ;
+        return Map(baseItemEntity, dto);
+    }
+
     private string GetCleanValue(string value)
     {
         if (string.IsNullOrWhiteSpace(value))
diff --git a/Jellyfin.Server.Implementations/Item/ChapterManager.cs b/Jellyfin.Server.Implementations/Item/ChapterManager.cs
index 273cc96ba..7b0f98fde 100644
--- a/Jellyfin.Server.Implementations/Item/ChapterManager.cs
+++ b/Jellyfin.Server.Implementations/Item/ChapterManager.cs
@@ -1,26 +1,74 @@
 using System;
 using System.Collections.Generic;
+using System.Collections.Immutable;
 using System.Linq;
 using Jellyfin.Data.Entities;
+using MediaBrowser.Controller.Chapters;
+using MediaBrowser.Controller.Drawing;
+using MediaBrowser.Model.Dto;
 using MediaBrowser.Model.Entities;
 using Microsoft.EntityFrameworkCore;
-using BaseItemDto = MediaBrowser.Controller.Entities.BaseItem;
 
 namespace Jellyfin.Server.Implementations.Item;
 
-public class ChapterManager
+/// <summary>
+/// The Chapter manager.
+/// </summary>
+public class ChapterManager : IChapterManager
 {
     private readonly IDbContextFactory<JellyfinDbContext> _dbProvider;
+    private readonly IImageProcessor _imageProcessor;
 
-    public ChapterManager(IDbContextFactory<JellyfinDbContext> dbProvider)
+    /// <summary>
+    /// Initializes a new instance of the <see cref="ChapterManager"/> class.
+    /// </summary>
+    /// <param name="dbProvider">The EFCore provider.</param>
+    /// <param name="imageProcessor">The Image Processor.</param>
+    public ChapterManager(IDbContextFactory<JellyfinDbContext> dbProvider, IImageProcessor imageProcessor)
     {
         _dbProvider = dbProvider;
+        _imageProcessor = imageProcessor;
     }
 
-    public IReadOnlyList<ChapterInfo> GetChapters(BaseItemDto baseItemDto)
+    /// <inheritdoc cref="IChapterManager"/>
+    public ChapterInfo? GetChapter(BaseItemDto baseItem, int index)
     {
         using var context = _dbProvider.CreateDbContext();
-        return context.Chapters.Where(e => e.ItemId.Equals(baseItemDto.Id)).Select(Map).ToList();
+        var chapter = context.Chapters.FirstOrDefault(e => e.ItemId.Equals(baseItem.Id) && e.ChapterIndex == index);
+        if (chapter is not null)
+        {
+            return Map(chapter, baseItem);
+        }
+
+        return null;
+    }
+
+    /// <inheritdoc cref="IChapterManager"/>
+    public IReadOnlyList<ChapterInfo> GetChapters(BaseItemDto baseItem)
+    {
+        using var context = _dbProvider.CreateDbContext();
+        return context.Chapters.Where(e => e.ItemId.Equals(baseItem.Id))
+            .ToList()
+            .Select(e => Map(e, baseItem))
+            .ToImmutableArray();
+    }
+
+    /// <inheritdoc cref="IChapterManager"/>
+    public void SaveChapters(Guid itemId, IReadOnlyList<ChapterInfo> chapters)
+    {
+        using var context = _dbProvider.CreateDbContext();
+        using (var transaction = context.Database.BeginTransaction())
+        {
+            context.Chapters.Where(e => e.ItemId.Equals(itemId)).ExecuteDelete();
+            for (var i = 0; i < chapters.Count; i++)
+            {
+                var chapter = chapters[i];
+                context.Chapters.Add(Map(chapter, i, itemId));
+            }
+
+            context.SaveChanges();
+            transaction.Commit();
+        }
     }
 
     private Chapter Map(ChapterInfo chapterInfo, int index, Guid itemId)
diff --git a/MediaBrowser.Providers/Chapters/ChapterManager.cs b/MediaBrowser.Controller/Chapters/ChapterManager.cs
index 3cbfe7d4d..a9e11f603 100644
--- a/MediaBrowser.Providers/Chapters/ChapterManager.cs
+++ b/MediaBrowser.Controller/Chapters/ChapterManager.cs
@@ -10,9 +10,7 @@ namespace MediaBrowser.Providers.Chapters
 {
     public class ChapterManager : IChapterManager
     {
-        private readonly IItemRepository _itemRepo;
-
-        public ChapterManager(IItemRepository itemRepo)
+        public ChapterManager(IDbContextFactory<JellyfinDbContext> dbProvider)
         {
             _itemRepo = itemRepo;
         }
diff --git a/MediaBrowser.Controller/Chapters/IChapterManager.cs b/MediaBrowser.Controller/Chapters/IChapterManager.cs
index c049bb97e..55762c7fc 100644
--- a/MediaBrowser.Controller/Chapters/IChapterManager.cs
+++ b/MediaBrowser.Controller/Chapters/IChapterManager.cs
@@ -1,5 +1,6 @@
 using System;
 using System.Collections.Generic;
+using MediaBrowser.Model.Dto;
 using MediaBrowser.Model.Entities;
 
 namespace MediaBrowser.Controller.Chapters
@@ -15,5 +16,20 @@ namespace MediaBrowser.Controller.Chapters
         /// <param name="itemId">The item.</param>
         /// <param name="chapters">The set of chapters.</param>
         void SaveChapters(Guid itemId, IReadOnlyList<ChapterInfo> chapters);
+
+        /// <summary>
+        /// Gets all chapters associated with the baseItem.
+        /// </summary>
+        /// <param name="baseItem">The baseitem.</param>
+        /// <returns>A readonly list of chapter instances.</returns>
+        IReadOnlyList<ChapterInfo> GetChapters(BaseItemDto baseItem);
+
+        /// <summary>
+        /// Gets a single chapter of a BaseItem on a specific index.
+        /// </summary>
+        /// <param name="baseItem">The baseitem.</param>
+        /// <param name="index">The index of that chapter.</param>
+        /// <returns>A chapter instance.</returns>
+        ChapterInfo? GetChapter(BaseItemDto baseItem, int index);
     }
 }
diff --git a/MediaBrowser.Controller/Persistence/IItemRepository.cs b/MediaBrowser.Controller/Persistence/IItemRepository.cs
index 2c52b2b45..21b9ee4b7 100644
--- a/MediaBrowser.Controller/Persistence/IItemRepository.cs
+++ b/MediaBrowser.Controller/Persistence/IItemRepository.cs
@@ -40,28 +40,6 @@ namespace MediaBrowser.Controller.Persistence
         BaseItem RetrieveItem(Guid id);
 
         /// <summary>
-        /// Gets chapters for an item.
-        /// </summary>
-        /// <param name="item">The item.</param>
-        /// <returns>The list of chapter info.</returns>
-        List<ChapterInfo> GetChapters(BaseItem item);
-
-        /// <summary>
-        /// Gets a single chapter for an item.
-        /// </summary>
-        /// <param name="item">The item.</param>
-        /// <param name="index">The chapter index.</param>
-        /// <returns>The chapter info at the specified index.</returns>
-        ChapterInfo GetChapter(BaseItem item, int index);
-
-        /// <summary>
-        /// Saves the chapters.
-        /// </summary>
-        /// <param name="id">The item id.</param>
-        /// <param name="chapters">The list of chapters to save.</param>
-        void SaveChapters(Guid id, IReadOnlyList<ChapterInfo> chapters);
-
-        /// <summary>
         /// Gets the media streams.
         /// </summary>
         /// <param name="query">The query.</param>
diff --git a/MediaBrowser.Providers/MediaBrowser.Providers.csproj b/MediaBrowser.Providers/MediaBrowser.Providers.csproj
index 9a65852f0..f2df731c0 100644
--- a/MediaBrowser.Providers/MediaBrowser.Providers.csproj
+++ b/MediaBrowser.Providers/MediaBrowser.Providers.csproj
@@ -23,7 +23,7 @@
     <PackageReference Include="Microsoft.Extensions.Http" />
     <PackageReference Include="Newtonsoft.Json" />
     <PackageReference Include="PlaylistsNET" />
-    <PackageReference Include="z440.atl.core"/>
+    <PackageReference Include="z440.atl.core" />
     <PackageReference Include="TMDbLib" />
   </ItemGroup>