using System.Diagnostics;
using Fast.Logic.RateControl;
using Fast.Web.Logic;
using static Fast.Models.Enums;
using static Fast.Web.Logic.SosCrudeHelper;
using RequiredData = Fast.Models.Crude.RequiredData;
using SosItem = Fast.Models.Crude.SosItem;
using SosTransferData = Fast.Models.Crude.SosTransferData;
using SosTransferItem = Fast.Models.Crude.SosTransferItem;
using SosTransferSet = Fast.Models.Crude.SosTransferSet;

namespace Fast.Web.Controllers;

[Authorize]
[ApiController]
[Route("api/[controller]")]
public class SosCrudeTransfersController : ODataController
{
    private readonly MyDbContext db;
    private readonly IWebHostEnvironment env;

    public SosCrudeTransfersController(MyDbContext db, IWebHostEnvironment env)
    {
        this.db = db;
        this.env = env;
    }

    private async Task<Deal> GetDeal(int dealId)
    {
        var deal =
            await db.Deals
            .Include(x => x.Counterparty)
            .Include(x => x.PhysicalDealType)
            .Where(x => x.Id == dealId).FirstAsync();

        return deal;
    }

    /// <summary>
    /// gives you the receipt points (buy deals) and delivery points (sell deal) for a deal
    /// </summary>
    private async Task<List<int>> GetPointIds(int dealId)
    {
        var pointIds = await (
            from x in db.PointSourceDeliveries
            where x.DealId == dealId
            select x.PointId
        ).Distinct().ToListAsync();

        return pointIds;
    }

    /// <summary>
    /// gives you the receipt meters (buy deals) and delivery meters (sell deal) for a deal
    /// </summary>
    private async Task<List<int>> GetMeterIds(int dealId)
    {
        var pointIds = await GetPointIds(dealId);

        var meterIds = await (
            from x in db.VwMeterPointCrudes
            where x.PointId != null && pointIds.Contains(x.PointId.Value)
            select x.MeterId!.Value
        ).Distinct().ToListAsync();

        return meterIds;
    }

    [Permission("SOS Crude Nomination", PermissionType.View)]
    [Route("[action]")]
    public async Task<IActionResult> GetRequiredData(int dealId)
    {
        var results = await GetRequiredDataInternal(dealId);
        return results;
    }

    [Permission("SOS Crude Nomination", PermissionType.View)]
    [Route("[action]")]
    public async Task<IActionResult> GetRequiredDataForTransferDeal(int transferDealId, DateOnly nomDate)
    {
        var dealId = await GetSourceDealId(transferDealId, nomDate);

        if (dealId == null)
            throw new Exception($"Source deal for TransferDealID {transferDealId} not found.");

        var results = await GetRequiredDataInternal(dealId.Value, nomDate);
        return results;
    }

    [Permission("SOS Crude Nomination", PermissionType.View)]
    [Route("[action]")]
    public async Task<IActionResult> GetRequiredDataForActualTneMeter(ActualItem item)
    {
        var dealId = await GetSourceDealIdBySupplyNom(item.SupplyNomId, item.NomDate);

        if (dealId == null)
            throw new Exception($"Source deal for SupplyNomId/NomDate {item.SupplyNomId}/{item.NomDate} not found.");

        var results = await GetRequiredDataInternal(dealId.Value, item.NomDate, item);
        return results;
    }

    private async Task<IActionResult> GetRequiredDataInternal(
        int dealId,
        DateOnly? nomDate = null,
        ActualItem? actualitem = null)
    {
        var deal = await GetDeal(dealId);
        var pipelineId = deal.PipelineId.GetValueOrDefault();
        if (nomDate == null)
            nomDate = deal.StartDate.GetValueOrDefault();
        var deliveryPointId = (await GetPointIds(dealId)).FirstOrDefault();

        var t1 = new SosCrudeController(db, env).GetRequiredDataTyped(User);
        var t2 = new SosCrudeSettingController(db).GetSettingsTyped(User, pipelineId, true);
        var t3 = new SosCrudeController(db, env).GetDataSetTyped(User, pipelineId, nomDate.Value);
        var t4 = GetTneMeter(actualitem);

        await Task.WhenAll(t1, t2, t3, t4);
        if (t3.Result == null)
        {
            return BadRequest("Rate calculator is null");
        }

        var requiredData = new RequiredData();
        requiredData.HasModifyPermission = t1.Result.HasModifyPermission;
        requiredData.Counterparties = t1.Result.Counterparties;
        requiredData.Pipelines = t1.Result.Pipelines;
        requiredData.Points = t1.Result.Points;
        requiredData.Meters = t1.Result.Meters;
        requiredData.AutoSelectPipelineId = t1.Result.AutoSelectPipelineId;

        var results = new SosTransferData();
        results.RequiredData = requiredData;
        results.SosSettingItem = t2.Result.PipeSettings;
        results.SosDataSet = t3.Result;
        results.NomDate = nomDate.Value;
        results.PipelineId = pipelineId;
        results.PointId = deliveryPointId;
        results.TneMeterId = t4.Result;

        return Ok(results);
    }

    [Permission("SOS Crude Nomination", PermissionType.View)]
    [Route("[action]")]
    public async Task<IActionResult> GetTransferSet(SosItem sourceItem, DateOnly nomDate)
    {
        var transferSet = await GetTransferSetInternal(sourceItem, nomDate);
        return Ok(transferSet);
    }

