using Fast.Shared.Logic.RateControl;
using static Fast.Shared.Logic.ValuationByPath.PathValuaterCommon;

namespace Fast.Shared.Logic.ValuationByPath;

public class PathValuaterGas
{
    private ValParams valParams = new();
    private readonly Enums.Product product = Enums.Product.NaturalGas;
    private const string ticketNumPrefix = "GAS";
    private RateCalculator? rateCalculator;
    private PathValuaterCommon common = null!;

    public async Task<List<PathValuationResult>> GetValuationValues(ValParams valParams)
    {
        this.valParams = valParams;
        if (valParams.PositionDateRanges.Count == 0)
            throw new Exception("Valuation requires a position date range");

        SetDefaultValParams();

        // Start rate calculator loading and database queries in parallel
        var rateCalculatorTask = RateCalculator.GetInstanceAsync(product);
        var getActualsTask = GetRecentActualsByKeyAsync();
        var getTransfersTask = GetTransfersThatEndWithMarketsAsync();
        var getAllTransferNomsTask = GetAllTransferNomsByKeyAsync();
        var getDealValsTask = GetDealValsAsync();
        var getContractDataTask = GetContractDataAsync();

        // Wait for rate calculator first since GetSingleLegNomsAsync needs it
        rateCalculator = await rateCalculatorTask;
        common = new PathValuaterCommon(rateCalculator, Enums.ProductCategory.NaturalGasAndLng);

        // Now start SingleLegNoms query which needs the rateCalculator
        var getSingleLegNomsTask = GetSingleLegNomsAsync(getActualsTask);

        // Wait for all tasks to complete
        await Task.WhenAll(getActualsTask, getTransfersTask, getAllTransferNomsTask, getDealValsTask, getSingleLegNomsTask, getContractDataTask);

        // Get results
        var (actualsBySimpleKey, actualsByFullKey) = await getActualsTask;
        var transfersThatEndWithMarkets = await getTransfersTask;
        var allTransferNomsDic = await getAllTransferNomsTask;
        var dealVals = await getDealValsTask;
        var contractData = await getContractDataTask;

        // Process results
        var singleLegNoms = await getSingleLegNomsTask;
        var multiLegNoms = GetMultiLegNoms(transfersThatEndWithMarkets, allTransferNomsDic, actualsByFullKey);

        // Use List's capacity constructor for better performance when size is known
        var pathVals = new List<PathValuationResult>(singleLegNoms.Count + multiLegNoms.Count);
        pathVals.AddRange(singleLegNoms);
        pathVals.AddRange(multiLegNoms);

        SetFinalVolumes(pathVals, actualsBySimpleKey);
        SetDealContractValues(pathVals, contractData);
        SetMiscDealValues(pathVals, dealVals);
        SetContractPrices(pathVals, dealVals);
        SetPriceAdjustments(pathVals, dealVals);
        SetOverrideValues(pathVals, actualsBySimpleKey);
        PathValuaterCommon.SetTransportAdjustments(pathVals);
        SetInvoiceValues(pathVals);

        return pathVals;
    }

    private class DealContractData(int contractId, string? ourContractNum, string? theirContractNum)
    {
        public int ContractId { get; set; } = contractId;
        public string? OurContractNum { get; set; } = ourContractNum;
        public string? TheirContractNum { get; set; } = theirContractNum;
    }

    private async static Task<Dictionary<int, DealContractData>> GetContractDataAsync()
    {
        using var db = Main.CreateContext();
        var taskDealToContractDict = (
            from d in db.Deals
            join c in db.Contracts on d.NettingContractNumber equals c.ContractNum
            where d.NettingContractNumber != null
            select new { DealId = d.Id, Data = new DealContractData(c.Id, c.ContractNum, c.TheirContractNum) }
        ).Distinct().ToDictionaryAsync(x => x.DealId, x => x.Data);

        var dealToContractDict = await taskDealToContractDict;
        return dealToContractDict;
    }

    private static void SetPriceAdjustments(List<PathValuationResult> pathVals, Dictionary<(int DealId, DateOnly PositionStartDate), ValuationResult> dealVals)
    {
        foreach (var pathVal in pathVals)
        {
            if (pathVal.ReceiptDealId == null || pathVal.DeliveryDealId == null)
                continue;

            var valKeyReceipt = (pathVal.ReceiptDealId.Value, pathVal.Day);
            if (dealVals.TryGetValue(valKeyReceipt, out var valReceipt))
                pathVal.ReceiptPriceAdj =
                    (decimal)(valReceipt.BasisPrice +
                    valReceipt.PremOrDisc);

            var valKeyDelivery = (pathVal.DeliveryDealId.Value, pathVal.Day);
            if (dealVals.TryGetValue(valKeyDelivery, out var valDelivery))
                pathVal.DeliveryPriceAdj =
                    (decimal)(valDelivery.BasisPrice +
                    valDelivery.PremOrDisc);

            if (pathVal.IsNetback)
                pathVal.ReceiptPriceAdj = pathVal.DeliveryPriceAdj;
        }
    }

    private static void SetMiscDealValues(List<PathValuationResult> pathVals, Dictionary<(int DealId, DateOnly PositionStartDate), ValuationResult> dealVals)
    {
        foreach (var pathVal in pathVals)
        {
            if (pathVal.ReceiptDealId == null || pathVal.DeliveryDealId == null)
                continue;

            var valKeyReceipt = (pathVal.ReceiptDealId.Value, pathVal.Day);
            if (dealVals.TryGetValue(valKeyReceipt, out var valReceipt))
                pathVal.ReceiptBaseOrSwing = valReceipt.BaseOrSwingText;

            var valKeyDelivery = (pathVal.DeliveryDealId.Value, pathVal.Day);
            if (dealVals.TryGetValue(valKeyDelivery, out var valDelivery))
                pathVal.DeliveryBaseOrSwing = valDelivery.BaseOrSwingText;
        }
    }

