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

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

        public MukodesiHelyDAL(DalHandler handler) : base(handler)
        {
        }

        public DataSet GetMukodesiHelyek(int tanevId)
        {
            var parameters = new List<CommandParameter>();
            parameters.Add(new CommandParameter("pTanevId", tanevId));

            var CommandText = @"
                select
                    ID,
                    C_NEV Nev,
                    C_IRANYITOSZAM Iranyitoszam,
                    C_TAGINTEZMENY Tagintezmeny,
                    C_VAROS Varos,
                    C_TELEFONSZAM Telefon,
                    C_EMAILCIM Email,
                    C_TAGINTEZMENYIKOD TagintezmenyiKod,
                    C_SZEKHELY Szekhely,
                    C_MUKODESIHELYAZONOSITO MukodesiHelyAzonosito,
                    C_MUKODESIHELYTIPUSA
                from T_MUKODESIHELY_OSSZES
                where
                    T_MUKODESIHELY_OSSZES.TOROLT='F' and T_MUKODESIHELY_OSSZES.C_TANEVID = :pTanevId";

            DataSet ds = GetData(CommandText, parameters, booleanColumns: "Tagintezmeny,Szekhely", dictionaryItemColumns: "C_MUKODESIHELYTIPUSA");
            return ds;
        }

        public DataSet GetMukodesiHelyekForDropDown(int tanevId)
        {
            var parameters = new List<CommandParameter>();
            parameters.Add(new CommandParameter("ptanevId", tanevId));

            StringBuilder commandTextBuilder = new StringBuilder(@"
SELECT
    ID
    ,C_NEV
FROM
    T_MUKODESIHELY_OSSZES
WHERE
    TOROLT = 'F' ");

            commandTextBuilder.Append(@" AND C_TANEVID = :ptanevId");

            using (SDACommand command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandText = commandTextBuilder.ToString();

                var ds = GetData(command.CommandText, parameters);
                return ds;
            }
        }

        public DataSet GetMukodesiHelyekForFelhelyValaszto(int pTanevId, int pOktatasiNevelesiFeladatId)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "uspGetMukodesiHelyekForFelhelyValaszto";

                command.Parameters.Add(nameof(pTanevId), pTanevId);
                command.Parameters.Add(nameof(pOktatasiNevelesiFeladatId), pOktatasiNevelesiFeladatId);

                var ds = new DataSet();

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

        public bool LetezikMukodesiHelyAzonosito(string mukodesiHelyAzonosito, int mukodesiHelyId, int tanevId)
        {
            using (SDACommand command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;

                command.Parameters.Add(nameof(mukodesiHelyAzonosito), mukodesiHelyAzonosito);
                command.Parameters.Add(nameof(mukodesiHelyId), mukodesiHelyId);
                command.Parameters.Add(nameof(tanevId), tanevId);

                command.CommandText = $@"
                IF EXISTS (
                    SELECT 1
                    FROM T_MUKODESIHELY_OSSZES
                    WHERE C_MUKODESIHELYAZONOSITO = @{nameof(mukodesiHelyAzonosito)} AND TOROLT = 'F'
                        AND C_TANEVID = @{nameof(tanevId)} AND ID <> @{nameof(mukodesiHelyId)}
                )
                    SELECT 1
                ELSE
                    SELECT 0
                ";

                return Convert.ToBoolean(command.ExecuteScalar());
            }
        }

        public void SetSzekhely(int mukodesiHelyID)
        {
            using (SDACommand command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandText = "sp_SetIntezmenySzekhely";
                command.Parameters.Add("MukodesiHelyId", mukodesiHelyID);
                command.CommandType = CommandType.StoredProcedure;
                command.ExecuteNonQuery();

                DalHelper.Commit();
            }
        }

        public void Insert(IMukodesiHely dto)
        {
            var entity = dto as MukodesiHely;

            entity.Insert();
            FollowUp(entity);

            DalHelper.Commit();
        }

        public void Update(IMukodesiHely dto)
        {
            var entity = dto as MukodesiHely;

            entity.Update();
            FollowUp(entity);

            DalHelper.Commit();
        }

        public void FullUpdate(IMukodesiHely dto)
        {
            var entity = dto as MukodesiHely;

            entity.FullUpdate();
            FollowUp(entity);

            DalHelper.Commit();
        }

        public void Delete(IMukodesiHely dto)
        {
            var entity = dto as MukodesiHely;

            var mukodesiHelyEntityConnections = EntityUtils.GetEntitiesConnections(new List<int> { dto.ID }, new List<string> { entity.GetEntityName() });
            if (mukodesiHelyEntityConnections.Count > 0 && mukodesiHelyEntityConnections[dto.ID].All(x => x.TargetTableName == "T_MEROHELY" || x.TargetTableName == "T_MEROHELY_OSSZES"))
            {
                foreach (var merohely in entity.Merohely)
                {
                    foreach (var meroallas in merohely.Meroallas)
                    {
                        foreach (var fajl in meroallas.FeltoltottFajl)
                        {
                            fajl.RemoveFromMeroallas(meroallas);
                            fajl.Delete();
                        }
                        meroallas.Delete();
                    }
                    foreach (var kozmuszamla in merohely.KozmuSzamla)
                    {
                        kozmuszamla.Delete();
                    }
                    merohely.Delete();
                }
            }

            entity.Delete();
            FollowUp(entity);

            DalHelper.Commit();
        }

        public void FollowUpMukodesiHely(int intezmenyId, int aktTanevId, int kovetkezoTanevId, int mukodesiHelyId)
        {
            using (SDACommand command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandText = "uspFollowUpMukodesiHely";
                command.Parameters.Add("intezmenyId", intezmenyId);
                command.Parameters.Add("aktTanevId", aktTanevId);
                command.Parameters.Add("kovetkezoTanevId", kovetkezoTanevId);
                command.Parameters.Add("mukodesiHelyId", mukodesiHelyId);
                command.CommandType = CommandType.StoredProcedure;
                command.ExecuteNonQuery();

                DalHelper.Commit();
            }
        }

        public IMukodesiHely Get()
        {
            return MukodesiHely.GiveAnInstance();
        }

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

        public bool MukodesiHelyAzonositoExists(IMukodesiHely entity)
        {
            return !string.IsNullOrWhiteSpace(entity.MukodesiHelyAzonosito) && LetezikMukodesiHelyAzonosito(entity.MukodesiHelyAzonosito, entity.ID, entity.TanevId);
        }

        public IMukodesiHely GetMukodesiHelyByNev(string nev, int tanevId)
        {
            var commandParameters = new Dictionary<string, object>()
                {
                    {nameof(nev), nev },
                    {nameof(tanevId), tanevId }
                };

            var entity = MukodesiHely.LoadWithFilter($" AND TOROLT = 'T' AND C_NEV = '@{nameof(nev)}' AND C_TANEVID = @{nameof(tanevId)}", commandParameters).FirstOrDefault();

            return entity;
        }
    }
}