using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.ResponseStandard;
using QMSDAL.DTO;
using System.Data.Common;

namespace QMSDAL.CustomCode.Matrix
{
    public interface IMatrixDAL
    {
        // ── READ — Queries ────────────────────────────────────────────────────

        /// <summary>
        /// Returns all matrix headers for the tenant (no MatrixId filter).
        /// Inputs and Outputs are NOT populated — call GetAllMatrixInputsAsync /
        /// GetAllMatrixOutputsAsync and stitch in the caller.
        /// </summary>
        Task<IEnumerable<MatrixDTO>> GetAllMatrixHeadersAsync(
                                              LoginDTO login,
                                              CancellationToken ct = default);

        /// <summary>Returns all input lines for the tenant across all matrices.</summary>
        Task<IEnumerable<MatrixInputDTO>> GetAllMatrixInputsAsync(
                                              LoginDTO login,
                                              CancellationToken ct = default);

        /// <summary>Returns all output rows for the tenant across all matrices.</summary>
        Task<IEnumerable<MatrixOutputDTO>> GetAllMatrixOutputsAsync(
                                              LoginDTO login,
                                              CancellationToken ct = default);

        /// <summary>
        /// Returns the matrix header. Inputs list is NOT populated here —
        /// call GetMatrixInputsAsync separately when lines are needed.
        /// </summary>
        Task<MatrixDTO?> GetMatrixAsync(
                                              int matrixId,
                                              LoginDTO login,
                                              CancellationToken ct = default);

        /// <summary>
        /// Returns all input lines for the given matrix.
        /// </summary>
        Task<IEnumerable<MatrixInputDTO>> GetMatrixInputsAsync(
                                              int matrixId,
                                              LoginDTO login,
                                              CancellationToken ct = default);

        /// <summary>
        /// Returns all output rows for the given matrix.
        /// Used to populate MatrixDTO.Outputs for Mode 2 display and editing.
        /// </summary>
        Task<IEnumerable<MatrixOutputDTO>> GetMatrixOutputsAsync(
                                              int matrixId,
                                              LoginDTO login,
                                              CancellationToken ct = default);

        /// <summary>
        /// Returns true if TMATRIXOUTPUT already has rows for this matrix.
        /// Used by ActivateMatrix to skip lot generation when pre-generated.
        /// </summary>
        Task<bool> HasOutputsGeneratedAsync(
                                              int matrixId,
                                              LoginDTO login,
                                              CancellationToken ct = default);

        /// <summary>
        /// Returns Set lot count and FGSerial count generated for
        /// this matrix (populated from TMATRIXOUTPUT).
        /// </summary>
        Task<(int Sets, int Serials)> GetMatrixOutputCountsAsync(
                                              int matrixId,
                                              LoginDTO login,
                                              CancellationToken ct = default);

        /// <summary>
        /// Returns true if allocationId resolves to a real MALLOCATION row.
        /// TMATRIX.ALLOCATIONID's FK requires the SO's own header allocation id —
        /// NOT a pending-allocation line's AllotedAllocationId.
        /// </summary>
        Task<bool> AllocationExistsAsync(
                                              int allocationId,
                                              LoginDTO login,
                                              CancellationToken ct = default);

        /// <summary>
        /// Returns count of rows matching matrixCode for the tenant,
        /// excluding the given matrixId (pass 0 for new records).
        /// </summary>
        Task<int> CheckDuplicateCodeAsync(
                                              string matrixCode,
                                              int excludeMatrixId,
                                              LoginDTO login,
                                              CancellationToken ct = default);

        /// <summary>
        /// Returns true if [setFrom..setTo] overlaps any Active matrix
        /// for the given allocation and mode, excluding excludeMatrixId.
        /// SetFrom / SetTo use short to match MatrixDTO.
        /// </summary>
        Task<bool> HasSetRangeOverlapAsync(
                                              int allocationId,
                                              byte matrixMode,
                                              short setFrom,
                                              short setTo,
                                              int excludeMatrixId,
                                              LoginDTO login,
                                              CancellationToken ct = default);

        /// <summary>
        /// Returns the next sequential AllocationSlNo for the given
        /// allocationId within the tenant.
        /// MAX(ALLOCATIONSLNO) + 1, or 1 if none exist yet.
        /// </summary>
        Task<short> GetNextAllocationSlNoAsync(
                                              int allocationId,
                                              LoginDTO login,
                                              CancellationToken ct = default);

