using System.Diagnostics;
using Fast.Shared.Logic.ValuationByPath;
using Fast.Web.Models;
using static Fast.Shared.Models.Enums;

namespace Fast.Web.Logic.CrudeOil;

public class GetInvoiceHelper
{
    public static async Task<CrudeInvoice> GetInvoiceAsync(int? invoiceId, int counterpartyId, int internalEntityId, DateOnly month)
    {
        CrudeInvoice invoice;

        using var db = Main.CreateContext();

        InvoiceCrude? existingInvoice = null;

        if (invoiceId.HasValue)
        {
            existingInvoice = await (
                from q in db.InvoiceCrudes
                    .Include(x => x.InvoiceCrudeLines)
                where q.Id == invoiceId.Value
                select q
            ).FirstOrDefaultAsync();
        }

        if (existingInvoice == null)
            invoice = await GetNewInvoiceAsync(counterpartyId, internalEntityId, month);
        else
            invoice = await GetExistingInvoiceAsync(existingInvoice, counterpartyId, internalEntityId, month);

        return invoice;
    }

    private static async Task<CrudeInvoice> GetNewInvoiceAsync(int counterpartyId, int internalEntityId, DateOnly month)
    {
        var invoice = new CrudeInvoice();
        invoice.MainInvoice = await GetMainInvoiceAsync(null, counterpartyId, internalEntityId, month);
        invoice.Lines = await InvoiceLineHelper.GetInitialLinesAsync(counterpartyId, internalEntityId, month);

        return invoice;
    }

    private static async Task<CrudeInvoice> GetExistingInvoiceAsync(InvoiceCrude dbInvoice, int counterpartyId, int internalEntityId, DateOnly month)
    {
        var invoice = new CrudeInvoice();
        invoice.MainInvoice = await GetMainInvoiceAsync(dbInvoice.Id, counterpartyId, internalEntityId, month);
        invoice.Lines = await InvoiceLineHelper.ConvertDbLinesAsync(dbInvoice.InvoiceCrudeLines);

        return invoice;
    }

    private static async Task<MainInvoice> GetMainInvoiceAsync(int? invoiceId, int counterpartyId, int internalEntityId, DateOnly month)
    {
        using var db = Main.CreateContext();

        // if there's no existing invoice in the database, then create a new one
        if (invoiceId.HasValue)
        {
            var existingInvoice = await (
                from q in db.InvoiceCrudes
                    .Include(x => x.InvoiceCrudeLines)
                        .ThenInclude(x => x.Deal)
                    .Include(x => x.InternalEntity)
                    .Include(x => x.InternalEntityCountry) //this is a `direct` property on InvoiceCrude, not from InternalEntity
                    .Include(x => x.InternalEntityState) //this is a `direct` property on InvoiceCrude, not from InternalEntity
                    .Include(x => x.Counterparty)
                    .Include(x => x.CounterpartyCountry) //this is a `direct` property on InvoiceCrude, not from Counterparty
                    .Include(x => x.CounterpartyState) //this is a `direct` property on InvoiceCrude, not from Counterparty
                    .Include(x => x.InvoiceType)
                    .Include(x => x.ContractPaymentType)
                    .Include(x => x.EmailedByNavigation)
                where q.Id == invoiceId.Value
                select q
            ).AsNoTracking().FirstOrDefaultAsync();

            if (existingInvoice != null)
            {
                var value = ConvertDbItem(existingInvoice);
                return value;
            }
        }

        var newMainInvoice = await GetNewMainInvoiceAsync(counterpartyId, internalEntityId, month);
        return newMainInvoice;
    }

