using System.Data;
using System.Diagnostics;
using System.Linq.Expressions;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Fast.Shared.Logic.Package;
using static Fast.Shared.Models.Enums;

namespace Fast.Web.Logic;

public abstract class ReportSourceBase
{
    public readonly MyDbContext db;
    public readonly SpreadsheetDocument wb;
    public readonly string reportName;
    public readonly int dataSourceId;
    public readonly int filterId;
    public readonly string filterName;
    public readonly int userInfoId;

    public Dictionary<string, ReportFilterParameter> filterParamsDic;

    public ReportSourceBase(MyDbContext context, SpreadsheetDocument wb, string reportName, int dataSourceId, int filterId, string filterName, List<ReportFilterParameter> filterParams, int userInfoId)
    {
        db = context;
        this.wb = wb;
        this.reportName = reportName;
        this.dataSourceId = dataSourceId;
        this.filterId = filterId;
        this.filterName = filterName;
        this.userInfoId = userInfoId;
        //In most cases we need to get filterParams from the database
        //But in unit test cases we may pass in a list of filterParams that we convert to a dictionary
        var hasFilterParams = filterParams != null && filterParams.Count > 0;
        if (hasFilterParams)
            filterParamsDic = filterParams!.ToDictionary(x => x.Name, StringComparer.OrdinalIgnoreCase);
        else
            filterParamsDic = GetNewFilterParamsDic();
    }

    private Dictionary<string, ReportFilterParameter> GetNewFilterParamsDic()
    {
        var filterParamsDic = (
            from q in db.ReportFilterParameters
            where q.FilterId == filterId
                && q.DataSourceId == dataSourceId
            select q
        ).ToDictionary(x => x.Name, StringComparer.OrdinalIgnoreCase);
        return filterParamsDic;
    }

    public abstract Task<List<ReportFilterParameter>> Run();

    protected List<ReportFilterParameter> GetNewFilterParams()
    {
        //return a new filterParams list since they may have been modified with default values set by the individual report sources
        var newFilterParams = filterParamsDic.Values.ToList();
        return newFilterParams;
    }

    protected void FillSheet<T>(string sheetName, List<T> queryResultList, bool freezeFirstRow = true)
    {
        var templateFormulas = OpenXmlHelper.FillSheetForReport(wb, sheetName, queryResultList);
        OpenXmlHelper.PostProcessingForReport(wb, sheetName, templateFormulas, queryResultList.Count, freezeFirstRow);
        OpenXmlHelper.SaveAllChanges(wb);
    }

    protected void FillSheet(string sheetName, DataTable dataTable, bool freezeFirstRow = true)
    {
        var templateFormulas = OpenXmlHelper.FillSheetForReport(wb, sheetName, dataTable);
        OpenXmlHelper.PostProcessingForReport(wb, sheetName, templateFormulas, dataTable.Rows.Count, freezeFirstRow);
        OpenXmlHelper.SaveAllChanges(wb);
    }

    public void SaveWorkbook()
    {
        if (wb.WorkbookPart == null)
        {
            return;
        }

        SharedStringTablePart? sharedStringPart = wb.WorkbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
        if (sharedStringPart?.SharedStringTable != null)
        {
            int actualCount = sharedStringPart.SharedStringTable.Elements<SharedStringItem>().Count();
            sharedStringPart.SharedStringTable.Count = (uint)actualCount;
            sharedStringPart.SharedStringTable.UniqueCount = (uint)actualCount;

        }

        WorkbookStylesPart? stylesPart = wb.WorkbookPart.GetPartsOfType<WorkbookStylesPart>().FirstOrDefault();
        if (stylesPart?.Stylesheet != null)
        {
            if (stylesPart.Stylesheet.Fonts is { } fonts)
                fonts.Count = (uint)fonts.ChildElements.Count;

            if (stylesPart.Stylesheet.Fills is { } fills)
                fills.Count = (uint)fills.ChildElements.Count;

            if (stylesPart.Stylesheet.Borders is { } borders)
                borders.Count = (uint)borders.ChildElements.Count;

            if (stylesPart.Stylesheet.NumberingFormats is { } numberingFormats)
                numberingFormats.Count = (uint)numberingFormats.ChildElements.Count;

            if (stylesPart.Stylesheet.CellFormats is { } cellFormats)
                cellFormats.Count = (uint)cellFormats.ChildElements.Count;

            if (stylesPart.Stylesheet.CellStyleFormats is { } cellStyleFormats)
                cellStyleFormats.Count = (uint)cellStyleFormats.ChildElements.Count;

            stylesPart.Stylesheet.Save();
        }

        wb.WorkbookPart?.Workbook?.Save();
    }

