using System.Collections.Concurrent;
using System.Globalization;
using System.Text.RegularExpressions;
using Fast.Shared.Logic.ValuationCommon;
using Flee.PublicTypes;
using static Fast.Shared.Models.Enums;
using Pairs = Fast.Shared.Logic.ValuationCommon;

namespace Fast.Shared.Logic.Valuation;

public partial class Pricer
{
    private enum IndexType
    {
        Forward = 1,
        Daily = 2,
        Monthly = 3
    }

    private const string bulletSymbol = "\u2022";

    public class IndexItem
    {
        public string Name { get; set; } = "";
        public bool IsMonthly { get; set; }
        public int MappedIndexId { get; set; }
        public string HybridIndexDefinition { get; set; } = "";
        public bool IsHybrid { get; set; }
        public int ProductId { get; set; }
    }

    private class BusinessDayNums
    {
        public int BD1 { get; set; } //business days from the first day of the month through expiration
        public int BD2 { get; set; } //business days after expiration through the end of the month
        public int BDT { get; set; } //business days from the first day of the month to the last day of the month
    }

    private readonly DateOnly asOfDate;
    public int? UnitPriceId;
    private readonly double? missingValue;
    private readonly bool includeIndexDetails;
    private Dictionary<int, int?> indexPriceUnits = new();
    private Dictionary<Pairs.IntegerDatePair, BusinessDayNums> ExpirationBusinessDayCounts = new();
    private readonly MyLazy<Dictionary<int, Dictionary<DateOnly, double>>> dailyPricesByIndex;
    private readonly MyLazy<Dictionary<int, Dictionary<DateOnly, double>>> monthlyPricesByIndex;
    private readonly ExpressionContext expressionContext;
    private readonly Dictionary<Pairs.IndexWithSuffixKey, PriceDetail> IndexWithSuffixPriceDetails = new();
    public Dictionary<int, IndexItem> Indexes = new();
    public Dictionary<Pairs.IndexDatePair, Pairs.DoubleDatePair> ForwardPrices { get; set; } = new();
    public Dictionary<Pairs.IndexDatePair, double> DailyPrices { get; set; } = new();
    public Dictionary<Pairs.IndexDatePair, double> MonthlyPrices { get; set; } = new();
    public Dictionary<int, int> FuturesDefaultIndexes { get; set; } = new();
    public UnitConverter UnitConverter = new();
    public ConcurrentDictionary<ValPriceKey, PricerResult> PriceResults { get; set; } = new();
    private ConcurrentDictionary<ValPriceEquationKey, PricerResult> PriceEquationResults { get; set; } = new();
    private readonly int unitOfMeasureId;
    private readonly int currencyId;
    private readonly List<DateRange> positionDateRanges;
    private readonly HashSet<string> validSuffixFirstParts = new() { "CM", "PM", "2M", "3M", "AG" };
    private readonly HashSet<string> validSuffixSecondParts = new() { "AD", "TD" };

    public Pricer(DateOnly asOfDate, List<DateRange> positionDateRanges, int unitOfMeasureId, int currencyId, double? missingValue = FastValues.MissingPrice, bool includeIndexDetails = false)
    {
        ArgumentOutOfRangeException.ThrowIfEqual(asOfDate, DateOnly.MinValue);

        dailyPricesByIndex = new MyLazy<Dictionary<int, Dictionary<DateOnly, double>>>(GetDailyPricesByIndex);
        monthlyPricesByIndex = new MyLazy<Dictionary<int, Dictionary<DateOnly, double>>>(GetMonthlyPricesByIndex);

        expressionContext = new();
        expressionContext.Imports.AddType(typeof(Math));
        expressionContext.Options.ParseCulture = new CultureInfo("en-US");
        expressionContext.Options.IntegersAsDoubles = true;
        expressionContext.ParserOptions.RecreateParser();

        this.includeIndexDetails = includeIndexDetails;
        this.asOfDate = asOfDate;
        this.missingValue = missingValue;

        this.unitOfMeasureId = unitOfMeasureId;
        this.currencyId = currencyId;
        this.positionDateRanges = positionDateRanges;
    }

    public async Task Load()
    {
        List<Task> tasks = new()
        {
            Task.Run(() => UnitConverter = new UnitConverter()),
            Task.Run(() => SetUnitPriceId(unitOfMeasureId, currencyId)),
            Task.Run(() => LoadForwardPrices()),
            Task.Run(() => LoadDailyPrices(positionDateRanges)),
            Task.Run(() => LoadMonthlyPrices(positionDateRanges)),
            Task.Run(() => LoadIndexes()),
            Task.Run(() => LoadFuturesDefaultIndexes()),
            Task.Run(() => LoadExpirationBusinessDayCounts())
        };
        await Task.WhenAll(tasks);
    }

    private void SetUnitPriceId(int unitOfMeasureId, int currencyId)
    {
        using var db = Main.CreateContext();
        var priceId = db.UnitPriceCombinations.Where(x => x.UnitOfMeasureId == unitOfMeasureId && x.CurrencyTypeId == currencyId).Select(x => x.Id).FirstOrDefault();
        UnitPriceId = priceId == 0 ? null : priceId;
    }

    private class ExpirationBusinessDayCount
    {
        public int ProductID { get; set; }
        public DateOnly ContractMonth { get; set; }
        public int BD1 { get; set; }
        public int BD2 { get; set; }
        public int BDT { get; set; }
    }

    private void LoadExpirationBusinessDayCounts()
    {
        // Raw SQL queries are not supported by In-Memory provider
        if (ProgramShared.IsLocalModeEnabled)
        {
            ExpirationBusinessDayCounts = GenerateLocalModeBusinessDayCounts();
            return;
        }

        using var db = Main.CreateContext();
        FormattableString sql = @$"
            with Expirations as (
                select
                    product_id,
                    contract_month,
                    expiration_date,
                    expiration_date + INTERVAL '1 day' AS ContractMonthPlusOneDay,
                    contract_month + INTERVAL '1 month' AS ContractMonthPlusOneMonth,
                    expiration_date + INTERVAL '1 day' AS ExpirationDatePlusOneDay,
                    (date_trunc('month', contract_month) + INTERVAL '1 month' - INTERVAL '1 day')::date AS ContractMonthEnd
                from Product_Expiration
                where benchmark_id in (1,2)
            )
            select
                e1.product_id as ProductID,
                e1.contract_month as ContractMonth,
                (select count(*) from vw_business_calendar where date >= e1.contract_month and date <= e2.expiration_date) as BD1,
                (select count(*) from vw_business_calendar where date >= e2.ExpirationDatePlusOneDay and date <= e1.ContractMonthEnd) as BD2,
                (select count(*) from vw_business_calendar where date >= e1.contract_month and date <= e1.ContractMonthEnd) as BDT
            from Expirations e1
            join Expirations e2 on e1.ContractMonthPlusOneMonth = e2.contract_month
            and e1.product_id = e2.product_id
        ";
        var results = db.Database.SqlQuery<ExpirationBusinessDayCount>(sql).ToList();
        ExpirationBusinessDayCounts = results.ToDictionary(x => new Pairs.IntegerDatePair(x.ProductID, x.ContractMonth), x => new BusinessDayNums { BD1 = x.BD1, BD2 = x.BD2, BDT = x.BDT });
    }

