namespace Fast.Shared.Logic;

public static class DataHelper
{
    public static async Task<List<IdName>> GetIndexesAsync(bool includeHybrid, bool useShortNames, Enums.ProductCategory productCategory)
    {
        var includeGas = productCategory == Enums.ProductCategory.NaturalGasAndLng || productCategory == Enums.ProductCategory.All;
        var includeCrude = productCategory == Enums.ProductCategory.CrudeOil || productCategory == Enums.ProductCategory.All;
        var includeNgls = productCategory == Enums.ProductCategory.NGLs || productCategory == Enums.ProductCategory.All;

        using var db = Main.CreateContext();
        var indexes = (await (
            from q in db.MarketIndices
            join alias in db.MarketIndexAliases on new { IndexId = q.Id, IndexAliasTypeId = 1 } equals new { alias.IndexId, alias.IndexAliasTypeId } into j1
            from alias in j1.DefaultIfEmpty()
            let isGasIndex = q.Product.CategoryId == (int)Enums.ProductCategory.NaturalGasAndLng
            let isCrudeIndex = q.Product.CategoryId == (int)Enums.ProductCategory.CrudeOil
            let isNglIndex = q.Product.CategoryId == (int)Enums.ProductCategory.NGLs
            where (includeHybrid || !includeHybrid && q.IndexTypeId != (int)Enums.MarketIndexType.Hybrid) &&
                ((includeGas && isGasIndex) || (includeCrude && isCrudeIndex) || (includeNgls && isNglIndex))
            select new IdName(q.Id, useShortNames && !string.IsNullOrWhiteSpace(alias.IndexAlias) ? alias.IndexAlias : q.Name ?? "")
        ).AsNoTracking().ToListAsync()).OrderBy(x => x.Name).ToList();

        return indexes;
    }

    public static async Task<List<IdName>> GetProducersAsync(bool useNickNames)
    {
        using var db = Main.CreateContext();
        var activeProducers = await (
            from q in db.Counterparties
            where q.InactiveDate == null && q.CounterpartyRelationships.Any(x => x.BusinessRelationshipId == 2)
            orderby q.Name
            select new IdName(q.Id, useNickNames && !string.IsNullOrWhiteSpace(q.ShortName) ? q.ShortName : q.Name)
        ).ToListAsync();

        var inactiveProducers = await (
            from q in db.Counterparties
            where q.InactiveDate != null && q.CounterpartyRelationships.Any(x => x.BusinessRelationshipId == 2)
            orderby q.Name
            select new IdName(q.Id, "{Inactive} " + q.Name)
        ).ToListAsync();

        var producers = activeProducers.Concat(inactiveProducers).ToList();

        return producers;
    }

    public static async Task<List<EntityInfo>> GetInternalEntitiesAsync(bool orderByShortName)
    {
        using var db = Main.CreateContext();
        DateTime currentDate = DateTime.Now.Date;
        DateOnly currentDateOnly = DateOnly.FromDateTime(currentDate);

        var activeCounterparties = (await (
            from q in db.Counterparties
            where (q.InactiveDate == null || q.InactiveDate > currentDateOnly) && q.CounterpartyRelationships.Any(x => x.BusinessRelationshipId == 1)
            select new EntityInfo
            {
                EntityId = q.Id,
                ShortName = !string.IsNullOrWhiteSpace(q.ShortName) ? q.ShortName : q.Name,
                FullName = q.Name,
                IsActive = true
            }
        ).AsNoTracking().ToListAsync()
        ).OrderBy(x => orderByShortName ? x.ShortName : x.FullName);

        var inactiveCounterparties = (await (
            from q in db.Counterparties
            where (q.InactiveDate != null && q.InactiveDate <= currentDateOnly) && q.CounterpartyRelationships.Any(x => x.BusinessRelationshipId == 1)
            select new EntityInfo
            {
                EntityId = q.Id,
                ShortName = "{Inactive} " + (!string.IsNullOrWhiteSpace(q.ShortName) ? q.ShortName : q.Name),
                FullName = "{Inactive} " + q.Name,
                IsActive = false
            }
        ).AsNoTracking().ToListAsync()
        ).OrderBy(x => orderByShortName ? x.ShortName : x.FullName);

        var entities = activeCounterparties.Concat(inactiveCounterparties).ToList();
        return entities;
    }

