namespace MMDAL.Query.StockReservation;

/// <summary>
/// Required indexes:
///   TRESERVATION: (TENANTID, RESERVATIONSTATUS, ISDELETED)
///   TRESERVATION: (TENANTID, FOROBJECTHEADERID, FOROBJECTTYPEID, FOROBJECTID, ISDELETED)
///   TRESERVATION: (TENANTID, STOREID, ITEMID, SKUID, ISDELETED)
///   TRESERVATION: (TENANTID, ITEMID, SKUID, RESERVATIONSTATUS, ISDELETED)
///   TRESERVATION: (TENANTID, MRPRUNID, ISDELETED)
///   TSTOCKPOSITION: (OUID, ITEMID, SKUID, STOREID) — unique, existing
/// </summary>
public static class StockReservationQB
{
    public const string GET_RESERVATION = @"
SELECT  
    r.STOCKRESERVATIONID              AS StockReservationId,
    r.TENANTID                        AS TenantId,
    r.OUID                            AS OUId,
    r.PERIODID                        AS PeriodId,
    r.RESERVATIONNUMBER               AS ReservationNumber,
    r.RESERVATIONDATE                 AS ReservationDate,
    r.RESERVATIONMODE                 AS ReservationMode,
    r.RESERVATIONSTATUS               AS ReservationStatus,
    r.RESERVATIONSOURCE               AS ReservationSource,
    r.ASSIGNMENTLEVEL                 AS AssignmentLevel,
    r.FOROBJECTHEADERTYPEID           AS ForObjectHeaderTypeId,
    r.FOROBJECTHEADERID               AS ForObjectHeaderId,
    r.FOROBJECTTYPEID                 AS ForObjectTypeId,
    r.FOROBJECTID                     AS ForObjectId,
    r.FORALLOTEDALLOCATIONID          AS ForAllotedAllocationId,
    r.FORBIZTRANSACTIONTYPEID         AS ForBizTransactionTypeId,
    r.FORDATE                         AS ForDate,
    r.FORSLNO                         AS ForSlNo,
    r.ITEMID                          AS ItemId,
    r.SKUID                           AS SKUId,
    r.LOTID                           AS LotId,
    r.PACKID                          AS PackId,
    r.STOREID                         AS StoreId,
    r.FROMOBJECTHEADERTYPEID          AS FromObjectHeaderTypeId,
    r.FROMOBJECTHEADERID              AS FromObjectHeaderId,
    r.FROMOBJECTTYPEID                AS FromObjectTypeId,
    r.FROMOBJECTID                    AS FromObjectId,
    r.FROMALLOTEDALLOCATIONID         AS FromAllotedAllocationId,
    r.FROMBIZTRANSACTIONTYPEID        AS FromBizTransactionTypeId,
    r.RESERVEDQUANTITY                AS ReservedQuantity,
    r.ISSUEDQUANTITY                  AS IssuedQuantity,
    r.MRPRUNID                        AS MrpRunId,
    r.MRPRUNDETAILID                  AS MrpRunDetailId,
    r.ISLOTLOCKED                     AS IsLotLocked,
    r.ISFREETRACKED                   AS IsFreeTracked,
    r.FREETRACKREASON                 AS FreeTrackReason,
    r.FREETRACKEDON                   AS FreeTrackedOn,
    r.FREETRACKBYID                   AS FreeTrackById,
    r.ISAUTOLINKED                    AS IsAutoLinked,
    r.LINKEDPOTYPEID                  AS LinkedPoTypeId,
    r.LINKEDPOID                      AS LinkedPoId,
    r.SUBCONOBJECTHEADERTYPEID        AS SubconObjectHeaderTypeId,
    r.SUBCONOBJECTHEADERID            AS SubconObjectHeaderId,
    r.SUBCONLOCATION                  AS SubconLocation,
    r.SUBCONSENTOUT                   AS SubconSentOut,
    r.SUBCONEXPECTEDRETURN            AS SubconExpectedReturn,
    r.ISDELETED                       AS IsDeleted,
    r.DELETEDON                       AS DeletedOn,
    r.DELETEDBYID                     AS DeletedById,
    r.DELETEREASON                    AS DeleteReason,
    r.VERSION                         AS Version,
    r.CREATEDBYID                     AS CreatedById,
    r.CREATEDON                       AS CreatedOn,
    r.MODIFIEDBYID                    AS ModifiedById,
    r.MODIFIEDON                      AS ModifiedOn,
    
