using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using CRMDAL.DTO.Action;
using CRMDAL.Query.Action;
using Dapper;
using GB5Shared.CriteriaHandler;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using Microsoft.Data.SqlClient;
using Newtonsoft.Json;
using GB5Shared.Validation;
using static GB5Shared.DTO.Framework.Enum.FrameworkEnumDTO;
using GB5Shared.Resource.Response;
using MMDAL.Query.LotType;
using System.Data.Common;

namespace CRMDAL.CustomCode.Action
{
    // Receives the single aggregate row from ActionQB.GET_ACTIVITY_REGISTER_TOTAL.
    file sealed class ActivityRegisterTotalsDTO
    {
        public int TotalTask       { get; set; }
        public int TotalActivity   { get; set; }
        public int TotalProductive { get; set; }
        public int TotalAllocation { get; set; }
    }

    public class ActionDAL: IActionDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;
        public ActionDAL(IQueryExecutor IQueryExecutor, IValidation validation)
        {
            _QueryExecutor = IQueryExecutor;
            _Validation = validation;
        }


         public async Task<IEnumerable<TActionDTO>> GetAction(LoginDTO loginDTO, string sql, object parameters)
        {
            var actionDict = new Dictionary<long, TActionDTO>();

            var result = await _QueryExecutor.QueryMultiMapAsync<TActionDTO, TActionDetailDTO, TActionItemsDTO, TActionDTO>(
                loginDTO,
                sql,
                (action, detail, item) =>
                {
                    // Parent
                    if (!actionDict.TryGetValue(action.TActionId, out var currentaction))
                    {
                        currentaction = action;
                        currentaction.ActionDetailArray = new List<TActionDetailDTO>();
                        actionDict.Add(currentaction.TActionId, currentaction);
                    }

                    // Child
                    if (detail != null)
                    {
                        var currentdetail = currentaction.ActionDetailArray
                            .FirstOrDefault(s => s.TActionDetailId == detail.TActionDetailId);

                        if (currentdetail == null)
                        {
                            currentdetail = detail;
                            currentdetail.ActionItemsArray = new List<TActionItemsDTO>();
                            currentaction.ActionDetailArray.Add(currentdetail);
                        }

                        // Subchild
                        if (item != null &&
                            !currentdetail.ActionItemsArray.Any(d => d.TActionItemsId == item.TActionItemsId))
                        {
                            currentdetail.ActionItemsArray.Add(item);
                        }
                        ;
                    }

                    return currentaction;
                },
                parameters,
                splitOn: "TActionDetailId,TActionItemsId"
            );

            return actionDict.Values.ToList();
        }



