using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.ListQuery;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using MMDAL.DTO.LotDetail;
using MMDAL.DTO.LotType;
using MMDAL.DTO.MMHead;
using MMDAL.Query.LotType;
using MMDAL.Query.MM;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Reflection.Emit;
using System.Text;
using System.Text.Json;
using System.Threading;
using System.Threading.Tasks;
using static GB5Shared.DTO.Framework.Enum.FrameworkEnumDTO;
using static GB5Shared.GB5Constant.Constant;

namespace MMDAL.CustomCode.MMHead
{
    public class MMHeadDAL : IMMHeadDAL
    {
        private readonly IQueryExecutor _queryExecutor;

        public MMHeadDAL(IQueryExecutor queryExecutor)
        {
            _queryExecutor = queryExecutor;
        }


        //public async Task<string> SaveTVPAsync(LoginDTO loginDTO, List<MMHeadDTO> heads, List<MMDetailDTO> details, List<MMChargesDTO> charges)
        //{
        //    try
        //    {
        //        Dictionary<string, (DataTable, string)> tvpParameters = new Dictionary<string, (DataTable, string)>
        //        {
        //            { "@MMHead", (ToDataTable<MMHeadDTO>( heads), "TVP_MMHead") },
        //            { "@MMDetail", (ToDataTable<MMDetailDTO> (details), "TVP_MMDetail") },
        //            { "@MMCharge",  (ToDataTable<MMChargesDTO>(charges), "TVP_MMCharges") }
        //        };

        //        await _queryExecutor.BulkInsertMultipleAsync(loginDTO, "POST_MM_DOCUMENT", tvpParameters);

        //        return "MMHead Saved Successfully";
        //    }
        //    catch (Exception)
        //    {
        //        throw;
        //    }
        //}