    private static void SetContractPrices(List<PathValuationResult> pathVals, Dictionary<(int DealId, DateOnly PositionStartDate), ValuationResult> dealVals)
    {
        foreach (var pathVal in pathVals)
        {
            if (pathVal.ReceiptDealId == null || pathVal.DeliveryDealId == null)
                continue;

            var valKeyReceipt = (pathVal.ReceiptDealId.Value, pathVal.Day);
            if (dealVals.TryGetValue(valKeyReceipt, out var valReceipt))
                pathVal.ReceiptContractPrice = (decimal)valReceipt.ContractPrice;

            var valKeyDelivery = (pathVal.DeliveryDealId.Value, pathVal.Day);
            if (dealVals.TryGetValue(valKeyDelivery, out var valDelivery))
                pathVal.DeliveryContractPrice = (decimal)valDelivery.ContractPrice;

            if (pathVal.IsNetback)
                pathVal.ReceiptContractPrice = pathVal.DeliveryContractPrice;
        }
    }

    private static void SetOverrideValues(List<PathValuationResult> pathVals, Dictionary<ActualSimpleKey, ActualItem> actualsDic)
    {
        foreach (var pathVal in pathVals)
        {
            ActualSimpleKey receiptKey = new((int)Enums.ActualType.Buy, pathVal.SupplyNomId, pathVal.MarketNomId, pathVal.LastTransferDealId);
            if (actualsDic.TryGetValue(receiptKey, out var receiptActual))
            {
                if (receiptActual.TransportRate != null)
                {
                    pathVal.ReceiptTransportTotal = receiptActual.TransportRate.Value;
                    pathVal.IsReceiptTransportOverridden = true;
                }

                if (receiptActual.Price != null)
                {
                    pathVal.ReceiptContractPrice = receiptActual.Price.Value;
                    pathVal.IsReceiptContractPriceOverridden = true;
                }

                if (receiptActual.Adder != null)
                {
                    pathVal.ReceiptPriceAdj = receiptActual.Adder.Value;
                    pathVal.IsReceiptPriceAdjOverridden = true;
                }

                if (receiptActual.TneMeterId != null)
                {
                    pathVal.TneMeterId = receiptActual.TneMeterId;
                    pathVal.TneMeter = receiptActual.TneMeterName;
                }
            }

            ActualSimpleKey deliveryKey = new((int)Enums.ActualType.Sell, pathVal.SupplyNomId, pathVal.MarketNomId, pathVal.LastTransferDealId);
            if (actualsDic.TryGetValue(deliveryKey, out var deliveryActual))
            {
                if (deliveryActual.Price != null)
                {
                    pathVal.DeliveryContractPrice = deliveryActual.Price.Value;
                    pathVal.IsDeliveryContractPriceOverridden = true;
                }

                if (deliveryActual.Adder != null)
                {
                    pathVal.DeliveryPriceAdj = deliveryActual.Adder.Value;
                    pathVal.IsDeliveryPriceAdjOverridden = true;
                }
            }
        }
    }

    private async Task<(Dictionary<ActualSimpleKey, ActualItem>, Dictionary<ActuallFullKey, ActualItem>)> GetRecentActualsByKeyAsync()
    {
        using var db = Main.CreateContext();

        var actualsQuery = (
            from q in db.GasActuals.AsNoTracking()
            select new ActualItem
            {
                Date = q.SupplyNom.Date,
                ActualTypeId = q.ActualTypeId,
                SupplyNomId = q.SupplyNomId,
                MarketNomId = q.MarketNomId,
                LastTransferId = q.LastTransferId,
                Volume = q.Volume,
                Price = q.Price,
                Adder = q.Adder,
                TransportRate = q.TransportRate,
                IsLinked = q.IsLinked,
                SavedBy = q.SavedBy,
                SaveDate = q.SaveDate,
                TneMeterId = q.TneMeterId,
                TneMeterName = q.TneMeter == null ? null : q.TneMeter.Name,
                ReceiptDealId = q.SupplyNom.DealId,
                DeliveryDealId = q.MarketNom.DealId,
                ReceiptTransferDealId = q.SupplyNom.TransferDealId,
                DeliveryTransferDealId = q.MarketNom.TransferDealId,
                ReceiptMeterId = q.SupplyNom.MeterId,
                DeliveryMeterId = q.MarketNom.MeterId,
                ReceiptPointId = q.SupplyNom.PointId,
                DeliveryPointId = q.MarketNom.PointId
            }
        );
        actualsQuery = GetActualQueryWithFilters(actualsQuery, valParams);

        // Materialize once to avoid duplicate database round-trips
        var actualsList = await actualsQuery.ToListAsync();

        // Process both dictionaries from the in-memory list
        var actualsDicBySimpleKey = actualsList
            .GroupBy(q => new { q.ActualTypeId, q.SupplyNomId, q.MarketNomId, q.LastTransferId })
            .Select(g => g.OrderByDescending(x => x.SaveDate).First())
            .ToDictionary(x => new ActualSimpleKey(x.ActualTypeId, x.SupplyNomId, x.MarketNomId, x.LastTransferId));

        var actualsByFullKey = actualsList
            .GroupBy(q => new
            {
                q.Date,
                q.ActualTypeId,
                q.ReceiptDealId,
                q.DeliveryDealId,
                q.ReceiptTransferDealId,
                q.DeliveryTransferDealId,
                q.ReceiptMeterId,
                q.DeliveryMeterId,
                q.ReceiptPointId,
                q.DeliveryPointId,
                q.LastTransferId
            })
            .Select(g => g.OrderByDescending(x => x.SaveDate).First())
            .ToDictionary(x => new ActuallFullKey(
                x.Date,
                x.ActualTypeId,
                x.ReceiptDealId,
                x.DeliveryDealId,
                x.ReceiptTransferDealId,
                x.DeliveryTransferDealId,
                x.ReceiptMeterId,
                x.DeliveryMeterId,
                x.ReceiptPointId,
                x.DeliveryPointId,
                x.LastTransferId
            ));

        return (actualsDicBySimpleKey, actualsByFullKey);
    }

