using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Kreta.Core;
using Kreta.DataAccess.Interfaces;
using Kreta.DataAccessManual.Interfaces;
using Kreta.DataAccessManual.Util;
using Kreta.Framework;
using Kreta.Framework.Security.PasswordCrypting;
using Kreta.Framework.Util;
using SDA.DataProvider;
using SDA.Kreta.Entities;

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

        public FelhasznaloBelepesDal(DalHandler handler, GridParameters gridParameters) : base(handler, gridParameters)
        {
        }

        public void GrantLogin(int felhasznaloId, string bejelentkezesiNev, string plainPassword, int tanevId, int? gondviseloId = null, bool storedplain = false, int? intezmenyId = null, bool kotelezoValtoztatni = false)
        {
            var salt = string.Empty;
            var password = plainPassword;

            if (!storedplain)
            {
                salt = SaltGenerator.GenerateSalt();
                password = BasePasswordCrypter.EncodePasswordSHA1(plainPassword, salt);
            }

            var belepes = FelhasznaloBelepes.GiveAnInstance();
            belepes.BejelentkezesiNev = bejelentkezesiNev;
            belepes.FelhasznaloId = felhasznaloId;
            belepes.Jelszo = password;
            belepes.So = salt;
            belepes.KotelezoValtoztatni = kotelezoValtoztatni;
            belepes.TanevId = tanevId;

            if (intezmenyId.HasValue)
            {
                belepes.IntezmenyId = intezmenyId.Value;
            }

            if (gondviseloId.HasValue)
            {
                belepes.GondviseloId = gondviseloId.Value;
            }

            belepes.Insert();

            DalHelper.Commit();
        }

        public void UpdateLastLogin(int felhasznaloBelepesId, DateTime lastLogin)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandText = $"UPDATE T_FELHASZNALOBELEPES SET C_UTOLSOBELEPES = @{nameof(lastLogin)} WHERE ID = @{nameof(felhasznaloBelepesId)}";

                command.Parameters.Add(nameof(felhasznaloBelepesId), SDADBType.Int).Value = felhasznaloBelepesId;
                command.Parameters.Add(nameof(lastLogin), SDADBType.DateTime).Value = lastLogin;

                command.ExecuteNonQuery();
            }

            DalHelper.Commit();
        }

        public void UpdateLastLoginByUserId(int userId, DateTime lastLogin, bool isGondviselo)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;

                command.CommandText = $@"UPDATE 
                                            T_FELHASZNALOBELEPES
                                        SET 
                                             C_UTOLSOBELEPESMOBIL = @lastLogin
                                            ,SERIAL = SERIAL + 1
                                            ,LASTCHANGED = GETDATE()
                                            ,MODIFIER = @userId
                                        WHERE
                                            {(isGondviselo ? "C_GONDVISELOID" : "C_GONDVISELOID IS NULL AND C_FELHASZNALOID")} = @userId";

                command.Parameters.Add(nameof(userId), userId);
                command.Parameters.Add(nameof(lastLogin), lastLogin);

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

        public void UpdateMeghiusultBelepesek(int felhasznaloBelepesId, int meghiusultBelepesek)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandText = $"UPDATE T_FELHASZNALOBELEPES SET C_MEGHIUSULTBELEPESEK = @{nameof(meghiusultBelepesek)} WHERE ID = @{nameof(felhasznaloBelepesId)}";

                command.Parameters.Add(nameof(felhasznaloBelepesId), SDADBType.Int).Value = felhasznaloBelepesId;
                command.Parameters.Add(nameof(meghiusultBelepesek), SDADBType.Int).Value = meghiusultBelepesek;

                command.ExecuteNonQuery();
            }

            DalHelper.Commit();
        }

        public void UpdateFelhasznalonev(int intezmenyId, int tanevId, int modifierId, int tanuloId, int? gondviseloId, string felhasznalonev)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;

                command.Parameters.Add("pIntezmenyId", SDADBType.Int).Value = intezmenyId;
                command.Parameters.Add("pTanevId", SDADBType.Int).Value = tanevId;
                command.Parameters.Add("pModifierId", SDADBType.Int).Value = modifierId;
                command.Parameters.Add("pTanuloId", SDADBType.Int).Value = tanuloId;
                command.Parameters.Add("pFelhasznalonev", SDADBType.String).Value = felhasznalonev;
                var isGondviselo = gondviseloId.HasValue;
                if (isGondviselo)
                {
                    command.Parameters.Add("pGondviseloId", SDADBType.Int).Value = gondviseloId.Value;
                }

                var commandText = @"
                    UPDATE
                        T_FELHASZNALOBELEPES_OSSZES
                    SET
                         C_BEJELENTKEZESINEV = @pFelhasznalonev
                        ,SERIAL += 1
                        ,LASTCHANGED = GETDATE()
                        ,MODIFIER = @pModifierId
                    WHERE
                            C_INTEZMENYID = @pIntezmenyId
                        AND C_TANEVID = @pTanevId
                        AND TOROLT = 'F' ";
                if (isGondviselo)
                {
                    commandText += @"
                        AND C_FELHASZNALOID = @pTanuloId
                        AND C_GONDVISELOID = @pGondviseloId";
                }
                else
                {
                    commandText += @"
                        AND C_FELHASZNALOID = @pTanuloId
                        AND C_GONDVISELOID IS NULL";
                }

                command.CommandText = commandText;
                command.ExecuteNonQuery();
            }

            DalHelper.Commit();
        }

        public void Delete(int id)
        {
            var entity = FelhasznaloBelepes.GiveAnInstance();
            entity.LoadByID(id);
            entity.Delete();

            DalHelper.Commit();
        }

        public void FullUpdate(IFelhasznaloBelepes dto)
        {
            var entity = dto as FelhasznaloBelepes;
            entity.FullUpdate();

            DalHelper.Commit();
        }

        public void Update(IFelhasznaloBelepes dto)
        {
            var entity = dto as FelhasznaloBelepes;
            entity.Update();

            DalHelper.Commit();
        }

        public IFelhasznaloBelepes Get()
        {
            return FelhasznaloBelepes.GiveAnInstance();
        }

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

        public void Insert(IFelhasznaloBelepes dto)
        {
            var entity = dto as FelhasznaloBelepes;
            entity.Insert();
            dto.ID = entity.ID;

            DalHelper.Commit();
        }

        public DataSet Get(string felhasznaloNev)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;

                command.Parameters.Add(nameof(felhasznaloNev), felhasznaloNev);

                command.CommandText = $@"