    private async Task<SosTransferSet?> GetTransferSetForDealInternal(int dealId, DateOnly? nomDate = null)
    {
        var deal = await GetDeal(dealId);
        if (nomDate == null)
            nomDate = deal.StartDate.GetValueOrDefault();
        var meterIds = await GetMeterIds(dealId);

        SosItem sourceItem = new();
        sourceItem.SourceTicket = deal.TicketNum ?? string.Empty;
        sourceItem.SourceMeterIds = meterIds;
        sourceItem.SupplyCounterpartyId = deal.CounterpartyId.GetValueOrDefault();
        sourceItem.SourceDealVolume = deal.Volume.GetValueOrDefault();
        sourceItem.SourceDealProduct = deal.ProductId;

        var transferSet = await GetTransferSetInternal(sourceItem, nomDate.Value);

        sourceItem.ReceiptMeter = GetReceiptMeterName(transferSet);
        sourceItem.SupplyCounterparty = deal.Counterparty?.Name ?? string.Empty;
        sourceItem.DealType = deal.PhysicalDealType?.Name ?? string.Empty;

        return transferSet;
    }

    [Permission("T&E Deduction Meter", PermissionType.Standard)]
    [Route("[action]")]
    public async Task<IActionResult> GetTransferSetForDeal(int dealId)
    {
        var result = await GetTransferSetForDealInternal(dealId);
        return Ok(result);
    }

    /// <summary>
    /// finds the source DealID for a given TransferDealID and Nomination Date.
    /// </summary>
    private static async Task<int?> GetSourceDealId(int transferDealId, DateOnly nomDate)
    {
        int? sourceDealId = null;

        using var db1 = Main.CreateContext();
        var taskMarkets = (
            from m in db1.CrudeMarkets
                .Include(x => x.TransferDeal)
            where m.Date == nomDate
                && (m.TransferDeal != null && m.TransferDeal.CrudePathId != null)
            select new
            {
                MarketNomId = m.Id,
                m.TransferDealId
            }
        ).ToListAsync();

        using var db2 = Main.CreateContext();
        var taskMarketSupplies = (
            from ms in db2.CrudeMarketSupplies
                .Include(x => x.MarketNom)
                    .ThenInclude(x => x.TransferDeal)
            where ms.Date == nomDate
                && ms.MarketNom.Date == nomDate
                && ms.MarketNom.TransferDeal != null
                && ms.MarketNom.TransferDeal.CrudePathId != null
            select new
            {
                ms.SupplyNomId,
                ms.MarketNomId
            }
        ).ToListAsync();

        using var db3 = Main.CreateContext();
        var taskSupplies = (
            from s in db3.CrudeSupplies
            where s.Date == nomDate
            select new
            {
                SupplyNomId = s.Id,
                s.TransferDealId,
                s.DealId
            }
        ).ToListAsync();

        var markets = await taskMarkets;
        var marketSupplies = await taskMarketSupplies;
        var supplies = await taskSupplies;

        // to find the source DealId for a given TransferDealId, we need to search in this order:
        // market -> marketSupply -> supply
        // supply will give us a DealId or TransferDealId.
        // if DealId is not found in supply, then we use the new TransferDealId to find market and search again.

        // step #1:  look at markets on transferDealId to find MarketNomId.
        var market = markets.FirstOrDefault(x => x.TransferDealId == transferDealId);

        while (market != null)
        {
            // step #2:  look at marketSupplies on MarketNomId to find SupplyNomId.
            var marketSupply = marketSupplies.FirstOrDefault(ms => ms.MarketNomId == market.MarketNomId);
            if (marketSupply != null)
            {
                // step #3:  look at supplies on SupplyNomId.
                // use DealId if not null and stop searching.  otherwise use the new TransferDealId to find market and search again
                var supply = supplies.FirstOrDefault(s => s.SupplyNomId == marketSupply.SupplyNomId);
                if (supply?.DealId != null)
                {
                    sourceDealId = supply.DealId.Value;
                    break;
                }
                else if (supply?.TransferDealId != null)
                {
                    market = markets.FirstOrDefault(x => x.TransferDealId == supply.TransferDealId.Value);
                    continue;
                }
            }

            // stop searching if it ever gets here, to prevent an infinite loop.
            // if it gets here, then there's somehow a TransferDealId that doesn't have a source deal.
            // this should never happen, but just in case.
            market = null;
            break;
        }

        return sourceDealId;
    }

    private static async Task<int?> GetSourceDealIdBySupplyNom(int supplyNomId, DateOnly nomDate)
    {
        int? sourceDealId = null;

        using var db1 = Main.CreateContext();
        var taskMarkets = (
            from m in db1.CrudeMarkets
                .Include(x => x.TransferDeal)
            where m.Date == nomDate
                && (m.TransferDeal != null && m.TransferDeal.CrudePathId != null)
            select new
            {
                MarketNomId = m.Id,
                m.TransferDealId
            }
        ).ToListAsync();

        using var db2 = Main.CreateContext();
        var taskMarketSupplies = (
            from ms in db2.CrudeMarketSupplies
                .Include(x => x.MarketNom)
                    .ThenInclude(x => x.TransferDeal)
            where ms.Date == nomDate
                && ms.MarketNom.Date == nomDate
                && ms.MarketNom.TransferDeal != null
                && ms.MarketNom.TransferDeal.CrudePathId != null
            select new
            {
                ms.SupplyNomId,
                ms.MarketNomId
            }
        ).ToListAsync();

        using var db3 = Main.CreateContext();
        var taskSupplies = (
            from s in db3.CrudeSupplies
            where s.Date == nomDate
            select new
            {
                SupplyNomId = s.Id,
                s.TransferDealId,
                s.DealId
            }
        ).ToListAsync();

        var markets = await taskMarkets;
        var marketSupplies = await taskMarketSupplies;
        var supplies = await taskSupplies;

        int loopCount = 0;
        const int maxSearchDepth = 9;

        var supply = supplies.FirstOrDefault(s => s.SupplyNomId == supplyNomId);

        while (supply != null && loopCount < maxSearchDepth)
        {
            if (supply.DealId != null)
            {
                sourceDealId = supply.DealId.Value;
                break;
            }
            else if (supply.TransferDealId != null)
            {
                var market = markets.FirstOrDefault(x => x.TransferDealId == supply.TransferDealId.Value);
                if (market != null)
                {
                    var marketSupply = marketSupplies.FirstOrDefault(ms => ms.MarketNomId == market.MarketNomId);
                    if (marketSupply != null)
                    {
                        supply = supplies.FirstOrDefault(s => s.SupplyNomId == marketSupply.SupplyNomId);
                        loopCount++;
                        continue;
                    }
                }
            }

            // Stop searching if it ever gets here, to prevent an infinite loop.
            // If it gets here, then there's somehow a supplyNomId that doesn't have a source deal.
            // This should never happen, but just in case.
            supply = null;
            break;
        }

        return sourceDealId;
    }