    private static Dictionary<Pairs.IntegerDatePair, BusinessDayNums> GenerateLocalModeBusinessDayCounts()
    {
        var result = new Dictionary<Pairs.IntegerDatePair, BusinessDayNums>();
        var today = DateTime.Today;
        var productIds = new[] { 1, 2, 11 }; // Natural Gas, Crude Oil, Physical Crude

        // Generate business day counts for current month and next 12 months
        for (int monthOffset = 0; monthOffset <= 12; monthOffset++)
        {
            var contractMonth = new DateOnly(today.Year, today.Month, 1).AddMonths(monthOffset);
            foreach (var productId in productIds)
            {
                var key = new Pairs.IntegerDatePair(productId, contractMonth);
                // Typical business day distribution: ~15 days before expiration, ~7 after, ~22 total
                result[key] = new BusinessDayNums { BD1 = 15, BD2 = 7, BDT = 22 };
            }
        }
        return result;
    }

    private static double GetLocalModeSamplePrice(int productId)
    {
        // Return realistic sample prices for different products
        return productId switch
        {
            (int)Enums.Product.NaturalGas => 3.50,      // $/MMBtu
            (int)Enums.Product.CrudeOil => 75.00,       // $/barrel
            (int)Enums.Product.Condensate => 70.00,    // $/barrel
            (int)Enums.Product.Retrograde => 68.00,    // $/barrel
            _ => 50.00                                   // Default price
        };
    }

    private void LoadFuturesDefaultIndexes()
    {
        using var db = Main.CreateContext();
        var futuresDefaultIndexesQuery = db.MarketIndices.Where(x => x.IsFutureDefault == true).Select(x => new { x.Id, x.ProductId }).ToList();
        FuturesDefaultIndexes = futuresDefaultIndexesQuery.ToDictionary(n => n.ProductId, n => n.Id);
    }

    private Dictionary<int, Dictionary<DateOnly, double>> GetDailyPricesByIndex()
    {
        return ConvertPriceDictionary(DailyPrices);
    }

    private Dictionary<int, Dictionary<DateOnly, double>> GetMonthlyPricesByIndex()
    {
        return ConvertPriceDictionary(MonthlyPrices);
    }

    private static Dictionary<int, Dictionary<DateOnly, double>> ConvertPriceDictionary(Dictionary<Pairs.IndexDatePair, double> oldDict)
    {
        oldDict ??= new Dictionary<Pairs.IndexDatePair, double>();

        var distinctIndexIds = (from q in oldDict.Keys
                                select q.IndexId).Distinct().ToArray();

        var newDict = (
            from indexId in distinctIndexIds
            let prices = (from q in oldDict where q.Key.IndexId == indexId select new { priceDate = q.Key.PriceDate, price = q.Value }).ToDictionary(x => x.priceDate, x => x.price)
            select new { indexId, prices }
        ).ToDictionary(x => x.indexId, x => x.prices);

        return newDict;
    }

    private void LoadForwardPrices()
    {
        using var db = Main.CreateContext();
        //look 3 months backwards from the minimum position date
        //this should be enough to get the latest forward prices
        var minPositionDate = positionDateRanges.Min(x => x.FromDate).AddMonths(-3);
        int forwardTypeId = Convert.ToInt32(IndexType.Forward);

        var maxDatesQuery =
            from q in db.MarketPrices
            where q.PriceDate <= asOfDate && q.MarketTypeId == forwardTypeId && q.PriceDate >= minPositionDate
            group q by new { q.IndexId, q.ContractMonth } into g
            select new
            {
                g.Key.IndexId,
                g.Key.ContractMonth,
                MaxPriceDate = g.Max(x => x.PriceDate)
            };

        var latestPricesQuery =
            from q in db.MarketPrices
            join d in maxDatesQuery
                on new { q.IndexId, q.ContractMonth, q.PriceDate }
                equals new { d.IndexId, d.ContractMonth, PriceDate = d.MaxPriceDate }
            where q.MarketTypeId == forwardTypeId
            select new
            {
                q.IndexId,
                q.ContractMonth,
                q.PriceDate,
                q.Price
            };

        ForwardPrices = latestPricesQuery
            .AsNoTracking()
            .AsEnumerable()
            .ToDictionary(
                q => new Pairs.IndexDatePair(q.IndexId.GetValueOrDefault(), q.ContractMonth),
                q => new Pairs.DoubleDatePair(q.Price, q.PriceDate),
                new Pairs.IndexDateComparer()
            );
    }

    private class PriceItem
    {
        public int? IndexId;
        public DateOnly PriceDate;
        public DateOnly ContractMonth;
        public double Price;
    }

    private void LoadDailyPrices(List<DateRange> positionDateRanges)
    {
        using var db = Main.CreateContext();
        var minPositionDate = positionDateRanges.Min(x => x.FromDate);
        var maxPositionDate = positionDateRanges.Max(x => x.ToDate);
        int dailyTypeId = Convert.ToInt32(IndexType.Daily);

        var dailyIndexesBetweenPositionDates = (
            from q in db.MarketPrices
            where q.PriceDate <= asOfDate && q.MarketTypeId == dailyTypeId && q.PriceDate >= minPositionDate && q.PriceDate <= maxPositionDate
            select new PriceItem
            {
                IndexId = q.IndexId,
                PriceDate = q.PriceDate,
                ContractMonth = q.ContractMonth,
                Price = q.Price
            }
        ).AsNoTracking().ToList();

        //get max dates for each index that are before the minimum of the filter parameter dates
        //this is so that we have at least one of the most recent index prices for each index
        var maxDates = (
            from q in db.MarketPrices
            where q.PriceDate <= asOfDate && q.MarketTypeId == dailyTypeId && q.PriceDate < minPositionDate
            group q by new { q.IndexId } into g
            select new { g.Key.IndexId, MaxPriceDate = g.Max(x => x.PriceDate) });

        var mostRecentDailyIndexesPriorToPositionDates = (
            from q in db.MarketPrices
            where q.PriceDate <= asOfDate && q.MarketTypeId == dailyTypeId && q.PriceDate < minPositionDate
            join d in maxDates on new { q.IndexId, q.PriceDate } equals new { d.IndexId, PriceDate = d.MaxPriceDate }
            select new PriceItem
            {
                IndexId = q.IndexId,
                PriceDate = q.PriceDate,
                ContractMonth = q.ContractMonth,
                Price = q.Price
            }
        ).AsNoTracking();

        var daily = dailyIndexesBetweenPositionDates.Concat(mostRecentDailyIndexesPriorToPositionDates).AsEnumerable();

        //for some indexes (E.G. Crude Oil Indexes), we have multiple Contract Months for the same Price Date
        //for these we get the price/date pair with the minimum contract months
        //jlupo: review this logic when we start running Crude position reports since it may be incorrect
        var minContractMonths = (
            from q in daily
            group q by new { q.IndexId, q.PriceDate } into g
            select new { g.Key.IndexId, g.Key.PriceDate, MinContractMonth = g.Min(x => x.ContractMonth) });

        var query = (
                from q in daily
                join d in minContractMonths on new { q.IndexId, q.PriceDate, q.ContractMonth } equals new { d.IndexId, d.PriceDate, ContractMonth = d.MinContractMonth }
                select q
            ).AsEnumerable();

        DailyPrices = (
            from q in query
            select new
            {
                key = new Pairs.IndexDatePair(q.IndexId.GetValueOrDefault(), q.PriceDate),
                value = q.Price
            }
        ).ToDictionary(x => x.key, x => x.value, new Pairs.IndexDateComparer());
    }

