using System.Data.Common;
using GB5Shared.DaprCache;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.Telemetry;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.EntityHandler;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.ListQuery;
using GB5Shared.QueryExecutor;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using QMSDAL.CustomCode.Matrix;
using QMSDAL.DTO;
using QMSDAL.Query.Matrix;
using static GB5Shared.GB5Constant.Constant;

namespace QMSBLL.Matrix
{
    public class MatrixBLL : IMatrixBLL
    {
        // Max individual serial tiles returned per component (per set in Set View,
        // per item overall in FG View) — bulk/weight components can need thousands
        // of units per set; TotalSerialsForItem/SerialRangeFrom/To always carry the
        // true count/range regardless of this cap.
        private const int MAX_SERIAL_TILES_PER_ITEM = 10;

        private readonly IMatrixDAL _matrixDAL;
        private readonly AutoNumber _autoNumber;
        private readonly VoucherNumberService _voucherNumberService;
        private readonly IQueryExecutor _queryExecutor;
        private readonly KeyInvalidate _keyInvalidate;
        private readonly BaseEntityAppService<MatrixDTO> _baseEntityAppService;
        private readonly IListHandler<MatrixListQuery, MatrixListDTO> _listHandler;
        private readonly ILogger<MatrixBLL> _logger;

        public MatrixBLL(
            IMatrixDAL matrixDAL,
            AutoNumber autoNumber,
            VoucherNumberService voucherNumberService,
            IQueryExecutor queryExecutor,
            KeyInvalidate keyInvalidate,
            BaseEntityAppService<MatrixDTO> baseEntityAppService,
            IListHandler<MatrixListQuery, MatrixListDTO> listHandler,
            ILogger<MatrixBLL> logger)
        {
            _matrixDAL = matrixDAL;
            _autoNumber = autoNumber;
            _voucherNumberService = voucherNumberService;
            _queryExecutor = queryExecutor;
            _keyInvalidate = keyInvalidate;
            _baseEntityAppService = baseEntityAppService;
            _listHandler = listHandler;
            _logger = logger;
        }

        // ── Get single matrix ─────────────────────────────────────────────────
        public async Task<string> GetMatrixAsync(
            int matrixId, LoginDTO login, CancellationToken ct = default)
        {
            try
            {
                var header = await _matrixDAL.GetMatrixAsync(matrixId, login, ct).ConfigureAwait(false);
                if (header is null) return JsonConvert.SerializeObject(null);

                var inputsTask = _matrixDAL.GetMatrixInputsAsync(matrixId, login, ct);
                var outputsTask = _matrixDAL.GetMatrixOutputsAsync(matrixId, login, ct);
                var countsTask = _matrixDAL.GetMatrixOutputCountsAsync(matrixId, login, ct);

                await Task.WhenAll(inputsTask, outputsTask, countsTask).ConfigureAwait(false);

                header.Inputs = (await inputsTask)?.ToList() ?? [];
                header.Outputs = (await outputsTask)?.ToList() ?? [];

                var (sets, serials) = await countsTask;
                header.SetsCount = sets;
                header.SerialsCount = serials;

                return JsonConvert.SerializeObject(header);
            }
            catch { throw; }
        }

        // ── Get all matrices ──────────────────────────────────────────────────
        public async Task<IEnumerable<MatrixDTO>> GetAllMatricesFullAsync(
            LoginDTO login, CancellationToken ct = default)
        {
            var headersTask = _matrixDAL.GetAllMatrixHeadersAsync(login, ct);
            var inputsTask = _matrixDAL.GetAllMatrixInputsAsync(login, ct);
            var outputsTask = _matrixDAL.GetAllMatrixOutputsAsync(login, ct);
            await Task.WhenAll(headersTask, inputsTask, outputsTask).ConfigureAwait(false);

            var headers = (await headersTask).ToList();
            var inputs = (await inputsTask).ToLookup(i => i.MatrixId);
            var outputs = (await outputsTask).ToLookup(o => o.MatrixId);

            foreach (var h in headers)
            {
                h.Inputs = inputs[h.MatrixId].ToList();
                h.Outputs = outputs[h.MatrixId].ToList();
            }
            return headers;
        }

        // ── Paged list ────────────────────────────────────────────────────────
        public Task<PagedResult<MatrixListDTO>> GetMatrixListAsync(
            MatrixListQuery query, LoginDTO login, CancellationToken ct = default)
            => _listHandler.HandleAsync(query, login, ct);

        // ── Save ──────────────────────────────────────────────────────────────
        public async Task<string> SaveMatrixAsync(
            MatrixDTO dto, LoginDTO login, CancellationToken ct = default)
        {
            if (dto is null) throw new ArgumentNullException(nameof(dto));

            // MatrixCode rules:
            //   New record + BizTransactionTypeId > 0  → auto-generated (caller sends "" or omits it)
            //   New record + BizTransactionTypeId <= 0 → caller must supply MatrixCode manually
            //   Update                                  → MatrixCode already persisted; must not be blank
            if (dto.MatrixId != 0 && string.IsNullOrWhiteSpace(dto.MatrixCode))
                return "Matrix code is required.";
            if (dto.MatrixId == 0 && dto.BizTransactionTypeId <= 0 && string.IsNullOrWhiteSpace(dto.MatrixCode))
                return "Matrix code is required. Provide BizTransactionTypeId for auto-generation or supply MatrixCode manually.";
            if (dto.AllocationId == 0) return "Allocation is required.";
            if (!await _matrixDAL.AllocationExistsAsync(dto.AllocationId, login, ct).ConfigureAwait(false))
                return $"AllocationId {dto.AllocationId} does not resolve to a valid Sales Order allocation. " +
                       "This must be the SO's own header allocation id (MALLOCATION.ALLOCATIONID) — " +
                       "not a pending-allocation line's AllotedAllocationId.";
            if (dto.MatrixMode == 1 && dto.FgItemId == 0) return "FG Item is required for FGItem mode.";
            if (dto.MatrixMode == 2 && dto.ProcessGroupId == 0) return "Process Group is required for OpenMix mode.";
            if (dto.MatrixMode != 2 && dto.SetFrom > dto.SetTo) return "Set From cannot be greater than Set To.";
            if (dto.MatrixMode != 2 && dto.SetFrom < 1) return "Set From must be greater than zero.";
            if (dto.MatrixId != 0 && dto.MatrixStatus != 0) return "Only Draft matrices can be modified.";

            if (dto.MatrixMode != 2 && dto.Outputs != null && dto.Outputs.Count > 0)
                return "Outputs cannot be saved manually for Product or FGItem mode.";

            if (dto.MatrixMode == 2 && dto.Outputs != null && dto.Outputs.Count > 0)
            {
                for (int i = 0; i < dto.Outputs.Count; i++)
                {
                    var op = dto.Outputs[i];
                    if (op.ItemId == 0) return $"Output line {i + 1}: ItemId is required.";
                    if (op.SkuId == 0) return $"Output line {i + 1}: SkuId is required.";
                    if (op.LotQuantity <= 0) return $"Output line {i + 1}: LotQuantity must be greater than zero.";
                    if (op.OutputLevel != 2) return $"Output line {i + 1}: OutputLevel must be 2 for OpenMix mode.";
                }
            }

            if (dto.MatrixMode != 2)
            {
                bool overlap = await _matrixDAL.HasSetRangeOverlapAsync(dto.AllocationId, dto.MatrixMode, dto.SetFrom, dto.SetTo, dto.MatrixId, login, ct).ConfigureAwait(false);
                if (overlap) return "Set range overlaps with an existing Active matrix for this allocation.";
            }

            if (dto.MatrixMode == 2) { dto.SetFrom = 0; dto.SetTo = 0; dto.FgItemId = -1; dto.FgSkuId = -1; }
            if (dto.MatrixMode != 2) dto.ProcessGroupId = -1;
            if (dto.MatrixMode != 2 && dto.Inputs != null) foreach (var input in dto.Inputs) input.MixProportion = 0;
            // NOTE: LotId == 0 means "unset" (FE default) and normalizes to the -1
            // NONE sentinel. A real LotId is a large negative sequence value (this
            // schema's PK convention, e.g. -1499999813) — checking "<= 0" here would
            // wrongly reset every real batch assignment back to NONE, since a real
            // negative LotId already satisfies "<= 0". Only 0 itself means unset.
            if (dto.Inputs != null) foreach (var input in dto.Inputs) if (input.LotId == 0) input.LotId = -1;
            if (dto.Outputs != null) foreach (var output in dto.Outputs) if (output.LotId == 0) output.LotId = -1;

            bool isNew = dto.MatrixId == 0;
            if (isNew) dto.Version = 0;

            var trans = await _queryExecutor.BeginTransactionAsync(login);
            try
            {
                if (isNew)
                {
                    dto.AllocationSlNo = await _matrixDAL.GetNextAllocationSlNoAsync(dto.AllocationId, login, ct).ConfigureAwait(false);

                    if (dto.BizTransactionTypeId > 0)
                    {
                        // BizTransactionTypeId supplied → auto-generate MatrixCode via voucher number service.
                        // Counter auto-commits independently (transaction: null) so the
                        // sequence number is never rolled back even if the main tx fails.
                        string? generated = await _voucherNumberService.GetNextVoucherNumberAsync(
                            dto.BizTransactionTypeId,
                            DateOnly.FromDateTime(DateTime.Today),
                            login,
                            transaction: null,
                            ct).ConfigureAwait(false);

                        if (generated != null)
                            dto.MatrixCode = generated;
                    }
                    else
                    {
                        // No BizTransactionTypeId → MatrixCode was supplied manually by the caller.
                        // Validation above already ensured it is non-blank.
                    }

                    // Duplicate check runs after auto-generation so the generated code is tested too.
                    int dupCount = await _matrixDAL.CheckDuplicateCodeAsync(dto.MatrixCode, dto.MatrixId, login, ct).ConfigureAwait(false);
                    if (dupCount > 0) return $"Matrix code '{dto.MatrixCode}' is already in use.";

                    int inputCount = dto.Inputs?.Count ?? 0;
                    int outputCount = dto.Outputs?.Count ?? 0;
                    var auto = await _autoNumber.GetNumberAsync(1 + inputCount + outputCount, AUTONUMBERCONSTANT.MATRIX, login).ConfigureAwait(false);
                    dto.MatrixId = auto.StartNumber;

                    if (inputCount > 0)
                    {
                        int offset = 1;
                        foreach (var input in dto.Inputs!)
                        { input.MatrixInputId = auto.StartNumber + offset; input.MatrixId = dto.MatrixId; input.SlNo = (short)offset++; }
                    }
                    if (outputCount > 0)
                    {
                        int offset = 1 + (dto.Inputs?.Count ?? 0); short slNo = 0;
                        foreach (var output in dto.Outputs!)
                        { output.MatrixOutputId = auto.StartNumber + offset++; output.MatrixId = dto.MatrixId; output.SlNo = ++slNo; output.OutputLevel = 2; output.TenantId = login.ClientId; }
                    }
                }
                else
                {
                    if (dto.Inputs != null && dto.Inputs.Count > 0)
                    {
                        var inputAuto = await _autoNumber.GetNumberAsync(dto.Inputs.Count, AUTONUMBERCONSTANT.MATRIXINPUT, login).ConfigureAwait(false);
                        int offset = 0;
                        foreach (var input in dto.Inputs)
                        { input.MatrixInputId = inputAuto.StartNumber + offset; input.MatrixId = dto.MatrixId; input.SlNo = (short)(offset++ + 1); }
                    }
                    if (dto.Outputs != null && dto.Outputs.Count > 0)
                    {
                        var outputAuto = await _autoNumber.GetNumberAsync(dto.Outputs.Count, AUTONUMBERCONSTANT.MATRIXOUTPUT, login).ConfigureAwait(false);
                        int offset = 0; short slNo = 0;
                        foreach (var output in dto.Outputs)
                        { output.MatrixOutputId = outputAuto.StartNumber + offset++; output.MatrixId = dto.MatrixId; output.SlNo = ++slNo; output.OutputLevel = 2; output.TenantId = login.ClientId; }
                    }
                }

                if (isNew) await _matrixDAL.SaveMatrixAsync(dto, login, trans, ct).ConfigureAwait(false);
                else await _matrixDAL.UpdateMatrixAsync(dto, login, trans, ct).ConfigureAwait(false);

                if (!isNew)
                {
                    await _matrixDAL.DeleteMatrixInputsAsync(dto.MatrixId, login, trans, ct).ConfigureAwait(false);
                    if (dto.MatrixMode == 2)
                        await _matrixDAL.DeleteMatrixOutputsAsync(dto.MatrixId, login, trans, ct).ConfigureAwait(false);
                }

                if (dto.Inputs != null && dto.Inputs.Count > 0)
                    await _matrixDAL.BulkSaveMatrixInputsAsync(dto.Inputs, dto.MatrixId, login, trans, ct).ConfigureAwait(false);
                if (dto.MatrixMode == 2 && dto.Outputs != null && dto.Outputs.Count > 0)
                    await _matrixDAL.BulkSaveMatrixOutputsAsync(dto.Outputs, login, trans, ct).ConfigureAwait(false);

                // ── TRESERVATION — create for input lines not yet reserved ────────
                // IsReservation == true  → reservation already exists, skip.
                // IsReservation == false → insert now (inside same transaction so it
                //                          rolls back with the header if anything fails).
                //
                // Gated by MBIZTRANSACTIONTYPE.RESERVATIONTYPE (CK_MBIZTRANSACTIONTYPE_RESERVATIONTYPE):
                //   0 = Not Required → never auto-reserve
                //   1 = Auto         → auto-reserve here (the only case that proceeds)
                //   2 = Manual       → reservation is created through a separate manual
                //                      process, not silently here
                // BizTransactionTypeId <= 0 has no policy to consult — preserve the
                // previous always-try behavior rather than guessing.
                // Resolved dynamically by ENTITYCODE — verified live against MENTITY on
                // 217.216.78.142/VFGPLTEST (ENTITYCODE = 'MATRIX' / 'MATRIXINPUT').
                int resEntityTMatrix = await _matrixDAL.GetEntityIdByCodeAsync("MATRIX", login, ct).ConfigureAwait(false)
                    ?? throw new InvalidOperationException("No MENTITY row found for code 'MATRIX'.");
                int resEntityTMatrixInput = await _matrixDAL.GetEntityIdByCodeAsync("MATRIXINPUT", login, ct).ConfigureAwait(false)
                    ?? throw new InvalidOperationException("No MENTITY row found for code 'MATRIXINPUT'.");
                const int RESERVATIONTYPE_AUTO = 1;

                bool autoReservationAllowed = dto.BizTransactionTypeId <= 0;
                if (dto.BizTransactionTypeId > 0)
                {
                    int reservationType = await _matrixDAL
                        .GetBizTransactionTypeReservationTypeAsync(dto.BizTransactionTypeId, login, ct)
                        .ConfigureAwait(false);
                    autoReservationAllowed = reservationType == RESERVATIONTYPE_AUTO;
                }

                var inputsToReserve = autoReservationAllowed
                    ? dto.Inputs?.Where(i => !i.IsReservation && i.AllocatedQty > 0).ToList() ?? []
                    : [];

                if (inputsToReserve.Count > 0)
                {
                    var now = DateTime.UtcNow;

                    var reservations = inputsToReserve.Select(input => new ReservationInsertDTO
                    {
                        TenantId                = login.ClientId,
                        OuId                    = login.WorkOUId,
                        PeriodId                = login.WorkPeriodId,
                        ReservationNumber       = dto.MatrixCode,
                        ReservationDate         = now,
                        ReferenceNumber         = dto.MatrixCode,
                        ReferenceDate           = now,
                        ForObjectHeaderTypeId   = resEntityTMatrix,
                        ForObjectHeaderId       = dto.MatrixId,
                        ForObjectTypeId         = resEntityTMatrixInput,
                        ForObjectId             = input.MatrixInputId,
                        ForAllotedAllocationId  = null,  // FK → TPENDINGALLOCATION — not applicable for matrix reservations
                        ForBizTransactionTypeId = dto.BizTransactionTypeId > 0 ? dto.BizTransactionTypeId : null,
                        ForSlNo                 = input.SlNo,
                        ForDate                 = now,
                        ItemId                  = input.ItemId,
                        SkuId                   = input.SkuId > 0 ? input.SkuId : null,
                        LotId                   = input.LotId > 0 ? input.LotId : null,
                        ReservedFromType        = 0,
                        ReservedQuantity        = input.AllocatedQty,
                        UsedQuantity            = 0,
                        BalanceQuantity         = input.AllocatedQty,
                        CreatedById             = login.UserId,
                        CreatedOn               = now,
                        ModifiedById            = login.UserId,
                        ModifiedOn              = now
                    }).ToList();

                    await _matrixDAL.BulkSaveReservationsAsync(reservations, login, trans, ct).ConfigureAwait(false);
                }

                await _queryExecutor.CommitAsync(trans);

                try
                {
                    var ck = new CacheKeyGeneration().KeyGeneration(dto.MatrixId, EntityConstant.OBJECTMATRIX, CacheKeyLevel.CLIENT_LEVEL, login);
                    await _keyInvalidate.AllInvalidateCache(ck).ConfigureAwait(false);
                }
                catch (Exception cacheEx) { _logger.LogWarning(cacheEx, "Cache invalidation failed for MatrixId {MatrixId}.", dto.MatrixId); }

                return isNew ? "Details saved successfully." : "Details updated successfully.";
            }
            catch { await _queryExecutor.RollbackAsync(trans); throw; }
        }