    public static async Task<List<IdName>> GetBooksAsync()
    {
        using var db = Main.CreateContext();
        var items = await (
            from q in db.Books
            where q.IsActive == true
            orderby q.Name
            select new IdName(q.Id, q.Name ?? "")
        ).ToListAsync();

        return items;
    }

    public static async Task<List<IdName>> GetBrokersAsync()
    {
        using var db = Main.CreateContext();
        var items = await (
            from q in db.Brokers
            orderby q.Name
            select new IdName(q.Id, q.Name ?? "")
        ).ToListAsync();

        return items;
    }

    public static async Task<List<IdName>> GetBrokerAccountsAsync()
    {
        using var db = Main.CreateContext();
        var items = await (
            from q in db.BrokerAccounts
            orderby q.Name
            select new IdName(q.Id, q.Name ?? "")
        ).ToListAsync();

        return items;
    }

    public static async Task<List<IdName>> GetDealPurposesAsync()
    {
        using var db = Main.CreateContext();
        var items = await (
            from q in db.DealPurposeTypes
            orderby q.Name
            select new IdName(q.Id, q.Name ?? "")
        ).ToListAsync();

        return items;
    }

    public static async Task<List<IdName>> GetDealStatusesAsync()
    {
        using var db = Main.CreateContext();
        var items = await (
            from q in db.DealStatuses
            orderby q.Name
            select new IdName(q.Id, q.Name ?? "")
        ).ToListAsync();

        return items;
    }

    public static async Task<List<IdName>> GetDealTypesAsync()
    {
        using var db = Main.CreateContext();
        var items = await (
            from q in db.PhysicalDealTypes
            where q.IsActive == true
            orderby q.Name
            select new IdName(q.Id, q.Name ?? "")
        ).ToListAsync();

        return items;
    }

    public static async Task<List<IdName>> GetPortfoliosAsync()
    {
        using var db = Main.CreateContext();
        var items = await (
            from q in db.Portfolios
            where q.IsActive == true
            orderby q.Name
            select new IdName(q.Id, q.Name ?? "")
        ).ToListAsync();

        return items;
    }

    public static async Task<List<IdName>> GetRegionsAsync()
    {
        using var db = Main.CreateContext();
        var items = await (
            from q in db.Regions
            where q.IsActive == true
            orderby q.Name
            select new IdName(q.Id, q.Name ?? "")
        ).ToListAsync();

        return items;
    }

    public static async Task<List<IdName>> GetRelationshipsAsync()
    {
        using var db = Main.CreateContext();
        var items = await (
            from q in db.BusinessRelationships
            orderby q.Name
            select new IdName(q.Id, q.Name ?? "")
        ).ToListAsync();

        return items;
    }

    public static async Task<List<IdName>> GetStrategiesAsync()
    {
        using var db = Main.CreateContext();
        var items = await (
            from q in db.Strategies
            where q.IsActive == true
            orderby q.Name
            select new IdName(q.Id, q.Name ?? "")
        ).ToListAsync();

        return items;
    }

    public static async Task<List<IdName>> GetTradersAsync()
    {
        using var db = Main.CreateContext();
        var items = await (
            from q in db.AppUsers
            orderby q.DisplayName
            select new IdName(q.Id, q.DisplayName ?? "")
        ).ToListAsync();

        return items;
    }

    public static async Task<List<IdName>> GetTransactionTypesAsync()
    {
        List<int> includedTransTypes = new() {
            (int)Enums.TransactionType.PhysicalGas,
            (int)Enums.TransactionType.Futures,
            (int)Enums.TransactionType.Options,
            (int)Enums.TransactionType.BasisSwaps,
            (int)Enums.TransactionType.FixedFloatingSwaps,
            (int)Enums.TransactionType.SwingSwaps,
            (int)Enums.TransactionType.PhysicalNGL,
            (int)Enums.TransactionType.PhysicalCrudeOil
        };

        using var db = Main.CreateContext();
        var items = await (
            from q in db.TransactionTypes
            orderby q.Name
            select new IdName(q.Id, q.Name ?? "")
        ).ToListAsync();

        return items;
    }