    private static IQueryable<ActualItem> GetActualQueryWithFilters(IQueryable<ActualItem> query, ValParams valParams)
    {
        var positionDateExp = Util.Date.GetDateRangeExpression<ActualItem>(valParams.PositionDateRanges, "Date", false);
        query = query.Where(positionDateExp);

        return query;
    }

    private async Task<List<PathValuationResult>> GetSingleLegNomsAsync(
        Task<(Dictionary<ActualSimpleKey, ActualItem>, Dictionary<ActuallFullKey, ActualItem>)> actualsTask)
    {
        using var db = Main.CreateContext();
        var singleLegNomsQuery = (
            from q in db.GasMarketSupplies.AsNoTracking()
            join receiptMeterProduct in DataHelper.RecentMeterProductsQueryable(db) on q.SupplyNom.MeterId equals receiptMeterProduct.MeterId
            where receiptMeterProduct.ProductId == (int)product
            join deliveryMeterProduct in DataHelper.RecentMeterProductsQueryable(db) on q.MarketNom.MeterId equals deliveryMeterProduct.MeterId
            where deliveryMeterProduct.ProductId == (int)product
            let receiptPipeObj = receiptMeterProduct.SourceZone!.Pipeline!
            let deliveryPipeObj = deliveryMeterProduct.SourceZone!.Pipeline!
            where q.SupplyNom.DealId != null
                && q.MarketNom.DealId != null
                && q.Volume != null && q.Volume != 0
            select new PathValuationResult
            {
                Day = q.Date,
                ReceiptDeal = q.SupplyNom.Deal!.TicketNum,
                ReceiptInternalEntity = q.SupplyNom.Deal!.InternalEntity!.Name,
                ReceiptCounterparty = q.SupplyNom.Deal!.Counterparty!.Name,
                ReceiptCounterpartyShort = q.SupplyNom.Deal!.Counterparty!.ShortName ?? "",
                ReceiptDealPurpose = q.SupplyNom.Deal!.DealPurpose != null ? q.SupplyNom.Deal!.DealPurpose!.Name ?? "" : "",
                ReceiptDealPurposeId = q.SupplyNom.Deal!.DealPurposeId,
                ReceiptPipe = receiptPipeObj.PipeShort ?? receiptPipeObj.Name!,
                ReceiptPoint = q.SupplyNom.Point != null ? q.SupplyNom.Point!.Name! : "",
                ReceiptMeter = q.SupplyNom.Meter!.Name,
                ReceiptPipeContract = q.SupplyNom.PipelineContract != null ? q.SupplyNom.PipelineContract!.ContractId : "",
                ReceiptPipeContractOwner = q.SupplyNom.PipelineContract != null && q.SupplyNom.PipelineContract!.ContractOwner != null ? q.SupplyNom.PipelineContract!.ContractOwner!.ShortName ?? q.SupplyNom.PipelineContract!.ContractOwner!.Name : "",
                ReceiptNomVol = q.Volume ?? 0,
                DeliveryDeal = q.MarketNom.Deal!.TicketNum,
                DeliveryInternalEntity = q.MarketNom.Deal!.InternalEntity!.Name,
                DeliveryCounterparty = q.MarketNom.Deal!.Counterparty!.Name,
                DeliveryCounterpartyShort = q.MarketNom.Deal!.Counterparty!.ShortName ?? "",
                DeliveryDealPurpose = q.MarketNom.Deal!.DealPurpose != null ? q.MarketNom.Deal!.DealPurpose!.Name ?? "" : "",
                DeliveryDealPurposeId = q.MarketNom.Deal!.DealPurposeId,
                DeliveryPipe = deliveryPipeObj.PipeShort ?? deliveryPipeObj.Name!,
                DeliveryPoint = q.MarketNom.Point!.Name!,
                DeliveryMeter = q.MarketNom.Meter!.Name,
                DeliveryPipeContract = "", //for any single nom, there is only one contract, so we'll set delivery later
                DeliveryPipeContractOwner = "",
                DeliveryNomVol = q.Volume ?? 0,
                ReceiptDealId = q.SupplyNom.DealId,
                ReceiptProductId = q.SupplyNom.Deal != null ? q.SupplyNom.Deal!.ProductId : null,
                ReceiptInternalEntityId = q.SupplyNom.Deal!.InternalEntityId,
                ReceiptCounterpartyId = q.SupplyNom.Deal!.CounterpartyId,
                ReceiptTransferDealId = q.SupplyNom.TransferDealId,
                ReceiptPipeId = receiptPipeObj.Id,
                ReceiptPointId = q.SupplyNom.PointId,
                ReceiptMeterId = q.SupplyNom.MeterId,
                ReceiptPipeContractId = q.SupplyNom.PipelineContractId,
                ReceiptPortfolioId = q.SupplyNom.Deal!.PortfolioId,
                DeliveryDealId = q.MarketNom.DealId,
                DeliveryProductId = q.MarketNom.Deal != null ? q.MarketNom.Deal!.ProductId : null,
                DeliveryInternalEntityId = q.MarketNom.Deal!.InternalEntityId,
                DeliveryCounterpartyId = q.MarketNom.Deal!.CounterpartyId,
                DeliveryTransferDealId = q.MarketNom.TransferDealId,
                DeliveryPipeId = deliveryPipeObj.Id,
                DeliveryPointId = q.MarketNom.PointId,
                DeliveryMeterId = q.MarketNom.MeterId,
                DeliveryPipeContractId = null, //for any single nom, there is only one contract, so we'll set delivery later
                NonJurisdictional = receiptPipeObj.PipelineTypeId == (int)Enums.PipelineType.NonJurisdictional,
                IsAgency = q.SupplyNom.Deal != null && q.SupplyNom.Deal!.Counterparty!.CounterpartyRelationships.Any(x => x.BusinessRelationshipId == (int)Enums.BusinessRelationship.Agency),
                IsNetback = q.SupplyNom.Deal != null && q.SupplyNom.Deal!.IsNetback,
                SupplyNomId = q.SupplyNomId,
                MarketNomId = q.MarketNomId,
                HasTransfers = false,
                TneMeterId = q.MarketNom.MeterId,
                TneMeter = q.MarketNom.Meter!.Name,
            }
        );

        singleLegNomsQuery = GetPathQueryWithFilters(singleLegNomsQuery, valParams);
        var singleLegNoms = await singleLegNomsQuery.ToListAsync() ?? new List<PathValuationResult>();

        // Now await the actuals (may already be complete by this point)
        var (_, actualsByFullKey) = await actualsTask;

        foreach (var normalNom in singleLegNoms)
        {
            var multiLegFinalIds = new MultiLegFinals()
            {
                FirstReceiptDealId = normalNom.ReceiptDealId!.Value,
                FirstReceiptMeterId = normalNom.ReceiptMeterId,
                FirstReceiptPointId = normalNom.ReceiptPointId!.Value,
                LastDeliveryDealId = normalNom.DeliveryDealId!.Value,
                LastDeliveryMeterId = normalNom.DeliveryMeterId,
                LastDeliveryPointId = normalNom.DeliveryPointId!.Value,
                LastTransferDealId = normalNom.DeliveryTransferDealId,
            };
            normalNom.ReceiptTransports.Add(common.GetTransportItem(normalNom, multiLegFinalIds, actualsByFullKey, null, null, Enums.ActualType.Sell));

            //for single leg noms, the delivery pipe contract is the same as the receipt pipe contract
            normalNom.DeliveryPipeContract = normalNom.ReceiptPipeContract;
            normalNom.DeliveryPipeContractOwner = normalNom.ReceiptPipeContractOwner;
            normalNom.DeliveryPipeContractId = normalNom.ReceiptPipeContractId;
        }

        return singleLegNoms;
    }

