using RecruitmentDAL.DTO.SelectionProcess;
using RecruitmentDAL.Query.SelectionProcess;
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.SelectionProcess
{
    public class SelectionProcessDAL : ISelectionProcessDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        #region Get

        public async Task<string> GetSelectionProcessTemplate(int SelectionProcessTemplateId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = SelectionProcessQB.GET_SELECTIONPROCESSTEMPLATE;

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

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

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

        #endregion

        #region Select List

        public async Task<string> GetSelectListSelectionProcessTemplate(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = SelectionProcessQB.GET_SELECTLIST_SELECTIONPROCESSTEMPLATE;

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

                var result = await _QueryExecutor
                    .QueryAsync<SelectionProcessTemplatePickListDTO>(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> SaveSelectionProcessTemplate(SelectionProcessTemplateDTO SelectionProcessTemplateDTO, LoginDTO LoginDTO, DbTransaction tx, CancellationToken ct)
        {
            try
            {
                const string sql = SelectionProcessQB.SAVE_SELECTIONPROCESSTEMPLATE;

                return await _QueryExecutor
                    .ExecuteAsync(LoginDTO, sql, SelectionProcessTemplateDTO, 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> UpdateSelectionProcessTemplate(SelectionProcessTemplateDTO SelectionProcessTemplateDTO, LoginDTO LoginDTO, DbTransaction tx, CancellationToken ct)
        {
            try
            {
                const string sql = SelectionProcessQB.UPDATE_SELECTIONPROCESSTEMPLATE;

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

                throw new Exception(error);
            }
        }

        #endregion

        #region Delete

        // Soft-delete via STATUS — history/audit trail is preserved.
        public async Task<string> DeleteSelectionProcessTemplate(int SelectionProcessTemplateId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = SelectionProcessQB.DELETE_SELECTIONPROCESSTEMPLATE;

                var parameter = new
                {
                    SelectionProcessTemplateId,
                    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 Get

        // List variant — returns every stage for a given template, ordered by StageOrder.
        public async Task<string> GetSelectionProcessStage(int SelectionProcessTemplateId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = SelectionProcessQB.GET_SELECTIONPROCESSSTAGE;

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

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

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

        #endregion

        #region Select List

        public async Task<string> GetSelectListSelectionProcessStage(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = SelectionProcessQB.GET_SELECTLIST_SELECTIONPROCESSSTAGE;

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

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

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

        #endregion

        #region Save

        public async Task<int> SaveSelectionProcessStage(SelectionProcessStageDTO SelectionProcessStageDTO, LoginDTO LoginDTO, DbTransaction tx, CancellationToken ct)
        {
            try
            {
                const string sql = SelectionProcessQB.SAVE_SELECTIONPROCESSSTAGE;

                return await _QueryExecutor
                    .ExecuteAsync(LoginDTO, sql, SelectionProcessStageDTO, 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> UpdateSelectionProcessStage(SelectionProcessStageDTO SelectionProcessStageDTO, LoginDTO LoginDTO, DbTransaction tx, CancellationToken ct)
        {
            try
            {
                const string sql = SelectionProcessQB.UPDATE_SELECTIONPROCESSSTAGE;

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

                throw new Exception(error);
            }
        }

        #endregion

        #region Delete

        // Soft-delete via STATUS — history/audit trail is preserved.
        public async Task<string> DeleteSelectionProcessStage(int SelectionProcessStageId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = SelectionProcessQB.DELETE_SELECTIONPROCESSSTAGE;

                var parameter = new
                {
                    SelectionProcessStageId,
                    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);
            }
        }

        public async Task<int> GetSelectionProcessStageQuizId(int SelectionProcessStageId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = SelectionProcessQB.GET_SELECTIONPROCESSSTAGE_QUIZID;

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

                return await _QueryExecutor
                    .ExecuteScalarAsync<int>(LoginDTO, sql, parameters, cancellationToken: ct)
                    .ConfigureAwait(false);
            }
            catch
            {
                throw;
            }
        }

        public async Task<bool> IsSelectionProcessStageTerminalHireCategory(int SelectionProcessStageId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = SelectionProcessQB.GET_SELECTIONPROCESSSTAGE_TERMINALHIRE_FLAG;

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

                return await _QueryExecutor
                    .ExecuteScalarAsync<bool>(LoginDTO, sql, parameters, cancellationToken: ct)
                    .ConfigureAwait(false);
            }
            catch
            {
                throw;
            }
        }

        #endregion
    }
}