using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;
using TMSDAL.DTO.TrainingDomain;
using TMSDAL.Query.ExternalVendor;
using TMSDAL.Query.TrainingDomain;

namespace TMSDAL.CustomCode.TrainingDomain
{
    public class TrainingDomainDAL : ITrainingDomainDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public TrainingDomainDAL(IQueryExecutor queryExecutor, IValidation validation)
        {
            _QueryExecutor = queryExecutor;
            _Validation = validation;
        }

        public async Task<string> GetTrainingDomain(int trainingDomainId, LoginDTO loginDTO)
        {
            try
            {
                string sql = TrainingDomainQB.GET_DOMAIN;
                var parameters = new { TrainingDomainId = trainingDomainId, TenantId = loginDTO.ClientId };
                var result = await _QueryExecutor.QuerySingleAsync<TrainingDomainDTO>(loginDTO, sql, parameters);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveTrainingDomain(TrainingDomainDTO dto, LoginDTO loginDTO)
        {
            try
            {
                string sql = TrainingDomainQB.SAVE_DOMAIN;
                return await _QueryExecutor.ExecuteAsync(loginDTO, sql, dto);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task<int> UpdateTrainingDomain(TrainingDomainDTO dto, LoginDTO loginDTO)
        {
            try
            {
                string sql = TrainingDomainQB.UPDATE_DOMAIN;
                return await _QueryExecutor.ExecuteAsync(loginDTO, sql, dto);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.UpdateErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task<string> DeleteTrainingDomain(int trainingDomainId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { TrainingDomainId = trainingDomainId, TenantId = loginDTO.ClientId };

                var programmes = (await _QueryExecutor.QueryAsync<RefNameDTO>(loginDTO, TrainingDomainQB.CHECK_DOMAIN_PROGRAMMES, parameters)).ToList();
                var needs      = (await _QueryExecutor.QueryAsync<RefNameDTO>(loginDTO, TrainingDomainQB.CHECK_DOMAIN_NEEDS,      parameters)).ToList();

                if (programmes.Count > 0 || needs.Count > 0)
                {
                    var lines = new List<string>();
                    if (programmes.Count > 0)
                        lines.Add("Training Programmes: " + string.Join(", ", programmes.Select(r => r.RefName)));
                    if (needs.Count > 0)
                        lines.Add("Training Needs: " + string.Join(", ", needs.Select(r => r.RefName)));
                    throw new Exception("Training Domain cannot be deleted. It is currently used in — " + string.Join(" | ", lines) + ".");
                }

                int result = await _QueryExecutor.ExecuteAsync(loginDTO, TrainingDomainQB.DELETE_DOMAIN, parameters);
                return result > 0 ? SuccessResponse.DeleteSuccessMessage : ErrorResponse.DeleteNotFoundMessage;
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.DeleteErrorMessage);
                throw new Exception(error);
            }
        }

        private class RefNameDTO
        {
            public string RefName { get; set; } = string.Empty;
        }

        public async Task<string> GetDomainList(LoginDTO loginDTO)
        {
            try
            {
                string sql = TrainingDomainQB.GET_DOMAIN_LIST;
                var parameters = new { TenantId = loginDTO.ClientId };
                var result = await _QueryExecutor.QueryAsync<TrainingDomainDTO>(loginDTO, sql, parameters);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListTrainingDomain(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
        {
            var result = await _QueryExecutor.QueryAsync<TrainingDomainPicklistDTO>(
                login, TrainingDomainQB.GET_SELECTLIST_DOMAIN,
                new { TenantId = login.ClientId });
            return JsonConvert.SerializeObject(result);
        }

        public async Task<int> GetDefaultTrainingDomainId(LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<int>(
                    loginDTO, TrainingDomainQB.GET_DEFAULT_DOMAIN,
                    new { TenantId = loginDTO.ClientId }, cancellationToken: ct);
                return result.FirstOrDefault();
            }
            catch (Exception) { throw; }
        }
    }
}