        // ── Delete ────────────────────────────────────────────────────────────
        public async Task<string> DeleteMatrixAsync(
            int matrixId, LoginDTO login, CancellationToken ct = default)
        {
            try
            {
                var matrix = await _matrixDAL.GetMatrixAsync(matrixId, login, ct).ConfigureAwait(false);
                if (matrix is null) return "Matrix not found.";
                if (matrix.MatrixStatus != 0) return "Only Draft matrices can be deleted.";

                var trans = await _queryExecutor.BeginTransactionAsync(login);
                try
                {
                    await _matrixDAL.SoftDeleteMatrixAsync(matrixId, login, trans, ct).ConfigureAwait(false);
                    await _queryExecutor.CommitAsync(trans);
                    var ck = new CacheKeyGeneration().KeyGeneration(matrixId, EntityConstant.OBJECTMATRIX, CacheKeyLevel.CLIENT_LEVEL, login);
                    await _keyInvalidate.AllInvalidateCache(ck);
                    return "Details deleted successfully.";
                }
                catch (Exception) { await _queryExecutor.RollbackAsync(trans); throw; }
            }
            catch (Exception) { throw; }
        }

        // ── GenerateMatrixLots — PREVIEW ONLY, no DB write ───────────────────
        // ── GenerateMatrixLotsAsync — Matrix-independent ───────────────────────
        // Caller passes AllotedAllocationId (e.g. from GetSelectListAllocationWithItem)
        // instead of MatrixId. Sourced from the same matrix-independent summary as
        // GetMatrixAllocationPreview/GetMatrixCompositionPreview (TPENDINGALLOCATION/
        // TALLOCATION/MALLOCATION/MPARTY/MITEM for the summary, MBOM/TLOT/TLOTDETAIL
        // for BOM + serials) — does not touch TMATRIX / TMATRIXINPUT / TMATRIXOUTPUT.
        public async Task<MatrixGenerationResultDTO> GenerateMatrixLotsAsync(
            int allotedAllocationId, int setsToGenerate, LoginDTO login, CancellationToken ct = default)
        {
            var summary = await GetAllocationItemSummaryAsync(allotedAllocationId, login, ct).ConfigureAwait(false)
                ?? throw new InvalidOperationException($"No pending allocation found for AllotedAllocationId {allotedAllocationId}.");

            int generateNow = setsToGenerate > 0 ? Math.Min(setsToGenerate, summary.Remaining) : summary.Remaining;
            generateNow = Math.Max(generateNow, 0);

            // Serials are generated against the manufactured FG output items
            // (BOMLEVEL = 0) — same items shown on the old Matrix Planner's
            // Output tab — not the raw-material inputs consumed to build them.
            var bomRows = await _matrixDAL.ExplodeBOMOutputsAsync(summary.ItemId, 1, login, ct).ConfigureAwait(false);
            var bomItemsForPreview = (bomRows ?? Enumerable.Empty<ExplodeBOMOutputDTO>())
                .GroupBy(r => r.ItemId)
                .Select(g => new MatrixInputDTO
                {
                    ItemId = g.First().ItemId,
                    ItemCode = g.First().ItemCode,
                    ItemName = g.First().ItemName,
                    SkuId = g.First().SkuId,
                    SkuCode = g.First().SkuCode,
                    SkuName = g.First().SkuName,
                    RequiredQty = g.First().RequiredQty,
                    UomCode = g.First().UomCode,
                    LotId = -1
                })
                .ToList();

            int setFrom = summary.SetsGenerated + 1;
            int setTo = setFrom + generateNow - 1;
            string setRange = generateNow > 0 ? $"Sets {setFrom}–{setTo}" : "All sets generated";
            int totalFGTypes = bomItemsForPreview.Count;

            // ── Per-item independent serial counters (read-only — no DB write) ─
            // Each component keeps its OWN running sequence, continuing from its
            // own last-issued run (same source as the Composition dashboard's
            // LastRun) — NOT a single sequence shared across components. QtyPerSet
            // is the real BOM-required quantity per set for that component.
            var itemIds = bomItemsForPreview.Select(b => b.ItemId).ToList();
            var stockRows = (await _matrixDAL
                .GetStockAndLastSerialForItemsAsync(itemIds, login, ct)
                .ConfigureAwait(false))
                .ToDictionary(s => s.ItemId);

            var qtyPerSetByItem = bomItemsForPreview.ToDictionary(
                b => b.ItemId,
                b => Math.Max((int)Math.Round(b.RequiredQty, MidpointRounding.AwayFromZero), 1));

            // Running counter per item — starts at that item's own LastRun. Advanced
            // by whole ranges (not incremented one-by-one) so a 2600-unit component
            // doesn't cost 2600 iterations per set. Placeholder prefix:
            // "{ItemCode}-{run:D4}" until a dedicated per-item serial-prefix source
            // is confirmed.
            var runningCounter = bomItemsForPreview.ToDictionary(
                b => b.ItemId,
                b => { stockRows.TryGetValue(b.ItemId, out var s); return ExtractLastRun(s?.LastSerialNo); });

            string Serial(MatrixInputDTO bom, int run) => $"{bom.ItemCode}-{run:D4}";

            // ── Build Set View (in-memory — predicted, not yet saved) ─────────
            // Serial tiles are capped per component per set so a bulk/weight
            // component (e.g. 2600 units/set) doesn't blow the response up to tens
            // of thousands of lines — TotalSerialsForItem + SerialRangeFrom/To carry
            // the true count/range regardless of the cap.
            var sets = Enumerable.Range(setFrom, generateNow).Select(setNumber =>
            {
                var fgItemsForSet = bomItemsForPreview.Select(bom =>
                {
                    int qtyPerSet = qtyPerSetByItem[bom.ItemId];
                    int rangeStart = runningCounter[bom.ItemId] + 1;
                    int rangeEnd = runningCounter[bom.ItemId] + qtyPerSet;
                    runningCounter[bom.ItemId] = rangeEnd;

                    int displayCount = Math.Min(qtyPerSet, MAX_SERIAL_TILES_PER_ITEM);
                    var serials = new List<GeneratedSerialDTO>(displayCount);
                    for (int slotIdx = 1; slotIdx <= displayCount; slotIdx++)
                    {
                        int run = rangeStart + slotIdx - 1;
                        serials.Add(new GeneratedSerialDTO
                        {
                            SlotNumber = slotIdx,
                            RunningNumber = run,
                            LotNumber = Serial(bom, run)
                        });
                    }

                    return new GeneratedSetFGItemDTO
                    {
                        FGItemName = bom.ItemName ?? string.Empty,
                        FGItemCode = bom.ItemCode ?? string.Empty,
                        QtyPerSet = qtyPerSet,
                        Serials = serials,
                        TotalSerialsForItem = qtyPerSet,
                        SerialRangeFrom = Serial(bom, rangeStart),
                        SerialRangeTo = Serial(bom, rangeEnd)
                    };
                }).ToList();

                return new GeneratedSetDTO
                {
                    SetNumber = setNumber,
                    SetCode = $"SET-{setNumber:D6}",
                    TotalSerials = fgItemsForSet.Sum(f => f.TotalSerialsForItem),
                    Status = "PLANNED",
                    FGItems = fgItemsForSet
                };
            }).ToList();

            // ── Build FG View from the same Set View data (in-memory — predicted) ─
            // Serials list is the (already-capped) tiles carried over from Set View;
            // TotalSerials/SerialRangeFrom/To reflect the true full run.
            var fgItems = bomItemsForPreview.Select((bom, idx) =>
            {
                var setFgRows = sets
                    .SelectMany(set => set.FGItems
                        .Where(f => f.FGItemCode == bom.ItemCode)
                        .Select(f => (set.SetNumber, FgRow: f)))
                    .ToList();

                var serials = setFgRows
                    .SelectMany(r => r.FgRow.Serials.Select(s => new GeneratedFGSerialDTO
                    {
                        SetLabel = $"S{r.SetNumber}",
                        RunningNumber = s.RunningNumber,
                        LotNumber = s.LotNumber
                    }))
                    .Take(MAX_SERIAL_TILES_PER_ITEM)
                    .ToList();

                int totalSerialsForItem = setFgRows.Sum(r => r.FgRow.TotalSerialsForItem);
                string rangeFrom = setFgRows.Count > 0 ? setFgRows[0].FgRow.SerialRangeFrom : string.Empty;
                string rangeTo = setFgRows.Count > 0 ? setFgRows[^1].FgRow.SerialRangeTo : string.Empty;

                return new GeneratedFGItemDTO
                {
                    RowNumber = idx + 1,
                    FGItemName = bom.ItemName ?? string.Empty,
                    FGItemCode = bom.ItemCode ?? string.Empty,
                    TotalSerials = totalSerialsForItem,
                    Serials = serials,
                    SerialRangeFrom = rangeFrom,
                    SerialRangeTo = rangeTo
                };
            }).ToList();

            int totalSerialsCount = fgItems.Sum(f => f.TotalSerials);

            // ── Product Composition & Next Serial Numbers dashboard ───────────
            var composition = await BuildCompositionPreviewAsync(summary.ItemId, generateNow, login, ct)
                .ConfigureAwait(false);

            string soNumber = summary.AllocationName;
            string productName = summary.ItemName;

            return new MatrixGenerationResultDTO
            {
                MatrixId = 0,
                MatrixCode = string.Empty,
                HeaderTitle = $"{totalSerialsCount} serial numbers ready",
                HeaderSubtitle = $"{generateNow} sets · {soNumber} · {productName} · Review and save below",
                TotalSets = generateNow,
                TotalSerials = totalSerialsCount,
                TotalFGTypes = totalFGTypes,
                TLotRecords = generateNow + totalSerialsCount,
                Reference = string.Empty,
                FooterTitle = $"{totalSerialsCount} serial numbers ready to save",
                FooterSubtitle = $"{soNumber} · {productName} · {setRange}",
                SONumber = soNumber,
                ProductName = productName,
                SetRange = setRange,
                Sets = sets,
                FGItems = fgItems,
                Composition = composition
            };
        }

