using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace Fast.Web.Logic.ReportSources;

class WaspData(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)
{

    public override async Task<List<ReportFilterParameter>> Run()
    {
        Valuater val = new();
        ValParams valParams = await GetValParams();
        valParams.TransactionTypeIds.Add(3);
        valParams.IsPooledFutureDeal = false;
        valParams.IsWaspNumNull = false;
        List<ValuationResult> vals = await val.GetValuationValues(valParams);

        if (!vals.Any())
            return GetNewFilterParams();

        var ValsGrouped = Valuater.GetValuationValuesGrouped(vals);

        var dealsQuery = (
            from d in db.Deals
                .Include(x => x.Broker)
                .Include(x => x.BrokerAccount)
                .Include(x => x.Product)
            select d
        );
        dealsQuery = val.GetQueryWithFilters(dealsQuery);

        var localDeals = await dealsQuery.ToListAsync();
        localDeals = (
            from d in localDeals
            orderby d.BrokerAccount?.Name, d.Product?.Name, d.AccountingMonth, d.TradingDate, d.TicketNum?.TicketSort()
            select d
        ).ToList();

        var Output = (
            from v in ValsGrouped
            join d in localDeals
                on v.DealId.GetValueOrDefault() equals d.Id
            orderby d.BrokerAccount?.Name, d.Product?.Name, d.AccountingMonth, d.TradingDate, d.TicketNum?.TicketSort()
            select new { v, d }
        ).ToList();

        var minContractMonth = Util.Date.FirstDayOfMonth(Output.Select(x => x.d.StartDate).Min());
        var maxContractMonth = Util.Date.FirstDayOfMonth(Output.Select(x => x.d.StartDate).Max());

        var Expirations = await (
            from pe in db.ProductExpirations
            where pe.ContractMonth >= minContractMonth && pe.ContractMonth <= maxContractMonth
                && pe.BenchmarkId == (int)Enums.Benchmark.Global
            select pe
        ).ToDictionaryAsync(x => System.Tuple.Create(x.ContractMonth, x.ProductId));

        List<OutputItem> DetailList = [];
        Tuple<DateOnly, int> ExpirationKey;
        int ProductID;

        WASPHelper waspPricer = new(db, valParams.AsOfDate);

        DateOnly noStartDate = new(1900, 1, 1);
        DateOnly noEndDate = new(9999, 12, 31);
        foreach (var o in Output)
        {
            var row = new OutputItem();
            {
                var withBlock = row;
                withBlock.AccountingMonth = o.d.AccountingMonth.GetValueOrDefault();
                withBlock.TicketNum = o.d.TicketNum ?? "";
                withBlock.TradeDate = o.d.TradingDate ?? noStartDate;
                withBlock.ProductionMonth = Util.Date.FirstDayOfMonth(o.d.StartDate ?? noStartDate);
                withBlock.NumContracts = o.d.NumOfContracts.GetValueOrDefault() * (o.d.BuyButton ?? -1);
                withBlock.TradeVolume = o.v.SumPositionAmount;
                withBlock.TradePrice = o.d.FixedPrice.GetValueOrDefault();
                withBlock.TradeDollars = o.v.SumNotionalAmount;
                withBlock.MarketPrice = o.v.AvgMarketPrice;
                withBlock.PLByTrade = o.v.SumPnL;
                if (o.d.BuyButton == 1)
                {
                    withBlock.Commission = 0;
                    withBlock.CommissionPerMMBTU = 0;
                }
                else
                {
                    withBlock.Commission = (o.d.Broker?.Commission ?? 0) * o.d.NumOfContracts.GetValueOrDefault() * 2.0;
                    withBlock.CommissionPerMMBTU = ((o.d.Broker?.Commission ?? 0) * (double)o.d.NumOfContracts.GetValueOrDefault() * 2.0) / o.v.SumPositionAmount * -1.0;
                }
                withBlock.HedgeFeeDollars = o.d.HedgeFee.GetValueOrDefault() * o.v.SumPositionAmount * -1.0;
                withBlock.HedgeFeePerMMBTU = o.d.HedgeFee.GetValueOrDefault();
                withBlock.WASPPrice = waspPricer.GetWaspPrice(o.d.WaspNum ?? "", o.d.ProductId, o.d.TicketNum ?? "", o.d.NumOfContracts.GetValueOrDefault(), o.d.FixedPrice, o.d.BrokerId, o.d.BuyButton, o.d.AccountingMonth, o.d.StartDate);
                withBlock.AccountingMonth = o.d.AccountingMonth.GetValueOrDefault();
                withBlock.BrokerAccount = o.d.BrokerAccount?.Name ?? "";
                withBlock.Commodity = o.d.Product?.Name ?? "";
                withBlock.WASPNum = o.d.WaspNum ?? "";
                ProductID = o.d.ProductId;
                ExpirationKey = System.Tuple.Create(withBlock.ProductionMonth, ProductID);
                if ((Expirations.ContainsKey(ExpirationKey)))
                    withBlock.ExpirationDate = Expirations[ExpirationKey].ExpirationDate ?? noEndDate;
            }

            DetailList.Add(row);
        }

        DataTable dt = WriteOutputItemsToDataTable(DetailList);
        FillSheet("WASP Data", dt);

        return GetNewFilterParams();
    }