    private async Task<List<PathValuationResult>> GetTransfersThatEndWithMarketsAsync()
    {
        using var db = Main.CreateContext();
        List<PathValuationResult> transfersThatEndWithMarkets;

        var transfersThatEndWithMarketsQuery = (
            from q in db.GasMarketSupplies.AsNoTracking()
            join receiptMeterProduct in DataHelper.RecentMeterProductsQueryable(db) on q.SupplyNom.MeterId equals receiptMeterProduct.MeterId
            where receiptMeterProduct.ProductId == (int)product
            join deliveryMeterProduct in DataHelper.RecentMeterProductsQueryable(db) on q.MarketNom.MeterId equals deliveryMeterProduct.MeterId
            where deliveryMeterProduct.ProductId == (int)product
            let receiptPipeObj = receiptMeterProduct.SourceZone!.Pipeline!
            let deliveryPipeObj = deliveryMeterProduct.SourceZone!.Pipeline!
            where q.MarketNom.DealId != null
                && q.SupplyNom.TransferDealId != null
                && q.Volume != null && q.Volume != 0
            select new PathValuationResult
            {
                Day = q.Date,
                ReceiptDeal = q.SupplyNom.TransferDeal!.TicketNum!,
                ReceiptInternalEntity = "",
                ReceiptCounterparty = "",
                ReceiptCounterpartyShort = "",
                ReceiptDealPurpose = "",
                ReceiptDealPurposeId = null,
                ReceiptPipe = receiptPipeObj.PipeShort ?? receiptPipeObj.Name!,
                ReceiptPoint = q.SupplyNom.Point != null ? q.SupplyNom.Point!.Name! : "",
                ReceiptMeter = q.SupplyNom.Meter!.Name,
                ReceiptPipeContract = q.SupplyNom.PipelineContract != null ? q.SupplyNom.PipelineContract!.ContractId : "",
                ReceiptPipeContractOwner = q.SupplyNom.PipelineContract != null && q.SupplyNom.PipelineContract!.ContractOwner != null ? q.SupplyNom.PipelineContract!.ContractOwner!.ShortName ?? q.SupplyNom.PipelineContract!.ContractOwner!.Name : "",
                ReceiptNomVol = q.Volume ?? 0,
                DeliveryDeal = q.MarketNom.Deal!.TicketNum,
                DeliveryInternalEntity = q.MarketNom.Deal!.InternalEntity!.Name,
                DeliveryCounterparty = q.MarketNom.Deal!.Counterparty!.Name,
                DeliveryCounterpartyShort = q.MarketNom.Deal!.Counterparty!.ShortName ?? "",
                DeliveryDealPurpose = q.MarketNom.Deal!.DealPurpose != null ? q.MarketNom.Deal!.DealPurpose!.Name ?? "" : "",
                DeliveryDealPurposeId = q.MarketNom.Deal!.DealPurposeId,
                DeliveryPipe = deliveryPipeObj.PipeShort ?? deliveryPipeObj.Name!,
                DeliveryPoint = q.MarketNom.Point != null ? q.MarketNom.Point!.Name! : "",
                DeliveryMeter = q.MarketNom.Meter!.Name,
                DeliveryPipeContract = "", //for any single nom, there is only one contract, so we'll set delivery later
                DeliveryPipeContractOwner = "",
                DeliveryNomVol = q.Volume ?? 0,
                ReceiptDealId = q.SupplyNom.DealId,
                ReceiptProductId = q.SupplyNom.Deal != null ? q.SupplyNom.Deal!.ProductId : null,
                ReceiptInternalEntityId = null,
                ReceiptCounterpartyId = null,
                ReceiptTransferDealId = q.SupplyNom.TransferDealId,
                ReceiptPipeId = receiptPipeObj.Id,
                ReceiptPointId = q.SupplyNom.PointId,
                ReceiptMeterId = q.SupplyNom.MeterId,
                ReceiptPipeContractId = q.SupplyNom.PipelineContractId,
                ReceiptPortfolioId = q.SupplyNom.Deal != null ? q.SupplyNom.Deal!.PortfolioId : null,
                DeliveryDealId = q.MarketNom.DealId,
                DeliveryProductId = q.MarketNom.Deal != null ? q.MarketNom.Deal!.ProductId : null,
                DeliveryInternalEntityId = q.MarketNom.Deal!.InternalEntityId,
                DeliveryCounterpartyId = q.MarketNom.Deal!.CounterpartyId,
                DeliveryTransferDealId = q.MarketNom.TransferDealId,
                DeliveryPipeId = deliveryPipeObj.Id,
                DeliveryPointId = q.MarketNom.PointId,
                DeliveryMeterId = q.MarketNom.MeterId,
                DeliveryPipeContractId = null, //for any single nom, there is only one contract, so we'll set delivery later
                NonJurisdictional = receiptPipeObj.PipelineTypeId == (int)Enums.PipelineType.NonJurisdictional,
                IsAgency = q.SupplyNom.Deal != null && q.SupplyNom.Deal!.Counterparty!.CounterpartyRelationships.Any(x => x.BusinessRelationshipId == (int)Enums.BusinessRelationship.Agency),
                IsNetback = q.SupplyNom.Deal != null && q.SupplyNom.Deal!.IsNetback,
                SupplyNomId = q.SupplyNomId,
                MarketNomId = q.MarketNomId
            }
        );
        transfersThatEndWithMarketsQuery = GetPathQueryWithFilters(transfersThatEndWithMarketsQuery, valParams);
        transfersThatEndWithMarkets = await transfersThatEndWithMarketsQuery.ToListAsync() ?? new List<PathValuationResult>();
        return transfersThatEndWithMarkets;
    }