        // ── SaveSetSerialGeneration — Matrix-independent SAVE ──────────────────
        // Persists the same per-item, per-set serials shown by GenerateMatrixLotsAsync
        // into TLOT and TLOTDETAIL only. Caller passes AllotedAllocationId instead of
        // MatrixId. Does not touch TMATRIX / TMATRIXINPUT / TMATRIXOUTPUT. Every unit
        // is saved (not capped) — the response-size cap only applies to the preview.
        public async Task<ActivationConfirmDTO> SaveSetSerialGenerationAsync(
            int allotedAllocationId, int setsToGenerate, LoginDTO login, CancellationToken ct = default,
            Dictionary<int, HashSet<int>>? selectedSerials = null)
        {
            selectedSerials ??= new Dictionary<int, HashSet<int>>();
            if (setsToGenerate <= 0)
                throw new InvalidOperationException("Please give a SetsToGenerate — it can't be null or 0.");

            var summary = await GetAllocationItemSummaryAsync(allotedAllocationId, login, ct).ConfigureAwait(false)
                ?? throw new InvalidOperationException($"No pending allocation found for AllotedAllocationId {allotedAllocationId}.");

            int generateNow = Math.Min(setsToGenerate, summary.Remaining);
            if (generateNow <= 0)
                throw new InvalidOperationException("No sets remaining to generate for this allocation.");

            // Serials are saved against the manufactured FG output items
            // (BOMLEVEL = 0) — same items shown on the old Matrix Planner's
            // Output tab — not the raw-material inputs consumed to build them.
            var bomRows = await _matrixDAL.ExplodeBOMOutputsAsync(summary.ItemId, 1, login, ct).ConfigureAwait(false);
            var bomItems = (bomRows ?? Enumerable.Empty<ExplodeBOMOutputDTO>())
                .GroupBy(r => r.ItemId)
                .Select(g => g.First())
                .ToList();

            if (bomItems.Count == 0)
                throw new InvalidOperationException($"No BOM found for item {summary.ItemId}.");

            // RM stock sufficiency is deliberately NOT checked here — by request,
            // this endpoint always generates the requested serials regardless of
            // raw material availability. rmItems is still needed below (feeds the
            // TRESERVATION write), just no longer gates the save on a shortage.
            var rmRows = await _matrixDAL.ExplodeBOMInputsAsync(summary.ItemId, generateNow, login, ct).ConfigureAwait(false);
            var rmItems = (rmRows ?? Enumerable.Empty<ExplodeBOMInputDTO>())
                .GroupBy(r => r.ItemId)
                .Select(g => g.First())
                .ToList();

            var itemIds = bomItems.Select(b => b.ItemId).ToList();
            var stockRows = (await _matrixDAL
                .GetStockAndLastSerialForItemsAsync(itemIds, login, ct)
                .ConfigureAwait(false))
                .ToDictionary(s => s.ItemId);
            var reservedRows = (await _matrixDAL
                .GetReservedQtyForItemsAsync(itemIds, login, ct)
                .ConfigureAwait(false))
                .ToDictionary(r => r.ItemId);

            // Same AvailableQty shown on the Composition table / GetSelectableSerials'
            // picklist (TotalQuantity − ReservedQuantity) — the widest fresh range a
            // selection is allowed to draw numbers from, even though the number
            // actually SELECTED must still equal exactly RequiredQty (QtyPerSet ×
            // generateNow). Lets the user pick any N out of a wider shown pool
            // instead of only the first N sequential numbers.
            var availableQtyByItem = bomItems.ToDictionary(b =>
                b.ItemId,
                b =>
                {
                    stockRows.TryGetValue(b.ItemId, out var s);
                    reservedRows.TryGetValue(b.ItemId, out var r);
                    decimal avail = (s?.TotalQuantity ?? 0) - (r?.ReservedQuantity ?? 0);
                    return Math.Max((int)Math.Round(avail, MidpointRounding.AwayFromZero), 0);
                });

            var qtyPerSetByItem = bomItems.ToDictionary(
                b => b.ItemId,
                b => Math.Max((int)Math.Round(b.RequiredQty, MidpointRounding.AwayFromZero), 1));

            var runningCounter = bomItems.ToDictionary(
                b => b.ItemId,
                b => { stockRows.TryGetValue(b.ItemId, out var s); return ExtractLastRun(s?.LastSerialNo); });

            // Skipped-serial pool per item (RunningNumber → LotId) — a selected
            // number matching one of these is REACTIVATED in place (its existing
            // TLOT row flips Status 0→1) instead of a fresh number being issued,
            // exactly like picking "2, 3, 4" out of an available "1, 2, 3, 4, 5"
            // pool where 1 and 5 were skipped in an earlier run.
            var skippedByItem = new Dictionary<int, Dictionary<int, int>>(); // ItemId → (RunningNumber → LotId)

            foreach (var bom in bomItems)
            {
                if (!selectedSerials.ContainsKey(bom.ItemId)) continue;
                var skipped = await _matrixDAL.GetSkippedSerialsAsync(bom.ItemId, login, ct).ConfigureAwait(false);
                skippedByItem[bom.ItemId] = skipped
                    .Select(s => { s.RunningNumber = ExtractLastRun(s.LotNumber); return s; })
                    .ToDictionary(s => s.RunningNumber, s => s.LotId);
            }

            // When the caller sends a SelectedSerials entry for an item:
            //  1. the count selected must exactly equal that item's required
            //     quantity for this run (QtyPerSet × generateNow, i.e. "This Run")
            //     — not fewer, not more.
            //  2. every selected number must be a REAL option — either inside
            //     this run's fresh sequential range, or a previously-skipped
            //     number available for reuse for this item. Anything else
            //     (a stale/typo'd number) is rejected before any DB write.
            foreach (var bom in bomItems)
            {
                if (!selectedSerials.TryGetValue(bom.ItemId, out var selectedForItem))
                    continue;

                string itemLabel = string.IsNullOrEmpty(bom.ItemCode) ? bom.ItemId.ToString() : bom.ItemCode;

                // Picking scattered numbers out of a wide AvailableQty range only
                // has an unambiguous mapping to "which set" for a single-set run —
                // for multiple sets it's unclear which set the gap/skip numbers
                // between sparse picks belong to. Keep it simple: one set at a time.
                if (generateNow != 1)
                    throw new InvalidOperationException(
                        $"Item {itemLabel}: SelectedSerials is only supported when generating exactly 1 set at " +
                        $"a time (SetsToGenerate resolved to {generateNow}). Generate one set per call when using manual serial selection.");

                int requiredForItem = qtyPerSetByItem[bom.ItemId] * generateNow;
                int selectedCount = selectedForItem?.Count ?? 0;

                if (selectedCount != requiredForItem)
                    throw new InvalidOperationException(
                        $"Item {itemLabel} requires exactly {requiredForItem} selected serial(s) for this run " +
                        $"({qtyPerSetByItem[bom.ItemId]} per set × {generateNow} set(s)), but {selectedCount} were selected.");

                // Valid range matches what GetSelectableSerials actually showed —
                // the full AvailableQty-wide range, not just RequiredQty numbers —
                // so the user can pick e.g. "2539,2540" out of a wider "2538-2553"
                // pool. The COUNT selected still must equal RequiredQty exactly
                // (enforced above); only the valid POSITIONS are widened here.
                int freshRangeStart = runningCounter[bom.ItemId] + 1;
                int freshRangeEnd = runningCounter[bom.ItemId] + availableQtyByItem[bom.ItemId];

                var invalid = selectedForItem!
                    .Where(n => !skippedByItem[bom.ItemId].ContainsKey(n) && (n < freshRangeStart || n > freshRangeEnd))
                    .ToList();
                if (invalid.Count > 0)
                    throw new InvalidOperationException(
                        $"Item {itemLabel}: selected serial number(s) {string.Join(", ", invalid)} are neither a " +
                        $"previously-skipped serial available for reuse, nor within this item's available range " +
                        $"({freshRangeStart}–{freshRangeEnd}).");
            }

            // Distribute each item's flat selection across the individual sets:
            // sort ascending, then slice into generateNow chunks of QtyPerSet size
            // each — chunk 0 → Set setFrom, chunk 1 → Set setFrom+1, etc. Simple,
            // deterministic, and matches how sequential fresh generation already
            // groups serials by set.
            var selectionChunksByItem = new Dictionary<int, List<HashSet<int>>>();
            foreach (var bom in bomItems)
            {
                if (!selectedSerials.TryGetValue(bom.ItemId, out var selectedForItem) || selectedForItem == null)
                    continue;

                int qtyPerSet = qtyPerSetByItem[bom.ItemId];
                var sorted = selectedForItem.OrderBy(n => n).ToList();
                var chunks = new List<HashSet<int>>(generateNow);
                for (int setIdx = 0; setIdx < generateNow; setIdx++)
                    chunks.Add(sorted.Skip(setIdx * qtyPerSet).Take(qtyPerSet).ToHashSet());
                selectionChunksByItem[bom.ItemId] = chunks;
            }

            var (_, setLotTypeId, fgSerialLotTypeId) = await ResolveLotFormatAsync(login, ct).ConfigureAwait(false);

            int setFrom = summary.SetsGenerated + 1;
            int setTo = setFrom + generateNow - 1;

            // Reactivated (previously-skipped) serials reuse an existing LotId —
            // they don't need a fresh row. For an item with a selection, the
            // fresh-row count is however far the counter must advance to reach
            // the highest FRESH (non-reactivated) pick — which can exceed
            // QtyPerSet when picks are scattered across a wider AvailableQty
            // range — not just QtyPerSet × generateNow.
            int totalUnits = bomItems.Sum(b =>
            {
                if (!selectedSerials.TryGetValue(b.ItemId, out var sel) || sel == null)
                    return qtyPerSetByItem[b.ItemId] * generateNow;

                var freshPicks = sel.Where(n => !skippedByItem[b.ItemId].ContainsKey(n)).ToList();
                int highestFreshPick = freshPicks.Count > 0 ? freshPicks.Max() : runningCounter[b.ItemId];
                return Math.Max(highestFreshPick - runningCounter[b.ItemId], 0);
            });

            // +generateNow: one real SET-level TLOT/TLOTDETAIL row per set (ItemId =
            // the FG item itself), so "sets generated" becomes a direct, unambiguous
            // COUNT(*) — matching how the matrix-based ActivateMatrix flow already
            // works — instead of inferring it from a BOM component's quantity.
            int totalRows = totalUnits + generateNow;

            var lotAuto = await _autoNumber.GetNumberAsync(totalRows, "LOT", login).ConfigureAwait(false);
            var detailAuto = await _autoNumber.GetNumberAsync(totalRows, "LOTDETAIL", login).ConfigureAwait(false);
            int lotSeq = lotAuto.StartNumber;
            int detailSeq = detailAuto.StartNumber;

            // Resolved dynamically by system code — not hardcoded — since the
            // underlying BIZTRANSACTIONTYPEID differs per OU (verified live: 3 rows
            // share BIZTRANSACTIONTYPECODE = 'LTPRD' for this tenant, one per OUID).
            int bizTxTypeLotProduction = await _matrixDAL
                .GetBizTransactionTypeIdByCodeAsync("LTPRD", login, ct)
                .ConfigureAwait(false)
                ?? throw new InvalidOperationException("No MBIZTRANSACTIONTYPE row found for code 'LTPRD' (LotProduction).");
            int objectHeaderTypeId = EntityConstant.OBJECTALLOCATION;

            // This generation run's own identifying reference — the matrix-independent
            // equivalent of matrix.MatrixCode in the matrix-based path (ActivateMatrix).
            // StockLedgerNumber must be THIS transaction's own number, not the SO/item —
            // matches the convention used everywhere else (StockPostingBLL.head.MMHeadNumber,
            // ProductionBLL.dto.ProductionNumber).
            string runReference = $"SER-{DateTime.UtcNow:yyyy}-{Math.Abs(allotedAllocationId) % 100000:D5}";

            var now = DateTime.UtcNow;
            var expiry = new DateTime(9999, 12, 31);

            var tlotList = new List<TlotInsertDTO>(totalRows);
            var tlotDetailList = new List<TlotDetailInsertDTO>(totalRows);
            string firstLot = string.Empty, lastLot = string.Empty;
            int skippedCount = 0;
            var reactivations = new List<(int LotId, string LotNumber)>();

            var trans = await _queryExecutor.BeginTransactionAsync(login);
            try
            {
            // Re-seed runningCounter from a locking (UPDLOCK, HOLDLOCK) read taken
            // inside this transaction, immediately before the numbers below are
            // actually assigned — serializes concurrent saves for the same item so
            // two callers can't read the same last serial and generate colliding
            // LotNumbers. The earlier unlocked read (stockRows) only fed validation
            // above; the locked value here is what's actually used to number rows.
            foreach (var bom in bomItems)
            {
                var lastSerial = await _matrixDAL
                    .GetLastSerialForItemLockingAsync(bom.ItemId, login, trans, ct)
                    .ConfigureAwait(false);
                runningCounter[bom.ItemId] = ExtractLastRun(lastSerial);
            }

            for (int setIdx = 0; setIdx < generateNow; setIdx++)
            {
                int setNumber = setFrom + setIdx;

                // ── SET lot — real, directly-countable record for this one set ────
                // Includes the allocation's own short name (e.g. "00046/2627") —
                // TLOT's unique constraint is (ItemId, SkuId, LotNumber, LotType), and
                // ItemId here is the PARENT item, which is shared across every SO for
                // the same product. Without a per-allocation qualifier, "Set 1" on two
                // different SOs for the same product collide on the same LotNumber.
                int setLotId = lotSeq++;
                int setDetailId = detailSeq++;
                string setLotNumber = $"{summary.ItemCode}-{summary.AllocationShortName}-SET-{setNumber:D4}";

                tlotList.Add(new TlotInsertDTO
                {
                    LotId = setLotId,
                    LotNumber = setLotNumber,
                    ItemId = summary.ItemId,
                    SkuId = -1,
                    LotTypeId = setLotTypeId,
                    ProcessId = -1,
                    LotDate = now,
                    LotExpiryDate = expiry,
                    Status = 1,
                    Version = 0,
                    TenantId = login.ClientId,
                    CreatedById = login.UserId,
                    CreatedOn = now,
                    ModifiedById = login.UserId,
                    ModifiedOn = now,
                    AllocationId = summary.HeaderAllocationId
                });

                tlotDetailList.Add(new TlotDetailInsertDTO
                {
                    LotDetailId = setDetailId,
                    BizTransactionTypeId = bizTxTypeLotProduction,
                    ObjectHeaderTypeId = objectHeaderTypeId,
                    ObjectHeaderId = allotedAllocationId,
                    ObjectTypeId = -1,
                    ObjectId = -1,
                    SlNo = 1,
                    LotId = setLotId,
                    Quantity = 1,
                    GoodQuantity = 1,
                    RejectedQuantity = 0,
                    ReworkQuantity = 0,
                    OtherQuantity = 0,
                    PackId = -1,
                    PackQuantity = 0,
                    OuId = login.WorkOUId,
                    StoreId = login.WorkStoreId,
                    StockLedgerNumber = runReference,
                    StockLedgerDate = now,
                    ReferenceNumber = summary.AllocationShortName,
                    ReferenceDate = now,
                    LocationType = 0,
                    PartyBranchId = -1,
                    StockPostType = 0,
                    MaterialOwnershipType = 0,
                    UsedInLotId = -1,
                    ReasonId = -1,
                    MarkedGoodQuantity = 0,
                    PlannedQuantity = 1,
                    BinId = -1,
                    ItemPostedCost = 0,
                    D1 = 0,
                    D2 = 0,
                    D3 = 0,
                    D4 = 0,
                    D5 = 0,
                    AllocationId = summary.HeaderAllocationId
                });

                foreach (var bom in bomItems)
                {
                    // This set's chunk of the selection (if any) — the specific
                    // RunningNumbers the user picked for JUST this set, per the
                    // sorted/sliced distribution computed above. No entry at all
                    // for this item → every generated number for this set is kept
                    // (preserves old behavior for callers not yet sending selections).
                    bool hasSelectionForItem = selectionChunksByItem.TryGetValue(bom.ItemId, out var chunksForItem);
                    HashSet<int>? selectedForSet = hasSelectionForItem ? chunksForItem![setIdx] : null;

                    // With a selection present (generateNow is guaranteed 1 here —
                    // enforced above), the picks may be scattered across a WIDE
                    // AvailableQty range (e.g. 2539,2540 out of 2538-2553) rather
                    // than packed into the first QtyPerSet slots. Generate every
                    // number up through the HIGHEST picked one (fresh, non-skipped
                    // numbers only — reactivated/skipped-reuse picks don't advance
                    // the counter) so every number in between gets a real row,
                    // marked skipped if not itself selected.
                    int qtyPerSet;
                    if (hasSelectionForItem)
                    {
                        var freshPicks = selectedForSet!.Where(n => !skippedByItem[bom.ItemId].ContainsKey(n)).ToList();
                        int highestFreshPick = freshPicks.Count > 0 ? freshPicks.Max() : runningCounter[bom.ItemId];
                        qtyPerSet = Math.Max(highestFreshPick - runningCounter[bom.ItemId], 0);
                    }
                    else
                    {
                        qtyPerSet = qtyPerSetByItem[bom.ItemId];
                    }

                    for (int unit = 0; unit < qtyPerSet; unit++)
                    {
                        runningCounter[bom.ItemId]++;
                        int runningNumber = runningCounter[bom.ItemId];
                        string lotNumber = $"{bom.ItemCode}-{runningNumber:D4}";
                        int lotId = lotSeq++;
                        int detailId = detailSeq++;

                        // Deselected in the FG View before saving — still write the row
                        // (so this number is never reissued) but mark it Status=0
                        // ("skipped/available") and GoodQuantity=0 (not real stock),
                        // instead of Status=1 ("issued"). Offered back later via
                        // GetSkippedSerialsAsync.
                        bool isExcluded = hasSelectionForItem
                            && (selectedForSet == null || !selectedForSet.Contains(runningNumber));

                        tlotList.Add(new TlotInsertDTO
                        {
                            LotId = lotId,
                            LotNumber = lotNumber,
                            ItemId = bom.ItemId,
                            SkuId = bom.SkuId > 0 ? bom.SkuId : -1,
                            LotTypeId = fgSerialLotTypeId,
                            ProcessId = -1,
                            LotDate = now,
                            LotExpiryDate = expiry,
                            Status = (byte)(isExcluded ? 0 : 1),
                            Version = 0,
                            TenantId = login.ClientId,
                            CreatedById = login.UserId,
                            CreatedOn = now,
                            ModifiedById = login.UserId,
                            ModifiedOn = now,
                            AllocationId = summary.HeaderAllocationId
                        });

                        tlotDetailList.Add(new TlotDetailInsertDTO
                        {
                            LotDetailId = detailId,
                            BizTransactionTypeId = bizTxTypeLotProduction,
                            ObjectHeaderTypeId = objectHeaderTypeId,
                            ObjectHeaderId = allotedAllocationId,
                            ObjectTypeId = -1,
                            ObjectId = -1,
                            SlNo = 1,
                            LotId = lotId,
                            Quantity = isExcluded ? 0 : 1,
                            GoodQuantity = isExcluded ? 0 : 1,
                            RejectedQuantity = 0,
                            ReworkQuantity = 0,
                            OtherQuantity = 0,
                            PackId = -1,
                            PackQuantity = 0,
                            OuId = login.WorkOUId,
                            StoreId = login.WorkStoreId,
                            StockLedgerNumber = runReference,
                            StockLedgerDate = now,
                            ReferenceNumber = summary.AllocationShortName,
                            ReferenceDate = now,
                            LocationType = 0,
                            PartyBranchId = -1,
                            StockPostType = 0,
                            MaterialOwnershipType = 0,
                            UsedInLotId = -1,
                            ReasonId = -1,
                            MarkedGoodQuantity = 0,
                            PlannedQuantity = isExcluded ? 0 : 1,
                            BinId = -1,
                            ItemPostedCost = 0,
                            D1 = 0,
                            D2 = 0,
                            D3 = 0,
                            D4 = 0,
                            D5 = 0,
                            AllocationId = summary.HeaderAllocationId
                        });

                        if (isExcluded)
                        {
                            skippedCount++;
                            continue;
                        }

                        if (firstLot.Length == 0) firstLot = lotNumber;
                        lastLot = lotNumber;
                    }
                }
            }

            // Reactivations — selected numbers that matched a previously-skipped
            // serial (Status 0 → 1 in place) instead of a fresh insert.
            foreach (var bom in bomItems)
            {
                if (!selectedSerials.TryGetValue(bom.ItemId, out var selectedForItem) || selectedForItem == null)
                    continue;

                foreach (var runningNumber in selectedForItem)
                {
                    if (!skippedByItem[bom.ItemId].TryGetValue(runningNumber, out var lotId))
                        continue;

                    string lotNumber = $"{bom.ItemCode}-{runningNumber:D4}";
                    reactivations.Add((lotId, lotNumber));
                    if (firstLot.Length == 0) firstLot = lotNumber;
                    lastLot = lotNumber;
                }
            }

                foreach (var (reactivatedLotId, _) in reactivations)
                    await _matrixDAL.ReactivateSkippedSerialAsync(
                        reactivatedLotId, runReference, now, login, trans, ct).ConfigureAwait(false);

                await _matrixDAL.BulkSaveTlotRowsAsync(tlotList, login, trans, ct).ConfigureAwait(false);
                await _matrixDAL.BulkSaveTlotDetailRowsAsync(tlotDetailList, login, trans, ct).ConfigureAwait(false);

                // ── TRESERVATION — reserve the RM consumed by this run ────────────
                // Same CK_MBIZTRANSACTIONTYPE_RESERVATIONTYPE gate as ActivateMatrix
                // (0 = Not Required → skip, 1 = Auto → reserve here, 2 = Manual →
                // created through a separate manual process, not silently here).
                // BizTransactionTypeId <= 0 has no policy to consult — preserve the
                // same always-try fallback ActivateMatrix uses.
                bool autoReservationAllowed = summary.BizTransactionTypeId <= 0;
                if (summary.BizTransactionTypeId > 0)
                {
                    int reservationType = await _matrixDAL
                        .GetBizTransactionTypeReservationTypeAsync(summary.BizTransactionTypeId, login, ct)
                        .ConfigureAwait(false);
                    autoReservationAllowed = reservationType == 1; // 1 = Auto
                }

                if (autoReservationAllowed && rmItems.Count > 0)
                {
                    int resEntityAllocation = EntityConstant.OBJECTALLOCATION;

                    var reservations = rmItems.Select(rm => new ReservationInsertDTO
                    {
                        TenantId                = login.ClientId,
                        OuId                    = login.WorkOUId,
                        PeriodId                = login.WorkPeriodId,
                        ReservationNumber       = runReference,
                        ReservationDate         = now,
                        ReferenceNumber         = summary.AllocationShortName,
                        ReferenceDate           = now,
                        ForObjectHeaderTypeId   = resEntityAllocation,
                        ForObjectHeaderId       = allotedAllocationId,
                        ForObjectTypeId         = null,
                        ForObjectId             = null,
                        ForAllotedAllocationId  = allotedAllocationId,
                        ForBizTransactionTypeId = summary.BizTransactionTypeId > 0 ? summary.BizTransactionTypeId : null,
                        ForSlNo                 = rm.SlNo,
                        ForDate                 = now,
                        ItemId                  = rm.ItemId,
                        SkuId                   = rm.SkuId > 0 ? rm.SkuId : null,
                        LotId                   = null,
                        ReservedFromType        = 0,
                        ReservedQuantity        = rm.RequiredQty,
                        UsedQuantity            = 0,
                        BalanceQuantity         = rm.RequiredQty,
                        CreatedById             = login.UserId,
                        CreatedOn               = now,
                        ModifiedById            = login.UserId,
                        ModifiedOn              = now
                    }).ToList();

                    await _matrixDAL.BulkSaveReservationsAsync(reservations, login, trans, ct).ConfigureAwait(false);
                }

                await _queryExecutor.CommitAsync(trans);
            }
            catch (Exception ex)
            {
                await _queryExecutor.RollbackAsync(trans);
                // A concurrent call for the same item can win the locked "next serial"
                // read a moment after this one but insert first — the locking read in
                // GetLastSerialForItemLockingAsync prevents corrupt/duplicate data (this
                // transaction rolls back whole, nothing partial persists) but can't
                // prevent the raw unique-constraint error surfacing here. Give the
                // caller a clear, actionable message instead of the raw SQL text.
                if (ex.Message.Contains("UKTLOT_ITEMID_SKUID_LOTNUMBER_LOTTYPE", StringComparison.OrdinalIgnoreCase))
                    throw new InvalidOperationException(
                        "Another request just generated serials for this allocation at the same moment. " +
                        "Please retry.", ex);
                throw;
            }

            string setRange = $"Sets {setFrom}–{setTo}";
            // tlotList includes the SET-level rows (one per set) and, for excluded
            // serials, a Status=0 row that was never actually issued — subtract both,
            // then add back the reactivated serials (issued via UPDATE, not part of
            // tlotList at all) so the reported count reflects every real, issued
            // component serial for this run.
            int componentSerialCount = tlotList.Count - generateNow - skippedCount + reactivations.Count;
            return new ActivationConfirmDTO
            {
                Title = $"{componentSerialCount} serial numbers saved" +
                    (skippedCount > 0 ? $" ({skippedCount} skipped, available for reuse)" : string.Empty) +
                    (reactivations.Count > 0 ? $" ({reactivations.Count} reused from previously-skipped)" : string.Empty),
                Subtitle = $"{setRange} · {summary.AllocationName} · {summary.ItemName}",
                Reference = runReference,
                SetLots = generateNow,
                Serials = componentSerialCount,
                TLotRecords = tlotList.Count + reactivations.Count,
                SetRange = setRange,
                FirstLotNumber = firstLot,
                LastLotNumber = lastLot,
                Status = "SAVED"
            };
        }

