using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Kreta.DataAccessManual.Interfaces;
using Kreta.DataAccessManual.Util;
using Kreta.Enums;
using Kreta.Framework;
using Kreta.Framework.Util;
using SDA.DataProvider;
using SDA.Kreta.Entities;

namespace Kreta.DataAccessManual
{
    internal class NaptariHetDAL : DataAccessBase, INaptariHetDal
    {
        public NaptariHetDAL(DalHandler handler) : base(handler)
        {
        }

        public NaptariHetDAL(DalHandler handler, GridParameters parameters) : base(handler, parameters)
        {
        }

        /// INFO @DevKornel: Mobil használja
        public DataSet GetNaptariHetList(int tanevId, NaptariHetAndOrarendOverlapFilter naptariHetAndOrarendOverlapFilter = null)
        {
            var parameters = new List<CommandParameter>
            {
                new CommandParameter("pTanevId", tanevId)
            };

            var commandText = @"
                SELECT
                     ID AS ID
                    ,C_HETSORSZAMA AS HetSorszama
                    ,C_HETKEZDONAPJA AS HetKezdoNapja
                    ,C_HETUTOLSONAPJA AS HetUtolsoNapja
                    ,C_HETIREND AS Hetirend
                FROM
                    T_NAPTARIHET_OSSZES
        WHERE
          C_TANEVID = :pTanevId";

            if (naptariHetAndOrarendOverlapFilter != null)
            {
                commandText += " AND C_HETKEZDONAPJA <= :pVegNapDatuma AND :pKezdoNapDatuma <= C_HETUTOLSONAPJA";
                parameters.Add(new CommandParameter("pKezdoNapDatuma", naptariHetAndOrarendOverlapFilter.OrarendElemKezdoNapDatuma.Date));
                parameters.Add(new CommandParameter("pVegNapDatuma", naptariHetAndOrarendOverlapFilter.OrarendElemVegNapDatuma.Date));
            }

            DataSet ds = GetData(commandText, parameters, dictionaryItemColumns: "Hetirend");
            return ds;
        }

        public void SetHetirend(Dictionary<int, int?> modifiedHetirendList, int intezmenyId, int tanevId)
        {
            foreach (KeyValuePair<int, int?> modifiedHetirend in modifiedHetirendList)
            {
                int naptariHetId = modifiedHetirend.Key;
                int? hetirend = modifiedHetirend.Value;

                var naptariHet = NaptariHet.GiveAnInstance();
                naptariHet.LoadByID(naptariHetId);

                if (hetirend.HasValue)
                {
                    naptariHet.HetiRend = hetirend.Value;
                }
                else
                {
                    naptariHet.HetiRend = null;
                }

                naptariHet.Update();

                var napIdList = GetNaptariNapIdsIntervallumonBelul(naptariHet.HetKezdonapja, naptariHet.HetUtolsoNapja);
                foreach (var napId in napIdList)
                {
                    var naptariNap = NaptariNap.GiveAnInstance();
                    naptariNap.LoadByID(napId);

                    var dal = new TanevRendjeDAL(null);
                    var ds = dal.GetTanevRendjeByDatum(naptariNap.NapDatuma, naptariNap.TanevId, naptariNap.IntezmenyId);

                    foreach (DataRow item in ds.Tables[0].Rows)
                    {
                        var tanevRendje = TanevRendje.GiveAnInstance();
                        tanevRendje.LoadByID(item.Field<int>("TanevrendId"));

                        if (hetirend.HasValue)
                            tanevRendje.HetiRend = hetirend.Value;
                        else
                            tanevRendje.HetiRend = (int)HetiRendTipusEnum.MindegyikHet;

                        tanevRendje.Update();
                    }
                }
            }

            DalHelper.Commit();
        }

