using Dapper;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.ListQuery;
using QMSDAL.DTO;

// FIX-1: Removed unused "using Google.Rpc;" — not referenced anywhere in this file.

namespace QMSDAL.Query.Matrix
{
    // ─────────────────────────────────────────────────────────────────────────
    //  MatrixQB — SQL constants for TMATRIX, TMATRIXINPUT, TMATRIXOUTPUT
    //
    //  Rules:
    //    • All column names UPPERCASE
    //    • All parameters use @ParameterName (Dapper named binding)
    //    • TENANTID filter always included
    //    • No user-supplied values concatenated
    //    • Parameter names must match DAL anonymous-object property names exactly
    // ─────────────────────────────────────────────────────────────────────────
    public static class MatrixQB
    {
        // ── Get single matrix header ──────────────────────────────────────────
        // FIX-2: Added MX.MATRIXCODE lookup for SUPERSEDEDBYID (SB join).
        //        Added FgSkuCode / FgSkuName (SKU join was present but columns not projected).
        //        Added ProcessCode (MP join was present but PROCESSCODE not projected).
        //        Added ProcessGroupCode (PG join was present but PROCESSGROUPCODE not projected).
        //        Added OutputLotNumber (LOT join was present but LOTNUMBER not projected).
        //        Added MatrixModeName / BatchAssignModeName / MatrixStatusName as CASE expressions
        //        so the DTO display-name fields are populated without a second round-trip.
        //        TENANTID filter added to MALLOCATION join for multi-tenant safety.

        public const string GET_MATRIX_HEADER = @"
SELECT
    MX.MATRIXID             AS MatrixId,
    MX.MATRIXCODE           AS MatrixCode,
    MX.MATRIXMODE           AS MatrixMode,
    MX.MATRIXMODE           AS MatrixModeName,

    MX.BATCHASSIGNMODE      AS BatchAssignMode,
    MX.BATCHASSIGNMODE      AS BatchAssignModeName,

    MX.MATRIXSTATUS         AS MatrixStatus,
    MX.MATRIXSTATUS         AS MatrixStatusName,

    MX.ALLOCATIONID         AS AllocationId,
    AL.ALLOCATIONNAME       AS AllocationName,

    MX.MMDETAILID           AS MmDetailId,

    MX.ALLOCATIONSLNO       AS AllocationSlNo,
    MX.SETFROM              AS SetFrom,
    MX.SETTO                AS SetTo,

    MX.FGITEMID             AS FgItemId,
    FGI.ITEMCODE            AS FgItemCode,
    FGI.ITEMNAME            AS FgItemName,

    MX.FGSKUID              AS FgSkuId,
    SKU.SKUCODE             AS FgSkuCode,
    SKU.SKUNAME             AS FgSkuName,

    MX.PROCESSID            AS ProcessId,
    MP.PROCESSCODE          AS ProcessCode,
    MP.PROCESSNAME          AS ProcessName,

    MX.PROCESSGROUPID       AS ProcessGroupId,
    PG.PROCESSGROUPCODE     AS ProcessGroupCode,
    PG.PROCESSGROUPNAME     AS ProcessGroupName,

    MX.OUTPUTLOTID          AS OutputLotId,
    LOT.LOTNUMBER           AS OutputLotNumber,

    MX.PROCESSLOTQTY        AS ProcessLotQty,

    MX.SUPERSEDEDBYID       AS SupersededById,
    SB.MATRIXCODE           AS SupersededByMatrixCode,

    MX.ACTIVATEDON          AS ActivatedOn,
    MX.REMARKS              AS Remarks,

    MX.VERSION              AS Version,
    MX.STATUS               AS Status,
    MX.SORTORDER            AS SortOrder,
    MX.SOURCETYPE           AS SourceType,
    MX.TENANTID             AS TenantId,

    MX.CREATEDBYID          AS CreatedById,
    MX.CREATEDON            AS CreatedOn,
    MX.MODIFIEDBYID         AS ModifiedById,
    MX.MODIFIEDON           AS ModifiedOn

FROM TMATRIX MX

JOIN MALLOCATION AL
    ON  AL.ALLOCATIONID = MX.ALLOCATIONID

LEFT JOIN MITEM FGI
    ON  FGI.ITEMID  = MX.FGITEMID

LEFT JOIN MSKU SKU
    ON  SKU.SKUID  = MX.FGSKUID

LEFT JOIN MPROCESS MP
    ON  MP.PROCESSID  = MX.PROCESSID

LEFT JOIN MPROCESSGROUP PG
    ON  PG.PROCESSGROUPID  = MX.PROCESSGROUPID

LEFT JOIN TLOT LOT
    ON  LOT.LOTID       = MX.OUTPUTLOTID

LEFT JOIN TMATRIX SB
    ON  SB.MATRIXID       = MX.SUPERSEDEDBYID

WHERE MX.MATRIXID = @MatrixId
AND   MX.TENANTID = @TenantId;";


        // ── Get all matrix headers for tenant ────────────────────────────────

        public const string GET_ALL_MATRIX_HEADERS = @"
SELECT
    MX.MATRIXID             AS MatrixId,
    MX.MATRIXCODE           AS MatrixCode,
    MX.MATRIXMODE           AS MatrixMode,
    MX.MATRIXMODE           AS MatrixModeName,
    MX.BATCHASSIGNMODE      AS BatchAssignMode,
    MX.BATCHASSIGNMODE      AS BatchAssignModeName,
    MX.MATRIXSTATUS         AS MatrixStatus,
    MX.MATRIXSTATUS         AS MatrixStatusName,
    MX.ALLOCATIONID         AS AllocationId,
    AL.ALLOCATIONNAME       AS AllocationName,
    MX.MMDETAILID           AS MmDetailId,
    MX.ALLOCATIONSLNO       AS AllocationSlNo,
    MX.SETFROM              AS SetFrom,
    MX.SETTO                AS SetTo,
    MX.FGITEMID             AS FgItemId,
    FGI.ITEMCODE            AS FgItemCode,
    FGI.ITEMNAME            AS FgItemName,
    MX.FGSKUID              AS FgSkuId,
    SKU.SKUCODE             AS FgSkuCode,
    SKU.SKUNAME             AS FgSkuName,
    MX.PROCESSID            AS ProcessId,
    MP.PROCESSCODE          AS ProcessCode,
    MP.PROCESSNAME          AS ProcessName,
    MX.PROCESSGROUPID       AS ProcessGroupId,
    PG.PROCESSGROUPCODE     AS ProcessGroupCode,
    PG.PROCESSGROUPNAME     AS ProcessGroupName,
    MX.OUTPUTLOTID          AS OutputLotId,
    LOT.LOTNUMBER           AS OutputLotNumber,
    MX.PROCESSLOTQTY        AS ProcessLotQty,
    MX.SUPERSEDEDBYID       AS SupersededById,
    SB.MATRIXCODE           AS SupersededByMatrixCode,
    MX.ACTIVATEDON          AS ActivatedOn,
    MX.REMARKS              AS Remarks,
    MX.VERSION              AS Version,
    MX.STATUS               AS Status,
    MX.SORTORDER            AS SortOrder,
    MX.SOURCETYPE           AS SourceType,
    MX.TENANTID             AS TenantId,
    MX.CREATEDBYID          AS CreatedById,
    MX.CREATEDON            AS CreatedOn,
    MX.MODIFIEDBYID         AS ModifiedById,
    MX.MODIFIEDON           AS ModifiedOn
FROM TMATRIX MX
JOIN  MALLOCATION AL ON AL.ALLOCATIONID = MX.ALLOCATIONID
LEFT JOIN MITEM FGI  ON FGI.ITEMID      = MX.FGITEMID
LEFT JOIN MSKU SKU   ON SKU.SKUID       = MX.FGSKUID
LEFT JOIN MPROCESS MP ON MP.PROCESSID   = MX.PROCESSID
LEFT JOIN MPROCESSGROUP PG ON PG.PROCESSGROUPID = MX.PROCESSGROUPID
LEFT JOIN TLOT LOT   ON LOT.LOTID       = MX.OUTPUTLOTID
LEFT JOIN TMATRIX SB ON SB.MATRIXID     = MX.SUPERSEDEDBYID
WHERE MX.TENANTID = @TenantId
AND   MX.STATUS  <> 2
ORDER BY MX.MATRIXID;";


        // ── Get all input lines for tenant ────────────────────────────────────

        public const string GET_ALL_MATRIX_INPUTS = @"
SELECT
    MI.MATRIXINPUTID    AS MatrixInputId,
    MI.MATRIXID         AS MatrixId,
    MI.SLNO             AS SlNo,
    MI.ITEMID           AS ItemId,
    IT.ITEMCODE         AS ItemCode,
    IT.ITEMNAME         AS ItemName,
    MI.SKUID            AS SkuId,
    SK.SKUCODE          AS SkuCode,
    SK.SKUNAME          AS SkuName,
    MI.ITEMCATEGORY     AS ItemCategory,
    CASE MI.ITEMCATEGORY
        WHEN 0 THEN 'RM'
        WHEN 1 THEN 'BO_Qty'
        WHEN 2 THEN 'BO_Serial'
        ELSE        'Unknown'
    END                 AS ItemCategoryName,
    MI.LOTID            AS LotId,
    LT.LOTNUMBER        AS BatchNo,
    LT.LOTNUMBER        AS LotNumber,
    MI.PLANNEDQTY               AS PlannedQty,
    MI.PLANNEDQTY               AS RequiredQty,
    CAST(0 AS NUMERIC(18,4))    AS QuantityPer,
    U.UOMCODE                   AS UomCode,
    MI.ALLOCATEDQTY             AS AllocatedQty,
    MI.CONSUMEDQTY              AS ConsumedQty,
    MI.MIXPROPORTION            AS MixProportion,
    MI.LINESTATUS       AS LineStatus,
    CASE MI.LINESTATUS
        WHEN 0 THEN 'Open'
        WHEN 1 THEN 'Partial'
        WHEN 2 THEN 'Consumed'
        WHEN 3 THEN 'Cancelled'
        ELSE        'Unknown'
    END                 AS LineStatusName,
    MI.TENANTID         AS TenantId
FROM  TMATRIXINPUT MI
JOIN  MITEM  IT ON IT.ITEMID = MI.ITEMID
JOIN  MSKU   SK ON SK.SKUID  = MI.SKUID
LEFT JOIN TLOT LT ON LT.LOTID = MI.LOTID AND MI.LOTID <> -1
LEFT JOIN MUOM U  ON U.UOMID  = IT.STOCKUOMID
WHERE MI.TENANTID = @TenantId
ORDER BY MI.MATRIXID, MI.SLNO;";


        // ── Get all output rows for tenant ────────────────────────────────────