    public static async Task<List<IdName>> GetTerritoriesAsync()
    {
        using var db = Main.CreateContext();

        var items = await (
            from q in db.Territories
            orderby q.Name
            select new IdName(q.Id, q.Name ?? "")
        ).ToListAsync();

        return items;
    }

    public static async Task<List<IdName>> GetContractStatusesAsync()
    {
        using var db = Main.CreateContext();

        var items = await (
            from q in db.ContractStatuses
            orderby q.Name
            select new IdName(q.Id, q.Name ?? "")
        ).ToListAsync();

        return items;
    }

    public static async Task<List<IdName>> GetPlantsAsync()
    {
        using var db = Main.CreateContext();

        var activePlants = await (
            from q in db.Plants
            where q.IsActive == true
            orderby q.Name
            select new IdName(q.Id, q.Name)
        ).ToListAsync();

        var inactivePlants = await (
            from q in db.Plants
            where q.IsActive == false
            orderby q.Name
            select new IdName(q.Id, "{Inactive} " + q.Name)
        ).ToListAsync();

        var producers = activePlants.Concat(inactivePlants).ToList();

        return producers;
    }

    public static async Task<List<IdName>> GetProductCategoriesAsync()
    {
        using var db = Main.CreateContext();
        var items = await (
            from q in db.ProductCategories
            orderby q.Name
            select new IdName(q.Id, q.Name)
        ).ToListAsync();

        return items;
    }

    public static async Task<List<IdName>> GetProductsAsync(bool includeCategoryPrefix = true)
    {
        using var db = Main.CreateContext();

        var items = await (
            from q in db.Products
            orderby q.CategoryId, q.SortIndex
            select new IdName(q.Id, includeCategoryPrefix ? $"{q.Category.Prefix} - {q.Name}" : q.Name)
        ).ToListAsync();

        return items;
    }

    public static async Task<List<ZoneInfo>> GetZonesAsync()
    {
        using var db = Main.CreateContext();

        var items = await (
            from q in db.Zones
            orderby q.Name
            select new ZoneInfo { ZoneId = q.Id, ZoneName = q.Name, PipeId = q.PipelineId }
        ).ToListAsync();

        return items;
    }

    public static List<IdName> GetMonths()
    {
        var months = new List<IdName> {
            new(1, "January"),
            new(2, "February"),
            new(3, "March"),
            new(4, "April"),
            new(5, "May"),
            new(6, "June"),
            new(7, "July"),
            new(8, "August"),
            new(9, "September"),
            new(10, "October"),
            new(11, "November"),
            new(12, "December")
        };

        return months;
    }

    public static async Task<List<IdName>> GetPriceUnits()
    {
        using var db = Main.CreateContext();

        var priceUnits = await (
            from q in db.UnitPriceCombinations
            orderby q.Id
            select new IdName(q.Id, q.CurrencyType.Symbol + "/" + q.UnitOfMeasure.Symbol)
        ).ToListAsync();

        return priceUnits;
    }

    public static async Task<List<IdName>> GetInternalEntitiesOldAsync(bool useShortNames)
    {
        using var db = Main.CreateContext();
        DateTime currentDate = DateTime.Now.Date;

        var activeCounterparties = await (
            from q in db.Counterparties
            where q.InactiveDate == null && q.CounterpartyRelationships.Any(x => x.BusinessRelationshipId == 1)
            orderby q.Name
            select new IdName(q.Id, useShortNames && !string.IsNullOrWhiteSpace(q.ShortName) ? q.ShortName : q.Name)
        ).ToListAsync();

        var inactiveCounterparties = await (
            from q in db.Counterparties
            where q.InactiveDate != null && q.CounterpartyRelationships.Any(x => x.BusinessRelationshipId == 1)
            orderby q.Name
            select new IdName(q.Id, "{Inactive} " + (useShortNames && !string.IsNullOrWhiteSpace(q.ShortName) ? q.ShortName : q.Name))
        ).ToListAsync();

        var entities = activeCounterparties.Concat(inactiveCounterparties).ToList();
        return entities;
    }