    private async Task<Dictionary<(DateOnly Day, string DeliveryDeal), PathValuationResult>> GetAllTransferNomsByKeyAsync()
    {
        using var db = Main.CreateContext();
        Dictionary<(DateOnly Day, string DeliveryDeal), PathValuationResult> allTransferNomsDic;

        var allTransferNomsQuery = (
            from q in db.GasMarketSupplies.AsNoTracking()
            join receiptMeterProduct in DataHelper.RecentMeterProductsQueryable(db) on q.SupplyNom.MeterId equals receiptMeterProduct.MeterId
            where receiptMeterProduct.ProductId == (int)product
            join deliveryMeterProduct in DataHelper.RecentMeterProductsQueryable(db) on q.MarketNom.MeterId equals deliveryMeterProduct.MeterId
            where deliveryMeterProduct.ProductId == (int)product
            let receiptPipeObj = receiptMeterProduct.SourceZone!.Pipeline!
            let deliveryPipeObj = deliveryMeterProduct.SourceZone!.Pipeline!
            where q.MarketNom.TransferDealId != null
            select new PathValuationResult
            {
                Day = q.Date,
                ReceiptDeal = q.SupplyNom.DealId != null ? q.SupplyNom.Deal!.TicketNum! : q.SupplyNom.TransferDeal!.TicketNum!,
                ReceiptInternalEntity = q.SupplyNom.Deal != null ? q.SupplyNom.Deal!.InternalEntity!.Name! : "",
                ReceiptCounterparty = q.SupplyNom.Deal != null ? q.SupplyNom.Deal!.Counterparty!.Name! : "",
                ReceiptCounterpartyShort = q.SupplyNom.Deal != null ? q.SupplyNom.Deal!.Counterparty!.ShortName ?? "" : "",
                ReceiptDealPurpose = q.SupplyNom.Deal!.DealPurpose != null ? q.SupplyNom.Deal!.DealPurpose!.Name ?? "" : "",
                ReceiptDealPurposeId = q.SupplyNom.Deal!.DealPurposeId,
                ReceiptPipe = receiptPipeObj.PipeShort ?? receiptPipeObj.Name!,
                ReceiptPoint = q.SupplyNom.Point != null ? q.SupplyNom.Point!.Name! : "",
                ReceiptMeter = q.SupplyNom.Meter!.Name,
                ReceiptPipeContract = q.SupplyNom.PipelineContract != null ? q.SupplyNom.PipelineContract!.ContractId : "",
                ReceiptPipeContractOwner = q.SupplyNom.PipelineContract != null && q.SupplyNom.PipelineContract!.ContractOwner != null ? q.SupplyNom.PipelineContract!.ContractOwner!.ShortName ?? q.SupplyNom.PipelineContract!.ContractOwner!.Name : "",
                ReceiptNomVol = q.Volume ?? 0,
                DeliveryDeal = q.MarketNom.TransferDeal!.TicketNum!,
                DeliveryInternalEntity = "",
                DeliveryCounterparty = "",
                DeliveryCounterpartyShort = "",
                DeliveryDealPurpose = "",
                DeliveryDealPurposeId = null,
                DeliveryPipe = deliveryPipeObj.PipeShort ?? deliveryPipeObj.Name!,
                DeliveryPoint = q.MarketNom.Point!.Name!,
                DeliveryMeter = q.MarketNom.Meter!.Name,
                DeliveryPipeContract = "", //for any single nom, there is only one contract, so we'll set delivery later
                DeliveryPipeContractOwner = "",
                DeliveryNomVol = q.Volume ?? 0,
                ReceiptDealId = q.SupplyNom.DealId,
                ReceiptProductId = q.SupplyNom.Deal != null ? q.SupplyNom.Deal!.ProductId : null,
                ReceiptInternalEntityId = q.SupplyNom.Deal != null ? q.SupplyNom.Deal!.InternalEntityId : null,
                ReceiptCounterpartyId = q.SupplyNom.Deal != null ? q.SupplyNom.Deal!.CounterpartyId : null,
                ReceiptTransferDealId = q.SupplyNom.TransferDealId,
                ReceiptPipeId = receiptPipeObj.Id,
                ReceiptPointId = q.SupplyNom.PointId,
                ReceiptMeterId = q.SupplyNom.MeterId,
                ReceiptPipeContractId = q.SupplyNom.PipelineContractId,
                ReceiptPortfolioId = q.SupplyNom.Deal != null ? q.SupplyNom.Deal!.PortfolioId : null,
                DeliveryDealId = q.MarketNom.DealId,
                DeliveryProductId = q.MarketNom.Deal != null ? q.MarketNom.Deal!.ProductId : null,
                DeliveryInternalEntityId = null,
                DeliveryCounterpartyId = null,
                DeliveryTransferDealId = q.MarketNom.TransferDealId,
                DeliveryPipeId = deliveryPipeObj.Id,
                DeliveryPointId = q.MarketNom.PointId,
                DeliveryMeterId = q.MarketNom.MeterId,
                DeliveryPipeContractId = null, //for any single nom, there is only one contract, so we'll set delivery later
                NonJurisdictional = receiptPipeObj.PipelineTypeId == (int)Enums.PipelineType.NonJurisdictional,
                IsAgency = q.SupplyNom.Deal != null && q.SupplyNom.Deal!.Counterparty!.CounterpartyRelationships.Any(x => x.BusinessRelationshipId == (int)Enums.BusinessRelationship.Agency),
                IsNetback = q.SupplyNom.Deal != null && q.SupplyNom.Deal!.IsNetback,
                SupplyNomId = q.SupplyNom.Id,
                MarketNomId = q.MarketNom.Id
            }
        );
        allTransferNomsQuery = GetPathQueryWithFilters(allTransferNomsQuery, valParams);
        allTransferNomsDic = await allTransferNomsQuery.ToDictionaryAsync(x => (x.Day, x.DeliveryDeal)) ?? new Dictionary<(DateOnly Day, string DeliveryDeal), PathValuationResult>();
        return allTransferNomsDic;
    }

