using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using PayRollDAL.DTO.Leave;
using PayRollDAL.DTO.TLeave;
using PayRollDAL.Query.Leave;
using PayRollDAL.Query.TLeave;
using System.Data.Common;
using System.Text.Json;
using System.Text.RegularExpressions;
using static GB5Shared.GB5Constant.Constant;

namespace PayRollDAL.CustomeCode.TLeave
{
    public class TLeaveDAL : ITLeaveDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public TLeaveDAL(IQueryExecutor queryExecutor)
        {
            _QueryExecutor = queryExecutor;
        }

        public async Task<int> SaveTLeave(TLeaveDTO tLeaveDTO, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            try
            {
                string sql = TLeaveQB.SAVE_TLEAVE;
                var param = BuildParams(tLeaveDTO, loginDTO);
                return await _QueryExecutor.ExecuteAsync(loginDTO, sql, param, transaction);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> UpdateTLeave(TLeaveDTO tLeaveDTO, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            try
            {
                string sql = TLeaveQB.UPDATE_TLEAVE;
                var param = BuildParams(tLeaveDTO, loginDTO);
                return await _QueryExecutor.ExecuteAsync(loginDTO, sql, param, transaction);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveTLeaveDetail(TLeaveDetailDTO detailDTO, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            try
            {
                string sql = TLeaveQB.SAVE_TLEAVE_DETAIL;
                return await _QueryExecutor.ExecuteAsync(loginDTO, sql, detailDTO, transaction);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> DeleteTLeaveDetails(int tLeaveId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            try
            {
                string sql = TLeaveQB.DELETE_TLEAVE_DETAILS;
                return await _QueryExecutor.ExecuteAsync(loginDTO, sql, new { TLeaveId = tLeaveId }, transaction);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> DeleteTLeave(int tLeaveId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            string sql = TLeaveQB.DELETE_TLEAVE;
            return await _QueryExecutor.ExecuteAsync(loginDTO, sql, new { TLeaveId = tLeaveId }, transaction)
                .ConfigureAwait(false);
        }

        public async Task<int> UpdateTLeaveStatus(int tLeaveId, byte status, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            string sql = TLeaveQB.UPDATE_TLEAVE_STATUS;
            return await _QueryExecutor.ExecuteAsync(loginDTO, sql,
                new { TLeaveId = tLeaveId, Status = status, UserId = loginDTO.UserId, ModifiedOn = DateTime.UtcNow },
                transaction).ConfigureAwait(false);
        }

        public async Task<IEnumerable<TLeavePicklistDTO>> CheckDuplicateLeaveDates(
            int employeeId, DateOnly leaveDate, int excludeTLeaveId, LoginDTO loginDTO)
        {
            string sql = TLeaveQB.CHECK_DUPLICATE_LEAVE_DATES;
            return await _QueryExecutor.QueryAsync<TLeavePicklistDTO>(loginDTO, sql,
                new { EmployeeId = employeeId, LeaveDate = leaveDate, ExcludeTLeaveId = excludeTLeaveId })
                .ConfigureAwait(false);
        }

        public async Task<TLeaveDTO?> GetTLeave(int tLeaveId, LoginDTO loginDTO)
        {
            try
            {
                string sql = TLeaveQB.GET_TLEAVE;
                return await _QueryExecutor.QuerySingleAsync<TLeaveDTO>(loginDTO, sql, new { TLeaveId = tLeaveId });
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<List<TLeaveDetailDTO>> GetTLeaveDetailsByLeave(int tLeaveId, LoginDTO loginDTO)
        {
            try
            {
                string sql = TLeaveQB.GET_TLEAVE_DETAILS_BY_LEAVE;
                var result = await _QueryExecutor.QueryAsync<TLeaveDetailDTO>(loginDTO, sql, new { TLeaveId = tLeaveId });
                return result.ToList();
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<byte?> GetLeaveTypeById(int leaveTypeId, LoginDTO loginDTO)
        {
            string sql = TLeaveQB.GET_LEAVE_TYPE_BY_ID;
            return await _QueryExecutor.QuerySingleAsync<byte?>(loginDTO, sql, new { LeaveTypeId = leaveTypeId })
                .ConfigureAwait(false);
        }

        // ── Leave Credit / Debit Posting ─────────────────────────────────────────────

        public async Task<int> CountEligibleEmployeesForPosting(TLeaveDTO dto, LoginDTO loginDTO)
        {
            var detail = dto.TLeaveDetailArray[0];
            var param = BuildOptionalFilterParams(dto);
            param.Add("workouid", loginDTO.WorkOUId);

            string sql;
            switch (dto.RegularCreditType)
            {
                case 0:
                    sql = TLeaveQB.COUNT_ELIGIBLE_EMPLOYEES_BY_PAYPROCESS;
                    param.Add("fromdate", detail.FromDate);
                    param.Add("todate", detail.ToDate);
                    break;
                case 1:
                    sql = TLeaveQB.COUNT_ELIGIBLE_EMPLOYEES_BY_DATEOFJOINING;
                    param.Add("joiningdate", detail.DateOfJoining);
                    break;
                case 2:
                    sql = TLeaveQB.COUNT_ELIGIBLE_EMPLOYEES_BY_DAILYATTENDANCE;
                    param.Add("attendancedate", detail.AttendanceDate);
                    param.Add("attendancetodate", detail.AttendanceToDate);
                    break;
                default:
                    throw new ArgumentException($"Unsupported RegularCreditType: {dto.RegularCreditType}");
            }

            return await _QueryExecutor.ExecuteScalarAsync<int>(loginDTO, sql, param).ConfigureAwait(false);
        }

        public async Task<int> InsertTLeaveHeaderForPosting(TLeaveDTO dto, int tLeaveId, LoginDTO loginDTO, DbTransaction transaction)
        {
            var param = new
            {
                TLeaveId = tLeaveId,
                dto.OUId,
                dto.PeriodId,
                dto.BizTransactionTypeId,
                dto.TLeaveLeaveNumber,
                dto.TLeaveTypeOfLeave,
                loginDTO.UserId
            };
            return await _QueryExecutor.ExecuteAsync(loginDTO, TLeaveQB.INSERT_TLEAVE_HEADER_FOR_POSTING, param, transaction)
                .ConfigureAwait(false);
        }

        public async Task<int> InsertTLeaveDetailForPosting(TLeaveDTO dto, int tLeaveId, int detailStartId, LoginDTO loginDTO, DbTransaction transaction)
        {
            var detail = dto.TLeaveDetailArray[0];
            var param = BuildOptionalFilterParams(dto);
            param.Add("detailstartid", detailStartId);
            param.Add("tleaveid", tLeaveId);
            param.Add("leavetypeid", detail.LeaveTypeId);
            param.Add("leavedate", detail.TLeaveDetailLeaveDate);
            param.Add("validtilldate", detail.TLeaveDetailValidTill);
            param.Add("noofdaysperleaveday", dto.TLeaveNumberOfDays);
            param.Add("leavedays", dto.LeaveDays);
            param.Add("workouid", loginDTO.WorkOUId);
            param.Add("fromdate", detail.FromDate);
            param.Add("todate", detail.ToDate);
            param.Add("typeofleave", dto.TLeaveTypeOfLeave);
            param.Add("resignationfrom", detail.ResignationFromDate);
            param.Add("resignationto", detail.ResignationToDate);
            param.Add("joiningdate", detail.DateOfJoining);
            param.Add("attendancedate", detail.AttendanceDate);
            param.Add("attendancetodate", detail.AttendanceToDate);

            string sql;
            switch (dto.RegularCreditType)
            {
                case 0:
                    if (string.IsNullOrWhiteSpace(dto.FormulaCode) || !Regex.IsMatch(dto.FormulaCode, "^[A-Za-z0-9_]+$"))
                        throw new ArgumentException("FormulaCode must be alphanumeric/underscore only.");
                    sql = string.Format(TLeaveQB.INSERT_TLEAVEDETAIL_FROM_PAYPROCESS_SELECT, "F_" + dto.FormulaCode);
                    break;
                case 1:
                    sql = TLeaveQB.INSERT_TLEAVEDETAIL_FROM_EMPLOYEE_SELECT;
                    break;
                case 2:
                    sql = TLeaveQB.INSERT_TLEAVEDETAIL_FROM_DAILYATTENDANCE_SELECT;
                    break;
                default:
                    throw new ArgumentException($"Unsupported RegularCreditType: {dto.RegularCreditType}");
            }

            return await _QueryExecutor.ExecuteAsync(loginDTO, sql, param, transaction).ConfigureAwait(false);
        }

        public async Task<List<TLeaveDetailDTO>> GetLeaveBalanceForMakeZero(TLeaveDTO dto, LoginDTO loginDTO)
        {
            var detail = dto.TLeaveDetailArray[0];
            var param = BuildOptionalFilterParams(dto);
            param.Add("leavetypeid", detail.LeaveTypeId);
            param.Add("leavedate", detail.TLeaveDetailLeaveDate);
            param.Add("workouid", loginDTO.WorkOUId);
            param.Add("leavecreditclassid", BIZTRANSACTIONCLASSCONSTANT.LEAVECREDIT);
            param.Add("leavedebitclassid", BIZTRANSACTIONCLASSCONSTANT.LEAVEDEBIT);
            param.Add("leaveclassid", BIZTRANSACTIONCLASSCONSTANT.LEAVE);
            param.Add("iscredit", dto.TempLeaveTypes == 0 ? 1 : 0);

            var result = await _QueryExecutor.QueryAsync<TLeaveDetailDTO>(loginDTO, TLeaveQB.GET_LEAVE_BALANCE_FOR_MAKEZERO, param)
                .ConfigureAwait(false);
            return result.ToList();
        }

        public async Task<int> BulkInsertTLeaveDetailForMakeZero(IEnumerable<TLeaveDetailDTO> details, LoginDTO loginDTO, DbTransaction transaction)
        {
            return await _QueryExecutor.BulkInsertAsync(loginDTO, TLeaveQB.INSERT_TLEAVEDETAIL_FOR_MAKEZERO, details, transaction)
                .ConfigureAwait(false);
        }

        private static Dictionary<string, object?> BuildOptionalFilterParams(TLeaveDTO dto)
        {
            static object? OrNull(int value) => value == -1 ? null : value;

            return new Dictionary<string, object?>
            {
                { "employeetypeid", OrNull(dto.EmployeeTypeId) },
                { "paygroupid", OrNull(dto.PayGroupId) },
                { "stafftypeid", OrNull(dto.StaffTypeId) },
                { "unitid", OrNull(dto.UnitId) },
                { "worktypeid", OrNull(dto.WorkTypeId) },
                { "divisionid", OrNull(dto.DivisionId) },
                { "payconfigurationid", OrNull(dto.PayConfigurationId) },
                { "shiftid", OrNull(dto.ShiftId) },
                { "departmentid", OrNull(dto.DepartmentId) }
            };
        }

        // ── Helpers ──────────────────────────────────────────────────────────────────

        /// <summary>
        /// Builds a Dapper parameter bag from the DTO, then injects
        /// <c>@UserId</c> from <c>loginDTO.UserId</c> so that CREATEDBYID
        /// and MODIFIEDBYID always come from the authenticated session —
        /// never from a client-supplied DTO value.
        /// </summary>
        private static DynamicParameters BuildParams(TLeaveDTO dto, LoginDTO loginDTO)
        {
            var p = new DynamicParameters(dto);
            p.Add("UserId", loginDTO.UserId);
            return p;
        }
        public async Task<string> GetSelectListTLeave(
    int FirstNumber,
    int MaxResult,
    CriteriaDTO CriteriaDTO,
    LoginDTO LoginDTO,
    bool IsCount = false)
        {
            try
            {
                string Json = "";

                // Initialize filters
                string LeaveNumber = null;
                string EmployeeCode = null;
                string EmployeeName = null;
                string LeaveCode = null;
                string LeaveName = null;

                int? BizTransactionTypeId = null;
                int? EmployeeId = null;
                int? PeriodId = null;

                if (CriteriaDTO?.SectionCriteriaList != null)
                {
                    foreach (var section in CriteriaDTO.SectionCriteriaList)
                    {
                        if (section.AttributesCriteriaList == null)
                            continue;

                        foreach (var attr in section.AttributesCriteriaList)
                        {
                            string fieldName = attr.FieldName?.ToLower();
                            object fieldValue = attr.FieldValue;

                            switch (fieldName)
                            {
                                case "leavenumber":
                                    string leaveNumVal = GetStringValue(fieldValue);
                                    if (!string.IsNullOrWhiteSpace(leaveNumVal))
                                        LeaveNumber = leaveNumVal;
                                    break;

                                case "employeecode":
                                    string empCodeVal = GetStringValue(fieldValue);
                                    if (!string.IsNullOrWhiteSpace(empCodeVal))
                                        EmployeeCode = empCodeVal;
                                    break;

                                case "employeename":
                                    string empNameVal = GetStringValue(fieldValue);
                                    if (!string.IsNullOrWhiteSpace(empNameVal))
                                        EmployeeName = empNameVal;
                                    break;

                                case "leavecode":
                                    string leaveCodeVal = GetStringValue(fieldValue);
                                    if (!string.IsNullOrWhiteSpace(leaveCodeVal))
                                        LeaveCode = leaveCodeVal;
                                    break;

                                case "leavename":
                                    string leaveNameVal = GetStringValue(fieldValue);
                                    if (!string.IsNullOrWhiteSpace(leaveNameVal))
                                        LeaveName = leaveNameVal;
                                    break;

                                case "biztransactiontypeid":
                                    BizTransactionTypeId = GetIntValue(fieldValue);
                                    break;

                                case "employeeid":
                                    EmployeeId = GetIntValue(fieldValue);
                                    break;

                                case "periodid":
                                    PeriodId = GetIntValue(fieldValue);
                                    break;
                            }
                        }
                    }
                }

                // Build WHERE conditions
                List<string> whereConditions = new List<string>();
                var parameters = new Dictionary<string, object>();

                parameters.Add("firstnumber", FirstNumber);
                parameters.Add("maxresult", MaxResult);

                // Add BizTransactionTypeId filter (from TLEAVE table)
                if (BizTransactionTypeId.HasValue && BizTransactionTypeId.Value != -1)
                {
                    whereConditions.Add(" T.BIZTRANSACTIONTYPEID = @biztransactiontypeid ");
                    parameters.Add("biztransactiontypeid", BizTransactionTypeId.Value);
                    System.Diagnostics.Debug.WriteLine($"Adding BizTransactionTypeId filter: {BizTransactionTypeId.Value}");
                }

                // Add EmployeeId filter (from TLEAVEDETAIL table)
                if (EmployeeId.HasValue && EmployeeId.Value != -1)
                {
                    whereConditions.Add(" D.EMPLOYEEID = @employeeid ");
                    parameters.Add("employeeid", EmployeeId.Value);
                    System.Diagnostics.Debug.WriteLine($"Adding EmployeeId filter: {EmployeeId.Value}");
                }

                // Add PeriodId filter
                if (PeriodId.HasValue && PeriodId.Value != -1)
                {
                    whereConditions.Add(" T.PERIODID = @periodid ");
                    parameters.Add("periodid", PeriodId.Value);
                }

                // Add search conditions
                List<string> searchConditions = new List<string>();

                if (!string.IsNullOrWhiteSpace(LeaveNumber))
                {
                    searchConditions.Add(" T.LEAVENUMBER LIKE @leavenumber ");
                    parameters.Add("leavenumber", $"%{LeaveNumber}%");
                }

                if (!string.IsNullOrWhiteSpace(EmployeeCode))
                {
                    searchConditions.Add(" E.EMPLOYEECODE LIKE @employeecode ");
                    parameters.Add("employeecode", $"%{EmployeeCode}%");
                }

                if (!string.IsNullOrWhiteSpace(EmployeeName))
                {
                    searchConditions.Add(" E.EMPLOYEENAME LIKE @employeename ");
                    parameters.Add("employeename", $"%{EmployeeName}%");
                }

                if (!string.IsNullOrWhiteSpace(LeaveCode))
                {
                    searchConditions.Add(" L.LEAVECODE LIKE @leavecode ");
                    parameters.Add("leavecode", $"%{LeaveCode}%");
                }

                if (!string.IsNullOrWhiteSpace(LeaveName))
                {
                    searchConditions.Add(" L.LEAVENAME LIKE @leavename ");
                    parameters.Add("leavename", $"%{LeaveName}%");
                }

                // Combine conditions
                string dynamicFilters = "";

                if (whereConditions.Any())
                {
                    dynamicFilters += " AND " + string.Join(" AND ", whereConditions);
                }

                if (searchConditions.Any())
                {
                    dynamicFilters += " AND (" + string.Join(" OR ", searchConditions) + ")";
                }

                // Build final SQL
                string sqlQuery = TLeaveQB.GET_SELECTLIST_TLEAVE.Replace("{DYNAMIC_FILTERS}", dynamicFilters);

                // Debug output
                System.Diagnostics.Debug.WriteLine("=== FINAL SQL QUERY ===");
                System.Diagnostics.Debug.WriteLine(sqlQuery);
                System.Diagnostics.Debug.WriteLine("=== PARAMETERS ===");
                foreach (var p in parameters)
                {
                    System.Diagnostics.Debug.WriteLine($"{p.Key} = {p.Value}");
                }

                if (!IsCount)
                {
                    IEnumerable<TLeavePicklistDTO> TLeaveDTO =
                        await _QueryExecutor.QueryAsync<TLeavePicklistDTO>(
                            LoginDTO,
                            sqlQuery,
                            parameters);

                    System.Diagnostics.Debug.WriteLine($"Records found: {TLeaveDTO.Count()}");

                    Json = JsonConvert.SerializeObject(TLeaveDTO);
                }
                else
                {
                    Json = "0";
                }

                return Json;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine($"Error: {ex.Message}");
                System.Diagnostics.Debug.WriteLine($"Stack Trace: {ex.StackTrace}");
                throw;
            }
        }

        // GB4 parity: TLeaveDAL.GetTLeaveDetails — OUId filter, DateFilter toggle
        // (0 = filter on detail row date, else on leave header date), PeriodFrom/PeriodTo
        // range, plus the extra criteria fields from ApplyGetTLeaveDetails.
        public async Task<string> GetTLeaveDetails(
            int FirstNumber,
            int MaxResult,
            CriteriaDTO CriteriaDTO,
            LoginDTO LoginDTO,
            bool Count = false)
        {
            try
            {
                int? OUId = null;
                string DateFilter = "0";
                DateTime? PeriodFrom = null;
                DateTime? PeriodTo = null;

                int? BizTransactionTypeId = null;
                int? EmployeeId = null;
                int? LeaveType = null;
                int? LeaveStatus = null;
                int? DepartmentId = null;
                int? ReasonId = null;

                if (CriteriaDTO?.SectionCriteriaList != null)
                {
                    foreach (var section in CriteriaDTO.SectionCriteriaList)
                    {
                        if (section.AttributesCriteriaList == null)
                            continue;

                        foreach (var attr in section.AttributesCriteriaList)
                        {
                            string fieldName = attr.FieldName?.ToLower();
                            object fieldValue = attr.FieldValue;

                            switch (fieldName)
                            {
                                case "ouid":
                                    int ouVal = GetIntValue(fieldValue);
                                    if (ouVal != -1) OUId = ouVal;
                                    break;

                                case "datefilter":
                                    string dateFilterVal = GetStringValue(fieldValue);
                                    if (!string.IsNullOrWhiteSpace(dateFilterVal))
                                        DateFilter = dateFilterVal;
                                    break;

                                case "periodfrom":
                                    string fromVal = GetStringValue(fieldValue);
                                    if (!string.IsNullOrWhiteSpace(fromVal) && DateTime.TryParse(fromVal, out var fromDt))
                                        PeriodFrom = fromDt;
                                    break;

                                case "periodto":
                                    string toVal = GetStringValue(fieldValue);
                                    if (!string.IsNullOrWhiteSpace(toVal) && DateTime.TryParse(toVal, out var toDt))
                                        PeriodTo = toDt;
                                    break;

                                case "biztransactiontypeid":
                                    int bizVal = GetIntValue(fieldValue);
                                    if (bizVal != -1) BizTransactionTypeId = bizVal;
                                    break;

                                case "employeeid":
                                    int empVal = GetIntValue(fieldValue);
                                    if (empVal != -1) EmployeeId = empVal;
                                    break;

                                case "leavetype":
                                    int leaveTypeVal = GetIntValue(fieldValue);
                                    if (leaveTypeVal != -1) LeaveType = leaveTypeVal;
                                    break;

                                case "leavestatus":
                                    int leaveStatusVal = GetIntValue(fieldValue);
                                    if (leaveStatusVal != -1) LeaveStatus = leaveStatusVal;
                                    break;

                                case "departmentid":
                                    int deptVal = GetIntValue(fieldValue);
                                    if (deptVal != -1) DepartmentId = deptVal;
                                    break;

                                case "reasonid":
                                    int reasonVal = GetIntValue(fieldValue);
                                    if (reasonVal != -1) ReasonId = reasonVal;
                                    break;
                            }
                        }
                    }
                }

                var whereConditions = new List<string>();
                var parameters = new Dictionary<string, object>();

                if (OUId.HasValue)
                {
                    whereConditions.Add(" LEAVE.OUID = @ouid ");
                    parameters.Add("ouid", OUId.Value);
                }

                string dateColumn = DateFilter == "0" ? "LEAVEDETAIL.LEAVEDATE" : "LEAVE.LEAVEDATE";

                if (PeriodFrom.HasValue)
                {
                    whereConditions.Add($" {dateColumn} >= @periodfrom ");
                    parameters.Add("periodfrom", PeriodFrom.Value);
                }

                if (PeriodTo.HasValue)
                {
                    whereConditions.Add($" {dateColumn} <= @periodto ");
                    parameters.Add("periodto", PeriodTo.Value);
                }

                if (BizTransactionTypeId.HasValue)
                {
                    whereConditions.Add(" LEAVE.BIZTRANSACTIONTYPEID = @biztransactiontypeid ");
                    parameters.Add("biztransactiontypeid", BizTransactionTypeId.Value);
                }

                if (EmployeeId.HasValue)
                {
                    whereConditions.Add(" LEAVEDETAIL.EMPLOYEEID = @employeeid ");
                    parameters.Add("employeeid", EmployeeId.Value);
                }

                if (LeaveType.HasValue)
                {
                    whereConditions.Add(" LEAVEDETAIL.LEAVETYPEID = @leavetype ");
                    parameters.Add("leavetype", LeaveType.Value);
                }

                if (LeaveStatus.HasValue)
                {
                    whereConditions.Add(" LEAVE.STATUS = @leavestatus ");
                    parameters.Add("leavestatus", LeaveStatus.Value);
                }

                if (DepartmentId.HasValue)
                {
                    whereConditions.Add(" EMP.DEPARTMENTID = @departmentid ");
                    parameters.Add("departmentid", DepartmentId.Value);
                }

                if (ReasonId.HasValue)
                {
                    whereConditions.Add(" LEAVE.REASONID = @reasonid ");
                    parameters.Add("reasonid", ReasonId.Value);
                }

                string sql = TLeaveQB.GET_TLEAVEDETAILS;
                if (whereConditions.Any())
                {
                    sql += " AND " + string.Join(" AND ", whereConditions);
                }

                if (!Count)
                {
                    sql += "\r\n ORDER BY LEAVE.LEAVEDATE, LEAVE.LEAVENUMBER, LEAVEDETAIL.LEAVEDATE";

                    var result = await _QueryExecutor.QueryAsync<LeaveRequestReportDTO>(
                        LoginDTO, sql, parameters);

                    return JsonConvert.SerializeObject(result ?? new List<LeaveRequestReportDTO>());
                }
                else
                {
                    int totalCount = await _QueryExecutor.ExecuteScalarAsync<int>(
                        LoginDTO, $"SELECT COUNT(*) FROM ({sql}) AS TLeaveDetailsTotal", parameters);

                    return totalCount.ToString();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<List<TotalTakenLeaveDTO>> GetTotalLeaveTaken(
            int FirstNumber,
            int MaxResult,
            CriteriaDTO CriteriaDTO,
            LoginDTO LoginDTO,
            bool IsCount = false)
        {
            try
            {
                int? OUId = null;
                int? EmployeeId = null;
                int? DepartmentId = null;
                int? DesignationId = null;
                int? PayConfigurationId = null;
                DateTime? PeriodFromDate = null;
                DateTime? PeriodToDate = null;

                if (CriteriaDTO?.SectionCriteriaList != null)
                {
                    foreach (var section in CriteriaDTO.SectionCriteriaList)
                    {
                        if (section.AttributesCriteriaList == null)
                            continue;

                        foreach (var attr in section.AttributesCriteriaList)
                        {
                            string fieldName = attr.FieldName?.ToLower();
                            object fieldValue = attr.FieldValue;

                            switch (fieldName)
                            {
                                case "ouid":
                                    int ouVal = GetIntValue(fieldValue);
                                    if (ouVal != -1) OUId = ouVal;
                                    break;

                                case "employeeid":
                                    int empId = GetIntValue(fieldValue);
                                    if (empId != -1) EmployeeId = empId;
                                    break;

                                case "departmentid":
                                    int deptId = GetIntValue(fieldValue);
                                    if (deptId != -1) DepartmentId = deptId;
                                    break;

                                case "designationid":
                                    int desgId = GetIntValue(fieldValue);
                                    if (desgId != -1) DesignationId = desgId;
                                    break;

                                case "payconfigurationid":
                                    int payConfigId = GetIntValue(fieldValue);
                                    if (payConfigId != -1) PayConfigurationId = payConfigId;
                                    break;

                                // GB4 parity fix: periodfromdate/periodtodate are sent as epoch
                                // values by the frontend (same convention as EmployeeDAL,
                                // DailyAttendanceDAL, etc. in this codebase) — parsing with
                                // DateTime.TryParse against the raw value silently fails for
                                // real callers. Use FromEpoch like every sibling DAL method.
                                case "periodfromdate":
                                    PeriodFromDate = FromEpoch(Convert.ToInt64(fieldValue));
                                    break;

                                case "periodtodate":
                                    PeriodToDate = FromEpoch(Convert.ToInt64(fieldValue));
                                    break;
                            }
                        }
                    }
                }

                if (PeriodFromDate == null || PeriodToDate == null)
                    throw new ArgumentException("PeriodFromDate and PeriodToDate are required for GetTotalLeaveTaken.");

                var parameters = new Dictionary<string, object>
                {
                    // GB4 parity fix: emp.WORKOUID IN(:ouid) is sourced from CriteriaDTO's
                    // "ouid" field in GB4, not hardcoded to the session's WorkOUId. Default
                    // to LoginDTO.WorkOUId only when the caller did not supply one.
                    { "ouid", OUId ?? LoginDTO.WorkOUId },
                    { "tenantid", LoginDTO.ClientId },
                    { "periodfromdate", PeriodFromDate.Value },
                    { "periodtodate", PeriodToDate.Value },
                    { "employeeid", EmployeeId },
                    { "departmentid", DepartmentId },
                    { "designationid", DesignationId },
                    { "payconfigurationid", PayConfigurationId },
                    // GB4 parity fix: FirstNumber/MaxResult were accepted but never applied —
                    // GET_TOTAL_LEAVE_TAKEN now genuinely pages via ROW_NUMBER()/RowNum.
                    { "firstnumber", FirstNumber },
                    { "maxresult", MaxResult },
                };

                if (!IsCount)
                {
                    var result = await _QueryExecutor.QueryAsync<TotalTakenLeaveDTO>(
                        LoginDTO,
                        TLeaveQB.GET_TOTAL_LEAVE_TAKEN,
                        parameters);

                    return (result ?? []).ToList();
                }
                else
                {
                    int totalCount = await _QueryExecutor.ExecuteScalarAsync<int>(
                        LoginDTO,
                        TLeaveQB.GET_TOTAL_LEAVE_TAKEN_COUNT,
                        parameters);

                    return [new TotalTakenLeaveDTO { TotalCount = totalCount }];
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static DateTime FromEpoch(long epoch) =>
            epoch > 9_999_999_999L
                ? DateTimeOffset.FromUnixTimeMilliseconds(epoch).UtcDateTime
                : DateTimeOffset.FromUnixTimeSeconds(epoch).UtcDateTime;

        // Helper method for integer conversion
        private int GetIntValue(object value)
        {
            if (value == null) return -1;

            if (value is JsonElement element)
            {
                if (element.ValueKind == JsonValueKind.Number)
                    return element.GetInt32();

                if (element.ValueKind == JsonValueKind.String &&
                    int.TryParse(element.GetString(), out int result))
                    return result;
            }

            try
            {
                return Convert.ToInt32(value);
            }
            catch
            {
                return -1;
            }
        }

        // Helper method for string conversion
        private string GetStringValue(object value)
        {
            if (value == null) return "";

            if (value is JsonElement element)
            {
                if (element.ValueKind == JsonValueKind.String)
                    return element.GetString() ?? "";

                return element.ToString();
            }

            return Convert.ToString(value) ?? "";
        }
    }
}