using Core;
using Core.DTO;
using LiMan.DB.DBModels;
using LiMan.DB.DTO;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using NLog;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace LiMan.DB.Controllers
{
    public class DbController : IDisposable
    {
        #region Private Fields

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

        #endregion Private Fields

        #region Public Constructors

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

        #endregion Public Constructors

        #region Public Methods

        /// <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 attivfazioni 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;
                dbResult = localDbCtx
                .DbSetSubLicenze
                .Where(x => x.IdxLic == idxLic)
                .OrderBy(o => o.VetoUnlock)
                .ToList();
            }
            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)
        {
            bool answ = false;

            // verifica se si può procedere...
            var licenza = GetLicenza(MasterKey);
            if (licenza != null)
            {
                var attivList = GetAttivazioniByLic(licenza.IdxLic, false);
                if (licenza.IsValid && (licenza.NumLicenze >= (attivList.Count + ParamDict.Count)))
                {
                    DateTime vetoDate = DateTime.Today.AddDays(numDayVeto);
                    using (LMDbContext localDbCtx = new LMDbContext(_configuration))
                    {
                        try
                        {
                            // genero subLicenze...
                            var newRec = ParamDict
                                .Select(x => new SubLicenzaModel()
                                {
                                    IdxLic = licenza.IdxLic,
                                    CodImpiego = x.Key,
                                    VetoUnlock = vetoDate,
                                    Tipo = TipoLicenza.UserKey,
                                    Chiave = x.Value
                                })
                                .ToList();
                            localDbCtx
                                .DbSetSubLicenze
                                .AddRange(newRec);

                            localDbCtx.SaveChanges();
                        }
                        catch (Exception exc)
                        {
                            Log.Error($"Errore in AttivazioniTryAdd per MasterKey {MasterKey} | #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 MasterKey {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 bool DbForceMigrate()
        {
            bool answ = false;
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                try
                {
                    localDbCtx.DbForceMigrate();
                    answ = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in DbForceMigrate");
                }
            }
            return answ;
        }

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

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

        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 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.Attivazioni)
                .Include(t => t.Tickets.Where(c => c.Status < StatoRichiesta.Approvata))
                .OrderByDescending(o => o.Scadenza)
                .ToList();
            }
            return dbResult;
        }

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

        public LicenzaModel LicenzaByKey(string MasterKey)
        {
            LicenzaModel dbResult = new LicenzaModel();
            using (LMDbContext localDbCtx = new LMDbContext(_configuration))
            {
                DateTime oggi = DateTime.Today;
                dbResult = localDbCtx
                .DbSetLicenze
                .Where(x => x.Chiave == MasterKey)
                .FirstOrDefault();
            }
            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;
        }

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

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

#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

        public bool UpsertApplicazione(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;
                        localDbCtx.Entry(currData).State = EntityState.Modified;
                    }
                    else
                    {
                        localDbCtx
                            .DbSetApp
                            .Add(updItem);
                    }
                    localDbCtx.SaveChanges();
                    done = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione in UpdateApplicazioni:{Environment.NewLine}{exc}");
                }
            }
            return done;
        }

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