using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5Exception;
using GB5Shared.QueryExecutor;
using MMDAL.DTO.Indent;
using MMDAL.DTO.ItemReason;
using MMDAL.DTO.Production;
using MMDAL.Query.Indent;
using MMDAL.Query.ItemReason;
using MMDAL.Query.Production;
using Newtonsoft.Json;
using System.Data.Common;
using static GB5Shared.GB5Constant.Constant;

namespace MMDAL.CustomCode.Production
{
    public class ProductionDAL : IProductionDAL
    {
        private readonly IQueryExecutor _qe;

        public ProductionDAL(IQueryExecutor queryExecutor)
            => _qe = queryExecutor;

        // ── Get header ────────────────────────────────────────────────────────

        public async Task<ProductionHeaderViewDTO?> GetProduction(
            int productionId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionQB.GET_PRODUCTION_SQL,
                DBType.PostGre => ProductionQB.GET_PRODUCTION_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            return await _qe
                .QuerySingleAsync<ProductionHeaderViewDTO>(login, sql,
                    new { ProductionId = productionId, TenantId = login.ClientId }, cancellationToken: ct)
                .ConfigureAwait(false);
        }

        // ── Get details ───────────────────────────────────────────────────────

        public async Task<IEnumerable<ProductionDetailSaveDTO>> GetProductionDetails(
            int productionId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionQB.GET_PRODUCTION_DETAILS_SQL,
                DBType.PostGre => ProductionQB.GET_PRODUCTION_DETAILS_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            return await _qe
                .QueryAsync<ProductionDetailSaveDTO>(login, sql,
                    new { ProductionId = productionId, TenantId = login.ClientId }, cancellationToken: ct)
                .ConfigureAwait(false) ?? [];
        }

        // ── Get stoppages ─────────────────────────────────────────────────────

        public async Task<IEnumerable<ProductionStoppageSaveDTO>> GetProductionStoppages(
            int productionId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionQB.GET_PRODUCTION_STOPPAGES_SQL,
                DBType.PostGre => ProductionQB.GET_PRODUCTION_STOPPAGES_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            return await _qe
                .QueryAsync<ProductionStoppageSaveDTO>(login, sql,
                    new { ProductionId = productionId, TenantId = login.ClientId }, cancellationToken: ct)
                .ConfigureAwait(false) ?? [];
        }

        // ── Get paged list ────────────────────────────────────────────────────
        // QueryPagedAsync wraps the full SQL in COUNT(*) which breaks with OFFSET/FETCH.
        // Use separate paged query + scalar count query instead.

        public async Task<(IEnumerable<ProductionListItemDTO> Items, int TotalCount)> GetProductionList(
            int ouId, DateTime? fromDate, DateTime? toDate,
            int firstNumber, int maxResult,
            LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionQB.GET_PRODUCTION_LIST_SQL,
                DBType.PostGre => ProductionQB.GET_PRODUCTION_LIST_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            string countSql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionQB.GET_PRODUCTION_LIST_COUNT_SQL,
                DBType.PostGre => ProductionQB.GET_PRODUCTION_LIST_COUNT_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            var param = new
            {
                OUId       = ouId,
                FromDate   = fromDate,
                ToDate     = toDate,
                TenantId   = login.ClientId,
                FirstNumber = firstNumber,
                MaxResult   = maxResult
            };

            var countParam = new { OUId = ouId, FromDate = fromDate, ToDate = toDate, TenantId = login.ClientId };

            var items = await _qe
                .QueryAsync<ProductionListItemDTO>(login, sql, param, cancellationToken: ct)
                .ConfigureAwait(false) ?? [];

            var total = await _qe
                .ExecuteScalarAsync<int>(login, countSql, countParam, cancellationToken: ct)
                .ConfigureAwait(false);

            return (items, total);
        }

        // ── Save header ───────────────────────────────────────────────────────