        public async Task<IEnumerable<TActionPicklistDTO>> GetSelectListAction(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string baseSql = ActionQB.GET_SELECTLIST_ACTION;
                var parameters = new DynamicParameters();
                parameters.Add("firstnumber", FirstNumber);
                parameters.Add("maxresult",   MaxResult);

                var (whereClause, _)   = CriteriaBuilder.Build(CriteriaDTO,              parameters, baseSql);
                var (userWhereClause, _) = CriteriaBuilder.Build(LoginDTO.UserCriteriaDTO, parameters, baseSql);

                string sql = baseSql.Replace("/*CRITERIA*/", $"{whereClause} {userWhereClause}");

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


        // GB4 parity: TaskDAL.GetActivityWiseAnalysisReportSb's CriteriaWithQueryField table,
        // fed into the shared CommonFunctionFrameDAL.ApplyCriteriaSb. Every field here is applied
        // dynamically per attr.OperationType (Equal/In -> IN(...), NotIn -> NOT IN(...),
        // NotEqual -> <>, GreaterThan(OrEqualTo) -> >(=), LessThan(OrEqualTo) -> <(=),
        // Like -> LIKE '%...%'), matching ApplyCriteriaSb instead of hardcoding IN like the
        // previous GB5 version did. Column refs are against GET_ACTIVITY_REGISTER's own aliases
        // (TASK, ACTION, ACTDETIL, EMP, INCHARGE, PARTY, ALLOCATION, mas, city, lead, ftask,
        // PARENTTASK, attparty) — NOT GetTaskReportCriteria's task.* aliases, which belong to a
        // different query entirely.
        private static readonly (string FieldName, string Column, bool IsDate)[] ActivityRegisterCriteriaFields =
        [
            ("ouid",                   "ACTION.ouid",                 false),
            ("personid",               "EMP.employeeid",              false),
            ("inchargeid",             "INCHARGE.employeeid",         false),
            ("partyid",                "PARTY.partyid",                false),
            ("allocationid",           "ALLOCATION.Allocationid",     false),
            ("actionid",               "ACTION.ACTIONID",             false),
            ("departmentid",           "EMP.DepartmentId",            false),
            ("taskid",                 "ACTDETIL.TASKID",             false),
            ("leadid",                 "ACTDETIL.LEADID",             false),
            ("periodfrom",             "ftask.TASKDATE",              true),
            ("periodto",               "ftask.TASKDATE",              true),
            ("cityid",                 "city.CITYID",                 false),
            ("contactid",              "lead.CONTACTID",              false),
            ("parenttaskid",           "PARENTTASK.TASKID",           false),
            ("status",                 "EMP.Status",                  false),
            ("partybranchlocationid",  "mas.PartyBranchLocationId",   false),
            ("designationid",          "EMP.DESIGNATIONID",           false),
            ("allocationpartyid",      "attparty.PARTYID",            false),
            ("reportingtoid",          "EMP.REPORTINGTOEMPLOYEEID",   false),
        ];

        public async Task<string> GetActivityRegisterReport(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            if (CriteriaDTO?.SectionCriteriaList == null || CriteriaDTO.SectionCriteriaList.Count == 0)
                throw new ArgumentException("CriteriaDTO with at least one section is required.");

            string periodFromDate = "";
            string periodToDate = "";
            string periodFromDateRaw = "";
            string periodToDateRaw = "";
            var periodFromDateOp = CriteriaDTO.OperationType.GreaterThanOrEqualTo;
            var periodToDateOp = CriteriaDTO.OperationType.LessThanOrEqualTo;
            int actionId = 0;
            int reportType = 0;
            int forLead = 1;
            int orderBy = 0;
            int duration = 0;
            string partyBranchIdRaw = "";
            var partyBranchIdOp = CriteriaDTO.OperationType.Equal;
            // GB4 GetActivityRegisterReport separately built a raw "MEMPLOYEE.DEPARTMENTID IN (...)"
            // clause for the DR-Present/Leave/DailyReportNo/NotPunch side-queries, which reference
            // the unaliased MEMPLOYEE table — those queries have no join to the main report's EMP
            // alias, so this can't be folded into the dynamic criteria table (which targets
            // GET_ACTIVITY_REGISTER's own aliases only). Still made operator-dynamic via
            // AppendDynamicCriterion, just against a different column per side-query.
            string departmentIdRaw = "";
            var departmentIdOp = CriteriaDTO.OperationType.Equal;
            string employerStatusRaw = "";
            var employerStatusOp = CriteriaDTO.OperationType.Equal;

            // GB4 parity: only fields with no equivalent in GetActivityWiseAnalysisReportSb's
            // dynamic criteria table are read manually here — ReportType, ForLead, OrderBy,
            // Duration, EmployerStatus (report-shape/paging controls, not row filters), and
            // PeriodFromDate/PeriodToDate (used both for the mandatory-field check below and the
            // {periodfromdate} base-query substitution, so they need dedicated local variables
            // regardless). PartyBranchId is also read manually — GB4's own criteria table has it
            // commented out (see GetActivityWiseAnalysisReportSb) and always applies a fallback to
            // LoginDTO.WorkPartyBranchId when absent, which the generic per-attribute loop can't
            // express. Every other filterable field (OUId, PersonId, InchargeId, PartyId,
            // AllocationId, ActionId, DepartmentId, TaskId, LeadId, PeriodFrom/To, CityId,
            // ContactId, PARENTTASKID, Status, PartyBranchLocationId, DesignationId,
            // AllocationPartyId, ReportingToId) is applied dynamically below via
            // AppendDynamicActivityRegisterCriteria — no per-field hardcoding.
            foreach (var attr in CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList)
            {
                string field = attr.FieldName.ToLower();
                string val = attr.FieldValue?.ToString() ?? "";
                if (field == "periodfromdate" && !string.IsNullOrEmpty(val))
                {
                    periodFromDate = ParseCriteriaDate(val);
                    periodFromDateRaw = val;
                    periodFromDateOp = attr.OperationType;
                }
                else if (field == "periodtodate" && !string.IsNullOrEmpty(val))
                {
                    periodToDate = ParseCriteriaDate(val);
                    periodToDateRaw = val;
                    periodToDateOp = attr.OperationType;
                }
                else if (field == "actionid" && !string.IsNullOrEmpty(val))
                    actionId = Convert.ToInt32(val);
                else if (field == "reporttype" && !string.IsNullOrEmpty(val))
                    reportType = Convert.ToInt32(val);
                else if (field == "forlead" && !string.IsNullOrEmpty(val))
                    forLead = Convert.ToInt32(val);
                else if (field == "departmentid" && !string.IsNullOrEmpty(val))
                {
                    departmentIdRaw = val;
                    departmentIdOp = attr.OperationType;
                }
                else if (field == "orderby" && !string.IsNullOrEmpty(val))
                    orderBy = Convert.ToInt32(val);
                else if (field == "duration" && !string.IsNullOrEmpty(val))
                    duration = Convert.ToInt32(val);
                else if (field == "partybranchid" && !string.IsNullOrEmpty(val))
                {
                    partyBranchIdRaw = val;
                    partyBranchIdOp = attr.OperationType;
                }
                else if (field == "employerstatus" && !string.IsNullOrEmpty(val))
                {
                    employerStatusRaw = val;
                    employerStatusOp = attr.OperationType;
                }
            }

            if (string.IsNullOrEmpty(periodFromDate) && actionId == 0)
                throw new ArgumentException("PeriodFromDate is required.");

            var sql = new System.Text.StringBuilder(ActionQB.GET_ACTIVITY_REGISTER
                     .Replace("{replacefilters}", "")
                     .Replace("{periodfromdate}", periodFromDate));

            if (reportType == 1)
                sql.Append("\r\n AND ACTDETIL.LEADID <> -1");

            if (!string.IsNullOrEmpty(periodFromDate))
                AppendDynamicCriterion(sql, "ACTDETIL.PERIODFROM", periodFromDateOp, periodFromDateRaw, isDate: true);

            if (!string.IsNullOrEmpty(periodToDate))
                AppendDynamicCriterion(sql, "ACTDETIL.PERIODTO", periodToDateOp, periodToDateRaw, isDate: true, isPeriodTo: true);

            if (!string.IsNullOrEmpty(partyBranchIdRaw))
                AppendDynamicCriterion(sql, "mas.PARTYBRANCHID", partyBranchIdOp, partyBranchIdRaw, isDate: false);
            else if (LoginDTO.WorkPartyBranchId != -1)
                sql.Append($"\r\n AND mas.PARTYBRANCHID IN ({LoginDTO.WorkPartyBranchId})");

            if (duration == 1)
                sql.Append("\r\n AND ACTDETIL.DURATION > 0");

            if (!string.IsNullOrEmpty(employerStatusRaw))
                AppendDynamicCriterion(sql, "EMP.EMPLOYEESTATUS", employerStatusOp, employerStatusRaw, isDate: false);

            AppendDynamicActivityRegisterCriteria(sql, CriteriaDTO);
            AppendDynamicActivityRegisterCriteria(sql, LoginDTO.UserCriteriaDTO);

            // GB4 parity: GetActivityRegisterReport (ActionDAL.cs) ran GET_ACTIVITY_REGISTER_TOTAL
            // with the SAME criteria filters as the main query, then stamped its single row of
            // duration sums (TotalTask/TotalActivity/TotalProductive/TotalAllocation) onto every
            // row of the result. Build the filter clause once so both queries stay in sync.
            var totalFilters = new System.Text.StringBuilder();
            if (reportType == 1)
                totalFilters.Append("\r\n AND ACTDETIL.LEADID <> -1");
            if (!string.IsNullOrEmpty(periodFromDate))
                AppendDynamicCriterion(totalFilters, "ACTDETIL.PERIODFROM", periodFromDateOp, periodFromDateRaw, isDate: true);
            if (!string.IsNullOrEmpty(periodToDate))
                AppendDynamicCriterion(totalFilters, "ACTDETIL.PERIODTO", periodToDateOp, periodToDateRaw, isDate: true, isPeriodTo: true);
            if (!string.IsNullOrEmpty(partyBranchIdRaw))
                AppendDynamicCriterion(totalFilters, "mas.PARTYBRANCHID", partyBranchIdOp, partyBranchIdRaw, isDate: false);
            else if (LoginDTO.WorkPartyBranchId != -1)
                totalFilters.Append($"\r\n AND mas.PARTYBRANCHID IN ({LoginDTO.WorkPartyBranchId})");
            if (duration == 1)
                totalFilters.Append("\r\n AND ACTDETIL.DURATION > 0");

            if (!string.IsNullOrEmpty(employerStatusRaw))
                AppendDynamicCriterion(totalFilters, "EMP.EMPLOYEESTATUS", employerStatusOp, employerStatusRaw, isDate: false);

            AppendDynamicActivityRegisterCriteria(totalFilters, CriteriaDTO);
            AppendDynamicActivityRegisterCriteria(totalFilters, LoginDTO.UserCriteriaDTO);

            // ======================================================
            // PAGINATION
            // ======================================================

            string pagedSql = $@"
WITH ActivityCTE AS
(
    SELECT
        BaseQ.*,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
    FROM
    (
        {sql}
    ) AS BaseQ
)
SELECT *
FROM ActivityCTE
WHERE
    (@firstnumber = -1 AND @maxresult = -1)
    OR RowNum BETWEEN @firstnumber AND @maxresult;";


            var parameters = new
            {
                firstnumber = FirstNumber,
                maxresult = MaxResult
            };

            var data = await _QueryExecutor.QueryAsync<ActivityRegisterDTO>(
                LoginDTO,
                pagedSql,
                parameters);

            var list = data.ToList();

            // GB4 parity: totals were only computed/stamped in the ForLead==1 branch
            // (GetActivityRegisterReport, ActionDAL.cs) — mirrors that gate exactly.
            if (forLead == 1 && list.Count > 0)
            {
                var totalsRow = (await _QueryExecutor.QueryAsync<ActivityRegisterTotalsDTO>(
                    LoginDTO,
                    ActionQB.GET_ACTIVITY_REGISTER_TOTAL + totalFilters,
                    null)).FirstOrDefault();

                if (totalsRow != null)
                {
                    foreach (var row in list)
                    {
                        row.TotalTask       = totalsRow.TotalTask;
                        row.TotalActivity   = totalsRow.TotalActivity;
                        row.TotalProductive = totalsRow.TotalProductive;
                        row.TotalAllocation = totalsRow.TotalAllocation;
                    }
                }
            }

            // GB4 parity: the ForLead==1 branch also merged DR-Present/DR-Leave/DailyReportNo/
            // NotPunch employee rows into the result set (GetActivityRegisterReport, ActionDAL.cs).
            if (forLead == 1)
            {
                var drDepartmentFilterSb = new System.Text.StringBuilder();
                if (!string.IsNullOrEmpty(departmentIdRaw))
                    AppendDynamicCriterion(drDepartmentFilterSb, "MEMPLOYEE.DEPARTMENTID", departmentIdOp, departmentIdRaw, isDate: false);
                string drDepartmentFilter = drDepartmentFilterSb.ToString();

                var dateParam = new { periodfromdate = periodFromDate };
                var present = await _QueryExecutor.QueryAsync<DRPresentDTO>(LoginDTO,ActionQB.GET_DRPRESENT + drDepartmentFilter,dateParam);
                var leave = await _QueryExecutor.QueryAsync<DRLeaveDTO>(LoginDTO,ActionQB.GET_DRLEAVE +drDepartmentFilter.Replace("MEMPLOYEE.DEPARTMENTID", "b.DEPARTMENTID"),dateParam);
                var drNo = await _QueryExecutor.QueryAsync<DailyReportNoListDTO>(LoginDTO,ActionQB.GET_DAILYREPORT_NO + drDepartmentFilter,null);
                var notPunch = await _QueryExecutor.QueryAsync<NotPunchDTO>(LoginDTO,ActionQB.GET_NOT_PUNCH_LIST + drDepartmentFilter,dateParam);

                foreach (var p in present)
                    list.Add(new ActivityRegisterDTO
                    {
                        PersonCode = p.EmployeeCode,
                        PersonName = p.EmployeeName,
                        Type = p.Type
                    });

                foreach (var l in leave)
                    list.Add(new ActivityRegisterDTO
                    {
                        PersonCode = l.EmployeeCode,
                        PersonName = l.EmployeeName,
                        Type = l.Type
                    });

                foreach (var d in drNo)
                    list.Add(new ActivityRegisterDTO
                    {
                        PersonCode = d.EmployeeCode,
                        PersonName = d.EmployeeName,
                        Type = d.Type
                    });

                foreach (var n in notPunch)
                    list.Add(new ActivityRegisterDTO
                    {
                        PersonCode = n.EmployeeCode,
                        PersonName = n.EmployeeName,
                        Type = n.Type
                    });
            }
            return JsonConvert.SerializeObject(list);
        }

        public async Task<string> GetReadingEntryReport(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool Count = false)
        {
            if (CriteriaDTO?.SectionCriteriaList == null || CriteriaDTO.SectionCriteriaList.Count == 0)
                throw new ArgumentException("CriteriaDTO is required.");

            string periodFromDate = "";
            string periodToDate = "";
            string activityId = "";

            foreach (var attr in CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList)
            {
                string field = attr.FieldName.ToLower();
                string val = attr.FieldValue?.ToString() ?? "";
                if (field == "periodfromdate" && !string.IsNullOrEmpty(val))
                    periodFromDate = new DateTime(1970, 1, 1).AddMilliseconds(Convert.ToInt64(val)).ToString("yyyy/MM/dd");
                else if (field == "periodtodate" && !string.IsNullOrEmpty(val))
                    periodToDate = new DateTime(1970, 1, 1).AddMilliseconds(Convert.ToInt64(val)).ToString("yyyy/MM/dd");
                else if (field == "activityid" && !string.IsNullOrEmpty(val))
                    activityId = val;
            }

            if (Count)
            {
                var countSql = new System.Text.StringBuilder(ActionQB.GET_READING_ENTRY_REPORT_COUNT);
                if (!string.IsNullOrEmpty(periodFromDate))
                    countSql.Append($"\r\n AND c.PERIODFROM >= '{periodFromDate}'");
                if (!string.IsNullOrEmpty(periodToDate))
                    countSql.Append($"\r\n AND c.PERIODTO <= '{periodToDate}'");
                if (!string.IsNullOrEmpty(activityId))
                    countSql.Append($"\r\n AND c.ACTIVITYID IN ({activityId})");
                int total = await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, countSql.ToString());
                return JsonConvert.SerializeObject(new ReadingEntryReportDTO { NumberOfRecords = total });
            }

            var cteSql = new System.Text.StringBuilder(ActionQB.GET_READING_ENTRY_REPORT_CTE);
            var whereClauses = new List<string>();
            if (!string.IsNullOrEmpty(periodFromDate)) whereClauses.Add($"c.PERIODFROM >= '{periodFromDate}'");
            if (!string.IsNullOrEmpty(periodToDate))   whereClauses.Add($"c.PERIODTO <= '{periodToDate}'");
            if (!string.IsNullOrEmpty(activityId))     whereClauses.Add($"c.ACTIVITYID IN ({activityId})");

            if (whereClauses.Count > 0)
            {
                cteSql.Replace("WHERE 1=1", $"WHERE 1=1\r\n  AND {string.Join("\r\n  AND ", whereClauses)}");
            }

            string pagedCteSql = cteSql.ToString();
            if (FirstNumber != -1 && MaxResult != -1)
                pagedCteSql += $"\r\n ORDER BY AssetId OFFSET {FirstNumber - 1} ROWS FETCH NEXT {MaxResult - FirstNumber + 1} ROWS ONLY";

            var result = await _QueryExecutor.QueryAsync<ReadingEntryReportDTO>(LoginDTO, pagedCteSql);
            return JsonConvert.SerializeObject(result);
        }

