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

namespace RecruitmentDAL.CustomeCode.Interview
{
    public class InterviewDAL : IInterviewDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public InterviewDAL(IQueryExecutor QueryExecutor, IValidation validation)
        {
            _QueryExecutor = QueryExecutor;
            _Validation = validation;
        }

        public async Task<InterviewDTO> GetInterview(int InterviewId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = InterviewQB.GET_INTERVIEW;
                var parameters = new { InterviewId = InterviewId };
                InterviewDTO InterviewDTOs = await _QueryExecutor
                    .QuerySingleAsync<InterviewDTO>(LoginDTO, sql, parameters, cancellationToken: ct)
                    .ConfigureAwait(false);
                return InterviewDTOs;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListInterview(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string SQL = InterviewQB.GETSELECTLIST_INTERVIEW;
                var Result = await _QueryExecutor
                    .QueryAsync<InterviewPickListDTO>(LoginDTO, SQL, null!, cancellationToken: ct)
                    .ConfigureAwait(false);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveInterview(InterviewDTO InterviewDTO, LoginDTO LoginDTO, DbTransaction? transaction, CancellationToken ct)
        {
            try
            {
                const string Sql = InterviewQB.SAVE_INTERVIEW;
                return await _QueryExecutor
                    .ExecuteAsync(LoginDTO, Sql, InterviewDTO, transaction, cancellationToken: ct)
                    .ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string Error = await _Validation
                    .HandleException(ex, $"{ErrorResponse.SaveErrorMessage}")
                    .ConfigureAwait(false);
                throw new Exception(Error);
            }
        }

        public async Task<int> UpdateInterview(InterviewDTO InterviewDTO, LoginDTO LoginDTO, DbTransaction? transaction, CancellationToken ct)
        {
            try
            {
                const string Sql = InterviewQB.UPDATE_INTERVIEW;
                return await _QueryExecutor
                    .ExecuteAsync(LoginDTO, Sql, InterviewDTO, transaction, cancellationToken: ct)
                    .ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                string Error = await _Validation
                    .HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}")
                    .ConfigureAwait(false);
                throw new Exception(Error);
            }
        }



        // Non-transactional follow-up fired by InterviewBLL.SaveInterview right after
        // EcpIntegrationService.ScheduleMeetingAsync returns — stores the ECP-side MeetingId onto
        // TINTERVIEW.ECPMEETINGID. Deliberately outside SaveInterview's own ExecuteSaveAsync
        // transaction (same pattern as IApplicationDAL.UpdateApplicationCurrentStage) since the
        // ECP call happens after the interview save has already committed.
        public async Task UpdateInterviewEcpMeetingId(int InterviewId, int EcpMeetingId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = InterviewQB.UPDATE_INTERVIEW_ECPMEETINGID;
                var parameters = new
                {
                    InterviewId = InterviewId,
                    EcpMeetingId = EcpMeetingId,
                    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}")
                    .ConfigureAwait(false);
                throw new Exception(Error);
            }
        }

        public async Task<string> DeleteInterview(int InterviewId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string Sql = InterviewQB.DELETE_INTERVIEW;
                var parameter = new { InterviewId = InterviewId };
                var result = await _QueryExecutor
                    .ExecuteAsync(LoginDTO, Sql, parameter, cancellationToken: ct)
                    .ConfigureAwait(false);

                if (result > 0)
                {
                    return $"{SuccessResponse.DeleteSuccessMessage}";
                }
                else
                {
                    return $"{ErrorResponse.DeleteNotFoundMessage}";
                }
            }
            catch (Exception ex)
            {
                string Error = await _Validation
                    .HandleException(ex, $"{ErrorResponse.DeleteErrorMessage}")
                    .ConfigureAwait(false);
                throw new Exception(Error);
            }
        }
    }
}