using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Fast.Shared.Logic.Package;
using Fast.Shared.Logic.RateControl;
using Fast.Web.Logic.GasControl;

namespace Fast.Web.Logic.ReportSources;

public class Nominations(MyDbContext context, SpreadsheetDocument wb, string reportName, int dataSourceId, int filterId, string filterName, List<ReportFilterParameter> filterParams, int userInfoId)
    : ReportSourceBase(context, wb, reportName, dataSourceId, filterId, filterName, filterParams, userInfoId)
{
    private const uint BoldStyleIndex = 9;
    private const uint BoldSize11StyleIndex = 10;
    private const uint BoldItalicSize11StyleIndex = 11;
    private const uint BoldLightBlueStyleIndex = 12;
    private const uint BoldLightGoldenrodYellowStyleIndex = 13;
    private const uint LightGrayBackgroundStyleIndex = 14;
    private const uint ThinBorderStyleIndex = 15;
    private const uint ThickBorderStyleIndex = 16;
    private const uint BoldThinBorderStyleIndex = 17;
    private const uint CenteredWrappedTextStyleIndex = 18;
    private const uint BoldGreenCenteredWrappedTextStyleIndex = 19;
    private const uint NumberNoDecimalsStyleIndex2 = 20;
    private const uint NumberNoDecimalsStyleIndex3 = 21;
    private const uint NumberNoDecimalsLightGrayStyleIndex = 22;
    private const uint NumberNoDecimalsStyleIndex = 2;
    private const uint BoldGreenCenteredWrappedTextStyleIndex2 = 23;
    private const uint BoldLeftAlignedStyleIndex = 24;
    private const uint Bold10PtCenteredLightGoldenrodYellowStyleIndex = 25;
    private const uint LightGoldenrodYellowBackgroundOnlyStyleIndex = 26;
    private const uint BoldSize11YellowBgLeftAlignedStyleIndex = 27;
    private const uint Bold11PtCenteredLightGoldenrodYellowStyleIndex = 28;
    private const uint NumberNoDecimalsCenteredStyleIndex = 29;
    private const uint NumberNoDecimalsCenteredBoldStyleIndex = 30;
    private const uint NumberNoDecimalsLightGrayBackgroundCenteredStyleIndex = 31;
    private const uint NumberNoDecimalsBoldStyleIndex = 32;
    private const uint NumberNoDecimalsLightGrayBackgroundStyleIndex = 33;

    private OpenXmlHelper.StylesheetCache? stylesheetCache;

    public override async Task<List<ReportFilterParameter>> Run()
    {
        var orderedGasDays = GetOrderedGasDays();
        int pipelineId = int.Parse(filterParamsDic["Pipeline"].Value1);
        var hiddenDealsForPipeDic = await GetHiddenDealsForPipe(pipelineId);
        var metersByPointId = (from mp in db.VwMeterPointGas join m in db.Meters on mp.MeterId equals m.Id select new { mp, m }).AsNoTracking().ToLookup(x => x.mp.PointId, x => x.m);

        // Reinitialize the workbook to ensure clean Content_Types.xml and relationships
        OpenXmlHelper.ReinitializeWorkbook(wb);

        var stylesheet = wb.WorkbookPart!.WorkbookStylesPart!.Stylesheet!;
        stylesheetCache = new OpenXmlHelper.StylesheetCache(stylesheet);

        foreach (var gasDay in orderedGasDays)
        {
            var noms = await GetNominationsForGasDay(gasDay, pipelineId, metersByPointId, hiddenDealsForPipeDic);
            if (noms == null)
                return GetNewFilterParams(); // exit run

            var orderedNoms = (from n in noms orderby n.DeliveryMeterName, n.PipelineContract, n.SupplyCounterparty, n.SupplyTicketNum, n.SupplyTicketNum?.TicketSort() select n).ToList();
            CreateDryGasNoms(gasDay, orderedNoms);
        }

        stylesheetCache.CommitToStylesheet();

        FormatPrinting();
        return GetNewFilterParams();
    }

    private async Task<Dictionary<HiddenDealsForPipeKey, HiddenDealsForPipeValue>> GetHiddenDealsForPipe(int pipelineId)
    {
        var hiddenDealsForPipeLocal = await (from hd in db.SosHiddenDeals where hd.DeliveryPoint.PipelineId == pipelineId select hd).AsNoTracking().ToListAsync();

        var hiddenDealsForPipeList = (
            from hd in hiddenDealsForPipeLocal
            group hd by new
            {
                DeliveryPointID = hd.DeliveryPointId,
                ReceiptpointID = hd.ReceiptPointId.GetValueOrDefault(),
                ReceiptMeterID = hd.ReceiptMeterId
            } into g
            select new
            {
                g.Key.DeliveryPointID,
                g.Key.ReceiptpointID,
                g.Key.ReceiptMeterID,
                TransferDealsIds = g.Select(y => y.TransferDealId.GetValueOrDefault()),
                DealIds = g.Select(y => y.SupplyDealId.GetValueOrDefault())
            }
        ).ToList();

        var hiddenDealsForPipeDic = hiddenDealsForPipeList.ToDictionary(
        x => new HiddenDealsForPipeKey
        {
            DeliveryPointID = x.DeliveryPointID,
            ReceiptpointID = x.ReceiptpointID,
            ReceiptMeterID = x.ReceiptMeterID
        },
        x => new HiddenDealsForPipeValue
        {
            TransferDealsIds = x.TransferDealsIds,
            DealIds = x.DealIds
        }
        );

        return hiddenDealsForPipeDic;
    }

    private async Task<List<GasMarketSupply>> GetMarketSupplyForDay(DateOnly gasDay, int pipelineID)
    {
        var MarketSupplies = await (
                from ms in db.GasMarketSupplies
                    .Include(x => x.SupplyNom)
                    .ThenInclude(x => x.Deal)
                    .ThenInclude(x => x!.Counterparty)
                    .Include(x => x.SupplyNom)
                    .ThenInclude(x => x.Meter)
                    .Include(x => x.SupplyNom)
                    .ThenInclude(x => x.Point)
                    .Include(x => x.SupplyNom)
                    .ThenInclude(x => x.PtrDeliveryMeter)
                    .Include(x => x.SupplyNom)
                    .ThenInclude(x => x.PipelineContract)
                    .Include(x => x.SupplyNom)
                    .ThenInclude(x => x.PtrPipelineContract)
                    .Include(x => x.SupplyNom)
                    .ThenInclude(x => x.Duns)
                    .Include(x => x.SupplyNom)
                    .ThenInclude(x => x.TransferDeal)
                    .ThenInclude(x => x!.TransferMeterMap)
                    .ThenInclude(x => x.Meter1)
                    .ThenInclude(x => x.MeterProducts)
                    .ThenInclude(x => x.SourceZone)
                    .Include(x => x.SupplyNom)
                    .ThenInclude(x => x.TransferDeal)
                    .ThenInclude(x => x!.TransferMeterMap)
                    .ThenInclude(x => x.Meter2)
                    .ThenInclude(x => x.MeterProducts)
                    .ThenInclude(x => x.SourceZone)
                    .Include(x => x.MarketNom)
                    .ThenInclude(x => x.Deal)
                    .ThenInclude(x => x!.Counterparty)
                    .Include(x => x.MarketNom)
                    .ThenInclude(x => x.Deal)
                    .ThenInclude(x => x!.PriceIndex)
                    .Include(x => x.MarketNom)
                    .ThenInclude(x => x.Deal)
                    .ThenInclude(x => x!.Point)
                    .Include(x => x.MarketNom)
                    .ThenInclude(x => x.Meter)
                    .Include(x => x.MarketNom)
                    .ThenInclude(x => x.TransferDeal)
                    .ThenInclude(x => x!.TransferMeterMap)
                    .ThenInclude(x => x.Meter1)
                    .ThenInclude(x => x.MeterProducts)
                    .ThenInclude(x => x.SourceZone)
                    .Include(x => x.MarketNom)
                    .ThenInclude(x => x.TransferDeal)
                    .ThenInclude(x => x!.TransferMeterMap)
                    .ThenInclude(x => x.Meter2)
                    .ThenInclude(x => x.MeterProducts)
                    .ThenInclude(x => x.SourceZone)
                let IsMarketMeter1Supply = ms.MarketNom.TransferDeal != null && ms.MarketNom.TransferDeal.IsMeter1Supply
                let marketXferM1product = ms.MarketNom.TransferDeal!.TransferMeterMap.Meter1.MeterProducts
                    .Where(x => x.ProductId == (int)Enums.Product.NaturalGas && x.EffectiveDate <= gasDay).OrderByDescending(x => x.EffectiveDate).First()
                let marketXferM2product = ms.MarketNom.TransferDeal!.TransferMeterMap.Meter2.MeterProducts
                    .Where(x => x.ProductId == (int)Enums.Product.NaturalGas && x.EffectiveDate <= gasDay).OrderByDescending(x => x.EffectiveDate).First()
                let marketXferM1pipeId = marketXferM1product.SourceZone != null ? marketXferM1product.SourceZone!.PipelineId : 0
                let marketXferM2pipeId = marketXferM2product.SourceZone != null ? marketXferM2product.SourceZone!.PipelineId : 0
                where ms.Date == gasDay &&
                (
                   (ms.MarketNom.DealId != null && ms.MarketNom.Deal!.PipelineId == pipelineID)
                   || (ms.MarketNom.DealId == null && IsMarketMeter1Supply
                       && marketXferM2pipeId == pipelineID)
                   || (ms.MarketNom.DealId == null
                       && !IsMarketMeter1Supply
                       && marketXferM1pipeId == pipelineID)
                )
                select ms
            ).AsNoTracking().ToListAsync();

        return MarketSupplies;
    }

    private async Task<List<Nomination>?> GetNominationsForGasDay(DateOnly gasDay, int pipelineId, ILookup<int?, Meter> metersByPointId, Dictionary<HiddenDealsForPipeKey, HiddenDealsForPipeValue> hiddenDealsForPipeDic)
    {
        RateCalculator rateCalculator = await RateCalculator.GetInstanceAsync(Enums.Product.NaturalGas);
        List<GasMarketSupply> marketSupplies = await GetMarketSupplyForDay(gasDay, pipelineId);
        List<Nomination> currentDayNominations = [];

        if (marketSupplies.Count == 0)
        {
            OpenXmlHelper.AddOrModifyInfoSheet(wb, "A4", "No data for the selected date and pipeline");
        }

        foreach (var msup in marketSupplies)
        {
            Nomination nom = new();
            Meter? DeliveryMeter;
            Meter? ReceiptMeter;
            Counterparty? SupplyCounterparty = null;
            int? SupplyCounterpartyID = null;
            Counterparty? MarketCounterparty = null;
            bool IsSupplyTransferDeal;
            bool IsMarketTransferDeal;

            if (msup.SupplyNom.DealId == null)
            {
                IsSupplyTransferDeal = true;
            }
            else
            {
                IsSupplyTransferDeal = false;
            }

            if (msup.MarketNom.DealId == null)
            {
                IsMarketTransferDeal = true;
            }
            else
            {
                IsMarketTransferDeal = false;
            }

            if (!IsMarketTransferDeal)
            {
                if (metersByPointId.Contains(msup.MarketNom.Deal?.PointId))
                    DeliveryMeter = metersByPointId[msup.MarketNom.Deal?.PointId].First();
                else
                {
                    AddMissingMapMsg(msup.MarketNom.Deal?.Point?.Name ?? "");
                    return null;
                }
                MarketCounterparty = msup.MarketNom.Deal?.Counterparty;
            }
            else if (msup.MarketNom.TransferDeal?.IsMeter1Supply ?? false)
            {
                DeliveryMeter = msup.MarketNom.TransferDeal.TransferMeterMap.Meter2;
            }
            else
            {
                DeliveryMeter = msup.MarketNom.TransferDeal?.TransferMeterMap.Meter1;
            }

            if (!IsSupplyTransferDeal)
            {
                SupplyCounterparty = msup.SupplyNom.Deal?.Counterparty;
                SupplyCounterpartyID = SupplyCounterparty?.Id;
            }
            else
            {
                SupplyCounterpartyID = GasControlHelper.GetTransferSupplyCounterpartyID(db, msup.SupplyNom.TransferDealId.GetValueOrDefault(), gasDay);
            }

            ReceiptMeter = msup.SupplyNom.Meter;

            nom.ReceiptMeterName = ReceiptMeter?.Name ?? "";
            nom.DeliveryMeterName = DeliveryMeter?.Name ?? "";
            HiddenDealsForPipeKey key = new() { DeliveryPointID = msup.MarketNom.Deal == null ? 0 : msup.MarketNom.Deal.PointId.GetValueOrDefault(), ReceiptpointID = msup.SupplyNom.PointId.GetValueOrDefault(), ReceiptMeterID = ReceiptMeter?.Id ?? 0 };

            if (hiddenDealsForPipeDic.ContainsKey(key))
            {
                // We dont want to show the deal if it is hidden.
                if (msup.SupplyNom.DealId.HasValue)
                {
                    if (hiddenDealsForPipeDic[key].DealIds.ToList().Contains(msup.SupplyNom.DealId.GetValueOrDefault()))
                        continue;
                }
                else if (hiddenDealsForPipeDic[key].TransferDealsIds.ToList().Contains(msup.SupplyNom.TransferDealId.GetValueOrDefault()))
                    continue;
            }

            Meter? PtrDeliveryMeter = msup.SupplyNom.PtrDeliveryMeter;

            nom.PtrDeliveryMeterName = PtrDeliveryMeter != null ? PtrDeliveryMeter.Name : "";
            nom.ReceiptMeterNumber = ReceiptMeter?.MeterProducts
                .Where(x => x.ProductId == (int)Enums.Product.NaturalGas && x.EffectiveDate <= gasDay).OrderByDescending(x => x.EffectiveDate).FirstOrDefault()?.Number ?? "";
            nom.DeliveryMeterNumber = DeliveryMeter?.MeterProducts
                .Where(x => x.ProductId == (int)Enums.Product.NaturalGas && x.EffectiveDate <= gasDay).OrderByDescending(x => x.EffectiveDate).FirstOrDefault()?.Number ?? "";
            nom.PtrDeliveryMeterNumber = PtrDeliveryMeter != null ? PtrDeliveryMeter?.MeterProducts
                .Where(x => x.ProductId == (int)Enums.Product.NaturalGas && x.EffectiveDate <= gasDay).OrderByDescending(x => x.EffectiveDate).FirstOrDefault()?.Number ?? "" : "";
            nom.PipelineContract = msup.SupplyNom.PipelineContract == null ? "No Pipeline Contract" : msup.SupplyNom.PipelineContract.ContractId;
            nom.PtrPipelineContract = msup.SupplyNom.PtrPipelineContract == null ? "" : msup.SupplyNom.PtrPipelineContract.ContractId;

            nom.NomFuelPercent = Util.Nz(rateCalculator.GetFuelPercent(msup.SupplyNom.PipelineContractId, gasDay, ReceiptMeter?.Id ?? 0, DeliveryMeter?.Id ?? 0));
            if (PtrDeliveryMeter != null)
                nom.PtrFuelPercent = Util.Nz(rateCalculator.GetFuelPercent(msup.SupplyNom.PtrPipelineContractId, gasDay, ReceiptMeter?.Id ?? 0, PtrDeliveryMeter.Id));
            else
                nom.PtrFuelPercent = 0;
            nom.PtrPercent = Util.Nz(rateCalculator.GetPtrPercent(gasDay, ReceiptMeter?.Id ?? 0, (PtrDeliveryMeter == null ? (int?)null : (int?)PtrDeliveryMeter.Id), SupplyCounterpartyID, msup.SupplyNom.DealId, msup.SupplyNom.TransferDealId, msup.SupplyNom.PipelineContractId));

            nom.Duns = msup.SupplyNom.Duns != null ? msup.SupplyNom.Duns.Duns : "";
            nom.Volume = msup.Volume.GetValueOrDefault();

            int SupplyNomID = msup.SupplyNomId;
            nom.Delivered = (
                from ms in marketSupplies
                where ms.SupplyNomId == SupplyNomID
                && (ms.MarketNom.DealId.HasValue ? metersByPointId[ms.MarketNom.Deal?.PointId].FirstOrDefault()?.Id ?? 0 :
                       ms.MarketNom.TransferDeal?.IsMeter1Supply ?? false ? ms.MarketNom.TransferDeal.TransferMeterMap.Meter2.Id : ms.MarketNom.TransferDeal?.TransferMeterMap.Meter1.Id)
                   == DeliveryMeter?.Id
                select ms.Volume.GetValueOrDefault()
            ).Sum();

            nom.PtrAmt = (int)Math.Round((double)nom.Delivered / (1.0 - nom.PtrPercent) - (double)nom.Delivered, 0);
            nom.NomFuelAmt = (int)Math.Round((double)nom.Delivered / (1.0 - nom.NomFuelPercent) - (double)nom.Delivered, 0);
            nom.PtrFuelAmt = (int)Math.Round((double)nom.PtrAmt / (1.0 - nom.PtrFuelPercent) - (double)nom.PtrAmt, 0);

            nom.WellheadPtrTotal = nom.PtrAmt + nom.PtrFuelAmt;

            nom.WellheadTotal = nom.Delivered + nom.NomFuelAmt + nom.WellheadPtrTotal;

            nom.MarketCounterparty = !IsMarketTransferDeal ? MarketCounterparty?.ShortName ?? "" : "TransferCP";
            nom.SupplyCounterparty = !IsSupplyTransferDeal ? SupplyCounterparty?.ShortName ?? "" : "TransferCP";

            if (IsMarketTransferDeal)
                nom.MarketDealType = "Transfer";
            else
            {
                Deal? d = msup.MarketNom.Deal;

                if (d != null && (d.PhysicalDealTypeId == 2 || d.PhysicalDealTypeId == 7) && d.FixedPriceButton == 0 && d.PriceIndex?.IndexTypeId == (int)Enums.MarketIndexType.Daily)
                    // if baseload or swing and daily index
                    nom.MarketDealType = "Swing";
                else if (d != null && (d.PhysicalDealTypeId == 2 || d.PhysicalDealTypeId == 7) && d.FixedPriceButton == 1 && d.StartDate.HasValue && d.EndDate.HasValue && ((d.EndDate.Value.ToDateTime(TimeOnly.MinValue) - d.StartDate.Value.ToDateTime(TimeOnly.MinValue)).Days + 1) != DateTime.DaysInMonth(d.StartDate.Value.Year, d.StartDate.Value.Month))
                    // if baseload or swing and fixed price with date range that does not span entire month
                    nom.MarketDealType = "Swing";
                else if (d != null && (d.PhysicalDealTypeId == 2 || d.PhysicalDealTypeId == 7))
                    // baseload or swing
                    nom.MarketDealType = "Baseload";
                else
                    nom.MarketDealType = "Swing";
            }

            if (IsSupplyTransferDeal)
                nom.SupplyTicketNum = msup.SupplyNom.TransferDeal?.TicketNum;
            else
                nom.SupplyTicketNum = msup.SupplyNom.Deal?.TicketNum;

            if (IsMarketTransferDeal)
                nom.MarketTicketNum = msup.MarketNom.TransferDeal?.TicketNum;
            else
                nom.MarketTicketNum = msup.MarketNom.Deal?.TicketNum;
            nom.MarketNomID = msup.MarketNomId;
            nom.SupplyNomID = msup.SupplyNomId;
            nom.ActivityNumber = msup.SupplyNom.ActNumber;
            nom.Rank = msup.SupplyNom.Rank;

            currentDayNominations.Add(nom);
        }
        return currentDayNominations;
    }

    private void AddMissingMapMsg(string pointName)
    {
        OpenXmlHelper.RemoveAllWorksheets(wb);
        OpenXmlHelper.AddOrModifyInfoSheet(wb, "A3", string.Format("There are no meters mapped to point {0}", pointName));
    }

    private IEnumerable<DateOnly> GetOrderedGasDays()
    {
        var gasDays = new HashSet<DateOnly>();
        var dateRanges = GetDateRanges(Enums.DateStyle.DateRange, "Gas Day");
        foreach (var dateRange in dateRanges)
        {
            foreach (DateOnly day in Util.Date.EachDay(dateRange.FromDate, dateRange.ToDate))
            {
                gasDays.Add(day);
            }
        }

        return gasDays.OrderBy(x => x);
    }

    private void FormatPrinting()
    {
        var workbookPart = wb.WorkbookPart;
        var sheets = workbookPart!.Workbook!.GetFirstChild<Sheets>();

        foreach (var sheet in sheets!.Elements<Sheet>())
        {
            var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id!);
            SetNomPrint(worksheetPart);
        }
    }

    private void CreateDryGasNoms(DateOnly GasDay, List<Nomination> Nominations)
    {
        OpenXmlHelper.AddEmptySheet(wb, GasDay.ToString("M-d-yyyy"));

        var workbookPart = wb.WorkbookPart;
        var sheets = workbookPart?.Workbook?.GetFirstChild<Sheets>();
        var sheet = sheets?.Elements<Sheet>().FirstOrDefault(s => s.Name == GasDay.ToString("M-d-yyyy"));
        var worksheetPart = (WorksheetPart?)workbookPart?.GetPartById(sheet?.Id!);
        var sheetData = worksheetPart?.Worksheet?.GetFirstChild<SheetData>();

        if (worksheetPart == null || sheetData == null) return;

        const int SupplyColCount = 14;

        const int ColTicketNum = 1;
        const int ColReceiptMeterNum = 2;
        const int ColReceiptMeterName = 3;
        const int ColProducer = 4;
        const int ColRank = 5;
        const int ColDunsNum = 6;
        const int ColWellheadTotal = 7;
        const int ColWellheadPtrAmt = 8;
        const int ColDeliveredPtrAmt = 9;
        const int ColPTRPipelineContract = 10;
        const int ColFuelAmt = 11;
        const int ColDelivered = 12;
        const int ColDeliveryMeterNum = 13;
        const int ColActNumber = 14;

        const int RowStart = 3;

        int Row = RowStart;
        int Col = SupplyColCount + 1;

        static int GetFirstColWithTotal()
        {
            int min = ColWellheadTotal;
            if (ColWellheadPtrAmt < min)
                min = ColWellheadPtrAmt;
            if (ColDeliveredPtrAmt < min)
                min = ColDeliveredPtrAmt;
            if (ColPTRPipelineContract < min)
                min = ColPTRPipelineContract;
            if (ColFuelAmt < min)
                min = ColFuelAmt;
            if (ColDelivered < min)
                min = ColDelivered;
            return min;
        }

        int MaxMarketColumn = 0;

        OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, ColTicketNum, Row, "Ticket #", BoldGreenCenteredWrappedTextStyleIndex);
        OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, ColReceiptMeterNum, Row, "Rec\nMeter #", BoldGreenCenteredWrappedTextStyleIndex);
        OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, ColReceiptMeterName, Row, "Rec\nMeter", BoldGreenCenteredWrappedTextStyleIndex);
        OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, ColProducer, Row, "Producer", BoldGreenCenteredWrappedTextStyleIndex);
        OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, ColRank, Row, "Rank", BoldGreenCenteredWrappedTextStyleIndex);
        OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, ColDunsNum, Row, "Duns", BoldGreenCenteredWrappedTextStyleIndex);
        OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, ColDelivered, Row, "Delivered", BoldGreenCenteredWrappedTextStyleIndex);
        OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, ColWellheadPtrAmt, Row, "Wellhead\nPTR Amt", BoldGreenCenteredWrappedTextStyleIndex);
        OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, ColDeliveredPtrAmt, Row, "Delivered\nPTR Amt", BoldGreenCenteredWrappedTextStyleIndex);
        OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, ColPTRPipelineContract, Row, "PTR\nContract #", BoldGreenCenteredWrappedTextStyleIndex);
        OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, ColFuelAmt, Row, "Fuel\nAmt", BoldGreenCenteredWrappedTextStyleIndex);
        OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, ColWellheadTotal, Row, "Wellhead\nTotal", BoldGreenCenteredWrappedTextStyleIndex);
        OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, ColDeliveryMeterNum, Row, "Del.\nMeter #", BoldGreenCenteredWrappedTextStyleIndex);
        OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, ColActNumber, Row, "Act #", BoldGreenCenteredWrappedTextStyleIndex);

        void applyBorderToHeaderCell(int col)
        {
            OpenXmlHelper.ApplyOutlineBorderToRange(worksheetPart, stylesheetCache!, Row, col, 1, 1, BorderStyleValues.Thin);
        }

        applyBorderToHeaderCell(ColTicketNum);
        applyBorderToHeaderCell(ColReceiptMeterNum);
        applyBorderToHeaderCell(ColReceiptMeterName);
        applyBorderToHeaderCell(ColProducer);
        applyBorderToHeaderCell(ColRank);
        applyBorderToHeaderCell(ColDunsNum);
        applyBorderToHeaderCell(ColWellheadTotal);
        applyBorderToHeaderCell(ColWellheadPtrAmt);
        applyBorderToHeaderCell(ColDeliveredPtrAmt);
        applyBorderToHeaderCell(ColPTRPipelineContract);
        applyBorderToHeaderCell(ColFuelAmt);
        applyBorderToHeaderCell(ColDelivered);
        applyBorderToHeaderCell(ColDeliveryMeterNum);
        applyBorderToHeaderCell(ColActNumber);

        Row += 1;

        var _distinctPipelineContracts = (from e in Nominations select e.PipelineContract).Distinct();

        foreach (var _PipeContractName in _distinctPipelineContracts)
        {
            string _PipeContract = _PipeContractName;
            var PipeContractNoms = (from e in Nominations where e.PipelineContract == _PipeContract select e);
            string MyPipeContract = _PipeContractName;
            Dictionary<int, string> formulasContainer = new()
            {
                [ColDelivered] = string.Empty,
                [ColWellheadTotal] = string.Empty,
                [ColWellheadPtrAmt] = string.Empty,
                [ColDeliveredPtrAmt] = string.Empty,
                [ColFuelAmt] = string.Empty
            };

            Row += 1;

            var DistinctDeliveryMeters = (from e in PipeContractNoms select e.DeliveryMeterName).Distinct();

            int FirstContractMarketCount = 0;
            if (DistinctDeliveryMeters.Any())
            {
                string FirstDeliveryMeterName = DistinctDeliveryMeters.First();
                FirstContractMarketCount = (from e in Nominations where e.DeliveryMeterName == FirstDeliveryMeterName && e.PipelineContract == MyPipeContract select e.MarketNomID).Distinct().Count();
            }

            OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, 1, Row, "Contract #: " + _PipeContract, BoldLightBlueStyleIndex);
            OpenXmlHelper.MergeCells(worksheetPart, $"A{Row}", $"{OpenXmlHelper.GetColumnName(SupplyColCount + FirstContractMarketCount)}{Row}");

            int meterIndex = 0;
            foreach (var MeterName in DistinctDeliveryMeters)
            {
                meterIndex += 1;
                string DeliveryMeter = MeterName;
                var DeliveryMeterNoms = (from e in PipeContractNoms where e.DeliveryMeterName == DeliveryMeter select e);

                string MyMeterName = MeterName;

                var ThisContractDistinctMarkets = (
                    from e in Nominations
                    where e.DeliveryMeterName == MyMeterName && e.PipelineContract == MyPipeContract
                    select new { e.MarketTicketNum, e.MarketCounterparty, e.MarketDealType, e.MarketNomID }
                )
                    .Distinct()
                    .OrderBy(n => n.MarketDealType != "Swing")
                    .ThenBy(n => n.MarketDealType != "Baseload")
                    .ThenBy(n => n.MarketDealType != "Transfer")
                    .ThenBy(n => n.MarketCounterparty)
                    .ThenBy(n => n.MarketTicketNum)
                    .ThenBy(n => n.MarketTicketNum?.TicketSort());

                if (ThisContractDistinctMarkets.Count() > MaxMarketColumn)
                    MaxMarketColumn = ThisContractDistinctMarkets.Count();

                int SwingCount = 0;
                int BaseloadCount = 0;
                int TransferCount = 0;

                foreach (var Market in ThisContractDistinctMarkets)
                {
                    if (Market.MarketDealType == "Swing")
                        SwingCount += 1;
                    if (Market.MarketDealType == "Baseload")
                        BaseloadCount += 1;
                    if (Market.MarketDealType == "Transfer")
                        TransferCount += 1;
                }

                Row += 1;
                int PipeContractRow = Row;

                OpenXmlHelper.ApplyStyleToRangeWithPreservedBorder(worksheetPart, stylesheetCache!, PipeContractRow, 1, 3, SupplyColCount + ThisContractDistinctMarkets.Count(), LightGoldenrodYellowBackgroundOnlyStyleIndex);
                OpenXmlHelper.ApplyOutlineBorderToRange(worksheetPart, stylesheetCache!, PipeContractRow, 1, 3, SupplyColCount + ThisContractDistinctMarkets.Count(), BorderStyleValues.Thin);

                OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, 1, PipeContractRow, DeliveryMeter, BoldSize11YellowBgLeftAlignedStyleIndex);
                OpenXmlHelper.MergeCells(worksheetPart, $"A{PipeContractRow}", $"{OpenXmlHelper.GetColumnName(SupplyColCount)}{PipeContractRow}");

                Row += 3;

                var DistinctSupplies = (from e in DeliveryMeterNoms select e.SupplyNomID).Distinct();

                OpenXmlHelper.ApplyStyleToRangeWithPreservedBorder(worksheetPart, stylesheetCache!, Row, 1, DistinctSupplies.Count(), SupplyColCount + ThisContractDistinctMarkets.Count(), ThinBorderStyleIndex);
                OpenXmlHelper.ApplyOutlineBorderToRange(worksheetPart, stylesheetCache!, Row, 1, DistinctSupplies.Count(), SupplyColCount + ThisContractDistinctMarkets.Count(), BorderStyleValues.Thin);

                WriteBaseSwingHeader(worksheetPart, PipeContractRow, SupplyColCount + 1, DistinctSupplies.Count() + 3, SwingCount, BaseloadCount, TransferCount);

                bool IsAlternateRow = true;

                var firstSupplyRow = Row;
                var distinctSuppliesCount = DistinctSupplies.Count();

                foreach (var Supply in DistinctSupplies)
                {
                    int SupplyNomID = Supply;
                    var SupplyNoms = (from e in DeliveryMeterNoms where e.SupplyNomID == SupplyNomID select e);
                    var SupplyInfo = SupplyNoms.First();

                    OpenXmlHelper.InsertCell(ColTicketNum, Row, worksheetPart, SupplyInfo.SupplyTicketNum ?? "");
                    OpenXmlHelper.InsertCell(ColReceiptMeterNum, Row, worksheetPart, SupplyInfo.ReceiptMeterNumber ?? "");
                    OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, ColDelivered, Row, SupplyInfo.Delivered, NumberNoDecimalsStyleIndex);
                    OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, ColWellheadTotal, Row, SupplyInfo.WellheadTotal, NumberNoDecimalsStyleIndex);
                    OpenXmlHelper.InsertCell(ColReceiptMeterName, Row, worksheetPart, SupplyInfo.ReceiptMeterName ?? "");
                    OpenXmlHelper.InsertCell(ColProducer, Row, worksheetPart, SupplyInfo.SupplyCounterparty ?? "");
                    OpenXmlHelper.InsertCell(ColRank, Row, worksheetPart, SupplyInfo.Rank.ToString());
                    OpenXmlHelper.InsertCell(ColDeliveryMeterNum, Row, worksheetPart, SupplyInfo.DeliveryMeterNumber ?? "");
                    OpenXmlHelper.InsertCell(ColActNumber, Row, worksheetPart, SupplyInfo.ActivityNumber ?? "");
                    OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, ColWellheadPtrAmt, Row, SupplyInfo.WellheadPtrTotal, NumberNoDecimalsStyleIndex);
                    OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, ColDeliveredPtrAmt, Row, SupplyInfo.PtrAmt, NumberNoDecimalsStyleIndex);
                    OpenXmlHelper.InsertCell(ColPTRPipelineContract, Row, worksheetPart, SupplyInfo.PtrPipelineContract ?? "");
                    OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, ColFuelAmt, Row, SupplyInfo.NomFuelAmt, NumberNoDecimalsStyleIndex);
                    OpenXmlHelper.InsertCell(ColDunsNum, Row, worksheetPart, SupplyInfo.Duns ?? "");

                    Col = SupplyColCount + 1;

                    foreach (var Market in ThisContractDistinctMarkets)
                    {
                        string MarketTicketNum = Market.MarketTicketNum;
                        var Nom = (from e in SupplyNoms where e.MarketTicketNum == MarketTicketNum select e);

                        if (Nom.Any())
                        {
                            OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, Col, PipeContractRow + 1, Market.MarketCounterparty, Bold10PtCenteredLightGoldenrodYellowStyleIndex);
                            OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, Col, PipeContractRow + 2, Market.MarketTicketNum, Bold10PtCenteredLightGoldenrodYellowStyleIndex);
                            OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, Col, Row, Nom.First().Volume, NumberNoDecimalsCenteredStyleIndex);
                        }
                        Col += 1;
                    }

                    IsAlternateRow = !IsAlternateRow;

                    if (IsAlternateRow)
                    {
                        // supply
                        OpenXmlHelper.ApplyStyleToRangeWithPreservedBorder(worksheetPart, stylesheetCache!, Row, 1, 1, SupplyColCount, NumberNoDecimalsLightGrayBackgroundStyleIndex);
                        // market
                        OpenXmlHelper.ApplyStyleToRangeWithPreservedBorder(worksheetPart, stylesheetCache!, Row, SupplyColCount + 1, 1, ThisContractDistinctMarkets.Count(), NumberNoDecimalsLightGrayBackgroundCenteredStyleIndex);
                    }

                    Row += 1;
                }

                // Add thin column borders
                for (int col = ColTicketNum; col <= SupplyColCount; col++)
                {
                    OpenXmlHelper.ApplyOutlineBorderToRange(worksheetPart, stylesheetCache!, firstSupplyRow, col, distinctSuppliesCount, 1, BorderStyleValues.Thin);
                }

                var totalMeterCell = OpenXmlHelper.InsertCell(ColReceiptMeterNum, Row, worksheetPart, "Total Meter:" + MyMeterName);
                totalMeterCell.StyleIndex = BoldLeftAlignedStyleIndex;

                OpenXmlHelper.MergeCells(worksheetPart, $"{OpenXmlHelper.GetColumnName(ColReceiptMeterNum)}{Row}", $"{OpenXmlHelper.GetColumnName(GetFirstColWithTotal() - 1)}{Row}");

                string sumRange;

                sumRange = $"{OpenXmlHelper.GetColumnName(ColDelivered)}{Row - DistinctSupplies.Count()}:{OpenXmlHelper.GetColumnName(ColDelivered)}{Row - 1}";
                var deliveredSumCell = OpenXmlHelper.InsertFormulaCell(ColDelivered, Row, worksheetPart, $"SUM({sumRange})");
                deliveredSumCell.StyleIndex = NumberNoDecimalsBoldStyleIndex;

                sumRange = $"{OpenXmlHelper.GetColumnName(ColWellheadTotal)}{Row - DistinctSupplies.Count()}:{OpenXmlHelper.GetColumnName(ColWellheadTotal)}{Row - 1}";
                var wellheadTotalSumCell = OpenXmlHelper.InsertFormulaCell(ColWellheadTotal, Row, worksheetPart, $"SUM({sumRange})");
                wellheadTotalSumCell.StyleIndex = NumberNoDecimalsBoldStyleIndex;

                sumRange = $"{OpenXmlHelper.GetColumnName(ColWellheadPtrAmt)}{Row - DistinctSupplies.Count()}:{OpenXmlHelper.GetColumnName(ColWellheadPtrAmt)}{Row - 1}";
                var wellheadPtrAmtSumCell = OpenXmlHelper.InsertFormulaCell(ColWellheadPtrAmt, Row, worksheetPart, $"SUM({sumRange})");
                wellheadPtrAmtSumCell.StyleIndex = NumberNoDecimalsBoldStyleIndex;

                sumRange = $"{OpenXmlHelper.GetColumnName(ColDeliveredPtrAmt)}{Row - DistinctSupplies.Count()}:{OpenXmlHelper.GetColumnName(ColDeliveredPtrAmt)}{Row - 1}";
                var deliveredPtrAmtSumCell = OpenXmlHelper.InsertFormulaCell(ColDeliveredPtrAmt, Row, worksheetPart, $"SUM({sumRange})");
                deliveredPtrAmtSumCell.StyleIndex = NumberNoDecimalsBoldStyleIndex;

                sumRange = $"{OpenXmlHelper.GetColumnName(ColFuelAmt)}{Row - DistinctSupplies.Count()}:{OpenXmlHelper.GetColumnName(ColFuelAmt)}{Row - 1}";
                var fuelAmtSumCell = OpenXmlHelper.InsertFormulaCell(ColFuelAmt, Row, worksheetPart, $"SUM({sumRange})");
                fuelAmtSumCell.StyleIndex = NumberNoDecimalsBoldStyleIndex;

                for (int i = 0; i < ThisContractDistinctMarkets.Count(); i++)
                {
                    int marketCol = SupplyColCount + i + 1;
                    sumRange = $"{OpenXmlHelper.GetColumnName(marketCol)}{Row - DistinctSupplies.Count()}:{OpenXmlHelper.GetColumnName(marketCol)}{Row - 1}";
                    var marketSumCell = OpenXmlHelper.InsertFormulaCell(marketCol, Row, worksheetPart, $"SUM({sumRange})");
                    marketSumCell.StyleIndex = NumberNoDecimalsCenteredBoldStyleIndex;
                }

                formulasContainer[ColDelivered] += $"{OpenXmlHelper.GetColumnName(ColDelivered)}{Row}" + (meterIndex == DistinctDeliveryMeters.Count() ? "" : "+");
                formulasContainer[ColWellheadTotal] += $"{OpenXmlHelper.GetColumnName(ColWellheadTotal)}{Row}" + (meterIndex == DistinctDeliveryMeters.Count() ? "" : "+");
                formulasContainer[ColWellheadPtrAmt] += $"{OpenXmlHelper.GetColumnName(ColWellheadPtrAmt)}{Row}" + (meterIndex == DistinctDeliveryMeters.Count() ? "" : "+");
                formulasContainer[ColDeliveredPtrAmt] += $"{OpenXmlHelper.GetColumnName(ColDeliveredPtrAmt)}{Row}" + (meterIndex == DistinctDeliveryMeters.Count() ? "" : "+");
                formulasContainer[ColFuelAmt] += $"{OpenXmlHelper.GetColumnName(ColFuelAmt)}{Row}" + (meterIndex == DistinctDeliveryMeters.Count() ? "" : "+");
            }

            Row += 1;

            var totalContractCell = OpenXmlHelper.InsertCell(ColReceiptMeterNum, Row, worksheetPart, "Total Contract #:" + MyPipeContract);
            totalContractCell.StyleIndex = BoldLeftAlignedStyleIndex;

            OpenXmlHelper.MergeCells(worksheetPart, $"{OpenXmlHelper.GetColumnName(ColReceiptMeterNum)}{Row}", $"{OpenXmlHelper.GetColumnName(GetFirstColWithTotal() - 1)}{Row}");

            var contractDeliveredSumCell = OpenXmlHelper.InsertFormulaCell(ColDelivered, Row, worksheetPart, formulasContainer[ColDelivered]);
            contractDeliveredSumCell.StyleIndex = NumberNoDecimalsBoldStyleIndex;
            var contractWellheadTotalSumCell = OpenXmlHelper.InsertFormulaCell(ColWellheadTotal, Row, worksheetPart, formulasContainer[ColWellheadTotal]);
            contractWellheadTotalSumCell.StyleIndex = NumberNoDecimalsBoldStyleIndex;
            var contractWellheadPtrAmtSumCell = OpenXmlHelper.InsertFormulaCell(ColWellheadPtrAmt, Row, worksheetPart, formulasContainer[ColWellheadPtrAmt]);
            contractWellheadPtrAmtSumCell.StyleIndex = NumberNoDecimalsBoldStyleIndex;
            var contractDeliveredPtrAmtSumCell = OpenXmlHelper.InsertFormulaCell(ColDeliveredPtrAmt, Row, worksheetPart, formulasContainer[ColDeliveredPtrAmt]);
            contractDeliveredPtrAmtSumCell.StyleIndex = NumberNoDecimalsBoldStyleIndex;
            var contractFuelAmtSumCell = OpenXmlHelper.InsertFormulaCell(ColFuelAmt, Row, worksheetPart, formulasContainer[ColFuelAmt]);
            contractFuelAmtSumCell.StyleIndex = NumberNoDecimalsBoldStyleIndex;

            Row += 2;
        }

        var ws = worksheetPart.Worksheet;
        if (ws == null) return;

        OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, 1, 1, "Dry Gas Nominations  " + GasDay.ToString("M-d-yyyy") + " " + filterParamsDic["Pipeline"].Preview, BoldItalicSize11StyleIndex);
        OpenXmlHelper.MergeCells(worksheetPart, "A1", "J1");
        OpenXmlHelper.AutoFitColumns(ws, workbookPart!);

        // Set column A width because auto-size makes it too wide
        var columns = ws.GetFirstChild<Columns>() ?? new Columns();

        columns.Append(new Column()
        {
            Min = 1U,
            Max = 1U,
            Width = 12,
            CustomWidth = true
        });

        if (ws.GetFirstChild<Columns>() == null)
            ws.InsertBefore(columns, sheetData);

        FreezeRows(ws);
    }

    private void WriteBaseSwingHeader(WorksheetPart worksheetPart, int StartRow, int StartCol, int TotalRows, int SwingCount, int BaseloadCount, int TransferCount)
    {
        if (SwingCount > 0)
        {
            string startCell = $"{OpenXmlHelper.GetColumnName(StartCol)}{StartRow}";
            string endCell = $"{OpenXmlHelper.GetColumnName(StartCol + SwingCount - 1)}{StartRow}";
            OpenXmlHelper.MergeCells(worksheetPart, startCell, endCell);
            OpenXmlHelper.ApplyStyleToRangeWithPreservedBorder(worksheetPart, stylesheetCache!, StartRow, StartCol, TotalRows, SwingCount, ThickBorderStyleIndex);
            OpenXmlHelper.ApplyOutlineBorderToRange(worksheetPart, stylesheetCache!, StartRow, StartCol, TotalRows, SwingCount, BorderStyleValues.Thick);
            OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, StartCol, StartRow, "Swing", Bold11PtCenteredLightGoldenrodYellowStyleIndex);
        }
        StartCol += SwingCount;

        if (BaseloadCount > 0)
        {
            string startCell = $"{OpenXmlHelper.GetColumnName(StartCol)}{StartRow}";
            string endCell = $"{OpenXmlHelper.GetColumnName(StartCol + BaseloadCount - 1)}{StartRow}";
            OpenXmlHelper.MergeCells(worksheetPart, startCell, endCell);
            OpenXmlHelper.ApplyStyleToRangeWithPreservedBorder(worksheetPart, stylesheetCache!, StartRow, StartCol, TotalRows, BaseloadCount, ThickBorderStyleIndex);
            OpenXmlHelper.ApplyOutlineBorderToRange(worksheetPart, stylesheetCache!, StartRow, StartCol, TotalRows, BaseloadCount, BorderStyleValues.Thick);
            OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, StartCol, StartRow, "Baseload", Bold11PtCenteredLightGoldenrodYellowStyleIndex);
        }
        StartCol += BaseloadCount;

        if (TransferCount > 0)
        {
            string startCell = $"{OpenXmlHelper.GetColumnName(StartCol)}{StartRow}";
            string endCell = $"{OpenXmlHelper.GetColumnName(StartCol + TransferCount - 1)}{StartRow}";
            OpenXmlHelper.MergeCells(worksheetPart, startCell, endCell);
            OpenXmlHelper.ApplyStyleToRangeWithPreservedBorder(worksheetPart, stylesheetCache!, StartRow, StartCol, TotalRows, TransferCount, ThickBorderStyleIndex);
            OpenXmlHelper.ApplyOutlineBorderToRange(worksheetPart, stylesheetCache!, StartRow, StartCol, TotalRows, TransferCount, BorderStyleValues.Thick);
            OpenXmlHelper.ApplyStyleToCellWithPreservedBorder(worksheetPart, stylesheetCache!, StartCol, StartRow, "Transfer", Bold11PtCenteredLightGoldenrodYellowStyleIndex);
        }
    }

    private static void FreezeRows(Worksheet ws)
    {
        var sheetViews = new SheetViews();
        var sheetView = new SheetView() { WorkbookViewId = 0U };

        var pane = new Pane()
        {
            VerticalSplit = 3D,
            TopLeftCell = "A4",
            ActivePane = PaneValues.BottomLeft,
            State = PaneStateValues.Frozen
        };

        sheetView.Append(pane);
        sheetViews.Append(sheetView);

        ws.RemoveAllChildren<SheetViews>();
        ws.InsertAt(sheetViews, 0);
    }

    private static void SetNomPrint(WorksheetPart worksheetPart)
    {
        if (worksheetPart == null) return;

        var worksheet = worksheetPart.Worksheet;
        if (worksheet == null) return;

        worksheet.Elements<PrintOptions>().FirstOrDefault()?.Remove();
        worksheet.Elements<PageMargins>().FirstOrDefault()?.Remove();
        worksheet.Elements<PageSetup>().FirstOrDefault()?.Remove();
        worksheet.Elements<HeaderFooter>().FirstOrDefault()?.Remove();

        var sheetData = worksheetPart.Worksheet?.GetFirstChild<SheetData>();
        if (sheetData == null) return;

        var workbookPart = worksheetPart.GetParentParts().OfType<WorkbookPart>().FirstOrDefault();
        if (workbookPart == null) return;

        int sheetIndex = 0;
        string? sheetName = null;
        var sheets = workbookPart.Workbook?.Sheets?.Elements<Sheet>().ToList();
        if (sheets == null) return;

        for (int i = 0; i < sheets.Count; i++)
        {
            var sheet = sheets[i];
            if (sheet.Id == null) continue;

            var part = workbookPart.GetPartById(sheet.Id!);
            if (part == worksheetPart)
            {
                sheetName = sheet.Name?.Value;
                sheetIndex = i;
                break;
            }
        }

        if (string.IsNullOrEmpty(sheetName)) return;

        var rows = sheetData.Elements<Row>().ToList();
        int lastRow = 0;
        if (rows.Count != 0)
        {
            lastRow = (int)rows.Where(r => r.RowIndex != null).Max(r => r.RowIndex!.Value);
        }

        int lastCol = 0;
        foreach (var row in rows)
        {
            var cells = row.Elements<Cell>().ToList();
            if (cells.Count != 0)
            {
                foreach (var cell in cells)
                {
                    if (cell.CellReference != null && cell.CellReference.HasValue)
                    {
                        string? cellRef = cell.CellReference.Value;
                        if (!string.IsNullOrEmpty(cellRef))
                        {
                            string colRef = new(cellRef.TakeWhile(c => !char.IsDigit(c)).ToArray());
                            if (!string.IsNullOrEmpty(colRef))
                            {
                                int colIndex = OpenXmlHelper.GetColumnIndex(colRef);
                                if (colIndex > lastCol) lastCol = colIndex;
                            }
                        }
                    }
                }
            }
        }

        if (lastRow > 0 && lastCol > 0)
        {
            var definedNames = workbookPart.Workbook!.DefinedNames;
            if (definedNames == null)
            {
                definedNames = new DefinedNames();
                workbookPart.Workbook.AppendChild(definedNames);
            }

            var existingPrintArea = definedNames.Elements<DefinedName>().FirstOrDefault(dn => dn.Name == "_xlnm.Print_Area" && dn.LocalSheetId?.Value == (uint)sheetIndex);
            existingPrintArea?.Remove();

            var printAreaName = new DefinedName() { Name = "_xlnm.Print_Area", LocalSheetId = (UInt32Value)(uint)sheetIndex };
            printAreaName.Text = $"'{sheetName}'!$A$1:${OpenXmlHelper.GetColumnName(lastCol)}${lastRow}";
            definedNames.AppendChild(printAreaName);

            var existingTitles = definedNames.Elements<DefinedName>().FirstOrDefault(dn => dn.Name == "_xlnm.Print_Titles" && dn.LocalSheetId?.Value == (uint)sheetIndex);
            existingTitles?.Remove();

            var printTitles = new DefinedName() { Name = "_xlnm.Print_Titles", LocalSheetId = (UInt32Value)(uint)sheetIndex };
            printTitles.Text = $"'{sheetName}'!$3:$3";
            definedNames.AppendChild(printTitles);
        }

        foreach (var columnData in worksheet.Elements<Columns>().SelectMany(cols => cols.Elements<Column>()))
        {
            if (columnData.Width != null && columnData.Width.HasValue && columnData.Width > 100) columnData.Width = 100;
            if (columnData.Width != null) columnData.CustomWidth = true;
        }

        var sheetProperties = worksheet.Elements<SheetProperties>().FirstOrDefault();
        if (sheetProperties == null)
        {
            sheetProperties = new SheetProperties();
            worksheet.PrependChild(sheetProperties);
        }

        sheetProperties.PageSetupProperties = new PageSetupProperties { FitToPage = true, };

        var pageSetup = new PageSetup { Orientation = OrientationValues.Landscape, FitToWidth = (UInt32Value)1U, FitToHeight = (UInt32Value)0U };

        var printOptions = new PrintOptions();
        var pageMargins = new PageMargins { Left = 0.75, Right = 0.75, Top = 1.0, Bottom = 1.0, Header = 0.5, Footer = 0.5 };
        var headerFooter = new HeaderFooter();
        headerFooter.OddFooter = new OddFooter("&L&\"Times New Roman,Regular\"&10Printed on &D at &T&R&\"Times New Roman,Regular\"&10Page &P of &N");
        headerFooter.EvenFooter = new EvenFooter("&L&\"Times New Roman,Regular\"&10Printed on &D at &T&R&\"Times New Roman,Regular\"&10Page &P of &N");

        OpenXmlElement? predecessor = worksheet.GetFirstChild<SheetData>();
        var predecessorCandidateTypes = new[] { typeof(CustomSheetViews), typeof(MergeCells), typeof(PhoneticProperties), typeof(ConditionalFormatting), typeof(DataValidations), typeof(Hyperlinks) };

        foreach (var elementType in predecessorCandidateTypes)
        {
            var element = worksheet.Elements().LastOrDefault(e => e.GetType() == elementType);
            if (element != null) predecessor = element;
        }

        if (predecessor == null && worksheet.ChildElements.Count != 0)
            predecessor = worksheet.ChildElements[^1];
        else if (predecessor == null)
        {
            worksheet.Append(printOptions);
            worksheet.Append(pageMargins);
            worksheet.Append(pageSetup);
            worksheet.Append(headerFooter);
            return;
        }

        predecessor = worksheet.InsertAfter(printOptions, predecessor);
        predecessor = worksheet.InsertAfter(pageMargins, predecessor);
        predecessor = worksheet.InsertAfter(pageSetup, predecessor);
        worksheet.InsertAfter(headerFooter, predecessor);
    }

    private class HiddenDealsForPipeValue
    {
        public required IEnumerable<int> TransferDealsIds { get; set; }
        public required IEnumerable<int> DealIds { get; set; }
    }

    private class HiddenDealsForPipeKey : IEquatable<HiddenDealsForPipeKey>
    {
        public int DeliveryPointID;
        public int ReceiptpointID;
        public int ReceiptMeterID;

        public override bool Equals(object? obj)
        {
            return Equals(obj as HiddenDealsForPipeKey);
        }

        public bool Equals(HiddenDealsForPipeKey? other)
        {
            return DeliveryPointID == other?.DeliveryPointID &&
                   ReceiptpointID == other?.ReceiptpointID &&
                   ReceiptMeterID == other?.ReceiptMeterID;
        }

        public override int GetHashCode()
        {
            return HashCode.Combine(DeliveryPointID, ReceiptpointID, ReceiptMeterID);
        }

        public static bool operator ==(HiddenDealsForPipeKey pair1, HiddenDealsForPipeKey pair2)
        {
            return EqualityComparer<HiddenDealsForPipeKey>.Default.Equals(pair1, pair2);
        }

        public static bool operator !=(HiddenDealsForPipeKey pair1, HiddenDealsForPipeKey pair2)
        {
            return !(pair1 == pair2);
        }
    }

    private class Nomination
    {
        public string? ReceiptMeterName;
        public string? ReceiptMeterNumber;
        public string? DeliveryMeterName;
        public string? PtrDeliveryMeterName;
        public string? DeliveryMeterNumber;
        public string? PtrDeliveryMeterNumber;
        public string? PipelineContract;
        public string? PtrPipelineContract;
        public int NomFuelAmt;
        public double NomFuelPercent;
        public int PtrFuelAmt;
        public double PtrFuelPercent;
        public double PtrPercent;
        public int PtrAmt;
        public string? Duns;
        public string? MarketCounterparty;
        public string? SupplyCounterparty;
        public int Volume;
        public string? MarketDealType;
        public string? SupplyTicketNum;
        public string? MarketTicketNum;
        public int MarketNomID;
        public int SupplyNomID;
        public string? ActivityNumber;
        public int Delivered;
        public int WellheadPtrTotal;
        public int WellheadTotal;
        public int Rank;
    }
}
