using System.Diagnostics;
using System.Drawing;
using Aspose.Cells;
using Fast.Web.Controllers;
using SosSnapshot = Fast.Database.Models.SosSnapshot;

namespace Fast.Web.Logic;

public class SosNomSaver
{
    private readonly MyDbContext db;
    private readonly List<SosItem> items;
    private readonly List<SosItem> changedItems;
    private readonly DateOnly fromDate;
    private readonly DateOnly toDate;
    private readonly int deliveryPointId;
    private readonly int userId;
    private readonly string? saveNotes;

    private readonly IWebHostEnvironment env;

    private Dictionary<(int? dealId, int meterId, int? pointId, DateOnly date), GasMarket> dbMarketsDic = new();
    private Dictionary<(int? dealId, int? pointId, int? meterId, int? transferDealId, int? pipelineContractId, DateOnly nomDate), GasSupply> dbSuppliesDic = new();
    private Dictionary<(int supplyNomId, int marketNomId, DateOnly nomDate), GasMarketSupply> dbMktSupsDic = new();
    private Dictionary<int, (DateOnly startDate, DateOnly endDate)> normalDealDatesDic = new();
    private Dictionary<int, (DateOnly startDate, DateOnly endDate)> transferDealDatesDic = new();
    private Dictionary<int, int?> oldPipelineContractsBySupply = new();

    public SosNomSaver(MyDbContext db, int userId, List<SosItem> items, DateOnly fromDate, DateOnly toDate, int deliveryPointId, string? saveNotes, IWebHostEnvironment env)
    {
        this.db = db;
        this.items = items;
        this.changedItems = items.Where(x => x.HasSupplyChanges() || x.HasNomChanges()).ToList();
        this.fromDate = fromDate;
        this.toDate = toDate;
        this.deliveryPointId = deliveryPointId;
        this.userId = userId;
        this.saveNotes = saveNotes;
        this.env = env;
    }

    public async Task SaveAsync()
    {
        var sw = Stopwatch.StartNew();

        foreach (var item in items)
            item.PtrPercent = item.IsManualPtr || item.ModifiedProps.Contains("ptrPercent") ? item.PtrPercent : null;

        await db.Database.CreateExecutionStrategy().Execute(async () =>
        {
            await using var dbContextTransaction = await db.Database.BeginTransactionAsync();

            var allSupplyNormalDealIds = items.Select(x => x.DealId).Distinct();
            var allMarketNormalDealIds = items.SelectMany(x => x.Noms).Select(x => x.MarketDealId).Distinct();
            var allSupplyTransferIds = items.Select(x => x.TransferDealId).Distinct();
            var allNormalDealIds = allSupplyNormalDealIds.Concat(allMarketNormalDealIds).Distinct();
            normalDealDatesDic = db.Deals
                .Where(x => allNormalDealIds.Contains(x.Id))
                .ToDictionary(x => x.Id, x => (startDate: x.StartDate.GetValueOrDefault(), endDate: x.EndDate.GetValueOrDefault()));
            transferDealDatesDic = db.TransferDeals
                .Where(x => allSupplyTransferIds.Contains(x.Id))
                .ToDictionary(x => x.Id, x => (startDate: x.StartDate, endDate: x.EndDate));
            oldPipelineContractsBySupply = await GetChangedPipelineContractsBySupply();
            sw.Stop();
            Debug.WriteLine($"SaveAsync init took {sw.ElapsedMilliseconds} ms");
            sw.Restart();

            await CreateNecessaryMarkets();
            sw.Stop();
            Debug.WriteLine($"CreateNecessaryMarkets took {sw.ElapsedMilliseconds} ms");
            sw.Restart();

            await CreateNecessarySupplies();
            sw.Stop();
            Debug.WriteLine($"CreateNecessarySupplies took {sw.ElapsedMilliseconds} ms");
            sw.Restart();

            var supplyMeterIds = items.Select(x => x.ReceiptMeterId).Distinct().ToList();
            var marketMeterIds = items.SelectMany(x => x.Noms).Select(x => x.MarketDeliveryMeterId).Distinct().ToList();

            dbMktSupsDic = (await (
                from q in db.GasMarketSupplies
                where q.Date >= fromDate && q.Date <= toDate
                    && q.SupplyNom.MeterId != null && supplyMeterIds.Contains(q.SupplyNom.MeterId.Value)
                    && marketMeterIds.Contains(q.MarketNom.MeterId)
                select q
            ).ToListAsync()).ToDictionary(x => (x.SupplyNomId, x.MarketNomId, x.Date));
            sw.Stop();
            Debug.WriteLine($"Fill dbMktSupsDic took {sw.ElapsedMilliseconds} ms");
            sw.Restart();

            //Delete any existing noms that have changed.  They will be recreated later.
            await DeleteChangedNoms();
            sw.Stop();
            Debug.WriteLine($"DeleteChangedNoms took {sw.ElapsedMilliseconds} ms");
            sw.Restart();

            //update existing supplies and noms for any existing items
            foreach (var item in changedItems)
            {
                foreach (var nomDate in Util.Date.EachDay(fromDate, toDate))
                {
                    var isInvalid = HasInvalidNomDate(item.DealId, item.TransferDealId, nomDate);
                    if (isInvalid)
                        continue;

                    var newPipeContractId = item.PipeContractId;
                    oldPipelineContractsBySupply.TryGetValue(item.SupplyNomId ?? 0, out var oldPipeContractId);

                    GasSupply? tryGetSupply(int? pipeContractId)
                    {
                        if (dbSuppliesDic.TryGetValue((
                            item.DealId, item.ReceiptPointId, item.ReceiptMeterId,
                            item.TransferDealId, pipeContractId, nomDate), out var supply))
                            return supply;

                        return null;
                    }

                    GasSupply? sup = tryGetSupply(newPipeContractId)
                        ?? tryGetSupply(oldPipeContractId)
                        ?? tryGetSupply(null);

                    if (sup == null)
                        throw new Exception("could not find supply to update");

                    if (item.HasSupplyChanges())
                    {
                        if (item.ModifiedProps.Contains("activityNum"))
                            sup.ActNumber = item.ActivityNum;
                        if (item.ModifiedProps.Contains("notes"))
                            sup.Comment = item.Notes;
                        if (item.ModifiedProps.Contains("pipeContractId"))
                            sup.PipelineContractId = item.PipeContractId;
                        if (item.ModifiedProps.Contains("ptrContractId"))
                            sup.PtrPipelineContractId = item.PtrContractId;
                        if (item.ModifiedProps.Contains("ptrDeliveryMeterId"))
                            sup.PtrDeliveryMeterId = item.PtrDeliveryMeterId;
                        if (item.ModifiedProps.Contains("ptrPercent"))
                            sup.Ptr = item.PtrPercent;
                        if (item.ModifiedProps.Contains("sourceNotes"))
                        {
                            sup.SourceNotes = item.SourceNotes;
                            //we may need to update the source notes original supplies with the same source dealId and source meterId
                            if (item.TransferDealId != null)
                            {
                                var sourceDealId = db.Deals.Where(x => x.TicketNum == item.SourceTicket).Select(x => x.Id).FirstOrDefault();
                                var sourceMeterId = item.SourceMeterId;
                                var otherOriginalSupplies = db.GasSupplies.Where(x =>
                                    x.DealId == sourceDealId
                                    && x.MeterId == sourceMeterId
                                    && x.Id != sup.Id
                                    && x.Date == nomDate
                                ).ToList();
                                foreach (var otherSup in otherOriginalSupplies)
                                    otherSup.SourceNotes = item.SourceNotes;
                            }
                        }
                        sup.SavedBy = userId;
                        sup.SavedTime = DateTime.UtcNow;
                    }

                    var modifiedNoms = item.Noms.Where(x => x.IsModified).ToList();
                    foreach (var nom in modifiedNoms)
                    {
                        var foundMkt = dbMarketsDic.TryGetValue((
                            nom.MarketDealId, nom.MarketDeliveryMeterId, deliveryPointId, nomDate), out var mkt);
                        if (!foundMkt || mkt == null)
                            continue;

                        if (nom.Volume != null)
                        {
                            var newMktSup = new GasMarketSupply();
                            newMktSup.SupplyNomId = sup.Id;
                            newMktSup.MarketNomId = mkt.Id;
                            newMktSup.Date = nomDate;
                            newMktSup.Volume = nom.Volume;
                            newMktSup.Comment = nom.Notes;
                            newMktSup.UserId = userId;
                            newMktSup.IsKeepWhole = nom.IsKeepWhole;
                            newMktSup.IsActual = false;
                            db.GasMarketSupplies.Add(newMktSup);
                        }
                    }
                }
            }
            await db.SaveChangesAsync();
            sw.Stop();
            Debug.WriteLine($"Update existing supplies and noms took {sw.ElapsedMilliseconds} ms");
            sw.Restart();

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

            await dbContextTransaction.CommitAsync();
        });
    }

