using System.Data;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace Fast.Web.Logic.ReportSources;

class ValuationDataByDeal(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()
    {
        var val = new Valuater();
        var valParams = await GetValParams();
        var vals = await val.GetValuationValuesGrouped(valParams);

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

        DataTable dtMainData = InitValuationDataTable();

        var TransIds = valParams.TransactionTypeIds;
        var FilterTransIds = false;
        if (TransIds.Any())
            FilterTransIds = true;

        var dealQuery = (from d in db.VwRptValuationDataDeals select d);
        var dealExp = Util.Date.GetDateRangeExpression<VwRptValuationDataDeal>(valParams.PositionDateRanges, "StartDate", "EndDate", false);
        dealQuery = dealQuery.Where(dealExp);
        dealQuery = dealQuery.Where(x => !FilterTransIds || TransIds.Contains(x.TransactionTypeId!.Value));
        var deals = await dealQuery.ToArrayAsync();

        var items = (
            from v in vals
            join d in deals on v.DealId.GetValueOrDefault() equals d.Id
            select new
            {
                v.TicketNum,
                v.MasterTicketNum,
                TradeDate = d.TradingDate,
                v.AvgVolume,
                v.AvgMarketPrice,
                v.AvgContractPrice,
                v.AvgInvoicePrice,
                d.Basis,
                d.PremiumOrDiscount,
                PNL_MTM = v.SumPnL,
                d.Counterparty,
                d.CounterpartyNickname,
                d.TransactionType,
                d.TransactionTypeId,
                d.Pipeline,
                d.PipeShort,
                d.Point,
                d.Broker,
                d.PriceIndex,
                d.PriceIndex2,
                d.DealPurpose,
                d.DealType,
                d.InternalEntity,
                d.IsFixedPrice,
                IsSupplyDeal = d.BuyButton == (int)Enums.BuyButton.Buy,
                d.FixedPrice,
                IsMonthlyIndex = v.IsMonthlyIndexDeal,
                d.BrokerAccount,
                d.Book,
                d.Portfolio,
                d.Strategy,
                d.Region,
                d.NymexExecutionFee,
                d.ExecutionMargin,
                d.SalesMargin,
                d.StartDate,
                d.EndDate,
                d.BuyButton,
                d.CommissionPerContract,
                d.TraderShortName,
                d.TraderName,
                d.Comments,
                d.InternalMemo,
                d.VolumeTypeId
            }
        ).ToList();

        items = (from i in items orderby i.TransactionType, i.TicketNum.TicketSort() select i).ToList();

        foreach (var item in items)
        {
            DataRow r = dtMainData.NewRow();
            {
                var withBlock = r;
                withBlock[ColumnName.TicketNum] = item.TicketNum;
                withBlock[ColumnName.MasterTicketNum] = item.MasterTicketNum;
                withBlock[ColumnName.BuyOrSell] = item.BuyButton.GetValueOrDefault() == 1 ? "Buy" : "Sell";
                withBlock[ColumnName.TradeDate] = item.TradeDate == null ? DBNull.Value : (object)item.TradeDate;
                withBlock[ColumnName.CustomerOrSupply] = ValuationData.GetCustomerSupplyCaption(item.TransactionTypeId!.Value, item.IsSupplyDeal, item.BuyButton);

                if (item.TransactionTypeId == 1 & item.IsFixedPrice.GetValueOrDefault() == 1 & item.IsSupplyDeal == false)
                    withBlock[ColumnName.PositionType] = "Customer Fixed WASP";
                else if (item.TransactionTypeId == 1 & item.IsFixedPrice.GetValueOrDefault() == 1 & item.IsSupplyDeal == true)
                    withBlock[ColumnName.PositionType] = "Physical Fixed Price Supply";
                else if (item.TransactionTypeId == 3 || item.TransactionTypeId == 6)
                    withBlock[ColumnName.PositionType] = "Financial Supply Hedges in Place";
                else if (item.IsSupplyDeal == true)
                    withBlock[ColumnName.PositionType] = "Supply Deal";
                else
                    withBlock[ColumnName.PositionType] = "Other";

                withBlock[ColumnName.DealStartDate] = item.StartDate;
                withBlock[ColumnName.DealEndDate] = item.EndDate;
                withBlock[ColumnName.Volume] = item.AvgVolume;
                withBlock[ColumnName.MarketPrice] = item.AvgMarketPrice;
                withBlock[ColumnName.ContractPrice] = item.AvgContractPrice;
                withBlock[ColumnName.InvoicePrice] = item.AvgInvoicePrice;
                withBlock[ColumnName.Basis] = item.Basis == null ? DBNull.Value : (object)item.Basis;
                withBlock[ColumnName.PremiumOrDiscount] = item.PremiumOrDiscount == null ? DBNull.Value : (object)item.PremiumOrDiscount;
                withBlock[ColumnName.PNL_MTM] = item.PNL_MTM;
                withBlock[ColumnName.Counterparty] = item.Counterparty;
                withBlock[ColumnName.CounterpartyNickname] = item.CounterpartyNickname;
                withBlock[ColumnName.InternalEntity] = item.InternalEntity;
                withBlock[ColumnName.TransactionType] = item.TransactionType;
                withBlock[ColumnName.ProductGroup] = ValuationData.GetProductGroupName(item.TransactionTypeId.Value, item.TransactionType);
                withBlock[ColumnName.Pipeline] = item.Pipeline;
                withBlock[ColumnName.PipeShort] = item.PipeShort;
                withBlock[ColumnName.Point] = item.Point;
                withBlock[ColumnName.Broker] = item.Broker;
                withBlock[ColumnName.PriceIndex] = item.PriceIndex;
                withBlock[ColumnName.PriceIndex2] = item.PriceIndex2;
                withBlock[ColumnName.DealPurpose] = item.DealPurpose;
                withBlock[ColumnName.DealType] = item.DealType;

                if (item.IsFixedPrice.HasValue == false)
                    withBlock[ColumnName.PriceType] = "";
                else if (item.IsFixedPrice.GetValueOrDefault() == 1)
                    withBlock[ColumnName.PriceType] = "Fixed Price";
                else if (item.IsFixedPrice.GetValueOrDefault() == 0)
                    withBlock[ColumnName.PriceType] = "Index Price";
                withBlock[ColumnName.FixedPrice] = item.FixedPrice.GetValueOrDefault();

                withBlock[ColumnName.BrokerAccount] = item.BrokerAccount;
                withBlock[ColumnName.Book] = item.Book;
                withBlock[ColumnName.Portfolio] = item.Portfolio;
                withBlock[ColumnName.Strategy] = item.Strategy;
                withBlock[ColumnName.Region] = item.Region;
                withBlock[ColumnName.TotalFixedPrice] = item.FixedPrice.GetValueOrDefault() + item.Basis.GetValueOrDefault();
                withBlock[ColumnName.NymexExecutionFee] = item.NymexExecutionFee == null ? DBNull.Value : (object)item.NymexExecutionFee;
                withBlock[ColumnName.ExecutionMargin] = item.ExecutionMargin == null ? DBNull.Value : (object)item.ExecutionMargin;
                withBlock[ColumnName.SalesMargin] = item.SalesMargin == null ? DBNull.Value : (object)item.SalesMargin;
                withBlock[ColumnName.CommissionPerContract] = item.CommissionPerContract == null ? DBNull.Value : (object)item.CommissionPerContract;
                withBlock[ColumnName.TraderShortName] = item.TraderShortName == null ? DBNull.Value : (object)item.TraderShortName;
                withBlock[ColumnName.TraderName] = item.TraderName == null ? DBNull.Value : (object)item.TraderName;
                withBlock[ColumnName.Comments] = item.Comments;
                withBlock[ColumnName.InternalMemo] = item.InternalMemo;
                withBlock[ColumnName.IsFixed] = string.IsNullOrWhiteSpace(item.PriceIndex);
                withBlock[ColumnName.IsMonthlyIndex] = item.IsMonthlyIndex;
                withBlock[ColumnName.IsVolumeMonthly] = item.VolumeTypeId == 1;
            }
            dtMainData.Rows.Add(r);
        }

        FillSheet("Valuation Data by Deal", dtMainData);

        return GetNewFilterParams();
    }

    private DataTable InitValuationDataTable()
    {
        DataTable myDt = new();

        {
            var withBlock = myDt.Columns;
            withBlock.Add(ColumnName.TicketNum, typeof(string));
            withBlock.Add(ColumnName.BuyOrSell, typeof(string));
            withBlock.Add(ColumnName.CustomerOrSupply, typeof(string));
            withBlock.Add(ColumnName.TradeDate, typeof(DateOnly));
            withBlock.Add(ColumnName.PositionType, typeof(string));
            withBlock.Add(ColumnName.DealStartDate, typeof(DateOnly));
            withBlock.Add(ColumnName.DealEndDate, typeof(DateOnly));
            withBlock.Add(ColumnName.Volume, typeof(double));
            withBlock.Add(ColumnName.MarketPrice, typeof(double));
            withBlock.Add(ColumnName.ContractPrice, typeof(double));
            withBlock.Add(ColumnName.InvoicePrice, typeof(double));
            withBlock.Add(ColumnName.Basis, typeof(double));
            withBlock.Add(ColumnName.PremiumOrDiscount, typeof(double));
            withBlock.Add(ColumnName.PNL_MTM, typeof(double));
            withBlock.Add(ColumnName.Counterparty, typeof(string));
            withBlock.Add(ColumnName.CounterpartyNickname, typeof(string));
            withBlock.Add(ColumnName.TransactionType, typeof(string));
            withBlock.Add(ColumnName.ProductGroup, typeof(string));
            withBlock.Add(ColumnName.Pipeline, typeof(string));
            withBlock.Add(ColumnName.PipeShort, typeof(string));
            withBlock.Add(ColumnName.Point, typeof(string));
            withBlock.Add(ColumnName.Broker, typeof(string));
            withBlock.Add(ColumnName.PriceIndex, typeof(string));
            withBlock.Add(ColumnName.PriceIndex2, typeof(string));
            withBlock.Add(ColumnName.DealPurpose, typeof(string));
            withBlock.Add(ColumnName.DealType, typeof(string));
            withBlock.Add(ColumnName.InternalEntity, typeof(string));
            withBlock.Add(ColumnName.PriceType, typeof(string));
            withBlock.Add(ColumnName.FixedPrice, typeof(double));
            withBlock.Add(ColumnName.BrokerAccount, typeof(string));
            withBlock.Add(ColumnName.Book, typeof(string));
            withBlock.Add(ColumnName.Portfolio, typeof(string));
            withBlock.Add(ColumnName.Strategy, typeof(string));
            withBlock.Add(ColumnName.Region, typeof(string));
            withBlock.Add(ColumnName.TotalFixedPrice, typeof(double));
            withBlock.Add(ColumnName.NymexExecutionFee, typeof(double));
            withBlock.Add(ColumnName.ExecutionMargin, typeof(double));
            withBlock.Add(ColumnName.SalesMargin, typeof(double));
            withBlock.Add(ColumnName.MasterTicketNum, typeof(string));
            withBlock.Add(ColumnName.TraderShortName, typeof(string));
            withBlock.Add(ColumnName.TraderName, typeof(string));
            withBlock.Add(ColumnName.CommissionPerContract, typeof(double));
            withBlock.Add(ColumnName.IsFixed, typeof(bool));
            withBlock.Add(ColumnName.IsMonthlyIndex, typeof(bool));
            withBlock.Add(ColumnName.IsVolumeMonthly, typeof(bool));
            withBlock.Add(ColumnName.Comments, typeof(string));
            withBlock.Add(ColumnName.InternalMemo, typeof(string));
        }
        return myDt;
    }

    private class ColumnName
    {
        public const string TicketNum = "TicketNum";
        public const string MasterTicketNum = "Master TicketNum";
        public const string BuyOrSell = "Buy/Sell";
        public const string CustomerOrSupply = "Customer/Supply";
        public const string TradeDate = "Trade Date";
        public const string DealStartDate = "Deal Start Date";
        public const string DealEndDate = "Deal End Date";
        public const string Volume = "Volume";
        public const string MarketPrice = "Market Price";
        public const string ContractPrice = "Contract Price";
        public const string InvoicePrice = "Invoice Price";
        public const string Basis = "Basis";
        public const string PremiumOrDiscount = "Premium or Discount";
        public const string PNL_MTM = "MTM";
        public const string Counterparty = "Counterparty";
        public const string CounterpartyNickname = "Counterparty Nickname";
        public const string TransactionType = "Transaction Type";
        public const string ProductGroup = "Product Group";
        public const string Pipeline = "Pipeline";
        public const string PipeShort = "Pipe Short";
        public const string Point = "Point";
        public const string Broker = "Broker";
        public const string PriceIndex = "Price Index";
        public const string PriceIndex2 = "Price Index 2";
        public const string DealPurpose = "Deal Purpose";
        public const string DealType = "Deal Type";
        public const string InternalEntity = "Internal Entity";
        public const string PriceType = "Price Type";
        public const string FixedPrice = "Fixed Price";
        public const string PositionType = "Position Type";
        public const string BrokerAccount = "Broker Account";
        public const string Book = "Book";
        public const string Portfolio = "Portfolio";
        public const string Strategy = "Strategy";
        public const string Region = "Region";
        public const string TotalFixedPrice = "Total Fixed Price";
        public const string NymexExecutionFee = "Nymex Execution Fee";
        public const string ExecutionMargin = "Execution Margin";
        public const string SalesMargin = "Sales Margin";
        public const string CommissionPerContract = "Commission Per Contract";
        public const string TraderShortName = "Trader Short Name";
        public const string TraderName = "Trader Name";
        public const string IsFixed = "Is Fixed";
        public const string IsMonthlyIndex = "Is Monthly Index";
        public const string IsVolumeMonthly = "Is Volume Monthly";
        public const string Comments = "Comments";
        public const string InternalMemo = "Internal Memo";
    }
}