    [Permission("SOS Crude Nomination", PermissionType.View)]
    [Route("[action]")]
    public async Task<IActionResult> GetTransferSetForTransferDeal(int transferDealId, DateOnly nomDate)
    {
        var dealId = await GetSourceDealId(transferDealId, nomDate);

        if (dealId == null)
            throw new Exception($"Source deal for TransferDealID {transferDealId} not found.");

        var results = await GetTransferSetForDealInternal(dealId.Value, nomDate);

        FilterForTransferDeal(results, transferDealId);

        return Ok(results);
    }

    [Permission("SOS Crude Nomination", PermissionType.View)]
    [Route("[action]")]
    public async Task<IActionResult> GetTransferSetForTneMeter(ActualItem item)
    {
        var dealId = await GetSourceDealIdBySupplyNom(item.SupplyNomId, item.NomDate);

        if (dealId == null)
            throw new Exception($"Source deal for SupplyNomId/NomDate {item.SupplyNomId}/{item.NomDate} not found.");

        var results = await GetTransferSetForDealInternal(dealId.Value, item.NomDate);
        return Ok(results);
    }

    /// <summary>
    /// filters out items that aren't for the given transferDealId
    /// </summary>
    private static void FilterForTransferDeal(SosTransferSet? results, int transferDealId)
    {
        var transferPathGuid = (
            from q in results?.Items
            where q.MarketTransferDealId == transferDealId
                || q.SupplyTransferDealId == transferDealId
            select q.PathGuid
        ).FirstOrDefault();

        if (results != null && transferPathGuid != null)
            results.Items = results.Items.Where(x => x.PathGuid == transferPathGuid).ToList();
    }

    private static string GetReceiptMeterName(SosTransferSet? transferSet)
    {
        var value = string.Empty;

        if (transferSet == null)
        {
            return value;
        }

        var receiptMeterIds = transferSet.Items.Select(x => x.ReceiptMeterId).Distinct().ToList();
        if (receiptMeterIds.Count > 1)
        {
            value = "Multiple";
        }
        else
        {
            var transferItem = transferSet.Items.Where(x => x.ReceiptMeterId != 0).FirstOrDefault();
            value = transferItem?.ReceiptMeter ?? string.Empty;
        }

        return value;
    }