    public static async Task<MainInvoice> GetNewMainInvoiceAsync(int counterpartyId, int internalEntityId, DateOnly month)
    {
        month = Util.Date.FirstDayOfMonth(month);
        var firstDay = Util.Date.FirstDayOfMonth(month);
        var lastDay = Util.Date.LastDayOfMonth(month);

        static IQueryable<Contract> GetContractsQuery(MyDbContext db)
        {
            return (
                from q in db.Contracts
                .Include(x => x.ContractContact)
                .Include(x => x.ContractExhibitC)
                .Include(x => x.ContractAccounting)
                    .ThenInclude(x => x!.InvoiceCountry)
                .Include(x => x.ContractAccounting)
                    .ThenInclude(x => x!.InvoiceState)
                .Include(x => x.Product)
                .Include(x => x.PayToBank)
                select q
            ).AsNoTracking();
        }

        using var db1 = Main.CreateContext();
        var taskNettingContractNum = (
            from q in db1.CrudeMarketSupplies
            where q.MarketNom.Deal != null && q.MarketNom.Deal.BuyButton == -1
                && firstDay <= q.Date && q.Date <= lastDay
                && q.MarketNom.Deal.InternalEntityId.HasValue && q.MarketNom.Deal.CounterpartyId.HasValue
                && q.MarketNom.Deal.InternalEntityId.Value == internalEntityId
                && q.MarketNom.Deal.CounterpartyId.Value == counterpartyId
                && q.MarketNom.Deal.NettingContractNumber != null
            select q.MarketNom.Deal!.NettingContractNumber
        ).AsNoTracking().FirstAsync();

        using var db2 = Main.CreateContext();
        var taskInternalEntityContract = (
            from q in GetContractsQuery(db2)
            where q.IsInternalSideOfContract
                && q.InternalEntityId == internalEntityId
                && q.Product.CategoryId == (int)Enums.ProductCategory.CrudeOil
            select q
        ).FirstAsync();

        using var db3 = Main.CreateContext();
        var taskInvoiceContacts = (
            from c in db3.Contacts
                .Include(x => x.ContactCounterparties)
                .Include(x => x.ContactToContactTypes)
            let isInvoiceContact = c.ContactToContactTypes.Any(x => x.ContactTypeId == (int)Enums.ContactType.Invoice)
            let isActive = c.ContactCounterparties.Any(x => x.InactiveDate == null || DateOnly.FromDateTime(DateTime.Now) < x.InactiveDate)
            where isInvoiceContact && isActive
            select c
        ).AsNoTracking().ToListAsync();

        using var db4 = Main.CreateContext();
        var taskCounterpartyName = (
            from q in db4.Counterparties
            where q.Id == counterpartyId
            select q.Name
        ).AsNoTracking().FirstAsync();

        using var db5 = Main.CreateContext();
        var taskInternalEntityName = (
            from q in db5.Counterparties
            where q.Id == internalEntityId
            select q.Name
        ).AsNoTracking().FirstAsync();

        using var db6 = Main.CreateContext();
        var taskCounterpartyCustomerNum = (
            from q in db6.Counterparties
            where q.Id == counterpartyId
            select q.InternalCustomerNum
        ).AsNoTracking().FirstAsync();

        var swQueries = Stopwatch.StartNew();
        var nettingContractNum = await taskNettingContractNum;
        using var db7 = Main.CreateContext();
        var contract = await GetContractsQuery(db7).FirstAsync(x => x.ContractNum == nettingContractNum);
        var internalEntityContract = await taskInternalEntityContract;
        var invoiceContacts = await taskInvoiceContacts;
        var counterpartyName = await taskCounterpartyName;
        var internalEntityName = await taskInternalEntityName;
        var counterpartyCustomerNum = await taskCounterpartyCustomerNum;
        swQueries.Stop();
        Debug.WriteLine($"GetInvoiceItemsInternal-queries took {swQueries.ElapsedMilliseconds} ms");

        var swAfterQueries = Stopwatch.StartNew();
        var mainInvoice = new MainInvoice
        {
            Id = 0,
            InvoiceNum = string.Empty,
            Month = month,
            InvoiceDate = DateTime.Now.ToDateOnly(),
            InternalEntityId = internalEntityId,
            InternalEntity = internalEntityName,
            CounterpartyId = counterpartyId,
            Counterparty = counterpartyName,
            InvoiceTypeId = 1,
            InvoiceType = "Invoice",
            IsApproved = "no",
            CustomerNum = counterpartyCustomerNum,
            ContractPaymentTypeId = contract.PayToBankId,
            ContractPaymentType = contract.PayToBank == null ? null : contract.PayToBank.IsWire ? "Wire" : "ACH"
        };

        mainInvoice.DueDate = (new DueDateHelper()).GetDueDate();
        FillInternalEntityContactInfo(mainInvoice, internalEntityContract, invoiceContacts);
        FillPaymentInfo(mainInvoice, internalEntityContract, contract.PayToBankId);
        FillCounterpartyContactInfo(mainInvoice, contract, invoiceContacts);

        swAfterQueries.Stop();
        Debug.WriteLine($"GetInvoiceItemsInternal-after queries took {swAfterQueries.ElapsedMilliseconds} ms");

        return mainInvoice;
    }