        public async Task SaveProductionHeader(
            ProductionSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionQB.SAVE_PRODUCTION_SQL,
                DBType.PostGre => ProductionQB.SAVE_PRODUCTION_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _qe.ExecuteAsync(login, sql,
                new
                {
                    dto.ProductionId, dto.BizTransactionTypeId, dto.OUId, dto.PeriodId,
                    dto.ProductionDate, dto.ProductionNumber, dto.ReferenceNumber, dto.ReferenceDate,
                    dto.SelectType, dto.InChargeId, dto.ShiftId, dto.DepartmentId, dto.WorkCenterId,
                    dto.FromStoreId, dto.ToStoreId, dto.Type, dto.IsStockPost, dto.PatternId,
                    dto.Remarks,
                    Status = 1,
                    Version = 0,
                    CreatedById = login.UserId,
                    CreatedOn = DateTime.UtcNow,
                    ModifiedById = login.UserId,
                    ModifiedOn = DateTime.UtcNow,
                    TenantId = login.ClientId
                }, tx, ct)
                .ConfigureAwait(false);
        }

        public async Task UpdateProductionHeader(
            ProductionSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionQB.UPDATE_PRODUCTION_SQL,
                DBType.PostGre => ProductionQB.UPDATE_PRODUCTION_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _qe.ExecuteAsync(login, sql,
                new
                {
                    dto.BizTransactionTypeId, dto.OUId, dto.PeriodId,
                    dto.ProductionDate, dto.ReferenceNumber, dto.ReferenceDate,
                    dto.SelectType, dto.InChargeId, dto.ShiftId, dto.DepartmentId, dto.WorkCenterId,
                    dto.FromStoreId, dto.ToStoreId, dto.Type, dto.IsStockPost, dto.PatternId,
                    dto.Remarks,
                    Status = 1,
                    Version = 0,
                    CreatedById = login.UserId,
                    CreatedOn = DateTime.UtcNow,
                    ModifiedById = login.UserId,
                    ModifiedOn = DateTime.UtcNow,
                    dto.ProductionId, TenantId = login.ClientId
                }, tx, ct)
                .ConfigureAwait(false);
        }

        // ── Bulk save details — BulkInsert when ≥100 rows ────────────────────

        public async Task BulkSaveProductionDetails(
            IEnumerable<ProductionDetailSaveDTO> rows, int tenantId,
            LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionQB.SAVE_PRODUCTION_DETAIL_SQL,
                DBType.PostGre => ProductionQB.SAVE_PRODUCTION_DETAIL_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            var rowList = rows.ToList();
            if (rowList.Count == 0) return;

            if (rowList.Count >= 100)
            {
                var records = rowList.Select(d => new
                {
                    d.ProductionDetailId,
                    d.ProductionId,
                    d.SlNo,
                    d.LinkTypeId,
                    d.ProductionPlanDetailId,
                    d.ProcessGroupId,
                    d.DocumentDetailId,
                    d.IndentDetailId,
                    d.ItemId,
                    d.SkuId,
                    d.Quantity,
                    d.UomId,
                    d.AllocationId,
                    d.ProductionQuantity,
                    d.GoodQuantity,
                    d.RejectedQuantity,
                    d.ReworkQuantity,
                    d.OtherQuantity,
                    d.ProcessCost,
                    d.MaterialCost,
                    d.StockPostType,
                    d.ReadingFrom,
                    d.ReadingTo,
                    d.StartTime,
                    d.EndTime,
                    d.ExcessShortageQuantity,
                    d.OutputWeight,
                    d.SkuWeight,
                    d.MaterialInHouseProcessCost,
                    d.MaterialSubContractProcessCost,
                    d.MaterialProcessCost,
                    d.MaterialChargesCost,
                    d.MaterialRevenue,
                    d.MaterialTotalCost,
                    d.InHouseProcessCost,
                    d.SubContractProcessCost,
                    d.ChargesCost,
                    d.Revenue,
                    d.TotalCost,
                    TenantId = tenantId
                });
                await _qe.BulkInsertAsync(login, sql, records, tx).ConfigureAwait(false);
            }
            else
            {
                foreach (var d in rowList)
                {
                    await _qe.ExecuteAsync(login, sql,
                        new
                        {
                            d.ProductionDetailId,
                            d.ProductionId,
                            d.SlNo,
                            d.LinkTypeId,
                            d.ProductionPlanDetailId,
                            d.ProcessGroupId,
                            d.DocumentDetailId,
                            d.IndentDetailId,
                            d.ItemId,
                            d.SkuId,
                            d.Quantity,
                            d.UomId,
                            d.AllocationId,
                            d.ProductionQuantity,
                            d.GoodQuantity,
                            d.RejectedQuantity,
                            d.ReworkQuantity,
                            d.OtherQuantity,
                            d.ProcessCost,
                            d.MaterialCost,
                            d.StockPostType,
                            d.ReadingFrom,
                            d.ReadingTo,
                            d.StartTime,
                            d.EndTime,
                            d.ExcessShortageQuantity,
                            d.OutputWeight,
                            d.SkuWeight,
                            d.MaterialInHouseProcessCost,
                            d.MaterialSubContractProcessCost,
                            d.MaterialProcessCost,
                            d.MaterialChargesCost,
                            d.MaterialRevenue,
                            d.MaterialTotalCost,
                            d.InHouseProcessCost,
                            d.SubContractProcessCost,
                            d.ChargesCost,
                            d.Revenue,
                            d.TotalCost,
                            TenantId = tenantId
                        }, tx, ct)
                        .ConfigureAwait(false);
                }
            }
        }

