ANTOOZA-BACKEND/Infrastructure/Chat/RedisChatLeaderboard.cs

using Application.Abstractions.Chat;
using Application.Abstractions.Data;
using Microsoft.EntityFrameworkCore;
using StackExchange.Redis;

namespace Infrastructure.Chat;

/// <summary>
/// Redis Sorted Set 기반 채팅 리더보드 캐시. TTL 5초 — 만료 시 다음 호출이 DB GROUP BY 한 번 실행 후 재시드.
/// 효과: 매 메시지 GROUP BY 회피 (n msg → 1 GROUP BY per TTL window). Trade-off: 최대 5초 stale.
/// </summary>
internal sealed class RedisChatLeaderboard(IConnectionMultiplexer redis) : IChatLeaderboard
{
    private static readonly TimeSpan CacheTtl = TimeSpan.FromSeconds(5);

    private static string Key(int sessionId) => $"chat-leader:session:{sessionId}";

    public async Task<(int MyScore, int HigherCount)?> GetRankAsync(int sessionId, int memberId, IAppDbContext db, CancellationToken ct = default)
    {
        var r = redis.GetDatabase();
        var key = Key(sessionId);
        var member = (RedisValue)memberId.ToString();

        // 캐시 미스 → DB GROUP BY 로 재구축. TTL 동안 후속 호출은 Redis only
        if (!await r.KeyExistsAsync(key))
        {
            var rows = await db.MemberExpLog.AsNoTracking()
                .Where(e => e.BroadcastSessionID == sessionId)
                .GroupBy(e => e.MemberID)
                .Select(g => new { MemberId = g.Key, Sum = g.Sum(x => x.Amount) })
                .ToListAsync(ct);

            if (rows.Count == 0)
            {
                return null;
            }

            var entries = rows.Select(x => new SortedSetEntry(x.MemberId.ToString(), x.Sum)).ToArray();
            await r.SortedSetAddAsync(key, entries);
            await r.KeyExpireAsync(key, CacheTtl);
        }

        var score = await r.SortedSetScoreAsync(key, member);
        if (!score.HasValue)
        {
            return null;
        }

        // ZCOUNT (score, +inf] = 본인보다 높은 점수자 수 (Exclude.Start 로 본인 점수 제외)
        var higher = await r.SortedSetLengthAsync(key, score.Value, double.PositiveInfinity, exclude: Exclude.Start);

        return ((int)score.Value, (int)higher);
    }
}