using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using Fast.Models;

namespace Fast.Web.Logic.ReportSources;

class CreditLimits(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 HashSet<int> counterpartyIdFilterHashset = [];

    public override async Task<List<ReportFilterParameter>> Run()
    {
        var query = db.VwRptCreditLimits.AsQueryable();

        query = ApplyDateRangeFilters(query);
        query = await ApplyCounterpartyFilter(query);
        query = await ApplyCollateralProductFilter(query);

        var results = await query.ToListAsync() ?? new List<VwRptCreditLimit>();
        var recentResults = GetRecentResults(results);

        FillSheet("Credit Limit", results);
        FillSheet("Credit Limit Recent", recentResults);

        return GetNewFilterParams();
    }

    private IQueryable<VwRptCreditLimit> ApplyDateRangeFilters(IQueryable<VwRptCreditLimit> query)
    {
        query = ApplyDateRangeFilter(query, "Approved Date", "ApprovedDate");
        query = ApplyDateRangeFilter(query, "Reviewed Date", "ReviewedDate");
        query = ApplyDateRangeFilter(query, "Expiration Date", "ExpirationDate");
        query = ApplyDateRangeFilter(query, "Collateral Effective Date", "CollateralEffectiveDate");
        query = ApplyDateRangeFilter(query, "Collateral Expiration Date", "CollateralExpirationDate");

        return query;
    }

    private async Task<IQueryable<VwRptCreditLimit>> ApplyCounterpartyFilter(IQueryable<VwRptCreditLimit> query)
    {
        var filterName = "Counterparty";
        if (!HasParam(filterName))
            return query;

        await FixInvalidFilterParams(filterName);
        var counterpartyIds = GetIdList(filterName);
        counterpartyIdFilterHashset = counterpartyIds.ToHashSet();

        return query.Where(x => counterpartyIds.Any(id =>
            id == x.ParentCounterpartyId ||
            id == x.NaturalGasCounterpartyId ||
            id == x.NaturalGasCounterpartyId2 ||
            id == x.NglsCounterpartyId ||
            id == x.NglsCounterpartyId2 ||
            id == x.CrudeOilCounterpartyId ||
            id == x.CrudeOilCounterpartyId2)
        );
    }

    private async Task<IQueryable<VwRptCreditLimit>> ApplyCollateralProductFilter(IQueryable<VwRptCreditLimit> query)
    {
        var filterName = "Collateral Product";
        if (!HasParam(filterName))
            return query;

        await FixInvalidFilterParams(filterName);
        var productIds = GetIdList(filterName);

        return query.Where(x => x.ProductCategoryId.HasValue && productIds.Contains(x.ProductCategoryId.Value));
    }


    private IQueryable<VwRptCreditLimit> ApplyDateRangeFilter(IQueryable<VwRptCreditLimit> query, string parameterName, string columnName)
    {
        if (!HasParam(parameterName))
            return query;

        var expression = GetDateRangeExpression<VwRptCreditLimit>(parameterName, columnName, false);
        return query.Where(expression);
    }

    private List<RptCreditLimitRecent> GetRecentResults(List<VwRptCreditLimit> results)
    {
        if (results.Count == 0)
            return new List<RptCreditLimitRecent>();

        var groupedResults = results.GroupBy(x => x.ParentCounterparty).ToList();
        var recentResults = new List<RptCreditLimitRecent>();

        foreach (var parentResults in groupedResults)
        {
            RptCreditLimitRecent? newRecent;
            newRecent = GetNewRecent(parentResults, (int)Enums.ProductCategory.NaturalGasAndLng, 1);
            if (newRecent != null)
                recentResults.Add(newRecent);

            newRecent = GetNewRecent(parentResults, (int)Enums.ProductCategory.NaturalGasAndLng, 2);
            if (newRecent != null)
                recentResults.Add(newRecent);

            newRecent = GetNewRecent(parentResults, (int)Enums.ProductCategory.CrudeOil, 1);
            if (newRecent != null)
                recentResults.Add(newRecent);

            newRecent = GetNewRecent(parentResults, (int)Enums.ProductCategory.CrudeOil, 2);
            if (newRecent != null)
                recentResults.Add(newRecent);

            newRecent = GetNewRecent(parentResults, (int)Enums.ProductCategory.NGLs, 1);
            if (newRecent != null)
                recentResults.Add(newRecent);

            newRecent = GetNewRecent(parentResults, (int)Enums.ProductCategory.NGLs, 2);
            if (newRecent != null)
                recentResults.Add(newRecent);
        }

        recentResults = recentResults
            .OrderByDescending(x => x.ProductGroup)
            .ThenByDescending(x => x.ParentCounterparty)
            .ThenByDescending(x => x.ExpirationDate)
            .ThenByDescending(x => x.ReviewedDate)
            .ThenByDescending(x => x.ApprovedDate)
            .ThenByDescending(x => x.CollateralExpirationDate)
            .ThenByDescending(x => x.CollateralExpirationNotice)
            .ToList();

        return recentResults;
    }

    private RptCreditLimitRecent? GetNewRecent(IGrouping<string?, VwRptCreditLimit> parentResults, int productCategoryId, int counterpartyNum)
    {
        var mostRecentMainItems = GetMostRecentMainItems(parentResults, productCategoryId);
        var unexpiredCollaterals = GetUnexpiredCollaterals(parentResults);

        var mainItem = mostRecentMainItems.FirstOrDefault() ?? GetFallbackItem(parentResults);
        if (mainItem == null)
            return null;

        var productGroup = GetProductGroupName(productCategoryId);
        var collateralSummary = GetCollateralSummary(unexpiredCollaterals);
        var recent = GetRecentItem(mainItem, productGroup, collateralSummary);
        recent = GetRecentWithCounterpartyData(recent, mainItem, productCategoryId, counterpartyNum);

        return recent;
    }

    private static List<VwRptCreditLimit> GetMostRecentMainItems(IGrouping<string?, VwRptCreditLimit> parentResults, int productCategoryId)
    {
        return parentResults
            .Where(x => x.ProductCategoryId == productCategoryId)
            .OrderByDescending(x => x.ExpirationDate)
            .ThenByDescending(x => x.ReviewedDate)
            .ThenByDescending(x => x.ApprovedDate)
            .Take(1)
            .ToList();
    }

    private static List<VwRptCreditLimit> GetUnexpiredCollaterals(IGrouping<string?, VwRptCreditLimit> parentResults)
    {
        var currentDate = DateOnly.FromDateTime(DateTime.Now);
        var results = parentResults
            .Where(x => x.CollateralExpirationDate >= currentDate || x.CollateralExpirationNotice != null)
            .OrderByDescending(x => x.CollateralEffectiveDate)
            .ThenByDescending(x => x.CollateralExpirationDate ?? DateOnly.MinValue)
            .ToList();

        return results;
    }

    private class CollateralSummary
    {
        public decimal TotalAmount = 0;
        public string? Types = null;
        public DateOnly? ExpirationDate = null;
        public string? Notice = null;
    }

    private static CollateralSummary GetCollateralSummary(List<VwRptCreditLimit> collaterals)
    {
        var summary = new CollateralSummary();

        if (collaterals.Count == 0)
            return summary;

        summary.TotalAmount = collaterals.Sum(c => c.CollateralAmount ?? 0);
        summary.Types = string.Join(" • ", collaterals.Select(c => c.CollateralType).Distinct());

        var earliestExpirationDate = collaterals.Where(c => c.CollateralExpirationDate.HasValue)
            .Min(c => c.CollateralExpirationDate);

        if (earliestExpirationDate.HasValue)
            summary.ExpirationDate = earliestExpirationDate;
        else
        {
            var notices = collaterals.Select(c => c.CollateralExpirationNotice).Where(n => !string.IsNullOrWhiteSpace(n)).Distinct().ToList();
            var notice = notices.Count > 1 ? "Multiple" : notices.FirstOrDefault() ?? null;
            summary.Notice = notice;
        }

        return summary;
    }

    private static VwRptCreditLimit? GetFallbackItem(IGrouping<string?, VwRptCreditLimit> parentResults)
    {
        var item = parentResults
            .OrderByDescending(x => x.ExpirationDate)
            .ThenByDescending(x => x.ReviewedDate)
            .ThenByDescending(x => x.ApprovedDate)
            .FirstOrDefault();

        if (item != null)
            return GetItemWithoutProduct(item);
        else
            return item;
    }

    private static VwRptCreditLimit GetItemWithoutProduct(VwRptCreditLimit item)
    {
        //clone the item to avoid modifying the original
        var newItem = item.Copy()!;

        newItem.ProductCategoryId = null;
        newItem.CollateralAmount = null;
        newItem.CollateralType = null;
        newItem.CollateralExpirationDate = null;
        newItem.CollateralExpirationNotice = null;

        return newItem;
    }

    private static RptCreditLimitRecent GetRecentItem(VwRptCreditLimit mainItem, string? productGroup, CollateralSummary collateralSummary)
    {
        return new RptCreditLimitRecent
        {
            ApprovedDate = mainItem.ApprovedDate!.Value,
            ExpirationDate = mainItem.ExpirationDate,
            ReviewedDate = mainItem.ReviewedDate,
            ParentCounterparty = mainItem.ParentCounterparty,
            TotalCreditLimit = mainItem.TotalCreditLimit,
            ApprovedCreditLimit = mainItem.ApprovedCreditLimit!.Value,
            OverLimitException = mainItem.OverLimitException!.Value,
            ExceptionAmount = mainItem.ExceptionAmount,
            ExceptionExpiration = mainItem.ExceptionExpiration,
            ProductGroup = productGroup,
            CollateralAmount = collateralSummary.TotalAmount,
            CollateralType = collateralSummary.Types,
            CollateralExpirationDate = collateralSummary.ExpirationDate,
            CollateralExpirationNotice = collateralSummary.Notice
        };
    }

    private RptCreditLimitRecent? GetRecentWithCounterpartyData(RptCreditLimitRecent recent, VwRptCreditLimit item, int productCategoryId, int counterpartyNum)
    {
        var isNatGas = productCategoryId == (int)Enums.ProductCategory.NaturalGasAndLng;
        var isCrude = productCategoryId == (int)Enums.ProductCategory.CrudeOil;
        var isNgl = productCategoryId == (int)Enums.ProductCategory.NGLs;

        if (isNatGas && counterpartyNum == 1)
            return GetWithCounterparty(recent, item.ParentCounterpartyId, item.NatGasCreditLimit1, item.NaturalGasCounterpartyId, item.NatGasCounterparty1);

        if (isNatGas && counterpartyNum == 2)
            return GetWithCounterparty(recent, item.ParentCounterpartyId, item.NatGasCreditLimit2, item.NaturalGasCounterpartyId2, item.NatGasCounterparty2);

        if (isCrude && counterpartyNum == 1)
            return GetWithCounterparty(recent, item.ParentCounterpartyId, item.CrudeCreditLimit1, item.CrudeOilCounterpartyId, item.CrudeCounterparty1);

        if (isCrude && counterpartyNum == 2)
            return GetWithCounterparty(recent, item.ParentCounterpartyId, item.CrudeCreditLimit2, item.CrudeOilCounterpartyId2, item.CrudeCounterparty2);

        if (isNgl && counterpartyNum == 1)
            return GetWithCounterparty(recent, item.ParentCounterpartyId, item.NglsCreditLimit1, item.NglsCounterpartyId, item.NglsCounterparty1);

        if (isNgl && counterpartyNum == 2)
            return GetWithCounterparty(recent, item.ParentCounterpartyId, item.NglsCreditLimit2, item.NglsCounterpartyId2, item.NglsCounterparty2);

        return null;
    }

    private RptCreditLimitRecent? GetWithCounterparty(RptCreditLimitRecent recent, int? parentCounterpartyId, decimal? creditLimit, int? counterpartyId, string? counterparty)
    {
        if (!HasValidLimit(creditLimit, counterpartyId, parentCounterpartyId))
            return null;

        recent.Counterparty = counterparty;
        recent.CreditLimit = creditLimit;
        return recent;
    }

    private bool HasValidLimit(decimal? limit, int? counterpartyId, int? parentCounterpartyId)
    {
        if (!limit.HasValue)
            return false;

        // if there is no counterparty filter, then assume all are valid
        if (counterpartyIdFilterHashset.Count == 0)
            return true;

        return counterpartyIdFilterHashset.Contains(parentCounterpartyId.GetValueOrDefault()) ||
               counterpartyIdFilterHashset.Contains(counterpartyId.GetValueOrDefault());
    }

    private static string GetProductGroupName(int productGroupId)
    {
        var groupNames = new Dictionary<int, string>() {
                { 1, "Natural Gas"},
                { 2, "Crude Oil"},
                { 3, "NGLs" }
            };

        return groupNames[productGroupId];
    }
}
