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

namespace MMDAL.CustomCode.Indent
{
    public class IndentDAL : IIndentDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

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


        // ── Picklist (existing) ───────────────────────────────────────────────

        public async Task<string> GetSelectListIndent(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            string sql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => IndentQB.GET_SELECTLIST_INDENT_SQL,
                DBType.PostGre => IndentQB.GET_SELECTLIST_INDENT_PG,
                _ => throw new Exception("Unsupported database type")
            };

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

        // ── AmendmentSelectList ───────────────────────────────────────────────
        // GB4 parity: Indent.svc/AmendmentSelectList — IndentBLL.AmendmentSelectList

        public async Task<string> AmendmentSelectList(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            string indentId = null!, indentNumber = null!, nature = null!, inchargeId = null!,
                   processId = null!, bizTransactionTypeId = null!, nestingPlanId = null!,
                   itemId = null!, indentDetailId = null!, releaseStatus = null!;

            foreach (var section in CriteriaDTO?.SectionCriteriaList ?? Enumerable.Empty<SectionCriteriaDTO>())
            {
                foreach (var attr in section.AttributesCriteriaList ?? new List<AttributesCriteriaDTO>())
                {
                    switch (attr.FieldName?.Trim().ToLower())
                    {
                        case "indentid": indentId = GetCriteriaString(attr.FieldValue); break;
                        case "indentnumber": indentNumber = GetCriteriaString(attr.FieldValue); break;
                        case "nature": nature = GetCriteriaString(attr.FieldValue); break;
                        case "incharge": inchargeId = GetCriteriaString(attr.FieldValue); break;
                        case "process": processId = GetCriteriaString(attr.FieldValue); break;
                        case "biztransactiontype": bizTransactionTypeId = GetCriteriaString(attr.FieldValue); break;
                        case "nestingplan": nestingPlanId = GetCriteriaString(attr.FieldValue); break;
                        case "itemid": itemId = GetCriteriaString(attr.FieldValue); break;
                        case "indentdetail": indentDetailId = GetCriteriaString(attr.FieldValue); break;
                        case "releasestatus": releaseStatus = GetCriteriaString(attr.FieldValue); break;
                    }
                }
            }

            string sql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => IndentQB.GET_INDENT_AMENDMENT_PICKLIST_SQL,
                DBType.PostGre => IndentQB.GET_INDENT_AMENDMENT_PICKLIST_PG,
                _ => throw new Exception("Unsupported database type")
            };

            var param = new
            {
                ouid = LoginDTO.WorkOUId,
                periodid = LoginDTO.WorkPeriodId,
                indentid = string.IsNullOrWhiteSpace(indentId) ? null : $"%{indentId}%",
                indentnumber = string.IsNullOrWhiteSpace(indentNumber) ? null : $"%{indentNumber}%",
                nature = string.IsNullOrWhiteSpace(nature) ? null : $"%{nature}%",
                inchargeid = string.IsNullOrWhiteSpace(inchargeId) ? null : $"%{inchargeId}%",
                processid = string.IsNullOrWhiteSpace(processId) ? null : $"%{processId}%",
                biztransactiontypeid = string.IsNullOrWhiteSpace(bizTransactionTypeId) ? null : $"%{bizTransactionTypeId}%",
                nestingplanid = string.IsNullOrWhiteSpace(nestingPlanId) ? null : $"%{nestingPlanId}%",
                itemid = string.IsNullOrWhiteSpace(itemId) ? null : $"%{itemId}%",
                indentdetailid = string.IsNullOrWhiteSpace(indentDetailId) ? null : $"%{indentDetailId}%",
                releasestatus = string.IsNullOrWhiteSpace(releaseStatus) ? null : $"%{releaseStatus}%",
                firstnumber = FirstNumber,
                maxresult = MaxResult
            };

