Kavita/Kavita.Database/Repositories/UserRepository.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using AutoMapper;
using AutoMapper.QueryableExtensions;
using Kavita.API.Repositories;
using Kavita.Common.Extensions;
using Kavita.Common.Helpers;
using Kavita.Database.Extensions;
using Kavita.Models.Constants;
using Kavita.Models.DTOs;
using Kavita.Models.DTOs.Account;
using Kavita.Models.DTOs.Dashboard;
using Kavita.Models.DTOs.Filtering.v2;
using Kavita.Models.DTOs.KavitaPlus.Account;
using Kavita.Models.DTOs.Reader;
using Kavita.Models.DTOs.Scrobbling;
using Kavita.Models.DTOs.SeriesDetail;
using Kavita.Models.DTOs.SideNav;
using Kavita.Models.Entities;
using Kavita.Models.Entities.Enums.UserPreferences;
using Kavita.Models.Entities.User;
using Microsoft.AspNetCore.Identity;
using Microsoft.EntityFrameworkCore;

namespace Kavita.Database.Repositories;

public class UserRepository(DataContext context, UserManager<AppUser> userManager, IMapper mapper)
    : IUserRepository
{
    public void Add(AppUserAuthKey key)
    {
        context.AppUserAuthKey.Add(key);
    }

    public void Add(AppUserBookmark bookmark)
    {
        context.AppUserBookmark.Add(bookmark);
    }

    public void Add(AppUser user)
    {
        context.AppUser.Add(user);
    }

    public void Update(AppUser user)
    {
        context.Entry(user).State = EntityState.Modified;
    }

    public void Update(AppUserPreferences preferences)
    {
        context.Entry(preferences).State = EntityState.Modified;
    }

    public void Update(AppUserBookmark bookmark)
    {
        context.Entry(bookmark).State = EntityState.Modified;
    }

    public void Update(AppUserDashboardStream stream)
    {
        context.Entry(stream).State = EntityState.Modified;
    }

    public void Update(AppUserSideNavStream stream)
    {
        context.Entry(stream).State = EntityState.Modified;
    }

    public void Delete(AppUser? user)
    {
        if (user == null) return;
        context.AppUser.Remove(user);
    }

    public void Delete(AppUserAuthKey? key)
    {
        if (key == null) return;
        context.AppUserAuthKey.Remove(key);
    }

    public void Delete(AppUserBookmark bookmark)
    {
        context.AppUserBookmark.Remove(bookmark);
    }

    public void Delete(IEnumerable<AppUserDashboardStream> streams)
    {
        context.AppUserDashboardStream.RemoveRange(streams);
    }

    public void Delete(AppUserDashboardStream stream)
    {
        context.AppUserDashboardStream.Remove(stream);
    }

    public void Delete(IEnumerable<AppUserSideNavStream> streams)
    {
        context.AppUserSideNavStream.RemoveRange(streams);
    }

    public void Delete(AppUserSideNavStream stream)
    {
        context.AppUserSideNavStream.Remove(stream);
    }


    /// <summary>
    /// A one stop shop to get a tracked AppUser instance with any number of JOINs generated by passing bitwise flags.
    /// </summary>
    /// <param name="username"></param>
    /// <param name="includeFlags">Includes() you want. Pass multiple with flag1 | flag2 </param>
    /// <param name="ct"></param>
    /// <returns></returns>
    public async Task<AppUser?> GetUserByUsernameAsync(string username, AppUserIncludes includeFlags = AppUserIncludes.None, CancellationToken ct = default)
    {
        return await context.Users
            .Where(x => x.UserName == username)
            .Includes(includeFlags)
            .SingleOrDefaultAsync(ct);
    }

    /// <summary>
    /// A one stop shop to get a tracked AppUser instance with any number of JOINs generated by passing bitwise flags.
    /// </summary>
    /// <param name="userId"></param>
    /// <param name="includeFlags">Includes() you want. Pass multiple with flag1 | flag2 </param>
    /// <param name="ct"></param>
    /// <returns></returns>
    public async Task<AppUser?> GetUserByIdAsync(int userId, AppUserIncludes includeFlags = AppUserIncludes.None, CancellationToken ct = default)
    {
        return await context.Users
            .Where(x => x.Id == userId)
            .Includes(includeFlags)
            .FirstOrDefaultAsync(ct);
    }

    public async Task<AppUser?> GetUserByAuthKey(string authKey, AppUserIncludes includeFlags = AppUserIncludes.None, CancellationToken ct = default)
    {
        if (string.IsNullOrEmpty(authKey)) return null;

        return await context.AppUserAuthKey
            .Where(ak => ak.Key == authKey)
            .HasNotExpired()
            .Select(ak => ak.AppUser)
            .Includes(includeFlags)
            .FirstOrDefaultAsync(ct);
    }

    public async Task<IEnumerable<AppUserBookmark>> GetAllBookmarksAsync(CancellationToken ct = default)
    {
        return await context.AppUserBookmark.ToListAsync(ct);
    }

    public async Task<AppUserBookmark?> GetBookmarkForPage(int page, int chapterId, int imageOffset, int userId, CancellationToken ct = default)
    {
        return await context.AppUserBookmark
            .Where(b => b.Page == page && b.ChapterId == chapterId && b.AppUserId == userId && b.ImageOffset == imageOffset)
            .FirstOrDefaultAsync(ct);
    }

    public async Task<AppUserBookmark?> GetBookmarkAsync(int bookmarkId, CancellationToken ct = default)
    {
        return await context.AppUserBookmark
            .Where(b => b.Id == bookmarkId)
            .FirstOrDefaultAsync(ct);
    }


    /// <summary>
    /// This fetches the Id for a user. Use whenever you just need an ID.
    /// </summary>
    /// <param name="username"></param>
    /// <param name="ct"></param>
    /// <returns></returns>
    public async Task<int> GetUserIdByUsernameAsync(string username, CancellationToken ct = default)
    {
        return await context.Users
            .Where(x => x.UserName == username)
            .Select(u => u.Id)
            .SingleOrDefaultAsync(ct);
    }


    /// <summary>
    /// Returns all Bookmarks for a given set of Ids
    /// </summary>
    /// <param name="bookmarkIds"></param>
    /// <param name="ct"></param>
    /// <returns></returns>
    public async Task<IList<AppUserBookmark>> GetAllBookmarksByIds(IList<int> bookmarkIds, CancellationToken ct = default)
    {
        return await context.AppUserBookmark
            .Where(b => bookmarkIds.Contains(b.Id))
            .OrderBy(b => b.Created)
            .ToListAsync(ct);
    }

    public async Task<AppUser?> GetUserByEmailAsync(string email, AppUserIncludes includes = AppUserIncludes.None, CancellationToken ct = default)
    {
        var lowerEmail = email.ToLower();
        return await context.AppUser
            .Includes(includes)
            .FirstOrDefaultAsync(u => u.Email != null && u.Email.ToLower().Equals(lowerEmail), ct);
    }


    public async Task<IEnumerable<AppUserPreferences>> GetAllPreferencesByThemeAsync(int themeId, CancellationToken ct = default)
    {
        return await context.AppUserPreferences
            .Include(p => p.Theme)
            .Where(p => p.Theme.Id == themeId)
            .AsSplitQuery()
            .ToListAsync(ct);
    }

    public async Task<IEnumerable<AppUserPreferences>> GetAllPreferencesByFontAsync(string fontName, CancellationToken ct = default)
    {
        return await context.AppUserPreferences
            .Where(p => p.BookReaderFontFamily == fontName)
            .AsSplitQuery()
            .ToListAsync(ct);
    }

    public async Task<bool> HasAccessToLibrary(int libraryId, int userId, CancellationToken ct = default)
    {
        return await context.Library
            .Include(l => l.AppUsers)
            .AsSplitQuery()
            .AnyAsync(library => library.AppUsers.Any(user => user.Id == userId) && library.Id == libraryId, ct);
    }

    /// <summary>
    /// Does the user have library and age restriction access to a given series
    /// </summary>
    /// <returns></returns>
    public async Task<bool> HasAccessToSeries(int userId, int seriesId, CancellationToken ct = default)
    {
        var userRating = await context.AppUser.GetUserAgeRestriction(userId);
        return await context.Series
            .Include(s => s.Library)
            .Where(s => s.Library.AppUsers.Any(user => user.Id == userId))
            .RestrictAgainstAgeRestriction(userRating)
            .AsSplitQuery()
            .AnyAsync(s => s.Id == seriesId, ct);
    }

    public async Task<bool> HasAccessToVolume(int userId, int volumeId, CancellationToken ct = default)
    {
        var userRating = await context.AppUser.GetUserAgeRestriction(userId);
        return await context.Volume
            .Where(v => v.Id == volumeId)
            .Include(v => v.Series)
            .ThenInclude(s => s.Library)
            .Where(v => v.Series.Library.AppUsers.Any(user => user.Id == userId))
            .Select(v => v.Series)
            .RestrictAgainstAgeRestriction(userRating)
            .AsSplitQuery()
            .AnyAsync(ct);
    }

    public async Task<bool> HasAccessToChapter(int userId, int chapterId, CancellationToken ct = default)
    {
        var userRating = await context.AppUser.GetUserAgeRestriction(userId);
        return await context.Chapter
            .Include(c => c.Volume)
            .ThenInclude(v => v.Series)
            .ThenInclude(s => s.Library)
            .Where(c => c.Volume.Series.Library.AppUsers.Any(user => user.Id == userId))
            .RestrictAgainstAgeRestriction(userRating)
            .AsSplitQuery()
            .AnyAsync(c => c.Id == chapterId, ct);
    }

    public async Task<bool> HasAccessToPerson(int userId, int personId, CancellationToken ct = default)
    {
        var userRating = await context.AppUser.GetUserAgeRestriction(userId);
        return await context.Person
            .RestrictAgainstAgeRestriction(userRating)
            .AnyAsync(p => p.Id == personId, ct);
    }

    public Task<bool> HasAccessToReadingList(int userId, int readingListId, CancellationToken ct = default)
    {
        return context.ReadingList
            .Where(rl => rl.AppUserId == userId || rl.Promoted)
            .AnyAsync(rl => rl.Id == readingListId, ct);
    }

    public async Task<IEnumerable<AppUser>> GetAllUsersAsync(AppUserIncludes includeFlags = AppUserIncludes.None, bool track = true, CancellationToken ct = default)
    {
        var query = context.AppUser.Includes(includeFlags);

        if (track)
        {
            return await query.ToListAsync(ct);
        }

        return await query
            .AsNoTracking()
            .ToListAsync(ct);
    }

    public async Task<AppUser?> GetUserByConfirmationToken(string token, CancellationToken ct = default)
    {
        return await context.AppUser
            .SingleOrDefaultAsync(u => u.ConfirmationToken != null && u.ConfirmationToken.Equals(token), ct);
    }

    /// <summary>
    /// Returns the first admin account created
    /// </summary>
    /// <returns></returns>
    public async Task<AppUser> GetDefaultAdminUser(AppUserIncludes includes = AppUserIncludes.None, CancellationToken ct = default)
    {
        return await context.AppUser
            .Includes(includes)
            .Where(u => u.UserRoles.Any(r => r.Role.Name == PolicyConstants.AdminRole))
            .OrderBy(u => u.Created)
            .FirstAsync(ct);
    }

    public async Task<IEnumerable<AppUserRating>> GetSeriesWithRatings(int userId, CancellationToken ct = default)
    {
        return await context.AppUserRating
            .Where(u => u.AppUserId == userId && u.Rating > 0)
            .Include(u => u.Series)
            .AsSplitQuery()
            .ToListAsync(ct);
    }

    public async Task<IEnumerable<AppUserRating>> GetSeriesWithReviews(int userId, CancellationToken ct = default)
    {
        return await context.AppUserRating
            .Where(u => u.AppUserId == userId && !string.IsNullOrEmpty(u.Review))
            .Include(u => u.Series)
            .AsSplitQuery()
            .ToListAsync(ct);
    }

    public async Task<bool> HasHoldOnSeries(int userId, int seriesId, CancellationToken ct = default)
    {
        return await context.AppUser
            .AsSplitQuery()
            .AnyAsync(u => u.ScrobbleHolds.Select(s => s.SeriesId).Contains(seriesId) && u.Id == userId, ct);
    }

    public async Task<IList<ScrobbleHoldDto>> GetHolds(int userId, CancellationToken ct = default)
    {
        return await context.ScrobbleHold
            .Where(s => s.AppUserId == userId)
            .ProjectTo<ScrobbleHoldDto>(mapper.ConfigurationProvider)
            .ToListAsync(ct);
    }

    public async Task<string> GetLocale(int userId, CancellationToken ct = default)
    {
        return await context.AppUserPreferences.Where(p => p.AppUserId == userId)
            .Select(p => p.Locale)
            .SingleAsync(ct);
    }

    public async Task<IList<DashboardStreamDto>> GetDashboardStreams(int userId, bool visibleOnly = false, CancellationToken ct = default)
    {
        return await context.AppUserDashboardStream
            .Where(d => d.AppUserId == userId)
            .WhereIf(visibleOnly, d => d.Visible)
            .OrderBy(d => d.Order)
            .Include(d => d.SmartFilter)
            .Select(d => new DashboardStreamDto()
            {
                Id = d.Id,
                Name = d.Name,
                IsProvided = d.IsProvided,
                SmartFilterId = d.SmartFilter == null ? 0 : d.SmartFilter.Id,
                SmartFilterEncoded = d.SmartFilter == null ? null : d.SmartFilter.Filter,
                StreamType = d.StreamType,
                Order = d.Order,
                Visible = d.Visible
            })
            .ToListAsync(ct);
    }

    public async Task<IList<AppUserDashboardStream>> GetAllDashboardStreams(CancellationToken ct = default)
    {
        return await context.AppUserDashboardStream
            .OrderBy(d => d.Order)
            .ToListAsync(ct);
    }

    public async Task<AppUserDashboardStream?> GetDashboardStream(int streamId, CancellationToken ct = default)
    {
        return await context.AppUserDashboardStream
            .Include(d => d.SmartFilter)
            .FirstOrDefaultAsync(d => d.Id == streamId, ct);
    }


    public async Task<IList<AppUserDashboardStream>> GetDashboardStreamWithFilter(int filterId, CancellationToken ct = default)
    {
        return await context.AppUserDashboardStream
            .Include(d => d.SmartFilter)
            .Where(d => d.SmartFilter != null && d.SmartFilter.Id == filterId)
            .AsSplitQuery()
            .ToListAsync(ct);
    }

    public async Task<IList<SideNavStreamDto>> GetSideNavStreams(int userId, bool visibleOnly = false, CancellationToken ct = default)
    {
        var sideNavStreams = await context.AppUserSideNavStream
            .Where(d => d.AppUserId == userId)
            .WhereIf(visibleOnly, d => d.Visible)
            .OrderBy(d => d.Order)
            .Include(d => d.SmartFilter)
            .Select(d => new SideNavStreamDto()
            {
                Id = d.Id,
                Name = d.Name,
                IsProvided = d.IsProvided,
                SmartFilterId = d.SmartFilter == null ? 0 : d.SmartFilter.Id,
                SmartFilterEncoded = d.SmartFilter == null ? null : d.SmartFilter.Filter,
                LibraryId = d.LibraryId ?? 0,
                ExternalSourceId = d.ExternalSourceId ?? 0,
                StreamType = d.StreamType,
                Order = d.Order,
                Visible = d.Visible
            })
            .AsSplitQuery()
            .ToListAsync(ct);

        var libraryIds = sideNavStreams.Where(d => d.StreamType == SideNavStreamType.Library)
            .Select(d => d.LibraryId)
            .ToList();

        var libraryDtos = await context.Library
            .Where(l => libraryIds.Contains(l.Id))
            .ProjectTo<LibraryDto>(mapper.ConfigurationProvider)
            .ToListAsync(ct);

        foreach (var dto in sideNavStreams.Where(dto => dto.StreamType == SideNavStreamType.Library))
        {
            dto.Library = libraryDtos.FirstOrDefault(l => l.Id == dto.LibraryId);
        }

        var externalSourceIds = sideNavStreams.Where(d => d.StreamType == SideNavStreamType.ExternalSource)
            .Select(d => d.ExternalSourceId)
            .ToList();

        var externalSourceDtos = context.AppUserExternalSource
            .Where(l => externalSourceIds.Contains(l.Id))
            .ProjectTo<ExternalSourceDto>(mapper.ConfigurationProvider)
            .ToList();

        foreach (var dto in sideNavStreams.Where(dto => dto.StreamType == SideNavStreamType.ExternalSource))
        {
            dto.ExternalSource = externalSourceDtos.FirstOrDefault(l => l.Id == dto.ExternalSourceId);
        }

        return sideNavStreams;
    }

    public async Task<AppUserSideNavStream?> GetSideNavStream(int streamId, CancellationToken ct = default)
    {
        return await context.AppUserSideNavStream
            .Include(d => d.SmartFilter)
            .FirstOrDefaultAsync(d => d.Id == streamId, ct);
    }

    public async Task<AppUserSideNavStream?> GetSideNavStreamWithUser(int streamId, CancellationToken ct = default)
    {
        return await context.AppUserSideNavStream
            .Include(d => d.SmartFilter)
            .Include(d => d.AppUser)
            .FirstOrDefaultAsync(d => d.Id == streamId, ct);
    }

    public async Task<IList<AppUserSideNavStream>> GetSideNavStreamWithFilter(int filterId, CancellationToken ct = default)
    {
        return await context.AppUserSideNavStream
            .Include(d => d.SmartFilter)
            .Where(d => d.SmartFilter != null && d.SmartFilter.Id == filterId)
            .ToListAsync(ct);
    }

    public async Task<IList<AppUserSideNavStream>> GetSideNavStreamsByLibraryId(int libraryId, CancellationToken ct = default)
    {
        return await context.AppUserSideNavStream
            .Where(d => d.LibraryId == libraryId)
            .ToListAsync(ct);
    }

    public async Task<IList<AppUserSideNavStream>> GetSideNavStreamWithExternalSource(int externalSourceId, CancellationToken ct = default)
    {
        return await context.AppUserSideNavStream
            .Where(d => d.ExternalSourceId == externalSourceId)
            .ToListAsync(ct);
    }

    public async Task<IList<AppUserSideNavStream>> GetDashboardStreamsByIds(IList<int> streamIds, CancellationToken ct = default)
    {
        return await context.AppUserSideNavStream
            .Where(d => streamIds.Contains(d.Id))
            .ToListAsync(ct);
    }

    public async Task<IEnumerable<UserTokenInfo>> GetUserTokenInfo(CancellationToken ct = default)
    {
        var users = await context.AppUser
            .Select(u => new
            {
                u.Id,
                u.UserName,
                u.AniListAccessToken, // JWT Token
                u.MalAccessToken // JWT Token
            })
            .ToListAsync(ct);

        var userTokenInfos = users.Select(user => new UserTokenInfo
        {
            UserId = user.Id,
            Username = user.UserName,
            IsAniListTokenSet = !string.IsNullOrEmpty(user.AniListAccessToken),
            AniListValidUntilUtc = JwtHelper.GetTokenExpiry(user.AniListAccessToken),
            IsAniListTokenValid = JwtHelper.IsTokenValid(user.AniListAccessToken),
            IsMalTokenSet = !string.IsNullOrEmpty(user.MalAccessToken),
        });

        return userTokenInfos;
    }

    /// <summary>
    /// Returns the first user with a device email matching
    /// </summary>
    /// <param name="deviceEmail"></param>
    /// <param name="ct"></param>
    /// <returns></returns>
    public async Task<AppUser?> GetUserByDeviceEmail(string deviceEmail, CancellationToken ct = default)
    {
        return await context.AppUser
            .Where(u => u.Devices.Any(d => d.EmailAddress == deviceEmail))
            .FirstOrDefaultAsync(ct);
    }

    /// <summary>
    /// Returns a list of annotations ordered by page number.
    /// </summary>
    /// <param name="userId"></param>
    /// <param name="chapterId"></param>
    /// <param name="ct"></param>
    /// <returns></returns>
    public async Task<List<AnnotationDto>> GetAnnotations(int userId, int chapterId, CancellationToken ct = default)
    {
        var userPreferences = await context.AppUserPreferences.ToListAsync(ct);

        return await context.AppUserAnnotation
            .Where(a => a.ChapterId == chapterId)
            .RestrictBySocialPreferences(userId, userPreferences)
            .OrderBy(a => a.PageNumber)
            .ProjectTo<AnnotationDto>(mapper.ConfigurationProvider)
            .ToListAsync(ct);
    }

    public async Task<List<AnnotationDto>> GetAnnotationsByPage(int userId, int chapterId, int pageNum, CancellationToken ct = default)
    {
        var userPreferences = await context.AppUserPreferences.ToListAsync(ct);

        return await context.AppUserAnnotation
            .Where(a => a.ChapterId == chapterId && a.PageNumber == pageNum)
            .RestrictBySocialPreferences(userId, userPreferences)
            .OrderBy(a => a.PageNumber)
            .ProjectTo<AnnotationDto>(mapper.ConfigurationProvider)
            .ToListAsync(ct);
    }

    public async Task<AppUser?> GetByOidcId(string? oidcId, AppUserIncludes includes = AppUserIncludes.None, CancellationToken ct = default)
    {
        if (string.IsNullOrEmpty(oidcId)) return null;

        return await context.AppUser
            .Where(u => u.OidcId == oidcId)
            .Includes(includes)
            .FirstOrDefaultAsync(ct);
    }

    public async Task<AnnotationDto?> GetAnnotationDtoById(int userId, int annotationId, CancellationToken ct = default)
    {
        var userPreferences = await context.AppUserPreferences.ToListAsync(ct);

        return await context.AppUserAnnotation
            .Where(a => a.Id == annotationId)
            .RestrictBySocialPreferences(userId, userPreferences)
            .ProjectTo<AnnotationDto>(mapper.ConfigurationProvider)
            .FirstOrDefaultAsync(ct);
    }

    public async Task<List<AnnotationDto>> GetAnnotationDtosBySeries(int userId, int seriesId, CancellationToken ct = default)
    {
        var userPreferences = await context.AppUserPreferences.ToListAsync(ct);

        return await context.AppUserAnnotation
            .Where(a => a.SeriesId == seriesId)
            .RestrictBySocialPreferences(userId, userPreferences)
            .ProjectTo<AnnotationDto>(mapper.ConfigurationProvider)
            .ToListAsync(ct);
    }

    public async Task UpdateUserAsActive(int userId, CancellationToken ct = default)
    {
        await context.Set<AppUser>()
            .Where(u => u.Id == userId)
            .ExecuteUpdateAsync(setters => setters
                .SetProperty(u => u.LastActiveUtc, DateTime.UtcNow)
                .SetProperty(u => u.LastActive, DateTime.Now), ct);
    }

    /// <summary>
    /// Retrieve all reviews (series and chapter) for a given user, respecting profile privacy settings and age restrictions.
    /// </summary>
    /// <param name="userId">UserId of source user</param>
    /// <param name="requestingUserId">Viewer UserId</param>
    /// <param name="query">Search text to match against Series name</param>
    /// <param name="ratingFilter">Rating, only applies to series/chapters rated. Will show everything greater or equal to</param>
    /// <param name="ct"></param>
    /// <returns></returns>
    public async Task<IList<UserReviewExtendedDto>> GetAllReviewsForUser(int userId, int requestingUserId, string? query = null, float? ratingFilter = null, CancellationToken ct = default)
    {
        var bypassPreferences = userId == requestingUserId;
        if (!bypassPreferences)
        {
            var userPreferences = await context.AppUserPreferences
                .FirstOrDefaultAsync(u => u.AppUserId == userId, ct);

            if (userPreferences?.SocialPreferences?.ShareReviews == false)
            {
                return Array.Empty<UserReviewExtendedDto>();
            }
        }

        var userRating = await context.AppUser.GetUserAgeRestriction(requestingUserId);

        // Get series-level reviews
        var seriesReviews = await context.AppUserRating
            .WhereIf(ratingFilter is > 0, r => r.HasBeenRated && r.Rating >= ratingFilter!.Value)
            .Include(r => r.AppUser)
            .Include(r => r.Series)
            .ThenInclude(s => s.Metadata)
            .ThenInclude(sm => sm.People)
            .ThenInclude(smp => smp.Person)
            .Where(r => r.AppUserId == userId && !string.IsNullOrEmpty(r.Review))
            .WhereIf(!string.IsNullOrWhiteSpace(query), r => EF.Functions.Like(r.Series.Name, "%"+query+"%"))
            .RestrictAgainstAgeRestriction(userRating, requestingUserId)
            .OrderBy(r => r.SeriesId)
            .AsSplitQuery()
            .ProjectTo<UserReviewExtendedDto>(mapper.ConfigurationProvider)
            .ToListAsync(ct);

        // Get chapter-level reviews
        var chapterReviews = await context.AppUserChapterRating
            .Include(r => r.AppUser)
            .Include(r => r.Series)
            .Include(r => r.Chapter)
            .ThenInclude(c => c.People)
            .Where(r => r.AppUserId == userId && !string.IsNullOrEmpty(r.Review))
            .RestrictAgainstAgeRestriction(userRating, requestingUserId)
            .OrderBy(r => r.SeriesId)
            .ThenBy(r => r.ChapterId)
            .AsSplitQuery()
            .ProjectTo<UserReviewExtendedDto>(mapper.ConfigurationProvider)
            .ToListAsync(ct);

        // Combine and return both lists
        return seriesReviews.Concat(chapterReviews).ToList();
    }


    public async Task<IEnumerable<AppUser>> GetAdminUsersAsync(CancellationToken ct = default)
    {
        return (await userManager.GetUsersInRoleAsync(PolicyConstants.AdminRole)).OrderBy(u => u.CreatedUtc);
    }

    public async Task<bool> IsUserAdminAsync(AppUser? user, CancellationToken ct = default)
    {
        if (user == null) return false;
        return await userManager.IsInRoleAsync(user, PolicyConstants.AdminRole);
    }

    public async Task<IList<string>> GetRoles(int userId, CancellationToken ct = default)
    {
        var user = await context.Users.FirstOrDefaultAsync(u => u.Id == userId, ct);
        if (user == null) return ArraySegment<string>.Empty;

        // ReSharper disable once ConditionIsAlwaysTrueOrFalseAccordingToNullableAPIContract
        if (userManager == null)
        {
            // userManager is null on Unit Tests only
            return await context.UserRoles
                .Where(ur => ur.UserId == userId)
                .Select(ur => ur.Role.Name)
                .ToListAsync(ct);
        }

        return await userManager.GetRolesAsync(user);
    }

    public async Task<IList<string>> GetRolesByAuthKey(string? apiKey, CancellationToken ct = default)
    {
        if (string.IsNullOrEmpty(apiKey)) return ArraySegment<string>.Empty;

        var user = await context.AppUserAuthKey
            .Where(k => k.Key == apiKey)
            .HasNotExpired()
            .Select(k => k.AppUser)
            .FirstOrDefaultAsync(ct);
        if (user == null) return ArraySegment<string>.Empty;

        // ReSharper disable once ConditionIsAlwaysTrueOrFalseAccordingToNullableAPIContract
        if (userManager == null)
        {
            // userManager is null on Unit Tests only
            return await context.UserRoles
                .Where(ur => ur.User.AuthKeys.Any(k => k.Key == apiKey && (k.ExpiresAtUtc == null || k.ExpiresAtUtc < DateTime.UtcNow)))
                .Select(ur => ur.Role.Name)
                .ToListAsync(ct);
        }

        return await userManager.GetRolesAsync(user);
    }

    public async Task<AppUserRating?> GetUserRatingAsync(int seriesId, int userId, CancellationToken ct = default)
    {
        return await context.AppUserRating
            .Where(r => r.SeriesId == seriesId && r.AppUserId == userId)
            .FirstOrDefaultAsync(ct);
    }

    public async Task<AppUserChapterRating?> GetUserChapterRatingAsync(int userId, int chapterId, CancellationToken ct = default)
    {
        return await context.AppUserChapterRating
            .Where(r => r.AppUserId == userId && r.ChapterId == chapterId)
            .FirstOrDefaultAsync(ct);
    }

    public async Task<IList<UserReviewDto>> GetUserRatingDtosForSeriesAsync(int seriesId, int userId, CancellationToken ct = default)
    {
        var userPreferences = await context.AppUserPreferences.ToListAsync(ct);

        return await context.AppUserRating
            .Include(r => r.AppUser)
            .Where(r => r.SeriesId == seriesId)
            .RestrictBySocialPreferences(userId, userPreferences)
            .OrderBy(r => r.AppUserId == userId)
            .ThenBy(r => r.Rating)
            .AsSplitQuery()
            .ProjectTo<UserReviewDto>(mapper.ConfigurationProvider)
            .ToListAsync(ct);
    }

    public async Task<IList<UserReviewDto>> GetUserRatingDtosForChapterAsync(int chapterId, int userId, CancellationToken ct = default)
    {
        var userPreferences = await context.AppUserPreferences.ToListAsync(ct);

        return await context.AppUserChapterRating
            .Include(r => r.AppUser)
            .Where(r => r.ChapterId == chapterId)
            .RestrictBySocialPreferences(userId, userPreferences)
            .OrderBy(r => r.AppUserId == userId)
            .ThenBy(r => r.Rating)
            .AsSplitQuery()
            .ProjectTo<UserReviewDto>(mapper.ConfigurationProvider)
            .ToListAsync(ct);
    }

    public async Task<AppUserPreferences?> GetPreferencesAsync(string username, CancellationToken ct = default)
    {
        return await context.AppUserPreferences
            .Include(p => p.AppUser)
            .Include(p => p.Theme)
            .AsSplitQuery()
            .SingleOrDefaultAsync(p => p.AppUser.UserName == username, ct);
    }

    public async Task<IEnumerable<BookmarkDto>> GetBookmarkDtosForSeries(int userId, int seriesId, CancellationToken ct = default)
    {
        return await context.AppUserBookmark
            .Where(x => x.AppUserId == userId && x.SeriesId == seriesId)
            .OrderBy(x => x.Created)
            .AsNoTracking()
            .ProjectTo<BookmarkDto>(mapper.ConfigurationProvider)
            .ToListAsync(ct);
    }

    public async Task<IEnumerable<BookmarkDto>> GetBookmarkDtosForVolume(int userId, int volumeId, CancellationToken ct = default)
    {
        return await context.AppUserBookmark
            .Where(x => x.AppUserId == userId && x.VolumeId == volumeId)
            .OrderBy(x => x.Created)
            .AsNoTracking()
            .ProjectTo<BookmarkDto>(mapper.ConfigurationProvider)
            .ToListAsync(ct);
    }

    public async Task<IEnumerable<BookmarkDto>> GetBookmarkDtosForChapter(int userId, int chapterId, CancellationToken ct = default)
    {
        return await context.AppUserBookmark
            .Where(x => x.AppUserId == userId && x.ChapterId == chapterId)
            .OrderBy(x => x.Created)
            .AsNoTracking()
            .ProjectTo<BookmarkDto>(mapper.ConfigurationProvider)
            .ToListAsync(ct);
    }

    /// <summary>
    /// Get all bookmarks for the user
    /// </summary>
    /// <param name="userId"></param>
    /// <param name="filter">Only supports SeriesNameQuery</param>
    /// <param name="ct"></param>
    /// <returns></returns>
    public async Task<IEnumerable<BookmarkDto>> GetAllBookmarkDtos(int userId, FilterV2Dto filter, CancellationToken ct = default)
    {
        var query = context.AppUserBookmark
            .Where(x => x.AppUserId == userId)
            .OrderBy(x => x.Created)
            .AsNoTracking();

        var filterSeriesQuery = query.Join(context.Series, b => b.SeriesId, s => s.Id,
            (bookmark, series) => new BookmarkSeriesPair
            {
                Bookmark = bookmark,
                Series = series
            });

        var filterStatement = filter.Statements.FirstOrDefault(f => f.Field == FilterField.SeriesName);
        if (filterStatement == null || string.IsNullOrWhiteSpace(filterStatement.Value))
        {
            return await ApplyLimit(filterSeriesQuery
                    .Sort(filter.SortOptions)
                    .AsSplitQuery(), filter.LimitTo)
                .ProjectTo<BookmarkDto>(mapper.ConfigurationProvider)
                .ToListAsync(ct);
        }

        var queryString = filterStatement.Value.ToNormalized();
        switch (filterStatement.Comparison)
        {
            case FilterComparison.Equal:
                filterSeriesQuery = filterSeriesQuery.Where(s => s.Series.Name.Equals(queryString)
                                                               || s.Series.OriginalName.Equals(queryString)
                                                               || s.Series.LocalizedName.Equals(queryString)
                                                               || s.Series.SortName.Equals(queryString));
                break;
            case FilterComparison.BeginsWith:
                filterSeriesQuery = filterSeriesQuery.Where(s => EF.Functions.Like(s.Series.Name, $"{queryString}%")
                                                                 ||EF.Functions.Like(s.Series.OriginalName, $"{queryString}%")
                                                                 || EF.Functions.Like(s.Series.LocalizedName, $"{queryString}%")
                                                                 || EF.Functions.Like(s.Series.SortName, $"{queryString}%"));
                break;
            case FilterComparison.EndsWith:
                filterSeriesQuery = filterSeriesQuery.Where(s => EF.Functions.Like(s.Series.Name, $"%{queryString}")
                                                                 ||EF.Functions.Like(s.Series.OriginalName, $"%{queryString}")
                                                                 || EF.Functions.Like(s.Series.LocalizedName, $"%{queryString}")
                                                                 || EF.Functions.Like(s.Series.SortName, $"%{queryString}"));
                break;
            case FilterComparison.Matches:
                filterSeriesQuery = filterSeriesQuery.Where(s => EF.Functions.Like(s.Series.Name, $"%{queryString}%")
                                                                 ||EF.Functions.Like(s.Series.OriginalName, $"%{queryString}%")
                                                                 || EF.Functions.Like(s.Series.LocalizedName, $"%{queryString}%")
                                                                 || EF.Functions.Like(s.Series.SortName, $"%{queryString}%"));
                break;
            case FilterComparison.NotEqual:
                filterSeriesQuery = filterSeriesQuery.Where(s => s.Series.Name != queryString
                                                                 || s.Series.OriginalName != queryString
                                                                 || s.Series.LocalizedName != queryString
                                                                 || s.Series.SortName != queryString);
                break;
            case FilterComparison.MustContains:
            case FilterComparison.NotContains:
            case FilterComparison.GreaterThan:
            case FilterComparison.GreaterThanEqual:
            case FilterComparison.LessThan:
            case FilterComparison.LessThanEqual:
            case FilterComparison.Contains:
            case FilterComparison.IsBefore:
            case FilterComparison.IsAfter:
            case FilterComparison.IsInLast:
            case FilterComparison.IsNotInLast:
            default:
                break;
        }

        return await ApplyLimit(filterSeriesQuery
                .Sort(filter.SortOptions)
                .AsSplitQuery(), filter.LimitTo)
            .ProjectTo<BookmarkDto>(mapper.ConfigurationProvider)
            .ToListAsync(ct);
    }

    private static IQueryable<BookmarkSeriesPair> ApplyLimit(IQueryable<BookmarkSeriesPair> query, int limit)
    {
        return limit <= 0 ? query : query.Take(limit);
    }

    public async Task<UserDto?> GetUserDtoByAuthKeyAsync(string authKey, CancellationToken ct = default)
    {
        if (string.IsNullOrEmpty(authKey)) return null;

        return await context.AppUserAuthKey
            .Where(k => k.Key == authKey)
            .HasNotExpired()
            .Include(k => k.AppUser)
                .ThenInclude(u => u.UserRoles)
                    .ThenInclude(ur => ur.Role)
            .Select(k => k.AppUser)
            .ProjectTo<UserDto>(mapper.ConfigurationProvider)
            .FirstOrDefaultAsync(ct);
    }

    public async Task<int> GetUserIdByAuthKeyAsync(string authKey, CancellationToken ct = default)
    {
        if (string.IsNullOrEmpty(authKey)) return 0;

        return await context.AppUserAuthKey
            .Where(k => k.Key == authKey)
            .HasNotExpired()
            .Select(k => k.AppUserId)
            .FirstOrDefaultAsync(ct);
    }

    public async Task<UserDto?> GetUserDtoById(int userId, CancellationToken ct = default)
    {
        return await context.AppUser
            .Where(u => u.Id == userId)
            .ProjectTo<UserDto>(mapper.ConfigurationProvider)
            .FirstOrDefaultAsync(ct);
    }


    public async Task<IEnumerable<MemberDto>> GetEmailConfirmedMemberDtosAsync(bool emailConfirmed = true, CancellationToken ct = default)
    {
        return await context.Users
            .Where(u => (emailConfirmed && u.EmailConfirmed) || !emailConfirmed)
            .Include(x => x.Libraries)
            .Include(r => r.UserRoles)
            .ThenInclude(r => r.Role)
            .OrderBy(u => u.UserName)
            .Select(u => new MemberDto
            {
                Id = u.Id,
                Username = u.UserName,
                Email = u.Email,
                Created = u.Created,
                CreatedUtc = u.CreatedUtc,
                LastActive = u.LastActive,
                LastActiveUtc = u.LastActiveUtc,
                Roles = u.UserRoles.Select(r => r.Role.Name).ToList(),
                IsPending = !u.EmailConfirmed,
                IdentityProvider = u.IdentityProvider,
                AgeRestriction = new AgeRestrictionDto()
                {
                    AgeRating = u.AgeRestriction,
                    IncludeUnknowns = u.AgeRestrictionIncludeUnknowns
                },
                Libraries =  u.Libraries.Select(l => new LibraryDto
                {
                    Id = l.Id,
                    Name = l.Name,
                    Type = l.Type,
                    LastScanned = l.LastScanned,
                    Folders = l.Folders.Select(x => x.Path).ToList()
                }).ToList(),
            })
            .AsSplitQuery()
            .AsNoTracking()
            .ToListAsync(ct);
    }

    public Task<string?> GetCoverImageAsync(int userId, CancellationToken ct = default)
    {
        return context.AppUser
            .Where(u => u.Id == userId)
            .Select(u => u.CoverImage)
            .FirstOrDefaultAsync(ct);
    }

    public async Task<string?> GetPersonCoverImageAsync(int personId, CancellationToken ct = default)
    {
        return await context.Person
            .Where(p => p.Id == personId)
            .Select(p => p.CoverImage)
            .FirstOrDefaultAsync(ct);
    }

    public async Task<IList<AuthKeyDto>> GetAuthKeysForUserId(int userId, CancellationToken ct = default)
    {
        return await context.AppUserAuthKey
            .Where(k => k.AppUserId == userId)
            .ProjectTo<AuthKeyDto>(mapper.ConfigurationProvider)
            .ToListAsync(ct);
    }

    public async Task<IList<AuthKeyDto>> GetAllAuthKeysDtosWithExpiration(CancellationToken ct = default)
    {
        return await context.AppUserAuthKey
            .Where(k => k.ExpiresAtUtc != null)
            .ProjectTo<AuthKeyDto>(mapper.ConfigurationProvider)
            .ToListAsync(ct);
    }

    public async Task<AppUserAuthKey?> GetAuthKeyById(int authKeyId, CancellationToken ct = default)
    {
        return await context.AppUserAuthKey
            .Where(k => k.Id == authKeyId)
            .FirstOrDefaultAsync(ct);
    }

    public async Task<DateTime?> GetAuthKeyExpiration(string authKey, int userId, CancellationToken ct = default)
    {
        return await context.AppUserAuthKey
            .Where(k => k.Key == authKey && k.AppUserId == userId)
            .Select(k => k.ExpiresAtUtc)
            .FirstOrDefaultAsync(ct);
    }

    public async Task<AppUserSocialPreferences> GetSocialPreferencesForUser(int userId, CancellationToken ct = default)
    {
        return await context.AppUserPreferences
            .Where(p => p.AppUserId == userId)
            .Select(p => p.SocialPreferences)
            .FirstAsync(ct);
    }

    public async Task<AppUserPreferences> GetPreferencesForUser(int userId, CancellationToken ct = default)
    {
        return await context.AppUserPreferences
            .Where(p => p.AppUserId == userId)
            .FirstAsync(ct);
    }

    /// <summary>
    /// No Tracking
    /// </summary>
    /// <param name="userId"></param>
    /// <returns></returns>
    public async Task<AppUserOpdsPreferences> GetOpdsPreferences(int userId, CancellationToken ct = default)
    {
        return await context.AppUserPreferences
            .Where(p => p.AppUserId == userId)
            .Select(p => p.OpdsPreferences)
            .AsNoTracking()
            .FirstAsync(ct);
    }
}