ROLAC/API/ROLAC.API/Services/OfferingSessionService.cs

using System.Security.Claims;
using Microsoft.AspNetCore.Http;
using Microsoft.EntityFrameworkCore;
using ROLAC.API.Data;
using ROLAC.API.DTOs.Giving;
using ROLAC.API.DTOs.Shared;
using ROLAC.API.Entities;
using ROLAC.API.Services.Storage;

namespace ROLAC.API.Services;

public class OfferingSessionService : IOfferingSessionService
{
    private readonly AppDbContext         _db;
    private readonly IHttpContextAccessor _http;
    private readonly IFileStorage         _storage;

    public OfferingSessionService(AppDbContext db, IHttpContextAccessor http, IFileStorage storage)
    {
        _db      = db;
        _http    = http;
        _storage = storage;
    }

    private string CurrentUserId =>
        _http.HttpContext?.User.FindFirstValue(ClaimTypes.NameIdentifier) ?? "system";

    public async Task<PagedResult<OfferingSessionListItemDto>> GetPagedAsync(
        int page, int pageSize, DateOnly? from, DateOnly? to)
    {
        var query = _db.OfferingSessions.AsNoTracking().AsQueryable();
        if (from.HasValue) query = query.Where(s => s.SessionDate >= from.Value);
        if (to.HasValue)   query = query.Where(s => s.SessionDate <= to.Value);

        var total = await query.CountAsync();
        var rows  = await query
            .OrderByDescending(s => s.SessionDate)
            .Skip((page - 1) * pageSize).Take(pageSize)
            .ToListAsync();

        var ids = rows.Select(r => r.Id).ToList();
        var counts = await _db.Givings.AsNoTracking()
            .Where(g => g.OfferingSessionId != null && ids.Contains(g.OfferingSessionId.Value))
            .GroupBy(g => g.OfferingSessionId!.Value)
            .Select(grp => new { Id = grp.Key, Count = grp.Count() })
            .ToDictionaryAsync(x => x.Id, x => x.Count);

        var items = rows.Select(s => new OfferingSessionListItemDto
        {
            Id = s.Id, SessionDate = s.SessionDate.ToString("yyyy-MM-dd"), Status = s.Status,
            CashTotal = s.CashTotal, CheckTotal = s.CheckTotal,
            SystemTotal = s.SystemTotal, Difference = s.Difference,
            LineCount = counts.TryGetValue(s.Id, out var c) ? c : 0,
            HasProof = s.ProofPdfPath != null,
        }).ToList();

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

    public async Task<bool> DateExistsAsync(DateOnly date)
        => await _db.OfferingSessions.AnyAsync(s => s.SessionDate == date);

    // Distinguishes a unique-index collision on SessionDate (concurrent insert) from other DB errors.
    private async Task<bool> DateExistsConcurrentlyAsync(DateOnly date, int excludeId)
        => await _db.OfferingSessions.AsNoTracking().AnyAsync(s => s.SessionDate == date && s.Id != excludeId);

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

        var lines = await _db.Givings.AsNoTracking()
            .Where(g => g.OfferingSessionId == id).ToListAsync();

        var catNames = await _db.GivingCategories.AsNoTracking()
            .ToDictionaryAsync(c => c.Id, c => c.Name_en);
        var memberIds = lines.Where(l => l.MemberId != null).Select(l => l.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}");

        return new OfferingSessionDto
        {
            Id = s.Id, SessionDate = s.SessionDate, Status = s.Status,
            CashTotal = s.CashTotal, CheckTotal = s.CheckTotal,
            SystemTotal = s.SystemTotal, Difference = s.Difference, Notes = s.Notes,
            HasProof = s.ProofPdfPath != null,
            Givings = lines.Select(l => new OfferingGivingLineDto
            {
                Id = l.Id, MemberId = l.MemberId,
                MemberName = l.MemberId != null && memberNames.TryGetValue(l.MemberId.Value, out var n) ? n : null,
                GivingCategoryId = l.GivingCategoryId,
                CategoryName = catNames.TryGetValue(l.GivingCategoryId, out var cn) ? cn : "",
                Amount = l.Amount, PaymentMethod = l.PaymentMethod,
                CheckNumber = l.CheckNumber,
                ZelleReferenceCode = l.ZelleReferenceCode,
                PayPalTransactionId = l.PayPalTransactionId,
                IsAnonymous = l.IsAnonymous, Notes = l.Notes,
            }).ToList(),
        };
    }