        // ── Bulk save resources ───────────────────────────────────────────────

        public async Task BulkSaveProductionResources(
            IEnumerable<ProductionResourceSaveDTO> rows, int tenantId,
            LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionQB.SAVE_PRODUCTION_RESOURCE_SQL,
                DBType.PostGre => ProductionQB.SAVE_PRODUCTION_RESOURCE_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            var rowList = rows.ToList();
            if (rowList.Count == 0) return;

            if (rowList.Count >= 100)
            {
                var records = rowList.Select(r => new
                {
                    r.ProductionResourceId, r.ProductionId, r.ProductionDetailId,
                    r.ProductionProcessId, r.ProductionOperationId, r.SlNo,
                    r.ResourceId, r.ResourceTypeId, r.MachineId, r.EmployeeId,
                    r.Duration, r.StartDate, r.StartTime, r.EndDate, r.EndTime,
                    r.Quantity, r.UomId, r.MachineSpeed, TenantId = tenantId
                });
                await _qe.BulkInsertAsync(login, sql, records, tx).ConfigureAwait(false);
            }
            else
            {
                foreach (var r in rowList)
                {
                    await _qe.ExecuteAsync(login, sql,
                        new
                        {
                            r.ProductionResourceId, r.ProductionId, r.ProductionDetailId,
                            r.ProductionProcessId, r.ProductionOperationId, r.SlNo,
                            r.ResourceId, r.ResourceTypeId, r.MachineId, r.EmployeeId,
                            r.Duration, r.StartDate, r.StartTime, r.EndDate, r.EndTime,
                            r.Quantity, r.UomId, r.MachineSpeed, TenantId = tenantId
                        }, tx, ct)
                        .ConfigureAwait(false);
                }
            }
        }

        // ── Save stoppage ─────────────────────────────────────────────────────

        public async Task SaveProductionStoppage(
            ProductionStoppageSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionQB.SAVE_PRODUCTION_STOPPAGE_SQL,
                DBType.PostGre => ProductionQB.SAVE_PRODUCTION_STOPPAGE_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _qe.ExecuteAsync(login, sql,
                new
                {
                    dto.ProductionStoppageId, dto.ProductionId, dto.ProductionDetailId,
                    dto.ProductionProcessId, dto.ProductionOperationId, dto.ProductionResourceId, dto.SlNo,
                    dto.StoppageReasonId, dto.StartDate, dto.StartTime, dto.EndDate, dto.EndTime,
                    dto.StopPageDuration, dto.Remarks,
                    Status = 1,
                    Version = 0,
                    CreatedById =login.UserId,
                    ModifiedById =login.UserId,
                    TenantId = login.ClientId
                }, tx, ct)
                .ConfigureAwait(false);
        }

        // ── Production Entry queue (TPOSTEDINDENTDETAIL-backed) ───────────────