    protected List<int> GetIdList(string paramName)
    {
        List<int> idList = new();
        if (filterParamsDic.TryGetValue(paramName, out var dealFilterParam))
        {
            string value1 = dealFilterParam.Value1;
            idList = Util.String.ConvertCommaSeparatedIdsToList(value1);
        }
        return idList;
    }

    protected bool GetIsActiveValue(string paramName)
    {
        string paramValue = filterParamsDic[paramName].Value1;
        bool result = paramValue == "Active";
        return result;
    }

    protected int GetBuySellValue(string paramName)
    {
        string paramValue = filterParamsDic[paramName].Value1;
        int result = paramValue == "Buy Deals" ? 1 : -1;
        return result;
    }

    private int? GetHypotheticalValue(string paramName)
    {
        int? result;
        if (!HasParam(paramName) || filterParamsDic[paramName].Value1 == "Real Deals")
            result = 0; //real deals
        else if (filterParamsDic[paramName].Value1 == "Hypothetical Deals")
            result = 1; //hypothetical deals
        else if (filterParamsDic[paramName].Value1 == "All Deals")
            result = null; //all deals
        else
            throw new Exception("Unknown filter selection for 'Hypothetical'");
        return result;
    }

    protected bool HasParam(string paramName)
    {
        return filterParamsDic.ContainsKey(paramName);
    }

    private DateOnly GetDate(string paramName)
    {
        DateOnly result = DateOnly.ParseExact(filterParamsDic[paramName].Value1, "yyyy/MM/dd", null);
        return result;
    }

    protected List<DateRange> GetDateRanges(DateStyle dateStyle, string paramName)
    {
        List<DateRange> dateRanges = new();
        var dateRangeStrings = filterParamsDic[paramName].Value1.Split(",").ToList();
        foreach (string dateRangeString in dateRangeStrings)
        {
            DateRange newDateRange = new(dateStyle);
            DateOnly date1;
            DateOnly date2;
            var dateRangeSplit = dateRangeString.Split("-");
            date1 = DateOnly.ParseExact(dateRangeSplit[0], "yyyy/MM/dd", null);
            if (dateRangeSplit.Length > 1)
                date2 = DateOnly.ParseExact(dateRangeSplit[1], "yyyy/MM/dd", null);
            else
                date2 = date1;
            if (date1 <= date2)
            {
                newDateRange.FromDate = date1;
                newDateRange.ToDate = date2;
            }
            else
            {
                newDateRange.FromDate = date2;
                newDateRange.ToDate = date1;
            }
            dateRanges.Add(newDateRange);
        }
        return dateRanges;
    }

    protected Expression<Func<T, bool>> GetDateRangeExpression<T>(string filterParamName, string dbDatePropertyName, bool allowNulls)
    {
        var dateRanges = GetDateRanges(DateStyle.Unknown, filterParamName);
        var exp = Util.Date.GetDateRangeExpression<T>(dateRanges, dbDatePropertyName, allowNulls);
        return exp;
    }

    protected Expression<Func<T, bool>> GetDateRangeExpression<T>(string filterParamName, string dbFromDatePropertyName, string dbToDatePropertyName, bool allowNulls)
    {
        var dateRanges = GetDateRanges(DateStyle.Unknown, filterParamName);
        var exp = Util.Date.GetDateRangeExpression<T>(dateRanges, dbFromDatePropertyName, dbToDatePropertyName, allowNulls);
        return exp;
    }

    protected void AddDateRangeParam(string paramName, DateRange dateRange)
    {
        var newFilterParam = new ReportFilterParameter
        {
            Name = paramName,
            Preview = dateRange.DisplayText,
            Value1 = dateRange.StringValue
        };
        filterParamsDic.Add(paramName, newFilterParam);
    }