    /// <summary>
    /// Gets the transfer set for a given source item and nomination date.
    /// A transfer set is a list of transfer items, perhaps from multiple paths that start at different sources.
    /// Each path is assigned a path guid which remains the same for all the legs of the path.
    /// The transfer set also includes the pipe contracts for the path.
    /// The path names are also set for each transfer item.
    /// The source item (SosItem) is also included in the transfer set.
    /// </summary>
    private async Task<SosTransferSet> GetTransferSetInternal(SosItem sourceItem, DateOnly nomDate)
    {
        var productId = (int)Enums.Product.CrudeOil;

        var sw = Stopwatch.StartNew();

        var userId = Util.GetAppUserId(User);
        List<SosTransferItem> items = [];
        List<SosPipeContractInfo> pipeContracts = [];
        RateCalculator? rateCalculator = null;

        List<Task> tasks =
        [
            Task.Run(async () => { rateCalculator = await RateCalculator.GetInstanceAsync(Enums.Product.CrudeOil); }),
        ];
        await Task.WhenAll(tasks);

        if (rateCalculator == null)
            throw new Exception("RateCalculator is null");

        List<int> sourceMeterIds = sourceItem.SourceMeterId.HasValue
            ? [sourceItem.SourceMeterId.Value]
            : sourceItem.SourceMeterIds;

        var firstLegMktSupIds = await (
            from ms in db.CrudeMarketSupplies
            where ms.Date == nomDate && ms.SupplyNom.Deal != null
                && ms.SupplyNom.Deal.TicketNum == sourceItem.SourceTicket
                && ms.MarketNom.TransferDealId != null
                && sourceMeterIds.Contains(ms.SupplyNom.MeterId.GetValueOrDefault())
            select ms.Id
        ).ToListAsync();

        int pathNum = 1;
        foreach (var firstLegMktSupId in firstLegMktSupIds)
        {
            var nextSupplyTransferId = 0;
            int legNum = 1;
            SosTransferItem? previousLeg = null;
            string pathGuid = Guid.NewGuid().ToString("N")[..8];

            while (nextSupplyTransferId != -1)
            {
                List<int> mktSupIds = [];
                if (nextSupplyTransferId == 0)
                    mktSupIds.Add(firstLegMktSupId);
                else
                    mktSupIds = await (
                        from ms in db.CrudeMarketSupplies
                        where ms.Date == nomDate && ms.SupplyNom.TransferDealId == nextSupplyTransferId
                        select ms.Id
                    ).ToListAsync();

                if (mktSupIds.Count == 0)
                {
                    //MarketSupply not found
                    //if next MarketSupply not found then we don't have any volume and this is the last leg, but we still
                    //need to get the supply values and add this leg to the list
                    var pathLeg = await (
                        from s in db.CrudeSupplies
                        let supplyDealNum = s.Deal != null ? s.Deal.TicketNum : null
                        let supplyTransferNum = s.TransferDeal != null ? s.TransferDeal.TicketNum : null
                        let supplyMeter = s.Meter
                        where s.Date == nomDate && s.TransferDealId == nextSupplyTransferId
                        let meterProduct = supplyMeter.MeterProducts
                            .Where(x => x.ProductId == productId && x.EffectiveDate <= nomDate).OrderByDescending(x => x.EffectiveDate).FirstOrDefault()
                        let zone = meterProduct != null ? meterProduct.SourceZone : null
                        let pipe = zone != null ? zone.Pipeline : null
                        let pipeId = pipe != null ? pipe.Id : 0
                        let meterNumber = (meterProduct != null ? meterProduct.Number : null) ?? ""
                        select new
                        {
                            supplyTicket = supplyDealNum ?? supplyTransferNum,
                            supplyDealId = s.DealId,
                            receiptPipeId = pipeId,
                            receiptPipe = pipe != null ? pipe.Name : "",
                            receiptPointId = s.PointId,
                            receiptMeterId = s.MeterId.GetValueOrDefault(),
                            supplyTransferDealId = s.TransferDealId,
                            receiptMeter = supplyMeter.Name + "/" + meterNumber,
                            pipeContractId = s.PipelineContractId,
                            marketTransferMeterMapId = 0,
                            activityNum = s.ActNumber,
                            supplyNotes = s.Comment,
                            supplySourceNotes = s.SourceNotes,
                            isEnteredFromSos = s.Deal != null && s.Deal!.DealStatusId == (int)Enums.DealStatus.EnteredFromSOS
                        }
                    ).AsNoTracking().FirstAsync();

                    var f = new SosTransferItem();
                    f.Guid = Guid.NewGuid().ToString("N")[..8];
                    f.PathGuid = pathGuid;
                    f.PathLegNum = legNum;
                    f.PathName = $"Path {pathNum}: Unknown";
                    f.SupplyTicket = pathLeg.supplyTicket;
                    f.SupplyDealId = pathLeg.supplyDealId;
                    f.ReceiptPipe = pathLeg.receiptPipe;
                    f.ReceiptPipeId = pathLeg.receiptPipeId;
                    f.ReceiptPointId = pathLeg.receiptPointId;
                    f.ReceiptMeter = pathLeg.receiptMeter;
                    f.ReceiptMeterId = pathLeg.receiptMeterId;
                    f.SupplyTransferDealId = pathLeg.supplyTransferDealId;
                    f.MarketDealId = null;
                    f.DeliveryPointId = null;
                    f.DeliveryMeterId = null;
                    f.MarketTransferDealId = null;
                    f.PipeContractId = pathLeg.pipeContractId;
                    f.NomReceiptVol = 0;
                    f.DealVolume = legNum == 1 ? sourceItem.SourceDealVolume ?? 0 : previousLeg?.DeliveryVol ?? 0;
                    f.MarketTransferMeterMapId = pathLeg.marketTransferMeterMapId;
                    f.ActivityNum = pathLeg.activityNum;
                    f.SupplyNotes = pathLeg.supplyNotes;
                    f.SupplySourceNotes = pathLeg.supplySourceNotes;
                    f.IsKeepWhole = false;
                    f.IsEnteredFromSos = pathLeg.isEnteredFromSos;
                    f.NomNotes = null;

                    items.Add(f);
                    nextSupplyTransferId = -1;
                    legNum++;
                    previousLeg = f;
                }
                else
                {
                    //MarketSupply found
                    var pathLegs = await (
                        from ms in db.CrudeMarketSupplies
                        let supplyDealNum = ms.SupplyNom.Deal != null ? ms.SupplyNom.Deal.TicketNum : null
                        let supplyTransferNum = ms.SupplyNom.TransferDeal != null ? ms.SupplyNom.TransferDeal.TicketNum : null
                        let supplyMeter = ms.SupplyNom.Meter
                        let supplyMeterProduct = supplyMeter.MeterProducts
                            .Where(x => x.ProductId == productId && x.EffectiveDate <= nomDate).OrderByDescending(x => x.EffectiveDate).FirstOrDefault()
                        let supplyZone = supplyMeterProduct != null ? supplyMeterProduct.SourceZone : null
                        let supplyPipe = supplyZone != null ? supplyZone.Pipeline : null
                        let supplyPipeId = supplyPipe != null ? supplyPipe.Id : 0
                        let supplyMeterNumber = (supplyMeterProduct != null ? supplyMeterProduct.Number : "") ?? ""
                        let deliveryMeter = ms.MarketNom.Meter
                        let deliveryMeterProduct = deliveryMeter.MeterProducts
                            .Where(x => x.ProductId == productId && x.EffectiveDate <= nomDate).OrderByDescending(x => x.EffectiveDate).FirstOrDefault()
                        let deliveryZone = deliveryMeterProduct != null ? deliveryMeterProduct.SourceZone : null
                        let deliveryPipe = deliveryZone != null ? deliveryZone.Pipeline : null
                        let deliveryMeterNumber = (deliveryMeterProduct != null ? deliveryMeterProduct.Number : "") ?? ""
                        let marketTransfer = ms.MarketNom.TransferDeal
                        let supplyNom = ms.SupplyNom
                        where mktSupIds.Contains(ms.Id)
                        select new
                        {
                            supplyTicket = supplyDealNum ?? supplyTransferNum,
                            marketTransferId = ms.MarketNom.TransferDealId,
                            marketTransferNum = marketTransfer != null ? marketTransfer.TicketNum : null,
                            supplyDealId = ms.SupplyNom.DealId,
                            receiptPipe = supplyPipe != null ? supplyPipe.Name : "",
                            receiptPipeId = supplyPipeId,
                            receiptPointId = ms.SupplyNom.PointId,
                            receiptMeter = supplyMeter.Name + "/" + supplyMeterNumber,
                            receiptMeterId = supplyMeter.Id,
                            supplyTransferDealId = ms.SupplyNom.TransferDealId,
                            pipeContractId = ms.SupplyNom.PipelineContractId,
                            ptrContractId = ms.SupplyNom.PtrPipelineContractId,
                            ptrDeliveryMeterId = ms.SupplyNom.PtrDeliveryMeterId,
                            ptrPercent = ms.SupplyNom.Ptr,
                            activityNum = ms.SupplyNom.ActNumber,
                            supplyNotes = ms.SupplyNom.Comment,
                            supplySourceNotes = ms.SupplyNom.SourceNotes,
                            nomNotes = ms.Comment,
                            isKeepWhole = ms.IsKeepWhole,
                            deliveryVol = ms.Volume,
                            marketDealId = ms.MarketNom.DealId,
                            deliveryPipe = deliveryPipe != null ? deliveryPipe.Name : "",
                            deliveryPointId = ms.MarketNom.PointId,
                            deliveryMeter = deliveryMeter.Name + "/" + deliveryMeterNumber,
                            deliveryMeterId = deliveryMeter.Id,
                            marketTransferDealId = ms.MarketNom.TransferDealId,
                            marketTransferMeterMapId = marketTransfer != null ? marketTransfer.TransferMeterMapId : 0,
                            isEnteredFromSos = supplyNom.Deal != null && supplyNom.Deal.DealStatusId == (int)Enums.DealStatus.EnteredFromSOS
                        }
                    ).AsNoTracking().ToListAsync();

                    //When a transfer leg is going to another transfer then there should only be one item in pathLegs.
                    //When a transfer leg is going to a normal market or markets then there might be more than one item in pathLegs.
                    //In the case that there is more than one, we need to sum them up.
                    var pathLeg = pathLegs.First();
                    var hasMultiplePathLegs = pathLegs.Count > 1;

                    var f = new SosTransferItem();
                    f.Guid = Guid.NewGuid().ToString("N")[..8];
                    f.PathGuid = pathGuid;
                    f.PathName = $"Path {pathNum}: Unknown";
                    f.PathLegNum = legNum;
                    f.SupplyTicket = pathLeg.supplyTicket;
                    f.SupplyDealId = pathLeg.supplyDealId;
                    f.ReceiptPipe = pathLeg.receiptPipe;
                    f.ReceiptPipeId = pathLeg.receiptPipeId;
                    f.ReceiptPointId = pathLeg.receiptPointId;
                    f.ReceiptMeter = pathLeg.receiptMeter;
                    f.ReceiptMeterId = pathLeg.receiptMeterId;
                    f.SupplyTransferDealId = pathLeg.supplyTransferDealId;
                    f.PipeContractId = pathLeg.pipeContractId;

                    var volMeterPairs = pathLegs.Select(x => new VolumeMeterPair(x.deliveryVol ?? 0, x.deliveryMeterId)).ToList();

                    var totalDeliveryVol = pathLegs.Sum(x => x.deliveryVol ?? 0);

                    f.NomReceiptVol = totalDeliveryVol;
                    f.DealVolume = legNum == 1 ? sourceItem.SourceDealVolume ?? 0 : previousLeg?.DeliveryVol ?? 0;
                    f.ActivityNum = pathLeg.activityNum;
                    f.SupplyNotes = pathLeg.supplyNotes;
                    f.SupplySourceNotes = pathLeg.supplySourceNotes;
                    f.NomNotes = String.Join(Environment.NewLine, pathLegs.Where(x => !string.IsNullOrWhiteSpace(x.nomNotes)).Select(x => x.nomNotes));
                    f.IsKeepWhole = pathLegs.Max(x => x.isKeepWhole);
                    f.IsEnteredFromSos = pathLegs.Any(x => x.isEnteredFromSos);
                    f.DeliveryVol = totalDeliveryVol;
                    f.MarketDealId = hasMultiplePathLegs ? null : pathLeg.marketDealId;
                    var deliveryPipeNames = pathLegs.Select(x => x.deliveryPipe).Distinct().ToList();
                    f.DeliveryPipe = deliveryPipeNames.Count > 1 ? "Multiple" : deliveryPipeNames.First();
                    f.DeliveryPointId = hasMultiplePathLegs ? null : pathLeg.deliveryPointId;
                    var deliveryMeterNames = pathLegs.Select(x => x.deliveryMeter).Distinct().ToList();
                    f.DeliveryMeter = deliveryMeterNames.Count > 1 ? "Multiple" : deliveryMeterNames.First();
                    f.DeliveryMeterId = hasMultiplePathLegs ? null : pathLeg.deliveryMeterId;
                    f.MarketTransferDealId = hasMultiplePathLegs ? null : pathLeg.marketTransferDealId;
                    f.TransferNum = hasMultiplePathLegs ? null : pathLeg.marketTransferNum;
                    f.MarketTransferMeterMapId = hasMultiplePathLegs ? 0 : pathLeg.marketTransferMeterMapId;
                    items.Add(f);
                    nextSupplyTransferId = pathLeg.marketTransferId ?? -1;
                    legNum++;
                    previousLeg = f;
                }
            }

            pathNum++;
        }

        if (items.Count != 0)
        {
            int initialPipeId = items.First().ReceiptPipeId;
            await SetPathNames(items, sourceItem, initialPipeId);

            List<int> pipeIds = items.Select(x => x.ReceiptPipeId).Distinct().ToList();
            pipeContracts = (await GetPipeContracts(pipeIds, nomDate));
        }

        Debug.WriteLine($"GetTransferSet tasks took {sw.ElapsedMilliseconds} ms");

        SosTransferSet sosTransfers = new();
        sosTransfers.Items = items;
        sosTransfers.PipeContracts = pipeContracts;
        sosTransfers.SosItem = sourceItem;

        sw.Stop();
        Debug.WriteLine($"GetTransferSet took {sw.ElapsedMilliseconds} ms");

        return sosTransfers;
    }

