using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;
using RecruitmentDAL.DTO.JobProfileLibrary;
using RecruitmentDAL.Query.JobProfileLibrary;
using System;
using System.Data.Common;
using System.Threading;
using System.Threading.Tasks;

namespace RecruitmentDAL.CustomeCode.JobProfileLibrary
{
    public class JobProfileLibraryDAL : IJobProfileLibraryDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        #region Get

        public async Task<string> GetJobProfileLibrary(int JobProfileLibraryId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = JobProfileLibraryQB.GET_JOBPROFILELIBRARY;

                var parameters = new
                {
                    JobProfileLibraryId = JobProfileLibraryId,
                    TenantId = LoginDTO.ClientId
                };

                JobProfileLibraryDTO result = await _QueryExecutor
                    .QuerySingleAsync<JobProfileLibraryDTO>(LoginDTO, sql, parameters, cancellationToken: ct)
                    .ConfigureAwait(false);

                return JsonConvert.SerializeObject(result);
            }
            catch
            {
                throw;
            }
        }

        #endregion


        #region Select List

        public async Task<string> GetSelectListJobProfileLibrary(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = JobProfileLibraryQB.GET_SELECTLIST_JOBPROFILELIBRARY;

                var parameters = new
                {
                    SearchKeyword = CriteriaDTO?.SearchKeyword ?? string.Empty,
                    TenantId = LoginDTO.ClientId
                };

                var result = await _QueryExecutor
                    .QueryAsync<JobProfileLibraryDTO>(LoginDTO, sql, parameters, cancellationToken: ct)
                    .ConfigureAwait(false);

                return JsonConvert.SerializeObject(result);
            }
            catch
            {
                throw;
            }
        }

        #endregion


        #region Save

        // JOBPROFILELIBRARYID is pre-allocated via AutoNumber before this call (see
        // JobProfileLibraryBLL.SaveJobProfileLibrary) — this is a plain rows-affected INSERT,
        // not an identity-returning one.
        public async Task<int> SaveJobProfileLibrary(
            JobProfileLibraryDTO JobProfileLibraryDTO, LoginDTO LoginDTO, DbTransaction transaction, CancellationToken ct)
        {
            try
            {
                const string sql = JobProfileLibraryQB.SAVE_JOBPROFILELIBRARY;

                return await _QueryExecutor
                    .ExecuteAsync(LoginDTO, sql, JobProfileLibraryDTO, transaction, ct)
                    .ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _Validation
                    .HandleException(ex, ErrorResponse.SaveErrorMessage)
                    .ConfigureAwait(false);

                throw new Exception(error);
            }
        }

        #endregion


        #region Update

        public async Task<int> UpdateJobProfileLibrary(
            JobProfileLibraryDTO JobProfileLibraryDTO, LoginDTO LoginDTO, DbTransaction transaction, CancellationToken ct)
        {
            try
            {
                const string sql = JobProfileLibraryQB.UPDATE_JOBPROFILELIBRARY;

                return await _QueryExecutor
                    .ExecuteAsync(LoginDTO, sql, JobProfileLibraryDTO, transaction, ct)
                    .ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _Validation
                    .HandleException(ex, ErrorResponse.UpdateErrorMessage)
                    .ConfigureAwait(false);

                throw new Exception(error);
            }
        }

        #endregion


        #region Delete

        public async Task<int> DeleteJobProfileLibrary(
            int JobProfileLibraryId, LoginDTO LoginDTO, DbTransaction transaction, CancellationToken ct)
        {
            try
            {
                string sql = JobProfileLibraryQB.DELETE_JOBPROFILELIBRARY;

                var parameters = new
                {
                    JobProfileLibraryId = JobProfileLibraryId,
                    ModifiedById = LoginDTO.UserId,
                    ModifiedOn = DateTime.UtcNow,
                    TenantId = LoginDTO.ClientId
                };

                return await _QueryExecutor
                    .ExecuteAsync(LoginDTO, sql, parameters, transaction, ct)
                    .ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string error = await _Validation
                    .HandleException(ex, ErrorResponse.DeleteErrorMessage)
                    .ConfigureAwait(false);

                throw new Exception(error);
            }
        }

        #endregion
    }
}