        /// <summary>
        /// Returns input lines that have FreeQty below PlannedQty
        /// for shortage reporting. FreeQty is populated from stock query.
        /// </summary>
        Task<IEnumerable<MatrixInputDTO>> GetStockShortageInputsAsync(
                                              int matrixId,
                                              LoginDTO login,
                                              CancellationToken ct = default);

        /// <summary>
        /// Returns available RM batches for a given item/SKU,
        /// optionally filtered by requiredQty for shortage check.
        /// </summary>
        Task<IEnumerable<BatchStockDTO>> GetAvailableBatchesAsync(
                                              int itemId,
                                              int skuId,
                                              decimal? requiredQty,
                                              LoginDTO login,
                                              CancellationToken ct = default);

        /// <summary>
        /// Resolves the MatrixId from a given IndentId via TINDENT.MATRIXID.
        /// Returns -1 if not found or not linked.
        /// </summary>
        Task<int> GetMatrixIdFromIndentAsync(
                                              int indentId,
                                              LoginDTO login,
                                              CancellationToken ct = default);

        /// <summary>
        /// Returns the single input line for a given matrix + item combination.
        /// Used at material issue time for batch and qty validation.
        /// Returns null if no restriction line exists.
        /// </summary>
        Task<MatrixInputDTO?> GetMatrixInputForItemAsync(
                                              int matrixId,
                                              int itemId,
                                              LoginDTO login,
                                              CancellationToken ct = default);

        // ── WRITE — Commands (all transactional) ──────────────────────────────

        /// <summary>Inserts a new TMATRIX header row.</summary>
        Task<Result<string>> SaveMatrixAsync(
                                              MatrixDTO dto,
                                              LoginDTO login,
                                              DbTransaction tx,
                                              CancellationToken ct = default);

        /// <summary>Updates an existing TMATRIX header row (Draft only).</summary>
        Task<Result<string>> UpdateMatrixAsync(
                                              MatrixDTO dto,
                                              LoginDTO login,
                                              DbTransaction tx,
                                              CancellationToken ct = default);

        /// <summary>
        /// Hard-deletes all TMATRIXOUTPUT rows for the given matrixId.
        /// Only called for Mode 2 on the update path — Mode 0/1 outputs are immutable.
        /// </summary>
        Task<Result<string>> DeleteMatrixOutputsAsync(
                                              int matrixId,
                                              LoginDTO login,
                                              DbTransaction tx,
                                              CancellationToken ct = default);

        /// <summary>
        /// Hard-deletes all TMATRIXINPUT rows for the given matrixId.
        /// Safe only on Draft matrices — enforced in BLL before calling.
        /// </summary>
        Task<Result<string>> DeleteMatrixInputsAsync(
                                              int matrixId,
                                              LoginDTO login,
                                              DbTransaction tx,
                                              CancellationToken ct = default);

        /// <summary>
        /// Bulk-inserts TMATRIXINPUT rows. IList used (not IEnumerable)
        /// because bulk operations require count and index access.
        /// </summary>
        Task<Result<string>> BulkSaveMatrixInputsAsync(
                                              IList<MatrixInputDTO> inputs,
                                              int matrixId,
                                              LoginDTO login,
                                              DbTransaction tx,
                                              CancellationToken ct = default);

        /// <summary>
        /// Soft-deletes a matrix: sets STATUS = 3 (Deleted).
        /// Only permitted on Draft matrices — enforced in BLL.
        /// </summary>
        Task<Result<string>> SoftDeleteMatrixAsync(
                                              int matrixId,
                                              LoginDTO login,
                                              DbTransaction tx,
                                              CancellationToken ct = default);

        /// <summary>
        /// Marks oldMatrixId as Superseded (MATRIXSTATUS = 2) and
        /// sets SUPERSEDEDBYID = newMatrixId on the old row.
        /// </summary>
        Task<Result<string>> SupersedeMatrixAsync(
                                              int oldMatrixId,
                                              int newMatrixId,
                                              LoginDTO login,
                                              DbTransaction tx,
                                              CancellationToken ct = default);

        /// <summary>
        /// Updates TMATRIXINPUT.LINESTATUS for all lines of the given matrix
        /// after OpenMix (Mode 2) activation:
        ///   AllocatedQty > 0 → 1 (Partial), otherwise → 0 (Open).
        /// </summary>
        Task<Result<string>> ActivateOpenMixInputsAsync(
                                              int matrixId,
                                              LoginDTO login,
                                              DbTransaction tx,
                                              CancellationToken ct = default);

