using CRMDAL.DTO.Task;
using CRMDAL.Query.Task;
using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;

namespace CRMDAL.CustomCode.Task
{
    public class TaskDAL : ITaskDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public TaskDAL(IQueryExecutor QueryExecutor)
        {
            _QueryExecutor = QueryExecutor;
        }

        public async Task<string> GetSelectListTask(int FirstNumber, int MaxResult, CriteriaDTO criteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string json = "";

                if (!IsCount)
                {
                    string sql = TaskQB.GetSelectList_Task;
                    var parameters = new DynamicParameters();
                    parameters.Add("firstnumber", FirstNumber);
                    parameters.Add("maxresult",   MaxResult);

                    ApplySelectListTaskCriteria(ref sql, parameters, criteriaDTO,              suffix: "");
                    ApplySelectListTaskCriteria(ref sql, parameters, LoginDTO.UserCriteriaDTO, suffix: "_u");

                    sql = sql.Replace("{CRITERIA}", "");

                    List<TaskPicklistDTO> list = (await _QueryExecutor.QueryAsync<TaskPicklistDTO>(LoginDTO, sql, parameters)).ToList();
                    json = JsonConvert.SerializeObject(list);
                }
                else
                {
                    string countSql = TaskQB.GetSelectList_Task_Count;
                    var countParameters = new DynamicParameters();

                    ApplySelectListTaskCriteria(ref countSql, countParameters, criteriaDTO,              suffix: "");
                    ApplySelectListTaskCriteria(ref countSql, countParameters, LoginDTO.UserCriteriaDTO, suffix: "_u");

                    countSql = countSql.Replace("{CRITERIA}", "");

                    int count = await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, countSql, countParameters);
                    json = count.ToString();
                }

                return json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        // GB4: ApplyTasksCriteira — maps CriteriaDTO FieldName → TTASK column, respecting
        // OperationType (Equal/NotEqual/Like/In/etc.) and JoinType (And/Or grouping).
        // Called twice: user CriteriaDTO (suffix="") and LoginDTO.UserCriteriaDTO (suffix="_u").
        private static void ApplySelectListTaskCriteria(
            ref string sql, DynamicParameters parameters, CriteriaDTO? criteriaDTO, string suffix)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return;

            // Step 1: resolve each attribute to (conditionSQL, paramName, value, joinType)
            var conditions = new List<(string Cond, string Param, object Val, CriteriaDTO.AttributeJoinOperationType Join)>();