    -- OU Display Fields (MORGANIZATIONUNIT)
    ou.ORGANIZATIONUNITCODE           AS OUCode,
    ou.ORGANIZATIONUNITNAME           AS OUName,
    
    -- Period Display Fields (MPERIOD)
    p.PERIODCODE                      AS PeriodCode,
    p.PERIODNAME                      AS PeriodName,
    
    -- Item Display Fields (MITEM)
    mi.ITEMCODE                       AS ItemCode,
    mi.ITEMNAME                       AS ItemName,
    
    -- SKU Display Fields (MSKU)
    sku.SKUCODE                       AS SKUCode,
    sku.SKUNAME                       AS SKUName,
    
    -- Lot Display Fields (TLOT)
    lot.LOTNUMBER                     AS LotNumber,
    
    -- Pack Display Fields (MPACK)
    pack.PACKCODE                     AS PackCode,
    pack.PACKNAME                     AS PackName,
    
    -- Store Display Fields (MSTORE)
    s.STORECODE                       AS StoreCode,
    s.STORENAME                       AS StoreName,
    
    -- User Display Fields (MUSER)
    createdBy.USERNAME                AS CreatedByUserName,
    modifiedBy.USERNAME               AS ModifiedByUserName,
    deletedBy.USERNAME                AS DeletedByUserName,
    freeTrackBy.USERNAME              AS FreeTrackByUserName,
    
    -- Document Type Display Fields (MDOCUMENTTYPE)
    forObjHeaderType.DOCUMENTTYPENAME AS ForObjectHeaderTypeName,
    fromObjHeaderType.DOCUMENTTYPENAME AS FromObjectHeaderTypeName,
    linkedPoType.DOCUMENTTYPENAME     AS LinkedPoTypeName,
    subconObjHeaderType.DOCUMENTTYPENAME AS SubconObjectHeaderTypeName,
    
    -- Document Set Display Fields (MDOCUMENTSET)
    forObjHeader.DOCUMENTSETNAME      AS ForObjectHeaderName,
    fromObjHeader.DOCUMENTSETNAME     AS FromObjectHeaderName,
    subconObjHeader.DOCUMENTSETNAME   AS SubconObjectHeaderName,
    
    -- Entity Type Display Fields (MENTITY)
    forObjType.ENTITYNAME             AS ForObjectTypeName,
    fromObjType.ENTITYNAME            AS FromObjectTypeName,
    
    -- Object Display Fields (Self-reference to TRESERVATION)
    forObjRes.RESERVATIONNUMBER       AS ForObjectReservationNumber,
    fromObjRes.RESERVATIONNUMBER      AS FromObjectReservationNumber,
    
    -- Allocation Display Fields (MALLOCATION)
    forAlloc.ALLOCATIONNAME           AS ForAllocationCode,
    fromAlloc.ALLOCATIONNAME          AS FromAllocationCode,
    
    -- Biz Transaction Type Display Fields (MBIZTRANSACTIONTYPE)
    forBizTxn.BIZTRANSACTIONTYPENAME  AS ForBizTransactionTypeName,
    fromBizTxn.BIZTRANSACTIONTYPENAME AS FromBizTransactionTypeName,
    
