tiku-backend.net/Tiku.Infrastructure/Scoreline/ScorelineQueryService.cs

using System.Text.Json;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.EntityFrameworkCore;
using Tiku.Application.Catalog;
using Tiku.Application.Scoreline;
using Tiku.Domain.Catalog;
using Tiku.Infrastructure.Persistence;

namespace Tiku.Infrastructure.Scoreline;

public sealed partial class ScorelineQueryService(
    TikuDbContext dbContext,
    ScorelineRecordQuery recordQuery) : IScorelineQueryService
{
    public async Task<CatalogList<ScorelineFieldItem>> GetFieldsAsync(
        ScorelineFilter filter,
        CancellationToken cancellationToken = default)
    {
        var query = dbContext.ScorelineFields.AsNoTracking()
            .Where(field => field.TenantId == filter.TenantId);
        if (filter.RegionId.HasValue)
        {
            query = query.Where(field => field.RegionId == filter.RegionId.Value || field.RegionId == null);
        }

        var fields = await query
            .OrderBy(field => field.SortOrder)
            .ThenBy(field => field.FieldName)
            .Take(ResolveLimit(filter.Limit))
            .ToArrayAsync(cancellationToken);

        return new CatalogList<ScorelineFieldItem>(fields.Select(ToFieldItem).ToArray());
    }

    public async Task<ScorelineRecordPage> GetRecordsAsync(
        ScorelineFilter filter,
        CancellationToken cancellationToken = default)
    {
        var page = Math.Max(filter.Page ?? 1, 1);
        var pageSize = Math.Clamp(filter.PageSize ?? 20, 1, 200);
        ValidateDynamicFilterKeys(filter.DynamicFilters);
        var result = await recordQuery.ExecutePageAsync(filter, page, pageSize, cancellationToken);
        return new ScorelineRecordPage(page, pageSize, result.Total ?? 0, result.Items);
    }

    public async Task<ScorelineRecordCursorPage> GetRecordsCursorAsync(
        ScorelineFilter filter,
        string? cursor,
        CancellationToken cancellationToken = default)
    {
        ValidateDynamicFilterKeys(filter.DynamicFilters);
        var pageSize = Math.Clamp(filter.PageSize ?? 20, 1, 200);
        var position = DecodeCursor(cursor);
        var result = await recordQuery.ExecuteCursorAsync(filter, pageSize, position, cancellationToken);
        var hasMore = result.Items.Count > pageSize;
        var items = result.Items.Take(pageSize).ToArray();
        var nextCursor = hasMore && items.Length > 0 ? EncodeCursor(items[^1]) : null;
        return new ScorelineRecordCursorPage(pageSize, hasMore, nextCursor, items);
    }

    public async Task<CatalogList<ScorelineRecordItem>> GetTrendAsync(
        ScorelineFilter filter,
        CancellationToken cancellationToken = default)
    {
        ValidateDynamicFilterKeys(filter.DynamicFilters);
        var trendFilter = filter with { Page = 1, PageSize = ResolveLimit(filter.Limit, 100) };
        var result = await recordQuery.ExecutePageAsync(trendFilter, 1, trendFilter.PageSize!.Value, cancellationToken);
        return new CatalogList<ScorelineRecordItem>(result.Items
            .OrderBy(item => item.Year)
            .ThenBy(item => item.MajorName)
            .ToArray());
    }

    public async Task<CatalogList<int>> GetYearsAsync(
        ScorelineFilter filter,
        CancellationToken cancellationToken = default)
    {
        var years = await BuildRecordQuery(filter)
            .Select(record => record.Year)
            .Distinct()
            .OrderByDescending(year => year)
            .Take(ResolveLimit(filter.Limit))
            .ToArrayAsync(cancellationToken);

        return new CatalogList<int>(years);
    }

    private IQueryable<ScorelineRecord> BuildRecordQuery(ScorelineFilter filter)
    {
        var query = dbContext.ScorelineRecords.AsNoTracking()
            .Where(record => record.TenantId == filter.TenantId);
        if (filter.RegionId.HasValue)
        {
            query = query.Where(record => record.RegionId == filter.RegionId.Value);
        }

        if (filter.SchoolId.HasValue)
        {
            query = query.Where(record => record.SchoolId == filter.SchoolId.Value);
        }

        if (filter.MajorId.HasValue)
        {
            query = query.Where(record => record.MajorId == filter.MajorId.Value);
        }

        if (filter.Year.HasValue)
        {
            query = query.Where(record => record.Year == filter.Year.Value);
        }

        if (!string.IsNullOrWhiteSpace(filter.Keyword))
        {
            var keyword = filter.Keyword.Trim();
            query = query.Where(record =>
                (record.SchoolName != null && record.SchoolName.Contains(keyword)) ||
                (record.MajorName != null && record.MajorName.Contains(keyword)));
        }

        return query;
    }

    private static void ValidateDynamicFilterKeys(IReadOnlyCollection<ScorelineDynamicFilter>? filters)
    {
        foreach (var dynamicFilter in filters ?? [])
        {
            if (!FieldKeyRegex().IsMatch(dynamicFilter.FieldKey))
            {
                throw new ScorelineQueryException("Scoreline field filter key is invalid.", "scoreline_field_filter_key_invalid");
            }

            if (dynamicFilter.Operator is not ("field" or "min" or "max"))
            {
                throw new ScorelineQueryException("Scoreline field filter operator is invalid.", "scoreline_field_filter_operator_invalid");
            }
        }
    }

    private static string EncodeCursor(ScorelineRecordItem item)
    {
        var json = JsonSerializer.SerializeToUtf8Bytes(new
        {
            v = 1,
            year = item.Year,
            schoolName = item.SchoolName,
            majorName = item.MajorName,
            id = item.Id
        });
        return Convert.ToBase64String(json).TrimEnd('=').Replace('+', '-').Replace('/', '_');
    }

    private static ScorelineCursorPosition? DecodeCursor(string? cursor)
    {
        if (string.IsNullOrWhiteSpace(cursor))
        {
            return null;
        }

        try
        {
            var normalized = cursor.Replace('-', '+').Replace('_', '/');
            normalized = normalized.PadRight(normalized.Length + ((4 - normalized.Length % 4) % 4), '=');
            using var document = JsonDocument.Parse(Convert.FromBase64String(normalized));
            var root = document.RootElement;
            if (root.GetProperty("v").GetInt32() != 1 ||
                !root.TryGetProperty("year", out var year) ||
                !root.TryGetProperty("id", out var id) ||
                !Guid.TryParse(id.GetString(), out var recordId))
            {
                throw new FormatException();
            }

            return new ScorelineCursorPosition(
                year.GetInt32(),
                ReadNullableString(root, "schoolName"),
                ReadNullableString(root, "majorName"),
                recordId);
        }
        catch (Exception exception) when (exception is FormatException or JsonException or InvalidOperationException or KeyNotFoundException)
        {
            throw new ScorelineQueryException("Scoreline cursor is invalid or unsupported.", "scoreline_cursor_invalid");
        }
    }

    private static string? ReadNullableString(JsonElement root, string name) =>
        root.TryGetProperty(name, out var value) && value.ValueKind == JsonValueKind.String
            ? value.GetString()
            : null;

    private static ScorelineFieldItem ToFieldItem(ScorelineField field)
    {
        return new ScorelineFieldItem(
            field.Id,
            field.LegacyId,
            field.RegionId,
            field.FieldKey,
            field.FieldName,
            field.FieldType,
            field.Unit,
            field.IsFilter,
            field.IsRequired,
            field.IsVisible,
            field.IsTrend,
            field.Options,
            field.Placeholder,
            field.Description,
            field.SortOrder);
    }

    private static ScorelineRecordItem ToRecordItem(ScorelineRecord record)
    {
        return new ScorelineRecordItem(
            record.Id,
            record.LegacyId,
            record.RegionId,
            record.SchoolId,
            record.MajorId,
            record.Year,
            record.SchoolName,
            record.MajorName,
            record.FieldValues);
    }

    private static int ResolveLimit(int? limit, int fallback = 500)
    {
        return Math.Clamp(limit ?? fallback, 1, 2000);
    }

    [GeneratedRegex("^[A-Za-z][A-Za-z0-9_]{0,63}$")]
    private static partial Regex FieldKeyRegex();
}

public sealed class ScorelineQueryException(string message, string code) : Exception(message)
{
    public string Code { get; } = code;
}