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

namespace Fast.Web.Logic.ReportSources;

class KeepWholes(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<KeepWholeReportItem> KeepWholeReportItems = [];
    private Dictionary<int, GasControlDeal>? LocalDeals;
    private Dictionary<int, Logic.GasControl.GasControlMeter>? Meters;
    private Dictionary<int, Logic.GasControl.Entity>? Entities;
    private Dictionary<int, Pipe>? Pipes;
    private Dictionary<IntegerDatePair, ValuationResult>? vals;
    private ValParams? valParams;

    public override async Task<List<ReportFilterParameter>> Run()
    {
        await GetVals();
        GetMarketSupplies();
        GetLocalDeals();
        GetMeters();
        GetEntities();
        await GetPipes();

        var NormalSupplyNoms = (
            from ms in MarketSupplies
            where ms.SupplyDealID != null
            select ms
        ).ToList();

        var NomsBySupplyTransferAndNomDate = (
            from ms in MarketSupplies
            where ms.SupplyTransferID != null
            orderby ms.SupplyTransferID, ms.NomDate, ms.MarketDealID, ms.MarketTransferID
            select ms
        ).ToLookup(x => System.Tuple.Create(x.SupplyTransferID.GetValueOrDefault(), x.NomDate));

        foreach (var nsn in NormalSupplyNoms)
        {
            if (nsn.MarketDealID != null)
            {
                // real market deal
                if (nsn.IsKeepWhole)
                {
                    KeepWholeReportItem kri = new();
                    FillKriSupply(kri, nsn);
                    FillKriMarket(kri, nsn);
                    KeepWholeReportItems.Add(kri);
                }
            }
            else
            {
                // transfer market deal
                GasControlMarketSupply? RealMarketDealNom = null;

                GasControlMarketSupply? PreviousNom = nsn;

                while (RealMarketDealNom == null && PreviousNom != null)
                {
                    var key = System.Tuple.Create(PreviousNom.MarketTransferID.GetValueOrDefault(), PreviousNom.NomDate);
                    var NextNoms = NomsBySupplyTransferAndNomDate[key];

                    if (NextNoms.Any())
                    {
                        foreach (var NextNom in NextNoms)
                        {
                            if (NextNom.MarketDealID != null && NextNom.IsKeepWhole == true)
                            {
                                RealMarketDealNom = NextNom;

                                KeepWholeReportItem kri = new();
                                FillKriSupply(kri, nsn);
                                FillKriMarket(kri, RealMarketDealNom);
                                KeepWholeReportItems.Add(kri);
                            }

                            PreviousNom = NextNom;
                        }
                    }
                    else
                        PreviousNom = null;
                }
            }
        }

        KeepWholeReportItems = (
            from kri in KeepWholeReportItems
            orderby kri.ReceiptPipeline, kri.ReceiptMeter, kri.ReceiptCounterparty, kri.MarketPipeline, kri.ReceiptTicketNum, kri.MarketTicketNum, kri.Day
            select kri
        ).ToList();

        DataTable dt = GetKeepWholeReportItemsToDataTable();
        FillSheet("Keep Wholes", dt);

        return GetNewFilterParams();
    }

    class KeepWholeReportItem
    {
        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? MarketTicketNum { get; set; }
        public string? MarketCounterparty { get; set; }
        public string? MarketPipeline { get; set; }
        public double MarketPrice { get; set; }
        public string? Comments { get; set; }
    }

    private void GetLocalDeals()
    {
        var query = (from d in db.Deals where d.TransactionTypeId == 1 select d);
        var exp = Util.Date.GetDateRangeExpression<Deal>(valParams?.PositionDateRanges ?? new List<DateRange>(), "StartDate", "EndDate", false);
        query = query.Where(exp);

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

    private void GetMarketSupplies()
    {
        var query = (from ms in db.GasMarketSupplies select ms);
        var exp = Util.Date.GetDateRangeExpression<GasMarketSupply>(valParams?.PositionDateRanges ?? new List<DateRange>(), "Date", false);
        query = query.Where(exp);

        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,
                IsKeepWhole = ms.IsKeepWhole,
                Comment = ms.Comment
            }
        ).ToList();
    }

    private void GetMeters()
    {
        Meters = (
            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 GasControlMeter()
            {
                ID = m.Id,
                Name = m.Name,
                Number = mp.Number,
                HubCode = mp.HubCode
            }
        ).ToDictionary(n => n.ID, n => n);
    }

    private async Task GetVals()
    {
        Valuater val = new();
        valParams = await GetValParams();
        valParams.TransactionTypeIds.Add(1);
        valParams.IncludeSosDeals = true;

        vals = (await val.GetValuationValues(valParams)).ToDictionary(
            x => new IntegerDatePair(x.DealId.GetValueOrDefault(), x.PositionStartDate),
            x => x
        );
    }

    private void GetEntities()
    {
        Entities = (
            from e in db.Counterparties
            select new Logic.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 FillKriSupply(KeepWholeReportItem kri, GasControlMarketSupply RealSupplyDealNom)
    {
        GasControlDeal? RealSupplyDeal = LocalDeals?[RealSupplyDealNom.SupplyDealID.GetValueOrDefault()];

        var rsd = RealSupplyDeal;
        var rsdn = RealSupplyDealNom;

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

    private void FillKriMarket(KeepWholeReportItem kri, GasControlMarketSupply RealMarketDealNom)
    {
        GasControlDeal? RealMarketDeal = LocalDeals?[RealMarketDealNom.MarketDealID.GetValueOrDefault()];
        ValuationResult? RealMarketDealVal = vals?[new IntegerDatePair(RealMarketDealNom.MarketDealID.GetValueOrDefault(), RealMarketDealNom.NomDate)];

        var rmd = RealMarketDeal;
        var rmdn = RealMarketDealNom;
        var rmdv = RealMarketDealVal;

        kri.MarketCounterparty = GetEntityName(rmd?.CounterpartyID);
        kri.MarketTicketNum = rmd?.TicketNum;
        kri.MarketPrice = rmdv?.ContractPrice ?? 0;
        kri.MarketPipeline = GetPipeName(rmd?.PipelineSourceDeliveryID.GetValueOrDefault() ?? 0);
        kri.Comments = rmdn.Comment;
    }

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

        var DistinctGroups = (
            from kri in KeepWholeReportItems
            group kri by new { kri.ReceiptTicketNum, kri.ReceiptMeter, kri.MarketTicketNum } into g
            select new
            {
                g.Key.ReceiptTicketNum,
                g.Key.ReceiptMeter,
                g.Key.MarketTicketNum
            }
        );

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

            var DataToWrite = (
                from kri in KeepWholeReportItems
                where kri.ReceiptTicketNum == ItemReceiptTicket
                    && kri.ReceiptMeter == ItemReceiptMeter
                    && kri.MarketTicketNum == ItemMarketTicket
                select kri
            ).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["Market Ticket #"] = i.MarketTicketNum;
                    withBlock["Market Counterparty"] = i.MarketCounterparty;
                    withBlock["Market Pipeline"] = i.MarketPipeline;
                    withBlock["Market Price"] = i.MarketPrice;
                    withBlock["Comments associated with KW (from SOS)"] = i.Comments;
                }
                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("Market Ticket #", typeof(string));
        dt.Columns.Add("Market Counterparty", typeof(string));
        dt.Columns.Add("Market Pipeline", typeof(string));
        dt.Columns.Add("Market Price", typeof(double));
        dt.Columns.Add("Comments associated with KW (from SOS)", typeof(string));
        return dt;
    }

    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)
        {
            Logic.GasControl.Entity? e = Entities?[EntityID.Value];

            if (e != null)
            {
                if (e.NickName != null)
                    return e.NickName;
                else
                    return e.Name ?? "";
            }
            else
                return "";
        }
        else
            return "";
    }
}
