using System.Data;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using Fast.Shared.Logic.RateControl;
using Fast.Web.Logic.GasControl;
// using DocumentFormat.OpenXml.Spreadsheet;

namespace Fast.Web.Logic.ReportSources;

class PtrData(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 List<GasControlMarketSupply>? MarketSupplies;
    private List<PtrReportItem> PtrReportItems = [];
    private Dictionary<int, GasControlDeal>? LocalDeals;
    private Dictionary<int, GasControlMeter>? Meters;
    private Dictionary<int, GasControl.Entity>? Entities;
    private Dictionary<int, Pipe>? Pipes;
    private Dictionary<int, Zone>? Zones;
    private Dictionary<int, GasControlTransferDeal>? TransferDeals;
    private Dictionary<int, PipeContract>? PipeContracts;

    private RateCalculator? rateCalculator;

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

        List<int> LocPipes = new();

        string paramName;

        paramName = "Month";
        if (!HasParam(paramName))
        {
            var dateRange = new DateRange(Enums.DateStyle.DateRange, Util.Date.FirstDayOfMonth(DateTime.Today), Util.Date.LastDayOfMonth(DateTime.Today));
            AddDateRangeParam(paramName, dateRange);
        }

        GetMarketSupplies();
        GetLocalDeals();
        GetTransferDeals();
        GetMeters();
        GetEntities();
        await GetPipes();
        GetZones();
        GetPipeContracts();


        paramName = "Pipeline";
        if (HasParam(paramName))
        {
            await FixInvalidFilterParams(paramName);
            LocPipes.AddRange(GetIdList(paramName));
        }
        else
            LocPipes = (from p in Pipes select p.Key).Distinct().ToList();

        //does not include marketsupplies with 0 nom volume
        List<PtrNom> PtrNoms = (
            from ms in MarketSupplies
            where ms.PtrPipeContractID != null && ms.PtrDeliveryMeterID != null && Zones != null && Meters != null && Zones != null && Meters != null && LocPipes.Contains(Zones[Meters[ms.SupplyMeterID.GetValueOrDefault()].ZoneID].PipelineId) && ms.SupplyMeterID != ms.PtrDeliveryMeterID.GetValueOrDefault()
            group ms by new { ms.PipeContractID, ms.PtrPipeContractID, ms.NomDate, ms.SupplyMeterID, ms.PtrDeliveryMeterID, ms.SupplyDealID, ms.SupplyTransferID } into g
            let PipeContractID = g.Key.PipeContractID
            let PtrPipeContractID = g.Key.PtrPipeContractID
            let NomDate = g.Key.NomDate
            let SupplyMeterID = g.Key.SupplyMeterID
            let PtrDeliveryMeterID = g.Key.PtrDeliveryMeterID
            let SupplyDealID = g.Key.SupplyDealID
            let SupplyTransferID = g.Key.SupplyTransferID
            let SumVolume = g.Sum(ms => ms.NominatedVolume)
            select new PtrNom
            {
                NominatedVolume = SumVolume,
                NomDate = NomDate,
                SupplyDealID = SupplyDealID,
                SupplyMeterID = SupplyMeterID,
                SupplyTransferID = SupplyTransferID,
                PtrDeliveryMeterID = PtrDeliveryMeterID,
                PtrPipeContractID = PtrPipeContractID,
                PipelineContractID = PipeContractID
            }
        ).ToList();

        var Noms = (
            from n in MarketSupplies
            orderby n.MarketTransferID, n.NomDate, n.SupplyDealID, n.SupplyTransferID
            select n
        );

        var NomsByMarketTransferAndDate = Noms.ToLookup(
            x => Tuple.Create(x.MarketTransferID, x.NomDate)
        );

        foreach (var PtrNom in PtrNoms)
        {
            List<double> PtrFuelPercents = [];
            var fuelPercent = rateCalculator.GetFuelPercent(PtrNom.PtrPipeContractID, PtrNom.NomDate, PtrNom.SupplyMeterID.GetValueOrDefault(), PtrNom.PtrDeliveryMeterID.GetValueOrDefault()).GetValueOrDefault();
            PtrFuelPercents.Add(fuelPercent);

            if (PtrNom.SupplyDealID != null)
            {
                PtrReportItem pri = new();
                GasControlMarketSupply Nom = ConvertPtrNomToMarketSupply(PtrNom);

                if (FillPriPtrReceiptVolume(pri, PtrFuelPercents, Nom, PtrNom))
                {
                    FillPriSupply(pri, Nom);
                    FillPriMarket(pri, Nom);
                    PtrReportItems.Add(pri);
                }
            }
            else
            {
                // transfer
                GasControlMarketSupply? RealSupplyDealDealNom = null;
                GasControlMarketSupply? LastNom = ConvertPtrNomToMarketSupply(PtrNom);

                while (RealSupplyDealDealNom == null)
                {
                    var PreviousNoms = NomsByMarketTransferAndDate[Tuple.Create(LastNom.SupplyTransferID, LastNom.NomDate)].ToList();

                    if (PreviousNoms.Any())
                    {
                        bool IsFirstNom = true;
                        int PreviousNomCount = PreviousNoms.Count;

                        foreach (var PreviousNom in PreviousNoms)
                        {
                            GasControlTransferDeal? PreviousNomMarketTransfer = TransferDeals?[PreviousNom.MarketTransferID.GetValueOrDefault()];

                            if (IsFirstNom)
                            {
                                if (PreviousNomMarketTransfer?.IsMeter1Supply ?? false)
                                    PtrFuelPercents.Add(rateCalculator.GetFuelPercent(PreviousNom.PipeContractID, PreviousNom.NomDate, PreviousNom.SupplyMeterID.GetValueOrDefault(), PreviousNomMarketTransfer.Meter2ID).GetValueOrDefault());
                                else
                                    PtrFuelPercents.Add(rateCalculator.GetFuelPercent(PreviousNom.PipeContractID, PreviousNom.NomDate, PreviousNom.SupplyMeterID.GetValueOrDefault(), PreviousNomMarketTransfer?.Meter1ID ?? 0).GetValueOrDefault());
                            }
                            IsFirstNom = false;

                            if (PreviousNom.SupplyDealID != null)
                            {
                                RealSupplyDealDealNom = PreviousNom;

                                PtrReportItem pri = new();
                                if (FillPriPtrReceiptVolume(pri, PtrFuelPercents, RealSupplyDealDealNom, PtrNom))
                                {
                                    FillPriSupply(pri, RealSupplyDealDealNom);
                                    FillPriMarket(pri, ConvertPtrNomToMarketSupply(PtrNom));
                                    PtrReportItems.Add(pri);
                                }
                            }

                            LastNom = PreviousNom;
                        }
                    }
                    else
                        break;
                }
            }
        }

        PtrReportItems = (from pri in PtrReportItems
                          orderby pri.ReceiptPipeline, pri.ReceiptMeter, pri.ReceiptCounterparty, pri.ReceiptTicketNum, pri.Day
                          select pri
                        ).ToList();

        var dt = WritePtrReportItems();
        FillSheet("PTR Data", dt);

        var filterParams = GetNewFilterParams();
        await Task.CompletedTask;
        return filterParams;
    }

    private GasControlMarketSupply ConvertPtrNomToMarketSupply(PtrNom PtrNom)
    {
        GasControlMarketSupply ms = new()
        {
            NominatedVolume = PtrNom.NominatedVolume,
            NomDate = PtrNom.NomDate,
            SupplyDealID = PtrNom.SupplyDealID,
            SupplyMeterID = PtrNom.SupplyMeterID,
            PtrDeliveryMeterID = PtrNom.PtrDeliveryMeterID,
            PtrPipeContractID = PtrNom.PtrPipeContractID,
            SupplyTransferID = PtrNom.SupplyTransferID
        };
        return ms;
    }

    private void FillPriSupply(PtrReportItem pri, GasControlMarketSupply RealSupplyDealNom)
    {
        GasControlDeal? RealSupplyDeal = LocalDeals?[RealSupplyDealNom.SupplyDealID.GetValueOrDefault()];

        var rsd = RealSupplyDeal;
        var rsdn = RealSupplyDealNom;

        pri.Day = rsdn.NomDate;
        pri.ReceiptTicketNum = rsd?.TicketNum ?? "";
        pri.ReceiptCounterparty = GetEntityName(rsd?.CounterpartyID);
        pri.ReceiptMeter = Meters?[rsdn.SupplyMeterID.GetValueOrDefault()].Name + " / " + Meters?[rsdn.SupplyMeterID.GetValueOrDefault()].Number;
        pri.ReceiptPipeline = GetPipeName(rsd?.PipelineSourceDeliveryID.GetValueOrDefault() ?? 0);
    }

    private void FillPriMarket(PtrReportItem pri, GasControlMarketSupply PtrNom)
    {
        pri.PtrContractOwner = GetOwnership(PtrNom.PtrPipeContractID);
        pri.PtrContractNum = PipeContracts?[PtrNom.PtrPipeContractID.GetValueOrDefault()].ContractID ?? "";
        pri.PtrDeliveryMeter = Meters?[PtrNom.PtrDeliveryMeterID.GetValueOrDefault()].Name + " / " + Meters?[PtrNom.PtrDeliveryMeterID.GetValueOrDefault()].Number;
    }

    private bool FillPriPtrReceiptVolume(PtrReportItem pri, List<double> PtrFuelPercents, GasControlMarketSupply RealSupplyDealNom, PtrNom PtrNom)
    {
        GasControlDeal? RealSupplyDeal = LocalDeals?[RealSupplyDealNom.SupplyDealID.GetValueOrDefault()];

        double? PtrPercent = rateCalculator?.GetPtrPercent(PtrNom.NomDate, PtrNom.SupplyMeterID.GetValueOrDefault(), PtrNom.PtrDeliveryMeterID, RealSupplyDeal?.CounterpartyID, PtrNom.SupplyDealID, PtrNom.SupplyTransferID, PtrNom.PipelineContractID);

        if (PtrPercent.GetValueOrDefault() == 0)
            return false;
        else
        {
            int Volume = PtrNom.NominatedVolume.GetValueOrDefault();

            int PTRAmt = (int)Math.Round(Volume / (1 - PtrPercent.GetValueOrDefault()) - Volume, 0);

            foreach (var PtrFuelPercent in PtrFuelPercents)
            {
                double PtrFuelAmt = Math.Round(PTRAmt / (1 - PtrFuelPercent) - PTRAmt, 0);

                PTRAmt = (int)(PTRAmt + PtrFuelAmt);
            }

            pri.PtrReceiptVolume = PTRAmt;

            if (PTRAmt > 0)
                return true;
            else
                return false;
        }
    }

    private DataTable WritePtrReportItems()
    {
        var dt = CreateDt();

        var DistinctGroups = (
            from pri in PtrReportItems
            group pri by new { pri.ReceiptTicketNum, pri.ReceiptMeter } into Group
            let ReceiptTicketNum = Group.Key.ReceiptTicketNum
            let ReceiptMeter = Group.Key.ReceiptMeter
            select new { ReceiptTicketNum, ReceiptMeter });

        foreach (var Item in DistinctGroups)
        {
            string ItemReceiptTicket = Item.ReceiptTicketNum;
            string ItemReceiptMeter = Item.ReceiptMeter;

            var DataToWrite = (from pri in PtrReportItems
                               where pri.ReceiptTicketNum == ItemReceiptTicket && pri.ReceiptMeter == ItemReceiptMeter
                               select pri
                               ).ToList();

            foreach (var i in DataToWrite)
            {
                var row = dt.NewRow();
                {
                    var withBlock = row;
                    withBlock["Day"] = i.Day;
                    withBlock["Receipt Ticket #"] = i.ReceiptTicketNum;
                    withBlock["Receipt Counterparty"] = i.ReceiptCounterparty;
                    withBlock["Receipt Pipeline"] = i.ReceiptPipeline;
                    withBlock["Receipt Meter"] = i.ReceiptMeter;
                    withBlock["PTR Contract Owner"] = i.PtrContractOwner;
                    withBlock["PTR Contract #"] = i.PtrContractNum;
                    withBlock["PTR Delivery Point"] = i.PtrDeliveryMeter;
                    withBlock["PTR Receipt Volume"] = i.PtrReceiptVolume;
                }
                dt.Rows.Add(row);
            }
        }

        return dt;
    }


    private DataTable CreateDt()
    {
        DataTable dt = new();
        dt.Columns.Add("Day", typeof(DateOnly));
        dt.Columns.Add("Receipt Ticket #", typeof(string));
        dt.Columns.Add("Receipt Counterparty", typeof(string));
        dt.Columns.Add("Receipt Pipeline", typeof(string));
        dt.Columns.Add("Receipt Meter", typeof(string));
        dt.Columns.Add("PTR Contract Owner", typeof(string));
        dt.Columns.Add("PTR Contract #", typeof(string));
        dt.Columns.Add("PTR Delivery Point", typeof(string));
        dt.Columns.Add("PTR Receipt Volume", typeof(double));
        return dt;
    }

    private void GetLocalDeals()
    {
        var query = (from d in db.Deals where d.TransactionTypeId == 1 select d);

        var monthExpression = GetDateRangeExpression<Deal>("Month", "StartDate", "EndDate", false);
        query = query.Where(monthExpression);

        LocalDeals = (
            from d in query
            select new GasControlDeal()
            {
                DealID = d.Id,
                TicketNum = d.TicketNum,
                CounterpartyID = d.CounterpartyId,
                PipelineSourceDeliveryID = d.PipelineSourceDeliveryId,
                StartDate = d.StartDate,
                EndDate = d.EndDate
            }
        ).ToDictionary(n => n.DealID, n => n); ;
    }

    private void GetTransferDeals()
    {
        var query = (from td in db.TransferDeals select td);

        var monthExpression = GetDateRangeExpression<TransferDeal>("Month", "StartDate", "EndDate", false);
        query = query.Where(monthExpression);

        TransferDeals = (
            from td in query
            select new GasControlTransferDeal()
            {
                TransferDealID = td.Id,
                TicketNum = td.TicketNum,
                IsMeter1Supply = td.IsMeter1Supply,
                Meter1ID = td.TransferMeterMap.Meter1Id,
                Meter2ID = td.TransferMeterMap.Meter2Id,
                StartDate = td.StartDate,
                EndDate = td.EndDate
            }
        ).ToDictionary(n => n.TransferDealID, n => n);
    }

    private void GetMarketSupplies()
    {
        var query = (from ms in db.GasMarketSupplies
                     where ms.Volume.GetValueOrDefault() > 0
                     select ms);

        var monthExpression = GetDateRangeExpression<GasMarketSupply>("Month", "Date", false);
        query = query.Where(monthExpression);

        MarketSupplies = (
            from ms in query
            select new GasControlMarketSupply
            {
                NomDate = ms.Date,
                MarketDealID = ms.MarketNom.DealId,
                MarketTransferID = ms.MarketNom.TransferDealId,
                SupplyDealID = ms.SupplyNom.DealId,
                SupplyTransferID = ms.SupplyNom.TransferDealId,
                SupplyMeterID = ms.SupplyNom.MeterId,
                Comment = ms.Comment,
                SupplyPointID = ms.SupplyNom.PointId,
                PipeContractID = ms.SupplyNom.PipelineContractId,
                PtrPipeContractID = ms.SupplyNom.PtrPipelineContractId,
                PtrDeliveryMeterID = ms.SupplyNom.PtrDeliveryMeterId,
                NominatedVolume = ms.Volume,
                MarketMeterID = ms.MarketNom.MeterId,
                MarketPointID = ms.MarketNom.PointId
            }
        ).ToList();
    }

    private void GetMeters()
    {
        Meters = (
            from m in db.Meters
            join mp in db.MeterProducts on m.Id equals mp.MeterId
            where mp.ProductId == (int)Enums.Product.NaturalGas
            select new GasControlMeter()
            {
                ID = m.Id,
                Name = m.Name,
                Number = mp.Number,
                HubCode = mp.HubCode,
                ZoneID = mp.SourceZoneId ?? 0
            }).ToDictionary(n => n.ID, n => n);
    }

    private void GetEntities()
    {
        Entities = (from e in db.Counterparties
                    select new GasControl.Entity()
                    {
                        ID = e.Id,
                        Name = e.Name,
                        NickName = e.ShortName
                    }).ToDictionary(n => n.ID, n => n);
    }

    private async Task GetPipes()
    {
        Pipes = (await DataHelper.GetPipelinesAsync(true))
           .Where(x => x.IsGasPipe)
           .Select(x => new Pipe()
           {
               ID = x.PipeId,
               Name = x.PipeName,
               PipeShort = x.PipeShort
           }).ToDictionary(n => n.ID, n => n);
    }

    private void GetZones()
    {
        Zones = (from z in db.Zones
                 select new Zone()
                 {
                     Id = z.Id,
                     Name = z.Name,
                     PipelineId = z.PipelineId
                 }).ToDictionary(n => n.Id, n => n);
    }

    private void GetPipeContracts()
    {
        PipeContracts = (from p in db.PipelineContracts
                         select new PipeContract()
                         {
                             ID = p.Id,
                             ContractID = p.ContractId,
                             PipelineID = p.PipelineId,
                             ContractOwnerID = p.ContractOwnerId
                         }).ToDictionary(n => n.ID, n => n);
    }

    public string GetPipeName(int PipelineID)
    {
        if (Pipes != null && Pipes.ContainsKey(PipelineID))
        {
            Pipe p = Pipes[PipelineID];
            if (p.PipeShort != null)
                return p.PipeShort;
            else
                return p.Name ?? "";
        }
        else
            return "";
    }

    public string GetEntityName(int? EntityID)
    {
        if (EntityID.HasValue)
        {
            GasControl.Entity? e = Entities?[EntityID.Value];
            if (e != null)
            {
                if (e.NickName != null)
                    return e.NickName;
                else
                    return e.Name ?? "";
            }
            else
                return "";
        }
        else
            return "";
    }

    public string GetOwnership(int? PipeContractID)
    {
        if (PipeContractID.HasValue)
        {
            PipeContract? K = PipeContracts?[PipeContractID.Value];
            return GetEntityName(K?.ContractOwnerID);
        }
        else
            return "";
    }

    private class PtrReportItem
    {
        public DateOnly Day { get; set; }
        public string? ReceiptTicketNum { get; set; }
        public string? ReceiptCounterparty { get; set; }
        public string? ReceiptPipeline { get; set; }
        public string? ReceiptMeter { get; set; }
        public string? PtrContractOwner { get; set; }
        public string? PtrContractNum { get; set; }
        public string? PtrDeliveryMeter { get; set; }
        public int PtrReceiptVolume { get; set; }
    }

    private class PtrNom
    {
        public int? NominatedVolume;
        public DateOnly NomDate;
        public int? SupplyDealID;
        public int? SupplyMeterID;
        public int? SupplyTransferID;
        public int? PtrDeliveryMeterID;
        public int? PtrPipeContractID;
        public int? PipelineContractID;
    }
}