    protected async Task<ValParams> GetValParams()
    {
        var valParams = new ValParams
        {
            Hypothetical = GetHypotheticalValue("Hypothetical")
        };

        if (HasParam("Position Date"))
            valParams.PositionDateRanges = GetDateRanges(DateStyle.MonthRange, "Position Date");
        else if (HasParam("Trade Date"))
            valParams.PositionDateRanges = GetDateRanges(DateStyle.MonthRange, "Trade Date");
        else if (HasParam("Accounting Month"))
            valParams.PositionDateRanges = GetDateRanges(DateStyle.MonthRange, "Accounting Month");
        else if (HasParam("Month"))
            valParams.PositionDateRanges = GetDateRanges(DateStyle.MonthRange, "Month");
        else
        {
            var currentMonthRange = new DateRange(DateStyle.DateRange, Util.Date.FirstDayOfMonth(DateTime.Today), Util.Date.LastDayOfMonth(DateTime.Today));
            valParams.PositionDateRanges.Add(currentMonthRange);
        }

        string paramName;

        paramName = "As Of Date";
        if (HasParam(paramName))
            valParams.AsOfDate = GetDate(paramName);

        paramName = "Accounting Month";
        if (HasParam(paramName))
            valParams.AccountingMonthRanges = GetDateRanges(DateStyle.MonthRange, paramName);

        paramName = "Book";
        if (HasParam(paramName))
        {
            await FixInvalidFilterParams(paramName);
            valParams.BookIds = GetIdList(paramName);
        }

        paramName = "Broker";
        if (HasParam(paramName))
        {
            await FixInvalidFilterParams(paramName);
            valParams.BrokerIds = GetIdList(paramName);
        }

        paramName = "Broker Account";
        if (HasParam(paramName))
        {
            await FixInvalidFilterParams(paramName);
            valParams.BrokerAccountIds = GetIdList(paramName);
        }

        paramName = "Buy/Sell";
        if (HasParam(paramName))
            valParams.BuySell = GetBuySellValue(paramName);

        paramName = "Counterparty";
        if (HasParam(paramName))
        {
            await FixInvalidFilterParams(paramName);
            valParams.CounterpartyIds = GetIdList(paramName);
        }

        paramName = "Deal Purpose";
        if (HasParam(paramName))
        {
            await FixInvalidFilterParams(paramName);
            valParams.DealPurposeIds = GetIdList(paramName);
        }

        paramName = "Deal Type";
        if (HasParam(paramName))
        {
            await FixInvalidFilterParams(paramName);
            valParams.DealTypeIds = GetIdList(paramName);
        }

        paramName = "Internal Entity";
        if (HasParam(paramName))
        {
            await FixInvalidFilterParams(paramName);
            valParams.InternalEntityIds = GetIdList(paramName);
        }

        paramName = "Pipeline";
        if (HasParam(paramName))
        {
            await FixInvalidFilterParams(paramName);
            valParams.PipelineIds = GetIdList(paramName);
        }

        paramName = "Point/Hub";
        if (HasParam(paramName))
        {
            await FixInvalidFilterParams(paramName);
            valParams.PointIds = GetIdList(paramName);
        }

        paramName = "Portfolio";
        if (HasParam(paramName))
        {
            await FixInvalidFilterParams(paramName);
            valParams.PortfolioIds = GetIdList(paramName);
        }

        paramName = "Price Index";
        if (HasParam(paramName))
        {
            await FixInvalidFilterParams(paramName);
            valParams.PriceIndexIds = GetIdList(paramName);
        }

        paramName = "Product";
        if (HasParam(paramName))
        {
            await FixInvalidFilterParams(paramName);
            valParams.ProductIds = GetIdList(paramName);
        }

        paramName = "Region";
        if (HasParam(paramName))
        {
            await FixInvalidFilterParams(paramName);
            valParams.RegionIds = GetIdList(paramName);
        }

        paramName = "Strategy";
        if (HasParam(paramName))
        {
            await FixInvalidFilterParams(paramName);
            valParams.StrategyIds = GetIdList(paramName);
        }

        paramName = "Trade Date";
        if (HasParam(paramName))
            valParams.TradeDateRanges = GetDateRanges(DateStyle.DateRange, paramName);

        paramName = "Trader";
        if (HasParam(paramName))
        {
            await FixInvalidFilterParams(paramName);
            valParams.TraderIds = GetIdList(paramName);
        }

        paramName = "Transaction Type";
        if (HasParam(paramName))
        {
            await FixInvalidFilterParams(paramName);
            valParams.TransactionTypeIds = GetIdList(paramName);
        }

        return valParams;
    }