    private async Task SetPathNames(List<SosTransferItem> transferItems, SosItem sourceItem, int initialPipeId)
    {
        var productId = (int)Enums.Product.CrudeOil;
        var asOfDate = DateOnly.FromDateTime(DateTime.Now.Date);

        var transferSetPathGroups = transferItems.GroupBy(x => x.PathName).ToList();

        var pathIdsForPipe = await (
            from q in db.CrudePathRoutes
            where q.OrderId == 1
                && q.MeterMap != null
            let meter1 = q.MeterMap!.Meter1
            let meter2 = q.MeterMap!.Meter2
            let meterProduct1 = meter1.MeterProducts
                .Where(x => x.ProductId == productId && x.EffectiveDate <= asOfDate).OrderByDescending(x => x.EffectiveDate).FirstOrDefault()
            let meterProduct2 = meter2.MeterProducts
                .Where(x => x.ProductId == productId && x.EffectiveDate <= asOfDate).OrderByDescending(x => x.EffectiveDate).FirstOrDefault()
            let zone1 = meterProduct1 != null ? meterProduct1.SourceZone : null
            let zone2 = meterProduct2 != null ? meterProduct2.SourceZone : null
            let pipe1 = zone1 != null ? zone1.Pipeline : null
            let pipe2 = zone2 != null ? zone2.Pipeline : null
            let pipeId1 = pipe1 != null ? pipe1.Id : 0
            let pipeId2 = pipe2 != null ? pipe2.Id : 0
            where (zone1 != null && zone2 != null)
                && (pipeId1 == initialPipeId || pipeId2 == initialPipeId)
                && q.Path.SourceCounterpartyId == sourceItem.SupplyCounterpartyId
                && (q.Path.SourceCounterpartyId == null || q.Path.SourceCounterpartyId == sourceItem.SupplyCounterpartyId)
            select q.PathId
        ).Distinct().ToListAsync();

        var dbPathRoutes = (await db.CrudePathRoutes.Where(x => pathIdsForPipe.Contains(x.PathId)).Include(x => x.Path).ToListAsync());
        var dbPathRouteIdsByMaxOrder = dbPathRoutes.GroupBy(x => x.PathId).ToLookup(x => x.Max(y => y.OrderId), x => x.Key);

        int pathNum = 1;
        foreach (var pathGroup in transferSetPathGroups)
        {
            var firstTransferNum = pathGroup.First().TransferNum;
            var firstSavedPathName = await db.TransferDeals.Where(x => x.TicketNum == firstTransferNum).Select(x => x.CrudePathName).FirstOrDefaultAsync();
            if (!string.IsNullOrWhiteSpace(firstSavedPathName) && !firstSavedPathName.ToLower().Contains("unknown"))
            {
                foreach (var route in pathGroup)
                    route.PathName = $"Path {pathNum}: {firstSavedPathName}";
                pathNum++;
                continue;
            }

            //find the pathRoutes where the built set's count matches the max order from the database and the source meter matches the first leg's receipt meter
            int setMaxOrder = pathGroup.Count() - 1; //subtract 1 since the last leg is the delivery meter
            List<int> matchingPathIds = dbPathRouteIdsByMaxOrder[setMaxOrder].ToList();
            List<CrudePathRoute> matchingPathRoutes = (
                from q in dbPathRoutes
                where matchingPathIds.Contains(q.PathId)
                    && (q.Path.SourceMeterId == pathGroup.First().ReceiptMeterId)
                select q
            ).ToList();

            //if there are multiple paths with the same max order, we prefer to get the ones that match the source meter, rather than a null source meter
            if (matchingPathRoutes.Any())
                matchingPathRoutes = matchingPathRoutes.ToList();

            foreach (var pathLeg in pathGroup)
            {
                //skip the last leg of the path group since it will be the delivery meter
                //and we don't need to match it to a path route, i.e. it won't have a market transfer meter map
                if (pathLeg.PathLegNum == pathGroup.Count())
                    continue;

                //we get the next leg's pipe contract id so we can match it to the path route's "ToPipeContractId", emphasis on "To"
                var nextLegPipeContractId = pathGroup.Where(x => x.PathLegNum == pathLeg.PathLegNum + 1).Select(x => x.PipeContractId).FirstOrDefault();

                matchingPathIds = (
                    from q in matchingPathRoutes
                    where q.MeterMapId == pathLeg.MarketTransferMeterMapId
                        && q.OrderId == pathLeg.PathLegNum
                        && ((pathLeg.PathLegNum == 1 && q.Path.SourcePipeContractId == pathLeg.PipeContractId) || q.Path.SourcePipeContractId == null || pathLeg.PathLegNum > 1)
                        && (nextLegPipeContractId == null || q.ToPipeContract == null || (q.ToPipeContractId == nextLegPipeContractId))
                    select q.PathId
                ).ToList();
                matchingPathRoutes = matchingPathRoutes.Where(x => matchingPathIds.Contains(x.PathId)).ToList();
            }

            // if we still have matching routes then order them to find the first best match, otherwise unkown path name
            if (matchingPathRoutes.Count > 0)
            {
                //the best matches have more specific path routes, so order items that have non-null source info first, e.g. SourceCounterparty etc.
                matchingPathRoutes = (
                    from q in matchingPathRoutes
                    orderby
                        q.Path.SourceCounterpartyId descending,
                        q.Path.SourceMeterId descending,
                        q.Path.SourcePipeContractId descending,
                        q.OrderId,
                        q.ToPipeContractId descending,
                        q.Path.Name
                    select q
                ).ToList();

                var firstBestPath = matchingPathRoutes.First();

                //there might be multiple identical paths with different names,
                //so we look for those and join them together if necessary
                var bestPathNames = (
                    from q in matchingPathRoutes
                    where q.Path.SourceCounterpartyId == firstBestPath.Path.SourceCounterpartyId
                        && q.Path.SourceMeterId == firstBestPath.Path.SourceMeterId
                        && q.Path.SourcePipeContractId == firstBestPath.Path.SourcePipeContractId
                        && q.ToPipeContractId == firstBestPath.ToPipeContractId
                        && q.OrderId == firstBestPath.OrderId
                    select q.Path.Name
                ).ToList();

                string bestPathName = string.Join(" • ", bestPathNames);
                var multiLabel = bestPathNames.Count > 1 ? " (Multi)" : "";

                foreach (var route in pathGroup)
                    route.PathName = $"Path {pathNum}{multiLabel}: {bestPathName}";
            }
            pathNum++;
        }
    }