    -- MRP Run Display Fields (TMRPRUN)
    CAST(mrpRun.MRPRUNID AS NVARCHAR) AS MrpRunCode,
    CAST(mrpRun.MRPID AS NVARCHAR)    AS MrpRunName

FROM TRESERVATION r
LEFT JOIN MORGANIZATIONUNIT ou ON ou.OUID = r.OUID
LEFT JOIN MPERIOD p ON p.PERIODID = r.PERIODID
LEFT JOIN MITEM mi ON mi.ITEMID = r.ITEMID
LEFT JOIN MSKU sku ON sku.SKUID = r.SKUID
LEFT JOIN TLOT lot ON lot.LOTID = r.LOTID
LEFT JOIN MPACK pack ON pack.PACKID = r.PACKID
LEFT JOIN MSTORE s ON s.STOREID = r.STOREID
LEFT JOIN MUSER createdBy ON createdBy.USERID = r.CREATEDBYID
LEFT JOIN MUSER modifiedBy ON modifiedBy.USERID = r.MODIFIEDBYID
LEFT JOIN MUSER deletedBy ON deletedBy.USERID = r.DELETEDBYID
LEFT JOIN MUSER freeTrackBy ON freeTrackBy.USERID = r.FREETRACKBYID
LEFT JOIN MDOCUMENTTYPE forObjHeaderType ON forObjHeaderType.DOCUMENTTYPEID = r.FOROBJECTHEADERTYPEID
LEFT JOIN MDOCUMENTSET forObjHeader ON forObjHeader.DOCUMENTSETID = r.FOROBJECTHEADERID
LEFT JOIN MENTITY forObjType ON forObjType.ENTITYID = r.FOROBJECTTYPEID
LEFT JOIN TRESERVATION forObjRes ON forObjRes.STOCKRESERVATIONID = r.FOROBJECTID
LEFT JOIN MDOCUMENTTYPE fromObjHeaderType ON fromObjHeaderType.DOCUMENTTYPEID = r.FROMOBJECTHEADERTYPEID
LEFT JOIN MDOCUMENTSET fromObjHeader ON fromObjHeader.DOCUMENTSETID = r.FROMOBJECTHEADERID
LEFT JOIN MENTITY fromObjType ON fromObjType.ENTITYID = r.FROMOBJECTTYPEID
LEFT JOIN TRESERVATION fromObjRes ON fromObjRes.STOCKRESERVATIONID = r.FROMOBJECTID
LEFT JOIN MALLOCATION forAlloc ON forAlloc.ALLOCATIONID = r.FORALLOTEDALLOCATIONID
LEFT JOIN MALLOCATION fromAlloc ON fromAlloc.ALLOCATIONID = r.FROMALLOTEDALLOCATIONID
LEFT JOIN MBIZTRANSACTIONTYPE forBizTxn ON forBizTxn.BIZTRANSACTIONTYPEID = r.FORBIZTRANSACTIONTYPEID
LEFT JOIN MBIZTRANSACTIONTYPE fromBizTxn ON fromBizTxn.BIZTRANSACTIONTYPEID = r.FROMBIZTRANSACTIONTYPEID
LEFT JOIN TMRPRUN mrpRun ON mrpRun.MRPRUNID = r.MRPRUNID
LEFT JOIN MDOCUMENTTYPE linkedPoType ON linkedPoType.DOCUMENTTYPEID = r.LINKEDPOTYPEID
LEFT JOIN MDOCUMENTTYPE subconObjHeaderType ON subconObjHeaderType.DOCUMENTTYPEID = r.SUBCONOBJECTHEADERTYPEID
LEFT JOIN MDOCUMENTSET subconObjHeader ON subconObjHeader.DOCUMENTSETID = r.SUBCONOBJECTHEADERID
WHERE r.STOCKRESERVATIONID = @StockReservationId";

   
    // ── Paged list GET ─────────────────────────────────────────

