using System.Data.Common;
using System.Linq;
using GB5Shared.DaprCache;
using GB5Shared.DTO.Framework.AutoNumber;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.EventLogPublish;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Telemetry;
using GB5Shared.Validation;
using Newtonsoft.Json;
using System.ComponentModel.DataAnnotations;
using TMSDAL.CustomCode.TrainingProgramme;
using TMSDAL.CustomCode.TrainingProgrammeRevision;
using TMSDAL.DTO.TrainingProgramme;
using TMSDAL.DTO.TrainingProgrammeRevision;
using TMSDAL.Enums;
using static GB5Shared.GB5Constant.Constant;

namespace TMSBLL.TrainingProgramme
{
    public class TrainingProgrammeBLL : ITrainingProgrammeBLL
    {
        private readonly ITrainingProgrammeDAL _TrainingProgrammeDAL;
        private readonly ITrainingProgrammeRevisionDAL _TrainingProgrammeRevisionDAL;
        private readonly AutoNumber _AutoNumber;
        private readonly IValidation _Validation;
        private readonly KeyInvalidate _KeyInvalidate;
        private readonly IQueryExecutor _QueryExecutor;
        private readonly EventLogPublish _EventLog;

        public TrainingProgrammeBLL(ITrainingProgrammeDAL trainingProgrammeDAL, ITrainingProgrammeRevisionDAL trainingProgrammeRevisionDAL,
            AutoNumber autoNumber, IValidation validation, KeyInvalidate keyInvalidate, IQueryExecutor queryExecutor, EventLogPublish eventLog)
        {
            _TrainingProgrammeDAL = trainingProgrammeDAL;
            _TrainingProgrammeRevisionDAL = trainingProgrammeRevisionDAL;
            _AutoNumber = autoNumber;
            _Validation = validation;
            _KeyInvalidate = keyInvalidate;
            _QueryExecutor = queryExecutor;
            _EventLog = eventLog;
        }