    [Permission("SOS Crude Nomination", PermissionType.Modify)]
    [Route("[action]")]
    public async Task<IActionResult> SavePathNoms(List<SosTransferItem> Items, DateOnly fromDate, DateOnly toDate)
    {
        int userId = Util.GetAppUserId(User);

        await db.Database.CreateExecutionStrategy().Execute(async () =>
        {
            await using var dbContextTransaction = await db.Database.BeginTransactionAsync();
            Items = Items.Where(x => x.HasChanges()).ToList();

            foreach (var nom in Items)
            {
                var itemsToDelete = db.CrudeMarketSupplies.Where(ms =>
                                    (ms.SupplyNom.DealId.GetValueOrDefault() == nom.SupplyDealId.GetValueOrDefault() &&
                                    ms.SupplyNom.TransferDealId.GetValueOrDefault() == nom.SupplyTransferDealId.GetValueOrDefault() &&
                                    ms.SupplyNom.MeterId.GetValueOrDefault() == nom.ReceiptMeterId &&
                                    ms.SupplyNom.PointId.GetValueOrDefault() == nom.ReceiptPointId.GetValueOrDefault()) &&
                                    (ms.MarketNom.DealId.GetValueOrDefault() == nom.MarketDealId.GetValueOrDefault() &&
                                    ms.MarketNom.TransferDealId.GetValueOrDefault() == nom.MarketTransferDealId.GetValueOrDefault() &&
                                    ms.MarketNom.MeterId == nom.DeliveryMeterId.GetValueOrDefault() &&
                                    ms.MarketNom.PointId.GetValueOrDefault() == nom.DeliveryPointId.GetValueOrDefault()) &&
                                    ms.IsActual == false &&
                                    ms.Date >= fromDate &&
                                    ms.Date <= toDate);

                db.CrudeMarketSupplies.RemoveRange(itemsToDelete);
            }
            await db.SaveChangesAsync();

            var transferDeals = db.TransferDeals
                .Where(td => Items.Select(x => x.SupplyTransferDealId.GetValueOrDefault())
                .Concat(Items.Select(x => x.MarketTransferDealId.GetValueOrDefault()))
                .Distinct().Contains(td.Id))
                .ToDictionary(n => n.Id, n => new SosStartEndDatePair(n.StartDate, n.EndDate));

            var deals = db.Deals
                .Where(td => Items.Select(x => x.SupplyDealId.GetValueOrDefault())
                .Concat(Items.Select(x => x.MarketDealId.GetValueOrDefault()))
                .Distinct().Contains(td.Id))
                .ToDictionary(n => n.Id, n => new SosStartEndDatePair(n.StartDate ?? DateOnly.MinValue, n.EndDate ?? DateOnly.MaxValue));

            var NomSupplies = db.CrudeSupplies.Where(x => x.Date >= fromDate && x.Date <= toDate).ToList();
            var NomMarkets = db.CrudeMarkets.Where(x => x.Date >= fromDate && x.Date <= toDate).ToList();

            foreach (DateOnly loopDate in Util.Date.EachDay(fromDate, toDate))
            {
                foreach (var item in Items)
                {
                    var nd = new SosNomDetails();
                    nd.Day = loopDate;
                    nd.SupplyDealID = item.SupplyDealId.HasValue ? item.SupplyDealId : item.SupplyTransferDealId;
                    nd.ReceiptPointID = item.ReceiptPointId;
                    nd.ReceiptMeterID = item.ReceiptMeterId;
                    nd.SupplyRank = FastValues.DefaultSosRank;
                    nd.PipelineCycleID = 1;
                    nd.PipelineContractID = item.PipeContractId;
                    nd.DunsID = null;
                    nd.ActNumber = item.ActivityNum;
                    nd.MarketDealID = item.MarketDealId.HasValue ? item.MarketDealId : item.MarketTransferDealId;
                    nd.MarketRank = FastValues.DefaultSosRank;
                    nd.DeliveryMeterId = item.DeliveryMeterId.GetValueOrDefault();
                    nd.DeliveryPointId = item.DeliveryPointId;
                    nd.NominatedVolume = item.DeliveryVol;
                    nd.IsSupplyTransferDeal = item.SupplyTransferDealId.HasValue;
                    nd.IsMarketTransferDeal = item.MarketTransferDealId.HasValue;
                    nd.SupplyNotes = item.SupplyNotes;
                    nd.SupplySourceNotes = item.SupplySourceNotes;
                    nd.NomNotes = item.NomNotes;
                    nd.IsKeepWhole = item.IsKeepWhole;
                    nd.IncludeInSave = item.HasChanges();
                    nd.UserID = userId;

                    SosCrudeNomSaver.SaveSingleNomination(nd, db, transferDeals, deals, NomSupplies, NomMarkets);
                }
            }

            await db.SaveChangesAsync();
            await dbContextTransaction.CommitAsync();
        });
        return Ok();
    }