    public const string GET_RESERVATION_LIST = @"
        SELECT  r.STOCKRESERVATIONID          AS StockReservationId,
                r.RESERVATIONNUMBER           AS ReservationNumber,
                r.RESERVATIONDATE             AS ReservationDate,
                r.RESERVATIONMODE             AS ReservationMode,
                r.RESERVATIONSTATUS           AS ReservationStatus,
                r.RESERVATIONSOURCE           AS ReservationSource,
                r.ASSIGNMENTLEVEL             AS AssignmentLevel,
                r.FOROBJECTHEADERID           AS ForObjectHeaderId,
                r.FOROBJECTID                 AS ForObjectId,
                r.ITEMID                      AS ItemId,
                mi.ITEMCODE                   AS ItemCode,
                mi.ITEMNAME                   AS ItemName,
                r.RESERVEDQUANTITY            AS ReservedQuantity,
                r.ISSUEDQUANTITY              AS IssuedQuantity,
                (r.RESERVEDQUANTITY - r.ISSUEDQUANTITY) AS BalanceQuantity,
                r.ISLOTLOCKED                 AS IsLotLocked,
                r.ISFREETRACKED               AS IsFreeTracked,
                r.ISAUTOLINKED                AS IsAutoLinked,
                r.SUBCONLOCATION              AS SubconLocation,
                s.STORENAME                   AS StoreName,
                r.FROMOBJECTHEADERID          AS FromObjectHeaderId
        FROM    TRESERVATION r
        JOIN    MITEM mi ON mi.ITEMID = r.ITEMID
        LEFT JOIN MSTORE s ON s.STOREID = r.STOREID
        WHERE   r.TENANTID = @TenantId
            AND r.ISDELETED = 0
            AND r.OUID      = @OUId
            AND (@StatusFilter IS NULL OR r.RESERVATIONSTATUS = @StatusFilter)
            AND (@ItemId      IS NULL OR r.ITEMID             = @ItemId)
        ORDER BY r.CREATEDON DESC";

    public const string GET_RESERVATION_LIST_COUNT = @"
        SELECT COUNT(1)
        FROM   TRESERVATION r
        WHERE  r.TENANTID = @TenantId
           AND r.ISDELETED = 0
           AND r.OUID      = @OUId
           AND (@StatusFilter IS NULL OR r.RESERVATIONSTATUS = @StatusFilter)
           AND (@ItemId      IS NULL OR r.ITEMID             = @ItemId)";

    // ── By MRP Run ─────────────────────────────────────────────

    public const string GET_RESERVATIONS_BY_MRP_RUN = @"
        SELECT  r.STOCKRESERVATIONID          AS StockReservationId,
                r.RESERVATIONNUMBER           AS ReservationNumber,
                r.RESERVATIONSTATUS           AS ReservationStatus,
                r.FOROBJECTID                 AS ForObjectId,
                r.ITEMID                      AS ItemId,
                mi.ITEMCODE                   AS ItemCode,
                mi.ITEMNAME                   AS ItemName,
                r.RESERVEDQUANTITY            AS ReservedQuantity,
                r.ISSUEDQUANTITY              AS IssuedQuantity,
                (r.RESERVEDQUANTITY - r.ISSUEDQUANTITY) AS BalanceQuantity,
                r.MRPRUNDETAILID              AS MrpRunDetailId
        FROM    TRESERVATION r
        JOIN    MITEM mi ON mi.ITEMID = r.ITEMID
        WHERE   r.TENANTID  = @TenantId
            AND r.MRPRUNID  = @MrpRunId
            AND r.ISDELETED = 0
        ORDER BY r.STOCKRESERVATIONID";

    // ── Stock availability query ───────────────────────────────
    // Returns current stock position with available = total - reserved

    public const string GET_STOCK_AVAILABILITY = @"
        SELECT  sp.STOCKPOSITIONID            AS StockPositionId,
                sp.STOREID                    AS StoreId,
                s.STORECODE                   AS StoreCode,
                s.STORENAME                   AS StoreName,
                sp.QUANTITY                   AS TotalQuantity,
                sp.RESERVEDQUANTITY           AS ReservedQuantity,
                (sp.QUANTITY - sp.RESERVEDQUANTITY) AS AvailableQuantity,
                sp.PIPELINERESERVEDQTY        AS PipelineReservedQty
        FROM    TSTOCKPOSITION sp
        JOIN    MSTORE s ON s.STOREID = sp.STOREID
        WHERE   sp.OUID    = @OUId
            AND sp.ITEMID  = @ItemId
            AND sp.SKUID   = @SKUId
            AND (sp.QUANTITY - sp.RESERVEDQUANTITY) > 0";

