using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Logging;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5Exception;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.ResponseStandard;
using GB5Shared.Validation;
using MMDAL.DTO.BinMaster;
using MMDAL.DTO.DiscountPrice;
using MMDAL.DTO.DutyTable;
using MMDAL.DTO.Item;
using MMDAL.DTO.ItemBrand;
using MMDAL.DTO.ItemGroup;
using MMDAL.DTO.ItemOU;
using MMDAL.DTO.ItemParty;
using MMDAL.DTO.ItemSubCategory;
using MMDAL.DTO.Model;
using MMDAL.DTO.PriceList;
using MMDAL.DTO.ProductCost;
using MMDAL.DTO.TaxTable;
using MMDAL.Query.Item;
using MMDAL.Query.ItemGroup;
using Unisoft.MMDAL.DTO.ItemCategory;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Text;
using System.Threading.Tasks;
namespace MMDAL.CustomCode.Item
{
    public class ItemDAL : IItemDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public ItemDAL(IQueryExecutor IQueryExecutor, IValidation validation)
        {
            _QueryExecutor = IQueryExecutor;
            _Validation = validation;
        }



        public async Task<string> GetItemMasterReport(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, int FromNumber, int ToNumber, string Orderbyfield, bool Count = false)
        {
            try
            {
                string sql = ItemQB.ITEMMASTER_REPORT;
                IEnumerable<ItemDTO> items = await _QueryExecutor.QueryAsync<ItemDTO>(LoginDTO, sql).ConfigureAwait(false);
                string json = JsonConvert.SerializeObject(items);
                return json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListItem(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            // Criteria field-name → SQL column mapping (matches GB4 ApplyGetSelectListItemNewCriteria)
            string[,] CriteriaWithQueryField = new string[,]
            {
                { "Category.Id",    "I.CATEGORYID"     },
                { "Name",           "I.ITEMNAME"       },
                { "ItemId",         "I.ITEMID"         },
                { "Code",           "I.ITEMCODE"       },
                { "Status",         "I.STATUS"         },
                { "CodeCategoryId", "I.CODECATEGORYID" },
            };

            try
            {
                string innerSQL = ApplyItemCriteria(ItemQB.GET_SELECTLIST_ITEM_INNER, CriteriaWithQueryField, CriteriaDTO);

                if (IsCount)
                {
                    string countSQL = $"SELECT COUNT(*) FROM ({innerSQL}) AS CountResult";
                    var countRows = await _QueryExecutor.QueryAsync<int>(LoginDTO, countSQL, null);
                    return countRows.FirstOrDefault().ToString();
                }
                else
                {
                    string fullSQL = $"WITH PagedItem AS ({innerSQL}) {ItemQB.GET_SELECTLIST_ITEM_OUTER}";
                    var parameter = new { firstnumber = FirstNumber, maxresult = MaxResult };
                    var Result = await _QueryExecutor.QueryAsync<ItemPickListDTONew>(LoginDTO, fullSQL, parameter);
                    return JsonConvert.SerializeObject(Result);
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListProductFamily(CriteriaDTO criteriaDTO, LoginDTO login)
        {
            var result = await _QueryExecutor.QueryWithCriteriaAsync<dynamic>(
                login, ItemQB.GET_SELECT_LIST_PRODUCT_FAMILY,
                new { TenantId = login.ClientId }, criteriaDTO);
            return JsonConvert.SerializeObject(result);
        }

        // GB4 parity: PooledItemBLL.GetItemSelectlistFromOU / ItemDAL.GetItemSelectlistFromItemOU.
        // Criteria field-name → SQL column mapping matches GB4 ApplyItemSelectlistFromOU.
        public async Task<string> GetItemSelectlistFromOU(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            string[,] CriteriaWithQueryField = new string[,]
            {
                { "Id",              "MI.ITEMID"            },
                { "Code",            "MI.ITEMCODE"          },
                { "Name",            "MI.ITEMNAME"          },
                { "Status",          "MI.STATUS"            },
                { "ItemType",        "MI.ITEMTYPE"          },
                { "BOMType",         "MI.BOMTYPE"           },
                { "IsStockPosting",  "MI.ISSTOCKPOSTING"    },
                { "IsMakeOrBuy",     "IOU.ISMAKEORBUY"      },
            };

            try
            {
                string innerSQL = ApplyItemCriteria(ItemQB.GET_ITEM_SELECTLIST_OU_INNER, CriteriaWithQueryField, CriteriaDTO);
                var parameter = new { ouid = LoginDTO.WorkOUId, firstnumber = FirstNumber, maxresult = MaxResult };

                if (IsCount)
                {
                    string countSQL = $"SELECT COUNT(*) FROM ({innerSQL}) AS CountResult";
                    var countRows = await _QueryExecutor.QueryAsync<int>(LoginDTO, countSQL, parameter);
                    return countRows.FirstOrDefault().ToString();
                }
                else
                {
                    string fullSQL = $"WITH PagedItemOU AS ({innerSQL}) {ItemQB.GET_ITEM_SELECTLIST_OU_OUTER}";
                    var result = await _QueryExecutor.QueryAsync<ItemPicklistDTO>(LoginDTO, fullSQL, parameter);
                    return JsonConvert.SerializeObject(result);
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        // GB4 parity: MMBLL.Item.ItemBLL.GetItemBasedOnDocument. DocumentId is mandatory
        // (thrown as GB5Exception when missing, matching GB4's MethodNotAllowedException);
        // Type selects Regular (0) vs Costing-Detail-based (1) shape; Name is an optional
        // Itemcode/Itemname LIKE filter applied after the DocumentId criteria.
        public async Task<string> GetItemBasedOnDocument(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                int documentId = -1, type = 0; // 0 Regular, 1 Costing Detail Based
                string name = "";

                var attrs = CriteriaDTO?.SectionCriteriaList?
                    .Where(s => s?.AttributesCriteriaList != null)
                    .SelectMany(s => s.AttributesCriteriaList)
                    ?? Enumerable.Empty<AttributesCriteriaDTO>();

                foreach (var attr in attrs)
                {
                    switch (attr.FieldName?.ToLower())
                    {
                        case "documentid": documentId = ExtractItemDetailIntValue(attr); break;
                        case "name": name = ExtractItemDetailStringValue(attr); break;
                        case "type": type = ExtractItemDetailIntValue(attr); break;
                    }
                }

                if (documentId == -1)
                    throw new Exception("Document must be supplied to get item filter..");

                string[,] CriteriaWithQueryField = new string[,]
                {
                    { "DocumentId", "a.DOCUMENTID" },
                };

                string innerSQL = type switch
                {
                    0 => ItemQB.GET_ITEM_PICKLIST_DOCUMENT_WISE_INNER,
                    1 => ItemQB.GET_ITEM_PICKLIST_DOCUMENT_WISE_COSTING_INNER,
                    _ => throw new Exception("Type Not supported.."),
                };

                // GB4 joins the "still-costed" DocumentId filter into TMMDETAIL via TMMHEAD (a.DOCUMENTID);
                // the costing variant filters TMMDETAIL directly (no a.DOCUMENTID join needed elsewhere).
                if (type == 0)
                {
                    innerSQL = ApplyItemCriteria(innerSQL, CriteriaWithQueryField, CriteriaDTO);
                }
                else
                {
                    // Costing variant already scopes the subquery by @documentid; still apply
                    // the outer DocumentId predicate against TMMDETAIL's own document header join.
                    innerSQL += " AND a.DOCUMENTID IN (SELECT DOCUMENTID FROM TMMHEAD WHERE DOCUMENTID = @documentid)";
                }

                if (!string.IsNullOrEmpty(name))
                {
                    string escaped = name.Replace("'", "''");
                    innerSQL += $" AND (b.ITEMCODE LIKE '%{escaped}%' OR b.ITEMNAME LIKE '%{escaped}%')";
                }

                var parameter = new { documentid = documentId, firstnumber = FirstNumber, maxresult = MaxResult };

                if (IsCount)
                {
                    string countSQL = $"SELECT COUNT(*) FROM ({innerSQL}) AS CountResult";
                    var countRows = await _QueryExecutor.QueryAsync<int>(LoginDTO, countSQL, parameter);
                    return countRows.FirstOrDefault().ToString();
                }
                else
                {
                    string fullSQL = $"WITH PagedItemDocument AS ({innerSQL}) {ItemQB.GET_ITEM_PICKLIST_DOCUMENT_WISE_OUTER}";
                    var result = await _QueryExecutor.QueryAsync<ItemPickListDTONew>(LoginDTO, fullSQL, parameter);
                    return JsonConvert.SerializeObject(result);
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── DIM* SelectList queries — GB4 parity: Item.svc/DIMItemBrandSelectList, DIMItemCategorySelectList,
        // DIMItemGroupSelectList, DIMItemModelSelectList, DIMItemSubCategorySelectList.
        // Reuses ApplyItemCriteria (below) for parameterized-free equality filtering on the same
        // Id/Code/Name field names GB4 exposed, scoped to the dimitemclass alias.
        public async Task<string> GetDIMItemBrandSelectList(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            string[,] CriteriaWithQueryField = new string[,]
            {
                { "Id",   "dimitemclass.ITEMBRANDID"   },
                { "Code", "dimitemclass.ITEMBRANDCODE" },
                { "Name", "dimitemclass.ITEMBRANDNAME" },
            };

            try
            {
                string sql = ApplyItemCriteria(ItemQB.GET_DIM_ITEMBRAND_SELECTLIST, CriteriaWithQueryField, CriteriaDTO);
                var result = await _QueryExecutor.QueryAsync<ItemBrandPicklistDTO>(LoginDTO, sql, null);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetDIMItemCategorySelectList(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            string[,] CriteriaWithQueryField = new string[,]
            {
                { "Id",   "dimitemclass.ITEMCATEGORYID"   },
                { "Code", "dimitemclass.ITEMCATEGORYCODE" },
                { "Name", "dimitemclass.ITEMCATEGORYNAME" },
            };

            try
            {
                string sql = ApplyItemCriteria(ItemQB.GET_DIM_ITEMCATEGORY_SELECTLIST, CriteriaWithQueryField, CriteriaDTO);
                var result = await _QueryExecutor.QueryAsync<ItemCategoryPicklistDTO>(LoginDTO, sql, null);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetDIMItemGroupSelectList(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            string[,] CriteriaWithQueryField = new string[,]
            {
                { "Id",   "dimitemclass.ITEMGROUPID"   },
                { "Code", "dimitemclass.ITEMGROUPCODE" },
                { "Name", "dimitemclass.ITEMGROUPNAME" },
            };

            try
            {
                string sql = ApplyItemCriteria(ItemQB.GET_DIM_ITEMGROUP_SELECTLIST, CriteriaWithQueryField, CriteriaDTO);
                var result = await _QueryExecutor.QueryAsync<ItemGroupPicklistDTO>(LoginDTO, sql, null);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetDIMItemModelSelectList(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            string[,] CriteriaWithQueryField = new string[,]
            {
                { "Id",   "dimitemclass.MODELID"   },
                { "Code", "dimitemclass.MODELCODE" },
                { "Name", "dimitemclass.MODELNAME" },
            };

            try
            {
                string sql = ApplyItemCriteria(ItemQB.GET_DIM_ITEMMODEL_SELECTLIST, CriteriaWithQueryField, CriteriaDTO);
                var result = await _QueryExecutor.QueryAsync<ModelPicklistDTO>(LoginDTO, sql, null);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetDIMItemSubCategorySelectList(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            string[,] CriteriaWithQueryField = new string[,]
            {
                { "Id",   "dimitemclass.ITEMSUBCATEGORYID"   },
                { "Code", "dimitemclass.ITEMSUBCATEGORYCODE" },
                { "Name", "dimitemclass.ITEMSUBCATEGORYNAME" },
            };

            try
            {
                string sql = ApplyItemCriteria(ItemQB.GET_DIM_ITEMSUBCATEGORY_SELECTLIST, CriteriaWithQueryField, CriteriaDTO);
                var result = await _QueryExecutor.QueryAsync<ItemSubCategoryPicklistDTO>(LoginDTO, sql, null);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Builds WHERE conditions from CriteriaDTO using join-forward semantics:
        // JoinType on attribute[i] specifies how attribute[i] connects to attribute[i+1].
        // Consecutive attributes connected by Or are wrapped in a single AND (...OR...) group,
        // which prevents the WHERE 1=1 OR condition bug that PickListExecutor produces.
        //
        // AllocationId is handled separately across ALL sections — when present with a valid
        // integer value it adds a subquery filter via TPENDINGALLOCATION.ALLOTEDALLOCATIONID.
        // Values of "", "0", or "-1" are treated as "not provided" and skipped.
        private string ApplyItemCriteria(string baseSql, string[,] fieldMap, CriteriaDTO criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null || criteriaDTO.SectionCriteriaList.Count == 0)
                return baseSql;

            // Collect AllocationId from any section, and regular attrs from section[0]
            AttributesCriteriaDTO allocationAttr = null;
            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                var match = section?.AttributesCriteriaList?.FirstOrDefault(a =>
                    a.FieldName?.Equals("AllocationId", StringComparison.OrdinalIgnoreCase) == true);
                if (match != null) { allocationAttr = match; break; }
            }

            var section0Attrs = criteriaDTO.SectionCriteriaList[0]?.AttributesCriteriaList?
                .Where(a => !a.FieldName?.Equals("AllocationId", StringComparison.OrdinalIgnoreCase) == true)
                .ToList() ?? new List<AttributesCriteriaDTO>();

            // Build a flat list of (sql_condition, join_to_next) for regular attributes
            var conditions = new List<(string Cond, CriteriaDTO.AttributeJoinOperationType JoinToNext)>();
            foreach (var attr in section0Attrs)
            {
                string val = attr.FieldValue?.ToString() ?? "";
                if (string.IsNullOrEmpty(val)) continue;

                string col = null;
                for (int m = 0; m < fieldMap.GetLength(0); m++)
                {
                    if (fieldMap[m, 0].Equals(attr.FieldName, StringComparison.OrdinalIgnoreCase))
                    {
                        col = fieldMap[m, 1];
                        break;
                    }
                }
                if (col == null) continue;

                string cond = BuildItemConditionSql(col, attr);
                if (cond != null)
                    conditions.Add((cond, attr.JoinType));
            }

            var sb = new StringBuilder(baseSql);

            // Walk the list grouping OR-connected conditions into parenthesised AND (...OR...) blocks
            int idx = 0;
            while (idx < conditions.Count)
            {
                var orGroup = new List<string> { conditions[idx].Cond };

                while (idx < conditions.Count - 1 &&
                       conditions[idx].JoinToNext == CriteriaDTO.AttributeJoinOperationType.Or)
                {
                    idx++;
                    orGroup.Add(conditions[idx].Cond);
                }

                sb.Append(orGroup.Count > 1
                    ? $" AND ({string.Join(" OR ", orGroup)})"
                    : $" AND {orGroup[0]}");

                idx++;
            }

            // AllocationId — subquery filter (skip if empty, "0", or "-1")
            if (allocationAttr != null)
            {
                // FieldValue may be a JsonElement (System.Text.Json deserialization via FastEndpoints)
                string allocVal = allocationAttr.FieldValue is System.Text.Json.JsonElement je
                    ? (je.ValueKind == System.Text.Json.JsonValueKind.String ? je.GetString() : je.GetRawText()) ?? ""
                    : allocationAttr.FieldValue?.ToString() ?? "";
                if (!string.IsNullOrEmpty(allocVal) && allocVal != "0" && allocVal != "-1" &&
                    long.TryParse(allocVal, out _))
                {
                    // MMASTERSCHEDULEDETAIL.ALLOCATIONID references MALLOCATION.ALLOCATIONID directly
                    sb.Append($" AND I.ITEMID IN (SELECT ITEMID FROM MMASTERSCHEDULEDETAIL WHERE ALLOCATIONID IN ({allocVal}))");
                }
            }

            return sb.ToString();
        }

        private string BuildItemConditionSql(string column, AttributesCriteriaDTO attr)
        {
            string val = attr.FieldValue?.ToString() ?? "";
            return attr.OperationType switch
            {
                CriteriaDTO.OperationType.Equal              => $"{column} IN({val})",
                CriteriaDTO.OperationType.In                 => $"{column} IN({val})",
                CriteriaDTO.OperationType.NotIn              => $"{column} NOT IN({val})",
                CriteriaDTO.OperationType.NotEqual           => $"{column} <> {val}",
                CriteriaDTO.OperationType.Like               => $"{column} LIKE ('%{val}%')",
                CriteriaDTO.OperationType.GreaterThan        => $"{column} > {val}",
                CriteriaDTO.OperationType.LessThan           => $"{column} < {val}",
                CriteriaDTO.OperationType.GreaterThanOrEqualTo => $"{column} >= '{val}'",
                CriteriaDTO.OperationType.LessThanOrEqualTo  => $"{column} <= '{val}'",
                _ => null
            };
        }
        public async Task<int> SaveItem(ItemDTO ItemDTO, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = ItemQB.SAVE_ITEM;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, ItemDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(Error);
            }
        }
        public async Task<int> UpdateItem(ItemDTO ItemDTO, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = ItemQB.UPDATE_ITEM;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, ItemDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(Error);
            }
        }
        public async Task<string> GetItem(int ItemId, LoginDTO LoginDTO)
        {
            string Json = "";
            try
            {
                string sql = ItemQB.GET_ITEM;
                var parameters = new { itemid = ItemId };
                ItemDTO ItemDTOs = await _QueryExecutor.QuerySingleAsync<ItemDTO>(LoginDTO, sql, parameters);
                Json = JsonConvert.SerializeObject(ItemDTOs);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Json = null;
            }
        }

        // ── DeleteItem (GB4 migration: DELETE /mms/Item.svc/?ItemId={ItemId}) ────────────
        //
        // GB4 source: MMBLL.Item.ItemBLL.DeleteItem. The three methods below split GB4's single
        // method along the GB5 layer boundary: the two reads feed the business decision that
        // ItemBLL.DeleteItem makes; this DAL only executes the cascade it is told to execute.

        public async Task<bool> ItemExists(int ItemId, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                int count = await _QueryExecutor.ExecuteScalarAsync<int>(
                    LoginDTO, ItemQB.GET_ITEM_EXISTS,
                    new { ItemId, TenantId = LoginDTO.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);

                return count > 0;
            }
            catch (Exception)
            {
                throw;
            }
        }

        // GB4 parity: stands in for FrameworkDAL WhereUsedDAL.WhereUsed(ItemId, typeof(Item), ...).
        // Returns the number of transaction rows referencing the item across TSTOCKLEDGER,
        // TMMDETAIL and TSTOCKPOSITION — 0 means the item is safe to delete.
        public async Task<int> GetItemTransactionUsageCount(int ItemId, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.ExecuteScalarAsync<int>(
                    LoginDTO, ItemQB.GET_ITEM_TRANSACTION_USAGE_COUNT,
                    new { ItemId, TenantId = LoginDTO.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Returns the names of tables that still reference this item through a declared foreign
        // key, excluding the five child tables DeleteItem cascades. Empty means nothing blocks.
        //
        // Two steps: read the FK list from the catalog, then probe all of them in one round trip.
        // The probe SQL is assembled from sys.tables/sys.columns names — catalog identifiers, not
        // caller input — and ItemId stays a parameter. Identifiers are bracket-quoted with ']'
        // escaped so an exotic table name cannot break out of the quoting.
        public async Task<IReadOnlyList<string>> GetItemBlockingReferences(int ItemId, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                var References = await _QueryExecutor.QueryAsync<ItemReferencingTableDTO>(
                    LoginDTO, ItemQB.GET_ITEM_REFERENCING_TABLES, null!,
                    cancellationToken: ct).ConfigureAwait(false);

                if (References == null || !References.Any())
                    return Array.Empty<string>();

                static string Quote(string Identifier) => $"[{Identifier.Replace("]", "]]")}]";

                var Probes = References.Select(Reference =>
                    $"SELECT '{Reference.TableName.Replace("'", "''")}' AS ReferencingTable " +
                    $"WHERE EXISTS (SELECT 1 FROM {Quote(Reference.SchemaName)}.{Quote(Reference.TableName)} " +
                    $"WHERE {Quote(Reference.ColumnName)} = @ItemId)");

                string ProbeSql = string.Join($"{Environment.NewLine}UNION ALL{Environment.NewLine}", Probes);

                var Blocking = await _QueryExecutor.QueryAsync<string>(
                    LoginDTO, ProbeSql, new { ItemId },
                    cancellationToken: ct).ConfigureAwait(false);

                return Blocking?.Distinct().ToList() ?? (IReadOnlyList<string>)Array.Empty<string>();
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Six DELETEs in one transaction, child-before-parent, matching GB4's statement order.
        // ExecuteInTransactionAsync owns the connection/transaction lifecycle, so there is no
        // hand-rolled Begin/Commit/Rollback here.
        public async Task<string> DeleteItem(int ItemId, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                // Child tables carry no TENANTID column, so they take ItemId only; MITEM itself
                // is deleted tenant-scoped. Each statement gets exactly the parameters its SQL
                // references rather than one shared object.
                var ChildParameters = new { ItemId };
                var ItemParameters  = new { ItemId, TenantId = LoginDTO.ClientId };

                var Statements = new List<(string sql, object param)>
                {
                    (ItemQB.DELETE_ITEM_DETAIL, ChildParameters),
                    (ItemQB.DELETE_ITEM_OU,     ChildParameters),
                    (ItemQB.DELETE_ITEM_ADDON,  ChildParameters),
                    (ItemQB.DELETE_ITEM_ALIAS,  ChildParameters),
                    (ItemQB.DELETE_ITEM_STOCK,  ChildParameters),
                    (ItemQB.DELETE_ITEM,        ItemParameters),
                };

                await _QueryExecutor.ExecuteInTransactionAsync(LoginDTO, Statements).ConfigureAwait(false);

                return $"{SuccessResponse.DeleteSuccessMessage} {ItemId}";
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.DeleteErrorMessage}").ConfigureAwait(false);
                throw new Exception(Error);
            }
        }

        public async Task<IEnumerable<ItemDetailDTO>> GetItemDetailsQualityParameter(
     int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct = default)
        {
            int itemDetailType = 1, fromRouting = -1, fromRoutingScreen = 1;
            var attrs = criteriaDTO?.SectionCriteriaList?[0]?.AttributesCriteriaList;
            if (attrs != null)
            {
                foreach (var attr in attrs)
                {
                    switch (attr.FieldName?.ToLower())
                    {
                        case "itemdetailtype": itemDetailType = ExtractItemDetailIntValue(attr); break;
                        case "fromrouting": fromRouting = ExtractItemDetailIntValue(attr); break;
                        case "fromroutingscreen": fromRoutingScreen = ExtractItemDetailIntValue(attr); break;
                    }
                }
            }

            try
            {
                string sql;
                if (fromRoutingScreen == 1)
                {
                    if (itemDetailType == 1)
                    {
                        sql = ItemQB.GET_ITEMDETAILS_QUALITY;
                        if (fromRouting == 0)
                            sql = sql.Replace(ItemQB.GET_ITEMDETAILS_QUALITY_PARAMSET_CONDITION, "");
                    }
                    else
                    {
                        sql = ItemQB.GET_ITEMDETAILS_QUALITYPARAMETERS;
                    }
                }
                else
                {
                    sql = ItemQB.GET_ITEMDETAILS_QUALITYPARAMETERS_FOR_ROUTINGS;
                }

                string[] controlFields = { "itemdetailtype", "fromrouting", "fromroutingscreen" };
                sql = ApplyItemDetailQualityCriteria(sql, criteriaDTO, controlFields, fromRoutingScreen, itemDetailType);

                // FIX 3: use correct alias per active query
                if (itemDetailType == 1)
                    sql += " ORDER BY paramset.SLNO";
                else
                    sql += " ORDER BY paramsetdet.SLNO";

                var result = await _QueryExecutor.QueryAsync<ItemDetailDTO>(loginDTO, sql, null);
                return result ?? Enumerable.Empty<ItemDetailDTO>();
            }
            catch (Exception)
            {
                throw;
            }
        }

        private string ApplyItemDetailQualityCriteria(
    string baseSql,
    CriteriaDTO criteriaDTO,
    string[] skipFields,
    int fromRoutingScreen,
    int itemDetailType)
        {
            if (criteriaDTO?.SectionCriteriaList == null || criteriaDTO.SectionCriteriaList.Count == 0)
                return baseSql;

            // FIX 4: paramset alias differs per query
            // GET_ITEMDETAILS_QUALITY          → alias: paramset
            // GET_ITEMDETAILS_QUALITYPARAMETERS / FOR_ROUTINGS → alias: paramsetdet
            bool useParamSetAlias = (fromRoutingScreen == 1 && itemDetailType == 1);
            string paramsetAlias = useParamSetAlias ? "paramset" : "paramsetdet";

            // FIX 5: INDENTID / INDENTDETAILID column names were wrong case
            var fieldMap = new string[,]
            {
        { "Item.Id",               "itd.ITEMID"                        },
        { "ItemId",                "itd.ITEMID"                        },
        { "SKUId",                 "itd.SKUID"                         },
        { "ParameterSetId",        $"{paramsetAlias}.PARAMETERSETID"    },
        { "RoutingParameterSetId", "rverde.PARAMETERSETID"             },
        { "PeriodFromDate",        "rver.FROMDATE"                     },
        { "PeriodToDate",          "rver.TODATE"                       },
        { "Version",               "rver.VERSION"                      },
        { "IndentId",              "indt.INDENTID"                     },
        { "IndentDetailId",        "indt.INDENTDETAILID"               },
            };

            var sectionAttrs = criteriaDTO.SectionCriteriaList[0]?.AttributesCriteriaList
                               ?? new List<AttributesCriteriaDTO>();

            var conditions = new List<(string Cond, CriteriaDTO.AttributeJoinOperationType JoinToNext)>();
            foreach (var attr in sectionAttrs)
            {
                if (skipFields.Contains(attr.FieldName?.ToLower())) continue;

                string col = null;
                for (int m = 0; m < fieldMap.GetLength(0); m++)
                {
                    if (fieldMap[m, 0].Equals(attr.FieldName, StringComparison.OrdinalIgnoreCase))
                    { col = fieldMap[m, 1]; break; }
                }
                if (col == null) continue;

                string val = ExtractItemDetailStringValue(attr);
                if (string.IsNullOrWhiteSpace(val)) continue;

                string cond = BuildItemDetailConditionSql(col, attr, val);
                if (cond != null)
                    conditions.Add((cond, attr.JoinType));
            }

            var sb = new StringBuilder(baseSql);
            int idx = 0;
            while (idx < conditions.Count)
            {
                var orGroup = new List<string> { conditions[idx].Cond };
                while (idx < conditions.Count - 1 &&
                       conditions[idx].JoinToNext == CriteriaDTO.AttributeJoinOperationType.Or)
                { idx++; orGroup.Add(conditions[idx].Cond); }

                sb.Append(orGroup.Count > 1
                    ? $" AND ({string.Join(" OR ", orGroup)})"
                    : $" AND {orGroup[0]}");
                idx++;
            }

            return sb.ToString();
        }


        private string ApplyItemDetailQualityCriteria(string baseSql, CriteriaDTO criteriaDTO, string[] skipFields)
        {
            if (criteriaDTO?.SectionCriteriaList == null || criteriaDTO.SectionCriteriaList.Count == 0)
                return baseSql;

            var fieldMap = new string[,]
            {
                { "Item.Id",               "itd.ITEMID"               },
                { "ItemId",                "itd.ITEMID"               },
                { "SKUId",                 "itd.SKUID"                },
                { "ParameterSetId",        "paramset.PARAMETERSETID"  },
                { "RoutingParameterSetId", "rverde.PARAMETERSETID"    },
                { "PeriodFromDate",        "rver.FROMDATE"            },
                { "PeriodToDate",          "rver.TODATE"              },
                { "Version",               "rver.VERSION"             },
                { "IndentId",              "indt.IndentId"            },
                { "IndentDetailId",        "indt.IndentDetailId"      },
            };

            var sectionAttrs = criteriaDTO.SectionCriteriaList[0]?.AttributesCriteriaList
                               ?? new List<AttributesCriteriaDTO>();

            var conditions = new List<(string Cond, CriteriaDTO.AttributeJoinOperationType JoinToNext)>();
            foreach (var attr in sectionAttrs)
            {
                if (skipFields.Contains(attr.FieldName?.ToLower())) continue;

                string col = null;
                for (int m = 0; m < fieldMap.GetLength(0); m++)
                {
                    if (fieldMap[m, 0].Equals(attr.FieldName, StringComparison.OrdinalIgnoreCase))
                    { col = fieldMap[m, 1]; break; }
                }
                if (col == null) continue;

                string val = ExtractItemDetailStringValue(attr);
                if (string.IsNullOrWhiteSpace(val)) continue;

                string cond = BuildItemDetailConditionSql(col, attr, val);
                if (cond != null)
                    conditions.Add((cond, attr.JoinType));
            }

            var sb = new StringBuilder(baseSql);
            int idx = 0;
            while (idx < conditions.Count)
            {
                var orGroup = new List<string> { conditions[idx].Cond };
                while (idx < conditions.Count - 1 &&
                       conditions[idx].JoinToNext == CriteriaDTO.AttributeJoinOperationType.Or)
                { idx++; orGroup.Add(conditions[idx].Cond); }

                sb.Append(orGroup.Count > 1
                    ? $" AND ({string.Join(" OR ", orGroup)})"
                    : $" AND {orGroup[0]}");
                idx++;
            }

            return sb.ToString();
        }

        private static string BuildItemDetailConditionSql(string column, AttributesCriteriaDTO attr, string val)
        {
            return attr.OperationType switch
            {
                CriteriaDTO.OperationType.Equal => $"{column} IN({val})",
                CriteriaDTO.OperationType.In => $"{column} IN({val})",
                CriteriaDTO.OperationType.NotIn => $"{column} NOT IN({val})",
                CriteriaDTO.OperationType.NotEqual => $"{column} <> {val}",
                CriteriaDTO.OperationType.Like => $"{column} LIKE ('%{val}%')",
                CriteriaDTO.OperationType.GreaterThan => $"{column} > {val}",
                CriteriaDTO.OperationType.LessThan => $"{column} < {val}",
                CriteriaDTO.OperationType.GreaterThanOrEqualTo => $"{column} >= '{val}'",
                CriteriaDTO.OperationType.LessThanOrEqualTo => $"{column} <= '{val}'",
                _ => null
            };
        }

        private static string ExtractItemDetailStringValue(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 ExtractItemDetailIntValue(AttributesCriteriaDTO attr)
        {
            string val = ExtractItemDetailStringValue(attr);
            return int.TryParse(val, out int v) ? v : 0;
        }

        // Barcode lookup result — internal shape only, mirrors GB4's inline MBARCODE/TLOT/MPACK/MPACKSETDETAIL query.
        private class BarcodeItemLookupDTO
        {
            public int PackId { get; set; }
            public string PackName { get; set; }
            public int LotId { get; set; }
            public string LotNumber { get; set; }
            public decimal PackQuantity { get; set; }
            public decimal Quantity { get; set; }
            public double ConversionFactor { get; set; }
        }

        // ── GetItemDetails via CriteriaDTO (GB4 migration: ItemDAL.GetNewItemDetailsFromTransaction) ──
        // Resolves everything needed to add an item line to a transaction: rate (item-party contract,
        // price list, or MITEMOU fallback), tax/duty/GST table detail, price costing, bin, discount-price
        // adjustment, average-cost override, item-party charges, comments, barcode, BOM production qty.
        public async Task<string> GetItemDetails(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                // ── Criteria extraction — manual field-by-name loop, matching GB4's own approach.
                // CriteriaParser.Parse isn't used here: it coerces values via CriteriaAttributeType,
                // which the Angular caller never sets, so every field would be mis-typed.
                int itemId = -1, skuId = -1, partyId = -1, storeId = -1, ouId = LoginDTO.WorkOUId;
                int transactionTypeId = -1, dutyTransactionTypeId = -1, priceCosting = -1;
                int salesPurchaseTag = -1, bizClassId = -1, partyPriceCategoryId = -1;
                int type = -1, barCodeId = -1;
                DateTime fromDate = DateTime.UtcNow, toDate = DateTime.UtcNow;

                var attrs = CriteriaDTO?.SectionCriteriaList?
                    .Where(s => s?.AttributesCriteriaList != null)
                    .SelectMany(s => s.AttributesCriteriaList)
                    ?? Enumerable.Empty<AttributesCriteriaDTO>();

                foreach (var attr in attrs)
                {
                    switch (attr.FieldName?.ToLower())
                    {
                        case "itemid": itemId = ExtractItemDetailIntValue(attr); break;
                        case "skuid": skuId = ExtractItemDetailIntValue(attr); break;
                        case "partyid": partyId = ExtractItemDetailIntValue(attr); break;
                        case "storeid": storeId = ExtractItemDetailIntValue(attr); break;
                        case "ouid": ouId = ExtractItemDetailIntValue(attr); break;
                        case "transactiontypeid": transactionTypeId = ExtractItemDetailIntValue(attr); break;
                        case "dutytransactiontypeid": dutyTransactionTypeId = ExtractItemDetailIntValue(attr); break;
                        case "pricecosting": priceCosting = ExtractItemDetailIntValue(attr); break;
                        case "salespurchasetag": salesPurchaseTag = ExtractItemDetailIntValue(attr); break;
                        case "bizclassid": bizClassId = ExtractItemDetailIntValue(attr); break;
                        case "salespurcategoryid": partyPriceCategoryId = ExtractItemDetailIntValue(attr); break;
                        case "type": type = ExtractItemDetailIntValue(attr); break;
                        case "barcodeid": barCodeId = ExtractItemDetailIntValue(attr); break;
                        case "periodfromdate": fromDate = DateTimeOffset.FromUnixTimeSeconds(ExtractItemDetailIntValue(attr)).UtcDateTime; break;
                        case "periodtodate": toDate = DateTimeOffset.FromUnixTimeSeconds(ExtractItemDetailIntValue(attr)).UtcDateTime; break;
                    }
                }
 

                var mainParams = new
                {
                    itemid = itemId,
                    skuid = skuId,
                    storeid = storeId,
                    ouid = ouId,
                    partyid = partyId,
                    bizclassid = bizClassId,
                    type = type,
                    fromdate = fromDate,
                    todate = toDate
                };

                ItemDetailsDTO detail = await _QueryExecutor.QuerySingleAsync<ItemDetailsDTO>(LoginDTO, ItemQB.GET_ITEM_DETAILS_FOR_TRANSACTION, mainParams);
                if (detail == null)
                    throw new Exception("Item details could not be resolved for the given Item/OU/Store context.");

                if (skuId != -1)
                    detail.SKUId = skuId;

                detail.TempPrices ??= new List<PriceListDetailDTO>();
                detail.ItemPartyChargesDTO ??= new List<ItemPartyChargesDTO>();

                // ── GST tariff code ──────────────────────────────────────────────────
                if (detail.GSTTariffId != -1)
                {
                    var gstTariff = await _QueryExecutor.QuerySingleAsync<ItemPicklistDTO>(LoginDTO, ItemQB.GET_ITEM_DETAILS_GST_TARIFF_CODE, new { gsttariffid = detail.GSTTariffId });
                    if (gstTariff != null)
                        detail.GSTTariffCode = gstTariff.ItemPicklistCode;
                }

                // ── Tax table detail ─────────────────────────────────────────────────
                if (detail.ItemTaxTableId != -1 && transactionTypeId != -1)
                {
                    var taxRows = (await _QueryExecutor.QueryAsync<TaxTableDetailDTO>(LoginDTO, ItemQB.GET_ITEM_DETAILS_TAX_TABLE,
                        new { taxtableid = detail.ItemTaxTableId, transactiontypeid = transactionTypeId, fromdate = fromDate, todate = toDate })).ToList();
                    detail.TaxTableDTO = new TaxTableDTO
                    {
                        TaxTableId = taxRows.Count != 0 ? taxRows[0].TaxTableId : 0,
                        TaxTableDetailArray = taxRows
                    };
                }

                // ── Duty table detail ────────────────────────────────────────────────
                if (detail.ItemDutyTableId != -1 && dutyTransactionTypeId != -1)
                {
                    var dutyRows = (await _QueryExecutor.QueryAsync<DutyTableDetailDTO>(LoginDTO, ItemQB.GET_ITEM_DETAILS_DUTY_TABLE,
                        new { dutytableid = detail.ItemDutyTableId, transactiontypeid = dutyTransactionTypeId })).ToList();
                    detail.DutyTableDTO = new DutyTableDTO
                    {
                        DutyTableId = dutyRows.Count != 0 ? dutyRows[0].DutyTableId : 0,
                        DutyTableDetailArray = dutyRows
                    };
                }

                // ── GST duty table (same duty query, keyed on GSTTableId) ────────────
                if (detail.GSTTableId != -1)
                {
                    var gstDutyRows = (await _QueryExecutor.QueryAsync<DutyTableDetailDTO>(LoginDTO, ItemQB.GET_ITEM_DETAILS_DUTY_TABLE,
                        new { dutytableid = detail.GSTTableId, transactiontypeid = dutyTransactionTypeId })).ToList();
                    detail.GSTDutyTableDTO = new DutyTableDTO
                    {
                        DutyTableId = gstDutyRows.Count != 0 ? gstDutyRows[0].DutyTableId : 0,
                        DutyTableDetailArray = gstDutyRows
                    };
                }

                // ── Price list (only when no item-party rate was resolved) ───────────
                if (detail.ItemRate == 0)
                {
                    if (partyPriceCategoryId == -1 && detail.ItemRateType == 1 && dutyTransactionTypeId != -1)
                        throw new Exception("Price List is not set in Party.");

                    string tagClause = salesPurchaseTag switch
                    {
                        0 => " AND C.PURCHASEPRICELISTTYPEID = B.PRICELISTTYPEID",
                        1 => " AND C.SALESPRICELISTTYPEID = B.PRICELISTTYPEID",
                        2 => " AND C.TRANSFERPRICELISTTYPEID = B.PRICELISTTYPEID",
                        _ => ""
                    };
                    string categoryClause = (detail.ItemRateType != 2 && detail.ItemPriceCategoryId != -1)
                        ? " AND C.PRICECATEGORYID = @itempricecategoryid" : "";

                    string historySql = ItemQB.GET_ITEM_DETAILS_PRICE_LIST_BASE + tagClause + categoryClause;
                    string currentSql = historySql + " AND A.EFFECTIVEFROM <= @fromdate AND A.EFFECTIVETO >= @todate";

                    var priceParams = new
                    {
                        itemid = itemId,
                        partypricecategoryid = partyPriceCategoryId,
                        itempricecategoryid = detail.ItemPriceCategoryId,
                        fromdate = fromDate,
                        todate = toDate
                    };

                    var currentPrice = await _QueryExecutor.QuerySingleAsync<PriceListDetailDTO>(LoginDTO, currentSql, priceParams);
                    if (currentPrice != null)
                        detail.PriceListDetailDTO = currentPrice;

                    var historyPrices = (await _QueryExecutor.QueryAsync<PriceListDetailDTO>(LoginDTO, historySql, priceParams)).ToList();
                    detail.TempPrices = historyPrices.OrderByDescending(p => p.TempEffectiveFrom).ToList();
                }

                // ── Price costing override ───────────────────────────────────────────
                if (priceCosting == 1)
                {
                    var productCost = await _QueryExecutor.QuerySingleAsync<ProductCostTotalCostDTO>(LoginDTO, ItemQB.GET_ITEM_DETAILS_PRODUCT_COST,
                        new { itemid = detail.ItemId, skuid = skuId });
                    if (productCost != null)
                        detail.ItemPurchaseCost = productCost.ProductCostTotalCost;
                }

                // ── Bin ───────────────────────────────────────────────────────────────
                var bin = await _QueryExecutor.QuerySingleAsync<ItemStoreBinDTO>(LoginDTO, ItemQB.GET_ITEM_DETAILS_BIN, new { itemid = itemId, storeid = storeId });
                if (bin != null)
                {
                    detail.BinId = bin.BinId;
                    detail.BinNumber = bin.BinNumber;
                }

                // ── Discount-price adjustment (slab pricing) ─────────────────────────
                if (detail.ItemRateType == 2)
                {
                    var discount = await _QueryExecutor.QuerySingleAsync<DiscountPriceDTO>(LoginDTO, ItemQB.GET_ITEM_DETAILS_DISCOUNT_PRICE,
                        new { partypricecategoryid = partyPriceCategoryId, itempricecategoryid = detail.ItemPriceCategoryId, ouid = ouId });
                    if (discount != null)
                    {
                        decimal pct = discount.DiscountPriceDiscountPercentage;
                        detail.PriceListDetailDTO.PriceListDetailRate = Math.Round(
                            (double)((decimal)detail.PriceListDetailDTO.PriceListDetailRate * (1 - pct / 100m)), 2);
                        foreach (var tempPrice in detail.TempPrices)
                        {
                            tempPrice.PriceListDetailRate = Math.Round(
                                (double)((decimal)tempPrice.PriceListDetailRate * (1 - pct / 100m)), 2);
                        }
                    }
                }

                // ── Fallback rate from MITEMOU ────────────────────────────────────────
                if (detail.ItemRate == 0)
                {
                    var itemOu = await _QueryExecutor.QuerySingleAsync<ItemOUDTO>(LoginDTO, ItemQB.GET_ITEM_DETAILS_ITEMOU_RATE, new { itemid = itemId, ouid = ouId });
                    if (itemOu == null)
                        throw new Exception("Item is not there in ITEMOU");

                    if (salesPurchaseTag == 0) detail.ItemRate = itemOu.ItemOULastPurchaseRate;
                    if (salesPurchaseTag == 1) detail.ItemRate = itemOu.ItemOUSalesRate;
                }

                // ── Average-cost override for costed BizTransactionClasses ──────────
                byte? stockValueAffecting = await _QueryExecutor.ExecuteScalarAsync<byte?>(LoginDTO, ItemQB.GET_ITEM_DETAILS_BIZCLASS_STOCKVALUEAFFECTING, new { bizclassid = bizClassId });
                bool isCostedBizClass = bizClassId == -1799999911 || bizClassId == -1399999821 || bizClassId == -1399999944 || bizClassId == -1799999904 || stockValueAffecting == 1;
                if (isCostedBizClass)
                {
                    double? avgCost = await _QueryExecutor.ExecuteScalarAsync<double?>(LoginDTO, ItemQB.GET_ITEM_DETAILS_AVERAGE_COST,
                        new { ouid = ouId, itemid = itemId, skuid = skuId, storeid = storeId });
                    if (avgCost.HasValue)
                        detail.ItemRate = avgCost.Value;
                }

                // ── Item-party charges ────────────────────────────────────────────────
                if (detail.ItemPartyDetailId != 0)
                {
                    var charges = (await _QueryExecutor.QueryAsync<ItemPartyChargesDTO>(LoginDTO, ItemQB.GET_ITEM_DETAILS_PARTY_CHARGES,
                        new { itempartydetailid = detail.ItemPartyDetailId })).ToList();
                    detail.ItemPartyChargesDTO = charges;
                }

                // ── Comments ──────────────────────────────────────────────────────────
                var comments = (await _QueryExecutor.QueryAsync<string>(LoginDTO, ItemQB.GET_ITEM_DETAILS_COMMENTS, new { itemid = itemId })).ToList();
                if (comments.Count != 0)
                    detail.ItemComment = string.Join(" <br> ", comments);

                // ── Barcode ───────────────────────────────────────────────────────────
                if (barCodeId != -1)
                {
                    var barcode = await _QueryExecutor.QuerySingleAsync<BarcodeItemLookupDTO>(LoginDTO, ItemQB.GET_ITEM_DETAILS_BARCODE, new { barcodeid = barCodeId });
                    if (barcode != null)
                    {
                        detail.ItemPackId = barcode.PackId;
                        detail.ItemPackName = barcode.PackName;
                        detail.LotId = barcode.LotId;
                        detail.LotNumber = barcode.LotNumber;
                        detail.ItemPackConversionFactor = barcode.ConversionFactor;
                        detail.Quantity = barcode.Quantity;
                        detail.PackQuantity = barcode.PackQuantity;
                    }
                }

                // ── BOM production qty ────────────────────────────────────────────────
                var bomRows = (await _QueryExecutor.QueryAsync<ItemPicklistDTO>(LoginDTO, ItemQB.GET_ITEM_DETAILS_BOM_PRODUCTION_QTY,
                    new { itemid = itemId, fromdate = fromDate, todate = toDate })).ToList();
                if (bomRows.Count != 0)
                {
                    detail.ProductionQty = bomRows[0].MaxLevel;
                    detail.BomItemPicklistDTOs = bomRows;
                }

                return JsonConvert.SerializeObject(new List<ItemDetailsDTO> { detail });
            }
            catch (Exception)
            {
                throw;
            }
        }

    }
}