    private static void FillInternalEntityContactInfo(MainInvoice mainInvoice, Contract? contract, IEnumerable<Contact> invoiceContacts)
    {
        mainInvoice.InternalEntityAddressLine1 = contract?.ContractAccounting?.InvoiceAddressLine1;
        mainInvoice.InternalEntityAddressLine2 = contract?.ContractAccounting?.InvoiceAddressLine2;
        mainInvoice.InternalEntityCity = contract?.ContractAccounting?.InvoiceCity;
        mainInvoice.InternalEntityZip = contract?.ContractAccounting?.InvoiceZip;
        mainInvoice.InternalEntityCountryId = contract?.ContractAccounting?.InvoiceCountryId;
        mainInvoice.InternalEntityCountry = contract?.ContractAccounting?.InvoiceCountry?.Name;
        mainInvoice.InternalEntityStateId = contract?.ContractAccounting?.InvoiceStateId;
        mainInvoice.InternalEntityState = contract?.ContractAccounting?.InvoiceState?.Name;
        mainInvoice.InternalEntityTelephoneNum = contract?.ContractAccounting?.InvoiceTelephoneNum;
        mainInvoice.InternalEntityFaxNum = contract?.ContractAccounting?.InvoiceFaxNum;
        mainInvoice.InternalEntityEmailAddress = contract?.ContractAccounting?.InvoiceEmailAddress;

        var invoiceContact = (
            from c in invoiceContacts
            where c.ContactCounterparties.Any(x => x.CounterpartyId == mainInvoice.InternalEntityId)
            select c
        ).LastOrDefault();

        var contactPhone = invoiceContact?.OfficePhone;
        var contactEmail = invoiceContact?.Email;
        var contactAttn = invoiceContact?.DisplayName;

        mainInvoice.ContactTelephoneNum = contactPhone ?? contract?.ContractAccounting?.InvoiceTelephoneNum;
        mainInvoice.ContactEmail = contactEmail ?? contract?.ContractAccounting?.InvoiceEmailAddress;
        mainInvoice.InternalEntityAttn = contactAttn ?? contract?.ContractAccounting?.InvoiceAttn;
    }

    private static void FillCounterpartyContactInfo(MainInvoice mainInvoice, Contract? contract, IEnumerable<Contact> invoiceContacts)
    {
        mainInvoice.CounterpartyAddressLine1 = contract?.ContractAccounting?.InvoiceAddressLine1;
        mainInvoice.CounterpartyAddressLine2 = contract?.ContractAccounting?.InvoiceAddressLine2;
        mainInvoice.CounterpartyCity = contract?.ContractAccounting?.InvoiceCity;
        mainInvoice.CounterpartyZip = contract?.ContractAccounting?.InvoiceZip;
        mainInvoice.CounterpartyCountryId = contract?.ContractAccounting?.InvoiceCountryId;
        mainInvoice.CounterpartyCountry = contract?.ContractAccounting?.InvoiceCountry?.Name;
        mainInvoice.CounterpartyStateId = contract?.ContractAccounting?.InvoiceStateId;
        mainInvoice.CounterpartyState = contract?.ContractAccounting?.InvoiceState?.Name;
        mainInvoice.CounterpartyTelephoneNum = contract?.ContractAccounting?.InvoiceTelephoneNum;
        mainInvoice.CounterpartyFaxNum = contract?.ContractAccounting?.InvoiceFaxNum;

        var counterpartyContacts = (
            from c in invoiceContacts
            where c.ContactCounterparties.Any(x => x.CounterpartyId == mainInvoice.CounterpartyId)
            select c
        ).ToList();

        var contactEmails = Util.String.ToCommaSeparatedString(true, counterpartyContacts.Select(x => x.Email).ToList());
        var contractDoEmail = contract?.ContractAccounting?.DoEmail ?? false;
        var contractEmails = contractDoEmail ? contract?.ContractAccounting?.InvoiceEmailAddress : null;
        var mergedEmails = Util.String.MergeCommaSeparatedStrings(true, contactEmails, contractEmails);

        var counterpartyAttn = counterpartyContacts.LastOrDefault()?.DisplayName;

        mainInvoice.CounterpartyEmailAddresses = mergedEmails;
        mainInvoice.CounterpartyAttn = counterpartyAttn ?? contract?.ContractAccounting?.InvoiceAttn;
    }

