using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;
using TMSDAL.DTO.TrainingNeed;
using TMSDAL.Query.TrainingNeed;

namespace TMSDAL.CustomCode.TrainingNeed
{
    public class TrainingNeedDAL : ITrainingNeedDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        public async Task<string> GetTrainingNeed(int trainingNeedId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { TrainingNeedId = trainingNeedId, TenantId = loginDTO.ClientId };
                var result = await _QueryExecutor.QuerySingleAsync<TrainingNeedDTO>(loginDTO, TrainingNeedQB.GET_TRAINING_NEED, parameters);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception) { throw; }
        }
        public async Task<string> GetSelectList(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
        {
            var result = await _QueryExecutor.QueryAsync<dynamic>(
                login, TrainingNeedQB.GET_SELECT_LIST,
                new { TenantId = login.ClientId });
            return JsonConvert.SerializeObject(result);
        }

        public async Task<string> GetTrainingNeedList(
    byte? needStatus,
    int? domainId,
    byte? priority,
    byte? sourceType,
    int page,
    int size,
    LoginDTO loginDTO)
        {
            try
            {
                var parameters = new
                {
                    TenantId = loginDTO.ClientId,
                    NeedStatus = needStatus,
                    DomainId = domainId,
                    Priority = priority,
                    SourceType = sourceType,
                    Offset = (page - 1) * size,
                    PageSize = size
                };

                // ✅ Get paged data
                var data = await _QueryExecutor.QueryAsync<TrainingNeedListDTO>(
                    loginDTO,
                    TrainingNeedQB.GET_TRAINING_NEED_LIST_PAGED,
                    parameters
                );

                // ✅ Get total count
                var total = await _QueryExecutor.TotalCountSql(
                    TrainingNeedQB.GET_TRAINING_NEED_LIST_PAGED_COUNT,
                    loginDTO,
                    parameters,
                    isCountQuery: true
                );

                // ✅ Construct response (manual PagedResult)
                var result = new
                {
                    Data = data,
                    TotalCount = total,
                    Page = page,
                    PageSize = size,
                    TotalPages = (int)Math.Ceiling(total / (double)size)
                };

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

        public async Task<int> SaveTrainingNeed(TrainingNeedDTO dto, LoginDTO loginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(loginDTO, TrainingNeedQB.SAVE_TRAINING_NEED, dto);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task<int> UpdateTrainingNeed(TrainingNeedDTO dto, LoginDTO loginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(loginDTO, TrainingNeedQB.UPDATE_TRAINING_NEED, dto);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.UpdateErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task<string> DeleteTrainingNeed(int trainingNeedId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { TrainingNeedId = trainingNeedId, TenantId = loginDTO.ClientId };
                int result = await _QueryExecutor.ExecuteAsync(loginDTO, TrainingNeedQB.DELETE_TRAINING_NEED, parameters);
                return result > 0 ? SuccessResponse.DeleteSuccessMessage : "Record not found or cannot be deleted (only Draft records can be deleted).";
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.DeleteErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task<byte> GetCurrentNeedStatus(int trainingNeedId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { TrainingNeedId = trainingNeedId, TenantId = loginDTO.ClientId };
                return await _QueryExecutor.ExecuteScalarAsync<byte>(loginDTO, TrainingNeedQB.GET_CURRENT_NEED_STATUS, parameters);
            }
            catch (Exception) { throw; }
        }

        public async Task<int> UpdateNeedStatus(SubmitTrainingNeedDTO dto, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new
                {
                    TrainingNeedId = dto.TrainingNeedId,
                    CurrentStatus = dto.CurrentStatus,
                    RequestedStatus = dto.RequestedStatus,
                    NeedStatus = dto.NeedStatus,
                    Remarks = dto.Remarks,
                    RejectionReason = dto.RejectionReason,
                    ModifiedById = loginDTO.UserId,
                    ModifiedOn = DateTime.UtcNow,
                    TenantId = loginDTO.ClientId
                };
                return await _QueryExecutor.ExecuteAsync(loginDTO, TrainingNeedQB.UPDATE_NEED_STATUS, parameters);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.UpdateErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task<int> FindOpenNeedIdBySource(string sourceRefEntity, int sourceRefId, byte needSourceType, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                var parameters = new
                {
                    SourceRefEntity = sourceRefEntity,
                    SourceRefId = sourceRefId,
                    NeedSourceType = needSourceType,
                    TenantId = loginDTO.ClientId
                };
                var result = await _QueryExecutor.QueryAsync<int>(loginDTO, TrainingNeedQB.FIND_OPEN_NEED_BY_SOURCE, parameters, cancellationToken: ct);
                return result.FirstOrDefault(); // 0 = not found (TrainingNeedId is never 0 for a saved row)
            }
            catch (Exception) { throw; }
        }
    }
}