pgsql-jellyfin/Jellyfin.Server.Implementations/Item/PeopleRepository.cs

// <copyright file="PeopleRepository.cs" company="PlaceholderCompany">
// Copyright (c) PlaceholderCompany. All rights reserved.
// </copyright>

namespace Jellyfin.Server.Implementations.Item;

using System;
using System.Collections.Generic;
using System.Collections.Immutable;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using Jellyfin.Data.Enums;
using Jellyfin.Database.Implementations;
using Jellyfin.Database.Implementations.Entities;
using Jellyfin.Database.Implementations.Entities.Libraries;
using Jellyfin.Extensions;
using MediaBrowser.Controller.Entities;
using MediaBrowser.Controller.Persistence;
using Microsoft.EntityFrameworkCore;

#pragma warning disable RS0030 // Do not use banned APIs
#pragma warning disable CA1304 // Specify CultureInfo
#pragma warning disable CA1311 // Specify a culture or use an invariant version
#pragma warning disable CA1862 // Use the 'StringComparison' method overloads to perform case-insensitive string comparisons

/// <summary>
/// Manager for handling people.
/// </summary>
/// <param name="dbProvider">Efcore Factory.</param>
/// <param name="itemTypeLookup">Items lookup service.</param>
/// <remarks>
/// Initializes a new instance of the <see cref="PeopleRepository"/> class.
/// </remarks>
public class PeopleRepository(IDbContextFactory<JellyfinDbContext> dbProvider, IItemTypeLookup itemTypeLookup) : IPeopleRepository
{
    private readonly IDbContextFactory<JellyfinDbContext> _dbProvider = dbProvider;

    /// <inheritdoc/>
    public IReadOnlyList<PersonInfo> GetPeople(InternalPeopleQuery filter)
    {
        return GetPeopleAsync(filter, CancellationToken.None)
            .GetAwaiter()
            .GetResult();
    }

    /// <inheritdoc/>
    public async Task<IReadOnlyList<PersonInfo>> GetPeopleAsync(InternalPeopleQuery filter, CancellationToken cancellationToken = default)
    {
        await using var context = _dbProvider.CreateDbContext();
        var dbQuery = TranslateQuery(context.Peoples.AsNoTracking(), context, filter);

        // Include PeopleBaseItemMap
        if (!filter.ItemId.IsEmpty())
        {
            dbQuery = dbQuery.Include(p => p.BaseItems!.Where(m => m.ItemId == filter.ItemId))
                .OrderBy(e => e.BaseItems!.First(e => e.ItemId == filter.ItemId).ListOrder)
                .ThenBy(e => e.PersonType)
                .ThenBy(e => e.Name);
        }
        else
        {
            dbQuery = dbQuery.OrderBy(e => e.Name);
        }

        if (filter.Limit > 0)
        {
            dbQuery = dbQuery.Take(filter.Limit);
        }

        var results = await dbQuery
            .ToArrayAsync(cancellationToken)
            .ConfigureAwait(false);

        return results.Select(Map).ToArray();
    }

    /// <inheritdoc/>
    public IReadOnlyList<string> GetPeopleNames(InternalPeopleQuery filter)
    {
        return GetPeopleNamesAsync(filter, CancellationToken.None)
            .GetAwaiter()
            .GetResult();
    }

    /// <inheritdoc/>
    public async Task<IReadOnlyList<string>> GetPeopleNamesAsync(InternalPeopleQuery filter, CancellationToken cancellationToken = default)
    {
        await using var context = _dbProvider.CreateDbContext();
        var dbQuery = TranslateQuery(context.Peoples.AsNoTracking(), context, filter)
            .Select(e => e.Name)
            .Distinct();

        if (filter.Limit > 0)
        {
            dbQuery = dbQuery.Take(filter.Limit);
        }

        return await dbQuery
            .ToArrayAsync(cancellationToken)
            .ConfigureAwait(false);
    }

    /// <inheritdoc />
    public void UpdatePeople(Guid itemId, IReadOnlyList<PersonInfo> people)
    {
        UpdatePeopleAsync(itemId, people, CancellationToken.None)
            .GetAwaiter()
            .GetResult();
    }

