using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.Resource.Response;
using SkillManagementBLL.Common;
using SkillManagementDAL.CustomCode.EmployeeSkillProfile;
using SkillManagementDAL.DTO.EmployeeSkillProfile;

namespace SkillManagementBLL.EmployeeSkillProfile
{
    public class EmployeeSkillProfileBLL : IEmployeeSkillProfileBLL
    {
        private readonly IEmployeeSkillProfileDAL _dal;
        private readonly AutoNumber               _autoNumber;
        private readonly ISkillHubNotifier        _hubNotifier;

        public EmployeeSkillProfileBLL(IEmployeeSkillProfileDAL dal, AutoNumber autoNumber, ISkillHubNotifier hubNotifier)
        {
            _dal         = dal;
            _autoNumber  = autoNumber;
            _hubNotifier = hubNotifier;
        }

        public async Task<string> GetByEmployee(int employeeId,int profileid,int skillid, LoginDTO login, CancellationToken ct)
            => await _dal.GetByEmployee(employeeId, profileid, skillid, login, ct);

        public async Task<string> GetByEmployeeId(int employeeId, LoginDTO login, CancellationToken ct)
            => await _dal.GetByEmployeeId(employeeId, login, ct);

        public async Task<string> GetCellDetail(int employeeId, int skillId, LoginDTO login, CancellationToken ct)
            => await _dal.GetCellDetail(employeeId, skillId, login, ct);

        public async Task<string> Save(EmployeeSkillProfileDTO dto, LoginDTO login, CancellationToken ct)
        {
            // ============================================================
            // VALIDATION
            // ============================================================
            if (dto.EmployeeId == 0)
                throw new ArgumentException("EmployeeId is required.");

            if (dto.SkillId == 0)
                throw new ArgumentException("SkillId is required.");

            if (dto.CurrentLevNo > 4)
                throw new ArgumentException("CurrentLevNo must be between 0 and 4.");

            if (dto.ProfileStatus > 3)
                throw new ArgumentException("ProfileStatus must be 0-3.");

            var now = DateTime.UtcNow;

            dto.TenantId = login.ClientId;
            dto.ModifiedById = login.UserId;
            dto.ModifiedOn = now;
            if (string.IsNullOrWhiteSpace(dto.ProfileName))
                dto.ProfileName = $"EMP-{dto.EmployeeId}-SKL-{dto.SkillId}";

            bool isNew = dto.ProfileId == 0;
            GB5Shared.DTO.Framework.AutoNumber.AutoNumberDTO? an = null;

            try
            {
                // ============================================================
                // INSERT
                // ============================================================
                if (isNew)
                {
                    an = await _autoNumber.GetNumberAsync(1, "MEMPLOYEESKILLPROFILE", login);

                    if (an == null || an.StartNumber == 0)
                        throw new Exception("Auto number generation failed for MEMPLOYEESKILLPROFILE.");

                    dto.ProfileId = an.StartNumber;

                    dto.CreatedById = login.UserId;
                    dto.CreatedOn = now;

                    var rows = await _dal.Insert(dto, login, ct);

                    if (rows == 0)
                        throw new Exception("Insert failed for Employee Skill Profile.");

                    await _hubNotifier.NotifyProfileChangedAsync(dto.EmployeeId, dto.SkillId, login.ClientId, ct).ConfigureAwait(false);
                    return $"{SuccessResponse.SaveSuccessMessage} {dto.ProfileId}";
                }

                // ============================================================
                // UPDATE
                // ============================================================
                else
                {
                    var rows = await _dal.Upsert(dto, login, ct);

                    if (rows == 0)
                        throw new Exception("Update failed for Employee Skill Profile.");

                    await _hubNotifier.NotifyProfileChangedAsync(dto.EmployeeId, dto.SkillId, login.ClientId, ct).ConfigureAwait(false);
                    return $"{SuccessResponse.UpdateSuccessMessage} {dto.ProfileId}";
                }
            }
            catch
            {
                // ============================================================
                // AUTO NUMBER ROLLBACK (ONLY FOR INSERT FAILURE)
                // ============================================================
                if (an != null && isNew)
                    await _autoNumber.RollbackAutoNumber("MEMPLOYEESKILLPROFILE", an.StartNumber, login);

                throw;
            }
        }