    public async Task<int> CreateAsync(CreateOfferingSessionRequest r)
    {
        if (await DateExistsAsync(r.SessionDate))
            throw new InvalidOperationException($"An offering session for {r.SessionDate:yyyy-MM-dd} already exists.");

        var systemTotal = r.Givings.Sum(g => g.Amount);
        var session = new OfferingSession
        {
            SessionDate = r.SessionDate, Status = "Submitted",
            CashTotal = r.CashTotal, CheckTotal = r.CheckTotal,
            SystemTotal = systemTotal,
            Difference  = (r.CashTotal + r.CheckTotal) - systemTotal,
            Notes = r.Notes,
            SubmittedAt = DateTimeOffset.UtcNow, SubmittedBy = CurrentUserId,
            Givings = r.Givings.Select(line => MapLine(line, r.SessionDate)).ToList(),
        };
        _db.OfferingSessions.Add(session);
        try
        {
            await _db.SaveChangesAsync();
        }
        catch (DbUpdateException)
        {
            if (await DateExistsConcurrentlyAsync(r.SessionDate, session.Id))
                throw new InvalidOperationException($"An offering session for {r.SessionDate:yyyy-MM-dd} already exists.");
            throw;
        }
        return session.Id;
    }

    public async Task ReopenAsync(int id)
    {
        var s = await _db.OfferingSessions.FindAsync(id)
            ?? throw new KeyNotFoundException($"OfferingSession {id} not found.");
        if (s.Status != "Submitted")
            throw new InvalidOperationException($"Only a Submitted session can be reopened (current: {s.Status}).");
        s.Status = "Draft";
        s.SubmittedAt = null; s.SubmittedBy = null;
        await _db.SaveChangesAsync();
    }

    public async Task ReplaceAsync(int id, CreateOfferingSessionRequest r)
    {
        var s = await _db.OfferingSessions
            .Include(x => x.Givings)
            .FirstOrDefaultAsync(x => x.Id == id)
            ?? throw new KeyNotFoundException($"OfferingSession {id} not found.");
        if (s.Status != "Draft")
            throw new InvalidOperationException($"Only a Draft (reopened) session can be edited (current: {s.Status}).");

        _db.Givings.RemoveRange(s.Givings);
        var systemTotal = r.Givings.Sum(g => g.Amount);
        s.CashTotal = r.CashTotal; s.CheckTotal = r.CheckTotal;
        s.SystemTotal = systemTotal;
        s.Difference  = (r.CashTotal + r.CheckTotal) - systemTotal;
        s.Notes = r.Notes;
        s.Status = "Submitted";
        s.SubmittedAt = DateTimeOffset.UtcNow; s.SubmittedBy = CurrentUserId;
        s.Givings = r.Givings.Select(line => MapLine(line, s.SessionDate)).ToList();
        await _db.SaveChangesAsync();
    }

    // ── Mobile offering entry (anonymous, one line at a time) ────────────────

    public async Task<OfferingEntrySummaryDto> GetEntrySummaryAsync(DateOnly date)
    {
        var session = await _db.OfferingSessions.AsNoTracking()
            .FirstOrDefaultAsync(s => s.SessionDate == date);

        if (session is null)
            return new OfferingEntrySummaryDto { SessionDate = date.ToString("yyyy-MM-dd") };

        var lines = await _db.Givings.AsNoTracking()
            .Where(g => g.OfferingSessionId == session.Id).ToListAsync();

        return new OfferingEntrySummaryDto
        {
            SessionId = session.Id,
            SessionDate = session.SessionDate.ToString("yyyy-MM-dd"),
            Status = session.Status,
            SystemTotal = session.SystemTotal,
            LineCount = lines.Count,
            HasProof = session.ProofPdfPath != null,
            Lines = await BuildLineDtosAsync(lines),
        };
    }