    private async Task MarketAndSupplyCleanup()
    {
        //delete markets that don't have noms
        await db.GasMarkets.Where(x => x.GasMarketSupplies.Count == 0).ExecuteDeleteAsync();

        //delete supplies that don't have a comment, don't have any contract, and don't have noms
        await db.GasSupplies.Where(x => string.IsNullOrWhiteSpace(x.Comment)
            && x.PipelineContractId == null && x.PtrPipelineContract == null
            && x.GasMarketSupplies.Count == 0
        ).ExecuteDeleteAsync();
    }

    private async Task DeleteChangedNoms()
    {
        var allChangedNomItems = items.Where(x => x.HasNomChanges()).ToList();
        foreach (var item in allChangedNomItems)
        {
            foreach (var nomDate in Util.Date.EachDay(fromDate, toDate))
            {
                var newPipeContractId = item.PipeContractId;
                oldPipelineContractsBySupply.TryGetValue(item.SupplyNomId ?? 0, out var oldPipeContractId);

                GasSupply? tryGetSupply(int? pipeContractId)
                {
                    if (dbSuppliesDic.TryGetValue((
                        item.DealId, item.ReceiptPointId, item.ReceiptMeterId,
                        item.TransferDealId, pipeContractId, nomDate), out var supply))
                        return supply;

                    return null;
                }

                GasSupply? sup = tryGetSupply(newPipeContractId)
                    ?? tryGetSupply(oldPipeContractId)
                    ?? tryGetSupply(null);

                if (sup == null)
                    continue;

                var modifiedNoms = item.Noms.Where(x => x.IsModified).ToList();
                foreach (var nom in modifiedNoms)
                {
                    var foundMkt = dbMarketsDic.TryGetValue((
                        nom.MarketDealId, nom.MarketDeliveryMeterId, deliveryPointId,
                        nomDate), out var mkt);
                    if (!foundMkt || mkt == null)
                        continue;

                    var foundMktSup = dbMktSupsDic.TryGetValue((
                        sup.Id, mkt.Id, nomDate), out var nomToDelete);
                    if (foundMktSup && nomToDelete != null)
                        db.GasMarketSupplies.Remove(nomToDelete);
                }
            }
        }
        await db.SaveChangesAsync();
    }

    private async Task<Dictionary<(int? DealId, int MeterId, int? PointId, DateOnly Date), GasMarket>> CreateNecessaryMarkets()
    {
        var itemMarketPairs = (
            from q in items.SelectMany(x => x.Noms)
            where q.MarketDealId.GetValueOrDefault() != 0
                && q.MarketDeliveryMeterId != 0
            select new
            {
                q.MarketDealId,
                q.MarketDeliveryMeterId
            }
        ).Distinct();

        var allItemMarketDealIds = itemMarketPairs.Select(x => x.MarketDealId).Distinct().ToList();

        //get existing markets
        var allDbMarkets = await GetAllDbMarkets(allItemMarketDealIds);
        var allDbMarketsLookup = allDbMarkets.ToLookup(x => (x.DealId, x.PointId));

        //delete existing markets where necessary
        foreach (var marketPair in itemMarketPairs)
        {
            //allDbMarketsLookup contains all markets from the db, for every nomDate in the save range
            foreach (var mkt in allDbMarketsLookup[(marketPair.MarketDealId, deliveryPointId)])
            {
                //delete any markets (and their associated noms) that have a different delivery meter than the ones we're saving
                var hasDifferentMeter = mkt.MeterId != marketPair.MarketDeliveryMeterId;
                //delete any markets (and their associated noms) that have nom dates outside the deal's start/end date
                var isInvalid = HasInvalidNomDate(mkt.DealId, null, mkt.Date);

                if (hasDifferentMeter || isInvalid)
                    db.GasMarkets.Remove(mkt);
            }
        }
        await db.SaveChangesAsync();

        //refresh dbMarkets now that we've deleted some
        allDbMarkets = await GetAllDbMarkets(allItemMarketDealIds);
        dbMarketsDic = allDbMarkets.ToDictionary(x => (x.DealId, x.MeterId, x.PointId, x.Date));

        //create new markets where necessary
        foreach (var marketPair in itemMarketPairs)
        {
            foreach (var nomDate in Util.Date.EachDay(fromDate, toDate))
            {
                if (!dbMarketsDic.ContainsKey(
                    (marketPair.MarketDealId, marketPair.MarketDeliveryMeterId, deliveryPointId, nomDate)))
                {
                    var isNomDateWithinDealDates = marketPair.MarketDealId != null
                        && normalDealDatesDic[marketPair.MarketDealId.Value].startDate <= nomDate
                        && normalDealDatesDic[marketPair.MarketDealId.Value].endDate >= nomDate;

                    if (!isNomDateWithinDealDates)
                        continue;

                    var market = new GasMarket
                    {
                        DealId = marketPair.MarketDealId,
                        TransferDealId = null,
                        MeterId = marketPair.MarketDeliveryMeterId,
                        PointId = deliveryPointId,
                        Rank = FastValues.DefaultSosRank,
                        Date = nomDate
                    };
                    db.GasMarkets.Add(market);
                }
            }
        }
        await db.SaveChangesAsync();

        //refresh dbMarkets now that we've added some
        allDbMarkets = await GetAllDbMarkets(allItemMarketDealIds);
        dbMarketsDic = allDbMarkets.ToDictionary(x => (x.DealId, x.MeterId, x.PointId, x.Date));

        return dbMarketsDic;
    }