    /// <inheritdoc />
    public async Task UpdatePeopleAsync(Guid itemId, IReadOnlyList<PersonInfo> people, CancellationToken cancellationToken = default)
    {
        foreach (var person in people)
        {
            person.Name = person.Name.Trim();
            person.Role = person.Role?.Trim() ?? string.Empty;
        }

        // multiple metadata providers can provide the _same_ person
        people = people.DistinctBy(e => e.Name + "-" + e.Type).ToArray();
        var personKeys = people.Select(e => e.Name + "-" + e.Type).ToArray();

        await using var context = _dbProvider.CreateDbContext();

        // Use the execution strategy to handle transactional consistency and retries
        // This is required for PostgreSQL NpgsqlRetryingExecutionStrategy
        var executionStrategy = context.Database.CreateExecutionStrategy();
        await executionStrategy.ExecuteAsync(async () =>
        {
            var existingPersons = await context.Peoples
                .Select(e => new
                {
                    item = e,
                    SelectionKey = e.Name + "-" + e.PersonType
                })
                .Where(p => personKeys.Contains(p.SelectionKey))
                .Select(f => f.item)
                .ToArrayAsync(cancellationToken)
                .ConfigureAwait(false);

            var toAdd = people
                .Where(e => e.Type is not PersonKind.Artist && e.Type is not PersonKind.AlbumArtist)
                .Where(e => !existingPersons.Any(f => f.Name == e.Name && f.PersonType == e.Type.ToString()))
                .Select(Map);

            await context.Peoples.AddRangeAsync(toAdd, cancellationToken).ConfigureAwait(false);
            await context.SaveChangesAsync(cancellationToken).ConfigureAwait(false);

            var personsEntities = toAdd.Concat(existingPersons).ToArray();

            var existingMaps = await context.PeopleBaseItemMap
                .Include(e => e.People)
                .Where(e => e.ItemId == itemId)
                .ToListAsync(cancellationToken)
                .ConfigureAwait(false);

            var listOrder = 0;

            foreach (var person in people)
            {
                if (person.Type == PersonKind.Artist || person.Type == PersonKind.AlbumArtist)
                {
                    continue;
                }

                var entityPerson = personsEntities.First(e => e.Name == person.Name && e.PersonType == person.Type.ToString());
                var existingMap = existingMaps.FirstOrDefault(e => e.People.Name == person.Name && e.People.PersonType == person.Type.ToString() && e.Role == person.Role);
                if (existingMap is null)
                {
                    await context.PeopleBaseItemMap.AddAsync(
                        new PeopleBaseItemMap()
                        {
                            Item = null!,
                            ItemId = itemId,
                            People = null!,
                            PeopleId = entityPerson.Id,
                            ListOrder = listOrder,
                            SortOrder = person.SortOrder,
                            Role = person.Role
                        },
                        cancellationToken).ConfigureAwait(false);
                }
                else
                {
                    // Update the order for existing mappings
                    existingMap.ListOrder = listOrder;
                    existingMap.SortOrder = person.SortOrder;
                    // person mapping already exists so remove from list
                    existingMaps.Remove(existingMap);
                }

                listOrder++;
            }

            context.PeopleBaseItemMap.RemoveRange(existingMaps);

            await context.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
        }).ConfigureAwait(false);
    }

    private PersonInfo Map(People people)
    {
        var mapping = people.BaseItems?.FirstOrDefault();
        var personInfo = new PersonInfo()
        {
            Id = people.Id,
            Name = people.Name,
            Role = mapping?.Role,
            SortOrder = mapping?.SortOrder
        };
        if (Enum.TryParse<PersonKind>(people.PersonType, out var kind))
        {
            personInfo.Type = kind;
        }

        return personInfo;
    }

    private People Map(PersonInfo people)
    {
        var personInfo = new People()
        {
            Name = people.Name,
            PersonType = people.Type.ToString(),
            Id = people.Id,
        };

        return personInfo;
    }

    private IQueryable<People> TranslateQuery(IQueryable<People> query, JellyfinDbContext context, InternalPeopleQuery filter)
    {
        if (filter.User is not null && filter.IsFavorite.HasValue)
        {
            var personType = itemTypeLookup.BaseItemKindNames[BaseItemKind.Person];
            var oldQuery = query;

            query = context.UserData
                .Where(u => u.Item!.Type == personType && u.IsFavorite == filter.IsFavorite && u.UserId.Equals(filter.User.Id))
                .Join(oldQuery, e => e.Item!.Name, e => e.Name, (item, person) => person)
                .Distinct()
                .AsNoTracking();
        }

        if (!filter.ItemId.IsEmpty())
        {
            query = query.Where(e => e.BaseItems!.Any(w => w.ItemId.Equals(filter.ItemId)));
        }

        if (!filter.AppearsInItemId.IsEmpty())
        {
            query = query.Where(e => e.BaseItems!.Any(w => w.ItemId.Equals(filter.AppearsInItemId)));
        }

        var queryPersonTypes = filter.PersonTypes.Where(IsValidPersonType).ToList();
        if (queryPersonTypes.Count > 0)
        {
            query = query.Where(e => queryPersonTypes.Contains(e.PersonType));
        }

        var queryExcludePersonTypes = filter.ExcludePersonTypes.Where(IsValidPersonType).ToList();

        if (queryExcludePersonTypes.Count > 0)
        {
            query = query.Where(e => !queryPersonTypes.Contains(e.PersonType));
        }

        if (filter.MaxListOrder.HasValue && !filter.ItemId.IsEmpty())
        {
            query = query.Where(e => e.BaseItems!.First(w => w.ItemId == filter.ItemId).ListOrder <= filter.MaxListOrder.Value);
        }

        if (!string.IsNullOrWhiteSpace(filter.NameContains))
        {
            var nameContainsUpper = filter.NameContains.ToUpper();
            query = query.Where(e => e.Name.ToUpper().Contains(nameContainsUpper));
        }

        return query;
    }

    private bool IsAlphaNumeric(string str)
    {
        if (string.IsNullOrWhiteSpace(str))
        {
            return false;
        }

        for (int i = 0; i < str.Length; i++)
        {
            if (!char.IsLetter(str[i]) && !char.IsNumber(str[i]))
            {
                return false;
            }
        }

        return true;
    }

    private bool IsValidPersonType(string value)
    {
        return IsAlphaNumeric(value);
    }
}