    protected async Task FixInvalidFilterParams(string paramName)
    {
        var hasChanges = false;

        var invalidDataFilterParams = await db.ReportFilterParameters
            .Where(x => x.Name == paramName &&
                   ((x.Value1 != null && x.Value1.Contains("NaN")) ||
                    (x.Preview != null && x.Preview.Contains("Not Found"))))
            .ToListAsync();

        if (invalidDataFilterParams.Count > 0)
        {
            db.ReportFilterParameters.RemoveRange(invalidDataFilterParams);
            Debug.WriteLine($"Removed {invalidDataFilterParams.Count} invalid filter parameters for {paramName}");
            hasChanges = true;
        }

        HashSet<int>? allIds = paramName switch
        {
            "Book" => await db.Books.Select(x => x.Id).ToHashSetAsync(),
            "Broker" => await db.Brokers.Select(x => x.Id).ToHashSetAsync(),
            "Broker Account" => await db.BrokerAccounts.Select(x => x.Id).ToHashSetAsync(),
            "Collateral Product" => await db.ProductCategories.Select(x => x.Id).ToHashSetAsync(),
            "Counterparty" => await db.Counterparties.Select(x => x.Id).ToHashSetAsync(),
            "Deal Purpose" => await db.DealPurposeTypes.Select(x => x.Id).ToHashSetAsync(),
            "Deal Status" => await db.DealStatuses.Select(x => x.Id).ToHashSetAsync(),
            "Deal Type" => await db.PhysicalDealTypes.Select(x => x.Id).ToHashSetAsync(),
            "Internal Entity" => await db.Counterparties.Select(x => x.Id).ToHashSetAsync(),
            "Lease" => await db.Leases.Select(x => x.Id).ToHashSetAsync(),
            "Meter" => await db.Meters.Select(x => x.Id).ToHashSetAsync(),
            "Pipeline" or "Market Pipeline" => await db.Pipelines.Select(x => x.Id).ToHashSetAsync(),
            "Plant" => await db.Plants.Select(x => x.Id).ToHashSetAsync(),
            "Point/Hub" => await db.Points.Select(x => x.Id).ToHashSetAsync(),
            "Portfolio" => await db.Portfolios.Select(x => x.Id).ToHashSetAsync(),
            "Price Index" => await db.MarketIndices.Select(x => x.Id).ToHashSetAsync(),
            "Producer" => await db.Counterparties.Select(x => x.Id).ToHashSetAsync(),
            "Product" => await db.Products.Select(x => x.Id).ToHashSetAsync(),
            "Relationship" => await db.CounterpartyRelationships.Select(x => x.Id).ToHashSetAsync(),
            "Region" => await db.Regions.Select(x => x.Id).ToHashSetAsync(),
            "Strategy" => await db.Strategies.Select(x => x.Id).ToHashSetAsync(),
            "Trader" => await db.AppUsers.Select(x => x.Id).ToHashSetAsync(),
            "Transaction Type" => await db.TransactionTypes.Select(x => x.Id).ToHashSetAsync(),
            _ => throw new ArgumentOutOfRangeException(paramName, $"`{paramName}` filter is not supported."),
        };

        var savedIdStrings = await db.ReportFilterParameters.Where(x => x.Name == paramName).Select(x => x.Value1).Distinct().ToListAsync();

        // Parse IDs safely - skip any that can't be parsed (should be cleaned up above, but this is extra safety)
        var savedIds = savedIdStrings
            .SelectMany(x => x.Split(',', StringSplitOptions.TrimEntries))
            .Where(x => int.TryParse(x, out _)) // Only keep parseable values
            .Select(x => int.Parse(x))
            .Distinct()
            .ToList();

        if (allIds == null || allIds.Count == 0 || savedIds.Count == 0)
        {
            if (hasChanges)
            {
                await db.SaveChangesAsync();
                filterParamsDic = GetNewFilterParamsDic();
            }
            return;
        }

        foreach (var savedId in savedIds)
        {
            if (allIds.Contains(savedId))
                continue;

            //if savedId is not in allIds, then remove it by updating the comma separated list in Value1 of FilterParameter,
            // to a new Value1 without the invalid savedId
            var possibleInvalidFilterParams = await db.ReportFilterParameters
                .Where(x => x.Name == paramName && x.Value1.Contains(savedId.ToString())).ToListAsync();

            var invalidFilterParams = possibleInvalidFilterParams
                .Where(x => x.Value1.Split(',', StringSplitOptions.TrimEntries).Select(x => int.Parse(x)).Contains(savedId));

            foreach (var invalidFilterParameter in invalidFilterParams)
            {
                var newValue1 = string.Join(",", invalidFilterParameter.Value1.Split(',', StringSplitOptions.TrimEntries)
                    .Select(x => int.Parse(x)).Where(x => x != savedId).ToList());

                if (string.IsNullOrEmpty(newValue1))
                    db.ReportFilterParameters.Remove(invalidFilterParameter);
                else
                {
                    invalidFilterParameter.Value1 = newValue1;
                    invalidFilterParameter.Preview = "Filter needs re-save";
                }
                hasChanges = true;
            }
        }

        await db.SaveChangesAsync();
        if (hasChanges)
            filterParamsDic = GetNewFilterParamsDic();
    }
}
