using RecruitmentDAL.DTO.Application;
using RecruitmentDAL.Query.Application;
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;
using System.Data.Common;
using System.Threading;
using System.Threading.Tasks;

namespace RecruitmentDAL.CustomeCode.Application
{
    public class ApplicationDAL : IApplicationDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        #region Get

        public async Task<string> GetApplication(int ApplicationId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = ApplicationQB.GET_APPLICATION;

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

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

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

        #endregion

        #region Select List

        public async Task<string> GetSelectListApplication(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = ApplicationQB.GET_SELECTLIST_APPLICATION;

                // CriteriaDTO.Id doubles as the optional JobRequisitionId filter for the
                // "pipeline view of one requisition" use case (mirrors MMHeadDAL's convention
                // of overloading CriteriaDTO.Id as the primary filter id). 0/unset = no filter.
                int? jobRequisitionId = CriteriaDTO != null && CriteriaDTO.Id != 0
                    ? CriteriaDTO.Id
                    : (int?)null;

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

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

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

        // Candidate-scoped application history (Phase 3 candidate self-service portal) — see
        // ApplicationQB.GET_APPLICATIONS_BY_CANDIDATE.
        public async Task<string> GetApplicationsByCandidate(int CandidateId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = ApplicationQB.GET_APPLICATIONS_BY_CANDIDATE;

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

                var result = await _QueryExecutor
                    .QueryAsync<ApplicationDTO>(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> SaveApplication(ApplicationDTO ApplicationDTO, LoginDTO LoginDTO, DbTransaction? transaction, CancellationToken ct)
        {
            try
            {
                const string sql = ApplicationQB.SAVE_APPLICATION;

                return await _QueryExecutor
                    .ExecuteAsync(LoginDTO, sql, ApplicationDTO, transaction, 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> UpdateApplication(ApplicationDTO ApplicationDTO, LoginDTO LoginDTO, DbTransaction? transaction, CancellationToken ct)
        {
            try
            {
                const string sql = ApplicationQB.UPDATE_APPLICATION;

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

                throw new Exception(error);
            }
        }

        #endregion

        #region Delete

        public async Task<string> DeleteApplication(int ApplicationId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = ApplicationQB.DELETE_APPLICATION;

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

                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

        #region Advance Pipeline Stage

        // Small, explicit follow-up UPDATE fired by ApplicationBLL.SaveApplicationStageHistory
        // right after the stage-history row is persisted — advances TAPPLICATION.CURRENTSTAGEID
        // to the new stage. Deliberately outside the stage-history's ExecuteSaveAsync transaction
        // (see IApplicationDAL.UpdateApplicationCurrentStage doc comment).
        public async Task UpdateApplicationCurrentStage(int applicationId, int newStageId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = ApplicationQB.UPDATE_APPLICATION_CURRENTSTAGE;

                var parameters = new
                {
                    ApplicationId = applicationId,
                    CurrentStageId = newStageId,
                    ModifiedById = LoginDTO.UserId,
                    ModifiedOn = DateTime.UtcNow,
                    TenantId = LoginDTO.ClientId
                };

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

                throw new Exception(error);
            }
        }

        #endregion

        #region Get

        public async Task<string> GetApplicationStageHistory(int ApplicationStageHistoryId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = ApplicationQB.GET_APPLICATIONSTAGEHISTORY;

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

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

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

        #endregion

        #region Select List

        public async Task<string> GetSelectListApplicationStageHistory(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = ApplicationQB.GET_SELECTLIST_APPLICATIONSTAGEHISTORY;

                // CriteriaDTO.Id carries the mandatory ApplicationId this list is scoped to —
                // this is what a future "Candidate Profile" view reads to render the full
                // per-stage trail for one application.
                int applicationId = CriteriaDTO?.Id ?? 0;

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

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

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

        #endregion

        #region Save

        public async Task<int> SaveApplicationStageHistory(ApplicationStageHistoryDTO ApplicationStageHistoryDTO, LoginDTO LoginDTO, DbTransaction? transaction, CancellationToken ct)
        {
            try
            {
                const string sql = ApplicationQB.SAVE_APPLICATIONSTAGEHISTORY;

                return await _QueryExecutor
                    .ExecuteAsync(LoginDTO, sql, ApplicationStageHistoryDTO, transaction, 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> UpdateApplicationStageHistory(ApplicationStageHistoryDTO ApplicationStageHistoryDTO, LoginDTO LoginDTO, DbTransaction? transaction, CancellationToken ct)
        {
            try
            {
                const string sql = ApplicationQB.UPDATE_APPLICATIONSTAGEHISTORY;

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

                throw new Exception(error);
            }
        }

        #endregion

        #region Delete

        public async Task<string> DeleteApplicationStageHistory(int ApplicationStageHistoryId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = ApplicationQB.DELETE_APPLICATIONSTAGEHISTORY;

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

                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
    }
}