using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Kreta.Core;
using Kreta.DataAccess.Interfaces;
using Kreta.DataAccessManual.Interfaces;
using Kreta.DataAccessManual.ParameterClasses;
using Kreta.DataAccessManual.Util;
using Kreta.Ellenorzo.Dao.VN.FeltoltottFajl;
using Kreta.Framework;
using Kreta.Framework.Util;
using SDA.DataProvider;
using SDA.Kreta.Entities;

namespace Kreta.DataAccessManual
{
    internal class DKT_FileDal : DataAccessBase, IDKT_FileDAL
    {
        public DKT_FileDal(DalHandler handler) : base(handler) { }
        public DKT_FileDal(DalHandler handler, GridParameters parameters) : base(handler, parameters) { }

        public IDKT_File Get()
        {
            return DKT_File.GiveAnInstance();
        }

        public IDKT_File Get(int id)
        {
            var entity = DKT_File.GiveAnInstance();
            entity.LoadByID(id);
            return entity;
        }

        public int Insert(IDKT_File dto)
        {
            var entity = dto as DKT_File;

            entity.Insert();
            dto.ID = entity.ID;
            DalHelper.Commit();

            return dto.ID;
        }

        public void Delete(int id, int userId)
        {
            DeleteAllDktFeladatFile(id, userId);

            var entity = Get(id) as DKT_File;

            entity.Torolt = true;

            entity.FullUpdate();
            DalHelper.Commit();
        }

        public void Delete(int id)
        {
            var entity = Get(id) as DKT_File;
            entity.Delete();
            DalHelper.Commit();
        }

        public void FullUpdate(IDKT_File dto)
        {
            var entity = dto as DKT_File;
            entity.FullUpdate(true);
            DalHelper.Commit();
        }

        public void Update(IDKT_File dto)
        {
            var entity = dto as DKT_File;
            entity.FullUpdate();
            DalHelper.Commit();
        }

        private void DeleteAllDktFeladatFile(int dktFileId, int userId)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "uspDeleteAllDktFeladatFile";

                command.Parameters.Add("pDKTFileId", dktFileId);
                command.Parameters.Add("pUserId", userId);
                command.Parameters.Add("pDate", DateTime.Now);