    private static IQueryable<PathValuationResult> GetPathQueryWithFilters(IQueryable<PathValuationResult> query, ValParams valParams)
    {
        var positionDateExp = Util.Date.GetDateRangeExpression<PathValuationResult>(valParams.PositionDateRanges, "Day", false);
        query = query.Where(positionDateExp);

        return query.AsNoTracking();
    }

    private void SetDefaultValParams()
    {
        valParams.IncludeSosDeals = true;
        valParams.IncludeBasisInContractPrice = false;
        valParams.PositionDateRanges = valParams.PositionDateRanges.OrderBy(x => x.FromDate).ToList();
        if (!valParams.ProductIds.Contains((int)product))
            valParams.ProductIds.Add((int)product);
    }

    private async Task<Dictionary<(int DealId, DateOnly PositionStartDate), ValuationResult>> GetDealValsAsync()
    {
        var val = new Valuater();
        //ignore specific BuySell valParam since we always need both buys and sells for netback purchases
        valParams.BuySell = null;
        //ignore specific CounterpartyIds since we need values for both sides, receipt and delivery
        valParams.CounterpartyIds = new List<int>();
        //ignore specific PipelineIds since we need values for both sides, receipt and delivery
        valParams.PipelineIds = new List<int>();

        var vals = await val.GetValuationValues(valParams);
        var dealVals = vals.ToDictionary(x => (x.DealId.GetValueOrDefault(), x.PositionStartDate)) ?? new Dictionary<(int DealId, DateOnly PositionStartDate), ValuationResult>();
        return dealVals;
    }