        public async Task<string> SaveTVPAsync(LoginDTO loginDTO, DataTable heads, DataTable details, DataTable charges,
            DataTable AddonList, DataTable PackDeatilList, DataTable LotDetailList, DataTable PacknumberList, DataTable ScheduleList
            ,DataTable parameterList,DataTable ItemReasonList,DataTable DetailProcessList,DataTable CostCenterAllocationList,
            DataTable AllocationList = null!, DataTable StockLedgerList = null!)
        {
            try
            {
                var tvpParameters = new Dictionary<string, (DataTable, string)>();

                // Add only if DataTable is not null and has rows
                if (heads != null && heads.Rows.Count > 0)
                    tvpParameters.Add("@MMHead", (heads, "TVP_MMHead"));

                if (details != null && details.Rows.Count > 0)
                    tvpParameters.Add("@MMDetail", (details, "TVP_MMDetail"));

                if (charges != null && charges.Rows.Count > 0)
                    tvpParameters.Add("@MMCharge", (charges, "TVP_MMCharges"));

                if (AddonList != null && AddonList.Rows.Count > 0)
                    tvpParameters.Add("@MMAddon", (AddonList, "TVP_ADDON"));

                if (PackDeatilList != null && PackDeatilList.Rows.Count > 0)
                    tvpParameters.Add("@TPACKDETAIL", (PackDeatilList, "TVB_PACKDETAIL"));

                if (LotDetailList != null && LotDetailList.Rows.Count > 0)
                    tvpParameters.Add("@TLOTDETAIL", (LotDetailList, "TVP_TLOTDETAIL"));

                if (PacknumberList != null && PacknumberList.Rows.Count > 0)
                    tvpParameters.Add("@TPACKNUMBER", (PacknumberList, "TVP_TPACKNUMBER"));

                // @TALLOCATION and @TSTOCKLEDGER are required (non-optional) TVP parameters in POST_MM_DOCUMENT.
                // Always pass them — use the provided table if populated, otherwise pass an empty schema-only table.
            //    tvpParameters.Add("@TALLOCATION",  (AllocationList  ?? BuildEmptyAllocationTable(),  "GB5_TVP_TALLOCATION"));
             //  tvpParameters.Add("@TSTOCKLEDGER", (StockLedgerList ?? BuildEmptyStockLedgerTable(), "TVP_TSTOCKLEDGER"));

                //if (ScheduleList != null && ScheduleList.Rows.Count > 0)
                //    tvpParameters.Add("@TMSCHEDULE", (ScheduleList, "TVP_TMMSCHEDULE"));

                //if (parameterList != null && parameterList.Rows.Count > 0)
                //    tvpParameters.Add("@TMMPARAMETER", (parameterList, "TVP_TMMPARAMETER"));

                //if (ItemReasonList != null && ItemReasonList.Rows.Count > 0)
                //    tvpParameters.Add("@TITEMREASON", (ItemReasonList, "TVP_TITEMREASON"));

                //if (DetailProcessList != null && DetailProcessList.Rows.Count > 0)
                //    tvpParameters.Add("@TMMDETAILPROCESS", (DetailProcessList, "TVP_TMMDETAILPROCESS"));

                //if (CostCenterAllocationList != null && CostCenterAllocationList.Rows.Count > 0)
                //    tvpParameters.Add("@TCOSTCENTERALLOCATION", (CostCenterAllocationList, "TVP_TCOSTCENTERALLOCATION"));
                //Dictionary<string, (DataTable, string)> tvpParameters = new Dictionary<string, (DataTable, string)>
                //{
                //    { "@MMHead", ( heads, "TVP_MMHead") },
                //    { "@MMDetail", (details, "TVP_MMDetail") },
                //    { "@MMCharge",  (charges, "TVP_MMCharges") },
                //    { "@MMAddon", (AddonList,"TVP_ADDON")},
                //    { "@TPACKDETAIL", (PackDeatilList,"TVB_PACKDETAIL")},
                //    { "@TLOTDETAIL" ,(LotDetailList ,"TVP_TLOTDETAIL")},
                //    { "@TPACKNUMBER" , (PacknumberList,"TVP_TPACKNUMBER") },
                //    //{ "@TMSCHEDULE", (ScheduleList, "TVP_TMMSCHEDULE") },
                //    //{ "@TMMPARAMETER", (parameterList, "TVP_TMMPARAMETER") },
                //    //{ "@TITEMREASON", (ItemReasonList, "TVP_TITEMREASON") },
                //    //{ "@TMMDETAILPROCESS", (DetailProcessList, "TVP_TMMDETAILPROCESS") },
                //    //{ "@TCOSTCENTERALLOCATION", (CostCenterAllocationList, "TVP_TCOSTCENTERALLOCATION") }
                //};

                await _queryExecutor.BulkInsertMultipleAsync(loginDTO, "POST_MM_DOCUMENT", tvpParameters);

                return $"Details Saved Successfully";
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> UpdateTVPAsync(LoginDTO loginDTO, DataTable heads, DataTable details, DataTable charges,
            DataTable AddonList, DataTable PackDeatilList, DataTable LotDetailList, DataTable PacknumberList, DataTable ScheduleList
            ,DataTable parameterList,DataTable ItemReasonList,DataTable DetailProcessList,DataTable CostCenterAllocationList,
            DataTable AllocationList = null!, DataTable StockLedgerList = null!)
        {
            try
            {
                var tvpParameters = new Dictionary<string, (DataTable, string)>();

                // Add only if DataTable is not null and has rows
                if (heads != null && heads.Rows.Count > 0)
                    tvpParameters.Add("@MMHead", (heads, "TVP_MMHead"));

                if (details != null && details.Rows.Count > 0)
                    tvpParameters.Add("@MMDetail", (details, "TVP_MMDetail"));

                if (charges != null && charges.Rows.Count > 0)
                    tvpParameters.Add("@MMCharge", (charges, "TVP_MMCharges"));

                if (AddonList != null && AddonList.Rows.Count > 0)
                    tvpParameters.Add("@MMAddon", (AddonList, "TVP_ADDON"));

                if (PackDeatilList != null && PackDeatilList.Rows.Count > 0)
                    tvpParameters.Add("@TPACKDETAIL", (PackDeatilList, "TVB_PACKDETAIL"));

                if (LotDetailList != null && LotDetailList.Rows.Count > 0)
                    tvpParameters.Add("@TLOTDETAIL", (LotDetailList, "TVP_TLOTDETAIL"));

                if (PacknumberList != null && PacknumberList.Rows.Count > 0)
                    tvpParameters.Add("@TPACKNUMBER", (PacknumberList, "TVP_TPACKNUMBER"));

                await _queryExecutor.BulkInsertMultipleAsync(loginDTO, "POST_MM_DOCUMENT_UPDATE", tvpParameters);

                return $"Details Updated Successfully";
            }
            catch (Exception)
            {
                throw;
            }
        }

        //public static DataTable ToDataTable<T>(this List<T> items)
        //{
        //    DataTable dataTable = new DataTable(typeof(T).Name);

        //    PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

        //    foreach (var prop in Props)
        //    {
        //        Type propType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
        //        dataTable.Columns.Add(prop.Name, propType);
        //    }

        //    foreach (var item in items)
        //    {
        //        var values = new object[Props.Length];
        //        for (int i = 0; i < Props.Length; i++)
        //        {
        //            values[i] = Props[i].GetValue(item, null) ?? DBNull.Value;
        //        }
        //        dataTable.Rows.Add(values);
        //    }

        //    return dataTable;
        //}


        public async Task<string> GetSelectListForDocument(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string sql = LoginDTO.DatabaseType == DBType.PostGre
                    ? MMheadQB.PG_GET_SELECTLIST_MMHEAD_DOCUMENT
                    : MMheadQB.GET_SELECTLIST_MMHEAD_DOCUMENT;

                var attributes = CriteriaDTO?.SectionCriteriaList?
                    .Where(section => section?.AttributesCriteriaList != null)
                    .SelectMany(section => section.AttributesCriteriaList)
                    .ToList() ?? new List<AttributesCriteriaDTO>();

                int? biztransactiontypeid = GetCriteriaInt(attributes, "BizTransactionTypeId")
                    ?? (CriteriaDTO.Id != 0 ? CriteriaDTO.Id : (int?)null);
                int? biztransactionclassid = GetCriteriaInt(attributes, "BizTransactionClassId");
                int? periodid = GetCriteriaInt(attributes, "PeriodId");
                int? status = GetCriteriaInt(attributes, "Status");
                int? voucherid = GetCriteriaInt(attributes, "VoucherId");
                int? partyid = GetCriteriaInt(attributes, "PartyId", "Party.Id");

                // FIX: Number / AllocationName / MMHeadPartyName arrive as three INDEPENDENT
                // criteria fields (confirmed from actual payload), not one shared search term.
                // Previously these were coalesced into a single @searchtext applied to all three
                // columns via OR — that silently broadened/narrowed results incorrectly.
                // Each is now its own optional filter. An empty string means "not filtering on this".
                string number = GetCriteriaString(attributes, "Number");
                string allocationname = GetCriteriaString(attributes, "AllocationName");
                string partyname = GetCriteriaString(attributes, "MMHeadPartyName");

                // Optional free-text search box (e.g. a single global search bar), kept separate
                // from the three specific filters above.
                string searchtext = CriteriaDTO?.SearchKeyword;

                var parameters = new
                {
                    biztransactiontypeid,
                    biztransactionclassid,
                    periodid,
                    status,
                    voucherid,
                    partyid,
                    ouid = LoginDTO.WorkOUId,
                    number = string.IsNullOrWhiteSpace(number) ? null : number,
                    allocationname = string.IsNullOrWhiteSpace(allocationname) ? null : allocationname,
                    partyname = string.IsNullOrWhiteSpace(partyname) ? null : partyname,
                    searchtext = string.IsNullOrWhiteSpace(searchtext) ? null : searchtext,
                    // FirstNumber is 1-based (matches the GenericDal/OFFSET (@FirstNumber - 1) convention
                    // used elsewhere), so convert to a 0-based skip for the RowNum > @skip comparison.
                    // -1 is the "no paging" sentinel and must pass through unchanged.
                    skip = FirstNumber == -1 ? -1 : FirstNumber - 1,
                    take = MaxResult
                };

                var result = await _queryExecutor.QueryAsync<MMHeadPicklistDTO>(LoginDTO, sql, parameters);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // GB4 parity: MMHead.svc/Get/MM/Load/Picklist/In/Voucher. Only MM documents not yet
        // linked to a posted voucher (H.VOUCHERID IS NULL) are eligible — GB4 enforced the same
        // condition via LEFT JOIN TVOUCHER ... WHERE vc.VOUCHERID IS NULL; GB5's TMMHEAD carries
        // VOUCHERID directly, so no join is needed. Only Number/BizTransactionTypeId are real
        // filters in the legacy query (an "Id" filter existed in GB4 but pointed at a non-existent
        // column and never worked — intentionally not carried over).
        public async Task<string> GetSelectListMMHeadForVoucher(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string sql = LoginDTO.DatabaseType == DBType.PostGre
                    ? MMheadQB.PG_GET_SELECTLIST_MMHEAD_FOR_VOUCHER
                    : MMheadQB.GET_SELECTLIST_MMHEAD_FOR_VOUCHER;

                var attributes = CriteriaDTO?.SectionCriteriaList?
                    .Where(section => section?.AttributesCriteriaList != null)
                    .SelectMany(section => section.AttributesCriteriaList)
                    .ToList() ?? new List<AttributesCriteriaDTO>();

                int? biztransactiontypeid = GetCriteriaInt(attributes, "BizTransactionTypeId");
                string number = GetCriteriaString(attributes, "Number");

                var parameters = new
                {
                    ouid = LoginDTO.WorkOUId,
                    biztransactiontypeid,
                    number = string.IsNullOrWhiteSpace(number) ? null : number,
                    skip = FirstNumber == -1 ? -1 : FirstNumber - 1,
                    take = MaxResult
                };

                var result = await _queryExecutor.QueryAsync<MMHeadPicklistDTO>(LoginDTO, sql, parameters);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Only returns a value when the caller actually supplied one for FieldName —
        // any field left out or sent as null/blank is treated as "no filter" rather than 0.
        // Accepts multiple candidate FieldName spellings since different front-end callers use
        // either the flat "XId" form or the nested-object "X.Id" form for the same filter.
        private static int? GetCriteriaInt(List<AttributesCriteriaDTO> attributes, params string[] fieldNames)
        {
            var value = NormalizeCriteriaValue(attributes
                .FirstOrDefault(attr => fieldNames.Any(fieldName => string.Equals(attr.FieldName, fieldName, StringComparison.OrdinalIgnoreCase)))
                ?.FieldValue);

            if (value == null) return null;
            if (value is string s && string.IsNullOrWhiteSpace(s)) return null;

            return Convert.ToInt32(value);
        }

        private static string GetCriteriaString(List<AttributesCriteriaDTO> attributes, string fieldName)
        {
            var value = NormalizeCriteriaValue(attributes
                .FirstOrDefault(attr => string.Equals(attr.FieldName, fieldName, StringComparison.OrdinalIgnoreCase))
                ?.FieldValue)?.ToString();

            return string.IsNullOrWhiteSpace(value) ? null : value;
        }

        // CriteriaDTO.FieldValue deserializes as a boxed System.Text.Json.JsonElement — unwrap it
        // to a native CLR value before using it, otherwise Convert.ToInt32/Dapper binding fails.
        private static object NormalizeCriteriaValue(object value)
        {
            if (value is not System.Text.Json.JsonElement je)
                return value;

            return je.ValueKind switch
            {
                System.Text.Json.JsonValueKind.String => je.GetString(),
                System.Text.Json.JsonValueKind.Number => je.TryGetInt32(out var i) ? i : je.GetDouble(),
                System.Text.Json.JsonValueKind.True => true,
                System.Text.Json.JsonValueKind.False => false,
                System.Text.Json.JsonValueKind.Null => null,
                _ => je.ToString()
            };
        }

        public async Task<string> GetMMHead(int DocumentId, LoginDTO LoginDTO)
        {
            try
            {
                var heads = await GetMMHeadListInternal(new[] { DocumentId }, LoginDTO).ConfigureAwait(false);
                return JsonConvert.SerializeObject(heads);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve MMHead data: {ex.Message}", ex);
            }
        }

        // GB4 parity: GetMMHeadAllocation accepts a comma-separated DocumentId list —
        // same GET_MMHEAD projection, scoped to N documents via an IN clause instead of one.
        public async Task<string> GetMMHeadList(int[] DocumentIds, LoginDTO LoginDTO)
        {
            try
            {
                var heads = await GetMMHeadListInternal(DocumentIds, LoginDTO).ConfigureAwait(false);
                return JsonConvert.SerializeObject(heads);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve MMHead data: {ex.Message}", ex);
            }
        }

        private async Task<List<MMHeadDTO>> GetMMHeadListInternal(int[] documentIds, LoginDTO LoginDTO)
        {
            var sql = MMheadQB.GET_MMHEAD;
            var parameters = new { documentid = documentIds };

            var MMheadDict = new Dictionary<int, MMHeadDTO>();

            await _queryExecutor.QueryMultiMapAsync<MMHeadDTO, MMDetailDTO, MMHeadDTO>(
                LoginDTO,
                sql,
                (parent, child) =>
                {
                    if (!MMheadDict.TryGetValue(parent.MMHeadId, out var existingParent))
                    {
                        existingParent = parent;
                        existingParent.MMDetailArray = new List<MMDetailDTO>();
                        MMheadDict[parent.MMHeadId] = existingParent;
                    }

                    // GET_MMHEAD's joins (e.g. the LFA allocation lookup) can fan out to more than
                    // one SQL result row for the same TMMDETAIL row — skip a detail already added
                    // for this MMDetailId so the response doesn't report duplicate detail lines.
                    if (child != null && child.DocumentId != 0
                        && !existingParent.MMDetailArray.Any(d => d.MMDetailId == child.MMDetailId))
                    {
                        existingParent.MMDetailArray.Add(child);
                    }

                    return existingParent;
                },
                parameters,
                splitOn: "DocumentId"
            );

            var heads = MMheadDict.Values.ToList();
            if (heads.Count == 0) return heads;

            var configParam = new { ConfigType = 0, AsOfDate = DateTime.UtcNow.Date, TenantId = LoginDTO.ClientId };
            var currentCfgTask = _queryExecutor.ExecuteScalarAsync<int?>(
                LoginDTO, MMDocumentQB.GET_ACTIVE_CONFIGVERSION_ID, configParam, cancellationToken: CancellationToken.None);

            // GB4 parity: MMHeadDTO.MMChargesArray must mirror the actual saved TMMCHARGES rows
            // (GET_MMHEAD_CHARGES). The config-driven "what-if" projection (GET_CHARGES_FOR_DOCUMENT /
            // LoadMMChargesDTO) is intentionally not fetched here — MMHeadDTO.Charges was removed
            // from the response, so querying it per document would just be discarded work.
            var mmChargesTask = _queryExecutor.QueryAsync<MMChargesDTO>(
                LoginDTO, MMheadQB.GET_MMHEAD_CHARGES, new { documentid = documentIds },
                cancellationToken: CancellationToken.None);

            await Task.WhenAll(currentCfgTask, mmChargesTask).ConfigureAwait(false);

            var currentConfigVersionId = await currentCfgTask;
            var mmChargesByDocument = (await mmChargesTask)
                .GroupBy(c => c.DocumentId)
                .ToDictionary(g => g.Key, g => g.ToList());

            for (var i = 0; i < heads.Count; i++)
            {
                heads[i].CurrentConfigVersionId = currentConfigVersionId;
                heads[i].ChargeConfigChanged    = currentConfigVersionId.HasValue
                                                && heads[i].ConfigVersionId > 0
                                                && currentConfigVersionId.Value != heads[i].ConfigVersionId;

                if (!mmChargesByDocument.TryGetValue(heads[i].MMHeadId, out var mmCharges))
                {
                    heads[i].MMChargesArray = new List<MMChargesDTO>();
                    continue;
                }

                // TMMCHARGES.DOCUMENTDETAILID = -1 marks a header-level charge row (goes into
                // MMHeadDTO.MMChargesArray); any other value is a detail-level charge that must
                // attach to that specific MMDetailArray row's MMChargesArray instead.
                // Built manually (not ToDictionary) because GET_MMHEAD's join fan-out can produce
                // more than one MMDetailArray row for the same MMDetailId — ToDictionary would
                // throw "An item with the same key has already been added" in that case.
                var detailById = new Dictionary<int, MMDetailDTO>();
                foreach (var d in heads[i].MMDetailArray)
                    detailById[d.MMDetailId] = d;
                var headerCharges = new List<MMChargesDTO>();

                foreach (var charge in mmCharges)
                {
                    if (charge.DocumentDetailId != -1 && detailById.TryGetValue(charge.DocumentDetailId, out var detail))
                        detail.MMChargesArray.Add(charge);
                    else
                        headerCharges.Add(charge);
                }

                heads[i].MMChargesArray = headerCharges;
            }

            return heads;
        }

        // ── Load from MM Allocation ───────────────────────────────────────────────

        public async Task<LoadMMHeadDTO?> GetLoadMMHeadFromAllocation(
            int[] documentIds, int[]? detailIds, int bizTransactionClassId, int nature,
            int storeId, int? partyId, LoginDTO login, CancellationToken ct)
        {
            var headerParam = new { DocumentIds = documentIds, TenantId = login.ClientId };
            var headerTask  = _queryExecutor.QueryAsync<LoadMMHeadDTO>(
                login, MMheadQB.GET_LOAD_HEADER_FROM_ALLOCATION, headerParam, cancellationToken: ct);

            string detailSql   = detailIds is { Length: > 0 }
                ? MMheadQB.GET_LOAD_DETAIL_FROM_ALLOCATION_ITEMWISE
                : MMheadQB.GET_LOAD_DETAIL_FROM_ALLOCATION;
            var detailParam    = detailIds is { Length: > 0 }
                ? (object)new { DetailIds  = detailIds,  TenantId = login.ClientId }
                : (object)new { DocumentIds = documentIds, TenantId = login.ClientId };
            var detailTask     = _queryExecutor.QueryAsync<LoadMMDetailDTO>(
                login, detailSql, detailParam, cancellationToken: ct);

            await Task.WhenAll(headerTask, detailTask).ConfigureAwait(false);

            var header  = (await headerTask).FirstOrDefault();
            var details = (await detailTask).ToList();
            if (header is null || details.Count == 0) return null;

            return await AssembleLoadResult(header, details, documentIds[0], login, ct);
        }

        // ── Load from INDENT ──────────────────────────────────────────────────────

        public async Task<LoadMMHeadDTO?> GetLoadMMHeadFromIndent(
            int[] indentIds, int[]? detailIds, int storeId, LoginDTO login, CancellationToken ct)
        {
            var param = new { IndentIds = indentIds, StoreId = storeId, TenantId = login.ClientId };

            var headerTask = _queryExecutor.QueryAsync<LoadMMHeadDTO>(
                login, MMheadQB.GET_LOAD_HEADER_FROM_INDENT, param, cancellationToken: ct);
            var detailTask = _queryExecutor.QueryAsync<LoadMMDetailDTO>(
                login, MMheadQB.GET_LOAD_DETAIL_FROM_INDENT, param, cancellationToken: ct);

            await Task.WhenAll(headerTask, detailTask).ConfigureAwait(false);

            var header  = (await headerTask).FirstOrDefault();
            var details = (await detailTask).ToList();
            if (header is null || details.Count == 0) return null;

            if (detailIds is { Length: > 0 })
                details = details.Where(d => detailIds.Contains(d.SourceDetailId)).ToList();

            if (details.Count == 0) return null;

            return await AssembleLoadResult(header, details, header.SourceDocumentId, login, ct);
        }

        // ── Load from Gate Entry ──────────────────────────────────────────────────

        public async Task<MMHeadDTO?> GetLoadMMHeadFromGateEntry(
            int[] gateEntryIds, int storeId, LoginDTO login, CancellationToken ct)
        {
            var param = new { GateEntryIds = gateEntryIds, StoreId = storeId, TenantId = login.ClientId };

            var headerTask = _queryExecutor.QueryAsync<MMHeadDTO>(
                login, MMDAL.Query.GateEntry.GateEntryQB.GET_LOAD_HEADER_FROM_GATEENTRY, param, cancellationToken: ct);
            var detailTask = _queryExecutor.QueryAsync<MMDetailDTO>(
                login, MMDAL.Query.GateEntry.GateEntryQB.GET_LOAD_DETAIL_FROM_GATEENTRY, param, cancellationToken: ct);

            await Task.WhenAll(headerTask, detailTask).ConfigureAwait(false);

            var header  = (await headerTask).FirstOrDefault();
            var details = (await detailTask).ToList();
            if (header is null || details.Count == 0) return null;

            // GateEntry never has charges/pack/batch on source — return header + details only
            header.MMDetailArray = details;
            return header;
            
        }

        public async Task MarkGateEntryLoadedAsync(int[] gateEntryIds, LoginDTO login, CancellationToken ct)
        {
            var param = new { GateEntryIds = gateEntryIds, TenantId = login.ClientId, ModifiedById = login.UserId };
            await _queryExecutor.ExecuteAsync(
                login, MMDAL.Query.GateEntry.GateEntryQB.MARK_GATEENTRY_LOADED, param, cancellationToken: ct)
                .ConfigureAwait(false);
        }

        public async Task<MMHeadDTO?> GetMMHeadForFinalizeAsync(int documentId, LoginDTO login, CancellationToken ct)
        {
            var param = new { DocumentId = documentId, TenantId = login.ClientId };

            var heads = await _queryExecutor.QueryAsync<MMHeadDTO>(
                login, MMHeadFinalizeQB.GET_HEAD_FOR_FINALIZE, param, cancellationToken: ct)
                .ConfigureAwait(false);
            var head = heads.FirstOrDefault();

            if (head is null) return null;

            var details = (await _queryExecutor.QueryAsync<MMDetailDTO>(
                login, MMHeadFinalizeQB.GET_DETAILS_FOR_FINALIZE, param, cancellationToken: ct)
                .ConfigureAwait(false)).ToList();

            head.MMDetailArray = details;
            return head;
        }

        public async Task<int> UpdateMMHeadStatusConditionalAsync(
            int documentId, short expectedStatus, short newStatus, LoginDTO login, CancellationToken ct)
        {
            var param = new { DocumentId = documentId, ExpectedStatus = expectedStatus, NewStatus = newStatus, TenantId = login.ClientId };
            return await _queryExecutor.ExecuteAsync(
                login, MMHeadFinalizeQB.UPDATE_HEAD_STATUS_CONDITIONAL, param, cancellationToken: ct)
                .ConfigureAwait(false);
        }

        // ── Delete (ported from GB4 MMHeadBLL.Delete) ─────────────────────────────
        private const int DeleteMmHeadEntityId = -1899997952; // TMMHEAD, matches AllocationPostingBLL/StockPostingBLL

        public async Task<MMHeadDTO?> GetMMHeadForDeleteAsync(int documentId, LoginDTO login, CancellationToken ct)
        {
            var param = new { DocumentId = documentId, TenantId = login.ClientId };
            var heads = await _queryExecutor.QueryAsync<MMHeadDTO>(
                login, DeleteMMHeadQB.GET_HEAD_FOR_DELETE, param, cancellationToken: ct)
                .ConfigureAwait(false);
            return heads.FirstOrDefault();
        }

        public async Task<MMHeadDeleteBlockersDTO> GetMMHeadDeleteBlockersAsync(int documentId, LoginDTO login, CancellationToken ct)
        {
            var param = new { DocumentId = documentId, MmHeadEntityId = DeleteMmHeadEntityId };
            var rows = await _queryExecutor.QueryAsync<MMHeadDeleteBlockersDTO>(
                login, DeleteMMHeadQB.GET_DELETE_BLOCKERS, param, cancellationToken: ct)
                .ConfigureAwait(false);
            return rows.First();
        }

        public async Task<int> DeleteMMHeadCascadeAsync(
            int documentId, int referenceDocumentId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        {
            var param = new
            {
                DocumentId = documentId,
                ReferenceDocumentId = referenceDocumentId,
                MmHeadEntityId = DeleteMmHeadEntityId,
                TenantId = login.ClientId
            };
            return await _queryExecutor.ExecuteAsync(
                login, DeleteMMHeadQB.DELETE_MMHEAD_CASCADE, param, tx, cancellationToken: ct)
                .ConfigureAwait(false);
        }

        // ── Voucher forming from MMHead (ProcedureBased) ──────────────────────────

        public async Task<MMHeadDTO?> GetMMHeadHeaderForVoucherForming(int documentId, LoginDTO login, CancellationToken ct)
        {
            var rows = await _queryExecutor.QueryAsync<MMHeadDTO>(
                login, MMheadQB.GET_MMHEAD_HEADER_FOR_VOUCHER_FORMING, new { documentid = documentId }, cancellationToken: ct)
                .ConfigureAwait(false);
            return rows.FirstOrDefault();
        }

        public async Task<List<MMToAccountPostDTO>> GetMM2AccountPostData(int documentId, LoginDTO login, CancellationToken ct)
        {
            var rows = await _queryExecutor.QueryAsync<MMToAccountPostDTO>(
                login, MMheadQB.GET_MM2_ACCOUNT_POST_DATA, new { documentid = documentId }, cancellationToken: ct)
                .ConfigureAwait(false);
            return rows.ToList();
        }

        // ── Account posting preview from FE (unsaved document) ────────────────────
        // TVPs are bound straight from the caller's DataTables — see GET_MM2_ACCOUNT_POST_PREVIEW_DATA
        // for why this replaces GB4's #Head/#Detail/#Charges staging-table dance.
        public async Task<List<MMToAccountPostDTO>> GetMM2AccountPostPreviewData(
            DataTable head, DataTable detail, DataTable charges, byte isSummary, LoginDTO login, CancellationToken ct)
        {
            var parameters = new DynamicParameters();
            parameters.Add("Head", head.AsTableValuedParameter("TVP_MMHEAD_FE"));
            parameters.Add("Detail", detail.AsTableValuedParameter("TVP_MMDETAIL_FE"));
            parameters.Add("Charges", charges.AsTableValuedParameter("TVP_MMCHARGES_FE"));
            parameters.Add("issummary", isSummary);

            var rows = await _queryExecutor.QueryAsync<MMToAccountPostDTO>(
                login, MMheadQB.GET_MM2_ACCOUNT_POST_PREVIEW_DATA, parameters, cancellationToken: ct)
                .ConfigureAwait(false);
            return rows.ToList();
        }

        public async Task<AccountsDAL.DTO.Account.AccountDTO?> GetAccountForPosting(int accountId, LoginDTO login, CancellationToken ct)
        {
            var rows = await _queryExecutor.QueryAsync<AccountsDAL.DTO.Account.AccountDTO>(
                login, AccountsDAL.Query.Account.AccountQB.GET_ACCOUNT_ID, new { accountid = accountId }, cancellationToken: ct)
                .ConfigureAwait(false);
            return rows.FirstOrDefault();
        }

        public async Task<AccountsDAL.DTO.CostCenter.CostCenterDTO?> GetCostCenterForPosting(int costCenterId, LoginDTO login, CancellationToken ct)
        {
            var rows = await _queryExecutor.QueryAsync<AccountsDAL.DTO.CostCenter.CostCenterDTO>(
                login, AccountsDAL.Query.CostCenter.CostCenterQB.GET_COSTCENTER, new { costcenterid = costCenterId }, cancellationToken: ct)
                .ConfigureAwait(false);
            return rows.FirstOrDefault();
        }

        public async Task<AdminDAL.DTO.Currency.CurrencyDTO?> GetCurrencyForPosting(int currencyId, LoginDTO login, CancellationToken ct)
        {
            var rows = await _queryExecutor.QueryAsync<AdminDAL.DTO.Currency.CurrencyDTO>(
                login, AdminDAL.Query.Currency.CurrencyQB.GET_CURRENCY, new { currencyid = currencyId }, cancellationToken: ct)
                .ConfigureAwait(false);
            return rows.FirstOrDefault();
        }

        public async Task<bool> CostCategoryConditionExistsForAccountGroup(int accountGroupId, LoginDTO login, CancellationToken ct)
        {
            var result = await _queryExecutor.ExecuteScalarAsync<int?>(
                login, AccountsDAL.Query.Payment.PaymentQB.CHECK_COST_CATEGORY_CONDITION_EXISTS_FOR_ACCOUNT_GROUP,
                new { AccountGroupId = accountGroupId, TenantId = login.ClientId }, cancellationToken: ct)
                .ConfigureAwait(false);
            return result.HasValue;
        }

        public async Task<int?> GetDefaultCashInstrumentId(LoginDTO login, CancellationToken ct)
        {
            var rows = await _queryExecutor.QueryAsync<int?>(
                login, MMheadQB.GET_DEFAULT_CASH_INSTRUMENT_ID, new { TenantId = login.ClientId }, cancellationToken: ct)
                .ConfigureAwait(false);
            return rows.FirstOrDefault();
        }

        // ── Shared assembly helper ────────────────────────────────────────────────

        private async Task<LoadMMHeadDTO> AssembleLoadResult(
            LoadMMHeadDTO header, List<LoadMMDetailDTO> details,
            int sourceDocumentId, LoginDTO login, CancellationToken ct)
        {
            var detailIds = details.Select(d => d.SourceDetailId).ToArray();
            var itemIds   = details.Select(d => d.ItemId).Distinct().ToArray();

            // Resolve config versions in parallel with secondary detail queries
            var configParam    = new { ConfigType = 0, AsOfDate = DateTime.UtcNow.Date, TenantId = login.ClientId };
            var srcCfgParam    = new { DocumentId = sourceDocumentId, TenantId = login.ClientId };

            var currentCfgTask = _queryExecutor.ExecuteScalarAsync<int?>(
                login, MMDocumentQB.GET_ACTIVE_CONFIGVERSION_ID, configParam, cancellationToken: ct);
            var sourceCfgTask  = _queryExecutor.ExecuteScalarAsync<int?>(
                login, MMheadQB.GET_HEAD_CONFIGVERSIONID, srcCfgParam, cancellationToken: ct);
            var chargesTask    = _queryExecutor.QueryAsync<LoadMMChargesDTO>(
                login, MMDocumentQB.GET_LOAD_CHARGES_FROM_DETAIL_IDS,
                new { DetailIds = detailIds, TenantId = login.ClientId }, cancellationToken: ct);
            var packTask       = _queryExecutor.QueryAsync<LoadMMPackDetailDTO>(
                login, MMDocumentQB.GET_LOAD_PACKDETAIL_FROM_DETAIL_IDS,
                new { DetailIds = detailIds, TenantId = login.ClientId }, cancellationToken: ct);
            var batchTask      = _queryExecutor.QueryAsync<LoadMMBatchDTO>(
                login, MMDocumentQB.GET_LOAD_BATCH_FROM_DETAIL_IDS,
                new { DetailIds = detailIds, TenantId = login.ClientId }, cancellationToken: ct);
            var attachTask     = _queryExecutor.QueryAsync<LoadMMAttachmentDTO>(
                login, MMDocumentQB.GET_MM_DOCUMENT_ITEM_IMAGES,
                new { ItemIds = itemIds }, cancellationToken: ct);

            await Task.WhenAll(
                currentCfgTask, sourceCfgTask,
                chargesTask, packTask, batchTask, attachTask)
                .ConfigureAwait(false);

            var currentConfigVersionId = await currentCfgTask;
            var sourceConfigVersionId  = await sourceCfgTask;

            header.CurrentConfigVersionId = currentConfigVersionId;
            header.SourceConfigVersionId  = sourceConfigVersionId;
            header.ChargeConfigChanged    = currentConfigVersionId.HasValue
                                         && sourceConfigVersionId.HasValue
                                         && currentConfigVersionId != sourceConfigVersionId;
            header.Details     = details;
            header.Charges     = (await chargesTask).ToList();
            header.PackDetails = (await packTask).ToList();
            header.Batches     = (await batchTask).ToList();
            header.Attachments = (await attachTask).ToList();
            return header;
        }

        private static DataTable BuildEmptyAllocationTable()
        {
            var dt = new DataTable();
            dt.Columns.Add("ALLOCATIONID",          typeof(int));
            dt.Columns.Add("BIZTRANSACTIONTYPEID",  typeof(int));
            dt.Columns.Add("OBJECTHEADERTYPEID",    typeof(int));
            dt.Columns.Add("OBJECTHEADERID",        typeof(int));
            dt.Columns.Add("OBJECTTYPEID",          typeof(int));
            dt.Columns.Add("OBJECTID",              typeof(int));
            dt.Columns.Add("ALLOCATIONTYPE",        typeof(byte));
            dt.Columns.Add("ALLOCATIONNATURE",      typeof(byte));
            dt.Columns.Add("QUANTITY",              typeof(decimal));
            dt.Columns.Add("PROCESSID",             typeof(int));
            dt.Columns.Add("ALLOTEDALLOCATIONID",   typeof(int));
            dt.Columns.Add("REJECTEDQUANTITY",      typeof(decimal));
            dt.Columns.Add("REWORKQUANTITY",        typeof(decimal));
            dt.Columns.Add("OTHERQUANTITY",         typeof(decimal));
            dt.Columns.Add("EXCESSQUANTITY",        typeof(decimal));
            dt.Columns.Add("MODIFIEDON",            typeof(DateTime));
            dt.Columns.Add("FREEQUANTITY",          typeof(decimal));
            return dt;
        }

        private static DataTable BuildEmptyStockLedgerTable()
        {
            var dt = new DataTable();
            dt.Columns.Add("STOCKLEDGERNUMBER",         typeof(string));
            dt.Columns.Add("STOCKLEDGERDATE",           typeof(DateTime));
            dt.Columns.Add("REFERENCENUMBER",           typeof(string));
            dt.Columns.Add("REFERENCEDATE",             typeof(DateTime));
            dt.Columns.Add("OUID",                      typeof(int));
            dt.Columns.Add("BIZTRANSACTIONTYPEID",      typeof(int));
            dt.Columns.Add("OBJECTTYPEID",              typeof(int));
            dt.Columns.Add("OBJECTID",                  typeof(int));
            dt.Columns.Add("LOCATIONTYPE",              typeof(byte));
            dt.Columns.Add("ALLOCATIONID",              typeof(int));
            dt.Columns.Add("STOREID",                   typeof(int));
            dt.Columns.Add("WORKCENTERID",              typeof(int));
            dt.Columns.Add("PARTYBRANCHID",             typeof(int));
            dt.Columns.Add("ITEMID",                    typeof(int));
            dt.Columns.Add("SKUID",                     typeof(int));
            dt.Columns.Add("STOCKPOSTTYPE",             typeof(byte));
            dt.Columns.Add("MATERIALOWNERSHIPTYPE",     typeof(byte));
            dt.Columns.Add("TRANSACTIONQUANTITY",       typeof(decimal));
            dt.Columns.Add("UOMID",                     typeof(int));
            dt.Columns.Add("CONVERSIONFACTOR",          typeof(decimal));
            dt.Columns.Add("QUANTITY",                  typeof(decimal));
            dt.Columns.Add("PACKQUANTITY",              typeof(decimal));
            dt.Columns.Add("PACKID",                    typeof(int));
            dt.Columns.Add("WEIGHT",                    typeof(decimal));
            dt.Columns.Add("CURRENCYID",                typeof(int));
            dt.Columns.Add("RATECONVERSIONFACTOR",      typeof(decimal));
            dt.Columns.Add("RATE",                      typeof(decimal));
            dt.Columns.Add("RATEPERUOMID",              typeof(int));
            dt.Columns.Add("COST",                      typeof(decimal));
            dt.Columns.Add("POSTEDCOST",                typeof(decimal));
            dt.Columns.Add("POSTEDVALUE",               typeof(decimal));
            dt.Columns.Add("GOODQUANTITY",              typeof(decimal));
            dt.Columns.Add("REJECTEDQUANTITY",          typeof(decimal));
            dt.Columns.Add("REWORKQUANTITY",            typeof(decimal));
            dt.Columns.Add("OTHERQUANTITY",             typeof(decimal));
            dt.Columns.Add("TRANSACTIONACTUALQUANTITY", typeof(decimal));
            dt.Columns.Add("ACTUALQUANTITY",            typeof(decimal));
            dt.Columns.Add("TRANSACTIONUOMID",          typeof(int));
            dt.Columns.Add("OBJECTDETAILTYPEID",        typeof(int));
            dt.Columns.Add("OBJECTDETAILID",            typeof(int));
            dt.Columns.Add("DELIVERYTYPE",              typeof(byte));
            dt.Columns.Add("MATERIALCOST",              typeof(decimal));
            dt.Columns.Add("PROCESSCOST",               typeof(decimal));
            dt.Columns.Add("CHARGESCOST",               typeof(decimal));
            dt.Columns.Add("REVENUE",                   typeof(decimal));
            dt.Columns.Add("INDENTDETAILID",            typeof(int));
            dt.Columns.Add("GCCURRENCYCONVERSION",      typeof(decimal));
            dt.Columns.Add("POSTEDCOSTGC",              typeof(decimal));
            dt.Columns.Add("POSTEDVALUEGC",             typeof(decimal));
            return dt;
        }

        // ── Row-level incremental save (SessionGuid path) ─────────────────────────
        // SQL is generated from the caller's verified column-map dictionaries (MMHeadBLL's
        // existing HeadMapping/DetailMapping/ChargeMapping) rather than hand-typed here —
        // see MMHeadIncrementalQB.cs header comment for why.

        public Task<DbTransaction> BeginTransactionAsync(LoginDTO login)
            => _queryExecutor.BeginTransactionAsync(login);

        public Task CommitAsync(DbTransaction tx)
            => _queryExecutor.CommitAsync(tx);

        public Task RollbackAsync(DbTransaction tx)
            => _queryExecutor.RollbackAsync(tx);

        private static string BuildInsertSql(string table, Dictionary<string, string> map, string existsGuardSql)
        {
            string columns = string.Join(", ", map.Values);
            string paramNames = string.Join(", ", map.Keys.Select(k => "@" + k));
            return $"INSERT INTO DBO.{table} ({columns})\nSELECT {paramNames}\nWHERE {existsGuardSql}";
        }

        // scopeDtoKey/scopeColumn default to the DocumentId/DOCUMENTID FK most sub-tables use.
        // Lot's TLOTDETAIL has no direct DocumentId column — it links via the polymorphic
        // OBJECTHEADERID column instead — so it passes LotDetailObjectHeaderId/OBJECTHEADERID.
        // Pass scopeDtoKey: null for tables that have no parent scope column to filter on
        // (TMMHEAD itself — MMHeadId/DOCUMENTID already uniquely identifies the row, and
        // MMHeadDTO has no separate DocumentId property for Dapper to bind @DocumentId to).
        private static string BuildUpdateSql(string table, Dictionary<string, string> map, string pkDtoKey, string pkColumn,
            string? scopeDtoKey = "DocumentId", string? scopeColumn = "DOCUMENTID")
        {
            string assignments = string.Join(", ",
                map.Where(kv => kv.Key != pkDtoKey).Select(kv => $"{kv.Value} = @{kv.Key}"));
            string sql = $"UPDATE DBO.{table} SET {assignments}\nWHERE {pkColumn} = @{pkDtoKey}";
            if (scopeDtoKey != null)
                sql += $" AND {scopeColumn} = @{scopeDtoKey}";
            return sql;
        }

        public async Task<int> SaveHeaderScalarAsync(
            LoginDTO login, MMHeadDTO head, bool isNewDocument,
            Dictionary<string, string> headMapping, DbTransaction tx, CancellationToken ct)
        {
            if (isNewDocument)
            {
                // TENANTID is deliberately NOT part of headMapping (MMHeadDTO carries no TenantId
                // property for it to bind to) — the legacy TVP path's stored proc (POST_MM_DOCUMENT)
                // must stamp it itself. This raw INSERT has no such helper, so without adding it
                // explicitly here every new document saved via the incremental/draft path commits
                // with a wrong/default TENANTID and becomes invisible to every later tenant-scoped
                // read of it (GetMMHeadForFinalizeAsync included) — confirmed live: a fresh INSERT
                // with rows_affected=1 followed immediately by GET_HEAD_FOR_FINALIZE for the same
                // DOCUMENTID + the actual TenantId returning 0 rows.
                string columns = string.Join(", ", headMapping.Values) + ", TENANTID";
                string paramNames = string.Join(", ", headMapping.Keys.Select(k => "@" + k)) + ", @TenantId";
                string insertSql = $"INSERT INTO DBO.TMMHEAD ({columns}) VALUES ({paramNames})";

                var parameters = new DynamicParameters(head);
                parameters.Add("TenantId", login.ClientId);
                return await _queryExecutor.ExecuteAsync(login, insertSql, parameters, tx, ct).ConfigureAwait(false);
            }

            string updateSql = BuildUpdateSql("TMMHEAD", headMapping, "MMHeadId", "DOCUMENTID", scopeDtoKey: null, scopeColumn: null);
            return await _queryExecutor.ExecuteAsync(login, updateSql, head, tx, ct).ConfigureAwait(false);
        }

        public Task<int> RecomputeAndUpdateHeaderTotalsAsync(
            LoginDTO login, int documentId, DbTransaction tx, CancellationToken ct)
            => _queryExecutor.ExecuteAsync(
                login, MMHeadIncrementalQB.RECOMPUTE_HEADER_TOTALS,
                new { DocumentId = documentId }, tx, ct);

        // The exists-guard INSERTs below (BuildInsertSql) resolve "was this exact row already
        // saved?" by ROWGUID alone — required, since ROWGUID carries a table-wide unique index
        // (UX_TMMDETAIL_ROWGUID etc.), not one scoped per document. A legitimate retry of the
        // SAME save resends the SAME DocumentId + ROWGUID, so the guard's 0-rows-affected no-op
        // is the correct, silent outcome there. But if a caller resends a ROWGUID that was already
        // committed under a DIFFERENT document (a fresh MMHeadId each attempt, e.g. a client that
        // reuses/caches row identifiers instead of generating new ones per draft), the guard still
        // no-ops — and unlike a genuine retry, that silently leaves the new document's detail row
        // simply missing, with no error, no rollback: an orphaned header row with a hole where this
        // line should be. Failing loudly here instead rolls back the whole save (via the caller's
        // try/catch around BeginTransactionAsync) rather than committing a half-saved document.
        private async Task<int> ExecuteGuardedInsertAsync(
            LoginDTO login, string table, string insertSql, object row, Guid? rowGuid, int documentId, DbTransaction tx, CancellationToken ct)
        {
            var rowsAffected = await _queryExecutor.ExecuteAsync(login, insertSql, row, tx, ct).ConfigureAwait(false);
            if (rowsAffected == 0)
                throw new InvalidOperationException(
                    $"{table} insert for ROWGUID {rowGuid} affected 0 rows while saving DocumentId {documentId} — " +
                    $"a row with this ROWGUID already exists (ROWGUID must be unique per row across the whole table, " +
                    "not reused across documents/save attempts). The client must generate a fresh row identifier for " +
                    "every new row instead of resending one already saved under a different document.");
            return rowsAffected;
        }

        public async Task<int> SaveIncrementalDetailAsync(
            LoginDTO login, int documentId,
            List<MMDetailDTO> newRows, List<MMDetailDTO> modifiedRows, List<int> deletedIds,
            Dictionary<string, string> detailMapping, DbTransaction tx, CancellationToken ct)
        {
            int affected = 0;

            if (deletedIds.Count > 0)
            {
                affected += await _queryExecutor.ExecuteAsync(login, MMHeadIncrementalQB.DELETE_CHARGES_FOR_DETAIL_IDS,
                    new { Ids = deletedIds, DocumentId = documentId }, tx, ct).ConfigureAwait(false);
                affected += await _queryExecutor.ExecuteAsync(login, MMHeadIncrementalQB.DELETE_DETAIL,
                    new { Ids = deletedIds, DocumentId = documentId }, tx, ct).ConfigureAwait(false);
            }

            if (newRows.Count > 0)
            {
                string insertSql = BuildInsertSql("TMMDETAIL", detailMapping, MMHeadIncrementalQB.DETAIL_ROWGUID_EXISTS_GUARD);
                foreach (var row in newRows)
                    affected += await ExecuteGuardedInsertAsync(login, "TMMDETAIL", insertSql, row, row.RowGuid, documentId, tx, ct).ConfigureAwait(false);
            }

            if (modifiedRows.Count > 0)
            {
                string updateSql = BuildUpdateSql("TMMDETAIL", detailMapping, "MMDetailId", "DOCUMENTDETAILID");
                foreach (var row in modifiedRows)
                    affected += await _queryExecutor.ExecuteAsync(login, updateSql, row, tx, ct).ConfigureAwait(false);
            }

            return affected;
        }

        public async Task<int> SaveIncrementalChargeAsync(
            LoginDTO login, int documentId,
            List<MMChargesDTO> newRows, List<MMChargesDTO> modifiedRows, List<int> deletedIds,
            Dictionary<string, string> chargeMapping, DbTransaction tx, CancellationToken ct)
        {
            int affected = 0;

            if (deletedIds.Count > 0)
            {
                affected += await _queryExecutor.ExecuteAsync(login, MMHeadIncrementalQB.DELETE_CHARGE,
                    new { Ids = deletedIds, DocumentId = documentId }, tx, ct).ConfigureAwait(false);
            }

            if (newRows.Count > 0)
            {
                string insertSql = BuildInsertSql("TMMCHARGES", chargeMapping, MMHeadIncrementalQB.CHARGE_ROWGUID_EXISTS_GUARD);
                foreach (var row in newRows)
                    affected += await ExecuteGuardedInsertAsync(login, "TMMCHARGES", insertSql, row, row.RowGuid, documentId, tx, ct).ConfigureAwait(false);
            }

            if (modifiedRows.Count > 0)
            {
                string updateSql = BuildUpdateSql("TMMCHARGES", chargeMapping, "MMChargesId", "DOCUMENTCHARGESID");
                foreach (var row in modifiedRows)
                    affected += await _queryExecutor.ExecuteAsync(login, updateSql, row, tx, ct).ConfigureAwait(false);
            }

            return affected;
        }

        public async Task<int> SaveIncrementalScheduleAsync(
            LoginDTO login, int documentId,
            List<MMScheduleDTO> newRows, List<MMScheduleDTO> modifiedRows, List<int> deletedIds,
            Dictionary<string, string> scheduleMapping, DbTransaction tx, CancellationToken ct)
        {
            int affected = 0;

            if (deletedIds.Count > 0)
            {
                affected += await _queryExecutor.ExecuteAsync(login, MMHeadIncrementalQB.DELETE_SCHEDULE,
                    new { Ids = deletedIds, DocumentId = documentId }, tx, ct).ConfigureAwait(false);
            }

            if (newRows.Count > 0)
            {
                string insertSql = BuildInsertSql("TMMSCHEDULE", scheduleMapping, MMHeadIncrementalQB.SCHEDULE_ROWGUID_EXISTS_GUARD);
                foreach (var row in newRows)
                    affected += await ExecuteGuardedInsertAsync(login, "TMMSCHEDULE", insertSql, row, row.RowGuid, documentId, tx, ct).ConfigureAwait(false);
            }

            if (modifiedRows.Count > 0)
            {
                string updateSql = BuildUpdateSql("TMMSCHEDULE", scheduleMapping, "MMScheduleId", "MMSCHEDULEID");
                foreach (var row in modifiedRows)
                    affected += await _queryExecutor.ExecuteAsync(login, updateSql, row, tx, ct).ConfigureAwait(false);
            }

            return affected;
        }

        public async Task<int> SaveIncrementalLotAsync(
            LoginDTO login, int documentHeaderId,
            List<LotDetailDTO> newRows, List<LotDetailDTO> modifiedRows, List<int> deletedIds,
            Dictionary<string, string> lotMapping, DbTransaction tx, CancellationToken ct)
        {
            int affected = 0;

            if (deletedIds.Count > 0)
            {
                affected += await _queryExecutor.ExecuteAsync(login, MMHeadIncrementalQB.DELETE_LOT,
                    new { Ids = deletedIds, DocumentId = documentHeaderId }, tx, ct).ConfigureAwait(false);
            }

            if (newRows.Count > 0)
            {
                string insertSql = BuildInsertSql("TLOTDETAIL", lotMapping, MMHeadIncrementalQB.LOT_ROWGUID_EXISTS_GUARD);
                foreach (var row in newRows)
                    affected += await ExecuteGuardedInsertAsync(login, "TLOTDETAIL", insertSql, row, row.RowGuid, documentHeaderId, tx, ct).ConfigureAwait(false);
            }

            if (modifiedRows.Count > 0)
            {
                string updateSql = BuildUpdateSql("TLOTDETAIL", lotMapping, "LotDetailId", "LOTDETAILID",
                    scopeDtoKey: "LotDetailObjectHeaderId", scopeColumn: "OBJECTHEADERID");
                foreach (var row in modifiedRows)
                    affected += await _queryExecutor.ExecuteAsync(login, updateSql, row, tx, ct).ConfigureAwait(false);
            }

            return affected;
        }

        public async Task<string> GetSelectListMMHead(CriteriaDTO crt,LoginDTO loginDTO)
        {
            var attrs = crt.SectionCriteriaList?.SelectMany(s => s.AttributesCriteriaList ?? []).ToList() ?? [];

            string number = GetStringValue(attrs.FirstOrDefault(a => a.FieldName == "Number")?.FieldValue);

            long bizTransactionTypeId = ParseLong(attrs.FirstOrDefault(a => a.FieldName == "BizTransactionType.Id")?.FieldValue);

            string bizTransactionClassIdRaw = GetStringValue(attrs.FirstOrDefault(a => a.FieldName == "BizTransactionClassId")?.FieldValue);
            long[] bizTransactionClassIds = ParseLongList(bizTransactionClassIdRaw);

            var param = new
            {
                number,
                biztransactiontypeid = bizTransactionTypeId,
                hasBizTransactionClassIds = bizTransactionClassIds.Length > 0,
                bizTransactionClassIds = bizTransactionClassIds.Length > 0 ? bizTransactionClassIds : new long[] { 0 },
                ouid = loginDTO.WorkOUId
            };

            var result = await _queryExecutor
                .QueryAsync<MMHeadPicklistDTO>(
                    loginDTO,
                    MMheadQB.GET_SELECTLIST_MMHEAD,
                    param);

            return JsonConvert.SerializeObject(result);
        }

        private static string GetStringValue(object? fieldValue)
        {
            if (fieldValue is null)
                return string.Empty;

            if (fieldValue is JsonElement jsonElement)
            {
                return jsonElement.ValueKind switch
                {
                    JsonValueKind.String =>jsonElement.GetString() ?? string.Empty,

                    JsonValueKind.Number =>jsonElement.GetRawText(),

                    JsonValueKind.True =>bool.TrueString,

                    JsonValueKind.False =>bool.FalseString,

                    JsonValueKind.Null =>string.Empty,_ =>jsonElement.GetRawText()
                };
            }

            return Convert.ToString(fieldValue)?? string.Empty;
        }

        private static long ParseLong(object? fieldValue)
        {
            string value = GetStringValue(fieldValue);

            return long.TryParse(value,out long result)? result: 0;
        }

        private static long[] ParseLongList(string raw) =>
            string.IsNullOrWhiteSpace(raw)
                ? []
                : raw.Split(',', StringSplitOptions.RemoveEmptyEntries)
                     .Select(s => long.TryParse(s.Trim(), out var id) ? id : (long?)null)
                     .Where(id => id.HasValue)
                     .Select(id => id!.Value)
                     .ToArray();
    }
}