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

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

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

        #region BaseCRUD

        public IEmail Get()
        {
            return Email.GiveAnInstance();
        }

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

        /// <summary>
        /// A void helyett az IEmail-return verzióst kell használni!
        /// </summary>
        [Obsolete]
        void IBaseDal<IEmail>.Insert(IEmail dto)
        {
            throw new System.NotImplementedException();
        }

        public IEmail Insert(IEmail dto)
        {
            var entity = dto as Email;
            entity.Insert();

            DalHelper.Commit();

            dto.ID = entity.ID;
            dto.Guid = entity.Guid;

            return dto;
        }

        /// <summary>
        /// IsHibasanMegadva flag false-ra állítódik!
        /// </summary>
        /// <param name="dto"></param>
        public void Update(IEmail dto)
        {
            var entity = dto as Email;
            entity.IsHibasanMegadva = false;
            entity.Update();

            DalHelper.Commit();
        }

        /// <summary>
        /// Csak az IsHibasanMegadva flag-et és a Guid-ot update-eli!
        /// </summary>
        /// <param name="tanevId"></param>
        /// <param name="guid"></param>
        public bool UpdateIsHibasanMegadvaFlag(int tanevId, Guid guid)
        {
            var commandText = @"
                SELECT ID
                FROM T_EMAIL_OSSZES                
                WHERE C_TANEVID = :pTanevId
                  AND C_GUID = :pGuid
            ";

            var commandParameterList = new List<CommandParameter>
            {
                new CommandParameter("pTanevId", tanevId),
                new CommandParameter("pGuid", guid)
            };
            DataSet selectResult = GetData(commandText, commandParameterList);

            int? emailId = null;
            var rows = selectResult.Tables[0].Rows;
            if (rows.Count > 0)
            {
                emailId = rows[0].Field<int>("ID");
            }
            if (!emailId.IsEntityId())
            {
                return false;
            }

            var entity = Email.GiveAnInstance();
            entity.LoadByID(emailId.Value);
            entity.IsHibasanMegadva = true;
            //NOTE: Azért állítunk be új Guid-ot, hogy többször ne tudja ugyanazzal az url-el az IsHibasanMegadva property-t true-ra állítani!
            entity.Guid = Guid.NewGuid();
            entity.Update();

            DalHelper.Commit();

            return true;
        }

        public void Delete(IEmail dto)
        {
            var entity = (Email)dto;
            entity.Delete();

            DalHelper.Commit();
        }

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

            DalHelper.Commit();
        }

        #endregion

        public void FullUpdate(IEmail dto)
        {
            var entity = dto as Email;
            entity.IsHibasanMegadva = false;
            entity.FullUpdate();

            DalHelper.Commit();
        }

        public IEmail GetSpecific(string condition, int tanevId)
        {
            var entity = Email.LoadWithFilter(condition + $" AND C_TANEVID = {tanevId}").FirstOrDefault();
            return entity;
        }

        public string GetEmailCimBygondviselo(int userId, int gondviseloId)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "uspGetEmailCimBygondviselo";

                command.Parameters.Add("pUserId", userId);
                command.Parameters.Add("pGondviseloId", gondviseloId);

                return command.ExecuteScalar().ToString();
            }
        }

        public void SetAlapertelmezett(int tanuloId, int userId, int tanevId)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;

                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "uspSetAlapertelmezettEmail";

                command.Parameters.Add("pUserId", SDADBType.Int).Value = userId;
                command.Parameters.Add("pTanuloId", SDADBType.Int).Value = tanuloId;
                command.Parameters.Add("pTanevId", SDADBType.Int).Value = tanevId;
                command.ExecuteNonQuery();
            }
        }

        /// INFO @DevKornel: Mobil használja
        public List<EmailResponseDao> GetFelhasznalokEmailCimei(EmailRequestDao request)
        {
            var parameters = new List<CommandParameter>();

            if (request.IsPublic.HasValue)
            {
                parameters.Add(new CommandParameter("pIsPublic", request.IsPublic.Value ? 'T' : 'F'));
            }

            var felhasznaloIdsString = SqlLogic.ParseListToParameter(request.FelhasznaloIds).ToString();
            var command = @"
                SELECT
                   e.ID AS ID
                  ,e.C_EMAILTIPUSA AS Tipus
                  ,e.C_EMAILCIM AS EmailCim
                  ,e.C_ALAPERTELMEZETT AS Alapertelmezett
                  ,e.C_FELHASZNALOID AS FelhasznaloId
                  ,e.C_GONDVISELOID AS GondviseloId
                FROM T_EMAIL e
                WHERE 1 = 1
                  " + (!string.IsNullOrWhiteSpace(felhasznaloIdsString) ? $@" AND e.C_FELHASZNALOID IN({felhasznaloIdsString}) " : "")
                    + (request.IsPublic.HasValue ? @" AND e.C_ISPUBLIC = :pIsPublic " : "");

            var dataSet = GetData(command, parameters);
            DataTable dataTable = dataSet.Tables[0];
            SetDNAME(dataTable, "Tipus");

            return dataTable.AsDataSet().ToDaoList<EmailResponseDao>();
        }

        public void AddFelhasznaloEmail(IFelhasznalo felhasznalo, IEmail email)
        {
            ((Felhasznalo)felhasznalo).Email.Add((Email)email);
        }

        /// <summary>
        /// A szűrőparaméterek alapján SP használatával lekérdezi az EmailDataSet-et!
        /// </summary>
        /// <param name="pco">Szűrő paraméterek</param>
        /// <returns></returns>
        public DataSet GetEmailDataSet(EmailSearchPco pco)
        {
            var parameters = SetParameters(pco);
            using (var sdaCommand = new SDACommand())
            {
                sdaCommand.Connection = UserContext.Instance.SDAConnection;
                sdaCommand.Transaction = UserContext.Instance.SDATransaction;
                sdaCommand.CommandType = CommandType.StoredProcedure;

                sdaCommand.CommandText = "uspGetEmail";

                foreach (var param in parameters)
                {
                    sdaCommand.Parameters.Add(param.Name, param.Value);
                }

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

                DataTable dataTable = dataSet.Tables[0];
                SetDNAME(dataTable, "EmailTipusa");
                SetBoolFields(dataTable, "IsAlapertelmezett,IsPublikus,IsHibasanMegadva");

                DataTable result = SortingAndPaging(dataTable, GridParameters);

                return result.AsDataSet();
            }
        }

        /// <summary>
        /// Keresési feltételek összeállítása az SP részére. A paraméternevek az SP-ben lévőkkel kell, hogy egyezzenek!
        /// </summary>
        private List<CommandParameter> SetParameters(EmailSearchPco pco)
        {
            var parameters = new List<CommandParameter>
            {
                new CommandParameter("pTanevId", pco.TanevId, SDADBType.Int)
            };

            if (pco.FelhasznaloId.HasValue)
            {
                parameters.Add(new CommandParameter("pFelhasznaloId", pco.FelhasznaloId.Value, SDADBType.Int));
            }

            if (pco.FelhasznaloTipus.HasValue)
            {
                parameters.Add(new CommandParameter("pFelhasznaloTipus", pco.FelhasznaloTipus.Value, SDADBType.Int));
            }

            if (pco.GondviseloId.HasValue)
            {
                parameters.Add(new CommandParameter("pGondviseloId", pco.GondviseloId.Value, SDADBType.Int));
            }

            if (pco.IsAlapertelmezett.HasValue)
            {
                parameters.Add(new CommandParameter("pIsAlapertelmezett", pco.IsAlapertelmezett.Value ? 'T' : 'F', SDADBType.Char));
            }

            if (pco.IsPublic.HasValue)
            {
                parameters.Add(new CommandParameter("pIsPublic", pco.IsPublic.Value ? 'T' : 'F', SDADBType.Char));
            }

            if (pco.EmailTipusa.HasValue)
            {
                parameters.Add(new CommandParameter("pEmailTipusa", pco.EmailTipusa.Value, SDADBType.Int));
            }

            if (pco.IsHibasanMegadva.HasValue)
            {
                parameters.Add(new CommandParameter("pIsHibasanMegadva", pco.IsHibasanMegadva.Value ? 'T' : 'F', SDADBType.Char));
            }

            return parameters;
        }
    }
}