ROLAC/API/ROLAC.API/Services/Form1099ReportService.cs

using Microsoft.EntityFrameworkCore;
using ROLAC.API.Data;
using ROLAC.API.DTOs.Finance;
using ROLAC.API.Entities;

namespace ROLAC.API.Services;

/// <summary>
/// Read-only aggregation producing the year-end 1099 recipient summary. CASH BASIS:
/// only Paid expenses whose PaidAt falls in the tax year, attributed to a tracked payee,
/// on a line whose category maps to a 1099 box (sub ?? group). Unmapped lines are excluded.
/// </summary>
public class Form1099ReportService : IForm1099ReportService
{
    private readonly AppDbContext _db;
    public Form1099ReportService(AppDbContext db) => _db = db;

    public async Task<List<Form1099BoxDto>> GetBoxesAsync() =>
        await _db.Form1099Boxes.AsNoTracking().Where(b => b.IsActive)
            .OrderBy(b => b.SortOrder)
            .Select(b => new Form1099BoxDto
            {
                Id = b.Id, BoxCode = b.BoxCode, Name_en = b.Name_en,
                Name_zh = b.Name_zh, FormType = b.FormType, SortOrder = b.SortOrder,
            }).ToListAsync();

    /// <summary>
    /// Pulls the reportable expense lines for the tax year and materializes them (anonymous
    /// projection -&gt; ToListAsync -&gt; in-memory map), mirroring Form990ReportService so the SQL
    /// translation stays simple on Npgsql. The tax year is a half-open UTC range
    /// [Jan 1 taxYear, Jan 1 taxYear+1), deterministic regardless of server timezone and matching
    /// how Expense.PaidAt is written (midnight UTC). Unmapped lines (no 1099 box) are dropped here
    /// so callers always receive reportable lines.
    /// </summary>
    private async Task<List<PaidLine>> LoadReportableLinesAsync(int taxYear)
    {
        var start = new DateTimeOffset(new DateTime(taxYear, 1, 1), TimeSpan.Zero);
        var end   = start.AddYears(1);
        var raw = await (
            from e in _db.Expenses.Where(e => e.Status == "Paid" && e.PaidAt != null
                                              && e.PaidAt >= start && e.PaidAt < end && e.PayeeId != null)
            join p   in _db.Payee1099s.Where(p => p.Is1099Tracked) on e.PayeeId equals p.Id
            join l   in _db.ExpenseLines          on e.Id              equals l.ExpenseId
            join sub in _db.ExpenseSubCategories  on l.SubCategoryId   equals sub.Id
            join grp in _db.ExpenseCategoryGroups on l.CategoryGroupId equals grp.Id
            select new
            {
                PayeeId = p.Id,
                p.LegalName,
                p.TinLast4,
                p.W9Status,
                PaidAt = e.PaidAt!.Value,
                e.Description,
                GroupName = grp.Name_en,
                SubName   = sub.Name_en,
                l.Amount,
                BoxId = sub.Form1099BoxId ?? grp.Form1099BoxId,
            }).ToListAsync();

        return raw.Where(x => x.BoxId != null)
            .Select(x => new PaidLine
            {
                PayeeId      = x.PayeeId,
                LegalName    = x.LegalName,
                TinLast4     = x.TinLast4,
                W9Status     = x.W9Status,
                PaidAt       = x.PaidAt,
                Description  = x.Description,
                CategoryName = x.GroupName + " / " + x.SubName,
                Amount       = x.Amount,
                BoxId        = x.BoxId,
            }).ToList();
    }

    public async Task<Form1099SummaryDto> GetAnnualSummaryAsync(int taxYear)
    {
        var boxes = await _db.Form1099Boxes.AsNoTracking().ToDictionaryAsync(b => b.Id, b => b.BoxCode);
        var lines = await LoadReportableLinesAsync(taxYear);

        var dto = new Form1099SummaryDto { TaxYear = taxYear };
        foreach (var g in lines.GroupBy(x => x.PayeeId))
        {
            var first = g.First();
            var nec   = g.Where(x => boxes.GetValueOrDefault(x.BoxId!.Value) == Form1099.BoxNec1).Sum(x => x.Amount);
            var rents = g.Where(x => boxes.GetValueOrDefault(x.BoxId!.Value) == Form1099.BoxMisc1).Sum(x => x.Amount);
            var w9Missing = first.W9Status != Form1099.W9Status.OnFile;
            var meets = nec >= Form1099.ReportingThreshold || rents >= Form1099.ReportingThreshold;
            dto.Rows.Add(new Form1099RecipientRowDto
            {
                PayeeId = first.PayeeId, LegalName = first.LegalName, TinLast4 = first.TinLast4,
                W9Status = first.W9Status, NecTotal = nec, RentsTotal = rents,
                GrandTotal = nec + rents, MeetsThreshold = meets, W9Missing = w9Missing,
            });
        }
        dto.Rows = dto.Rows.OrderByDescending(r => r.GrandTotal).ThenBy(r => r.LegalName).ToList();
        dto.TotalReportable       = dto.Rows.Sum(r => r.GrandTotal);
        dto.RecipientsAtThreshold = dto.Rows.Count(r => r.MeetsThreshold);
        dto.RecipientsMissingW9   = dto.Rows.Count(r => r.W9Missing);
        return dto;
    }

    public async Task<Form1099RecipientDetailDto?> GetRecipientDetailAsync(int payeeId, int taxYear)
    {
        var payee = await _db.Payee1099s.AsNoTracking().FirstOrDefaultAsync(p => p.Id == payeeId);
        if (payee is null) return null;
        var boxes = await _db.Form1099Boxes.AsNoTracking().ToDictionaryAsync(b => b.Id, b => b.BoxCode);
        var lines = (await LoadReportableLinesAsync(taxYear)).Where(x => x.PayeeId == payeeId).ToList();

        return new Form1099RecipientDetailDto
        {
            PayeeId = payee.Id, LegalName = payee.LegalName, TinLast4 = payee.TinLast4,
            W9Status = payee.W9Status, TaxYear = taxYear,
            Payments = lines.OrderBy(x => x.PaidAt).Select(x => new Form1099PaymentDto
            {
                PaidDate = DateOnly.FromDateTime(x.PaidAt.Date).ToString("yyyy-MM-dd"),
                Description = x.Description, CategoryName = x.CategoryName,
                BoxCode = boxes.GetValueOrDefault(x.BoxId!.Value) ?? "", Amount = x.Amount,
            }).ToList(),
        };
    }

    private sealed class PaidLine
    {
        public int            PayeeId      { get; set; }
        public string         LegalName    { get; set; } = "";
        public string?        TinLast4     { get; set; }
        public string         W9Status     { get; set; } = "";
        public DateTimeOffset PaidAt       { get; set; }
        public string         Description  { get; set; } = "";
        public string         CategoryName { get; set; } = "";
        public decimal        Amount       { get; set; }
        public int?           BoxId        { get; set; }
    }
}