mapo-core/MP.Data/Controllers/MpIocController.cs

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using MP.Data.DbModels;
using MP.Data.DbModels.Anag;
using NLog;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using static MP.Core.Objects.Enums;

namespace MP.Data.Controllers
{
    public class MpIocController
    {
        #region Public Constructors

        public MpIocController(IConfiguration configuration)
        {
            _configuration = configuration;
            string connStr = _configuration.GetConnectionString("MP.Data");
            options = new DbContextOptionsBuilder<MoonProContext>()
                                .UseSqlServer(connStr)
                                .Options;
            Log.Info("Avviata classe MpIocController");
        }

        #endregion Public Constructors

        #region Public Methods

        /// <summary>
        /// Insert record allarme
        /// </summary>
        /// <param name="dtRif">Data evento</param>
        /// <param name="machineId">Id macchina</param>
        /// <param name="memAddress">area memoria</param>
        /// <param name="memAddress">indice memoria</param>
        /// <param name="memAddress">valore status</param>
        /// <param name="memAddress">valore decodificato</param>
        /// <returns></returns>
        public async Task<bool> AlarmLogInsertAsync(DateTime dtRif, string machineId, string memAddress, int memIndex, int statusVal, string valDecoded)
        {
            using var dbCtx = new MoonProContext(options);

            var DtRif = new SqlParameter("@DtRif", dtRif);
            var MachineId = new SqlParameter("@MachineId", machineId);
            var MemAddress = new SqlParameter("@MemAddress", memAddress);
            var MemIndex = new SqlParameter("@MemIndex", memIndex);
            var StatusVal = new SqlParameter("@StatusVal", statusVal);
            var ValDecoded = new SqlParameter("@ValDecoded", valDecoded);
            var result = await dbCtx
                            .Database
                            .ExecuteSqlRawAsync("EXEC stp_AL_insertQuery @DtRif, @MachineId, @MemAddress, @MemIndex, @StatusVal, @ValDecoded, ", DtRif, MachineId, MemAddress, MemIndex, StatusVal, ValDecoded);
            return result != 0; ;
        }

        /// <summary>
        /// Restituisce l'anagrafica STATI per intero
        /// </summary>
        /// <returns></returns>
        public async Task<List<AnagStatiModel>> AnagStatiGetAllAsync()
        {
            using var dbCtx = new MoonProContext(options);

            var dbResult = await dbCtx
                               .DbSetAnagStati
                               .AsNoTracking()
                               .ToListAsync();
            return dbResult;
        }

        /// <summary>
        /// Restitusice elenco ultimi articoli per macchina
        /// </summary>
        /// <param name="tipo"></param>
        /// <param name="azienda"></param>
        /// </summary>
        /// <returns></returns>
        public async Task<List<AnagArticoliModel>> ArticoliGetLastByMaccAsync(string idxMacc)
        {
            using var dbCtx = new MoonProContext(options);

            var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacc);
            var dbResult = await dbCtx
                                 .DbSetArticoli
                                 .FromSqlRaw("exec dbo.stp_ART_getLastByMacch @idxMacchina", IdxMacchina)
                                 .AsNoTracking()
                                 .ToListAsync();
            return dbResult;
        }

        /// <summary>
        /// Apre in automatico un nuovo PODL/ODL chiudendo l'attuale (aperto)
        /// </summary>
        /// <param name="idxOdl">Idx ODL corrente</param>
        /// <param name="MatrOpr">Matricola operatore</param>
        /// <param name="idxMacchina">idx macchina da confermare</param>
        /// <param name="tCRich">TempoCiclo richiesto in attrezzaggio</param>
        /// <param name="pzPallet"># pz pallet</param>
        /// <param name="note">note ODL</param>
        /// <param name="startNewOdl">bool per avvio nuovo ODL (def: true)</param>
        /// <param name="qtyRich">Qty da produrre, deve essere >0</param>
        /// <param name="keyRich">KeyRich esterno, se vuoto uso vecchia, se KIT sovrascritto con KeyKit</param>
        /// <returns></returns>
        public async Task<bool> AutoStartOdlAsync(int idxOdl, int MatrOpr, string idxMacchina, decimal tCRich, int pzPallet, string note, bool startNewOdl, int qtyRich, string keyRich)
        {
            using var dbCtx = new MoonProContext(options);

            var IdxOdl = new SqlParameter("@idxOdl ", idxOdl);
            var MatrApp = new SqlParameter("@MatrApp ", MatrOpr);
            var IdxMacchina = new SqlParameter("@idxMacchina", idxMacchina);
            var TCRich = new SqlParameter("@TCRichAttr", tCRich);
            var PzPallet = new SqlParameter("@PzPallet", pzPallet);
            var Note = new SqlParameter("@Note", note);
            var StartNewOdl = new SqlParameter("@StartNewOdl", startNewOdl);
            var QtyRich = new SqlParameter("@QtyRich", qtyRich);
            var KeyRichiesta = new SqlParameter("@KeyRichiesta", keyRich);
            var result = await dbCtx
                            .Database
                            .ExecuteSqlRawAsync("EXEC stp_ODL_AutoStart @idxOdl, @MatrApp, @idxMacchina, @TCRichAttr, @PzPallet, @Note, @StartNewOdl, @QtyRich, @KeyRichiesta", IdxOdl, MatrApp, IdxMacchina, TCRich, PzPallet, Note, StartNewOdl, QtyRich, KeyRichiesta);
            return result != 0;
        }