        // ── GetGeneratedSetsAsync ────────────────────────────────────────────────
        // Backs the old Matrix Planner's SetFrom/SetTo picklist: instead of
        // letting the user type an arbitrary range, this returns exactly which
        // set numbers were actually generated (via SaveSetSerialGenerationAsync)
        // for this allocation — e.g. if 5 sets were created, returns [1,2,3,4,5],
        // whatever they really are, plus SetFrom/SetTo bounds. Fully dynamic:
        // parses SetNumber from each real SET-level TLOT row's LotNumber, no
        // set count/range hardcoded. Caller sends a CriteriaDTO with an
        // "AllotedAllocationId" field.
        public async Task<GeneratedSetsPicklistDTO> GetGeneratedSetsAsync(
            CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct = default)
        {
            var attrs = criteriaDTO?.SectionCriteriaList?
                .SelectMany(s => s.AttributesCriteriaList ?? new List<AttributesCriteriaDTO>())
                .ToList() ?? new List<AttributesCriteriaDTO>();

            var allotedAllocationIdAttr = attrs.FirstOrDefault(a =>
                string.Equals(a.FieldName, "AllotedAllocationId", StringComparison.OrdinalIgnoreCase));
            if (allotedAllocationIdAttr?.FieldValue == null ||
                !int.TryParse(allotedAllocationIdAttr.FieldValue.ToString(), out var allotedAllocationId))
                throw new InvalidOperationException("CriteriaDTO must include an \"AllotedAllocationId\" field.");

            // Optional — backs the Set To picklist: when the caller has already
            // picked a Set From value, pass it here so GeneratedSets only offers
            // sets >= SetFrom (a range can't go backward). Omit entirely for the
            // Set From picklist itself, which must show every generated set.
            var setFromAttr = attrs.FirstOrDefault(a =>
                string.Equals(a.FieldName, "SetFrom", StringComparison.OrdinalIgnoreCase));
            int? setFromFilter = setFromAttr?.FieldValue != null && int.TryParse(setFromAttr.FieldValue.ToString(), out var parsedSetFrom)
                ? parsedSetFrom
                : null;

            // Deliberately does NOT go through GetAllocationItemSummaryAsync/
            // TPENDINGALLOCATION — that table only holds allocations still awaiting
            // generation, so a fully-generated allocation (Remaining=0) would have
            // no pending row left even though its TLOT/TLOTDETAIL history (and thus
            // its real generated-set list) still exists. TLOTDETAIL.OBJECTHEADERID
            // already carries the AllotedAllocationId directly, so it's queried on
            // its own, independent of pending/remaining state.
            //
            // Filtered in SQL on the literal "-SET-" LotNumber marker — not
            // LotTypeId (this tenant's MLOTTYPE has no distinct SystemPrefix='SET'
            // row, so ResolveLotFormatAsync's SetLotTypeId resolves to -1, which
            // would match every TLOT row including component serials) and not
            // ItemId (would require resolving the parent product via
            // TPENDINGALLOCATION, which has no row left once fully generated).
            var rows = await _matrixDAL
                .GetGeneratedSetLotsAsync(allotedAllocationId, login, ct)
                .ConfigureAwait(false);

            var allGeneratedSets = rows
                .Select(r => ExtractLastRun(r.LotNumber))
                .Where(n => n > 0)
                .Distinct()
                .OrderBy(n => n)
                .ToList();

            // SetFrom/SetTo bounds always reflect the FULL generated range,
            // regardless of the optional SetFrom filter — the filter only narrows
            // which values GeneratedSets offers for picking, it doesn't change
            // what "has actually been generated" means for this allocation.
            var pickableSets = setFromFilter.HasValue
                ? allGeneratedSets.Where(n => n >= setFromFilter.Value).ToList()
                : allGeneratedSets;

            return new GeneratedSetsPicklistDTO
            {
                AllotedAllocationId = allotedAllocationId,
                ItemId = rows.Select(r => r.ItemId).FirstOrDefault(),
                SetFrom = allGeneratedSets.Count > 0 ? allGeneratedSets.Min() : 0,
                SetTo = allGeneratedSets.Count > 0 ? allGeneratedSets.Max() : 0,
                TotalGenerated = allGeneratedSets.Count,
                GeneratedSets = pickableSets
            };
        }

