using AdminDAL.CustomCode.UserAccessRights;
using AdminDAL.DTO.UserAccessRights;
using GB5Shared.DTO.Framework.User;
using PayRollDAL.CustomeCode.User;
using GB5Shared.Addon;
using GB5Shared.Connection;
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.GB5Solution;
using GB5Shared.Resource.Response;
using GB5Shared.ResponseStandard;
using GB5Shared.Validation;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using Newtonsoft.Json;
using PayRollBLL.Address;
using PayRollBLL.Contact;
using PayRollBLL.CustomCode.Contact;
using PayRollDAL.CustomeCode.Address;
using PayRollDAL.CustomeCode.Contact;
using PayRollDAL.CustomeCode.Employee;
using PayRollDAL.CustomeCode.TRMEmployee;
using PayRollDAL.DTO.Employee;
using PayRollDAL.Query.Employee;
using GB5Shared.Tree;
using GB5Shared.Telemetry;
using System.Transactions;
using Unisoft.PayrollDAL.Employee;
using static GB5Shared.GB5Constant.Constant;

namespace PayRollBLL.Employee
{

    public class EmployeeBLL : IEmployeeBLL
    {
        private readonly IEmployeeDAL _employeeDAL;
        private readonly ITRMEmployeeDAL _trmEmployeeDAL;
        private readonly AutoNumber _autoNumber;
        private readonly DefaultLanguageOptions _defaultLanguageOptions;
        private readonly EventLogPublish _eventLogPublish;
        private readonly CodeGeneration _codeGeneration;
        private readonly IAddressBLL _addressBLL;
        private readonly IContactBLL _contactBLL;
        private readonly ILogger<EmployeeBLL> _logger;
        private readonly IAddonService _addonService;
        private readonly IQueryExecutor _queryExecutor;
        private readonly IApplicationConnection _applicationConnection;
        private readonly IUserDAL _userDAL;
        private readonly IUserAccessRightsDAL _userAccessRightsDAL;
        private readonly PayRollDAL.CustomeCode.DailyAttendance.IDailyAttendanceDAL? _dailyAttendanceDAL;
        private readonly PayRollDAL.CustomeCode.Job.IJobDAL? _jobDAL;

        private const string AddonTable = "MEMPLOYEEADDON";
        private const string AddonFkCol  = "EMPLOYEEID";

        public EmployeeBLL(
            IEmployeeDAL employeeDAL,
            ITRMEmployeeDAL trmEmployeeDAL,
            AutoNumber autoNumber,
            EventLogPublish eventLogPublish,
            CodeGeneration codeGeneration,
            IOptions<DefaultLanguageOptions> defaultLanguageOptions,
            IAddressBLL? addressBLL,
            IContactBLL? contactBLL,
            IQueryExecutor queryExecutor,
            ILogger<EmployeeBLL> logger,
            IAddonService addonService,
            IApplicationConnection applicationConnection,
            IValidation validation,
            Dapr.Client.DaprClient daprClient,
            IUserDAL? userDAL = null,
            IUserAccessRightsDAL? userAccessRightsDAL = null,
            PayRollDAL.CustomeCode.DailyAttendance.IDailyAttendanceDAL? dailyAttendanceDAL = null,
            PayRollDAL.CustomeCode.Job.IJobDAL? jobDAL = null)
        {
            _employeeDAL = employeeDAL;
            _trmEmployeeDAL = trmEmployeeDAL;
            _autoNumber = autoNumber;
            _eventLogPublish = eventLogPublish;
            _codeGeneration = codeGeneration;
            _defaultLanguageOptions = defaultLanguageOptions.Value;
            _addressBLL = addressBLL ?? new AddressBLL(new AddressDAL(queryExecutor), autoNumber);
            _contactBLL = contactBLL ?? new ContactBLL(new ContactDAL(queryExecutor), autoNumber);
            _queryExecutor = queryExecutor;
            _applicationConnection = applicationConnection;
            _userDAL = userDAL ?? new UserDAL(queryExecutor, daprClient);
            _userAccessRightsDAL = userAccessRightsDAL ?? new UserAccessRightsDAL(queryExecutor, validation);
            _logger = logger;
            _addonService = addonService;
            _dailyAttendanceDAL = dailyAttendanceDAL;
            _jobDAL = jobDAL;
        }

        #region Get Employee
        public async Task<string> GetEmployee(int EmployeeId, string LanguageId, LoginDTO LoginDTO)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(LanguageId))
                    LanguageId = _defaultLanguageOptions.DefaultLanguageId;

                // Fetch JSON payload (LocationAddressDTO is already populated by DAL)
                string jsonResult = string.Equals(LanguageId, _defaultLanguageOptions.DefaultLanguageId, StringComparison.OrdinalIgnoreCase)
                    ? await _employeeDAL.GetEmployee(EmployeeId, LanguageId, LoginDTO)
                    : await _trmEmployeeDAL.GetTRMEmployee(EmployeeId, LanguageId, LoginDTO);

                jsonResult = jsonResult.Trim();

                if (jsonResult.StartsWith("[") && jsonResult.EndsWith("]"))
                    jsonResult = jsonResult[1..^1].Trim();

                // Fetch addon row as JSON and inject into EmployeeAddon[0].FeAddon
                var feAddon = await _addonService.GetAddonJsonAsync(AddonTable, AddonFkCol, EmployeeId, LoginDTO);
                var dto = JsonConvert.DeserializeObject<EmployeeDTO>(jsonResult);
                if (dto != null)
                {
                    dto.EmployeeAddon = new List<EmployeeAddon>
            {
                new EmployeeAddon { AddonEmployeeId = EmployeeId, FeAddon = feAddon }
            };

                    jsonResult = JsonConvert.SerializeObject(dto, new JsonSerializerSettings
                    {
                        NullValueHandling = NullValueHandling.Include,
                        DateFormatHandling = DateFormatHandling.MicrosoftDateFormat,
                        Formatting = Formatting.None
                    });
                }