    [Permission("SOS Crude Nomination", PermissionType.Modify)]
    [Route("[action]")]
    public async Task<IActionResult> DeleteTransfersInPath(SosItem sourceItem, DateOnly nomDate, int anyTransferDealId)
    {
        var transferSet = await GetTransferSetInternal(sourceItem, nomDate);

        //gets the path guid for the leg of the transfer set that has the transfer deal which the user selected to delete
        var pathGuid = transferSet.Items.Where(x => x.SupplyTransferDealId == anyTransferDealId || x.MarketTransferDealId == anyTransferDealId).Select(x => x.PathGuid).FirstOrDefault();
        //gets all the transfer items for the entire path that has a matching path guid
        var itemsWithSamePathGuid = transferSet.Items.Where(x => x.PathGuid == pathGuid).ToList();

        //get the items in the path that have a supply or market transfer deal
        var supplyTransferDealIds = itemsWithSamePathGuid.Where(x => x.SupplyTransferDealId != null).Select(x => x.SupplyTransferDealId!.Value).ToList();
        var marketTransferDealIds = itemsWithSamePathGuid.Where(x => x.MarketTransferDealId != null).Select(x => x.MarketTransferDealId!.Value).ToList();
        var transferDealIds = supplyTransferDealIds.Concat(marketTransferDealIds).ToList();

        //append sourceItem.TransferDealId in case GetTransferSetInternal didn't include it
        if (sourceItem.TransferDealId != null)
            transferDealIds.Add(sourceItem.TransferDealId.Value);

        var uniqueIds = transferDealIds.Distinct().ToList();

        await db.Database.CreateExecutionStrategy().Execute(async () =>
        {
            await using var dbContextTransaction = await db.Database.BeginTransactionAsync();
            var marketSuppliesToDelete = db.CrudeMarketSupplies.Where(ms =>
                (ms.SupplyNom.TransferDealId != null && uniqueIds.Contains(ms.SupplyNom.TransferDealId.Value)) ||
                (ms.MarketNom.TransferDealId != null && uniqueIds.Contains(ms.MarketNom.TransferDealId.Value))
            );

            if (marketSuppliesToDelete.Any())
                db.CrudeMarketSupplies.RemoveRange(marketSuppliesToDelete);

            var suppliesToDelete = db.CrudeSupplies.Where(s => s.TransferDealId != null && uniqueIds.Contains(s.TransferDealId.Value));
            if (suppliesToDelete.Any())
                db.CrudeSupplies.RemoveRange(suppliesToDelete);

            var marketsToDelete = db.CrudeMarkets.Where(m => m.TransferDealId != null && uniqueIds.Contains(m.TransferDealId.Value));
            if (marketsToDelete.Any())
                db.CrudeMarkets.RemoveRange(marketsToDelete);

            var transfersToDelete = db.TransferDeals.Where(td => uniqueIds.Contains(td.Id));
            if (transfersToDelete.Any())
                db.TransferDeals.RemoveRange(transfersToDelete);

            await db.SaveChangesAsync();
            await dbContextTransaction.CommitAsync();
        });

        return Ok();
    }