        // ── GetSkippedSerialsAsync ─────────────────────────────────────────────
        // Serials deselected during a prior SaveSetSerialGeneration call — their
        // TLOT row exists (Status=0) so the number was never reissued, but they
        // were never actually used. RunningNumber is parsed from LotNumber the
        // same way ExtractLastRun does, so callers can offer them back for reuse.
        public async Task<IEnumerable<SkippedSerialDTO>> GetSkippedSerialsAsync(
            int itemId, LoginDTO login, CancellationToken ct = default)
        {
            var rows = await _matrixDAL.GetSkippedSerialsAsync(itemId, login, ct).ConfigureAwait(false);
            foreach (var row in rows)
                row.RunningNumber = ExtractLastRun(row.LotNumber);
            return rows;
        }

        // ── GetSelectableSerialsAsync ────────────────────────────────────────────
        // Backs the "Select Serial" button on one FG item row: combines every
        // previously-skipped number ("Available" — e.g. 1 and 5 left over from an
        // earlier 1-2-3-4-5 run where 2,3,4 were used) with the fresh sequential
        // range into ONE ready-to-render, sorted picklist, plus the exact
        // RequiredQty the user must pick — so the frontend never computes ranges
        // or merges two sources itself. Caller sends a CriteriaDTO with an
        // "ItemId" field (required) and an optional "SerialNumber" field
        // (contains-text search over the merged picklist's LotNumbers) — the
        // fresh half of the picklist is computed in memory, not stored in any
        // table, so this filter is applied here rather than pushed into SQL via
        // CriteriaBuilder.
        public async Task<SelectableSerialPicklistDTO> GetSelectableSerialsAsync(
            CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct = default)
        {
            var attrs = criteriaDTO?.SectionCriteriaList?
                .SelectMany(s => s.AttributesCriteriaList ?? new List<AttributesCriteriaDTO>())
                .ToList() ?? new List<AttributesCriteriaDTO>();

            var itemIdAttr = attrs.FirstOrDefault(a =>
                string.Equals(a.FieldName, "ItemId", StringComparison.OrdinalIgnoreCase));
            if (itemIdAttr?.FieldValue == null || !int.TryParse(itemIdAttr.FieldValue.ToString(), out var itemId))
                throw new InvalidOperationException("CriteriaDTO must include an \"ItemId\" field.");

            string? serialNumberSearch = attrs.FirstOrDefault(a =>
                string.Equals(a.FieldName, "SerialNumber", StringComparison.OrdinalIgnoreCase))
                ?.FieldValue?.ToString();

            // Optional — defaults to 1 set when omitted, matching the single-set
            // assumption callers relied on before this field existed. Must match
            // whatever SetsToGenerate the caller is about to pass to
            // SaveSetSerialGenration, otherwise RequiredQty here won't match
            // "This Run" on the Composition table (QtyPerSet × Sets).
            var setsAttr = attrs.FirstOrDefault(a =>
                string.Equals(a.FieldName, "SetsToGenerate", StringComparison.OrdinalIgnoreCase));
            int setsToGenerate = setsAttr?.FieldValue != null && int.TryParse(setsAttr.FieldValue.ToString(), out var parsedSets) && parsedSets > 0
                ? parsedSets
                : 1;

            var bomInfo = await _matrixDAL.GetBomOutputQtyPerSetForItemAsync(itemId, login, ct).ConfigureAwait(false)
                ?? throw new InvalidOperationException(
                    $"Item {itemId} is not a BOM output (FG) component of any product — cannot resolve QtyPerSet.");

            int qtyPerSet = Math.Max((int)Math.Round(bomInfo.QtyPerSet, MidpointRounding.AwayFromZero), 1);
            int requiredQty = qtyPerSet * setsToGenerate;

            var stockTask = _matrixDAL.GetStockAndLastSerialForItemsAsync(new[] { itemId }, login, ct);
            var reservedTask = _matrixDAL.GetReservedQtyForItemsAsync(new[] { itemId }, login, ct);
            var skippedTask = _matrixDAL.GetSkippedSerialsAsync(itemId, login, ct);
            await Task.WhenAll(stockTask, reservedTask, skippedTask).ConfigureAwait(false);

            var stock = (await stockTask.ConfigureAwait(false)).FirstOrDefault();
            var reservation = (await reservedTask.ConfigureAwait(false)).FirstOrDefault();
            int lastRun = ExtractLastRun(stock?.LastSerialNo);

            // Same TotalQuantity − ReservedQuantity used on the Composition
            // table — the number of fresh sequential slots to offer as pickable,
            // starting right after LastRun. Matches "AvailableQty" as shown on
            // screen (e.g. AvailableQty=6 → offer LastRun+1..LastRun+6).
            decimal availableQuantity = (stock?.TotalQuantity ?? 0) - (reservation?.ReservedQuantity ?? 0);
            int availableSlots = Math.Max((int)Math.Round(availableQuantity, MidpointRounding.AwayFromZero), 0);

            var skipped = (await skippedTask.ConfigureAwait(false))
                .Select(s => { s.RunningNumber = ExtractLastRun(s.LotNumber); return s; })
                .ToList();

            string Serial(int run) => $"{bomInfo.ItemCode}-{run:D4}";

            // Picklist = previously-skipped numbers (Status=0, "Available",
            // reactivated on save) PLUS a fresh sequential range sized to
            // AvailableQty (never-issued numbers immediately after LastRun).
            // No historical already-issued (Status=1) serials are ever shown —
            // only what's actually free to pick right now. Empty when both
            // AvailableQty and skipped stock are 0 (e.g. first-ever run) — the
            // caller falls back to normal auto-generation for this run.
            var picklist = new List<SelectableSerialDTO>();
            foreach (var s in skipped)
                picklist.Add(new SelectableSerialDTO
                {
                    RunningNumber = s.RunningNumber,
                    LotNumber = s.LotNumber,
                    Source = "Available",
                    LotId = s.LotId
                });

            var alreadySkipped = skipped.Select(s => s.RunningNumber).ToHashSet();
            for (int i = 1; i <= availableSlots; i++)
            {
                int run = lastRun + i;
                if (alreadySkipped.Contains(run)) continue; // don't double-list a number already covered above
                picklist.Add(new SelectableSerialDTO
                {
                    RunningNumber = run,
                    LotNumber = Serial(run),
                    Source = "Available",
                    LotId = -1
                });
            }

            picklist = picklist.OrderBy(p => p.RunningNumber).ToList();

            if (!string.IsNullOrWhiteSpace(serialNumberSearch))
                picklist = picklist
                    .Where(p => p.LotNumber.Contains(serialNumberSearch, StringComparison.OrdinalIgnoreCase))
                    .ToList();

            return new SelectableSerialPicklistDTO
            {
                ItemId = itemId,
                ItemCode = bomInfo.ItemCode ?? string.Empty,
                ItemName = bomInfo.ItemName ?? string.Empty,
                QtyPerSet = qtyPerSet,
                SetsToGenerate = setsToGenerate,
                RequiredQty = requiredQty,
                LastRun = lastRun,
                AvailableCount = picklist.Count,
                Picklist = picklist
            };
        }