    public static async Task<List<PipeInfo>> GetPipelinesAsync(bool hasUnallocated)
    {
        using var db = Main.CreateContext();

        var productCategories = await db.Products
            .Where(p => p.CategoryId == (int)Enums.ProductCategory.CrudeOil || p.CategoryId == (int)Enums.ProductCategory.NaturalGasAndLng)
            .Select(p => new { p.Id, p.CategoryId })
            .AsNoTracking()
            .ToListAsync();

        var gasProductIds = productCategories
            .Where(p => p.CategoryId == (int)Enums.ProductCategory.NaturalGasAndLng)
            .Select(p => p.Id)
            .ToHashSet();

        var crudeProductIds = productCategories
            .Where(p => p.CategoryId == (int)Enums.ProductCategory.CrudeOil)
            .Select(p => p.Id)
            .ToHashSet();

        var pipesInitial = await (
            from q in db.Pipelines
            where q.ProductIds != null && (hasUnallocated || q.Name != "UNALLOCATED")
            select new
            {
                q.Id,
                q.Name,
                q.ProductIds,
                q.PipeShort
            }
        ).AsNoTracking().ToListAsync();

        var pipesFinal = (
            from q in pipesInitial
            let isInactive = q.Name != null && q.Name.Contains("inactive", StringComparison.CurrentCultureIgnoreCase)
            let productIds = q.ProductIds.Split(",").Select(x => int.Parse(x))
            orderby isInactive, q.Name
            select new PipeInfo
            {
                PipeId = q.Id,
                PipeName = q.Name,
                PipeShort = q.PipeShort ?? q.Name,
                IsGasPipe = productIds.Any(gasProductIds.Contains),
                IsCrudePipe = productIds.Any(crudeProductIds.Contains),
                ProductIds = productIds.ToList()
            }
        ).ToList();

        return pipesFinal;
    }

    public static async Task<List<PointInfo>> GetPointsAsync(bool includePipe, Enums.ProductCategory productCategory)
    {
        var includeGas = productCategory is Enums.ProductCategory.NaturalGasAndLng or Enums.ProductCategory.All;
        var includeCrude = productCategory is Enums.ProductCategory.CrudeOil or Enums.ProductCategory.All;

        using var db = Main.CreateContext();

        var productCategories = await db.Products
            .Where(p => p.CategoryId == (int)Enums.ProductCategory.CrudeOil || p.CategoryId == (int)Enums.ProductCategory.NaturalGasAndLng)
            .Select(p => new { p.Id, p.CategoryId })
            .AsNoTracking()
            .ToListAsync();

        var gasProductIds = productCategories
            .Where(p => p.CategoryId == (int)Enums.ProductCategory.NaturalGasAndLng)
            .Select(p => p.Id)
            .ToHashSet();

        var crudeProductIds = productCategories
            .Where(p => p.CategoryId == (int)Enums.ProductCategory.CrudeOil)
            .Select(p => p.Id)
            .ToHashSet();

        var points = await (
            from q in db.Points
            join pipeline in db.Pipelines on q.PipelineId equals pipeline.Id
            where q.PipelineId != null && !string.IsNullOrEmpty(q.ProductIds)
            select new
            {
                q.Id,
                q.Name,
                q.IsActive,
                q.PipelineId,
                q.ProductIds,
                PipeName = !string.IsNullOrWhiteSpace(pipeline.PipeShort) ? pipeline.PipeShort : pipeline.Name
            }
        ).AsNoTracking().ToListAsync();

        var result = new List<PointInfo>(points.Count);
        foreach (var point in points)
        {
            var productIds = point.ProductIds
                .Split(',', StringSplitOptions.RemoveEmptyEntries)
                .Select(int.Parse)
                .ToList();

            var isGasPoint = productIds.Any(gasProductIds.Contains);
            var isCrudePoint = productIds.Any(crudeProductIds.Contains);

            if (!((includeGas && isGasPoint) || (includeCrude && isCrudePoint)))
                continue;

            var displayName = includePipe
                ? $"{point.PipeName} / {point.Name ?? ""}"
                : point.Name ?? "";

            result.Add(new PointInfo
            {
                PointId = point.Id,
                PointName = point.IsActive ? displayName : $"{{Inactive}} {displayName}",
                PipeId = point.PipelineId!.Value,
                PipeName = point.PipeName,
                IsGasPoint = isGasPoint,
                IsCrudePoint = isCrudePoint,
                ProductIds = productIds,
                IsActive = point.IsActive
            });
        }

        var sortedResults = includePipe
            ? result.OrderByDescending(x => x.IsActive).ThenBy(x => x.PipeName).ThenBy(x => x.PointName).ToList()
            : result.OrderByDescending(x => x.IsActive).ThenBy(x => x.PointName).ToList();

        return sortedResults;
    }