        /// <summary>
        /// Sets MATRIXSTATUS = 1 (Active) and ACTIVATEDON = GETDATE()
        /// on the given matrix. Called after Set lots and FGSerials
        /// have been generated into TMATRIXOUTPUT.
        /// </summary>
        Task<Result<string>> ActivateMatrixAsync(
                                              int matrixId,
                                              LoginDTO login,
                                              DbTransaction tx,
                                              CancellationToken ct = default);

        /// <summary>
        /// Bulk-inserts TLOT rows generated during matrix activation.
        /// One row per Set lot or FGSerial depending on MatrixMode.
        /// </summary>
        Task<Result<string>> BulkSaveTlotRowsAsync(
                                              IEnumerable<TlotInsertDTO> lots,
                                              LoginDTO login,
                                              DbTransaction tx,
                                              CancellationToken ct = default);

        /// <summary>
        /// Bulk-inserts TMATRIXOUTPUT rows linking generated lots
        /// back to the matrix. Called in the same activation transaction.
        /// </summary>
        Task<Result<string>> BulkSaveMatrixOutputsAsync(
                                              IEnumerable<MatrixOutputDTO> outputs,
                                              LoginDTO login,
                                              DbTransaction tx,
                                              CancellationToken ct = default);

        /// <summary>
        /// Updates TMATRIXINPUT.CONSUMEDQTY and LINESTATUS
        /// for a single input line after a material issue is posted.
        /// </summary>
        Task<Result<string>> UpdateConsumedQtyAsync(
                                              int matrixInputId,
                                              decimal additionalQty,
                                              LoginDTO login,
                                              DbTransaction tx,
                                              CancellationToken ct = default);

        Task<string> GetSelectListMatrix(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct);

        /// <summary>
        /// Matrix-independent picklist joining TPENDINGALLOCATION with MITEM,
        /// MBIZTRANSACTIONTYPE, MALLOCATION and MPARTY (via TALLOCATION for quantities).
        /// Does not depend on TMATRIX.
        /// </summary>
        Task<string> GetSelectListAllocationWithItem(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct);

        /// <summary>Matrix-independent single allocation-line summary — no TMATRIX.</summary>
        Task<MatrixAllocationSummaryDTO?> GetAllocationItemSummaryAsync(int allotedAllocationId, LoginDTO login, CancellationToken ct = default);

        /// <summary>Count of TLOT rows already generated for this item + header allocation — no TMATRIXOUTPUT.</summary>
        Task<int> GetSetsGeneratedCountAsync(int itemId, int headerAllocationId, LoginDTO login, CancellationToken ct = default);

        /// <summary>Sets generated for ONE alloted-allocation line only — see GET_SETS_GENERATED_COUNT_FOR_ALLOTED_ALLOCATION.</summary>
        Task<int> GetSetsGeneratedCountForAllotedAllocationAsync(int itemId, int allotedAllocationId, LoginDTO login, CancellationToken ct = default);

        /// <summary>The actual SET-level TLOT rows generated for one alloted-allocation line — backs the SetFrom/SetTo picklist.</summary>
        Task<IEnumerable<GeneratedSetLotDTO>> GetGeneratedSetLotsAsync(int allotedAllocationId, LoginDTO login, CancellationToken ct = default);

        /// <summary>Reservation policy for a business transaction type — 0=Not Required, 1=Auto, 2=Manual.</summary>
        Task<int> GetBizTransactionTypeReservationTypeAsync(int bizTransactionTypeId, LoginDTO login, CancellationToken ct = default);

        /// <summary>Resolves a BizTransactionTypeId by its system code (e.g. "LTPRD"), scoped by tenant + OU. Null if not found.</summary>
        Task<int?> GetBizTransactionTypeIdByCodeAsync(string code, LoginDTO login, CancellationToken ct = default);

        /// <summary>Resolves an MENTITY.ENTITYID by its ENTITYCODE (e.g. "MATRIX"). Null if not found.</summary>
        Task<int?> GetEntityIdByCodeAsync(string code, LoginDTO login, CancellationToken ct = default);