            var result = await _QueryExecutor.QueryAsync<IndentAmendmentPicklistDTO>(LoginDTO, sql, param).ConfigureAwait(false);
            return JsonConvert.SerializeObject(result);
        }

        // ── Production/CombineIndentSelectList ───────────────────────────────
        // GB4 parity: ProductionBLL.GetCombineIndentSelectList

        public async Task<string> GetCombineIndentSelectList(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            string bizTransactionTypeId = null!, combineIndentId = null!, indentNumber = null!, processId = null!, workCenterId = null!;

            foreach (var section in CriteriaDTO?.SectionCriteriaList ?? Enumerable.Empty<SectionCriteriaDTO>())
            {
                foreach (var attr in section.AttributesCriteriaList ?? new List<AttributesCriteriaDTO>())
                {
                    switch (attr.FieldName?.Trim().ToLower())
                    {
                        case "biztransactiontypeid": bizTransactionTypeId = GetCriteriaString(attr.FieldValue); break;
                        case "combineindentid": combineIndentId = GetCriteriaString(attr.FieldValue); break;
                        case "indentnumber": indentNumber = GetCriteriaString(attr.FieldValue); break;
                        case "processid": processId = GetCriteriaString(attr.FieldValue); break;
                        case "workcenterid": workCenterId = GetCriteriaString(attr.FieldValue); break;
                    }
                }
            }

            string sql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => IndentQB.GET_COMBINEINDENT_SELECTLIST_SQL,
                DBType.PostGre => IndentQB.GET_COMBINEINDENT_SELECTLIST_PG,
                _ => throw new Exception("Unsupported database type")
            };

            var param = new
            {
                biztransactiontypeid = string.IsNullOrWhiteSpace(bizTransactionTypeId) ? null : $"%{bizTransactionTypeId}%",
                combineindentid = string.IsNullOrWhiteSpace(combineIndentId) ? null : $"%{combineIndentId}%",
                indentnumber = string.IsNullOrWhiteSpace(indentNumber) ? null : $"%{indentNumber}%",
                processid = string.IsNullOrWhiteSpace(processId) ? null : $"%{processId}%",
                workcenterid = string.IsNullOrWhiteSpace(workCenterId) ? null : $"%{workCenterId}%",
                firstnumber = FirstNumber,
                maxresult = MaxResult
            };

            var result = await _QueryExecutor.QueryAsync<IndentAndCombineIndentSelectListDTO>(LoginDTO, sql, param).ConfigureAwait(false);
            return JsonConvert.SerializeObject(result);
        }

        // ── Production/IndentReportSelectList ────────────────────────────────
        // GB4 parity: ProductionBLL.GetIndentReportSelectList — scoped to the caller's WorkPeriodId

        public async Task<string> GetIndentReportSelectList(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            string sql = LoginDTO.DatabaseType switch
            {
                DBType.SQL => IndentQB.GET_INDENTREPORT_SELECTLIST_SQL,
                DBType.PostGre => IndentQB.GET_INDENTREPORT_SELECTLIST_PG,
                _ => throw new Exception("Unsupported database type")
            };

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

        private static string GetCriteriaString(object value)
        {
            if (value == null) return string.Empty;
            if (value is System.Text.Json.JsonElement je) return je.ToString();
            return Convert.ToString(value) ?? string.Empty;
        }

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

        public async Task<IndentHeaderViewDTO?> GetIndentHeader(int indentId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.GET_INDENT_SQL,
                DBType.PostGre => IndentQB.GET_INDENT_PG,
                _ => throw new Exception("Unsupported database type")
            };

            return await _QueryExecutor
                .QuerySingleAsync<IndentHeaderViewDTO>(login, sql,
                    new { IndentId = indentId, TenantId = login.ClientId }, cancellationToken: ct)
                .ConfigureAwait(false);
        }

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

        public async Task<IEnumerable<IndentDetailViewDTO>> GetIndentDetails(int indentId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.GET_INDENT_DETAILS_SQL,
                DBType.PostGre => IndentQB.GET_INDENT_DETAILS_PG,
                _ => throw new Exception("Unsupported database type")
            };

            return await _QueryExecutor
                .QueryAsync<IndentDetailViewDTO>(login, sql,
                    new { IndentId = indentId, TenantId = login.ClientId }, cancellationToken: ct)
                .ConfigureAwait(false) ?? [];
        }

        // ── Get paged list ────────────────────────────────────────────────────

        public async Task<IEnumerable<IndentListItemDTO>> GetIndentList(int ouId, int firstNumber, int maxResult, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.GET_INDENT_LIST_SQL,
                DBType.PostGre => IndentQB.GET_INDENT_LIST_PG,
                _ => throw new Exception("Unsupported database type")
            };

            return await _QueryExecutor
                .QueryAsync<IndentListItemDTO>(login, sql,
                    new { OUId = ouId, FirstNumber = firstNumber, MaxResult = maxResult },
                    cancellationToken: ct)
                .ConfigureAwait(false) ?? [];
        }

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

        public async Task SaveIndentHeader(IndentSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.SAVE_INDENT_SQL,
                DBType.PostGre => IndentQB.SAVE_INDENT_PG,
                _ => throw new Exception("Unsupported database type")
            };

            await _QueryExecutor
                .ExecuteAsync(login, sql, new
                {
                    dto.IndentId,
                    dto.BizTransactionTypeId,
                    dto.OUId,
                    dto.PeriodId,
                    dto.IndentDate,
                    dto.IndentNumber,
                    dto.ReferenceDate,
                    dto.ReferenceNumber,
                    dto.Nature,
                    dto.ProcessId,
                    dto.PartyBranchId,
                    dto.WorkCenterId,
                    dto.PlannedNumberOfMachines,
                    dto.IsBatchRequired,
                    dto.ExpectedFirstDeliveryDate,
                    dto.ExpectedCompletionDate,
                    dto.ReceivedById,
                    dto.ReceivedOn,
                    dto.Remarks,
                    dto.ChangeReasonId,
                    dto.ChangeRemarks,
                    dto.IndentType,
                    dto.CombineIndentId,
                    dto.AmendmentSlNo,
                    dto.IndentNature,
                    dto.MatrixId,
                    dto.Status,
                    dto.Version,
                    CreatedById = login.UserId,
                    CreatedOn = DateTime.UtcNow,
                    ModifiedById = login.UserId,
                    ModifiedOn = DateTime.UtcNow
                }, tx, cancellationToken: ct)
                .ConfigureAwait(false);
        }

        // ── Update header ─────────────────────────────────────────────────────

        public async Task UpdateIndentHeader(IndentSaveDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.UPDATE_INDENT_SQL,
                DBType.PostGre => IndentQB.UPDATE_INDENT_PG,
                _ => throw new Exception("Unsupported database type")
            };
            await _QueryExecutor
                .ExecuteAsync(login, sql, new
                {
                    dto.IndentId,
                    dto.BizTransactionTypeId,
                    dto.OUId,
                    dto.PeriodId,
                    dto.IndentDate,
                    dto.IndentNumber,
                    dto.ReferenceDate,
                    dto.ReferenceNumber,
                    dto.Nature,
                    dto.ProcessId,
                    dto.PartyBranchId,
                    dto.WorkCenterId,
                    dto.PlannedNumberOfMachines,
                    dto.IsBatchRequired,
                    dto.ExpectedFirstDeliveryDate,
                    dto.ExpectedCompletionDate,
                    dto.ReceivedOn,
                    dto.Remarks,
                    dto.ChangeReasonId,
                    dto.ChangeRemarks,
                    dto.IndentType,
                    dto.Status,
                    dto.Version,
                    dto.CombineIndentId,
                    dto.AmendmentSlNo,
                    dto.IndentNature,
                    dto.MatrixId,
                    ModifiedById = login.UserId,
                    ModifiedOn = DateTime.UtcNow,
                    ReceivedById = login.UserId
                }, tx, cancellationToken: ct)
                .ConfigureAwait(false);
        }

        // ── BulkInsert details ────────────────────────────────────────────────

        public async Task BulkSaveIndentDetails(IEnumerable<IndentDetailInsertDTO> rows, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.BULK_INSERT_INDENT_DETAIL_SQL,
                DBType.PostGre => IndentQB.BULK_INSERT_INDENT_DETAIL_PG,
                _ => throw new Exception("Unsupported database type")
            };

            await _QueryExecutor
                .BulkInsertAsync(login, sql, rows, tx)
                .ConfigureAwait(false);
        }

        // ── BulkInsert materials ──────────────────────────────────────────────

        public async Task BulkSaveIndentMaterials(IEnumerable<IndentMaterialInsertDTO> rows, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.BULK_INSERT_INDENT_MATERIAL_SQL,
                DBType.PostGre => IndentQB.BULK_INSERT_INDENT_MATERIAL_PG,
                _ => throw new Exception("Unsupported database type")
            };

            await _QueryExecutor
                .BulkInsertAsync(login, sql, rows, tx)
                .ConfigureAwait(false);
        }

        // ── BulkInsert resources ──────────────────────────────────────────────

        public async Task BulkSaveIndentResources(IEnumerable<IndentResourceInsertDTO> rows, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.BULK_INSERT_INDENT_RESOURCE_SQL,
                DBType.PostGre => IndentQB.BULK_INSERT_INDENT_RESOURCE_PG,
                _ => throw new Exception("Unsupported database type")
            };

            await _QueryExecutor
                .BulkInsertAsync(login, sql, rows, tx)
                .ConfigureAwait(false);
        }

        // ── Delete details (cascade materials + resources) ────────────────────

        public async Task DeleteIndentDetails(int indentId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            // Three separate statements — multi-statement batch is unreliable across drivers.
            // Deletion order: resources → materials → details (child before parent).
            string sqlResources = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.DELETE_INDENT_RESOURCES_SQL,
                DBType.PostGre => IndentQB.DELETE_INDENT_RESOURCES_PG,
                _ => throw new Exception("Unsupported database type")
            };
            string sqlMaterials = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.DELETE_INDENT_MATERIALS_SQL,
                DBType.PostGre => IndentQB.DELETE_INDENT_MATERIALS_PG,
                _ => throw new Exception("Unsupported database type")
            };
            string sql = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.DELETE_INDENT_DETAILS_SQL,
                DBType.PostGre => IndentQB.DELETE_INDENT_DETAILS_PG,
                _ => throw new Exception("Unsupported database type")
            };

            var param = new { IndentId = indentId };
            await _QueryExecutor.ExecuteAsync(login, sqlResources, param, tx, cancellationToken: ct).ConfigureAwait(false);
            await _QueryExecutor.ExecuteAsync(login, sqlMaterials, param, tx, cancellationToken: ct).ConfigureAwait(false);
            await _QueryExecutor.ExecuteAsync(login, sql, param, tx, cancellationToken: ct).ConfigureAwait(false);
        }

        // ── Soft-delete header ────────────────────────────────────────────────

        public async Task SoftDeleteIndent(int indentId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.SOFT_DELETE_INDENT_SQL,
                DBType.PostGre => IndentQB.SOFT_DELETE_INDENT_PG,
                _ => throw new Exception("Unsupported database type")
            };

            await _QueryExecutor
                .ExecuteAsync(login, sql, new { IndentId = indentId }, tx, cancellationToken: ct)
                .ConfigureAwait(false);
        }

        // ── Phase 2: SO line ──────────────────────────────────────────────────

        public async Task<SOLineForWODTO?> GetSOLineForWO(int mmDetailId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.GET_SO_LINE_FOR_WO_SQL,
                DBType.PostGre => IndentQB.GET_SO_LINE_FOR_WO_PG,
                _ => throw new Exception("Unsupported database type")
            };

            return await _QueryExecutor
                .QuerySingleAsync<SOLineForWODTO>(login, sql,
                    new { MMDetailId = mmDetailId }, cancellationToken: ct)
                .ConfigureAwait(false);
        }

        // ── Phase 3: Production plan line ─────────────────────────────────────

        public async Task<ProductionPlanLineForWODTO?> GetProductionPlanLineForWO(int productionPlanDetailId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.GET_PRODUCTION_PLAN_LINE_FOR_WO_SQL,
                DBType.PostGre => IndentQB.GET_PRODUCTION_PLAN_LINE_FOR_WO_PG,
                _ => throw new Exception("Unsupported database type")
            };

            return await _QueryExecutor
                .QuerySingleAsync<ProductionPlanLineForWODTO>(login, sql,
                    new { ProductionPlanDetailId = productionPlanDetailId }, cancellationToken: ct)
                .ConfigureAwait(false);
        }

        // ── Phase 4: Routing detail resources ─────────────────────────────────

        public async Task<IEnumerable<RoutingDetailResourceDTO>> GetRoutingDetailResources(IEnumerable<int> routingDetailIds, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.GET_ROUTING_DETAIL_RESOURCES_SQL,
                DBType.PostGre => IndentQB.GET_ROUTING_DETAIL_RESOURCES_PG,
                _ => throw new Exception("Unsupported database type")
            };

            return await _QueryExecutor
                .QueryAsync<RoutingDetailResourceDTO>(login, sql,
                    new { RoutingDetailIds = routingDetailIds }, cancellationToken: ct)
                .ConfigureAwait(false) ?? [];
        }

        // ── Phase 6: Item default routing ─────────────────────────────────────

        public async Task<int> GetItemDefaultRoutingId(int itemId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.GET_ITEM_DEFAULT_ROUTING_SQL,
                DBType.PostGre => IndentQB.GET_ITEM_DEFAULT_ROUTING_PG,
                _ => throw new Exception("Unsupported database type")
            };

            // MITEM.ROUTINGID is nullable — ExecuteScalarAsync<int> returns 0 for NULL; normalise to -1.
            var id = await _QueryExecutor
                .ExecuteScalarAsync<int>(login, sql,
                    new { ItemId = itemId }, cancellationToken: ct)
                .ConfigureAwait(false);
            return id > 0 ? id : -1;
        }

        // ── Phase 2 (Production): WO Lifecycle ────────────────────────────────

        public async Task<IndentDetailInsertDTO?> GetIndentDetailById(int indentDetailId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.GET_INDENT_DETAIL_BY_ID_SQL,
                DBType.PostGre => IndentQB.GET_INDENT_DETAIL_BY_ID_PG,
                _ => throw new NotSupportedException("Unsupported database type")
            };

            var results = await _QueryExecutor.QueryAsync<IndentDetailInsertDTO>(login, sql,
                new { IndentDetailId = indentDetailId, TenantId = login.ClientId }, cancellationToken: ct)
                .ConfigureAwait(false);
            return results?.FirstOrDefault();
        }

        public async Task UpdateIndentDetailQuantity(int indentDetailId, decimal newQuantity, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.UPDATE_INDENT_DETAIL_QTY_SQL,
                DBType.PostGre => IndentQB.UPDATE_INDENT_DETAIL_QTY_PG,
                _ => throw new NotSupportedException("Unsupported database type")
            };

            await _QueryExecutor.ExecuteAsync(login, sql,
                new { IndentDetailId = indentDetailId, NewQuantity = newQuantity, TenantId = login.ClientId }, tx, ct)
                .ConfigureAwait(false);
        }

        public async Task CloseWorkOrder(int indentDetailId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.CLOSE_WORK_ORDER_SQL,
                DBType.PostGre => IndentQB.CLOSE_WORK_ORDER_PG,
                _ => throw new NotSupportedException("Unsupported database type")
            };

            await _QueryExecutor.ExecuteAsync(login, sql,
                new { IndentDetailId = indentDetailId, TenantId = login.ClientId }, tx, ct)
                .ConfigureAwait(false);
        }

        public async Task ShortCloseWorkOrder(int indentDetailId, decimal closedQuantity, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.SHORT_CLOSE_WORK_ORDER_SQL,
                DBType.PostGre => IndentQB.SHORT_CLOSE_WORK_ORDER_PG,
                _ => throw new NotSupportedException("Unsupported database type")
            };

            await _QueryExecutor.ExecuteAsync(login, sql,
                new { IndentDetailId = indentDetailId, ClosedQuantity = closedQuantity, TenantId = login.ClientId }, tx, ct)
                .ConfigureAwait(false);
        }

        public async Task<IEnumerable<WorkOrderBOMFeasibilityDTO>> GetBOMFeasibility(int indentDetailId, int storeId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.GET_WO_BOM_FEASIBILITY_SQL,
                DBType.PostGre => IndentQB.GET_WO_BOM_FEASIBILITY_PG,
                _ => throw new NotSupportedException("Unsupported database type")
            };

            return await _QueryExecutor.QueryAsync<WorkOrderBOMFeasibilityDTO>(login, sql,
                new { IndentDetailId = indentDetailId, StoreId = storeId, TenantId = login.ClientId }, cancellationToken: ct)
                .ConfigureAwait(false) ?? [];
        }

        public async Task<SupervisorWorkbenchKpiDTO?> GetSupervisorWorkbenchKPI(int ouId, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.GET_SUPERVISOR_KPI_SQL,
                DBType.PostGre => IndentQB.GET_SUPERVISOR_KPI_PG,
                _ => throw new NotSupportedException("Unsupported database type")
            };

            var results = await _QueryExecutor.QueryAsync<SupervisorWorkbenchKpiDTO>(login, sql,
                new { OUId = ouId, TenantId = login.ClientId }, cancellationToken: ct)
                .ConfigureAwait(false);
            return results?.FirstOrDefault();
        }

        // ── Phase 6 (Supervisor Workbench) ────────────────────────────────────

        public async Task DeleteIndentResources(int indentDetailId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => SupervisorWorkbenchQB.DELETE_INDENT_RESOURCES_SQL,
                DBType.PostGre => SupervisorWorkbenchQB.DELETE_INDENT_RESOURCES_PG,
                _ => throw new NotSupportedException("Unsupported database type")
            };
            await _QueryExecutor.ExecuteAsync(login, sql,
                new { IndentDetailId = indentDetailId, TenantId = login.ClientId }, tx, ct)
                .ConfigureAwait(false);
        }

        public async Task BulkSaveIndentResourceAssignments(IEnumerable<IndentResourceInsertDTO> resources, LoginDTO login, DbTransaction tx)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => SupervisorWorkbenchQB.SAVE_INDENT_RESOURCE_SQL,
                DBType.PostGre => SupervisorWorkbenchQB.SAVE_INDENT_RESOURCE_PG,
                _ => throw new NotSupportedException("Unsupported database type")
            };
            await _QueryExecutor.BulkInsertAsync(login, sql, resources, tx).ConfigureAwait(false);
        }

        public async Task ReleaseWorkOrder(int indentDetailId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => SupervisorWorkbenchQB.RELEASE_WORK_ORDER_SQL,
                DBType.PostGre => SupervisorWorkbenchQB.RELEASE_WORK_ORDER_PG,
                _ => throw new NotSupportedException("Unsupported database type")
            };
            await _QueryExecutor.ExecuteAsync(login, sql,
                new { IndentDetailId = indentDetailId, TenantId = login.ClientId, UserId = login.UserId }, tx, ct)
                .ConfigureAwait(false);
        }

        public async Task<(IEnumerable<SupervisorWOListItemDTO> Items, int TotalCount)> GetSupervisorWOList(
            int ouId, int statusFilter, int firstNumber, int maxResult, LoginDTO login, CancellationToken ct)
        {
            var (sql, countSql) = login.DatabaseType switch
            {
                DBType.SQL => (SupervisorWorkbenchQB.GET_SUPERVISOR_WO_LIST_SQL, SupervisorWorkbenchQB.GET_SUPERVISOR_WO_LIST_COUNT_SQL),
                DBType.PostGre => (SupervisorWorkbenchQB.GET_SUPERVISOR_WO_LIST_PG, SupervisorWorkbenchQB.GET_SUPERVISOR_WO_LIST_COUNT_PG),
                _ => throw new NotSupportedException("Unsupported database type")
            };
            var param = new { OuId = ouId, StatusFilter = statusFilter, TenantId = login.ClientId, FirstNumber = firstNumber, MaxResult = maxResult };
            var items = await _QueryExecutor.QueryAsync<SupervisorWOListItemDTO>(login, sql, param, cancellationToken: ct).ConfigureAwait(false);
            var totalCount = await _QueryExecutor.ExecuteScalarAsync<int>(login, countSql, param, cancellationToken: ct).ConfigureAwait(false);
            return (items, totalCount);
        }
        public async Task<IndentDTO> GetIndentById(LoginDTO loginDTO, string sql, object parameters)
        {
            var indentDict = new Dictionary<long, IndentDTO>();

            var result = await _QueryExecutor.QueryMultiMapAsync<IndentDTO, IndentDetailDTO, IndentMaterialDTO, IndentResourceDTO, Object, IndentDTO>
            (
                loginDTO,
                sql,
                (indent, detail, material, resource, dummy) =>
                {
                    // Parent - Indent
                    if (!indentDict.TryGetValue(indent.IndentId, out var currentIndent))
                    {
                        currentIndent = indent;
                        currentIndent.Details = new List<IndentDetailDTO>();

                        indentDict.Add(currentIndent.IndentId, currentIndent);
                    }

                    // Child - Detail
                    if (detail != null)
                    {
                        var currentDetail = currentIndent.Details
                            .FirstOrDefault(d => d.IndentDetailId == detail.IndentDetailId);

                        if (currentDetail == null)
                        {
                            currentDetail = detail;
                            currentDetail.Materials = new List<IndentMaterialDTO>();
                            currentDetail.Resources = new List<IndentResourceDTO>();

                            currentIndent.Details.Add(currentDetail);
                        }

                        // Material
                        if (material != null &&
                            !currentDetail.Materials.Any(m =>
                                m.IndentMaterialId == material.IndentMaterialId))
                        {
                            currentDetail.Materials.Add(material);
                        }

                        // Resource
                        if (resource != null &&
                            !currentDetail.Resources.Any(r =>
                                r.IndentResourceId == resource.IndentResourceId))
                        {
                            currentDetail.Resources.Add(resource);
                        }
                    }

                    return currentIndent;
                },
                parameters,
                splitOn: "IndentDetailId,IndentMaterialId,IndentResourceId,DummyId"
            );

            return indentDict.Values.FirstOrDefault();
        }
        public async Task<int> SaveWorkOrder(IndentSaveDTO IndentSaveDTO, LoginDTO loginDTO)
        {
            await _QueryExecutor.BeginTransactionAsync(loginDTO);

            try
            {
                // 1️⃣ INSERT MMINDENT
                IndentSaveDTO.IndentId = await _QueryExecutor.SessionQuerySingleAsync<int>(
                    loginDTO,
                    IndentQB.SAVE_INDENT,
                    IndentSaveDTO
                );

                if (IndentSaveDTO.IndentId <= 0)
                    throw new Exception("INDENT insert failed.");

                // 2️⃣ INSERT MMINDENTDETAIL
                foreach (var detail in IndentSaveDTO.Details)
                {
                    detail.IndentId = IndentSaveDTO.IndentId;

                    detail.IndentDetailId = await _QueryExecutor.SessionQuerySingleAsync<int>(
                        loginDTO,
                        IndentQB.SAVE_INDENT_DETAIL,
                        detail
                    );

                    if (detail.IndentDetailId <= 0)
                        throw new Exception("INDENTDETAIL insert failed.");

                    // 3️⃣ INSERT MMINDENTMATERIAL
                    if (detail.Materials != null)
                    {
                        foreach (var material in detail.Materials)
                        {
                            material.IndentDetailId = detail.IndentDetailId;

                            material.IndentMaterialId = await _QueryExecutor.SessionQuerySingleAsync<int>(
                                loginDTO,
                                IndentQB.SAVE_INDENT_MATERIAL,
                                material
                            );

                            if (material.IndentMaterialId <= 0)
                                throw new Exception("INDENTMATERIAL insert failed.");
                        }
                    }
                }

                await _QueryExecutor.SameSessionCommitAsync();

                return IndentSaveDTO.IndentId;
            }
            catch
            {
                await _QueryExecutor.SameSessionRollbackAsync();
                throw;
            }
        }
        public async Task UpdateWorkOrder(IndentSaveDTO indentDTO, LoginDTO loginDTO)
        {
            await _QueryExecutor.BeginTransactionAsync(loginDTO);

            try
            {
                // 1️⃣ UPDATE MMINDENT
                var affected = await _QueryExecutor.SessionExecuteAsync(
                    loginDTO,
                    IndentQB.UPDATE_INDENT,
                    indentDTO);

                if (affected <= 0)
                    throw new Exception("MMINDENT update failed.");

                // 2️⃣ Existing Detail Ids
                var dbDetailIds = (await _QueryExecutor.SessionQueryAsync<int>(
                    loginDTO,
                    @"SELECT INDENTDETAILID
              FROM MMINDENTDETAIL
              WHERE INDENTID=@IndentId",
                    new { indentDTO.IndentId }))
                    .ToList();

                var clientDetailIds = new List<int>();

                // 3️⃣ UPSERT DETAILS
                foreach (var detail in indentDTO.Details)
                {
                    detail.IndentId = indentDTO.IndentId;

                    if (detail.IndentDetailId > 0)
                    {
                        await _QueryExecutor.SessionExecuteAsync(
                            loginDTO,
                            IndentQB.UPDATE_INDENT_DETAIL,
                            detail);
                    }
                    else
                    {
                        detail.IndentDetailId = await _QueryExecutor.SessionQuerySingleAsync<int>(
                            loginDTO,
                            IndentQB.SAVE_INDENT_DETAIL,
                            detail);
                    }

                    clientDetailIds.Add(detail.IndentDetailId);

                    // 4️⃣ Existing Material Ids
                    var dbMaterialIds = (await _QueryExecutor.SessionQueryAsync<int>(
                        loginDTO,
                        @"SELECT INDENTMATERIALID
                  FROM MMINDENTMATERIAL
                  WHERE INDENTDETAILID=@IndentDetailId",
                        new { detail.IndentDetailId }))
                        .ToList();

                    var clientMaterialIds = new List<int>();

                    // 5️⃣ UPSERT MATERIALS
                    foreach (var material in detail.Materials)
                    {
                        material.IndentDetailId = detail.IndentDetailId;

                        if (material.IndentMaterialId > 0)
                        {
                            await _QueryExecutor.SessionExecuteAsync(
                                loginDTO,
                                IndentQB.UPDATE_INDENT_MATERIAL,
                                material);
                        }
                        else
                        {
                            material.IndentMaterialId = await _QueryExecutor.SessionQuerySingleAsync<int>(
                                loginDTO,
                                IndentQB.SAVE_INDENT_MATERIAL,
                                material);
                        }

                        clientMaterialIds.Add(material.IndentMaterialId);
                    }

                    // 6️⃣ DELETE REMOVED MATERIALS
                    foreach (var deleteId in dbMaterialIds.Except(clientMaterialIds))
                    {
                        await _QueryExecutor.SessionExecuteAsync(
                            loginDTO,
                            @"DELETE FROM TINDENTMATERIAL
                      WHERE INDENTMATERIALID=@Id",
                            new { Id = deleteId });
                    }
                }

                // 7️⃣ DELETE REMOVED DETAILS
                foreach (var deleteId in dbDetailIds.Except(clientDetailIds))
                {
                    // Delete child first
                    await _QueryExecutor.SessionExecuteAsync(
                        loginDTO,
                        @"DELETE FROM TINDENTMATERIAL
                  WHERE INDENTDETAILID=@Id",
                        new { Id = deleteId });

                    // Delete parent
                    await _QueryExecutor.SessionExecuteAsync(
                        loginDTO,
                        @"DELETE FROM TINDENTDETAIL
                  WHERE INDENTDETAILID=@Id",
                        new { Id = deleteId });
                }

                await _QueryExecutor.SameSessionCommitAsync();
            }
            catch
            {
                await _QueryExecutor.SameSessionRollbackAsync();
                throw;
            }
        }

        // ── Phase 7: Project-wise shortage report (GB4 port) ───────────────────

        public async Task<(IEnumerable<ProjectWiseShortageDTO> Items, int TotalCount)> GetProjectWiseShortage(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
        {
            var (dataSql, countSql) = login.DatabaseType switch
            {
                DBType.SQL => (IndentQB.PROJECT_WISE_SHORTAGE_SQL, IndentQB.PROJECT_WISE_SHORTAGE_COUNT_SQL),
                DBType.PostGre => (IndentQB.PROJECT_WISE_SHORTAGE_PG, IndentQB.PROJECT_WISE_SHORTAGE_COUNT_PG),
                _ => throw new NotSupportedException("Unsupported database type")
            };

            var param = ApplyProjectWiseShortageCriteria(criteriaDTO, firstNumber, maxResult);

            var items = await _QueryExecutor
                .QueryAsync<ProjectWiseShortageDTO>(login, dataSql, param, cancellationToken: ct)
                .ConfigureAwait(false) ?? [];

            var totalCount = await _QueryExecutor
                .ExecuteScalarAsync<int>(login, countSql, param, cancellationToken: ct)
                .ConfigureAwait(false);

            return (items, totalCount);
        }

        // Builds a fully-parameterized Dapper parameter set from CriteriaDTO for GetProjectWiseShortage.
        // Extracts: ItemId/ProjectItemId (required — the output-item scope, ported from GB4's
        // #projectlist temp table to a STRING_SPLIT-friendly CSV parameter), PeriodFromDate,
        // PeriodToDate, OUId, WorkCenterId, ProcessId, AllocationId, IndentId, SKUId, Type.
        // Optional int filters use an "always-bound sentinel" of -1 (see IndentQB's
        // "(@Param = -1 OR col = @Param)" pattern) so no per-request SQL text mutation is needed.
        private static DynamicParameters ApplyProjectWiseShortageCriteria(CriteriaDTO criteriaDTO, int firstNumber, int maxResult)
        {
            int ouId = -1, workCenterId = -1, processId = -1, allocationId = -1, indentId = -1, skuId = -1, type = 1;
            DateTime? periodFromDate = null;
            DateTime? periodToDate = null;
            string projectItemIds = "";

            var attrs = criteriaDTO?.SectionCriteriaList?.FirstOrDefault()?.AttributesCriteriaList;
            if (attrs != null)
            {
                foreach (var attr in attrs)
                {
                    switch (attr.FieldName?.ToLowerInvariant())
                    {
                        case "itemid":
                        case "projectitemid":
                            var idsRaw = ExtractProjectWiseShortageStringValue(attr);
                            if (!string.IsNullOrWhiteSpace(idsRaw))
                            {
                                projectItemIds = string.Join(",", idsRaw
                                    .Split(',', StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries)
                                    .Where(s => int.TryParse(s, out _)));
                            }
                            break;
                        case "periodfromdate": periodFromDate = ExtractProjectWiseShortageDateValue(attr); break;
                        case "periodtodate": periodToDate = ExtractProjectWiseShortageDateValue(attr); break;
                        case "ouid": ouId = ExtractProjectWiseShortageIntValue(attr, -1); break;
                        case "workcenterid": workCenterId = ExtractProjectWiseShortageIntValue(attr, -1); break;
                        case "processid": processId = ExtractProjectWiseShortageIntValue(attr, -1); break;
                        case "allocationid": allocationId = ExtractProjectWiseShortageIntValue(attr, -1); break;
                        case "indentid": indentId = ExtractProjectWiseShortageIntValue(attr, -1); break;
                        case "skuid": skuId = ExtractProjectWiseShortageIntValue(attr, -1); break;
                        case "type": type = ExtractProjectWiseShortageIntValue(attr, 1); break;
                    }
                }
            }

            // GB4 parity: ItemId (the output-item / project-item scope) is a required input.
            if (string.IsNullOrWhiteSpace(projectItemIds))
                throw new ArgumentException("ItemId (project item ids) is required for GetProjectWiseShortage.");

            var p = new DynamicParameters();
            p.Add("firstnumber", firstNumber);
            p.Add("maxresult", maxResult);
            p.Add("ProjectItemIds", projectItemIds);
            p.Add("PeriodFromDate", periodFromDate, System.Data.DbType.DateTime);
            p.Add("PeriodToDate", periodToDate, System.Data.DbType.DateTime);
            p.Add("OUId", ouId);
            p.Add("WorkCenterId", workCenterId);
            p.Add("ProcessId", processId);
            p.Add("AllocationId", allocationId);
            p.Add("IndentId", indentId);
            p.Add("SKUId", skuId);
            p.Add("Type", type);
            return p;
        }

        private static string ExtractProjectWiseShortageStringValue(AttributesCriteriaDTO attr)
        {
            if (attr?.FieldValue == null) return "";
            if (attr.FieldValue is System.Text.Json.JsonElement je)
                return je.ValueKind == System.Text.Json.JsonValueKind.String
                    ? je.GetString() ?? ""
                    : je.GetRawText() ?? "";
            return attr.FieldValue.ToString() ?? "";
        }

        private static int ExtractProjectWiseShortageIntValue(AttributesCriteriaDTO attr, int defaultValue)
        {
            var val = ExtractProjectWiseShortageStringValue(attr);
            return int.TryParse(val, out int v) ? v : defaultValue;
        }

        private static DateTime? ExtractProjectWiseShortageDateValue(AttributesCriteriaDTO attr)
        {
            var val = ExtractProjectWiseShortageStringValue(attr);
            if (string.IsNullOrWhiteSpace(val)) return null;
            if (long.TryParse(val, out long epochSeconds))
                return DateTimeOffset.FromUnixTimeSeconds(epochSeconds).UtcDateTime;
            return DateTime.TryParse(val, out DateTime dt) ? dt : null;
        }

        // ── Phase 7: Indent reference-number picklist (GB4 port) ───────────────

        public async Task<IEnumerable<IndentRefNumberPicklistDTO>> GetSelectListIndentRefNumber(
            int firstNumber, int maxResult, LoginDTO login, CancellationToken ct)
        {
            string sql = login.DatabaseType switch
            {
                DBType.SQL => IndentQB.INDENT_REFERENCE_NUMBER_SELECTLIST_SQL,
                DBType.PostGre => IndentQB.INDENT_REFERENCE_NUMBER_SELECTLIST_PG,
                _ => throw new NotSupportedException("Unsupported database type")
            };

            var param = new { firstnumber = firstNumber, maxresult = maxResult };
            return await _QueryExecutor
                .QueryAsync<IndentRefNumberPicklistDTO>(login, sql, param, cancellationToken: ct)
                .ConfigureAwait(false) ?? [];
        }

        public async Task<IEnumerable<IndentDTO>> GetListOfIndent(CriteriaDTO criteriaDTO,LoginDTO loginDTO)
        {
            var indentDict = new Dictionary<long, IndentDTO>();

            await _QueryExecutor.QueryMultiMapAsync<
                IndentDTO,
                IndentDetailDTO,
                IndentMaterialDTO,
                IndentResourceDTO,
                IndentEmptyDTO,
                IndentDTO>(
                loginDTO,
                IndentQB.GET_LIST_OF_INDENT,
                (indent, detail, material, resource, empty) =>
                {
                    // -----------------------------------------
                    // INDENT
                    // -----------------------------------------

                    if (!indentDict.TryGetValue(
                            indent.IndentId,
                            out var currentIndent))
                    {
                        currentIndent = indent;

                        currentIndent.Details =
                            new List<IndentDetailDTO>();

                        indentDict.Add(
                            currentIndent.IndentId,
                            currentIndent);
                    }

                    // -----------------------------------------
                    // INDENT DETAIL
                    // -----------------------------------------

                    if (detail != null &&
                        detail.IndentDetailId > 0)
                    {
                        var currentDetail =
                            currentIndent.Details.FirstOrDefault(
                                x => x.IndentDetailId ==
                                     detail.IndentDetailId);

                        if (currentDetail == null)
                        {
                            currentDetail = detail;

                            currentDetail.Materials ??=
                                new List<IndentMaterialDTO>();

                            currentDetail.Resources ??=
                                new List<IndentResourceDTO>();

                            currentIndent.Details.Add(currentDetail);
                        }

                        // -----------------------------------------
                        // INDENT MATERIAL
                        // -----------------------------------------

                        if (material != null &&
                            material.IndentMaterialId > 0)
                        {
                            if (!currentDetail.Materials.Any(
                                x => x.IndentMaterialId ==
                                     material.IndentMaterialId))
                            {
                                currentDetail.Materials.Add(material);
                            }
                        }

                        // -----------------------------------------
                        // INDENT RESOURCE
                        // -----------------------------------------

                        if (resource != null &&
                            resource.IndentResourceId > 0)
                        {
                            if (!currentDetail.Resources.Any(
                                x => x.IndentResourceId ==
                                     resource.IndentResourceId))
                            {
                                currentDetail.Resources.Add(resource);
                            }
                        }
                    }

                    return currentIndent;
                },
                criteriaDTO,
                splitOn:
                    "IndentDetailId,IndentMaterialId,IndentResourceId,DummyId");

            return indentDict.Values.ToList();
        }
    }
}