using Core;
using Core.DTO;
using LiMan.DB.DBModels;
using LiMan.DB.DTO;
using LiMan.DB.Migrations;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using NLog;
using Org.BouncyCastle.Asn1.Crmf;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Channels;
using System.Threading.Tasks;
using static Core.Enum;

namespace LiMan.DB.Controllers
{
    public class DbController : IDisposable
    {
        #region Public Constructors

        public DbController(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        #endregion Public Constructors

#if false
        /// <summary>
        /// Upsert record applicativo
        /// </summary>
        /// <param name="upRec"></param>
        /// <returns></returns>
        public bool ApplicativoUpsert(ApplicativoModel upRec)
        {
            bool fatto = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                try
                {
                    // verifico record esistente
                    var currRec = localDbCtx
                                    .DbSetApp
                                    .Where(x => x.CodApp == upRec.CodApp)
                                    .FirstOrDefault();

                    if (currRec != null)
                    {
                        currRec.CodApp = upRec.CodApp;
                        currRec.Descrizione = upRec.Descrizione;
                        currRec.TplConnString = upRec.TplConnString;
                    }
                    else
                    {
                        localDbCtx
                            .DbSetApp
                            .Add(upRec);
                    }
                    localDbCtx.SaveChanges();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in ApplicativoUpsert{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }
#endif

        #region Public Methods

        /// <summary>
        /// Verifica se una applcazione abbia record correlati...
        /// </summary>
        /// <param name="CodApp"></param>
        /// <returns></returns>
        public bool ApplicazioniHasChild(string CodApp)
        {
            bool answ = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                try
                {
                    // in primis deve esserci un record che possa essere collegato
                    ApplicativoModel currRec = localDbCtx
                       .DbSetApp
                       .Where(x => x.CodApp == CodApp)
                       .FirstOrDefault();
                    if (currRec != null)
                    {
                        // controllo: NON ci devono essere record correlati in licenze, revisioni...
                        int numLic = localDbCtx
                                        .DbSetLicenze
                                        .Where(x => x.CodApp == CodApp)
                                        .Count();
                        int numLogLic = localDbCtx
                                        .DbSetLogLicenze
                                        .Where(x => x.CodApp == CodApp)
                                        .Count();
                        int numRel = localDbCtx
                                        .DbSetReleases
                                        .Where(x => x.CodApp == CodApp)
                                        .Count();

                        // procedo solo se non ho record collegati
                        answ = numLic + numLogLic + numRel > 0;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in ApplicHasChild:{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Elimina il record indicato (se non ci sono record correlati...)
        /// </summary>
        /// <param name="rec2del"></param>
        /// <returns></returns>
        public bool ApplicazioniNextDelete(ApplicativoModel rec2del)
        {
            bool done = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                try
                {
                    ApplicativoModel currRec = localDbCtx
                       .DbSetApp
                       .Where(x => x.CodApp == rec2del.CodApp)
                       .FirstOrDefault();
                    if (currRec != null)
                    {
                        localDbCtx
                            .DbSetApp
                            .Remove(currRec);
                        localDbCtx.SaveChanges();
                        done = true;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in ApplicNextDelete:{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        public bool ApplicazioniNextUpdate(ApplicativoModel updItem)
        {
            bool done = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                try
                {
                    ApplicativoModel currData = localDbCtx
                       .DbSetApp
                       .Where(x => x.CodApp == updItem.CodApp)
                       .FirstOrDefault();
                    if (currData != null)
                    {
                        // aggiorno valori
                        currData.Descrizione = updItem.Descrizione;
                        currData.Tipo = updItem.Tipo;
                        currData.TplConnString = updItem.TplConnString;
                        localDbCtx.Entry(currData).State = EntityState.Modified;
                    }
                    else
                    {
                        localDbCtx
                            .DbSetApp
                            .Add(updItem);
                    }
                    localDbCtx.SaveChanges();
                    done = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in ApplicNextUpdate:{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        /// <summary>
        /// Elimino una licenza da quelle attivate (SE scaduto veto)
        /// </summary>
        /// <param name="ParamDict">Dizionario delle Licenze da eliminare</param>
        /// <param name="MasterKey">Chiave Master</param>
        public bool AttivazioniDelete(Dictionary<string, string> ParamDict, string MasterKey)
        {
            bool fatto = false;
            LicenzaModel mainLic = new LicenzaModel();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                DateTime oggi = DateTime.Today;
                List<SubLicenzaModel> items2del = new List<SubLicenzaModel>();
                // ciclo x ogni licenze a vedere SE sia eliminabile...
                foreach (var item in ParamDict)
                {
                    // calcolo DTO Attivazione
                    var itemsTgt = localDbCtx
                        .DbSetSubLicenze
                        .Where(x => x.CodImpiego == item.Key && x.Chiave == item.Value && x.LicenzaNav.Chiave == MasterKey && x.VetoUnlock <= oggi)
                        .ToList();
                    // se trovate aggiungo ad elenco...
                    if (itemsTgt != null && itemsTgt.Count > 0)
                    {
                        items2del.AddRange(itemsTgt);
                    }
                }

                // se ho qualcosa procedo...
                if (items2del != null && items2del.Count > 0)
                {
                    localDbCtx
                        .DbSetSubLicenze
                        .RemoveRange(items2del);
                    // salvo
                    localDbCtx.SaveChanges();
                    fatto = true;
                }
            }
            return fatto;
        }

        public bool AttivazioniDelete(int idxSubLic)
        {
            bool fatto = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                // recupero record
                var currData = localDbCtx
                    .DbSetSubLicenze
                    .Where(x => x.IdxSubLic == idxSubLic)
                    .FirstOrDefault();
                // se ho qualcosa procedo...
                if (currData != null)
                {
                    localDbCtx.DbSetSubLicenze.Remove(currData);
                    // salvo
                    localDbCtx.SaveChanges();
                    fatto = true;
                }
            }
            return fatto;
        }

        /// <summary>
        /// Lista attivazioni dati cod identificativi CodImp e AppKey
        /// </summary>
        /// <param name="AppKey"></param>
        /// <param name="CodImp"></param>
        /// <returns></returns>
        public List<SubLicenzaModel> AttivazioniGetAppImp(string AppKey, string CodImp)
        {
            List<SubLicenzaModel> dbResult = new List<SubLicenzaModel>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                dbResult = localDbCtx
                            .DbSetSubLicenze
                            .Where(x => x.CodImpiego == CodImp && x.Chiave == AppKey)
                            .Include(l => l.LicenzaNav)
                            .OrderBy(o => o.VetoUnlock)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Lista attivazioni x licenza
        /// </summary>
        /// <param name="idxLic"></param>
        /// <returns></returns>
        public List<SubLicenzaModel> AttivazioniGetByLic(int idxLic)
        {
            List<SubLicenzaModel> dbResult = new List<SubLicenzaModel>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                DateTime oggi = DateTime.Today;
                var rawRes = localDbCtx
                            .DbSetSubLicenze
                            .Where(x => x.IdxLic == idxLic)
                            .OrderBy(o => o.VetoUnlock)
                            .ToList();
                dbResult = rawRes != null ? rawRes : dbResult;
            }
            return dbResult;
        }

        /// <summary>
        /// Elimino le licenze con veto scaduto
        /// </summary>
        /// <param name="MasterKey">Chiave Master</param>
        public bool AttivazioniResetAvail(string MasterKey)
        {
            bool fatto = false;
            LicenzaModel mainLic = new LicenzaModel();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                DateTime oggi = DateTime.Today;

                // calcolo DTO Attivazione
                var item2Del = localDbCtx
                    .DbSetSubLicenze
                    .Where(x => x.LicenzaNav.Chiave == MasterKey && x.VetoUnlock < oggi)
                    .ToList();
                // se ho qualcosa procedo...
                if (item2Del != null && item2Del.Count > 0)
                {
                    localDbCtx
                        .DbSetSubLicenze
                        .RemoveRange(item2Del);
                    // salvo
                    localDbCtx.SaveChanges();
                }
                // comunque true se ci ha provato
                fatto = true;
            }
            return fatto;
        }

        /// <summary>
        /// Cerca di aggiungere sul DB le attivazioni richieste
        /// </summary>
        /// <param name="MasterKey">Master Lic</param>
        /// <param name="ParamDict">Elenco codici impiego (key) + valori in formato dizionari</param>
        /// <param name="numDayVeto">num gg di veto da impostare</param>
        /// <returns></returns>
        public bool AttivazioniTryAdd(string MasterKey, Dictionary<string, string> ParamDict, int numDayVeto, TipoLicenza tipoLic)
        {
            bool answ = false;

            // verifica se si può procedere...
            var licenza = GetLicenza(MasterKey);
            if (licenza != null)
            {
                var attivList = GetAttivazioniByLic(licenza.IdxLic, false);
                // elimino dall'elenco parametri le licenze già presenti...
                Dictionary<string, string> Par2Rem = new Dictionary<string, string>();
                foreach (var item in ParamDict)
                {
                    var currRec = attivList.FirstOrDefault(x => x.CodImpiego == item.Key && x.Chiave == item.Value);
                    // se c'è segno che è da eliminare...
                    if (currRec != null && currRec.CodImpiego == item.Key)
                    {
                        Par2Rem.Add(item.Key, item.Value);
                    }
                }
                // elimino da elenco parametri quelli che sono da eliminare...
                if (Par2Rem.Count > 0)
                {
                    foreach (var item in Par2Rem)
                    {
                        ParamDict.Remove(item.Key);
                    }
                }
                // controllo se ho ancora qualcosa da aggiungere...
                if (ParamDict.Count > 0)
                {
                    if (licenza.IsValid && (licenza.NumLicenze >= (attivList.Count + ParamDict.Count)))
                    {
                        DateTime oggi = DateTime.Today;
                        DateTime vetoDate = oggi.AddDays(numDayVeto);
                        using (LMDbContext dbCtx = new LMDbContext(_configuration))
                        {
                            try
                            {
                                // cerco eventuali licenze preesistenti (per CodImp) e le elimino SE
                                // sono libere...
                                List<SubLicenzaModel> list2rem = new List<SubLicenzaModel>();
                                foreach (var item in ParamDict)
                                {
                                    var rec2del = dbCtx
                                                    .DbSetSubLicenze
                                                    .Where(x => x.CodImpiego == item.Key && x.VetoUnlock <= oggi)
                                                    .FirstOrDefault();
                                    if (rec2del != null)
                                    {
                                        list2rem.Add(rec2del);
                                    }
                                }
                                // se ce ne sono rimuovo e salvo...
                                if (list2rem != null && list2rem.Count > 0)
                                {
                                    dbCtx
                                        .DbSetSubLicenze
                                        .RemoveRange(list2rem);

                                    dbCtx.SaveChanges();
                                }

                                // genero subLicenze...
                                var newRec = ParamDict
                                    .Select(x => new SubLicenzaModel()
                                    {
                                        IdxLic = licenza.IdxLic,
                                        CodImpiego = x.Key,
                                        VetoUnlock = vetoDate,
                                        Tipo = tipoLic,
                                        Chiave = x.Value
                                    })
                                    .ToList();
                                dbCtx
                                    .DbSetSubLicenze
                                    .AddRange(newRec);

                                dbCtx.SaveChanges();
                            }
                            catch (Exception exc)
                            {
                                Log.Error($"Errore in AttivazioniTryAdd per MasterKey {MasterKey} | tipo: {tipoLic} | #rec: {ParamDict.Count}{Environment.NewLine}{exc}");
                            }
                        }
                    }
                }
            }

            return answ;
        }

        /// <summary>
        /// Cerca di aggiornare le authKey sul DB x le chiavi
        /// </summary>
        /// <param name="MasterKey">Master Lic</param>
        /// <param name="ParamDict">Elenco codici impiego (key) + valori in formato dizionari</param>
        /// <returns></returns>
        public bool AttivazioniTryRefresh(string MasterKey, Dictionary<string, string> ParamDict)
        {
            bool answ = false;

            // verifica se si può procedere...
            var licenza = GetLicenza(MasterKey);
            if (licenza != null)
            {
                var attivList = GetAttivazioniByLic(licenza.IdxLic, false);
                if (licenza.IsValid)
                {
                    using (LMDbContext localDbCtx = new LMDbContext(_configuration))
                    {
                        try
                        {
                            // ciclo ed aggiorno
                            foreach (var item in ParamDict)
                            {
                                var currRec = localDbCtx
                                                .DbSetSubLicenze
                                                .Where(x => x.CodImpiego == item.Key)
                                                .FirstOrDefault();
                                if (currRec != null)
                                {
                                    currRec.Chiave = item.Value;
                                }
                            }

                            // salvo!
                            localDbCtx.SaveChanges();
                        }
                        catch (Exception exc)
                        {
                            Log.Error($"Errore in AttivazioniTryRefresh per ComKey {MasterKey} | #rec: {ParamDict.Count}{Environment.NewLine}{exc}");
                        }
                    }
                }
            }

            return answ;
        }

        public bool AttivazioniUnlock(int idxSubLic)
        {
            bool fatto = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                DateTime oggi = DateTime.Today;

                // recupero record
                var currData = localDbCtx
                    .DbSetSubLicenze
                    .Where(x => x.IdxSubLic == idxSubLic && x.VetoUnlock > oggi)
                    .FirstOrDefault();
                // se ho qualcosa procedo...
                if (currData != null)
                {
                    currData.VetoUnlock = oggi;
                    localDbCtx.Entry(currData).State = EntityState.Modified;
                    // salvo
                    localDbCtx.SaveChanges();
                    // true
                    fatto = true;
                }
            }
            return fatto;
        }

        public List<AuthClaimModel> AuthClaimByUserID(int userID)
        {
            List<AuthClaimModel> dbResult = new List<AuthClaimModel>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                DateTime oggi = DateTime.Today;
                dbResult = localDbCtx
                            .DbSetClaims
                            .Where(x => x.UserID == userID)
                            .ToList();
            }
            return dbResult;
        }

        public List<AuthClaimModel> AuthClaimByUserName(string userName)
        {
            List<AuthClaimModel> dbResult = new List<AuthClaimModel>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                DateTime oggi = DateTime.Today;
                dbResult = localDbCtx
                            .DbSetClaims
                            .Where(x => x.UserNav.Username == userName)
                            .Include(r => r.RoleNav)
                            .ToList();
            }
            return dbResult;
        }

        public bool AuthClaimRemove(AuthClaimModel rec2del)
        {
            bool answ = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                try
                {
                    var currData = localDbCtx
                                    .DbSetClaims
                                    .Where(x => x.ClaimID == rec2del.ClaimID)
                                    .FirstOrDefault();
                    if (currData != null)
                    {
                        localDbCtx
                            .DbSetClaims
                            .Remove(currData);
                    }
                    localDbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AuthClaimRemove | RoleID: {rec2del.RoleID} | UserId: {rec2del.UserID}{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        public bool AuthClaimUpsert(AuthClaimModel newRec)
        {
            bool answ = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                try
                {
                    var currData = localDbCtx
                                    .DbSetClaims
                                    .Where(x => x.ClaimID == newRec.ClaimID)
                                    .FirstOrDefault();
                    if (currData != null)
                    {
                        currData.UserID = newRec.UserID;
                        currData.RoleID = newRec.RoleID;
                        currData.DtMod = DateTime.Now;
                        // segno aggiornato
                        localDbCtx.Entry(currData).State = EntityState.Modified;
                    }
                    else
                    {
                        localDbCtx
                                    .DbSetClaims
                                    .Add(newRec);
                    }
                    localDbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AuthClaimUpsert | ClaimID: {newRec.ClaimID} | UserID: {newRec.UserID} | RoleID: {newRec.RoleID}{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Rimozione di ogni Role Claim x utente (che diventa inattivo)
        /// </summary>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public bool AuthRoleResetUser(int UserId)
        {
            bool answ = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                try
                {
                    var currData = localDbCtx
                                    .DbSetClaims
                                    .Where(x => x.UserID == UserId)
                                    .ToList();
                    if (currData != null)
                    {
                        // rimuovo intero range...
                        localDbCtx
                            .DbSetClaims
                            .RemoveRange(currData);
                    }
                    localDbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AuthUserRemoveRoles | UserId: {UserId}{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        public List<AuthRoleModel> AuthRolesGetAll()
        {
            List<AuthRoleModel> dbResult = new List<AuthRoleModel>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                dbResult = localDbCtx
                            .DbSetRoles
                            .ToList();
            }
            return dbResult;
        }

        public bool AuthRoleUpsert(AuthRoleModel newRec)
        {
            bool answ = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                try
                {
                    var currData = localDbCtx
                                    .DbSetRoles
                                    .Where(x => x.RoleID == newRec.RoleID)
                                    .FirstOrDefault();
                    if (currData != null)
                    {
                        currData.Descrizione = newRec.Descrizione;
                        currData.Ruolo = newRec.Ruolo;
                        // segno aggiornato
                        localDbCtx.Entry(currData).State = EntityState.Modified;
                    }
                    else
                    {
                        localDbCtx
                                    .DbSetRoles
                                    .Add(newRec);
                    }
                    localDbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AuthRoleUpsert | RoleID: {newRec.RoleID} | Ruolo: {newRec.Ruolo}{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        public List<AuthUserModel> AuthUserAll()
        {
            List<AuthUserModel> dbResult = new List<AuthUserModel>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                dbResult = localDbCtx
                            .DbSetUsers
                            .Include(c => c.Claims)
                            .ThenInclude(r => r.RoleNav)
                            .ToList();
            }
            return dbResult;
        }

        public List<AuthUserModel> AuthUserGetFilt(string userName)
        {
            List<AuthUserModel> dbResult = new List<AuthUserModel>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                dbResult = localDbCtx
                            .DbSetUsers
                            .Where(x => x.Username == userName)
                            .ToList();
            }
            return dbResult;
        }

        public bool AuthUserUpsert(AuthUserModel newRec)
        {
            bool answ = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                try
                {
                    var currData = localDbCtx
                                    .DbSetUsers
                                    .Where(x => x.UserID == newRec.UserID)
                                    .FirstOrDefault();
                    if (currData != null)
                    {
                        currData.Username = newRec.Username;
                        currData.AD_Domain = newRec.AD_Domain;
                        currData.AD_User = newRec.AD_User;
                        currData.Cognome = newRec.Cognome;
                        currData.Nome = newRec.Nome;
                        // segno aggiornato
                        localDbCtx.Entry(currData).State = EntityState.Modified;
                    }
                    else
                    {
                        localDbCtx
                                    .DbSetUsers
                                    .Add(newRec);
                    }
                    localDbCtx.SaveChanges();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in AuthUserUpsert | UserID: {newRec.UserID} | userName: {newRec.Username}{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Verifica e ripulisce eventuali vecchie SubLic rimaste senza riferimento
        /// </summary>
        /// <param name="upRec"></param>
        /// <returns></returns>
        public bool CheckCleanOldSubLic(InstalledReleasesModel upRec)
        {
            bool fatto = false;

            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                // cerco idxSublic da info...
                SubLicenzaModel recIst = localDbCtx
                                            .DbSetSubLicenze
                                            .FirstOrDefault(x => x.Chiave == upRec.AppKey);
                // se non fosse giusto CodImpiego e veto scaduto...
                if (!recIst.CodImpiego.Equals(upRec.CodImp))
                {
                    // log disuguaglianza chiave
                    Log.Info($"CheckOldSubLic | Mismatch licenza SubLic | CodApp: {upRec.CodApp} | AppKey: {upRec.AppKey} | App.CodImp: {upRec.CodImp} | Db.CodImp: {recIst.CodImpiego}");

                    // aggiorno CodImp se non bloccato...
                    if (recIst.VetoUnlock <= DateTime.Now)
                    {
                        // salvo record variazione licenza
                        LogCodImp recLogCodImp = new LogCodImp()
                        {
                            IdxLic = recIst.IdxLic,
                            IdxSubLic = recIst.IdxSubLic,
                            CodApp = upRec.CodApp,
                            CodImpOld = recIst.CodImpiego,
                            CodImpNew = upRec.CodImp,
                            DtMod = DateTime.Now
                        };
                        localDbCtx
                            .DbSetLogCodImp
                            .Add(recLogCodImp);

                        // elimino righe vecchie
                        var list2rem = localDbCtx
                                        .DbSetInstallRel
                                        .Where(x => x.CodApp == upRec.CodApp && x.CodImp != upRec.CodImp && x.AppKey == upRec.AppKey)
                                        .ToList();
                        if (list2rem != null && list2rem.Count > 0)
                        {
                            // loggo i record che vado ad eliminare
                            var sb = new StringBuilder();
                            sb.AppendLine("---------- Eliminazione SubLic scaduta ----------");
                            foreach (var item in list2rem)
                            {
                                sb.AppendLine($"Eliminazione SubLic scaduta | IdxInstall: {item.IdxInstall} | CodImp: {item.CodImp} | Vers: {item.VersNum} | NumImp: {item.NumImp} | LastCheck: {item.DtCheck}");
                            }
                            Log.Info(sb.ToString());

                            // elimino
                            localDbCtx
                                .DbSetInstallRel
                                .RemoveRange(list2rem);
                        }

                        // registro nuovo CodImpiego
                        recIst.CodImpiego = upRec.CodImp;
                        localDbCtx.Entry(recIst).State = EntityState.Modified;
                        // salvataggio!
                        try
                        {
                            // salvo
                            var res = localDbCtx.SaveChanges();
                            fatto = res != 0;
                        }
                        catch (Exception ex)
                        {
                            Log.Error($"Eccezione in CheckOldSubLic | IdxLic: {upRec.IdxLic} | CodApp: {upRec.CodApp} | VersNum: {upRec.VersNum} {Environment.NewLine}{ex}");
                        }

                        // log update codImpiego
                        Log.Info($"CheckOldSubLic | Aggiornamento SubLicenza | CodImp Aggiornato | CodApp: {upRec.CodApp} | AppKey: {upRec.AppKey} | App.CodImp: {upRec.CodImp} | Db.CodImp: {recIst.CodImpiego}");
                    }
                }
                // altrimenti controllo valori ID SubLic
                else
                {
                    var listInstRel2Chk = localDbCtx
                                            .DbSetInstallRel
                                            .Where(x => x.CodApp == upRec.CodApp && x.CodImp == upRec.CodImp && x.IdxSubLic != recIst.IdxSubLic)
                                            .ToList();
                    if(listInstRel2Chk!=null && listInstRel2Chk.Count>0)
                    {
                        // elimino
                        localDbCtx
                            .DbSetInstallRel
                            .RemoveRange(listInstRel2Chk);
                        // salvataggio!
                        try
                        {
                            // salvo
                            var res = localDbCtx.SaveChanges();
                            fatto = res != 0;
                        }
                        catch (Exception ex)
                        {
                            Log.Error($"Eccezione in CheckOldSubLic | IdxLic: {upRec.IdxLic} | CodApp: {upRec.CodApp} | VersNum: {upRec.VersNum} {Environment.NewLine}{ex}");
                        }
                    }
                }
            }
            return fatto;
        }

        public bool DbForceMigrate()
        {
            bool answ = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                try
                {
                    localDbCtx.DbForceMigrate();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in DbForceMigrate{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        public void Dispose()
        {
            // Clear database context
            //Log.Info("Dispose di GWMSController");
        }

        /// <summary>
        /// Elimino una richiesta enroll (anche se già approvata...)
        /// </summary>
        /// <param name="idReq">licId record da eliminare</param>
        public bool EnrollReqDelete(int idReq)
        {
            bool fatto = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                // se trovo... procedo...
                var rec2del = localDbCtx
                                .DbSetEnrollReq
                                .Where(x => x.IdReq == idReq)
                                .FirstOrDefault();
                if (rec2del != null)
                {
                    localDbCtx
                        .DbSetEnrollReq
                        .Remove(rec2del);
                    // salvo
                    localDbCtx.SaveChanges();
                    fatto = true;
                }
            }
            return fatto;
        }

        /// <summary>
        /// Elenco Enroll Attivi (non completati/cancellati)
        /// </summary>
        /// <returns></returns>
        public List<EnrollRequestModel> EnrollReqGetActive()
        {
            List<EnrollRequestModel> dbResult = new List<EnrollRequestModel>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                dbResult = localDbCtx
                            .DbSetEnrollReq
                            .Where(x => x.DtAppr == null)
                            .OrderByDescending(x => x.DtReq)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco completo Enroll Req
        /// </summary>
        /// <returns></returns>
        public List<EnrollRequestModel> EnrollReqGetAll()
        {
            List<EnrollRequestModel> dbResult = new List<EnrollRequestModel>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                dbResult = localDbCtx
                            .DbSetEnrollReq
                            .OrderByDescending(x => x.DtReq)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Recupero richiesta da licId (oppure empty...)
        /// </summary>
        /// <param name="idReq"></param>
        /// <returns></returns>
        public EnrollRequestModel EnrollReqGetById(int idReq)
        {
            EnrollRequestModel dbResult = new EnrollRequestModel();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                dbResult = localDbCtx
                            .DbSetEnrollReq
                            .FirstOrDefault(x => x.IdReq == idReq);
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco filtrato Enroll Req x data
        /// </summary>
        /// <param name="dtFrom"></param>
        /// <param name="dtTo"></param>
        /// <returns></returns>
        public List<EnrollRequestModel> EnrollReqGetFilt(DateTime dtFrom, DateTime dtTo)
        {
            List<EnrollRequestModel> dbResult = new List<EnrollRequestModel>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                dbResult = localDbCtx
                            .DbSetEnrollReq
                            .Where(x => x.DtReq >= dtFrom && x.DtReq <= dtTo)
                            .OrderByDescending(x => x.DtReq)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elimino eventuali richieste non approvate e scadute
        /// </summary>
        public bool EnrollReqPurgeInvalid()
        {
            bool fatto = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                List<EnrollRequestModel> item2del = new List<EnrollRequestModel>();
                // cerco in primis richieste NON associate a licenza
                var list2check = localDbCtx
                                .DbSetEnrollReq
                                .Where(x => x.IdxLic == 0)
                                .ToList();
                var list2del = list2check
                                .Where(x => x.IsScaduta)
                                .ToList();
                if (list2del != null)
                {
                    localDbCtx
                        .DbSetEnrollReq
                        .RemoveRange(list2del);
                    // salvo
                    int numDone = localDbCtx.SaveChanges();
                    fatto = numDone != 0;
                }
            }
            return fatto;
        }

        /// <summary>
        /// Upsert record richiesta enroll
        /// </summary>
        /// <param name="newRec"></param>
        /// <returns></returns>
        public int EnrollReqUpsert(EnrollRequestModel newRec)
        {
            int answ = 0;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                try
                {
                    var currData = localDbCtx
                                    .DbSetEnrollReq
                                    .Where(x => x.IdReq == newRec.IdReq && newRec.IdReq > 0)
                                    .FirstOrDefault();
                    if (currData != null)
                    {
                        currData.Passcode = newRec.Passcode;
                        currData.ReqPayload = newRec.ReqPayload;
                        currData.DtReq = newRec.DtReq;
                        currData.DtAppr = newRec.DtAppr;
                        currData.UserAppr = newRec.UserAppr;
                        currData.IdxLic = newRec.IdxLic;
                        // segno aggiornato
                        localDbCtx.Entry(currData).State = EntityState.Modified;
                        localDbCtx.SaveChanges();
                        answ = newRec.IdReq;
                    }
                    else
                    {
                        var result = localDbCtx
                                     .DbSetEnrollReq
                                     .Add(newRec);
                        localDbCtx.SaveChanges();
                        answ = newRec.IdReq;
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Errore in EnrollReqUpsert | Passcode: {newRec.Passcode} | DtReq: {newRec.DtReq}{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Elenco files attach da registrare
        /// </summary>
        /// <param name="idxTicket">Identificativo del ticket</param>
        /// <param name="baseDir">Directory di salvataggio dei file</param>
        /// <param name="fileUploaded">lista risultati della funzione di upload</param>
        /// <returns></returns>
        public bool FileAdd(int idxTicket, string baseDir, List<UploadResult> fileUploaded)
        {
            bool fatto = false;
            if (fileUploaded == null || fileUploaded.Count == 0)
            {
                Log.Error("Errore FileAdd: fileUploaded è vuoto/nullo");
            }
            else
            {
                using (LMDbContext localDbCtx = new LMDbContext(_configuration))
                {
                    // verifico Ticket sia esistente
                    var currTicket = localDbCtx
                                    .DbSetTicket
                                    .Where(x => x.IdxTicket == idxTicket)
                                    .FirstOrDefault();

                    if (currTicket != null)
                    {
                        var newFiles = fileUploaded
                                        .Select(x => new FileAttachModel()
                                        {
                                            IdxTicket = idxTicket,
                                            OriginalName = x.FileName,
                                            StorageName = x.StoredFileName,
                                            DtEvent = DateTime.Now,
                                            FullStoragePath = Path.Combine(baseDir, x.StoredFileName)
                                        }).ToList();

                        localDbCtx
                            .DbSetFileAttach
                            .AddRange(newFiles);

                        localDbCtx.SaveChanges();

                        fatto = true;
                    }
                }
            }
            return fatto;
        }

        /// <summary>
        /// Elenco file registrati dato ticket id
        /// </summary>
        /// <param name="idxTicket">Identificativo del ticket</param>
        /// <returns></returns>
        public List<FileAttachModel> FileGetFilt(int idxTicket)
        {
            List<FileAttachModel> dbResult = new List<FileAttachModel>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                // recupero locenza...
                dbResult = localDbCtx
                                .DbSetFileAttach
                                .Where(x => x.IdxTicket == idxTicket)
                                .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco applicativi in scadenza nel periodo considerato (min-max)
        /// </summary>
        /// <param name="minDate">Data iniziale da considerare</param>
        /// <param name="maxDate">Data finale da considerare</param>
        /// <returns></returns>
        public List<ApplicativoDTO> GetApplicativiExpiring(DateTime minDate, DateTime maxDate)
        {
            // cerco solo applicativi attivi
            bool hideData = true;
            List<ApplicativoDTO> dbResult = new List<ApplicativoDTO>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                DateTime oggi = DateTime.Today;

                // generazione stringhe casuali.... https://jonathancrozier.com/blog/how-to-generate-a-random-string-with-c-sharp
                StringBuilder sbRandChiave = new StringBuilder();
                StringBuilder sbRandEnigma = new StringBuilder();
                StringBuilder sbRandPayload = new StringBuilder();
                string stdChiave = "";
                string stdEnigma = "";
                string stdPayLoad = "";
                int stdIdxLic = 0;
                DateTime stdDataEnigma = DateTime.Today;
                if (hideData)
                {
                    int numChar = 80;
                    Random random = new Random();

                    // idxLic
                    stdIdxLic = random.Next(50000, 100000);

                    // chiave random
                    for (int i = 0; i < numChar; i++)
                    {
                        // Generate floating point numbers
                        double myFloat = random.NextDouble();
                        // Generate the char using below logic
                        var myChar = Convert.ToChar(Convert.ToInt32(Math.Floor(25 * myFloat) + 65));
                        sbRandChiave.Append(myChar);
                    }
                    stdChiave = $"{sbRandChiave}";

                    // random data enigma
                    stdDataEnigma = DateTime.Today.AddDays(random.Next(0, 15));

                    // random enigma
                    for (int i = 0; i < numChar; i++)
                    {
                        // Generate floating point numbers
                        double myFloat = random.NextDouble();
                        // Generate the char using below logic
                        var myChar = Convert.ToChar(Convert.ToInt32(Math.Floor(25 * myFloat) + 65));
                        sbRandEnigma.Append(myChar);
                    }
                    stdEnigma = $"{sbRandEnigma}";

                    // random payload
                    for (int i = 0; i < numChar; i++)
                    {
                        // Generate floating point numbers
                        double myFloat = random.NextDouble();
                        // Generate the char using below logic
                        var myChar = Convert.ToChar(Convert.ToInt32(Math.Floor(25 * myFloat) + 65));
                        sbRandPayload.Append(myChar);
                    }
                    stdPayLoad = $"{sbRandPayload}";
                }

                // calcolo DTO applicativi
                dbResult = localDbCtx
                    .DbSetLicenze
                    .Where(x => (x.Scadenza >= minDate && x.Scadenza < maxDate))
                    .OrderBy(o => o.Scadenza)
                    .Select(x => new ApplicativoDTO()
                    {
                        IdxLic = hideData ? stdIdxLic : x.IdxLic,
                        Chiave = hideData ? stdChiave : x.Chiave,
                        CodApp = x.CodApp,
                        CodInst = x.CodInst,
                        Descrizione = x.Descrizione,
                        Tipo = x.Tipo,
                        Scadenza = x.Scadenza,
                        NumLicenze = x.NumLicenze,
                        NumLicenzeAttive = -1,
                        DataEnigma = hideData ? stdDataEnigma : x.DataEnigma,
                        Enigma = hideData ? stdEnigma : x.Enigma,
                        Payload = hideData ? stdPayLoad : x.Payload
                    })
                    .ToList();
            }
            return dbResult;
        }

        public List<ApplicativoDTO> GetApplicativiFilt(bool OnlyActive, string CodApp, string CodInst, bool hideData)
        {
            List<ApplicativoDTO> dbResult = new List<ApplicativoDTO>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                DateTime oggi = DateTime.Today;

                // generazione stringhe casuali.... https://jonathancrozier.com/blog/how-to-generate-a-random-string-with-c-sharp
                StringBuilder sbRandChiave = new StringBuilder();
                StringBuilder sbRandEnigma = new StringBuilder();
                StringBuilder sbRandPayload = new StringBuilder();
                string stdChiave = "";
                string stdEnigma = "";
                string stdPayLoad = "";
                int stdIdxLic = 0;
                DateTime stdDataEnigma = DateTime.Today;
                if (hideData)
                {
                    int numChar = 80;
                    Random random = new Random();

                    // idxLic
                    stdIdxLic = random.Next(50000, 100000);

                    // chiave random
                    for (int i = 0; i < numChar; i++)
                    {
                        // Generate floating point numbers
                        double myFloat = random.NextDouble();
                        // Generate the char using below logic
                        var myChar = Convert.ToChar(Convert.ToInt32(Math.Floor(25 * myFloat) + 65));
                        sbRandChiave.Append(myChar);
                    }
                    stdChiave = $"{sbRandChiave}";

                    // random data enigma
                    stdDataEnigma = DateTime.Today.AddDays(random.Next(0, 15));

                    // random enigma
                    for (int i = 0; i < numChar; i++)
                    {
                        // Generate floating point numbers
                        double myFloat = random.NextDouble();
                        // Generate the char using below logic
                        var myChar = Convert.ToChar(Convert.ToInt32(Math.Floor(25 * myFloat) + 65));
                        sbRandEnigma.Append(myChar);
                    }
                    stdEnigma = $"{sbRandEnigma}";

                    // random payload
                    for (int i = 0; i < numChar; i++)
                    {
                        // Generate floating point numbers
                        double myFloat = random.NextDouble();
                        // Generate the char using below logic
                        var myChar = Convert.ToChar(Convert.ToInt32(Math.Floor(25 * myFloat) + 65));
                        sbRandPayload.Append(myChar);
                    }
                    stdPayLoad = $"{sbRandPayload}";
                }

                // conteggio preliminare licenze impiegate
                var countSubLic = localDbCtx
                                    .DbSetSubLicenze
                                    .GroupBy(x => x.IdxLic)
                                    .Select(g => new
                                    {
                                        IdxLic = g.Key,
                                        NumLic = g.Count()
                                    });

                // calcolo DTO applicativi
                dbResult = localDbCtx
                    .DbSetLicenze
                    .Where(x => (x.CodApp == CodApp || string.IsNullOrEmpty(CodApp)) && (x.CodInst == CodInst || string.IsNullOrEmpty(CodInst)) && (!OnlyActive || x.Scadenza > oggi))
                    .OrderByDescending(o => o.Scadenza)
                    .Select(x => new ApplicativoDTO()
                    {
                        IdxLic = hideData ? stdIdxLic : x.IdxLic,
                        Chiave = hideData ? stdChiave : x.Chiave,
                        CodApp = x.CodApp,
                        CodInst = x.CodInst,
                        Descrizione = x.Descrizione,
                        Tipo = x.Tipo,
                        Scadenza = x.Scadenza,
                        NumLicenze = x.NumLicenze,
                        NumLicenzeAttive = countSubLic.Where(s => s.IdxLic == x.IdxLic).Select(c => c.NumLic).FirstOrDefault(),
                        DataEnigma = hideData ? stdDataEnigma : x.DataEnigma,
                        Enigma = hideData ? stdEnigma : x.Enigma,
                        Payload = hideData ? stdPayLoad : x.Payload
                    })
                    .ToList();
            }
            return dbResult;
        }

        public List<ApplicativoModel> GetApplicazioni()
        {
            List<ApplicativoModel> dbResult = new List<ApplicativoModel>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                dbResult = localDbCtx
                            .DbSetApp
                            .ToList();
            }
            return dbResult;
        }

        public ApplicativoModel GetApplicazioniFilt(string codApp)
        {
            ApplicativoModel dbResult = new ApplicativoModel();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                var rawData = localDbCtx
                            .DbSetApp
                            .Where(x => x.CodApp == codApp)
                            .FirstOrDefault();
                if (rawData != null)
                {
                    dbResult = rawData;
                }
            }
            return dbResult;
        }

        public AttivazioneDTO GetAttivazione(string Chiave, string CodImpiego, bool hideData)
        {
            AttivazioneDTO dbResult = new AttivazioneDTO();
            LicenzaModel mainLic = new LicenzaModel();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                DateTime oggi = DateTime.Today;
                int stdIdxLic = 0;

                // generazione stringhe casuali.... https://jonathancrozier.com/blog/how-to-generate-a-random-string-with-c-sharp
                StringBuilder sbRandChiave = new StringBuilder();
                string stdChiave = "";

                if (hideData)
                {
                    int numChar = 80;
                    Random random = new Random();

                    // idxLic
                    stdIdxLic = random.Next(50000, 100000);

                    // chiave random
                    for (int i = 0; i < numChar; i++)
                    {
                        // Generate floating point numbers
                        double myFloat = random.NextDouble();
                        // Generate the char using below logic
                        var myChar = Convert.ToChar(Convert.ToInt32(Math.Floor(25 * myFloat) + 65));
                        sbRandChiave.Append(myChar);
                    }
                    stdChiave = $"{sbRandChiave}";
                }
                else
                {
                    // recupero se c'è licenza master
                    mainLic = localDbCtx
                                    .DbSetLicenze
                                    .Where(x => x.Chiave == Chiave)
                                    .FirstOrDefault();
                }

                // calcolo DTO applicativi
                dbResult = localDbCtx
                    .DbSetSubLicenze
                    .Where(x => x.LicenzaNav.Chiave == Chiave && x.CodImpiego == CodImpiego)
                    .OrderBy(o => o.VetoUnlock)
                    .Select(x => new AttivazioneDTO()
                    {
                        IdxLic = hideData ? stdIdxLic : x.IdxLic,
                        IdxSubLic = x.IdxSubLic,
                        CodImpiego = x.CodImpiego,
                        CodApp = mainLic.CodApp,
                        CodInst = mainLic.CodInst,
                        Descrizione = mainLic.Descrizione,
                        Tipo = x.Tipo,
                        Chiave = hideData ? stdChiave : x.Chiave,
                        VetoUnlock = x.VetoUnlock
                    })
                    .FirstOrDefault();
            }
            return dbResult;
        }

        /// <summary>
        /// Recupero da DB le attivazioni richieste
        /// </summary>
        /// <param name="IdxLic">Idx Licenza</param>
        /// <param name="hideData">Indica se generare rand parametri sensibili</param>
        public List<AttivazioneDTO> GetAttivazioniByLic(int IdxLic, bool hideData)
        {
            List<AttivazioneDTO> dbResult = new List<AttivazioneDTO>();
            LicenzaModel mainLic = new LicenzaModel();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                DateTime oggi = DateTime.Today;
                int stdIdxLic = 0;

                // generazione stringhe casuali.... https://jonathancrozier.com/blog/how-to-generate-a-random-string-with-c-sharp
                StringBuilder sbRandChiave = new StringBuilder();
                string stdChiave = "";

                if (hideData)
                {
                    int numChar = 80;
                    Random random = new Random();

                    // idxLic
                    stdIdxLic = random.Next(50000, 100000);

                    // chiave random
                    for (int i = 0; i < numChar; i++)
                    {
                        // Generate floating point numbers
                        double myFloat = random.NextDouble();
                        // Generate the char using below logic
                        var myChar = Convert.ToChar(Convert.ToInt32(Math.Floor(25 * myFloat) + 65));
                        sbRandChiave.Append(myChar);
                    }
                    stdChiave = $"{sbRandChiave}";
                }
                else
                {
                    // recupero se c'è licenza master
                    mainLic = localDbCtx
                                    .DbSetLicenze
                                    .Where(x => x.IdxLic == IdxLic)
                                    .FirstOrDefault();
                }

                // calcolo DTO Attivazione
                dbResult = localDbCtx
                    .DbSetSubLicenze
                    .Where(x => x.IdxLic == IdxLic)
                    .Select(x => new AttivazioneDTO()
                    {
                        IdxLic = hideData ? stdIdxLic : x.IdxLic,
                        IdxSubLic = x.IdxSubLic,
                        CodImpiego = x.CodImpiego,
                        CodApp = mainLic.CodApp,
                        CodInst = mainLic.CodInst,
                        Descrizione = mainLic.Descrizione,
                        Tipo = x.Tipo,
                        Chiave = hideData ? stdChiave : x.Chiave,
                        VetoUnlock = x.VetoUnlock
                    })
                    .ToList();
            }
            return dbResult;
        }

        public List<InstallazioneModel> GetInstallazioni()
        {
            List<InstallazioneModel> dbResult = new List<InstallazioneModel>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                dbResult = localDbCtx
                .DbSetInst
                .ToList();
            }
            return dbResult;
        }

        public LicenzaModel GetLicenza(int IdxLic)
        {
            LicenzaModel dbResult = new LicenzaModel();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                dbResult = localDbCtx
                .DbSetLicenze
                .Where(x => x.IdxLic == IdxLic)
                .FirstOrDefault();
            }
            return dbResult;
        }

        public LicenzaModel GetLicenza(string MasterKey)
        {
            LicenzaModel dbResult = new LicenzaModel();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                dbResult = localDbCtx
                .DbSetLicenze
                .Where(x => x.Chiave == MasterKey)
                .FirstOrDefault();
            }
            return dbResult;
        }

        public List<LicenzaModel> GetLicenze()
        {
            List<LicenzaModel> dbResult = new List<LicenzaModel>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                dbResult = localDbCtx
                .DbSetLicenze
                .ToList();
            }
            return dbResult;
        }

        public List<LicenzaModel> GetLicenzeFilt(bool OnlyActive, string CodApp, string CodInst)
        {
            List<LicenzaModel> dbResult = new List<LicenzaModel>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                DateTime oggi = DateTime.Today;
                dbResult = localDbCtx
                            .DbSetLicenze
                            .Where(x => (x.CodApp == CodApp || string.IsNullOrEmpty(CodApp)) && (x.CodInst == CodInst || string.IsNullOrEmpty(CodInst)) && (!OnlyActive || x.Scadenza > oggi))
                            .Include(a => a.ApplicativoNav)
                            .Include(a => a.Attivazioni)
                            .Include(t => t.Tickets.Where(c => c.Status < StatoRichiesta.Approvata))
                            .OrderByDescending(o => o.Scadenza)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Effettua pulizia record registrazione InstalledRelease eliminando quelli non presenti in elenco
        /// </summary>
        /// <param name="CodImp">CodImpiego</param>
        /// <param name="AppKey">Chiave App</param>
        /// <param name="ListCodApp">Elenco app gestite (=da tenere)</param>
        /// <returns>Num rec eliminati</returns>
        public int InstallRelClean(string CodImp, string AppKey, List<string> ListCodApp)
        {
            int numDel = 0;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                // cerco tutti i record associati con le chiavi richieste
                List<InstalledReleasesModel> listRec = localDbCtx
                                                        .DbSetInstallRel
                                                        .Where(x => x.CodImp == CodImp && x.AppKey == AppKey)
                                                        .ToList();
                List<InstalledReleasesModel> list2Del = new List<InstalledReleasesModel>();
                // se ho risultati
                if (listRec != null && listRec.Count > 0)
                {
                    // elimino da questo elenco quelli che appartengono all'elenco ricevuto dei dati gestiti
                    foreach (var item in listRec)
                    {
                        if (!ListCodApp.Contains(item.CodApp))
                        {
                            list2Del.Add(item);
                        }
                    }
                }

                // se ho record da eliminare procedo!
                if (list2Del.Count > 0)
                {
                    // altrimenti aggiungo...
                    localDbCtx
                        .DbSetInstallRel
                        .RemoveRange(list2Del);
                    // salvataggio!
                    try
                    {
                        // salvo
                        numDel = localDbCtx.SaveChanges();
                    }
                    catch (Exception ex)
                    {
                        Log.Error($"Eccezione in InstallRelClean | CodImp: {CodImp} | AppKey: {AppKey} | ListCodApp #: {ListCodApp.Count}{Environment.NewLine}{ex}");
                    }
                }
            }
            return numDel;
        }

        /// <summary>
        /// Effettua eliminazione record registrazione InstalledRelease dato idx SubLic
        /// </summary>
        /// <param name="idxSubLic">Idx sub licenza</param>
        /// <returns>Num rec eliminati</returns>
        public int InstallRelDelBySubLic(int idxSubLic)
        {
            int numDel = 0;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                // cerco tutti i record associati con le chiavi richieste
                List<InstalledReleasesModel> list2Del = localDbCtx
                                                        .DbSetInstallRel
                                                        .Where(x => x.IdxSubLic == idxSubLic)
                                                        .ToList();

                // se ho record da eliminare procedo!
                if (list2Del.Count > 0)
                {
                    // altrimenti aggiungo...
                    localDbCtx
                        .DbSetInstallRel
                        .RemoveRange(list2Del);
                    // salvataggio!
                    try
                    {
                        // salvo
                        numDel = localDbCtx.SaveChanges();
                    }
                    catch (Exception ex)
                    {
                        Log.Error($"Eccezione in InstallRelDelBySubLic | idxSubLic: {idxSubLic}{Environment.NewLine}{ex}");
                    }
                }
            }
            return numDel;
        }

        /// <summary>
        /// Esegue salvataggio di uno snapshot della situazione delle installazioni attive al giorno odierno x ogni applicativo registrato
        /// </summary>
        /// <param name="CurrData">Dati raw da cui registrare info</param>
        /// <returns></returns>
        public bool InstallRelHistSnapshot(List<AppStatusDTO> CurrData)
        {
            bool fatto = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                DateTime oggi = DateTime.Today;
                // creo i record che mi servono...
                var rec2check = CurrData
                                .Select(x => new InstalledReleasesHistoryModel
                                {
                                    CodApp = x.CodApp,
                                    DtRif = oggi,
                                    NumInst = x.NumInst,
                                    NumImp = x.NumImp,
                                    VersNum = x.VersNumCurrent,
                                    NumIS4 = x.DetailInstalled.Where(y => y.UpToDateStatus == 4).Count(),
                                    NumIS3 = x.DetailInstalled.Where(y => y.UpToDateStatus == 3).Count(),
                                    NumIS2 = x.DetailInstalled.Where(y => y.UpToDateStatus == 2).Count(),
                                    NumIS1 = x.DetailInstalled.Where(y => y.UpToDateStatus == 1).Count(),
                                    NumIS0 = x.DetailInstalled.Where(y => y.UpToDateStatus == 0).Count(),
                                })
                                .ToList();

                // verifico ogni record se siano esistenti e da aggiornare (CodApp + data) o da inserire...
                foreach (var item in rec2check)
                {
                    // cerco record esistente...
                    var currRec = localDbCtx
                                    .DbSetInstallRelHist
                                    .Where(x => x.CodApp == item.CodApp && x.DtRif == item.DtRif)
                                    .FirstOrDefault();
                    // se trovato aggiorno
                    if (currRec != null)
                    {
                        currRec.NumImp = item.NumImp;
                        currRec.NumInst = item.NumInst;
                        currRec.NumIS0 = item.NumIS0;
                        currRec.NumIS1 = item.NumIS1;
                        currRec.NumIS2 = item.NumIS2;
                        currRec.NumIS3 = item.NumIS3;
                        currRec.NumIS4 = item.NumIS4;
                        currRec.VersNum = item.VersNum;
                    }
                    else
                    {
                        // altrimenti aggiungo...
                        localDbCtx
                            .DbSetInstallRelHist
                            .Add(item);
                    }
                }
                // salvataggio!
                try
                {
                    // salvo
                    var res = localDbCtx.SaveChanges();
                    fatto = res != 0;
                }
                catch (Exception ex)
                {
                    Log.Error($"Eccezione in InstallRelHistSnapshot{Environment.NewLine}{ex}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Update/insert record registrazione InstalledRelease applicativo
        /// </summary>
        /// <param name="upRec"></param>
        /// <returns></returns>
        public bool InstallRelUpsert(InstalledReleasesModel upRec)
        {
            bool fatto = false;
            bool changed = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                // in primis verifico se CodApp è gestito, altrimenti loggo e stop...
                var okCodApp = localDbCtx
                                .DbSetApp
                                .Any(x => x.CodApp == upRec.CodApp);
                if (!okCodApp)
                {
                    Log.Info($"Errore | InstallRelUpsert | CodApp not found: {upRec.CodApp} ");
                }
                else
                {
                    // cerco record esistente...
                    var currRec = localDbCtx
                                    .DbSetInstallRel
                                    .Where(x => x.CodApp == upRec.CodApp && x.CodImp == upRec.CodImp && x.AppKey == upRec.AppKey)
                                    .FirstOrDefault();
                    if (currRec != null)
                    {
                        // cerco subLic...
                        var recSubLic = localDbCtx
                                            .DbSetSubLicenze
                                            .FirstOrDefault(x => x.IdxSubLic == currRec.IdxSubLic && currRec.IdxSubLic > 0);

                        // se idxSubLic mancante cerco...
                        if (currRec.IdxSubLic == 0 || recSubLic==null)
                        {
                            // cerco idxSublic da info...
                            SubLicenzaModel recIst = localDbCtx
                                            .DbSetSubLicenze
                                            .FirstOrDefault(x => x.Chiave == upRec.AppKey && x.CodImpiego == upRec.CodImp);
                            if (recIst != null)
                            {
                                currRec.IdxSubLic = recIst.IdxSubLic;
                                currRec.IdxLic = recIst.IdxLic;
                            }
                            else
                            {
                                recIst = localDbCtx
                                        .DbSetSubLicenze
                                        .FirstOrDefault(x => x.Chiave == upRec.AppKey);
                                if (recIst != null)
                                {
                                    // log disuguaglianza chiave
                                    Log.Info($"Mismatch licenza SubLic 01 | CodApp: {upRec.CodApp} | AppKey: {upRec.AppKey} | App.CodImp: {upRec.CodImp} | Db.CodImp: {recIst.CodImpiego}");

                                    currRec.IdxSubLic = recIst.IdxSubLic;
                                    currRec.IdxLic = recIst.IdxLic;
                                    // salvo record variazione licenza
                                    LogCodImp recLogCodImp = new LogCodImp()
                                    {
                                        IdxLic = recIst.IdxLic,
                                        IdxSubLic = recIst.IdxSubLic,
                                        CodApp = upRec.CodApp,
                                        CodImpOld = recIst.CodImpiego,
                                        CodImpNew = upRec.CodImp,
                                        DtMod = DateTime.Now
                                    };
                                    localDbCtx
                                        .DbSetLogCodImp
                                        .Add(recLogCodImp);

                                    // aggiorno CodImp se non bloccato...
                                    if (recIst.VetoUnlock <= DateTime.Now)
                                    {
                                        recIst.CodImpiego = upRec.CodImp;
                                        localDbCtx.Entry(recIst).State = EntityState.Modified;
                                        // log update codImpiego
                                        Log.Info($"Aggiornamento SubLicenza 01 | CodImp Aggiornato | CodApp: {upRec.CodApp} | AppKey: {upRec.AppKey} | App.CodImp: {upRec.CodImp} | Db.CodImp: {recIst.CodImpiego}");
                                    }
                                }
                            }
                        }
                        // indico cambiato SSE variata la versione...
                        changed = !currRec.VersNum.Equals(upRec.VersNum);

                        // ...quindi aggiorno...
                        currRec.DtCheck = upRec.DtCheck;
                        currRec.MastKey = upRec.MastKey;
                        currRec.NumImp = upRec.NumImp;
                        currRec.VersNum = upRec.VersNum;
                        changed = true;
                        localDbCtx.Entry(currRec).State = EntityState.Modified;
                    }
                    else
                    {
                        bool insOk = false;
                        // cerco idxSublic da info...
                        var recIst = localDbCtx
                                        .DbSetSubLicenze
                                        .FirstOrDefault(x => x.Chiave == upRec.AppKey && x.CodImpiego == upRec.CodImp);
                        if (recIst != null)
                        {
                            upRec.IdxSubLic = recIst.IdxSubLic;
                            upRec.IdxLic = recIst.IdxLic;
                            insOk = true;
                        }
                        // se non trovo cerco SOLO con chiave...
                        else
                        {
                            recIst = localDbCtx
                                        .DbSetSubLicenze
                                        .FirstOrDefault(x => x.Chiave == upRec.AppKey);
                            if (recIst != null)
                            {
                                // log disuguaglianza chiave
                                Log.Info($"Mismatch licenza SubLic 02 | CodApp: {upRec.CodApp} | AppKey: {upRec.AppKey} | App.CodImp: {upRec.CodImp} | Db.CodImp: {recIst.CodImpiego}");

                                upRec.IdxSubLic = recIst.IdxSubLic;
                                upRec.IdxLic = recIst.IdxLic;
                                insOk = true;
                                // salvo record variazione licenza
                                LogCodImp recLogCodImp = new LogCodImp()
                                {
                                    IdxLic = recIst.IdxLic,
                                    IdxSubLic = recIst.IdxSubLic,
                                    CodApp = upRec.CodApp,
                                    CodImpOld = recIst.CodImpiego,
                                    CodImpNew = upRec.CodImp,
                                    DtMod = DateTime.Now
                                };
                                localDbCtx
                                    .DbSetLogCodImp
                                    .Add(recLogCodImp);

                                // aggiorno CodImp se non bloccato...
                                if (recIst.VetoUnlock <= DateTime.Now)
                                {
                                    recIst.CodImpiego = upRec.CodImp;
                                    localDbCtx.Entry(recIst).State = EntityState.Modified;
                                    // log update codImpiego
                                    Log.Info($"Aggiornamento SubLicenza 02 | CodImp Aggiornato | CodApp: {upRec.CodApp} | AppKey: {upRec.AppKey} | App.CodImp: {upRec.CodImp} | Db.CodImp: {recIst.CodImpiego}");
                                }
                            }
                        }

                        if (insOk)
                        {
                            // altrimenti aggiungo...
                            localDbCtx
                                .DbSetInstallRel
                                .Add(upRec);
                            changed = true;
                        }
                        else
                        {
                            // loggo problema
                            Log.Error($"Exception licenza SubLic non trovata | CodApp: {upRec.CodApp} | AppKey: {upRec.AppKey} | CodImp: {upRec.CodImp}");
                        }
                    }
                    // salvataggio!
                    try
                    {
                        // salvo
                        var res = localDbCtx.SaveChanges();
                        fatto = res != 0 && changed;
                    }
                    catch (Exception ex)
                    {
                        Log.Error($"Eccezione in InstallRelUpsert | IdxLic: {upRec.IdxLic} | CodApp: {upRec.CodApp} | VersNum: {upRec.VersNum} {Environment.NewLine}{ex}");
                    }
                }
            }
            return fatto;
        }

        /// <summary>
        /// Calcola statistiche install per il periodo indicato
        /// </summary>
        /// <param name="dtStart"></param>
        /// <param name="dtEnd"></param>
        /// <param name="CodInst">Filtro Cliente (CodInstall)</param>
        /// <param name="TipoApp">Filtro TipoApp</param>
        /// <returns></returns>
        public InstallStatusDTO InstallStatusGetInfo(DateTime dtStart, DateTime dtEnd, string CodInst, string TipoApp)
        {
            InstallStatusDTO answ = new InstallStatusDTO();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                // conteggio preliminare licenze impiegate
                var countSubLic = localDbCtx
                                    .DbSetSubLicenze
                                    .GroupBy(x => x.IdxLic)
                                    .Select(g => new
                                    {
                                        IdxLic = g.Key,
                                        NumLic = g.Count()
                                    });

                // raccolgo in primis info riguardo updater...
                answ.UpdaterList = localDbCtx
                                .DbSetLicenze
                                .Where(x => x.CodApp == "UpdateManager" && (x.CodInst == CodInst || string.IsNullOrEmpty(CodInst)))
                                 .Select(x => new ApplicativoDTO()
                                 {
                                     IdxLic = x.IdxLic,
                                     Chiave = x.Chiave,
                                     CodApp = x.CodApp,
                                     CodInst = x.CodInst,
                                     Descrizione = x.Descrizione,
                                     Tipo = x.Tipo,
                                     Scadenza = x.Scadenza,
                                     NumLicenze = x.NumLicenze,
                                     NumLicenzeAttive = countSubLic.Where(s => s.IdxLic == x.IdxLic).Select(c => c.NumLic).FirstOrDefault(),
                                     DataEnigma = x.DataEnigma,
                                     Enigma = x.Enigma,
                                     Payload = x.Payload
                                 })
                                .ToList();

                // recupero info x calcolare num chiamate nel periodo indicato...
                var rawCallList = localDbCtx
                                .DbSetLogCall
                                .Where(x => x.DataRif >= dtStart && x.DataRif < dtEnd && x.TargetUrl.Contains("api/release"))
                                .ToList();

                // eseguo conteggio x applicazione...
                var countAppCall = rawCallList
                                    .GroupBy(x => x.CodApp)
                                    .OrderByDescending(x => x.Count())
                                    .ToDictionary(g => g.Key, g => g.Count());
                answ.ReqCountApp = countAppCall;

                // ora conteggio per ORA chiamata...
                var countHour = rawCallList
                                    .GroupBy(x => x.DataRif.Date.AddHours(x.DataRif.Hour))
                                    .ToDictionary(g => g.Key, g => g.Count());

                answ.ReqCountHour = countHour;

                // ora conteggio per GIORNO chiamata...
                var countDay = rawCallList
                                    .GroupBy(x => x.DataRif.Date)
                                    .ToDictionary(g => g.Key, g => g.Count());

                answ.ReqCountDay = countDay;

                // ora passo a valorizzare i numeri relativi ad app e versioni...
                var instRelList = localDbCtx
                                    .DbSetInstallRel
                                    .Include(l => l.LicenzaNav)
                                    .Include(a => a.ApplicativoNav)
                                    .Where(x => (string.IsNullOrEmpty(CodInst) || x.LicenzaNav.CodInst == CodInst)
                                            && (string.IsNullOrEmpty(TipoApp) || x.ApplicativoNav.Tipo == TipoApp))
                                    .Select(x => new AppRelStatusDTO()
                                    {
                                        CodApp = x.CodApp,
                                        CodInst = x.LicenzaNav.CodInst,
                                        CodImp = x.CodImp,
                                        DtCheck = DateTime.Now,
                                        IdxLic = x.IdxLic,
                                        IdxSubLic = x.IdxSubLic,
                                        NumImp = x.NumImp,
                                        VersNumInstall = x.VersNum
                                    })
                                    .ToList();

                // recupero dati accessori x fix dati cliente/install
                var listLic = localDbCtx
                                    .DbSetLicenze
                                    .ToList();

                // recupero releases
                var listAllVers = localDbCtx
                                    .DbSetReleases
                                    .ToList();

                // elenco subLic x verifica validità
                var listSubLic = localDbCtx
                                    .DbSetSubLicenze
                                    .ToList();

                // recupero list enroll x recuperare nomi PC...
                var listEnroll = localDbCtx
                                    .DbSetEnrollReq
                                    .Where(x => x.IdxLic > 0)
                                    .ToList();

                // raggruppo x calcolare ultimo...
                var lastRel = listAllVers
                                .Where(i => i.IsReleased)
                                .GroupBy(x => x.CodApp)
                                .Select(group => group
                                                    .OrderByDescending(i => i.VersVal)
                                                    .ThenBy(i => i.ReleaseDate)
                                                    .First())
                                .ToList();

                // predispongo dati x macchine/clienti
                string kMachName = "MachineName";
                Dictionary<string, DeviceDTO> devicesDict = new Dictionary<string, DeviceDTO>();
                // elenco item da rimuovere che non trovano corrispondenza...
                List<int> subLic2rem = new List<int>();
                // aggiungo info cliente/installazione + vers corrente applicativi
                foreach (var item in instRelList)
                {
                    var recInst = listLic.FirstOrDefault(x => x.IdxLic == item.IdxLic);
                    if (recInst != null)
                    {
                        item.Cliente = recInst.CodInst;
                    }

                    var recSubLic = listSubLic.FirstOrDefault(x => x.IdxSubLic == item.IdxSubLic);
                    if (recSubLic != null)
                    {
                        // in questo caso controllo enroll x recuperare nome PC
                        var recEnroll = listEnroll
                                        .Where(x => x.IdxLic == item.IdxLic)
                                        .FirstOrDefault(x => x.ReqPayload.Contains(recSubLic.CodImpiego));

                        if (recEnroll != null && !string.IsNullOrEmpty(recEnroll.ReqPayload))
                        {
                            var dictPayload = JsonConvert.DeserializeObject<Dictionary<string, string>>(recEnroll.ReqPayload);
                            if (dictPayload != null && dictPayload.ContainsKey(kMachName))
                            {
                                item.PcInst = dictPayload[kMachName];
                                // verifico se salvare in dizionario macchina e cliente...
                                if (!devicesDict.ContainsKey(item.CodImp))
                                {
                                    devicesDict.Add(item.CodImp, new DeviceDTO()
                                    {
                                        CodImp = item.CodImp,
                                        CodInst = item.CodInst,
                                        DevName = item.PcInst
                                    });
                                }
                            }
                            else
                            {
                                item.PcInst = "--UNAVAILABLE--";
                            }
                        }
                    }
                    else
                    {
                        subLic2rem.Add(item.IdxSubLic);
                    }
                    var recRel = lastRel.FirstOrDefault(x => x.CodApp == item.CodApp);
                    if (recRel != null)
                    {
                        item.VersNumCurrent = recRel.VersNum;
                    }
                }
                foreach (var i2rem in subLic2rem)
                {
                    var cRec = instRelList.FirstOrDefault(x => x.IdxSubLic == i2rem);
                    if (cRec != null)
                    {
                        instRelList.Remove(cRec);
                    }
                }

                // salvo info di dettaglio stato applicazioni
                answ.InstallRelList = instRelList;
                // salvo dizionario macchine cliente...
                answ.InstDevices = devicesDict;
                //.OrderBy(x => x.Value.DevName)
                //.ToDictionary(x => x.Key, x => x.Value);

                // calcolo statistica aggregata stato app
                var appStatus = instRelList
                                    .GroupBy(x => x.CodApp)
                                    .Select(g => new AppStatusDTO()
                                    {
                                        CodApp = g.Key,
                                        DtCheck = DateTime.Now,
                                        NumInst = g.Count(),
                                        NumImp = g.Sum(x => x.NumImp),
                                        VersNumCurrent = g.First().VersNumCurrent,
                                        DictVersNumInst = CountRelVers(g.ToList()),
                                        DictUpdate = CountRelStatus(g.ToList()),
                                        DetailInstalled = g.ToList(),
                                    })
                                    .ToList();
                answ.InstallStatus = appStatus;
            }

            return answ;
        }

        /// <summary>
        /// Effettua refresh del payload della licenza dato info + enigma generato dal client
        /// </summary>
        /// <param name="codInst"></param>
        /// <param name="codApp"></param>
        /// <param name="masterKey"></param>
        /// <param name="enigma"></param>
        /// <returns></returns>
        public Task<bool> LicenseUpdatePayload(string codInst, string codApp, string masterKey, string enigma)
        {
            bool fatto = false;
            string newPayload = "";
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                LicenzaModel mainLic = localDbCtx
                                        .DbSetLicenze
                                        .Where(x => x.CodApp == codApp && x.CodInst == codInst && x.Chiave == masterKey)
                                        .Include(a => a.Attivazioni)
                                        .FirstOrDefault();
                if (mainLic != null)
                {
                    // se ho attivazioni...
                    if (mainLic.Attivazioni != null && mainLic.Attivazioni.Count > 0)
                    {
                        // se ho trovato --> recupero info applicazioni....
                        var hashList = mainLic
                            .Attivazioni
                            .OrderBy(x => x.CodImpiego)
                            .Select(x => x.CodImpiego)
                            .ToList();
                        // costruisco stringa da elenco impieghi...
                        StringBuilder sb = new StringBuilder();
                        foreach (var item in hashList)
                        {
                            sb.AppendLine(item);
                        }
                        string rawData = sb.ToString();

                        // hashing!
                        using (var md5 = MD5.Create())
                        {
                            byte[] InputBytes = Encoding.UTF8.GetBytes(rawData);
                            var byteHash = md5.ComputeHash(InputBytes);
                            newPayload = BitConverter.ToString(byteHash).Replace("-", "").ToLowerInvariant();
                        }
                    }
                    else
                    {
                        // tengo vecchio payload
                        newPayload = mainLic.Payload;
                    }

                    // salvo info!
                    mainLic.Enigma = enigma;
                    mainLic.DataEnigma = DateTime.Now;
                    mainLic.Payload = newPayload;

                    // indico modificato
                    localDbCtx.Entry(mainLic).State = EntityState.Modified;
                    // salvo
                    localDbCtx.SaveChanges();
                    fatto = true;
                }
            }
            // restituisco fatto
            return Task.FromResult(fatto);
        }

        /// <summary>
        /// Record licenza da ID
        /// </summary>
        /// <param name="licId"></param>
        /// <returns></returns>
        public LicenzaModel LicenzaById(int licId)
        {
            LicenzaModel dbResult = new LicenzaModel();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                DateTime oggi = DateTime.Today;
                dbResult = localDbCtx
                .DbSetLicenze
                .FirstOrDefault(x => x.IdxLic == licId);
            }
            return dbResult;
        }

        /// <summary>
        /// Record licenza da MasterKey
        /// </summary>
        /// <param name="MasterKey"></param>
        /// <returns></returns>
        public LicenzaModel LicenzaByKey(string MasterKey)
        {
            LicenzaModel dbResult = new LicenzaModel();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                DateTime oggi = DateTime.Today;
                dbResult = localDbCtx
                .DbSetLicenze
                .FirstOrDefault(x => x.Chiave == MasterKey);
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco LOG chiamate all'API dato filtro + limite record
        /// </summary>
        /// <param name="CodApp"></param>
        /// <param name="CodInst"></param>
        /// <param name="DateMax"></param>
        /// <param name="maxNumRec"></param>
        /// <returns></returns>
        public List<LogCallModel> LogCallGetFilt(string CodApp, string CodInst, DateTime DateMax, int maxNumRec = 360)
        {
            List<LogCallModel> dbResult = new List<LogCallModel>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                // recupero lista...
                dbResult = localDbCtx
                            .DbSetLogCall
                            .Where(x => x.CodApp == CodApp && x.CodInst == CodInst && x.DataRif <= DateMax)
                            .OrderByDescending(x => x.DataRif)
                            .Take(maxNumRec)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Update/insert record Loc chiamate API
        /// </summary>
        /// <param name="currRequest"></param>
        /// <returns></returns>
        public async Task<bool> LogCallUpsert(LogCallModel currRequest)
        {
            bool fatto = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                // cerco record esistente...
                var currRec = localDbCtx
                                .DbSetLogCall
                                .Where(x => x.CodApp == currRequest.CodApp && x.CodInst == currRequest.CodInst && x.DataRif == currRequest.DataRif && x.TargetUrl == currRequest.TargetUrl)
                                .FirstOrDefault();
                if (currRec != null)
                {
                    // se trovo aggiorno
                    currRec.NumCall = currRequest.NumCall;
                }
                else
                {
                    //altrimenti aggiungo
                    localDbCtx
                        .DbSetLogCall
                        .Add(currRequest);
                }

                // salvo
                await localDbCtx.SaveChangesAsync();

                fatto = true;
            }
            return fatto;
        }

        public bool ReleaseDelete(ReleaseModel rec2del)
        {
            bool fatto = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                // cerco record esistente...
                var currRec = localDbCtx
                                .DbSetReleases
                                .Where(x => x.IdxRel == rec2del.IdxRel && rec2del.IdxRel > 0)
                                .FirstOrDefault();
                if (currRec != null)
                {
                    localDbCtx
                        .DbSetReleases
                        .Remove(currRec);

                    // salvo
                    localDbCtx.SaveChanges();
                }
                fatto = true;
            }
            return fatto;
        }

        /// <summary>
        /// Elenco release (completo) dato un applicativo
        /// </summary>
        /// <param name="CodApp">Codice applicativo</param>
        /// <returns></returns>
        public List<ReleaseDTO> ReleaseDtoGetByApp(string CodApp)
        {
            List<ReleaseDTO> dbResult = new List<ReleaseDTO>();
            var rawData = ReleaseGetByApp(CodApp);
            dbResult = rawData
                .Where(x => x.IsReleased)
                .Select(x => new ReleaseDTO()
                {
                    CodApp = x.CodApp,
                    ReleaseDate = x.ReleaseDate,
                    VersNum = x.VersNum,
                    VersText = x.VersText,
                    VersVal = x.VersVal,
                    RelTags = x.RelTags
                }).
                ToList();
            return dbResult;
        }

        /// <summary>
        /// Elenco release (completo) dato un applicativo applicativi
        /// </summary>
        /// <param name="CodApp">Codice applicativo</param>
        /// <param name="VersMin">Versione minima richiesta</param>
        /// <returns></returns>
        public List<ReleaseDTO> ReleaseDtoGetByAppVers(string CodApp, string MinVers)
        {
            List<ReleaseDTO> dbResult = new List<ReleaseDTO>();
            var rawData = ReleaseGetByAppVers(CodApp, MinVers);
            dbResult = rawData
                .Where(x => x.IsReleased)
                .Select(x => new ReleaseDTO()
                {
                    CodApp = x.CodApp,
                    ReleaseDate = x.ReleaseDate,
                    VersNum = x.VersNum,
                    VersText = x.VersText,
                    VersVal = x.VersVal,
                    RelTags = x.RelTags
                }).
                ToList();
            return dbResult;
        }

        /// <summary>
        /// Elenco release (completo) dato un applicativo applicativi
        /// </summary>
        /// <param name="CodApp">Codice applicativo</param>
        /// <param name="VersMin">Versione minima richiesta</param>
        /// <param name="VersMax">Versione massima consentita</param>
        /// <returns></returns>
        public List<ReleaseDTO> ReleaseDtoGetByAppVersLimit(string CodApp, string VersMin, string VersMax)
        {
            List<ReleaseDTO> dbResult = new List<ReleaseDTO>();
            Version versLimit = new Version(VersMax);
            var rawData = ReleaseGetByAppVers(CodApp, VersMin);
            dbResult = rawData
                .Where(x => x.IsReleased)
                .Select(x => new ReleaseDTO()
                {
                    CodApp = x.CodApp,
                    ReleaseDate = x.ReleaseDate,
                    VersNum = x.VersNum,
                    VersText = x.VersText,
                    VersVal = x.VersVal,
                    RelTags = x.RelTags,
                    IsPermitted = x.VersVal <= versLimit
                }).
                ToList();
            return dbResult;
        }

        /// <summary>
        /// Elenco release (completo) dato un applicativo applicativi
        /// </summary>
        /// <param name="CodApp">Codice applicativo</param>
        /// <returns></returns>
        public List<ReleaseModel> ReleaseGetByApp(string CodApp)
        {
            List<ReleaseModel> dbResult = new List<ReleaseModel>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                // calcolo DTO applicativi
                dbResult = localDbCtx
                    .DbSetReleases
                    .Where(x => (x.CodApp.ToLower() == CodApp.ToLower()))
                    .OrderBy(o => o.ReleaseDate)
                    .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco release (completo) dato un applicativo applicativi
        /// </summary>
        /// <param name="CodApp">Codice applicativo</param>
        /// <param name="VersMin">Versione minima richiesta</param>
        /// <returns></returns>
        public List<ReleaseModel> ReleaseGetByAppVers(string CodApp, string MinVers)
        {
            List<ReleaseModel> dbResult = new List<ReleaseModel>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                Version mVers = new Version(MinVers);
                // calcolo DTO applicativi
                dbResult = localDbCtx
                    .DbSetReleases
                    .Where(x => (x.CodApp.ToLower() == CodApp.ToLower()))
                    .ToList();

                // verifico per il criterio versione...
                dbResult = dbResult
                            .Where(x => x.VersVal >= mVers)
                            .OrderBy(o => o.ReleaseDate)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco di TUTTE le release CRITICAL come dizionario CodApp - release
        /// </summary>
        /// <param name="CodApp">Codice applicativo</param>
        /// <returns></returns>
        public Dictionary<string, ReleaseDTO> ReleaseGetCritical()
        {
            Dictionary<string, ReleaseDTO> dbResult = new Dictionary<string, ReleaseDTO>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                DateTime adesso = DateTime.Now;
                var rawList = localDbCtx
                          .DbSetReleases
                          .Where(x => x.ReleaseDate <= adesso)
                          .GroupBy(x => x.CodApp)
                          .Select(g => new
                          {
                              key = g.Key,
                              val = g.Where(x => x.ReleaseDate <= adesso)
                                      .ToList()
                          })
                          .ToDictionary(x => x.key, x => x.val);

                // ordinamento per VERO numero versione è fatto in ram --> devo riordinare qui...
                var dictLast = rawList.ToDictionary(x => x.Key, x => x.Value.OrderByDescending(v => v.VersVal).FirstOrDefault());

                // ora prendo SOLO quelli critical
                dbResult = dictLast
                            .Where(x => x.Value.RelTags == "CRITICAL")
                            .ToDictionary(x => x.Key, x => new ReleaseDTO()
                            {
                                CodApp = x.Value.CodApp,
                                ReleaseDate = x.Value.ReleaseDate,
                                VersText = x.Value.VersText,
                                VersNum = x.Value.VersNum,
                                VersVal = x.Value.VersVal,
                                RelTags = x.Value.RelTags
                            });
            }
            return dbResult;
        }

        /// <summary>
        /// Upsert Record Release
        /// </summary>
        /// <param name="currItem"></param>
        /// <returns></returns>
        public async Task<bool> ReleaseUpsert(ReleaseModel currItem)
        {
            bool fatto = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                // cerco record esistente...
                var currRec = localDbCtx
                                .DbSetReleases
                                .Where(x => (x.IdxRel == currItem.IdxRel && currItem.IdxRel > 0) || (x.CodApp == currItem.CodApp && x.VersNum == currItem.VersNum && x.VersText == currItem.VersText))
                                .FirstOrDefault();
                if (currRec != null)
                {
                    // se trovo aggiorno
                    currRec.VersNum = currItem.VersNum;
                    currRec.VersText = currItem.VersText;
                    currRec.ReleaseDate = currItem.ReleaseDate;
                    currRec.RelTags = currItem.RelTags;
                    localDbCtx.Entry(currRec).State = EntityState.Modified;
                }
                else
                {
                    // altrimenti aggiungo
                    localDbCtx
                        .DbSetReleases
                        .Add(currItem);
                }

                // salvo
                await localDbCtx.SaveChangesAsync();
                fatto = true;
            }
            return fatto;
        }

        /// <summary>
        /// Annulla modifiche su una specifica entity (cancel update)
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public bool rollBackEntity(object item)
        {
            bool answ = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                try
                {
                    if (localDbCtx.Entry(item).State == EntityState.Deleted || localDbCtx.Entry(item).State == EntityState.Modified)
                    {
                        localDbCtx.Entry(item).Reload();
                    }
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in rollBackEntity{Environment.NewLine}{exc}");
                }
            }
            return answ;
        }

        /// <summary>
        /// Statistiche del LOGchiamate all'API dato filtro
        /// </summary>
        /// <param name="DateFrom">Data minima</param>
        /// <param name="DateTo">DataMax</param>
        /// <param name="SearchVal">Valore cercato, se "" è tutti</param>
        /// <returns></returns>
        public List<StatsCallModel> StatsLogCallGetFilt(DateTime DateFrom, DateTime DateTo, string SearchVal = "")
        {
            List<StatsCallModel> dbResult = new List<StatsCallModel>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                var dtFrom = new SqlParameter("@DateFrom", DateFrom);
                var dtTo = new SqlParameter("@DateTo", DateTo);
                var srcVal = new SqlParameter("@SearchVal", SearchVal);
                dbResult = localDbCtx
                            .DbSetStatCall
                            .FromSqlRaw("exec dbo.stp_StatsCall_filt @DateFrom, @DateTo, @SearchVal", dtFrom, dtTo, srcVal)
                            .AsNoTracking()
                            .ToList();
            }
            return dbResult;
        }

        public bool TicketAddNew(SupportRequest currRequest)
        {
            bool fatto = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                // recupero locenza...
                var currLic = localDbCtx
                                .DbSetLicenze
                                .Where(x => x.CodApp == currRequest.CodApp && x.CodInst == currRequest.CodInst)
                                .FirstOrDefault();

                if (currLic != null)
                {
                    TicketModel newTicket = new TicketModel()
                    {
                        DtReq = DateTime.Now,
                        IdxLic = currLic.IdxLic,
                        IdxSubLic = currRequest.idxSubLic,
                        CodImpiego = currRequest.CodImp,
                        ContactEmail = currRequest.ContactEmail,
                        ContactPhone = currRequest.ContactPhone,
                        ReqBody = currRequest.ReqBody,
                        Status = StatoRichiesta.Richiesta,
                        Tipo = TipoLicenza.UserKey,
                        TType = currRequest.Tipo
                    };

                    localDbCtx
                        .DbSetTicket
                        .Add(newTicket);

                    localDbCtx.SaveChanges();

                    fatto = true;
                }
            }
            return fatto;
        }

        public List<TicketDTO> TicketGetAll(bool onlyOpen, int maxNum)
        {
            List<TicketDTO> dbResult = new List<TicketDTO>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                dbResult = localDbCtx
                            .DbSetTicket
                            .Where(x => (x.Status <= StatoRichiesta.Valutazione || !onlyOpen))
                            .OrderByDescending(x => x.IdxTicket)
                            .Include(l => l.LicenzaNav)
                            .Take(maxNum)
                            .Select(x => new TicketDTO
                            {
                                CodApp = x.LicenzaNav.CodApp,
                                CodInst = x.LicenzaNav.CodInst,
                                IdxTicket = x.IdxTicket,
                                IdxLic = x.IdxLic,
                                IdxSubLic = x.IdxSubLic,
                                CodImpiego = x.CodImpiego,
                                ContactName = x.ContactName,
                                ContactEmail = x.ContactEmail,
                                ContactPhone = x.ContactPhone,
                                DtReq = x.DtReq,
                                ReqBody = x.ReqBody,
                                Status = x.Status,
                                SupplAnsw = x.SupplAnsw,
                                SupplEmail = x.SupplEmail,
                                SupplUserCode = x.SupplUserCode,
                                Tipo = x.Tipo
                            })
                            .ToList();
            }
            return dbResult;
        }

        public List<TicketDTO> TicketGetFilt(bool onlyOpen, TipologiaTicket Tipo, string CodApp, string CodInst, string MasterKey, int maxNum)
        {
            List<TicketDTO> dbResult = new List<TicketDTO>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                // recupero licenza...
                var currLic = localDbCtx
                                .DbSetLicenze
                                .Where(x => x.CodApp == CodApp && x.CodInst == CodInst && x.Chiave == MasterKey)
                                .FirstOrDefault();

                if (currLic != null)
                {
                    dbResult = localDbCtx
                                .DbSetTicket
                                .Where(x => x.IdxLic == currLic.IdxLic && (x.Status <= StatoRichiesta.Valutazione || !onlyOpen) && (x.TType == Tipo || Tipo == TipologiaTicket.ND))
                                .OrderByDescending(x => x.IdxTicket)
                                .Take(maxNum)
                                .Select(x => new TicketDTO
                                {
                                    IdxTicket = x.IdxTicket,
                                    IdxLic = x.IdxLic,
                                    IdxSubLic = x.IdxSubLic,
                                    CodImpiego = x.CodImpiego,
                                    ContactName = x.ContactName,
                                    ContactEmail = x.ContactEmail,
                                    ContactPhone = x.ContactPhone,
                                    DtReq = x.DtReq,
                                    ReqBody = x.ReqBody,
                                    Status = x.Status,
                                    SupplAnsw = x.SupplAnsw,
                                    SupplEmail = x.SupplEmail,
                                    SupplUserCode = x.SupplUserCode,
                                    Tipo = x.Tipo
                                })
                                .ToList();
                }
            }
            return dbResult;
        }

        public List<TicketDTO> TicketGetFilt(bool onlyOpen, string CodApp, string CodInst)
        {
            List<TicketDTO> dbResult = new List<TicketDTO>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                dbResult = localDbCtx
                            .DbSetTicket
                            .Where(x => (x.Status <= StatoRichiesta.Valutazione || !onlyOpen) && (x.LicenzaNav.CodApp == CodApp || string.IsNullOrEmpty(CodApp)) && (x.LicenzaNav.CodInst == CodInst || string.IsNullOrEmpty(CodInst)))
                            .OrderByDescending(x => x.IdxTicket)
                            .Include(l => l.LicenzaNav)
                            .Select(x => new TicketDTO
                            {
                                CodApp = x.LicenzaNav.CodApp,
                                CodInst = x.LicenzaNav.CodInst,
                                IdxTicket = x.IdxTicket,
                                IdxLic = x.IdxLic,
                                IdxSubLic = x.IdxSubLic,
                                CodImpiego = x.CodImpiego,
                                ContactName = x.ContactName,
                                ContactEmail = x.ContactEmail,
                                ContactPhone = x.ContactPhone,
                                DtReq = x.DtReq,
                                ReqBody = x.ReqBody,
                                Status = x.Status,
                                SupplAnsw = x.SupplAnsw,
                                SupplEmail = x.SupplEmail,
                                SupplUserCode = x.SupplUserCode,
                                Tipo = x.Tipo,
                                TType = x.TType
                            })
                            .ToList();
            }
            return dbResult;
        }

        public List<TicketDTO> TicketGetFiltAllLic(bool onlyOpen, TipologiaTicket Tipo, string CodApp, string CodInst, int maxNum)
        {
            List<TicketDTO> dbResult = new List<TicketDTO>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                // recupero locenza...
                var currLic = localDbCtx
                                .DbSetLicenze
                                .Where(x => x.CodApp == CodApp && x.CodInst == CodInst)
                                .FirstOrDefault();

                if (currLic != null)
                {
                    dbResult = localDbCtx
                                .DbSetTicket
                                .Where(x => (x.LicenzaNav.CodApp == CodApp && x.LicenzaNav.CodInst == CodInst) && (x.Status <= StatoRichiesta.Valutazione || !onlyOpen) && (x.TType == Tipo || Tipo == TipologiaTicket.ND))
                                .OrderByDescending(x => x.IdxTicket)
                                .Take(maxNum)
                                .Select(x => new TicketDTO
                                {
                                    IdxTicket = x.IdxTicket,
                                    IdxLic = x.IdxLic,
                                    IdxSubLic = x.IdxSubLic,
                                    CodImpiego = x.CodImpiego,
                                    ContactName = x.ContactName,
                                    ContactEmail = x.ContactEmail,
                                    ContactPhone = x.ContactPhone,
                                    DtReq = x.DtReq,
                                    ReqBody = x.ReqBody,
                                    Status = x.Status,
                                    SupplAnsw = x.SupplAnsw,
                                    SupplEmail = x.SupplEmail,
                                    SupplUserCode = x.SupplUserCode,
                                    Tipo = x.Tipo
                                })
                                .ToList();
                }
            }
            return dbResult;
        }

        public bool TicketUpdateState(int IdxTicket, StatoRichiesta NewStatus)
        {
            bool fatto = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                var currRec = localDbCtx
                                .DbSetTicket
                                .Where(x => x.IdxTicket == IdxTicket)
                                .FirstOrDefault();

                if (currRec != null)
                {
                    currRec.Status = NewStatus;
                    localDbCtx.SaveChanges();
                    fatto = true;
                }
            }
            return fatto;
        }

        public bool UpsertInstallazione(InstallazioneModel updItem)
        {
            bool done = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                try
                {
                    InstallazioneModel currData = localDbCtx
                       .DbSetInst
                       .Where(x => x.CodInst == updItem.CodInst)
                       .FirstOrDefault();
                    if (currData != null)
                    {
                        // aggiorno valori
                        currData.Descrizione = updItem.Descrizione;
                        currData.Cliente = updItem.Cliente;
                        currData.Contatto = updItem.Contatto;
                        currData.Email = updItem.Email;
                        localDbCtx.Entry(currData).State = EntityState.Modified;
                    }
                    else
                    {
                        localDbCtx
                            .DbSetInst
                            .Add(updItem);
                    }
                    localDbCtx.SaveChanges();
                    done = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in UpdateApplicazioni:{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        public bool UpsertLicenza(LicenzaModel updItem)
        {
            bool done = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                try
                {
                    LicenzaModel currData = localDbCtx
                       .DbSetLicenze
                       .Where(x => x.IdxLic == updItem.IdxLic)
                       .FirstOrDefault();
                    if (currData != null)
                    {
                        // storicizzo vecchia licenza in log
                        var newLogLic = new LogLicenzaModel()
                        {
                            Chiave = currData.Chiave,
                            CodApp = currData.CodApp,
                            CodInst = currData.CodInst,
                            Descrizione = currData.Descrizione,
                            IdxLic = currData.IdxLic,
                            Locked = true,
                            NumLicenze = currData.NumLicenze,
                            Scadenza = currData.Scadenza,
                            Tipo = currData.Tipo
                        };

                        localDbCtx
                            .DbSetLogLicenze
                            .Add(newLogLic);

                        // aggiorno valori licenza
                        currData.CodApp = updItem.CodApp;
                        currData.CodInst = updItem.CodInst;
                        currData.NumLicenze = updItem.NumLicenze;
                        currData.Descrizione = updItem.Descrizione;
                        currData.Chiave = updItem.ChiaveCalc;
                        currData.Scadenza = updItem.Scadenza;
                        currData.Locked = updItem.Locked;
                        currData.Payload = updItem.Payload;
                        currData.DataEnigma = updItem.DataEnigma;
                        currData.Enigma = updItem.Enigma;
                        localDbCtx.Entry(currData).State = EntityState.Modified;
                    }
                    else
                    {
                        localDbCtx
                            .DbSetLicenze
                            .Add(updItem);
                    }
                    localDbCtx.SaveChanges();
                    done = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in UpdateLicenze:{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        public bool UpsertLogLic(LogLicenzaModel updItem)
        {
            bool done = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                try
                {
                    localDbCtx
                        .DbSetLogLicenze
                        .Add(updItem);
                    localDbCtx.SaveChanges();
                    done = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in UpdateApplicazioni:{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

        #endregion Public Methods

        #region Protected Methods

        /// <summary>
        /// Esegue conteggio numero versioni in elenco
        /// </summary>
        /// <param name="listRel"></param>
        /// <returns></returns>
        protected Dictionary<int, int> CountRelStatus(List<AppRelStatusDTO> listRel)
        {
            Dictionary<int, int> answ = new Dictionary<int, int>();
            if (listRel != null && listRel.Count > 0)
            {
                answ = listRel
                            .GroupBy(x => x.UpToDateStatus)
                            .ToDictionary(g => g.Key, g => g.Count());
            }
            return answ;
        }

        /// <summary>
        /// Esegue conteggio numero versioni in elenco
        /// </summary>
        /// <param name="listRel"></param>
        /// <returns></returns>
        protected Dictionary<string, int> CountRelVers(List<AppRelStatusDTO> listRel)
        {
            Dictionary<string, int> answ = new Dictionary<string, int>();
            if (listRel != null && listRel.Count > 0)
            {
                answ = listRel
                            .GroupBy(x => x.VersNumInstall)
                            .ToDictionary(g => g.Key, g => g.Count());
            }
            return answ;
        }

        #endregion Protected Methods

        #region Private Fields

        private static IConfiguration _configuration;
        private static NLog.Logger Log = LogManager.GetCurrentClassLogger();

        #endregion Private Fields

#if false
        public List<TicketModel> TicketGetByLic(int IdxLic, int maxNum)
        {
            List<TicketModel> dbResult = new List<TicketModel>();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                dbResult = localDbCtx
                            .DbSetTicket
                            .Where(x => x.IdxLic == IdxLic)
                            .OrderByDescending(x => x.DtReq)
                            .Take(maxNum)
                            .ToList();
            }
            return dbResult;
        }
#endif
    }
}