using RecruitmentDAL.DTO.InterviewScorecard;
using RecruitmentDAL.Query.InterviewScorecard;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace RecruitmentDAL.CustomeCode.InterviewScorecard
{
    public class InterviewScorecardDAL : IInterviewScorecardDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        #region Get

        public async Task<string> GetInterviewScorecard(int InterviewScorecardId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = InterviewScorecardQB.GET_INTERVIEWSCORECARD;

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

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

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

        #endregion

        #region Get By Interview

        // All active scorecards for one interview, each hydrated with its own detail rows —
        // bounded by panel size (one scorecard per interviewer), so the per-scorecard detail
        // fetch below is not a large-N concern.
        public async Task<string> GetInterviewScorecardByInterview(int InterviewId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = InterviewScorecardQB.GET_INTERVIEWSCORECARD_BY_INTERVIEW;
                var parameters = new { InterviewId = InterviewId, TenantId = LoginDTO.ClientId };

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

                foreach (var scorecard in scorecards)
                {
                    string detailSql = InterviewScorecardQB.GET_INTERVIEWSCORECARDDETAIL_BY_SCORECARD;
                    var detailParameters = new { InterviewScorecardId = scorecard.InterviewScorecardId, TenantId = LoginDTO.ClientId };

                    var details = await _QueryExecutor
                        .QueryAsync<InterviewScorecardDetailDTO>(LoginDTO, detailSql, detailParameters, cancellationToken: ct)
                        .ConfigureAwait(false);

                    scorecard.InterviewScorecardDetailArray = details?.ToList() ?? new List<InterviewScorecardDetailDTO>();
                }

                return JsonConvert.SerializeObject(scorecards);
            }
            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> SaveInterviewScorecard(InterviewScorecardDTO InterviewScorecardDTO, LoginDTO LoginDTO, DbTransaction tx, CancellationToken ct)
        {
            try
            {
                const string sql = InterviewScorecardQB.SAVE_INTERVIEWSCORECARD;

                return await _QueryExecutor
                    .ExecuteAsync(LoginDTO, sql, InterviewScorecardDTO, 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> UpdateInterviewScorecard(InterviewScorecardDTO InterviewScorecardDTO, LoginDTO LoginDTO, DbTransaction tx, CancellationToken ct)
        {
            try
            {
                const string sql = InterviewScorecardQB.UPDATE_INTERVIEWSCORECARD;

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

                throw new Exception(error);
            }
        }

        #endregion

        #region Delete

        // Soft delete (STATUS = 2 / Deleted) — see InterviewScorecardQB.DELETE_INTERVIEWSCORECARD.
        public async Task<string> DeleteInterviewScorecard(int InterviewScorecardId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = InterviewScorecardQB.DELETE_INTERVIEWSCORECARD;

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

        public async Task<string> GetInterviewScorecardDetail(int InterviewScorecardDetailId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = InterviewScorecardQB.GET_INTERVIEWSCORECARDDETAIL;

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

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

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

        #endregion

        #region Get By Scorecard

        public async Task<string> GetInterviewScorecardDetailByScorecard(int InterviewScorecardId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = InterviewScorecardQB.GET_INTERVIEWSCORECARDDETAIL_BY_SCORECARD;

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

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

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

        #endregion

        #region Save

        public async Task<int> SaveInterviewScorecardDetail(InterviewScorecardDetailDTO InterviewScorecardDetailDTO, LoginDTO LoginDTO, DbTransaction tx, CancellationToken ct)
        {
            try
            {
                const string sql = InterviewScorecardQB.SAVE_INTERVIEWSCORECARDDETAIL;

                return await _QueryExecutor
                    .ExecuteAsync(LoginDTO, sql, InterviewScorecardDetailDTO, 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> UpdateInterviewScorecardDetail(InterviewScorecardDetailDTO InterviewScorecardDetailDTO, LoginDTO LoginDTO, DbTransaction tx, CancellationToken ct)
        {
            try
            {
                const string sql = InterviewScorecardQB.UPDATE_INTERVIEWSCORECARDDETAIL;

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

                throw new Exception(error);
            }
        }

        #endregion

        #region Delete

        // Soft delete (STATUS = 2 / Deleted) — see InterviewScorecardQB.DELETE_INTERVIEWSCORECARDDETAIL.
        public async Task<string> DeleteInterviewScorecardDetail(int InterviewScorecardDetailId, LoginDTO LoginDTO, CancellationToken ct)
        {
            try
            {
                string sql = InterviewScorecardQB.DELETE_INTERVIEWSCORECARDDETAIL;

                var parameter = new
                {
                    InterviewScorecardDetailId = InterviewScorecardDetailId,
                    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
    }
}