    private List<PathValuationResult> GetMultiLegNoms(
        List<PathValuationResult> transfersThatEndWithMarkets,
        Dictionary<(DateOnly Day, string DeliveryDeal), PathValuationResult> allTransferNomsDic,
        Dictionary<ActuallFullKey, ActualItem> actualsByFullKey)
    {
        // Pre-allocate the list with a reasonable capacity to avoid resizing
        var multiLegNoms = new List<PathValuationResult>(transfersThatEndWithMarkets.Count);

        foreach (var transferNomEnd in transfersThatEndWithMarkets)
        {
            // start with the end of the transfer chain and work backwards
            // start with final real market delivery vol and gross up to producer receipt vol

            int legNum; //not needed but helps with debugging
            bool hasMoreTransfers;
            DateOnly date = transferNomEnd.Day;
            List<TransportItem> transportItems = new();

            string nextLegDeliveryDeal;
            PathValuationResult? transferNom;
            var multiLegFinals = new MultiLegFinals();

            //first loop just to find the beginning real receipt deal values
            legNum = 1;
            hasMoreTransfers = true;
            nextLegDeliveryDeal = transferNomEnd.ReceiptDeal;
            while (hasMoreTransfers)
            {
                if (!allTransferNomsDic.TryGetValue((date, nextLegDeliveryDeal), out transferNom))
                    break;

                legNum++;

                var isRealReceipt = transferNom.ReceiptDeal.StartsWith(ticketNumPrefix);
                if (isRealReceipt)
                {
                    multiLegFinals.FirstReceiptDealId = transferNom.ReceiptDealId!.Value;
                    multiLegFinals.FirstReceiptMeterId = transferNom.ReceiptMeterId;
                    multiLegFinals.FirstReceiptPointId = transferNom.ReceiptPointId!.Value;
                }
                else
                {
                    // continue with the next transfer by setting the delivery deal to the receipt deal
                    nextLegDeliveryDeal = transferNom.ReceiptDeal;
                    continue;
                }
                hasMoreTransfers = false;
            }

            //now loop to do calculations and create the multi-leg nom
            legNum = 1;
            hasMoreTransfers = true;
            nextLegDeliveryDeal = transferNomEnd.ReceiptDeal;
            multiLegFinals.LastDeliveryDealId = transferNomEnd.DeliveryDealId!.Value;
            multiLegFinals.LastDeliveryMeterId = transferNomEnd.DeliveryMeterId;
            multiLegFinals.LastDeliveryPointId = transferNomEnd.DeliveryPointId!.Value;
            multiLegFinals.LastTransferDealId = transferNomEnd.ReceiptTransferDealId;

            var transportItem = common.GetTransportItem(transferNomEnd, multiLegFinals, actualsByFullKey, null, null, Enums.ActualType.Sell);
            transportItems.Add(transportItem);
            while (hasMoreTransfers)
            {
                if (!allTransferNomsDic.TryGetValue((date, nextLegDeliveryDeal), out transferNom))
                    break;

                legNum++;

                var isRealReceipt = transferNom.ReceiptDeal.StartsWith(ticketNumPrefix);
                var actualType = isRealReceipt ? Enums.ActualType.Buy : Enums.ActualType.Transfer;
                transportItem = common.GetTransportItem(transferNom, multiLegFinals, actualsByFullKey, transportItem.NomVol, transportItem.NomOrActualVol, actualType);
                transportItems.Add(transportItem);

                if (!isRealReceipt)
                {
                    // continue with the next transfer by setting the delivery deal to the receipt deal
                    nextLegDeliveryDeal = transferNom.ReceiptDeal;
                    continue;
                }

                // here we have a real receipt and market volume, i.e. we can stop looking for more transfers
                transferNom.ReceiptTransports = transportItems;
                transferNom.ReceiptNomVol = transportItem.NomVol;
                transferNom.ReceiptTransferDealId = null;
                transferNom.DeliveryTransferDealId = null;

                transferNomEnd.LastTransferDealId = multiLegFinals.LastTransferDealId;
                transferNomEnd.ReceiptTransferDealId = null;
                transferNomEnd.DeliveryTransferDealId = null;

                var newMultiLegNom = GetNewMultiLegNom(transferNom, transferNomEnd);
                multiLegNoms.Add(newMultiLegNom);
                hasMoreTransfers = false;
            }
        }
        return multiLegNoms;
    }

