using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using MP.Data.DbModels;
using NLog;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace MP.Data.Controllers
{
    public class MpInveController : IDisposable
    {
        #region Public Constructors

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

        #endregion Public Constructors

        #region Public Methods

        public void Dispose()
        {
            _configuration = null;
        }

        #region gestione lotti esterni

        /// <summary>
        /// Elenco lotti esterni presenti sul db di ARCA
        /// </summary>
        /// <param name="codArt">Codice articolo</param>
        /// <param name="codLotto">Codice lotto</param>
        /// <param name="codMagazzino">Codice magazzino</param>
        /// <returns></returns>
        public List<AnagLottiArca> LottoEsterno(string codArt, string codLotto, string codMagazzino)
        {
            List<AnagLottiArca> dbResult = new List<AnagLottiArca>();
            using (var dbCtx = new MoonPro_ISContext(_configuration))
            {
                var DataGiac = new SqlParameter("@DataGiac", DBNull.Value);
                var CodArt = new SqlParameter("@CodArt", codArt);
                var CodLotto = new SqlParameter("@CodLotto", codLotto);
                var CodMagaz = new SqlParameter("@CodMagaz", codMagazzino);
                var OnlyTest = new SqlParameter("@OnlyTest", false);

                dbResult = dbCtx
                            .DbLottoArca
                            .FromSqlRaw("exec dbo.stp_GIAC_getByDate @DataGiac,@CodArt,@CodLotto,@CodMagaz,@OnlyTest", DataGiac, CodArt, CodLotto, CodMagaz, OnlyTest)
                            .AsNoTracking()
                            .AsEnumerable()
                            .ToList();
            }
            return dbResult;
        }

        #endregion gestione lotti esterni

        #region gestione articoli

        /// <summary>
        /// articolo MAG corrispondente all' articolo selezionato
        /// </summary>
        /// <param name="artSearch"></param>
        /// <returns></returns>
        public AnagArticoli_MAG artBySearch(string artSearch)
        {
            AnagArticoli_MAG dbResult = new AnagArticoli_MAG();
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                dbResult = dbCtx
                .DbArtMag
                .AsNoTracking()
                .Where(x => x.CodArt == artSearch)
                .FirstOrDefault();
            }
            return dbResult;
        }

        #endregion gestione articoli

        private DbContextOptions<MoonProContext> options;
        #region gestione config

        /// <summary>
        /// Elenco da tabella Config
        /// </summary>
        /// <returns></returns>
        public List<ConfigModel> ConfigGetAll()
        {
            List<ConfigModel> dbResult = new List<ConfigModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                .DbSetConfig
                .AsNoTracking()
                .OrderBy(x => x.Chiave)
                .ToList();
            }
            return dbResult;
        }

        #endregion gestione config

        #region gestione scansioni

        /// <summary>
        /// Elenco Scansioni Totali
        /// </summary>
        /// <returns></returns>
        public List<ScanDataModel> ScanList()
        {
            List<ScanDataModel> dbResult = new List<ScanDataModel>();
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                dbResult = dbCtx
                            .DbScanData
                            .AsNoTracking()
                            .OrderByDescending(x => x.DtScan)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco Scansioni Totali
        /// </summary>
        /// <param name="Udc"></param>
        /// <param name="InveSessId"></param>
        /// <returns></returns>
        public ScanDataModel ScanByUdcSession(string Udc, int InveSessId)
        {
            ScanDataModel dbResult = new ScanDataModel();
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                if (InveSessId != 0)
                {
                    dbResult = dbCtx
                                .DbScanData
                                .Where(x => (x.ScanValue == Udc) && (x.InveSessID == InveSessId))
                                .AsNoTracking()
                                .OrderByDescending(x => x.DtScan)
                                .FirstOrDefault();
                }
                else
                {
                    dbResult = dbCtx
                                .DbScanData
                                .Where(x => (x.ScanValue == Udc))
                                .AsNoTracking()
                                .OrderByDescending(x => x.DtScan)
                                .FirstOrDefault();
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco Scansioni dato lotto e sessione
        /// </summary>
        /// <param name="Lotto"></param>
        /// <param name="InveSessId"></param>
        /// <returns></returns>
        public List<ScanDataModel> ScanByLottoSession(string Lotto, int InveSessId)
        {
            List<ScanDataModel> dbResult = new List<ScanDataModel>();
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                if (InveSessId != 0)
                {
                    dbResult = dbCtx
                                .DbScanData
                                .Where(x => (x.Lotto == Lotto) && (x.InveSessID == InveSessId))
                                .AsNoTracking()
                                .OrderByDescending(x => x.DtScan)
                                .ToList();
                }
                else
                {
                    dbResult = dbCtx
                                .DbScanData
                                .Where(x => (x.Lotto == Lotto))
                                .AsNoTracking()
                                .OrderByDescending(x => x.DtScan)
                                .ToList();
                }
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco Scansioni per valore, operatore e sessione
        /// </summary>
        /// <returns></returns>
        public List<ScanDataModel> ScanByValueSessionOpr(string scanData, int InveSessId, string idOpr)
        {
            List<ScanDataModel> dbResult = new List<ScanDataModel>();
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                dbResult = dbCtx
                            .DbScanData
                            .Where(x => (x.ScanValue == scanData) && (x.InveSessID == InveSessId) && (x.UserScan == idOpr))
                            .AsNoTracking()
                            .OrderByDescending(x => x.DtScan)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Export dei dati data la sessione
        /// </summary>
        /// <param name="sessionID"></param>
        /// <returns></returns>
        public List<VExpInveSession> ExportSessionDetail(int sessionID)
        {
            List<VExpInveSession> dbResult = new List<VExpInveSession>();
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                dbResult = dbCtx
                            .DbExpSessione
                            .Where(x => (x.InveSessId == sessionID))
                            .AsNoTracking()
                            .OrderBy(x => x.DtScan)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco Scansioni dato Id sessione inventario
        /// </summary>
        /// <param name="InveSessId"></param>
        /// <returns></returns>
        public async Task<bool> updateScan(ScanDataModel editRec)
        {
            bool fatto = false;
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                try
                {
                    var currRec = dbCtx
                                        .DbScanData
                                        .Where(x => x.ScanID == editRec.ScanID)
                                        .FirstOrDefault();
                    if (currRec != null)
                    {
                        currRec.ScanID = editRec.ScanID;
                        currRec.CodArticolo = editRec.CodArticolo;
                        currRec.DtScan = editRec.DtScan;
                        currRec.UserScan = editRec.UserScan;
                        currRec.ScanValue = editRec.ScanValue;
                        currRec.IsForced = editRec.IsForced;
                        currRec.Lotto = editRec.Lotto;
                        currRec.RifExt = editRec.RifExt;
                        currRec.Qty = editRec.Qty;
                        currRec.Note = editRec.Note;
                        currRec.IsKnown = editRec.IsKnown;
                        currRec.IsUnique = editRec.IsUnique;
                        currRec.InveSessID = editRec.InveSessID;
                        dbCtx.Entry(currRec).State = EntityState.Modified;
                    }
                    else
                    {
                        dbCtx
                            .DbScanData
                            .Add(editRec);
                    }
                    await dbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante ScanBySession{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Inserisco nuova scansione
        /// </summary>
        /// <param name="InveSessId"></param>
        /// <returns></returns>

        /// <summary>
        /// insert di un record sessione
        /// </summary>
        /// <returns></returns>
        public async Task<bool> InsertNewScansione(ScanDataModel newRec)
        {
            bool fatto = false;
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                try
                {
                    dbCtx
                        .DbScanData
                        .Add(newRec);
                    await dbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante InsertNewScansione{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// delete scansione
        /// </summary>
        /// <returns></returns>
        public async Task<bool> deleteScansione(ScanDataModel record)
        {
            bool fatto = false;
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                try
                {
                    dbCtx
                        .DbScanData
                        .Remove(record);
                    await dbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante deleteScansione{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        #endregion gestione scansioni

        #region gestione operatori

        /// <summary>
        /// Elenco operatori
        /// </summary>
        /// /// <param name="MatrOpr"></param>
        /// /// <param name="authKey"></param>
        /// <returns></returns>
        public List<AnagOperatoriModel> ElencoOperatori()
        {
            List<AnagOperatoriModel> dbResult = new List<AnagOperatoriModel>();
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbOperatori
                            .Where(s => s.MatrOpr > 0)
                            .AsNoTracking()
                            .OrderBy(x => x.MatrOpr)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// login operatori
        /// </summary>
        /// /// <param name="MatrOpr"></param>
        /// /// <param name="authKey"></param>
        /// <returns></returns>
        public bool LoginOperatore(int matrOpr, string authKey)
        {
            List<AnagOperatoriModel> dbResult = new List<AnagOperatoriModel>();
            bool answ = false;
            using (var dbCtx = new MoonProContext(options))
            {
                dbResult = dbCtx
                            .DbOperatori
                            .Where(s => (s.MatrOpr > 0) && (s.MatrOpr == matrOpr) && (s.authKey == authKey))
                            .AsNoTracking()
                            .ToList();
                if (dbResult.Count == 1)
                {
                    answ = true;
                }
            }
            return answ;
        }

        #endregion gestione operatori

        #region gestione magazzini

        /// <summary>
        /// Elenco Magazzini
        /// </summary>
        /// <returns></returns>
        public List<AnagMagModel> MagazziniList()
        {
            List<AnagMagModel> dbResult = new List<AnagMagModel>();
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                dbResult = dbCtx
                            .DbAnagMag
                            .AsNoTracking()
                            .OrderBy(x => x.MagID)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// insert di un record magazzino
        /// </summary>
        /// <returns></returns>
        public async Task<bool> InsertNewMag(AnagMagModel newRec)
        {
            bool fatto = false;
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                try
                {
                    dbCtx
                        .DbAnagMag
                        .Add(newRec);
                    await dbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante InsertNewMag{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// modifica di un record magazzino
        /// </summary>
        /// <returns></returns>
        public async Task<bool> UpdateMag(AnagMagModel magRec)
        {
            bool fatto = false;
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                try
                {
                    var dbResult =
                    dbCtx
                        .DbAnagMag
                        .AsNoTracking()
                        .Where(x => x.MagID == magRec.MagID)
                        .FirstOrDefault();
                    if (dbResult != null)
                    {
                        if (dbResult.DescMag != magRec.DescMag)
                        {
                            dbResult.DescMag = magRec.DescMag.ToUpper();
                            dbCtx.Entry(dbResult).State = EntityState.Modified;
                        }
                    }
                    await dbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante UpdateMag{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// delete magazzino
        /// </summary>
        /// <returns></returns>
        public async Task<bool> DeleteMag(AnagMagModel record)
        {
            bool fatto = false;
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                try
                {
                    dbCtx
                        .DbAnagMag
                        .Remove(record);
                    await dbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante deleteMag{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        #endregion gestione magazzini

        #region gestione UDC

        /// <summary>
        /// elenco Udc
        /// </summary>
        /// <returns></returns>
        public List<AnagUdcModel> ElencoUdc()
        {
            List<AnagUdcModel> dbResult = new List<AnagUdcModel>();
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                dbResult = dbCtx
                            .DbUdcData
                            .Include(m => m.lottoNav)
                            .AsNoTracking()
                            .OrderByDescending(x => x.UDC)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// check Udc
        /// </summary>
        /// <param name="Udc"></param>
        /// <returns></returns>
        public AnagUdcModel IsUDC(string Udc)
        {
            AnagUdcModel dbResult = new AnagUdcModel();
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                dbResult = dbCtx
                            .DbUdcData
                            .Where(x => x.UDC == Udc)
                            .Include(m => m.lottoNav)
                            .AsNoTracking()
                            .OrderByDescending(x => x.UDC)
                            .FirstOrDefault(); ;
            }
            return dbResult;
        }

        #endregion gestione UDC

        #region gestione lotti interni

        /// <summary>
        /// elenco lotti
        /// </summary>
        /// <returns></returns>
        public List<AnagLottoModel> ElencoLotti()
        {
            List<AnagLottoModel> dbResult = new List<AnagLottoModel>();
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                dbResult = dbCtx
                            .DbLottoData
                            .AsNoTracking()
                            .OrderByDescending(x => x.Lotto)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// check lotto
        /// </summary>
        /// <param name="lotto"></param>
        /// <returns></returns>
        public AnagLottoModel LottoInterno(string lotto)
        {
            AnagLottoModel dbResult = new AnagLottoModel();
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                dbResult = dbCtx
                            .DbLottoData
                            .Where(x => x.Lotto == lotto)
                            .AsNoTracking()
                            .OrderByDescending(x => x.Lotto)
                            .FirstOrDefault();
            }
            return dbResult;
        }

        #endregion gestione lotti interni

        #region gestione sessione

        /// <summary>
        /// delete sessione
        /// </summary>
        /// <returns></returns>
        public async Task<bool> deleteSessione(InventorySessionModel record)
        {
            bool fatto = false;
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                try
                {
                    dbCtx
                        .DbInveSess
                        .Remove(record);
                    await dbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante deleteSessione{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// insert di un record sessione
        /// </summary>
        /// <returns></returns>
        public async Task<bool> InsertNewSessione(InventorySessionModel newRec)
        {
            bool fatto = false;
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                try
                {
                    dbCtx
                        .DbInveSess
                        .Add(newRec);
                    await dbCtx.SaveChangesAsync();
                    fatto = true;
                }
                catch (Exception exc)
                {
                    Log.Error($"Eccezione durante InsertNewSessione{Environment.NewLine}{exc}");
                }
            }
            return fatto;
        }

        /// <summary>
        /// Elenco Inventari Filtrati per id
        /// </summary>
        /// <returns></returns>
        public InventorySessionModel InventSessByID(int sessID)
        {
            InventorySessionModel dbResult = new InventorySessionModel();
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {

                dbResult = dbCtx
                            .DbInveSess
                            .Where(x => x.InveSessID == sessID)
                            .Include(m => m.AnagMagNav)
                            .AsNoTracking()
                            .OrderByDescending(x => x.DtStart)
                            .FirstOrDefault();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco Inventari tipo Azienda (TUTTI, chiusi e paerti) filtrati x data
        /// </summary>
        /// <param name="FromDate"></param>
        /// <param name="ToDate"></param>
        /// <returns></returns>
        public List<InventorySessionModel> InventSessHistList()
        {
            List<InventorySessionModel> dbResult = new List<InventorySessionModel>();
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                dbResult = dbCtx
                            .DbInveSess
                            .Where(x => x.Transferred)
                            .Include(m => m.AnagMagNav)
                            .AsNoTracking()
                            .OrderByDescending(x => x.DtStart)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Elenco Inventari CORRENTI (=aperti, senza data fine)
        /// </summary>
        /// <returns></returns>
        public List<InventorySessionModel> InventSessCurrList()
        {
            List<InventorySessionModel> dbResult = new List<InventorySessionModel>();
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                dbResult = dbCtx
                            .DbInveSess
                            .Where(x => (x.DtEnd == null) || !(x.Transferred))
                            .Include(m => m.AnagMagNav)
                            .AsNoTracking()
                            .OrderByDescending(x => x.DtStart)
                            .ToList();
            }
            return dbResult;
        }

        /// <summary>
        /// Chiusura/apertura sessione
        /// </summary>
        /// <param name="sessID"></param>
        /// <param name="flgclose"></param>
        /// <returns></returns>
        public async Task<bool> CloseOpenSessione(int sessID, bool flgClose)
        {
            bool answ = false;
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                var SessID = new SqlParameter("@InveSessID", sessID);
                var FlgClose = new SqlParameter("@FlgClose", flgClose);

                var callResulr = await dbCtx
                            .Database
                            .ExecuteSqlRawAsync("exec dbo.stp_INV_CloseOpen  @InveSessID, @FlgClose", SessID, FlgClose);

                answ = true;
            }
            return answ;
        }

        /// <summary>
        /// Trasferimento
        /// </summary>
        /// <param name="sessID"></param>
        /// <returns></returns>
        public async Task<bool> TransferSessione(int sessID)
        {
            bool answ = false;
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                var SessID = new SqlParameter("@InveSessID", sessID);

                var callResulr = await dbCtx
                            .Database
                            .ExecuteSqlRawAsync("exec dbo.stp_INV_InveSess_Transfer  @InveSessID", SessID);

                answ = true;
            }
            return answ;
        }

        #endregion gestione sessione

        #region gestione totale lotti

        /// <summary>
        /// Elenco Totale lotti x sessione
        /// </summary>
        /// <param name="sessID"></param>
        /// <returns></returns>
        public List<InveSessTotLotModel> InveSessTotLotList(int sessID)
        {
            List<InveSessTotLotModel> dbResult = new List<InveSessTotLotModel>();
            using (var dbCtx = new MoonPro_InveContext(_configuration))
            {
                var SessID = new SqlParameter("@sessId", sessID);

                dbResult = dbCtx
                            .DbTotLotti
                            .FromSqlRaw("exec dbo.stp_INVE_TotLotBySess @sessId", SessID)
                            .AsNoTracking()
                            .AsEnumerable()
                            .ToList();
            }
            return dbResult;
        }

        #endregion gestione totale lotti

        #endregion Public Methods

        #region Private Fields

        private static IConfiguration _configuration;

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

        #endregion Private Fields
    }
}