ANTOOZA-BACKEND/Application/Features/Admin/Dashboard/MailHealth/Handler.cs

using Application.Abstractions.Data;
using Application.Abstractions.Messaging;
using Domain.Entities.Common.ValueObject;
using Microsoft.EntityFrameworkCore;

namespace Application.Features.Admin.Dashboard.MailHealth;

public sealed class Handler(IAppDbContext db) : IQueryHandler<Query, Response>
{
    public async Task<Response> Handle(Query request, CancellationToken ct)
    {
        var now = DateTime.UtcNow;
        var processingStuckCutoff = now.AddMinutes(-5);
        var stalePendingCutoff = now.AddHours(-24);
        var recent24hCutoff = now.AddHours(-24);

        // 한 번의 쿼리로 모든 집계 산출 (테이블 1회 스캔)
        var stats = await db.EmailLog.AsNoTracking().GroupBy(x => 1).Select(g => new
        {
            PendingCount = g.Count(x => x.Status == MailStatus.Pending),
            ProcessingStuckCount = g.Count(x => x.Status == MailStatus.Processing && x.CreatedAt < processingStuckCutoff),
            StalePendingCount = g.Count(x => x.Status == MailStatus.Pending && x.CreatedAt < stalePendingCutoff),
            Sent24hCount = g.Count(x => x.Status == MailStatus.Sent && x.ProcessedAt != null && x.ProcessedAt >= recent24hCutoff),
            Failed24hCount = g.Count(x => x.Status == MailStatus.Failed && x.FailedAt != null && x.FailedAt >= recent24hCutoff),
            TotalRowCount = g.Count(),
            OldestPendingAt = g.Where(x => x.Status == MailStatus.Pending).Min(x => (DateTime?)x.CreatedAt)
        }).FirstOrDefaultAsync(ct);

        if (stats is null)
        {
            return new Response();
        }

        return new Response
        {
            PendingCount = stats.PendingCount,
            ProcessingStuckCount = stats.ProcessingStuckCount,
            StalePendingCount = stats.StalePendingCount,
            Sent24hCount = stats.Sent24hCount,
            Failed24hCount = stats.Failed24hCount,
            TotalRowCount = stats.TotalRowCount,
            OldestPendingAt = stats.OldestPendingAt
        };
    }
}