            int idx = 0;
            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                foreach (var attr in section.AttributesCriteriaList)
                {
                    string? fieldValue = attr.FieldValue?.ToString();
                    if (string.IsNullOrEmpty(fieldValue)) continue;

                    string? column = GetTaskColumnForField(attr.FieldName.ToLower());
                    if (column == null) continue;

                    string paramName = $"{attr.FieldName.ToLower().Replace(".", "_")}{suffix}_{idx++}";

                    // In/NotIn with populated InArray — Dapper expands IEnumerable to SQL IN list
                    if ((attr.OperationType == CriteriaDTO.OperationType.In ||
                         attr.OperationType == CriteriaDTO.OperationType.NotIn)
                        && attr.InArray?.Length > 0)
                    {
                        string op = attr.OperationType == CriteriaDTO.OperationType.In ? "IN" : "NOT IN";
                        var vals = attr.InArray.Select(v => Convert.ToInt32(v?.ToString())).ToList();
                        conditions.Add(($"{column} {op} @{paramName}", paramName, vals, attr.JoinType));
                        continue;
                    }

                    var (condSql, condVal) = BuildTaskCondition(column, paramName, attr.OperationType, fieldValue);
                    if (condSql == null) continue;

                    conditions.Add((condSql, paramName, condVal, attr.JoinType));
                }
            }

            // Step 2: apply conditions, collapsing consecutive Or-joined conditions into AND (...) groups
            int i = 0;
            while (i < conditions.Count)
            {
                var (cond, pname, pval, joinType) = conditions[i];

                if (joinType == CriteriaDTO.AttributeJoinOperationType.Or)
                {
                    // Collect this + all following conditions that complete the OR group
                    var orParts = new List<string> { cond };
                    parameters.Add(pname, pval);
                    i++;
                    while (i < conditions.Count)
                    {
                        var (nc, np, nv, nj) = conditions[i];
                        parameters.Add(np, nv);
                        orParts.Add(nc);
                        i++;
                        // A non-Or JoinType closes the group (None=0 or And=2 are both terminators)
                        if (nj != CriteriaDTO.AttributeJoinOperationType.Or) break;
                    }
                    sql = sql.Replace("{CRITERIA}", $" AND ({string.Join(" OR ", orParts)}) {{CRITERIA}}");
                }
                else
                {
                    parameters.Add(pname, pval);
                    sql = sql.Replace("{CRITERIA}", $" AND {cond} {{CRITERIA}}");
                    i++;
                }
            }
        }

        // Maps CriteriaDTO FieldName (case-insensitive, supports dotted NHibernate navigation paths) to TTASK column
        private static string? GetTaskColumnForField(string fieldName) => fieldName switch
        {
            "taskoriginalstatus" or "taskstatus"                                            => "TT.TASKSTATUS",
            "detailtype"                                                                    => "TT.DETAILTYPE",
            "status"                                                                        => "TT.STATUS",
            "incharge.id" or "inchargeid"                                                   => "TT.INCHARGEID",
            "assignedtoemployee.id" or "assignedtoemployeeid" or
            "employeeid" or "person.id" or "personid"                                       => "TT.ASSIGNEDTOEMPLOYEEID",
            "parenttask.id" or "parenttaskid"                                               => "TT.PARENTTASKID",
            "biztransactiontype.id" or "biztransactiontypeid"                               => "TT.BIZTRANSACTIONTYPEID",
            "ouid" or "ou.id"                                                               => "TT.OUID",
            "istodo"                                                                        => "TT.ISTODO",
            "leadid"                                                                        => "TT.LEADID",
            "allocationid"                                                                  => "TT.ALLOCATIONID",
            "number"                                                                        => "TT.TASKNUMBER",
            "taskname"                                                                      => "TT.TASKNAME",
            _                                                                               => null
        };

        private static readonly HashSet<string> _textTaskColumns =
            new(StringComparer.OrdinalIgnoreCase) { "TT.TASKNUMBER", "TT.TASKNAME" };

        // Builds the SQL condition fragment and the typed parameter value for a single attribute
        private static (string? Cond, object Val) BuildTaskCondition(
            string column, string paramName,
            CriteriaDTO.OperationType opType, string fieldValue)
        {
            bool isText = _textTaskColumns.Contains(column);
            // Defer int conversion — only called when the column is numeric and the operation needs a number.
            // Avoids FormatException when a text column carries a string value like "Ge".
            object asVal = isText ? (object)fieldValue : (object)Convert.ToInt32(fieldValue);

            return opType switch
            {
                CriteriaDTO.OperationType.Equal    or
                CriteriaDTO.OperationType.In       => ($"{column} = @{paramName}",   asVal),
                CriteriaDTO.OperationType.NotEqual or
                CriteriaDTO.OperationType.NotIn    => ($"{column} <> @{paramName}",  asVal),
                CriteriaDTO.OperationType.Like      => ($"{column} LIKE @{paramName}", (object)$"%{fieldValue}%"),
                CriteriaDTO.OperationType.StartWith => ($"{column} LIKE @{paramName}", (object)$"{fieldValue}%"),
                CriteriaDTO.OperationType.EndsWith  => ($"{column} LIKE @{paramName}", (object)$"%{fieldValue}"),
                CriteriaDTO.OperationType.GreaterThan          => ($"{column} > @{paramName}",  asVal),
                CriteriaDTO.OperationType.LessThan             => ($"{column} < @{paramName}",  asVal),
                CriteriaDTO.OperationType.GreaterThanOrEqualTo => ($"{column} >= @{paramName}", asVal),
                CriteriaDTO.OperationType.LessThanOrEqualTo    => ($"{column} <= @{paramName}", asVal),
                _ => (null, (object)0)
            };
        }

            public async Task<string> GetTask(int TaskId, LoginDTO LoginDTO)
            {
            string Json = "";
            try
            {
                string sql = TaskQB.GET_TASK;
                var parameters = new { Taskid = TaskId };
                TaskDTO TaskDTOs = await _QueryExecutor.QuerySingleAsync<TaskDTO>(LoginDTO, sql, parameters);
                Json = JsonConvert.SerializeObject(TaskDTOs);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Json = null;
            }
        }



        public async Task<int> SaveTask(TaskDTO TaskDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            try
            {
                TaskDTO.TaskCreatedOn  = DateTimeOffset.UtcNow;
                TaskDTO.TaskModifiedOn = DateTimeOffset.UtcNow;
                const string Sql = TaskQB.SAVE_TASK;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, TaskDTO, DbTransaction);
            }
            catch (Exception ex)
            {
                throw new Exception("Error while saving Task", ex);
            }
        }

        public async Task<int> UpdateTask(TaskDTO TaskDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            try
            {
                TaskDTO.TaskModifiedOn = DateTimeOffset.UtcNow;

                if (!TaskQB.ASSIGNED_TO_COLUMN_BY_TYPE.TryGetValue(TaskDTO.TaskAssignedToType, out var assignedToColumn))
                {
                    throw new Exception($"Unsupported TaskAssignedToType: {TaskDTO.TaskAssignedToType}");
                }

                string assignedToValueParam = assignedToColumn switch
                {
                    "ASSIGNEDTOROLEID" => "@AssignedToRoleId",
                    "ASSIGNEDTOUSERID" => "@AssignedToUserId",
                    "ASSIGNEDTOUSERGROUPID" => "@AssignedToUserGroupId",
                    "ASSIGNEDTOEMPLOYEEID" => "@AssignedToEmployeeId",
                    "ASSIGNEDTOWORKTYPEID" => "@AssignedToWorkTypeId",
                    "ASSIGNEDTOCONTACTID" => "@AssignedToContactId",
                    "PARTYBRANCHID" => "@PartyBranchId",
                    _ => throw new Exception($"Unmapped AssignedTo column: {assignedToColumn}"),
                };

                string sql = TaskQB.UPDATE_TASK.Replace(
                    "{AssignedToColumnAssignment}",
                    $"{assignedToColumn} = {assignedToValueParam},");

                return await _QueryExecutor.ExecuteAsync(LoginDTO, sql, TaskDTO, DbTransaction);
            }
            catch (Exception ex)
            {
                throw new Exception("Error while update Task", ex);
            }
        }

        public async Task<string> DeleteTask(int TaskId, LoginDTO LoginDTO, DbTransaction? dbTransaction = null)
        {
            try
            {
                const string Sql = TaskQB.DELETE_TASK;
                var parameters = new { taskid = TaskId };
                var result = await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, parameters, dbTransaction);
                return result > 0 ? "Task deleted successfully." : "Task not found.";
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<IEnumerable<ParenTaskValidationDTO>> GetParentTaskPlannedHours(int ParentTaskId, LoginDTO LoginDTO)
        {
            try
            {
                const string Sql = TaskQB.HOURS_VALIDATION_FOR_PARENTTASK;
                var parameters = new { parenttaskid = ParentTaskId };
                return await _QueryExecutor.QueryAsync<ParenTaskValidationDTO>(LoginDTO, Sql, parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<IEnumerable<TaskPicklistDTO>> GetMilestonePicklist(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string sql = TaskQB.GET_MILESTONE_PICKLIST;
                var parameters = new DynamicParameters();
                ApplyTaskPicklistCriteria(ref sql, parameters, CriteriaDTO);
                return await _QueryExecutor.QueryAsync<TaskPicklistDTO>(LoginDTO, sql, parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<IEnumerable<TaskPicklistDTO>> GetHeadingPicklist(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string sql = TaskQB.GET_HEADING_PICKLIST;
                var parameters = new DynamicParameters();
                ApplyTaskPicklistCriteria(ref sql, parameters, CriteriaDTO);
                return await _QueryExecutor.QueryAsync<TaskPicklistDTO>(LoginDTO, sql, parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<IEnumerable<TaskPicklistDTO>> GetRollupPicklist(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string sql = TaskQB.GET_ROLLUP_PICKLIST;
                var parameters = new DynamicParameters();
                ApplyTaskPicklistCriteria(ref sql, parameters, CriteriaDTO);
                return await _QueryExecutor.QueryAsync<TaskPicklistDTO>(LoginDTO, sql, parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<IEnumerable<TaskPicklistDTO>> GetWorkPicklist(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string sql = TaskQB.GET_WORK_PICKLIST;
                var parameters = new DynamicParameters();
                ApplyTaskPicklistCriteria(ref sql, parameters, CriteriaDTO);
                return await _QueryExecutor.QueryAsync<TaskPicklistDTO>(LoginDTO, sql, parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<IEnumerable<TaskPicklistDTO>> GetParentPicklist(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string sql = TaskQB.GET_PARENT_PICKLIST;
                var parameters = new DynamicParameters();
                ApplyTaskPicklistCriteria(ref sql, parameters, CriteriaDTO);
                return await _QueryExecutor.QueryAsync<TaskPicklistDTO>(LoginDTO, sql, parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // GB4 parity: TaskDAL.GetMilestonePicklist/GetHeadingPicklist — shared optional filters
        // (AllocationId, TaskId, TaskNumber/TaskName, TaskDescription, AssigneeId, ParentTaskId, InChargeId)
        private static void ApplyTaskPicklistCriteria(ref string sql, DynamicParameters parameters, CriteriaDTO? criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return;

            string allocationId = "", taskId = "", taskNumber = "", taskName = "",
                   taskDescription = "", assigneeId = "", parentTaskId = "", inChargeId = "";

            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                foreach (var attr in section.AttributesCriteriaList)
                {
                    string value = attr.FieldValue?.ToString() ?? "";
                    switch (attr.FieldName?.ToLower())
                    {
                        case "allocationid": allocationId = value; break;
                        case "taskid": taskId = value; break;
                        case "tasknumber": taskNumber = value; break;
                        case "taskname": taskName = value; break;
                        case "taskdescription": taskDescription = value; break;
                        case "assigneeid": assigneeId = value; break;
                        case "parenttaskid": parentTaskId = value; break;
                        case "inchargeid": inChargeId = value; break;
                    }
                }
            }

            if (allocationId != "")
            {
                sql += " AND CAST(TT.ALLOCATIONID AS VARCHAR(20)) LIKE @AllocationId";
                parameters.Add("AllocationId", $"%{allocationId}%");
            }
            if (taskId != "")
            {
                sql += " AND CAST(TT.TASKID AS VARCHAR(20)) LIKE @TaskId";
                parameters.Add("TaskId", $"%{taskId}%");
            }
            if (taskNumber != "" || taskName != "")
            {
                sql += " AND (TT.TASKNUMBER LIKE @TaskNumberOr OR TT.TASKNAME LIKE @TaskNameOr)";
                parameters.Add("TaskNumberOr", $"%{taskNumber}%");
                parameters.Add("TaskNameOr", $"%{taskName}%");
            }
            if (taskDescription != "")
            {
                sql += " AND TT.TASKDESCRIPTION LIKE @TaskDescription";
                parameters.Add("TaskDescription", $"%{taskDescription}%");
            }
            if (assigneeId != "")
            {
                sql += " AND CAST(TT.ASSIGNEDTOEMPLOYEEID AS VARCHAR(20)) LIKE @AssigneeId";
                parameters.Add("AssigneeId", $"%{assigneeId}%");
            }
            if (parentTaskId != "")
            {
                sql += " AND CAST(TT.PARENTTASKID AS VARCHAR(20)) LIKE @ParentTaskId";
                parameters.Add("ParentTaskId", $"%{parentTaskId}%");
            }
            if (inChargeId != "")
            {
                sql += " AND CAST(TT.INCHARGEID AS VARCHAR(20)) LIKE @InChargeId";
                parameters.Add("InChargeId", $"%{inChargeId}%");
            }
        }

        public async Task<IEnumerable<TaskQueryDTO>> GetTaskQuery(byte TaskType, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string sql = TaskQB.GET_TASK_ASSETACTIVITY_FOR_GENERATION;
                var parameters = new DynamicParameters();
                parameters.Add("TaskType", TaskType);
                parameters.Add("OuId", LoginDTO.WorkOUId);

                ApplyTaskQueryCriteria(ref sql, parameters, CriteriaDTO,              suffix: "");
                ApplyTaskQueryCriteria(ref sql, parameters, LoginDTO.UserCriteriaDTO, suffix: "_u");

                sql = sql.Replace("{CRITERIA}", "");

                return await _QueryExecutor.QueryAsync<TaskQueryDTO>(LoginDTO, sql, parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Maps CriteriaDTO field names to the eligibility query's table aliases.
        // Called twice: user CriteriaDTO (suffix="") and LoginDTO.UserCriteriaDTO (suffix="_u").
        private static void ApplyTaskQueryCriteria(
            ref string sql, DynamicParameters parameters, CriteriaDTO? criteriaDTO, string suffix)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return;

            int idx = 0;
            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                foreach (var attr in section.AttributesCriteriaList)
                {
                    string? fieldValue = attr.FieldValue?.ToString();
                    if (string.IsNullOrEmpty(fieldValue)) continue;

                    string? column = attr.FieldName.ToLower() switch
                    {
                        "activityid"                              => "activity.ACTIVITYID",
                        "assettypeid"                              => "asset.ASSETTYPEID",
                        "assetid"                                  => "assetActivity.ASSETID",
                        "partybranchid"                            => "asset.PARTYBRANCHID",
                        "machinetypeid"                            => "machine.MACHINETYPEID",
                        "machineid"                                => "assetActivity.MACHINEID",
                        "resourceid"                               => "assetActivity.RESOURCEID",
                        "resourcetype.id" or "resourcetypeid"      => "resourcename.RESOURCETYPEID",
                        "employeeid"                               => "assetActivity.EMPLOYEEID",
                        _                                          => null
                    };
                    if (column == null) continue;

                    // "tasktype" is consumed separately as the fixed @TaskType/@OuId scoping
                    // predicate — never treated as a generic equality filter here.
                    if (attr.FieldName.Equals("tasktype", StringComparison.OrdinalIgnoreCase)) continue;

                    string paramName = $"{attr.FieldName.ToLower().Replace(".", "_")}{suffix}_{idx++}";
                    parameters.Add(paramName, Convert.ToInt32(fieldValue));
                    sql = sql.Replace("{CRITERIA}", $" AND {column} = @{paramName} {{CRITERIA}}");
                }
            }
        }

        public async Task<int?> GetBizTransactionTypeForTaskGeneration(int BizTransactionClassId, LoginDTO LoginDTO)
        {
            try
            {
                var parameters = new
                {
                    BizTransactionClassId,
                    OuId     = LoginDTO.WorkOUId,
                    PeriodId = LoginDTO.WorkPeriodId
                };

                var results = (await _QueryExecutor.QueryAsync<int>(
                    LoginDTO, TaskQB.GET_BIZTRANSACTIONTYPE_FOR_TASK_GENERATION, parameters)).ToList();

                return results.Count > 0 ? results[0] : (int?)null;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async System.Threading.Tasks.Task UpdateAssetActivityLastGeneratedTill(int AssetActivityId, DateOnly LastGeneratedTill, LoginDTO LoginDTO)
        {
            try
            {
                var parameters = new { AssetActivityId, LastGeneratedTill };
                await _QueryExecutor.ExecuteAsync(LoginDTO, TaskQB.UPDATE_ASSETACTIVITY_LASTGENERATEDTILL, parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<IEnumerable<TaskListsDTO>> GetTaskListOnActivity(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                int dateValidation = 0;
                string actionDate = "";
                int essValidation = 0;
                string assignedToEmployeeId = "";
                string assignedToContactId = "";
                string assignedToUserId = "";

                if (CriteriaDTO?.SectionCriteriaList?.Count > 0)
                {
                    foreach (var attr in CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList)
                    {
                        string field = attr.FieldName.ToLower();
                        string fieldValue = attr.FieldValue?.ToString() ?? string.Empty;

                        if (field == "datevalidation")
                        {
                            dateValidation = Convert.ToInt16(fieldValue);
                        }
                        else if (field == "actiondate")
                        {
                            actionDate = ParseDateValue(fieldValue).ToString("yyyy/MM/dd");
                        }
                        else if (field == "essvalidation")
                        {
                            essValidation = Convert.ToInt16(fieldValue);
                        }
                        else if (field == "assignedtoemployeeid")
                        {
                            assignedToEmployeeId = fieldValue;
                        }
                        else if (field == "assignedtocontactid")
                        {
                            assignedToContactId = fieldValue;
                        }
                        else if (field == "assignedtouserid")
                        {
                            assignedToUserId = fieldValue;
                        }
                    }
                }

                string replaceFields = dateValidation == 0
                    ? "and task.TASKDATE <= GETDATE()"
                    : "";

                string sql = TaskQB.GET_TASK_LIST_ON_ACTIVITY
                    .Replace("{ReplaceFields}", replaceFields);

                if (essValidation == 0)
                    sql += " AND CAST(@actiondate AS DATE) <= task.EXPIREON";

                if (!string.IsNullOrEmpty(assignedToEmployeeId))
                    sql += " AND task.ASSIGNEDTOEMPLOYEEID IN @assignedtoemployeeid";

                if (!string.IsNullOrEmpty(assignedToContactId))
                    sql += " AND task.ASSIGNEDTOCONTACTID IN @assignedtocontactid";

                if (!string.IsNullOrEmpty(assignedToUserId))
                    sql += " AND task.ASSIGNEDTOUSERID IN @assignedtouserid";

                var parameters = new DynamicParameters();

                parameters.Add("actiondate", actionDate);
                parameters.Add("userid", LoginDTO.UserId);
                if (!string.IsNullOrEmpty(assignedToEmployeeId))
                    parameters.Add("assignedtoemployeeid", ParseIntList(assignedToEmployeeId));
                if (!string.IsNullOrEmpty(assignedToContactId))
                    parameters.Add("assignedtocontactid", ParseIntList(assignedToContactId));
                if (!string.IsNullOrEmpty(assignedToUserId))
                    parameters.Add("assignedtouserid", ParseIntList(assignedToUserId));

                // User criteria
                ApplyTaskCriteria(ref sql, parameters, CriteriaDTO, "");

                // Login restrictions
                ApplyTaskCriteria(ref sql, parameters, LoginDTO.UserCriteriaDTO, "_u");

                return await _QueryExecutor.QueryAsync<TaskListsDTO>(
                    LoginDTO,
                    sql,
                    parameters);
            }
            catch
            {
                throw;
            }
        }

        private static DateTime ParseDateValue(string value)
        {
            if (string.IsNullOrWhiteSpace(value))
                throw new ArgumentException("Date value cannot be empty.");

            // New format: yyyy-MM-dd
            if (DateTime.TryParse(value, out DateTime dateTime))
                return dateTime;

            // Old GB4 format: Unix epoch. GB4's CommonFunctionDAL.FromEpoch always
            // treats this as SECONDS (AddSeconds), not milliseconds — matched here
            // via the same digit-length heuristic as ParseDateValueForReport so a
            // 13-digit ms value (if ever sent) still resolves correctly too.
            if (long.TryParse(value, out long unixTime))
            {
                return value.Length >= 13
                    ? DateTimeOffset.FromUnixTimeMilliseconds(unixTime).UtcDateTime
                    : DateTimeOffset.FromUnixTimeSeconds(unixTime).UtcDateTime;
            }

            throw new ArgumentException($"Invalid date format: {value}");
        }

        private static void ApplyTaskCriteria(
            ref string sql,
            DynamicParameters parameters,
            CriteriaDTO? criteriaDTO,
            string suffix)
        {
            if (criteriaDTO?.SectionCriteriaList == null)
                return;

            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                foreach (var attr in section.AttributesCriteriaList)
                {
                    string? fieldValue = attr.FieldValue?.ToString();

                    if (string.IsNullOrWhiteSpace(fieldValue))
                        continue;

                    string paramName;

                    switch (attr.FieldName.ToLower())
                    {
                        case "inchargeid":
                            paramName = $"inchargeid{suffix}";
                            sql += $" AND task.INCHARGEID {GetOperator(attr.OperationType)} @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "periodfromdate":
                            paramName = $"periodfromdate{suffix}";
                            sql += $" AND task.PLANSTARTON {GetOperator(attr.OperationType)} @{paramName}";
                            parameters.Add(paramName, ParseDateValue(fieldValue));
                            break;

                        case "periodtodate":
                            paramName = $"periodtodate{suffix}";
                            sql += $" AND task.PLANENDON {GetOperator(attr.OperationType)} @{paramName}";
                            parameters.Add(paramName, ParseDateValue(fieldValue));
                            break;

                        case "taskid":
                            paramName = $"taskid{suffix}";
                            sql += $" AND task.TASKID {GetOperator(attr.OperationType)} @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "parenttaskid":
                            paramName = $"parenttaskid{suffix}";
                            sql += $" AND task.PARENTTASKID {GetOperator(attr.OperationType)} @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;
                    }
                }
            }
        }

        private static string GetOperator(CriteriaDTO.OperationType operationType) => operationType switch
        {
            CriteriaDTO.OperationType.GreaterThan => ">",
            CriteriaDTO.OperationType.GreaterThanOrEqualTo => ">=",
            CriteriaDTO.OperationType.LessThan => "<",
            CriteriaDTO.OperationType.LessThanOrEqualTo => "<=",
            CriteriaDTO.OperationType.NotEqual => "<>",
            _ => "="
        };

        public async Task<string> GetTaskReport(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string json = "";

                string searchText = "";
                int previousPending = -1;
                int isClosed = 1;
                string periodFromDate = "";
                string periodToDate = "";
                int isReport = 1;
                int isMail = 1;
                string parentTaskId = "";
                int overdueRequired = 1;
                string orderBy = "0";
                string partyBranchId = "";
                string reportOrderBy = "0";
                int fromMinutes = 0;
                int toMinutes = 0;
                int noOfDays = 0;
                int featureStatus = 1;
                int taskStatusForDisplay = -1;
                int taskListCallFromMobileApp = 1;
                string actionRequired = "1";
                string actionFromReplace = "";
                string actionSelectReplace = "";

                DateOnly fromDt = DateOnly.FromDateTime(DateTime.Now);
                DateOnly toDt = DateOnly.FromDateTime(DateTime.Now);

                if (CriteriaDTO != null)
                {
                    foreach (var section in CriteriaDTO.SectionCriteriaList ?? [])
                    {
                        foreach (var attribute in section.AttributesCriteriaList ?? [])
                        {
                            switch (attribute.FieldName?.ToLower())
                            {
                                case "searchtext":
                                    searchText = Convert.ToString(attribute.FieldValue) ?? "";
                                    break;

                                case "previouspending":
                                    previousPending = Convert.ToInt32(attribute.FieldValue?.ToString());
                                    break;

                                case "periodfromdate":
                                    fromDt = ParseDateValueForReport(attribute.FieldValue?.ToString() ?? "");
                                    periodFromDate = fromDt.ToString("dd-MMM-yyyy");
                                    break;

                                case "periodtodate":
                                    toDt = ParseDateValueForReport(attribute.FieldValue?.ToString() ?? "");
                                    periodToDate = toDt.ToString("dd-MMM-yyyy");
                                    break;

                                case "fromtime":
                                    {
                                        string fromTime = Convert.ToString(attribute.FieldValue) ?? "";
                                        string[] temp = fromTime.Split(':');
                                        if (temp.Length >= 2)
                                        {
                                            fromMinutes = Convert.ToInt32(temp[0]) * 60 + Convert.ToInt32(temp[1]);
                                        }
                                    }
                                    break;

                                case "totime":
                                    {
                                        string toTime = Convert.ToString(attribute.FieldValue) ?? "";
                                        string[] temp = toTime.Split(':');
                                        if (temp.Length >= 2)
                                        {
                                            toMinutes = Convert.ToInt32(temp[0]) * 60 + Convert.ToInt32(temp[1]);
                                        }
                                    }
                                    break;

                                case "isreport":
                                    isReport = Convert.ToInt32(attribute.FieldValue?.ToString());
                                    break;

                                case "ismail":
                                    isMail = Convert.ToInt32(attribute.FieldValue?.ToString());
                                    break;

                                case "overduerequired":
                                    overdueRequired = Convert.ToInt32(attribute.FieldValue?.ToString());
                                    break;

                                case "parenttaskid":
                                    parentTaskId = Convert.ToString(attribute.FieldValue) ?? "";
                                    break;

                                case "orderby":
                                    orderBy = Convert.ToString(attribute.FieldValue) ?? "0";
                                    break;

                                case "reportorderby":
                                    reportOrderBy = Convert.ToString(attribute.FieldValue) ?? "0";
                                    break;

                                case "partybranchid":
                                    partyBranchId = Convert.ToString(attribute.FieldValue) ?? "";
                                    break;

                                case "tasklistcallfrommobileapp":
                                    taskListCallFromMobileApp = Convert.ToInt32(attribute.FieldValue?.ToString());
                                    break;

                                case "noofdays":
                                    noOfDays = Convert.ToInt32(attribute.FieldValue?.ToString());
                                    break;

                                case "featurestatus":
                                    featureStatus = Convert.ToInt32(attribute.FieldValue?.ToString());
                                    break;

                                case "taskstatusfordisplay":
                                    taskStatusForDisplay = Convert.ToInt32(attribute.FieldValue?.ToString());
                                    break;

                                case "isclosed":
                                    isClosed = Convert.ToInt32(attribute.FieldValue?.ToString());
                                    break;

                                case "taskstatus":
                                    {
                                        string value = Convert.ToString(attribute.FieldValue) ?? "";
                                        if (value == "7")
                                        {
                                            attribute.OperationType = CriteriaDTO.OperationType.NotIn;
                                        }
                                    }
                                    break;

                                case "actionrequired":
                                    actionRequired = Convert.ToString(attribute.FieldValue) ?? "1";
                                    break;

                                case "actionfromreplace":
                                    actionFromReplace = Convert.ToString(attribute.FieldValue) ?? "";
                                    break;

                                case "actionselectreplace":
                                    actionSelectReplace = Convert.ToString(attribute.FieldValue) ?? "";
                                    break;
                            }
                        }
                    }
                }

                // No time-of-day combination needed here: with DateOnly-typed
                // PeriodFromDate/PeriodToDate, a "<= toDate" comparison already
                // covers the entire day, so the old totime/AddMinutes cutoff
                // (needed only to work around DATETIME's partial-day exclusion)
                // no longer applies.

                bool isMobileCall = taskListCallFromMobileApp == 0;
                string sql;

                if (isMobileCall)
                {
                    if (taskStatusForDisplay == -1)
                    {
                        sql = TaskQB.GET_TASKLIST_FOR_MOBILE_APP;
                    }
                    else
                    {
                        sql = TaskQB.GET_TASKLIST_FOR_MOBILE_APP_WITH_STATUS;
                    }
                }
                else if (IsCount)
                {
                    sql = TaskQB.GET_TASK_REPORT_COUNT;
                }
                else
                {
                    sql = TaskQB.GET_TASK_REPORT;
                }

                ApplyActionReplacements(ref sql, actionRequired);
                var parameters = new DynamicParameters();

                ApplyTaskReportCriteria(ref sql, parameters, CriteriaDTO, "");
                ApplyTaskReportCriteria(ref sql, parameters, LoginDTO.UserCriteriaDTO, "_user");
                ApplyPreviousPendingFilters(ref sql, parameters, previousPending, featureStatus, noOfDays, periodFromDate, periodToDate);
                ApplyMobileTaskFilters(ref sql, parameters, isMobileCall, taskStatusForDisplay, periodFromDate, periodToDate,orderBy, LoginDTO.WorkOUId);

                if (!isMobileCall)
                {
                    if (previousPending == -1)
                    {
                        if (!string.IsNullOrWhiteSpace(periodFromDate))
                        {
                            sql += " AND task.PLANSTARTON >= @PeriodFromDate";
                            parameters.Add("PeriodFromDate", DateOnly.Parse(periodFromDate));
                        }
                        if (!string.IsNullOrWhiteSpace(periodToDate))
                        {
                            sql += " AND task.PLANENDON <= @PeriodToDate";
                            parameters.Add("PeriodToDate", DateOnly.Parse(periodToDate));
                        }
                    }

                    if (isClosed == 0)
                    {
                        sql += " AND task.TaskStatus IN (0,1,2,3,4,5,6)";
                    }
                    else
                    {
                        sql += " AND task.TaskStatus IN (0,1,2,3,5,6)";
                    }

                    if (overdueRequired == 0 && !string.IsNullOrWhiteSpace(periodToDate))
                    {
                        sql += " AND task.PLANENDON < @OverduePeriodToDate";
                        parameters.Add("OverduePeriodToDate", DateOnly.Parse(periodToDate));
                    }

                    if (featureStatus != 1 && !string.IsNullOrWhiteSpace(periodToDate))
                    {
                        sql += @"
                                AND (
                                    task.PLANSTARTON < @FeaturePeriodToDate OR task.PLANSTARTON <= DATEADD(DAY,@FeatureNoOfDays,@FeaturePeriodToDate)
                                )";
                        parameters.Add("FeaturePeriodToDate", DateOnly.Parse(periodToDate));
                        parameters.Add("FeatureNoOfDays", noOfDays);
                    }

                    if (!string.IsNullOrWhiteSpace(searchText))
                    {
                        sql += @"
                                AND (
                                    task.TASKNUMBER LIKE @SearchText OR task.TASKNAME LIKE @SearchText OR task.TASKDESCRIPTION LIKE @SearchText
                                    OR employee.EMPLOYEENAME LIKE @SearchText OR employee.EMPLOYEECODE LIKE @SearchText
                                    OR assignemp.EMPLOYEENAME LIKE @SearchText OR assignemp.EMPLOYEECODE LIKE @SearchText
                                    OR malloc.ALLOCATIONNAME LIKE @SearchText
                                    OR mactivity.ACTIVITYNAME LIKE @SearchText OR mactivity.ACTIVITYCODE LIKE @SearchText
                                    OR mas.ASSETNAME LIKE @SearchText OR mas.ASSETCODE LIKE @SearchText
                                    OR mpb.PARTYBRANCHNAME LIKE @SearchText OR mpb.PARTYBRANCHCODE LIKE @SearchText
                                    OR parent.TASKNAME LIKE @SearchText OR parent.TASKNUMBER LIKE @SearchText
                                )";
                        parameters.Add("SearchText", $"%{searchText}%");
                    }
                }

                if (!IsCount)
                {
                    if (isMobileCall)
                    {
                        if (orderBy == "3")
                        {
                            sql += " ORDER BY task.PLANSTARTON";
                        }
                        else
                        {
                            sql += " ORDER BY task.TASKID";
                        }
                    }
                    else
                    {
                        ApplyOrderBy(ref sql, orderBy, searchText, IsCount);
                    }
                    sql += @"
                            OFFSET CASE WHEN @firstnumber = -1 THEN 0 ELSE @firstnumber END ROWS
                            FETCH NEXT
                            CASE WHEN @maxresult = -1
                                 THEN 2147483647
                                 ELSE @maxresult
                            END ROWS ONLY";
                    parameters.Add("firstnumber", FirstNumber);
                    parameters.Add("maxresult", MaxResult);
                }

                if (IsCount)
                {
                    return (await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, sql, parameters)).ToString();
                }

                var taskReports = (await _QueryExecutor.QueryAsync<TaskReportDTO>(LoginDTO, sql, parameters)).ToList() ?? [];

                if (isReport == 1)
                {
                    // Matches GB4 (TaskDAL.GetTaskReport): the Activiti workflow merge was disabled
                    // in 2019 as "not required" and always ran against an empty list, so the merge
                    // never executed. Kept as a no-op here for parity instead of the live DB round-trip.
                    var workflowTasks = new List<TaskReportDTO>();
                    if (workflowTasks.Count > 0)
                    {
                        foreach (var task in taskReports)
                        {
                            var workflowTask = workflowTasks.FirstOrDefault(x => x.ObjectId == task.TaskId.ToString());
                            if (workflowTask != null)
                            {
                                task.ProcessInstanceId = workflowTask.ProcessInstanceId;
                                task.DisplayValue = workflowTask.DisplayValue;
                                task.FromUserCode = workflowTask.FromUserCode;
                                task.InchargeMail = workflowTask.InchargeMail;
                                task.EntityCode = workflowTask.EntityCode;
                                task.DocumentNumber = workflowTask.DocumentNumber;
                                task.ObjectId = workflowTask.ObjectId;
                                task.ActivitiStatus = 0;
                            }
                            else
                            {
                                task.ActivitiStatus = 1;
                                task.ProcessInstanceId = "-1";
                            }
                        }
                    }
                }

                foreach (var item in taskReports)
                {
                    item.CallTotalOriginalPlannedHours = MinutesToTime(item.TotalOriginalPlannedHours);
                    item.CallTotalTaskActualHours = MinutesToTime(item.TotalTaskActualHours);
                    item.CallTotalTaskPlannedHours = MinutesToTime(item.TotalTaskPlannedHours);
                    item.CallOriginalPlannedHours = MinutesToTime(item.OriginalPlannedHours);
                    item.CallTaskActualHours = MinutesToTime(item.TaskActualHours);
                    item.CallTaskPlannedHours = MinutesToTime(item.TaskPlannedHours);
                    item.CallActionDetailDuration = MinutesToTime(item.ActionDetailDuration);
                }

                json = JsonConvert.SerializeObject(taskReports);
                return json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Returns DateOnly so the calendar date can't drift with the epoch
        // value's incidental UTC time-of-day (PLANSTARTON/PLANENDON/DUEON/
        // EXPIREON are date concepts, not date+time — see ParentTaskDatesDTO's
        // DateOnly fields for the same convention).
        private static DateOnly ParseDateValueForReport(string value)
        {
            if (string.IsNullOrWhiteSpace(value))
                throw new ArgumentException("Date value cannot be empty.");
            if (long.TryParse(value, out long unixTime))
            {
                // 13 digits = milliseconds, 10 digits = seconds — both are UTC instants.
                DateTime utcInstant = value.Length >= 13
                    ? DateTimeOffset.FromUnixTimeMilliseconds(unixTime).UtcDateTime
                    : DateTimeOffset.FromUnixTimeSeconds(unixTime).UtcDateTime;
                return DateOnly.FromDateTime(utcInstant);
            }
            if (DateTime.TryParse(value, out DateTime dateTime))
                return DateOnly.FromDateTime(dateTime);
            throw new ArgumentException($"Invalid date format: {value}");
        }

        // GB4 parity: GB4's generic ApplyCriteria always builds "Equal" fields as
        // IN(value), which works unchanged whether the caller sends one ID or a
        // comma-separated list. Every integer-equality field below mirrors that.
        private static int[] ParseIntList(string fieldValue) =>
            fieldValue.Split(',', StringSplitOptions.RemoveEmptyEntries).Select(x => Convert.ToInt32(x.Trim())).ToArray();

        // GB4 parity: TaskDAL.GetTaskReportCriteria's CriteriaWithQueryField table,
        // fed into the shared CommonFunctionFrameDAL.ApplyCriteria. Every field here
        // is applied dynamically per attr.OperationType (matching ApplyCriteria's
        // Equal/In -> IN(...), NotIn -> NOT IN(...), NotEqual -> <>, GreaterThan(OrEqualTo)
        // -> >(=), LessThan(OrEqualTo) -> <(=), Like -> LIKE '%...%'), not hardcoded to IN.
        private static readonly (string FieldName, string Column)[] TaskReportCriteriaFields =
        [
            ("ouid", "task.ouid"),
            ("inchargeid", "task.INCHARGEID"),
            ("requestedbyid", "task.REQUESTEDBYID"),
            ("taskstatus", "task.TASKSTATUS"),
            ("assetid", "task.ASSETID"),
            ("machineid", "task.MACHINEID"),
            ("resourceid", "task.RESOURCEID"),
            ("callid", "task.CALLID"),
            ("worktype", "task.WORKTYPEID"),
            ("tasknature", "task.TASKNATURE"),
            ("tasktype", "task.DETAILTYPE"),
            ("leadid", "task.LEADID"),
            ("milestoneid", "milestone.TASKID"),
            ("headingid", "head.TASKID"),
            ("workid", "work.TASKID"),
            ("taskid", "task.TASKID"),
            ("rollupid", "rollup.TASKID"),
            ("allocationid", "task.ALLOCATIONID"),
            ("assigneeid", "task.ASSIGNEDTOEMPLOYEEID"),
            ("parenttaskid", "task.PARENTTASKID"),
            ("taskdetailtype", "task.DETAILTYPE"),
            ("periodfrom", "task.PLANSTARTON"),
            ("periodto", "task.PLANSTARTON"),
            ("departmentid", "assignemp.DepartmentId"),
            ("istodo", "task.ISTODO"),
            ("partybranchid", "mas.partybranchid"),
            ("partybranchlocationid", "mas.PARTYBRANCHLOCATIONID"),
            ("activityid", "task.ACTIVITYID"),
            ("assettypeid", "mas.ASSETTYPEID"),
            ("activitytypeid", "mactivitytype.ACTIVITYTYPEID"),
            ("isscore", "mactivity.ISSCORE"),
            ("categoryid", "mas.CATEGORYID"),
            ("period", "asstypeact.PERIOD"),
            ("allocationsplitid", "task.ALLOCATIONSPLITID"),
            ("projectsequence", "task.PROJECTSEQUENCE"),
            ("resourcesequence", "task.RESOURCESEQUENCE"),
        ];

        private static readonly HashSet<string> TaskReportDateCriteriaFields = new(StringComparer.OrdinalIgnoreCase)
        {
            "periodfrom", "periodto"
        };

        private static void ApplyTaskReportCriteria(ref string sql, DynamicParameters parameters, CriteriaDTO? criteriaDTO, string suffix)
        {
            if (criteriaDTO?.SectionCriteriaList == null)
                return;

            int index = 0;
            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                foreach (var attr in section.AttributesCriteriaList)
                {
                    string? fieldValue = attr.FieldValue?.ToString();
                    if (string.IsNullOrWhiteSpace(fieldValue))
                        continue;

                    string? fieldName = attr.FieldName?.ToLower();

                    // taskstatus==7: GB4 parity special case (TaskDAL.GetTaskReport), overrides
                    // the caller's value/operator entirely with a fixed NOT IN (3,4,5).
                    if (fieldName == "taskstatus" && attr.OperationType == CriteriaDTO.OperationType.NotIn)
                    {
                        sql += " AND task.TASKSTATUS NOT IN (3,4,5)";
                        continue;
                    }

                    foreach (var (candidateFieldName, column) in TaskReportCriteriaFields)
                    {
                        if (fieldName != candidateFieldName)
                            continue;

                        string paramName = $"{candidateFieldName}{suffix}_{index++}";
                        bool isDate = TaskReportDateCriteriaFields.Contains(candidateFieldName);

                        ApplyDynamicCriteria(ref sql, parameters, column, paramName, attr.OperationType, fieldValue, isDate);
                        break;
                    }
                }
            }
        }

        // GB4 parity: CommonFunctionFrameDAL.ApplyCriteria's per-OperationType branching.
        private static void ApplyDynamicCriteria(ref string sql, DynamicParameters parameters, string column, string paramName, CriteriaDTO.OperationType operationType, string fieldValue, bool isDate)
        {
            switch (operationType)
            {
                case CriteriaDTO.OperationType.Equal:
                case CriteriaDTO.OperationType.In:
                    if (isDate)
                    {
                        sql += $" AND {column} = @{paramName}";
                        parameters.Add(paramName, ParseDateValue(fieldValue));
                    }
                    else
                    {
                        sql += $" AND {column} IN @{paramName}";
                        parameters.Add(paramName, ParseIntList(fieldValue));
                    }
                    break;

                case CriteriaDTO.OperationType.NotIn:
                    if (isDate)
                    {
                        sql += $" AND {column} <> @{paramName}";
                        parameters.Add(paramName, ParseDateValue(fieldValue));
                    }
                    else
                    {
                        sql += $" AND {column} NOT IN @{paramName}";
                        parameters.Add(paramName, ParseIntList(fieldValue));
                    }
                    break;

                case CriteriaDTO.OperationType.NotEqual:
                    sql += $" AND {column} <> @{paramName}";
                    parameters.Add(paramName, isDate ? ParseDateValue(fieldValue) : Convert.ToInt32(fieldValue));
                    break;

                case CriteriaDTO.OperationType.GreaterThanOrEqualTo:
                    sql += $" AND {column} >= @{paramName}";
                    parameters.Add(paramName, isDate ? ParseDateValue(fieldValue) : Convert.ToInt32(fieldValue));
                    break;

                case CriteriaDTO.OperationType.LessThanOrEqualTo:
                    sql += $" AND {column} <= @{paramName}";
                    parameters.Add(paramName, isDate ? ParseDateValue(fieldValue) : Convert.ToInt32(fieldValue));
                    break;

                case CriteriaDTO.OperationType.GreaterThan:
                    sql += $" AND {column} > @{paramName}";
                    parameters.Add(paramName, isDate ? ParseDateValue(fieldValue) : Convert.ToInt32(fieldValue));
                    break;

                case CriteriaDTO.OperationType.LessThan:
                    sql += $" AND {column} < @{paramName}";
                    parameters.Add(paramName, isDate ? ParseDateValue(fieldValue) : Convert.ToInt32(fieldValue));
                    break;

                case CriteriaDTO.OperationType.Like:
                    if (!isDate)
                    {
                        sql += $" AND {column} LIKE @{paramName}";
                        parameters.Add(paramName, $"%{fieldValue}%");
                    }
                    break;

                default:
                    // GB4 parity: ApplyCriteria emits nothing for None/Or/And/Between/StartWith/EndsWith.
                    break;
            }
        }

        private static string MinutesToTime(int minutes)
        {
            if (minutes <= 0)
                return "00:00";
            int hours = minutes / 60;
            int mins = minutes % 60;
            return $"{hours:D2}:{mins:D2}";
        }

        private static void ApplyPreviousPendingFilters(ref string sql, DynamicParameters parameters, int previousPending, int featureStatus, int noOfDays, string? periodFromDate, string? periodToDate)
        {
            if (string.IsNullOrWhiteSpace(periodFromDate) || string.IsNullOrWhiteSpace(periodToDate))
                return;
            switch (previousPending)
            {
                case 0:
                    sql += @"
                            AND (
                            (
                                task.PLANENDON >= @PrevFromDate AND task.PLANENDON <= @PrevToDate
                            ) OR (
                                task.TASKSTATUS NOT IN (4) AND task.PLANENDON <= @PrevFromDate
                            )";
                    if (featureStatus == 0)
                    {
                        sql += @" 
                                OR task.PLANENDON <= DATEADD(DAY,@NoOfDays,@PrevToDate)";
                    }
                    sql += ")";
                    parameters.Add("PrevFromDate", DateOnly.Parse(periodFromDate));
                    parameters.Add("PrevToDate", DateOnly.Parse(periodToDate));
                    parameters.Add("NoOfDays", noOfDays);
                    break;

                case 1:
                    sql += @"
                            AND (
                                task.PLANENDON >= @PrevFromDate AND task.PLANENDON <= @PrevToDate
                            )";
                    parameters.Add("PrevFromDate", DateOnly.Parse(periodFromDate));
                    parameters.Add("PrevToDate", DateOnly.Parse(periodToDate));
                    break;

                case -1:
                default:
                    break;
            }
        }

        private static void ApplyOrderBy(ref string sql, string orderBy, string searchText, bool isCount)
        {
            if (isCount)
                return;
            if (!string.IsNullOrWhiteSpace(searchText))
                return;

            switch (orderBy)
            {
                case "0":
                    sql += @"
                            ORDER BY
                            task.PROJECTSEQUENCE,
                            CASE
                                WHEN task.assignedtotype = 1 THEN role.ROLECODE
                                WHEN task.assignedtotype = 2 THEN usergroup.USERGROUPCODE
                                WHEN task.assignedtotype = 3 THEN assignuser.usercode
                                WHEN task.assignedtotype = 4 THEN assignemp.EMPLOYEECODE
                                WHEN task.assignedtotype = 5 THEN gcm.GCMCODE
                                WHEN task.assignedtotype = 6 THEN contact.FIRSTNAME
                            END,
                            task.PLANENDON";
                    break;

                case "1":
                    sql += @"
                            ORDER BY
                            task.PROJECTSEQUENCE,
                            CASE
                                WHEN task.assignedtotype = 1 THEN role.ROLECODE
                                WHEN task.assignedtotype = 2 THEN usergroup.USERGROUPCODE
                                WHEN task.assignedtotype = 3 THEN assignuser.usercode
                                WHEN task.assignedtotype = 4 THEN assignemp.EMPLOYEECODE
                                WHEN task.assignedtotype = 5 THEN gcm.GCMCODE
                                WHEN task.assignedtotype = 6 THEN contact.FIRSTNAME
                            END,
                            malloc.ALLOCATIONNAME,
                            task.PLANSTARTON,
                            task.PARENTTASKID";
                    break;

                case "2":
                    sql += @"
                            ORDER BY
                            task.TASKDATE,
                            task.PROJECTSEQUENCE,
                            CASE
                                WHEN task.assignedtotype = 1 THEN role.ROLECODE
                                WHEN task.assignedtotype = 2 THEN usergroup.USERGROUPCODE
                                WHEN task.assignedtotype = 3 THEN assignuser.usercode
                                WHEN task.assignedtotype = 4 THEN assignemp.EMPLOYEECODE
                                WHEN task.assignedtotype = 5 THEN gcm.GCMCODE
                                WHEN task.assignedtotype = 6 THEN contact.FIRSTNAME
                            END,
                            task.PLANSTARTON";
                    break;

                case "3":
                    sql += @" ORDER BY task.PLANSTARTON";
                    break;

                default:
                    sql += @" ORDER BY task.PROJECTSEQUENCE, task.PLANENDON";
                    break;
            }
        }

        private static void ApplyActionReplacements(ref string sql, string actionRequired)
        {
            string actionFromReplace;
            string actionSelectReplace;
            if (actionRequired == "0")
            {
                actionFromReplace = @"LEFT OUTER JOIN tactiondetail td
                                    ON task.taskid = td.taskid";
                actionSelectReplace = @",td.completedon CompletedOn,
                                         td.remarks AS ActionDetailRemarks,
                                         DATEDIFF(DAY,task.PLANSTARTON,GETDATE()) AS TaskOverdue";
            }
            else
            {
                actionFromReplace = "";
                actionSelectReplace = @",task.PLANENDON CompletedOn,
                                         task.REMARKS AS ActionDetailRemarks,
                                         DATEDIFF(DAY,task.PLANSTARTON,GETDATE()) AS TaskOverdue";
            }
            sql = sql.Replace("@actionfromreplace", actionFromReplace);
            sql = sql.Replace("@actionselectreplace", actionSelectReplace);
        }

        private static void ApplyMobileTaskFilters(ref string sql, DynamicParameters parameters, bool isMobileCall, int taskStatusForDisplay, string? periodFromDate, string? periodToDate, string orderBy, int workOUId)
        {
            if (!isMobileCall)
                return;

            if (!string.IsNullOrWhiteSpace(periodFromDate))
            {
                sql += " AND task.PLANSTARTON >= @MobilePeriodFromDate";
                parameters.Add("MobilePeriodFromDate", DateOnly.Parse(periodFromDate));
            }
            if (!string.IsNullOrWhiteSpace(periodToDate))
            {
                sql += " AND task.PLANSTARTON <= @MobilePeriodToDate";
                parameters.Add("MobilePeriodToDate", DateOnly.Parse(periodToDate));
            }
            if (taskStatusForDisplay == -1)
            {
                sql += " AND task.TaskStatus IN (0,1,2,3)";
            }
            else
            {
                parameters.Add("MobileOUId", workOUId);
                switch (taskStatusForDisplay)
                {
                    case 0: // Total
                        break;

                    case 1: // Overdue
                        sql += " AND task.TaskStatus IN (5)";
                        break;

                    case 2: // Pending
                        sql += " AND task.TaskStatus IN (0,1,2)";
                        break;

                    case 3: // Assigned
                        sql += " AND task.TaskStatus = 1";
                        break;

                    case 4: // In Progress
                        sql += " AND task.TaskStatus = 2";
                        break;

                    case 5: // Completed
                        sql += " AND task.TaskStatus = 3";
                        break;

                    case 6: // Closed
                        sql += " AND task.TaskStatus = 4";
                        break;

                    default:
                        throw new MethodAccessException("Type Not Supported.");
                }
            }
        }

        // GB4 parity: TaskDAL.GetWorkBench — WorkBenchType extracted from criteria decides
        // which report to run: 0 = task workbench (delegates to GetTaskReport, same query/DTO),
        // 1 = call workbench (TCALL-based, GET_CALL_WORKBENCH).
        public async Task<string> GetWorkBench(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool Count = false)
        {
            try
            {
                int workBenchType = -1;
                if (CriteriaDTO?.SectionCriteriaList != null)
                {
                    foreach (var section in CriteriaDTO.SectionCriteriaList)
                    {
                        foreach (var attr in section.AttributesCriteriaList ?? [])
                        {
                            if (string.Equals(attr.FieldName, "workbenchtype", StringComparison.OrdinalIgnoreCase))
                            {
                                workBenchType = Convert.ToInt32(attr.FieldValue?.ToString());
                            }
                        }
                    }
                }

                    if (workBenchType == 0)
                {
                    string sql = Count ? TaskQB.GET_WORK_BENCH_TASK_COUNT : TaskQB.WORK_BENCH_TASK;
                    var parameters = new DynamicParameters();

                    ApplyWorkBenchCriteria(ref sql, parameters, CriteriaDTO, "");
                    ApplyWorkBenchCriteria(ref sql, parameters, LoginDTO.UserCriteriaDTO, "_user");

                    if (Count)
                    {
                        return (await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, sql, parameters)).ToString();
                    }

                    sql += @"
                            ORDER BY task.TASKID
                            OFFSET CASE WHEN @firstnumber = -1 THEN 0 ELSE @firstnumber END ROWS
                            FETCH NEXT
                            CASE WHEN @maxresult = -1
                                 THEN 2147483647
                                 ELSE @maxresult
                            END ROWS ONLY";
                    parameters.Add("firstnumber", FirstNumber);
                    parameters.Add("maxresult", MaxResult);

                    var taskReports = (await _QueryExecutor.QueryAsync<TaskReportDTO>(LoginDTO, sql, parameters)).ToList();
                    return JsonConvert.SerializeObject(taskReports);
                }

                if (workBenchType == 1)
                {
                    string sql = Count ? TaskQB.GET_CALL_WORKBENCH_COUNT : TaskQB.GET_CALL_WORKBENCH;
                    var parameters = new DynamicParameters();

                    ApplyCallReportCriteria(ref sql, parameters, CriteriaDTO, "");
                    ApplyCallReportCriteria(ref sql, parameters, LoginDTO.UserCriteriaDTO, "_user");

                    if (Count)
                    {
                        return (await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, sql, parameters)).ToString();
                    }

                    sql += @"
                            ORDER BY call.CALLID
                            OFFSET CASE WHEN @firstnumber = -1 THEN 0 ELSE @firstnumber END ROWS
                            FETCH NEXT
                            CASE WHEN @maxresult = -1
                                 THEN 2147483647
                                 ELSE @maxresult
                            END ROWS ONLY";
                    parameters.Add("firstnumber", FirstNumber);
                    parameters.Add("maxresult", MaxResult);

                    var callReports = (await _QueryExecutor.QueryAsync<TaskReportDTO>(LoginDTO, sql, parameters)).ToList();
                    return JsonConvert.SerializeObject(callReports);
                }

                return Count ? "0" : JsonConvert.SerializeObject(new List<TaskReportDTO>());
            }
            catch (Exception)
            {
                throw;
            }
        }

        // GB4 parity: TaskDAL.GetCallReportCriteria
        private static void ApplyCallReportCriteria(ref string sql, DynamicParameters parameters, CriteriaDTO? criteriaDTO, string suffix)
        {
            if (criteriaDTO?.SectionCriteriaList == null)
                return;

            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                foreach (var attr in section.AttributesCriteriaList ?? [])
                {
                    string? fieldValue = attr.FieldValue?.ToString();
                    if (string.IsNullOrWhiteSpace(fieldValue))
                        continue;

                    string paramName;
                    switch (attr.FieldName?.ToLower())
                    {
                        case "callstatus":
                            paramName = $"callstatus{suffix}";
                            sql += $" AND call.CALLSTATUS = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "callallotedtotype":
                            paramName = $"allotedtotype{suffix}";
                            sql += $" AND call.ALLOTEDTOTYPE = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "employeeid":
                            paramName = $"allotedtoemployeeid{suffix}";
                            sql += $" AND call.ALLOTEDTOEMPLOYEEID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "contactid":
                            paramName = $"allotedtocontactid{suffix}";
                            sql += $" AND call.ALLOTEDTOCONTACTID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "partyid":
                            paramName = $"allotedtopartyid{suffix}";
                            sql += $" AND call.ALLOTEDTOPARTYID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "calltypeid":
                            paramName = $"calltypeid{suffix}";
                            sql += $" AND call.CALLTYPEID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "periodfrom":
                            paramName = $"promisseddate{suffix}";
                            sql += $" AND call.PROMISSEDDATE >= @{paramName}";
                            parameters.Add(paramName, DateTime.Parse(fieldValue));
                            break;

                        case "callpriority":
                            paramName = $"callpriorityid{suffix}";
                            sql += $" AND call.CALLPRIORITYID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "callclosed":
                            paramName = $"isclosed{suffix}";
                            sql += $" AND call.ISCLOSED = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;
                    }
                }
            }
        }

        // GB4 parity: TaskDAL.GetTaskReportCriteria, as applied to WORK_BENCH_TASK.
        // Restricted to fields whose table alias exists in WORK_BENCH_TASK's FROM clause
        // (task, mas, malloc, mactivity, mpb, E, req). GB4's GetTaskReportCriteria also
        // lists MileStoneId/HeadingId/WorkId/RollUpId/DepartmentId/ActivityTypeId/Period,
        // which reference aliases (milestone/head/work/rollup/assignemp/mactivitytype/
        // asstypeact) never joined in WORK_BENCH_TASK — those are intentionally omitted
        // here rather than reproducing what would be broken SQL if ever triggered.
        private static void ApplyWorkBenchCriteria(ref string sql, DynamicParameters parameters, CriteriaDTO? criteriaDTO, string suffix)
        {
            if (criteriaDTO?.SectionCriteriaList == null)
                return;

            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                foreach (var attr in section.AttributesCriteriaList ?? [])
                {
                    string? fieldValue = attr.FieldValue?.ToString();
                    if (string.IsNullOrWhiteSpace(fieldValue))
                        continue;

                    string paramName;
                    switch (attr.FieldName?.ToLower())
                    {
                        case "ouid":
                            paramName = $"ouid{suffix}";
                            sql += $" AND task.OUID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "inchargeid":
                            paramName = $"inchargeid{suffix}";
                            sql += $" AND task.INCHARGEID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "requestedbyid":
                            paramName = $"requestedbyid{suffix}";
                            sql += $" AND task.REQUESTEDBYID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "taskstatus":
                            paramName = $"taskstatus{suffix}";
                            sql += $" AND task.TASKSTATUS = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "assetid":
                            paramName = $"assetid{suffix}";
                            sql += $" AND task.ASSETID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "machineid":
                            paramName = $"machineid{suffix}";
                            sql += $" AND task.MACHINEID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "resourceid":
                            paramName = $"resourceid{suffix}";
                            sql += $" AND task.RESOURCEID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "callid":
                            paramName = $"callid{suffix}";
                            sql += $" AND task.CALLID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "worktype":
                            paramName = $"worktypeid{suffix}";
                            sql += $" AND task.WORKTYPEID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "tasknature":
                            paramName = $"tasknature{suffix}";
                            sql += $" AND task.TASKNATURE = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "tasktype":
                        case "taskdetailtype":
                            paramName = $"detailtype{suffix}";
                            sql += $" AND task.DETAILTYPE = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "leadid":
                            paramName = $"leadid{suffix}";
                            sql += $" AND task.LEADID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "taskid":
                            paramName = $"taskid{suffix}";
                            sql += $" AND task.TASKID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "allocationid":
                            paramName = $"allocationid{suffix}";
                            sql += $" AND task.ALLOCATIONID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "assigneeid":
                            paramName = $"assignedtoemployeeid{suffix}";
                            sql += $" AND task.ASSIGNEDTOEMPLOYEEID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "parenttaskid":
                            paramName = $"parenttaskid{suffix}";
                            sql += $" AND task.PARENTTASKID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        // GB4 maps BOTH PeriodFrom and PeriodTo to task.PLANSTARTON (not a
                        // typo — confirmed in TaskDAL.GetTaskReportCriteria); the operator
                        // (>=, <=, etc.) is driven by the caller's OperationType.
                        case "periodfrom":
                            paramName = $"periodfrom{suffix}";
                            sql += $" AND task.PLANSTARTON {GetOperator(attr.OperationType)} @{paramName}";
                            parameters.Add(paramName, ParseDateValue(fieldValue));
                            break;

                        case "periodto":
                            paramName = $"periodto{suffix}";
                            sql += $" AND task.PLANSTARTON {GetOperator(attr.OperationType)} @{paramName}";
                            parameters.Add(paramName, ParseDateValue(fieldValue));
                            break;

                        case "partybranchid":
                            paramName = $"partybranchid{suffix}";
                            sql += $" AND mas.PARTYBRANCHID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "activityid":
                            paramName = $"activityid{suffix}";
                            sql += $" AND task.ACTIVITYID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "assettypeid":
                            paramName = $"assettypeid{suffix}";
                            sql += $" AND mas.ASSETTYPEID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "istodo":
                            paramName = $"istodo{suffix}";
                            sql += $" AND task.ISTODO = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "categoryid":
                            paramName = $"categoryid{suffix}";
                            sql += $" AND mas.CATEGORYID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "isscore":
                            paramName = $"isscore{suffix}";
                            sql += $" AND mactivity.ISSCORE = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "allocationsplitid":
                            paramName = $"allocationsplitid{suffix}";
                            sql += $" AND task.ALLOCATIONSPLITID = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "projectsequence":
                            paramName = $"projectsequence{suffix}";
                            sql += $" AND task.PROJECTSEQUENCE = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;

                        case "resourcesequence":
                            paramName = $"resourcesequence{suffix}";
                            sql += $" AND task.RESOURCESEQUENCE = @{paramName}";
                            parameters.Add(paramName, Convert.ToInt32(fieldValue));
                            break;
                    }
                }
            }
        }

        // GB4 parity: TaskDAL.UpdateTaskData — kept as a thin dispatcher; the BLL
        // (CRMBLL.Task.TaskBLL.UpdateTaskData) owns transaction/workflow orchestration
        // via ExecuteSaveAsync, matching the SaveTask pattern. This method is kept only
        // to satisfy ITaskDAL for callers that still reference the interface member;
        // the real per-task logic lives in the DAL methods below, called directly by
        // the BLL's ExecuteSaveAsync callback.
        public async Task<string> UpdateTaskData(List<TaskDTO> taskDTOs, LoginDTO loginDTO)
        {
            try
            {
                foreach (var task in taskDTOs)
                    await UpdateTaskDataSingle(task, loginDTO, null);

                return "Details Updated Successfully";
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Persists a single task's field/status update. Mirrors GB4's branch on
        /// UpdateOnlyStatus (TaskDAL.cs:1561 in GB4): 0 = fast status-only update
        /// (GB4's UPDATE_TASK_QUERY_NEW); non-zero = full field update (GB4's
        /// UPDATE_TASK_QUERY), reusing UpdateTask/UPDATE_TASK with the
        /// TaskAssignedToType-gated AssignedTo* column write.
        /// </summary>
        public async Task<int> UpdateTaskDataSingle(TaskDTO taskDTO, LoginDTO loginDTO, DbTransaction? transaction)
        {
            if (taskDTO.UpdateOnlyStatus != 0)
            {
                return await UpdateTask(taskDTO, loginDTO, transaction!);
            }

            var parameters = new DynamicParameters();
            parameters.Add("TaskId", taskDTO.TaskId);
            parameters.Add("TaskOriginalStatus", taskDTO.TaskOriginalStatus);
            parameters.Add("UserId", loginDTO.UserId);
            parameters.Add("ModifiedOn", DateTimeOffset.UtcNow);
            return await _QueryExecutor.ExecuteAsync(loginDTO, TaskQB.UPDATE_TASK_STATUS_ONLY, parameters, transaction);
        }

        /// <summary>
        /// Extends the parent task's PlanEndOn/DueOn/ExpireOn to the later of its own
        /// value or the child's (GB4's ToUpdateParentDate region). No-ops silently when
        /// ParentTaskId does not resolve to a row (mirrors GB4's TaskId != -1 check).
        /// </summary>
        public async System.Threading.Tasks.Task UpdateParentTaskDates(int parentTaskId, DateOnly childPlanEndOn, DateOnly childDueOn, DateOnly childExpireOn, LoginDTO loginDTO, DbTransaction? transaction)
        {
            var parent = await _QueryExecutor.QuerySingleAsync<ParentTaskDatesDTO?>(
                loginDTO, TaskQB.GET_TASK_DATES_FOR_PARENT_CASCADE,
                new { TaskId = parentTaskId, TenantId = loginDTO.ClientId }, transaction);
            if (parent == null)
                return;

            var parameters = new DynamicParameters();
            parameters.Add("TaskId", parentTaskId);
            parameters.Add("TenantId", loginDTO.ClientId);
            parameters.Add("ParentPlanEndOn", childPlanEndOn > parent.TaskPlanEndOn ? childPlanEndOn : parent.TaskPlanEndOn);
            parameters.Add("ParentDueOn", childDueOn > parent.TaskDueOn ? childDueOn : parent.TaskDueOn);
            parameters.Add("ParentExpireOn", childExpireOn > parent.TaskExpireOn ? childExpireOn : parent.TaskExpireOn);
            await _QueryExecutor.ExecuteAsync(loginDTO, TaskQB.UPDATE_PARENT_TASK_DATES, parameters, transaction);
        }

        /// <summary>
        /// Cascades TASKSTATUS down to child rows when a Milestone/Heading/Work task is
        /// closed or force-closed (GB4's ToUpdateChilds-TaskStatus region). Only fires
        /// for detail types 0 (Milestone), 2 (Heading), 3 (Work) — GB4 has no cascade
        /// for detail type 4 (plain Task), which has no further descendants to cascade to.
        /// </summary>
        public async System.Threading.Tasks.Task CascadeChildTaskStatus(int taskId, byte taskDetailType, byte taskStatus, LoginDTO loginDTO, DbTransaction? transaction)
        {
            switch (taskDetailType)
            {
                case 0: // MileStone
                    await _QueryExecutor.ExecuteAsync(loginDTO, TaskQB.UPDATE_TASK_FROM_MILESTONE, new { MilestoneTaskId = taskId, TaskStatus = taskStatus }, transaction);
                    await _QueryExecutor.ExecuteAsync(loginDTO, TaskQB.UPDATE_WORK_FROM_MILESTONE, new { MilestoneTaskId = taskId, TaskStatus = taskStatus }, transaction);
                    await _QueryExecutor.ExecuteAsync(loginDTO, TaskQB.UPDATE_HEADING_FROM_MILESTONE, new { MilestoneTaskId = taskId, TaskStatus = taskStatus }, transaction);
                    break;

                case 2: // Heading
                    await _QueryExecutor.ExecuteAsync(loginDTO, TaskQB.UPDATE_TASK_FROM_HEADING, new { HeadingTaskId = taskId, TaskStatus = taskStatus }, transaction);
                    await _QueryExecutor.ExecuteAsync(loginDTO, TaskQB.UPDATE_WORK_FROM_HEADING, new { HeadingTaskId = taskId, TaskStatus = taskStatus }, transaction);
                    break;

                case 3: // Work
                    await _QueryExecutor.ExecuteAsync(loginDTO, TaskQB.UPDATE_TASK_FROM_WORK, new { WorkTaskId = taskId, TaskStatus = taskStatus }, transaction);
                    break;
            }
        }

        /// <summary>
        /// Posts a free-text comment against the task (GB4's TCOMMENT Posting region).
        /// No-ops when commentText is null/empty — GB4 always inserted a row even for an
        /// empty remark, but an empty audit comment carries no information, so this skips
        /// the insert entirely (a deliberate, minor behavior improvement, not a gap).
        /// </summary>
        public async System.Threading.Tasks.Task PostTaskComment(int commentId, int taskId, string? commentText, LoginDTO loginDTO, DbTransaction? transaction)
        {
            if (string.IsNullOrWhiteSpace(commentText))
                return;

            var parameters = new DynamicParameters();
            parameters.Add("CommentId", commentId);
            parameters.Add("UserId", loginDTO.UserId);
            parameters.Add("TimeStamp", DateTimeOffset.UtcNow);
            parameters.Add("ObjectId", taskId);
            parameters.Add("CommentText", commentText);
            await _QueryExecutor.ExecuteAsync(loginDTO, TaskQB.INSERT_TASK_COMMENT, parameters, transaction);
        }
    }

    /// <summary>Narrow projection used only by UpdateParentTaskDates.</summary>
    public sealed class ParentTaskDatesDTO
    {
        public int TaskId { get; set; }
        public DateOnly TaskPlanEndOn { get; set; }
        public DateOnly TaskDueOn { get; set; }
        public DateOnly TaskExpireOn { get; set; }
    }
}