using System.Data;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using Fast.Shared.Logic.RateControl;

namespace Fast.Web.Logic.ReportSources;

class SchedulingData(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 DataTable? dt;
    private Dictionary<int, Logic.GasControl.PipeContract>? PipeContracts;
    private ValParams? valParams;

    public override async Task<List<ReportFilterParameter>> Run()
    {
        dt = InitDataTable();
        RateCalculator rateCalculator = await RateCalculator.GetInstanceAsync(Enums.Product.NaturalGas);

        var val = new Valuater();
        valParams = await GetValParams();
        valParams.TransactionTypeIds.Add(1);
        var vals = await val.GetValuationValues(valParams);
        vals = vals.Where(x => x.DealId.HasValue).ToList();
        var valsLookup = vals.ToLookup(x => System.Tuple.Create(x.DealId.GetValueOrDefault(), x.PositionStartDate));

        var leaseSelected = new List<int>();
        var MeterSelected = new List<int>();
        var CpSelected = new List<int>();
        var PipelineSelected = new List<int>();

        string paramName;

        paramName = "Meter";
        if (HasParam(paramName))
        {
            await FixInvalidFilterParams(paramName);
            MeterSelected = GetIdList(paramName);
        }

        paramName = "Counterparty";
        if (HasParam(paramName))
        {
            await FixInvalidFilterParams(paramName);
            CpSelected = GetIdList(paramName);
        }

        paramName = "Pipeline";
        if (HasParam(paramName))
        {
            await FixInvalidFilterParams(paramName);
            PipelineSelected = GetIdList(paramName);
        }

        paramName = "Lease";
        if (HasParam(paramName))
        {
            await FixInvalidFilterParams(paramName);
            leaseSelected = GetIdList(paramName);
            var LeaseMeters = (
                from m in db.Meters
                where leaseSelected.Contains(m.LeaseId.GetValueOrDefault())
                select m.Id
            ).ToList();

            foreach (var mtr in LeaseMeters)
            {
                if (!MeterSelected.Contains(mtr))
                    MeterSelected.Add(mtr);
            }
        }

        PipeContracts = (
            from p in db.PipelineContracts
            select new Logic.GasControl.PipeContract()
            {
                ID = p.Id,
                ContractID = p.ContractId,
                PipelineID = p.PipelineId,
                ContractOwnerID = p.ContractOwnerId,
                IsCapacityRelease = p.IsCapacityRelease,
                DemandCharge = p.DemandCharge,
                RateUnitTimeOption = p.RateUnitTimeOption
            }
        ).ToDictionary(n => n.ID, n => n);

        var leases = (
            from b in db.Leases
            where leaseSelected.Count == 0 || leaseSelected.Contains(b.Id)
            select b
        ).ToDictionary(x => x.Id);

        List<SIPBBasicInfo> deals = [];

        var LeaseIdsByPointId = (await (
            from q in db.VwMeterPointGas
            join mtr in db.Meters on q.MeterId equals mtr.Id
            where (leaseSelected.Count == 0 || leaseSelected.Contains(mtr.LeaseId.GetValueOrDefault()))
            select new { q.PointId, mtr.LeaseId }
        ).AsNoTracking().Distinct().ToListAsync()).ToLookup(x => x.PointId, x => x.LeaseId);

        var meterPipes = await (
            from m in db.Meters
            join mp in DataHelper.RecentMeterProductsQueryable(db) on m.Id equals mp.MeterId
            where mp.ProductId == (int)Enums.Product.NaturalGas
            select new
            {
                MeterID = m.Id,
                PipeID = mp.SourceZone != null ? mp.SourceZone.PipelineId : 0,
                NonJurisdictional = mp.SourceZone != null && mp.SourceZone.Pipeline.PipelineTypeId == (int)Enums.PipelineType.NonJurisdictional,
                MeterNumber = mp.Number
            }
        ).AsNoTracking().ToDictionaryAsync(x => x.MeterID);

        var AllMeterPoints = (await (
            from mp in db.VwMeterPointGas
            join p in db.Points on mp.PointId equals p.Id
            join m in db.Meters on mp.MeterId equals m.Id
            join mproduct in DataHelper.RecentMeterProductsQueryable(db) on m.Id equals mproduct.MeterId
            where mproduct.ProductId == (int)Enums.Product.NaturalGas
            select new { meter = m, point = p, meterProduct = mproduct }
        ).AsNoTracking().ToListAsync()).ToLookup(x => x.meter.Id);

        var MarketSuppliesWithMarketDeal = GetMarketSuppliesWithMarketDeal(PipelineSelected, CpSelected, MeterSelected);

        var Entities = (
            from e in db.Counterparties
            join etm in db.CounterpartyRelationships on e.Id equals etm.CounterpartyId into j1
            from etm in j1.Where(x => x.BusinessRelationshipId == (int)Enums.BusinessRelationship.Agency).DefaultIfEmpty()
            select new { e.Id, e.Name, e.ShortName, IsAgency = etm != null }
        ).ToDictionary(x => x.Id);

        var marketNominationsQuery = (from m in db.GasMarkets select m);
        var marketNominationsExp = Util.Date.GetDateRangeExpression<GasMarket>(valParams.PositionDateRanges, "Date", false);
        marketNominationsQuery = marketNominationsQuery.Where(marketNominationsExp);
        var MarketNominations = marketNominationsQuery.ToLookup(x => System.Tuple.Create(x.TransferDealId, x.Date));

        var supplyNominationsQuery = (
            from s in db.GasSupplies
                .Include(x => x.Meter)
                .ThenInclude(x => x!.MeterProducts)
                .Include(x => x.PipelineContract)
                .Include(x => x.Deal)
                .ThenInclude(x => x!.Pipeline)
                .Include(x => x.Deal)
                .ThenInclude(x => x!.PhysicalDealType)
            select s
        );

        var supplyNominationsExp = Util.Date.GetDateRangeExpression<GasSupply>(valParams.PositionDateRanges, "Date", false);
        supplyNominationsQuery = supplyNominationsQuery.Where(supplyNominationsExp);
        var SupplyNominations = supplyNominationsQuery.ToLookup(x => System.Tuple.Create(x.Id, x.Date));

        var dealpurposes = (from d in db.DealPurposeTypes select new { d.Id, d.Name }).ToDictionary(x => x.Id, x => x.Name);

        var marketSupplyNominationsQuery = (from s in db.GasMarketSupplies select s);
        var marketSupplyNominationsExp = Util.Date.GetDateRangeExpression<GasMarketSupply>(valParams.PositionDateRanges, "Date", false);
        marketSupplyNominationsQuery = marketSupplyNominationsQuery.Where(marketSupplyNominationsExp);
        var MarketSupplyNominations = marketSupplyNominationsQuery.ToLookup(x => System.Tuple.Create(x.MarketNomId, x.Date));

        string? LeaseName = " ";
        string MainCounterparty = " ";
        string MainSupCounterparty = " ";
        string MainCPNickname = " ";
        DateOnly CurrentFlowDate = DateOnly.MinValue;
        foreach (var m in MarketSuppliesWithMarketDeal)
        {
            var firstOfMonth = Util.Date.FirstDayOfMonth(m.Date);

            var marketMeterPoint = AllMeterPoints?[m.MarketNom.MeterId].FirstOrDefault();
            var MarketMeter = new PipelineInfo
            {
                ID = marketMeterPoint?.meter.Id ?? 0,
                Name = marketMeterPoint?.point.Name ?? "", // point selected for name because meter name is not needed.
                Number = marketMeterPoint?.meterProduct?.Number ?? ""
            };

            var recpoint = AllMeterPoints?[m.SupplyNom.MeterId.GetValueOrDefault()].FirstOrDefault()?.point.Name;

            if (m.SupplyNom.DealId != null)
            {
                // straight supply to market deal
                ValuationResult? valuationResult = GetValuationResult(valsLookup, m.MarketNom.DealId.GetValueOrDefault(), m.Date);
                SIPBBasicInfo marketdeal = new()
                {
                    Month = m.Date.ToString("MMMM") + " " + m.Date.Year.ToString(),
                    Entity = Entities[m.SupplyNom.Deal?.InternalEntityId.GetValueOrDefault() ?? 0].Name,
                    TicketNum = m.MarketNom.Deal?.TicketNum ?? "",
                    SaleMMBTU = m.Volume.GetValueOrDefault(),
                    DealPurpose = m.MarketNom.Deal?.DealPurposeId == null ? "0" : m.MarketNom.Deal.DealPurposeId?.ToString() ?? "0",
                    Count = 1,
                    WellheadMMBTU = m.Volume.GetValueOrDefault(),
                    SupplyMeter = m.SupplyNom.Meter?.MeterProducts
                        .Where(x => x.ProductId == (int)Enums.Product.NaturalGas && x.EffectiveDate <= m.Date).OrderByDescending(x => x.EffectiveDate).FirstOrDefault()?.Number ?? "",
                    FlowDate = m.Date,
                    VolumeStatus = m.IsActual ? "Actual" : "Nominated",
                    SupplyTicketNum = m.SupplyNom.Deal?.TicketNum ?? "",
                    Pipeline = m.MarketNom.Deal?.Pipeline?.Name ?? "",
                    PipeShort = m.MarketNom.Deal?.Pipeline?.PipeShort ?? "",
                    NominationID = m.Id.ToString(),
                    DealType = m.MarketNom.Deal?.PhysicalDealType?.Name ?? "",
                    PhysicalMMBTU = 0,
                    TransportRow = 0,
                    TransportMeter = "-",
                    SupplyPipeline = m.SupplyNom.Deal?.Pipeline?.Name ?? "",
                    SupplyDealType = m.SupplyNom.Deal?.PhysicalDealType?.Name ?? ""
                };
                marketdeal.SaleMMBTU = Math.Round(marketdeal.SaleMMBTU, 0);
                marketdeal.WellheadMMBTU = Math.Round(marketdeal.WellheadMMBTU, 0);
                if (valuationResult != null)
                {
                    marketdeal.SalePrice = valuationResult.InvoicePrice;
                    marketdeal.GrossSales = Math.Round(marketdeal.SalePrice * marketdeal.SaleMMBTU, 2);
                }

                if (int.Parse(marketdeal.DealPurpose) > 0)
                    marketdeal.DealPurpose = dealpurposes[int.Parse(marketdeal.DealPurpose)];

                var supplyMarketMeterPoint = AllMeterPoints?[m.SupplyNom.MeterId.GetValueOrDefault()].FirstOrDefault();
                var SupplyMarketMeter = new PipelineInfo
                {
                    ID = supplyMarketMeterPoint?.meter.Id ?? 0,
                    Name = supplyMarketMeterPoint?.point.Name ?? "",
                    Number = supplyMarketMeterPoint?.meterProduct?.Number ?? ""
                };

                if (m.SupplyNom.PipelineContract != null)
                    marketdeal.AgreementNumber = m.SupplyNom.PipelineContract.ContractId;

                var pointLeaseID = LeaseIdsByPointId[m.SupplyNom.Deal?.PointId.GetValueOrDefault() ?? 0].FirstOrDefault();

                Lease? CurrentLease = null;
                if (pointLeaseID != null)
                    CurrentLease = leases[pointLeaseID.Value];

                if (CurrentLease != null)
                {
                    marketdeal.Lease = CurrentLease.Name ?? "";
                    marketdeal.LeaseNumber = CurrentLease.Number ?? "";
                }

                if (Entities.ContainsKey(m.MarketNom.Deal?.CounterpartyId.GetValueOrDefault() ?? 0))
                {
                    var counterparty = Entities[m.MarketNom.Deal?.CounterpartyId ?? 0];
                    marketdeal.Counterparty = counterparty.Name;
                    marketdeal.CounterpartyShort = counterparty.ShortName;
                    marketdeal.Agency = counterparty.IsAgency ? "Y" : "N";
                }

                MainCounterparty = marketdeal.Counterparty ?? "";
                MainCPNickname = marketdeal.CounterpartyShort ?? "";

                var SupplyCounterparty = Entities[m.SupplyNom.Deal?.CounterpartyId ?? 0];
                marketdeal.SupplyCounterparty = SupplyCounterparty.Name;
                MainSupCounterparty = SupplyCounterparty.Name;
                marketdeal.MeterNumber = MarketMeter.Number;
                marketdeal.DeliveryPoint = MarketMeter.Name;
                marketdeal.ReceiptPoint = recpoint ?? "";
                marketdeal.SupplyMeterName = m.SupplyNom.Meter?.Name ?? "";

                /// removed BTU code and midstream.GasAnalysisBtuFactor table on 2023-08-10
                marketdeal.SalesMCF = 0; //Math.Round(marketdeal.SaleMMBTU / marketBtu, 0);
                marketdeal.MCF = 0; //Math.Round(marketdeal.WellheadMMBTU / supplyBtu, 0);

                var isNonJurisdictional = m.MarketNom.Deal?.Pipeline?.PipelineTypeId == (int)Enums.PipelineType.NonJurisdictional;
                var isAgency = SupplyCounterparty.IsAgency;
                marketdeal.TransportRate = rateCalculator.GetTransRate(m.SupplyNom.PipelineContractId, m.Date, m.SupplyNom.MeterId.GetValueOrDefault(), MarketMeter?.ID ?? 0, isNonJurisdictional, isAgency);
                // get adjusted wellhead MMBTU by appplyin fuel factor
                var FuelPercentage = rateCalculator.GetFuelPercent(m.SupplyNom.PipelineContractId, m.Date, m.SupplyNom.MeterId.GetValueOrDefault(), MarketMeter?.ID ?? 0);
                if (FuelPercentage == null)
                    marketdeal.FuelFactor = 0;
                else
                    marketdeal.FuelFactor = FuelPercentage.Value;
                double FuelAmount = marketdeal.SaleMMBTU / (double)(1 - marketdeal.FuelFactor) - marketdeal.SaleMMBTU;
                marketdeal.FuelAmount = FuelAmount;
                marketdeal.WellheadMMBTU = marketdeal.SaleMMBTU + FuelAmount;
                marketdeal.TransportVolume = marketdeal.SaleMMBTU;
                marketdeal.TransportAmt = Math.Round(marketdeal.TransportVolume * marketdeal.TransportRate, 2);
                marketdeal.Purchase = marketdeal.GrossSales - marketdeal.TransportAmt;
                // rounding values after calculating totals
                marketdeal.NetbackPrice = Math.Round(marketdeal.Purchase / (double)marketdeal.WellheadMMBTU, 5);
                marketdeal.WellheadMMBTU = Math.Round(marketdeal.WellheadMMBTU, 0);
                marketdeal.PhysicalMMBTU = marketdeal.WellheadMMBTU;
                marketdeal.InvPrice = marketdeal.SalePrice;
                deals.Add(marketdeal);
            }
            else
            {
                // find corresponding supply side
                double TransAmtSum = 0;
                double currentMMBTU;
                double FuelRate;

                //TransportRows by string NominationID
                Dictionary<string, List<SIPBBasicInfo>> TransportRowsByNomId = [];
                List<SIPBBasicInfo> AllTransportRows = [];

                if (m.Volume == null)
                    currentMMBTU = 0;
                else
                    currentMMBTU = m.Volume.Value;
                var supplyDealID = m.SupplyNom.TransferDealId;
                var DealDate = m.Date;
                int? SupplyDeal = null;
                GasSupply? NomSupply = new();

                // Get Fuel Factor, new mmbtu, Trans Rate for the final deal first
                double? TransportRate;
                bool FinalNonJurisdictional = meterPipes[m.SupplyNom.MeterId.GetValueOrDefault()].NonJurisdictional;
                TransportRate = rateCalculator.GetTransRate(m.SupplyNom.PipelineContractId, m.Date, m.SupplyNom.MeterId.GetValueOrDefault(), m.MarketNom.MeterId, FinalNonJurisdictional, false);
                var FinalTransRate = TransportRate;
                var FuelPercentage = rateCalculator.GetFuelPercent(m.SupplyNom.PipelineContractId, m.Date, m.SupplyNom.MeterId.GetValueOrDefault(), m.MarketNom.MeterId);
                double FinalFuelFactor = 0;
                if (FuelPercentage == null)
                    FuelRate = 0;
                else
                    FuelRate = FuelPercentage.Value;
                FinalFuelFactor = FuelRate;
                double FuelAmount = currentMMBTU / (1 - FuelRate) - currentMMBTU;
                var finalfuelamount = FuelAmount;
                currentMMBTU += FuelAmount;
                var CurrentTransportRow = 1;
                while (SupplyDeal == null)
                {
                    var MarketNoms = MarketNominations[System.Tuple.Create(supplyDealID, DealDate)];
                    // don't inlude Market table rows where they don't exist in the MarketSupply table
                    // this can happen if there are saved SOS market rows without any nominations
                    MarketNoms = MarketNoms.Where(x => MarketSupplyNominations[System.Tuple.Create(x.Id, DealDate)].Any()).ToList();

                    if (MarketNoms == null || !MarketNoms.Any())
                        break;

                    string? currentsupplydealid = null;
                    // process the Nominations that correspond to that supply deal
                    foreach (var nom in MarketNoms)
                    {
                        var mktnom = nom;
                        GasMarketSupply? MarketSupplyDeal = MarketSupplyNominations[System.Tuple.Create(mktnom.Id, DealDate)].FirstOrDefault();

                        NomSupply = SupplyNominations[System.Tuple.Create(MarketSupplyDeal?.SupplyNomId ?? 0, DealDate)].FirstOrDefault();

                        var TransSupply = m.SupplyNom.TransferDealId;
                        if (NomSupply?.DealId == null)
                        {
                            supplyDealID = NomSupply?.TransferDealId;
                            // This has more than a transfer deal.. get new mmbtu between the two sides
                            int transferMarketMeterId = meterPipes[m.MarketNom.MeterId].MeterID;
                            string transferMarketMeterNumber = meterPipes[m.MarketNom.MeterId].MeterNumber;

                            double? TransferRate;
                            bool NonJurisdictional = meterPipes[NomSupply?.MeterId ?? 0].NonJurisdictional;
                            TransferRate = rateCalculator.GetTransRate(NomSupply?.PipelineContractId, m.Date, NomSupply?.MeterId ?? 0, transferMarketMeterId, NonJurisdictional, false);
                            FuelPercentage = rateCalculator.GetFuelPercent(NomSupply?.PipelineContractId, m.Date, NomSupply?.MeterId ?? 0, transferMarketMeterId);
                            if (FuelPercentage == null)
                                FuelRate = 0;
                            else
                                FuelRate = FuelPercentage.Value;
                            FuelAmount = currentMMBTU / (1 - FuelRate) - currentMMBTU;
                            currentMMBTU += FuelAmount;

                            // Create 'sub row' for transportation cost of this nomination
                            SIPBBasicInfo TransportInfo = new()
                            {
                                MeterNumber = MarketMeter.Number,
                                TransportMeter = transferMarketMeterNumber,
                                TransportVolume = m.Volume.GetValueOrDefault(),
                                TransportRate = TransferRate.GetValueOrDefault(),
                                TransportAmt = m.Volume.GetValueOrDefault() * TransferRate.GetValueOrDefault(),
                                FuelFactor = FuelRate,
                                SupplyMeter = NomSupply?.Meter?.MeterProducts
                                    .Where(x => x.ProductId == (int)Enums.Product.NaturalGas && x.EffectiveDate <= m.Date).OrderByDescending(x => x.EffectiveDate).FirstOrDefault()?.Number ?? "",
                                MCF = 0,
                                SalesMCF = 0,
                                NetbackPrice = 0,
                                PhysicalMMBTU = m.Volume,
                                Month = m.Date.ToString("MMMM") + " " + m.Date.Year.ToString(),
                                Counterparty = MainCounterparty,
                                CounterpartyShort = MainCPNickname,
                                TicketNum = m.MarketNom.Deal?.TicketNum ?? "",
                                Pipeline = m.MarketNom.Deal?.Pipeline?.Name ?? "",
                                PipeShort = m.MarketNom.Deal?.Pipeline?.PipeShort ?? "",
                                NominationID = m.Id.ToString(),
                                TransportRow = CurrentTransportRow,
                                SupplyTicketNum = NomSupply?.TransferDealId.ToString() ?? "",
                                FlowDate = m.Date,
                                SupplyMeterName = m.SupplyNom.Meter?.Name ?? "",
                                Nonjurisdictional = NonJurisdictional,
                                SupplyCounterparty = MainSupCounterparty,
                                DealType = m.MarketNom.Deal?.PhysicalDealType?.Name ?? ""
                            };

                            if (currentsupplydealid != null)
                            {
                                SIPBBasicInfo? transrow = null;
                                if (TransportRowsByNomId.ContainsKey(currentsupplydealid))
                                    transrow = TransportRowsByNomId[currentsupplydealid].FirstOrDefault();

                                if (transrow != null)
                                    transrow.TransferIn = m.Volume == null ? 0 : m.Volume + FuelAmount;
                            }
                            currentsupplydealid = TransportInfo.NominationID;
                            TransportInfo.FuelAmount = FuelAmount;
                            // TransportInfo.TransferIn = If(m.Volume Is Nothing, 0, m.Volume + FuelAmount)
                            TransportInfo.TransferOuts = m.Volume == null ? 0 : m.Volume;
                            if (NomSupply?.PipelineContractId != null)
                                TransportInfo.AgreementNumber = NomSupply?.PipelineContract?.ContractId ?? "";
                            TransportInfo.TransportAmt = Math.Round(TransportInfo.TransportAmt, 2);
                            TransAmtSum += TransportInfo.TransportAmt;
                            AllTransportRows.Add(TransportInfo);
                            if (!TransportRowsByNomId.ContainsKey(TransportInfo.NominationID))
                                TransportRowsByNomId.Add(TransportInfo.NominationID, new List<SIPBBasicInfo>());
                            TransportRowsByNomId[TransportInfo.NominationID].Add(TransportInfo);
                            CurrentTransportRow += 1;
                            TransSupply = NomSupply?.TransferDealId;
                            TransportInfo.InvPrice = TransportInfo.TransportRate;
                        }
                        else
                        {
                            SupplyDeal = NomSupply.DealId;
                            // this is the first supply deal, get all the values.

                            var fSupplyCounterparty = Entities[NomSupply.Deal?.CounterpartyId ?? 0];
                            if (FinalNonJurisdictional && fSupplyCounterparty.IsAgency)
                                FinalTransRate = 0;

                            var ValuationResult = GetValuationResult(valsLookup, m.MarketNom.DealId.GetValueOrDefault(), m.Date);
                            SIPBBasicInfo marketdeal = new()
                            {
                                Month = m.Date.ToString("MMMM") + " " + m.Date.Year.ToString(),
                                Entity = Entities[NomSupply.Deal?.InternalEntityId ?? 0].Name,
                                TicketNum = m.MarketNom.Deal?.TicketNum ?? "",
                                SaleMMBTU = m.Volume.GetValueOrDefault(),
                                VolumeStatus = m.IsActual ? "Actual" : "Nominated",
                                Count = 1,
                                FuelAmount = finalfuelamount,
                                MeterNumber = NomSupply.Meter?.Name ?? "",
                                MarketSupply = MarketSupplyDeal,
                                Pipeline = m.MarketNom.Deal?.Pipeline?.Name,
                                PipeShort = m.MarketNom.Deal?.Pipeline?.PipeShort,
                                FlowDate = m.Date,
                                SupplyTicketNum = NomSupply.Deal?.TicketNum,
                                NominationID = m.Id.ToString(),
                                WellheadMMBTU = m.Volume.GetValueOrDefault(),
                                SupplyMeter = NomSupply.Meter?.MeterProducts
                                    .Where(x => x.ProductId == (int)Enums.Product.NaturalGas && x.EffectiveDate <= m.Date).OrderByDescending(x => x.EffectiveDate).FirstOrDefault()?.Number,
                                FuelFactor = FinalFuelFactor,
                                TransportRate = FinalTransRate.GetValueOrDefault(),
                                TransportVolume = m.Volume.GetValueOrDefault(),
                                TransportAmt = m.Volume.GetValueOrDefault() * FinalTransRate.GetValueOrDefault(),
                                DeliveryPoint = MarketMeter.Name,
                                ReceiptPoint = recpoint,
                                DealType = m.MarketNom.Deal?.PhysicalDealType?.Name,
                                DealPurpose = m.MarketNom.Deal?.DealPurposeId.ToString(),
                                PhysicalMMBTU = 0,
                                TransportRow = 0,
                                TransportMeter = "-",
                                SupplyPipeline = NomSupply.Deal?.Pipeline?.Name,
                                SupplyDealType = NomSupply.Deal?.PhysicalDealType?.Name
                            };

                            marketdeal.SaleMMBTU = Math.Round(marketdeal.SaleMMBTU, 0);
                            marketdeal.WellheadMMBTU = Math.Round(marketdeal.WellheadMMBTU, 0);
                            marketdeal.TransportAmt = Math.Round(marketdeal.TransportAmt, 2);

                            if (ValuationResult != null)
                            {
                                marketdeal.SalePrice = ValuationResult.InvoicePrice;
                                marketdeal.GrossSales = Math.Round(marketdeal.SalePrice * marketdeal.SaleMMBTU, 2);
                            }

                            if (int.Parse(marketdeal.DealPurpose ?? "0") > 0)
                                marketdeal.DealPurpose = dealpurposes[int.Parse(marketdeal.DealPurpose ?? "0")];

                            TransAmtSum += marketdeal.TransportAmt;

                            if (m.SupplyNom.PipelineContract != null)
                                marketdeal.AgreementNumber = m.SupplyNom.PipelineContract.ContractId;

                            LeaseName = null;

                            var pointLeaseID = LeaseIdsByPointId[NomSupply.Deal?.PointId ?? 0].FirstOrDefault();

                            Lease? CurrentLease = null;
                            if (pointLeaseID != null)
                                CurrentLease = leases[pointLeaseID.Value];

                            if (CurrentLease != null)
                            {
                                marketdeal.Lease = CurrentLease.Name;
                                marketdeal.LeaseNumber = CurrentLease.Number;
                                LeaseName = CurrentLease.Name;
                            }

                            if (Entities.ContainsKey(m.MarketNom.Deal?.CounterpartyId ?? 0))
                            {
                                var counterparty = Entities[m.MarketNom.Deal?.CounterpartyId ?? 0];
                                marketdeal.Counterparty = counterparty.Name;
                                marketdeal.CounterpartyShort = counterparty.ShortName;
                                marketdeal.Agency = counterparty.IsAgency ? "Y" : "N";
                            }

                            MainCounterparty = marketdeal.Counterparty ?? "";
                            MainCPNickname = marketdeal.CounterpartyShort ?? "";


                            marketdeal.SupplyCounterparty = Entities[NomSupply.Deal?.CounterpartyId ?? 0].Name;
                            MainSupCounterparty = marketdeal.SupplyCounterparty;

                            var finalMarketMeterPoint = AllMeterPoints?[m.MarketNom.MeterId].FirstOrDefault();
                            var FinalMarketMeter = new { ID = finalMarketMeterPoint?.meter.Id, finalMarketMeterPoint?.meterProduct?.Number };

                            var finalSupplyMarketMeterPoint = AllMeterPoints?[NomSupply.MeterId.GetValueOrDefault()].FirstOrDefault();
                            var FinalSupplyMarketMeter = new { ID = finalSupplyMarketMeterPoint?.meter.Id, finalSupplyMarketMeterPoint?.meterProduct?.Number };

                            marketdeal.MeterNumber = FinalMarketMeter.Number;
                            marketdeal.SupplyMeterName = NomSupply.Meter?.Name;

                            double MarketBTU = 0;
                            if (MarketBTU == 0)
                                marketdeal.SalesMCF = 0;
                            else
                                marketdeal.SalesMCF = Math.Round(marketdeal.SaleMMBTU / MarketBTU, 0);
                            double SupplyBTU = 0;
                            if (SupplyBTU == 0)
                                marketdeal.MCF = 0;
                            else
                                marketdeal.MCF = Math.Round(marketdeal.WellheadMMBTU / SupplyBTU, 0);

                            // get Final mmbtu
                            var transferMeterId = meterPipes[m.MarketNom.MeterId].MeterID;
                            var transferMeterNumber = meterPipes[m.MarketNom.MeterId].MeterNumber;
                            double? TransferRate;
                            bool NonJurisdictional = meterPipes[NomSupply.MeterId.GetValueOrDefault()].NonJurisdictional;
                            TransferRate = rateCalculator.GetTransRate(NomSupply.PipelineContractId, m.Date, NomSupply.MeterId.GetValueOrDefault(), transferMeterId, NonJurisdictional, false);
                            FuelPercentage = rateCalculator.GetFuelPercent(NomSupply.PipelineContractId, m.Date, NomSupply.MeterId.GetValueOrDefault(), transferMeterId);

                            if (FuelPercentage == null)
                                FuelRate = 0;
                            else
                                FuelRate = FuelPercentage.GetValueOrDefault();

                            FuelAmount = currentMMBTU / (1 - FuelRate) - currentMMBTU;
                            currentMMBTU += FuelAmount;
                            marketdeal.WellheadMMBTU = currentMMBTU;
                            marketdeal.InvPrice = marketdeal.SalePrice;

                            // Create 'sub row' for transportation cost of this nomination (First Transfer Nomination)
                            SIPBBasicInfo TransportInfo = new()
                            {
                                MeterNumber = MarketMeter.Number,
                                TransportMeter = transferMeterNumber,
                                TransportVolume = m.Volume.GetValueOrDefault(),
                                TransportRate = TransferRate.GetValueOrDefault(),
                                PhysicalMMBTU = m.Volume.GetValueOrDefault(),
                                TransportAmt = m.Volume.GetValueOrDefault() * TransferRate.GetValueOrDefault(),
                                FuelFactor = FuelRate,
                                SupplyMeter = m.SupplyNom.Meter?.MeterProducts
                                    .Where(x => x.ProductId == (int)Enums.Product.NaturalGas && x.EffectiveDate <= m.Date).OrderByDescending(x => x.EffectiveDate).FirstOrDefault()?.Number,
                                MCF = 0,
                                SalesMCF = 0,
                                NetbackPrice = 0,
                                Lease = LeaseName,
                                Month = m.Date.ToString("MMMM") + " " + m.Date.Year.ToString(),
                                Counterparty = MainCounterparty,
                                CounterpartyShort = MainCPNickname,
                                TicketNum = m.MarketNom.Deal?.TicketNum,
                                NominationID = m.Id.ToString(),
                                TransportRow = CurrentTransportRow,
                                SupplyTicketNum = TransSupply.ToString(),
                                FlowDate = m.Date,
                                SupplyMeterName = NomSupply.Meter?.Name,
                                Pipeline = m.MarketNom.Deal?.Pipeline?.Name,
                                PipeShort = m.MarketNom.Deal?.Pipeline?.PipeShort,
                                Nonjurisdictional = NonJurisdictional,
                                SupplyCounterparty = MainSupCounterparty,
                                SupplyPipeline = NomSupply.Deal?.Pipeline?.Name,
                                SupplyDealType = NomSupply.Deal?.PhysicalDealType?.Name,
                                DealType = m.MarketNom.Deal?.PhysicalDealType?.Name
                            };

                            TransportInfo.FuelAmount = FuelAmount;
                            TransportInfo.TransferOuts = m.Volume == null ? 0 : m.Volume;
                            TransportInfo.TransferIn = marketdeal.SaleMMBTU;
                            TransportInfo.TransportAmt = Math.Round(TransportInfo.TransportAmt, 2);
                            TransAmtSum += TransportInfo.TransportAmt;
                            CurrentTransportRow += 1;
                            if (NomSupply.PipelineContractId != null)
                                TransportInfo.AgreementNumber = NomSupply.PipelineContract?.ContractId;
                            TransportInfo.InvPrice = TransportInfo.TransportRate;
                            marketdeal.Purchase = marketdeal.GrossSales - TransAmtSum;
                            marketdeal.NetbackPrice = Math.Round(marketdeal.Purchase / (double)marketdeal.WellheadMMBTU, 5);
                            marketdeal.WellheadMMBTU = Math.Round(marketdeal.WellheadMMBTU, 0);
                            marketdeal.PhysicalMMBTU = marketdeal.WellheadMMBTU;
                            AllTransportRows.Add(TransportInfo);
                            if (!TransportRowsByNomId.ContainsKey(TransportInfo.NominationID))
                                TransportRowsByNomId.Add(TransportInfo.NominationID, new List<SIPBBasicInfo>());
                            TransportRowsByNomId[TransportInfo.NominationID].Add(TransportInfo);
                            deals.Add(marketdeal);
                            if (fSupplyCounterparty.IsAgency)
                            {
                                foreach (var trow in AllTransportRows)
                                {
                                    if (trow.Nonjurisdictional)
                                    {
                                        trow.TransportRate = 0;
                                        trow.InvPrice = 0;
                                    }
                                    //this is if transport rows should have the same wellhead as reg rows
                                    trow.PhysicalMMBTU = marketdeal.WellheadMMBTU;
                                }
                            }
                            foreach (var t in AllTransportRows)
                            {
                                t.Lease = LeaseName;
                                t.SupplyPipeline = NomSupply.Deal?.Pipeline?.Name;
                                t.SupplyDealType = NomSupply.Deal?.PhysicalDealType?.Name;
                                deals.Add(t);
                            }
                        }
                    }
                }
            }
        }

        // Add concatenated dealtypes for '' report.
        var distinctDealDeliveryPointNames = deals.Select(x => x.DeliveryPoint).Distinct().ToList();
        var dealsByDeliveryPointName = deals.ToLookup(x => x.DeliveryPoint);

        foreach (var delivPointName in distinctDealDeliveryPointNames)
        {
            List<string> types = [];

            var currentDeals = dealsByDeliveryPointName[delivPointName];
            var dealtypes = currentDeals.Select(x => x.DealType).Distinct();

            string? typesstring = dealtypes.FirstOrDefault();
            if (dealtypes.Count() > 1)
            {
                var count = 1;
                foreach (var dt in dealtypes)
                {
                    if (count > 1)
                        typesstring += " & " + dt;
                    count += 1;
                }
            }
            foreach (var cd in currentDeals)
                cd.PriceType = typesstring;
        }

        PopulateDatatable(deals);

        FillSheet("Scheduling Data", dt);

        return GetNewFilterParams();
    }

    private ValuationResult? GetValuationResult(ILookup<Tuple<int, DateOnly>, ValuationResult> valsByDealIdAndPositionStart, int dealId, DateOnly nomDate)
    {
        var key = System.Tuple.Create(dealId, nomDate);
        var result = valsByDealIdAndPositionStart[key];
        return result.FirstOrDefault();
    }

    public double? GetDemandCharge(int? ContractID, DateOnly NomDate)
    {
        double? DemandCharge = null;

        if (ContractID != null)
        {
            Logic.GasControl.PipeContract? Contract;
            Contract = PipeContracts?[ContractID.Value];
            if (Contract?.IsCapacityRelease.GetValueOrDefault() == true && Contract.DemandCharge.HasValue)
            {
                if (Contract.RateUnitTimeOption == 1)
                    // daily
                    DemandCharge = Contract.DemandCharge;
                else
                    // monthly
                    DemandCharge = Contract.DemandCharge / Convert.ToDouble(DateTime.DaysInMonth(NomDate.Year, NomDate.Month));
            }
        }

        return DemandCharge;
    }

    public List<GasMarketSupply> GetMarketSuppliesWithMarketDeal(IEnumerable<int> SelectedPipeline, IEnumerable<int> SelectedCounterParties, IEnumerable<int> SelectedMeters)
    {
        // returns set of marketsupply nominations with filters applied and corresponding actual or nominated volume.

        var query = (
            from ms in db.GasMarketSupplies
                .Include(x => x.MarketNom)
                .ThenInclude(x => x.Deal)
                .ThenInclude(x => x!.DealPurpose)
                .Include(x => x.MarketNom)
                .ThenInclude(x => x.Deal)
                .ThenInclude(x => x!.PhysicalDealType)
                .Include(x => x.MarketNom)
                .ThenInclude(x => x.Deal)
                .ThenInclude(x => x!.Pipeline)
                .Include(x => x.SupplyNom)
                .ThenInclude(x => x.Meter)
                .ThenInclude(x => x!.MeterProducts)
                .Include(x => x.SupplyNom)
                .ThenInclude(x => x.PipelineContract)
                .Include(x => x.SupplyNom)
                .ThenInclude(x => x.Deal)
                .ThenInclude(x => x!.DealPurpose)
                .Include(x => x.SupplyNom)
                .ThenInclude(x => x.Deal)
                .ThenInclude(x => x!.PhysicalDealType)
                .Include(x => x.SupplyNom)
                .ThenInclude(x => x.Deal)
                .ThenInclude(x => x!.Pipeline)
            let supplyDealPipeId = ms.SupplyNom.Deal != null && ms.SupplyNom.Deal.PipelineId != null ? ms.SupplyNom.Deal.PipelineId.Value : 0
            let marketDealCptyId = ms.MarketNom.Deal != null && ms.MarketNom.Deal.CounterpartyId != null ? ms.MarketNom.Deal.CounterpartyId.Value : 0
            where ms.MarketNom.DealId != null
                && (SelectedPipeline.Count() == 0 || SelectedPipeline.Contains(supplyDealPipeId))
                && (SelectedCounterParties.Count() == 0 || SelectedCounterParties.Contains(marketDealCptyId))
                && (SelectedMeters.Count() == 0 || SelectedMeters.Contains(ms.SupplyNom.MeterId ?? 0))
                && (ms.IsActual == false)
            select ms
        );
        var noDateRange = new List<DateRange>();
        var exp = Util.Date.GetDateRangeExpression<GasMarketSupply>(valParams?.PositionDateRanges ?? noDateRange, "date", false);
        query = query.Where(exp);
        query = (
            from q in query
            orderby q.Date, q.MarketNom.Deal != null ? q.MarketNom.Deal.TicketNum : null, q.Id
            select q
        );

        var MarketSuppliesWithMarketDeal = query.ToList();

        return MarketSuppliesWithMarketDeal;
    }

    private void PopulateDatatable(IEnumerable<SIPBBasicInfo> deals)
    {
        foreach (var d in deals)
        {
            if (dt != null)
            {
                var row = dt.NewRow();
                row["Month"] = string.IsNullOrWhiteSpace(d.Month) ? null : DateOnly.Parse(d.Month);
                row["Supply Meter Number"] = d.SupplyMeter;
                row["Internal Entity"] = d.Entity;
                row["Counterparty"] = d.Counterparty;
                row["Supply Counterparty"] = d.SupplyCounterparty;
                row["Sale TicketNum"] = d.TicketNum;
                row["Lease"] = d.Lease;
                row["Sales MMBTU"] = d.SaleMMBTU;
                row["Sales MCF"] = d.SalesMCF;
                row["Sale Price"] = d.SalePrice;
                row["Gross Sales"] = d.GrossSales;
                row["Agreement Number"] = d.AgreementNumber;
                row["Meter Number"] = d.MeterNumber;
                row["Transport Meter Number"] = d.TransportMeter;
                row["Transport Volume"] = d.TransportVolume;
                row["Transport Rate"] = d.TransportRate;
                row["Transport Amount"] = d.TransportAmt;
                row["Fuel Factor"] = d.FuelFactor;
                row["Wellhead Allocation MCF"] = d.MCF;
                row["Wellhead Allocation MMBTU"] = d.WellheadMMBTU;
                row["Supply TicketNum"] = d.SupplyTicketNum;
                row["Netback Price"] = d.NetbackPrice;
                row["Purchase"] = d.Purchase;
                row["Nomination ID"] = d.NominationID;
                row["Lease Number"] = d.LeaseNumber;
                row["Flow Date"] = d.FlowDate;
                row["Pipeline"] = d.Pipeline;
                row["Delivery Point"] = d.DeliveryPoint;
                row["Fuel Amount"] = d.FuelAmount;
                row["Deal type"] = d.DealType;
                row["Physical MMBTU"] = d.PhysicalMMBTU;
                row["Transport Row"] = d.TransportRow;
                row["Supply Meter Name"] = d.SupplyMeterName;
                row["Price Type"] = d.PriceType;
                row["Pipe Short"] = d.PipeShort;
                row["Counterparty Short"] = d.CounterpartyShort;
                row["Deal Purpose"] = d.DealPurpose;
                row["Volume Status"] = d.VolumeStatus;
                row["Agency"] = d.Agency;
                row["Count"] = d.Count;
                row["Transfer In"] = d.TransferIn == null ? DBNull.Value : (object)d.TransferIn;
                row["Transfer Out"] = d.TransferOuts == null ? DBNull.Value : (object)d.TransferOuts;
                row["Transfer In $"] = DBNull.Value;
                row["Transfer Out $"] = DBNull.Value;
                row["Receipt Point"] = d.ReceiptPoint;
                row["Inv Price"] = d.InvPrice;
                row["Supply Pipeline"] = d.SupplyPipeline;
                row["Supply Deal Type"] = d.SupplyDealType;
                dt.Rows.Add(row);
            }
        }
    }

    DataTable InitDataTable()
    {
        DataTable myDt = new();
        myDt.Columns.Add("Month", typeof(DateOnly));
        myDt.Columns.Add("Supply Meter Number", typeof(string));
        myDt.Columns.Add("Internal Entity", typeof(string));
        myDt.Columns.Add("Counterparty", typeof(string));
        myDt.Columns.Add("Supply Counterparty", typeof(string));
        myDt.Columns.Add("Lease", typeof(string));
        myDt.Columns.Add("Sale TicketNum", typeof(string));
        myDt.Columns.Add("Sales MCF", typeof(double));
        myDt.Columns.Add("Sales MMBTU", typeof(double));
        myDt.Columns.Add("Sale Price", typeof(double));
        myDt.Columns.Add("Agreement Number", typeof(string));
        myDt.Columns.Add("Gross Sales", typeof(double));
        myDt.Columns.Add("Meter Number", typeof(string));
        myDt.Columns.Add("Transport Agreement Number", typeof(string));
        myDt.Columns.Add("Transport Meter Number", typeof(string));
        myDt.Columns.Add("Transport Volume", typeof(double));
        myDt.Columns.Add("Transport Rate", typeof(double));
        myDt.Columns.Add("Transport Amount", typeof(double));
        myDt.Columns.Add("Fuel Factor", typeof(double));
        myDt.Columns.Add("Wellhead Allocation MCF", typeof(double));
        myDt.Columns.Add("Wellhead Allocation MMBTU", typeof(double));
        myDt.Columns.Add("Supply TicketNum", typeof(string));
        myDt.Columns.Add("Netback Price", typeof(double));
        myDt.Columns.Add("Purchase", typeof(double));
        myDt.Columns.Add("Nomination ID");
        myDt.Columns.Add("Lease Number", typeof(string));
        myDt.Columns.Add("Flow Date", typeof(DateOnly));
        myDt.Columns.Add("Pipeline", typeof(string));
        myDt.Columns.Add("Delivery Point", typeof(string));
        myDt.Columns.Add("Fuel Amount", typeof(double));
        myDt.Columns.Add("Deal Type", typeof(string));
        myDt.Columns.Add("Physical MMBTU", typeof(double));
        myDt.Columns.Add("Physical Amount", typeof(double));
        myDt.Columns.Add("Transport Row", typeof(int));
        myDt.Columns.Add("Supply Meter Name", typeof(string));
        myDt.Columns.Add("Price Type", typeof(string));
        myDt.Columns.Add("Pipe Short", typeof(string));
        myDt.Columns.Add("Counterparty Short", typeof(string));
        myDt.Columns.Add("Deal Purpose", typeof(string));
        myDt.Columns.Add("Volume Status", typeof(string));
        myDt.Columns.Add("Agency", typeof(string));
        myDt.Columns.Add("Count", typeof(int));
        myDt.Columns.Add("Transfer In", typeof(double));
        myDt.Columns.Add("Transfer Out", typeof(double));
        myDt.Columns.Add("Transfer In $", typeof(double));
        myDt.Columns.Add("Transfer Out $", typeof(double));
        myDt.Columns.Add("Receipt Point", typeof(string));
        myDt.Columns.Add("Inv Price", typeof(double));
        myDt.Columns.Add("Supply Pipeline", typeof(string));
        myDt.Columns.Add("Supply Deal Type", typeof(string));
        return myDt;
    }

    private class SIPBBasicInfo
    {
        public string? TicketNum { get; set; }
        public string? Month { get; set; }
        public string? SupplyMeter { get; set; }
        public string? SupplyMeterName { get; set; }
        public string? Entity { get; set; }
        public string? Counterparty { get; set; }
        public string? SupplyCounterparty { get; set; }
        public string? Lease { get; set; }
        public double SaleMMBTU { get; set; }
        public double SalePrice { get; set; }
        public double GrossSales { get; set; }
        public string? AgreementNumber { get; set; }
        public string? MeterNumber { get; set; }
        public string? TransportMeter { get; set; }
        public double TransportVolume { get; set; }
        public double TransportRate { get; set; }
        public double TransportAmt { get; set; }
        public double FuelFactor { get; set; }
        public double? MCF { get; set; }
        public double? SalesMCF { get; set; }
        public double WellheadMMBTU { get; set; }
        public string? SupplyTicketNum { get; set; }
        public double? NetbackPrice { get; set; }
        public double Purchase { get; set; }
        public string? NominationID { get; set; }
        public string? LeaseNumber { get; set; }
        public DateOnly FlowDate { get; set; }
        public string? Pipeline { get; set; }
        public string? DeliveryPoint { get; set; }
        public double FuelAmount { get; set; }
        public string? DealType { get; set; }
        public double? PhysicalMMBTU { get; set; }
        public int TransportRow { get; set; }
        public bool Nonjurisdictional { get; set; }
        public string? PriceType { get; set; }
        public string? PipeShort { get; set; }
        public string? CounterpartyShort { get; set; }
        public string? DealPurpose { get; set; }
        public string? VolumeStatus { get; set; }
        public string? Agency { get; set; }
        public int Count { get; set; }
        public double? TransferIn { get; set; }
        public double? TransferOuts { get; set; }
        public double? TransferInDollars { get; set; }
        public double? TransferOutDollars { get; set; }
        public string? ReceiptPoint { get; set; }
        public double? InvPrice { get; set; }
        public string? SupplyPipeline { get; set; }
        public string? SupplyDealType { get; set; }
        public GasMarketSupply? MarketSupply { get; set; }
    }

    private class PipelineInfo
    {
        public int ID { get; set; }
        public string? Name { get; set; }
        public string? Number { get; set; }
    }
}
