ROLAC/API/ROLAC.API/Services/GivingService.cs

using Microsoft.EntityFrameworkCore;
using ROLAC.API.Data;
using ROLAC.API.DTOs.Giving;
using ROLAC.API.DTOs.Shared;
using ROLAC.API.Entities;

namespace ROLAC.API.Services;

public class GivingService : IGivingService
{
    private readonly AppDbContext _db;
    public GivingService(AppDbContext db) => _db = db;

    public async Task<PagedResult<GivingListItemDto>> GetPagedAsync(
        int page, int pageSize, string? search, int? categoryId, DateOnly? from, DateOnly? to)
    {
        var query = _db.Givings.AsNoTracking().AsQueryable();

        if (categoryId.HasValue) query = query.Where(g => g.GivingCategoryId == categoryId.Value);
        if (from.HasValue)       query = query.Where(g => g.GivingDate >= from.Value);
        if (to.HasValue)         query = query.Where(g => g.GivingDate <= to.Value);
        if (!string.IsNullOrWhiteSpace(search))
        {
            var s    = search.Trim().ToLower();
            var term = search.Trim();
            query = query.Where(g =>
                (g.CheckNumber != null && g.CheckNumber.ToLower().Contains(s)) ||
                (g.Notes       != null && g.Notes.ToLower().Contains(s)) ||
                (g.Member != null && (
                    (g.Member.FirstName_en + " " + g.Member.LastName_en).ToLower().Contains(s) ||
                    (g.Member.FirstName_zh != null && g.Member.FirstName_zh.Contains(term)) ||
                    (g.Member.LastName_zh  != null && g.Member.LastName_zh.Contains(term)))));
        }

        var total = await query.CountAsync();
        var rows  = await query
            .OrderByDescending(g => g.GivingDate).ThenByDescending(g => g.Id)
            .Skip((page - 1) * pageSize).Take(pageSize)
            .ToListAsync();

        var catNames = await _db.GivingCategories.AsNoTracking()
            .ToDictionaryAsync(c => c.Id, c => c.Name_en);
        var memberIds = rows.Where(r => r.MemberId != null).Select(r => r.MemberId!.Value).ToHashSet();
        var memberNames = await _db.Members.AsNoTracking()
            .Where(m => memberIds.Contains(m.Id))
            .ToDictionaryAsync(m => m.Id, m => $"{m.FirstName_en} {m.LastName_en}");

        var items = rows.Select(g => new GivingListItemDto
        {
            Id = g.Id, MemberId = g.MemberId,
            MemberName = g.MemberId != null && memberNames.TryGetValue(g.MemberId.Value, out var n) ? n : null,
            GivingCategoryId = g.GivingCategoryId,
            CategoryName = catNames.TryGetValue(g.GivingCategoryId, out var cn) ? cn : "",
            Amount = g.Amount, PaymentMethod = g.PaymentMethod,
            GivingDate = g.GivingDate.ToString("yyyy-MM-dd"),
            IsAnonymous = g.IsAnonymous, OfferingSessionId = g.OfferingSessionId,
        }).ToList();

        return new PagedResult<GivingListItemDto>
            { Items = items, TotalCount = total, Page = page, PageSize = pageSize };
    }

    public async Task<GivingDto?> GetByIdAsync(int id)
    {
        var g = await _db.Givings.AsNoTracking().FirstOrDefaultAsync(x => x.Id == id);
        if (g is null) return null;

        string? memberName = null;
        if (g.MemberId != null)
            memberName = await _db.Members.AsNoTracking()
                .Where(m => m.Id == g.MemberId)
                .Select(m => m.FirstName_en + " " + m.LastName_en)
                .FirstOrDefaultAsync();

        return new GivingDto
        {
            Id = g.Id, MemberId = g.MemberId, MemberName = memberName,
            GivingCategoryId = g.GivingCategoryId, OfferingSessionId = g.OfferingSessionId,
            Amount = g.Amount, PaymentMethod = g.PaymentMethod, CheckNumber = g.CheckNumber,
            ZelleReferenceCode = g.ZelleReferenceCode, PayPalTransactionId = g.PayPalTransactionId,
            GivingDate = g.GivingDate, IsAnonymous = g.IsAnonymous, Notes = g.Notes,
        };
    }

    public async Task<int> CreateAsync(CreateGivingRequest r)
    {
        var g = MapFromRequest(new Giving(), r);
        g.OfferingSessionId = null;
        _db.Givings.Add(g);
        await _db.SaveChangesAsync();
        return g.Id;
    }

    public async Task UpdateAsync(int id, UpdateGivingRequest r)
    {
        var g = await _db.Givings.FindAsync(id)
            ?? throw new KeyNotFoundException($"Giving {id} not found.");
        await GuardSessionNotLockedAsync(g.OfferingSessionId);
        MapFromRequest(g, r);
        await _db.SaveChangesAsync();
    }

    public async Task DeleteAsync(int id)
    {
        var g = await _db.Givings.FindAsync(id)
            ?? throw new KeyNotFoundException($"Giving {id} not found.");
        await GuardSessionNotLockedAsync(g.OfferingSessionId);
        _db.Givings.Remove(g);
        await _db.SaveChangesAsync();
    }

    private async Task GuardSessionNotLockedAsync(int? sessionId)
    {
        if (sessionId is null) return;
        var status = await _db.OfferingSessions
            .Where(s => s.Id == sessionId).Select(s => s.Status).FirstOrDefaultAsync();
        if (status is "Submitted" or "Reconciled")
            throw new InvalidOperationException(
                "This giving belongs to a locked offering session. Reopen the session to edit.");
    }

    private static Giving MapFromRequest(Giving g, CreateGivingRequest r)
    {
        g.MemberId = r.IsAnonymous ? null : r.MemberId;
        g.GivingCategoryId = r.GivingCategoryId;
        g.Amount = r.Amount; g.PaymentMethod = r.PaymentMethod;
        g.CheckNumber = r.CheckNumber; g.ZelleReferenceCode = r.ZelleReferenceCode;
        g.PayPalTransactionId = r.PayPalTransactionId; g.GivingDate = r.GivingDate;
        g.IsAnonymous = r.IsAnonymous; g.Notes = r.Notes;
        return g;
    }
}