    // ── INSERT reservation ─────────────────────────────────────

    // In StockReservationQB.cs, create a simplified test query
    public const string INSERT_RESERVATION = @"
    INSERT INTO TRESERVATION (
        TENANTID, OUID, PERIODID,
        RESERVATIONNUMBER, RESERVATIONDATE,
        RESERVATIONMODE, RESERVATIONSTATUS, RESERVATIONSOURCE, ASSIGNMENTLEVEL,
        FOROBJECTHEADERID, FOROBJECTID,
        ITEMID, SKUID, STOREID,
        RESERVEDQUANTITY, ISSUEDQUANTITY,
        ISDELETED, VERSION,
        CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
    ) VALUES (
        @TenantId, @OUId, @PeriodId,
        @ReservationNumber, @ReservationDate,
        @ReservationMode, @ReservationStatus, @ReservationSource, @AssignmentLevel,
        @ForObjectHeaderId, @ForObjectId,
        @ItemId, @SKUId, @StoreId,
        @ReservedQuantity, @IssuedQuantity,
        0, 1,
        @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
    );
    SELECT CAST(SCOPE_IDENTITY() AS INT);";
    public const string UPDATE_RESERVATION_NUMBER = @"
    UPDATE TRESERVATION
    SET    RESERVATIONNUMBER = @ReservationNumber
    WHERE  STOCKRESERVATIONID = @StockReservationId
       AND TENANTID            = @TenantId";

    // ── UPDATE stock-side reserved quantity (optimistic) ──────
    // Returns 0 rows affected if VERSION mismatch → retry signal

    public const string INCREMENT_STOCK_RESERVED = @"
        UPDATE TSTOCKPOSITION
        SET    RESERVEDQUANTITY = RESERVEDQUANTITY + @Quantity
        WHERE  OUID    = @OUId
           AND ITEMID  = @ItemId
           AND SKUID   = @SKUId
           AND STOREID = @StoreId
           AND (QUANTITY - RESERVEDQUANTITY) >= @Quantity";

    public const string DECREMENT_STOCK_RESERVED = @"
        UPDATE TSTOCKPOSITION
        SET    RESERVEDQUANTITY = RESERVEDQUANTITY - @Quantity
        WHERE  OUID    = @OUId
           AND ITEMID  = @ItemId
           AND SKUID   = @SKUId
           AND STOREID = @StoreId
           AND RESERVEDQUANTITY >= @Quantity";

    // ── UPDATE pipeline-side reserved quantity ─────────────────

    public const string DECREMENT_PIPELINE_PENDING = @"
        UPDATE TPENDINGALLOCATION
        SET    PENDINGQUANTITY = PENDINGQUANTITY - @Quantity
        WHERE  ALLOTEDALLOCATIONID = @FromAllotedAllocationId
           AND PENDINGQUANTITY     >= @Quantity
           AND TENANTID            = @TenantId";

    public const string INCREMENT_PIPELINE_PENDING = @"
        UPDATE TPENDINGALLOCATION
        SET    PENDINGQUANTITY = PENDINGQUANTITY + @Quantity
        WHERE  ALLOTEDALLOCATIONID = @FromAllotedAllocationId
           AND TENANTID            = @TenantId";

    // ── Soft-delete / release reservation ─────────────────────

    public const string RELEASE_RESERVATION = @"
        UPDATE TRESERVATION
        SET    RESERVATIONSTATUS = 3,       -- Released
               ISDELETED         = 1,
               DELETEDON         = @DeletedOn,
               DELETEDBYID       = @DeletedById,
               DELETEREASON      = @DeleteReason,
               MODIFIEDBYID      = @ModifiedById,
               MODIFIEDON        = @ModifiedOn,
               VERSION           = VERSION + 1
        WHERE  STOCKRESERVATIONID = @StockReservationId
           AND TENANTID           = @TenantId
           AND VERSION            = @Version
           AND ISDELETED          = 0";