    private void LoadMonthlyPrices(List<DateRange> positionDateRanges)
    {
        using var db = Main.CreateContext();
        var minPositionMonth = Util.Date.FirstDayOfMonth(positionDateRanges.Min(x => x.FromDate));
        var maxPositionMonth = Util.Date.LastDayOfMonth(positionDateRanges.Max(x => x.ToDate));
        int monthlyTypeId = Convert.ToInt32(IndexType.Monthly);

        var monthlyIndexesBetweenPositionMonths = (
            from q in db.MarketPrices
            where q.PublishDate <= asOfDate && q.MarketTypeId == monthlyTypeId && q.PriceDate >= minPositionMonth && q.PriceDate <= maxPositionMonth
            select new PriceItem
            {
                IndexId = q.IndexId,
                PriceDate = q.PriceDate,
                ContractMonth = q.ContractMonth,
                Price = q.Price
            }
        ).AsNoTracking().ToList();

        //get max dates for each index that are before the minimum of the filter parameter dates
        //this is so that we have at least one of the most recent index prices for each index
        var maxDates = (
            from q in db.MarketPrices
            where q.PublishDate <= asOfDate && q.MarketTypeId == monthlyTypeId && q.PriceDate < minPositionMonth
            group q by new { q.IndexId } into g
            select new { g.Key.IndexId, MaxPriceDate = g.Max(x => x.PriceDate) }).AsNoTracking();

        var mostRecentMonthlyIndexesPriorToPositionDates = (
            from q in db.MarketPrices
            where q.PublishDate <= asOfDate && q.MarketTypeId == monthlyTypeId && q.PriceDate < minPositionMonth
            join d in maxDates on new { q.IndexId, q.PriceDate } equals new { d.IndexId, PriceDate = d.MaxPriceDate }
            select new PriceItem
            {
                IndexId = q.IndexId,
                PriceDate = q.PriceDate,
                ContractMonth = q.ContractMonth,
                Price = q.Price
            }
        ).AsNoTracking();

        var monthly = monthlyIndexesBetweenPositionMonths.Concat(mostRecentMonthlyIndexesPriorToPositionDates).AsEnumerable();

        MonthlyPrices = (
            from q in monthly
            select new
            {
                key = new Pairs.IndexDatePair(q.IndexId.GetValueOrDefault(), q.PriceDate),
                value = q.Price
            }
        ).ToDictionary(x => x.key, x => x.value, new Pairs.IndexDateComparer());
    }

    private void LoadIndexes()
    {
        using var db = Main.CreateContext();
        var indexes = (
            from q in db.MarketIndices
            join ppim in db.PublishedToInternalIndices on q.Id equals ppim.PublishedIndexId into j1
            from ppim in j1.DefaultIfEmpty()
            select new
            {
                q.Id,
                IsMonthly = q.IndexTypeId == (int)Enums.MarketIndexType.Monthly,
                IsHybrid = q.IndexTypeId == (int)Enums.MarketIndexType.Hybrid,
                q.Name,
                q.HybridIndexDefinition,
                MappedIndexId = ppim != null ? ppim.InternalIndexId : q.Id,
                q.UnitPriceId,
                q.ProductId
            }
        ).AsNoTracking().ToList();

        Indexes = indexes.ToDictionary(
            x => x.Id, x => new IndexItem
            {
                IsMonthly = x.IsMonthly,
                MappedIndexId = x.MappedIndexId,
                IsHybrid = x.IsHybrid,
                Name = x.Name,
                HybridIndexDefinition = x.HybridIndexDefinition,
                ProductId = x.ProductId
            }
        );

        indexPriceUnits = indexes.Select(x => new { x.Id, x.UnitPriceId }).ToDictionary(n => n.Id, n => n.UnitPriceId);
    }

    private double? ConvertPrice(int? FromUnitPriceId, double? PriceToConvert)
    {
        if (FromUnitPriceId.HasValue && UnitPriceId.HasValue)
            return UnitConverter.GetPrice(FromUnitPriceId.Value, UnitPriceId.GetValueOrDefault(), PriceToConvert.GetValueOrDefault());
        else
            return PriceToConvert;
    }

    private PriceDetail GetForwardCurvePrice(int indexId, string suffix, DateOnly date)
    {
        PriceDetail retval = new() { PriceConverted = missingValue, PriceOriginal = missingValue, IsPosted = false };

        if (!Indexes.ContainsKey(indexId))
            return retval;

        Pairs.IndexDatePair indexDatePair = new()
        {
            IndexId = indexId,
            PriceDate = Util.Date.FirstDayOfMonth(date)
        };

        if (!string.IsNullOrWhiteSpace(suffix))
            retval = GetIndexWithSuffixPrice(indexId, suffix, date);
        else if (ForwardPrices.TryGetValue(indexDatePair, out var item))
        {
            retval.PriceDate = item.Date;
            retval.PriceOriginal = item.Double;
            retval.PriceConverted = ConvertPrice(indexPriceUnits[indexId], retval.PriceOriginal);
        }
        return retval;
    }

    private PriceDetail GetDailyPrice(int indexId, DateOnly date, bool makeUnitConversion = true)
    {
        PriceDetail retval = new() { PriceConverted = missingValue, PriceOriginal = missingValue };

        if (Indexes.TryGetValue(indexId, out var index) && index.IsMonthly)
            throw new ArgumentException("indexId is not a daily index");
        else
        {
            Pairs.IndexDatePair indexDatePair = new() { IndexId = indexId, PriceDate = date };
            if (DailyPrices.TryGetValue(indexDatePair, out var price))
            {
                retval.PriceDate = date;
                retval.PriceOriginal = price;
                if (makeUnitConversion)
                    retval.PriceConverted = ConvertPrice(indexPriceUnits[indexId], retval.PriceOriginal);
                else
                    retval.PriceConverted = retval.PriceOriginal;
            }
            retval.IsPosted = !IsMissingPrice(retval.PriceConverted);
        }
        return retval;
    }

    private PriceDetail GetMonthlyPrice(int indexId, DateOnly date, bool makeUnitConversion = true)
    {
        PriceDetail retval = new() { PriceConverted = missingValue, PriceOriginal = missingValue };

        if (Indexes.TryGetValue(indexId, out var index) && index.IsMonthly == false)
            throw new ArgumentException("indexId is not a monthly index");
        else
        {
            Pairs.IndexDatePair indexDatePair = new() { IndexId = indexId, PriceDate = Util.Date.FirstDayOfMonth(date) };
            if (MonthlyPrices.TryGetValue(indexDatePair, out var price))
            {
                retval.PriceDate = Util.Date.FirstDayOfMonth(date);
                retval.PriceOriginal = price;
                if (makeUnitConversion)
                    retval.PriceConverted = ConvertPrice(indexPriceUnits[indexId], retval.PriceOriginal);
                else
                    retval.PriceConverted = retval.PriceOriginal;
            }
            retval.IsPosted = !IsMissingPrice(retval.PriceConverted);
        }
        return retval;
    }

