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

namespace RecruitmentDAL.CustomeCode.JobOffer
{
    public class JobOfferDAL : IJobOfferDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        #region Get

        public async Task<string> GetJobOffer(int JobOfferId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = JobOfferQB.GET_JOBOFFER;
                var parameters = new { JobOfferId = JobOfferId };
                JobOfferDTO JobOfferDTOs = await _QueryExecutor
                    .QuerySingleAsync<JobOfferDTO>(LoginDTO, sql, parameters, cancellationToken: ct)
                    .ConfigureAwait(false);
                string json = JsonConvert.SerializeObject(JobOfferDTOs);
                return json;
            }
            catch
            {
                throw;
            }
        }

        #endregion

        #region Select List

        public async Task<string> GetSelectListJobOffer(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = JobOfferQB.GETSELECTLIST_JOBOFFER;

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

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

        #endregion

        #region Save

        public async Task<int> SaveJobOffer(JobOfferDTO JobOfferDTO, LoginDTO LoginDTO, DbTransaction? transaction, CancellationToken ct)
        {
            try
            {
                const string sql = JobOfferQB.SAVE_JOBOFFER;

                return await _QueryExecutor
                    .ExecuteAsync(LoginDTO, sql, JobOfferDTO, transaction, cancellationToken: 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> UpdateJobOffer(JobOfferDTO JobOfferDTO, LoginDTO LoginDTO, DbTransaction? transaction, CancellationToken ct)
        {
            try
            {
                const string sql = JobOfferQB.UPDATE_JOBOFFER;

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

                throw new Exception(error);
            }
        }

        #endregion

        #region Response

        // Standalone, non-transactional follow-up update (same pattern as
        // IApplicationDAL.UpdateApplicationCurrentStage / IInterviewDAL.UpdateInterviewEcpMeetingId)
        // — called after the JobOffer save has already committed, once the candidate responds.
        public async Task UpdateJobOfferResponse(int JobOfferId, byte OfferResponseStatus, DateTime OfferResponseDate, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = JobOfferQB.UPDATE_JOBOFFER_RESPONSE;
                var parameters = new
                {
                    JobOfferId = JobOfferId,
                    OfferResponseStatus = OfferResponseStatus,
                    OfferResponseDate = OfferResponseDate,
                    TenantId = LoginDTO.ClientId
                };

                await _QueryExecutor
                    .ExecuteAsync(LoginDTO, sql, parameters, cancellationToken: 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<string> DeleteJobOffer(int JobOfferId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = JobOfferQB.DELETE_JOBOFFER;
                var parameter = new { JobOfferId = JobOfferId };

                int result = await _QueryExecutor
                    .ExecuteAsync(LoginDTO, sql, parameter, cancellationToken: ct)
                    .ConfigureAwait(false);

                return result > 0
                    ? SuccessResponse.DeleteSuccessMessage
                    : ErrorResponse.DeleteNotFoundMessage;
            }
            catch (Exception ex)
            {
                string error = await _Validation
                    .HandleException(ex, ErrorResponse.DeleteErrorMessage)
                    .ConfigureAwait(false);

                throw new Exception(error);
            }
        }

        #endregion
    }
}