        public const string GET_ALL_MATRIX_OUTPUTS = @"
SELECT
    MO.MATRIXOUTPUTID   AS MatrixOutputId,
    MO.MATRIXID         AS MatrixId,
    MO.SLNO             AS SlNo,
    MO.OUTPUTLEVEL      AS OutputLevel,
    CASE MO.OUTPUTLEVEL
        WHEN 0 THEN 'Set'
        WHEN 1 THEN 'FGSerial'
        WHEN 2 THEN 'MixOutputLot'
        ELSE        'Unknown'
    END                 AS OutputLevelName,
    MO.ITEMID           AS ItemId,
    I.ITEMCODE          AS ItemCode,
    I.ITEMNAME          AS ItemName,
    MO.SKUID            AS SkuId,
    S.SKUCODE           AS SkuCode,
    S.SKUNAME           AS SkuName,
    MO.LOTID            AS LotId,
    LT.LOTNUMBER        AS LotNumber,
    MO.LOTQUANTITY              AS LotQuantity,
    MO.LOTQUANTITY              AS RequiredQty,
    CAST(0 AS NUMERIC(18,4))    AS QuantityPer,
    U.UOMCODE                   AS UomCode,
    MO.TENANTID                 AS TenantId
FROM  TMATRIXOUTPUT MO
LEFT JOIN MITEM I  ON I.ITEMID  = MO.ITEMID AND MO.ITEMID <> -1
LEFT JOIN MSKU  S  ON S.SKUID   = MO.SKUID  AND MO.SKUID  <> -1
LEFT JOIN TLOT  LT ON LT.LOTID  = MO.LOTID  AND MO.LOTID  <> -1
LEFT JOIN MUOM  U  ON U.UOMID   = I.STOCKUOMID AND MO.ITEMID <> -1
WHERE MO.TENANTID = @TenantId
ORDER BY MO.MATRIXID, MO.SLNO;";


        // ── Get input lines for a matrix ──────────────────────────────────────
        // FIX-3: Added ItemCategoryName and LineStatusName as CASE expressions
        //        so DTO display-name fields are populated on read.
        //        LotNumber alias corrected: DTO has both BatchNo and LotNumber
        //        mapped from the same LOTNUMBER column — added explicit LotNumber alias.

        public const string GET_MATRIX_INPUTS = @"
SELECT
    MI.MATRIXINPUTID    AS MatrixInputId,
    MI.MATRIXID         AS MatrixId,
    MI.SLNO             AS SlNo,

    MI.ITEMID           AS ItemId,
    IT.ITEMCODE         AS ItemCode,
    IT.ITEMNAME         AS ItemName,

    MI.SKUID            AS SkuId,
    SK.SKUCODE          AS SkuCode,
    SK.SKUNAME          AS SkuName,

    MI.ITEMCATEGORY     AS ItemCategory,
    CASE MI.ITEMCATEGORY
        WHEN 0 THEN 'RM'
        WHEN 1 THEN 'BO_Qty'
        WHEN 2 THEN 'BO_Serial'
        ELSE        'Unknown'
    END                 AS ItemCategoryName,

    MI.LOTID            AS LotId,
    LT.LOTNUMBER        AS BatchNo,
    LT.LOTNUMBER        AS LotNumber,

    MI.PLANNEDQTY               AS PlannedQty,
    MI.PLANNEDQTY               AS RequiredQty,
    CAST(0 AS NUMERIC(18,4))    AS QuantityPer,
    U.UOMCODE                   AS UomCode,
    MI.ALLOCATEDQTY             AS AllocatedQty,
    MI.CONSUMEDQTY              AS ConsumedQty,
    MI.MIXPROPORTION            AS MixProportion,

    MI.LINESTATUS       AS LineStatus,
    CASE MI.LINESTATUS
        WHEN 0 THEN 'Open'
        WHEN 1 THEN 'Partial'
        WHEN 2 THEN 'Consumed'
        WHEN 3 THEN 'Cancelled'
        ELSE        'Unknown'
    END                 AS LineStatusName,

    MI.TENANTID         AS TenantId

FROM  TMATRIXINPUT MI
JOIN  MITEM  IT ON IT.ITEMID = MI.ITEMID
LEFT JOIN MSKU SK ON SK.SKUID = MI.SKUID AND MI.SKUID <> -1
LEFT JOIN TLOT LT ON LT.LOTID = MI.LOTID AND MI.LOTID <> -1
LEFT JOIN MUOM U  ON U.UOMID  = IT.STOCKUOMID

WHERE MI.MATRIXID = @MatrixId
AND   MI.TENANTID = @TenantId
ORDER BY MI.SLNO;";

        // ── Get output counts (sets and serials) per matrix ───────────────────
        // Correct shape: one row per OUTPUTLEVEL, mapped to OutputLevelCount
        // private class in DAL (OutputLevel + OutputCount).

        public const string GET_MATRIX_OUTPUT_COUNTS = @"
SELECT
    OUTPUTLEVEL     AS OutputLevel,
    COUNT(*)        AS OutputCount
FROM  TMATRIXOUTPUT
WHERE MATRIXID    = @MatrixId
AND   TENANTID    = @TenantId
AND   OUTPUTLEVEL IN (0, 1)
GROUP BY OUTPUTLEVEL;";


        // ── Check MatrixCode uniqueness ───────────────────────────────────────
        // FIX-4: STATUS <> 2 guard kept (excludes soft-deleted rows).
        //        MATRIXSTATUS <> 3 added as belt-and-braces for Deleted status.

        // ── Validation: does this AllocationId resolve to a real MALLOCATION row? ─
        // TMATRIX.ALLOCATIONID has FK_TMATRIX_ALLOCATIONID → MALLOCATION.ALLOCATIONID
        // (the SO's own HEADER allocation id) — NOT the AllotedAllocationId of a
        // specific pending-allocation line. Sending the wrong one (a common mix-up,
        // since picklists like GetSelectListAllocationWithItem key on AllotedAllocationId)
        // fails the raw INSERT with an opaque SQL FK error; this lets SaveMatrixAsync
        // catch it up front with a clear message instead.
        public const string CHECK_ALLOCATION_EXISTS = @"
SELECT COUNT(*)
FROM MALLOCATION
WHERE ALLOCATIONID = @AllocationId
AND   ALLOCATIONID < -1
AND   TENANTID      = @TenantId";

        public const string CHECK_DUPLICATE_CODE = @"
SELECT COUNT(*)
FROM  TMATRIX
WHERE MATRIXCODE    = @MatrixCode
AND   MATRIXID     <> @ExcludeMatrixId
AND   TENANTID      = @TenantId
AND   STATUS       <> 2
AND   MATRIXSTATUS <> 3;";


        // ── Check set range overlap for same AllocationId ─────────────────────
        // Returns count > 0 if overlap found.
        // @ExcludeMatrixId excludes the matrix being edited or superseded.
        // FIX-5: MATRIXSTATUS <> 3 added so soft-deleted rows don't block new ranges.

        public const string CHECK_SET_OVERLAP = @"
SELECT COUNT(*)
FROM  TMATRIX
WHERE ALLOCATIONID  = @AllocationId
AND   MATRIXID     <> @ExcludeMatrixId
AND   MATRIXMODE    = @MatrixMode
AND   MATRIXSTATUS  = 1
AND   STATUS       <> 2
AND   MATRIXSTATUS <> 3
AND   TENANTID      = @TenantId
AND   SETFROM      <= @SetTo
AND   SETTO        >= @SetFrom;";


        // ── Get next AllocationSlNo for a given AllocationId ──────────────────
        // Returns MAX(ALLOCATIONSLNO) + 1; ISNULL handles no-rows case (returns 1).
        // FIX-6: MATRIXSTATUS <> 3 added so deleted rows don't inflate the sequence.

        public const string GET_NEXT_ALLOCATION_SLNO = @"
SELECT ISNULL(MAX(ALLOCATIONSLNO), 0) + 1
FROM  TMATRIX
WHERE ALLOCATIONID  = @AllocationId
AND   TENANTID      = @TenantId
AND   STATUS       <> 2
AND   MATRIXSTATUS <> 3;";


        // ── Check stock position before activation ────────────────────────────
        // Returns shortage lines only (NetQty < AllocatedQty).
        // Uses CROSS APPLY on TLOTDETAIL to compute live stock balance.
        // FIX-7: LotNumber alias added so DTO.BatchNo is populated in shortage results.
        //        AND MI.LOTID <> -1 guard ensures unassigned lines are excluded.

        public const string CHECK_STOCK_POSITION = @"
SELECT
    MI.MATRIXINPUTID                        AS MatrixInputId,
    MI.ITEMID                               AS ItemId,
    IT.ITEMCODE                             AS ItemCode,
    IT.ITEMNAME                             AS ItemName,
    MI.SKUID                                AS SkuId,
    LT.LOTNUMBER                            AS BatchNo,
    LT.LOTNUMBER                            AS LotNumber,
    MI.ALLOCATEDQTY                         AS AllocatedQty,
    LB.NetQty                               AS FreeQty,
    MI.ALLOCATEDQTY - LB.NetQty             AS ShortageQty
FROM  TMATRIXINPUT MI
JOIN  MITEM IT
    ON  IT.ITEMID = MI.ITEMID
JOIN  TLOT LT
    ON  LT.LOTID  = MI.LOTID
CROSS APPLY (
    SELECT ISNULL(SUM(LD.GOODQUANTITY), 0) AS NetQty
    FROM   TLOTDETAIL LD
    WHERE  LD.LOTID = MI.LOTID
) LB
WHERE MI.MATRIXID     = @MatrixId
AND   MI.ITEMCATEGORY = 0
AND   MI.LOTID       <> -1
AND   MI.TENANTID     = @TenantId
AND   LB.NetQty       < MI.ALLOCATEDQTY;";


        // ── BOM explosion: single query via PRODUCTIONITEMID ─────────────────
        // Resolves the BOM and explodes components in one pass.
        // The inner SELECT TOP 1 picks the most recently created active default-version
        // BOM for the given ProductItemId — handles duplicate ISDEFAULTVERSION=1 rows.

        public const string EXPLODE_BOM_FOR_ITEM = @"
SELECT
    BD.ITEMID       AS ItemId,
    I.ITEMCODE      AS ItemCode,
    I.ITEMNAME      AS ItemName,
    BD.SKUID        AS SkuId,
    S.SKUCODE       AS SkuCode,
    S.SKUNAME       AS SkuName,
    SUM(BD.FinalQUANTITY * @PlannedQty) AS RequiredQty,
    u.uomcode
FROM mitem a
INNER JOIN MBOM B         ON a.defaultbomid = b.bomid
                          AND b.TENANTID     = @TenantId
INNER JOIN MBOMDETAIL BD  ON BD.BOMID = B.BOMID
LEFT  JOIN MITEM I        ON I.ITEMID = BD.ITEMID
LEFT  JOIN MSKU  S        ON S.SKUID  = BD.SKUID
LEFT  JOIN MITEMOU itemou ON i.itemid = itemou.itemid AND itemou.ouid = @OuId
INNER JOIN muom u         ON i.STOCKUOMID = u.uomid
WHERE a.ITEMID     = @ProductItemId
AND   a.TENANTID   = @TenantId
AND   a.STATUS     IN (0, 1)
AND   BD.BOMLEVEL  NOT IN (0)
AND   i.ISMAKEORBUY  = 1
AND   i.isbatchstock = 0
AND   i.inspectiontype = 2
GROUP BY
    BD.ITEMID, I.ITEMCODE, I.ITEMNAME,
    BD.SKUID, S.SKUCODE, S.SKUNAME, u.uomcode;";