    // ── Partial unreserve — reduce reserved quantity ───────────

    public const string REDUCE_RESERVED_QUANTITY = @"
        UPDATE TRESERVATION
        SET    RESERVEDQUANTITY = RESERVEDQUANTITY - @ReleaseQuantity,
               MODIFIEDBYID    = @ModifiedById,
               MODIFIEDON      = @ModifiedOn,
               VERSION         = VERSION + 1
        WHERE  STOCKRESERVATIONID = @StockReservationId
           AND TENANTID           = @TenantId
           AND VERSION            = @Version
           AND (RESERVEDQUANTITY - ISSUEDQUANTITY) >= @ReleaseQuantity
           AND ISDELETED          = 0";

    // ── Free-track ─────────────────────────────────────────────

    public const string SAVE_FREE_TRACK = @"
        INSERT INTO TRESERVATION (
            STOCKRESERVATIONID, TENANTID, OUID,
            RESERVATIONNUMBER, RESERVATIONDATE,
            RESERVATIONMODE, RESERVATIONSTATUS, RESERVATIONSOURCE, ASSIGNMENTLEVEL,
            FOROBJECTHEADERTYPEID, FOROBJECTHEADERID, FOROBJECTTYPEID, FOROBJECTID,
            FORALLOTEDALLOCATIONID,
            ITEMID, SKUID,
            RESERVEDQUANTITY, ISSUEDQUANTITY,
            ISFREETRACKED, FREETRACKREASON, FREETRACKEDON, FREETRACKBYID,
            ISDELETED, VERSION,
            CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
        ) VALUES (
            @StockReservationId, @TenantId, @OUId,
            @ReservationNumber, GETUTCDATE(),
            3, 5, 0, @AssignmentLevel,
            @ForObjectHeaderTypeId, @ForObjectHeaderId, @ForObjectTypeId, @ForObjectId,
            @ForAllotedAllocationId,
            @ItemId, @SKUId,
            0, 0,
            1, @Reason, GETUTCDATE(), @FreeTrackById,
            0, 1,
            @CreatedById, GETUTCDATE(), @ModifiedById, GETUTCDATE()
        )";

    // ── Reassign — mark original as reassigned ─────────────────

    public const string MARK_REASSIGNED = @"
        UPDATE TRESERVATION
        SET    RESERVATIONSTATUS = 4,       -- Reassigned
               MODIFIEDBYID    = @ModifiedById,
               MODIFIEDON      = @ModifiedOn,
               VERSION         = VERSION + 1
        WHERE  STOCKRESERVATIONID = @StockReservationId
           AND TENANTID           = @TenantId
           AND VERSION            = @Version
           AND RESERVATIONSTATUS  = 2       -- must be Confirmed
           AND ISDELETED          = 0";

    // ── Confirm tentative reservations ─────────────────────────

    public const string CONFIRM_TENTATIVE = @"
        UPDATE TRESERVATION
        SET    RESERVATIONSTATUS = 2,       -- Confirmed
               RESERVATIONMODE  = 2,       -- PostPlanning
               MODIFIEDBYID    = @ModifiedById,
               MODIFIEDON      = @ModifiedOn,
               VERSION         = VERSION + 1
        WHERE  TENANTID         = @TenantId
           AND MRPRUNID         = @MrpRunId
           AND RESERVATIONSTATUS = 1        -- Tentative
           AND ISDELETED        = 0";

    // Single confirm by ID
    public const string CONFIRM_ONE_TENTATIVE = @"
        UPDATE TRESERVATION
        SET    RESERVATIONSTATUS = 2,
               RESERVATIONMODE  = 2,
               MODIFIEDBYID    = @ModifiedById,
               MODIFIEDON      = @ModifiedOn,
               VERSION         = VERSION + 1
        WHERE  STOCKRESERVATIONID = @StockReservationId
           AND TENANTID           = @TenantId
           AND RESERVATIONSTATUS  = 1
           AND ISDELETED          = 0";