    public static IQueryable<MeterProduct> RecentMeterProductsQueryable(MyDbContext db, DateOnly? asOfDate = null)
    {
        // default to today if no asOfDate was provided
        var asOfDateLocal = asOfDate ?? DateOnly.FromDateTime(DateTime.Now.Date);

        var maxEffectiveDates = (
            from q in db.MeterProducts
            where q.EffectiveDate <= asOfDateLocal
            group q by new { q.MeterId, q.ProductId } into g
            select new { g.Key.MeterId, g.Key.ProductId, MaxEffective = g.Max(x => x.EffectiveDate) });

        var recentMeterProducts = (
            from q in db.MeterProducts
            join d in maxEffectiveDates on new { q.MeterId, q.ProductId, q.EffectiveDate } equals new { d.MeterId, d.ProductId, EffectiveDate = d.MaxEffective }
            select q
        );

        return recentMeterProducts;
    }

    public static async Task<List<MeterInfo>> GetMetersByProductAsync(Enums.ProductCategory productCategory, List<int>? meterIds = null, DateOnly? asOfDate = null)
    {
        static MyDbContext getNewDb() => Main.CreateContext();

        var includeGas = productCategory == Enums.ProductCategory.NaturalGasAndLng || productCategory == Enums.ProductCategory.All;
        var includeCrude = productCategory == Enums.ProductCategory.CrudeOil || productCategory == Enums.ProductCategory.All;

        var crudeProductIdsTask = getNewDb().Products.Where(x => x.CategoryId == (int)Enums.ProductCategory.CrudeOil).Select(x => x.Id).ToListAsync();
        var gasProductIdsTask = getNewDb().Products.Where(x => x.CategoryId == (int)Enums.ProductCategory.NaturalGasAndLng).Select(x => x.Id).ToListAsync();

        // default to today if no asOfDate was provided
        var asOfDateLocal = asOfDate ?? DateOnly.FromDateTime(DateTime.Now.Date);

        var db = getNewDb();
        var recentMetersTask = (
            from q in RecentMeterProductsQueryable(db)
                .Include(x => x.Meter)
                .Include(x => x.SourceZone)
                .ThenInclude(x => x!.Pipeline)
                .Include(x => x.MeterProductSourcePoints)
            select q
        ).AsNoTracking().ToListAsync();

        List<MeterInfo> activeMetersCrudeLocal = new();
        List<MeterInfo> activeMetersGasLocal = new();
        List<MeterInfo> inactiveMetersCrudeLocal = new();
        List<MeterInfo> inactiveMetersGasLocal = new();

        List<int> gasProductIds = await gasProductIdsTask;
        List<int> crudeProductIds = await crudeProductIdsTask;
        var mostRecentMeters = await recentMetersTask;

        if (includeCrude)
        {
            activeMetersCrudeLocal = (
                from mp in mostRecentMeters
                let isActive = (mp.Meter.InactiveDate == null || (asOfDateLocal < mp.Meter.InactiveDate))
                where isActive
                    && crudeProductIds.Contains(mp.ProductId)
                    && (meterIds == null || meterIds.Count == 0 || meterIds.Contains(mp.MeterId))
                select new MeterInfo
                {
                    MeterId = mp.MeterId,
                    MeterName = mp.Meter.Name,
                    MeterNum = mp.Number ?? "",
                    ZoneId = mp.SourceZoneId ?? 0,
                    PipeId = mp.SourceZone == null ? 0 : mp.SourceZone!.PipelineId,
                    PipeCode = mp.SourceZone == null ? "" : mp.SourceZone!.Pipeline.PipeCode ?? "",
                    MeterNameAndNum = mp.Meter.Name + "/" + mp.Number,
                    PlantIdsStr = mp.PlantIds,
                    PointIds = mp.MeterProductSourcePoints.Select(sp => sp.PointId).ToList(),
                    ProducerIdsStr = mp.Meter.ProducerIds,
                    IsCrudeMeter = true,
                    IsGasMeter = false,
                    ProductId = mp.ProductId
                }
            ).Distinct().ToList();

            inactiveMetersCrudeLocal = (
                from mp in mostRecentMeters
                let isInactive = (mp.Meter.InactiveDate != null && (mp.Meter.InactiveDate <= asOfDateLocal))
                where isInactive
                    && crudeProductIds.Contains(mp.ProductId)
                    && (meterIds == null || meterIds.Count == 0 || meterIds.Contains(mp.MeterId))
                select new MeterInfo
                {
                    MeterId = mp.MeterId,
                    MeterName = "{Inactive} " + mp.Meter.Name,
                    MeterNum = mp.Number ?? "",
                    ZoneId = mp.SourceZoneId ?? 0,
                    PipeId = mp.SourceZone == null ? 0 : mp.SourceZone!.PipelineId,
                    PipeCode = mp.SourceZone == null ? "" : mp.SourceZone!.Pipeline.PipeCode ?? "",
                    MeterNameAndNum = "{Inactive} " + mp.Meter.Name + "/" + mp.Number,
                    PlantIdsStr = mp.PlantIds,
                    PointIds = mp.MeterProductSourcePoints.Select(sp => sp.PointId).ToList(),
                    ProducerIdsStr = mp.Meter.ProducerIds,
                    IsCrudeMeter = true,
                    IsGasMeter = false,
                    ProductId = mp.ProductId
                }
            ).Distinct().ToList();
        }

        if (includeGas)
        {
            activeMetersGasLocal = (
                from mp in mostRecentMeters
                let isActive = (mp.Meter.InactiveDate == null || (asOfDateLocal < mp.Meter.InactiveDate))
                where isActive
                    && gasProductIds.Contains(mp.ProductId)
                    && (meterIds == null || meterIds.Count == 0 || meterIds.Contains(mp.MeterId))
                select new MeterInfo
                {
                    MeterId = mp.MeterId,
                    MeterName = mp.Meter.Name,
                    MeterNum = mp.Number ?? "",
                    ZoneId = mp.SourceZoneId ?? 0,
                    PipeId = mp.SourceZone == null ? 0 : mp.SourceZone!.PipelineId,
                    PipeCode = mp.SourceZone == null ? "" : mp.SourceZone!.Pipeline.PipeCode ?? "",
                    MeterNameAndNum = mp.Meter.Name + "/" + mp.Number,
                    PlantIdsStr = mp.PlantIds,
                    PointIds = mp.MeterProductSourcePoints.Select(sp => sp.PointId).ToList(),
                    ProducerIdsStr = mp.Meter.ProducerIds,
                    IsCrudeMeter = false,
                    IsGasMeter = true,
                    ProductId = mp.ProductId
                }
            ).Distinct().ToList();

            inactiveMetersGasLocal = (
                from mp in mostRecentMeters
                let isInactive = (mp.Meter.InactiveDate != null && (mp.Meter.InactiveDate <= asOfDateLocal))
                where isInactive
                    && gasProductIds.Contains(mp.ProductId)
                    && (meterIds == null || meterIds.Count == 0 || meterIds.Contains(mp.MeterId))
                select new MeterInfo
                {
                    MeterId = mp.MeterId,
                    MeterName = "{Inactive} " + mp.Meter.Name,
                    MeterNum = mp.Number ?? "",
                    ZoneId = mp.SourceZoneId ?? 0,
                    PipeId = mp.SourceZone == null ? 0 : mp.SourceZone!.PipelineId,
                    PipeCode = mp.SourceZone == null ? "" : mp.SourceZone!.Pipeline.PipeCode ?? "",
                    MeterNameAndNum = "{Inactive} " + mp.Meter.Name + "/" + mp.Number,
                    PlantIdsStr = mp.PlantIds,
                    PointIds = mp.MeterProductSourcePoints.Select(sp => sp.PointId).ToList(),
                    ProducerIdsStr = mp.Meter.ProducerIds,
                    IsCrudeMeter = false,
                    IsGasMeter = true,
                    ProductId = mp.ProductId
                }
            ).Distinct().ToList();
        }

        List<MeterInfo> activeMetersLocal = (activeMetersCrudeLocal.Concat(activeMetersGasLocal)).OrderBy(x => x.MeterName).ToList();
        List<MeterInfo> inactiveMetersLocal = (inactiveMetersCrudeLocal.Concat(inactiveMetersGasLocal)).OrderBy(x => x.MeterName).ToList();

        var activeMeters = new List<MeterInfo>();
        foreach (var meter in activeMetersLocal)
        {
            var m = new MeterInfo();
            m.MeterId = meter.MeterId;
            m.MeterName = meter.MeterName;
            m.MeterNum = meter.MeterNum;
            m.ZoneId = meter.ZoneId;
            m.PipeId = meter.PipeId;
            m.PipeCode = meter.PipeCode;
            m.MeterNameAndNum = meter.MeterNameAndNum;
            m.PlantIds = meter.PlantIdsStr == null ? new List<int>() : (meter.PlantIdsStr.Split(",", StringSplitOptions.TrimEntries)).Select(x => Int32.Parse(x)).ToList();
            m.PointIds = meter.PointIds ?? new List<int>();
            m.ProducerIds = meter.ProducerIdsStr == null ? new List<int>() : meter.ProducerIdsStr.Split(",", StringSplitOptions.TrimEntries).Select(x => Int32.Parse(x)).ToList();
            m.IsGasMeter = meter.IsGasMeter;
            m.IsCrudeMeter = meter.IsCrudeMeter;
            m.ProductId = meter.ProductId;
            activeMeters.Add(m);
        }

        var inactiveMeters = new List<MeterInfo>();
        foreach (var meter in inactiveMetersLocal)
        {
            var m = new MeterInfo();
            m.MeterId = meter.MeterId;
            m.MeterName = meter.MeterName;
            m.MeterNum = meter.MeterNum;
            m.ZoneId = meter.ZoneId;
            m.PipeId = meter.PipeId;
            m.PipeCode = meter.PipeCode;
            m.MeterNameAndNum = meter.MeterNameAndNum;
            m.PlantIds = meter.PlantIdsStr == null ? new List<int>() : (meter.PlantIdsStr.Split(",", StringSplitOptions.TrimEntries)).Select(x => Int32.Parse(x)).ToList();
            m.PointIds = meter.PointIds ?? new List<int>();
            m.ProducerIds = meter.ProducerIdsStr == null ? new List<int>() : meter.ProducerIdsStr.Split(",", StringSplitOptions.TrimEntries).Select(x => Int32.Parse(x)).ToList();
            m.IsGasMeter = meter.IsGasMeter;
            m.IsCrudeMeter = meter.IsCrudeMeter;
            m.ProductId = meter.ProductId;
            inactiveMeters.Add(m);
        }

        var meters = activeMeters.Concat(inactiveMeters).ToList();
        return meters;
    }

