using RecruitmentDAL.DTO.FlsBridge;
using RecruitmentDAL.Query.FlsBridge;
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.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace RecruitmentDAL.CustomeCode.FlsBridge
{
    public class RecruitmentFlsActionDAL : IRecruitmentFlsActionDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        #region Get

        public async Task<string> GetRecruitmentFlsAction(int RecruitmentFlsActionId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = RecruitmentFlsActionQB.GET_RECRUITMENTFLSACTION;
                var parameters = new { RecruitmentFlsActionId = RecruitmentFlsActionId, TenantId = LoginDTO.ClientId };
                RecruitmentFlsActionDTO dto = await _QueryExecutor
                    .QuerySingleAsync<RecruitmentFlsActionDTO>(LoginDTO, sql, parameters, cancellationToken: ct)
                    .ConfigureAwait(false);
                return JsonConvert.SerializeObject(dto);
            }
            catch
            {
                throw;
            }
        }

        public async Task<RecruitmentFlsActionDTO?> GetRecruitmentFlsActionByRespondentId(int FlsRespondentId, LoginDTO LoginDTO, CancellationToken ct)
        {
            var result = await _QueryExecutor
                .QueryAsync<RecruitmentFlsActionDTO>(
                    LoginDTO, RecruitmentFlsActionQB.GET_RECRUITMENTFLSACTION_BY_RESPONDENTID,
                    new { FlsRespondentId = FlsRespondentId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
            return result.FirstOrDefault();
        }

        #endregion

        #region Select List

        public async Task<string> GetSelectListRecruitmentFlsAction(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = RecruitmentFlsActionQB.GET_SELECTLIST_RECRUITMENTFLSACTION;
                var result = await _QueryExecutor
                    .QueryAsync<RecruitmentFlsActionDTO>(LoginDTO, sql, new { TenantId = LoginDTO.ClientId }, 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> SaveRecruitmentFlsAction(RecruitmentFlsActionDTO RecruitmentFlsActionDTO, LoginDTO LoginDTO, DbTransaction tx, CancellationToken ct)
        {
            try
            {
                const string sql = RecruitmentFlsActionQB.SAVE_RECRUITMENTFLSACTION;
                return await _QueryExecutor
                    .ExecuteAsync(LoginDTO, sql, RecruitmentFlsActionDTO, 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> UpdateRecruitmentFlsAction(RecruitmentFlsActionDTO RecruitmentFlsActionDTO, LoginDTO LoginDTO, DbTransaction tx, CancellationToken ct)
        {
            try
            {
                const string sql = RecruitmentFlsActionQB.UPDATE_RECRUITMENTFLSACTION;
                return await _QueryExecutor
                    .ExecuteAsync(LoginDTO, sql, RecruitmentFlsActionDTO, tx, 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> DeleteRecruitmentFlsAction(int RecruitmentFlsActionId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = RecruitmentFlsActionQB.DELETE_RECRUITMENTFLSACTION;
                var parameter = new
                {
                    RecruitmentFlsActionId = RecruitmentFlsActionId,
                    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

        #region Coordinator natural-key lookups

        public async Task<int> ResolveFormTemplateIdByCode(string FormTemplateCode, LoginDTO LoginDTO, CancellationToken ct)
        {
            var result = await _QueryExecutor.QueryAsync<int>(
                LoginDTO, RecruitmentFlsActionQB.RESOLVE_FORMTEMPLATEID_BY_CODE,
                new { Code = FormTemplateCode }, cancellationToken: ct).ConfigureAwait(false);
            return result.FirstOrDefault();
        }

        public async Task<int> ResolveModuleIdByCode(string ModuleCode, LoginDTO LoginDTO, CancellationToken ct)
        {
            var result = await _QueryExecutor.QueryAsync<int>(
                LoginDTO, RecruitmentFlsActionQB.RESOLVE_MODULEID_BY_CODE,
                new { Code = ModuleCode }, cancellationToken: ct).ConfigureAwait(false);
            return result.FirstOrDefault();
        }

        public async Task<int> ResolveRegistrationIdByCode(string RegistrationCode, LoginDTO LoginDTO, CancellationToken ct)
        {
            var result = await _QueryExecutor.QueryAsync<int>(
                LoginDTO, RecruitmentFlsActionQB.RESOLVE_REGISTRATIONID_BY_CODE,
                new { Code = RegistrationCode, TenantId = LoginDTO.ClientId }, cancellationToken: ct).ConfigureAwait(false);
            return result.FirstOrDefault();
        }

        public async Task<int> ResolveInstanceIdByCode(string InstanceCode, int FlsRegistrationId, LoginDTO LoginDTO, CancellationToken ct)
        {
            var result = await _QueryExecutor.QueryAsync<int>(
                LoginDTO, RecruitmentFlsActionQB.RESOLVE_INSTANCEID_BY_CODE,
                new { Code = InstanceCode, FlsRegistrationId = FlsRegistrationId, TenantId = LoginDTO.ClientId }, cancellationToken: ct).ConfigureAwait(false);
            return result.FirstOrDefault();
        }

        public async Task<int> ResolveGroupIdByCode(string GroupCode, int FlsInstanceId, LoginDTO LoginDTO, CancellationToken ct)
        {
            var result = await _QueryExecutor.QueryAsync<int>(
                LoginDTO, RecruitmentFlsActionQB.RESOLVE_GROUPID_BY_CODE,
                new { Code = GroupCode, FlsInstanceId = FlsInstanceId, TenantId = LoginDTO.ClientId }, cancellationToken: ct).ConfigureAwait(false);
            return result.FirstOrDefault();
        }

        public async Task InsertFlsInstanceOpen(int FlsInstanceId, int FlsRegistrationId, string InstanceCode, string InstanceName, LoginDTO LoginDTO, CancellationToken ct)
        {
            await _QueryExecutor.ExecuteAsync(
                LoginDTO, RecruitmentFlsActionQB.INSERT_FLS_INSTANCE_OPEN,
                new
                {
                    FlsInstanceId = FlsInstanceId,
                    FlsRegistrationId = FlsRegistrationId,
                    InstanceCode = InstanceCode,
                    InstanceName = InstanceName,
                    CreatedById = LoginDTO.UserId,
                    ModifiedById = LoginDTO.UserId,
                    TenantId = LoginDTO.ClientId
                },
                cancellationToken: ct).ConfigureAwait(false);
        }

        public async Task InsertFlsInstanceGroup(int GroupId, int FlsInstanceId, string GroupCode, string GroupName, LoginDTO LoginDTO, CancellationToken ct)
        {
            await _QueryExecutor.ExecuteAsync(
                LoginDTO, RecruitmentFlsActionQB.INSERT_FLS_INSTANCE_GROUP,
                new
                {
                    GroupId = GroupId,
                    FlsInstanceId = FlsInstanceId,
                    GroupCode = GroupCode,
                    GroupName = GroupName,
                    CreatedById = LoginDTO.UserId,
                    ModifiedById = LoginDTO.UserId,
                    TenantId = LoginDTO.ClientId
                },
                cancellationToken: ct).ConfigureAwait(false);
        }

        #endregion
    }
}