    private async Task<Dictionary<(int? DealId, int? PointId, int? MeterId, int? TransferDealId, int? PipelineContractId, DateOnly Date), GasSupply>> CreateNecessarySupplies()
    {
        var changedItemsBySupplyKey = changedItems
            .ToDictionary(x => (x.DealId, x.ReceiptPointId, x.ReceiptMeterId, x.TransferDealId, x.PipeContractId));

        var changedItemSupplyDealIds = changedItems
            .Where(x => x.DealId != null)
            .Select(x => x.DealId).Distinct().ToList();
        var changedItemSupplyTransferIds = changedItems
            .Where(x => x.TransferDealId != null)
            .Select(x => x.TransferDealId).Distinct().ToList();
        var allItemSupplyDealIds = items
            .Where(x => x.DealId != null)
            .Select(x => x.DealId).Distinct().ToList();
        var allItemSupplyTransferIds = items
            .Where(x => x.TransferDealId != null)
            .Select(x => x.TransferDealId).Distinct().ToList();

        //get existing supplies
        var changedDbSupplyItems = await GetAllDbSupplies(changedItemSupplyDealIds, changedItemSupplyTransferIds);
        var changedDbSuppliesLookup = changedDbSupplyItems.ToLookup(x => (x.DealId, x.PointId, x.MeterId, x.TransferDealId));

        //delete existing suppiles where necessary
        foreach (var item in changedItems)
        {
            //changedDbSuppliesLookup contains all changed supplies from the db, for every nomDate in the save range
            foreach (var sup in changedDbSuppliesLookup[(item.DealId, item.ReceiptPointId, item.ReceiptMeterId, item.TransferDealId)])
            {
                //delete any supplies (and their associated noms) that have nom dates outside the deal's start/end date
                var isInvalid = HasInvalidNomDate(sup.DealId, sup.TransferDealId, sup.Date);

                if (isInvalid)
                    db.GasSupplies.Remove(sup);
            }
        }
        await db.SaveChangesAsync();

        //refresh dbSupplies now that we've deleted some
        var allDbSupplies = await GetAllDbSupplies(allItemSupplyDealIds, allItemSupplyTransferIds);
        dbSuppliesDic = allDbSupplies.ToDictionary(x => (x.DealId, x.PointId, x.MeterId, x.TransferDealId, x.PipelineContractId, x.Date));

        //create new supplies where necessary
        foreach (var supplyKey in changedItemsBySupplyKey.Keys)
        {
            foreach (var nomDate in Util.Date.EachDay(fromDate, toDate))
            {
                var isInvalid = HasInvalidNomDate(supplyKey.DealId, supplyKey.TransferDealId, nomDate);
                if (isInvalid)
                    continue;

                var changedItem = changedItemsBySupplyKey[supplyKey];
                var newPipeContractId = changedItem.PipeContractId;
                oldPipelineContractsBySupply.TryGetValue(changedItem.SupplyNomId ?? 0, out var oldPipeContractId);

                bool itemExists(int? pipeContractId) => dbSuppliesDic.ContainsKey((
                    supplyKey.DealId, supplyKey.ReceiptPointId, supplyKey.ReceiptMeterId,
                    supplyKey.TransferDealId, pipeContractId, nomDate));

                var newContractItemExists = itemExists(newPipeContractId);
                var oldContractItemExists = itemExists(oldPipeContractId);
                var nullContractItemExists = itemExists(null);

                if (newContractItemExists || oldContractItemExists || nullContractItemExists)
                    continue;

                //these values might be overwritten after this method returns when we update existing supplies
                //whether they get updated or not depends on whether the user has changed the values in the UI
                var supply = GetNewSupply(
                    nomDate,
                    supplyKey.DealId,
                    supplyKey.ReceiptPointId,
                    supplyKey.ReceiptMeterId,
                    supplyKey.TransferDealId,
                    newPipeContractId,
                    changedItem
                );
                db.GasSupplies.Add(supply);
            }
        }
        await db.SaveChangesAsync();

        //refresh dbSupplies now that we've added some
        allDbSupplies = await GetAllDbSupplies(allItemSupplyDealIds, allItemSupplyTransferIds);
        dbSuppliesDic = allDbSupplies.ToDictionary(x => (x.DealId, x.PointId, x.MeterId, x.TransferDealId, x.PipelineContractId, x.Date));

        return dbSuppliesDic;
    }

    private GasSupply GetNewSupply(
        DateOnly nomDate,
        int? dealId,
        int? receiptPointId,
        int? receiptMeterId,
        int? transferDealId,
        int? pipeContractId,
        SosItem? changedItem = null)
    {
        return new GasSupply
        {
            DealId = dealId,
            PointId = receiptPointId,
            MeterId = receiptMeterId,
            TransferDealId = transferDealId,
            Rank = FastValues.DefaultSosRank,
            PipelineCycleId = 1,
            PipelineContractId = pipeContractId,
            Date = nomDate,
            ActNumber = changedItem?.ActivityNum,
            PtrDeliveryMeterId = changedItem?.PtrDeliveryMeterId,
            PtrPipelineContractId = changedItem?.PtrContractId,
            Ptr = changedItem?.PtrPercent,
            Comment = changedItem?.Notes,
            SourceNotes = changedItem?.SourceNotes,
            CreatedBy = userId,
            CreatedTime = DateTime.UtcNow,
            SavedBy = userId,
            SavedTime = DateTime.UtcNow
        };
    }