    public async Task<OfferingEntryLineAddedDto> AppendLineAsync(DateOnly date, OfferingGivingLineRequest line)
    {
        var session = await _db.OfferingSessions
            .Include(s => s.Givings)
            .FirstOrDefaultAsync(s => s.SessionDate == date);

        Giving giving;
        if (session is null)
        {
            session = new OfferingSession { SessionDate = date, Status = "Draft" };
            _db.OfferingSessions.Add(session);
            giving = MapLine(line, date);
            session.Givings.Add(giving);
            RecalcTotals(session);
            try
            {
                await _db.SaveChangesAsync();
            }
            catch (DbUpdateException)
            {
                // Lost the create race — another phone inserted today's session first.
                // Start clean and append to the now-existing session instead.
                _db.ChangeTracker.Clear();
                session = await _db.OfferingSessions.Include(s => s.Givings)
                    .FirstAsync(s => s.SessionDate == date);
                ReopenIfLocked(session);
                giving = MapLine(line, date);
                session.Givings.Add(giving);
                RecalcTotals(session);
                await _db.SaveChangesAsync();
            }
        }
        else
        {
            ReopenIfLocked(session);   // auto-reopen a Submitted/Reconciled session (decision #3)
            giving = MapLine(line, date);
            session.Givings.Add(giving);
            RecalcTotals(session);
            await _db.SaveChangesAsync();
        }

        var lineDto = (await BuildLineDtosAsync([giving])).Single();
        return new OfferingEntryLineAddedDto
        {
            SessionId = session.Id,
            SessionDate = session.SessionDate.ToString("yyyy-MM-dd"),
            Status = session.Status,
            SystemTotal = session.SystemTotal,
            LineCount = session.Givings.Count,
            Line = lineDto,
        };
    }

    public async Task<List<MemberTypeaheadDto>> SearchMembersForEntryAsync(string? search, int take)
    {
        if (string.IsNullOrWhiteSpace(search))
            return [];

        // ILike is a case-insensitive LIKE (PostgreSQL) — so "chen" matches "Chen".
        var pattern = $"%{search.Trim()}%";
        return await _db.Members.AsNoTracking()
            .Where(m => EF.Functions.ILike(m.FirstName_en, pattern)
                     || EF.Functions.ILike(m.LastName_en, pattern)
                     || (m.NickName     != null && EF.Functions.ILike(m.NickName, pattern))
                     || (m.FirstName_zh != null && EF.Functions.ILike(m.FirstName_zh, pattern))
                     || (m.LastName_zh  != null && EF.Functions.ILike(m.LastName_zh, pattern)))
            .OrderBy(m => m.LastName_en).ThenBy(m => m.FirstName_en)
            .Take(take)
            .Select(m => new MemberTypeaheadDto
            {
                Id = m.Id, NickName = m.NickName,
                FirstName_en = m.FirstName_en, LastName_en = m.LastName_en,
            })
            .ToListAsync();
    }

    // Resolve category + member names for a set of lines (shared by entry summary/append).
    private async Task<List<OfferingGivingLineDto>> BuildLineDtosAsync(List<Giving> lines)
    {
        var catNames = await _db.GivingCategories.AsNoTracking()
            .ToDictionaryAsync(c => c.Id, c => c.Name_en);
        var memberIds = lines.Where(l => l.MemberId != null).Select(l => l.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}");

        return lines.Select(l => new OfferingGivingLineDto
        {
            Id = l.Id, MemberId = l.MemberId,
            MemberName = l.MemberId != null && memberNames.TryGetValue(l.MemberId.Value, out var n) ? n : null,
            GivingCategoryId = l.GivingCategoryId,
            CategoryName = catNames.TryGetValue(l.GivingCategoryId, out var cn) ? cn : "",
            Amount = l.Amount, PaymentMethod = l.PaymentMethod,
            CheckNumber = l.CheckNumber,
            ZelleReferenceCode = l.ZelleReferenceCode,
            PayPalTransactionId = l.PayPalTransactionId,
            IsAnonymous = l.IsAnonymous, Notes = l.Notes,
        }).ToList();
    }