SELECT f.ID
      ,f.C_KOTELEZOVALTOZTATNI
      ,f.C_MEGHIUSULTBELEPESEK
      ,f.C_UTOLSOBELEPES
      ,f.C_JELSZOVALTOZTATASIDEJE
      ,f.C_ELFOGADOTTGDPRNYILATKOZAT
      ,f.C_GDPRELFOGADASDATUMA
      ,C_FELHASZNALOID
      ,f.C_GONDVISELOID
FROM T_FELHASZNALOBELEPES f
INNER JOIN T_TANEV t ON t.ID = f.C_TANEVID AND t.C_AKTIV = 'T' AND t.TOROLT = 'F'
WHERE f.C_BEJELENTKEZESINEV = @{nameof(felhasznaloNev)}
  AND f.TOROLT = 'F'
";

                var ds = new DataSet();
                using (var adapter = new SDADataAdapter())
                {
                    adapter.SelectCommand = command;
                    adapter.Fill(ds);
                }

                return ds;
            }
        }

        public DataSet GetFehasznaloBelepesDataSet(int tanevId)
        {
            using (var sdaCommand = new SDACommand())
            {
                sdaCommand.Connection = UserContext.Instance.SDAConnection;
                sdaCommand.Transaction = UserContext.Instance.SDATransaction;
                sdaCommand.CommandType = CommandType.StoredProcedure;
                sdaCommand.CommandText = "uspGetFelhasznaloBelepesData";

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

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

                SetDNAME(result.Tables[0], "GondviseloRokonsagiFokId");
                return result;
            }
        }

        public List<IFelhasznaloBelepes> GetFelhasznaloBelepesei(string generaltBejelentkezesiNev, int tanevId)
        {
            var commandParameters = new Dictionary<string, object>()
                {
                    {nameof(generaltBejelentkezesiNev), generaltBejelentkezesiNev },
                    { nameof(tanevId), tanevId }
                };

            return FelhasznaloBelepes.LoadWithFilter($" AND UPPER(C_BEJELENTKEZESINEV) = UPPER(@{nameof(generaltBejelentkezesiNev)}) AND C_TANEVID = @{nameof(tanevId)} AND TOROLT ='F' ", commandParameters).ToList<IFelhasznaloBelepes>();
        }

        public bool IsUserNameExistsInTanev(string userName, int tanevId, int? felhasznaloId = null, bool isgondviselo = false)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.Parameters.Add("pUserName", userName);
                command.Parameters.Add("pTanevId", tanevId);

                command.CommandText = @"
                    SELECT
                        ID
                    FROM T_FELHASZNALOBELEPES_OSSZES
                    WHERE
                        UPPER(C_BEJELENTKEZESINEV) = UPPER(:pUserName) AND C_TANEVID = :pTanevId AND TOROLT = 'F'";

                if (felhasznaloId.IsEntityId())
                {
                    command.Parameters.Add("pUserId", felhasznaloId.Value);

                    if (isgondviselo)
                    {
                        command.CommandText += @" AND C_GONDVISELOID <> :pUserId";
                    }
                    else
                    {
                        command.CommandText += @" AND C_FELHASZNALOID <> :pUserId";
                    }
                }

                var result = command.ExecuteScalar();

                return result != null && result != DBNull.Value;
            }
        }

        public int? IsUserNameExistsInIntezmeny(string userName, int intezmenyId, int? felhasznaloId = null, bool isgondviselo = false)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.Parameters.Add("pUserName", userName);
                command.Parameters.Add("pIntezmenyId", intezmenyId);

                command.CommandText = @"
                    SELECT
                        ID
                    FROM T_FELHASZNALOBELEPES_OSSZES
                    WHERE
                        UPPER(C_BEJELENTKEZESINEV) = UPPER(:pUserName) AND C_INTEZMENYID = :pIntezmenyId AND TOROLT = 'F'";

                if (felhasznaloId.IsEntityId())
                {
                    command.Parameters.Add("pUserId", felhasznaloId.Value);

                    if (isgondviselo)
                    {
                        command.CommandText += @" AND C_GONDVISELOID <> :pUserId";
                    }
                    else
                    {
                        command.CommandText += @" AND C_FELHASZNALOID <> :pUserId";
                    }
                }

                var result = command.ExecuteScalar();

                return result != null && result != DBNull.Value ? (int)result : (int?)null;
            }
        }
    }
}