    [Permission("T&E Deduction Meter", PermissionType.Standard)]
    [Route("[action]")]
    public async Task<IActionResult> SaveTneMeter(int dealId, int tneMeterId)
    {
        var deal = (
            from d in db.Deals
            where d.Id == dealId
            select d
        ).FirstAsync().Result;

        int? meterId = tneMeterId == 0 ? null : tneMeterId;
        // deal.TnEMeterId = meterId;

        await db.SaveChangesAsync();
        return Ok();
    }

    public class ActualItem
    {
        public int ActualTypeId { get; set; }
        public int SupplyNomId { get; set; }
        public int MarketNomId { get; set; }
        public int? LastTransferId { get; set; }
        public DateOnly SaveDate { get; set; }
        public int? TneMeterId { get; set; }
        public DateOnly NomDate { get; set; }
        public bool IsLinked { get; set; }
    }

    [Permission("T&E Deduction Meter", PermissionType.Standard)]
    [Route("[action]")]
    public async Task<IActionResult> SaveActualTneMeter(ActualItem item)
    {
        var appUserId = Util.GetAppUserId(User);

        var mostRecentItem = await (
            from x in db.CrudeActuals
            where x.ActualTypeId == item.ActualTypeId
                && x.SupplyNomId == item.SupplyNomId
                && x.MarketNomId == item.MarketNomId
                && x.LastTransferId == item.LastTransferId
            orderby x.SaveDate descending
            select x
        ).FirstOrDefaultAsync();

        if (mostRecentItem == null)
        {
            // save a new item for today
            var newItem = new CrudeActual
            {
                ActualTypeId = item.ActualTypeId,
                SupplyNomId = item.SupplyNomId,
                MarketNomId = item.MarketNomId,
                LastTransferId = item.LastTransferId,
                SaveDate = DateTime.Now.ToDateOnly(),
                SavedBy = appUserId,
                TneMeterId = item.TneMeterId,
                IsLinked = item.IsLinked
            };
            db.CrudeActuals.Add(newItem);
        }
        else
        {
            // modify the most recent item
            mostRecentItem.TneMeterId = item.TneMeterId;
            mostRecentItem.SavedBy = appUserId;
        }

        await db.SaveChangesAsync();
        return Ok();
    }

    private static async Task<int?> GetTneMeter(ActualItem? item)
    {
        if (item == null)
            return null;

        using var db = Main.CreateContext();
        var value = await (
            from q in db.CrudeActuals.AsNoTracking()
            where q.ActualTypeId == item.ActualTypeId
                && q.SupplyNomId == item.SupplyNomId
                && q.MarketNomId == item.MarketNomId
                && q.LastTransferId == item.LastTransferId
            orderby q.SaveDate descending
            select q.TneMeterId
        ).FirstOrDefaultAsync();

        return value;
    }

}