        // ── ActivateMatrix — SAVES to DB ──────────────────────────────────────
        public async Task<ActivationConfirmDTO> ActivateMatrixAsync(
            int matrixId, LoginDTO login, CancellationToken ct = default)
        {
            var matrix = await _matrixDAL.GetMatrixAsync(matrixId, login, ct).ConfigureAwait(false)
                ?? throw new InvalidOperationException($"Matrix {matrixId} not found.");

            if (matrix.MatrixStatus != 0)
                throw new InvalidOperationException(
                    $"Matrix {matrix.MatrixCode} is not in Draft status and cannot be activated.");

            var inputs = await _matrixDAL.GetMatrixInputsAsync(matrixId, login, ct).ConfigureAwait(false);
            var inputList = inputs?.ToList() ?? [];

            var unassigned = inputList
                .Where(i => i.ItemCategory == 0 && (i.LotId == -1 || i.LotId == 0))
                .DistinctBy(i => i.ItemId)
                .ToList();

            if (unassigned.Count > 0)
            {
                var items = string.Join(", ",
                    unassigned.Select(i => $"ItemId={i.ItemId}, ItemCode={i.ItemCode ?? "NULL"}"));
                throw new InvalidOperationException(
                    $"The following RM items have no batch assigned: {items}");
            }

            if (matrix.MatrixMode != 2)
            {
                bool overlap = await _matrixDAL
                    .HasSetRangeOverlapAsync(matrix.AllocationId, matrix.MatrixMode,
                        matrix.SetFrom, matrix.SetTo, matrixId, login, ct)
                    .ConfigureAwait(false);
                if (overlap)
                    throw new InvalidOperationException(
                        "Set range overlaps with an existing Active matrix for this allocation.");
            }

            var shortages = await _matrixDAL.GetStockShortageInputsAsync(matrixId, login, ct).ConfigureAwait(false);
            var shortageList = shortages?.ToList() ?? [];
            if (shortageList.Count > 0)
            {
                var items = string.Join("; ",
                    shortageList.Select(s =>
                        $"{s.ItemCode} (required {s.AllocatedQty}, available {s.FreeQty}, short {s.ShortageQty})"));
                throw new InvalidOperationException($"Insufficient stock for activation: {items}");
            }

            // ── Mode 2 (OpenMix) ──────────────────────────────────────────────
            if (matrix.MatrixMode == 2)
            {
                var trans2 = await _queryExecutor.BeginTransactionAsync(login);
                try
                {
                    await _matrixDAL.ActivateOpenMixInputsAsync(matrixId, login, trans2, ct).ConfigureAwait(false);
                    await _matrixDAL.ActivateMatrixAsync(matrixId, login, trans2, ct).ConfigureAwait(false);
                    await _queryExecutor.CommitAsync(trans2);
                    var ck = new CacheKeyGeneration().KeyGeneration(matrixId, EntityConstant.OBJECTMATRIX, CacheKeyLevel.CLIENT_LEVEL, login);
                    await _keyInvalidate.AllInvalidateCache(ck);

                    return new ActivationConfirmDTO
                    {
                        Title = "OpenMix activated",
                        Subtitle = $"{matrix.MatrixCode} · No serials generated",
                        Reference = $"SER-{DateTime.UtcNow:yyyy}-{Math.Abs(matrixId) % 100000:D5}",
                        SetLots = 0,
                        Serials = 0,
                        TLotRecords = 0,
                        SetRange = string.Empty,
                        Status = "PLANNED"
                    };
                }
                catch (Exception) { await _queryExecutor.RollbackAsync(trans2); throw; }
            }

            // ── Mode 0 / 1 ────────────────────────────────────────────────────
            int setCount;
            List<MatrixOutputDTO> savedOutputList;

            bool preGenerated = await _matrixDAL
                .HasOutputsGeneratedAsync(matrixId, login, ct).ConfigureAwait(false);

            if (preGenerated)
            {
                // ── Already has outputs — just activate ───────────────────────
                var trans3 = await _queryExecutor.BeginTransactionAsync(login);
                try
                {
                    await _matrixDAL.ActivateMatrixAsync(matrixId, login, trans3, ct).ConfigureAwait(false);
                    await _queryExecutor.CommitAsync(trans3);
                }
                catch (Exception) { await _queryExecutor.RollbackAsync(trans3); throw; }

                savedOutputList = (await _matrixDAL.GetMatrixOutputsAsync(matrixId, login, ct)
                    .ConfigureAwait(false))?.ToList() ?? [];
                setCount = matrix.SetTo - matrix.SetFrom + 1;
            }
            else
            {
                // ── Fresh activation — build + save all lots ──────────────────
                var trans = await _queryExecutor.BeginTransactionAsync(login);
                try
                {
                    // Lot-sequence read happens on `trans` (with UPDLOCK, HOLDLOCK) so it is
                    // serialized with the TLOT insert below and can't race another activation.
                    var (tlotList, tlotDetailList, outputList, sc, _) =
                        await BuildLotListsAsync(matrix, login, trans, ct).ConfigureAwait(false);
                    setCount = sc;

                    // 1. TLOT — lot master records
                    await _matrixDAL.BulkSaveTlotRowsAsync(tlotList, login, trans, ct)
                        .ConfigureAwait(false);

                    // 2. TLOTDETAIL — stock IN rows (GoodQuantity=1)
                    //    Makes lots visible to GET_AVAILABLE_BATCHES stock queries
                    await _matrixDAL.BulkSaveTlotDetailRowsAsync(tlotDetailList, login, trans, ct)
                        .ConfigureAwait(false);

                    // 3. TMATRIXOUTPUT — matrix planning link (OutputLevel 0=SET, 1=FG serial)
                    await _matrixDAL.BulkSaveMatrixOutputsAsync(outputList, login, trans, ct)
                        .ConfigureAwait(false);

                    // 4. TMATRIX — mark as Active (MatrixStatus = 1)
                    await _matrixDAL.ActivateMatrixAsync(matrixId, login, trans, ct)
                        .ConfigureAwait(false);

                    await _queryExecutor.CommitAsync(trans);
                }
                catch (Exception) { await _queryExecutor.RollbackAsync(trans); throw; }

                savedOutputList = (await _matrixDAL.GetMatrixOutputsAsync(matrixId, login, ct)
                    .ConfigureAwait(false))?.ToList() ?? [];
            }

            var cacheKey = new CacheKeyGeneration().KeyGeneration(
                matrixId, EntityConstant.OBJECTMATRIX, CacheKeyLevel.CLIENT_LEVEL, login);
            await _keyInvalidate.AllInvalidateCache(cacheKey);

            // ── Build confirmation response ────────────────────────────────────
            var fgLots = savedOutputList.Where(o => o.OutputLevel == 1).ToList();
            var setLots = savedOutputList.Where(o => o.OutputLevel == 0).ToList();

            int totalSerials = fgLots.Count;
            int totalSetLots = setLots.Count;
            int setFrom = matrix.SetFrom;
            int setTo = matrix.SetFrom + setCount - 1;
            string setRange = $"Sets {setFrom}–{setTo}";

            var orderedFgLots = fgLots
                .Where(o => !string.IsNullOrEmpty(o.LotNumber))
                .OrderBy(o => o.LotNumber, StringComparer.OrdinalIgnoreCase)
                .ToList();

            string firstLot = orderedFgLots.Count > 0 ? orderedFgLots.First().LotNumber ?? "-" : "-";
            string lastLot = orderedFgLots.Count > 0 ? orderedFgLots.Last().LotNumber ?? "-" : "-";

            return new ActivationConfirmDTO
            {
                Title = $"{totalSerials} serial numbers saved",
                Subtitle = $"{setRange} · {matrix.SoNumber ?? matrix.MatrixCode} · {matrix.FgItemName ?? string.Empty}",
                Reference = $"SER-{DateTime.UtcNow:yyyy}-{Math.Abs(matrixId) % 100000:D5}",
                SetLots = totalSetLots,
                Serials = totalSerials,
                TLotRecords = totalSetLots + totalSerials,
                SetRange = setRange,
                FirstLotNumber = firstLot,
                LastLotNumber = lastLot,
                Status = "PLANNED"
            };
        }

        // ── BuildLotListsAsync ────────────────────────────────────────────────
        private async Task<(
            List<TlotInsertDTO> TlotList,
            List<TlotDetailInsertDTO> TlotDetailList,
            List<MatrixOutputDTO> OutputList,
            int SetCount,
            int SerialsPerSet)>
        BuildLotListsAsync(MatrixDTO matrix, LoginDTO login, DbTransaction transaction, CancellationToken ct)
        {
            // ── LotType IDs + Format ──────────────────────────────────────────
            var (lotFormat, setLotTypeId, fgSerialLotTypeId) = await ResolveLotFormatAsync(login, ct).ConfigureAwait(false);

            // Locking read (UPDLOCK, HOLDLOCK) inside the caller's transaction — serializes
            // concurrent ActivateMatrix calls against the same lot prefix so two callers
            // can't read the same MAX(sequence) and generate duplicate lot numbers.
            int lastSequence = await _queryExecutor
                .ExecuteScalarAsync<int>(login, MatrixQB.GET_LAST_LOT_SEQUENCE,
                    new { Prefix = lotFormat.Prefix, Suffix = lotFormat.Suffix, TenantId = login.ClientId },
                    transaction: transaction,
                    cancellationToken: ct)
                .ConfigureAwait(false);

            int nextSeq = lastSequence + 1;

            // ── BOM explosion with dedup ──────────────────────────────────────
            var bomItems = (await ExplodeForActivationAsync(matrix, login, ct).ConfigureAwait(false))
                .GroupBy(b => b.ItemId)
                .Select(g => g.First())
                .ToList();

            int setCount = matrix.SetTo - matrix.SetFrom + 1;
            int serialsPerSet = bomItems.Count;
            int totalLots = setCount + (setCount * serialsPerSet);

            // ── Auto-numbers ──────────────────────────────────────────────────
            var lotAuto = await _autoNumber.GetNumberAsync(totalLots, "LOT", login).ConfigureAwait(false);
            var outputAuto = await _autoNumber.GetNumberAsync(totalLots, AUTONUMBERCONSTANT.MATRIXOUTPUT, login).ConfigureAwait(false);
            var detailAuto = await _autoNumber.GetNumberAsync(totalLots, "LOTDETAIL", login).ConfigureAwait(false);

            var tlotList = new List<TlotInsertDTO>(totalLots);
            var tlotDetailList = new List<TlotDetailInsertDTO>(totalLots);
            var outputList = new List<MatrixOutputDTO>(totalLots);

            int lotSeq = lotAuto.StartNumber;
            int outputSeq = outputAuto.StartNumber;
            int detailSeq = detailAuto.StartNumber;
            short slNo = 0;
            var now = DateTime.UtcNow;
            var expiry = new DateTime(9999, 12, 31);

            // ── TLOTDETAIL constants ──────────────────────────────────────────
            // Resolved dynamically by system code — not hardcoded — since the
            // underlying BIZTRANSACTIONTYPEID differs per OU (verified live: 3 rows
            // share BIZTRANSACTIONTYPECODE = 'LTPRD' for this tenant, one per OUID).
            int bizTxTypeLotProduction = await _matrixDAL
                .GetBizTransactionTypeIdByCodeAsync("LTPRD", login, ct)
                .ConfigureAwait(false)
                ?? throw new InvalidOperationException("No MBIZTRANSACTIONTYPE row found for code 'LTPRD' (LotProduction).");
            // Resolved dynamically by ENTITYCODE — verified live against MENTITY on
            // 217.216.78.142/VFGPLTEST (ENTITYCODE = 'MATRIX' / 'MATRIXOUTPUT').
            int entityTMatrix = await _matrixDAL.GetEntityIdByCodeAsync("MATRIX", login, ct).ConfigureAwait(false)
                ?? throw new InvalidOperationException("No MENTITY row found for code 'MATRIX'.");
            int entityTMatrixOutput = await _matrixDAL.GetEntityIdByCodeAsync("MATRIXOUTPUT", login, ct).ConfigureAwait(false)
                ?? throw new InvalidOperationException("No MENTITY row found for code 'MATRIXOUTPUT'.");

            for (int set = matrix.SetFrom; set <= matrix.SetTo; set++)
            {
                // ── SET lot ───────────────────────────────────────────────────
                int setLotId = lotSeq++;
                int setOutputId = outputSeq++;
                string setLotNum = FormatLotNumber(lotFormat, nextSeq++);

                tlotList.Add(new TlotInsertDTO
                {
                    LotId = setLotId,
                    LotNumber = setLotNum,
                    ItemId = matrix.FgItemId != -1 ? matrix.FgItemId : -1,
                    SkuId = matrix.FgSkuId != -1 ? matrix.FgSkuId : -1,
                    LotTypeId = setLotTypeId,
                    ProcessId = matrix.ProcessId,
                    LotDate = now,
                    LotExpiryDate = expiry,
                    Status = 1,
                    Version = 0,
                    TenantId = login.ClientId,
                    CreatedById = login.UserId,
                    CreatedOn = now,
                    ModifiedById = login.UserId,
                    ModifiedOn = now,
                    AllocationId = matrix.AllocationId
                });

                outputList.Add(new MatrixOutputDTO
                {
                    MatrixOutputId = setOutputId,
                    MatrixId = matrix.MatrixId,
                    SlNo = ++slNo,
                    OutputLevel = 0,
                    ItemId = matrix.FgItemId != -1 ? matrix.FgItemId : -1,
                    SkuId = matrix.FgSkuId != -1 ? matrix.FgSkuId : -1,
                    LotId = setLotId,
                    LotQuantity = set,
                    TenantId = login.ClientId
                });

                // TLOTDETAIL for SET lot
                tlotDetailList.Add(new TlotDetailInsertDTO
                {
                    LotDetailId = detailSeq++,
                    BizTransactionTypeId = bizTxTypeLotProduction,
                    ObjectHeaderTypeId = entityTMatrix,
                    ObjectHeaderId = matrix.MatrixId,
                    ObjectTypeId = entityTMatrixOutput,
                    ObjectId = setOutputId,
                    SlNo = 1,
                    LotId = setLotId,
                    Quantity = 1,
                    GoodQuantity = 1,
                    RejectedQuantity = 0,
                    ReworkQuantity = 0,
                    OtherQuantity = 0,
                    PackId = -1,
                    PackQuantity = 0,
                    OuId = login.WorkOUId,
                    StoreId = login.WorkStoreId,
                    StockLedgerNumber = matrix.MatrixCode,
                    StockLedgerDate = now,
                    ReferenceNumber = matrix.SoNumber ?? matrix.MatrixCode,
                    ReferenceDate = now,
                    LocationType = 0,
                    PartyBranchId = -1,
                    StockPostType = 0,
                    MaterialOwnershipType = 0,
                    UsedInLotId = -1,
                    ReasonId = -1,
                    MarkedGoodQuantity = 0,
                    PlannedQuantity = 1,
                    BinId = -1,
                    ItemPostedCost = 0,
                    D1 = 0,
                    D2 = 0,
                    D3 = 0,
                    D4 = 0,
                    D5 = 0,
                    AllocationId = matrix.AllocationId
                });

                // ── FG serial lots ────────────────────────────────────────────
                foreach (var bomItem in bomItems)
                {
                    int fgLotId = lotSeq++;
                    int fgOutputId = outputSeq++;
                    string fgLotNum = FormatLotNumber(lotFormat, nextSeq++);

                    tlotList.Add(new TlotInsertDTO
                    {
                        LotId = fgLotId,
                        LotNumber = fgLotNum,
                        ItemId = bomItem.ItemId,
                        SkuId = bomItem.SkuId,
                        LotTypeId = fgSerialLotTypeId,
                        ProcessId = matrix.ProcessId,
                        LotDate = now,
                        LotExpiryDate = expiry,
                        Status = 1,
                        Version = 0,
                        TenantId = login.ClientId,
                        CreatedById = login.UserId,
                        CreatedOn = now,
                        ModifiedById = login.UserId,
                        ModifiedOn = now,
                        AllocationId = matrix.AllocationId
                    });

                    outputList.Add(new MatrixOutputDTO
                    {
                        MatrixOutputId = fgOutputId,
                        MatrixId = matrix.MatrixId,
                        SlNo = ++slNo,
                        OutputLevel = 1,
                        ItemId = bomItem.ItemId,
                        SkuId = bomItem.SkuId,
                        LotId = fgLotId,
                        LotQuantity = 1,
                        TenantId = login.ClientId
                    });

                    // TLOTDETAIL for FG serial lot
                    tlotDetailList.Add(new TlotDetailInsertDTO
                    {
                        LotDetailId = detailSeq++,
                        BizTransactionTypeId = bizTxTypeLotProduction,
                        ObjectHeaderTypeId = entityTMatrix,
                        ObjectHeaderId = matrix.MatrixId,
                        ObjectTypeId = entityTMatrixOutput,
                        ObjectId = fgOutputId,
                        SlNo = 1,
                        LotId = fgLotId,
                        Quantity = 1,
                        GoodQuantity = 1,
                        RejectedQuantity = 0,
                        ReworkQuantity = 0,
                        OtherQuantity = 0,
                        PackId = -1,
                        PackQuantity = 0,
                        OuId = login.WorkOUId,
                        StoreId = login.WorkStoreId,
                        StockLedgerNumber = matrix.MatrixCode,
                        StockLedgerDate = now,
                        ReferenceNumber = matrix.SoNumber ?? matrix.MatrixCode,
                        ReferenceDate = now,
                        LocationType = 0,
                        PartyBranchId = -1,
                        StockPostType = 0,
                        MaterialOwnershipType = 0,
                        UsedInLotId = -1,
                        ReasonId = -1,
                        MarkedGoodQuantity = 0,
                        PlannedQuantity = 1,
                        BinId = -1,
                        ItemPostedCost = 0,
                        D1 = 0,
                        D2 = 0,
                        D3 = 0,
                        D4 = 0,
                        D5 = 0,
                        AllocationId = matrix.AllocationId
                    });
                }
            }

            return (tlotList, tlotDetailList, outputList, setCount, serialsPerSet);
        }