    class OutputItem
    {
        [Column("Accounting Month")]
        public DateOnly AccountingMonth { get; set; }

        [Column("Ticket #")]
        public string? TicketNum { get; set; }

        [Column("Trade Month")]
        public DateOnly TradeDate { get; set; }

        [Column("Production Month")]
        public DateOnly ProductionMonth { get; set; }

        [Column("No. of K's")]
        public int NumContracts { get; set; }

        [Column("Trade Volume")]
        public double TradeVolume { get; set; }

        [Column("Trade Price")]
        public double TradePrice { get; set; }

        [Column("Trade Dollars")]
        public double TradeDollars { get; set; }

        [Column("Market Price")]
        public double MarketPrice { get; set; }

        [Column("P/L by Trade")]
        public double PLByTrade { get; set; }

        [Column("Commission")]
        public double Commission { get; set; }

        [Column("Commission Per mmbtu")]
        public double CommissionPerMMBTU { get; set; }

        [Column("Hedge Fee Dollars")]
        public double HedgeFeeDollars { get; set; }

        [Column("Hedge Fee Per mmbtu")]
        public double HedgeFeePerMMBTU { get; set; }

        [Column("WASP Price")]
        public double WASPPrice { get; set; }

        [Column("WASP Num")]
        public string? WASPNum { get; set; }

        [Column("Broker Account")]
        public string? BrokerAccount { get; set; }

        [Column("Commodity")]
        public string? Commodity { get; set; }

        [Column("Expiration Date")]
        public DateOnly ExpirationDate { get; set; }
    }

    private DataTable WriteOutputItemsToDataTable(List<OutputItem> OutputItems)
    {
        DataTable myDt = new();

        Type t = typeof(OutputItem);
        var props = t.GetProperties().ToList();
        for (var i = 0; i <= props.Count - 1; i++)
        {
            var prop = props[i];
            var dsiplayName = Util.String.GetColDisplayName(prop);
            myDt.Columns.Add(dsiplayName, prop.PropertyType);
        }

        foreach (var oi in OutputItems)
        {
            DataRow r = myDt.NewRow();
            for (var i = 0; i <= props.Count - 1; i++)
            {
                var prop = props[i];
                var propValue = prop.GetValue(oi);
                r[i] = propValue is DateOnly dt && dt == DateOnly.MinValue ? DBNull.Value : propValue;
            }
            myDt.Rows.Add(r);
        }

        return myDt;
    }
}
