using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Kreta.Core.Logic;
using Kreta.DataAccess.Interfaces;
using Kreta.DataAccessManual.Interfaces;
using Kreta.DataAccessManual.ParameterClasses;
using Kreta.DataAccessManual.Util;
using Kreta.Enums;
using Kreta.Enums.ManualEnums;
using Kreta.Framework;
using Kreta.Framework.Util;
using SDA.DataProvider;
using SDA.Kreta.Entities;
using static SDA.Kreta.Entities.DKT_Feladat;

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

        public IDKT_Feladat Get()
        {
            return GiveAnInstance();
        }

        public IDKT_Feladat Get(int id)
        {
            DKT_Feladat entity = GiveAnInstance();
            entity.LoadByID(id);
            return entity;
        }

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

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

        public DataSet GetAllSchemaHazifeladatNotification()
        {
            using (var sdaCommand = new SDACommand())
            {
                sdaCommand.Connection = UserContext.Instance.SDAConnection;
                sdaCommand.Transaction = UserContext.Instance.SDATransaction;
                sdaCommand.CommandType = CommandType.StoredProcedure;
                sdaCommand.CommandText = "uspGetAllSchemaKikuldendoHazifeladat";

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

                return result;
            }
        }

        public void SetHazifeladatAsKikuldott(List<int> idList)
        {
            using (SDACommand sdaCommand = new SDACommand())
            {
                sdaCommand.Connection = UserContext.Instance.SDAConnection;
                sdaCommand.Transaction = UserContext.Instance.SDATransaction;
                sdaCommand.CommandType = CommandType.StoredProcedure;
                sdaCommand.CommandText = "uspSetHazifeladatAsKikuldott";

                sdaCommand.Parameters.Add("pHazifeladatIdListString", SDADBType.String).Value = SqlLogic.ParseListToParameter(idList);

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

        public DataSet GetFeladatokByDateRange(int intezmenyId, int tanevId, DateTime start, DateTime end, int? feladatTipus)
        {
            using (var sdaCommand = new SDACommand())
            {
                sdaCommand.Connection = UserContext.Instance.SDAConnection;
                sdaCommand.Transaction = UserContext.Instance.SDATransaction;
                sdaCommand.CommandType = CommandType.StoredProcedure;
                sdaCommand.CommandText = "uspGetFeladatokByDateRange";

                sdaCommand.Parameters.Add("pIntezmenyId", SDADBType.Int).Value = intezmenyId;
                sdaCommand.Parameters.Add("pTanevId", SDADBType.Int).Value = tanevId;
                sdaCommand.Parameters.Add("pDatumTol", SDADBType.DateTime).Value = start;
                sdaCommand.Parameters.Add("pDatumIg", SDADBType.DateTime).Value = end;
                if (feladatTipus.HasValue)
                {
                    sdaCommand.Parameters.Add("pFeladatTipus", SDADBType.Int).Value = feladatTipus.Value;
                }

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

                return dataSet;
            }
        }

        public bool HasOrarendiOraKapcsolodoHazifeladatot(int tanevId, int orarendiOraGroupId, DateTime oraErvenyessegKezdete, DateTime oraErvenyessegVege, bool idoszakonKivul = true)
        {
            using (var sdaCommand = new SDACommand())
            {
                string commandText = @"
                    IF EXISTS (
                      SELECT 1
                      FROM T_DKT_FELADAT_OSSZES hf
                        INNER JOIN T_ORARENDIORA_OSSZES oo on oo.C_ORARENDIORAGROUPID = hf.C_ORARENDIORAGROUPID AND oo.TOROLT = 'F'
                      WHERE hf.C_ORARENDIORAGROUPID = :pOrarendiOraGroupId
                        AND hf.C_TANITASIORAID IS NULL
                        " + (idoszakonKivul ? "AND (hf.C_ROGZITESIDOPONT < :pOraErvenyessegKezdete OR hf.C_ROGZITESIDOPONT > :pOraErvenyessegVege) " :
                        "AND (hf.C_ROGZITESIDOPONT >= :pOraErvenyessegKezdete AND hf.C_ROGZITESIDOPONT < :pOraErvenyessegVege) ") + @"
                        AND hf.C_TANEVID = :pTanevId
                        AND hf.C_FELADATTIPUSID = 0
                        AND hf.TOROLT = 'F'
                    )
                      SELECT 1
                    ELSE
                      SELECT 0
                ";

                sdaCommand.Connection = UserContext.Instance.SDAConnection;
                sdaCommand.Transaction = UserContext.Instance.SDATransaction;
                sdaCommand.CommandType = CommandType.Text;
                sdaCommand.CommandText = commandText;

                sdaCommand.Parameters.Add("pTanevId", SDADBType.Int).Value = tanevId;
                sdaCommand.Parameters.Add("pOrarendiOraGroupId", SDADBType.Int).Value = orarendiOraGroupId;
                sdaCommand.Parameters.Add("pOraErvenyessegKezdete", SDADBType.DateTime).Value = oraErvenyessegKezdete.Date;
                sdaCommand.Parameters.Add("pOraErvenyessegVege", SDADBType.DateTime).Value = oraErvenyessegVege;

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

        //TODO: Kiemelni tároltba!!!
        /// INFO: Mobil használja
        public DataSet GetTanuloHaziFeladat(int intezmenyId, int? tanevId, int tanuloId, OktNevelesiKategoriaEnum? feladatKategoria, TanuloHaziFeladatSearchPCO pco)
        {
            var dataSet = new DataSet();
            using (var sdaCommand = new SDACommand())
            {
                string commandText = $@"SELECT
                       hf.ID as ID
                      ,tgy.ID AS TantargyId
                      ,tgy.C_NEV AS TantargyNev
                      ,tgy.c_TARGYKATEGORIA AS TargyKategoria
                      ,IIF(hf.C_TANITASIORAID IS NOT NULL, 'T', 'F') AS isTanitasiOra
                      ,ISNULL(hf.C_TANITASIORAID, hf.C_ORARENDIORAGROUPID) AS EventId
                      ,hf.C_TANITASIORAID AS TanitasiOraId
                      ,ISNULL(tn.C_NYOMTATASINEV, oo.C_NYOMTATASINEV) AS TanarNeve
                      ,tn.helyettesitoNev AS HelyettesitoNev
                      ,hf.C_SZOVEG AS HaziFeladatSzoveg
                      ,hf.C_ROGZITESIDOPONT AS HaziFeladatRogzitesDatuma
                      ,hf.CREATED AS HaziFeladatTenylegesRogzitesDatuma
                      ,hf.C_BEADASHATARIDO AS HaziFeladatHatarido
                      ,hf.ID AS HaziFeladatId
                      ,hf.C_ROGZITOALKALMAZOTTID AS HaziFeladatRogzitoId
                      ,'T' AS IsTanarRogzitette
                      ,hf.C_HFBEADANDOTIPUSID AS HfBeadandoTipusId
                      ,ocs.C_NEV AS OsztalyCsoport
                      ,ocs.ID AS OsztalyCsoportId
                      ,hf.C_ORASZAM AS Oraszam
                      ,ISNULL(thfs.C_ISMEGOLDVA,'F') as MegoldottHF
                      ,hf.C_DATUM as OraDatuma
                      ,hf.C_LATHATOSAGIDOPONT LathatosagIdopont
                      ,hf.C_HFCSATOLASENGEDELYEZESTIPUSI HaziFeladatCsatolasEngedelyezesTipusId
                    FROM T_DKT_FELADAT_OSSZES hf
                      INNER JOIN T_TANTARGY_OSSZES tgy ON tgy.ID = hf.C_TANTARGYID AND tgy.TOROLT = 'F'
                      INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = hf.C_OSZTALYCSOPORTID AND ocs.TOROLT = 'F'
                                                            AND (ocs.C_FELADATKATEGORIAID = @{nameof(feladatKategoria)} OR @{nameof(feladatKategoria)} IS NULL)
                      INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.C_OSZTALYCSOPORTID = hf.C_OSZTALYCSOPORTID AND tcs.C_TANULOID = @tanuloId AND tcs.TOROLT = 'F'
                      LEFT JOIN T_TANULOHAZIFELADATSTATUSZ_OSSZES thfs ON thfs.C_HAZIFELADATOKID = hf.ID AND thfs.C_TANULOID = @tanuloId
                      LEFT JOIN (
                        SELECT tn.ID, fh.C_NYOMTATASINEV, hTanar.C_NYOMTATASINEV AS helyettesitoNev
                        FROM T_TANITASIORA_OSSZES tn
                          INNER JOIN T_FELHASZNALO_OSSZES fh ON fh.ID = tn.C_TANARID AND fh.TOROLT = 'F'
                          LEFT JOIN T_FELHASZNALO_OSSZES hTanar ON hTanar.ID = tn.C_HELYETTESITOTANARID AND fh.TOROLT = 'F'
                        WHERE tn.TOROLT = 'F'
                      ) tn ON tn.ID = hf.C_TANITASIORAID
                      LEFT JOIN (
                        SELECT oo.ID, orr.C_DATUM, fh.C_NYOMTATASINEV, oo.C_TANTARGYID, oo.C_OSZTALYCSOPORTID, oo.C_TANARID, oo.C_ORASZAM, oo.C_ORAKEZDETE
                        FROM T_ORAREND_OSSZES orr
                          INNER JOIN T_ORARENDIORA_OSSZES oo ON orr.C_ORARENDIORAID = oo.ID
                          INNER JOIN T_FELHASZNALO_OSSZES fh ON fh.ID = oo.C_TANARID AND fh.TOROLT = 'F'
                        WHERE oo.TOROLT = 'F'
                      ) oo ON hf.C_DATUM = oo.C_DATUM 
                        AND oo.C_TANTARGYID = hf.C_TANTARGYID
                        AND oo.C_OSZTALYCSOPORTID = hf.C_OSZTALYCSOPORTID
                        AND oo.C_TANARID = hf.C_ALKALMAZOTTID
                        AND (oo.C_ORASZAM = hf.C_ORASZAM OR (hf.C_ORASZAM IS NULL AND hf.C_IDOPONT = oo.C_ORAKEZDETE))
                    WHERE hf.TOROLT = 'F'
                      AND hf.C_FELADATTIPUSID = 0
                      AND hf.C_INTEZMENYID = @intezmenyId
                      AND tcs.C_TANULOID = @tanuloId
                      AND hf.C_TANEVID = @tanevId
                      AND hf.C_BEADASHATARIDO >= tcs.C_BELEPESDATUM
                      AND (hf.C_BEADASHATARIDO < tcs.C_KILEPESDATUM OR tcs.C_KILEPESDATUM IS NULL)
                      AND tcs.C_TANEVID = @tanevId
                      AND (hf.C_TANITASIORAID IS NOT NULL 
                        OR (hf.C_TANITASIORAID IS NULL 
                          AND oo.C_TANTARGYID = hf.C_TANTARGYID 
                          AND oo.C_OSZTALYCSOPORTID = hf.C_OSZTALYCSOPORTID
                          AND (oo.C_ORASZAM = hf.C_ORASZAM
                            OR (hf.C_ORASZAM IS NULL
                              AND hf.C_IDOPONT = oo.C_ORAKEZDETE))))
                    {(pco.RegiHaziFeladatokElrejtese ? " AND hf.C_BEADASHATARIDO >= CAST(GETDATE() AS date)" : "")}
                    {(pco.TantargyId.HasValue ? $@" AND tgy.ID = @{nameof(pco.TantargyId)}" : "")}
                    {(pco.HaziFeladatKiiras.HasValue ? $@" AND hf.C_ROGZITESIDOPONT >= @{nameof(pco.HaziFeladatKiiras)}" : "")}
                    {(pco.HaziFeladatHataridoKezdoDatum.HasValue ? $@" AND @{nameof(pco.HaziFeladatHataridoKezdoDatum)} <= hf.C_BEADASHATARIDO" : "")}
                    {(pco.HaziFeladatHataridoVegeDatum.HasValue ? $@" AND hf.C_BEADASHATARIDO <= @{nameof(pco.HaziFeladatHataridoVegeDatum)}" : "")}
                    {(!string.IsNullOrWhiteSpace(pco.TanarNev) ? $@" AND ISNULL(tn.C_NYOMTATASINEV, oo.C_NYOMTATASINEV) LIKE '%' +  @{nameof(pco.TanarNev)} + '%'" : "")}
                ";

                sdaCommand.Connection = UserContext.Instance.SDAConnection;
                sdaCommand.Transaction = UserContext.Instance.SDATransaction;
                sdaCommand.CommandType = CommandType.Text;
                sdaCommand.CommandText = commandText;

                sdaCommand.Parameters.Add(nameof(intezmenyId), SDADBType.Int).Value = intezmenyId;

                if (tanevId.HasValue)
                {
                    sdaCommand.Parameters.Add(nameof(tanevId), SDADBType.Int).Value = tanevId;
                }
                else
                {
                    sdaCommand.Parameters.Add(nameof(tanevId), SDADBType.Int).Value = DBNull.Value;
                }

                sdaCommand.Parameters.Add(nameof(tanuloId), SDADBType.Int).Value = tanuloId;

                if (feladatKategoria.HasValue)
                {
                    sdaCommand.Parameters.Add(nameof(feladatKategoria), SDADBType.Int).Value = (int)feladatKategoria;
                }
                else
                {
                    sdaCommand.Parameters.Add(nameof(feladatKategoria), SDADBType.Int).Value = DBNull.Value;
                }

                if (!string.IsNullOrWhiteSpace(pco.TanarNev))
                {
                    sdaCommand.Parameters.Add(nameof(pco.TanarNev), SDADBType.String).Value = pco.TanarNev.ToLowerInvariant();
                }

                if (pco.TantargyId.HasValue)
                {
                    sdaCommand.Parameters.Add(nameof(pco.TantargyId), SDADBType.Int).Value = pco.TantargyId;
                }

                if (pco.HaziFeladatKiiras.HasValue)
                {
                    sdaCommand.Parameters.Add(nameof(pco.HaziFeladatKiiras), SDADBType.DateTime).Value = pco.HaziFeladatKiiras;
                }

                if (pco.HaziFeladatHataridoKezdoDatum.HasValue)
                {
                    sdaCommand.Parameters.Add(nameof(pco.HaziFeladatHataridoKezdoDatum), SDADBType.DateTime).Value = pco.HaziFeladatHataridoKezdoDatum;
                }

                if (pco.HaziFeladatHataridoVegeDatum.HasValue)
                {
                    sdaCommand.Parameters.Add(nameof(pco.HaziFeladatHataridoVegeDatum), SDADBType.DateTime).Value = pco.HaziFeladatHataridoVegeDatum;
                }

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

            SetBoolFields(dataSet.Tables[0], "isTanitasiOra,MegoldottHF");
            DataTable dataTable = SortingAndPaging(dataSet.Tables[0], GridParameters);

            DataSet result = dataTable.AsDataSet();
            return result;
        }

        public DataSet GetHaziFeladatForTanitasiOra(int tanitasiOraId, OktNevelesiKategoriaEnum? kategoria, int feladatTipusId = (int)FeladatTipusEnum.HaziFeladat)
        {
            return GetHaziFeladatForOra(kategoria, tanitasiOraId: tanitasiOraId, feladatTipusId: feladatTipusId);
        }

        public DataSet GetHaziFeladatForOrarendiOra(int orarendiOraId, DateTime? date, int? oraszam, OktNevelesiKategoriaEnum? kategoria, int feladatTipusId = (int)FeladatTipusEnum.HaziFeladat)
        {
            return GetHaziFeladatForOra(kategoria, orarendiOraId, date, oraszam, feladatTipusId: feladatTipusId);
        }

        //TODO: Kiemelni tároltba és kiírtani a GetData-t!!!
        private DataSet GetHaziFeladatForOra(OktNevelesiKategoriaEnum? kategoria, int? orarendiOraId = null, DateTime? date = null, int? oraszam = null, int? tanitasiOraId = null, int feladatTipusId = (int)FeladatTipusEnum.HaziFeladat)
        {
            var commandParameterList = new List<CommandParameter>
            {
                orarendiOraId.HasValue ?
                    new CommandParameter(nameof(orarendiOraId), (int)orarendiOraId) :
                    new CommandParameter(nameof(orarendiOraId), DBNull.Value),
                date.HasValue ?
                    new CommandParameter(nameof(date), ((DateTime)date).Date) :
                    new CommandParameter(nameof(date), DBNull.Value),
                oraszam.HasValue ?
                    new CommandParameter(nameof(oraszam), (int)oraszam) :
                    new CommandParameter(nameof(oraszam), DBNull.Value),
                tanitasiOraId.HasValue ?
                    new CommandParameter(nameof(tanitasiOraId), (int)tanitasiOraId) :
                    new CommandParameter(nameof(tanitasiOraId), DBNull.Value),
                kategoria.HasValue ?
                    new CommandParameter(nameof(kategoria), (int)kategoria) :
                    new CommandParameter(nameof(kategoria), DBNull.Value)
            };

            var commandTextStringBuilder = new StringBuilder($@"
                SELECT
                  hf.ID
                  ,hf.C_ROGZITESIDOPONT AS FeladasDatuma
                  ,hf.C_BEADASHATARIDO AS Hatarido
                  ,'T' AS IsTanarRogzitette
                  ,hf.C_ORASZAM AS Oraszam
                  ,ocs.C_NEV AS OsztCsop
                  ,ocs.ID AS OsztalyCsoportId
                  ,fh.C_NYOMTATASINEV AS Rogzito
                  ,hf.C_ROGZITOALKALMAZOTTID AS RogzitoId
                  ,hf.C_SZOVEG AS Szoveg
                  ,hf.C_TANITASIORAID AS TanoraId
                  ,tt.C_NEV AS Tantargy
                  ,fhh.C_NYOMTATASINEV AS HelyettesitoTanarNev
                  ,hf.CREATED AS HaziFeladatTenylegesRogzitesDatuma
                  ,hf.C_LATHATOSAGIDOPONT LathatosagIdopont
                    ,hf.C_FELADATTIPUSID FeladatTipusId
                    ,hf.C_ISLATHATO IsLathato");

            if (orarendiOraId.HasValue)
            {
                commandTextStringBuilder.Append($@"
                    FROM T_ORARENDIORA oo
                    JOIN T_DKT_FELADAT hf on oo.C_TANTARGYID = hf.C_TANTARGYID and oo.C_OSZTALYCSOPORTID = hf.C_OSZTALYCSOPORTID
                        AND oo.C_TANARID = hf.C_ALKALMAZOTTID AND(hf.C_ORASZAM = oo.C_ORASZAM OR CONVERT(datetime, CONVERT(varchar(8), hf.C_IDOPONT, 8)) = oo.C_ORAKEZDETE)
                        AND hf.C_DATUM = @{ nameof(date.Value.Date)}
                    INNER JOIN T_FELHASZNALO fh ON fh.ID = hf.C_ROGZITOALKALMAZOTTID AND fh.TOROLT = 'F'
                    INNER JOIN T_TANTARGY tt ON tt.ID = hf.C_TANTARGYID AND tt.TOROLT = 'F'
                    INNER JOIN T_OSZTALYCSOPORT ocs ON ocs.ID = hf.C_OSZTALYCSOPORTID AND ocs.TOROLT = 'F'

                    LEFT JOIN T_FELHASZNALO fhh ON fhh.ID = hf.C_HELYETTESITOALKALMAZOTTID AND fhh.TOROLT = 'F'
                where
                    hf.TOROLT = 'F' AND oo.ID = @{nameof(orarendiOraId)} 
                    AND hf.C_FELADATTIPUSID = {feladatTipusId}
                ");

                if (date.HasValue)
                {
                    commandTextStringBuilder.Append($@"AND hf.C_DATUM = @{nameof(date.Value.Date)} ");
                }

                if (oraszam.HasValue)
                {
                    commandTextStringBuilder.Append($@"AND hf.C_ORASZAM = @{nameof(oraszam)} ");
                } /*nem lesz valami használható hiszen ez módosítható és nem fogja követni a hf napirendnél pedig használhatatlan*/
            }
            else if (tanitasiOraId.HasValue)
            {
                commandTextStringBuilder.Append($@"
                    FROM T_TANITASIORA t
                    JOIN T_DKT_FELADAT hf on t.C_TANTARGYID = hf.C_TANTARGYID and t.C_OSZTALYCSOPORTID = hf.C_OSZTALYCSOPORTID
                        AND (t.C_TANARID = hf.C_ALKALMAZOTTID OR t.C_HELYETTESITOTANARID = hf.C_ALKALMAZOTTID)
                        AND hf.C_DATUM = t.C_DATUM
                        AND (hf.C_ORASZAM = t.C_ORASZAM OR CONVERT(datetime, CONVERT(varchar(8), hf.C_IDOPONT, 8)) = CONVERT(datetime, CONVERT(varchar(8), t.C_ORAKEZDETE, 8)))
                    INNER JOIN T_FELHASZNALO fh ON fh.ID = hf.C_ROGZITOALKALMAZOTTID AND fh.TOROLT = 'F'
                    INNER JOIN T_TANTARGY tt ON tt.ID = hf.C_TANTARGYID AND tt.TOROLT = 'F'
                    INNER JOIN T_OSZTALYCSOPORT ocs ON ocs.ID = hf.C_OSZTALYCSOPORTID AND ocs.TOROLT = 'F'

                    LEFT JOIN T_FELHASZNALO fhh ON fhh.ID = hf.C_HELYETTESITOALKALMAZOTTID AND fhh.TOROLT = 'F'
                where
                    hf.TOROLT = 'F' AND t.ID = @{nameof(tanitasiOraId)} 
                    AND hf.C_FELADATTIPUSID = {feladatTipusId}
                ");
            }

            if (kategoria.HasValue)
            {
                commandTextStringBuilder.Append($@" AND ocs.C_FELADATKATEGORIAID = @{nameof(kategoria)} ");
            }

            string commandText = commandTextStringBuilder.ToString();

            DataSet result = GetData(commandText, commandParameterList, booleanColumns: "IsTanarRogzitette,IsLathato");
            return result;
        }
    }
}