using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Text;
using System.Text.Json;
using System.Threading;
using System.Threading.Tasks;
using GB5Shared.Connection;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Entity;
using GB5Shared.DTO.Framework.Logging;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5CommonFunction;
using GB5Shared.GB5Exception;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using Microsoft.Extensions.Logging;
using MMDAL.DTO.AccountBooksDetail;
using MMDAL.DTO.MMHead;
using MMDAL.DTO.MMReports;
using MMDAL.Query.MMReports;
using Newtonsoft.Json;

namespace MMDAL.CustomCode.MMReports
{
    public class MMReportsDAL : IMMReportsDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly ILogger<MMReportsDAL> _Logger;

        public MMReportsDAL(
            IQueryExecutor QueryExecutor,
            ILogger<MMReportsDAL> Logger)
        {
            _QueryExecutor = QueryExecutor;
            _Logger = Logger;
        }

        public async IAsyncEnumerable<HeadWithDetailRegisterDTO> MMHeadRegister(
        int OrderbyType,
        int FirstNumber,
        int MaxResult,
        CriteriaDTO CriteriaDTO,
        LoginDTO LoginDTO,
        bool IsDocument = false,
        bool Count = false,
        [EnumeratorCancellation] CancellationToken cancellationToken = default)
        {
            string sql;
            object parameters;
             
            CriteriaDTO ??= new CriteriaDTO();

            bool hasCriteria = CriteriaDTO.SectionCriteriaList?.Count > 0;
            CriteriaParameters criteriaParams = hasCriteria
                ? ParseCriteriaParameters(CriteriaDTO)
                : new CriteriaParameters();

            criteriaParams.PeriodFromDate = criteriaParams.PeriodFromDate == DateTime.MinValue
                ? DateTime.MinValue
                : criteriaParams.PeriodFromDate;
            criteriaParams.PeriodToDate = criteriaParams.PeriodToDate == DateTime.MaxValue
                ? DateTime.MaxValue
                : criteriaParams.PeriodToDate;

            sql = Count
                ? MMReportsQB.GET_MV_MM_REGISTER_REPORT_COUNT
                : MMReportsQB.GET_MV_MM_REGISTER_REPORT;

            sql = ApplyMV_MMRegister(sql, CriteriaDTO, LoginDTO);
            sql = ApplyMV_MMRegister(sql, LoginDTO.UserCriteriaDTO, LoginDTO);

            var sqlBuilder = new StringBuilder();
            if (hasCriteria)
            {
                ApplyAdditionalFilters(sqlBuilder, criteriaParams);
            }

            if (!Count)
            {
                string orderByClause = GetOrderByClause(OrderbyType);

                if (FirstNumber == -1 && MaxResult == -1)
                    sql += sqlBuilder.ToString() + $" ORDER BY {orderByClause}";
                else
                    sql = ApplyPagination(sql, sqlBuilder.ToString(), orderByClause, FirstNumber, MaxResult);
            }
            else
            {
                sql += sqlBuilder.ToString();
            }

            parameters = CreateParameters(criteriaParams, FirstNumber, MaxResult);

            // TEMPORARY diagnostic — rows outside the requested PeriodFromDate/PeriodToDate range
            // are coming back despite the filter apparently being built correctly in code. Log the
            // exact SQL and bound parameter values actually sent to the DB so this can be confirmed
            // from evidence instead of static-analysis guessing. Remove once root-caused.
            _Logger.LogWarning(
                "MMHeadRegister diagnostic — periodfromdate={PeriodFromDate}, periodtodate={PeriodToDate}, ParamKeys={ParamKeys}, SQL={Sql}",
                criteriaParams.PeriodFromDate, criteriaParams.PeriodToDate,
                string.Join(",", ((Dictionary<string, object>)parameters).Keys),
                sql);

            await foreach (var ReturnReport in _QueryExecutor.QueryStreamAsync<HeadWithDetailRegisterDTO>(
                LoginDTO, sql, parameters, cancellationToken).WithCancellation(cancellationToken))
            {
                yield return ReturnReport;
            }
        }


        private CriteriaParameters ParseCriteriaParameters(CriteriaDTO criteriaDTO)
        {
            var parameters = new CriteriaParameters();

            if (criteriaDTO?.SectionCriteriaList == null)
                return parameters;

            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                foreach (var attribute in section.AttributesCriteriaList)
                {
                    var fieldName = attribute.FieldName?.ToLower();
                    var fieldValue = attribute.FieldValue;

                    switch (fieldName)
                    {
                        case "includeremarks":
                            parameters.IncludeRemarks = Convert.ToString(fieldValue);
                            break;
                        case "periodfromdate":
                            parameters.PeriodFromDate = ConvertEpochToDate(fieldValue).Date;
                            break;
                        case "periodtodate":
                            parameters.PeriodToDate = ConvertEpochToDate(fieldValue).Date;
                            break;
                        case "contracttype":
                            parameters.ContractType = Convert.ToInt32(fieldValue);
                            break;
                        case "status":
                            parameters.Status = Convert.ToString(fieldValue);
                            break;
                        case "overdue":
                            parameters.Overdue = Convert.ToInt32(fieldValue);
                            break;
                        case "ebnumber":
                            parameters.EBNumber = Convert.ToString(fieldValue);
                            break;
                        case "gateentry":
                            parameters.GateEntry = Convert.ToString(fieldValue);
                            break;
                        case "einvoice":
                            parameters.EInvoice = Convert.ToString(fieldValue);
                            break;
                        case "dimensionrequired":
                            parameters.DimensionRequired = Convert.ToString(fieldValue);
                            break;
                        case "v1":
                            parameters.V1 = Convert.ToString(fieldValue);
                            break;
                        case "v2":
                            parameters.V2 = Convert.ToString(fieldValue);
                            break;
                        case "v3":
                            parameters.V3 = Convert.ToString(fieldValue);
                            break;
                        case "v4":
                            parameters.V4 = Convert.ToString(fieldValue);
                            break;
                        case "v5":
                            parameters.V5 = Convert.ToString(fieldValue);
                            break;
                        case "rework":
                            parameters.Rework = Convert.ToInt32(fieldValue);
                            break;
                        case "rejected":
                            parameters.Rejected = Convert.ToInt32(fieldValue);
                            break;
                        case "expiry":
                            parameters.Expiry = Convert.ToInt32(fieldValue);
                            break;
                    }
                }
            }

