using RecruitmentDAL.DTO.Candidate;
using RecruitmentDAL.Query.Candidate;
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;

namespace RecruitmentDAL.CustomeCode.Candidate
{
    public class CandidateDAL : ICandidateDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        #region Get

        public async Task<string> GetCandidate(int CandidateId, LoginDTO LoginDTO, CancellationToken ct)
        {
            string json = "";
            try
            {
                string sql = CandidateQB.GET_CANDIDATE;
                var parameters = new { CandidateId = CandidateId, TenantId = LoginDTO.ClientId };
                CandidateDTO CandidateDTOs = await _QueryExecutor
                    .QuerySingleAsync<CandidateDTO>(LoginDTO, sql, parameters, cancellationToken: ct)
                    .ConfigureAwait(false);
                json = JsonConvert.SerializeObject(CandidateDTOs);
                return json;
            }
            catch
            {
                throw;
            }
        }

        #endregion

        #region Select List

        // Paged via QueryPagedAsync — see CandidateQB.GET_SELECTLIST_CANDIDATE for why (candidate
        // pool grows large, unlike other GetSelectList lists in this module) and for the note on
        // IQueryExecutor.QueryPagedAsync's real signature (no separate count-sql parameter; note
        // it also has no CancellationToken parameter, so ct is not forwarded into that call —
        // matching every other QueryPagedAsync call site in the repo, e.g. KmArtifactDAL,
        // LoyaltyProgramDAL).
        public async Task<string> GetSelectListCandidate(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = CandidateQB.GET_SELECTLIST_CANDIDATE;

                var parameters = new
                {
                    TenantId = LoginDTO.ClientId,
                    SearchKeyword = CriteriaDTO?.SearchKeyword,
                    Offset = Math.Max(0, FirstNumber - 1),
                    PageSize = MaxResult
                };

                var result = await _QueryExecutor
                    .QueryPagedAsync<CandidateDTO>(LoginDTO, sql, parameters)
                    .ConfigureAwait(false);

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

        // Bridge query for gb4.7mfe's legacy Applicant dashboard — resolves a batch of legacy
        // applicant emails to their matching Candidate's current pipeline stage, where a real
        // Candidate row exists. See CandidateQB.GET_CURRENT_STAGE_BY_EMAIL for the join detail
        // and why this can't instead be added to the legacy Applicant report's own SQL.
        public async Task<string> GetCurrentStageByEmail(IEnumerable<string> Emails, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                var emailList = (Emails ?? Enumerable.Empty<string>())
                    .Where(e => !string.IsNullOrWhiteSpace(e))
                    .Distinct()
                    .ToList();

                if (emailList.Count == 0)
                    return JsonConvert.SerializeObject(new List<CandidateCurrentStageDTO>());

                string sql = CandidateQB.GET_CURRENT_STAGE_BY_EMAIL;
                var parameters = new { TenantId = LoginDTO.ClientId, Emails = emailList };

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

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

        // Duplicate-applicant lookup for the public career-site apply flow — see
        // CandidateQB.GET_CANDIDATE_BY_EMAIL.
        public async Task<string> GetCandidateByEmail(string Email, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = CandidateQB.GET_CANDIDATE_BY_EMAIL;
                var parameters = new { Email = Email, TenantId = LoginDTO.ClientId };
                CandidateDTO result = await _QueryExecutor
                    .QuerySingleAsync<CandidateDTO>(LoginDTO, sql, parameters, cancellationToken: ct)
                    .ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch
            {
                throw;
            }
        }

        // Internal-mobility dedup lookup — see CandidateQB.GET_CANDIDATE_BY_EMPLOYEEID.
        public async Task<string> GetCandidateByEmployeeId(int EmployeeId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = CandidateQB.GET_CANDIDATE_BY_EMPLOYEEID;
                var parameters = new { EmployeeId = EmployeeId, TenantId = LoginDTO.ClientId };
                CandidateDTO result = await _QueryExecutor
                    .QuerySingleAsync<CandidateDTO>(LoginDTO, sql, parameters, cancellationToken: ct)
                    .ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch
            {
                throw;
            }
        }

        #endregion

        #region Save

        // Called from BaseEntityAppService.ExecuteSaveAsync's persistFunc — must run on the
        // pipeline's own transaction (tx) so validation/workflow/event-publish/outbox all commit
        // or roll back atomically with the insert.
        public async Task<int> SaveCandidate(CandidateDTO CandidateDTO, LoginDTO LoginDTO, DbTransaction tx, CancellationToken ct)
        {
            try
            {
                const string sql = CandidateQB.SAVE_CANDIDATE;

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

                throw new Exception(error);
            }
        }

        #endregion

        #region Update

        public async Task<int> UpdateCandidate(CandidateDTO CandidateDTO, LoginDTO LoginDTO, DbTransaction tx, CancellationToken ct)
        {
            try
            {
                const string sql = CandidateQB.UPDATE_CANDIDATE;

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

                throw new Exception(error);
            }
        }

        // Standalone follow-up call — NOT part of any ExecuteSaveAsync transaction. See
        // CandidateQB.UPDATE_CANDIDATE_EMPLOYEEID doc comment for why.
        public async Task UpdateCandidateEmployeeId(int CandidateId, int EmployeeId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = CandidateQB.UPDATE_CANDIDATE_EMPLOYEEID;
                var parameters = new
                {
                    CandidateId = CandidateId,
                    EmployeeId = EmployeeId,
                    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> DeleteCandidate(int CandidateId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = CandidateQB.DELETE_CANDIDATE;
                var parameter = new
                {
                    CandidateId = CandidateId,
                    TenantId = LoginDTO.ClientId,
                    ModifiedById = LoginDTO.UserId,
                    ModifiedOn = DateTime.UtcNow
                };

                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);

                throw new Exception(error);
            }
        }

        #endregion
    }
}