        /// <summary>
        /// Processing intera catena eventi verifica cambio stato in singola transazione e con unico DbContext
        /// </summary>
        /// <param name="tipoInput"></param>
        /// <param name="IdxMacchina"></param>
        /// <param name="InizioStato"></param>
        /// <param name="IdxTipo"></param>
        /// <param name="CodArt"></param>
        /// <param name="Value"></param>
        /// <param name="MatrOpr"></param>
        /// <param name="pallet"></param>
        /// <returns></returns>
        public async Task<bool> CheckCambiaStatoBatchAsync(tipoInputEvento tipoInput, string IdxMacchina, DateTime InizioStato, int IdxTipo, string CodArt, string Value, int MatrOpr, string pallet)
        {
            await using var dbCtx = new MoonProContext(options);
            //await using var tx = await dbCtx.Database.BeginTransactionAsync();

            try
            {
                var pIdxMacchina = new SqlParameter("@IdxMacchina", IdxMacchina);
                var pIdxTipo = new SqlParameter("@IdxTipo", IdxTipo);
                var pInizioStato = new SqlParameter("@InizioStato", InizioStato);
                var pCodArticolo = new SqlParameter("@codArticolo", CodArt);
                var pValue = new SqlParameter("@Value", Value);
                var pMatrOpr = new SqlParameter("@MatrOpr", MatrOpr);
                var pPallet = new SqlParameter("@pallet", pallet);

                string opType = tipoInput switch
                {
                    tipoInputEvento.barcode => "BARCODE",
                    tipoInputEvento.hw => "HW",
                    _ => "ND"
                };

                TransizioneStatiModel? rigaTrans = null;

                if (opType != "ND")
                {
                    string sql = tipoInput switch
                    {
                        tipoInputEvento.barcode => "EXEC dbo.stp_TS_getUserForcedTrans @IdxMacchina, @IdxTipo",
                        tipoInputEvento.hw => "EXEC dbo.stp_TS_getByIdxMacchIdxTipoEv @IdxMacchina, @IdxTipo",
                        _ => string.Empty
                    };

                    // ✅ prima chiamata ASYNC
                    rigaTrans = (await dbCtx.DbSetSMES
                                        .FromSqlRaw(sql, pIdxMacchina, pIdxTipo)
                                        .AsNoTracking()
                                        .ToListAsync()
                                        ).FirstOrDefault();
                }

                if (rigaTrans != null && rigaTrans.IdxStato != rigaTrans.next_IdxStato)
                {
                    var pIdxStato = new SqlParameter("@IdxStato", rigaTrans.next_IdxStato);

                    await dbCtx.Database.ExecuteSqlRawAsync(
                        "EXEC dbo.stp_DDB_InsStatoBatch @IdxMacchina, @InizioStato, @IdxStato, @codArticolo, @Value, @MatrOpr, @pallet",
                        pIdxMacchina, pInizioStato, pIdxStato, pCodArticolo, pValue, pMatrOpr, pPallet);

                    // eseguo solo se evento manuale/barcode
                    if (tipoInput == tipoInputEvento.barcode)
                    {
                        var pMaxAgeSec = new SqlParameter("@maxAgeSec", 0);
                        await dbCtx.Database.ExecuteSqlRawAsync(
                            "EXEC stp_MSE_recalc @maxAgeSec, @idxMacchina",
                            pMaxAgeSec, pIdxMacchina);
                    }
                }
                else
                {
                    Log.Debug($"Nessun cambio stato richiesto | {opType} | {IdxMacchina} | {IdxTipo}");
                }

                // Nessuna eccezione = successo
                //await tx.CommitAsync();
                return true;
            }
            catch (Exception ex)
            {
                //await tx.RollbackAsync();
                // Log dettagliato errore
                Log.Error(ex, $"Errore in CheckCambiaStatoBatchAsync: {IdxMacchina} | {tipoInput}");
                throw; // O return false; se il chiamante gestisce fallimenti
            }
        }

        /// <summary>
        /// Effettua conferma prod macchina dell'intero periodo da confermare (ultima conferma - dtEvent)
        /// </summary>
        /// <param name="idxMacchina">idx macchina da confermare</param>
        /// <param name="modoConfProd">0=periodo, 1 = giorno, 2 = turno</param>
        /// <param name="numPzConfermati">qta pezzi BUONI da confermare</param>
        /// <param name="numPzScarto">qta pezzi SCARTO da confermare</param>
        /// <param name="DataOraApp">DataOra in cui registrare approvazione</param>
        /// <param name="MatrOpr">Matricola operatore</param>
        /// <returns></returns>
        public async Task<bool> ConfermaProdMacchinaAsync(string idxMacchina, int modoConfProd, int numPzConfermati, int numPzScarto, DateTime DataOraApp, int MatrOpr)
        {
            using var dbCtx = new MoonProContext(options);

            var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
            var DataOra = new SqlParameter("@DataOra ", DateTime.Now);
            var rigaProd = (await dbCtx
                        .DbSetStatoProd
                        .FromSqlRaw("EXEC stp_StatoProd_getByMacchina @IdxMacchina, @DataOra ", IdxMacchina, DataOra)
                        .AsNoTracking()
                        .ToListAsync())
                        .FirstOrDefault();

            //var rigaProd = await StatoProdMacchinaAsync(idxMacchina, DateTime.Now);
            //var IdxMacchina = new SqlParameter("@idxMacchina", idxMacchina);

            var MatrApp = new SqlParameter("@MatrApp ", MatrOpr);
            var DataFrom = new SqlParameter("@dataFrom ", rigaProd.DataFrom);
            var DataTo = new SqlParameter("@dataTo", DataOraApp);
            var PezziConf = new SqlParameter("@pezziConf", numPzConfermati);
            var PezziScar = new SqlParameter("@pezziScar", numPzScarto);
            var TipoConf = new SqlParameter("@TipoConf", modoConfProd);
            var DtOraApp = new SqlParameter("@DataOraApp", DataOraApp);
            var TestConferma = new SqlParameter("@TestConferma", true);

            var result = await dbCtx
                            .Database
                            .ExecuteSqlRawAsync("EXEC stp_ConfermaProduzCompleta @idxMacchina, @MatrApp, @dataFrom, @dataTo, @pezziConf, @pezziScar, @TipoConf, @DataOraApp, @TestConferma ", IdxMacchina, MatrApp, DataFrom, DataTo, PezziConf, PezziScar, TipoConf, DtOraApp, TestConferma);

            return result != 0;
        }

        /// <summary>
        /// Effettua conferma prod macchina dell'intero periodo da confermare (ultima conferma - dtEvent)
        /// </summary>
        /// <param name="idxMacchina">idx macchina da confermare</param>
        /// <param name="modoConfProd">0=periodo, 1 = giorno, 2 = turno</param>
        /// <param name="numPzConfermati">qta pezzi BUONI da confermare</param>
        /// <param name="numPzLasciati">qta pezzi LASCIATI da confermare</param>
        /// <param name="numPzScarto">qta pezzi SCARTO da confermare</param>
        /// <param name="DataOraApp">DataOra in cui registrare approvazione</param>
        /// <param name="MatrOpr">Matricola operatore</param>
        /// <returns></returns>
        public async Task<bool> ConfermaProdMacchinaFullAsync(string idxMacchina, int modoConfProd, int numPzConfermati, int numPzLasciati, int numPzScarto, DateTime DataOraApp, int MatrOpr)
        {
            using var dbCtx = new MoonProContext(options);

            var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
            var DataOra = new SqlParameter("@DataOra ", DateTime.Now);
            var rigaProd = (await dbCtx
                        .DbSetStatoProd
                        .FromSqlRaw("EXEC stp_StatoProd_getByMacchina @IdxMacchina, @DataOra ", IdxMacchina, DataOra)
                        .AsNoTracking()
                        .ToListAsync())
                        .FirstOrDefault();

            //var rigaProd = await StatoProdMacchinaAsync(idxMacchina, DateTime.Now);
            //var IdxMacchina = new SqlParameter("@idxMacchina", idxMacchina);

            var MatrApp = new SqlParameter("@MatrApp ", MatrOpr);
            var DataFrom = new SqlParameter("@dataFrom ", rigaProd.DataFrom);
            var DataTo = new SqlParameter("@dataTo", DataOraApp);
            var PezziConf = new SqlParameter("@pezziConf", numPzConfermati);
            var PezziLasc = new SqlParameter("@pezziLasciati", numPzLasciati);
            var PezziScar = new SqlParameter("@pezziScar", numPzScarto);
            var TipoConf = new SqlParameter("@TipoConf", modoConfProd);
            var DtOraApp = new SqlParameter("@DataOraApp", DataOraApp);
            var TestConferma = new SqlParameter("@TestConferma", true);
            var Force = new SqlParameter("@Force", false);

            var result = await dbCtx
                            .Database
                            .ExecuteSqlRawAsync("EXEC stp_ConfermaProduzCompletaFull @idxMacchina, @MatrApp, @dataFrom, @dataTo, @pezziConf, @pezziLasciati, @pezziScar, @TipoConf, @DataOraApp, @TestConferma,@Force", IdxMacchina, MatrApp, DataFrom, DataTo, PezziConf, PezziLasc, PezziScar, TipoConf, DtOraApp, TestConferma, Force);

            return result != 0;
        }