    private async Task<Dictionary<int, GasSupply>> GetChangedDbSupplies()
    {
        var supplyIds = changedItems
            .Where(x => x.SupplyNomId != null)
            .Select(x => x.SupplyNomId)
            .Distinct().ToList();

        var dbItems = await (
            from q in db.GasSupplies
            where supplyIds.Contains(q.Id)
            select q
        ).ToListAsync();

        var dict = dbItems.ToDictionary(x => x.Id);
        return dict;
    }

    private async Task<Dictionary<int, int?>> GetChangedPipelineContractsBySupply()
    {
        var supplyIds = changedItems
            .Where(x => x.SupplyNomId != null)
            .Select(x => x.SupplyNomId)
            .Distinct().ToList();

        var dbItems = await (
            from q in db.GasSupplies
            where supplyIds.Contains(q.Id)
            select new { q.Id, q.PipelineContractId }
        ).ToListAsync();

        var dict = dbItems.ToDictionary(x => x.Id, x => x.PipelineContractId);
        return dict;
    }

    private async Task<List<GasSupply>> GetAllDbSupplies()
    {
        var allItemSupplyDealIds = items
            .Where(x => x.DealId != null)
            .Select(x => x.DealId).Distinct().ToList();
        var allItemSupplyTransferIds = items
            .Where(x => x.TransferDealId != null)
            .Select(x => x.TransferDealId).Distinct().ToList();

        var dbItems = await (
            from q in db.GasSupplies
            where q.Date >= fromDate && q.Date <= toDate
                && (allItemSupplyDealIds.Contains(q.DealId) || allItemSupplyTransferIds.Contains(q.TransferDealId))
            select q
        ).ToListAsync();
        return dbItems;
    }

    private async Task<List<GasSupply>> GetAllDbSupplies(List<int?> itemSupplyDealIds, List<int?> itemSupplyTransferIds)
    {
        var dbSupplies = await (
            from q in db.GasSupplies
            where q.Date >= fromDate && q.Date <= toDate
                && (itemSupplyDealIds.Contains(q.DealId) || itemSupplyTransferIds.Contains(q.TransferDealId))
            select q
        ).ToListAsync();
        return dbSupplies;
    }

    private async Task<List<GasMarket>> GetAllDbMarkets(List<int?> itemMarketDealIds)
    {
        var dbMarkets = await (
            from q in db.GasMarkets
            where q.Date >= fromDate && q.Date <= toDate
                && itemMarketDealIds.Contains(q.DealId)
                && q.PointId == deliveryPointId
            select q
        ).ToListAsync();
        return dbMarkets;
    }

    private bool HasInvalidNomDate(int? dealId, int? transferDealId, DateOnly nomDate)
    {
        bool hasInvalidNomDate =
            (dealId != null && (nomDate < normalDealDatesDic[dealId.Value].startDate || nomDate > normalDealDatesDic[dealId.Value].endDate)) ||
            (transferDealId != null && (nomDate < transferDealDatesDic[transferDealId.Value].startDate || nomDate > transferDealDatesDic[transferDealId.Value].endDate));

        return hasInvalidNomDate;
    }

    public class MarketAndSupply
    {
        public GasMarket market = new();
        public GasSupply supply = new();
        public int? volume;
    }
    public class ColumnHeaders
    {
        public string headerName = "";
        public int headerIndex = 0;
    }

    public async Task CreateSnapshotAsync()
    {
        var visibleItems = items.Where(x => !x.IsHidden).ToList();
        if (visibleItems.Count == 0)
            return;

        var (wb, ws, ws1) = CreateAndConfigureWorkbook();
        var styles = CreateSnapshotStyles(wb);
        var snapshotData = await PrepareSnapshotData();

        CreateSnapshotDataColumns(visibleItems, snapshotData.itemSettings, ws, styles,
            snapshotData.pipelineContracts, snapshotData.deliveryMeters);
        var (lastNomsColumn, swingCount, baseloadCount) = CreateSnapshotNomsColumns(
            visibleItems, snapshotData.deliveryMeters, snapshotData.deliveryPoint.PipeId, ws,
            snapshotData.itemSettings, styles);
        CreateSnapshotTransfersColumn(visibleItems, ws, lastNomsColumn, styles);
        ApplySnapshotBorders(ws, snapshotData.itemSettings, swingCount, baseloadCount, lastNomsColumn);

        ws.AutoFitColumns();
        ws1.AutoFitColumns();

        string saveDateTime = DateTime.Now.ToString("yyyy-MM-dd HHmmss");
        string fromDateHyphenated = fromDate.ToString("yyyy-MM-dd");
        string deliveryPointName = snapshotData.points.FirstOrDefault(x => x.PointId == deliveryPointId)?.PointName ?? "";
        string deliveryPipeName = snapshotData.pipelines.FirstOrDefault(x => x.PipeId == snapshotData.deliveryPoint.PipeId)?.PipeShort ?? "";
        string fileNameOriginal = Util.String.GetLegalFileName($"Gas SOS {deliveryPipeName} - {deliveryPointName} {fromDateHyphenated} - {saveDateTime}.xlsx");
        string fileNameOnDisk = Guid.NewGuid().ToString("N") + ".xlsx";
        string productFolder = "Gas";
        int productId = 1;

        await SaveSnapshotAsync(productFolder, fileNameOriginal, wb, fileNameOnDisk, productId, snapshotData.deliveryPoint.PipeId);
    }

    private async Task SaveSnapshotAsync(string productFolder, string fileNameOriginal, Workbook workbook, string fileNameOnDisk, int productId, int pipeId)
    {
        try
        {
            await using var fileStream = new MemoryStream();
            workbook.Save(fileStream, SaveFormat.Xlsx);
            fileStream.Position = 0;

            var formFile = new FormFile(fileStream, 0, fileStream.Length, fileNameOriginal, fileNameOriginal)
            {
                Headers = new HeaderDictionary(),
                ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            };

            var subFolder = Path.Combine("SosSnapshots", productFolder);
            var uploadResult = await Util.File.SaveFileAsync(env.ContentRootPath, subFolder, formFile);

            if (uploadResult == null)
                throw new Exception("Failed to save SOS snapshot file.");

            fileNameOnDisk = uploadResult;

            SaveSnapshotToDB(fileNameOriginal, fileNameOnDisk, productId, pipeId);
        }
        catch (Exception ex)
        {
            Debug.WriteLine($"Snapshot save failed: {ex.Message}");
        }
    }