        public async Task<string> BulkSave(BulkSaveEmployeeSkillProfileRequestDTO request, LoginDTO login, CancellationToken ct)
        {
            if (request.EmployeeId == 0)
                throw new ArgumentException("EmployeeId is required.");

            var items = request.SkillLevels;
            if (items == null || items.Count == 0)
                return "No skill levels to save.";

            // Count distinct natural keys for ProfileId==0 items — that is the
            // number of actual INSERTs needed.  Duplicates within the batch reuse
            // BULK_UPSERT (MERGE) and do not need a new auto-number.
            var newKeyCount = items
                .Where(x => x.ProfileId == 0)
                .Select(x => $"{x.SkillId}_{(x.ProductFamilyId != 0 ? x.ProductFamilyId : -1)}_{(x.ModelId != 0 ? x.ModelId : -1)}")
                .Distinct()
                .Count();

            GB5Shared.DTO.Framework.AutoNumber.AutoNumberDTO? an = null;
            int nextId = 0;

            if (newKeyCount > 0)
            {
                an = await _autoNumber.GetNumberAsync(newKeyCount, "MEMPLOYEESKILLPROFILE", login);
                nextId = an.StartNumber;
            }

            var now = DateTime.UtcNow;

            // Track natural keys already processed in this batch.
            // Same (SkillId, ProductFamilyId, ModelId) appearing more than once
            // must BulkUpsert on subsequent rows — inserting would violate the UK.
            var seenKeys = new HashSet<string>();

            try
            {
                foreach (var item in items)
                {
                    if (item.ProfileStatus > 3)
                        throw new ArgumentException($"ProfileStatus must be 0-3 for SkillId={item.SkillId}.");

                    // Resolve SLNO with cascading fallback.
                    byte currentLevNo = 0;
                    if (item.SlevelId != 0)
                        currentLevNo = await _dal.GetSlNoBySlevelId(item.SlevelId, item.SkillId, login);
                    if (currentLevNo == 0 && item.CurrentLevNo != 0)
                        currentLevNo = item.CurrentLevNo;
                    if (currentLevNo == 0 && !string.IsNullOrWhiteSpace(item.LevelLabel))
                        currentLevNo = await _dal.GetSlNoByLevelLabel(item.SkillId, item.LevelLabel.Trim(), login);

                    int pfId = item.ProductFamilyId != 0 ? item.ProductFamilyId : -1;
                    int mdId = item.ModelId         != 0 ? item.ModelId         : -1;
                    string naturalKey       = $"{item.SkillId}_{pfId}_{mdId}";
                    bool   alreadyInBatch   = !seenKeys.Add(naturalKey);

                    // isNew only when ProfileId==0 AND this natural key hasn't been
                    // seen yet in this batch — duplicates always BulkUpsert.
                    bool isNew = item.ProfileId == 0 && !alreadyInBatch;

                    var dto = new EmployeeSkillProfileDTO
                    {
                        ProfileId        = isNew ? nextId++ : item.ProfileId,
                        ProfileName      = item.ProfileName ?? $"EMP-{request.EmployeeId}-SKL-{item.SkillId}",
                        EmployeeId       = request.EmployeeId,
                        SkillId          = item.SkillId,
                        CurrentLevNo     = currentLevNo,
                        ProfileStatus    = item.ProfileStatus,
                        AssessedDt       = item.AssessedDt,
                        NextReviewDt     = item.NextReviewDt,
                        TrainingPlanDate = item.TrainingPlanDate,
                        ProductFamilyId  = pfId,
                        ModelId          = mdId,
                        AssessedById     = -1,
                        TenantId         = login.ClientId,
                        CreatedById      = login.UserId,
                        CreatedOn        = now,
                        ModifiedById     = login.UserId,
                        ModifiedOn       = now,
                        Status           = 1,
                        SourceType       = 5,
                        SortOrder        = 9999
                    };

                    if (isNew)
                        await _dal.Insert(dto, login, ct);
                    else
                        await _dal.BulkUpsert(dto, login, ct);

                    await _hubNotifier.NotifyProfileChangedAsync(dto.EmployeeId, dto.SkillId, login.ClientId, ct).ConfigureAwait(false);
                }

                var employeeName = await _dal.GetEmployeeName(request.EmployeeId, login);
                return string.Format(SuccessResponse.SaveSuccessForEmployee, employeeName);
            }
            catch
            {
                if (an != null)
                    await _autoNumber.RollbackAutoNumber("MEMPLOYEESKILLPROFILE", an.StartNumber, login);
                throw;
            }
        }
        public async Task<string> GetSelectList(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
    => await _dal.GetSelectList(criteriaDTO, login, ct);

        private void SetAuditFields(EmployeeSkillProfileDTO dto, LoginDTO login)
        {
            dto.TenantId     = login.ClientId;
            dto.ModifiedById = login.UserId;
            dto.CreatedById  = login.UserId;
        }

        public async Task<string> GetEmployeeSkillProfileList(LoginDTO login, CancellationToken ct)
    => await _dal.GetEmployeeSkillProfileList(login, ct);
    }
}