        /// <summary>
        /// Record ConfFlux dato macchina (oppure tutti se vuoto)
        /// </summary>
        /// <param name="idxMacc"></param>
        /// <returns></returns>
        public async Task<List<ConfFluxModel>> ConfFluxFiltAsync(string idxMacc)
        {
            using var dbCtx = new MoonPro_FluxContext(_configuration);

            var query = dbCtx.DbSetConfFlux
                                .AsNoTracking()
                                .AsQueryable();

            if (!string.IsNullOrEmpty(idxMacc))
                query = query.Where(x => x.IdxMacchina == idxMacc);

            var dbResult = await query.ToListAsync();

            return dbResult;
        }

        /// <summary>
        /// Elenco da tabella Config
        /// </summary>
        /// <returns></returns>
        public async Task<List<ConfigModel>> ConfigGetAllAsync()
        {
            using var dbCtx = new MoonProContext(options);

            var dbResult = await dbCtx
                                .DbSetConfig
                                .AsNoTracking()
                                .OrderBy(x => x.Chiave)
                                .ToListAsync();

            return dbResult;
        }

        /// <summary>
        /// Update record config
        /// </summary>
        /// <returns></returns>
        public async Task<bool> ConfigUpdateAsync(ConfigModel updRec)
        {
            using var dbCtx = new MoonProContext(options);

            bool fatto = false;
            var dbResult = dbCtx
                      .DbSetConfig
                      .Where(x => x.Chiave == updRec.Chiave)
                      .FirstOrDefault();
            if (dbResult != null)
            {
                dbResult.Valore = updRec.Valore;
                fatto = dbCtx.SaveChanges() > 0;
            }

            return fatto;
        }

        /// <summary>
        /// Intera tab dati macchina
        /// </summary>
        /// <returns></returns>
        public async Task<List<DatiMacchineModel>> DatiMacchineGetAllAsync()
        {
            using var dbCtx = new MoonProContext(options);

            var dbResult = await dbCtx
                                     .DbSetDatiMacchine
                                     .AsNoTracking()
                                     .OrderBy(x => x.IdxMacchina)
                                     .ToListAsync();
            return dbResult;
        }

        /// <summary>
        /// Inserimento record in DDB
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="inizioStato"></param>
        /// <param name="idxStato"></param>
        /// <param name="codArt"></param>
        /// <param name="value"></param>
        /// <param name="matrOpr"></param>
        /// <param name="pallet"></param>
        /// <returns></returns>
        public async Task<bool> DDB_InsStatoBatchAsync(string idxMacchina, DateTime inizioStato, int idxStato, string codArt, string value, int matrOpr, string pallet)
        {
            using var dbCtx = new MoonProContext(options);

            var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
            var InizioStato = new SqlParameter("@InizioStato", inizioStato);
            var IdxStato = new SqlParameter("@IdxStato", idxStato);
            var CodArticolo = new SqlParameter("@codArticolo", codArt);
            var Value = new SqlParameter("@Value", value);
            var MatrOpr = new SqlParameter("@MatrOpr", matrOpr);
            var Pallet = new SqlParameter("@pallet", pallet);
            var result = await dbCtx
                                .Database
                                .ExecuteSqlRawAsync("exec dbo.stp_DDB_InsStatoBatch @IdxMacchina, @InizioStato, @IdxStato, @codArticolo, @Value, @MatrOpr, @pallet", IdxMacchina, InizioStato, IdxStato, CodArticolo, Value, MatrOpr, Pallet);

            return result > 0;
        }

        /// <summary>
        /// Elenco tabella decodifica articoli / codice decimale
        /// </summary>
        /// <param name="codArt">Vuoto = tutti / Singolo CodArt</param>
        /// <returns></returns>
        public async Task<List<DecNumArticoliModel>> DecNumArtGetFiltAsync(string codArt = "")
        {
            using var dbCtx = new MoonProContext(options);

            var query = dbCtx.DbSetDecNumArt
                                    .AsNoTracking()
                                    .AsQueryable();

            if (!string.IsNullOrEmpty(codArt))
                query = query.Where(x => x.CodArticolo == codArt);

            var dbResult = await query.ToListAsync();

            return dbResult;
        }

