using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using MongoDB.Driver;
using MP.Data.DbModels;
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.Repository.IOC
{
    public class IocRepository : BaseRepository, IIocRepository
    {
        #region Public Constructors

        public IocRepository(IDbContextFactory<MoonProContext> ctxFactory) : base(ctxFactory)
        {
        }

        #endregion Public Constructors

        #region Public Methods

        /// <inheritdoc />
        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 = await CreateContextAsync();
            //await using var tx = await dbCtx.Database.BeginTransactionAsync(IsolationLevel.ReadCommitted);

            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
            }
        }

        /// <inheritdoc />
        public async Task<List<ConfigModel>> ConfigGetAllAsync()
        {
            await using var dbCtx = await CreateContextAsync();
            var dbResult = await dbCtx
                                .DbSetConfig
                                .AsNoTracking()
                                .OrderBy(x => x.Chiave)
                                .ToListAsync();
            return dbResult;
        }

        /// <inheritdoc />
        public async Task<List<DatiMacchineModel>> DatiMacchineGetAllAsync()
        {
            await using var dbCtx = await CreateContextAsync();
            var dbResult = await dbCtx
                                     .DbSetDatiMacchine
                                     .AsNoTracking()
                                     .OrderBy(x => x.IdxMacchina)
                                     .ToListAsync();
            return dbResult;
        }

        /// <inheritdoc />
        public async Task<bool> EvListMicroStatoInsertAsync(MicroStatoMacchinaModel newRecMsm, EventListModel newRecEv)
        {
            await using var dbCtx = await CreateContextAsync();
            //// eseguo in transazione...
            //await using var tx = await dbCtx.Database.BeginTransactionAsync(IsolationLevel.ReadCommitted);

            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);
                }

                // 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 (Exception ex)
            {
                //await tx.RollbackAsync();
                // Log dettagliato errore
                Log.Error(ex, $"Errore in EvListMicroStatoInsertAsync: {newRecEv.IdxMacchina}");
                throw;
            }
        }

        /// <inheritdoc />
        public async Task<bool> KeepAliveUpsertAsync(string IdxMacc, DateTime OraServer, DateTime OraMacc)
        {
            await using var dbCtx = await CreateContextAsync();
            bool fatto = false;

            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;
        }

        /// <inheritdoc />
        public async Task<List<Macchine2SlaveModel>> Macchine2SlaveAsync()
        {
            await using var dbCtx = await CreateContextAsync();
            List<Macchine2SlaveModel> dbResult = await dbCtx
                                                        .DbSetM2S
                                                        .AsNoTracking()
                                                        .OrderBy(x => x.IdxMacchina)
                                                        .ToListAsync();

            return dbResult;
        }

        /// <inheritdoc />
        public async Task<MacchineModel?> MacchineGetByIdxAsync(string IdxMacchina)
        {
            await using var dbCtx = await CreateContextAsync();
            MacchineModel dbResult = await dbCtx
                                        .DbSetMacchine
                                        .FirstOrDefaultAsync(x => x.IdxMacchina == IdxMacchina);
            return dbResult;
        }

        /// <inheritdoc />
        public async Task<bool> MacchineUpsertAsync(MacchineModel entity)
        {
            await using var dbCtx = await CreateContextAsync();

            // 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);
            }
            return await dbCtx.SaveChangesAsync() > 0;
        }

        /// <inheritdoc />
        public async Task<List<MicroStatoMacchinaModel>> MicroStatoMacchinaGetByIdxMaccAsync(string IdxMacc)
        {
            await using var dbCtx = await CreateContextAsync();
            List<MicroStatoMacchinaModel> dbResult = new List<MicroStatoMacchinaModel>();
            dbResult = await dbCtx
                                .DbSetMicroStatoMacc
                                .Where(x => x.IdxMacchina == IdxMacc)
                                .AsNoTracking()
                                .ToListAsync();
            return dbResult;
        }

        /// <inheritdoc />
        public async Task<bool> MicroStatoMacchinaUpsertAsync(MicroStatoMacchinaModel newRec)
        {
            await using var dbCtx = await CreateContextAsync();
            bool fatto = false;

            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;
        }

        /// <inheritdoc />
        public async Task<bool> SignalLogInsertAsync(SignalLogModel newRec)
        {
            await using var dbCtx = await CreateContextAsync();
            var currRec = dbCtx
                            .DbSetSignalLog
                            .Add(newRec);
            return await dbCtx.SaveChangesAsync() > 0;
        }

        /// <inheritdoc />
        public async Task<List<TransizioneIngressiModel>> StateMachineIngressiAsync(int idxFam)
        {
            await using var dbCtx = await CreateContextAsync();
            var IdxFamIn = new SqlParameter("@IdxFamigliaIngresso", idxFam);
            var dbResult = await dbCtx
                                    .DbSetSMI
                                    .FromSqlRaw("exec dbo.stp_TRI_getByIdxFamIng @IdxFamigliaIngresso", IdxFamIn)
                                    .AsNoTracking()
                                    .ToListAsync();
            return dbResult;
        }

        /// <inheritdoc />
        public async Task<StatoProdModel> StatoProdMacchinaAsync(string idxMacchina, DateTime dtReq)
        {
            await using var dbCtx = await CreateContextAsync();

            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;
        }

        /// <inheritdoc />
        public async Task<VMSFDModel?> VMSFDGetByMaccAsync(string idxMacc)
        {
            await using var dbCtx = await CreateContextAsync();
            var IdxMacchina = new SqlParameter("@IdxMacchina", idxMacc);
            var dbResult = (await dbCtx
                            .DbSetMSFD
                            .FromSqlRaw("exec dbo.stp_MSFD_getMacc @IdxMacchina", IdxMacchina)
                            .AsNoTracking()
                            .ToListAsync())
                            .FirstOrDefault();
            return dbResult;
        }

        /// <inheritdoc />
        public async Task<List<VMSFDModel>> VMSFDGetMultiByMaccAsync(string idxMacc)
        {
            await using var dbCtx = await CreateContextAsync();
            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 Protected Fields

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

        #endregion Protected Fields
    }
}