        public List<int> GetNaptariNapIdsIntervallumonBelul(DateTime kezd, DateTime veg)
        {
            var pList = new List<CommandParameter>(); ///TODO (@DevK.): Obj. init-el
            pList.Add(new CommandParameter("pKezd", kezd));
            pList.Add(new CommandParameter("pVeg", veg));

            const string command = @"
            select ID from T_NAPTARINAP nn
            where
                nn.TOROLT = 'F'
                and nn.C_NAPDATUMA between :pKezd and :pVeg";

            var ds = this.GetData(command, pList);
            return ds.Tables[0].AsEnumerable().Select(x => x.Field<int>("ID")).ToList();
        }

        public DataSet GetHetirendHelyettesitesList(int tanevId, Dictionary<int, int?> modifiedHetirendList)
        {
            var parameters = new List<CommandParameter>
            {
                new CommandParameter("pTanevId", tanevId),
            };

            var commandText = @"
                select
                  hi.C_HELYETTESITESNAPJA as Datum
                  ,fHelyettesito.C_NYOMTATASINEV as Helyettesito
                  ,fHelyettesitett.C_NYOMTATASINEV as fHelyettesitett
                  ,ocs.C_NEV as OsztalyCsoport
                  ,t.C_NEV as Tantargy
                from T_HELYETTESITESIIDOSZAK_OSSZES hi
                join T_ORARENDIORA_OSSZES oo on oo.ID = hi.C_HELYETTESITETTORARENDID
                join T_FELHASZNALO_OSSZES fHelyettesito on fHelyettesito.Id = hi.C_HELYETTESTANAROKID
                join T_FELHASZNALO_OSSZES fHelyettesitett on fHelyettesitett.ID = oo.C_TANARID
                join T_OSZTALYCSOPORT_OSSZES ocs on ocs.ID = oo.C_OSZTALYCSOPORTID
                join T_TANTARGY_OSSZES t on t.ID = oo.C_TANTARGYID
                where
                  oo.C_HETIREND != 1554 and hi.TOROLT = 'F' and hi.C_TANEVID = :pTanevId and hi.C_HETSORSZAMA in (
                    select nh.C_HETSORSZAMA from T_NAPTARIHET_OSSZES nh where nh.ID in (
            ";

            foreach (var item in modifiedHetirendList)
            {
                commandText += item.Key.ToString() + ",";
            }

            commandText = commandText.Substring(0, commandText.Length - 1);
            commandText += " ) )";

            DataSet ds = GetData(commandText, parameters);
            return ds;
        }

        public int GetNaptariHetSorszamByDate(DateTime datum, int tanevId)
        {
            const string commandText = @"
                SELECT C_HETSORSZAMA AS Sorszam 
				FROM T_NAPTARIHET_OSSZES nh
                INNER JOIN T_TANEV_OSSZES t ON t.ID = nh.C_TANEVID AND t.TOROLT = 'F'
                WHERE nh.C_HETKEZDONAPJA <= 
					CASE 
						WHEN @pDatum < t.C_KEZDONAP THEN t.C_KEZDONAP 
						WHEN @pDatum > t.C_UTOLSONAP THEN t.C_UTOLSONAP 
						ELSE @pDatum 
						END
					AND CASE 
					  WHEN @pDatum < t.C_KEZDONAP THEN t.C_KEZDONAP 
					  WHEN @pDatum > t.C_UTOLSONAP THEN t.C_UTOLSONAP
					  ELSE @pDatum 
					END <= nh.C_HETUTOLSONAPJA
					AND nh.C_TANEVID = :pTanevId
					AND nh.TOROLT = 'F'
            ";

            var paramList = new List<CommandParameter> {
                new CommandParameter("pDatum", datum.Date),
                new CommandParameter("pTanevId", tanevId)
            };

            var ds = GetData(commandText, parameters: paramList);
            return ds.Tables[0].Rows[0].Field<int>("Sorszam");
        }

        public int? GetNaptariHetHetirendByDate(DateTime datum, int tanevId)
        {
            var commandText = $@"
                SELECT C_HETIREND AS Hetirend
                FROM T_NAPTARIHET_OSSZES nh
                WHERE
                    nh.C_HETKEZDONAPJA <= @{nameof(datum)}
                    AND @{nameof(datum)} <= nh.C_HETUTOLSONAPJA
                    AND nh.C_TANEVID = @{nameof(tanevId)}
                    AND nh.TOROLT = 'F'";

            var commandParameterList = new List<CommandParameter>
            {
                new CommandParameter(nameof(datum), datum),
                new CommandParameter(nameof(tanevId), tanevId)
            };

            var ds = GetData(
                commandText: commandText,
                parameters: commandParameterList);

            return ds.Tables[0].Rows.Count == 0 ? null : ds.Tables[0].Rows[0].Field<int?>("Hetirend");
        }
        public List<int> GetSortedHetirendIdList(int tanevId)
        {
            var hetirendList = HetiRendTipus.LoadWithFilter(string.Format("AND T_HETIRENDTIPUS_OSSZES.ID <> {0} AND T_DICTIONARYITEMBASE_OSSZES.C_TANEVID = {1} AND T_DICTIONARYITEMBASE_OSSZES.TOROLT = 'F' AND T_DICTIONARYITEMBASE_OSSZES.C_VISIBLE = 'T' ", (int)HetiRendTipusEnum.MindegyikHet, tanevId));
            return hetirendList.OrderBy(x => x.Value).Select(x => x.ID).Distinct().ToList();
        }

        public void DeleteHetesek(int tanevId, List<int> szunetIds, int modifierUserId)
        {
            if (szunetIds != null && szunetIds.Any())
            {
                using (var command = new SDACommand())
                {
                    command.Connection = UserContext.Instance.SDAConnection;
                    command.Transaction = UserContext.Instance.SDATransaction;

                    var commandText = @"
                    UPDATE h
                    SET
                      h.TOROLT = 'T',
                      h.LASTCHANGED = GETDATE(),
              h.SERIAL = h.SERIAL + 1,
                      h.MODIFIER = :pUserId
                    FROM T_HETES_OSSZES h
                      INNER JOIN T_NAPTARIHET_OSSZES nh ON nh.C_HETSORSZAMA = h.C_HETSORSZAMA AND nh.C_TANEVID = h.C_TANEVID AND nh.TOROLT = 'F'
                    WHERE h.TOROLT = 'F'
                      AND h.C_TANEVID = :pTanevId
                    ";

                    commandText += $" AND nh.ID IN ({string.Join(",", szunetIds)})";

                    command.CommandText = commandText;
                    command.Parameters.Add("pTanevId", tanevId);
                    command.Parameters.Add("pUserId", modifierUserId);

                    command.ExecuteNonQuery();
                }
            }
        }

        public void DeleteHelyettesites(int tanevId, Dictionary<int, int?> modifiedHetirendList)
        {
            var commandText = @"
                update hi
                  set TOROLT = 'T'
                from T_HELYETTESITESIIDOSZAK_OSSZES hi
                join T_ORARENDIORA_OSSZES oo on oo.ID = hi.C_HELYETTESITETTORARENDID
                where
                  oo.C_HETIREND != 1554 and hi.TOROLT = 'F' and hi.C_TANEVID = :pTanevId and hi.C_HETSORSZAMA in (
                    select nh.C_HETSORSZAMA from T_NAPTARIHET_OSSZES nh where nh.ID in (
            ";

            foreach (var item in modifiedHetirendList)
            {
                commandText += item.Key.ToString() + ",";
            }

            commandText = commandText.Substring(0, commandText.Length - 1);
            commandText += " ) )";

            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandText = commandText;
                command.Parameters.Add("pTanevId", tanevId);

                command.ExecuteNonQuery();
            }
        }
    }

    public class NaptariHetAndOrarendOverlapFilter
    {
        public DateTime OrarendElemKezdoNapDatuma { get; set; }

        public DateTime OrarendElemVegNapDatuma { get; set; }
    }
}