    private PriceDetail GetDailyOrMonthlyPrice(bool getFallbackMonthPrice, int indexId, string suffix, DateOnly date, bool makeUnitConversion = true)
    {
        PriceDetail retval = new() { PriceConverted = missingValue, PriceOriginal = missingValue };

        if (!Indexes.ContainsKey(indexId))
            return retval;

        if (!string.IsNullOrWhiteSpace(suffix))
            retval = GetIndexWithSuffixPrice(indexId, suffix, date, makeUnitConversion);
        else if (!Indexes[indexId].IsMonthly)
        {
            retval = GetDailyPrice(indexId, date, makeUnitConversion);
            if (IsMissingPrice(retval.PriceConverted))
                retval = GetMostRecentDailyPrice(indexId, date, makeUnitConversion);
        }
        else
        {
            retval = GetMonthlyPrice(indexId, date, makeUnitConversion);
            if (getFallbackMonthPrice && IsMissingPrice(retval.PriceConverted))
                retval = GetMostRecentMonthlyPrice(indexId, date, makeUnitConversion);
        }
        return retval;
    }

    private PriceDetail GetIndexWithSuffixPrice(int indexId, string suffix, DateOnly date, bool makeUnitConversion = true)
    {
        PriceDetail retval = new() { PriceConverted = missingValue, PriceOriginal = missingValue };
        date = Util.Date.FirstDayOfMonth(date);
        int productId = Indexes[indexId].ProductId;

        Pairs.IndexWithSuffixKey key = new(indexId, suffix, productId, date);
        if (IndexWithSuffixPriceDetails.TryGetValue(key, out var details))
            retval = details;
        else
        {
            var suffixFirstPart = suffix[0..2];
            var suffixSecondPart = suffix[2..];

            if (!IsValidSuffix(suffixFirstPart, suffixSecondPart))
                return retval;

            var priceCalcType = suffixSecondPart == "AD" ? PriceCalcType.AllDays : PriceCalcType.TradeDays;

            if (suffixFirstPart == "CM")
            {
                retval.PriceOriginal = GetCalendarMonthPrice(indexId, productId, date, priceCalcType);
                retval.PriceConverted = makeUnitConversion ? ConvertPrice(indexPriceUnits[indexId], retval.PriceOriginal) : retval.PriceOriginal;
                retval.PriceCm = retval.PriceConverted;
            }
            else if (suffixFirstPart == "PM")
            {
                retval.PriceOriginal = GetPromptMonthPrice(0, indexId, productId, date, priceCalcType);
                retval.PriceConverted = makeUnitConversion ? ConvertPrice(indexPriceUnits[indexId], retval.PriceOriginal) : retval.PriceOriginal;
                retval.PricePm = retval.PriceConverted;
            }
            else if (suffixFirstPart == "2M")
            {
                retval.PriceOriginal = GetPromptMonthPrice(1, indexId, productId, date, priceCalcType);
                retval.PriceConverted = makeUnitConversion ? ConvertPrice(indexPriceUnits[indexId], retval.PriceOriginal) : retval.PriceOriginal;
                retval.Price2m = retval.PriceConverted;
            }
            else if (suffixFirstPart == "3M")
            {
                retval.PriceOriginal = GetPromptMonthPrice(2, indexId, productId, date, priceCalcType);
                retval.PriceConverted = makeUnitConversion ? ConvertPrice(indexPriceUnits[indexId], retval.PriceOriginal) : retval.PriceOriginal;
                retval.Price3m = retval.PriceConverted;
            }
            else if (suffixFirstPart == "AG")
            {
                retval.PriceOriginal = GetArgusPrice(indexId, date, priceCalcType);
                retval.PriceConverted = makeUnitConversion ? ConvertPrice(indexPriceUnits[indexId], retval.PriceOriginal) : retval.PriceOriginal;
                retval.PriceAg = retval.PriceConverted;
            }

            IndexWithSuffixPriceDetails[key] = retval;

            retval.PriceDate = date;
            retval.IsPosted = !IsMissingPrice(retval.PriceConverted);
        }

        return retval;
    }

    private bool IsValidSuffix(string suffixFirstPart, string suffixSecondPart)
    {
        bool isFirstPartValid = validSuffixFirstParts.Contains(suffixFirstPart);
        bool isSecondPartValid = validSuffixSecondParts.Contains(suffixSecondPart);
        return isFirstPartValid && isSecondPartValid;
    }

    //primarily used for Crude indexes with suffixes
    private double GetCalendarMonthPrice(int indexId, int productId, DateOnly date, PriceCalcType priceCalcType)
    {
        // Raw SQL queries are not supported by In-Memory provider
        if (ProgramShared.IsLocalModeEnabled)
            return GetLocalModeSamplePrice(productId);

        using var db = Main.CreateContext();
        DateOnly beginDate = Util.Date.FirstDayOfMonth(date);
        DateOnly endDate = Util.Date.LastDayOfMonth(date);
        DateOnly oneMonthAhead = beginDate.AddMonths(1);
        DateOnly twoMonthsAhead = beginDate.AddMonths(2);
        int useBusinessDays = priceCalcType == PriceCalcType.TradeDays ? 1 : 0;

        FormattableString queryStr = @$"
            select avg(price) from (
                select m.price
                from market_price m
                left join market_index pit on pit.id = m.index_id
                left join product_expiration pe on pe.product_id = {productId}
                    and pe.contract_month = {oneMonthAhead}
                left join vw_business_calendar priceBd on priceBd.date = m.price_date
                where m.index_id = {indexId}
                and pe.benchmark_id in (1,2)
                and ({useBusinessDays} = 0 or priceBd.date is not null)
                and (
                    (m.contract_month = {oneMonthAhead} and m.price_date >= {beginDate} and m.price_date <= pe.expiration_date)
                    or
                    (m.contract_month = {twoMonthsAhead} and m.price_date > pe.expiration_date and m.price_date <= {endDate})
                )
            ) s
        ";

        var price = db.Database.SqlQuery<double?>(queryStr).ToList().FirstOrDefault();
        return price ?? missingValue.GetValueOrDefault();
    }

