mapo-core/MP.Data/Repository/Utils/StatsDetailRepository.cs

using EgwCoreLib.Utils;
using Microsoft.EntityFrameworkCore;
using MP.Data.DbModels.Utils;
using NLog;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace MP.Data.Repository.Utils
{
    public class StatsDetailRepository : BaseRepository, IStatsDetailRepository
    {
        #region Public Constructors

        public StatsDetailRepository(IDbContextFactory<MoonPro_UtilsContext> ctxFactory) : base(ctxFactory)
        {
        }

        #endregion Public Constructors

        #region Public Methods

        /// <inheritdoc />
        public async Task<List<StatsDetailModel>> GetFiltAsync(DateTime dtStart, DateTime dtEnd, string sEnvir = "", string sType = "")
        {
            await using var dbCtx = await CreateContextAsync();
            List<StatsDetailModel> answ = new List<StatsDetailModel>();

            // recupero ed ordino per data-ora
            var query = dbCtx.DbSetStatsDet
             .Where(x => x.Hour >= dtStart && x.Hour <= dtEnd);

            if (!string.IsNullOrEmpty(sEnvir))
                query = query.Where(x => x.Destination == sEnvir);

            if (!string.IsNullOrEmpty(sType))
                query = query.Where(x => x.Type == sType);

            answ = await query
                         .AsNoTracking()
                         .OrderBy(x => x.Hour)
                         .ThenBy(x => x.Destination)
                         .ThenBy(x => x.Type)
                         .ToListAsync();

            return answ;
        }

        /// <inheritdoc />
        public async Task<List<StatsDetailModel>> GetParetoAsync(DateTime dtStart, DateTime dtEnd, int maxRec, string sDest = "", string sType = "")
        {
            await using var dbCtx = await CreateContextAsync();
            List<StatsDetailModel> answ = new List<StatsDetailModel>();

            // recupero ed ordino per data-ora
            var query = dbCtx.DbSetStatsDet
             .Where(x => x.Hour >= dtStart && x.Hour <= dtEnd);

            if (!string.IsNullOrEmpty(sDest))
                query = query.Where(x => x.Destination == sDest);

            if (!string.IsNullOrEmpty(sType))
                query = query.Where(x => x.Type == sType);

            answ = await query
                         .AsNoTracking()
                         .OrderByDescending(x => x.RequestCount)
                         .ThenBy(x => x.Hour)
                         .ThenBy(x => x.Destination)
                         .ThenBy(x => x.Type)
                         .Take(maxRec)
                         .ToListAsync();

            return answ;
        }

        /// <inheritdoc />
        public async Task<DtUtils.Periodo> GetRangeAsync(string sEnvir, string sType)
        {
            await using var dbCtx = await CreateContextAsync();
            DtUtils.Periodo answ = new DtUtils.Periodo(DtUtils.PeriodSet.Today);

            var query = dbCtx.DbSetStatsDet.AsQueryable();

            if (!string.IsNullOrEmpty(sEnvir))
                query = query.Where(x => x.Destination == sEnvir);

            if (!string.IsNullOrEmpty(sType))
                query = query.Where(x => x.Type == sType);

            var minHour = await query.MinAsync(x => x.Hour);
            var maxHour = await query.MaxAsync(x => x.Hour);
            answ.Inizio = minHour;
            answ.Fine = maxHour;
            return answ;
        }

        /// <inheritdoc />
        public async Task<int> UpsertManyAsync(List<StatsDetailModel> listRecords, bool removeOld)
        {
            if (listRecords == null || !listRecords.Any()) return 0;

            int answ = 0;
            await using var dbCtx = await CreateContextAsync();
            await using var tx = await dbCtx.Database.BeginTransactionAsync();

            try
            {
                // 1. Calcolo del range temporale basato sulla lista in arrivo per ottimizzare la query SQL
                var minHour = listRecords.Min(x => x.Hour);
                var maxHour = listRecords.Max(x => x.Hour);

                // 2. Gestione eliminazione distruttiva (se richiesto)
                if (removeOld)
                {
                    // uso direttamente ExecuteDelete quando in EFCore8...
#if false
                            await dbCtx
                                    .DbSetStatsDet
                                    .Where(x => x.Hour >= startDate && x.Hour <= endDate)
                                    .ExecuteDeleteAsync();
#endif

                    var itemsToRemove = await dbCtx.DbSetStatsDet
                                                    .Where(x => x.Hour >= minHour && x.Hour <= maxHour)
                                                    .ToListAsync();

                    if (itemsToRemove.Any())
                    {
                        dbCtx.DbSetStatsDet.RemoveRange(itemsToRemove);
                        await dbCtx.SaveChangesAsync();
                    }
                }

                // 3. LOGICA DI UPSERT (Merge basato su Destination + Type + Hour)

                // Recuperiamo dal DB solo i record che rientrano nel range temporale della lista in arrivo
                var existingRecords = await dbCtx.DbSetStatsDet
                    .Where(x => x.Hour >= minHour && x.Hour <= maxHour)
                    .ToListAsync();

                // Creiamo il dizionario di lookup con la chiave composta (Dest, Type, Hour)
                var lookup = existingRecords.ToDictionary(
                    x => (x.Destination, x.Type, x.Hour),
                    x => x
                );

                foreach (var incoming in listRecords)
                {
                    // Creiamo la chiave di ricerca basata sul record in arrivo
                    var key = (incoming.Destination, incoming.Type, incoming.Hour);

                    if (lookup.TryGetValue(key, out var existing))
                    {
                        // --- CASO: UPDATE ---
                        // Aggiorniamo i valori del record esistente con quelli nuovi
                        existing.RequestCount = incoming.RequestCount;
                        existing.AvgDuration = incoming.AvgDuration;
                        existing.MinDuration = incoming.MinDuration;
                        existing.MaxDuration = incoming.MaxDuration;
                        existing.NoReply = incoming.NoReply;
                    }
                    else
                    {
                        // --- CASO: INSERT ---
                        // Il record non esiste per questa combinazione, lo aggiungiamo
                        await dbCtx.DbSetStatsDet.AddAsync(incoming);
                    }
                }

                // 4. Salvataggio finale delle modifiche (Insert + Update)
                answ = await dbCtx.SaveChangesAsync();

                // Commit della transazione
                await tx.CommitAsync();

                // Pulizia memoria del ChangeTracker per evitare accumuli durante sessioni lunghe
                dbCtx.ChangeTracker.Clear();

                return answ;
            }
            catch (Exception ex)
            {
                await tx.RollbackAsync();
                Log.Error(ex, "Error during StatsDetail UpsertManyAsync");
                throw;
            }
        }

        #endregion Public Methods

#if false
        /// <inheritdoc />
        public async Task<int> UpsertManyAsyncOrig(List<StatsDetailModel> listRecords, bool removeOld)
        {
            int answ = 0;
            await using var dbCtx = await CreateContextAsync();
            await using var tx = await dbCtx.Database.BeginTransactionAsync();
            try
            {
                // in primis se richiesto calcolo range periodo e svuoto...
                if (removeOld)
                {
                    var firstRec = listRecords.OrderBy(x => x.Hour).FirstOrDefault();
                    var lastRec = listRecords.OrderByDescending(x => x.Hour).FirstOrDefault();

                    if (firstRec != null && lastRec != null)
                    {
                        DateTime startDate = firstRec.Hour;
                        DateTime endDate = lastRec.Hour;

                        var items = await dbCtx.DbSetStatsDet
                                                .Where(x => x.Hour >= startDate && x.Hour <= endDate)
                                                .ToListAsync();

                        dbCtx.DbSetStatsDet.RemoveRange(items);
                        await dbCtx.SaveChangesAsync();
                    }
                }

                await dbCtx
                            .DbSetStatsDet
                            .AddRangeAsync(listRecords);

                // salvo!
                answ = await dbCtx.SaveChangesAsync();
                // commit transazione
                await tx.CommitAsync();

                // libero memoria del changeTracker
                dbCtx.ChangeTracker.Clear();
                return answ;
            }
            catch
            {
                await tx.RollbackAsync();
                throw;
            }
        }
#endif

        #region Protected Fields

        protected static NLog.Logger Log = LogManager.GetCurrentClassLogger();

        #endregion Protected Fields
    }
}