        /// <summary>Non-locking read of the current lot sequence — preview/display only.</summary>
        Task<int> GetLastLotSequencePreviewAsync(string prefix, string suffix, LoginDTO login, CancellationToken ct = default);

        /// <summary>Current stock + last issued serial per item, from TLOT/TLOTDETAIL only.</summary>
        Task<IEnumerable<ItemStockDTO>> GetStockAndLastSerialForItemsAsync(IEnumerable<int> itemIds, LoginDTO login, CancellationToken ct = default);

        /// <summary>Locking (UPDLOCK, HOLDLOCK) read of the last issued serial for one item — must run inside the caller's transaction.</summary>
        Task<string?> GetLastSerialForItemLockingAsync(int itemId, LoginDTO login, DbTransaction transaction, CancellationToken ct = default);

        /// <summary>Reserved qty per item, from TRESERVATION only (active reservations).</summary>
        Task<IEnumerable<ItemReservationDTO>> GetReservedQtyForItemsAsync(IEnumerable<int> itemIds, LoginDTO login, CancellationToken ct = default);

        /// <summary>Skipped/available serials (TLOT STATUS = 0) for one item — deselected during a prior save.</summary>
        Task<IEnumerable<SkippedSerialDTO>> GetSkippedSerialsAsync(int itemId, LoginDTO login, CancellationToken ct = default);

        /// <summary>Existing issued serials (TLOT STATUS = 1) for one item — real active stock, pickable to fulfill a run via an existing lot instead of a new one.</summary>
        Task<IEnumerable<SkippedSerialDTO>> GetIssuedSerialsAsync(int itemId, LoginDTO login, CancellationToken ct = default);

        /// <summary>Free RM stock-on-hand (TSTOCKPOSITION.QUANTITY − RESERVEDQUANTITY, summed across stores) for a set of items.</summary>
        Task<IEnumerable<ItemFreeStockDTO>> GetStockOnHandForItemsAsync(IEnumerable<int> itemIds, LoginDTO login, CancellationToken ct = default);

        /// <summary>Reactivates a previously-skipped serial (TLOT STATUS 0 → 1) in place — user picked it to fulfill the current run instead of issuing a fresh number.</summary>
        Task<Result<string>> ReactivateSkippedSerialAsync(int lotId, string stockLedgerNumber, DateTime stockLedgerDate, LoginDTO login, DbTransaction tx, CancellationToken ct = default);

        /// <summary>Resolves a BOM output component's QtyPerSet directly from its own ItemId (no parent product context needed).</summary>
        Task<BomOutputQtyPerSetDTO?> GetBomOutputQtyPerSetForItemAsync(int itemId, LoginDTO login, CancellationToken ct = default);

        // ── BOM Explosion ─────────────────────────────────────────────────────

        /// <summary>
        /// Returns BOM component lines (BOMLEVEL != 0) for the given item and planned qty.
        /// These form the Input array of the ExplodeBOM response.
        /// </summary>
        Task<IEnumerable<ExplodeBOMInputDTO>> ExplodeBOMInputsAsync(
                                              int productItemId,
                                              int plannedQty,
                                              LoginDTO login,
                                              CancellationToken ct = default);

        /// <summary>
        /// Returns the production item / FG output line (BOMLEVEL = 0) for the given item and planned qty.
        /// These form the Output array of the ExplodeBOM response.
        /// </summary>
        Task<IEnumerable<ExplodeBOMOutputDTO>> ExplodeBOMOutputsAsync(
                                              int productItemId,
                                              int plannedQty,
                                              LoginDTO login,
                                              CancellationToken ct = default);

        Task<MatrixSummaryDTO?> GetMatrixSummaryAsync(
    int allotedAllocationId, LoginDTO login, CancellationToken ct = default);

        Task<Result<string>> BulkSaveTlotDetailRowsAsync(
           IEnumerable<TlotDetailInsertDTO> details, LoginDTO login,
           DbTransaction tx, CancellationToken ct = default);

        /// <summary>
        /// Bulk-inserts TRESERVATION rows for input lines where IsReservation == false.
        /// Each row reserves AllocatedQty of an RM batch against the matrix.
        /// Called inside the SaveMatrix transaction so reservations roll back with the header.
        /// </summary>
        Task<Result<string>> BulkSaveReservationsAsync(
            IEnumerable<ReservationInsertDTO> reservations,
            LoginDTO login,
            DbTransaction tx,
            CancellationToken ct = default);
    }
}