    private (Workbook wb, Worksheet ws, Worksheet ws1) CreateAndConfigureWorkbook()
    {
        var wb = new Workbook();
        var ws = wb.Worksheets[0];
        ws.Name = "SOS Snapshot";

        var ws1 = wb.Worksheets.Add("Sos Notes");
        var boldStyle = wb.CreateStyle();
        boldStyle.Font.IsBold = true;
        ws1.Cells["A1"].Value = "Notes:";
        ws1.Cells["A1"].SetStyle(boldStyle);
        ws1.Cells["B1"].Value = saveNotes ?? "No notes were entered.";

        return (wb, ws, ws1);
    }

    private async Task<(List<IdName> deliveryMeters, List<PipeInfo> pipelines, List<PointInfo> points, PointInfo deliveryPoint, List<PipelineContract> pipelineContracts, SosSettingItem itemSettings)> PrepareSnapshotData()
    {
        using var dbContext = Main.CreateContext();
        var deliveryMeters = await SosHelper.GetDeliveryMeters(deliveryPointId, fromDate);
        var pipelines = await DataHelper.GetPipelinesAsync(false);
        var points = await DataHelper.GetPointsAsync(false, Enums.ProductCategory.NaturalGasAndLng);
        var deliveryPoint = points.First(x => x.PointId == deliveryPointId);
        var pipelineContracts = await dbContext.PipelineContracts.ToListAsync();
        var itemSettings = await SosSettingController.GetSettingsInternal(userId, deliveryPoint.PipeId);
        return (deliveryMeters, pipelines, points, deliveryPoint, pipelineContracts, itemSettings);
    }

    private static Dictionary<string, Style> CreateSnapshotStyles(Workbook wb)
    {
        var boldStyle = wb.CreateStyle();
        boldStyle.Font.IsBold = true;

        var centerTextStyle = wb.CreateStyle();
        centerTextStyle.HorizontalAlignment = TextAlignmentType.Center;

        var percentTwoDecStyle = wb.CreateStyle();
        percentTwoDecStyle.Custom = "0.00%";

        var percentSixDecStyle = wb.CreateStyle();
        percentSixDecStyle.Custom = "0.000000%";

        var thousandsCommaStyle = wb.CreateStyle();
        thousandsCommaStyle.Custom = "#,##0";

        return new Dictionary<string, Style>(StringComparer.OrdinalIgnoreCase)
        {
            { "bold", boldStyle },
            { "centerText", centerTextStyle },
            { "percentTwoDec", percentTwoDecStyle },
            { "percentSixDec", percentSixDecStyle },
            { "thousandsComma", thousandsCommaStyle }
        };
    }
    private static void ApplySnapshotBorders(Worksheet ws, SosSettingItem itemSettings, int swingNomCount, int baseloadNomCount, int transfersColumnIndex)
    {
        int firstMarketColIdx = itemSettings.MainDisplayInfos.Max(x => x.Order);
        int maxDataRowIdx = ws.Cells.MaxDataRow + 1;
        int maxDataColIdx = ws.Cells.MaxDataColumn + 1;

        var headersRange = ws.Cells.CreateRange(5, 0, 1, maxDataColIdx);
        headersRange.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);

