using System.Data.Common;
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.TrainingProgramme;
using TMSDAL.Query.ExternalVendor;
using TMSDAL.Query.TrainingProgramme;

namespace TMSDAL.CustomCode.TrainingProgramme
{
    public class TrainingProgrammeDAL : ITrainingProgrammeDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        public async Task<TrainingProgrammeDTO?> GetTrainingProgramme(int programmeId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { ProgrammeId = programmeId, TenantId = loginDTO.ClientId };
                return await _QueryExecutor.QuerySingleAsync<TrainingProgrammeDTO>(loginDTO, TrainingProgrammeQB.GET_PROGRAMME, parameters);
            }
            catch (Exception) { throw; }
        }

        public async Task<List<ProgrammeSessionDTO>> GetProgrammeSessions(int programmeId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { ProgrammeId = programmeId, TenantId = loginDTO.ClientId };
                var result = await _QueryExecutor.QueryAsync<ProgrammeSessionDTO>(loginDTO, TrainingProgrammeQB.GET_PROGRAMME_SESSIONS, parameters);
                return result.ToList();
            }
            catch (Exception) { throw; }
        }

        public async Task<List<SessionTopicDTO>> GetSessionTopics(int programmeSessionId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { ProgrammeSessionId = programmeSessionId, TenantId = loginDTO.ClientId };
                var result = await _QueryExecutor.QueryAsync<SessionTopicDTO>(loginDTO, TrainingProgrammeQB.GET_SESSION_TOPICS, parameters);
                return result.ToList();
            }
            catch (Exception) { throw; }
        }

        public async Task<List<ProgrammePrerequisiteDTO>> GetProgrammePrerequisites(int programmeId, LoginDTO loginDTO)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<ProgrammePrerequisiteDTO>(loginDTO, TrainingProgrammeQB.GET_PROGRAMME_PREREQUISITES, new { ProgrammeId = programmeId });
                return result.ToList();
            }
            catch (Exception) { throw; }
        }

        public async Task<List<ProgrammeSkillMapDTO>> GetProgrammeSkillMaps(int programmeId, LoginDTO loginDTO)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<ProgrammeSkillMapDTO>(loginDTO, TrainingProgrammeQB.GET_PROGRAMME_SKILLMAPS, new { ProgrammeId = programmeId });
                return result.ToList();
            }
            catch (Exception) { throw; }
        }

        public async Task<List<ProgrammeSkillMapDTO>> GetProgrammeSkillMapsForTrigger(int programmeId, int assessmentId, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<ProgrammeSkillMapDTO>(
                    loginDTO, TrainingProgrammeQB.GET_PROGRAMME_SKILLMAPS_FOR_TRIGGER,
                    new { ProgrammeId = programmeId, AssessmentId = assessmentId, TenantId = loginDTO.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);
                return result.ToList();
            }
            catch (Exception) { throw; }
        }

        public async Task<List<ProgrammeCompetencyMapDTO>> GetProgrammeCompetencyMaps(int programmeId, LoginDTO loginDTO)
        {
            try
            {
                var result = await _QueryExecutor.QueryAsync<ProgrammeCompetencyMapDTO>(loginDTO, TrainingProgrammeQB.GET_PROGRAMME_COMPETENCYMAPS, new { ProgrammeId = programmeId });
                return result.ToList();
            }
            catch (Exception) { throw; }
        }

        public async Task SaveProgrammePrerequisite(ProgrammePrerequisiteDTO dto, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.SAVE_PROGRAMME_PREREQUISITE, dto, tx);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task DeleteProgrammePrerequisites(int programmeId, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.DELETE_PROGRAMME_PREREQUISITES, new { ProgrammeId = programmeId }, tx);
            }
            catch (Exception) { throw; }
        }

        public async Task SaveProgrammeSkillMap(ProgrammeSkillMapDTO dto, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.SAVE_PROGRAMME_SKILLMAP, dto, tx);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task DeleteProgrammeSkillMaps(int programmeId, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.DELETE_PROGRAMME_SKILLMAPS, new { ProgrammeId = programmeId }, tx);
            }
            catch (Exception) { throw; }
        }

        public async Task SaveProgrammeCompetencyMap(ProgrammeCompetencyMapDTO dto, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.SAVE_PROGRAMME_COMPETENCYMAP, dto, tx);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task DeleteProgrammeCompetencyMaps(int programmeId, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.DELETE_PROGRAMME_COMPETENCYMAPS, new { ProgrammeId = programmeId }, tx);
            }
            catch (Exception) { throw; }
        }

        public async Task<string> GetProgrammeCatalogue(
            byte? isActive,
            byte? programmeType,
            int? domainId,
            int page,
            int size,
            LoginDTO loginDTO)
        {
            try
            {
                var parameters = new
                {
                    TenantId = loginDTO.ClientId,
                    IsActive = isActive,
                    ProgrammeType = programmeType,
                    DomainId = domainId,
                    Offset = (page - 1) * size,   // zero-based offset for SQL OFFSET
                    PageSize = size
                };

                // QueryPagedAsync expects a single SQL that includes both the data rows
                // and a TotalCount column. The QB query must use OFFSET/FETCH and
                // COUNT(*) OVER() pattern so the interface can split them internally.
                var result = await _QueryExecutor.QueryPagedAsync<TrainingProgrammePicklistDTO>(
                    loginDTO,
                    TrainingProgrammeQB.GET_CATALOGUE_PAGED,
                    parameters);

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

        public async Task<int> SaveTrainingProgramme(TrainingProgrammeDTO dto, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.SAVE_PROGRAMME, dto, tx);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task<int> UpdateTrainingProgramme(TrainingProgrammeDTO dto, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.UPDATE_PROGRAMME, dto, tx);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.UpdateErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task<int> DeleteTrainingProgramme(int programmeId, LoginDTO loginDTO)
        {
            try
            {
                var parameters = new { ProgrammeId = programmeId, TenantId = loginDTO.ClientId };
                return await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.DELETE_PROGRAMME, parameters);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.DeleteErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task SaveProgrammeSession(ProgrammeSessionDTO dto, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.SAVE_PROGRAMME_SESSION, dto, tx);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task DeleteProgrammeSessions(int programmeId, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                var parameters = new { ProgrammeId = programmeId, TenantId = loginDTO.ClientId };
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.DELETE_PROGRAMME_SESSIONS, parameters, tx);
            }
            catch (Exception) { throw; }
        }

        public async Task SaveSessionTopic(SessionTopicDTO dto, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.SAVE_SESSION_TOPIC, dto, tx);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.SaveErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task DeleteSessionTopics(int programmeId, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                var parameters = new { ProgrammeId = programmeId };
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.DELETE_SESSION_TOPICS, parameters, tx);
            }
            catch (Exception) { throw; }
        }

        public async Task DeletePaperSectionsByProgramme(int programmeId, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.DELETE_PAPER_SECTIONS_BY_PROGRAMME, new { ProgrammeId = programmeId }, tx);
            }
            catch (Exception) { throw; }
        }

        public async Task DeleteAssessmentPapersByProgramme(int programmeId, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.DELETE_ASSESSMENT_PAPERS_BY_PROGRAMME, new { ProgrammeId = programmeId }, tx);
            }
            catch (Exception) { throw; }
        }

        public async Task DeleteAssessmentQuestionsByProgramme(int programmeId, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.DELETE_ASSESSMENT_QUESTIONS_BY_PROGRAMME, new { ProgrammeId = programmeId }, tx);
            }
            catch (Exception) { throw; }
        }

        public async Task DeleteAttemptResponsesByProgramme(int programmeId, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.DELETE_ATTEMPT_RESPONSES_BY_PROGRAMME, new { ProgrammeId = programmeId }, tx);
            }
            catch (Exception) { throw; }
        }

        public async Task DeleteAssessmentAttemptsByProgramme(int programmeId, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.DELETE_ASSESSMENT_ATTEMPTS_BY_PROGRAMME, new { ProgrammeId = programmeId }, tx);
            }
            catch (Exception) { throw; }
        }

        public async Task DeleteSessionAssessmentsByProgramme(int programmeId, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.DELETE_SESSION_ASSESSMENTS_BY_PROGRAMME, new { ProgrammeId = programmeId }, tx);
            }
            catch (Exception) { throw; }
        }

        public async Task DeleteAttendanceByProgramme(int programmeId, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.DELETE_ATTENDANCE_BY_PROGRAMME, new { ProgrammeId = programmeId }, tx);
            }
            catch (Exception) { throw; }
        }

        public async Task DeleteInstanceSessionsByProgramme(int programmeId, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.DELETE_INSTANCE_SESSIONS_BY_PROGRAMME, new { ProgrammeId = programmeId }, tx);
            }
            catch (Exception) { throw; }
        }

        public async Task DeleteLmsBridgeByProgramme(int programmeId, LoginDTO loginDTO, DbTransaction? tx = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, TrainingProgrammeQB.DELETE_LMS_BRIDGE_BY_PROGRAMME, new { ProgrammeId = programmeId }, tx);
            }
            catch (Exception) { throw; }
        }

        public async Task<string> GetSelectListTrainingProgramme(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
        {
            var result = await _QueryExecutor.QueryWithCriteriaAsync<TrainingProgrammePicklistDTO>(
                login, TrainingProgrammeQB.GET_SELECTLIST_PROGRAMME,
                new { TenantId = login.ClientId }, criteriaDTO, ct);
            return JsonConvert.SerializeObject(result);
        }

        public async Task<string> GetTrainingProgrammeList(LoginDTO loginDTO)
        {
            try
            {
                var baseParameters = new { TenantId = loginDTO.ClientId };

                var programmes = (await _QueryExecutor.QueryAsync<TrainingProgrammeDTO>(
                    loginDTO,
                    TrainingProgrammeQB.GET_TRAINING_PROGRAMME_LIST,
                    baseParameters)).ToList();

                if (!programmes.Any())
                    return JsonConvert.SerializeObject(programmes);

                var programmeIds = programmes.Select(p => p.ProgrammeId).ToList();

                var childParameters = new
                {
                    TenantId = loginDTO.ClientId,
                    ProgrammeIds = programmeIds
                };

                // Fetch all child data in parallel
                var sessionsTask = _QueryExecutor.QueryAsync<ProgrammeSessionDTO>(loginDTO, TrainingProgrammeQB.GET_PROGRAMME_SESSIONS_LIST, childParameters);
                var prerequisitesTask = _QueryExecutor.QueryAsync<ProgrammePrerequisiteDTO>(loginDTO, TrainingProgrammeQB.GET_PROGRAMME_PREREQUISITES_LIST, childParameters);
                var skillMapsTask = _QueryExecutor.QueryAsync<ProgrammeSkillMapDTO>(loginDTO, TrainingProgrammeQB.GET_PROGRAMME_SKILLMAPS_LIST, childParameters);
                var competencyMapsTask = _QueryExecutor.QueryAsync<ProgrammeCompetencyMapDTO>(loginDTO, TrainingProgrammeQB.GET_PROGRAMME_COMPETENCYMAPS_LIST, childParameters);

                await Task.WhenAll(sessionsTask, prerequisitesTask, skillMapsTask, competencyMapsTask);

                var sessions = (await sessionsTask).ToList();

                // Now fetch topics using all SessionIds from the sessions result
                List<SessionTopicDTO> topics = new();
                if (sessions.Any())
                {
                    var sessionIds = sessions.Select(s => s.ProgrammeSessionId).ToList();
                    var topicParameters = new { ProgrammeSessionIds = sessionIds };
                    topics = (await _QueryExecutor.QueryAsync<SessionTopicDTO>(
                        loginDTO,
                        TrainingProgrammeQB.GET_SESSION_TOPICS_LIST,
                        topicParameters)).ToList();
                }

                // Map topics into sessions
                var topicsMap = topics.GroupBy(t => t.ProgrammeSessionId)
                                      .ToDictionary(g => g.Key, g => g.ToList());

                foreach (var session in sessions)
                    session.Topics = topicsMap.GetValueOrDefault(session.ProgrammeSessionId);

                // Map sessions and other children into programmes
                var sessionsMap = sessions.GroupBy(x => x.ProgrammeId).ToDictionary(g => g.Key, g => g.ToList());
                var prerequisitesMap = (await prerequisitesTask).GroupBy(x => x.ProgrammeId).ToDictionary(g => g.Key, g => g.ToList());
                var skillMapsMap = (await skillMapsTask).GroupBy(x => x.ProgrammeId).ToDictionary(g => g.Key, g => g.ToList());
                var competencyMapsMap = (await competencyMapsTask).GroupBy(x => x.ProgrammeId).ToDictionary(g => g.Key, g => g.ToList());

                foreach (var programme in programmes)
                {
                    programme.Sessions = sessionsMap.GetValueOrDefault(programme.ProgrammeId);
                    programme.ProgrammePrerequisites = prerequisitesMap.GetValueOrDefault(programme.ProgrammeId);
                    programme.SkillMaps = skillMapsMap.GetValueOrDefault(programme.ProgrammeId);
                    programme.CompetencyMaps = competencyMapsMap.GetValueOrDefault(programme.ProgrammeId);
                }

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

    }
}