                return jsonResult;
            }
            catch (Exception ex)
            {
                throw new Exception($"{PayRollResource.EmployeeGetError} {ex.Message}", ex);
            }
        }
        #endregion

        #region Save Employee
        public async Task<string> SaveEmployee(EmployeeDTO EmployeeDTO, string languageId, LoginDTO LoginDTO)
        {
            using var scope = new TransactionScope(
                        TransactionScopeOption.Required,
                        new TransactionOptions
                        {
                            IsolationLevel = IsolationLevel.ReadCommitted
                        },
                        TransactionScopeAsyncFlowOption.Enabled
                    );
            if (string.IsNullOrWhiteSpace(languageId))
                languageId = _defaultLanguageOptions.DefaultLanguageId;

            try
            {
                InitializeEmployeeDates(EmployeeDTO, LoginDTO);
                int initialId = EmployeeDTO.EmployeeId;
                string resultMessage;

                if (LoginDTO.DatabaseType == 1)
                {
                    EmployeeDTO.EmployeeReasonForESILeaving ??= "NONE";
                    EmployeeDTO.EmployeeReasonForPFLeaving ??= "NONE";
                }

                // New Employee
                if (initialId == 0 || initialId == -1)
                {
                    if (!string.Equals(languageId, _defaultLanguageOptions.DefaultLanguageId, StringComparison.OrdinalIgnoreCase))
                        throw new Exception(PayRollResource.EmployeeSaveAlert);

                    var autoNumberDTO = await _autoNumber.GetNumberAsync(1, "EMPLOYEE", LoginDTO);
                    EmployeeDTO.EmployeeId = autoNumberDTO.StartNumber;
                    EmployeeDTO.LanguageID = languageId;

                    // MENTITYMEMBERS.MEMBERNAME for EMPLOYEE's CategoryMemberId is "CodeDefineId" —
                    // CodeGeneration.AssignCode reads that exact property via reflection to pick
                    // which MCODEDEFINE category (e.g. UNIEMP vs Employee) applies. The caller sends
                    // its choice as EmployeeCodeDefineId, so bridge it across before AssignCode runs.
                    // NOTE: -1 is this system's "unset" sentinel — real CodeDefineId values are large
                    // negative numbers (e.g. -1499999998), so this must check `!= -1`, not `> 0`.
                    if (EmployeeDTO.EmployeeCodeDefineId != -1)
                        EmployeeDTO.CodeDefineId = EmployeeDTO.EmployeeCodeDefineId;

                    EmployeeDTO = await _codeGeneration.AssignCode("EMPLOYEE", EmployeeDTO, LoginDTO);

                    EmployeeDTO.OuWiseDepartmentWiseHeadId = await GetOuWiseDepartmentWiseHeadIdAsync(
                        EmployeeDTO.WorkOUId, EmployeeDTO.DepartmentId, LoginDTO);

                    await _employeeDAL.SaveEmployee(EmployeeDTO, LoginDTO);
                    await _employeeDAL.SaveOrUpdateEmployeeESIAsync(EmployeeDTO, LoginDTO);
                    await _employeeDAL.SaveOrUpdateEmployeePFAsync(EmployeeDTO, LoginDTO);

                    await SaveRelatedEmployeeData(EmployeeDTO, LoginDTO);
                    await SaveOrUpdateEmployeeUserAccountAsync(EmployeeDTO, LoginDTO, isNew: true);
                    await PostEmployeeUserAccessRightsAsync(EmployeeDTO, LoginDTO);

                    await _eventLogPublish.PublishEventLogAsync(
                        $"Employee Created with Id: {EmployeeDTO.EmployeeId}, Code: {EmployeeDTO.EmployeeCode}",
                        EmployeeDTO,
                        EventTypeConstant.SAVEEMPLOYEEEVENTTYPEID,
                        EmployeeDTO.EmployeeId,
                        LoginDTO,
                        "eventlog-topic"
                    );

                    resultMessage = PayRollResource.EmployeeSave;
                }
                // Update Employee
                else if (string.Equals(languageId, _defaultLanguageOptions.DefaultLanguageId, StringComparison.OrdinalIgnoreCase))
                {
                    await ValidateEmployeeVersionFrom(EmployeeDTO, LoginDTO);
                    EmployeeDTO.LanguageID = languageId;

                    EmployeeDTO.OuWiseDepartmentWiseHeadId = await GetOuWiseDepartmentWiseHeadIdAsync(
                        EmployeeDTO.WorkOUId, EmployeeDTO.DepartmentId, LoginDTO);

                    // Same bridge as the new-employee branch above: UpdateEmployee's SQL writes
                    // CODEDEFINEID = @CodeDefineId, but CodeDefineId is never otherwise populated
                    // on update (AssignCode only runs for new employees), so without this the
                    // previously-saved CodeDefineId gets silently overwritten back to its -1 default.
                    if (EmployeeDTO.EmployeeCodeDefineId != -1)
                        EmployeeDTO.CodeDefineId = EmployeeDTO.EmployeeCodeDefineId;

                    await _employeeDAL.UpdateEmployee(EmployeeDTO, LoginDTO);
                    await _employeeDAL.SaveOrUpdateEmployeeESIAsync(EmployeeDTO, LoginDTO);
                    await _employeeDAL.SaveOrUpdateEmployeePFAsync(EmployeeDTO, LoginDTO);
                    if (EmployeeDTO.EmployeeShiftPatternDTOs != null && EmployeeDTO.EmployeeShiftPatternDTOs.Count > 0)
                    {
                        await UpdateEmployeeShiftPatternAsyncData(EmployeeDTO.EmployeeShiftPatternDTOs, LoginDTO);
                    }
                    // Handle resignation logic including ESI/PF closure
                    await ProcessEmployeeResignationAsync(EmployeeDTO, LoginDTO);

                    await SaveRelatedEmployeeData(EmployeeDTO, LoginDTO);
                    await SaveOrUpdateEmployeeUserAccountAsync(EmployeeDTO, LoginDTO, isNew: false);
                    await PostEmployeeUserAccessRightsAsync(EmployeeDTO, LoginDTO);

                    resultMessage = PayRollResource.EmployeeUpdate;
                }
                // Translation Save/Update
                else
                {
                    int translationCount = await _employeeDAL.CountTRMEmployee(EmployeeDTO, languageId, LoginDTO);
                    EmployeeDTO.LanguageID = languageId;

                    if (translationCount == 0)
                        await _trmEmployeeDAL.SaveTRMEmployee(EmployeeDTO, LoginDTO);
                    else
                        await _trmEmployeeDAL.UpdateTRMEmployee(EmployeeDTO, LoginDTO);

                    await SaveRelatedEmployeeData(EmployeeDTO, LoginDTO);

                    resultMessage = translationCount == 0
                        ? PayRollResource.EmployeeTranslationSave
                        : PayRollResource.EmployeeTranslationUpdate;
                }

                // Addon upsert — applies when frontend sends EmployeeAddon[0].FeAddon
                var addonEntry = EmployeeDTO.EmployeeAddon?.FirstOrDefault();
                if (addonEntry != null && !string.IsNullOrWhiteSpace(addonEntry.FeAddon))
                {
                    var addonToSave = JsonConvert.DeserializeObject<EmployeeAddon>(addonEntry.FeAddon)
                                      ?? new EmployeeAddon();
                    addonToSave.AddonEmployeeId = EmployeeDTO.EmployeeId;
                    await _employeeDAL.UpsertEmployeeAddon(addonToSave, LoginDTO);
                }

                scope.Complete();
                return resultMessage;
            }
            catch (Exception ex)
            {
                throw new Exception($"Error saving employee {EmployeeDTO?.EmployeeId}. Details: {ex.Message}", ex);
            }
        }
        #endregion
        #region Resignation Business Logic

        private async Task ProcessEmployeeResignationAsync(EmployeeDTO EmployeeDTO, LoginDTO loginDTO)
        {
            if (EmployeeDTO == null || EmployeeDTO.EmployeeId <= 0)
                return;

            if (!IsValidResignationDate(EmployeeDTO.EmployeeDateOfResignation))
            {
                _logger?.LogInformation("Resignation date invalid for Employee {EmployeeId}", EmployeeDTO.EmployeeId);
                return;
            }

            try
            {
                _logger?.LogInformation("Processing resignation for Employee {EmployeeId}", EmployeeDTO.EmployeeId);

                // Update Attendance
                await UpdateAttendanceAfterResignationAsync(EmployeeDTO.EmployeeId, EmployeeDTO.EmployeeDateOfResignation, loginDTO);

                // Update User Status
                await UpdateUserStatusAfterResignationAsync(EmployeeDTO.EmployeeId, EmployeeDTO.EmployeeDateOfResignation, loginDTO);

                // Close ESI & PF
                await _employeeDAL.CloseEmployeeESIAsync(EmployeeDTO, loginDTO);
                await _employeeDAL.CloseEmployeePFAsync(EmployeeDTO, loginDTO);

                _logger?.LogInformation("Resignation processed successfully for Employee {EmployeeId}", EmployeeDTO.EmployeeId);
            }
            catch (Exception ex)
            {
                _logger?.LogError(ex, "Error processing resignation for Employee {EmployeeId}", EmployeeDTO.EmployeeId);
                throw;
            }
        }

        private async Task UpdateAttendanceAfterResignationAsync(int employeeId, DateTime resignationDate, LoginDTO loginDTO)
        {
            if (employeeId <= 0) throw new ArgumentException("Invalid Employee ID");
            if (!IsValidResignationDate(resignationDate)) return;

            try
            {
                int updatedCount = await _employeeDAL.UpdateAttendanceAfterResignationAsync(employeeId, resignationDate, loginDTO);
                _logger?.LogInformation("Updated {Count} attendance records for Employee {EmployeeId}", updatedCount, employeeId);
            }
            catch (Exception ex)
            {
                _logger?.LogError(ex, "Error updating attendance for Employee {EmployeeId}", employeeId);
                throw;
            }
        }

        private async Task UpdateUserStatusAfterResignationAsync(int employeeId, DateTime resignationDate, LoginDTO loginDTO)
        {
            if (employeeId <= 0) throw new ArgumentException("Invalid Employee ID");
            if (!IsValidResignationDate(resignationDate)) return;

            try
            {
                bool updated = await _employeeDAL.UpdateUserStatusAfterResignationAsync(employeeId, resignationDate, loginDTO);
                if (updated)
                    _logger?.LogInformation("User status updated for Employee {EmployeeId}", employeeId);
                else
                    _logger?.LogWarning("User status not updated for Employee {EmployeeId}", employeeId);
            }
            catch (Exception ex)
            {
                _logger?.LogError(ex, "Error updating user status for Employee {EmployeeId}", employeeId);
                throw;
            }
        }

        private bool IsValidResignationDate(DateTime resignationDate)
        {
            return resignationDate.Year != 9999 &&
                   resignationDate > DateTime.MinValue &&
                   resignationDate < DateTime.MaxValue;
        }

        #endregion
        #region
        private async Task UpdateEmployeeShiftPatternAsyncData(IList<EmployeeShiftPatternDTO> employeeShiftPatternDTOs, LoginDTO loginDTO)
        {
            if (employeeShiftPatternDTOs == null || employeeShiftPatternDTOs.Count == 0)
                return;

            try
            {
                foreach (var shiftPattern in employeeShiftPatternDTOs)
                {
                    await _employeeDAL.UpdateEmployeeShiftPatternAsync(shiftPattern, loginDTO);
                }

                _logger?.LogInformation("Updated {Count} shift pattern(s) for Employee {EmployeeId}",
                    employeeShiftPatternDTOs.Count,
                    employeeShiftPatternDTOs.First().EmployeeId);
            }
            catch (Exception ex)
            {
                _logger?.LogError(ex, "Error updating shift patterns for Employee {EmployeeId}",
                    employeeShiftPatternDTOs.FirstOrDefault()?.EmployeeId);
                throw;
            }
        }
        #endregion
        #region Initialize Employee Dates
        private void InitializeEmployeeDates(EmployeeDTO EmployeeDTO, LoginDTO loginDTO)
        {
            var now = DateTime.UtcNow;
            var maxDate = new DateTime(9999, 12, 31);

            // Set audit fields
            if (EmployeeDTO.EmployeeId <= 0)
            {
                EmployeeDTO.EmployeeCreatedById = loginDTO.UserId;
                EmployeeDTO.EmployeeCreatedOn = now;
            }
            EmployeeDTO.EmployeeModifiedById = loginDTO.UserId;
            EmployeeDTO.EmployeeModifiedOn = now;

            // SourceType is valid (5 = Employee)
            if (EmployeeDTO.SourceType <= 0 || EmployeeDTO.SourceType > 5)
            {
                EmployeeDTO.SourceType = 5;
            }

            // Ensure TenantId is valid for FK
            if (EmployeeDTO.TenantId == 0)
            {
                // Use default global client -1 (from MCLIENT table)
                EmployeeDTO.TenantId = -1;
            }


            // Initialize EmployeeVersionFrom if not set
            if (EmployeeDTO.EmployeeVersionFrom == DateTime.MinValue ||
                EmployeeDTO.EmployeeVersionFrom == default)
            {
                EmployeeDTO.EmployeeVersionFrom = now;
            }

            // Initialize critical dates with safe defaults
            if (EmployeeDTO.EmployeeDateOfBirth == DateTime.MinValue || EmployeeDTO.EmployeeDateOfBirth == default)
                EmployeeDTO.EmployeeDateOfBirth = new DateTime(1990, 1, 1);

            if (EmployeeDTO.EmployeeActualDateOfBirth == DateTime.MinValue || EmployeeDTO.EmployeeActualDateOfBirth == default)
                EmployeeDTO.EmployeeActualDateOfBirth = EmployeeDTO.EmployeeDateOfBirth;

            if (EmployeeDTO.EmployeeDateOfJoining == DateTime.MinValue || EmployeeDTO.EmployeeDateOfJoining == default)
                EmployeeDTO.EmployeeDateOfJoining = now;

            if (EmployeeDTO.EmployeeDateOfIntimation == DateTime.MinValue || EmployeeDTO.EmployeeDateOfIntimation == default)
                EmployeeDTO.EmployeeDateOfIntimation = EmployeeDTO.EmployeeDateOfJoining;

            if (EmployeeDTO.EmployeeProbationStartDate == DateTime.MinValue || EmployeeDTO.EmployeeProbationStartDate == default)
                EmployeeDTO.EmployeeProbationStartDate = EmployeeDTO.EmployeeDateOfJoining;

            // Future dates - set to max date if not valid
            if (EmployeeDTO.EmployeeDateOfResignation == DateTime.MinValue || EmployeeDTO.EmployeeDateOfResignation == default)
                EmployeeDTO.EmployeeDateOfResignation = maxDate;

            if (EmployeeDTO.EmployeeCalculatedDateOfRetirment == DateTime.MinValue || EmployeeDTO.EmployeeCalculatedDateOfRetirment == default)
                EmployeeDTO.EmployeeCalculatedDateOfRetirment = maxDate;

            if (EmployeeDTO.EmployeeActualDateOfRetirment == DateTime.MinValue || EmployeeDTO.EmployeeActualDateOfRetirment == default)
                EmployeeDTO.EmployeeActualDateOfRetirment = maxDate;

            if (EmployeeDTO.EmployeeDateOfConfirmation == DateTime.MinValue || EmployeeDTO.EmployeeDateOfConfirmation == default)
                EmployeeDTO.EmployeeDateOfConfirmation = maxDate;

            if (EmployeeDTO.EmployeeCardValidTill == DateTime.MinValue || EmployeeDTO.EmployeeCardValidTill == default)
                EmployeeDTO.EmployeeCardValidTill = maxDate;

            if (EmployeeDTO.EmployeeDateOfReJoin == DateTime.MinValue || EmployeeDTO.EmployeeDateOfReJoin == default)
                EmployeeDTO.EmployeeDateOfReJoin = maxDate;

            // PF/ESI dates
            if (EmployeeDTO.EmployeePFJoinDate == DateTime.MinValue || EmployeeDTO.EmployeePFJoinDate == default)
                EmployeeDTO.EmployeePFJoinDate = maxDate;

            if (EmployeeDTO.EmployeePFEndDate == DateTime.MinValue || EmployeeDTO.EmployeePFEndDate == default)
                EmployeeDTO.EmployeePFEndDate = maxDate;

            if (EmployeeDTO.EmployeeESIJoinDate == DateTime.MinValue || EmployeeDTO.EmployeeESIJoinDate == default)
                EmployeeDTO.EmployeeESIJoinDate = maxDate;

            if (EmployeeDTO.EmployeeESIEndDate == DateTime.MinValue || EmployeeDTO.EmployeeESIEndDate == default)
                EmployeeDTO.EmployeeESIEndDate = maxDate;

            if (EmployeeDTO.EmployeeTrainingendingDate == DateTime.MinValue || EmployeeDTO.EmployeeTrainingendingDate == default)
                EmployeeDTO.EmployeeTrainingendingDate = now;

            if (EmployeeDTO.EmployeePatternDate == DateTime.MinValue || EmployeeDTO.EmployeePatternDate == default)
                EmployeeDTO.EmployeePatternDate = now;

            // Initialize ContactDTO dates if present
            if (EmployeeDTO.ContactDTO != null)
            {
                if (EmployeeDTO.ContactDTO.ContactModifiedOn == DateTime.MinValue ||
                    EmployeeDTO.ContactDTO.ContactModifiedOn == default)
                    EmployeeDTO.ContactDTO.ContactModifiedOn = now;

                if (EmployeeDTO.ContactDTO.ContactDob == DateTime.MinValue ||
                    EmployeeDTO.ContactDTO.ContactDob == default)
                    EmployeeDTO.ContactDTO.ContactDob = EmployeeDTO.EmployeeDateOfBirth;
            }
        }
        #endregion
        #region Validation & Related Saves

        private async Task ValidateEmployeeVersionFrom(EmployeeDTO EmployeeDTO, LoginDTO LoginDTO)
        {
            if (EmployeeDTO.EmployeeVersionFrom == null || EmployeeDTO.EmployeeVersionFrom == DateTime.MinValue)
                return;

            var futureVersion = await _employeeDAL.GetFutureEmployeeVersionAsync(EmployeeDTO.EmployeeId, EmployeeDTO.EmployeeVersionFrom, LoginDTO);

            if (futureVersion != null)
            {
                string existingVersionDate = futureVersion.EmployeeVersionFrom.ToString("dd-MMM-yyyy");
                throw new InvalidOperationException($"A version already exists till {existingVersionDate}. Cannot create a prior version.");
            }
        }

        private async Task SaveRelatedEmployeeData(EmployeeDTO EmployeeDTO, LoginDTO LoginDTO)
        {
            try
            {
                int empId = EmployeeDTO.EmployeeId;

                if (EmployeeDTO.PresentAddressDTO != null)
                {
                    EmployeeDTO.PresentAddressDTO.AddressObjectId = empId;
                    await _addressBLL.SaveOrUpdatePresentAddress(EmployeeDTO.PresentAddressDTO, LoginDTO);
                }

                if (EmployeeDTO.PermanentAddressDTO != null)
                {
                    EmployeeDTO.PermanentAddressDTO.AddressObjectId = empId;
                    await _addressBLL.SaveOrUpdatePermanentAddress(EmployeeDTO.PermanentAddressDTO, LoginDTO);
                }

                if (EmployeeDTO.ContactDTO != null)
                {
                    await _contactBLL.SaveOrUpdateContactAsync(EmployeeDTO.ContactDTO, LoginDTO, empId);
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"Error saving related employee data for EmployeeId: {EmployeeDTO.EmployeeId}", ex);
            }
        }

        // GB4 parity (Redmine #43104): resolves the addon-based-approval head for this employee's
        // OU+Department combination. Z_DEPTWISEOUWISEHEAD is an optional per-tenant customization
        // table, so — exactly like GB4 — any failure here (table missing, etc.) is swallowed and
        // treated as "no head configured" rather than failing the whole employee save.
        private async Task<int> GetOuWiseDepartmentWiseHeadIdAsync(int workOUId, int departmentId, LoginDTO loginDTO)
        {
            try
            {
                var headId = await _queryExecutor.QuerySingleAsync<int?>(
                    loginDTO,
                    EmployeeQB.GET_OUWISE_DEPARTMENTWISE_HEAD,
                    new { ouid = workOUId, depid = departmentId });

                return headId ?? -1;
            }
            catch
            {
                // No need to fail the save — some tenants don't have Z_DEPTWISEOUWISEHEAD at all.
                return -1;
            }
        }

        // GB4 parity: employees flagged EmployeeIsUser == 0 ("0 = Yes") also get a MUSER login
        // account created/kept in sync alongside the employee record. Ported as a private helper
        // reusing FrameworkDAL's already-correct UserDAL/UserQB SQL (same pattern already used
        // above for AddressBLL/ContactBLL fallback construction) instead of hand-duplicating the
        // ~90-column MUSER insert/update statements.
        private async Task SaveOrUpdateEmployeeUserAccountAsync(EmployeeDTO EmployeeDTO, LoginDTO LoginDTO, bool isNew)
        {
            if (EmployeeDTO.EmployeeIsUser != 0)
                return;

            try
            {
                var userDTO = new UserDTO
                {
                    UserId = EmployeeDTO.EmployeeId,
                    UserCode = (EmployeeDTO.EmployeeCode ?? string.Empty).ToUpper(),
                    UserName = EmployeeDTO.EmployeeName,
                    UserLoginName = EmployeeDTO.EmployeeName,
                    UserPrimaryMail = EmployeeDTO.EmployeePrimaryEMailId,
                    UserPrimaryMobile = EmployeeDTO.EmployeePrimaryMobile,
                    UserPrimaryPhone = EmployeeDTO.EmployeePrimaryMobile,
                    RoleId = EmployeeDTO.RoleId,
                    UserWorkOuId = EmployeeDTO.WorkOUId,
                    UserWorkPeriodId = LoginDTO.WorkPeriodId,
                    UserDOB = EmployeeDTO.EmployeeDateOfBirth,
                    UserValidFrom = EmployeeDTO.EmployeeDateOfJoining,
                    UserValidTo = EmployeeDTO.EmployeeDateOfResignation,
                    UserPasswordType = EmployeeDTO.PassWordType,
                    UserIsEmployee = 0,
                    UserModifiedById = LoginDTO.UserId,
                    UserModifiedOn = DateTime.UtcNow
                };

                if (isNew)
                {
                    userDTO.UserCreatedById = LoginDTO.UserId;
                    userDTO.UserCreatedOn = DateTime.UtcNow;
                }

                if (userDTO.UserPasswordType == 0)
                {
                    // A 12-char plaintext here AES-GCM-encrypts to 56 Base64 chars, overflowing
                    // MUSER.PASSWORD's real NVARCHAR(50) (confirmed live, tracker §31.13 — same
                    // bug as FrameworkBLL.User.UserBLL.SaveUser, which this mirrors). 8 chars
                    // encrypts to 48 Base64 chars, safely under 50.
                    string generatedPassword = _applicationConnection.RandomGenerateAlphaNumeric(8);
                    userDTO.UserGeneratedPassword = generatedPassword;
                    userDTO.UserPassword = _applicationConnection.Encrypt(generatedPassword, userDTO.UserCode);
                }
                else if (!string.IsNullOrWhiteSpace(EmployeeDTO.PassWord))
                {
                    userDTO.UserPassword = _applicationConnection.Encrypt(EmployeeDTO.PassWord, userDTO.UserCode);
                }

                if (isNew)
                    await _userDAL.SaveUser(userDTO, LoginDTO);
                else
                    await _userDAL.UpdateUser(userDTO, LoginDTO);
            }
            catch (Exception ex)
            {
                _logger?.LogError(ex, "Error saving user account for Employee {EmployeeId}", EmployeeDTO.EmployeeId);
                throw;
            }
        }

        // GB4 parity (Redmine #23141): posts a single default UserAccessRights row (Type=0,
        // scoped to the login's WorkOUId, 1 period) for the employee's linked user account.
        private async Task PostEmployeeUserAccessRightsAsync(EmployeeDTO EmployeeDTO, LoginDTO LoginDTO)
        {
            if (EmployeeDTO.EmployeeIsUser != 0)
                return;

            try
            {
                var userAccessRightsDTO = new UserAccessRightsDTO
                {
                    UserAccessRightsId = 0,
                    UserId = EmployeeDTO.EmployeeId,
                    UserAccessRightsSlno = 1,
                    UserAccessRightsType = 0,
                    OrganizationUnitId = LoginDTO.WorkOUId,
                    OrganizationGroupId = -1,
                    UserAccessRightsNoOfPeriod = 1
                };

                await _userAccessRightsDAL.SaveUserAccessRights(userAccessRightsDTO, LoginDTO);
            }
            catch (Exception ex)
            {
                _logger?.LogError(ex, "Error posting UserAccessRights for Employee {EmployeeId}", EmployeeDTO.EmployeeId);
                throw;
            }
        }

        #endregion

        #region Delete & Transfer

        public async Task<Result<string>> DeleteEmployee(int EmployeeId, LoginDTO LoginDTO)
        {
            try
            {
                // Delete addon row before the parent to avoid any FK constraint issues
                await _employeeDAL.DeleteEmployeeAddon(EmployeeId, LoginDTO);

                var trmResult = await _trmEmployeeDAL.DeleteTRMEmployee(EmployeeId, LoginDTO);
                if (!trmResult.IsSuccess)
                    return Result<string>.Failure("Failed to delete Translation Employee. Parent not deleted.");

                var empResult = await _employeeDAL.DeleteEmployee(EmployeeId, LoginDTO);
                if (!empResult.IsSuccess)
                    return Result<string>.Failure("Translation deleted, but failed to delete Employee.");

                return Result<string>.Success("Employee and translations deleted successfully.");
            }
            catch (Exception ex)
            {
                throw new Exception($"Error deleting EmployeeId {EmployeeId}", ex);
            }
        }

        public async Task<TransferAdvanceResultDTO> PostTransferEmployeeAdvance(
            List<PostTransferEmployeeAdvanceDTO> PostTransferEmployeeAdvanceDTOs,
            LoginDTO LoginDTO)
        {
            return await _employeeDAL.PostTransferEmployeeAdvance(PostTransferEmployeeAdvanceDTOs, LoginDTO);
        }

        public async Task<string> GetTransferEmployeeAdvance(LoginDTO LoginDTO)
        {
            return await _employeeDAL.GetTransferEmployeeAdvance(LoginDTO);
        }

        #endregion


        public async Task<string> SaveEmployeeSecretKey(int employeeId, string secretKey, LoginDTO loginDTO)
        {
            try
            {
                return await _employeeDAL.SaveEmployeeSecretKey(employeeId, secretKey, loginDTO);
            }
            catch (Exception ex)
            {
                throw new Exception($"Error updating secret key", ex);
            }
        }
        // Single BLL call backing both of GB4's Employee.svc.cs picklist services
        // (GetSelectListEmployee and GetEmployeePicklistSelect2) — returns page data + Total together.
        public async Task<(string Json, int Total)> GetSelectListEmployee(int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                return await _employeeDAL.GetSelectListEmployee(firstNumber, maxResult, criteriaDTO, loginDTO);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetEmployeeHierarchy(CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                GB5Trace.Step("build-employee-hierarchy", new { loginDTO.ClientId });

                var forest = await BuildEmployeeHierarchyTree(criteriaDTO, loginDTO, ct).ConfigureAwait(false);
                return JsonConvert.SerializeObject(forest);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("build-employee-hierarchy-failed", ex);
                throw;
            }
        }

        public async Task<string> GetHRMSDashboard(CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                GB5Trace.Step("build-hrms-dashboard", new { loginDTO.ClientId });

                var dashboard = await _employeeDAL.GetHRMSDashboard(criteriaDTO, loginDTO, ct).ConfigureAwait(false);
                return JsonConvert.SerializeObject(dashboard);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("build-hrms-dashboard-failed", ex);
                throw;
            }
        }

        // Flat list -> tree, matching the legacy GB4 recursive-CTE root-scoping semantics:
        //  - CriteriaDTO "EmployeeId" given: return just that employee's own subtree (its Parent
        //    reference is cleared so it presents as a standalone root), matching legacy's
        //    "ReportingToEmployeeId" branch.
        //  - CriteriaDTO "OUId" given: DAL already forced ParentId=-1 for every employee in that
        //    OU (see EmployeeQB.GET_EMPLOYEE_HIERARCHY), so they're already roots after
        //    ConvertToTree — just filter the returned forest down to those OU-anchored roots
        //    (using the WorkOuId carried on the DTO purely for this filter) so unrelated
        //    top-of-org employees outside the OU aren't also returned as extra roots.
        //  - Neither given: natural top-of-org roots (ReportingToEmployeeId = -1) fall out of
        //    ConvertToTree already.
        private async Task<IList<EmployeeHierachyTreeDTO>> BuildEmployeeHierarchyTree(CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            var flat = await _employeeDAL.GetEmployeeHierarchy(criteriaDTO, loginDTO, ct).ConfigureAwait(false);

            var mapped = flat.Select(e => new EmployeeHierachyTreeDTO
            {
                Id = e.EmployeeId,
                Code = e.EmployeeCode,
                Name = e.EmployeeName,
                WorkOuId = e.WorkOuId,
                ParentId = e.ParentId,
                ParentCode = e.ParentCode,
                ParentName = e.ParentName,
                Designation = e.Designation,
                Department = e.Department,
                EmployeePhoto = e.EmployeePhoto,
                ThumbNail = e.ThumbNail,
                PrimaryEmailId = e.PrimaryEmailId,
                PrimaryMobile = e.PrimaryMobile,
                BloodGroup = e.BloodGroup,
                FatherName = e.FatherName,
                AdminReportingToId = e.AdminReportingToId,
                AdminReportingToCode = e.AdminReportingToCode,
                AdminReportingToName = e.AdminReportingToName
            }).ToList();

            foreach (var node in mapped)
            {
                node.Parent = node.ParentId == -1 ? null : new EmployeeHierachyTreeDTO { Id = node.ParentId };
            }

            var forest = TreeHelper.ConvertToTree(mapped);

            // NOTE: ConvertToTree deliberately nulls out each node's Parent reference right after
            // linking it into its parent's children list (avoids Parent<->children JSON cycles),
            // so TreeHelper.GetDepth (which climbs via .Parent) can't be used here — it would
            // always report 0. Elevel is set via an explicit depth-first walk instead.
            static void SetElevel(EmployeeHierachyTreeDTO node, int depth)
            {
                node.Elevel = depth;
                foreach (var child in node.children)
                {
                    SetElevel(child, depth + 1);
                }
            }
            foreach (var root in forest)
            {
                SetElevel(root, 0);
            }

            int? scopedEmployeeId = ExtractIntAttribute(criteriaDTO, "employeeid") is int eid and > 0 ? eid : null;
            int? scopedOuId = ExtractIntAttribute(criteriaDTO, "ouid") is int oid and > 0 ? oid : null;

            if (scopedEmployeeId is not null)
            {
                var node = TreeHelper.FindTreeNode(forest, scopedEmployeeId.Value);
                if (node == null) return new List<EmployeeHierachyTreeDTO>();
                node.Parent = null;
                return new List<EmployeeHierachyTreeDTO> { node };
            }

            if (scopedOuId is not null)
            {
                return forest.Where(n => n.WorkOuId == scopedOuId.Value).ToList();
            }

            return forest;
        }

        private static int? ExtractIntAttribute(CriteriaDTO? criteriaDTO, string fieldName)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return null;
            foreach (var sec in criteriaDTO.SectionCriteriaList)
            {
                foreach (var attr in sec.AttributesCriteriaList)
                {
                    if (string.Equals(attr.FieldName, fieldName, StringComparison.OrdinalIgnoreCase) && attr.FieldValue != null)
                    {
                        if (int.TryParse(attr.FieldValue.ToString(), out var value)) return value;
                    }
                }
            }
            return null;
        }

        public async Task<(int SeparationYears, int SeparationMonths, int SeparationDays)> GetDifferenceFromJoiningDate(
            int employeeId, string separationDate, LoginDTO loginDTO)
        {
            if (!DateTime.TryParse(separationDate, out var parsedSeparationDate))
                throw new ArgumentException($"Invalid SeparationDate '{separationDate}'.", nameof(separationDate));

            return await _employeeDAL.GetDifferenceFromJoiningDate(employeeId, parsedSeparationDate, loginDTO);
        }

        public async Task<int> GetEmployeeIdByCode(string employeeCode, LoginDTO loginDTO)
        {
            return await _employeeDAL.GetEmployeeIdByCode(employeeCode, loginDTO);
        }

        public async Task<(List<EmployeeProfileReportDTO> Items, int Total)> GetEmployeeProfileReport(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                GB5Trace.Step("get-employee-profile-report", new { firstNumber, maxResult });
                return await _employeeDAL.GetEmployeeProfileReport(firstNumber, maxResult, criteriaDTO, loginDTO, ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("get-employee-profile-report-failed", ex);
                throw;
            }
        }

        public async Task<(List<EmployeePostingSelectListDTO> Items, int Total)> EmployeePostingSelectionList(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                GB5Trace.Step("employee-posting-selection-list", new { firstNumber, maxResult });
                return await _employeeDAL.EmployeePostingSelectionList(firstNumber, maxResult, criteriaDTO, loginDTO, ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("employee-posting-selection-list-failed", ex);
                throw;
            }
        }

        public async Task<(List<EmployeeVsShiftPatternDTO> Items, int Total)> GetEmployeeVsShiftPattern(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                GB5Trace.Step("get-employee-vs-shift-pattern", new { firstNumber, maxResult });
                return await _employeeDAL.GetEmployeeVsShiftPattern(firstNumber, maxResult, criteriaDTO, loginDTO, ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("get-employee-vs-shift-pattern-failed", ex);
                throw;
            }
        }

        public async Task<(List<EmployeeVsShiftPatternNewDTO> Items, int Total)> GetEmployeeVsShiftPatternNew(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            try
            {
                GB5Trace.Step("get-employee-vs-shift-pattern-new", new { firstNumber, maxResult });
                return await _employeeDAL.GetEmployeeVsShiftPatternNew(firstNumber, maxResult, criteriaDTO, loginDTO, ct).ConfigureAwait(false);
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("get-employee-vs-shift-pattern-new-failed", ex);
                throw;
            }
        }

        public async Task<List<EmployeeVersionPickListDTO>> GetEmployeeVersionPicklist(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            return await _employeeDAL.GetEmployeeVersionPicklist(firstNumber, maxResult, criteriaDTO, loginDTO, ct).ConfigureAwait(false);
        }

        public async Task<List<EmployeeBasedLegecyInformationDTO>> LegecyBasedEmployeeInformation(
            int employeeId, LoginDTO loginDTO, CancellationToken ct)
        {
            return await _employeeDAL.LegecyBasedEmployeeInformation(employeeId, loginDTO, ct).ConfigureAwait(false);
        }

        public async Task<List<EmployeeVersionInPayProcessDTO>> GetEmployeeVersionInPayProcess(
            CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            return await _employeeDAL.GetEmployeeVersionInPayProcess(criteriaDTO, loginDTO, ct).ConfigureAwait(false);
        }

        public async Task<EmployeeVersionDTO?> GetEmployeeVersion(int employeeVersionId, LoginDTO loginDTO, CancellationToken ct)
        {
            return await _employeeDAL.GetEmployeeVersion(employeeVersionId, loginDTO, ct).ConfigureAwait(false);
        }

        public async Task<List<DuplicateEmployeeDTO>> GetDuplicateEmployee(CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            return await _employeeDAL.GetDuplicateEmployee(criteriaDTO, loginDTO, ct).ConfigureAwait(false);
        }

        // GB4 parity (EmployeeBLL.cs:1732-1816): two hard guards run before any write, then the
        // version + its addon-version rows are deleted, and MEMPLOYEE/MEMPLOYEEADDON are rolled back
        // to the new latest remaining version. All in one transaction (TLeaveBLL.SaveTLeave pattern).
        public async Task<string> DeleteEmployeeVersion(int employeeVersionId, LoginDTO loginDTO, CancellationToken ct)
        {
            var Trans = await _queryExecutor.BeginTransactionAsync(loginDTO);
            try
            {
                int payProcessCount = await _employeeDAL.GetPayProcessDoneAfterEmployeeVersion(employeeVersionId, loginDTO, Trans, ct).ConfigureAwait(false);
                if (payProcessCount > 0)
                    throw new InvalidOperationException("Already Payprocess is done after this version unable to delete ..");

                int laterVersionCount = await _employeeDAL.GetLatestVersionAfterGivenVersion(employeeVersionId, loginDTO, Trans, ct).ConfigureAwait(false);
                if (laterVersionCount > 0)
                    throw new InvalidOperationException("Already new version is done after selected version..Delete that first.");

                int employeeId = await _employeeDAL.GetEmployeeIdFromVersion(employeeVersionId, loginDTO, Trans, ct).ConfigureAwait(false);

                await _employeeDAL.DeleteEmployeeAddonVersionByVersion(employeeVersionId, loginDTO, Trans, ct).ConfigureAwait(false);
                await _employeeDAL.DeleteEmployeeVersionById(employeeVersionId, loginDTO, Trans, ct).ConfigureAwait(false);

                int latestVersionId = await _employeeDAL.GetEmployeeVersionLatestId(employeeId, loginDTO, Trans, ct).ConfigureAwait(false);

                await _employeeDAL.RollbackEmployeeToLatestVersion(latestVersionId, loginDTO, Trans, ct).ConfigureAwait(false);
                await _employeeDAL.RollbackEmployeeAddonToLatestVersion(latestVersionId, loginDTO, Trans, ct).ConfigureAwait(false);

                await _queryExecutor.CommitAsync(Trans);
                return SuccessResponse.DeleteSuccessMessage;
            }
            catch (Exception)
            {
                await _queryExecutor.RollbackAsync(Trans);
                throw;
            }
        }

        public async Task<bool> RegisterDeletion(int employeeId, DateTime effectiveFrom, LoginDTO loginDTO, CancellationToken ct)
        {
            if (_dailyAttendanceDAL == null)
                throw new InvalidOperationException("DailyAttendance DAL is not available.");

            bool payProcessDone = await _dailyAttendanceDAL.IsPayProcessDoneFromDate(employeeId, effectiveFrom, loginDTO, ct).ConfigureAwait(false);
            if (payProcessDone)
                throw new InvalidOperationException("Pay process already done for the given period. Registration cannot be deleted.");

            await _dailyAttendanceDAL.DeleteDailyAttendanceFromDate(employeeId, effectiveFrom, loginDTO, ct).ConfigureAwait(false);

            return true;
        }

        [GB5Shared.Attributes.ManualTransactionJustified(
            "Bulk shift-pattern batch update across N employees — no single owning entity DTO; loops the existing single-item DAL save.")]
        public async Task<string> UpdateEmployeeShiftPattern(List<EmployeeShiftPatternDTO> employeeShiftPatternDTOs, LoginDTO loginDTO, CancellationToken ct)
        {
            if (employeeShiftPatternDTOs == null || employeeShiftPatternDTOs.Count == 0)
                throw new ArgumentException("At least one employee shift pattern is required.");

            var trans = await _queryExecutor.BeginTransactionAsync(loginDTO).ConfigureAwait(false);
            try
            {
                foreach (var dto in employeeShiftPatternDTOs)
                {
                    await _employeeDAL.UpdateEmployeeShiftPatternAsync(dto, loginDTO, trans, ct).ConfigureAwait(false);
                }

                await _queryExecutor.CommitAsync(trans).ConfigureAwait(false);
                return SuccessResponse.UpdateSuccess;
            }
            catch
            {
                await _queryExecutor.RollbackAsync(trans).ConfigureAwait(false);
                throw;
            }
        }

        public async Task<(List<PayRollDAL.DTO.Job.JOBDetailReportDTO> Items, int Total)> GetJOBDetailReport(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            if (_jobDAL == null)
                throw new InvalidOperationException("Job DAL is not available.");

            return await _jobDAL.GetJOBDetailReport(firstNumber, maxResult, criteriaDTO, loginDTO, ct).ConfigureAwait(false);
        }
    }
}