        public async Task<string> GetActivityRegisterWithOutDRLeavePresent(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            if (CriteriaDTO?.SectionCriteriaList == null || CriteriaDTO.SectionCriteriaList.Count == 0)
                throw new ArgumentException("CriteriaDTO is required.");

            string periodFromDate = "";
            string periodToDate = "";
            int actionId = 0;
            int reportType = 0;
            int orderBy = 0;
            string departmentFilter = "";

            foreach (var attr in CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList)
            {
                string field = attr.FieldName.ToLower();
                string val = attr.FieldValue?.ToString() ?? "";
                if (field == "periodfromdate" && !string.IsNullOrEmpty(val))
                    periodFromDate = new DateTime(1970, 1, 1).AddMilliseconds(Convert.ToInt64(val)).ToString("yyyy/MM/dd");
                else if (field == "periodtodate" && !string.IsNullOrEmpty(val))
                    periodToDate = new DateTime(1970, 1, 1).AddMilliseconds(Convert.ToInt64(val)).ToString("yyyy/MM/dd");
                else if (field == "actionid" && !string.IsNullOrEmpty(val))
                    actionId = Convert.ToInt32(val);
                else if (field == "reporttype" && !string.IsNullOrEmpty(val))
                    reportType = Convert.ToInt32(val);
                else if (field == "departmentid" && !string.IsNullOrEmpty(val))
                    departmentFilter = $" AND MEMPLOYEE.DEPARTMENTID IN ({val})";
                else if (field == "orderby" && !string.IsNullOrEmpty(val))
                    orderBy = Convert.ToInt32(val);
            }

            if (string.IsNullOrEmpty(periodFromDate) && actionId == 0)
                throw new ArgumentException("PeriodFromDate is required.");

            var sql = new System.Text.StringBuilder(
                ActionQB.GET_ACTIVITY_REGISTER
                    .Replace("{replacefilters}", departmentFilter)
                    .Replace("{periodfromdate}", periodFromDate));

            if (reportType == 1)
                sql.Append("\r\n AND ACTDETIL.LEADID <> -1");
            if (!string.IsNullOrEmpty(periodFromDate))
                sql.Append($"\r\n AND ACTDETIL.PERIODFROM >= '{periodFromDate}'");
            if (!string.IsNullOrEmpty(periodToDate))
                sql.Append($"\r\n AND ACTDETIL.PERIODTO <= '{periodToDate}'");
            if (actionId > 0)
                sql.Append($"\r\n AND ACTION.ACTIONID = {actionId}");
            sql.Append(orderBy == 1
                ? "\r\n ORDER BY ACTION.PERIODFROM, EMP.EMPLOYEECODE, ACTDETIL.PERIODFROM"
                : "\r\n ORDER BY EMP.EMPLOYEECODE, ACTDETIL.PERIODFROM");

            string pagedSql = $@"WITH ActivityCTE AS (
                SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
                FROM ({sql}) BaseQ)
            SELECT * FROM ActivityCTE
            WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);";

            var parameters = new { firstnumber = FirstNumber, maxresult = MaxResult };
            var result = await _QueryExecutor.QueryAsync<ActivityRegisterWithOutDRLeavePresentDTO>(LoginDTO, pagedSql, parameters);
            return JsonConvert.SerializeObject(result);
        }

        public async Task<string> UpdateActivity(SelectionActionDTO SelectionActionDTO, LoginDTO LoginDTO)
        {
            if (SelectionActionDTO == null)
                throw new ArgumentNullException(nameof(SelectionActionDTO));
            if (SelectionActionDTO.ActionId <= 0)
                throw new ArgumentException("ActionId is required.");
            if (SelectionActionDTO.ActionDetailId <= 0)
                throw new ArgumentException("ActionDetailId is required.");

            var transaction = await _QueryExecutor.BeginTransactionAsync(LoginDTO);
            try
            {
                var headerParam = new
                {
                    transactiondate = SelectionActionDTO.TransactionDate,
                    particulars     = SelectionActionDTO.Particulars,
                    actionid        = SelectionActionDTO.ActionId
                };
                await _QueryExecutor.ExecuteAsync(LoginDTO, ActionQB.UPDATE_ACTION_BASED_ON_SELECTION, headerParam, transaction);

                var detailParam = new
                {
                    activityid       = SelectionActionDTO.ActivityId,
                    actionstatus     = SelectionActionDTO.ActionStatus,
                    selectionstatus  = SelectionActionDTO.SelectionStatus,
                    reasonid         = SelectionActionDTO.ReasonId,
                    reasonremarks    = SelectionActionDTO.ReasonRemarks,
                    selectionremarks = SelectionActionDTO.SelectionRemarks,
                    actiondetailid   = SelectionActionDTO.ActionDetailId
                };
                await _QueryExecutor.ExecuteAsync(LoginDTO, ActionQB.UPDATE_ACTION_DETAIL_BASED_ON_SELECTION, detailParam, transaction);

                await _QueryExecutor.CommitAsync(transaction);
                return "Records Updated Successfully.";
            }
            catch
            {
                await _QueryExecutor.RollbackAsync(transaction);
                throw;
            }
        }

        public async Task<string> CustomerAndLeadPicklist(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            var sql = new System.Text.StringBuilder(ActionQB.GET_CUSTOMER_AND_LEAD_PICKLIST);

            if (CriteriaDTO?.SectionCriteriaList != null)
            {
                foreach (var section in CriteriaDTO.SectionCriteriaList)
                    foreach (var attr in section.AttributesCriteriaList)
                    {
                        string field = attr.FieldName.ToLower();
                        string val = attr.FieldValue?.ToString() ?? "";
                        if (!string.IsNullOrEmpty(val))
                        {
                            if (field == "code")
                                sql.Append($"\r\n AND xx.PARTYBRANCHCODE LIKE '%{val}%'");
                            else if (field == "name")
                                sql.Append($"\r\n AND xx.PARTYBRANCHNAME LIKE '%{val}%'");
                            else if (field == "cityname")
                                sql.Append($"\r\n AND xx.CITYNAME LIKE '%{val}%'");
                            else if (field == "zipcode")
                                sql.Append($"\r\n AND xx.ZIPCODE LIKE '%{val}%'");
                        }
                    }
            }

            string pagedSql = $@"WITH PicklistCTE AS (
                SELECT *, ROW_NUMBER() OVER (ORDER BY Id) AS RowNum
                FROM ({sql}) PickQ)
            SELECT Id, Code, Name, ClientGeoCode, SelectionType FROM PicklistCTE
            WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);";

            var parameters = new { userid = LoginDTO.UserId, firstnumber = FirstNumber, maxresult = MaxResult };
            var result = await _QueryExecutor.QueryAsync<CustomerAndLeadSelectListDTO>(LoginDTO, pagedSql, parameters);
            return JsonConvert.SerializeObject(result);
        }

        public async Task<string> ActionSummary(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            string fromDate = "";
            string toDate = "";

            if (CriteriaDTO?.SectionCriteriaList != null && CriteriaDTO.SectionCriteriaList.Count > 0)
            {
                foreach (var attr in CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList)
                {
                    string field = attr.FieldName.ToLower();
                    string val = attr.FieldValue?.ToString() ?? "";
                    if (field == "periodfromdate" && !string.IsNullOrEmpty(val))
                        fromDate = new DateTime(1970, 1, 1).AddMilliseconds(Convert.ToInt64(val)).ToString("yyyy/MM/dd");
                    else if (field == "periodtodate" && !string.IsNullOrEmpty(val))
                        toDate = new DateTime(1970, 1, 1).AddMilliseconds(Convert.ToInt64(val)).ToString("yyyy/MM/dd");
                }
            }

            if (string.IsNullOrEmpty(fromDate) || string.IsNullOrEmpty(toDate))
                throw new ArgumentException("PeriodFromDate and PeriodToDate are required.");

            var sql = new System.Text.StringBuilder(ActionQB.GET_ACTION_SUMMARY);
            sql.Append($"\r\n AND a.TRANSACTIONDATE >= '{fromDate}'");
            sql.Append($"\r\n AND a.TRANSACTIONDATE <= '{toDate}'");

            if (CriteriaDTO?.SectionCriteriaList != null)
            {
                foreach (var section in CriteriaDTO.SectionCriteriaList)
                    foreach (var attr in section.AttributesCriteriaList)
                    {
                        string field = attr.FieldName.ToLower();
                        string val = attr.FieldValue?.ToString() ?? "";
                        if (!string.IsNullOrEmpty(val))
                        {
                            if (field == "employeeid")
                                sql.Append($"\r\n AND f.EMPLOYEEID IN ({val})");
                            else if (field == "partyid")
                                sql.Append($"\r\n AND d.PARTYID IN ({val})");
                            else if (field == "partybranchid")
                                sql.Append($"\r\n AND c.PARTYBRANCHID IN ({val})");
                            else if (field == "leadid")
                                sql.Append($"\r\n AND b.LEADID IN ({val})");
                        }
                    }
            }

            sql.Append("\r\n ORDER BY a.TRANSACTIONDATE, b.PERIODFROM");

            string pagedSql = $@"WITH SummaryCTE AS (
                SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
                FROM ({sql}) SummaryQ)
            SELECT * FROM SummaryCTE
            WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);";

            var parameters = new { firstnumber = FirstNumber, maxresult = MaxResult };
            var result = await _QueryExecutor.QueryAsync<ActionSummaryDTO>(LoginDTO, pagedSql, parameters);
            return JsonConvert.SerializeObject(result);
        }


        public async Task<int> SaveAction(TActionDTO TActionDTO, LoginDTO LoginDTO, DbTransaction? transaction = null)
        {
            try
            {
                const string Sql = ActionQB.SAVE_ACTION;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, TActionDTO, transaction);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(Error);
            }
        }


        public async Task<int> UpdateAction(TActionDTO TActionDTO, LoginDTO LoginDTO, DbTransaction? transaction = null)
        {
            try
            {
                const string sql = ActionQB.UPDATE_ACTION;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, sql, TActionDTO, transaction);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(Error);
            }
        }



        public async Task<string> DeleteAction(int TActionId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = ActionQB.DELETE_ACTION;
                var parameter = new { tactionid = TActionId };
                var result = await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, parameter);

                if (result > 0)
                {
                    return $"{SuccessResponse.DeleteSuccessMessage}";
                }
                else
                {
                    return $"{ErrorResponse.DeleteNotFoundMessage}";
                }
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.DeleteErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<int> SaveActionDetail(TActionDetailDTO detail, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(loginDTO, ActionQB.SAVE_ACTION_DETAIL, detail, transaction);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async System.Threading.Tasks.Task DeleteActionDetails(int actionId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, ActionQB.DELETE_ACTION_DETAILS, new { actionid = actionId }, transaction);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, "Error deleting action details.");
                throw new Exception(Error);
            }
        }

        public async System.Threading.Tasks.Task UpdateTaskForAction(int taskId, DateOnly periodFrom, DateOnly periodTo, int actualHours, int actionStatus, int numberOfData, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, ActionQB.UPDATE_TASK_FOR_ACTION, new
                {
                    taskid       = taskId,
                    periodfrom   = periodFrom,
                    periodto     = periodTo,
                    actualhours  = actualHours,
                    actionstatus = actionStatus,
                    numberofdata = numberOfData
                }, transaction);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, "Error updating task for action.");
                throw new Exception(Error);
            }
        }

        public async System.Threading.Tasks.Task ReverseTaskForAction(int taskId, int actualHours, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, ActionQB.REVERSE_TASK_FOR_ACTION, new
                {
                    taskid      = taskId,
                    actualhours = actualHours
                }, transaction);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, "Error reversing task hours.");
                throw new Exception(Error);
            }
        }

        public async System.Threading.Tasks.Task UpdateParentTaskForActionDetail(int taskId, int actualHours, bool isReverse, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            try
            {
                string sql = isReverse
                    ? ActionQB.UPDATE_PARENT_TASK_FOR_ACTION_REVERSE
                    : ActionQB.UPDATE_PARENT_TASK_FOR_ACTION_ADD;

                await _QueryExecutor.ExecuteAsync(loginDTO, sql, new
                {
                    taskid      = taskId,
                    actualhours = actualHours
                }, transaction);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, "Error updating parent task hours for action.");
                throw new Exception(Error);
            }
        }

        public async System.Threading.Tasks.Task UpdateCallStatus(int callId, int callStatus, bool isClosed, DateOnly completedOn, string completedOnTime, int userId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, ActionQB.UPDATE_CALL_STATUS, new
                {
                    callid          = callId,
                    callstatus      = callStatus,
                    isclosed        = isClosed ? 1 : 0,
                    completedon     = completedOn,
                    completedontime = completedOnTime,
                    userid          = userId
                }, transaction);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, "Error updating call status.");
                throw new Exception(Error);
            }
        }

        public async System.Threading.Tasks.Task UpdateLead(
            int leadId, int priorityId, int successStatus, DateOnly expectedDate, DateOnly wonLostDate,
            double expectedFcRevenue, double orderConfidence, double revenueConfidence,
            byte leadStatus, string lostRemarks, int leadCycleDetailId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, ActionQB.UPDATE_LEAD, new
                {
                    leadid            = leadId,
                    priorityid        = priorityId,
                    successstatus     = successStatus,
                    expecteddate      = expectedDate,
                    wonlostdate       = wonLostDate,
                    expectedfcrevenue = expectedFcRevenue,
                    orderconfidence   = orderConfidence,
                    revenueconfidence = revenueConfidence,
                    leadstatus        = leadStatus,
                    lostremarks       = lostRemarks,
                    leadcycledetailid = leadCycleDetailId
                }, transaction);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, "Error updating lead.");
                throw new Exception(Error);
            }
        }

        // ── GB4: Geo-fencing / location tracking ──────────────────────────
        public async Task<byte> GetBizTransactionTypeIsLocationTrack(int bizTransactionTypeId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            try
            {
                return await _QueryExecutor.ExecuteScalarAsync<byte>(loginDTO,
                    ActionQB.GET_BIZTRANSACTIONTYPE_ISLOCATIONTRACK,
                    new { biztransactiontypeid = bizTransactionTypeId }, transaction);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, "Error reading BIZTransactionType location-track setting.");
                throw new Exception(Error);
            }
        }

        public async Task<int> GetGeoFencingSetting(LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            try
            {
                return await _QueryExecutor.ExecuteScalarAsync<int>(loginDTO, ActionQB.GET_GEOFENCING_SETTING, null, transaction);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, "Error reading geo-fencing setting.");
                throw new Exception(Error);
            }
        }

        public async System.Threading.Tasks.Task PostToLocationTrack(string masterGeoCode, string actualGeoCode, int bizTransactionTypeId, int userId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, ActionQB.POST_TO_LOCATION_TRACK, new
                {
                    mastergeocode      = masterGeoCode,
                    actualgeocode      = actualGeoCode,
                    biztransactiontypeid = bizTransactionTypeId,
                    userid             = userId
                }, transaction);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, "Error posting to location track.");
                throw new Exception(Error);
            }
        }

        // ── GB4: Task completed-percentage recalculation ──────────────────
        public async System.Threading.Tasks.Task UpdateCompletedPercentage(int personId, int taskId, int detailType, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, ActionQB.UPDATE_COMPLETED_PERCENTAGE, new
                {
                    personid   = personId,
                    taskid     = taskId,
                    detailtype = detailType
                }, transaction);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, "Error updating task completed percentage.");
                throw new Exception(Error);
            }
        }

        public async System.Threading.Tasks.Task UpdateCompletedPercentageSecondTime(int detailType, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, ActionQB.UPDATE_COMPLETED_PERCENTAGE_SECONDTIME, new
                {
                    detailtype = detailType
                }, transaction);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, "Error updating task completed percentage (second pass).");
                throw new Exception(Error);
            }
        }

        public async System.Threading.Tasks.Task UpdateCompletedPercentageForResourcePunch(int parentTaskId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(loginDTO, ActionQB.UPDATE_COMPLETED_PERCENTAGE_RESOURCEPUNCH, new
                {
                    parenttaskid = parentTaskId
                }, transaction);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, "Error updating task completed percentage for resource punch.");
                throw new Exception(Error);
            }
        }

        public async Task<int?> GetParentTaskId(int taskId, LoginDTO loginDTO, DbTransaction? transaction = null)
        {
            try
            {
                return await _QueryExecutor.ExecuteScalarAsync<int?>(loginDTO, ActionQB.GET_PARENT_TASK_ID, new { taskid = taskId }, transaction);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, "Error reading parent task id.");
                throw new Exception(Error);
            }
        }

        // Validates a criteria value that is meant to be a comma-separated list of integer IDs
        // (e.g. PersonId, InchargeId, OuId, PartyBranchId, DepartmentId), and re-renders it from
        // the parsed integers. GetActivityRegisterReport builds its WHERE clause by string
        // concatenation (no @parameter binding, since the values feed a dynamically-assembled
        // CTE) — this closes the SQL-injection vector on those fields without changing the
        // query shape. Throws FormatException/OverflowException on anything that isn't a plain
        // comma-separated integer list, same as the existing Convert.ToInt32 calls in this method.
        private static string SafeIntListLiteral(string value) =>
            string.Join(",", value.Split(',', StringSplitOptions.RemoveEmptyEntries)
                                   .Select(v => Convert.ToInt32(v.Trim())));

        // GB4 parity: CommonFunctionFrameDAL.ApplyCriteriaSb's per-OperationType branching,
        // applied against ActivityRegisterCriteriaFields. Skips any field not in that table
        // (e.g. the ones already handled by name above — TaskId, PeriodFromDate/ToDate,
        // ActionId, DepartmentId, PartyBranchId, PersonId, InchargeId, OUId — so they are not
        // double-applied) and any attribute with an empty FieldValue, matching ApplyCriteriaSb's
        // own empty-value skip.
        private static void AppendDynamicActivityRegisterCriteria(System.Text.StringBuilder sql, CriteriaDTO? criteriaDTO)
        {
            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? fieldName = attr.FieldName?.ToLower();

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

                        AppendDynamicCriterion(sql, column, attr.OperationType, fieldValue, isDate, isPeriodTo: candidateFieldName == "periodto");
                        break;
                    }
                }
            }
        }

        // GB4 parity: CommonFunctionFrameDAL.ApplyCriteriaSb's operator branching — Equal/In ->
        // IN(...), NotIn -> NOT IN(...), NotEqual -> <>, GreaterThan(OrEqualTo) -> >(=),
        // LessThan(OrEqualTo) -> <(=), Like -> LIKE '%...%'. Values are string-concatenated (not
        // @parameter-bound) to match this method's existing CTE-building approach, so every
        // branch validates its input first: SafeIntListLiteral for int lists, ParseCriteriaDate
        // (which only ever returns a machine-formatted date or throws) for dates.
        private static void AppendDynamicCriterion(System.Text.StringBuilder sql, string column, CriteriaDTO.OperationType operationType, string fieldValue, bool isDate, bool isPeriodTo = false)
        {
            switch (operationType)
            {
                case CriteriaDTO.OperationType.Equal:
                    sql.Append(isDate
                        ? $"\r\n AND {column} = '{ParseCriteriaDate(fieldValue, isPeriodTo)}'"
                        : $"\r\n AND {column} IN ({SafeIntListLiteral(fieldValue)})");
                    break;

                case CriteriaDTO.OperationType.In:
                    // GB4 parity: ApplyCriteriaSb only has an isDate==false branch for In — a date-
                    // classified field with OperationType=In is a silent no-op in GB4, not "=".
                    if (!isDate)
                        sql.Append($"\r\n AND {column} IN ({SafeIntListLiteral(fieldValue)})");
                    break;

                case CriteriaDTO.OperationType.NotIn:
                    // GB4 parity: same silent no-op for date fields as In (ApplyCriteriaSb has no
                    // date branch for NotIn either).
                    if (!isDate)
                        sql.Append($"\r\n AND {column} NOT IN ({SafeIntListLiteral(fieldValue)})");
                    break;

                case CriteriaDTO.OperationType.NotEqual:
                    sql.Append(isDate
                        ? $"\r\n AND {column} <> '{ParseCriteriaDate(fieldValue, isPeriodTo)}'"
                        : $"\r\n AND {column} <> {Convert.ToInt32(fieldValue)}");
                    break;

                case CriteriaDTO.OperationType.GreaterThanOrEqualTo:
                    sql.Append(isDate
                        ? $"\r\n AND {column} >= '{ParseCriteriaDate(fieldValue, isPeriodTo)}'"
                        : $"\r\n AND {column} >= {Convert.ToInt32(fieldValue)}");
                    break;

                case CriteriaDTO.OperationType.LessThanOrEqualTo:
                    sql.Append(isDate
                        ? $"\r\n AND {column} <= '{ParseCriteriaDate(fieldValue, isPeriodTo)}'"
                        : $"\r\n AND {column} <= {Convert.ToInt32(fieldValue)}");
                    break;

                case CriteriaDTO.OperationType.GreaterThan:
                    sql.Append(isDate
                        ? $"\r\n AND {column} > '{ParseCriteriaDate(fieldValue, isPeriodTo)}'"
                        : $"\r\n AND {column} > {Convert.ToInt32(fieldValue)}");
                    break;

                case CriteriaDTO.OperationType.LessThan:
                    sql.Append(isDate
                        ? $"\r\n AND {column} < '{ParseCriteriaDate(fieldValue, isPeriodTo)}'"
                        : $"\r\n AND {column} < {Convert.ToInt32(fieldValue)}");
                    break;

                case CriteriaDTO.OperationType.Like:
                    if (!isDate)
                        sql.Append($"\r\n AND {column} LIKE '%{fieldValue.Replace("'", "''")}%'");
                    break;

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

        // GB4 parity: CommonFunctionFrameDAL.ApplyCriteriaSb pushed PeriodTo/PeriodToDate/
        // UsagePeriodTo epoch values forward by (24*60*60)-1 seconds before formatting, so the
        // "to" boundary is inclusive of the whole day (23:59:59) rather than truncating to
        // midnight — PERIODFROM/PERIODTO/TASKDATE all carry a time component (see the
        // CONVERT(CHAR(5), ..., 108) time-of-day projections in ActionQB.GET_ACTIVITY_REGISTER),
        // so a midnight-truncated <= would silently drop same-day rows with a later time.
        private static string ParseCriteriaDate(string value, bool endOfDay = false)
        {
            if (string.IsNullOrWhiteSpace(value))
                return string.Empty;

            value = value.Trim().Trim('\'');

            // ISO date format: yyyy-MM-dd
            if (DateTime.TryParseExact(
                value,
                "yyyy-MM-dd",
                System.Globalization.CultureInfo.InvariantCulture,
                System.Globalization.DateTimeStyles.None,
                out var isoDate))
            {
                return endOfDay
                    ? isoDate.ToString("yyyy/MM/dd") + " 23:59:59"
                    : isoDate.ToString("yyyy/MM/dd");
            }

            // Unix timestamp
            if (long.TryParse(value, out var timestamp))
            {
                // Support both seconds and milliseconds
                DateTime date;

                if (timestamp < 10_000_000_000)
                {
                    // Unix seconds
                    date = DateTimeOffset
                        .FromUnixTimeSeconds(timestamp)
                        .DateTime;
                }
                else
                {
                    // Unix milliseconds
                    date = DateTimeOffset
                        .FromUnixTimeMilliseconds(timestamp)
                        .DateTime;
                }

                return endOfDay
                    ? date.ToString("yyyy/MM/dd") + " 23:59:59"
                    : date.ToString("yyyy/MM/dd");
            }

            throw new ArgumentException(
                $"Invalid date value '{value}'. Expected yyyy-MM-dd or Unix timestamp.");
        }
    }
}