        // ── FormatLotNumber ───────────────────────────────────────────────────
        private static string FormatLotNumber(LotNumberFormat format, int sequenceNumber)
        {
            int prefixLen = format.Prefix?.Length ?? 0;
            int suffixLen = format.Suffix?.Length ?? 0;
            int seqDigits = format.LotNumberSize - prefixLen - suffixLen;
            if (seqDigits < 1) seqDigits = 5;
            return $"{format.Prefix}{sequenceNumber.ToString().PadLeft(seqDigits, '0')}{format.Suffix}";
        }

        // ── Supersede ─────────────────────────────────────────────────────────
        public async Task<string> SupersedeMatrixAsync(
            int oldMatrixId, MatrixDTO newDto, LoginDTO login, CancellationToken ct = default)
        {
            if (newDto is null) throw new ArgumentNullException(nameof(newDto));

            var oldMatrix = await _matrixDAL.GetMatrixAsync(oldMatrixId, login, ct).ConfigureAwait(false);
            if (oldMatrix is null) return "Matrix not found.";
            if (oldMatrix.MatrixStatus != 1) return "Only Active matrices can be superseded.";
            if (newDto.MatrixMode != oldMatrix.MatrixMode) return $"New matrix mode must match the original matrix mode ({oldMatrix.MatrixMode}).";
            if (string.IsNullOrWhiteSpace(newDto.MatrixCode)) return "New matrix code is required.";
            if (newDto.MatrixMode != 2 && newDto.SetFrom > newDto.SetTo) return "Set From cannot be greater than Set To.";
            if (newDto.MatrixMode == 1 && (newDto.FgItemId == 0 || newDto.FgItemId < -1)) return "FG Item is required for FGItem mode.";
            if (newDto.MatrixMode == 2 && (newDto.ProcessGroupId == 0 || newDto.ProcessGroupId < -1)) return "Process Group is required for OpenMix mode.";

            int dupCount = await _matrixDAL.CheckDuplicateCodeAsync(newDto.MatrixCode, 0, login, ct).ConfigureAwait(false);
            if (dupCount > 0) return $"Matrix code '{newDto.MatrixCode}' is already in use.";

            if (newDto.MatrixMode != 2)
            {
                bool overlap = await _matrixDAL.HasSetRangeOverlapAsync(oldMatrix.AllocationId, newDto.MatrixMode, newDto.SetFrom, newDto.SetTo, oldMatrixId, login, ct).ConfigureAwait(false);
                if (overlap) return $"Set range {newDto.SetFrom}-{newDto.SetTo} overlaps with another active matrix.";
            }

            var trans = await _queryExecutor.BeginTransactionAsync(login);
            try
            {
                newDto.AllocationId = oldMatrix.AllocationId;
                newDto.TenantId = oldMatrix.TenantId;
                newDto.MatrixStatus = 0;
                newDto.SupersededById = -1;
                newDto.Version = 0;
                newDto.AllocationSlNo = await _matrixDAL.GetNextAllocationSlNoAsync(oldMatrix.AllocationId, login, ct).ConfigureAwait(false);

                int inputCount = newDto.Inputs?.Count ?? 0;
                var auto = await _autoNumber.GetNumberAsync(1 + inputCount, AUTONUMBERCONSTANT.MATRIX, login).ConfigureAwait(false);
                newDto.MatrixId = auto.StartNumber;

                if (inputCount > 0)
                {
                    int offset = 1;
                    foreach (var input in newDto.Inputs!)
                    {
                        input.MatrixInputId = auto.StartNumber + offset;
                        input.MatrixId = newDto.MatrixId;
                        input.SlNo = (short)offset++;
                        input.TenantId = oldMatrix.TenantId;
                        input.LotId = input.LotId == 0 ? -1 : input.LotId;
                        input.LineStatus = 0;
                        input.ConsumedQty = 0;
                        input.AllocatedQty = 0;
                        if (newDto.MatrixMode != 2) input.MixProportion = 0;
                    }
                }

                await _matrixDAL.SaveMatrixAsync(newDto, login, trans, ct).ConfigureAwait(false);
                if (inputCount > 0)
                    await _matrixDAL.BulkSaveMatrixInputsAsync(newDto.Inputs!, newDto.MatrixId, login, trans, ct).ConfigureAwait(false);
                await _matrixDAL.SupersedeMatrixAsync(oldMatrixId, newDto.MatrixId, login, trans, ct).ConfigureAwait(false);
                await _queryExecutor.CommitAsync(trans);

                try { var ok = new CacheKeyGeneration().KeyGeneration(oldMatrixId, EntityConstant.OBJECTMATRIX, CacheKeyLevel.CLIENT_LEVEL, login); await _keyInvalidate.AllInvalidateCache(ok).ConfigureAwait(false); } catch (Exception ex) { _logger.LogWarning(ex, "Cache invalidation failed for old MatrixId {Id}.", oldMatrixId); }
                try { var nk = new CacheKeyGeneration().KeyGeneration(newDto.MatrixId, EntityConstant.OBJECTMATRIX, CacheKeyLevel.CLIENT_LEVEL, login); await _keyInvalidate.AllInvalidateCache(nk).ConfigureAwait(false); } catch (Exception ex) { _logger.LogWarning(ex, "Cache invalidation failed for new MatrixId {Id}.", newDto.MatrixId); }

                return $"Matrix superseded successfully. New MatrixId = {newDto.MatrixId}.";
            }
            catch { await _queryExecutor.RollbackAsync(trans); throw; }
        }

        // ── ExplodeBOM ────────────────────────────────────────────────────────
        public async Task<string> ExplodeBOMAsync(
            int itemId, int setFrom, int setTo, LoginDTO login, CancellationToken ct = default,
            int? allotedAllocationId = null)
        {
            // Optional — when the caller sends AllotedAllocationId, validate SetFrom/
            // SetTo against the sets actually generated for it (via GetGeneratedSets),
            // instead of silently accepting an out-of-range value and returning a
            // plausible-looking result for sets that don't exist. Omit entirely to
            // keep the old raw-ItemId/SetFrom/SetTo behavior unvalidated.
            if (allotedAllocationId.HasValue)
            {
                var rows = await _matrixDAL
                    .GetGeneratedSetLotsAsync(allotedAllocationId.Value, login, ct)
                    .ConfigureAwait(false);

                var generatedSets = rows
                    .Select(r => ExtractLastRun(r.LotNumber))
                    .Where(n => n > 0)
                    .Distinct()
                    .ToList();

                if (generatedSets.Count == 0)
                    throw new InvalidOperationException(
                        $"No sets have been generated yet for AllotedAllocationId {allotedAllocationId.Value}.");

                var missing = Enumerable.Range(setFrom, setTo - setFrom + 1)
                    .Where(n => !generatedSets.Contains(n))
                    .ToList();
                if (missing.Count > 0)
                    throw new InvalidOperationException(
                        $"Set(s) {string.Join(", ", missing)} were not generated for AllotedAllocationId " +
                        $"{allotedAllocationId.Value} (generated: {string.Join(", ", generatedSets.OrderBy(n => n))}).");
            }

            int plannedQty = Math.Max(setTo - setFrom + 1, 1);
            var inputTask = _matrixDAL.ExplodeBOMInputsAsync(itemId, plannedQty, login, ct);
            var outputTask = _matrixDAL.ExplodeBOMOutputsAsync(itemId, plannedQty, login, ct);
            await Task.WhenAll(inputTask, outputTask).ConfigureAwait(false);
            return JsonConvert.SerializeObject(
                new { Input = inputTask.Result.ToList(), Output = outputTask.Result.ToList() },
                new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore });
        }

        // ── GetAvailableBatches ───────────────────────────────────────────────
        public async Task<string> GetAvailableBatchesAsync(
            int itemId, int skuId, decimal? requiredQty, LoginDTO login, CancellationToken ct = default)
        {
            try { return JsonConvert.SerializeObject(await _matrixDAL.GetAvailableBatchesAsync(itemId, skuId, requiredQty, login, ct).ConfigureAwait(false)); }
            catch { throw; }
        }

        // ── ValidateIssue ─────────────────────────────────────────────────────
        public async Task<ValidateIssueResultDTO> ValidateIssueAsync(
            int indentId, int itemId, int lotId, decimal qty, LoginDTO login, CancellationToken ct = default)
        {
            if (login == null) throw new ArgumentNullException(nameof(login));
            if (qty <= 0) return ValidateIssueResultDTO.Block("Issue quantity must be greater than zero.", 0, 0, 0, qty);

            var matrixId = await _matrixDAL.GetMatrixIdFromIndentAsync(indentId, login, ct).ConfigureAwait(false);
            if (matrixId == 0) return ValidateIssueResultDTO.Allow("No matrix restriction found. Issue is allowed.", 0, 0, 0, qty);

            var input = await _matrixDAL.GetMatrixInputForItemAsync(matrixId, itemId, login, ct).ConfigureAwait(false);
            if (input == null) return ValidateIssueResultDTO.Allow("Item is not controlled by matrix. Issue is allowed.", 0, 0, 0, qty);
            if (input.ItemCategory != 0) return ValidateIssueResultDTO.Allow("Non-controlled item. Issue is allowed.", input.AllocatedQty, input.ConsumedQty, input.AllocatedQty - input.ConsumedQty, qty);

            if (input.LotId != -1 && input.LotId != lotId)
            {
                var batchName = string.IsNullOrWhiteSpace(input.BatchNo) ? $"Lot #{input.LotId}" : input.BatchNo;
                return ValidateIssueResultDTO.Block($"Invalid batch selected. Please use '{batchName}'.", input.AllocatedQty, input.ConsumedQty, input.AllocatedQty - input.ConsumedQty, qty);
            }

            var allocated = input.AllocatedQty;
            var consumed = input.ConsumedQty;
            var available = allocated - consumed;

            if (available <= 0) return ValidateIssueResultDTO.Block("No quantity available for issue. Allocation is fully consumed.", allocated, consumed, available, qty);
            if (qty > available) return ValidateIssueResultDTO.Block("Insufficient quantity available for issue.", allocated, consumed, available, qty);

            return ValidateIssueResultDTO.Allow("Issue is allowed. You can proceed.", allocated, consumed, available, qty);
        }