        public async Task<string> GetTrainingProgramme(int programmeId, LoginDTO loginDTO)
        {
            try
            {
                var programme = await _TrainingProgrammeDAL.GetTrainingProgramme(programmeId, loginDTO);
                if (programme == null) return JsonConvert.SerializeObject(null);

                programme.Sessions = await _TrainingProgrammeDAL.GetProgrammeSessions(programmeId, loginDTO);
                if (programme.Sessions != null)
                {
                    foreach (var session in programme.Sessions)
                    {
                        session.Topics = await _TrainingProgrammeDAL.GetSessionTopics(session.ProgrammeSessionId, loginDTO);
                    }
                }

                programme.ProgrammePrerequisites = await _TrainingProgrammeDAL.GetProgrammePrerequisites(programmeId, loginDTO);
                programme.SkillMaps              = await _TrainingProgrammeDAL.GetProgrammeSkillMaps(programmeId, loginDTO);
                programme.CompetencyMaps         = await _TrainingProgrammeDAL.GetProgrammeCompetencyMaps(programmeId, loginDTO);

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

        public async Task<string> GetProgrammeCatalogue(byte? isActive, byte? programmeType, int? domainId, int page, int size, LoginDTO loginDTO)
        {
            try { return await _TrainingProgrammeDAL.GetProgrammeCatalogue(isActive, programmeType, domainId, page, size, loginDTO); }
            catch (Exception) { throw; }
        }

        public async Task<string> SaveTrainingProgramme(TrainingProgrammeDTO dto, LoginDTO loginDTO)
        {
            if (dto == null) throw new ArgumentNullException(nameof(dto));
            AutoNumberDTO? autoNumberDTO = null;

            try
            {
                await _Validation.NotEmpty(dto.ProgrammeCode, nameof(dto.ProgrammeCode));
                await _Validation.NotEmpty(dto.ProgrammeTitle, nameof(dto.ProgrammeTitle));
                if (dto.DurationHours <= 0)
                    throw new ValidationException("DurationHours must be greater than 0.");
                if (dto.MinCapacity > dto.MaxCapacity)
                    throw new ValidationException("MinParticipants cannot exceed MaxParticipants.");
                if (!Enum.IsDefined(typeof(ComplexityLevel), dto.ComplexityLevel))
                    throw new ValidationException($"Invalid ComplexityLevel value: {dto.ComplexityLevel}.");
                if (dto.ComplexityLevel < (byte)ComplexityLevel.SessionLevel && dto.Sessions != null && dto.Sessions.Count > 0)
                    throw new ValidationException("Sessions were supplied but ComplexityLevel does not allow session-level data. Set ComplexityLevel to SessionLevel or higher.");
                if (dto.ComplexityLevel < (byte)ComplexityLevel.TopicLevel && dto.Sessions != null && dto.Sessions.Any(s => s.Topics != null && s.Topics.Count > 0))
                    throw new ValidationException("Topics were supplied but ComplexityLevel does not allow topic-level data. Set ComplexityLevel to TopicLevel.");
                if (dto.ProgrammePrerequisites != null && dto.ProgrammePrerequisites.Any(p => p.PrereqProgrammeId == 0 || p.PrereqProgrammeId == -1))
                    throw new ValidationException("Each ProgrammePrerequisite must reference a valid, existing PrereqProgrammeId — MTRAININGPROGRAMME has no -1/NONE sentinel row, so 0 or -1 violate the foreign key. Omit the prerequisite row entirely for a purely skill-based prerequisite (PrereqSkillId only), or supply a real ProgrammeId.");

                dto.TenantId = loginDTO.ClientId;
                dto.ModifiedById = loginDTO.UserId;
                dto.ModifiedOn = DateTime.UtcNow;

                // AutoNumber is allocated outside the transaction — counter always advances in auto-commit
                bool isNew = dto.ProgrammeId == 0;
                if (isNew)
                {
                    autoNumberDTO = await _AutoNumber.GetAutoNumber(1, "TRAININGPROGRAMME", loginDTO);
                    dto.ProgrammeId = autoNumberDTO.StartNumber;
                    dto.CreatedById = loginDTO.UserId;
                    dto.CreatedOn = DateTime.UtcNow;
                    dto.Version = 1;
                }
                else
                {
                    dto.Version += 1;
                }

                DbTransaction tx = await _QueryExecutor.BeginTransactionAsync(loginDTO);
                try
                {
                    string message;
                    if (isNew)
                    {
                        await _TrainingProgrammeDAL.SaveTrainingProgramme(dto, loginDTO, tx);
                        await SaveChildRecords(dto, loginDTO, tx);
                        message = string.Format(SuccessResponse.SaveSuccessWithCodeAndName, dto.ProgrammeCode, dto.ProgrammeTitle);
                    }
                    else
                    {
                        // Snapshot the full aggregate (fields + children) as it exists in the DB
                        // right now — BEFORE UpdateTrainingProgramme + the destructive delete cascade
                        // below overwrite/remove it. GetTrainingProgramme is a plain read with no
                        // transaction dependency, so it's safe to call here: nothing in this method
                        // has written anything to the DB yet at this point.
                        GB5Trace.Step("snapshot-programme-revision", new { dto.ProgrammeId, revisionNo = dto.Version - 1 });
                        string priorSnapshotJson = await GetTrainingProgramme(dto.ProgrammeId, loginDTO);

                        await _TrainingProgrammeRevisionDAL.SaveRevision(new TrainingProgrammeRevisionDTO
                        {
                            ProgrammeId           = dto.ProgrammeId,
                            RevisionNo            = (short)(dto.Version - 1),
                            EffectiveFrom         = DateTime.UtcNow,
                            ProgrammeSnapshotJson = priorSnapshotJson,
                            ChangeSummary         = dto.RevisionRemarks,
                            RevisedById           = loginDTO.UserId,
                            RevisedOn             = DateTime.UtcNow,
                            TenantId              = loginDTO.ClientId
                        }, loginDTO, tx, default);

                        await _TrainingProgrammeDAL.UpdateTrainingProgramme(dto, loginDTO, tx);

                        // Delete in FK-safe order: deepest children first before parents.
                        // Always delete unconditionally — DB may have data from a prior higher-ComplexityLevel save.
                        await _TrainingProgrammeDAL.DeleteProgrammeCompetencyMaps(dto.ProgrammeId, loginDTO, tx);
                        await _TrainingProgrammeDAL.DeleteProgrammeSkillMaps(dto.ProgrammeId, loginDTO, tx);
                        await _TrainingProgrammeDAL.DeleteProgrammePrerequisites(dto.ProgrammeId, loginDTO, tx);
                        await _TrainingProgrammeDAL.DeleteSessionTopics(dto.ProgrammeId, loginDTO, tx);
                        // MSESSIONASSESSMENT children (deepest first)
                        await _TrainingProgrammeDAL.DeletePaperSectionsByProgramme(dto.ProgrammeId, loginDTO, tx);
                        await _TrainingProgrammeDAL.DeleteAssessmentPapersByProgramme(dto.ProgrammeId, loginDTO, tx);
                        await _TrainingProgrammeDAL.DeleteAssessmentQuestionsByProgramme(dto.ProgrammeId, loginDTO, tx);
                        await _TrainingProgrammeDAL.DeleteAttemptResponsesByProgramme(dto.ProgrammeId, loginDTO, tx);
                        await _TrainingProgrammeDAL.DeleteAssessmentAttemptsByProgramme(dto.ProgrammeId, loginDTO, tx);
                        await _TrainingProgrammeDAL.DeleteSessionAssessmentsByProgramme(dto.ProgrammeId, loginDTO, tx);
                        // TINSTANCESESSION children (deepest first)
                        await _TrainingProgrammeDAL.DeleteAttendanceByProgramme(dto.ProgrammeId, loginDTO, tx);
                        await _TrainingProgrammeDAL.DeleteInstanceSessionsByProgramme(dto.ProgrammeId, loginDTO, tx);
                        await _TrainingProgrammeDAL.DeleteLmsBridgeByProgramme(dto.ProgrammeId, loginDTO, tx);
                        await _TrainingProgrammeDAL.DeleteProgrammeSessions(dto.ProgrammeId, loginDTO, tx);

                        await SaveChildRecords(dto, loginDTO, tx);
                        message = string.Format(SuccessResponse.UpdateSuccessWithCodeAndName, dto.ProgrammeCode, dto.ProgrammeTitle);
                    }

                    await _QueryExecutor.CommitAsync(tx);

                    if (!isNew)
                    {
                        GB5Trace.Step("event-publish", new { EventTypeId = EventTypeConstant.TMSPROGRAMMEREVISIONSAVEDEVENTTYPEID });
                        await _EventLog.PublishEventLogAsync(
                            "Training Programme Revision Saved",
                            new { dto.ProgrammeId, RevisionNo = dto.Version - 1 },
                            EventTypeConstant.TMSPROGRAMMEREVISIONSAVEDEVENTTYPEID,
                            dto.ProgrammeId,
                            loginDTO).ConfigureAwait(false);
                    }

                    var cacheKey = new CacheKeyGeneration().KeyGeneration(dto.ProgrammeId, EntityConstant.OBJECTTRAININGPROGRAMME, CacheKeyLevel.CLIENT_LEVEL, loginDTO);
                    await _KeyInvalidate.AllInvalidateCache(cacheKey);
                    return message;
                }
                catch
                {
                    await _QueryExecutor.RollbackAsync(tx);
                    throw;
                }
            }
            catch (ValidationException vex) { throw new Exception(vex.Message); }
            catch (Exception)
            {
                if (autoNumberDTO != null && dto.ProgrammeId > 0)
                    await _AutoNumber.RollbackAutoNumber("TRAININGPROGRAMME", dto.ProgrammeId, loginDTO);
                throw;
            }
        }

        public async Task<string> DeleteTrainingProgramme(int programmeId, LoginDTO loginDTO)
        {
            try
            {
                int result = await _TrainingProgrammeDAL.DeleteTrainingProgramme(programmeId, loginDTO);
                if (result > 0)
                {
                    var cacheKey = new CacheKeyGeneration().KeyGeneration(programmeId, EntityConstant.OBJECTTRAININGPROGRAMME, CacheKeyLevel.CLIENT_LEVEL, loginDTO);
                    await _KeyInvalidate.AllInvalidateCache(cacheKey);
                    return SuccessResponse.DeleteSuccessMessage;
                }
                return ErrorResponse.DeleteNotFoundMessage;
            }
            catch (Exception) { throw; }
        }

        public async Task<string> GetTrainingProgrammeList(LoginDTO loginDTO)
        {
            try { return await _TrainingProgrammeDAL.GetTrainingProgrammeList(loginDTO); }
            catch (Exception) { throw; }
        }

        public async Task<string> GetSelectListTrainingProgramme(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
            => await _TrainingProgrammeDAL.GetSelectListTrainingProgramme(criteriaDTO, login, ct);

        private async Task SaveChildRecords(TrainingProgrammeDTO dto, LoginDTO loginDTO, DbTransaction tx)
        {
            // ── 1. Sessions + Topics ──────────────────────────────────────────────────
            // Track final session IDs so SkillMaps/CompetencyMaps can validate references
            var validSessionIds = new HashSet<int>();
            if (dto.ComplexityLevel >= (byte)ComplexityLevel.SessionLevel && dto.Sessions != null)
            {
                foreach (var session in dto.Sessions)
                {
                    session.ProgrammeId = dto.ProgrammeId;
                    session.TenantId = loginDTO.ClientId;

                    if (session.ProgrammeSessionId == 0)
                    {
                        var sessionAuto = await _AutoNumber.GetAutoNumber(1, "PROGRAMMESESSION", loginDTO);
                        session.ProgrammeSessionId = sessionAuto.StartNumber;
                    }

                    validSessionIds.Add(session.ProgrammeSessionId);
                    await _TrainingProgrammeDAL.SaveProgrammeSession(session, loginDTO, tx);

                    if (session.Topics != null)
                    {
                        foreach (var topic in session.Topics)
                        {
                            topic.ProgrammeId = dto.ProgrammeId;
                            topic.ProgrammeSessionId = session.ProgrammeSessionId;
                            topic.TenantId = loginDTO.ClientId;

                            if (topic.SessionTopicId == 0)
                            {
                                var topicAuto = await _AutoNumber.GetAutoNumber(1, "SESSIONTOPIC", loginDTO);
                                topic.SessionTopicId = topicAuto.StartNumber;
                            }

                            await _TrainingProgrammeDAL.SaveSessionTopic(topic, loginDTO, tx);
                        }
                    }
                }
            }

            // ── 2. Prerequisites ──────────────────────────────────────────────────────
            if (dto.ProgrammePrerequisites != null)
            {
                foreach (var prereq in dto.ProgrammePrerequisites)
                {
                    prereq.ProgrammeId = dto.ProgrammeId;

                    if (prereq.ProgrammePrereqId == 0)
                    {
                        var auto = await _AutoNumber.GetAutoNumber(1, "PROGRAMMEPREREQ", loginDTO);
                        prereq.ProgrammePrereqId = auto.StartNumber;
                    }

                    await _TrainingProgrammeDAL.SaveProgrammePrerequisite(prereq, loginDTO, tx);
                }
            }

            // ── 3. Skill Maps ─────────────────────────────────────────────────────────
            if (dto.SkillMaps != null)
            {
                foreach (var skillMap in dto.SkillMaps)
                {
                    skillMap.ProgrammeId = dto.ProgrammeId;
                    // 0 = not linked to a session; any non-zero ID not in current session list = stale → default to -1
                    if (skillMap.ProgrammeSessionId == 0 || !validSessionIds.Contains(skillMap.ProgrammeSessionId))
                        skillMap.ProgrammeSessionId = -1;

                    if (skillMap.ProgrammeSkillMapId == 0)
                    {
                        var auto = await _AutoNumber.GetAutoNumber(1, "PROGRAMMESKILLMAP", loginDTO);
                        skillMap.ProgrammeSkillMapId = auto.StartNumber;
                    }

                    await _TrainingProgrammeDAL.SaveProgrammeSkillMap(skillMap, loginDTO, tx);
                }
            }

            // ── 4. Competency Maps ────────────────────────────────────────────────────
            if (dto.CompetencyMaps != null)
            {
                foreach (var compMap in dto.CompetencyMaps)
                {
                    compMap.ProgrammeId = dto.ProgrammeId;
                    // Same stale-ID normalisation as SkillMaps
                    if (compMap.ProgrammeSessionId == 0 || !validSessionIds.Contains(compMap.ProgrammeSessionId))
                        compMap.ProgrammeSessionId = -1;

                    if (compMap.ProgCompetencyMapId == 0)
                    {
                        var auto = await _AutoNumber.GetAutoNumber(1, "PROGRAMMECOMPMAP", loginDTO);
                        compMap.ProgCompetencyMapId = auto.StartNumber;
                    }

                    await _TrainingProgrammeDAL.SaveProgrammeCompetencyMap(compMap, loginDTO, tx);
                }
            }
        }
    }
}