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.Linq;
using TMSDAL.DTO.TrainerProfile;
using TMSDAL.Query.ExternalVendor;
using TMSDAL.Query.TrainerProfile;

namespace TMSDAL.CustomCode.TrainerProfile
{
    public class TrainerProfileDAL : ITrainerProfileDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        public async Task<string> GetTrainerProfile(int trainerId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { TrainerId = trainerId, TenantId = loginDTO.ClientId };
                var result = await _QueryExecutor.QuerySingleAsync<TrainerProfileDTO>(loginDTO, TrainerProfileQB.GET_TRAINER, parameters);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception) { throw; }
        }

        public async Task SaveTrainerProfile(TrainerProfileDTO dto, LoginDTO loginDTO)
        {
            await _QueryExecutor.ExecuteAsync(
                loginDTO,
                TrainerProfileQB.SAVE_TRAINER,
                new
                {
                    dto.TrainerId,
                    dto.TrainerCode,
                    dto.TrainerName,
                    dto.TrainerFullName,
                    dto.TrainerType,
                    dto.EmployeeId,           // BLL sets to -1 when External
                    dto.ExternalVendorId,     // BLL sets to -1 when Internal
                    dto.Specialisations,
                    dto.QualifiedProgrammeIds,
                    dto.MaxSessionsPerMonth,
                    dto.Email,
                    dto.Phone,
                    dto.IsActive,
                    dto.Remarks,
                    // VERSION omitted — hardcoded 0 in SQL
                    Status = (byte)1,
                    SortOrder = (short)9999,
                    dto.ContactId,
                    // SOURCETYPE omitted — hardcoded 5 in SQL
                    dto.TenantId,
                    dto.CreatedById,
                    dto.CreatedOn,
                    dto.ModifiedById,
                    dto.ModifiedOn
                });
        }

        public async Task UpdateTrainerProfile(TrainerProfileDTO dto, LoginDTO loginDTO)
        {
            await _QueryExecutor.ExecuteAsync(
                loginDTO,
                TrainerProfileQB.UPDATE_TRAINER,
                new
                {
                    dto.TrainerId,
                    dto.TrainerCode,
                    dto.TrainerName,
                    dto.TrainerFullName,
                    dto.TrainerType,
                    dto.EmployeeId,           // BLL sets to -1 when External
                    dto.ExternalVendorId,     // BLL sets to -1 when Internal
                    dto.Specialisations,
                    dto.QualifiedProgrammeIds,
                    dto.MaxSessionsPerMonth,
                    dto.Email,
                    dto.Phone,
                    dto.IsActive,
                    dto.Remarks,
                    // VERSION omitted — SQL does VERSION + 1 automatically
                    Status = (byte)1,
                    SortOrder = (short)9999,
                    dto.ContactId,
                    dto.TenantId,
                    dto.ModifiedById,
                    dto.ModifiedOn
                });
        }
        public async Task<string> GetSelectList(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
        {
            var result = await _QueryExecutor.QueryAsync<dynamic>(
                login, TrainerProfileQB.GET_SELECTLIST_TRAINER,
                new { TenantId = login.ClientId });
            return JsonConvert.SerializeObject(result);
        }

        public async Task<int?> GetTrainerIdByEmployeeId(int employeeId, LoginDTO login, CancellationToken ct)
        {
            var result = await _QueryExecutor.QueryAsync<int>(
                login, TrainerProfileQB.GET_TRAINERID_BY_EMPLOYEEID,
                new { EmployeeId = employeeId, TenantId = login.ClientId }, cancellationToken: ct);
            return result.Any() ? result.First() : null;
        }

        public async Task<string> DeleteTrainerProfile(int trainerId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { TrainerId = trainerId, TenantId = loginDTO.ClientId };
                int result = await _QueryExecutor.ExecuteAsync(loginDTO, TrainerProfileQB.DELETE_TRAINER, parameters);
                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<string> GetSelectListTrainerProfile(LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { TenantId = loginDTO.ClientId };
                var result = await _QueryExecutor.QueryAsync<TrainerProfileDTO>(loginDTO, TrainerProfileQB.GET_TRAINER_LIST, parameters);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception) { throw; }
        }
    }
}