    private static void FillPaymentInfo(MainInvoice mainInvoice, Contract? contract, int? payToBankId)
    {
        mainInvoice.AccountingContactEmail = contract?.ContractAccounting?.InvoiceEmailAddress;
        mainInvoice.AccountingContactPhone = contract?.ContractAccounting?.InvoiceTelephoneNum;
        mainInvoice.AccountingContactName = contract?.ContractAccounting?.InvoiceAttn;

        if (!payToBankId.HasValue)
            return;

        switch (payToBankId.Value)
        {
            case (int)Enums.ContractPaymentType.ExCWire:
                mainInvoice.PaymentBankName = contract?.ContractExhibitC?.BankName;
                mainInvoice.PaymentCityState = contract?.ContractExhibitC?.CityState;
                mainInvoice.PaymentAbaNumWire = contract?.ContractExhibitC?.AbaNumWire;
                mainInvoice.PaymentAbaNumAch = contract?.ContractExhibitC?.AbaNumAch;
                mainInvoice.PaymentAccountName = contract?.ContractExhibitC?.AccountName;
                mainInvoice.PaymentForFurtherCreditTo = contract?.ContractExhibitC?.ForFurtherCreditTo;
                mainInvoice.PaymentCreditName = contract?.ContractExhibitC?.CreditName;
                mainInvoice.PaymentAccountNum = contract?.ContractExhibitC?.AccountNum;
                break;
            case (int)Enums.ContractPaymentType.ExCAch:
                mainInvoice.PaymentBankName = contract?.ContractExhibitC?.BankName;
                mainInvoice.PaymentCityState = contract?.ContractExhibitC?.CityState;
                mainInvoice.PaymentAbaNumWire = contract?.ContractExhibitC?.AbaNumWire;
                mainInvoice.PaymentAbaNumAch = contract?.ContractExhibitC?.AbaNumAch;
                mainInvoice.PaymentAccountName = contract?.ContractExhibitC?.AccountName;
                mainInvoice.PaymentForFurtherCreditTo = contract?.ContractExhibitC?.ForFurtherCreditTo;
                mainInvoice.PaymentCreditName = contract?.ContractExhibitC?.CreditName;
                mainInvoice.PaymentAccountNum = contract?.ContractExhibitC?.AccountNum;
                break;
            case (int)Enums.ContractPaymentType.ExCAlt1Wire:
                mainInvoice.PaymentBankName = contract?.ContractExhibitC?.Alt1BankName;
                mainInvoice.PaymentCityState = contract?.ContractExhibitC?.Alt1CityState;
                mainInvoice.PaymentAbaNumWire = contract?.ContractExhibitC?.Alt1AbaNumWire;
                mainInvoice.PaymentAbaNumAch = contract?.ContractExhibitC?.Alt1AbaNumAch;
                mainInvoice.PaymentAccountName = contract?.ContractExhibitC?.Alt1AccountName;
                mainInvoice.PaymentForFurtherCreditTo = contract?.ContractExhibitC?.Alt1ForFurtherCreditTo;
                mainInvoice.PaymentCreditName = contract?.ContractExhibitC?.Alt1CreditName;
                mainInvoice.PaymentAccountNum = contract?.ContractExhibitC?.Alt1AccountNum;
                break;
            case (int)Enums.ContractPaymentType.ExCAlt1Ach:
                mainInvoice.PaymentBankName = contract?.ContractExhibitC?.Alt1BankName;
                mainInvoice.PaymentCityState = contract?.ContractExhibitC?.Alt1CityState;
                mainInvoice.PaymentAbaNumWire = contract?.ContractExhibitC?.Alt1AbaNumWire;
                mainInvoice.PaymentAbaNumAch = contract?.ContractExhibitC?.Alt1AbaNumAch;
                mainInvoice.PaymentAccountName = contract?.ContractExhibitC?.Alt1AccountName;
                mainInvoice.PaymentForFurtherCreditTo = contract?.ContractExhibitC?.Alt1ForFurtherCreditTo;
                mainInvoice.PaymentCreditName = contract?.ContractExhibitC?.Alt1CreditName;
                mainInvoice.PaymentAccountNum = contract?.ContractExhibitC?.Alt1AccountNum;
                break;
            case (int)Enums.ContractPaymentType.ExCAlt2Wire:
                mainInvoice.PaymentBankName = contract?.ContractExhibitC?.Alt2BankName;
                mainInvoice.PaymentCityState = contract?.ContractExhibitC?.Alt2CityState;
                mainInvoice.PaymentAbaNumWire = contract?.ContractExhibitC?.Alt2AbaNumWire;
                mainInvoice.PaymentAbaNumAch = contract?.ContractExhibitC?.Alt2AbaNumAch;
                mainInvoice.PaymentAccountName = contract?.ContractExhibitC?.Alt2AccountName;
                mainInvoice.PaymentForFurtherCreditTo = contract?.ContractExhibitC?.Alt2ForFurtherCreditTo;
                mainInvoice.PaymentCreditName = contract?.ContractExhibitC?.Alt2CreditName;
                mainInvoice.PaymentAccountNum = contract?.ContractExhibitC?.Alt2AccountNum;
                break;
            case (int)Enums.ContractPaymentType.ExCAlt2Ach:
                mainInvoice.PaymentBankName = contract?.ContractExhibitC?.Alt2BankName;
                mainInvoice.PaymentCityState = contract?.ContractExhibitC?.Alt2CityState;
                mainInvoice.PaymentAbaNumWire = contract?.ContractExhibitC?.Alt2AbaNumWire;
                mainInvoice.PaymentAbaNumAch = contract?.ContractExhibitC?.Alt2AbaNumAch;
                mainInvoice.PaymentAccountName = contract?.ContractExhibitC?.Alt2AccountName;
                mainInvoice.PaymentForFurtherCreditTo = contract?.ContractExhibitC?.Alt2ForFurtherCreditTo;
                mainInvoice.PaymentCreditName = contract?.ContractExhibitC?.Alt2CreditName;
                mainInvoice.PaymentAccountNum = contract?.ContractExhibitC?.Alt2AccountNum;
                break;
            case (int)Enums.ContractPaymentType.AccWire:
                mainInvoice.PaymentBankName = contract?.ContractAccounting?.WireBankName;
                mainInvoice.PaymentCityState = contract?.ContractAccounting?.ChecksCity + ", " + contract?.ContractAccounting?.ChecksState;
                mainInvoice.PaymentAbaNumWire = contract?.ContractAccounting?.WireAbaNum;
                mainInvoice.PaymentAbaNumAch = contract?.ContractAccounting?.AchAbaNum;
                mainInvoice.PaymentAccountName = contract?.ContractAccounting?.AchAccountNum;
                mainInvoice.PaymentForFurtherCreditTo = contract?.ContractExhibitC?.ForFurtherCreditTo;
                mainInvoice.PaymentCreditName = contract?.ContractExhibitC?.CreditName;
                mainInvoice.PaymentAccountNum = contract?.ContractAccounting?.WireAccountNum;
                break;
            case (int)Enums.ContractPaymentType.AccAch:
                mainInvoice.PaymentBankName = contract?.ContractAccounting?.AchBank;
                mainInvoice.PaymentCityState = contract?.ContractAccounting?.ChecksCity + ", " + contract?.ContractAccounting?.ChecksState;
                mainInvoice.PaymentAbaNumWire = contract?.ContractAccounting?.WireAbaNum;
                mainInvoice.PaymentAbaNumAch = contract?.ContractAccounting?.AchAbaNum;
                mainInvoice.PaymentAccountName = contract?.ContractAccounting?.AchAccountNum;
                mainInvoice.PaymentForFurtherCreditTo = contract?.ContractExhibitC?.ForFurtherCreditTo;
                mainInvoice.PaymentCreditName = contract?.ContractExhibitC?.CreditName;
                mainInvoice.PaymentAccountNum = contract?.ContractAccounting?.AchAccountNum;
                break;
            default:
                break;
        }
    }