    //primarily used for Crude indexes with suffixes
    private double GetPromptMonthPrice(int monthShiftNum, int indexId, int productId, DateOnly date, PriceCalcType priceCalcType)
    {
        // Raw SQL queries are not supported by In-Memory provider
        if (ProgramShared.IsLocalModeEnabled)
            return GetLocalModeSamplePrice(productId);

        using var db = Main.CreateContext();
        DateOnly beginDate = Util.Date.FirstDayOfMonth(date);
        DateOnly oneMonthAgo = beginDate.AddMonths(-1);
        DateOnly contractMonth = beginDate.AddMonths(monthShiftNum);
        int useBusinessDays = priceCalcType == PriceCalcType.TradeDays ? 1 : 0;

        FormattableString queryStr = @$"
            select avg(price) from (
                select m.price
                from market_price m
                left join market_index pit on pit.id = m.index_id
                left join product_expiration pe1 on pe1.product_id = {productId}
                    and pe1.contract_month = {oneMonthAgo}
                left join product_expiration pe2 on pe2.product_id = {productId}
                    and pe2.contract_month = {beginDate}
                left join vw_business_calendar priceBd on priceBd.date = m.price_date
        	    where m.index_id = {indexId}
                and pe1.benchmark_id in (1,2)
                and pe2.benchmark_id in (1,2)
                and ({useBusinessDays} = 0 or priceBd.date is not null)
        	    and m.contract_month = {contractMonth} and m.price_date > pe1.expiration_date and m.price_date <= pe2.expiration_date
            ) s
        ";

        //due to odd EF core issues, we sometimes have to use ToList before FirstOrDefault when using SqlQuery with a primitive type
        var price = db.Database.SqlQuery<double?>(queryStr).ToList().FirstOrDefault();
        return price ?? missingValue.GetValueOrDefault();
    }

    //primarily used for Crude indexes with suffixes
    private double GetArgusPrice(int indexId, DateOnly date, PriceCalcType priceCalcType)
    {
        // Raw SQL queries are not supported by In-Memory provider
        if (ProgramShared.IsLocalModeEnabled)
            return GetLocalModeSamplePrice((int)Enums.Product.CrudeOil); // Argus is typically crude pricing

        using var db = Main.CreateContext();
        DateOnly beginDate = Util.Date.FirstDayOfMonth(date);
        DateOnly oneMonthAgo = beginDate.AddMonths(-1);
        DateOnly twoMonthsAgo = beginDate.AddMonths(-2);
        int useBusinessDays = priceCalcType == PriceCalcType.TradeDays ? 1 : 0;

        FormattableString queryStr = @$"
            WITH DateBoundaries AS (
                SELECT
                    make_date(EXTRACT(YEAR FROM {twoMonthsAgo})::int, EXTRACT(MONTH FROM {twoMonthsAgo})::int, 25) AS two_months_ago_25th,
                    make_date(EXTRACT(YEAR FROM {oneMonthAgo})::int, EXTRACT(MONTH FROM {oneMonthAgo})::int, 24) AS one_month_ago_24th
            ), NextBusinessDays AS (
                SELECT
                    -- Find the first business day strictly after the 25th of two months ago
                    (SELECT bc.date
                     FROM vw_business_calendar bc
                     WHERE bc.date > db.two_months_ago_25th
                     ORDER BY bc.date
                     LIMIT 1) AS start_date,
                    -- Find the first business day strictly after the 24th of one month ago
                    (SELECT bc.date
                     FROM vw_business_calendar bc
                     WHERE bc.date > db.one_month_ago_24th
                     ORDER BY bc.date
                     LIMIT 1) AS end_date
                FROM DateBoundaries db
            )
            SELECT avg(m.price)
            FROM market_price m
            LEFT JOIN vw_business_calendar priceBd ON priceBd.date = m.price_date
            CROSS JOIN NextBusinessDays nbd
            WHERE m.index_id = {indexId}
              AND m.contract_month = {beginDate}
              AND ({useBusinessDays} = 0 OR priceBd.date IS NOT NULL)
              AND m.price_date >= nbd.start_date
              AND m.price_date <= nbd.end_date;
        ";

        //due to odd EF core issues, we sometimes have to use ToList before FirstOrDefault when using SqlQuery with a primitive type
        var price = db.Database.SqlQuery<double?>(queryStr).ToList().FirstOrDefault();
        return price ?? missingValue.GetValueOrDefault();
    }

    private PriceDetail GetMostRecentDailyPrice(int indexId, DateOnly date, bool makeUnitConversion = true)
    {
        PriceDetail retval = new() { PriceConverted = missingValue, PriceOriginal = missingValue };
        if (dailyPricesByIndex.Value?.ContainsKey(indexId) == true)
        {
            var firstDayOfDealMonth = Util.Date.FirstDayOfMonth(date);
            var mostRecentPricePairs = (
                from q in dailyPricesByIndex.Value[indexId]
                where q.Key <= date && q.Key >= firstDayOfDealMonth
                orderby q.Key descending
                select q
            );

            if (mostRecentPricePairs.Any())
            {
                retval.PriceDate = mostRecentPricePairs.First().Key;
                retval.PriceOriginal = mostRecentPricePairs.First().Value;
                if (makeUnitConversion)
                    retval.PriceConverted = ConvertPrice(indexPriceUnits[indexId], retval.PriceOriginal);
                else
                    retval.PriceConverted = retval.PriceOriginal;
            }
            retval.IsPosted = !IsMissingPrice(retval.PriceConverted);
        }
        return retval;
    }

    private PriceDetail GetMostRecentMonthlyPrice(int indexId, DateOnly date, bool makeUnitConversion = true)
    {
        PriceDetail retval = new() { PriceConverted = missingValue, PriceOriginal = missingValue };
        if (monthlyPricesByIndex.Value?.ContainsKey(indexId) == true)
        {
            var mostRecentPricePairs = (
                from q in monthlyPricesByIndex.Value[indexId]
                where q.Key <= date
                orderby q.Key descending
                select q
            );

            if (mostRecentPricePairs.Any())
            {
                retval.PriceDate = mostRecentPricePairs.First().Key;
                retval.PriceOriginal = mostRecentPricePairs.First().Value;
                if (makeUnitConversion)
                    retval.PriceConverted = ConvertPrice(indexPriceUnits[indexId], retval.PriceOriginal);
                else
                    retval.PriceConverted = retval.PriceOriginal;
            }
            retval.IsPosted = !IsMissingPrice(retval.PriceConverted);
        }
        return retval;
    }