        public async Task<IEnumerable<ProductionEntryQueueRowDTO>> GetProductionEntryQueue(
            int ouId, int workCenterId, bool showCompleted, int nestingPlanId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionEntryQueueQB.GET_PRODUCTION_ENTRY_QUEUE_SQL,
                DBType.PostGre => ProductionEntryQueueQB.GET_PRODUCTION_ENTRY_QUEUE_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            return await _qe
                .QueryAsync<ProductionEntryQueueRowDTO>(login, sql,
                    new { OUId = ouId, WorkCenterId = workCenterId, ShowCompleted = showCompleted, NestingPlanId = nestingPlanId },
                    cancellationToken: ct)
                .ConfigureAwait(false) ?? [];
        }

        // ── Save quality reason breakdown (TITEMREASON) ───────────────────────

        public async Task SaveItemReason(
            ItemReasonSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ItemReasonQB.SAVE_ITEM_REASON_SQL,
                DBType.PostGre => ItemReasonQB.SAVE_ITEM_REASON_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _qe.ExecuteAsync(login, sql,
                new
                {
                    dto.ItemReasonId, dto.ObjectHeaderTypeId, dto.ObjectHeaderId,
                    dto.ObjectTypeId, dto.ObjectId, dto.BizTransactionTypeId,
                    dto.ItemId, dto.SkuId, dto.DocumentDate, dto.SlNo, dto.ReasonId,
                    dto.GoodQuantity, dto.RejectedQuantity, dto.ReworkQuantity, dto.OtherQuantity,
                    dto.Remarks, dto.LotId
                }, tx, ct)
                .ConfigureAwait(false);
        }

        // ── Insert TSTOCKLEDGER row (delta stock post) ────────────────────────

        public async Task InsertStockLedgerFromProduction(
            ProductionStockLedgerPostDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionQB.INSERT_STOCK_LEDGER_FROM_PRODUCTION_SQL,
                DBType.PostGre => ProductionQB.INSERT_STOCK_LEDGER_FROM_PRODUCTION_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _qe.ExecuteAsync(login, sql, dto, tx, ct).ConfigureAwait(false);
        }

        // ── Delete children ───────────────────────────────────────────────────