    private static PathValuationResult GetNewMultiLegNom(PathValuationResult receiptNom, PathValuationResult deliveryNom)
    {
        // Use object initializer for better performance and readability
        return new PathValuationResult
        {
            Day = deliveryNom.Day,
            ReceiptDeal = receiptNom.ReceiptDeal,
            ReceiptInternalEntity = receiptNom.ReceiptInternalEntity,
            ReceiptCounterparty = receiptNom.ReceiptCounterparty,
            ReceiptCounterpartyShort = receiptNom.ReceiptCounterpartyShort,
            ReceiptDealPurpose = receiptNom.ReceiptDealPurpose,
            ReceiptDealPurposeId = receiptNom.ReceiptDealPurposeId,
            ReceiptPipe = receiptNom.ReceiptPipe,
            ReceiptPoint = receiptNom.ReceiptPoint,
            ReceiptMeter = receiptNom.ReceiptMeter,
            ReceiptPipeContract = receiptNom.ReceiptPipeContract,
            ReceiptPipeContractOwner = receiptNom.ReceiptPipeContractOwner,
            ReceiptNomVol = receiptNom.ReceiptNomVol,
            DeliveryDeal = deliveryNom.DeliveryDeal,
            DeliveryInternalEntity = deliveryNom.DeliveryInternalEntity,
            DeliveryCounterparty = deliveryNom.DeliveryCounterparty,
            DeliveryCounterpartyShort = deliveryNom.DeliveryCounterpartyShort,
            DeliveryDealPurpose = deliveryNom.DeliveryDealPurpose,
            DeliveryDealPurposeId = deliveryNom.DeliveryDealPurposeId,
            DeliveryPipe = deliveryNom.DeliveryPipe,
            DeliveryPoint = deliveryNom.DeliveryPoint,
            DeliveryMeter = deliveryNom.DeliveryMeter,
            DeliveryPipeContract = deliveryNom.ReceiptPipeContract,  //we use receipt of the delivery nom since pipe contract is always tied to supply (not a market)
            DeliveryPipeContractOwner = deliveryNom.ReceiptPipeContractOwner,
            DeliveryNomVol = deliveryNom.DeliveryNomVol,
            ReceiptDealId = receiptNom.ReceiptDealId,
            ReceiptProductId = receiptNom.ReceiptProductId,
            ReceiptInternalEntityId = receiptNom.ReceiptInternalEntityId,
            ReceiptCounterpartyId = receiptNom.ReceiptCounterpartyId,
            ReceiptTransferDealId = receiptNom.ReceiptTransferDealId,
            ReceiptPipeId = receiptNom.ReceiptPipeId,
            ReceiptPointId = receiptNom.ReceiptPointId,
            ReceiptMeterId = receiptNom.ReceiptMeterId,
            ReceiptPipeContractId = receiptNom.ReceiptPipeContractId,
            ReceiptPortfolioId = receiptNom.ReceiptPortfolioId,
            DeliveryDealId = deliveryNom.DeliveryDealId,
            DeliveryProductId = deliveryNom.DeliveryProductId,
            DeliveryInternalEntityId = deliveryNom.DeliveryInternalEntityId,
            DeliveryCounterpartyId = deliveryNom.DeliveryCounterpartyId,
            DeliveryTransferDealId = deliveryNom.DeliveryTransferDealId,
            DeliveryPipeId = deliveryNom.DeliveryPipeId,
            DeliveryPointId = deliveryNom.DeliveryPointId,
            DeliveryMeterId = deliveryNom.DeliveryMeterId,
            DeliveryPipeContractId = deliveryNom.ReceiptPipeContractId, //we use receipt of the delivery nom since pipe contract is always tied to supply (not a market)
            ReceiptTransportTotal = receiptNom.ReceiptTransportTotal,
            ReceiptTransports = receiptNom.ReceiptTransports,
            NonJurisdictional = receiptNom.NonJurisdictional,
            IsAgency = receiptNom.IsAgency,
            IsNetback = receiptNom.IsNetback,
            SupplyNomId = receiptNom.SupplyNomId,
            MarketNomId = deliveryNom.MarketNomId,
            LastTransferDealId = deliveryNom.LastTransferDealId,
            HasTransfers = true,
            TneMeterId = deliveryNom.DeliveryMeterId,
            TneMeter = deliveryNom.DeliveryMeter
        };
    }

    private static void SetDealContractValues(List<PathValuationResult> pathVals, Dictionary<int, DealContractData> dealToContractDict)
    {
        foreach (var pathVal in pathVals)
        {
            if (pathVal.DeliveryDealId.HasValue && dealToContractDict.TryGetValue(pathVal.DeliveryDealId.Value, out DealContractData? contractData))
            {
                pathVal.DeliveryInternalContractNum = contractData.OurContractNum;
                pathVal.DeliveryCounterpartyContractNum = contractData.TheirContractNum;
                pathVal.DeliveryDealContractId = contractData.ContractId;
            }
        }
    }

    private static void SetFinalVolumes(List<PathValuationResult> pathVals, Dictionary<ActualSimpleKey, ActualItem> actualsDic)
    {
        foreach (var pathVal in pathVals)
        {
            ActualSimpleKey receiptKey = new((int)Enums.ActualType.Buy, pathVal.SupplyNomId, pathVal.MarketNomId, pathVal.LastTransferDealId);
            if (actualsDic.TryGetValue(receiptKey, out var receiptActual))
                pathVal.ReceiptActualVol = receiptActual.Volume;

            ActualSimpleKey deliveryKey = new((int)Enums.ActualType.Sell, pathVal.SupplyNomId, pathVal.MarketNomId, pathVal.LastTransferDealId);
            if (actualsDic.TryGetValue(deliveryKey, out var deliveryActual))
                pathVal.DeliveryActualVol = deliveryActual.Volume;

            pathVal.ReceiptVol = pathVal.ReceiptActualVol ?? pathVal.ReceiptNomVol;
            pathVal.DeliveryVol = pathVal.DeliveryActualVol ?? pathVal.DeliveryNomVol;
        }
    }

    private static void SetInvoiceValues(List<PathValuationResult> pathVals)
    {
        foreach (var pathVal in pathVals)
        {
            pathVal.ReceiptPurchasePrice = pathVal.ReceiptContractPrice + pathVal.ReceiptPriceAdj;
            pathVal.ReceiptInvoicePrice = pathVal.ReceiptContractPrice + pathVal.ReceiptPriceAdj - pathVal.ReceiptTransportTotal;
            pathVal.ReceiptInvoiceAmount = pathVal.ReceiptInvoicePrice * pathVal.ReceiptVol;

            pathVal.DeliveryInvoicePrice = pathVal.DeliveryContractPrice + pathVal.DeliveryPriceAdj;
            pathVal.DeliveryInvoiceAmount = pathVal.DeliveryInvoicePrice * pathVal.DeliveryVol;
        }
    }
}