    // ── Update issued quantity (called by stock issue BLL) ─────

    public const string UPDATE_ISSUED_QUANTITY = @"
        UPDATE TRESERVATION
        SET    ISSUEDQUANTITY = ISSUEDQUANTITY + @IssuedQty,
               RESERVATIONSTATUS = CASE
                   WHEN (ISSUEDQUANTITY + @IssuedQty) >= RESERVEDQUANTITY THEN 7  -- FullyFulfilled
                   WHEN (ISSUEDQUANTITY + @IssuedQty) > 0                  THEN 6  -- PartiallyFulfilled
                   ELSE RESERVATIONSTATUS
               END,
               MODIFIEDBYID  = @ModifiedById,
               MODIFIEDON    = @ModifiedOn,
               VERSION       = VERSION + 1
        WHERE  STOCKRESERVATIONID = @StockReservationId
           AND TENANTID           = @TenantId
           AND (RESERVEDQUANTITY - ISSUEDQUANTITY) >= @IssuedQty
           AND ISDELETED          = 0";

    // ── Subcontracting dispatch — mark SubcontractingOut ───────

    public const string MARK_SUBCON_OUT = @"
        UPDATE TRESERVATION
        SET    RESERVATIONSTATUS        = 8,    -- SubcontractingOut
               SUBCONOBJECTHEADERTYPEID = @SubconObjectHeaderTypeId,
               SUBCONOBJECTHEADERID     = @SubconObjectHeaderId,
               SUBCONLOCATION          = @SubconLocation,
               SUBCONSENTOUT           = @SubconSentOut,
               SUBCONEXPECTEDRETURN    = @SubconExpectedReturn,
               MODIFIEDBYID            = @ModifiedById,
               MODIFIEDON              = @ModifiedOn,
               VERSION                 = VERSION + 1
        WHERE  STOCKRESERVATIONID = @StockReservationId
           AND TENANTID           = @TenantId
           AND VERSION            = @Version
           AND ISDELETED          = 0";

    // ── Subcontracting return — revert to Confirmed ─────────────

    public const string MARK_SUBCON_RETURNED = @"
        UPDATE TRESERVATION
        SET    RESERVATIONSTATUS        = 2,    -- Confirmed
               RESERVEDQUANTITY        = @ActualReturnedQty,
               SUBCONOBJECTHEADERID    = NULL,
               SUBCONLOCATION         = NULL,
               SUBCONSENTOUT          = NULL,
               MODIFIEDBYID           = @ModifiedById,
               MODIFIEDON             = @ModifiedOn,
               VERSION                = VERSION + 1
        WHERE  STOCKRESERVATIONID = @StockReservationId
           AND TENANTID           = @TenantId
           AND VERSION            = @Version
           AND RESERVATIONSTATUS  = 8           -- must be SubcontractingOut
           AND ISDELETED          = 0";

    // ── Reassignment log INSERT ────────────────────────────────

    public const string INSERT_REASSIGNMENT_LOG = @"
        INSERT INTO TREASSIGNMENTLOG (
            REASSIGNMENTLOGID,
            TENANTID,
            ORIGINALRESERVATIONID,
            ORIGFOROBJECTHEADERTYPEID, ORIGFOROBJECTHEADERID,
            ORIGFOROBJECTTYPEID, ORIGFOROBJECTID,
            NEWRESERVATIONID,
            NEWFOROBJECTHEADERTYPEID, NEWFOROBJECTHEADERID,
            NEWFOROBJECTTYPEID, NEWFOROBJECTID,
            REASSIGNEDQUANTITY, REASON,
            REASSIGNEDBYID, REASSIGNEDON
        ) VALUES (
            @ReassignmentLogId,
            @TenantId,
            @OriginalReservationId,
            @OrigForObjectHeaderTypeId, @OrigForObjectHeaderId,
            @OrigForObjectTypeId, @OrigForObjectId,
            @NewReservationId,
            @NewForObjectHeaderTypeId, @NewForObjectHeaderId,
            @NewForObjectTypeId, @NewForObjectId,
            @ReassignedQuantity, @Reason,
            @ReassignedById, GETUTCDATE()
        )";