        // ── BOM explosion: Output lines (BOMLEVEL = 0) — production item row ─
        // Same BOM resolution as EXPLODE_BOM_FOR_ITEM; only the BOMLEVEL filter differs.

        public const string EXPLODE_BOM_OUTPUT_FOR_ITEM = @"
SELECT
    MIN(BD.SLNO)                         AS SlNo,
    BD.ITEMID                            AS ItemId,
    I.ITEMCODE                           AS ItemCode,
    I.ITEMNAME                           AS ItemName,
    BD.SKUID                             AS SkuId,
    S.SKUCODE                            AS SkuCode,
    S.SKUNAME                            AS SkuName,
    SUM(BD.FinalQUANTITY * @PlannedQty)  AS RequiredQty,
    -- QuantityPer must be RequiredQty for ONE unit (PlannedQty=1). MBOMDETAIL.QUANTITY
    -- is empty/zero across this master data — FINALQUANTITY is the column that's
    -- actually populated (it's what RequiredQty itself is derived from), so use it
    -- here too instead of a column with no real data.
    SUM(BD.FinalQUANTITY)                AS QuantityPer,
    u.uomcode                            AS UomCode,
    CAST(@PlannedQty AS INT)             AS SetNumber
FROM mitem a
INNER JOIN MBOM B         ON a.defaultbomid = b.bomid
                          AND b.TENANTID     = @TenantId
INNER JOIN MBOMDETAIL BD  ON BD.BOMID = B.BOMID
LEFT  JOIN MITEM I        ON I.ITEMID = BD.ITEMID
LEFT  JOIN MSKU  S        ON S.SKUID  = BD.SKUID
LEFT  JOIN MITEMOU itemou ON i.itemid = itemou.itemid AND itemou.ouid = @OuId
INNER JOIN muom u         ON i.STOCKUOMID = u.uomid
WHERE a.ITEMID    = @ProductItemId
AND   a.TENANTID  = @TenantId
AND   a.STATUS    IN (0, 1)
AND   BD.BOMLEVEL IN (0)
GROUP BY
    BD.ITEMID, I.ITEMCODE, I.ITEMNAME,
    BD.SKUID, S.SKUCODE, S.SKUNAME, u.uomcode;";



        // ── BOM explosion: Mode 0 — via MITEM.DEFAULTBOMID ───────────────────
        // Used when MatrixMode=Product and no explicit BomId is supplied.

        public const string EXPLODE_BOM_FOR_PRODUCT = @"
SELECT
    BD.ITEMID                           AS ItemId,
    I.ITEMCODE                          AS ItemCode,
    I.ITEMNAME                          AS ItemName,

    BD.SKUID                            AS SkuId,
    S.SKUCODE                           AS SkuCode,
    S.SKUNAME                           AS SkuName,

    CAST(0 AS TINYINT)                  AS ItemCategory,
    CAST(-1 AS INT)                     AS LotId,
    NULL                                AS BatchNo,
    NULL                                AS LotNumber,

    B.BOMID                             AS BomId,
    B.BOMCODE                           AS BomCode,
    BD.UOMID                            AS UomId,

    SUM(BD.QUANTITY)                    AS QtyPer,
    SUM(BD.FINALQUANTITY) * @PlannedQty           AS PlannedQty,
    CAST(0 AS NUMERIC(18,4))            AS AllocatedQty,
    CAST(0 AS NUMERIC(18,4))            AS ConsumedQty,
    CAST(0 AS NUMERIC(9,4))             AS MixProportion,
    CAST(0 AS TINYINT)                  AS LineStatus

FROM       MITEM      A
INNER JOIN MBOM       B  ON B.BOMID    = A.DEFAULTBOMID
                        AND B.TENANTID = @TenantId
INNER JOIN MBOMDETAIL BD ON BD.BOMID   = B.BOMID
INNER JOIN MITEM      I  ON I.ITEMID   = BD.ITEMID
LEFT  JOIN MSKU       S  ON S.SKUID    = BD.SKUID

WHERE A.ITEMID    = @ProductItemId
AND   A.TENANTID  = @TenantId
AND   A.STATUS    IN (0, 1)
AND   BD.BOMLEVEL NOT IN (0)

GROUP BY
    BD.ITEMID, I.ITEMCODE, I.ITEMNAME,
    BD.SKUID,  S.SKUCODE,  S.SKUNAME,
    B.BOMID,   B.BOMCODE,  BD.UOMID;";


        // ── BOM explosion: Mode 1 — via MBOM.PRODUCTIONITEMID ─────────────────
        // Used when MatrixMode=FGItem and no explicit BomId is supplied.
        // Picks the active default-version BOM for the FG item.

        public const string EXPLODE_BOM_FOR_FGITEM = @"
SELECT
    BD.ITEMID                           AS ItemId,
    I.ITEMCODE                          AS ItemCode,
    I.ITEMNAME                          AS ItemName,

    BD.SKUID                            AS SkuId,
    S.SKUCODE                           AS SkuCode,
    S.SKUNAME                           AS SkuName,

    CAST(0 AS TINYINT)                  AS ItemCategory,
    CAST(-1 AS INT)                     AS LotId,
    NULL                                AS BatchNo,
    NULL                                AS LotNumber,

    B.BOMID                             AS BomId,
    B.BOMCODE                           AS BomCode,
    BD.UOMID                            AS UomId,

    SUM(BD.QUANTITY)                    AS QtyPer,
    SUM(BD.QUANTITY) * @PlannedQty      AS PlannedQty,
    CAST(0 AS NUMERIC(18,4))            AS AllocatedQty,
    CAST(0 AS NUMERIC(18,4))            AS ConsumedQty,
    CAST(0 AS NUMERIC(9,4))             AS MixProportion,
    CAST(0 AS TINYINT)                  AS LineStatus

FROM       MBOM       B
INNER JOIN MBOMDETAIL BD ON BD.BOMID   = B.BOMID
INNER JOIN MITEM      I  ON I.ITEMID   = BD.ITEMID
LEFT  JOIN MSKU       S  ON S.SKUID    = BD.SKUID

WHERE B.PRODUCTIONITEMID  = @FgItemId
AND   B.TENANTID          = @TenantId
AND   B.STATUS            = 1
AND   B.ISDEFAULTVERSION  = 1
AND   BD.BOMLEVEL         NOT IN (0)

GROUP BY
    BD.ITEMID, I.ITEMCODE, I.ITEMNAME,
    BD.SKUID,  S.SKUCODE,  S.SKUNAME,
    B.BOMID,   B.BOMCODE,  BD.UOMID;";


        // ── BOM explosion: explicit BomId — full per-line detail ─────────────
        // One row per MBOMDETAIL line (no GROUP BY) so BomLineId, BomLevel,
        // ProductionStage, MaterialType etc. are all meaningful.
        // PlannedQty = FINALQUANTITY / PRODUCTIONQTY * @PlannedQty
        //   — FINALQUANTITY already includes waste + adjustment per BOM ref qty
        //   — dividing by PRODUCTIONQTY normalises to per-unit before scaling to sets

        public const string EXPLODE_BOM_BY_BOMID = @"
SELECT
    -- BOM line identity
    BD.BOMLINEID                                                AS BomLineId,
    BD.SLNO                                                     AS BomSlNo,
    BD.BOMLEVEL                                                 AS BomLevel,
    BD.PRODUCTIONSTAGE                                          AS ProductionStage,
    BD.MATERIALTYPE                                             AS MaterialType,
    BD.IOTYPE                                                   AS IoType,
    BD.CALCULATIONTYPE                                          AS CalculationType,

    -- BOM header
    B.BOMID                                                     AS BomId,
    B.BOMCODE                                                   AS BomCode,
    B.PRODUCTIONQTY                                             AS BomProductionQty,

    -- Component item
    BD.ITEMID                                                   AS ItemId,
    I.ITEMCODE                                                  AS ItemCode,
    I.ITEMNAME                                                  AS ItemName,
    I.ITEMSHORTNAME                                             AS ItemShortName,
    I.STOCKUOMID                                                AS StockUomId,
    I.ISBATCHSTOCK                                              AS IsBatchStock,
    I.ISSKUTRACKINGAPPLICABLE                                   AS IsSkuTrackingApplicable,
    I.CATEGORYID                                                AS CategoryId,
    I.SUBCATEGORYID                                             AS SubCategoryId,
    I.ITEMGROUPID                                               AS ItemGroupId,

    -- Component SKU
    BD.SKUID                                                    AS SkuId,
    S.SKUCODE                                                   AS SkuCode,
    S.SKUNAME                                                   AS SkuName,

    -- Planning defaults (not yet allocated)
    CAST(0 AS TINYINT)                                          AS ItemCategory,
    CAST(-1 AS INT)                                             AS LotId,
    NULL                                                        AS BatchNo,
    NULL                                                        AS LotNumber,

    -- BOM quantity detail
    BD.UOMID                                                    AS UomId,
    BD.QUANTITY                                                 AS QtyPer,
    BD.PERCENTAGE                                               AS BomPercentage,
    BD.PERPRODUCTIONQUANTITY                                    AS PerProductionQuantity,
    BD.REQUIREDPRODUCTIONQUANTITY                               AS RequiredProductionQuantity,
    BD.WASTEPERCENTAGE                                          AS WastePercentage,
    BD.NETQUANTITY                                              AS NetQuantity,
    BD.FINALQUANTITY                                            AS FinalQuantity,

    -- PlannedQty: scale FINALQUANTITY (per BOM ref qty) to actual set count
    CAST(
        BD.FINALQUANTITY / NULLIF(B.PRODUCTIONQTY, 0) * @PlannedQty
    AS NUMERIC(18,4))                                           AS PlannedQty,

    CAST(0 AS NUMERIC(18,4))                                    AS AllocatedQty,
    CAST(0 AS NUMERIC(18,4))                                    AS ConsumedQty,
    CAST(0 AS NUMERIC(9,4))                                     AS MixProportion,
    CAST(0 AS TINYINT)                                          AS LineStatus

FROM       MBOM       B
INNER JOIN MBOMDETAIL BD ON BD.BOMID  = B.BOMID
INNER JOIN MITEM      I  ON I.ITEMID  = BD.ITEMID
LEFT  JOIN MSKU       S  ON S.SKUID   = BD.SKUID

WHERE B.BOMID     = @BomId
AND   B.TENANTID  = @TenantId
AND   B.STATUS    = 1
AND   BD.BOMLEVEL NOT IN (0)

ORDER BY BD.SLNO, BD.BOMLEVEL;";


        // ── Check whether output lots have already been generated for a matrix ──
        // Returns 1 if TMATRIXOUTPUT has any rows for the given MatrixId, else 0.
        // Used by ActivateMatrix to skip lot generation when pre-generated.

        public const string GET_HAS_OUTPUTS = @"
SELECT CASE WHEN EXISTS (
    SELECT 1 FROM TMATRIXOUTPUT
    WHERE  MATRIXID  = @MatrixId
    AND    TENANTID  = @TenantId
) THEN 1 ELSE 0 END;";

        // ── Reservation policy for a business transaction type ─────────────────
        // CK_MBIZTRANSACTIONTYPE_RESERVATIONTYPE: 0=Not Required, 1=Auto, 2=Manual.
        // Callers use this to decide whether SaveMatrix should auto-create
        // TRESERVATION rows for a matrix's input lines.
        public const string GET_BIZTRANSACTIONTYPE_RESERVATIONTYPE = @"
SELECT RESERVATIONTYPE
FROM MBIZTRANSACTIONTYPE
WHERE BIZTRANSACTIONTYPEID = @BizTransactionTypeId";

        // ── Resolve a BizTransactionTypeId dynamically by its system code ──────
        // Replaces hardcoded IDs like "LTPRD" (-1499999569) baked into BLL code.
        // Verified live on 217.216.78.142/VFGPLTEST: BIZTRANSACTIONTYPECODE = 'LTPRD'
        // ("LotProduction") is NOT unique per TenantId alone — 3 rows exist for the
        // same tenant (-1399999727), one per OUID. Must also filter by OUID or the
        // pick is non-deterministic; falls back to the tenant-wide row with no OUID
        // match if this specific OU has no row of its own (mirrors how OuId is
        // optional/nullable-scoped elsewhere in this module, e.g. MITEMOU joins).
        public const string GET_BIZTRANSACTIONTYPE_ID_BY_CODE = @"
SELECT TOP 1 BIZTRANSACTIONTYPEID
FROM MBIZTRANSACTIONTYPE
WHERE BIZTRANSACTIONTYPECODE = @Code
AND   TENANTID               = @TenantId
ORDER BY CASE WHEN OUID = @OuId THEN 0 ELSE 1 END, BIZTRANSACTIONTYPEID";

        // ── Resolve an MENTITY.ENTITYID by its ENTITYCODE ───────────────────────
        // Replaces hardcoded IDs like "MATRIX"/"MATRIXOUTPUT" baked into BLL code.
        // MENTITY is a global master (no TENANTID/OUID column, verified live on
        // 217.216.78.142/VFGPLTEST) — ENTITYCODE is unique on its own, no per-OU
        // duplication like MBIZTRANSACTIONTYPE.
        public const string GET_ENTITY_ID_BY_CODE = @"
SELECT ENTITYID
FROM MENTITY
WHERE ENTITYCODE = @Code";

        // ── Lookup LotType IDs (SET and FGSERIAL) for activation ─────────────
        // Match by SYSTEMPREFIX — system-defined identifier, not user-editable LOTTYPENAME.

        public const string GET_LOT_TYPE_IDS = @"
SELECT
    LOTTYPEID                       AS LotTypeId,
    ISNULL(SYSTEMPREFIX, '')        AS SystemPrefix,
    PREFIX                          AS Prefix,
    SUFFIX                          AS Suffix,
    CAST(ISNULL(FROMNUMBER, '1') AS INT)        AS FromNumber,
    CAST(ISNULL(LOTNUMBERSIZE, 12) AS INT)      AS LotNumberSize
FROM MLOTTYPE
WHERE TENANTID = @TenantId
AND   STATUS   = 1";


        public const string GET_LAST_LOT_SEQUENCE = @"
SELECT ISNULL(MAX(
    TRY_CAST(
        SUBSTRING(
            T.LOTNUMBER,
            LEN(@Prefix) + 1,
            LEN(T.LOTNUMBER) - LEN(@Prefix) - LEN(@Suffix)
        ) AS INT
    )
), 0)
FROM TLOT T WITH (UPDLOCK, HOLDLOCK)
INNER JOIN MLOTTYPE LT ON LT.LOTTYPEID = T.LOTTYPEID
WHERE LT.TENANTID  = @TenantId
AND   T.LOTNUMBER  LIKE @Prefix + '%'
AND   T.LOTNUMBER  LIKE '%' + @Suffix";

        // ── Non-locking read of the same sequence, for read-only preview/display
        //    purposes only (Product Composition dashboard, Generate Now preview).
        //    Never use this ahead of an actual lot-number assignment — use
        //    GET_LAST_LOT_SEQUENCE (with UPDLOCK, HOLDLOCK) for that.
        public const string GET_LAST_LOT_SEQUENCE_PREVIEW = @"
SELECT ISNULL(MAX(
    TRY_CAST(
        SUBSTRING(
            T.LOTNUMBER,
            LEN(@Prefix) + 1,
            LEN(T.LOTNUMBER) - LEN(@Prefix) - LEN(@Suffix)
        ) AS INT
    )
), 0)
FROM TLOT T
INNER JOIN MLOTTYPE LT ON LT.LOTTYPEID = T.LOTTYPEID
WHERE LT.TENANTID  = @TenantId
AND   T.LOTNUMBER  LIKE @Prefix + '%'
AND   T.LOTNUMBER  LIKE '%' + @Suffix";

        // ── Locking read of the last issued serial for ONE item — TLOT only.
        //    WITH (UPDLOCK, HOLDLOCK), same pattern as GET_LAST_LOT_SEQUENCE, so it
        //    must run inside the caller's transaction right before the insert —
        //    serializes concurrent SaveSetSerialGenerationAsync calls against the
        //    same item so two callers can't read the same last serial and generate
        //    colliding LotNumbers ("{ItemCode}-{NNNN}"). TLOT has no TENANTID column
        //    (verified live on 217.216.78.142/VFGPLTEST) — tenant is scoped via the
        //    MITEM join instead, same as how GET_LAST_LOT_SEQUENCE scopes via MLOTTYPE.
        public const string GET_LAST_SERIAL_FOR_ITEM_LOCKING = @"
SELECT TOP 1 T.LOTNUMBER
FROM TLOT T WITH (UPDLOCK, HOLDLOCK)
INNER JOIN MITEM MI ON MI.ITEMID = T.ITEMID
WHERE T.ITEMID    = @ItemId
AND   MI.TENANTID = @TenantId
ORDER BY T.LOTID DESC";

        // ── Current stock + last issued serial per item — TLOT / TLOTDETAIL only.
        //    Deliberately does NOT touch any TMATRIX* table. TLOT/TLOTDETAIL have no
        //    TENANTID column (verified live on 217.216.78.142/VFGPLTEST) — tenant is
        //    scoped via the MITEM join instead.
        public const string GET_STOCK_AND_LAST_SERIAL_FOR_ITEMS = @"
SELECT
    LT.ITEMID AS ItemId,

    ISNULL(SUM(CASE WHEN LD.GOODQUANTITY > 0 THEN LD.GOODQUANTITY ELSE 0 END), 0)
    - ISNULL(ABS(SUM(CASE WHEN LD.GOODQUANTITY < 0 THEN LD.GOODQUANTITY ELSE 0 END)), 0)
                                AS TotalQuantity,

    (
        SELECT TOP 1 T2.LOTNUMBER
        FROM   TLOT T2
        WHERE  T2.ITEMID = LT.ITEMID
        ORDER BY T2.LOTID DESC
    )                           AS LastSerialNo

FROM TLOT LT
INNER JOIN MITEM MI ON MI.ITEMID = LT.ITEMID
LEFT JOIN TLOTDETAIL LD ON LD.LOTID = LT.LOTID
WHERE LT.ITEMID   IN @ItemIds
AND   MI.TENANTID = @TenantId
GROUP BY LT.ITEMID";

        // ── Reserved qty per item — TRESERVATION only. Active reservations
        //    (STATUS = 1) whose BALANCEQUANTITY is still outstanding (not yet
        //    used/released). Deliberately does NOT filter by TENANTID — column
        //    presence on this table hasn't been verified as queryable.
        public const string GET_RESERVED_QTY_FOR_ITEMS = @"
SELECT
    ITEMID                      AS ItemId,
    ISNULL(SUM(BALANCEQUANTITY), 0) AS ReservedQuantity
FROM TRESERVATION
WHERE ITEMID IN @ItemIds
AND   STATUS  = 1
GROUP BY ITEMID";

        // ── Skipped/available serials — TLOT rows with STATUS = 0. Written by
        //    SaveSetSerialGenerationAsync when the user deselects a serial in the
        //    FG View before saving: the row exists (so the number is never
        //    reissued) but was never actually used. Offered back here so a future
        //    generation run can let the user manually reuse one instead of it
        //    sitting unused forever.
        public const string GET_SKIPPED_SERIALS_FOR_ITEM = @"
SELECT
    LT.LOTID     AS LotId,
    LT.LOTNUMBER AS LotNumber,
    LT.ITEMID    AS ItemId,
    LT.LOTDATE   AS LotDate
FROM TLOT LT
WHERE LT.ITEMID = @ItemId
AND   LT.STATUS = 0
ORDER BY LT.LOTID";

        // ── Existing ISSUED serials — TLOT rows with STATUS = 1, i.e. real
        //    active stock (the units making up TotalQuantity/AvailableQuantity
        //    on the Composition table). Offered as a pickable option alongside
        //    skipped serials so the user can satisfy a run's requirement using
        //    stock that already physically exists, instead of only generating
        //    brand-new numbers. Excludes lots that have ALREADY been linked to
        //    some other run — SaveSetSerialGenerationAsync marks a link with a
        //    quantity-neutral TLOTDETAIL row (GOODQUANTITY = 0); once linked, a
        //    lot can't be picked again for a different allocation (a physical
        //    unit is only ever counted toward one run at a time).
        public const string GET_ISSUED_SERIALS_FOR_ITEM = @"
SELECT
    LT.LOTID     AS LotId,
    LT.LOTNUMBER AS LotNumber,
    LT.ITEMID    AS ItemId,
    LT.LOTDATE   AS LotDate
FROM TLOT LT
WHERE LT.ITEMID = @ItemId
AND   LT.STATUS = 1
AND   NOT EXISTS (
    SELECT 1 FROM TLOTDETAIL LD
    WHERE LD.LOTID = LT.LOTID
    AND   LD.GOODQUANTITY = 0
)
ORDER BY LT.LOTID";

        // ── Resolve one FG output component's QtyPerSet directly by its OWN
        //    ItemId — used by GetSelectableSerials, which only receives the
        //    component's ItemId (not the parent product/allocation context).
        //    Same BOMLEVEL=0 output-line resolution as EXPLODE_BOM_OUTPUT_FOR_ITEM,
        //    just entered from the component side instead of the parent side.
        public const string GET_BOM_OUTPUT_QTY_PER_SET_FOR_ITEM = @"
SELECT TOP 1
    BD.ITEMID              AS ItemId,
    I.ITEMCODE              AS ItemCode,
    I.ITEMNAME              AS ItemName,
    SUM(BD.FINALQUANTITY)   AS QtyPerSet
FROM MBOMDETAIL BD
INNER JOIN MBOM B   ON B.BOMID = BD.BOMID
INNER JOIN MITEM A  ON A.DEFAULTBOMID = B.BOMID AND A.STATUS IN (0, 1)
INNER JOIN MITEM I  ON I.ITEMID = BD.ITEMID
WHERE BD.ITEMID = @ItemId
AND   BD.BOMLEVEL = 0
GROUP BY BD.ITEMID, I.ITEMCODE, I.ITEMNAME;";

        // ── Reactivate a previously-skipped serial — flips a Status=0 TLOT row
        //    (and its TLOTDETAIL) back to issued (Status=1, GoodQuantity=1) in
        //    place, instead of inserting a brand-new row. Used when the user
        //    picks a previously-skipped number (e.g. from GetSkippedSerials) to
        //    fulfill this run's required quantity. LotDetailId is looked up by
        //    LotId since one skipped TLOT row has exactly one TLOTDETAIL row.
        public const string REACTIVATE_SKIPPED_SERIAL = @"
UPDATE TLOT
SET STATUS = 1, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn
WHERE LOTID = @LotId AND STATUS = 0;

UPDATE TLOTDETAIL
SET QUANTITY = 1, GOODQUANTITY = 1, PLANNEDQUANTITY = 1,
    STOCKLEDGERNUMBER = @StockLedgerNumber, STOCKLEDGERDATE = @StockLedgerDate
WHERE LOTID = @LotId;";

        // FIX-10: Added explicit column aliases (LotTypeId, SystemPrefix) so
        //         the LotTypeRow sealed record in MatrixBLL maps correctly.
        //         Without aliases, Dapper maps by position on some providers
        //         which can silently swap the two columns.


        // ── Available RM batches for item/SKU lookup ──────────────────────────
        // FIX-11: WHERE clause was filtering LT.ITEMID = -1 AND LT.SKUID = -1
        //         which returns only the sentinel row — clearly wrong.
        //         Corrected to filter by the passed @ItemId and @SkuId parameters.
        //         @RequiredQty threshold in IsSufficient changed from hardcoded 5
        //         to @RequiredQty so the caller's actual requirement is used.
        // FIX-12: @TenantId was accepted as a query parameter but never referenced
        //         in the WHERE clause (multi-tenant safety hole) — now applied to
        //         LT.TENANTID. SkuId match now uses ISNULL(LT.SKUID,-1)=@SkuId so
        //         non-SKU-tracked lots (NULL SkuId) still match sentinel @SkuId=-1.

        public const string GET_AVAILABLE_BATCHES = @"
SELECT
    B.LotId,
    B.BatchNo,
    B.ItemId,
    B.ItemCode,
    B.ItemName,
    B.ReceivedDate,
    B.ReceivedQty,
    B.AllocatedQty,
    B.ConsumedQty,

    (B.ReceivedQty - B.ConsumedQty - B.AllocatedQty) AS FreeQty,

    CASE
        WHEN (B.ReceivedQty - B.ConsumedQty - B.AllocatedQty) >= @RequiredQty
        THEN CAST(1 AS BIT)
        ELSE CAST(0 AS BIT)
    END AS IsSufficient

FROM
(
    SELECT
        LT.LOTID             AS LotId,
        LT.LOTNUMBER         AS BatchNo,
        LT.ITEMID            AS ItemId,
        IT.ITEMCODE          AS ItemCode,
        IT.ITEMNAME          AS ItemName,
        LT.LOTDATE           AS ReceivedDate,

        ISNULL(LS.ReceivedQty, 0)  AS ReceivedQty,
        ISNULL(AL.AllocatedQty, 0) AS AllocatedQty,
        ISNULL(LS.ConsumedQty, 0)  AS ConsumedQty

    FROM TLOT LT

    INNER JOIN MITEM IT
        ON IT.ITEMID = LT.ITEMID

    OUTER APPLY
    (
        SELECT
            SUM(CASE
                    WHEN LD.GOODQUANTITY > 0
                    THEN LD.GOODQUANTITY
                    ELSE 0
                END) AS ReceivedQty,

            ABS(SUM(CASE
                    WHEN LD.GOODQUANTITY < 0
                    THEN LD.GOODQUANTITY
                    ELSE 0
                END)) AS ConsumedQty
        FROM TLOTDETAIL LD
        WHERE LD.LOTID = LT.LOTID
    ) LS

    OUTER APPLY
    (
        SELECT
            SUM(MI2.ALLOCATEDQTY) AS AllocatedQty
        FROM TMATRIXINPUT MI2
        WHERE MI2.LOTID = LT.LOTID
    ) AL

    WHERE
        LT.ITEMID = @ItemId
        AND ISNULL(LT.SKUID, -1) = @SkuId
        AND LT.STATUS = 1
        -- TLOT has no TENANTID column (confirmed live) — scoped via the mandatory
        -- MITEM join instead, same pattern used elsewhere in this file.
        AND IT.TENANTID = @TenantId

) B

ORDER BY
    B.ReceivedDate,
    B.BatchNo;";


        // ── Get MatrixId linked to an indent ──────────────────────────────────
        // FIX-12: Added TENANTID filter — was missing, unsafe in multi-tenant context.

        public const string GET_MATRIXID_FROM_INDENT = @"
SELECT TOP 1 MATRIXID
FROM  TINDENT
WHERE INDENTID = @IndentId
AND   TENANTID = @TenantId;";


        // ── Get single input line by matrix + item ────────────────────────────
        // FIX-13: AND MI.LOTID <> -1 sentinel guard added to TLOT LEFT JOIN.
        //         LotNumber alias added alongside BatchNo (both map from LOTNUMBER).
        //         ItemCategory and LineStatus CASE expressions added for display names
        //         so ValidateIssueAsync has ItemCategoryName / LineStatusName available
        //         if needed downstream without a second query.

        public const string GET_MATRIX_INPUT_FOR_ITEM = @"
SELECT
    MI.MATRIXINPUTID    AS MatrixInputId,
    MI.MATRIXID         AS MatrixId,
    MI.SLNO             AS SlNo,

    MI.ITEMID           AS ItemId,
    MI.SKUID            AS SkuId,

    MI.ITEMCATEGORY     AS ItemCategory,
    CASE MI.ITEMCATEGORY
        WHEN 0 THEN 'RM'
        WHEN 1 THEN 'BO_Qty'
        WHEN 2 THEN 'BO_Serial'
        ELSE        'Unknown'
    END                 AS ItemCategoryName,

    MI.LOTID            AS LotId,
    LT.LOTNUMBER        AS BatchNo,
    LT.LOTNUMBER        AS LotNumber,

    MI.PLANNEDQTY       AS PlannedQty,
    MI.ALLOCATEDQTY     AS AllocatedQty,
    MI.CONSUMEDQTY      AS ConsumedQty,
    MI.MIXPROPORTION    AS MixProportion,

    MI.LINESTATUS       AS LineStatus,
    CASE MI.LINESTATUS
        WHEN 0 THEN 'Open'
        WHEN 1 THEN 'Partial'
        WHEN 2 THEN 'Consumed'
        WHEN 3 THEN 'Cancelled'
        ELSE        'Unknown'
    END                 AS LineStatusName,

    MI.TENANTID         AS TenantId

FROM  TMATRIXINPUT MI
LEFT JOIN TLOT LT
    ON  LT.LOTID  = MI.LOTID
    AND MI.LOTID <> -1

WHERE MI.MATRIXID = @MatrixId
AND   MI.ITEMID   = @ItemId
AND   MI.TENANTID = @TenantId
ORDER BY MI.SLNO;";


        // ── Insert TMATRIX header ─────────────────────────────────────────────
        // Columns match live TMATRIX column list exactly (28 columns).
        // CreatedOn / ModifiedOn are stamped in DAL before this executes.

        public const string SAVE_MATRIX = @"
INSERT INTO TMATRIX (
    MATRIXID,       MATRIXCODE,     MATRIXMODE,     BATCHASSIGNMODE,
    ALLOCATIONID,   MMDETAILID,     ALLOCATIONSLNO,
    SETFROM,        SETTO,
    FGITEMID,       FGSKUID,        PROCESSID,
    PROCESSGROUPID, OUTPUTLOTID,    PROCESSLOTQTY,
    MATRIXSTATUS,   SUPERSEDEDBYID, ACTIVATEDON,    REMARKS,
    VERSION,        STATUS,         SORTORDER,      SOURCETYPE,
    TENANTID,
    CREATEDBYID,    CREATEDON,
    MODIFIEDBYID,   MODIFIEDON
)
VALUES (
    @MatrixId,      @MatrixCode,    @MatrixMode,    @BatchAssignMode,
    @AllocationId,  @MmDetailId,    @AllocationSlNo,
    @SetFrom,       @SetTo,
    @FgItemId,      @FgSkuId,       @ProcessId,
    @ProcessGroupId,@OutputLotId,   @ProcessLotQty,
    @MatrixStatus,  @SupersededById,@ActivatedOn,   @Remarks,
    @Version,       @Status,        @SortOrder,     @SourceType,
    @TenantId,
    @CreatedById,   @CreatedOn,
    @ModifiedById,  @ModifiedOn
);";


        // ── Update TMATRIX header (Draft only) ────────────────────────────────
        // VERSION = VERSION + 1 for optimistic concurrency tracking.
        // Guard: MATRIXSTATUS = 0 prevents editing Active / Superseded / Deleted rows.
        // FIX-14: PROCESSID added to UPDATE SET — was missing, would silently
        //         lose ProcessId changes on edit.

        public const string UPDATE_MATRIX = @"
UPDATE TMATRIX SET
    MATRIXCODE      = @MatrixCode,
    MATRIXMODE      = @MatrixMode,
    BATCHASSIGNMODE = @BatchAssignMode,
    ALLOCATIONID    = @AllocationId,
    MMDETAILID      = @MmDetailId,
    ALLOCATIONSLNO  = @AllocationSlNo,
    SETFROM         = @SetFrom,
    SETTO           = @SetTo,
    FGITEMID        = @FgItemId,
    FGSKUID         = @FgSkuId,
    PROCESSID       = @ProcessId,
    PROCESSGROUPID  = @ProcessGroupId,
    PROCESSLOTQTY   = @ProcessLotQty,
    REMARKS         = @Remarks,
    SORTORDER       = @SortOrder,
    VERSION         = VERSION + 1,
    MODIFIEDBYID    = @ModifiedById,
    MODIFIEDON      = @ModifiedOn
WHERE MATRIXID     = @MatrixId
AND   MATRIXSTATUS = 0
AND   TENANTID     = @TenantId;";


        // ── Delete all input lines for a matrix ───────────────────────────────

        public const string DELETE_MATRIX_INPUTS = @"
DELETE FROM TMATRIXINPUT
WHERE MATRIXID = @MatrixId
AND   TENANTID = @TenantId;";


        // ── Insert a single TMATRIXINPUT row (used by BulkInsertAsync) ────────
        // Columns match live TMATRIXINPUT column list exactly (13 columns).
        // CreatedOn / ModifiedOn are NOT in the live table — deliberately excluded.

        public const string SAVE_MATRIX_INPUT = @"
INSERT INTO TMATRIXINPUT (
    MATRIXINPUTID, MATRIXID,     SLNO,
    ITEMID,        SKUID,        ITEMCATEGORY,
    LOTID,         PLANNEDQTY,   ALLOCATEDQTY,
    CONSUMEDQTY,   MIXPROPORTION,LINESTATUS,
    TENANTID
) VALUES (
    @MatrixInputId, @MatrixId,    @SlNo,
    @ItemId,        @SkuId,       @ItemCategory,
    @LotId,         @PlannedQty,  @AllocatedQty,
    @ConsumedQty,   @MixProportion,@LineStatus,
    @TenantId
);";


        // ── Soft-delete TMATRIX (Draft only) ──────────────────────────────────

        public const string SOFT_DELETE_MATRIX = @"
UPDATE TMATRIX SET
    STATUS       = 2,
    MATRIXSTATUS = 3,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE MATRIXID     = @MatrixId
AND   MATRIXSTATUS = 0
AND   TENANTID     = @TenantId;";


        // ── Supersede: mark old matrix as Superseded ──────────────────────────
        // Sets MATRIXSTATUS = 2, SUPERSEDEDBYID = newMatrixId.
        // Guard: MATRIXSTATUS = 1 ensures only Active rows are superseded.

        public const string SUPERSEDE_MATRIX = @"
UPDATE TMATRIX SET
    MATRIXSTATUS   = 2,
    SUPERSEDEDBYID = @NewMatrixId,
    MODIFIEDBYID   = @ModifiedById,
    MODIFIEDON     = @ModifiedOn
WHERE MATRIXID     = @OldMatrixId
AND   MATRIXSTATUS = 1
AND   TENANTID     = @TenantId;";


        // ── Activate: set MatrixStatus = 1 and stamp ActivatedOn ─────────────
        // Guard: MATRIXSTATUS = 0 prevents re-activation.

        public const string ACTIVATE_MATRIX = @"
UPDATE TMATRIX SET
    MATRIXSTATUS = 1,
    ACTIVATEDON  = @ActivatedOn,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE MATRIXID     = @MatrixId
AND   MATRIXSTATUS = 0
AND   TENANTID     = @TenantId;";


        // ── Update OpenMix input line statuses on activation ──────────────────

        public const string ACTIVATE_OPENMIX_INPUTS = @"
UPDATE TMATRIXINPUT SET
    LINESTATUS = CASE
        WHEN ALLOCATEDQTY > 0 THEN 1   -- Partial (open for issue)
        ELSE 0                          -- Open
    END
WHERE MATRIXID = @MatrixId
AND   TENANTID = @TenantId;";


        // ── Get all output rows for a matrix ──────────────────────────────────
        // Used by GetMatrixAsync to populate MatrixDTO.Outputs for Mode 2 display/edit.

        public const string GET_MATRIX_OUTPUTS = @"
SELECT
    MO.MATRIXOUTPUTID   AS MatrixOutputId,
    MO.MATRIXID         AS MatrixId,
    MO.SLNO             AS SlNo,

    MO.OUTPUTLEVEL      AS OutputLevel,
    CASE MO.OUTPUTLEVEL
        WHEN 0 THEN 'Set'
        WHEN 1 THEN 'FGSerial'
        WHEN 2 THEN 'MixOutputLot'
        ELSE        'Unknown'
    END                 AS OutputLevelName,

    MO.ITEMID           AS ItemId,
    I.ITEMCODE          AS ItemCode,
    I.ITEMNAME          AS ItemName,

    MO.SKUID            AS SkuId,
    S.SKUCODE           AS SkuCode,
    S.SKUNAME           AS SkuName,

    MO.LOTID            AS LotId,
    LT.LOTNUMBER        AS LotNumber,

    MO.LOTQUANTITY              AS LotQuantity,
    MO.LOTQUANTITY              AS RequiredQty,
    CAST(0 AS NUMERIC(18,4))    AS QuantityPer,
    U.UOMCODE                   AS UomCode,
    MO.TENANTID                 AS TenantId

FROM  TMATRIXOUTPUT MO
LEFT JOIN MITEM I  ON I.ITEMID  = MO.ITEMID AND MO.ITEMID <> -1
LEFT JOIN MSKU  S  ON S.SKUID   = MO.SKUID  AND MO.SKUID  <> -1
LEFT JOIN TLOT  LT ON LT.LOTID  = MO.LOTID  AND MO.LOTID  <> -1
LEFT JOIN MUOM  U  ON U.UOMID   = I.STOCKUOMID AND MO.ITEMID <> -1

WHERE MO.MATRIXID = @MatrixId
AND   MO.TENANTID = @TenantId
ORDER BY MO.SLNO;";


        // ── Delete all output rows for a matrix (Mode 2 / Draft only) ─────────
        // Only called by SaveMatrixAsync on the update path for Mode 2.
        // Mode 0/1 outputs are owned by ActivateMatrixAsync and must never be deleted here.

        public const string DELETE_MATRIX_OUTPUTS = @"
DELETE FROM TMATRIXOUTPUT
WHERE MATRIXID = @MatrixId
AND   TENANTID = @TenantId;";


        // ── Insert TLOT row during activation ─────────────────────────────────
        // FIX-15: TENANTID added to INSERT — was missing.
        //         TlotInsertDTO carries TenantId; without this the column
        //         defaults to whatever the DB default is (likely -1 sentinel).

        public const string SAVE_LOT_FOR_ACTIVATION = @"
INSERT INTO TLOT (
    LOTID,         LOTNUMBER,
    ITEMID,        SKUID,
    LOTTYPEID,     PROCESSID,
    LOTDATE,       LOTEXPIRYDATE,
    STATUS,        VERSION,
    CREATEDBYID,   CREATEDON,
    MODIFIEDBYID,  MODIFIEDON,
    ALLOCATIONID
)
VALUES (
    @LotId,        @LotNumber,
    @ItemId,       @SkuId,
    @LotTypeId,    @ProcessId,
    @LotDate,      @LotExpiryDate,
    @Status,       @Version,
    @CreatedById,  @CreatedOn,
    @ModifiedById, @ModifiedOn,
    @AllocationId
);";

        


        // ── Insert TMATRIXOUTPUT row during activation ────────────────────────
        // Columns match live TMATRIXOUTPUT column list exactly (9 columns).
        // CreatedOn / ModifiedOn are NOT in the live table — deliberately excluded.

        public const string SAVE_MATRIX_OUTPUT = @"
INSERT INTO TMATRIXOUTPUT (
    MATRIXOUTPUTID, MATRIXID,    SLNO,
    OUTPUTLEVEL,    ITEMID,      SKUID,
    LOTID,          LOTQUANTITY, TENANTID
) VALUES (
    @MatrixOutputId, @MatrixId,  @SlNo,
    @OutputLevel,    @ItemId,    @SkuId,
    @LotId,          @LotQuantity,@TenantId
);";


        // ── Update consumed qty on a single input line ────────────────────────
        // Increments CONSUMEDQTY by @AdditionalQty.
        // Derives LINESTATUS from resulting totals:
        //   >= ALLOCATEDQTY → 2 (Consumed)
        //   > 0             → 1 (Partial)
        //   else            → 0 (Open)

        public const string UPDATE_CONSUMED_QTY = @"
UPDATE TMATRIXINPUT SET
    CONSUMEDQTY  = CONSUMEDQTY + @AdditionalQty,
    LINESTATUS   = CASE
                       WHEN (CONSUMEDQTY + @AdditionalQty) >= ALLOCATEDQTY THEN 2
                       WHEN (CONSUMEDQTY + @AdditionalQty) >  0            THEN 1
                       ELSE 0
                   END,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE MATRIXINPUTID = @MatrixInputId
AND   TENANTID      = @TenantId;";


        // ─────────────────────────────────────────────────────────────────────
        //  BuildMatrixList — paged list with dynamic WHERE + ORDER BY
        // ─────────────────────────────────────────────────────────────────────
        // FIX-16: AllocationId validation changed from == 0 to <= 0
        //         to also reject negative values arriving from a buggy caller.
        //         Sentinel -1 must never be accepted as a valid AllocationId here.

        public static (string Sql, DynamicParameters Params) BuildMatrixList(
            MatrixListCriteria c,
            LoginDTO login,
            ISqlDialect d,
            string orderBy)
        {
            // ─────────────────────────────────────────────
            // VALIDATION
            // ─────────────────────────────────────────────
            ArgumentNullException.ThrowIfNull(c);
            ArgumentNullException.ThrowIfNull(login);
            ArgumentNullException.ThrowIfNull(d);

            var tenantId = login.ClientId;

            if (tenantId == 0)
                throw new InvalidOperationException(
                    "Valid TenantId is required from LoginDTO.");

            if (c.AllocationId <= 0)
                throw new InvalidOperationException(
                    "Valid AllocationId is required.");

            // ─────────────────────────────────────────────
            // QUERY CONTEXT
            // ─────────────────────────────────────────────
            var ctx = new QueryContext(d)
                .Register("matrix", "MX")
                .Register("allocation", "AL");

            // ─────────────────────────────────────────────
            // BASE CLAUSES
            // ─────────────────────────────────────────────
            var clauses = new List<SqlClause>
            {
                new("MX.TENANTID = @TenantId",
                    new DynamicParameters(new { TenantId = tenantId })),

                new("MX.STATUS <> 2",
                    new DynamicParameters()),

                new("MX.MATRIXSTATUS <> 3",
                    new DynamicParameters()),

                new("MX.ALLOCATIONID = @AllocationId",
                    new DynamicParameters(new { AllocationId = c.AllocationId }))
            };

            // ─────────────────────────────────────────────
            // OPTIONAL FILTERS
            // ─────────────────────────────────────────────
            if (c.StatusFilter.HasValue)
                clauses.Add(new SqlClause(
                    "MX.MATRIXSTATUS = @StatusFilter",
                    new DynamicParameters(new { StatusFilter = c.StatusFilter.Value })));

            if (c.ModeFilter.HasValue)
                clauses.Add(new SqlClause(
                    "MX.MATRIXMODE = @ModeFilter",
                    new DynamicParameters(new { ModeFilter = c.ModeFilter.Value })));

            if (!string.IsNullOrWhiteSpace(c.SearchText))
            {
                var search = $"%{c.SearchText.Trim()}%";
                clauses.Add(new SqlClause(
                    $"MX.MATRIXCODE {d.LikeCaseOp} {d.Param("SearchText")}",
                    new DynamicParameters(new { SearchText = search })));
            }

            var (whereSql, whereParams) = SqlClause.AsWhere(clauses.ToArray());

            // ─────────────────────────────────────────────
            // SAFE ORDER BY
            // ─────────────────────────────────────────────
            var allowedOrderBy = new HashSet<string>(StringComparer.OrdinalIgnoreCase)
            {
                "MX.MATRIXID",
                "MX.MATRIXCODE",
                "MX.MATRIXMODE",
                "MX.MATRIXSTATUS",
                "MX.ACTIVATEDON"
            };

            var cleanOrderBy = orderBy?
                .Split(' ', StringSplitOptions.RemoveEmptyEntries)
                .FirstOrDefault();

            if (string.IsNullOrWhiteSpace(cleanOrderBy) ||
                !allowedOrderBy.Contains(cleanOrderBy))
                orderBy = "MX.MATRIXID DESC";

            var (pagingSql, pagingParams) = SqlClauses.Paging(ctx, c, orderBy);

            // ─────────────────────────────────────────────
            // FINAL SQL
            // FIX-17: Subquery for SerialsCount filtered to OUTPUTLEVEL = 1 only
            //         (was correct already). InputLineCount subquery kept as-is.
            //         MatrixMode / MatrixStatus CASE names added so list DTO
            //         display-name fields are populated without a second query.
            // ─────────────────────────────────────────────
            var sql = $@"
SELECT
    {d.TotalCountExpr()},

    MX.MATRIXID         AS MatrixId,
    MX.MATRIXCODE       AS MatrixCode,
    MX.MATRIXMODE       AS MatrixMode,
    CASE MX.MATRIXMODE
        WHEN 0 THEN 'Product'
        WHEN 1 THEN 'FGItem'
        WHEN 2 THEN 'OpenMix'
        ELSE        'Unknown'
    END                 AS MatrixModeName,

    MX.MATRIXSTATUS     AS MatrixStatus,
    CASE MX.MATRIXSTATUS
        WHEN 0 THEN 'Draft'
        WHEN 1 THEN 'Active'
        WHEN 2 THEN 'Superseded'
        WHEN 3 THEN 'Deleted'
        ELSE        'Unknown'
    END                 AS MatrixStatusName,

    MX.ALLOCATIONID     AS AllocationId,
    AL.ALLOCATIONNAME   AS AllocationName,

    MX.SETFROM          AS SetFrom,
    MX.SETTO            AS SetTo,
    MX.ACTIVATEDON      AS ActivatedOn,

    (SELECT COUNT(*)
     FROM   TMATRIXOUTPUT MO
     WHERE  MO.MATRIXID   = MX.MATRIXID
     AND    MO.OUTPUTLEVEL = 1
     AND    MO.TENANTID   = MX.TENANTID)   AS SerialsCount,

    (SELECT COUNT(*)
     FROM   TMATRIXINPUT MI
     WHERE  MI.MATRIXID = MX.MATRIXID
     AND    MI.TENANTID = MX.TENANTID)     AS InputLineCount,

    FGI.ITEMNAME                           AS FgItemName,
    PG.PROCESSGROUPNAME                    AS ProcessGroupName

FROM TMATRIX MX
INNER JOIN MALLOCATION AL
    ON  AL.ALLOCATIONID = MX.ALLOCATIONID
    AND AL.TENANTID     = MX.TENANTID

LEFT JOIN MITEM FGI
    ON  FGI.ITEMID  = MX.FGITEMID
    AND MX.FGITEMID <> -1

LEFT JOIN MPROCESSGROUP PG
    ON  PG.PROCESSGROUPID  = MX.PROCESSGROUPID
    AND MX.PROCESSGROUPID <> -1

{whereSql}

{pagingSql}";

            return (sql, SqlClause.Merge(whereParams, pagingParams));
        }


        // ── Select-list / picklist ────────────────────────────────────────────
        // FIX-18: STATUS <> 2 and MATRIXSTATUS <> 3 filters added so soft-deleted
        //         rows are excluded from dropdowns.
        //         ORDER BY MATRIXCODE added for consistent presentation.

        public const string GET_SELECTLIST_MATRIX = @"
SELECT
    MX.MATRIXID         AS MatrixId,
    MX.MATRIXCODE       AS MatrixCode,
    MX.MATRIXMODE       AS MatrixMode,
    MX.BATCHASSIGNMODE  AS BatchAssignMode,
    MX.MATRIXSTATUS     AS MatrixStatus,
    MX.REMARKS          AS Remarks
FROM  TMATRIX MX
WHERE MX.TENANTID      = @TenantId
AND   MX.STATUS       <> 2
AND   MX.MATRIXSTATUS <> 3
ORDER BY MX.MATRIXCODE;";

        // ── Select-list / picklist — Allocation + Item (Matrix-independent) ────
        // Deliberately does NOT join TMATRIX. Sourced from TPENDINGALLOCATION,
        // TALLOCATION, MITEM, MBIZTRANSACTIONTYPE, MALLOCATION and MPARTY only.
        // TotalQuantity  = TALLOCATION.QUANTITY of the alloted allocation line.
        // AvailableQuantity = TPENDINGALLOCATION.PENDINGQUANTITY.
        // NOTE: CriteriaBuilder.ExtractAliasFromSql prefixes EVERY criteria field
        // with the FIRST table alias it finds in the SQL text — it has no concept
        // of which joined table a field actually belongs to, or that some SELECT
        // columns (ItemName, PartyName, AvailableQuantity) are aliases for a
        // DIFFERENT underlying column/table than the driving one. Sending a
        // criteria field like "PartyName" against the un-wrapped query below would
        // generate "PA.PARTYNAME" — a column that doesn't exist on TPENDINGALLOCATION
        // at all (it's really PT.PARTYNAME from MPARTY) — causing "Invalid column
        // name" errors.
        //
        // Fix: wrap the real query in an outer SELECT, and write the inner
        // "FROM TPENDINGALLOCATION AS PA" using the optional AS keyword. The alias
        // regex's FIRST match then captures "AS" itself (not "PA") as the alias —
        // and "AS" is in ExtractAliasFromSql's reserved-word list, so it returns NO
        // prefix at all. Criteria fields become unprefixed (e.g. "PARTYNAME = @X"),
        // which resolves unambiguously against the outer subquery's own SELECT-list
        // aliases (PartyName, ItemName, AvailableQuantity, ...) — exactly the
        // column names the criteria payload already expects.
        public const string GET_SELECTLIST_ALLOCATION_WITH_ITEM = @"
SELECT * FROM
(
    SELECT DISTINCT
        PA.ALLOTEDALLOCATIONID                 AS AllotedAllocationId,
        PA.DOCUMENTDATE                        AS DocumentDate,
        PA.DOCUMENTNUMBER                      AS DocumentNumber,
        PA.REFERENCENUMBER                     AS RefNumber,
        PA.REFERENCEDATE                       AS RefDate,
        PA.PENDINGDATE                         AS PendingDate,

        PA.BIZTRANSACTIONTYPEID                AS BizTransactionTypeId,
        ISNULL(MB.BIZTRANSACTIONTYPECODE, '')  AS BizTransactionTypeCode,
        ISNULL(MB.BIZTRANSACTIONTYPENAME, '')  AS BizTransactionTypeName,

        PA.ITEMID                              AS ItemId,
        ISNULL(MI.ITEMCODE, '')                AS ItemCode,
        ISNULL(MI.ITEMNAME, '')                AS ItemName,

        MA.ALLOCATIONID                        AS AllocationId,
        MA.ALLOCATIONNAME                      AS AllocationName,

        ISNULL(PT.PARTYID, 0)                  AS PartyId,
        ISNULL(PT.PARTYCODE, '')               AS PartyCode,
        ISNULL(PT.PARTYNAME, '')               AS PartyName,

        ISNULL(TA.QUANTITY, 0)                 AS TotalQuantity,
        ISNULL(PA.PENDINGQUANTITY, 0)          AS AvailableQuantity

    FROM TPENDINGALLOCATION AS PA

    LEFT JOIN TALLOCATION TA
           ON TA.ALLOCATIONID = PA.ALLOTEDALLOCATIONID

    LEFT JOIN MBIZTRANSACTIONTYPE MB
           ON MB.BIZTRANSACTIONTYPEID = PA.BIZTRANSACTIONTYPEID

    LEFT JOIN MITEM MI
           ON MI.ITEMID = PA.ITEMID
          AND MI.TENANTID = @TenantId

    INNER JOIN MALLOCATION MA
           ON MA.ALLOCATIONID  = PA.HEADERALLOCATIONID
          AND MA.ALLOCATIONID  < -1
          AND MA.TENANTID      = @TenantId

    LEFT JOIN MPARTY PT
           ON PT.PARTYID = MA.PARTYID

    -- OUTPUTTYPE = 0 is the actual ordered/parent line; 1+ are derived/exploded
    -- output lines generated for the same SO (e.g. sub-parts under a nacelle-cover
    -- kit) — confirmed by comparing real TPENDINGALLOCATION rows for a parent vs
    -- its exploded children: every other column matched (including
    -- PREALLOTEDALLOCATIONID, both -1), only OUTPUTTYPE differed (0 vs 1).
    -- Without this filter, every exploded child line up too, cluttering the
    -- Sales Order Line picklist with items the customer never directly ordered.
    WHERE PA.PENDINGQUANTITY > 0
    AND   PA.OUTPUTTYPE     = 0

    -- Dynamic BOM-existence check — no hardcoded item codes/names. Excludes
    -- any allocation whose item has no BOM output (FG component) configured
    -- at all (BOMLEVEL = 0), e.g. an item that was created but never had its
    -- recipe set up. Without this, selecting such an allocation always leads
    -- to an empty Composition table with nothing to generate — a dead end
    -- for the user. Re-checked live via EXISTS every call, so an item that
    -- gets its BOM configured later automatically starts appearing.
    AND EXISTS (
        SELECT 1
        FROM MITEM A
        INNER JOIN MBOM B        ON B.BOMID = A.DEFAULTBOMID
        INNER JOIN MBOMDETAIL BD ON BD.BOMID = B.BOMID
        WHERE A.ITEMID    = PA.ITEMID
        AND   A.STATUS    IN (0, 1)
        AND   BD.BOMLEVEL = 0
    )
) AllocationItemPicklist
WHERE 1 = 1
{DYNAMIC_WHERE}
ORDER BY DocumentDate DESC";

        // ── Single allocation-line summary (Matrix-independent) ────────────────
        // Same table set as GET_SELECTLIST_ALLOCATION_WITH_ITEM, keyed to one
        // AllotedAllocationId. Deliberately does NOT touch TMATRIX/TMATRIXOUTPUT.
        public const string GET_ALLOCATION_ITEM_SUMMARY = @"
SELECT
    PA.ALLOTEDALLOCATIONID                 AS AllotedAllocationId,
    PA.HEADERALLOCATIONID                  AS HeaderAllocationId,
    PA.BIZTRANSACTIONTYPEID                AS BizTransactionTypeId,

    PA.ITEMID                              AS ItemId,
    ISNULL(MI.ITEMCODE, '')                AS ItemCode,
    ISNULL(MI.ITEMNAME, '')                AS ItemName,

    MA.ALLOCATIONID                        AS AllocationId,
    MA.ALLOCATIONNAME                      AS AllocationName,
    ISNULL(MA.ALLOCATIONSHORTNAME, '')     AS AllocationShortName,

    ISNULL(PT.PARTYID, 0)                  AS PartyId,
    ISNULL(PT.PARTYCODE, '')               AS PartyCode,
    ISNULL(PT.PARTYNAME, '')               AS PartyName,

    ISNULL(TA.QUANTITY, 0)                 AS SetsOrdered

FROM TPENDINGALLOCATION PA

LEFT JOIN TALLOCATION TA
       ON TA.ALLOCATIONID = PA.ALLOTEDALLOCATIONID

LEFT JOIN MITEM MI
       ON MI.ITEMID = PA.ITEMID
      AND MI.TENANTID = @TenantId

INNER JOIN MALLOCATION MA
       ON MA.ALLOCATIONID  = PA.HEADERALLOCATIONID
      AND MA.ALLOCATIONID  < -1
      AND MA.TENANTID      = @TenantId

LEFT JOIN MPARTY PT
       ON PT.PARTYID = MA.PARTYID

WHERE PA.ALLOTEDALLOCATIONID = @AllotedAllocationId";

        // ── Sets generated — TLOT only (no TMATRIXOUTPUT) ──────────────────────
        // Generic per-item, per-allocation TLOT row count. Callers pass whichever
        // ItemId they need counted — for the matrix-independent flow this is one
        // BOM component's ItemId (the FG item itself never gets a TLOT row there),
        // and the caller divides the result by that component's qty-per-set to
        // get "sets generated" (see MatrixBLL.GetAllocationItemSummaryAsync).
        public const string GET_SETS_GENERATED_COUNT = @"
SELECT COUNT(*)
FROM TLOT
WHERE ITEMID       = @ItemId
AND   ALLOCATIONID = @HeaderAllocationId";

        // ── Sets generated — scoped to ONE alloted-allocation line ─────────────
        // TLOT.ALLOCATIONID is the SO-level HEADERALLOCATIONID, shared by every
        // FG item line under that SO — a BOM component common to multiple FG
        // items (e.g. a shared resin/gelcoat) would be massively over-counted if
        // scoped only by that. TLOTDETAIL.OBJECTHEADERID carries the SPECIFIC
        // AllotedAllocationId (see SaveSetSerialGenerationAsync's TlotDetailInsertDTO),
        // so joining through it isolates units generated for this exact FG item
        // line only.
        // NOTE: neither TLOT nor TLOTDETAIL carries a TENANTID column (confirmed —
        // no other query against these two tables in this file filters by it, and
        // TlotInsertDTO.TenantId is explicitly "keep for BLL use, not inserted").
        public const string GET_SETS_GENERATED_COUNT_FOR_ALLOTED_ALLOCATION = @"
SELECT COUNT(*)
FROM TLOT LT
INNER JOIN TLOTDETAIL LD
        ON LD.LOTID              = LT.LOTID
       AND LD.OBJECTHEADERTYPEID = @ObjectHeaderTypeId
       AND LD.OBJECTHEADERID     = @AllotedAllocationId
WHERE LT.ITEMID = @ItemId";

        // ── Generated SET-level lots for one alloted-allocation line ────────────
        // Returns the actual SET-level TLOT rows SaveSetSerialGenerationAsync
        // wrote for this allocation (LotNumber = "{ItemCode}-{AllocationShortName}
        // -SET-{NNNN}", ItemId = the PARENT product itself, one row per set) — the
        // real, dynamic list of which set numbers physically exist for this SO
        // line, so the old Matrix Planner's SetFrom/SetTo picker can be restricted
        // to only sets that were actually generated instead of accepting an
        // arbitrary typed range. No set count or number is ever hardcoded — this
        // reads back exactly what was written, whatever that turns out to be.
        // Filtered on the literal "-SET-" marker in LOTNUMBER (the exact substring
        // SaveSetSerialGenerationAsync writes: "{ItemCode}-{AllocationShortName}
        // -SET-{NNNN}") rather than LOTTYPEID or ITEMID: LOTTYPEID isn't reliably
        // populated/distinct per tenant, and ITEMID alone would require resolving
        // the parent product's ItemId via TPENDINGALLOCATION first — which fails
        // for allocations that are already fully generated (no pending row left).
        public const string GET_GENERATED_SET_LOTS_FOR_ALLOTED_ALLOCATION = @"
SELECT
    LT.LOTID     AS LotId,
    LT.LOTNUMBER AS LotNumber,
    LT.LOTDATE   AS LotDate,
    LT.ITEMID    AS ItemId
FROM TLOT LT
INNER JOIN TLOTDETAIL LD
        ON LD.LOTID              = LT.LOTID
       AND LD.OBJECTHEADERTYPEID = @ObjectHeaderTypeId
       AND LD.OBJECTHEADERID     = @AllotedAllocationId
WHERE LT.LOTNUMBER LIKE '%-SET-%'
ORDER BY LT.LOTID";

        public const string GET_MATRIX_SUMMARY = @"
SELECT
    TM.MATRIXID                     AS MatrixId,
    TM.MATRIXCODE                   AS MatrixCode,
    TM.MATRIXSTATUS                 AS MatrixStatus,
    TM.MATRIXMODE                   AS MatrixMode,
    TM.SETFROM                      AS SetFrom,
    TM.SETTO                        AS SetTo,
    TM.FGITEMID                     AS FgItemId,
    FG.ITEMCODE                     AS FgItemCode,
    FG.ITEMNAME                     AS FgItemName,
    MA.ALLOCATIONNAME               AS SONumber,
    ISNULL(PT.PARTYNAME, '')        AS CustomerName,
    ISNULL(PT.PARTYCODE, '')        AS CustomerCode,
    ISNULL(TM.SETTO - TM.SETFROM + 1, 0) AS SetsOrdered,
    ISNULL((
        SELECT COUNT(*)
        FROM   TMATRIXOUTPUT MO
        WHERE  MO.MATRIXID    = TM.MATRIXID
        AND    MO.OUTPUTLEVEL = 0
        AND    MO.TENANTID    = TM.TENANTID
    ), 0)                           AS SetsGenerated
FROM  TPENDINGALLOCATION PA
LEFT JOIN TMATRIX TM
       ON TM.ALLOCATIONID  = PA.HEADERALLOCATIONID
       AND TM.MATRIXSTATUS IN (0, 1)
       AND TM.MATRIXID     < -1
       AND TM.TENANTID      = @TenantId
LEFT JOIN MITEM FG
       ON FG.ITEMID = TM.FGITEMID
INNER JOIN MALLOCATION MA
       ON MA.ALLOCATIONID  = PA.HEADERALLOCATIONID
       AND MA.ALLOCATIONID < -1
LEFT JOIN MPARTY PT
       ON PT.PARTYID       = MA.PARTYID
       AND MA.PARTYID      < -1
WHERE PA.ALLOTEDALLOCATIONID = @AllotedAllocationId
ORDER BY TM.MATRIXID DESC";

        // ── Insert TRESERVATION row for a matrix input line ───────────────────
        // Called once per input line where IsReservation == false.
        // PACKID, STOREID, and FROM* columns are omitted — all nullable in the table.
        // RESERVEDFROMTYPE: CK allows 0 or 1 only.

        // Columns match the real TRESERVATION schema (StockReservationQB.INSERT_RESERVATION).
        // STOCKRESERVATIONID is IDENTITY — SQL Server generates it via SCOPE_IDENTITY().
        // Old-schema columns also populated: REFERENCENUMBER, REFERENCEDATE, RESERVEDFROMTYPE,
        //   USEDQUANTITY, BALANCEQUANTITY — avoids NULLs in legacy reporting queries.
        // Verified live against TRESERVATION on 217.216.78.142/VFGPLTEST — this
        // table has NO TenantId, RESERVATIONMODE, RESERVATIONSOURCE, ASSIGNMENTLEVEL
        // or ISDELETED columns (the previous version of this INSERT referenced all
        // five and would have thrown "Invalid column name" the first time it ran).
        // RESERVATIONSTATUS also doesn't exist — the real column is STATUS.
        public const string SAVE_RESERVATION = @"
INSERT INTO TRESERVATION (
    OUID,                   PERIODID,
    BIZTRANSACTIONTYPEID,
    RESERVATIONNUMBER,      RESERVATIONDATE,
    REFERENCENUMBER,        REFERENCEDATE,
    FOROBJECTHEADERTYPEID,  FOROBJECTHEADERID,       FOROBJECTTYPEID,     FOROBJECTID,
    FORALLOTEDALLOCATIONID, FORBIZTRANSACTIONTYPEID, FORSLNO,             FORDATE,
    ITEMID,                 SKUID,                   LOTID,
    RESERVEDFROMTYPE,
    RESERVEDQUANTITY,       USEDQUANTITY,            BALANCEQUANTITY,
    STATUS,                 VERSION,
    CREATEDBYID,            CREATEDON,
    MODIFIEDBYID,           MODIFIEDON
) VALUES (
    @OuId,                  @PeriodId,
    @ForBizTransactionTypeId,
    @ReservationNumber,     @ReservationDate,
    @ReferenceNumber,       @ReferenceDate,
    @ForObjectHeaderTypeId, @ForObjectHeaderId,      @ForObjectTypeId,    @ForObjectId,
    @ForAllotedAllocationId,@ForBizTransactionTypeId,@ForSlNo,            @ForDate,
    @ItemId,                @SkuId,                  @LotId,
    @ReservedFromType,
    @ReservedQuantity,      @UsedQuantity,           @BalanceQuantity,
    1,                      1,
    @CreatedById,           @CreatedOn,
    @ModifiedById,          @ModifiedOn
);";


        public const string SAVE_LOT_DETAIL_FOR_ACTIVATION = @"
INSERT INTO TLOTDETAIL (
    LOTDETAILID,          BIZTRANSACTIONTYPEID,  OBJECTHEADERTYPEID,
    OBJECTHEADERID,       OBJECTTYPEID,          OBJECTID,
    SLNO,                 LOTID,
    QUANTITY,             GOODQUANTITY,          REJECTEDQUANTITY,
    REWORKQUANTITY,       OTHERQUANTITY,
    PACKID,               PACKQUANTITY,
    OUID,                 STOREID,
    STOCKLEDGERNUMBER,    STOCKLEDGERDATE,
    REFERENCENUMBER,      REFERENCEDATE,
    LOCATIONTYPE,         PARTYBRANCHID,
    STOCKPOSTTYPE,        MATERIALOWNERSHIPTYPE,
    USEDINLOTID,          REASONID,
    MARKEDGOODQUANTITY,   PLANNEDQUANTITY,
    BINID,                ITEMPOSTEDCOST,
    D1, D2, D3, D4, D5,
    ALLOCATIONID
)
VALUES (
    @LotDetailId,         @BizTransactionTypeId, @ObjectHeaderTypeId,
    @ObjectHeaderId,      @ObjectTypeId,         @ObjectId,
    @SlNo,                @LotId,
    @Quantity,            @GoodQuantity,         @RejectedQuantity,
    @ReworkQuantity,      @OtherQuantity,
    @PackId,              @PackQuantity,
    @OuId,                @StoreId,
    @StockLedgerNumber,   @StockLedgerDate,
    @ReferenceNumber,     @ReferenceDate,
    @LocationType,        @PartyBranchId,
    @StockPostType,       @MaterialOwnershipType,
    @UsedInLotId,         @ReasonId,
    @MarkedGoodQuantity,  @PlannedQuantity,
    @BinId,               @ItemPostedCost,
    @D1, @D2, @D3, @D4, @D5,
    @AllocationId
);";

        // ── RM stock-on-hand for a set of items — TSTOCKPOSITION only. Summed
        //    across every store for the tenant's current OU (a raw material can
        //    sit in more than one store), net of RESERVEDQUANTITY, so it reflects
        //    truly free stock the same way GetAvailableBatches/AvailableQuantity
        //    do elsewhere. Purely dynamic — no item is ever named/excluded in
        //    code; @ItemIds is whatever the BOM explosion resolves at call time.
        public const string GET_STOCK_ON_HAND_FOR_ITEMS = @"
SELECT
    SP.ITEMID                                          AS ItemId,
    ISNULL(SUM(SP.QUANTITY - SP.RESERVEDQUANTITY), 0)  AS FreeQuantity
FROM TSTOCKPOSITION SP
WHERE SP.ITEMID IN @ItemIds
GROUP BY SP.ITEMID";
    }
}