        /// <summary>
        /// Stored x recuperare ultimi dossier macchina
        /// </summary>
        /// <param name="idxMacc"></param>
        /// <returns></returns>
        public async Task<List<DossierModel>> DossGetLastByMaccAsync(string idxMacc)
        {
            List<DossierModel> dbResult = new();
            using (var dbCtx = new MoonPro_FluxContext(_configuration))
            {
                var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacc);
                dbResult = await dbCtx
                                 .DbSetDossiers
                                 .FromSqlRaw("exec dbo.stp_DOSS_getLastByMacch @idxMacchina", IdxMacchina)
                                 .AsNoTracking()
                                 .ToListAsync();
            }
            return dbResult;
        }

        /// <summary>
        /// Aggiunta record EventList
        /// </summary>
        /// <param name="newRec"></param>
        /// <returns></returns>
        public async Task<bool> EvListInsertAsync(EventListModel newRec)
        {
            using var dbCtx = new MoonProContext(options);

            dbCtx.DbSetEvList.Add(newRec);
            return await dbCtx.SaveChangesAsync() > 0;
        }

        /// <summary>
        /// Aggiunta record MicroStato + EventList
        /// </summary>
        /// <param name="newRecMsm"></param>
        /// <param name="newRecEv"></param>
        /// <returns></returns>
        public async Task<bool> EvListMicroStatoInsertAsync(MicroStatoMacchinaModel newRecMsm, EventListModel newRecEv)
        {
            // eseguo in transazione...
            await using var dbCtx = new MoonProContext(options);
            await using var tx = await dbCtx.Database.BeginTransactionAsync();

            try
            {
                // inizio con record microstato...
                var actRec = await dbCtx
                                    .DbSetMicroStatoMacc
                                    .FindAsync(newRecMsm.IdxMacchina);
                //.SingleOrDefaultAsync();
                if (actRec == null)
                {
                    dbCtx.DbSetMicroStatoMacc.Add(newRecMsm);
                }
                else
                {
                    actRec.IdxMicroStato = newRecMsm.IdxMicroStato;
                    actRec.InizioStato = newRecMsm.InizioStato;
                    actRec.Value = newRecMsm.Value;

                    // Update() allega l'entità e segna tutti i campi come Modified
                    dbCtx.DbSetMicroStatoMacc.Update(actRec);
#if false
                    dbCtx.Entry(actRec).State = EntityState.Modified;
#endif
                }

                // ora record EVList
                dbCtx.DbSetEvList.Add(newRecEv);

                // EF Core 8 raggruppa automaticamente INSERT/UPDATE in un batch
                var rowsAffected = await dbCtx.SaveChangesAsync();
                await tx.CommitAsync();

                return rowsAffected > 0;
            }
            catch
            {
                await tx.RollbackAsync();
                throw;
            }
        }

        /// <summary>
        /// Chiamata x stored recupero FluxLog x macchina (first)
        /// </summary>
        /// <param name="idxMacc"></param>
        /// <param name="numMax"></param>
        /// <returns></returns>
        public async Task<List<FluxLogModel>> FluxLogFirstByMaccAsync(string idxMacc, int numMax)
        {
            List<FluxLogModel> dbResult = new();

            using var dbCtx = new MoonPro_FluxContext(_configuration);
            var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacc);
            var NumMax = new SqlParameter("@numMax", numMax);

            dbResult = await dbCtx
                             .DbSetFluxLog
                             .FromSqlRaw("exec dbo.stp_FL_getFirstByMacc @IdxMacchina, @numMax", IdxMacchina, NumMax)
                             .AsNoTracking()
                             .ToListAsync();
            return dbResult;
        }

        /// <summary>
        /// Elenco ultimi n record flux log dato macchina e flusso (ordinato x data registrazione)
        /// </summary>
        /// <param name="DtMax">Data massima x eventi</param>
        /// <param name="DtMin">Data minima x eventi</param>
        /// <param name="IdxMacchina">* = tutte, altrimenti solo x una data macchina</param>
        /// <param name="CodFlux">*=tutti, altrimenti solo selezionato</param>
        /// <param name="MaxRec">numero massimo record da restituire</param>
        /// <returns></returns>
        public async Task<List<FluxLogModel>> FluxLogGetLastFiltAsync(DateTime DtMax, DateTime DtMin, string IdxMacchina, string CodFlux, int MaxRec)
        {
            List<FluxLogModel> dbResult = new List<FluxLogModel>();
            using var dbCtx = new MoonPro_FluxContext(_configuration);

            dbResult = await dbCtx
                                .DbSetFluxLog
                                .AsNoTracking()
                                .Where(x => (x.dtEvento >= DtMin && x.dtEvento <= DtMax) && (IdxMacchina == "*" || x.IdxMacchina == IdxMacchina) && (CodFlux == "*" || x.CodFlux == CodFlux))
                                .OrderByDescending(x => x.dtEvento)
                                .Take(MaxRec)
                                .ToListAsync();
            return dbResult;
        }

        /// <summary>
        /// Aggiunta record FluxLog
        /// </summary>
        /// <param name="newRec"></param>
        /// <returns></returns>
        public async Task<bool> FluxLogInsertAsync(FluxLogModel newRec)
        {
            bool fatto = false;
            using var dbCtx = new MoonPro_FluxContext(_configuration);

            var currRec = dbCtx
                                .DbSetFluxLog
                                .Add(newRec);
            await dbCtx.SaveChangesAsync();

            return fatto;
        }

        /// <summary>
        /// Stored x eseguire Snapshot FluxLog (= Dossier) dato periodo
        /// </summary>
        /// <param name="idxMacc"></param>
        /// <returns></returns>
        public async Task<bool> FluxLogTakeSnapshotLastAsync(string idxMacc, DateTime dataInizio, DateTime dataFine)
        {
            bool fatto = false;
            using var dbCtx = new MoonPro_FluxContext(_configuration);

            var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacc);
            var DataInizio = new SqlParameter("@DtMin", dataInizio);
            var DataFine = new SqlParameter("@DtMax", dataFine);

            var result = await dbCtx
                            .Database
                            .ExecuteSqlRawAsync("EXEC stp_FL_TakeSnapshotLast @IdxMacchina, @DtMin, @DtMax", IdxMacchina, DataInizio, DataFine);
            fatto = result > 0;
            return fatto;
        }

        /// <summary>
        /// Upsert record keepalive
        /// </summary>
        /// <param name="IdxMacc"></param>
        /// <param name="OraServer"></param>
        /// <param name="OraMacc"></param>
        /// <returns></returns>
        public async Task<bool> KeepAliveUpsertAsync(string IdxMacc, DateTime OraServer, DateTime OraMacc)
        {
            bool fatto = false;
            using var dbCtx = new MoonProContext(options);

            var currRec = await dbCtx
                            .DbSetKeepAlive
                            .Where(x => x.IdxMacchina == IdxMacc)
                            .FirstOrDefaultAsync();
            if (currRec != null)
            {
                currRec.DataOraServer = OraServer;
                currRec.DataOraMacchina = OraMacc;
                dbCtx.Entry(currRec).State = EntityState.Modified;
            }
            else
            {
                KeepAliveModel newRec = new KeepAliveModel()
                {
                    IdxMacchina = IdxMacc,
                    DataOraMacchina = OraMacc,
                    DataOraServer = OraServer,
                    DataOraStart = DateTime.Now
                };
                dbCtx
                    .DbSetKeepAlive
                    .Add(newRec);
            }
            fatto = await dbCtx.SaveChangesAsync() > 0;

            return fatto;
        }

        public async Task<List<LinkMenuModel>> ListLinkFiltAsync(string tipoLink)
        {
            List<LinkMenuModel> dbResult = new List<LinkMenuModel>();
            using var dbCtx = new MoonProContext(options);

            dbResult = await dbCtx
                                .DbSetLinkMenu
                                .Where(x => x.TipoLink == tipoLink)
                                .AsNoTracking()
                                .OrderBy(x => x.Ordine)
                                .ToListAsync();
            return dbResult;
        }

        /// <summary>
        /// Elenco valori ammessi x tabella/colonna con filtro parametrico
        /// </summary>
        /// <param name="tabName">Filtro tabella (se "" tutto)</param>
        /// <param name="fieldName">Filtro colonna (se "" tutto)</param>
        /// <returns></returns>
        public async Task<List<ListValuesModel>> ListValuesFiltAsync(string tabName, string fieldName)
        {
            List<ListValuesModel> dbResult = new List<ListValuesModel>();
            using var dbCtx = new MoonProContext(options);

            var query = dbCtx
                            .DbSetListValues
                            .AsNoTracking()
                            .AsQueryable();

            if (!string.IsNullOrEmpty(tabName))
                query = query.Where(x => x.TableName == tabName);
            if (!string.IsNullOrEmpty(fieldName))
                query = query.Where(x => x.FieldName == fieldName);

            dbResult = await query.ToListAsync();

            return dbResult;
        }

        /// <summary>
        /// Intera tabella relazione master/slave in machine (gestione setup master - slave)
        /// </summary>
        /// <returns></returns>
        public async Task<List<Macchine2SlaveModel>> Macchine2SlaveAsync()
        {
            List<Macchine2SlaveModel> dbResult = new List<Macchine2SlaveModel>();
            using var dbCtx = new MoonProContext(options);

            dbResult = await dbCtx
                                .DbSetM2S
                                .AsNoTracking()
                                .OrderBy(x => x.IdxMacchina)
                                .ToListAsync();

            return dbResult;
        }

        /// <summary>
        /// Elenco Record Macchine
        /// </summary>
        /// <returns></returns>
        public async Task<List<MacchineModel>> MacchineGetAllAsync()
        {
            List<MacchineModel> dbResult = new List<MacchineModel>();
            using var dbCtx = new MoonProContext(options);

            dbResult = await dbCtx
                            .DbSetMacchine
                            .ToListAsync();

            return dbResult;
        }

        public async Task<MacchineModel?> MacchineGetByIdxAsync(string IdxMacchina)
        {
            MacchineModel dbResult = null;
            using var dbCtx = new MoonProContext(options);

            dbResult = await dbCtx
                            .DbSetMacchine
                            .FirstOrDefaultAsync(x => x.IdxMacchina == IdxMacchina);

            return dbResult;
        }

        /// <summary>
        /// Elenco da tabella Macchine
        /// </summary>
        /// <param name="codGruppo"></param>
        /// <returns></returns>
        public async Task<List<MacchineModel>> MacchineGetFiltAsync(string codGruppo)
        {
            List<MacchineModel> dbResult = new List<MacchineModel>();
            using var dbCtx = new MoonProContext(options);

            if (codGruppo == "*")
            {
                dbResult = await dbCtx
                                .DbSetMacchine
                                .AsNoTracking()
                                .OrderBy(x => x.IdxMacchina)
                                .ToListAsync();
            }
            else
            {
                dbResult = await dbCtx
                                .DbSetGrp2Macc
                                .Where(g => g.CodGruppo == codGruppo)
                                .Join(dbCtx.DbSetMacchine,
                                    g => g.IdxMacchina,
                                    m => m.IdxMacchina,
                                    (g, m) => m
                                )
                                .AsNoTracking()
                                .OrderBy(x => x.IdxMacchina)
                                .ToListAsync();
            }

            return dbResult;
        }

        /// <summary>
        /// Upsert Record Macchine ASYNC
        /// </summary>
        /// <returns></returns>
        public async Task<bool> MacchineUpsertAsync(MacchineModel entity)
        {
            using var dbCtx = new MoonProContext(options);

            // Recuperiamo l'entità tracciata dal context
            var trackedEntity = await dbCtx
                                            .DbSetMacchine
                                            .FirstOrDefaultAsync(x => x.IdxMacchina == entity.IdxMacchina);

            if (trackedEntity != null)
            {
                // Aggiorna i valori dell'entità tracciata con quelli della nuova
                dbCtx.Entry(trackedEntity).CurrentValues.SetValues(entity);
            }
            else
            {
                dbCtx.DbSetMacchine.Update(entity);
            }
            bool fatto = await dbCtx.SaveChangesAsync() > 0;
            return fatto;
        }

        /// <summary>
        /// Elenco da tabella Macchine
        /// </summary>
        /// <param name="IdxMacc"></param>
        /// <returns></returns>
        public async Task<List<MicroStatoMacchinaModel>> MicroStatoMacchinaGetByIdxMaccAsync(string IdxMacc)
        {
            List<MicroStatoMacchinaModel> dbResult = new List<MicroStatoMacchinaModel>();
            using var dbCtx = new MoonProContext(options);

            dbResult = await dbCtx
                                .DbSetMicroStatoMacc
                                .Where(x => x.IdxMacchina == IdxMacc)
                                .AsNoTracking()
                                .ToListAsync();
            return dbResult;
        }

        /// <summary>
        /// Aggiornamento record Microstato macchina
        /// </summary>
        /// <param name="newRec"></param>
        /// <returns></returns>
        public async Task<bool> MicroStatoMacchinaUpsertAsync(MicroStatoMacchinaModel newRec)
        {
            bool fatto = false;
            using var dbCtx = new MoonProContext(options);

            var actRec = await dbCtx
                                .DbSetMicroStatoMacc
                                .Where(x => x.IdxMacchina == newRec.IdxMacchina)
                                .AsNoTracking()
                                .FirstOrDefaultAsync();
            if (actRec == null)
            {
                dbCtx
                    .DbSetMicroStatoMacc
                    .Add(newRec);
            }
            else
            {
                actRec.IdxMicroStato = newRec.IdxMicroStato;
                actRec.InizioStato = newRec.InizioStato;
                actRec.Value = newRec.Value;

                dbCtx.Entry(actRec).State = EntityState.Modified;
            }
            fatto = await dbCtx.SaveChangesAsync() > 0;

            return fatto;
        }

        /// <summary>
        /// Elenco da tabella MappaStatoExplModel
        /// </summary>
        /// <returns></returns>
        public async Task<List<MappaStatoExplModel>> MseGetAllAsync(int maxAge = 2000)
        {
            List<MappaStatoExplModel> dbResult = new List<MappaStatoExplModel>();
            using var dbCtx = new MoonProContext(options);

            var maxAgeSec = new SqlParameter("@maxAgeSec", maxAge);

            dbResult = await dbCtx
                            .DbSetMSE
                            .FromSqlRaw("EXEC stp_MSE_getData @maxAgeSec", maxAgeSec)
                            .AsNoTracking()
                            .ToListAsync();

            return dbResult;
        }

        /// <summary>
        /// Generazione automatica ODL
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <returns></returns>
        public async Task<bool> OdlAutoDayGenAsync(string idxMacchina, DateTime dataInizio, DateTime dataFine, string codArticolo)
        {
            using var dbCtx = new MoonProContext(options);

            var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
            var DataInizio = new SqlParameter("@DataInizio", dataInizio);
            var DataFine = new SqlParameter("@DataFine", dataFine);
            var CodArticolo = new SqlParameter("@codArticolo", codArticolo);

            var result = await dbCtx
                                .Database
                                .ExecuteSqlRawAsync("EXEC stp_ODL_AutoDayGener @IdxMacchina, @DataInizio, @DataFine, @codArticolo", IdxMacchina, DataInizio, DataFine, CodArticolo);

            // indico eseguito!
            return result > 0;
        }

        /// <summary>
        /// Generazione automatica ODL completa
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <returns></returns>
        public async Task<bool> OdlAutoDayGenFullAsync(string idxMacchina, DateTime dataInizio, DateTime dataFine, string codArticolo, int? pzPODL, int? pzPallet, string? keyRichiesta, int? tcAssegnato, string? codGruppo, bool flgCreaPODL, bool flgCheckTC)
        {
            using var dbCtx = new MoonProContext(options);

            var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
            var DataInizio = new SqlParameter("@DataInizio", dataInizio);
            var DataFine = new SqlParameter("@DataFine", dataFine);
            var CodArticolo = new SqlParameter("@codArticolo", codArticolo);
            var PzPODL = new SqlParameter("@PzPODL", pzPODL);
            var PzPallet = new SqlParameter("@PzPallet", pzPallet);
            var KeyRichiesta = new SqlParameter("@KeyRichiesta", keyRichiesta);
            var TCAssegnato = new SqlParameter("@TCAssegnato", tcAssegnato);
            var CodGruppo = new SqlParameter("@codGruppo", codGruppo);
            var FlgCreaPODL = new SqlParameter("@flgCreaPODL", flgCreaPODL);
            var FlgCheckTC = new SqlParameter("@flgCheckTC", flgCheckTC);

            var result = await dbCtx
                            .Database
                            .ExecuteSqlRawAsync("EXEC stp_ODL_AutoDayGenerFull @IdxMacchina, @DataInizio, @DataFine, @codArticolo, @PzPODL, @PzPallet, @KeyRichiesta, @TCAssegnato, @codGruppo, @flgCreaPODL, @flgCheckTC", IdxMacchina, DataInizio, DataFine, CodArticolo, PzPODL, PzPallet, KeyRichiesta, TCAssegnato, CodGruppo, FlgCreaPODL, FlgCheckTC);

            return result > 0;
        }

        /// <summary>
        /// ODL corrente macchina
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <returns></returns>
        public async Task<ODLExpModel> OdlCurrByMaccAsync(string idxMacchina)
        {
            ODLExpModel answ = new();
            using var dbCtx = new MoonProContext(options);

            var pIdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
            // attenzione: se la stored resituisce una tabella, il primo elemento va recuperato in RAM!!!
            var dbRes = (await dbCtx.DbSetODLExp
                 .FromSqlRaw("EXEC stp_ODL_getByMacchina @IdxMacchina", pIdxMacchina)
                 .AsNoTracking()
                 .ToListAsync()) // Esegue la query e scarica i risultati in memoria
                 .FirstOrDefault(); // Prende il primo elemento dalla lista in RAM
            answ = dbRes ?? new();

            return answ;
        }

        /// <summary>
        /// Fix ODL per macchine SLAVE
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="numDayPrev"></param>
        /// <param name="doInsert"></param>
        /// <returns></returns>
        public async Task<bool> OdlFixMachineSlave(string idxMacchina, int numDayPrev, int doInsert)
        {
            using var dbCtx = new MoonProContext(options);

            var idxMaccParam = new SqlParameter("@IdxMacchina", idxMacchina ?? "");
            var numDayPrevParam = new SqlParameter("@NumDayPrev", numDayPrev);
            var doInsertParam = new SqlParameter("@DoInsert", doInsert);

            var result = await dbCtx
                                .Database
                                .ExecuteSqlRawAsync("EXEC stp_ODL_fixMachineSlave @IdxMacchina, @NumDayPrev, @DoInsert", idxMaccParam, numDayPrevParam, doInsertParam);

            return result != -1;
        }

        /// <summary>
        /// Fix ODL per macchine SLAVE Async
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="numDayPrev"></param>
        /// <param name="doInsert"></param>
        /// <returns></returns>
        public async Task<bool> OdlFixMachineSlaveAsync(string idxMacchina, int numDayPrev, int doInsert)
        {
            using var dbCtx = new MoonProContext(options);

            var IdxMacc = new SqlParameter("@IdxMacchina", idxMacchina);
            var NumDayPrev = new SqlParameter("@NumDayPrev", numDayPrev);
            var DoInsert = new SqlParameter("@DoInsert", doInsert);
            var result = await dbCtx
                        .Database
                        .ExecuteSqlRawAsync("EXEC stp_ODL_fixMachineSlave @IdxMacchina, @NumDayPrev, @DoInsert", IdxMacc, NumDayPrev, DoInsert);
            return result != 0;
        }

        /// <summary>
        /// ODL corrente macchina
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <returns></returns>
        public async Task<ODLExpModel> OdlLastByMaccAsync(string idxMacchina)
        {
            ODLExpModel answ = new();
            using var dbCtx = new MoonProContext(options);

            var pIdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
            // attenzione: se la stored resituisce una tabella, il primo elemento va recuperato in RAM!!!
            var dbRes = (await dbCtx.DbSetODLExp
                 .FromSqlRaw("EXEC stp_ODL_getLastByMacchina @IdxMacchina", pIdxMacchina)
                 .AsNoTracking()
                 .ToListAsync()) // Esegue la query e scarica i risultati in memoria
                 .FirstOrDefault(); // Prende il primo elemento dalla lista in RAM
            answ = dbRes ?? new();

            return answ;
        }

        /// <summary>
        /// Elenco ODL data macchina e periodo
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="dtStart"></param>
        /// <param name="dtEnd"></param>
        /// <returns></returns>
        public async Task<List<ODLExpModel>> OdlListByMaccPeriodoAsync(string idxMacchina, DateTime dtStart, DateTime dtEnd)
        {
            List<ODLExpModel> dbResult = new List<ODLExpModel>();
            using var dbCtx = new MoonProContext(options);

            var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
            var DataFrom = new SqlParameter("@dataFrom", dtStart);
            var DataTo = new SqlParameter("@dataTo", dtEnd);
            dbResult = await dbCtx
                            .DbSetODLExp
                            .FromSqlRaw("EXEC stp_ODL_getByMacchinaPeriodo @IdxMacchina, @dataFrom, @dataTo", IdxMacchina, DataFrom, DataTo)
                            .AsNoTracking()
                            .ToListAsync();

            return dbResult;
        }

        /// <summary>
        /// Conteggio PzProd Macchina Async
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <returns></returns>
        public async Task<PzProdModel> PezziProdMacchinaAsync(string idxMacchina)
        {
            PzProdModel dbResult = new PzProdModel();
            using var dbCtx = new MoonProContext(options);

            var pIdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
            dbResult = (await dbCtx
                                .DbSetPzProd
                                .FromSqlRaw("EXEC stp_PzProd_getByMacchina @IdxMacchina", pIdxMacchina)
                                .AsNoTracking()
                                .ToListAsync())
                                .FirstOrDefault(); // recupero da RAM
            return dbResult;
        }

        /// <summary>
        /// Recupero elenco PODL EXPL filtrati x macchina, articolo...
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="codArticolo"></param>
        /// <param name="codGruppo"></param>
        /// <param name="onlyFree">True = aperti (=senza ODL)</param>
        /// <returns></returns>
        public async Task<List<PODLExpModel>> POdlGetByMaccArtAsync(string idxMacchina, string codArticolo, string codGruppo, bool onlyFree)
        {
            List<PODLExpModel> dbResult = new List<PODLExpModel>();
            using var dbCtx = new MoonProContext(options);

            var pIdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
            var pCodArticolo = new SqlParameter("@CodArticolo", codArticolo);
            var pCodGruppo = new SqlParameter("@CodGruppo", codGruppo);
            var pOnlyFree = new SqlParameter("@onlyFree", onlyFree);

            dbResult = await dbCtx
                            .DbSetPODLExp
                            .FromSqlRaw("EXEC stp_PODL_getByMaccArt @IdxMacchina, @CodArticolo, @CodGruppo, @onlyFree", pIdxMacchina, pCodArticolo, pCodGruppo, pOnlyFree)
                            .AsNoTracking()
                            .ToListAsync();
            return dbResult;
        }

        /// <summary>
        /// Effettua ricalcolo MSE x macchina indicata
        /// </summary>
        /// <param name="idxMacchina">idx macchina da confermare</param>
        /// <param name="maxAgeSec">Num massimo secondi di "vecchiaia" del dato</param>
        /// <returns></returns>
        public async Task<bool> RecalcMseAsync(string idxMacchina, int maxAgeSec)
        {
            using var dbCtx = new MoonProContext(options);

            var rigaProd = await StatoProdMacchinaAsync(idxMacchina, DateTime.Now);
            var MaxAgeSec = new SqlParameter("@maxAgeSec ", maxAgeSec);
            var IdxMacchina = new SqlParameter("@idxMacchina", idxMacchina);

            var result = await dbCtx
                            .Database
                            .ExecuteSqlRawAsync("EXEC stp_MSE_recalc @maxAgeSec, @idxMacchina ", MaxAgeSec, IdxMacchina);

            return result != 0;
        }

        /// <summary>
        /// Registra controllo
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="matrOpr"></param>
        /// <param name="esitoOk"></param>
        /// <param name="note"></param>
        /// <param name="dataOra"></param>
        /// <returns></returns>
        public async Task<bool> RegControlliInsertAsync(string idxMacchina, int matrOpr, bool esitoOk, string note, DateTime dataOra)
        {
            using var dbCtx = new MoonProContext(options);

            var IdxMacc = new SqlParameter("@IdxMacchina", idxMacchina);
            var MatrOpr = new SqlParameter("@MatrOpr", matrOpr);
            var EsitoOk = new SqlParameter("@EsitoOk", esitoOk);
            var Note = new SqlParameter("@Note", note);
            var DataOra = new SqlParameter("@DataOra", dataOra);

            var result = await dbCtx
                            .Database
                            .ExecuteSqlRawAsync("EXEC stp_RC_insert @IdxMacchina, @MatrOpr, @EsitoOk, @Note, @DataOra", IdxMacc, MatrOpr, EsitoOk, Note, DataOra);
            return result != 0;
        }

        /// <summary>
        /// Aggiunta record Registro Dichiarazioni
        /// </summary>
        /// <param name="newRec"></param>
        /// <returns></returns>
        public async Task<bool> RegDichiarInsertAsync(RegistroDichiarazioniModel newRec)
        {
            using var dbCtx = new MoonProContext(options);

            var TagCode = new SqlParameter("@TagCode", newRec.TagCode);
            var IdxMacchina = new SqlParameter("@IdxMacchina", newRec.IdxMacchina);
            var DtRec = new SqlParameter("@DtRec", newRec.DtRec);
            var MatrOpr = new SqlParameter("@MatrOpr", newRec.MatrOpr);
            var ValString = new SqlParameter("@ValString", newRec.ValString);

            var result = await dbCtx
                             .Database
                             .ExecuteSqlRawAsync("exec dbo.stp_DD_insertQuery @TagCode, @IdxMacchina, @DtRec, @MatrOpr, @ValString", TagCode, IdxMacchina, DtRec, MatrOpr, ValString);

            return result != 0;
        }

        /// <summary>
        /// Update record Registro Dichiarazioni
        /// </summary>
        /// <param name="newRec"></param>
        /// <returns></returns>
        public async Task<bool> RegDichiarUpdateAsync(RegistroDichiarazioniModel newRec)
        {
            using var dbCtx = new MoonProContext(options);

            var Original_IdxDich = new SqlParameter("@Original_IdxDich", newRec.IdxDich);
            var DtRec = new SqlParameter("@DtRec", newRec.DtRec);
            var TagCode = new SqlParameter("@TagCode", newRec.TagCode);
            var ValString = new SqlParameter("@ValString", newRec.ValString);
            var MatrOpr = new SqlParameter("@MatrOpr", newRec.MatrOpr);
            var result = await dbCtx
                             .Database
                             .ExecuteSqlRawAsync("exec dbo.stp_DD_updateQuery @Original_IdxDich, @DtRec, @TagCode, @ValString, @MatrOpr", Original_IdxDich, DtRec, TagCode, ValString, MatrOpr);

            return result != 0;
        }

        /// <summary>
        /// Aggiunta record RegistroScarti
        /// </summary>
        /// <param name="newRec"></param>
        /// <returns></returns>
        public async Task<bool> RegScartiInsertAsync(RegistroScartiModel newRec)
        {
            using var dbCtx = new MoonProContext(options);

            var IdxMacchina = new SqlParameter("@idxMacchina", newRec.IdxMacchina);
            var DataOra = new SqlParameter("@DataOra", newRec.DataOra);
            var Causale = new SqlParameter("@Causale", newRec.Causale);
            var Qta = new SqlParameter("@Qta", newRec.Qta);
            var Note = new SqlParameter("@Note", newRec.Note);
            var MatrOpr = new SqlParameter("@MatrOpr", newRec.MatrOpr);

            var result = await dbCtx
                             .DbSetRegWithCheck
                             .FromSqlRaw("exec dbo.stp_RS_Insert_withCheck @idxMacchina, @DataOra, @Causale, @Qta, @Note, @MatrOpr", IdxMacchina, DataOra, Causale, Qta, Note, MatrOpr)
                             .AsNoTracking()
                             .ToListAsync();
            return result.Count != 0;
        }

        /// <summary>
        /// Aggiunta record RemoteRebootLog
        /// </summary>
        /// <param name="newRec">Record da inserire</param>
        /// <param name="doClean">Esecuzione stored di cleanup</param>
        /// <param name="num2keep">Parametro max num2keep x stored cleanup</param>
        /// <returns></returns>
        public async Task<bool> RemRebootLogAddAndCleanAsync(RemoteRebootLogModel newRec, bool doClean, int num2keep)
        {
            bool fatto = false;
            using var dbCtx = new MoonProContext(options);

            // 1. Transazione minima: SOLO INSERT + COMMIT
            await using var tx = await dbCtx.Database.BeginTransactionAsync();
            try
            {
                dbCtx.DbSetRemRebLog.Add(newRec);
                fatto = await dbCtx.SaveChangesAsync() > 0;
                await tx.CommitAsync(); // 🔑 Commit prima della pulizia
            }
            catch
            {
                await tx.RollbackAsync();
                throw;
            }

            // 2. Cleanup "smart" fuori transazione
            if (doClean && fatto)
            {
                try
                {
                    var param = new SqlParameter("@num2keep", num2keep);
                    await dbCtx.Database.ExecuteSqlRawAsync(
                        "EXEC dbo.stp_RRL_KeepLatest @num2keep, @multiplier", param,
                        new SqlParameter("@multiplier", 2.5f)); // Fisso e poiché è coinvolto MAC address è + alto
                }
                catch (Exception ex)
                {
                    // Log dell'errore, ma NON rilanciare: il record è già salvo
                    Log.Error(ex, "Failed to execute RRL cleanup for {Machine}", newRec.IdxMacchina);
                }
            }

            return fatto;
        }

        /// <summary>
        /// Aggiunta record RemoteRebootLog
        /// </summary>
        /// <param name="newRec"></param>
        /// <returns></returns>
        public async Task<bool> RemRebootLogAddAsync(RemoteRebootLogModel newRec)
        {
            using var dbCtx = new MoonProContext(options);

            var dbResult = dbCtx
                                 .DbSetRemRebLog
                                 .Add(newRec);

            return await dbCtx.SaveChangesAsync() > 0;
        }

        /// <summary>
        /// Recupera tutti i record di RemoteRebootLog
        /// </summary>
        /// <returns></returns>
        public async Task<List<RemoteRebootLogModel>> RemRebootLogGetAllAsync()
        {
            using var dbCtx = new MoonProContext(options);

            var dbResult = await dbCtx
                                .DbSetRemRebLog
                                .AsNoTracking()
                                .OrderByDescending(x => x.IdxReboot)
                                .ToListAsync();
            return dbResult;
        }

        /// <summary>
        /// Recupera ultimo record x ogni IdxMacchina x avere ultimo attivo
        /// </summary>
        /// <returns></returns>
        public async Task<List<RemoteRebootLogModel>> RemRebootLogGetLastAsync()
        {
            using var dbCtx = new MoonProContext(options);
            var dbResult = await dbCtx
                             .DbSetRemRebLog
                             .FromSqlRaw("EXEC stp_RRL_getLast")
                             .AsNoTracking()
                             .ToListAsync();
            return dbResult;
        }

        /// <summary>
        /// Recupera ultimo record x ogni IdxMacchina x avere ultimo attivo
        /// </summary>
        /// <returns></returns>
        public async Task<bool> RemRebootLogKeepLastAsync(int num2keep)
        {
            using var dbCtx = new MoonProContext(options);

            var pNum2Keep = new SqlParameter("@num2keep", num2keep);
            // La SP gestisce già la logica di soglia (1.5x), ma la specifico x sicurezza
            var pThresh = new SqlParameter("@threshMult", 1.5);
            var dbResult = await dbCtx.Database.ExecuteSqlRawAsync(
                "EXEC dbo.stp_RRL_KeepLatest @num2keep, @threshMult", pNum2Keep, pThresh);

            return dbResult != 0;
        }

        /// <summary>
        /// Aggiunta record SignalLog Async
        /// </summary>
        /// <param name="newRec"></param>
        /// <returns></returns>
        public async Task<bool> SignalLogInsertAsync(SignalLogModel newRec)
        {
            using var dbCtx = new MoonProContext(options);

            var currRec = dbCtx
                            .DbSetSignalLog
                            .Add(newRec);

            return await dbCtx.SaveChangesAsync() > 0;
        }

        /// <summary>
        /// Tabella state machine eventi 2 stati data macchina e tipo evento
        /// </summary>
        /// <param name="idxTipo"></param>
        /// <returns></returns>
        public async Task<List<TransizioneStatiModel>> SMES_getHwTransitionsAsync(string idxMacchina, int idxTipo)
        {
            using var dbCtx = new MoonProContext(options);

            var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
            var IdxTipo = new SqlParameter("@IdxTipo", idxTipo);
            var dbResult = await dbCtx
                              .DbSetSMES
                              .FromSqlRaw("exec dbo.stp_TS_getByIdxMacchIdxTipoEv @IdxMacchina, @IdxTipo", IdxMacchina, IdxTipo)
                              .AsNoTracking()
                              .ToListAsync();

            return dbResult;
        }

        /// <summary>
        /// Tabella state machine eventi 2 stati data macchina e tipo evento
        /// </summary>
        /// <param name="idxTipo"></param>
        /// <returns></returns>
        public async Task<List<TransizioneStatiModel>> SMES_getUserForcedAsync(string idxMacchina, int idxTipo)
        {
            await using var dbCtx = new MoonProContext(options);

            var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
            var IdxTipo = new SqlParameter("@IdxTipo", idxTipo);
            var dbResult = await dbCtx
                                .DbSetSMES
                                .FromSqlRaw("exec dbo.stp_TS_getUserForcedTrans @IdxMacchina, @IdxTipo", IdxMacchina, IdxTipo)
                                .AsNoTracking()
                                .ToListAsync();

            return dbResult;
        }

        /// <summary>
        /// Intera tabella state machine ingressi 2 eventi
        /// </summary>
        /// <returns></returns>
        public List<TransizioneIngressiModel> StateMachineIngressi(int idxFam)
        {
            List<TransizioneIngressiModel> dbResult = new List<TransizioneIngressiModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                var IdxFamIn = new SqlParameter("@IdxFamigliaIngresso", idxFam);
                dbResult = dbCtx
                            .DbSetSMI
                            .FromSqlRaw("exec dbo.stp_TRI_getByIdxFamIng @IdxFamigliaIngresso", IdxFamIn)
                            .AsNoTracking()
                            .AsEnumerable()
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Intera tabella state machine ingressi 2 eventi
        /// </summary>
        /// <returns></returns>
        public async Task<List<TransizioneIngressiModel>> StateMachineIngressiAsync(int idxFam)
        {
            using var dbCtx = new MoonProContext(options);

            var IdxFamIn = new SqlParameter("@IdxFamigliaIngresso", idxFam);
            var dbResult = await dbCtx
                                    .DbSetSMI
                                    .FromSqlRaw("exec dbo.stp_TRI_getByIdxFamIng @IdxFamigliaIngresso", IdxFamIn)
                                    .AsNoTracking()
                                    .ToListAsync();

            return dbResult;
        }

        /// <summary>
        /// Stato prod macchina (completo) Async
        /// </summary>
        /// <param name="idxMacchina"></param>
        /// <param name="dtReq"></param>
        /// <returns></returns>
        public async Task<StatoProdModel> StatoProdMacchinaAsync(string idxMacchina, DateTime dtReq)
        {
            using var dbCtx = new MoonProContext(options);

            var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacchina);
            var DataOra = new SqlParameter("@DataOra ", dtReq);
            var dbResult = (await dbCtx
                           .DbSetStatoProd
                           .FromSqlRaw("EXEC stp_StatoProd_getByMacchina @IdxMacchina, @DataOra ", IdxMacchina, DataOra)
                           .AsNoTracking()
                           .ToListAsync())
                           .FirstOrDefault();

            return dbResult;
        }

        /// <summary>
        /// Intera vista v_MSFD in modo ASYNC
        /// </summary>
        /// <returns></returns>
        public async Task<List<VMSFDModel>> VMSFDGetAllAsync()
        {
            using var dbCtx = new MoonProContext(options);

            var dbResult = await dbCtx
                               .DbSetMSFD
                               .AsNoTracking()
                               .OrderBy(x => x.IdxMacchina)
                               .ToListAsync();

            return dbResult;
        }

        /// <summary>
        /// Vista v_MSFD x singola macchina (da stored) - singolo record
        /// </summary>
        /// <param name="idxMacc"></param>
        /// <returns></returns>
        public async Task<VMSFDModel?> VMSFDGetByMaccAsync(string idxMacc)
        {
            using var dbCtx = new MoonProContext(options);

            var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacc);
            var dbResult = (await dbCtx
                            .DbSetMSFD
                            .FromSqlRaw("exec dbo.stp_MSFD_getMacc @IdxMacchina", IdxMacchina)
                            .AsNoTracking()
                            .ToListAsync())
                            .FirstOrDefault();

            return dbResult;
        }

        /// <summary>
        /// Vista v_MSFD delle machine MULTI filtrato x macchina (da stored)
        /// </summary>
        /// <param name="idxMacc"></param>
        /// <returns></returns>
        public async Task<List<VMSFDModel>> VMSFDGetMultiByMaccAsync(string idxMacc)
        {
            using var dbCtx = new MoonProContext(options);

            var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacc);

            var dbResult = await dbCtx
                            .DbSetMSFD
                            .FromSqlRaw("exec dbo.stp_MSFD_getMulti @IdxMacchina", IdxMacchina)
                            .AsNoTracking()
                            .ToListAsync();

            return dbResult;
        }

        #endregion Public Methods

        #region Private Fields

        private static IConfiguration _configuration;
        private static NLog.Logger Log = LogManager.GetCurrentClassLogger();
        private DbContextOptions<MoonProContext> options;

        #endregion Private Fields

#if false
        public void Dispose()
        {
            _configuration = null;
        }
#endif
    }
}