using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using Microsoft.Data.SqlClient;
using Newtonsoft.Json;
using QMSDAL.DTO;
using QMSDAL.Query.Matrix;
using System.Data;
using System.Data.Common;

namespace QMSDAL.CustomCode.Matrix
{
    public class MatrixDAL : IMatrixDAL
    {
        private readonly IQueryExecutor _qe;

        public MatrixDAL(IQueryExecutor queryExecutor)
        {
            _qe = queryExecutor;
        }

        // ── Read: all headers for tenant ──────────────────────────────────────
        public async Task<IEnumerable<MatrixDTO>> GetAllMatrixHeadersAsync(
            LoginDTO login, CancellationToken ct = default)
        {
            var result = await _qe
                .QueryAsync<MatrixDTO>(login, MatrixQB.GET_ALL_MATRIX_HEADERS,
                    new { TenantId = login.ClientId }, cancellationToken: ct)
                .ConfigureAwait(false);
            return result ?? Enumerable.Empty<MatrixDTO>();
        }

        // ── Read: all input lines for tenant ──────────────────────────────────
        public async Task<IEnumerable<MatrixInputDTO>> GetAllMatrixInputsAsync(
            LoginDTO login, CancellationToken ct = default)
        {
            var result = await _qe
                .QueryAsync<MatrixInputDTO>(login, MatrixQB.GET_ALL_MATRIX_INPUTS,
                    new { TenantId = login.ClientId }, cancellationToken: ct)
                .ConfigureAwait(false);
            return result ?? Enumerable.Empty<MatrixInputDTO>();
        }

        // ── Read: all output rows for tenant ──────────────────────────────────
        public async Task<IEnumerable<MatrixOutputDTO>> GetAllMatrixOutputsAsync(
            LoginDTO login, CancellationToken ct = default)
        {
            var result = await _qe
                .QueryAsync<MatrixOutputDTO>(login, MatrixQB.GET_ALL_MATRIX_OUTPUTS,
                    new { TenantId = login.ClientId }, cancellationToken: ct)
                .ConfigureAwait(false);
            return result ?? Enumerable.Empty<MatrixOutputDTO>();
        }

        // ── Read: single header ───────────────────────────────────────────────
        public async Task<MatrixDTO?> GetMatrixAsync(
            int matrixId, LoginDTO login, CancellationToken ct = default)
        {
            return await _qe
                .QuerySingleAsync<MatrixDTO>(login, MatrixQB.GET_MATRIX_HEADER,
                    new { MatrixId = matrixId, TenantId = login.ClientId })
                .ConfigureAwait(false);
        }