    private static MainInvoice ConvertDbItem(InvoiceCrude dbItem)
    {
        var item = new MainInvoice();
        item.Id = dbItem.Id;
        item.InvoiceNum = dbItem.InvoiceNum;
        item.Month = dbItem.Month;
        item.InternalEntityId = dbItem.InternalEntityId;
        item.InternalEntity = dbItem.InternalEntity.Name;
        item.CounterpartyId = dbItem.CounterpartyId;
        item.Counterparty = dbItem.Counterparty.Name;
        item.InvoiceTypeId = dbItem.InvoiceTypeId;
        item.InvoiceType = dbItem.InvoiceType.Name;
        item.InvoiceDate = dbItem.InvoiceDate;
        item.DueDate = dbItem.DueDate;
        item.CustomerNum = dbItem.CustomerNum;
        item.ContactName = dbItem.ContactName;
        item.ContactEmail = dbItem.ContactEmail;
        item.ContactTelephoneNum = dbItem.ContactPhone;
        item.ContractPaymentTypeId = dbItem.ContractPaymentTypeId;
        if (dbItem.ContractPaymentType != null)
            item.ContractPaymentType = dbItem.ContractPaymentType.IsWire ? "Wire" : "ACH";
        else
            item.ContractPaymentType = null;
        item.FileNameOriginal = dbItem.FileNameOriginal;
        item.FileNameOnDisk = dbItem.FileNameOnDisk;
        item.EmailedBy = dbItem.EmailedBy;
        item.EmailedByName = dbItem.EmailedByNavigation?.DisplayName;
        item.EmailedTime = dbItem.EmailedTime;
        item.InternalEntityCountryId = dbItem.InternalEntityCountryId;
        item.InternalEntityCountry = dbItem.InternalEntityCountry?.Name;
        item.InternalEntityStateId = dbItem.InternalEntityStateId;
        item.InternalEntityState = dbItem.InternalEntityState?.Name;
        item.InternalEntityAttn = dbItem.InternalEntityAttn;
        item.InternalEntityTelephoneNum = dbItem.InternalEntityTelephoneNum;
        item.InternalEntityEmailAddress = dbItem.InternalEntityEmailAddress;
        item.InternalEntityAddressLine1 = dbItem.InternalEntityAddressLine1;
        item.InternalEntityAddressLine2 = dbItem.InternalEntityAddressLine2;
        item.InternalEntityCity = dbItem.InternalEntityCity;
        item.InternalEntityZip = dbItem.InternalEntityZip;
        item.InternalEntityFaxNum = dbItem.InternalEntityFaxNum;
        item.CounterpartyAddressLine1 = dbItem.CounterpartyAddressLine1;
        item.CounterpartyAddressLine2 = dbItem.CounterpartyAddressLine2;
        item.CounterpartyFaxNum = dbItem.CounterpartyFaxNum;
        item.CounterpartyEmailAddresses = dbItem.CounterpartyEmailAddresses;
        item.CounterpartyTelephoneNum = dbItem.CounterpartyTelephoneNum;
        item.CounterpartyCity = dbItem.CounterpartyCity;
        item.CounterpartyZip = dbItem.CounterpartyZip;
        item.CounterpartyAttn = dbItem.CounterpartyAttn;
        item.CounterpartyCountryId = dbItem.CounterpartyCountryId;
        item.CounterpartyCountry = dbItem.CounterpartyCountry?.Name;
        item.CounterpartyStateId = dbItem.CounterpartyStateId;
        item.CounterpartyState = dbItem.CounterpartyState?.Name;
        item.PaymentBankName = dbItem.PaymentBankName;
        item.PaymentCityState = dbItem.PaymentCityState;
        item.PaymentAbaNumWire = dbItem.PaymentAbaNumWire;
        item.PaymentAbaNumAch = dbItem.PaymentAbaNumAch;
        item.PaymentAccountName = dbItem.PaymentAccountName;
        item.PaymentForFurtherCreditTo = dbItem.PaymentForFurtherCreditTo;
        item.PaymentCreditName = dbItem.PaymentCreditName;
        item.PaymentAccountNum = dbItem.PaymentAccountNum;
        item.AccountingContactName = dbItem.AccountingContactName;
        item.AccountingContactPhone = dbItem.AccountingContactPhone;
        item.AccountingContactEmail = dbItem.AccountingContactEmail;
        item.Subtotal = dbItem.Subtotal;
        item.SalesTaxRate = dbItem.SalesTaxRate;
        item.SalesTaxAmount = dbItem.SalesTaxAmount;
        item.GrandTotal = dbItem.GrandTotal;
        item.Notes = dbItem.Notes;
        item.TotalQuantity = dbItem.TotalQuantity;
        item.ActualVolume = dbItem.ActualVolume;
        item.UsageDiff = dbItem.UsageDiff;

        var hasDetailLines = dbItem.InvoiceCrudeLines.Count > 0;
        var allDetailLinesApproved = dbItem.InvoiceCrudeLines.All(x => x.ApprovedBy.HasValue);
        var isInvoiceApproved = hasDetailLines && allDetailLinesApproved;
        item.IsApproved = isInvoiceApproved ? "yes" : "no";

        return item;
    }