        ApplyAreaOutlineBorder(ws, 0, firstMarketColIdx, maxDataRowIdx, swingNomCount);
        ApplyAreaOutlineBorder(ws, 0, firstMarketColIdx + swingNomCount, maxDataRowIdx, baseloadNomCount);
        ApplyAreaOutlineBorder(ws, 0, transfersColumnIndex, maxDataRowIdx, 1);
    }

    private static void ApplyAreaOutlineBorder(Worksheet ws, int startRow, int startCol, int rowCount, int colCount)
    {
        if (rowCount <= 0 || colCount <= 0)
            return;

        var range = ws.Cells.CreateRange(startRow, startCol, rowCount, colCount);
        range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thick, Color.Black);
        range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thick, Color.Black);
        range.SetOutlineBorder(BorderType.LeftBorder, CellBorderType.Thick, Color.Black);
        range.SetOutlineBorder(BorderType.RightBorder, CellBorderType.Thick, Color.Black);
    }

    private static void CreateSnapshotDataColumns(List<SosItem> visibleItems, SosSettingItem itemSettings, Worksheet ws, Dictionary<string, Style> styles, List<PipelineContract> pipelineContracts, List<IdName> deliveryMeters)
    {
        var thousandsCommaStyle = styles["thousandsComma"];
        var styleMap = new Dictionary<string, Style>(StringComparer.OrdinalIgnoreCase)
        {
            { "Deal Volume", thousandsCommaStyle },
            { "Unsch'd Volume", thousandsCommaStyle },
            { "Total Receipt Volume", thousandsCommaStyle },
            { "Nom Receipt Volume", thousandsCommaStyle },
            { "Ptr Receipt Volume", thousandsCommaStyle },
            { "Total Delivery Volume", thousandsCommaStyle },
            { "Nom Delivery Volume", thousandsCommaStyle },
            { "Nom Fuel Amount", thousandsCommaStyle },
            { "Ptr Fuel Amount", thousandsCommaStyle },
            { "PTR Amount", thousandsCommaStyle },
            { "Del. Volume Market Deal Vol", thousandsCommaStyle },
            { "Cumulative Receipt Volume", thousandsCommaStyle },
            { "Nom Fuel %", styles["percentTwoDec"] },
            { "Ptr Fuel %", styles["percentTwoDec"] },
            { "PTR %", styles["percentSixDec"] }
        };

        var delVolSum = visibleItems.Sum(x => x.DeliveryVol);
        foreach (var headerName in itemSettings.MainDisplayInfos)
        {
            if (headerName.DisplayName == "Del. Volume Market Deal Vol")
            {
                ws.Cells[4, headerName.Order - 1].PutValue("Del. Volume Market Deal Vol");
                var sumCell = ws.Cells[5, headerName.Order - 1];
                sumCell.PutValue(delVolSum);
                sumCell.SetStyle(thousandsCommaStyle);
            }
            else
            {
                ws.Cells[5, headerName.Order - 1].PutValue(headerName.DisplayName);
            }
        }

        const int dataStartRow = 6;
        int currentRow = dataStartRow;
        foreach (var item in visibleItems)
        {
            foreach (var prop in item.GetType().GetProperties())
            {
                var lowercasePropName = char.ToLower(prop.Name[0]) + prop.Name[1..];
                var matchingHeader = itemSettings.MainDisplayInfos.FirstOrDefault(x => x.PropName == lowercasePropName);
                if (matchingHeader == null)
                    continue;

                var itemValue = prop.GetValue(item);
                object? cellValue = itemValue;
                if (itemValue != null)
                {
                    if (lowercasePropName == "pipeContractId")
                    {
                        cellValue = pipelineContracts.FirstOrDefault(x => x.Id == (int)itemValue)?.ContractId ?? "";
                    }
                    else if (lowercasePropName == "ptrDeliveryMeterId")
                    {
                        cellValue = deliveryMeters.FirstOrDefault(x => x.Id == (int)itemValue)?.Name ?? "";
                    }
                    else if (lowercasePropName == "ptrContractId")
                    {
                        cellValue = pipelineContracts.FirstOrDefault(x => x.Id == (int)itemValue && x.IsPtrContract == true)?.ContractId ?? "";
                    }
                }

                var cell = ws.Cells[currentRow, matchingHeader.Order - 1];
                cell.PutValue(cellValue ?? "");

                if (styleMap.TryGetValue(matchingHeader.DisplayName, out var style))
                {
                    cell.SetStyle(style);
                }
            }
            currentRow++;
        }
    }

    private (int lastColumn, int swingCount, int baseloadCount) CreateSnapshotNomsColumns(List<SosItem> visibleItems, List<IdName> deliveryMeters, int pipeId, Worksheet ws, SosSettingItem itemSettings, Dictionary<string, Style> styles)
    {
        var thousandsCommaStyle = styles["thousandsComma"];
        var boldStyle = styles["bold"];
        var centerTextStyle = styles["centerText"];

        Style boldThousandsCommaStyle = ws.Workbook.CreateStyle();
        boldThousandsCommaStyle.Font.IsBold = true;
        boldThousandsCommaStyle.Custom = "#,##0";

        var sosMarkets = SosDatabaseHelper.GetMarketsAsync(pipeId, deliveryPointId, fromDate).Result;
        int? firstSavedDeliveryMeterId = sosMarkets.FirstOrDefault(x => x.MarketDeliveryMeterId != null)?.MarketDeliveryMeterId ?? null;
        //the default delivery meter is the first saved delivery meter, or the first delivery meter in the list when there is only one, otherwise null so that the user is forced to pick
        int? defaultDeliveryMeterId = firstSavedDeliveryMeterId != null ? firstSavedDeliveryMeterId.Value : deliveryMeters.Count == 1 ? deliveryMeters.First().Id : null;

        foreach (var mkt in sosMarkets)
        {
            mkt.Guid = Guid.NewGuid().ToString("N")[..8];
            //if the market delivery meter is missing then set it to the default delivery meter, otherwise leave it blank for the user to pick
            mkt.MarketDeliveryMeterId ??= defaultDeliveryMeterId ?? null;
        }
        int startColumnForNoms = itemSettings.MainDisplayInfos.Max(x => x.Order);
        int lastHeaderColumn = startColumnForNoms;
        var uniqueNoms = visibleItems.SelectMany(x => x.Noms).DistinctBy(x => new { x.MarketDealId, x.MarketDeliveryMeterId }).ToList();
        var swingNomCount = uniqueNoms.Count(x => x.MarketDealType == "Swing");
        var baseloadNomCount = uniqueNoms.Count(x => x.MarketDealType == "Baseload");

        if (swingNomCount > 0)
        {
            ws.Cells.Merge(0, lastHeaderColumn, 1, swingNomCount);
            ws.Cells[0, lastHeaderColumn].PutValue("Swing Markets");
            ws.Cells[0, lastHeaderColumn].SetStyle(centerTextStyle);
        }
        if (baseloadNomCount > 0)
        {
            ws.Cells.Merge(0, lastHeaderColumn + swingNomCount, 1, baseloadNomCount);
            ws.Cells[0, lastHeaderColumn + swingNomCount].PutValue("Baseload Markets");
            ws.Cells[0, lastHeaderColumn + swingNomCount].SetStyle(centerTextStyle);
        }

        foreach (var nom in uniqueNoms)
        {
            var marketDeal = sosMarkets.Where(x =>
                x.MarketDealId == nom.MarketDealId &&
                x.MarketDeliveryMeterId == nom.MarketDeliveryMeterId
            ).FirstOrDefault();
            var marketDealVol = marketDeal?.MarketDealVolume ?? 0;
            var marketNomVol = visibleItems.Where(x => x.Noms.Any(y => y.MarketDealId == nom.MarketDealId && y.MarketDeliveryMeterId == nom.MarketDeliveryMeterId)).Sum(x => x.Noms.Where(y => y.MarketDealId == nom.MarketDealId && y.MarketDeliveryMeterId == nom.MarketDeliveryMeterId).Sum(y => y.Volume));
            var deliveryMeterName = deliveryMeters.Where(x => x.Id == nom.MarketDeliveryMeterId).FirstOrDefault()?.Name ?? "";

            ws.Cells[1, lastHeaderColumn].PutValue(nom.MarketTicket);
            ws.Cells[1, lastHeaderColumn].SetStyle(boldStyle);
            ws.Cells[2, lastHeaderColumn].PutValue($"Del Mtr {deliveryMeterName}");
            ws.Cells[3, lastHeaderColumn].PutValue(marketDeal?.MarketCounterparty ?? "");
            ws.Cells[3, lastHeaderColumn].SetStyle(boldStyle);
            ws.Cells[4, lastHeaderColumn].PutValue($"Deal Vol {marketDealVol}");
            ws.Cells[4, lastHeaderColumn].SetStyle(thousandsCommaStyle);
            ws.Cells[5, lastHeaderColumn].PutValue($"Nom/Act Vol {marketNomVol}");
            ws.Cells[5, lastHeaderColumn].SetStyle(thousandsCommaStyle);

            int currentMarketRow = 6;
            foreach (var item in visibleItems)
            {
                var nomInfoForRow = item.Noms.Where(x => x.MarketDealId == nom.MarketDealId && x.MarketDeliveryMeterId == nom.MarketDeliveryMeterId).FirstOrDefault();
                if (nomInfoForRow != null)
                {
                    if (nomInfoForRow.Volume != null)
                    {
                        ws.Cells[currentMarketRow, lastHeaderColumn].PutValue(nomInfoForRow.Volume);
                    }

                    if (!string.IsNullOrWhiteSpace(nomInfoForRow.Notes) || nomInfoForRow.IsKeepWhole)
                    {
                        ws.Cells[currentMarketRow, lastHeaderColumn].SetStyle(boldThousandsCommaStyle);
                        int commentIndex = ws.Comments.Add(currentMarketRow, lastHeaderColumn);
                        Comment comment = ws.Comments[commentIndex];
                        comment.Note = nomInfoForRow.Notes + (nomInfoForRow.IsKeepWhole ? "\nKeep Whole \u2714" : "");
                        comment.AutoSize = true;
                    }
                    else
                    {
                        ws.Cells[currentMarketRow, lastHeaderColumn].SetStyle(thousandsCommaStyle);
                    }
                }
                currentMarketRow++;
            }
            lastHeaderColumn++;
        }

        return (lastHeaderColumn, swingNomCount, baseloadNomCount);
    }

    private static void CreateSnapshotTransfersColumn(List<SosItem> visibleItems, Worksheet ws, int lastHeaderColumn, Dictionary<string, Style> styles)
    {
        var thousandsCommaStyle = styles["thousandsComma"];
        var boldStyle = styles["bold"];

        var transfersSum = visibleItems.Select(x => x.TransferSumVol).Sum();
        Column transfersColumn = ws.Cells.Columns[lastHeaderColumn];
        Cell transfersHeaderCell = ws.Cells[0, lastHeaderColumn];
        transfersHeaderCell.PutValue("Transfers");
        transfersHeaderCell.SetStyle(boldStyle);
        ws.Cells[4, lastHeaderColumn].PutValue(("Nom/Act Vol"));
        ws.Cells[5, lastHeaderColumn].PutValue(transfersSum);
        ws.Cells[5, lastHeaderColumn].SetStyle(thousandsCommaStyle);
        int currentTransfersRow = 6;
        foreach (var item in visibleItems)
        {
            ws.Cells[currentTransfersRow, lastHeaderColumn].PutValue(item.TransferSumVol);
            ws.Cells[currentTransfersRow, lastHeaderColumn].SetStyle(thousandsCommaStyle);
            currentTransfersRow++;
        }
    }
    private void SaveSnapshotToDB(string fileNameOriginal, string fileNameOnDisk, int productId, int pipeId)
    {
        using var dbContext = Main.CreateContext();
        var nomDate = fromDate;
        var snapshot = new SosSnapshot
        {
            NomDate = nomDate,
            ProductId = productId,
            PipeId = pipeId,
            PointId = deliveryPointId,
            FileNameOriginal = fileNameOriginal,
            FileNameOnDisk = fileNameOnDisk,
            CreatedBy = userId,
            CreatedTime = DateTime.UtcNow,
            Notes = saveNotes
        };
        dbContext.SosSnapshots.Add(snapshot);
        dbContext.SaveChanges();
    }


    public static MarketAndSupply? SaveSingleNomination(SosNomDetails nd, MyDbContext db, Dictionary<int, SosStartEndDatePair>? transferDeals = null, Dictionary<int, SosStartEndDatePair>? deals = null, List<GasSupply>? nomSupplies = null, List<GasMarket>? nomMarkets = null)
    {
        {
            DateOnly MarketStartDate = DateOnly.MinValue;
            DateOnly MarketEndDate = DateOnly.MaxValue;
            DateOnly SupplyStartDate = DateOnly.MinValue;
            DateOnly SupplyEndDate = DateOnly.MaxValue;

            transferDeals ??= db.TransferDeals
                    .Where(td => (nd.IsMarketTransferDeal && td.Id == nd.MarketDealID) ||
                                 (nd.IsSupplyTransferDeal && td.Id == nd.SupplyDealID))
                    .ToDictionary(n => n.Id, n => new SosStartEndDatePair(n.StartDate, n.EndDate));

            deals ??= db.Deals
              .Where(d => (!nd.IsMarketTransferDeal && d.Id == nd.MarketDealID) ||
                          (!nd.IsSupplyTransferDeal && d.Id == nd.SupplyDealID))
              .ToDictionary(n => n.Id, n => new SosStartEndDatePair(n.StartDate.GetValueOrDefault(), n.EndDate.GetValueOrDefault(DateOnly.MaxValue)));

            if (nd.MarketDealID.HasValue)
            {
                if (nd.IsMarketTransferDeal)
                {
                    var StartEndDatePair = transferDeals[nd.MarketDealID.Value];

                    MarketStartDate = StartEndDatePair.StartDate;
                    MarketEndDate = StartEndDatePair.EndDate;
                }
                else
                {
                    var StartEndDatePair = deals[nd.MarketDealID.Value];

                    MarketStartDate = StartEndDatePair.StartDate;
                    MarketEndDate = StartEndDatePair.EndDate;
                }
            }

            if (nd.IsSupplyTransferDeal)
            {
                var StartEndDatePair = transferDeals[nd.SupplyDealID.GetValueOrDefault()];

                SupplyStartDate = StartEndDatePair.StartDate;
                SupplyEndDate = StartEndDatePair.EndDate;
            }
            else
            {
                var StartEndDatePair = deals[nd.SupplyDealID.GetValueOrDefault()];

                SupplyStartDate = StartEndDatePair.StartDate;
                SupplyEndDate = StartEndDatePair.EndDate;
            }

            if (nd.Day >= SupplyStartDate && nd.Day <= SupplyEndDate &&
                (!nd.MarketDealID.HasValue || (nd.Day >= MarketStartDate && nd.Day <= MarketEndDate)))
            {
                GasSupply? SupplyNom = null;
                GasMarket? MarketNom = null;

                // for normal deals we find the supplies where the pipeline contract matches because there may be cloned supply rows for one deal each with only the contract different
                // for transfer deals there can only be one supply row and thus we find that row regardless of contract
                if (nd.IsSupplyTransferDeal)
                {
                    if (nomSupplies == null)
                    {
                        SupplyNom = db.GasSupplies
                            .FirstOrDefault(sup => sup.TransferDealId == nd.SupplyDealID &&
                                sup.Date == nd.Day &&
                                sup.MeterId == nd.ReceiptMeterID);
                    }
                    else
                    {
                        SupplyNom = nomSupplies
                            .FirstOrDefault(sup => sup.TransferDealId == nd.SupplyDealID &&
                                sup.Date == nd.Day &&
                                sup.MeterId == nd.ReceiptMeterID);
                    }
                }
                else
                {
                    if (nomSupplies == null)
                    {
                        if (nd.PipelineContractID == null)
                        {
                            SupplyNom = db.GasSupplies.FirstOrDefault(sup => sup.DealId == nd.SupplyDealID &&
                                sup.Date == nd.Day &&
                                sup.PointId == nd.ReceiptPointID &&
                                sup.MeterId == nd.ReceiptMeterID &&
                                sup.PipelineContractId == null);
                        }
                        else
                        {
                            SupplyNom = db.GasSupplies.FirstOrDefault(sup => sup.DealId == nd.SupplyDealID &&
                                sup.Date == nd.Day &&
                                sup.PointId == nd.ReceiptPointID &&
                                sup.MeterId == nd.ReceiptMeterID &&
                                sup.PipelineContractId.GetValueOrDefault() == nd.PipelineContractID.GetValueOrDefault());
                        }
                    }
                    else
                    {
                        if (nd.PipelineContractID == null)
                        {
                            SupplyNom = nomSupplies.FirstOrDefault(sup => sup.DealId == nd.SupplyDealID &&
                                sup.Date == nd.Day &&
                                sup.PointId == nd.ReceiptPointID &&
                                sup.MeterId == nd.ReceiptMeterID &&
                                sup.PipelineContractId == null);
                        }
                        else
                        {
                            SupplyNom = nomSupplies.FirstOrDefault(sup => sup.DealId == nd.SupplyDealID &&
                                sup.Date == nd.Day &&
                                sup.PointId == nd.ReceiptPointID &&
                                sup.MeterId == nd.ReceiptMeterID &&
                                sup.PipelineContractId.GetValueOrDefault() == nd.PipelineContractID.GetValueOrDefault());
                        }
                    }
                }
                if (nd.MarketDealID.HasValue)
                {
                    if (nd.IsMarketTransferDeal)
                    {
                        if (nomMarkets == null)
                            MarketNom = db.GasMarkets.FirstOrDefault(mkt => mkt.TransferDealId == nd.MarketDealID && mkt.Date == nd.Day && mkt.MeterId == nd.DeliveryMeterId);
                        else
                            MarketNom = nomMarkets.FirstOrDefault(mkt => mkt.TransferDealId == nd.MarketDealID && mkt.Date == nd.Day && mkt.MeterId == nd.DeliveryMeterId);
                    }
                    else
                    {
                        if (nomMarkets == null)
                            MarketNom = db.GasMarkets.FirstOrDefault(mkt => mkt.DealId == nd.MarketDealID && mkt.Date == nd.Day && mkt.MeterId == nd.DeliveryMeterId);
                        else
                            MarketNom = nomMarkets.FirstOrDefault(mkt => mkt.DealId == nd.MarketDealID && mkt.Date == nd.Day && mkt.MeterId == nd.DeliveryMeterId);
                    }
                }

                GasSupply snom;

                if (SupplyNom != null)
                {
                    snom = SupplyNom;
                }
                else
                {
                    snom = new GasSupply();

                    if (nomSupplies != null && nd.IncludeInSave)
                        nomSupplies.Add(snom);

                    if (nd.IsSupplyTransferDeal)
                        snom.TransferDealId = nd.SupplyDealID;
                    else
                    {
                        snom.DealId = nd.SupplyDealID;
                        snom.PointId = nd.ReceiptPointID;
                    }
                    snom.Date = nd.Day;
                    snom.MeterId = nd.ReceiptMeterID;
                    snom.CreatedBy = nd.UserID ?? 0;
                    snom.CreatedTime = DateTime.UtcNow;
                }

                snom.PipelineCycleId = 1;
                snom.PipelineContractId = nd.PipelineContractID;
                snom.PtrDeliveryMeterId = nd.PtrDeliveryMeterID;
                snom.PtrPipelineContractId = nd.PtrPipelineContractID;
                snom.ActNumber = nd.ActNumber;
                snom.Comment = nd.SupplyNotes;
                snom.SourceNotes = nd.SupplySourceNotes;
                snom.Rank = FastValues.DefaultSosRank;
                snom.Ptr = nd.IsManualPtr ? nd.PTR : null;
                snom.SavedBy = nd.UserID ?? 0;
                snom.SavedTime = DateTime.UtcNow;

                GasMarket? mnom = null;

                if (nd.DeliveryMeterId > 0)
                {
                    if (MarketNom != null)
                        mnom = MarketNom;
                    else
                    {
                        mnom = new GasMarket();

                        if (nomMarkets != null)
                        {
                            var existingMarketNom = db.GasMarkets.FirstOrDefault(mn => mn.DealId == nd.MarketDealID && mn.Date == nd.Day && (nd.IsMarketTransferDeal ? mn.TransferDealId == nd.MarketDealID : mn.TransferDealId == null) && mn.PointId == nd.DeliveryPointId);

                            if (existingMarketNom != null)
                                mnom = existingMarketNom;
                            else if (nd.IncludeInSave)
                                nomMarkets.Add(mnom);
                        }

                        if (nd.IsMarketTransferDeal)
                            mnom.TransferDealId = nd.MarketDealID;
                        else
                        {
                            mnom.DealId = nd.MarketDealID;
                            mnom.PointId = nd.DeliveryPointId;
                        }
                        mnom.Date = nd.Day;
                    }
                    mnom.MeterId = nd.DeliveryMeterId;
                    mnom.Rank = FastValues.DefaultSosRank;
                }

                MarketAndSupply myMarketAndSupply = new();

                if (nd.NominatedVolume != null && nd.DeliveryMeterId > 0 && mnom != null)
                {
                    var msup = new GasMarketSupply
                    {
                        Date = nd.Day,
                        UserId = nd.UserID ?? 0,
                        Comment = nd.NomNotes,
                        SupplyNom = snom,
                        MarketNom = mnom,
                        IsKeepWhole = nd.IsKeepWhole,
                        Volume = nd.NominatedVolume,
                        IsActual = false
                    };

                    if (nd.IncludeInSave)
                        db.GasMarketSupplies.Add(msup);

                    myMarketAndSupply.volume = nd.NominatedVolume;
                }
                else
                {
                    if (MarketNom == null && nd.DeliveryMeterId > 0)
                    {
                        if (nd.IncludeInSave && mnom != null)
                            db.GasMarkets.Add(mnom);
                    }
                    if (SupplyNom == null)
                    {
                        if (nd.IncludeInSave)
                            db.GasSupplies.Add(snom);
                    }
                }

                if (mnom != null)
                {
                    myMarketAndSupply.market = mnom;
                    myMarketAndSupply.supply = snom;
                }

                return myMarketAndSupply;
            }

            return null;
        }
    }
}