    [GeneratedRegex(@"\[[^\[]*\]")]
    private static partial Regex FindIndexRegex();
    private PricerResult GetHybridEquationPrice(string equation, DateOnly date, ValPriceType valPriceType, Func<int, string, PriceDetail> getPriceForIndex)
    {
        ValPriceEquationKey key = new(equation, date, valPriceType);
        if (PriceEquationResults.TryGetValue(key, out var result))
            return result;
        else
            result = new() { IsMonthly = true };

        var firstDayOfMonth = Util.Date.FirstDayOfMonth(date);
        var crudeProductIdMonthPair = new Pairs.IntegerDatePair((int)Enums.Product.CrudeOil, firstDayOfMonth);
        if (equation.Contains("[BD") && ExpirationBusinessDayCounts.TryGetValue(crudeProductIdMonthPair, out BusinessDayNums? bdNums))
        {
            equation = equation.Replace("[BD1]", bdNums.BD1.ToString());
            equation = equation.Replace("[BD2]", bdNums.BD2.ToString());
            equation = equation.Replace("[BDT]", bdNums.BDT.ToString());
            result.PriceParts.BD1 = bdNums.BD1;
            result.PriceParts.BD2 = bdNums.BD2;
            result.PriceParts.BDT = bdNums.BDT;
        }

        Match indexMatch = FindIndexRegex().Match(equation);
        bool hasMissingPrices = false;

        while (indexMatch.Success)
        {
            PriceDetail detail = new();
            int indexId;
            bool gotIndexId;
            var indexIdAndSuffixWithBrackets = indexMatch.Value;
            var indexIdAndSuffixWithoutBrackets = Util.String.StripBrackets(indexMatch.Value);
            string suffix = "";
            if (indexIdAndSuffixWithoutBrackets.Contains(bulletSymbol))
            {
                gotIndexId = int.TryParse(indexIdAndSuffixWithoutBrackets.Split(bulletSymbol)[0], out indexId);
                if (gotIndexId)
                    suffix = indexIdAndSuffixWithoutBrackets.Split(bulletSymbol)[1];
            }
            else
                gotIndexId = int.TryParse(indexIdAndSuffixWithoutBrackets, out indexId);

            if (!gotIndexId)
                indexId = 0;

            if (Indexes.ContainsKey(indexId))
            {
                if (!Indexes[indexId].IsMonthly)
                    result.IsMonthly = false;
            }

            if (!hasMissingPrices || includeIndexDetails)
            {
                detail = getPriceForIndex(indexId, suffix);
                detail.IndexId = indexId;
                UpdateHybridPriceParts(result, detail);
                if (!IsMissingPrice(detail.PriceConverted))
                    equation = Microsoft.VisualBasic.Strings.Replace(equation, indexIdAndSuffixWithBrackets, detail.PriceConverted.GetValueOrDefault().ToString(), Compare: Microsoft.VisualBasic.CompareMethod.Text) ?? "";
                else
                {
                    hasMissingPrices = true;
                    equation = Microsoft.VisualBasic.Strings.Replace(equation, indexIdAndSuffixWithBrackets, "[Missing]", Compare: Microsoft.VisualBasic.CompareMethod.Text) ?? "";
                }
            }

            if (includeIndexDetails)
            {
                result.FirstIndexDetail.HybridDefinition = equation;
                result.FirstIndexDetail.Items.Add(detail);
            }

            indexMatch = indexMatch.NextMatch();
        }

        if (!hasMissingPrices)
        {
            try
            {
                IGenericExpression<double> eGeneric = expressionContext.CompileGeneric<double>(equation);
                result.Price = eGeneric.Evaluate();
            }
            catch (Exception)
            {
                result.Price = missingValue;
            }
        }
        else
            result.Price = missingValue;

        PriceEquationResults.TryAdd(key, result);
        return result;
    }

    private PricerResult GetHybridIndexPrice(int hybridIndexId, DateOnly date, ValPriceType valPriceType, Func<int, string, PriceDetail> getPriceForIndex)
    {
        PricerResult result = new() { IsMonthly = true };

        string equation = "0";
        if (IsHybrid(hybridIndexId))
        {
            equation = Indexes[hybridIndexId].HybridIndexDefinition;
            if (string.IsNullOrWhiteSpace(equation))
                throw new ApplicationException(Indexes[hybridIndexId].Name + ": Hybrid index does not have a valid equation.");
        }

        return GetHybridEquationPrice(equation, date, valPriceType, getPriceForIndex);
    }

    private static void UpdateHybridPriceParts(PricerResult result, PriceDetail detail)
    {
        if (detail.PriceCm != null)
            result.PriceParts.PriceCm = detail.PriceCm;
        else if (detail.PricePm != null)
            result.PriceParts.PricePm = detail.PricePm;
        else if (detail.Price2m != null)
            result.PriceParts.Price2m = detail.Price2m;
        else if (detail.Price3m != null)
            result.PriceParts.Price3m = detail.Price3m;
        else if (detail.PriceAg != null && result.PriceParts.PriceAg1 == null)
            result.PriceParts.PriceAg1 = detail.PriceAg;
        else if (detail.PriceAg != null && result.PriceParts.PriceAg2 == null)
            result.PriceParts.PriceAg2 = detail.PriceAg;
    }

    private double? GetAvgOfNonConvertedPrices(int indexId, string suffix, IEnumerable<DateOnly> dates, bool skipMissingValues)
    {
        double? sumPrices = 0;
        int numOfdatesWithPrice = 0;

        foreach (var d in dates)
        {
            var price = GetDailyOrMonthlyPrice(false, indexId, suffix, d, false).PriceConverted;
            if (!IsMissingPrice(price))
            {
                sumPrices = sumPrices.GetValueOrDefault() + price.GetValueOrDefault();
                numOfdatesWithPrice += 1;
            }
            else if (!skipMissingValues)
            {
                sumPrices = missingValue;
                numOfdatesWithPrice = 1;
                break;
            }
        }

        double avgPrice = 0;
        if (sumPrices.HasValue)
            avgPrice = sumPrices.GetValueOrDefault() / Convert.ToDouble(numOfdatesWithPrice);
        return avgPrice;
    }

    private bool IsIndexMapped(int indexId)
    {
        if (!Indexes.TryGetValue(indexId, out var index))
            return false;
        else
            return indexId != index.MappedIndexId;
    }

    public bool IsHybrid(int indexId)
    {
        if (Indexes.TryGetValue(indexId, out var index) && index.IsHybrid)
            return true;
        else
            return false;
    }

    public bool IsMissingPrice(double? price)
    {
        if (missingValue.HasValue)
            return price.GetValueOrDefault() == missingValue.GetValueOrDefault();
        else
            return !price.HasValue;
    }

    public PricerResult GetForwardPrice(int indexId, DateOnly date)
    {
        ArgumentOutOfRangeException.ThrowIfLessThan(indexId, 1);
        ArgumentOutOfRangeException.ThrowIfEqual(date, DateOnly.MinValue);

        PricerResult result;
        var mappedIndex = Indexes[indexId].MappedIndexId;
        ValPriceKey key = new(mappedIndex, date, Enums.ValPriceType.ForwardPrice);
        if (PriceResults.TryGetValue(key, out var priceResult))
            result = priceResult;
        else if (IsHybrid(mappedIndex))
        {
            result = GetHybridIndexPrice(mappedIndex, date, ValPriceType.ForwardPrice, new Func<int, string, PriceDetail>((IndexId, Suffix) =>
            {
                if (!Indexes.TryGetValue(IndexId, out var index))
                    return new PriceDetail() { PriceConverted = missingValue, PriceOriginal = missingValue, IsPosted = false };
                else if (IsHybrid(index.MappedIndexId))
                {
                    // if the index is hybrid then we are using recursive calls here until we find an index that is not hybrid.
                    var retVal = GetForwardPrice(Indexes[IndexId].MappedIndexId, date);
                    PriceDetail hybridPriceDetail = new();
                    {
                        var withBlock = hybridPriceDetail;
                        withBlock.PriceConverted = retVal.Price;
                        withBlock.IsPosted = retVal.IsPosted;
                        withBlock.PriceDate = DateOnly.MinValue; // we dont know the price date
                                                                 // since this is a hybrid index, we dont know what the "original" price is, because we have many prices (maybe).
                                                                 // Use the price we got as Original price.
                        withBlock.PriceOriginal = retVal.Price;
                    }
                    if (IsIndexMapped(IndexId))
                        hybridPriceDetail.MappedIndexId = Indexes[IndexId].MappedIndexId;
                    return hybridPriceDetail;
                }
                else
                {
                    var res = GetForwardCurvePrice(Indexes[IndexId].MappedIndexId, Suffix, date);
                    if (IsIndexMapped(IndexId))
                        res.MappedIndexId = Indexes[IndexId].MappedIndexId;
                    return res;
                }
            }));

            result.IsMonthly = true;
            result.IsPosted = false;
        }
        else
        {
            // get the price of Indexes(indexId).MappedIndexId, yes, that one,
            result = new PricerResult() { IsMonthly = true };
            var priceDetail = GetForwardCurvePrice(mappedIndex, string.Empty, date);
            result.Price = priceDetail.PriceConverted;
            if (includeIndexDetails)
            {
                priceDetail.IndexId = indexId;
                if (IsIndexMapped(indexId))
                    priceDetail.MappedIndexId = Indexes[indexId].MappedIndexId;
                result.FirstIndexDetail.Items.Add(priceDetail);
            }
        }

        PriceResults.TryAdd(key, result);
        return result;
    }