    // ── Subcon tracking report ─────────────────────────────────

    public const string GET_SUBCON_TRACKING = @"
        SELECT  r.STOCKRESERVATIONID          AS StockReservationId,
                r.RESERVATIONNUMBER           AS ReservationNumber,
                mi.ITEMCODE                   AS ItemCode,
                mi.ITEMNAME                   AS ItemName,
                r.RESERVEDQUANTITY            AS ReservedQuantity,
                r.FOROBJECTID                 AS ForObjectId,
                r.SUBCONLOCATION              AS SubconLocation,
                r.SUBCONSENTOUT               AS SentOutDate,
                r.SUBCONEXPECTEDRETURN        AS ExpectedReturnDate,
                CASE WHEN r.SUBCONEXPECTEDRETURN < CAST(GETUTCDATE() AS DATE) THEN 1 ELSE 0 END AS IsOverdue
        FROM    TRESERVATION r
        JOIN    MITEM mi ON mi.ITEMID = r.ITEMID
        WHERE   r.TENANTID         = @TenantId
            AND r.OUID             = @OUId
            AND r.RESERVATIONSTATUS = 8   -- SubcontractingOut
            AND r.ISDELETED        = 0
        ORDER BY r.SUBCONEXPECTEDRETURN ASC";

    // ── Check for active reservation on same FOR demand+item ──
    // Used to prevent duplicates

    public const string CHECK_DUPLICATE_ACTIVE = @"
        SELECT COUNT(1)
        FROM   TRESERVATION
        WHERE  TENANTID           = @TenantId
           AND FOROBJECTHEADERID  = @ForObjectHeaderId
           AND FOROBJECTID        = @ForObjectId
           AND ITEMID             = @ItemId
           AND SKUID              = @SKUId
           AND RESERVATIONSTATUS  NOT IN (3, 4, 7)   -- not Released/Reassigned/FullyFulfilled
           AND ISFREETRACKED      = 0
           AND ISDELETED          = 0";

    // ── Reassignment log history for a reservation ─────────────

    public const string GET_REASSIGNMENT_LOG = @"
        SELECT  l.REASSIGNMENTLOGID      AS ReassignmentLogId,
                l.ORIGINALRESERVATIONID  AS OriginalReservationId,
                l.NEWRESERVATIONID       AS NewReservationId,
                l.REASSIGNEDQUANTITY     AS ReassignedQuantity,
                l.REASON                 AS Reason,
                l.REASSIGNEDBYID         AS ReassignedById,
                l.REASSIGNEDON           AS ReassignedOn
        FROM    TREASSIGNMENTLOG l
        WHERE   l.TENANTID = @TenantId
            AND (l.ORIGINALRESERVATIONID = @StockReservationId
              OR l.NEWRESERVATIONID      = @StockReservationId)
        ORDER BY l.REASSIGNEDON DESC";
    public const string GET_SELECTLIST_STOCKRESERVATION = @"
WITH StockReservation AS
(
    SELECT
        SR.STOCKRESERVATIONID    AS StockReservationId,
        SR.RESERVATIONNUMBER     AS ReservationNumber,
        SR.RESERVATIONDATE       AS ReservationDate,

        ROW_NUMBER() OVER
        (
            ORDER BY SR.STOCKRESERVATIONID DESC
        ) AS RowNum
    FROM TRESERVATION SR
)
SELECT
    StockReservationId,
    ReservationNumber,
    ReservationDate
FROM StockReservation
WHERE
    (@firstnumber = -1 AND @maxresult = -1)
    OR (RowNum BETWEEN @firstnumber AND @maxresult);
";

}