    public static int? GetPipeForMeterProduct(List<MeterInfo> meters, int? meterId, int? productId)
    {
        if (meterId == null)
            return null;

        List<MeterInfo> meterPipes = new();

        foreach (var meterInfo in meters)
        {
            if (meterInfo.MeterId != meterId)
                continue;

            if (meterInfo.ProductId == productId)
                return meterInfo.PipeId;

            meterPipes.Add(meterInfo);
        }

        return meterPipes.First().PipeId;
    }

    /// <summary>
    /// Gets counterparties for the selected products.
    /// </summary>
    /// <remarks>
    /// Accepts multiple product selections.<br/>
    /// ProductSelection.All includes Counterparties without any specified product.
    /// </remarks>
    public static async Task<List<EntityInfo>> GetCounterpartiesAsync(bool orderByShortName, Enums.ProductCategory productCategory, params Enums.ProductCategory[] productCategories)
    {
        //we have productSelection and productSelections parameters because we want to allow multiple selections, but require at least one
        productCategories = productCategories.Append(productCategory).Distinct().ToArray();

        var includeAllProducts = productCategories.Contains(Enums.ProductCategory.All);
        var includeGas = productCategories.Contains(Enums.ProductCategory.NaturalGasAndLng) || includeAllProducts;
        var includeCrude = productCategories.Contains(Enums.ProductCategory.CrudeOil) || includeAllProducts;

        using var db = Main.CreateContext();
        DateTime currentDate = DateTime.Now.Date;
        DateOnly currentDateOnly = DateOnly.FromDateTime(currentDate);

        var productsByCounterpartyIdLookup = (await (
            from ct in db.CounterpartyProducts
            select new
            {
                ct.CounterpartyId,
                ProductCategoryId = ct.Product.CategoryId
            }
        ).Distinct().AsNoTracking().ToListAsync()).ToLookup(x => x.CounterpartyId, x => x.ProductCategoryId);

        var activeCounterparties = await (
            from c in db.Counterparties
            where
                (c.InactiveDate == null || c.InactiveDate.Value > currentDateOnly)
            select new EntityInfo
            {
                EntityId = c.Id,
                ShortName = !string.IsNullOrWhiteSpace(c.ShortName) ? c.ShortName : c.Name,
                FullName = c.Name,
                IsActive = true,
                ChildIds = c.CounterpartyParentParents.Select(x => x.CounterpartyId).ToList()
            }
        ).AsNoTracking().ToListAsync();

        activeCounterparties = (
            from q in activeCounterparties
            where
                includeAllProducts ||
                (includeGas && productsByCounterpartyIdLookup[q.EntityId].Contains((int)Enums.ProductCategory.NaturalGasAndLng)) ||
                (includeCrude && productsByCounterpartyIdLookup[q.EntityId].Contains((int)Enums.ProductCategory.CrudeOil))
            select q
        ).OrderBy(x => orderByShortName ? x.ShortName : x.FullName).ToList();

        var inactiveCounterparties = await (
            from c in db.Counterparties
            where
                (c.InactiveDate != null && c.InactiveDate <= currentDateOnly)
            select new EntityInfo
            {
                EntityId = c.Id,
                ShortName = "{Inactive} " + (!string.IsNullOrWhiteSpace(c.ShortName) ? c.ShortName : c.Name),
                FullName = "{Inactive} " + c.Name,
                IsActive = false,
                ChildIds = c.CounterpartyParentParents.Select(x => x.CounterpartyId).ToList()
            }
        ).AsNoTracking().ToListAsync();

        inactiveCounterparties = (
            from q in inactiveCounterparties
            where
                includeAllProducts ||
                (includeGas && productsByCounterpartyIdLookup[q.EntityId].Contains((int)Enums.ProductCategory.NaturalGasAndLng)) ||
                (includeCrude && productsByCounterpartyIdLookup[q.EntityId].Contains((int)Enums.ProductCategory.CrudeOil))
            select q
        ).OrderBy(x => orderByShortName ? x.ShortName : x.FullName).ToList();

        var counterparties = activeCounterparties.Concat(inactiveCounterparties).ToList();
        return counterparties;
    }

    public static async Task<List<IdName>> GetLeasesAsync()
    {
        using var db = Main.CreateContext();
        var items = await (
            from q in db.Leases
            orderby q.Name
            select new IdName(q.Id, q.Name ?? "")
        ).ToListAsync();

        return items;
    }
}