    public PricerResult GetEquationPostedOrForwardPrice(string equation, DateOnly date)
    {
        if (string.IsNullOrWhiteSpace(equation))
            equation = "0";
        ArgumentOutOfRangeException.ThrowIfEqual(date, DateOnly.MinValue);

        PricerResult result = GetHybridEquationPrice(equation, date, ValPriceType.PostedOrForwardPrice, new Func<int, string, PriceDetail>((IndexId, Suffix) =>
        {
            if (!Indexes.ContainsKey(IndexId))
                return new PriceDetail() { PriceConverted = missingValue, PriceOriginal = missingValue };
            else if (IsHybrid(IndexId))
            {
                // if the index is hybrid then we are using recursive calls here until we find an index that is not hybrid.
                var retVal = GetPostedOrForwardPrice(IndexId, date);
                PriceDetail hybridPriceDetail = new();
                {
                    var withBlock = hybridPriceDetail;
                    withBlock.PriceConverted = retVal.Price;
                    withBlock.IsPosted = retVal.IsPosted;
                    withBlock.PriceDate = DateOnly.MinValue; // we dont know the price date
                                                             // since this is a hybrid index, we dont know what the "original" price is, because we have many prices (maybe).
                                                             // Use the price we got as Original price.
                    withBlock.PriceOriginal = retVal.Price;
                }
                return hybridPriceDetail;
            }
            else
                return GetDailyOrMonthlyPrice(false, IndexId, Suffix, date);
        }));

        return result;
    }

    public PricerResult GetPostedOrForwardPrice(int indexId, DateOnly date)
    {
        ArgumentOutOfRangeException.ThrowIfLessThan(indexId, 1);
        ArgumentOutOfRangeException.ThrowIfEqual(date, DateOnly.MinValue);

        PricerResult result;
        ValPriceKey key = new(indexId, date, ValPriceType.PostedOrForwardPrice);
        if (PriceResults.TryGetValue(key, out var priceResult))
            result = priceResult;
        else if (IsHybrid(indexId)) // checks if index id is a hybrid index.
        {
            result = GetHybridIndexPrice(indexId, date, ValPriceType.PostedOrForwardPrice, new Func<int, string, PriceDetail>((IndexId, Suffix) =>
            {
                if (!Indexes.ContainsKey(IndexId))
                    return new PriceDetail() { PriceConverted = missingValue, PriceOriginal = missingValue };
                else if (IsHybrid(IndexId))
                {
                    // if the index is hybrid then we are using recursive calls here until we find an index that is not hybrid.
                    var retVal = GetPostedOrForwardPrice(IndexId, date);
                    PriceDetail hybridPriceDetail = new();
                    {
                        var withBlock = hybridPriceDetail;
                        withBlock.PriceConverted = retVal.Price;
                        withBlock.IsPosted = retVal.IsPosted;
                        withBlock.PriceDate = DateOnly.MinValue; // we dont know the price date
                                                                 // since this is a hybrid index, we dont know what the "original" price is, because we have many prices (maybe).
                                                                 // Use the price we got as Original price.
                        withBlock.PriceOriginal = retVal.Price;
                    }
                    return hybridPriceDetail;
                }
                else
                    return GetDailyOrMonthlyPrice(false, IndexId, Suffix, date);
            }));
        }
        else
        {
            result = new PricerResult() { IsMonthly = Indexes[indexId].IsMonthly };
            var priceDetail = GetDailyOrMonthlyPrice(false, indexId, string.Empty, date);
            result.Price = priceDetail.PriceConverted;
            if (includeIndexDetails)
            {
                priceDetail.IndexId = indexId;
                result.FirstIndexDetail.Items.Add(priceDetail);
            }
        }

        if (IsMissingPrice(result.Price))
        {
            // Daily or monthly index not found, get the forward curve of the Mapped Index and return it.
            var priceDetail = GetForwardPrice(indexId, date);
            if (indexId != Indexes[indexId].MappedIndexId && (IsHybrid(indexId) || IsHybrid(Indexes[indexId].MappedIndexId)))
                // if any index is mapped store value in second object.
                result.SecondIndexDetail = priceDetail.FirstIndexDetail; // store in second object to keep values in the first one.
            else
                result.FirstIndexDetail = priceDetail.FirstIndexDetail;// overwrite
            result.Price = priceDetail.Price;
            result.IsPosted = false;
        }
        else
            result.IsPosted = true;

        PriceResults.TryAdd(key, result);
        return result;
    }

    public PricerResult GetMostRecentPostedPrice(int indexId, DateOnly date)
    {
        ArgumentOutOfRangeException.ThrowIfLessThan(indexId, 1);
        ArgumentOutOfRangeException.ThrowIfEqual(date, DateOnly.MinValue);

        PricerResult result;
        ValPriceKey key = new(indexId, date, ValPriceType.MostRecentPostedPrice);
        if (PriceResults.TryGetValue(key, out var priceResult))
            result = priceResult;
        else if (IsHybrid(indexId)) // checks if index id is a hybrid index.
        {
            result = GetHybridIndexPrice(indexId, date, ValPriceType.MostRecentPostedPrice, new Func<int, string, PriceDetail>((IndexId, Suffix) =>
            {
                if (!Indexes.ContainsKey(IndexId))
                    return new PriceDetail() { PriceConverted = missingValue, PriceOriginal = missingValue };
                else if (IsHybrid(IndexId))
                {
                    // if the index is hybrid then we are using recursive calls here until we find an index that is not hybrid.
                    var retVal = GetMostRecentPostedPrice(IndexId, date);
                    PriceDetail hybridPriceDetail = new();
                    {
                        var withBlock = hybridPriceDetail;
                        withBlock.PriceConverted = retVal.Price;
                        withBlock.IsPosted = retVal.IsPosted;
                        withBlock.PriceDate = DateOnly.MinValue; // we dont know the price date
                                                                 // since this is a hybrid index, we dont know what the "original" price is, because we have many prices (maybe).
                                                                 // Use the price we got as Original price.
                        withBlock.PriceOriginal = retVal.Price;
                    }
                    return hybridPriceDetail;
                }
                else
                    return GetDailyOrMonthlyPrice(true, IndexId, Suffix, date);
            }));
        }
        else
        {
            result = new PricerResult() { IsMonthly = Indexes[indexId].IsMonthly };
            var priceDetail = GetDailyOrMonthlyPrice(true, indexId, string.Empty, date);
            result.Price = priceDetail.PriceConverted;
            if (includeIndexDetails)
            {
                priceDetail.IndexId = indexId;
                result.FirstIndexDetail.Items.Add(priceDetail);
            }
        }

        result.IsPosted = !IsMissingPrice(result.Price);
        PriceResults.TryAdd(key, result);
        return result;
    }