    public class NomActualTotals
    {
        public DateOnly Month { get; set; }
        public decimal TotalNomOrActualVol { get; set; }
        public decimal TotalNomOrActualAmount { get; set; }
        public int CounterpartyId { get; set; }
        public int InternalEntityId { get; set; }
    }

    //example usage:
    // var totalsByEntities = await GetNomActualTotalsByEntities(startMonth, endMonth);
    // totalsByEntities.TryGetValue((month, counterpartyId, internalEntityId), out var nomActualTotals);
    public static async Task<Dictionary<(DateOnly Month, int CounterpartyId, int InternalEntityId), NomActualTotals>> GetNomActualTotalsByEntities(DateOnly startMonth, DateOnly endMonth)
    {
        var totals = new List<NomActualTotals>();

        var valResults = await GetValuationResults(startMonth, endMonth);
        var valResultsByEntity = valResults.GroupBy(x => new
        {
            Month = Util.Date.FirstDayOfMonth(x.Day),
            x.DeliveryCounterpartyId,
            x.DeliveryInternalEntityId
        }
        ).ToList();

        foreach (var valGroup in valResultsByEntity)
        {
            var total = new NomActualTotals();
            var counterpartyId = valGroup.Key.DeliveryCounterpartyId;
            var internalEntityId = valGroup.Key.DeliveryInternalEntityId;
            if (counterpartyId == null || internalEntityId == null)
                continue;

            total.Month = valGroup.Key.Month;
            total.CounterpartyId = counterpartyId.Value;
            total.InternalEntityId = internalEntityId.Value;
            total.TotalNomOrActualVol = valGroup.Sum(x => x.DeliveryActualVol ?? x.DeliveryNomVol);
            total.TotalNomOrActualAmount = valGroup.Sum(x => (x.DeliveryActualVol ?? x.DeliveryNomVol) * (x.DeliveryContractPrice + x.DeliveryPriceAdj));
            totals.Add(total);
        }

        var totalsByEntities = totals.ToDictionary(x => (x.Month, x.CounterpartyId, x.InternalEntityId));
        return totalsByEntities;
    }

    private static async Task<List<PathValuationResult>> GetValuationResults(DateOnly startMonth, DateOnly endMonth)
    {
        var firstOfMonth = Util.Date.FirstDayOfMonth(startMonth);
        var lastOfMonth = Util.Date.LastDayOfMonth(endMonth);
        var valParams = new ValParams();

        valParams.TransactionTypeIds.Add((int)Enums.TransactionType.PhysicalCrudeOil);
        valParams.PositionDateRanges.Add(new DateRange(DateStyle.MonthRange, firstOfMonth, lastOfMonth));
        valParams.IncludeBasisInContractPrice = false;

        var val = new PathValuaterCrude();
        var results = (await val.GetValuationValues(valParams)).ToList();

        return results;
    }
}