        // ── GetSelectListMatrix ───────────────────────────────────────────────
        public async Task<string> GetSelectListMatrix(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
            => await _matrixDAL.GetSelectListMatrix(criteriaDTO, login, ct);

        // ── GetSelectListAllocationWithItem ───────────────────────────────────
        public async Task<string> GetSelectListAllocationWithItem(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
            => await _matrixDAL.GetSelectListAllocationWithItem(criteriaDTO, login, ct);

        // ── GetMatrixPreview ──────────────────────────────────────────────────
        public async Task<MatrixPreviewDTO> GetMatrixPreviewAsync(
            int matrixId, LoginDTO login, CancellationToken ct = default)
        {
            var matrix = await _matrixDAL.GetMatrixAsync(matrixId, login, ct).ConfigureAwait(false)
                ?? throw new InvalidOperationException($"Matrix {matrixId} not found.");

            int setsOnSO = matrix.SetTo - matrix.SetFrom + 1;
            var (alreadyGenerated, _) = await _matrixDAL
                .GetMatrixOutputCountsAsync(matrixId, login, ct).ConfigureAwait(false);
            int remaining = setsOnSO - alreadyGenerated;

            var bomItems = await ExplodeForActivationAsync(matrix, login, ct).ConfigureAwait(false);
            var existingOutputs = alreadyGenerated > 0
                ? (await _matrixDAL.GetMatrixOutputsAsync(matrixId, login, ct)
                    .ConfigureAwait(false))?.ToList() ?? []
                : [];

            var fgItems = bomItems.Select((bom, idx) =>
            {
                int qtyPerSet = 1;
                int thisRun = remaining * qtyPerSet;

                string nextSerialFrom;
                if (alreadyGenerated > 0)
                {
                    int existingCount = existingOutputs
                        .Count(o => o.OutputLevel == 1 && o.ItemId == bom.ItemId);
                    nextSerialFrom = existingCount > 0
                        ? $"Serial #{existingCount + 1} onwards"
                        : "First serial";
                }
                else
                {
                    nextSerialFrom = "First serial";
                }

                string serialsThisRun = thisRun > 0
                    ? $"{nextSerialFrom} ({thisRun} serials)"
                    : "All sets generated";

                return new FGItemPreviewDTO
                {
                    RowNumber = idx + 1,
                    FGItemName = bom.ItemName ?? string.Empty,
                    FGItemCode = bom.ItemCode ?? string.Empty,
                    QtyPerSet = qtyPerSet,
                    ThisRun = thisRun,
                    NextSerialFrom = nextSerialFrom,
                    SerialsThisRun = serialsThisRun
                };
            }).ToList();

            int totalSerials = fgItems.Sum(x => x.ThisRun);
            int setFrom = remaining > 0 ? matrix.SetFrom + alreadyGenerated : matrix.SetFrom;
            int setTo = remaining > 0 ? setFrom + remaining - 1 : matrix.SetTo;

            return new MatrixPreviewDTO
            {
                SetsOnSO = setsOnSO,
                AlreadyGenerated = alreadyGenerated,
                Remaining = remaining,
                TotalSerials = totalSerials,
                SetRange = remaining > 0
                    ? $"Sets {setFrom}–{setTo}"
                    : "All sets generated",
                Summary = remaining > 0
                    ? $"Sets {setFrom}–{setTo} · {totalSerials} serials across {fgItems.Count} FG types"
                    : $"All {setsOnSO} sets already generated",
                FGItems = fgItems
            };
        }

        // ── GetMatrixSummary ──────────────────────────────────────────────────
        public async Task<MatrixSummaryDTO?> GetMatrixSummaryAsync(
            int allotedAllocationId, LoginDTO login, CancellationToken ct = default)
        {
            if (allotedAllocationId == 0 || allotedAllocationId == -1)
                return null;

            return await _matrixDAL
                .GetMatrixSummaryAsync(allotedAllocationId, login, ct)
                .ConfigureAwait(false);
        }

        // ── GetAllocationItemSummary — Matrix-independent summary card ─────────
        // Sourced only from TPENDINGALLOCATION/TALLOCATION/MALLOCATION/MPARTY/MITEM
        // (allocation + item + party facts) and TLOT (sets generated). Does NOT
        // touch TMATRIX / TMATRIXINPUT / TMATRIXOUTPUT.
        //
        // SetsGenerated is a direct count of real SET-level TLOT rows — one per
        // set, ItemId = the FG item itself (see SaveSetSerialGenerationAsync),
        // scoped precisely to THIS alloted-allocation line via TLOTDETAIL.OBJECTHEADERID
        // (not TLOT.ALLOCATIONID, which is the SO-level HeaderAllocationId shared
        // by every FG item line under that SO). Fully dynamic — reflects live DB
        // state regardless of how many separate save calls (1, then 6, then 3, or
        // all 10 at once) built up to that state.
        private async Task<MatrixAllocationSummaryDTO?> GetAllocationItemSummaryAsync(
            int allotedAllocationId, LoginDTO login, CancellationToken ct)
        {
            var summary = await _matrixDAL.GetAllocationItemSummaryAsync(allotedAllocationId, login, ct).ConfigureAwait(false);
            if (summary is null) return null;

            summary.SetsGenerated = await _matrixDAL
                .GetSetsGeneratedCountForAllotedAllocationAsync(summary.ItemId, allotedAllocationId, login, ct)
                .ConfigureAwait(false);

            return summary;
        }

        // ── GetMatrixAllocationPreview — combines Summary + Composition ────────
        // Single call for the UI: caller passes AllotedAllocationId only (e.g. from
        // GetSelectListAllocationWithItem). Matrix-independent end to end.
        public async Task<MatrixAllocationPreviewDTO?> GetMatrixAllocationPreviewAsync(
            int allotedAllocationId, LoginDTO login, CancellationToken ct = default)
        {
            var summary = await GetAllocationItemSummaryAsync(allotedAllocationId, login, ct).ConfigureAwait(false);
            if (summary is null)
                return null;

            var composition = await BuildEnrichedCompositionPreviewAsync(
                summary, summary.Remaining, login, ct).ConfigureAwait(false);

            return new MatrixAllocationPreviewDTO
            {
                Summary = summary,
                Composition = composition
            };
        }

        // ── GetMatrixCompositionPreview — standalone, caller-supplied set count ─
        // Used by the "Generate Now" input: recomputes the Product Composition
        // dashboard for a candidate SetsToGenerate WITHOUT saving anything.
        public async Task<MatrixCompositionDTO?> GetMatrixCompositionPreviewAsync(
            int allotedAllocationId, int? setsToGenerate, LoginDTO login, CancellationToken ct = default)
        {
            var summary = await GetAllocationItemSummaryAsync(allotedAllocationId, login, ct).ConfigureAwait(false);
            if (summary is null)
                return null;

            int sets = setsToGenerate ?? summary.Remaining;
            return await BuildEnrichedCompositionPreviewAsync(summary, sets, login, ct).ConfigureAwait(false);
        }

        // ── BuildEnrichedCompositionPreviewAsync ───────────────────────────────
        // Shared by every caller of the composition preview — adds the header-level
        // fields (SONumber/Product/SetRange/SerialNumbers/TotalRecords) on top of
        // BuildCompositionPreviewAsync's per-BOM-component rows, so no caller can
        // forget to populate them (as GetMatrixAllocationPreviewAsync previously did).
        private async Task<MatrixCompositionDTO> BuildEnrichedCompositionPreviewAsync(
            MatrixAllocationSummaryDTO summary, int sets, LoginDTO login, CancellationToken ct)
        {
            var composition = await BuildCompositionPreviewAsync(summary.ItemId, sets, login, ct).ConfigureAwait(false);

            int setFrom = summary.SetsGenerated + 1;
            int setTo = setFrom + sets - 1;

            composition.SONumber = summary.AllocationName;
            composition.ProductCode = summary.ItemCode;
            composition.ProductName = summary.ItemName;
            composition.SetRange = sets > 0 ? $"Sets {setFrom}–{setTo}" : string.Empty;
            composition.SerialNumbers = (int)Math.Round(
                composition.Items.Sum(i => i.ThisRun), MidpointRounding.AwayFromZero);
            composition.TotalRecords = composition.SerialNumbers;

            return composition;
        }

        // ── BuildCompositionPreviewAsync ───────────────────────────────────────
        // Product Composition dashboard — sourced only from MBOM/MBOMDETAIL
        // (recipe), TLOT/TLOTDETAIL (stock + last-run history) and TRESERVATION
        // (reserved qty). Read-only, no DB writes. Each item's LastRun is its
        // OWN last sequence number — no shared/global sequence prediction
        // (that proved unreliable against real data and isn't needed here).
        private async Task<MatrixCompositionDTO> BuildCompositionPreviewAsync(
            int fgItemId, int setsToGenerate, LoginDTO login, CancellationToken ct)
        {
            int sets = Math.Max(setsToGenerate, 0);

            // Serials are generated against the manufactured FG output items
            // (BOMLEVEL = 0), not the raw-material inputs consumed to build
            // them — same items shown on the old Matrix Planner's Output tab.
            var bomRows = await _matrixDAL.ExplodeBOMOutputsAsync(fgItemId, 1, login, ct).ConfigureAwait(false);
            var bomItems = (bomRows ?? Enumerable.Empty<ExplodeBOMOutputDTO>())
                .GroupBy(r => r.ItemId)
                .Select(g => g.First())
                .ToList();

            if (bomItems.Count == 0)
                return new MatrixCompositionDTO { SetsToGenerate = sets, Items = [] };

            var itemIds = bomItems.Select(b => b.ItemId).ToList();

            var stockRows = (await _matrixDAL
                .GetStockAndLastSerialForItemsAsync(itemIds, login, ct)
                .ConfigureAwait(false))
                .ToDictionary(s => s.ItemId);

            var reservedRows = (await _matrixDAL
                .GetReservedQtyForItemsAsync(itemIds, login, ct)
                .ConfigureAwait(false))
                .ToDictionary(r => r.ItemId);

            var items = bomItems.Select((bom, idx) =>
            {
                stockRows.TryGetValue(bom.ItemId, out var stock);
                reservedRows.TryGetValue(bom.ItemId, out var reservation);

                decimal qtyPerSet = bom.RequiredQty;
                decimal totalQuantity = stock?.TotalQuantity ?? 0;
                decimal reservedQuantity = reservation?.ReservedQuantity ?? 0;
                decimal thisRun = qtyPerSet * sets;

                int lastRun = ExtractLastRun(stock?.LastSerialNo);
                // Serial run count = ThisRun (QtyPerSet × Sets) — one serial per physical
                // unit of this component, not one per set.
                int runCount = (int)Math.Round(thisRun, MidpointRounding.AwayFromZero);

                return new MatrixCompositionItemDTO
                {
                    RowNumber = idx + 1,
                    ItemId = bom.ItemId,
                    ItemCode = bom.ItemCode ?? string.Empty,
                    ItemName = bom.ItemName ?? string.Empty,
                    UomCode = bom.UomCode ?? string.Empty,
                    TotalQuantity = totalQuantity,
                    ReservedQuantity = reservedQuantity,
                    AvailableQuantity = totalQuantity - reservedQuantity,
                    QtyPerSet = qtyPerSet,
                    ThisRun = thisRun,
                    LastRun = lastRun,
                    NextSerialFrom = runCount > 0 ? lastRun + 1 : 0,
                    NextSerialTo = runCount > 0 ? lastRun + runCount : 0
                };
            }).ToList();

            return new MatrixCompositionDTO { SetsToGenerate = sets, Items = items };
        }

        // ── ExtractLastRun ──────────────────────────────────────────────────────
        // Pulls the numeric run out of a "{ItemCode}-{run:D4}" lot number (e.g.
        // "RMRE0009-0066" → 66). Anchored to the END of the string — the item
        // code itself often embeds its own digits (e.g. "RMRE0009"), and a
        // first-match regex would incorrectly pick those up instead of the
        // trailing run, silently restarting the sequence and colliding with an
        // already-saved lot number (UKTLOT_ITEMID_SKUID_LOTNUMBER_LOTTYPE).
        // Returns 0 for null/empty input or lot numbers with no readable digit
        // run (e.g. corrupted values like "NA0000NaN/2627").
        private static int ExtractLastRun(string? lotNumber)
        {
            if (string.IsNullOrWhiteSpace(lotNumber)) return 0;
            var match = System.Text.RegularExpressions.Regex.Match(lotNumber, @"(\d+)\s*$");
            return match.Success && int.TryParse(match.Value, out var run) ? run : 0;
        }

        // ── Private helpers ───────────────────────────────────────────────────
        // ── ResolveLotFormatAsync ───────────────────────────────────────────────
        private async Task<(LotNumberFormat Format, int SetLotTypeId, int FgSerialLotTypeId)> ResolveLotFormatAsync(
            LoginDTO login, CancellationToken ct)
        {
            var lotTypeRows = await _queryExecutor
                .QueryAsync<LotTypeRow>(login, MatrixQB.GET_LOT_TYPE_IDS,
                    new { TenantId = login.ClientId }, cancellationToken: ct)
                .ConfigureAwait(false);

            var lotTypeList = lotTypeRows?.ToList() ?? [];
            var lotTypeMap = lotTypeList
                .Where(r => !string.IsNullOrWhiteSpace(r.SystemPrefix))
                .GroupBy(r => r.SystemPrefix, StringComparer.OrdinalIgnoreCase)
                .ToDictionary(g => g.Key, g => g.First(), StringComparer.OrdinalIgnoreCase);

            lotTypeMap.TryGetValue("SET", out var setLotType);
            lotTypeMap.TryGetValue("FGSERIAL", out var fgSerialLotType);

            int setLotTypeId = setLotType?.LotTypeId ?? -1;
            int fgSerialLotTypeId = fgSerialLotType?.LotTypeId ?? -1;

            var formatRow = lotTypeList.FirstOrDefault(r =>
                !string.IsNullOrEmpty(r.Prefix) && r.LotNumberSize > 0);

            var lotFormat = new LotNumberFormat
            {
                Prefix = formatRow?.Prefix ?? "ML",
                Suffix = formatRow?.Suffix ?? string.Empty,
                FromNumber = formatRow?.FromNumber ?? 1,
                LotNumberSize = formatRow?.LotNumberSize ?? 12
            };

            return (lotFormat, setLotTypeId, fgSerialLotTypeId);
        }
        private sealed class LotTypeRow
        {
            public int LotTypeId { get; set; }
            public string SystemPrefix { get; set; } = string.Empty;
            public string? Prefix { get; set; }
            public string? Suffix { get; set; }
            public int FromNumber { get; set; } = 1;
            public int LotNumberSize { get; set; } = 12;
        }

        private sealed class LotNumberFormat
        {
            public string Prefix { get; set; } = "ML";
            public string Suffix { get; set; } = string.Empty;
            public int FromNumber { get; set; } = 1;
            public int LotNumberSize { get; set; } = 12;
        }

        // ── ExplodeForActivationAsync ─────────────────────────────────────────
        // Serials are generated against the manufactured FG output items
        // (BOMLEVEL = 0) — same items shown on Matrix Planner's Output tab —
        // not the raw-material inputs consumed to build them.
        private async Task<List<MatrixInputDTO>> ExplodeForActivationAsync(
            MatrixDTO matrix, LoginDTO login, CancellationToken ct)
        {
            var rows = await _matrixDAL
                .ExplodeBOMOutputsAsync(matrix.FgItemId, 1, login, ct)
                .ConfigureAwait(false);

            return (rows ?? Enumerable.Empty<ExplodeBOMOutputDTO>())
                .Select(r => new MatrixInputDTO
                {
                    ItemId = r.ItemId,
                    ItemCode = r.ItemCode,
                    ItemName = r.ItemName,
                    SkuId = r.SkuId,
                    SkuCode = r.SkuCode,
                    SkuName = r.SkuName,
                    RequiredQty = r.RequiredQty,
                    AllocatedQty = 1,
                    UomCode = r.UomCode,
                    LotId = -1
                })
                .GroupBy(r => r.ItemId)
                .Select(g => g.First())
                .ToList();
        }
    }
}