    public PricerResult GetPostedPrice(int indexId, DateOnly date)
    {
        ArgumentOutOfRangeException.ThrowIfLessThan(indexId, 1);
        ArgumentOutOfRangeException.ThrowIfEqual(date, DateOnly.MinValue);

        PricerResult result;
        // checks if index id is a hybrid index.
        if (IsHybrid(indexId))
        {
            result = GetHybridIndexPrice(indexId, date, ValPriceType.PostedOrForwardPrice, new Func<int, string, PriceDetail>((IndexId, Suffix) =>
            {
                if (!Indexes.ContainsKey(IndexId))
                    return new PriceDetail() { PriceConverted = missingValue, PriceOriginal = missingValue };
                else if (IsHybrid(IndexId))
                {
                    // if the index is hybrid then we are using recursive calls here until we find an index that is not hybrid.
                    var retVal = GetPostedPrice(IndexId, date);
                    PriceDetail hybridPriceDetail = new();
                    {
                        var withBlock = hybridPriceDetail;
                        withBlock.PriceConverted = retVal.Price;
                        withBlock.IsPosted = retVal.IsPosted;
                        withBlock.PriceDate = DateOnly.MinValue; // we dont know the price date
                                                                 // since this is a hybrid index, we dont know what the "original" price is, because we have many prices (maybe).
                                                                 // Use the price we got as Original price.
                        withBlock.PriceOriginal = retVal.Price;
                    }
                    return hybridPriceDetail;
                }
                else
                    return GetDailyOrMonthlyPrice(false, IndexId, Suffix, date);
            }));
        }
        else
        {
            result = new PricerResult() { IsMonthly = Indexes[indexId].IsMonthly };
            result.Price = GetDailyOrMonthlyPrice(false, indexId, string.Empty, date).PriceConverted;
        }

        result.IsPosted = !IsMissingPrice(result.Price);
        return result;
    }

    private static bool IsBusinessDay(DateOnly d, HashSet<DateOnly> holidays)
    {
        return d.DayOfWeek != DayOfWeek.Saturday && d.DayOfWeek != DayOfWeek.Sunday && !holidays.Contains(d);
    }

    /// <summary>
    ///     ''' returns the latest business day on or before the passed-in date
    ///     ''' </summary>
    private static DateOnly GetLatestBusinessDay(DateOnly d, HashSet<DateOnly> holidays)
    {
        if (IsBusinessDay(d, holidays))
            return d;
        else
            return GetLatestBusinessDay(d.AddDays(-1), holidays);
    }

    public PricerResult GetPostedAvgPrice(int indexId, IEnumerable<DateOnly> dates, bool skipMissingValues = false)
    {
        ArgumentOutOfRangeException.ThrowIfLessThan(indexId, 1);
        if (!dates.Any())
            throw new ArgumentOutOfRangeException(nameof(dates));

        PricerResult result;
        if (IsHybrid(indexId))
        {
            result = GetHybridIndexPrice(indexId, dates.FirstOrDefault(), ValPriceType.PostedOrForwardPrice, new Func<int, string, PriceDetail>((IndexId, Suffix) =>
            {
                if (!Indexes.ContainsKey(IndexId))
                    return new PriceDetail() { PriceConverted = missingValue, PriceOriginal = missingValue, IsPosted = false };
                else if (IsHybrid(IndexId))
                {
                    // if the index is hybrid then we are using recursive calls here until we find an index that is not hybrid.
                    var avgRetVal = GetPostedAvgPrice(IndexId, dates);
                    PriceDetail hybridPriceDetail = new();
                    {
                        var withBlock = hybridPriceDetail;
                        withBlock.PriceConverted = avgRetVal.Price;
                        withBlock.IsPosted = avgRetVal.IsPosted;
                        withBlock.PriceDate = DateOnly.MinValue; // we dont know the price date
                                                                 // since this is a hybrid index, we dont know what the "original" price is, because we have many prices (maybe).
                                                                 // Use the price we got as Original price.
                        withBlock.PriceOriginal = avgRetVal.Price;
                    }
                    return hybridPriceDetail;
                }
                else
                {
                    PriceDetail retval = new();
                    double? avgPrice = GetAvgOfNonConvertedPrices(IndexId, Suffix, dates, skipMissingValues);
                    if (!IsMissingPrice(avgPrice))
                    {
                        retval.PriceOriginal = avgPrice;
                        retval.PriceConverted = ConvertPrice(indexPriceUnits[IndexId], retval.PriceOriginal);
                    }
                    return retval;
                }
            }));
        }
        else
        {
            result = new PricerResult() { IsMonthly = Indexes[indexId].IsMonthly, Price = missingValue };
            double? avgPrice = GetAvgOfNonConvertedPrices(indexId, string.Empty, dates, skipMissingValues);
            if (!IsMissingPrice(avgPrice))
            {
                result.Price = avgPrice;
                result.Price = ConvertPrice(indexPriceUnits[indexId], result.Price);
            }
        }

        result.IsPosted = !IsMissingPrice(result.Price);
        return result;
    }

    public static void ThrowFuturesDefaultException(int productId)
    {
        string productName = Enum.GetName(typeof(Enums.ProductCategory), productId) ?? "ID " + productId.ToString();
        throw new Exception(string.Format("Could not find the futures default index for product {0}.  Please set one up for this product in the database.", productName));
    }
}

public class PricerResult
{
    public double? Price { get; set; }
    public bool IsPosted { get; set; } = false;
    public bool IsMonthly { get; set; }
    public HybridPriceParts PriceParts { get; set; } = new();
    public PriceDetails FirstIndexDetail { get; set; } = new PriceDetails();
    public PriceDetails SecondIndexDetail { get; set; } = new PriceDetails();
}

public class HybridPriceParts
{
    public double? PriceCm { get; set; }
    public double? PricePm { get; set; }
    public double? Price2m { get; set; }
    public double? Price3m { get; set; }
    public double? PriceAg1 { get; set; }
    public double? PriceAg2 { get; set; }
    public int? BD1 { get; set; }
    public int? BD2 { get; set; }
    public int? BDT { get; set; }
}

public class PriceDetails
{
    public List<PriceDetail> Items { get; set; } = new List<PriceDetail>();
    public string HybridDefinition { get; set; } = "";
}

public class PriceDetail
{
    public int? MappedIndexId { get; set; }
    public bool IsPosted { get; set; }
    public int IndexId { get; set; }
    public DateOnly PriceDate { get; set; }
    public double? PriceOriginal { get; set; }
    public double? PriceConverted { get; set; }
    public double? PriceCm { get; set; }
    public double? PricePm { get; set; }
    public double? Price2m { get; set; }
    public double? Price3m { get; set; }
    public double? PriceAg { get; set; }
}