        public async Task DeleteProductionChildren(
            int productionId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string stoppageSql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionQB.DELETE_PRODUCTION_STOPPAGES_SQL,
                DBType.PostGre => ProductionQB.DELETE_PRODUCTION_STOPPAGES_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };
            string processSql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionQB.DELETE_PRODUCTION_PROCESSES_SQL,
                DBType.PostGre => ProductionQB.DELETE_PRODUCTION_PROCESSES_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };
            string resourceSql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionQB.DELETE_PRODUCTION_RESOURCES_SQL,
                DBType.PostGre => ProductionQB.DELETE_PRODUCTION_RESOURCES_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };
            string detailSql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionQB.DELETE_PRODUCTION_DETAILS_SQL,
                DBType.PostGre => ProductionQB.DELETE_PRODUCTION_DETAILS_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            string itemReasonSql = login.DatabaseType switch
            {
                DBType.SQL     => ItemReasonQB.DELETE_ITEM_REASON_BY_HEADER_SQL,
                DBType.PostGre => ItemReasonQB.DELETE_ITEM_REASON_BY_HEADER_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            var param = new { ProductionId = productionId, TenantId = login.ClientId };
            // Delete in FK-dependency order: stoppages → processes → resources → details
            await _qe.ExecuteAsync(login, stoppageSql, param, tx, ct).ConfigureAwait(false);
            await _qe.ExecuteAsync(login, processSql, param, tx, ct).ConfigureAwait(false);
            await _qe.ExecuteAsync(login, resourceSql, param, tx, ct).ConfigureAwait(false);
            await _qe.ExecuteAsync(login,
                itemReasonSql,
                new { ObjectHeaderTypeId = EntityConstant.OBJECTPRODUCTION, ObjectHeaderId = productionId },
                tx, ct).ConfigureAwait(false);
            await _qe.ExecuteAsync(login, detailSql, param, tx, ct).ConfigureAwait(false);
        }

        // ── Soft delete ───────────────────────────────────────────────────────

        public async Task SoftDeleteProduction(
            int productionId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionQB.SOFT_DELETE_PRODUCTION_SQL,
                DBType.PostGre => ProductionQB.SOFT_DELETE_PRODUCTION_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _qe.ExecuteAsync(login, sql,
                new { ProductionId = productionId, TenantId = login.ClientId }, tx, ct)
                .ConfigureAwait(false);
        }

        // ── Update WO release status ──────────────────────────────────────────

        public async Task UpdateWOReleaseStatus(
            int indentDetailId, byte releaseStatus,
            LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL     => ProductionQB.UPDATE_WO_RELEASESTATUS_SQL,
                DBType.PostGre => ProductionQB.UPDATE_WO_RELEASESTATUS_PG,
                _              => throw new NotSupportedException("Unsupported database type")
            };

            await _qe.ExecuteAsync(login, sql,
                new { IndentDetailId = indentDetailId, ReleaseStatus = releaseStatus, TenantId = login.ClientId },
                tx, ct)
                .ConfigureAwait(false);
        }
        public async Task<string> GetSelectListProduction(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            string sql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => ProductionQB.GET_SELECTLIST_PRODUCTION_SQL,
                DBType.PostGre => ProductionQB.GET_SELECTLIST_PRODUCTION_PG,
                _ => throw new Exception("Unsupported database type")
            };

            var param = new { firstnumber = FirstNumber, maxresult = MaxResult };
            var result = await _qe.QueryAsync<ProductionPicklistDTO>(LoginDTO, sql, param)
                .ConfigureAwait(false);
            return JsonConvert.SerializeObject(result);
        }

        // ── Production.svc/RejectionReport ───────────────────────────────────
        // GB4 parity: ProductionBLL.GetRejectionReport (SubType 0 = Summary, 1 = Periodic, 2 = ItemCategory)

        public async Task<string> GetRejectionReport(int Type, int SubType, int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            if (CriteriaDTO == null)
                throw new NotFoundException("Criteria must be supplied");

            DateTime? periodFromDate = null, periodToDate = null;
            int indentId = -1, itemId = -1, skuId = -1, processId = -1, workCenterId = -1, employeeId = -1, ouId = -1, periodType = -1;

            foreach (var section in CriteriaDTO.SectionCriteriaList ?? Enumerable.Empty<SectionCriteriaDTO>())
            {
                foreach (var attr in section.AttributesCriteriaList ?? new List<AttributesCriteriaDTO>())
                {
                    switch (attr.FieldName?.Trim().ToLower())
                    {
                        case "periodfromdate": periodFromDate = ParseEpochDate(attr.FieldValue); break;
                        case "periodtodate": periodToDate = ParseEpochDate(attr.FieldValue); break;
                        case "indentid": indentId = GetCriteriaInt(attr.FieldValue, -1); break;
                        case "itemid": itemId = GetCriteriaInt(attr.FieldValue, -1); break;
                        case "skuid": skuId = GetCriteriaInt(attr.FieldValue, -1); break;
                        case "processid": processId = GetCriteriaInt(attr.FieldValue, -1); break;
                        case "workcenterid": workCenterId = GetCriteriaInt(attr.FieldValue, -1); break;
                        case "employeeid": employeeId = GetCriteriaInt(attr.FieldValue, -1); break;
                        case "ouid": ouId = GetCriteriaInt(attr.FieldValue, -1); break;
                        case "periodtype": periodType = GetCriteriaInt(attr.FieldValue, -1); break;
                    }
                }
            }

            if (periodFromDate == null)
                throw new NotFoundException("PeriodFromDate must be supplied");
            if (periodToDate == null)
                throw new NotFoundException("PeriodToDate must be supplied");

            var parameters = new DynamicParameters();
            parameters.Add("periodfromdate", periodFromDate);
            parameters.Add("periodtodate", periodToDate);
            parameters.Add("indentid", indentId);
            parameters.Add("itemid", itemId);
            parameters.Add("skuid", skuId);
            parameters.Add("processid", processId);
            parameters.Add("workcenterid", workCenterId);
            parameters.Add("employeeid", employeeId);
            parameters.Add("ouid", ouId);

            if (SubType == 2)
            {
                if (IsCount)
                {
                    int catCount = await _qe.QuerySingleAsync<int>(LoginDTO, RejectionReportQB.BASE_ITEMCATEGORY_COUNT, parameters).ConfigureAwait(false);
                    return catCount.ToString();
                }

                parameters.Add("firstnumber", FirstNumber);
                parameters.Add("maxresult", MaxResult);
                string catPagedSql = WrapPaged(RejectionReportQB.BASE_ITEMCATEGORY, "(SELECT NULL)");
                var catResult = await _qe.QueryAsync<RejectionReportDTO>(LoginDTO, catPagedSql, parameters).ConfigureAwait(false);
                return JsonConvert.SerializeObject(catResult);
            }

            var dimensions = SubType == 1 ? RejectionReportDimensions.Periodic : RejectionReportDimensions.Summary;
            if (!dimensions.TryGetValue(Type, out var dimension))
                throw new MethodNotAllowedException("Type not supported for this SubType.");

            if (IsCount)
            {
                string countSql = SubType == 1 ? RejectionReportQB.BASE_PERIODIC_COUNT : RejectionReportQB.BASE_SUMMARY_COUNT;
                parameters.Add("periodtype", periodType);
                int count = await _qe.QuerySingleAsync<int>(LoginDTO, countSql, parameters).ConfigureAwait(false);
                return count.ToString();
            }

            string baseSql = SubType == 1 ? RejectionReportQB.BASE_PERIODIC : RejectionReportQB.BASE_SUMMARY;
            string sql = baseSql.Replace("{FIELDS}", dimension.Fields);
            if (!string.IsNullOrWhiteSpace(dimension.GroupBy))
                sql += $" GROUP BY {dimension.GroupBy}";

            string orderExpr = "(SELECT NULL)";
            if (SubType == 1)
            {
                parameters.Add("periodtype", periodType);
                orderExpr = "SubPeriodFromDate";
            }

            parameters.Add("firstnumber", FirstNumber);
            parameters.Add("maxresult", MaxResult);
            string pagedSql = WrapPaged(sql, orderExpr);

            var result = await _qe.QueryAsync<RejectionReportDTO>(LoginDTO, pagedSql, parameters).ConfigureAwait(false);
            return JsonConvert.SerializeObject(result);
        }

        // Wraps an aggregated (GROUP BY) query in a ROW_NUMBER() page so no RejectionReport
        // variant can return an unbounded result set. @firstnumber/@maxresult = -1 skips paging.
        private static string WrapPaged(string aggregatedSql, string orderExpr) => $@"
            WITH Agg AS ({aggregatedSql}),
            Paged AS (
                SELECT *, ROW_NUMBER() OVER (ORDER BY {orderExpr}) AS RowNum FROM Agg
            )
            SELECT * FROM Paged
            WHERE (@firstnumber = -1 AND @maxresult = -1)
               OR (RowNum BETWEEN @firstnumber AND @maxresult)
            ORDER BY RowNum;";

        private static DateTime? ParseEpochDate(object value)
        {
            string raw = value switch
            {
                null => null,
                System.Text.Json.JsonElement je => je.ToString(),
                _ => Convert.ToString(value)
            };

            if (string.IsNullOrWhiteSpace(raw)) return null;
            if (DateTime.TryParse(raw, out var dt)) return dt;

            if (long.TryParse(raw, out var epoch))
            {
                return epoch > 100000000000
                    ? DateTimeOffset.FromUnixTimeMilliseconds(epoch).DateTime
                    : DateTimeOffset.FromUnixTimeSeconds(epoch).DateTime;
            }
            return null;
        }

        private static int GetCriteriaInt(object value, int def)
        {
            if (value == null) return def;
            if (value is System.Text.Json.JsonElement je && je.TryGetInt32(out int i)) return i;
            return Convert.ToInt32(value);
        }
    }
}