            return parameters;
        }

        // Accepts either a numeric epoch (ms or seconds — the historical /Date(N)/ style value
        // this method was originally written for) or a plain date/datetime string like
        // "2026-08-01" — some callers send criteria dates in ISO form rather than epoch.
        // Try epoch first (preserves existing behavior exactly), fall back to DateTime.Parse.
        private DateTime ConvertEpochToDate(object fieldValue)
        {
            string? raw = fieldValue switch
            {
                JsonElement je when je.ValueKind == JsonValueKind.Number => je.GetInt64().ToString(),
                JsonElement je => je.GetString(),
                _ => Convert.ToString(fieldValue)
            };

            if (long.TryParse(raw, out long epochValue))
                return FromEpoch(epochValue);

            // The frontend only ever intends to send a date, never a time-of-day — truncate any
            // time component that comes through on a well-formed single value too, so this method
            // always returns a pure date regardless of what the caller happened to include.
            if (DateTime.TryParse(raw, out DateTime parsedDate))
                return parsedDate.Date;

            // Known frontend defect: some date-range pickers send several comma-joined values in
            // one FieldValue (e.g. "2026-08-07,2026-08-06,2026-08-07 08:54:40.259") for an
            // OperationType (GreaterThan/LessThanOrEqualTo) that expects exactly one value —
            // confirmed this isn't a legitimate multi-value operation type for this field. Rather
            // than fail the whole report, pick a token that parses as a date. The frontend never
            // intentionally sends a time-of-day, so a token WITHOUT one (no space) is preferred
            // over one with — this makes the pick robust to token order, not just the token
            // position seen in the one example confirmed so far. Logged as a warning (not silently
            // swallowed) since this masks a client bug — fix belongs in whatever criteria/date-range
            // component built this value.
            if (raw != null && raw.Contains(','))
            {
                var tokens = raw.Split(',', StringSplitOptions.TrimEntries | StringSplitOptions.RemoveEmptyEntries);

                var ordered = tokens
                    .OrderBy(t => t.Contains(' ')) // date-only tokens (no time) first
                    .ToList();

                foreach (var token in ordered)
                {
                    if (long.TryParse(token, out long tokenEpoch))
                    {
                        _Logger.LogWarning(
                            "Criteria date value '{RawValue}' had multiple comma-joined values — using '{Token}'.",
                            raw, token);
                        return FromEpoch(tokenEpoch).Date;
                    }
                    if (DateTime.TryParse(token, out DateTime tokenDate))
                    {
                        _Logger.LogWarning(
                            "Criteria date value '{RawValue}' had multiple comma-joined values — using '{Token}'.",
                            raw, token);
                        return tokenDate.Date;
                    }
                }
            }

            throw new FormatException($"Criteria date value '{raw}' is neither a valid epoch timestamp nor a valid date string.");
        }

        private void ApplyAdditionalFilters(StringBuilder sqlBuilder, CriteriaParameters parameters)
        {
            if (parameters.DimensionRequired == "0")
            {
                if (parameters.PeriodFromDate != DateTime.MinValue)
                    sqlBuilder.Append("AND a.DocumentDate >= @periodfromdate ").AppendLine();

                if (parameters.PeriodToDate != DateTime.MaxValue)
                    sqlBuilder.Append("AND a.DocumentDate <= @periodtodate ").AppendLine();

                if (!string.IsNullOrEmpty(parameters.V1))
                    sqlBuilder.Append("AND a.v1 = @v1 ").AppendLine();

                if (!string.IsNullOrEmpty(parameters.V2))
                    sqlBuilder.Append("AND a.v2 = @v2 ").AppendLine();

                if (!string.IsNullOrEmpty(parameters.V3))
                    sqlBuilder.Append("AND a.v3 = @v3 ").AppendLine();

                if (!string.IsNullOrEmpty(parameters.V4))
                    sqlBuilder.Append("AND a.v4 = @v4 ").AppendLine();

                if (!string.IsNullOrEmpty(parameters.V5))
                    sqlBuilder.Append("AND a.v5 = @v5 ").AppendLine();
            }

            if (parameters.EBNumber == "0")
            {
                sqlBuilder.Append("AND a.EBNumber <> '' ").AppendLine();
            }

            if (parameters.IncludeRemarks == "0")
            {
                sqlBuilder.Append("AND a.includeRemarks in (0) ").AppendLine();
            }
            else if (parameters.IncludeRemarks == "1")
            {
                sqlBuilder.Append("AND a.includeRemarks in (1) ").AppendLine();
            }

            if (parameters.EInvoice == "0")
            {
                sqlBuilder.Append("AND (a.gstinnumber <> '' AND a.IRNnumber <> '')").AppendLine();
            }
            else if (parameters.EInvoice == "1")
            {
                sqlBuilder.Append("AND (a.gstinnumber <> '' AND a.IRNnumber = '')").AppendLine();
            }

            if (parameters.GateEntry == "0")
            {
                sqlBuilder.Append("AND a.gateentryid <> -1 ").AppendLine();
            }

            if (parameters.ContractType == 0)
            {
                // Date range already added by the DimensionRequired block above;
                // only add here when DimensionRequired is not "0"
                if (parameters.DimensionRequired != "0")
                {
                    sqlBuilder.Append("AND a.DocumentDate >= @periodfromdate").AppendLine();
                    sqlBuilder.Append("AND a.DocumentDate <= @periodtodate").AppendLine();
                }
            }
            else if (parameters.ContractType == 1)
            {
                sqlBuilder.Append("AND CAST(a.APPLICABLETO AS DATE) >= @periodtodate").AppendLine();
                sqlBuilder.Append("AND CAST(a.APPLICABLETO AS DATE) <= @periodtodate").AppendLine();
            }
            else if (parameters.ContractType == 2)
            {
                if (parameters.Expiry == 0)
                {
                    sqlBuilder.Append("AND CAST(a.APPLICABLETO AS DATE) >= @expirydate").AppendLine();
                }
                else if (parameters.Expiry == 1)
                {
                    sqlBuilder.Append("AND CAST(a.APPLICABLETO AS DATE) >= @periodtodate").AppendLine();
                }
            }

            if (parameters.Overdue == 0)
            {
                sqlBuilder.Append("AND a.DocumentDate <= @periodtodate");
            }
            else if (parameters.Overdue == 1)
            {
                sqlBuilder.Append("AND a.DocumentDate > @periodtodate");
            }

            if (parameters.Rework == 1)
            {
                sqlBuilder.Append("AND a.REWORKQUANTITY <> 0 ").AppendLine();
            }

            if (parameters.Rejected == 1)
            {
                sqlBuilder.AppendLine("AND a.REJECTEDQUANTITY <> 0 ");
            }
        }

        private string GetOrderByClause(int type)
        {
            return type switch
            {
                0 => "BIZTransactionClassId, DocumentDate, DocumentNumber",
                1 => "AccountCode, DocumentDate, DocumentNumber",
                2 or 3 => "ITEMCODE, AccountCode, DocumentDate, DocumentNumber",
                _ => "DocumentDate, DocumentNumber"
            };
        }

        private string ApplyPagination(string sql, string filters, string orderByClause, int firstNumber, int maxResult)
        {
            sql += filters;

            return $@"
                WITH Data_CTE AS (
                    SELECT *, 
                           ROW_NUMBER() OVER (ORDER BY {orderByClause}) AS RowNum
                    FROM (
                        {sql}
                    ) AS InnerQuery
                )
                SELECT * 
                FROM Data_CTE 
                WHERE RowNum BETWEEN @firstnumber + 1 AND @firstnumber + @maxresult";
        }

        private object CreateParameters(CriteriaParameters criteriaParams, int firstNumber, int maxResult)
        {
            var parameters = new Dictionary<string, object>();

            // Only pass dates if valid
            parameters.Add("periodfromdate",
    criteriaParams.PeriodFromDate != DateTime.MinValue
        ? (object)criteriaParams.PeriodFromDate
        : null);

            parameters.Add("periodtodate",
                criteriaParams.PeriodToDate != DateTime.MaxValue
                    ? (object)criteriaParams.PeriodToDate
                    : null);



            parameters.Add("v1", string.IsNullOrEmpty(criteriaParams.V1) ? null : (object)criteriaParams.V1);
            parameters.Add("v2", string.IsNullOrEmpty(criteriaParams.V2) ? null : (object)criteriaParams.V2);
            parameters.Add("v3", string.IsNullOrEmpty(criteriaParams.V3) ? null : (object)criteriaParams.V3);
            parameters.Add("v4", string.IsNullOrEmpty(criteriaParams.V4) ? null : (object)criteriaParams.V4);
            parameters.Add("v5", string.IsNullOrEmpty(criteriaParams.V5) ? null : (object)criteriaParams.V5);

            parameters.Add("dimensionrequired", string.IsNullOrEmpty(criteriaParams.DimensionRequired) ? null : (object)criteriaParams.DimensionRequired);
            parameters.Add("einvoice", string.IsNullOrEmpty(criteriaParams.EInvoice) ? null : (object)criteriaParams.EInvoice);
            parameters.Add("gateentry", string.IsNullOrEmpty(criteriaParams.GateEntry) ? null : (object)criteriaParams.GateEntry);
            parameters.Add("ebnumber", string.IsNullOrEmpty(criteriaParams.EBNumber) ? null : (object)criteriaParams.EBNumber);

            parameters.Add("status", string.IsNullOrEmpty(criteriaParams.Status) ? null : (object)criteriaParams.Status);


            parameters.Add("overdue", criteriaParams.Overdue);
            parameters.Add("contracttype", criteriaParams.ContractType);
            parameters.Add("rework", criteriaParams.Rework);
            parameters.Add("rejected", criteriaParams.Rejected);
            parameters.Add("expiry", criteriaParams.Expiry);

            if (maxResult != -1)
            {
                parameters.Add("firstnumber", firstNumber);
                parameters.Add("maxresult", maxResult);
            }

            return parameters;
        }

        public string ApplyMV_MMRegister(string sql, CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            if (criteriaDTO == null)
            {
                return sql;
            }

            string[,] criteriaWithQueryField = new string[,]
            {
                {"BizTransactionId", "a.BIZTransactionId"},
                {"BIZTransactionTypeId","a.BIZTransactionTypeId"},
                {"BIZTransactionClassId","a.BIZTransactionClassId"},
                {"BIZTransactionSubClassId","a.BIZTransactionSubClassId"},
                {"OUId","a.OUId"},
                {"PartyId","a.PartyId"},
                {"PartyBranchId","a.PartyBranchId"},
                {"PeriodId", "a.PeriodId"},
                {"PartyContactId", "a.PartyContactId"},
                {"StoreId", "a.StoreId"},
                {"FromStoreId", "a.FromStoreId"},
                {"ToStoreId", "a.ToStoreId"},
                {"DepartmentId", "a.DepartmentId"},
                {"AllocationId", "a.AllocationId"},
                {"ItemAllocationId", "a.AllocationId"},
                {"InchargeId", "a.InchargeId"},
                {"TaxTransactionTypeId", "a.TaxTransactionTypeId"},
                {"DutyTransactionTypeId", "a.DutyTransactionTypeId"},
                {"TransporterId", "a.TransporterId"},
                {"VehicleId", "a.VehicleId"},
                {"DriverId", "a.DriverId"},
                {"ParameterSetId", "a.ParameterSetId"},
                {"CreatedById", "a.CreatedById"},
                {"ModifiedById", "a.ModifiedById"},
                {"Status", "a.Status"},
                {"ItemId", "a.ItemId"},
                {"SKUId", "a.SKUId"},
                {"StockPostType", "a.StockPostType"},
                {"DocumentId", "a.DocumentId"},
                {"MaterialAccountId", "a.MATERIALACCOUNTID"},
                {"AccountId", "a .DeatilACCOUNTID"},
                {"FromDocumentId", "a.DocumentId"},
                {"ToDocumentId", "a.DocumentId"},
                {"MaterialReceiptStatus","a.MATERIALRECEIPTSTATUS"},
                {"IsStockPosting", "a.ISSTOCKPOSTING"},
                {"ItemGroupId","a.itemgroupid"},
                {"ItemCategoryId","a.ItemCategoryId"},
                {"ItemBrandId","a.ItemBrandId"},
                {"CodeCategoryId","a.CODECATEGORYID"},
                {"ModeofPayment", "a.ModeofPayment"},
                {"DocumentStatus", "a.DetailDocumentStatus"},
                {"RecoveryType", "a.RecoveryType"},
                {"ReservationType", "a.ReservationType"},
                {"RecoveryPartyBranchId", "a.RecoveryPartyBranchId"},
                {"DetailReasonId", "a.reasonid"},
                {"ClaimId", "a.ClaimId"},
                {"ReferenceEmployeeId", "a.ReferenceEmployeeId"},
                {"ReferencePartyId", "a.ReferencePartyId"},
                {"DeferalPlanId","a.DEFERALPLANID"},
                {"InspectionNumberId", "a.InspectionNumberId"},
                {"ContactId", "a.ContactId"},
                {"MultiBIZTransactionClassId","a.BIZTransactionClassId"},
                {"AliasItemGroupId", "a.ALIASITEMGROUPID"},
                {"SubCategoryId","a.SubCategoryId"},
                {"ItemTypeId","a.ItemTypeId"},
                {"DocumentType","a.DocumentType"},
                {"PartyAgentId","a.PartyAgentId"},
                {"StateId","a.StateId"},
                {"StandardRegionId","a.StandardRegionId"},
            };

            return ApplyCriteria(sql, criteriaWithQueryField, criteriaDTO, loginDTO);
        }

        public string ApplyCriteria(string sql, string[,] criteriaWithQueryField, CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            if (criteriaDTO == null || criteriaDTO.SectionCriteriaList == null || criteriaDTO.SectionCriteriaList.Count == 0)
            {
                return sql;
            }

            int length = criteriaWithQueryField.GetLength(0);
            string text = "";
            string text2 = "";

            for (int i = 0; i < criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList.Count; i++)
            {
                for (int j = 0; j < length; j++)
                {
                    text = criteriaWithQueryField[j, 0];
                    text2 = criteriaWithQueryField[j, 1];
                    string text3 = ((text2.Length < 7) ? text2 : text2.Substring(0, 7));

                    if (!(criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldName.ToLower() == text.ToLower()))
                    {
                        continue;
                    }

                    bool flag = false;
                    string text4 = "";

                    if (text.ToLower() == "PeriodFromDate".ToLower() || text.ToLower() == "UsagePeriodFrom".ToLower() || text.ToLower() == "PeriodFrom".ToLower() || text.ToLower() == "PeriodToDate".ToLower() || text.ToLower() == "UsagePeriodTo".ToLower() || text.ToLower() == "PeriodTo".ToLower() || text.ToLower() == "ModifiedOn".ToLower() || text.ToLower() == "TimeStamp".ToLower() || text.ToLower() == "TaskPlanDate".ToLower() || text.ToLower() == "ExchangeDate".ToLower() || text.ToLower() == "DateofReginationPeriodFrom".ToLower() || text.ToLower() == "DateofReginationPeriodTo".ToLower() || text.ToLower() == "DateofJoiningPeriodFrom".ToLower() || text.ToLower() == "DateofJoiningPeriodTo".ToLower())
                    {
                        flag = true;
                        long unixTime = Convert.ToInt64(criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue);
                        DateTime dateTime = FromEpoch(unixTime);
                        if (text.ToLower() == "PeriodTo".ToLower() || text.ToLower() == "PeriodToDate".ToLower() || text.ToLower() == "UsagePeriodTo".ToLower())
                        {
                            dateTime = dateTime.AddSeconds(86399.0);
                        }
                        string text5 = "yyyy-MM-dd HH:mm:ss";
                        if (loginDTO.DatabaseType == 1)
                        {
                            text5 = "dd-MMM-yyyy hh:mm:ss tt";
                        }
                        text4 = dateTime.ToString(text5);
                    }

                    if (criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue == "")
                    {
                        continue;
                    }

                    if (criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].JoinType == CriteriaDTO.AttributeJoinOperationType.And)
                    {
                        sql += "   and ";
                    }
                    else if (criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].JoinType == CriteriaDTO.AttributeJoinOperationType.Or)
                    {
                        sql += "   or ";
                    }
                    else
                    {
                        sql += "   and ";
                    }

                    if (criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].OperationType == CriteriaDTO.OperationType.Equal)
                    {
                        sql = (flag ? ((!(text3 == "CONVERT")) ? (sql + text2 + "='" + text4 + "' \r\n") : (sql + text2 + "=cast('" + text4 + "' as date)\r\n")) : (sql + text2 + " IN(" + criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue?.ToString() + ") \r\n"));
                    }
                    else if (criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].OperationType == CriteriaDTO.OperationType.In)
                    {
                        if (!flag)
                        {
                            sql = sql + text2 + " IN(" + criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue?.ToString() + ") \r\n";
                        }
                    }
                    else if (criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].OperationType == CriteriaDTO.OperationType.NotIn)
                    {
                        if (!flag)
                        {
                            sql = sql + text2 + " NOT IN(" + criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue?.ToString() + ") \r\n";
                        }
                    }
                    else if (criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].OperationType == CriteriaDTO.OperationType.NotEqual)
                    {
                        sql = (flag ? ((!(text3 == "CONVERT")) ? (sql + text2 + " <>'" + text4 + "' \r\n") : (sql + text2 + " <>cast('" + text4 + "' as date)\r\n")) : (sql + text2 + " <>" + criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue?.ToString() + " \r\n"));
                    }
                    else if (criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].OperationType == CriteriaDTO.OperationType.GreaterThanOrEqualTo)
                    {
                        sql = (flag ? ((!(text3 == "CONVERT")) ? (sql + text2 + " >='" + text4 + "' \r\n") : (sql + text2 + " >=cast('" + text4 + "' as date)\r\n")) : (sql + text2 + " >='" + criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue?.ToString() + "' \r\n"));
                    }
                    else if (criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].OperationType == CriteriaDTO.OperationType.LessThanOrEqualTo)
                    {
                        sql = (flag ? ((!(text3 == "CONVERT")) ? (sql + text2 + " <='" + text4 + "' \r\n") : (sql + text2 + " <=cast('" + text4 + "' as date) \r\n")) : (sql + text2 + " <='" + criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue?.ToString() + "' \r\n"));
                    }
                    else if (criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].OperationType == CriteriaDTO.OperationType.GreaterThan)
                    {
                        sql = (flag ? ((!(text3 == "CONVERT")) ? (sql + text2 + " >'" + text4 + "' \r\n") : (sql + text2 + " >cast('" + text4 + "' as date) \r\n")) : (sql + text2 + " >" + criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue?.ToString() + " \r\n"));
                    }
                    else if (criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].OperationType == CriteriaDTO.OperationType.LessThan)
                    {
                        sql = (flag ? ((!(text3 == "CONVERT")) ? (sql + text2 + " <'" + text4 + "' \r\n") : (sql + text2 + " <cast('" + text4 + "' as date) \r\n")) : (sql + text2 + " <" + criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue?.ToString() + " \r\n"));
                    }
                    else if (criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].OperationType == CriteriaDTO.OperationType.Like && !flag)
                    {
                        sql = sql + text2 + " LIKE ('%" + criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[i].FieldValue?.ToString() + "%') \r\n";
                    }
                }
            }

            return sql;
        }

        public static DateTime FromEpoch(long epoch)
        {
            return epoch > 9999999999
                ? DateTimeOffset.FromUnixTimeMilliseconds(epoch).UtcDateTime
                : DateTimeOffset.FromUnixTimeSeconds(epoch).UtcDateTime;
        }

        private class CriteriaParameters
        {
            public string IncludeRemarks { get; set; } = "";
            public DateTime PeriodFromDate { get; set; } = DateTime.MinValue;
            public DateTime PeriodToDate { get; set; } = DateTime.MaxValue;
            public int ContractType { get; set; }
            public string Status { get; set; } = "";
            public int Overdue { get; set; } = 2;
            public string EBNumber { get; set; } = "1";
            public string GateEntry { get; set; } = "1";
            public string EInvoice { get; set; } = "2";
            public string DimensionRequired { get; set; } = "0";
            public string V1 { get; set; } = "";
            public string V2 { get; set; } = "";
            public string V3 { get; set; } = "";
            public string V4 { get; set; } = "";
            public string V5 { get; set; } = "";
            public int Rework { get; set; }
            public int Rejected { get; set; }
            public int Expiry { get; set; } = -1;
        }

        public async Task<(List<MMPendingStatusReportDTO> Items, int Total)> GetMMPendingStatusReport(
            int firstNumber, int maxResult, string bizTransactionClassIds, int? ouId, DateTime periodFromDate, DateTime periodToDate, LoginDTO loginDTO, CancellationToken ct = default)
        {
            var countParam = new { BizTransactionClassIds = bizTransactionClassIds, OuId = ouId, TenantId = loginDTO.ClientId, PeriodFromDate = periodFromDate, PeriodToDate = periodToDate };
            string listSql = MMReportsQB.GET_MM_PENDING_STATUS_REPORT + " ORDER BY BiztransactionClassId OFFSET @Offset ROWS FETCH NEXT @MaxResult ROWS ONLY";
            var listParam = new Dictionary<string, object>
            {
                ["BizTransactionClassIds"] = bizTransactionClassIds,
                ["OuId"] = ouId,
                ["TenantId"] = loginDTO.ClientId,
                ["PeriodFromDate"] = periodFromDate,
                ["PeriodToDate"] = periodToDate,
                ["Offset"] = firstNumber <= 1 ? 0 : firstNumber - 1,
                ["MaxResult"] = maxResult > 0 ? maxResult : 50
            };
            var listTask = _QueryExecutor.QueryAsync<MMPendingStatusReportDTO>(loginDTO, listSql, listParam, cancellationToken: ct);
            var countTask = _QueryExecutor.ExecuteScalarAsync<int>(loginDTO, $"SELECT COUNT(1) FROM ({MMReportsQB.GET_MM_PENDING_STATUS_REPORT}) t", countParam, cancellationToken: ct);
            await Task.WhenAll(listTask, countTask).ConfigureAwait(false);
            return (listTask.Result.ToList(), countTask.Result);
        }

        public async Task<IEnumerable<PendingCountByTransactionTypeDTO>> GetPendingCountsByTransactionType(
            IEnumerable<int> bizTransactionTypeIds, LoginDTO loginDTO, CancellationToken ct = default)
        {
            var ids = bizTransactionTypeIds.ToList();
            if (ids.Count == 0) return Enumerable.Empty<PendingCountByTransactionTypeDTO>();

            var param = new { BizTransactionTypeIds = ids };
            return await _QueryExecutor.QueryAsync<PendingCountByTransactionTypeDTO>(
                loginDTO, MMReportsQB.GET_PENDING_COUNT_BY_TRANSACTIONTYPE, param, cancellationToken: ct).ConfigureAwait(false);
        }

        // Procurement Overview Dashboard (Phase 1) -----------------------------------------------

        private static decimal ComputeContractUtilizationPercent(List<ContractTypeSplitDTO> contractSplit, decimal totalValue)
        {
            // Heuristic: ContractTypeId > 0 == an explicit contract/rate-agreement type was set on
            // the document; <= 0 covers both "no contract type" (FK unset/default) and this
            // codebase's usual negative-sentinel convention for "NONE". NOT verified against live
            // MCONTRACTTYPE picklist data for the target tenant -- confirm before trusting this
            // split for a specific client.
            if (totalValue <= 0) return 0;
            var contractValue = contractSplit.Where(c => c.ContractTypeId > 0).Sum(c => c.TotalValue);
            return Math.Round(100m * contractValue / totalValue, 1);
        }

        public async Task<ProcurementSpendOverviewDTO> GetProcurementSpendOverview(
            string bizTransactionClassIdsCsv, IEnumerable<int> bizTransactionClassIds,
            int? ouId, DateTime periodFromDate, DateTime periodToDate, LoginDTO loginDTO, CancellationToken ct = default)
        {
            var param = new { BizTransactionClassIds = bizTransactionClassIds.ToList(), OuId = ouId, PeriodFromDate = periodFromDate, PeriodToDate = periodToDate };

            var monthlyTask = _QueryExecutor.QueryAsync<MonthlySpendTrendDTO>(loginDTO, MMReportsQB.GET_MM_MONTHLY_SPEND_TREND, param, cancellationToken: ct);
            var categoryTask = _QueryExecutor.QueryAsync<CategorySpendDTO>(loginDTO, MMReportsQB.GET_MM_CATEGORY_SPEND, param, cancellationToken: ct);
            var contractTask = _QueryExecutor.QueryAsync<ContractTypeSplitDTO>(loginDTO, MMReportsQB.GET_MM_CONTRACT_TYPE_SPLIT, param, cancellationToken: ct);
            var suppliersTask = GetMMRegisterPartyWiseSummaryReport(1, int.MaxValue, bizTransactionClassIdsCsv, 0, ouId, loginDTO, ct);

            await Task.WhenAll(monthlyTask, categoryTask, contractTask, suppliersTask).ConfigureAwait(false);

            var monthly = monthlyTask.Result.ToList();
            var totalSpend = monthly.Sum(m => m.TotalValue);
            var contractSplit = contractTask.Result.ToList();

            return new ProcurementSpendOverviewDTO
            {
                TotalSpendYTD = totalSpend,
                ActiveSupplierCount = suppliersTask.Result.Items.Count,
                ContractUtilizationPercent = ComputeContractUtilizationPercent(contractSplit, totalSpend),
                MonthlyTrend = monthly,
                CategoryBreakdown = categoryTask.Result.OrderByDescending(c => c.TotalValue).ToList(),
                ContractTypeSplit = contractSplit
            };
        }

        private async Task<FunnelStageDTO> GetFunnelStage(string stageName, int bizTransactionClassId, LoginDTO loginDTO, CancellationToken ct)
        {
            var param = new { BizTransactionClassId = bizTransactionClassId };
            var row = (await _QueryExecutor.QueryAsync<FunnelStageDTO>(loginDTO, MMReportsQB.GET_PENDING_COUNT_BY_TRANSACTIONCLASS, param, cancellationToken: ct).ConfigureAwait(false)).FirstOrDefault();
            return new FunnelStageDTO
            {
                StageName = stageName,
                BizTransactionTypeId = bizTransactionClassId,
                OpenCount = row?.OpenCount ?? 0,
                OpenQuantity = row?.OpenQuantity ?? 0
            };
        }

        public async Task<PRToPOTrackerDashboardDTO> GetPRToPOTrackerDashboard(
            int firstNumber, int maxResult,
            int prBizTransactionClassId, int inquiryBizTransactionClassId,
            int quotationBizTransactionClassId, int poBizTransactionClassId,
            int? ouId, DateTime periodFromDate, DateTime periodToDate, LoginDTO loginDTO, CancellationToken ct = default)
        {
            var prCountTask = GetFunnelStage("PR Created", prBizTransactionClassId, loginDTO, ct);
            var inquiryCountTask = GetFunnelStage("Inquiry Sent", inquiryBizTransactionClassId, loginDTO, ct);
            var quotationCountTask = GetFunnelStage("Quotation Received", quotationBizTransactionClassId, loginDTO, ct);
            var poCountTask = GetFunnelStage("PO Created", poBizTransactionClassId, loginDTO, ct);

            var agingParam = new { BizTransactionClassId = prBizTransactionClassId, OuId = ouId };
            var agingTask = _QueryExecutor.QueryAsync<PRAgingBucketDTO>(loginDTO, MMReportsQB.GET_PR_AGING_BUCKETS, agingParam, cancellationToken: ct);

            var cycleParam = new
            {
                PoClassId = poBizTransactionClassId,
                PrClassId = prBizTransactionClassId,
                OuId = ouId,
                PeriodFromDate = periodFromDate,
                PeriodToDate = periodToDate
            };
            var cycleTask = _QueryExecutor.QueryAsync<CycleTimeTrendDTO>(loginDTO, MMReportsQB.GET_PR_TO_PO_CYCLE_TIME_TREND, cycleParam, cancellationToken: ct);

            string openPrsSql = MMReportsQB.GET_MM_PENDING_REGISTER_LIST_REPORT + " ORDER BY DocumentDate DESC OFFSET @Offset ROWS FETCH NEXT @MaxResult ROWS ONLY";
            var openPrsParam = new Dictionary<string, object>
            {
                ["BizTransactionClassIds"] = new List<int> { prBizTransactionClassId },
                ["OuId"] = ouId,
                ["TenantId"] = loginDTO.ClientId,
                ["Offset"] = firstNumber <= 1 ? 0 : firstNumber - 1,
                ["MaxResult"] = maxResult > 0 ? maxResult : 50
            };
            var openPrsTask = _QueryExecutor.QueryAsync<MMPendingRegisterListReportDTO>(loginDTO, openPrsSql, openPrsParam, cancellationToken: ct);

            await Task.WhenAll(prCountTask, inquiryCountTask, quotationCountTask, poCountTask, agingTask, cycleTask, openPrsTask).ConfigureAwait(false);

            var funnel = new List<FunnelStageDTO> { prCountTask.Result, inquiryCountTask.Result, quotationCountTask.Result, poCountTask.Result };

            return new PRToPOTrackerDashboardDTO
            {
                Funnel = funnel,
                AgingBuckets = agingTask.Result.ToList(),
                CycleTimeTrend = cycleTask.Result.ToList(),
                OpenPRs = openPrsTask.Result.ToList()
            };
        }

        public async Task<POMonitoringDashboardDTO> GetPOMonitoringDashboard(
            string poBizTransactionClassIdsCsv, int poBizTransactionClassId, int grnBizTransactionClassId,
            int? ouId, DateTime periodFromDate, DateTime periodToDate, LoginDTO loginDTO, CancellationToken ct = default)
        {
            var spendParam = new { BizTransactionClassIds = new[] { poBizTransactionClassId }, OuId = ouId, PeriodFromDate = periodFromDate, PeriodToDate = periodToDate };
            var monthlyTask = _QueryExecutor.QueryAsync<MonthlySpendTrendDTO>(loginDTO, MMReportsQB.GET_MM_MONTHLY_SPEND_TREND, spendParam, cancellationToken: ct);
            var contractTask = _QueryExecutor.QueryAsync<ContractTypeSplitDTO>(loginDTO, MMReportsQB.GET_MM_CONTRACT_TYPE_SPLIT, spendParam, cancellationToken: ct);
            var supplierTask = GetMMRegisterPartyWiseSummaryReport(1, int.MaxValue, poBizTransactionClassIdsCsv, 0, ouId, loginDTO, ct);

            var detailParam = new
            {
                PoClassId = poBizTransactionClassId,
                GrnClassId = grnBizTransactionClassId,
                OuId = ouId,
                PeriodFromDate = periodFromDate,
                PeriodToDate = periodToDate
            };
            var detailTask = _QueryExecutor.QueryAsync<PurchaseStatusReportDTO>(loginDTO, MMReportsQB.GET_PO_MONITORING_DETAIL, detailParam, cancellationToken: ct);

            await Task.WhenAll(monthlyTask, contractTask, supplierTask, detailTask).ConfigureAwait(false);

            var detail = detailTask.Result.ToList();
            var totalPOValue = detail.Sum(d => d.POValue);
            var openPOs = detail.Where(d => d.GRNId == 0).ToList();
            var withGrn = detail.Where(d => d.GRNId != 0).ToList();
            var onTime = withGrn.Count(d => d.GRNDate <= d.PlannedDeliveryDate);
            var delayed = withGrn.Count - onTime;

            var deliveryStatus = new List<DeliveryStatusSplitDTO>
            {
                new() { StatusLabel = "OnTime", Count = onTime, TotalValue = withGrn.Where(d => d.GRNDate <= d.PlannedDeliveryDate).Sum(d => d.GRNValue) },
                new() { StatusLabel = "Delayed", Count = delayed, TotalValue = withGrn.Where(d => d.GRNDate > d.PlannedDeliveryDate).Sum(d => d.GRNValue) },
                new() { StatusLabel = "PendingReceipt", Count = openPOs.Count, TotalValue = openPOs.Sum(d => d.POValue) },
            };

            return new POMonitoringDashboardDTO
            {
                TotalPOValue = totalPOValue,
                OpenPOCount = openPOs.Count,
                OnTimeDeliveryPercent = withGrn.Count > 0 ? Math.Round(100m * onTime / withGrn.Count, 1) : 0,
                DelayedPOCount = delayed,
                ContractUtilizationPercent = ComputeContractUtilizationPercent(contractTask.Result.ToList(), totalPOValue),
                MonthlyTrend = monthlyTask.Result.ToList(),
                SupplierWiseValue = supplierTask.Result.Items,
                DeliveryStatus = deliveryStatus,
                OpenPOs = openPOs.OrderByDescending(d => d.DocumentDate).ToList()
            };
        }

        public async Task<SpendAnalysisDashboardDTO> GetSpendAnalysisDashboard(
            string bizTransactionClassIdsCsv, IEnumerable<int> bizTransactionClassIds,
            int? ouId, DateTime periodFromDate, DateTime periodToDate, LoginDTO loginDTO, CancellationToken ct = default)
        {
            var param = new { BizTransactionClassIds = bizTransactionClassIds.ToList(), OuId = ouId, PeriodFromDate = periodFromDate, PeriodToDate = periodToDate };

            var monthlyTask = _QueryExecutor.QueryAsync<MonthlySpendTrendDTO>(loginDTO, MMReportsQB.GET_MM_MONTHLY_SPEND_TREND, param, cancellationToken: ct);
            var categoryTask = _QueryExecutor.QueryAsync<CategorySpendDTO>(loginDTO, MMReportsQB.GET_MM_CATEGORY_SPEND, param, cancellationToken: ct);
            var contractTask = _QueryExecutor.QueryAsync<ContractTypeSplitDTO>(loginDTO, MMReportsQB.GET_MM_CONTRACT_TYPE_SPLIT, param, cancellationToken: ct);
            var suppliersTask = GetMMRegisterPartyWiseSummaryReport(1, int.MaxValue, bizTransactionClassIdsCsv, 0, ouId, loginDTO, ct);

            await Task.WhenAll(monthlyTask, categoryTask, contractTask, suppliersTask).ConfigureAwait(false);

            var monthly = monthlyTask.Result.ToList();
            var totalSpend = monthly.Sum(m => m.TotalValue);

            return new SpendAnalysisDashboardDTO
            {
                TotalSpendYTD = totalSpend,
                ContractCoveragePercent = ComputeContractUtilizationPercent(contractTask.Result.ToList(), totalSpend),
                MonthlyTrend = monthly,
                CategoryBreakdown = categoryTask.Result.OrderByDescending(c => c.TotalValue).ToList(),
                TopSuppliers = suppliersTask.Result.Items.OrderByDescending(s => s.TotalValue).Take(10).ToList()
            };
        }

        // Parses a CSV of BizTransactionClassIds into a real List<int> for Dapper's "IN @Param" list
        // expansion. Both GET_MM_REGISTER_PARTYWISE_SUMMARY and GET_MM_PENDING_REGISTER_PARTYWISE_SUMMARY
        // (and GET_MM_PENDING_REGISTER_LIST_REPORT below) were fixed off STRING_SPLIT for this same
        // reason -- see the comments on each. Never pass the raw CSV string to those queries anymore.
        private static List<int> ParseIdCsv(string csv)
        {
            if (string.IsNullOrWhiteSpace(csv)) return new List<int>();
            return csv.Split(',', StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries)
                .Select(s => int.TryParse(s, out var v) ? v : (int?)null)
                .Where(v => v.HasValue)
                .Select(v => v!.Value)
                .ToList();
        }

        public async Task<(List<MMRegisterPartyWiseSummaryReportDTO> Items, int Total)> GetMMRegisterPartyWiseSummaryReport(
            int firstNumber, int maxResult, string bizTransactionClassIds, int type, int? ouId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            string baseSql = type == 1 ? MMReportsQB.GET_MM_PENDING_REGISTER_PARTYWISE_SUMMARY : MMReportsQB.GET_MM_REGISTER_PARTYWISE_SUMMARY;
            var classIds = ParseIdCsv(bizTransactionClassIds);
            var countParam = new { BizTransactionClassIds = classIds, OuId = ouId, TenantId = loginDTO.ClientId };
            string listSql = baseSql + " ORDER BY PartyName OFFSET @Offset ROWS FETCH NEXT @MaxResult ROWS ONLY";
            var listParam = new Dictionary<string, object>
            {
                ["BizTransactionClassIds"] = classIds,
                ["OuId"] = ouId,
                ["TenantId"] = loginDTO.ClientId,
                ["Offset"] = firstNumber <= 1 ? 0 : firstNumber - 1,
                ["MaxResult"] = maxResult > 0 ? maxResult : 50
            };
            var listTask = _QueryExecutor.QueryAsync<MMRegisterPartyWiseSummaryReportDTO>(loginDTO, listSql, listParam, cancellationToken: ct);
            var countTask = _QueryExecutor.ExecuteScalarAsync<int>(loginDTO, $"SELECT COUNT(1) FROM ({baseSql}) t", countParam, cancellationToken: ct);
            await Task.WhenAll(listTask, countTask).ConfigureAwait(false);
            return (listTask.Result.ToList(), countTask.Result);
        }

        public async Task<(List<MMRegisterItemWiseReportDTO> Items, int Total)> GetMMRegisterItemWiseReport(
            int firstNumber, int maxResult, string bizTransactionClassIds, int? ouId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            var countParam = new { BizTransactionClassIds = bizTransactionClassIds, OuId = ouId, TenantId = loginDTO.ClientId };
            string listSql = MMReportsQB.GET_MM_REGISTER_ITEMWISE_REPORT + " ORDER BY ItemName OFFSET @Offset ROWS FETCH NEXT @MaxResult ROWS ONLY";
            var listParam = new Dictionary<string, object>
            {
                ["BizTransactionClassIds"] = bizTransactionClassIds,
                ["OuId"] = ouId,
                ["TenantId"] = loginDTO.ClientId,
                ["Offset"] = firstNumber <= 1 ? 0 : firstNumber - 1,
                ["MaxResult"] = maxResult > 0 ? maxResult : 50
            };
            var listTask = _QueryExecutor.QueryAsync<MMRegisterItemWiseReportDTO>(loginDTO, listSql, listParam, cancellationToken: ct);
            var countTask = _QueryExecutor.ExecuteScalarAsync<int>(loginDTO, $"SELECT COUNT(1) FROM ({MMReportsQB.GET_MM_REGISTER_ITEMWISE_REPORT}) t", countParam, cancellationToken: ct);
            await Task.WhenAll(listTask, countTask).ConfigureAwait(false);
            return (listTask.Result.ToList(), countTask.Result);
        }

        public async Task<(List<MMPendingRegisterListReportDTO> Items, int Total)> GetMMPendingRegisterListReport(
            int firstNumber, int maxResult, string bizTransactionClassIds, int? ouId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            var classIds = ParseIdCsv(bizTransactionClassIds);
            var countParam = new { BizTransactionClassIds = classIds, OuId = ouId, TenantId = loginDTO.ClientId };
            string listSql = MMReportsQB.GET_MM_PENDING_REGISTER_LIST_REPORT + " ORDER BY DocumentDate DESC OFFSET @Offset ROWS FETCH NEXT @MaxResult ROWS ONLY";
            var listParam = new Dictionary<string, object>
            {
                ["BizTransactionClassIds"] = classIds,
                ["OuId"] = ouId,
                ["TenantId"] = loginDTO.ClientId,
                ["Offset"] = firstNumber <= 1 ? 0 : firstNumber - 1,
                ["MaxResult"] = maxResult > 0 ? maxResult : 50
            };
            var listTask = _QueryExecutor.QueryAsync<MMPendingRegisterListReportDTO>(loginDTO, listSql, listParam, cancellationToken: ct);
            var countTask = _QueryExecutor.ExecuteScalarAsync<int>(loginDTO, $"SELECT COUNT(1) FROM ({MMReportsQB.GET_MM_PENDING_REGISTER_LIST_REPORT}) t", countParam, cancellationToken: ct);
            await Task.WhenAll(listTask, countTask).ConfigureAwait(false);
            return (listTask.Result.ToList(), countTask.Result);
        }

        // Sales Overview Dashboard (Phase 1) -- Enquiry->Quotation->Order funnel tile. Reuses
        // GetFunnelStage/GET_PR_AGING_BUCKETS/GET_MM_PENDING_REGISTER_LIST_REPORT verbatim, exactly
        // as GetPRToPOTrackerDashboard does below -- these primitives are generic across any
        // BizTransactionClass, not procurement-specific. No cycle-time-trend here (MV_MMRegister-based,
        // confirmed empty on GB5DEMO for every class, Sales included).
        public async Task<SalesFunnelDashboardDTO> GetSalesFunnelDashboard(
            int firstNumber, int maxResult,
            int enquiryBizTransactionClassId, int quotationBizTransactionClassId, int orderBizTransactionClassId,
            int? ouId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            var enquiryCountTask = GetFunnelStage("Enquiry", enquiryBizTransactionClassId, loginDTO, ct);
            var quotationCountTask = GetFunnelStage("Quotation", quotationBizTransactionClassId, loginDTO, ct);
            var orderCountTask = GetFunnelStage("Order", orderBizTransactionClassId, loginDTO, ct);

            var agingParam = new { BizTransactionClassId = enquiryBizTransactionClassId, OuId = ouId };
            var agingTask = _QueryExecutor.QueryAsync<PRAgingBucketDTO>(loginDTO, MMReportsQB.GET_PR_AGING_BUCKETS, agingParam, cancellationToken: ct);

            string openDocsSql = MMReportsQB.GET_MM_PENDING_REGISTER_LIST_REPORT + " ORDER BY DocumentDate DESC OFFSET @Offset ROWS FETCH NEXT @MaxResult ROWS ONLY";
            var openDocsParam = new Dictionary<string, object>
            {
                ["BizTransactionClassIds"] = new List<int> { enquiryBizTransactionClassId, quotationBizTransactionClassId, orderBizTransactionClassId },
                ["OuId"] = ouId,
                ["TenantId"] = loginDTO.ClientId,
                ["Offset"] = firstNumber <= 1 ? 0 : firstNumber - 1,
                ["MaxResult"] = maxResult > 0 ? maxResult : 50
            };
            var openDocsTask = _QueryExecutor.QueryAsync<MMPendingRegisterListReportDTO>(loginDTO, openDocsSql, openDocsParam, cancellationToken: ct);

            await Task.WhenAll(enquiryCountTask, quotationCountTask, orderCountTask, agingTask, openDocsTask).ConfigureAwait(false);

            return new SalesFunnelDashboardDTO
            {
                Funnel = new List<FunnelStageDTO> { enquiryCountTask.Result, quotationCountTask.Result, orderCountTask.Result },
                AgingBuckets = agingTask.Result.ToList(),
                OpenSalesDocuments = openDocsTask.Result.ToList()
            };
        }
    }
}