                command.ExecuteNonQuery();
                DalHelper.Commit();
            }
        }

        public void ConnectToHaziFeladat(int intezmenyId, int tanevId, int haziFeladatId, int csatolmanyId)
        {
            if (!IsExistsHaziFeladatCsatolmany(tanevId, haziFeladatId, csatolmanyId))
            {
                var entity = DKT_FeladatFile.GiveAnInstance();

                entity.IntezmenyId = intezmenyId;
                entity.TanevId = tanevId;
                entity.FeladatId = haziFeladatId;
                entity.FileId = csatolmanyId;

                entity.Insert();
                DalHelper.Commit();
            }
        }

        private bool IsExistsHaziFeladatCsatolmany(int tanevId, int haziFeladatId, int csatolmanyId)
        {
            using (var command = new SDACommand())
            {

                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "uspIsExistsHaziFeladatCsatolmany";

                command.Parameters.Add("pTanevId", tanevId);
                command.Parameters.Add("pHaziFeladatId", haziFeladatId);
                command.Parameters.Add("pCsatolmanyId", csatolmanyId);

                bool result = Convert.ToBoolean(command.ExecuteScalar());
                return result;
            }
        }

        public DataSet GetHaziFeladatCsatolmanyok(int tanevId, HFCsatolmanyokSearchPCO pco)
        {
            var dataSet = new DataSet();
            using (var sdaCommand = new SDACommand())
            {
                sdaCommand.Connection = UserContext.Instance.SDAConnection;
                sdaCommand.Transaction = UserContext.Instance.SDATransaction;
                sdaCommand.CommandType = CommandType.StoredProcedure;
                sdaCommand.CommandText = "uspGetHazifeladatCsatolmanyData";

                sdaCommand.Parameters.Add("pTanevId", SDADBType.Int).Value = tanevId;

                if (pco.FeladatKategoriaId.IsEntityId())
                {
                    sdaCommand.Parameters.Add("pFeladatKategoriaId", SDADBType.Int).Value = pco.FeladatKategoriaId.Value;
                }

                if (pco.FeladatEllatasiHelyId.IsEntityId())
                {
                    sdaCommand.Parameters.Add("pFeladatEllatasiHelyId", SDADBType.Int).Value = pco.FeladatEllatasiHelyId.Value;
                }

                if (pco.OraDatumaSrc.HasValue)
                {
                    sdaCommand.Parameters.Add("pOraDatuma", SDADBType.DateTime).Value = pco.OraDatumaSrc.Value;
                }

                if (pco.OsztalyCsoportSrc.IsEntityId())
                {
                    sdaCommand.Parameters.Add("pOsztalyCsoportId", SDADBType.Int).Value = pco.OsztalyCsoportSrc.Value;
                }

                if (pco.TanarSrc.IsEntityId())
                {
                    sdaCommand.Parameters.Add("pTanarId", SDADBType.Int).Value = pco.TanarSrc.Value;
                }

                if (pco.TantargySrc.IsEntityId())
                {
                    sdaCommand.Parameters.Add("pTantargyId", SDADBType.Int).Value = pco.TantargySrc.Value;
                }

                if (!string.IsNullOrWhiteSpace(pco.FajlNevSrc))
                {
                    sdaCommand.Parameters.Add("pFajlNeve", SDADBType.String).Value = pco.FajlNevSrc;
                }

                if (pco.FajlMeretTolSrc.IsEntityId())
                {
                    sdaCommand.Parameters.Add("pFajlMeretTol", SDADBType.Int).Value = pco.FajlMeretTolSrc.Value * 1024 * 1024;
                }

                if (pco.FajlMeretIgSrc.IsEntityId())
                {
                    sdaCommand.Parameters.Add("pFajlMeretIg", SDADBType.Int).Value = pco.FajlMeretIgSrc.Value * 1024 * 1024;
                }

                if (pco.FeltoltesDatumTolSrc.HasValue)
                {
                    sdaCommand.Parameters.Add("pFeltoltesDatumaTol", SDADBType.DateTime).Value = pco.FeltoltesDatumTolSrc.Value;
                }

                if (pco.FeltoltesDatumIgSrc.HasValue)
                {
                    sdaCommand.Parameters.Add("pFeltoltesDatumaIg", SDADBType.DateTime).Value = pco.FeltoltesDatumIgSrc.Value.Date.AddDays(1).AddSeconds(-1);
                }

                if (pco.HFHataridoTolSrc.HasValue)
                {
                    sdaCommand.Parameters.Add("pHataridoTol", SDADBType.DateTime).Value = pco.HFHataridoTolSrc.Value;
                }

                if (pco.HFHataridoIgSrc.HasValue)
                {
                    sdaCommand.Parameters.Add("pHataridoIg", SDADBType.DateTime).Value = pco.HFHataridoIgSrc.Value.Date.AddDays(1).AddSeconds(-1);
                }

                if (pco.IsOnlineOra.HasValue)
                {
                    sdaCommand.Parameters.Add("pIsOnlineOra", pco.IsOnlineOra.ToBool());
                }

                using (var sdaDataAdapter = new SDADataAdapter())
                {
                    sdaDataAdapter.SelectCommand = sdaCommand;
                    sdaDataAdapter.Fill(dataSet);
                }
            }

            SetBoolFields(dataSet.Tables[0], "Torolt,IsOnlineOra");
            return dataSet;
        }

        public double GetHaziFeladatCsatolmanyokOsszMeret(int tanevId, int tanarId)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "uspGetHaziFeladatCsatolmanyokOsszMeret";

                command.Parameters.Add("pTanevId", SDADBType.Int).Value = tanevId;
                command.Parameters.Add("pTanarId", SDADBType.Int).Value = tanarId;

                if (double.TryParse(command.ExecuteScalar()?.ToString(), out double result))
                {
                    return result;
                }

                return 0D;
            }
        }

        public DataSet GetCsatolmanyokForHaziFeladatDataSet(int tanevId, int intezmenyId, int haziFeladatId)
        {
            using (var sdaCommand = new SDACommand())
            {
                sdaCommand.Connection = UserContext.Instance.SDAConnection;
                sdaCommand.Transaction = UserContext.Instance.SDATransaction;
                sdaCommand.CommandType = CommandType.StoredProcedure;
                sdaCommand.CommandText = "uspGetCsatolmanyokForHaziFeladatDataSet";

                sdaCommand.Parameters.Add("pTanevId", SDADBType.Int).Value = tanevId;
                sdaCommand.Parameters.Add("pIntezmenyId", intezmenyId);
                sdaCommand.Parameters.Add("pHaziFeladatId", SDADBType.Int).Value = haziFeladatId;

                var dataSet = new DataSet();
                using (var sdaDataAdapter = new SDADataAdapter())
                {
                    sdaDataAdapter.SelectCommand = sdaCommand;
                    sdaDataAdapter.Fill(dataSet);
                }

                return dataSet;
            }
        }

        /// INFO: Mobil használja
        public DataSet FileKereses(int intezmenyId, int tanevId, FileKeresesRequestDao model, bool isKepLista)
        {
            StringBuilder command = new StringBuilder(@"
                SELECT  
                  orf.ID AS ID,
                    f.ID AS FileID,
                  f.C_FILENEV KepNeve,
                    orf.C_MEGJEGYZES Megjegyzes,
                  f.C_FELTOLTESDATUM AS Datum,
                  f.C_UTVONAL AS FajlUrl,
                    f.C_EXTENSION AS FajlKiterjesztes,
                    f.C_FILEGUID AS GUID,
                  orf.C_ORARENDIORAID AS OrarendiOraId,
                  orf.C_TANITASIORAID AS TanitasiOraId,
                  orf.C_ISTANULOLATHATO AS Megjelenitett,
                  ocs.C_NEV AS OsztalyCsoport,
                  t.C_NEV AS Tantargy,
                  ISNULL(fhelyettesitotanar.C_NYOMTATASINEV, ftanar.C_NYOMTATASINEV) AS TanarHelyettesito
                FROM T_DKT_FILE f
                INNER JOIN T_ORAFILE orf ON f.ID = orf.C_DKT_FILEID
                LEFT JOIN (
                    T_ORARENDIORA oo 
                    INNER JOIN T_ORATULAJDONSAGTIPUS oottOnlineOra ON oottOnlineOra.ID = 8615
                    LEFT JOIN T_ORARENDIORATULAJDONSAG ootOnlineOra ON ootOnlineOra.C_ORATULAJDONSAGID = oottOnlineOra.ID AND ootOnlineOra.C_ORARENDIORAID = oo.ID
                ) ON oo.ID = orf.C_ORARENDIORAID AND (f.C_FELTOLTESDATUM >= oo.C_ORAERVENYESSEGKEZDETE AND (f.C_FELTOLTESDATUM < oo.C_ORAERVENYESSEGVEGE 
                    OR (f.C_FELTOLTESDATUM = oo.C_ORAERVENYESSEGVEGE AND f.C_FELTOLTESDATUM = oo.C_ORAERVENYESSEGKEZDETE)))
                LEFT JOIN (
                    T_TANITASIORA tora 
                    INNER JOIN T_ORATULAJDONSAGTIPUS tottOnlineOra ON tottOnlineOra.ID = 8615
                    LEFT JOIN T_TANITASIORATULAJDONSAG totOnlineOra ON totOnlineOra.C_ORATULAJDONSAGID = tottOnlineOra.ID AND totOnlineOra.C_TANITASIORAID = tora.ID
                ) ON tora.ID = orf.C_TANITASIORAID
                LEFT JOIN T_OSZTALYCSOPORT ocs ON ocs.ID = oo.C_OSZTALYCSOPORTID OR ocs.ID = tora.C_OSZTALYCSOPORTID
                LEFT JOIN T_TANTARGY t ON t.ID = oo.C_TANTARGYID OR t.ID = tora.C_TANTARGYID
                LEFT JOIN T_HELYETTESITESIIDOSZAK hi ON (hi.C_HELYETTESITETTORARENDID = orf.C_ORARENDIORAID
                      OR hi.C_HELYETTESITETTORARENDID = tora.C_ORARENDIORAGROUPID)
                      AND CONVERT(VARCHAR(10), hi.C_HELYETTESITESNAPJA, 120) = CONVERT(VARCHAR(10), f.C_FELTOLTESDATUM, 120)
                LEFT JOIN T_FELHASZNALO ftanar ON ftanar.ID = tora.C_TANARID OR ftanar.ID = oo.C_TANARID
                LEFT JOIN T_FELHASZNALO  fhelyettesitotanar ON fhelyettesitotanar.ID = hi.C_HELYETTESTANAROKID
                WHERE 
                orf.C_TANEVID = :pTanevId
                AND orf.C_INTEZMENYID = :pIntezmenyId
                AND orf.C_ORAFILETIPUSID = :pOraFileTipusId");

            if (!string.IsNullOrWhiteSpace(model.FajlNeve))
            {
                command.AppendFormat(@" AND LOWER(f.C_FILENEV) like :pFajlNeve");
            }

            if (model.FeladatKategoriaId.IsEntityId())
            {
                command.AppendFormat(@" AND ocs.C_FELADATKATEGORIAID = :pFeladatKategoriaId");
            }

            if (model.FeladatEllatasiHelyId.IsEntityId())
            {
                command.AppendFormat(@" AND ocs.C_FELADATELLATASIHELYID = :pFeladatEllatasiHelyId");
            }

            if (model.OsztalyCsoportId.HasValue)
            {
                command.AppendFormat(@" AND (oo.C_OSZTALYCSOPORTID = :pOsztalyCsoportId OR tora.C_OSZTALYCSOPORTID = :pOsztalyCsoportId)");
            }

            if (model.TantargyId.HasValue)
            {
                command.AppendFormat(@" AND (oo.C_TANTARGYID = :pTantargyId OR tora.C_TANTARGYID = :pTantargyId)");
            }

            if (model.FoglalkozasId.HasValue)
            {
                command.AppendFormat(@" AND (oo.C_FOGLALKOZASID = :pFoglalkozasId OR tora.C_FOGLALKOZASID = :pFoglalkozasId)");
            }

            if (model.TanitasiOraId.HasValue)
            {
                command.AppendFormat(@" AND (orf.C_TANITASIORAID = :pTanitasiOraId)");

                if (model.OrarendiOraId.HasValue)
                {
                    command.AppendFormat(@" OR (orf.C_ORARENDIORAID = :pOrarendiOraId)");
                }
                else if (!model.OrarendiOraId.HasValue && !isKepLista)
                {
                    command.AppendFormat(@" AND (orf.C_ORARENDIORAID IS NULL)");
                }
            }
            else if (!model.TanitasiOraId.HasValue && !isKepLista)
            {
                command.AppendFormat(@" AND (orf.C_TANITASIORAID IS NULL)");
                if (model.OrarendiOraId.HasValue)
                {
                    command.AppendFormat(@" AND (orf.C_ORARENDIORAID = :pOrarendiOraId)");
                }
                else if (!model.OrarendiOraId.HasValue)
                {
                    command.AppendFormat(@" AND (orf.C_ORARENDIORAID IS NULL)");
                }
            }

            if (model.TanarHelyettesitoId.HasValue)
            {
                command.AppendFormat(@" AND (oo.C_TANARID = :pTanarHelyettesitoId OR tora.C_TANARID = :pTanarHelyettesitoId OR hi.C_HELYETTESTANAROKID = :pTanarHelyettesitoId)");
            }

            if (model.DatumTol.HasValue)
            {
                command.AppendFormat(@" AND (CONVERT(VARCHAR(10), f.C_FELTOLTESDATUM, 120) >= CONVERT(VARCHAR(10), :pDatumTol, 120))");
            }

            if (model.DatumIg.HasValue)
            {
                command.AppendFormat(@" AND (CONVERT(VARCHAR(10), f.C_FELTOLTESDATUM, 120) <= CONVERT(VARCHAR(10), :pDatumIg, 120))");
            }

            if (model.IsOnlineOra.HasValue)
            {
                command.AppendFormat(@" AND (tora.ID IS NOT NULL AND ISNULL(totOnlineOra.C_BOOLERTEK, tottOnlineOra.C_BOOLDEFAULT) = {0} OR oo.ID IS NOT NULL AND ISNULL(ootOnlineOra.C_BOOLERTEK, oottOnlineOra.C_BOOLDEFAULT) = {0})",
                    model.IsOnlineOra.ToBool() ? "'T'" : "'F'");
            }

            var parameters = AddCommandParametersByFeltoltottfajlokResponseModel(model, tanevId, intezmenyId);
            var result = GetData(command.ToString(), parameters);
            SetBoolFields(result.Tables[0], "Megjelenitett");

            return result;
        }
        /// INFO @MadachF: Mobil használja
        public int GetFeltoltottFajlokSzamaByOrarendiOraId(int intezmenyId, int tanevId, DateTime feltoltesDatuma, int orarendiOraId)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "uspGetFeltoltottFajlokSzamaByOrarendiOraId";

                command.Parameters.Add("pIntezmenyId", intezmenyId);
                command.Parameters.Add("pTanevId", tanevId);
                command.Parameters.Add("pOrarendiOraId", orarendiOraId);
                command.Parameters.Add("pFeloltesDatum", feltoltesDatuma);

                var result = command.ExecuteScalar();
                return Convert.ToInt32(result);
            }
        }

        /// INFO @MadachF: Mobil használja
        public int GetFeltoltottFajlokSzamaByTanitasiOraId(int intezmenyId, int tanevId, int tanitasiOraId)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "uspGetFeltoltottFajlokSzamaByTanitasiOraId";

                command.Parameters.Add("pIntezmenyId", intezmenyId);
                command.Parameters.Add("pTanevId", tanevId);
                command.Parameters.Add("pTanitasiOraId", tanitasiOraId);

                var result = command.ExecuteScalar();
                return Convert.ToInt32(result);
            }
        }

        private static List<CommandParameter> AddCommandParametersByFeltoltottfajlokResponseModel(FileKeresesRequestDao model, int tanevId, int intezmenyId)
        {
            List<CommandParameter> parameters = new List<CommandParameter>
            {
                new CommandParameter("pTanevId", tanevId),
                new CommandParameter("pIntezmenyId", intezmenyId),
                new CommandParameter("pOraFileTipusId", model.OraFileTipusId)
            };

            if (model.DatumTol.HasValue)
            {
                parameters.Add(new CommandParameter("pDatumTol", model.DatumTol));
            }

            if (model.DatumIg.HasValue)
            {
                parameters.Add(new CommandParameter("pDatumIg", model.DatumIg));
            }

            if (model.FeladatEllatasiHelyId.IsEntityId())
            {
                parameters.Add(new CommandParameter("pFeladatEllatasiHelyId", model.FeladatEllatasiHelyId));
            }

            if (model.FeladatKategoriaId.IsEntityId())
            {
                parameters.Add(new CommandParameter("pFeladatKategoriaId", model.FeladatKategoriaId));
            }

            if (!string.IsNullOrWhiteSpace(model.FajlNeve))
            {
                parameters.Add(new CommandParameter("pFajlNeve", $"%{model.FajlNeve.ToLower()}%"));
            }

            if (model.OsztalyCsoportId.HasValue)
            {
                parameters.Add(new CommandParameter("pOsztalyCsoportId", model.OsztalyCsoportId));
            }

            if (model.TanarHelyettesitoId.HasValue)
            {
                parameters.Add(new CommandParameter("pTanarHelyettesitoId", model.TanarHelyettesitoId));
            }

            if (model.TantargyId.HasValue)
            {
                parameters.Add(new CommandParameter("pTantargyId", model.TantargyId));
            }

            if (model.FoglalkozasId.HasValue)
            {
                parameters.Add(new CommandParameter("pFoglalkozasId", model.FoglalkozasId));
            }

            if (model.OrarendiOraId.HasValue)
            {
                parameters.Add(new CommandParameter("pOrarendiOraId", model.OrarendiOraId));
            }

            if (model.OrarendiOraGroupId.HasValue)
            {
                parameters.Add(new CommandParameter("pOrarendiOraGroupId", model.OrarendiOraGroupId));
            }

            if (model.TanitasiOraId.HasValue)
            {
                parameters.Add(new CommandParameter("pTanitasiOraId", model.TanitasiOraId));
            }

            return parameters;
        }

        /// INFO @Tojcsi: Mobil használja
        public bool GetJogosultsag(int tanuloId, int fileId, int tanevId)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "uspGetDKTFileJogosultsag";

                command.Parameters.Add("pTanuloId", tanuloId);
                command.Parameters.Add("pFileId", fileId);
                command.Parameters.Add("pTanevId", tanevId);

                var result = command.ExecuteScalar();
                return Convert.ToInt32(result) > 0;
            }
        }
    }
}