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.TrainingDomain;
using TMSDAL.DTO.TrainingInstance;
using TMSDAL.Query.AssessmentPaper;
using TMSDAL.Query.TrainingDomain;
using TMSDAL.Query.TrainingInstance;
using TMSDAL.Query.TrainingNeed;

namespace TMSDAL.CustomCode.TrainingInstance
{
    public class TrainingInstanceDAL : ITrainingInstanceDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        public async Task<TrainingInstanceDTO?> GetTrainingInstance(int instanceId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { InstanceId = instanceId, TenantId = loginDTO.ClientId };
                return await _QueryExecutor.QuerySingleAsync<TrainingInstanceDTO>(loginDTO, TrainingInstanceQB.GET_INSTANCE, parameters);
            }
            catch (Exception) { throw; }
        }

        public async Task<string> GetTrainingInstancePageList(
    byte? instanceStatus,
    int? programmeId,
    int page,
    int size,
    LoginDTO loginDTO)
        {
            try
            {
                var parameters = new
                {
                    TenantId = loginDTO.ClientId,
                    InstanceStatus = instanceStatus,
                    ProgrammeId = programmeId,
                    Offset = (page - 1) * size,
                    PageSize = size
                };

                // ✅ Fetch paged data
                var data = await _QueryExecutor.QueryAsync<TrainingInstanceListDTO>(
                    loginDTO,
                    TrainingInstanceQB.GET_INSTANCE_LIST_PAGED,
                    parameters
                );

                // ✅ Fetch total count
                var total = await _QueryExecutor.TotalCountSql(
                    TrainingInstanceQB.GET_INSTANCE_LIST_PAGED_COUNT,
                    loginDTO,
                    parameters,
                    isCountQuery: true
                );

                // ✅ Build response
                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<string> GetTrainingInstanceList(LoginDTO loginDTO)
        {
            try
            {
                string sql = TrainingInstanceQB.GET_TRAINING_INSTANCE_LIST;
                var parameters = new { TenantId = loginDTO.ClientId };
                var result = await _QueryExecutor.QueryAsync<TrainingInstanceListDTO>(loginDTO, sql, parameters);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<string> GetSelectList(CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            var result = await _QueryExecutor.QueryWithCriteriaAsync<dynamic>(
                    loginDTO,
                    TrainingInstanceQB.GET_SELECT_LIST,
                    new { TenantId = loginDTO.ClientId },
                    criteriaDTO,
                    ct);
            return JsonConvert.SerializeObject(result);
        }

        public async Task<List<InstanceSessionDTO>> GetInstanceSessions(int instanceId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { InstanceId = instanceId, TenantId = loginDTO.ClientId };
                var result = await _QueryExecutor.QueryAsync<InstanceSessionDTO>(loginDTO, TrainingInstanceQB.GET_INSTANCE_SESSIONS, parameters);
                return result.ToList();
            }
            catch (Exception) { throw; }
        }

        public async Task<InstanceDeliveryConfigDTO?> GetDeliveryConfig(int instanceId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { InstanceId = instanceId, TenantId = loginDTO.ClientId };
                return await _QueryExecutor.QuerySingleAsync<InstanceDeliveryConfigDTO>(loginDTO, TrainingInstanceQB.GET_DELIVERY_CONFIG, parameters);
            }
            catch (Exception) { throw; }
        }

        public async Task<TrainingInstanceDTO?> GetInstanceCapacity(int instanceId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { InstanceId = instanceId, TenantId = loginDTO.ClientId };
                return await _QueryExecutor.QuerySingleAsync<TrainingInstanceDTO>(loginDTO, TrainingInstanceQB.GET_INSTANCE_CAPACITY, parameters);
            }
            catch (Exception) { throw; }
        }

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

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

        public async Task<int> DeleteTrainingInstance(int instanceId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { InstanceId = instanceId, TenantId = loginDTO.ClientId };
                return await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.DELETE_INSTANCE, parameters);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.DeleteErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task SaveInstanceSession(InstanceSessionDTO dto, LoginDTO loginDTO)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.SAVE_INSTANCE_SESSION, dto);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task UpdateInstanceSession(InstanceSessionDTO dto, LoginDTO loginDTO)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.UPDATE_INSTANCE_SESSION, dto);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.UpdateErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task<int?> GetExistingSessionId(int instanceId, int programmeSessionId, LoginDTO loginDTO)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<int>(
                    loginDTO, TrainingInstanceQB.GET_EXISTING_SESSION_ID_BY_PROGRAMME_SESSION,
                    new { InstanceId = instanceId, ProgrammeSessionId = programmeSessionId });
                return result.Any() ? result.First() : (int?)null;
            }
            catch (Exception) { throw; }
        }

        public async Task DeleteInstanceSessions(int instanceId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { InstanceId = instanceId, TenantId = loginDTO.ClientId };
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.DELETE_INSTANCE_SESSIONS, parameters);
            }
            catch (Exception) { throw; }
        }

        public async Task SaveDeliveryConfig(InstanceDeliveryConfigDTO dto, LoginDTO loginDTO)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.SAVE_DELIVERY_CONFIG, dto);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task UpdateDeliveryConfig(InstanceDeliveryConfigDTO dto, LoginDTO loginDTO)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.UPDATE_DELIVERY_CONFIG, dto);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.UpdateErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task DeleteDeliveryConfig(int instanceId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { InstanceId = instanceId, TenantId = loginDTO.ClientId };
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.DELETE_DELIVERY_CONFIG, parameters);
            }
            catch (Exception) { throw; }
        }

        public async Task DeleteCertRenewalAlertsByInstance(int instanceId, LoginDTO loginDTO)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.DELETE_CERT_RENEWAL_ALERTS_BY_INSTANCE, new { InstanceId = instanceId });
        }

        public async Task DeleteCertificatesByInstance(int instanceId, LoginDTO loginDTO)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.DELETE_CERTIFICATES_BY_INSTANCE, new { InstanceId = instanceId });
        }

        public async Task DeleteAttemptResponsesByInstance(int instanceId, LoginDTO loginDTO)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.DELETE_ATTEMPT_RESPONSES_BY_INSTANCE, new { InstanceId = instanceId });
        }

        public async Task DeleteAssessmentAttemptsByInstance(int instanceId, LoginDTO loginDTO)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.DELETE_ASSESSMENT_ATTEMPTS_BY_INSTANCE, new { InstanceId = instanceId });
        }

        public async Task DeleteEvaluationResponsesByInstance(int instanceId, LoginDTO loginDTO)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.DELETE_EVALUATION_RESPONSES_BY_INSTANCE, new { InstanceId = instanceId });
        }

        public async Task DeleteEvaluationInstancesByInstance(int instanceId, LoginDTO loginDTO)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.DELETE_EVALUATION_INSTANCES_BY_INSTANCE, new { InstanceId = instanceId });
        }

        public async Task DeleteAttendanceByInstance(int instanceId, LoginDTO loginDTO)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.DELETE_ATTENDANCE_BY_INSTANCE, new { InstanceId = instanceId });
        }

        public async Task DeleteSkillUpgradesByInstance(int instanceId, LoginDTO loginDTO)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.DELETE_SKILL_UPGRADES_BY_INSTANCE, new { InstanceId = instanceId });
        }

        public async Task DeleteEnrollmentsByInstance(int instanceId, LoginDTO loginDTO)
        {
            var parameters = new { InstanceId = instanceId };
            await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.DELETE_ENROLLMENTS_BY_INSTANCE, parameters);
        }

        public async Task DeleteCompletionsByInstance(int instanceId, LoginDTO loginDTO)
        {
            var parameters = new { InstanceId = instanceId };
            await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.DELETE_COMPLETIONS_BY_INSTANCE, parameters);
        }

        public async Task DeleteEvaluationInsightsByInstance(int instanceId, LoginDTO loginDTO)
        {
            var parameters = new { InstanceId = instanceId };
            await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.DELETE_EVALUATION_INSIGHTS_BY_INSTANCE, parameters);
        }

        public async Task DeleteLmsBridgeByInstance(int instanceId, LoginDTO loginDTO)
        {
            var parameters = new { InstanceId = instanceId };
            await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.DELETE_LMS_BRIDGE_BY_INSTANCE, parameters);
        }

        public async Task UpdateInstanceStatus(int instanceId, byte status, int modifiedById, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new
                {
                    InstanceId = instanceId,
                    InstanceStatus = status,
                    ModifiedById = modifiedById,
                    ModifiedOn = DateTime.UtcNow,
                    TenantId = loginDTO.ClientId
                };
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.UPDATE_INSTANCE_STATUS, parameters);
            }
            catch (Exception) { throw; }
        }

        public async Task IncrementEnrolledCount(int instanceId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { InstanceId = instanceId, TenantId = loginDTO.ClientId };
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.INCREMENT_ENROLLED_COUNT, parameters);
            }
            catch (Exception) { throw; }
        }

        public async Task DecrementEnrolledCount(int instanceId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { InstanceId = instanceId, TenantId = loginDTO.ClientId };
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingInstanceQB.DECREMENT_ENROLLED_COUNT, parameters);
            }
            catch (Exception) { throw; }
        }
    }
}