        // ── Read: all input lines for a matrix ────────────────────────────────
        public async Task<IEnumerable<MatrixInputDTO>> GetMatrixInputsAsync(
            int matrixId, LoginDTO login, CancellationToken ct = default)
        {
            var result = await _qe
                .QueryAsync<MatrixInputDTO>(login, MatrixQB.GET_MATRIX_INPUTS,
                    new { MatrixId = matrixId, TenantId = login.ClientId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
            return result ?? Enumerable.Empty<MatrixInputDTO>();
        }

        // ── Read: all output rows for a matrix ────────────────────────────────
        public async Task<IEnumerable<MatrixOutputDTO>> GetMatrixOutputsAsync(
            int matrixId, LoginDTO login, CancellationToken ct = default)
        {
            var result = await _qe
                .QueryAsync<MatrixOutputDTO>(login, MatrixQB.GET_MATRIX_OUTPUTS,
                    new { MatrixId = matrixId, TenantId = login.ClientId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
            return result ?? Enumerable.Empty<MatrixOutputDTO>();
        }

        // ── Read: output counts (Set lots + FGSerials) ────────────────────────
        private sealed class OutputLevelCount
        {
            public byte OutputLevel { get; set; }
            public int OutputCount { get; set; }
        }

        public async Task<(int Sets, int Serials)> GetMatrixOutputCountsAsync(
            int matrixId, LoginDTO login, CancellationToken ct = default)
        {
            var rows = await _qe
                .QueryAsync<OutputLevelCount>(login, MatrixQB.GET_MATRIX_OUTPUT_COUNTS,
                    new { MatrixId = matrixId, TenantId = login.ClientId },
                    cancellationToken: ct)
                .ConfigureAwait(false);

            int sets = 0, serials = 0;
            foreach (var row in rows ?? Enumerable.Empty<OutputLevelCount>())
            {
                switch (row.OutputLevel)
                {
                    case 0: sets = row.OutputCount; break;
                    case 1: serials = row.OutputCount; break;
                }
            }
            return (sets, serials);
        }

        // ── Validation: does AllocationId resolve to a real MALLOCATION row? ───
        public async Task<bool> AllocationExistsAsync(
            int allocationId, LoginDTO login, CancellationToken ct = default)
        {
            int count = await _qe
                .ExecuteScalarAsync<int>(login, MatrixQB.CHECK_ALLOCATION_EXISTS,
                    new { AllocationId = allocationId, TenantId = login.ClientId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
            return count > 0;
        }

        // ── Validation: duplicate matrix code ─────────────────────────────────
        public async Task<int> CheckDuplicateCodeAsync(
            string matrixCode, int excludeMatrixId, LoginDTO login,
            CancellationToken ct = default)
        {
            return await _qe
                .ExecuteScalarAsync<int>(login, MatrixQB.CHECK_DUPLICATE_CODE,
                    new { MatrixCode = matrixCode, ExcludeMatrixId = excludeMatrixId, TenantId = login.ClientId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
        }

        // ── Validation: set-range overlap ─────────────────────────────────────
        public async Task<bool> HasSetRangeOverlapAsync(
            int allocationId, byte matrixMode, short setFrom, short setTo,
            int excludeMatrixId, LoginDTO login, CancellationToken ct = default)
        {
            int count = await _qe
                .ExecuteScalarAsync<int>(login, MatrixQB.CHECK_SET_OVERLAP,
                    new { AllocationId = allocationId, MatrixMode = matrixMode, SetFrom = setFrom, SetTo = setTo, ExcludeMatrixId = excludeMatrixId, TenantId = login.ClientId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
            return count > 0;
        }

        // ── Read: next AllocationSlNo ─────────────────────────────────────────
        public async Task<short> GetNextAllocationSlNoAsync(
            int allocationId, LoginDTO login, CancellationToken ct = default)
        {
            int next = await _qe
                .ExecuteScalarAsync<int>(login, MatrixQB.GET_NEXT_ALLOCATION_SLNO,
                    new { AllocationId = allocationId, TenantId = login.ClientId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
            return (short)(next < 1 ? 1 : next);
        }

        // ── Validation: check if output lots already generated ────────────────
        public async Task<bool> HasOutputsGeneratedAsync(
            int matrixId, LoginDTO login, CancellationToken ct = default)
        {
            int flag = await _qe
                .ExecuteScalarAsync<int>(login, MatrixQB.GET_HAS_OUTPUTS,
                    new { MatrixId = matrixId, TenantId = login.ClientId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
            return flag == 1;
        }

        // ── Validation: stock shortage inputs ─────────────────────────────────
        public async Task<IEnumerable<MatrixInputDTO>> GetStockShortageInputsAsync(
            int matrixId, LoginDTO login, CancellationToken ct = default)
        {
            var result = await _qe
                .QueryAsync<MatrixInputDTO>(login, MatrixQB.CHECK_STOCK_POSITION,
                    new { MatrixId = matrixId, TenantId = login.ClientId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
            return result ?? Enumerable.Empty<MatrixInputDTO>();
        }

        // ── Read: available RM batches for item/SKU ───────────────────────────
        public async Task<IEnumerable<BatchStockDTO>> GetAvailableBatchesAsync(
            int itemId, int skuId, decimal? requiredQty,
            LoginDTO login, CancellationToken ct = default)
        {
            var result = await _qe
                .QueryAsync<BatchStockDTO>(login, MatrixQB.GET_AVAILABLE_BATCHES,
                    new { ItemId = itemId, SkuId = skuId, RequiredQty = requiredQty ?? 0m, TenantId = login.ClientId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
            return result ?? Enumerable.Empty<BatchStockDTO>();
        }

        // ── Read: MatrixId linked to an indent ────────────────────────────────
        public async Task<int> GetMatrixIdFromIndentAsync(
            int indentId, LoginDTO login, CancellationToken ct = default)
        {
            var matrixId = await _qe
                .ExecuteScalarAsync<int?>(login, MatrixQB.GET_MATRIXID_FROM_INDENT,
                    new { IndentId = indentId, TenantId = login.ClientId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
            return matrixId ?? 0;
        }

        // ── Read: single input line for a specific item ───────────────────────
        public async Task<MatrixInputDTO?> GetMatrixInputForItemAsync(
            int matrixId, int itemId, LoginDTO login, CancellationToken ct = default)
        {
            var result = await _qe
                .QueryAsync<MatrixInputDTO>(login, MatrixQB.GET_MATRIX_INPUT_FOR_ITEM,
                    new { MatrixId = matrixId, ItemId = itemId, TenantId = login.ClientId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
            return result.FirstOrDefault();
        }

        // ── Read: matrix summary by alloted allocation ────────────────────────
        public async Task<MatrixSummaryDTO?> GetMatrixSummaryAsync(
            int allotedAllocationId, LoginDTO login, CancellationToken ct = default)
        {
            var results = await _qe
                .QueryAsync<MatrixSummaryDTO>(login, MatrixQB.GET_MATRIX_SUMMARY,
                    new { AllotedAllocationId = allotedAllocationId, TenantId = login.ClientId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
            return results?.FirstOrDefault();
        }

        // ── Write: insert TMATRIX header ──────────────────────────────────────
        public async Task<Result<string>> SaveMatrixAsync(
            MatrixDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            dto.CreatedById = login.UserId; dto.CreatedOn = DateTime.UtcNow;
            dto.ModifiedById = login.UserId; dto.ModifiedOn = DateTime.UtcNow;
            dto.TenantId = login.ClientId;
            int rows = await _qe.ExecuteAsync(login, MatrixQB.SAVE_MATRIX, dto, tx, ct).ConfigureAwait(false);
            return rows > 0 ? Result<string>.Success("Matrix saved.") : Result<string>.Failure("Failed to save matrix.");
        }

        // ── Write: update TMATRIX header (Draft only) ─────────────────────────
        public async Task<Result<string>> UpdateMatrixAsync(
            MatrixDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            dto.ModifiedById = login.UserId; dto.ModifiedOn = DateTime.UtcNow; dto.TenantId = login.ClientId;
            int rows = await _qe.ExecuteAsync(login, MatrixQB.UPDATE_MATRIX, dto, tx, ct).ConfigureAwait(false);
            return rows > 0 ? Result<string>.Success("Matrix updated.") : Result<string>.Failure("Matrix not found or not in Draft status.");
        }

        // ── Write: delete all output rows (Mode 2 update path) ────────────────
        public async Task<Result<string>> DeleteMatrixOutputsAsync(
            int matrixId, LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            await _qe.ExecuteAsync(login, MatrixQB.DELETE_MATRIX_OUTPUTS,
                new { MatrixId = matrixId, TenantId = login.ClientId }, tx, ct).ConfigureAwait(false);
            return Result<string>.Success("Outputs cleared.");
        }

        // ── Write: delete all input lines ─────────────────────────────────────
        public async Task<Result<string>> DeleteMatrixInputsAsync(
            int matrixId, LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            await _qe.ExecuteAsync(login, MatrixQB.DELETE_MATRIX_INPUTS,
                new { MatrixId = matrixId, TenantId = login.ClientId }, tx, ct).ConfigureAwait(false);
            return Result<string>.Success("Inputs cleared.");
        }

        // ── Write: bulk-insert TMATRIXINPUT rows ──────────────────────────────
        public async Task<Result<string>> BulkSaveMatrixInputsAsync(
            IList<MatrixInputDTO> inputs, int matrixId,
            LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            if (inputs == null || inputs.Count == 0)
                return Result<string>.Success("No inputs to save.");

            foreach (var input in inputs)
            {
                input.MatrixId = matrixId;
                input.TenantId = login.ClientId;
                // 0 means "unset" (FE default); a real LotId is a large negative
                // sequence value in this schema, so only 0 normalizes to -1/NONE —
                // matches the equivalent MatrixBLL.SaveMatrixAsync normalization.
                if (input.LotId == 0) input.LotId = -1;
            }

            int rows = await _qe.BulkInsertAsync(login, MatrixQB.SAVE_MATRIX_INPUT, inputs, tx).ConfigureAwait(false);
            return Result<string>.Success($"{rows} input(s) saved.");
        }

        // ── Write: soft-delete TMATRIX (Draft only) ───────────────────────────
        public async Task<Result<string>> SoftDeleteMatrixAsync(
            int matrixId, LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            int rows = await _qe.ExecuteAsync(login, MatrixQB.SOFT_DELETE_MATRIX,
                new { MatrixId = matrixId, TenantId = login.ClientId, ModifiedById = login.UserId, ModifiedOn = DateTime.UtcNow },
                tx, ct).ConfigureAwait(false);
            return rows > 0 ? Result<string>.Success("Matrix deleted.") : Result<string>.Failure("Matrix not found or not in Draft status.");
        }

        // ── Write: mark old matrix as Superseded ──────────────────────────────
        public async Task<Result<string>> SupersedeMatrixAsync(
            int oldMatrixId, int newMatrixId, LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            int rows = await _qe.ExecuteAsync(login, MatrixQB.SUPERSEDE_MATRIX,
                new { OldMatrixId = oldMatrixId, NewMatrixId = newMatrixId, TenantId = login.ClientId, ModifiedById = login.UserId, ModifiedOn = DateTime.UtcNow },
                tx, ct).ConfigureAwait(false);
            return rows > 0 ? Result<string>.Success("Matrix superseded.") : Result<string>.Failure("Matrix not found or already superseded.");
        }

        // ── Write: update OpenMix input statuses after activation ─────────────
        public async Task<Result<string>> ActivateOpenMixInputsAsync(
            int matrixId, LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            await _qe.ExecuteAsync(login, MatrixQB.ACTIVATE_OPENMIX_INPUTS,
                new { MatrixId = matrixId, TenantId = login.ClientId }, tx, ct).ConfigureAwait(false);
            return Result<string>.Success("OpenMix input statuses updated.");
        }

        // ── Write: activate matrix (MATRIXSTATUS = 1) ─────────────────────────
        public async Task<Result<string>> ActivateMatrixAsync(
            int matrixId, LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            int rows = await _qe.ExecuteAsync(login, MatrixQB.ACTIVATE_MATRIX,
                new { MatrixId = matrixId, TenantId = login.ClientId, ActivatedOn = DateTime.UtcNow, ModifiedById = login.UserId, ModifiedOn = DateTime.UtcNow },
                tx, ct).ConfigureAwait(false);
            return rows > 0 ? Result<string>.Success("Matrix activated.") : Result<string>.Failure("Matrix not found or already active.");
        }

        // ── Write: bulk-insert TLOT rows (activation step) ────────────────────
        // Uses SqlBulkCopy instead of IQueryExecutor.BulkInsertAsync (which issues
        // one INSERT round-trip per row via Dapper) — matrix/serial generation can
        // produce hundreds of rows per run, and row-by-row inserts were the main
        // cause of slow saves. Scoped to this DAL only; the shared BulkInsertAsync
        // helper is used by 30+ other call sites across the solution and is left
        // untouched here.
        public async Task<Result<string>> BulkSaveTlotRowsAsync(
            IEnumerable<TlotInsertDTO> lots, LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            var list = lots?.ToList() ?? [];
            if (list.Count == 0) return Result<string>.Success("No lots to create.");

            using var table = new DataTable();
            table.Columns.Add("LOTID", typeof(int));
            table.Columns.Add("LOTNUMBER", typeof(string));
            table.Columns.Add("ITEMID", typeof(int));
            table.Columns.Add("SKUID", typeof(int));
            table.Columns.Add("LOTTYPEID", typeof(int));
            table.Columns.Add("PROCESSID", typeof(int));
            table.Columns.Add("LOTDATE", typeof(DateTime));
            table.Columns.Add("LOTEXPIRYDATE", typeof(DateTime));
            table.Columns.Add("STATUS", typeof(byte));
            table.Columns.Add("VERSION", typeof(short));
            table.Columns.Add("CREATEDBYID", typeof(int));
            table.Columns.Add("CREATEDON", typeof(DateTime));
            table.Columns.Add("MODIFIEDBYID", typeof(int));
            table.Columns.Add("MODIFIEDON", typeof(DateTime));
            table.Columns.Add("ALLOCATIONID", typeof(int));

            foreach (var lot in list)
            {
                table.Rows.Add(
                    lot.LotId, lot.LotNumber, lot.ItemId, lot.SkuId, lot.LotTypeId, lot.ProcessId,
                    lot.LotDate, lot.LotExpiryDate, lot.Status, lot.Version,
                    lot.CreatedById, lot.CreatedOn, lot.ModifiedById, lot.ModifiedOn, lot.AllocationId);
            }

            await BulkCopyTableAsync(table, "TLOT", tx, ct).ConfigureAwait(false);
            return Result<string>.Success($"{list.Count} lot(s) created.");
        }

        // ── Shared SqlBulkCopy runner for this DAL's scoped bulk-insert paths ──
        private static async Task BulkCopyTableAsync(
            DataTable table, string destinationTable, DbTransaction tx, CancellationToken ct)
        {
            if (tx.Connection is not SqlConnection sqlConnection || tx is not SqlTransaction sqlTransaction)
                throw new InvalidOperationException(
                    $"BulkCopyTableAsync requires a SqlConnection/SqlTransaction (got {tx.Connection?.GetType().Name}).");

            using var bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default, sqlTransaction)
            {
                DestinationTableName = destinationTable,
                BulkCopyTimeout = 120
            };
            foreach (DataColumn col in table.Columns)
                bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);

            await bulkCopy.WriteToServerAsync(table, ct).ConfigureAwait(false);
        }

        // ── Write: bulk-insert TMATRIXOUTPUT rows (activation step) ──────────
        public async Task<Result<string>> BulkSaveMatrixOutputsAsync(
            IEnumerable<MatrixOutputDTO> outputs, LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            var list = outputs?.ToList() ?? [];
            if (list.Count == 0) return Result<string>.Success("No outputs to create.");
            foreach (var output in list) output.TenantId = login.ClientId;
            int rows = await _qe.BulkInsertAsync(login, MatrixQB.SAVE_MATRIX_OUTPUT, list, tx).ConfigureAwait(false);
            return Result<string>.Success($"{rows} output(s) created.");
        }

        // ── Write: update consumed qty on a single input line ─────────────────
        public async Task<Result<string>> UpdateConsumedQtyAsync(
            int matrixInputId, decimal additionalQty,
            LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            if (additionalQty <= 0)
                return Result<string>.Failure("Additional qty must be greater than zero.");
            int rows = await _qe.ExecuteAsync(login, MatrixQB.UPDATE_CONSUMED_QTY,
                new { MatrixInputId = matrixInputId, AdditionalQty = additionalQty, TenantId = login.ClientId, ModifiedById = login.UserId, ModifiedOn = DateTime.UtcNow },
                tx, ct).ConfigureAwait(false);
            return rows > 0 ? Result<string>.Success("Consumed qty updated.") : Result<string>.Failure("Matrix input line not found.");
        }

        // ── Select list ───────────────────────────────────────────────────────
        public async Task<string> GetSelectListMatrix(
            CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
        {
            var result = await _qe.QueryAsync<MatrixPicklistDTO>(login, MatrixQB.GET_SELECTLIST_MATRIX, new { TenantId = login.ClientId });
            return JsonConvert.SerializeObject(result);
        }

        // ── Single allocation-line summary (Matrix-independent) ────────────────
        public async Task<MatrixAllocationSummaryDTO?> GetAllocationItemSummaryAsync(
            int allotedAllocationId, LoginDTO login, CancellationToken ct = default)
        {
            var result = await _qe
                .QueryAsync<MatrixAllocationSummaryDTO>(login, MatrixQB.GET_ALLOCATION_ITEM_SUMMARY,
                    new { AllotedAllocationId = allotedAllocationId, TenantId = login.ClientId }, cancellationToken: ct)
                .ConfigureAwait(false);
            return result?.FirstOrDefault();
        }

        // ── Sets generated — TLOT only ──────────────────────────────────────────
        public async Task<int> GetSetsGeneratedCountAsync(
            int itemId, int headerAllocationId, LoginDTO login, CancellationToken ct = default)
        {
            return await _qe
                .ExecuteScalarAsync<int>(login, MatrixQB.GET_SETS_GENERATED_COUNT,
                    new { ItemId = itemId, HeaderAllocationId = headerAllocationId, TenantId = login.ClientId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
        }

        // ── Sets generated — scoped to ONE alloted-allocation line ─────────────
        public async Task<int> GetSetsGeneratedCountForAllotedAllocationAsync(
            int itemId, int allotedAllocationId, LoginDTO login, CancellationToken ct = default)
        {
            return await _qe
                .ExecuteScalarAsync<int>(login, MatrixQB.GET_SETS_GENERATED_COUNT_FOR_ALLOTED_ALLOCATION,
                    new
                    {
                        ItemId = itemId,
                        AllotedAllocationId = allotedAllocationId,
                        ObjectHeaderTypeId = GB5Shared.GB5Constant.Constant.EntityConstant.OBJECTALLOCATION
                    },
                    cancellationToken: ct)
                .ConfigureAwait(false);
        }

        // ── Generated SET-level lots for one alloted-allocation line ────────────
        // Returns the actual sets SaveSetSerialGenerationAsync wrote — the real,
        // dynamic list backing the old Matrix Planner's SetFrom/SetTo picklist.
        public async Task<IEnumerable<GeneratedSetLotDTO>> GetGeneratedSetLotsAsync(
            int allotedAllocationId, LoginDTO login, CancellationToken ct = default)
        {
            var result = await _qe
                .QueryAsync<GeneratedSetLotDTO>(login, MatrixQB.GET_GENERATED_SET_LOTS_FOR_ALLOTED_ALLOCATION,
                    new
                    {
                        AllotedAllocationId = allotedAllocationId,
                        ObjectHeaderTypeId = GB5Shared.GB5Constant.Constant.EntityConstant.OBJECTALLOCATION
                    },
                    cancellationToken: ct)
                .ConfigureAwait(false);
            return result ?? Enumerable.Empty<GeneratedSetLotDTO>();
        }

        // ── Reservation policy for a business transaction type ─────────────────
        // CK_MBIZTRANSACTIONTYPE_RESERVATIONTYPE: 0=Not Required, 1=Auto, 2=Manual.
        // Returns 0 (Not Required) if BizTransactionTypeId doesn't resolve to a row —
        // a safe default that skips auto-reservation for an unrecognized type rather
        // than risking an unintended auto-create.
        public async Task<int> GetBizTransactionTypeReservationTypeAsync(
            int bizTransactionTypeId, LoginDTO login, CancellationToken ct = default)
        {
            return await _qe
                .ExecuteScalarAsync<int>(login, MatrixQB.GET_BIZTRANSACTIONTYPE_RESERVATIONTYPE,
                    new { BizTransactionTypeId = bizTransactionTypeId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
        }

        // ── Resolve a BizTransactionTypeId by its system code (e.g. "LTPRD") ───
        public async Task<int?> GetBizTransactionTypeIdByCodeAsync(
            string code, LoginDTO login, CancellationToken ct = default)
        {
            return await _qe
                .ExecuteScalarAsync<int?>(login, MatrixQB.GET_BIZTRANSACTIONTYPE_ID_BY_CODE,
                    new { Code = code, TenantId = login.ClientId, OuId = login.WorkOUId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
        }

        // ── Resolve an MENTITY.ENTITYID by its ENTITYCODE (e.g. "MATRIX") ──────
        public async Task<int?> GetEntityIdByCodeAsync(
            string code, LoginDTO login, CancellationToken ct = default)
        {
            return await _qe
                .ExecuteScalarAsync<int?>(login, MatrixQB.GET_ENTITY_ID_BY_CODE,
                    new { Code = code },
                    cancellationToken: ct)
                .ConfigureAwait(false);
        }

        // ── Non-locking read of the lot sequence — preview/display only ────────
        public async Task<int> GetLastLotSequencePreviewAsync(
            string prefix, string suffix, LoginDTO login, CancellationToken ct = default)
        {
            return await _qe
                .ExecuteScalarAsync<int>(login, MatrixQB.GET_LAST_LOT_SEQUENCE_PREVIEW,
                    new { Prefix = prefix, Suffix = suffix, TenantId = login.ClientId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
        }

        // ── Locking read of the last issued serial for ONE item — must run inside
        //    the caller's transaction, immediately before the TLOT insert, so
        //    concurrent saves for the same item are serialized instead of racing
        //    on the same last-serial read.
        public async Task<string?> GetLastSerialForItemLockingAsync(
            int itemId, LoginDTO login, DbTransaction transaction, CancellationToken ct = default)
        {
            return await _qe
                .ExecuteScalarAsync<string?>(login, MatrixQB.GET_LAST_SERIAL_FOR_ITEM_LOCKING,
                    new { ItemId = itemId, TenantId = login.ClientId },
                    transaction: transaction,
                    cancellationToken: ct)
                .ConfigureAwait(false);
        }

        // ── Read: current stock + last issued serial per item (TLOT/TLOTDETAIL only) ──
        public async Task<IEnumerable<ItemStockDTO>> GetStockAndLastSerialForItemsAsync(
            IEnumerable<int> itemIds, LoginDTO login, CancellationToken ct = default)
        {
            var ids = itemIds?.Distinct().ToList() ?? [];
            if (ids.Count == 0) return Enumerable.Empty<ItemStockDTO>();

            var result = await _qe
                .QueryAsync<ItemStockDTO>(login, MatrixQB.GET_STOCK_AND_LAST_SERIAL_FOR_ITEMS,
                    new { ItemIds = ids, TenantId = login.ClientId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
            return result ?? Enumerable.Empty<ItemStockDTO>();
        }

        // ── Read: reserved qty per item (TRESERVATION only) ────────────────────
        public async Task<IEnumerable<ItemReservationDTO>> GetReservedQtyForItemsAsync(
            IEnumerable<int> itemIds, LoginDTO login, CancellationToken ct = default)
        {
            var ids = itemIds?.Distinct().ToList() ?? [];
            if (ids.Count == 0) return Enumerable.Empty<ItemReservationDTO>();

            var result = await _qe
                .QueryAsync<ItemReservationDTO>(login, MatrixQB.GET_RESERVED_QTY_FOR_ITEMS,
                    new { ItemIds = ids },
                    cancellationToken: ct)
                .ConfigureAwait(false);
            return result ?? Enumerable.Empty<ItemReservationDTO>();
        }

        // ── Read: skipped/available serials for one item (TLOT, STATUS = 0) ────
        public async Task<IEnumerable<SkippedSerialDTO>> GetSkippedSerialsAsync(
            int itemId, LoginDTO login, CancellationToken ct = default)
        {
            var result = await _qe
                .QueryAsync<SkippedSerialDTO>(login, MatrixQB.GET_SKIPPED_SERIALS_FOR_ITEM,
                    new { ItemId = itemId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
            return result ?? Enumerable.Empty<SkippedSerialDTO>();
        }

        // ── Read: RM free stock-on-hand for a set of items (TSTOCKPOSITION) ─────
        public async Task<IEnumerable<ItemFreeStockDTO>> GetStockOnHandForItemsAsync(
            IEnumerable<int> itemIds, LoginDTO login, CancellationToken ct = default)
        {
            var ids = itemIds?.Distinct().ToList() ?? [];
            if (ids.Count == 0) return Enumerable.Empty<ItemFreeStockDTO>();

            var result = await _qe
                .QueryAsync<ItemFreeStockDTO>(login, MatrixQB.GET_STOCK_ON_HAND_FOR_ITEMS,
                    new { ItemIds = ids }, cancellationToken: ct)
                .ConfigureAwait(false);
            return result ?? Enumerable.Empty<ItemFreeStockDTO>();
        }

        // ── Read: existing issued serials for one item (TLOT, STATUS = 1) ──────
        public async Task<IEnumerable<SkippedSerialDTO>> GetIssuedSerialsAsync(
            int itemId, LoginDTO login, CancellationToken ct = default)
        {
            var result = await _qe
                .QueryAsync<SkippedSerialDTO>(login, MatrixQB.GET_ISSUED_SERIALS_FOR_ITEM,
                    new { ItemId = itemId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
            return result ?? Enumerable.Empty<SkippedSerialDTO>();
        }

        // ── Reactivate a previously-skipped serial in place (Status 0 → 1) ──────
        public async Task<Result<string>> ReactivateSkippedSerialAsync(
            int lotId, string stockLedgerNumber, DateTime stockLedgerDate, LoginDTO login,
            DbTransaction tx, CancellationToken ct = default)
        {
            var now = DateTime.UtcNow;
            int rows = await _qe.ExecuteAsync(login, MatrixQB.REACTIVATE_SKIPPED_SERIAL,
                new
                {
                    LotId = lotId,
                    ModifiedById = login.UserId,
                    ModifiedOn = now,
                    StockLedgerNumber = stockLedgerNumber,
                    StockLedgerDate = stockLedgerDate
                },
                tx, ct).ConfigureAwait(false);

            return rows > 0
                ? Result<string>.Success($"Serial (LotId {lotId}) reactivated.")
                : Result<string>.Failure($"LotId {lotId} was not found or is not currently skipped (Status != 0).");
        }

        // ── Read: this component's QtyPerSet, resolved directly from its own ItemId ──
        public async Task<BomOutputQtyPerSetDTO?> GetBomOutputQtyPerSetForItemAsync(
            int itemId, LoginDTO login, CancellationToken ct = default)
        {
            var result = await _qe
                .QueryAsync<BomOutputQtyPerSetDTO>(login, MatrixQB.GET_BOM_OUTPUT_QTY_PER_SET_FOR_ITEM,
                    new { ItemId = itemId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
            return result?.FirstOrDefault();
        }

        // ── Select list — Allocation + Item (Matrix-independent) ──────────────
        public async Task<string> GetSelectListAllocationWithItem(
            CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
            => await _qe.QueryWithCriteriaJsonAsync<AllocationWithItemPicklistDTO>(
                login, MatrixQB.GET_SELECTLIST_ALLOCATION_WITH_ITEM,
                new { TenantId = login.ClientId }, criteriaDTO, ct);

        // ── BOM Explosion: Input lines (BOMLEVEL != 0) ────────────────────────
        public async Task<IEnumerable<ExplodeBOMInputDTO>> ExplodeBOMInputsAsync(
            int productItemId, int plannedQty, LoginDTO login, CancellationToken ct = default)
        {
            var result = await _qe
                .QueryAsync<ExplodeBOMInputDTO>(login, MatrixQB.EXPLODE_BOM_FOR_ITEM,
                    new { ProductItemId = productItemId, PlannedQty = (decimal)plannedQty, OuId = login.WorkOUId, TenantId = login.ClientId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
            return result ?? Enumerable.Empty<ExplodeBOMInputDTO>();
        }

        // ── BOM Explosion: Output line (BOMLEVEL = 0) ─────────────────────────
        public async Task<IEnumerable<ExplodeBOMOutputDTO>> ExplodeBOMOutputsAsync(
            int productItemId, int plannedQty, LoginDTO login, CancellationToken ct = default)
        {
            var result = await _qe
                .QueryAsync<ExplodeBOMOutputDTO>(login, MatrixQB.EXPLODE_BOM_OUTPUT_FOR_ITEM,
                    new { ProductItemId = productItemId, PlannedQty = (decimal)plannedQty, OuId = login.WorkOUId, TenantId = login.ClientId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
            return result ?? Enumerable.Empty<ExplodeBOMOutputDTO>();
        }

        // In MatrixDAL:
        public async Task<Result<string>> BulkSaveTlotDetailRowsAsync(
            IEnumerable<TlotDetailInsertDTO> details, LoginDTO login,
            DbTransaction tx, CancellationToken ct = default)
        {
            var list = details?.ToList() ?? [];
            if (list.Count == 0) return Result<string>.Success("No lot details to create.");

            using var table = new DataTable();
            table.Columns.Add("LOTDETAILID", typeof(int));
            table.Columns.Add("BIZTRANSACTIONTYPEID", typeof(int));
            table.Columns.Add("OBJECTHEADERTYPEID", typeof(int));
            table.Columns.Add("OBJECTHEADERID", typeof(int));
            table.Columns.Add("OBJECTTYPEID", typeof(int));
            table.Columns.Add("OBJECTID", typeof(int));
            table.Columns.Add("SLNO", typeof(short));
            table.Columns.Add("LOTID", typeof(int));
            table.Columns.Add("QUANTITY", typeof(decimal));
            table.Columns.Add("GOODQUANTITY", typeof(decimal));
            table.Columns.Add("REJECTEDQUANTITY", typeof(decimal));
            table.Columns.Add("REWORKQUANTITY", typeof(decimal));
            table.Columns.Add("OTHERQUANTITY", typeof(decimal));
            table.Columns.Add("PACKID", typeof(int));
            table.Columns.Add("PACKQUANTITY", typeof(decimal));
            table.Columns.Add("OUID", typeof(int));
            table.Columns.Add("STOREID", typeof(int));
            table.Columns.Add("STOCKLEDGERNUMBER", typeof(string));
            table.Columns.Add("STOCKLEDGERDATE", typeof(DateTime));
            table.Columns.Add("REFERENCENUMBER", typeof(string));
            table.Columns.Add("REFERENCEDATE", typeof(DateTime));
            table.Columns.Add("LOCATIONTYPE", typeof(byte));
            table.Columns.Add("PARTYBRANCHID", typeof(int));
            table.Columns.Add("STOCKPOSTTYPE", typeof(byte));
            table.Columns.Add("MATERIALOWNERSHIPTYPE", typeof(byte));
            table.Columns.Add("USEDINLOTID", typeof(int));
            table.Columns.Add("REASONID", typeof(int));
            table.Columns.Add("MARKEDGOODQUANTITY", typeof(decimal));
            table.Columns.Add("PLANNEDQUANTITY", typeof(decimal));
            table.Columns.Add("BINID", typeof(int));
            table.Columns.Add("ITEMPOSTEDCOST", typeof(decimal));
            table.Columns.Add("D1", typeof(decimal));
            table.Columns.Add("D2", typeof(decimal));
            table.Columns.Add("D3", typeof(decimal));
            table.Columns.Add("D4", typeof(decimal));
            table.Columns.Add("D5", typeof(decimal));
            table.Columns.Add("ALLOCATIONID", typeof(int));

            foreach (var d in list)
            {
                table.Rows.Add(
                    d.LotDetailId, d.BizTransactionTypeId, d.ObjectHeaderTypeId, d.ObjectHeaderId, d.ObjectTypeId, d.ObjectId,
                    d.SlNo, d.LotId, d.Quantity, d.GoodQuantity, d.RejectedQuantity, d.ReworkQuantity, d.OtherQuantity,
                    d.PackId, d.PackQuantity, d.OuId, d.StoreId, d.StockLedgerNumber, d.StockLedgerDate,
                    d.ReferenceNumber, d.ReferenceDate, d.LocationType, d.PartyBranchId, d.StockPostType,
                    d.MaterialOwnershipType, d.UsedInLotId, d.ReasonId, d.MarkedGoodQuantity, d.PlannedQuantity,
                    d.BinId, d.ItemPostedCost, d.D1, d.D2, d.D3, d.D4, d.D5, d.AllocationId);
            }

            await BulkCopyTableAsync(table, "TLOTDETAIL", tx, ct).ConfigureAwait(false);
            return Result<string>.Success($"{list.Count} lot detail(s) created.");
        }

        // ── Write: bulk-insert TRESERVATION rows ──────────────────────────────
        public async Task<Result<string>> BulkSaveReservationsAsync(
            IEnumerable<ReservationInsertDTO> reservations,
            LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            var list = reservations?.ToList() ?? [];
            if (list.Count == 0) return Result<string>.Success("No reservations to create.");
            int rows = await _qe.BulkInsertAsync(login, MatrixQB.SAVE_RESERVATION, list, tx)
                .ConfigureAwait(false);
            return Result<string>.Success($"{rows} reservation(s) created.");
        }

    }
}