    private static void RecalcTotals(OfferingSession session)
    {
        session.SystemTotal = session.Givings.Sum(g => g.Amount);
        session.Difference  = (session.CashTotal + session.CheckTotal) - session.SystemTotal;
    }

    // Revert a locked (Submitted/Reconciled) session to Draft so entry can continue.
    private static void ReopenIfLocked(OfferingSession session)
    {
        if (session.Status is "Submitted" or "Reconciled")
        {
            session.Status = "Draft";
            session.SubmittedAt  = null; session.SubmittedBy  = null;
            session.ReconciledAt = null; session.ReconciledBy = null;
        }
    }

    // ── Paper-proof PDF (one merged file per session) ────────────────────────

    public async Task SaveProofAsync(int id, Stream content, string fileName)
    {
        var s = await _db.OfferingSessions.FindAsync(id)
            ?? throw new KeyNotFoundException($"OfferingSession {id} not found.");

        var path = $"finance/offering-proofs/{s.SessionDate.Year}/{s.SessionDate.Month}/{s.Id}-proof.pdf";
        if (s.ProofPdfPath != null && s.ProofPdfPath != path)
            await _storage.DeleteAsync(s.ProofPdfPath);
        var saved = await _storage.SaveAsync(content, path);
        s.ProofPdfPath = saved;
        await _db.SaveChangesAsync();
    }

    public async Task<(Stream stream, string contentType)?> OpenProofAsync(int id)
    {
        var s = await _db.OfferingSessions.AsNoTracking().FirstOrDefaultAsync(x => x.Id == id)
            ?? throw new KeyNotFoundException($"OfferingSession {id} not found.");
        if (s.ProofPdfPath is null) return null;
        var stream = await _storage.OpenReadAsync(s.ProofPdfPath);
        if (stream is null) return null;
        return (stream, "application/pdf");
    }

    public async Task DeleteProofAsync(int id)
    {
        var s = await _db.OfferingSessions.FindAsync(id)
            ?? throw new KeyNotFoundException($"OfferingSession {id} not found.");
        if (s.ProofPdfPath is null) return;
        await _storage.DeleteAsync(s.ProofPdfPath);
        s.ProofPdfPath = null;
        await _db.SaveChangesAsync();
    }

    // ── Date-keyed proof (anonymous mobile page knows the date, not the id) ──

    // Attach a proof PDF to the date's session, creating a Draft session if none
    // exists yet (the volunteer may snap the count sheet before any line is keyed).
    public async Task SaveProofForDateAsync(DateOnly date, Stream content, string fileName)
    {
        var session = await _db.OfferingSessions.FirstOrDefaultAsync(s => s.SessionDate == date);
        if (session is null)
        {
            session = new OfferingSession { SessionDate = date, Status = "Draft" };
            _db.OfferingSessions.Add(session);
            try
            {
                await _db.SaveChangesAsync();
            }
            catch (DbUpdateException)
            {
                // Lost the create race — another phone inserted today's session first.
                _db.ChangeTracker.Clear();
                session = await _db.OfferingSessions.FirstAsync(s => s.SessionDate == date);
            }
        }
        await SaveProofAsync(session.Id, content, fileName);
    }

    public async Task<(Stream stream, string contentType)?> OpenProofForDateAsync(DateOnly date)
    {
        var session = await _db.OfferingSessions.AsNoTracking()
            .FirstOrDefaultAsync(s => s.SessionDate == date);
        if (session is null) return null;
        return await OpenProofAsync(session.Id);
    }

    private static Giving MapLine(OfferingGivingLineRequest line, DateOnly sessionDate) => new()
    {
        MemberId            = line.IsAnonymous ? null : line.MemberId,
        GivingCategoryId    = line.GivingCategoryId,
        Amount              = line.Amount,
        PaymentMethod       = line.PaymentMethod,
        CheckNumber         = line.CheckNumber,
        ZelleReferenceCode  = line.ZelleReferenceCode,
        PayPalTransactionId = line.PayPalTransactionId,
        GivingDate          = sessionDate,
        IsAnonymous         = line.IsAnonymous,
        Notes               = line.Notes,
    };
}