namespace MMDAL.Query.InventoryReports
{
    /// <summary>
    /// SQL for the Inventory Overview Dashboard's report tiles.
    ///
    /// GET_REORDER_BREACH_REPORT is a GB5-native reparameterization of legacy GB4's
    /// RegisterQueryBuilder.GET_BELOW_ROL_STORE_SKU_REPORT (Type 2, Store+SKU level) --
    /// /Users/venkatv/Downloads/VVMMBLLDAL/MMDAL/Query/MM/RegisterQueryBuilder.cs:8116. The legacy
    /// query spliced filter values into SQL text via CommonFunctionFrameDAL.ChangedByNamedParam and
    /// collapsed multi-bin/multi-pending rows with an outer GROUP BY + MAX/SUM; this version uses
    /// bound Dapper parameters throughout and pre-aggregates each OUTER APPLY so no outer GROUP BY
    /// is needed (also avoids a leading-CTE, which QueryPagedAsync's COUNT(*) wrap can't nest).
    ///
    /// Required indexes:
    ///   TSTOCKPOSITION: (OUID, ITEMID, SKUID, STOREID) -- already the table's natural key
    ///   MITEMSTOCK: (ITEMID, SKUID, STOREID)
    ///   TPENDINGALLOCATION: (ITEMID, OUID, SKUID), (BIZTRANSACTIONTYPEID)
    /// </summary>
    public static class InventoryReportsQB
    {
        // BizTransactionClassId sentinel for "Purchase Order" -- confirmed still live on GB5's
        // schema (reused verbatim by GB5Solution/Compliance/ComplianceDAL/Query/GSTEWayBill/
        // EWayBillPayloadQB.cs:124, commented "PurchaseOrder" there too). Passed as a bound
        // parameter (not hardcoded into the SQL text) so a caller can override it if a client's
        // data uses a different class id for this concept -- default supplied by the BLL.
        public const string GET_REORDER_BREACH_REPORT = @"
SELECT
    c.ITEMCATEGORYID                                       AS ItemCategoryId,
    c.ITEMCATEGORYCODE                                      AS ItemCategoryCode,
    c.ITEMCATEGORYNAME                                      AS ItemCategoryName,
    s.STOREID                                               AS StoreId,
    s.STORECODE                                             AS StoreCode,
    s.STORENAME                                             AS StoreName,
    i.ITEMID                                                AS ItemId,
    i.ITEMCODE                                               AS ItemCode,
    i.ITEMNAME                                               AS ItemName,
    t.SKUID                                                 AS SKUId,
    sku.SKUCODE                                              AS SKUCode,
    sku.SKUNAME                                              AS SKUName,
    ISNULL(ist.ROL, 0)                                       AS ROL,
    ISNULL(iou.LEADTIME, i.LEADTIME)                         AS LeadTime,
    ISNULL(iou.LOTSIZE, 0)                                   AS LotSize,
    ISNULL(iou.MAXLEVEL, i.MAXLEVEL)                         AS MaxLevel,
    ISNULL(iou.MINLEVEL, i.MINLEVEL)                         AS MinLevel,
    ISNULL(t.QUANTITY, 0)                                    AS AvailableQuantity,
    ISNULL(t.RESERVEDQUANTITY, 0)                            AS ReservedQuantity,
    ISNULL(t.QUANTITY, 0) - ISNULL(t.RESERVEDQUANTITY, 0)    AS SOH,
    u.UOMCODE                                                AS UOMCode,
    ISNULL(ist.BINNUMBER, '')                                AS BinNumber,
    ISNULL(iou.LASTPURCHASERATE, 0)                          AS LastPurchaseRate,
    ISNULL(vp.SupplyPending, 0)                              AS SupplyPending,
    ISNULL(vp.DemandPending, 0)                              AS DemandPending,
    ISNULL(vp.SupplyOpenQuantity, 0)                         AS SupplyOpenQuantity,
    ISNULL(vp.DemandOpenQuantity, 0)                         AS DemandOpenQuantity,
    ISNULL(vp.NetSupplyDemand, 0)                            AS NetSupplyDemand,
    ISNULL(po.POPendingQuantity, 0)                          AS POPendingQuantity,
    CASE
        WHEN ISNULL(t.QUANTITY, 0) < ISNULL(iou.MINLEVEL, i.MINLEVEL)
            THEN ISNULL(iou.MINLEVEL, i.MINLEVEL) - ISNULL(t.QUANTITY, 0)
        ELSE ISNULL(iou.MAXLEVEL, i.MAXLEVEL) - ISNULL(t.QUANTITY, 0)
    END                                                       AS RequiredQuantity,
    (CASE
        WHEN ISNULL(t.QUANTITY, 0) < ISNULL(iou.MINLEVEL, i.MINLEVEL)
            THEN ISNULL(iou.MINLEVEL, i.MINLEVEL) - ISNULL(t.QUANTITY, 0)
        ELSE ISNULL(iou.MAXLEVEL, i.MAXLEVEL) - ISNULL(t.QUANTITY, 0)
    END) - ISNULL(po.POPendingQuantity, 0)                   AS BalanceQuantity
FROM        TSTOCKPOSITION t
JOIN        MITEM i             ON i.ITEMID = t.ITEMID
JOIN        MSKU sku            ON sku.SKUID = t.SKUID
JOIN        MSTORE s            ON s.STOREID = t.STOREID
JOIN        MITEMCATEGORY c     ON c.ITEMCATEGORYID = i.CATEGORYID
JOIN        MUOM u              ON u.UOMID = i.STOCKUOMID
LEFT JOIN   MITEMOU iou         ON iou.ITEMID = t.ITEMID AND iou.OUID = t.OUID
OUTER APPLY (
    SELECT TOP 1 ist2.ROL, ist2.BINNUMBER
    FROM   MITEMSTOCK ist2
    WHERE  ist2.ITEMID = t.ITEMID AND ist2.SKUID = t.SKUID AND ist2.STOREID = t.STOREID
    ORDER  BY ist2.ROL DESC
) ist
OUTER APPLY (
    SELECT
        SUM(CASE WHEN pa.DEMANDSUPPLYTYPE = 4 THEN pa.PENDINGQUANTITY ELSE 0 END)                              AS SupplyPending,
        SUM(CASE WHEN pa.DEMANDSUPPLYTYPE = 1 THEN pa.QUANTITY ELSE 0 END)                                     AS DemandPending,
        SUM(CASE WHEN pa.DEMANDSUPPLYTYPE = 4 THEN pa.PENDINGQUANTITY - pa.RESERVEDFROMQUANTITY ELSE 0 END)    AS SupplyOpenQuantity,
        SUM(CASE WHEN pa.DEMANDSUPPLYTYPE = 1 THEN pa.PENDINGQUANTITY - pa.RESERVEDFORQUANTITY ELSE 0 END)     AS DemandOpenQuantity,
        SUM(CASE
                WHEN pa.DEMANDSUPPLYTYPE = 4 THEN pa.PENDINGQUANTITY - pa.RESERVEDFROMQUANTITY
                WHEN pa.DEMANDSUPPLYTYPE = 1 THEN (pa.PENDINGQUANTITY - pa.RESERVEDFORQUANTITY) * -1
                ELSE 0
            END)                                                                                                AS NetSupplyDemand
    FROM   TPENDINGALLOCATION pa
    WHERE  pa.ITEMID = t.ITEMID AND pa.OUID = t.OUID AND pa.SKUID = t.SKUID AND pa.PENDINGQUANTITY > 0
) vp
OUTER APPLY (
    SELECT SUM(pa.PENDINGQUANTITY) AS POPendingQuantity
    FROM   TPENDINGALLOCATION pa
    JOIN   MBIZTRANSACTIONTYPE btt ON btt.BIZTRANSACTIONTYPEID = pa.BIZTRANSACTIONTYPEID
    WHERE  btt.BIZTRANSACTIONCLASSID = @SupplyBizTransactionClassId
       AND pa.ITEMID = t.ITEMID AND pa.OUID = t.OUID AND pa.SKUID = t.SKUID
       AND pa.PENDINGQUANTITY > 0
) po
WHERE   t.OUID = @OuId
    AND (@StoreId IS NULL OR t.STOREID = @StoreId)
    AND (@ItemCategoryId IS NULL OR i.CATEGORYID = @ItemCategoryId)
    AND (ISNULL(t.QUANTITY, 0) - ISNULL(t.RESERVEDQUANTITY, 0)) < ISNULL(ist.ROL, 0)
ORDER BY c.ITEMCATEGORYCODE, i.ITEMCODE, sku.SKUCODE";

        // Inventory Ageing tile -- fixed 4-bucket (0-30/31-60/61-90/>90 days), Item+ItemCategory
        // grain, reparameterized from legacy GB4's StockPositionQueryBuilder
        // .STOCK_AGEING_DETAIL_REPORT_ITEMCATEGORY -- see StockAgeingReportDTO.cs for the full
        // provenance note and the deliberate scope trim (fixed buckets/dimension vs. legacy's
        // admin-configurable 1-6 buckets across 9 dimensions).
        //
        // "CurrentStock" is read from TSTOCKPOSITION (GB5's own maintained running balance) rather
        // than re-derived by summing TSTOCKLEDGER the way legacy did -- GB5 already has a correct,
        // live-updated position table; re-summation would be redundant. The per-band INWARD
        // quantities still come from TSTOCKLEDGER since bucket-dating needs transaction history.
        // Allocation is the same FIFO-waterfall technique as legacy: current stock is sliced against
        // each band's inward quantity, most-recent band first, each band absorbing
        // min(remaining stock, that band's inward) before the next band is considered.
        //
        // Required indexes:
        //   TSTOCKPOSITION: (OUID, ITEMID) -- covered by the table's own (OUID,ITEMID,SKUID,STOREID) key
        //   TSTOCKLEDGER: (OUID, ITEMID, STOCKPOSTTYPE, STOCKLEDGERDATE)
        public const string GET_STOCK_AGEING_REPORT = @"
SELECT
    c.ITEMCATEGORYID                                            AS ItemCategoryId,
    c.ITEMCATEGORYCODE                                          AS ItemCategoryCode,
    c.ITEMCATEGORYNAME                                          AS ItemCategoryName,
    i.ITEMID                                                    AS ItemId,
    i.ITEMCODE                                                  AS ItemCode,
    i.ITEMNAME                                                  AS ItemName,
    u.UOMCODE                                                   AS UOMCode,
    ISNULL(stockAgg.CurrentStock, 0)                            AS CurrentStock,
    ISNULL(stockAgg.CurrentStockValue, 0)                       AS CurrentStockValue,
    CASE WHEN ISNULL(stockAgg.CurrentStock,0) >= ISNULL(inw.Band1Qty,0)
        THEN ISNULL(inw.Band1Qty,0) ELSE ISNULL(stockAgg.CurrentStock,0) END                              AS Age1GoodQuantity,
    CASE WHEN ISNULL(stockAgg.CurrentStock,0) >= ISNULL(inw.Band1Qty,0)
        THEN ISNULL(inw.Band1Value,0) ELSE ISNULL(stockAgg.CurrentStockValue,0) END                       AS Age1Value,
    CASE WHEN ISNULL(stockAgg.CurrentStock,0) >= (ISNULL(inw.Band1Qty,0)+ISNULL(inw.Band2Qty,0))
        THEN ISNULL(inw.Band2Qty,0)
        ELSE CASE WHEN ISNULL(stockAgg.CurrentStock,0) > ISNULL(inw.Band1Qty,0)
                  THEN ISNULL(stockAgg.CurrentStock,0) - ISNULL(inw.Band1Qty,0) ELSE 0 END
    END                                                                                                    AS Age2GoodQuantity,
    CASE WHEN ISNULL(stockAgg.CurrentStock,0) >= (ISNULL(inw.Band1Qty,0)+ISNULL(inw.Band2Qty,0))
        THEN ISNULL(inw.Band2Value,0)
        ELSE CASE WHEN ISNULL(stockAgg.CurrentStock,0) > ISNULL(inw.Band1Qty,0)
                  THEN ISNULL(stockAgg.CurrentStockValue,0) - ISNULL(inw.Band1Value,0) ELSE 0 END
    END                                                                                                    AS Age2Value,
    CASE WHEN ISNULL(stockAgg.CurrentStock,0) >= (ISNULL(inw.Band1Qty,0)+ISNULL(inw.Band2Qty,0)+ISNULL(inw.Band3Qty,0))
        THEN ISNULL(inw.Band3Qty,0)
        ELSE CASE WHEN ISNULL(stockAgg.CurrentStock,0) > (ISNULL(inw.Band1Qty,0)+ISNULL(inw.Band2Qty,0))
                  THEN ISNULL(stockAgg.CurrentStock,0) - (ISNULL(inw.Band1Qty,0)+ISNULL(inw.Band2Qty,0)) ELSE 0 END
    END                                                                                                    AS Age3GoodQuantity,
    CASE WHEN ISNULL(stockAgg.CurrentStock,0) >= (ISNULL(inw.Band1Qty,0)+ISNULL(inw.Band2Qty,0)+ISNULL(inw.Band3Qty,0))
        THEN ISNULL(inw.Band3Value,0)
        ELSE CASE WHEN ISNULL(stockAgg.CurrentStock,0) > (ISNULL(inw.Band1Qty,0)+ISNULL(inw.Band2Qty,0))
                  THEN ISNULL(stockAgg.CurrentStockValue,0) - (ISNULL(inw.Band1Value,0)+ISNULL(inw.Band2Value,0)) ELSE 0 END
    END                                                                                                    AS Age3Value,
    CASE WHEN ISNULL(stockAgg.CurrentStock,0) > (ISNULL(inw.Band1Qty,0)+ISNULL(inw.Band2Qty,0)+ISNULL(inw.Band3Qty,0))
        THEN ISNULL(stockAgg.CurrentStock,0) - (ISNULL(inw.Band1Qty,0)+ISNULL(inw.Band2Qty,0)+ISNULL(inw.Band3Qty,0)) ELSE 0 END
                                                                                                             AS Age4GoodQuantity,
    CASE WHEN ISNULL(stockAgg.CurrentStock,0) > (ISNULL(inw.Band1Qty,0)+ISNULL(inw.Band2Qty,0)+ISNULL(inw.Band3Qty,0))
        THEN ISNULL(stockAgg.CurrentStockValue,0) - (ISNULL(inw.Band1Value,0)+ISNULL(inw.Band2Value,0)+ISNULL(inw.Band3Value,0)) ELSE 0 END
                                                                                                             AS Age4Value
FROM        MITEM i
JOIN        MITEMCATEGORY c    ON c.ITEMCATEGORYID = i.CATEGORYID
JOIN        MUOM u             ON u.UOMID = i.STOCKUOMID
OUTER APPLY (
    SELECT SUM(t.QUANTITY) AS CurrentStock, SUM(t.AVERAGEVALUE) AS CurrentStockValue
    FROM   TSTOCKPOSITION t
    WHERE  t.OUID = @OuId AND t.ITEMID = i.ITEMID
) stockAgg
OUTER APPLY (
    SELECT
        SUM(CASE WHEN DATEDIFF(DAY, sl.STOCKLEDGERDATE, @AsOnDate) <= 30 THEN sl.GOODQUANTITY ELSE 0 END)  AS Band1Qty,
        SUM(CASE WHEN DATEDIFF(DAY, sl.STOCKLEDGERDATE, @AsOnDate) <= 30 THEN sl.POSTEDVALUE ELSE 0 END)   AS Band1Value,
        SUM(CASE WHEN DATEDIFF(DAY, sl.STOCKLEDGERDATE, @AsOnDate) > 30
                  AND DATEDIFF(DAY, sl.STOCKLEDGERDATE, @AsOnDate) <= 60 THEN sl.GOODQUANTITY ELSE 0 END)  AS Band2Qty,
        SUM(CASE WHEN DATEDIFF(DAY, sl.STOCKLEDGERDATE, @AsOnDate) > 30
                  AND DATEDIFF(DAY, sl.STOCKLEDGERDATE, @AsOnDate) <= 60 THEN sl.POSTEDVALUE ELSE 0 END)   AS Band2Value,
        SUM(CASE WHEN DATEDIFF(DAY, sl.STOCKLEDGERDATE, @AsOnDate) > 60
                  AND DATEDIFF(DAY, sl.STOCKLEDGERDATE, @AsOnDate) <= 90 THEN sl.GOODQUANTITY ELSE 0 END)  AS Band3Qty,
        SUM(CASE WHEN DATEDIFF(DAY, sl.STOCKLEDGERDATE, @AsOnDate) > 60
                  AND DATEDIFF(DAY, sl.STOCKLEDGERDATE, @AsOnDate) <= 90 THEN sl.POSTEDVALUE ELSE 0 END)   AS Band3Value
    FROM   TSTOCKLEDGER sl
    WHERE  sl.OUID = @OuId AND sl.ITEMID = i.ITEMID
       AND sl.STOCKPOSTTYPE = 0
       AND sl.STOCKLEDGERDATE <= @AsOnDate
) inw
WHERE   (@ItemCategoryId IS NULL OR i.CATEGORYID = @ItemCategoryId)
    AND ISNULL(stockAgg.CurrentStock, 0) > 0
ORDER BY c.ITEMCATEGORYCODE, i.ITEMCODE";

        // Non-Moving / Obsolete Stock tile -- simplified, reparameterized port of legacy GB4's
        // GET_NON_MOVING_REPORT. See NonMovingReportDTO.cs for the full scope-trim rationale
        // (posted-value costing only, LastTransactedDays staleness only). The stock-adjustment
        // exclusion sentinels ARE ported (legacy flags them load-bearing via an inline comment
        // decoding each id) -- confirm these still mean the same thing on GB5's live
        // MBIZTRANSACTIONCLASS data before trusting the filter.
        //
        // Required indexes:
        //   TSTOCKLEDGER: (OUID, ITEMID, SKUID, STOCKPOSTTYPE, STOCKLEDGERDATE)
        public const string GET_NON_MOVING_REPORT = @"
SELECT
    sl.ITEMID                                                   AS ItemId,
    i.ITEMCODE                                                  AS ItemCode,
    i.ITEMNAME                                                  AS ItemName,
    sl.SKUID                                                    AS SKUId,
    sku.SKUCODE                                                 AS SKUCode,
    sku.SKUNAME                                                 AS SKUName,
    i.CATEGORYID                                                AS ItemCategoryId,
    c.ITEMCATEGORYCODE                                          AS ItemCategoryCode,
    c.ITEMCATEGORYNAME                                          AS ItemCategoryName,
    SUM(CASE WHEN sl.STOCKPOSTTYPE = 0 THEN sl.GOODQUANTITY ELSE sl.GOODQUANTITY * -1 END)                 AS Stock,
    SUM(CASE WHEN sl.STOCKPOSTTYPE = 0 THEN sl.POSTEDVALUE ELSE sl.POSTEDVALUE * -1 END)                   AS StockValue,
    ROUND(
        SUM(CASE WHEN sl.STOCKPOSTTYPE = 0 THEN sl.GOODQUANTITY ELSE sl.GOODQUANTITY * -1 END)
        / (CASE WHEN SUM(CASE WHEN sl.STOCKPOSTTYPE = 0 THEN sl.POSTEDVALUE ELSE sl.POSTEDVALUE * -1 END) = 0
                THEN 1
                ELSE SUM(CASE WHEN sl.STOCKPOSTTYPE = 0 THEN sl.POSTEDVALUE ELSE sl.POSTEDVALUE * -1 END)
           END) * 100, 4)                                                                                   AS NonMovingPercent,
    MAX(sl.STOCKLEDGERDATE)                                     AS LastTransactedDate,
    DATEDIFF(DAY, MAX(sl.STOCKLEDGERDATE), @AsOnDate)           AS LastTransactedDays
FROM        TSTOCKLEDGER sl
JOIN        MBIZTRANSACTIONTYPE btt   ON btt.BIZTRANSACTIONTYPEID = sl.BIZTRANSACTIONTYPEID
JOIN        MITEM i                   ON i.ITEMID = sl.ITEMID
JOIN        MSKU sku                  ON sku.SKUID = sl.SKUID
JOIN        MITEMCATEGORY c           ON c.ITEMCATEGORYID = i.CATEGORYID
WHERE   sl.OUID = @OuId
    AND sl.STOCKPOSTTYPE IN (0, 1)
    AND sl.STOCKLEDGERDATE <= @AsOnDate
    AND (@ItemCategoryId IS NULL OR i.CATEGORYID = @ItemCategoryId)
    -- Stock-adjustment/transfer exclusion -- see class doc comment for provenance of these ids.
    AND btt.BIZTRANSACTIONCLASSID NOT IN
        (-1799999914, -1399999839, -1399999838, -1799999909, -1799999913)
GROUP BY sl.ITEMID, sl.SKUID, i.ITEMCODE, i.ITEMNAME, sku.SKUCODE, sku.SKUNAME,
         i.CATEGORYID, c.ITEMCATEGORYCODE, c.ITEMCATEGORYNAME
HAVING  SUM(CASE WHEN sl.STOCKPOSTTYPE = 0 THEN sl.GOODQUANTITY ELSE sl.GOODQUANTITY * -1 END) <> 0
    AND DATEDIFF(DAY, MAX(sl.STOCKLEDGERDATE), @AsOnDate) > @AgeDays
ORDER BY c.ITEMCATEGORYCODE, i.ITEMCODE";

        // Stock Movement tile -- fresh design against TSTOCKLEDGER's real columns (legacy's
        // equivalent, StockLedgerDAL.GetStockLedger, is HQL/NHibernate with no portable SQL -- see
        // StockMovementDashboardDTO.cs for the full provenance note). Three independent result
        // sets composed by the DAL into one dashboard object, same shape as Finance's
        // AccountReceivableDashboardReport (Kpi+InchargeWise+AgeingWise).
        //
        // STOCKPOSTTYPE: 0=IN, 1=OUT, 2=WIP IN, 3=WIP OUT (confirmed via GB5's own
        // StockLedgerRowDTO.cs comment) -- this tile reports normal IN/OUT (0,1) only; WIP
        // movement is surfaced separately by the WIP Tracker tile's utilization/position views.
        //
        // Required indexes:
        //   TSTOCKLEDGER: (OUID, ITEMID, STOCKPOSTTYPE, STOCKLEDGERDATE)
        public const string GET_STOCK_MOVEMENT_DAILY_TREND = @"
SELECT
    CAST(sl.STOCKLEDGERDATE AS DATE)                                                        AS MovementDate,
    SUM(CASE WHEN sl.STOCKPOSTTYPE = 0 THEN sl.GOODQUANTITY ELSE 0 END)                      AS InwardQuantity,
    SUM(CASE WHEN sl.STOCKPOSTTYPE = 0 THEN sl.POSTEDVALUE ELSE 0 END)                       AS InwardValue,
    SUM(CASE WHEN sl.STOCKPOSTTYPE = 1 THEN sl.GOODQUANTITY ELSE 0 END)                      AS OutwardQuantity,
    SUM(CASE WHEN sl.STOCKPOSTTYPE = 1 THEN sl.POSTEDVALUE ELSE 0 END)                       AS OutwardValue
FROM   TSTOCKLEDGER sl
WHERE  sl.OUID = @OuId
   AND sl.STOCKPOSTTYPE IN (0, 1)
   AND sl.STOCKLEDGERDATE >= @PeriodFromDate AND sl.STOCKLEDGERDATE <= @PeriodToDate
GROUP BY CAST(sl.STOCKLEDGERDATE AS DATE)
ORDER BY CAST(sl.STOCKLEDGERDATE AS DATE)";

        public const string GET_STOCK_MOVEMENT_BY_SOURCE = @"
SELECT
    btt.BIZTRANSACTIONTYPEID                                                                 AS BizTransactionTypeId,
    btt.BIZTRANSACTIONTYPENAME                                                                AS SourceLabel,
    SUM(CASE WHEN sl.STOCKPOSTTYPE = 0 THEN sl.GOODQUANTITY ELSE sl.GOODQUANTITY * -1 END)    AS Quantity,
    SUM(CASE WHEN sl.STOCKPOSTTYPE = 0 THEN sl.POSTEDVALUE ELSE sl.POSTEDVALUE * -1 END)      AS Value
FROM   TSTOCKLEDGER sl
JOIN   MBIZTRANSACTIONTYPE btt ON btt.BIZTRANSACTIONTYPEID = sl.BIZTRANSACTIONTYPEID
WHERE  sl.OUID = @OuId
   AND sl.STOCKPOSTTYPE IN (0, 1)
   AND sl.STOCKLEDGERDATE >= @PeriodFromDate AND sl.STOCKLEDGERDATE <= @PeriodToDate
GROUP BY btt.BIZTRANSACTIONTYPEID, btt.BIZTRANSACTIONTYPENAME
ORDER BY SUM(CASE WHEN sl.STOCKPOSTTYPE = 0 THEN sl.POSTEDVALUE ELSE sl.POSTEDVALUE * -1 END) DESC";

        public const string GET_STOCK_MOVEMENT_TOP_MOVERS = @"
SELECT TOP (@TopN)
    sl.ITEMID                                                                                AS ItemId,
    i.ITEMCODE                                                                                AS ItemCode,
    i.ITEMNAME                                                                                AS ItemName,
    SUM(CASE WHEN sl.STOCKPOSTTYPE = 0 THEN sl.GOODQUANTITY ELSE 0 END)                       AS InwardQuantity,
    SUM(CASE WHEN sl.STOCKPOSTTYPE = 1 THEN sl.GOODQUANTITY ELSE 0 END)                       AS OutwardQuantity
FROM   TSTOCKLEDGER sl
JOIN   MITEM i ON i.ITEMID = sl.ITEMID
WHERE  sl.OUID = @OuId
   AND sl.STOCKPOSTTYPE IN (0, 1)
   AND sl.STOCKLEDGERDATE >= @PeriodFromDate AND sl.STOCKLEDGERDATE <= @PeriodToDate
GROUP BY sl.ITEMID, i.ITEMCODE, i.ITEMNAME
ORDER BY SUM(sl.GOODQUANTITY) DESC";

        // Stock Overview / Stock Position Analysis tile -- new aggregation over TSTOCKPOSITION.
        // See StockOverviewDashboardDTO.cs for the full provenance note, including how WIP-by-stage
        // folds into this tile via GET_WIP_BY_STAGE (grouped by SKUID, not a separate mechanism).
        //
        // Required indexes:
        //   TSTOCKPOSITION: (OUID, ITEMID, SKUID, STOREID) -- already the table's natural key
        public const string GET_STOCK_BY_CATEGORY = @"
SELECT
    c.ITEMCATEGORYID                                            AS ItemCategoryId,
    c.ITEMCATEGORYCODE                                          AS ItemCategoryCode,
    c.ITEMCATEGORYNAME                                          AS ItemCategoryName,
    SUM(t.QUANTITY)                                             AS Quantity,
    SUM(t.AVERAGEVALUE)                                         AS Value
FROM   TSTOCKPOSITION t
JOIN   MITEM i         ON i.ITEMID = t.ITEMID
JOIN   MITEMCATEGORY c ON c.ITEMCATEGORYID = i.CATEGORYID
WHERE  t.OUID = @OuId AND t.QUANTITY <> 0
GROUP BY c.ITEMCATEGORYID, c.ITEMCATEGORYCODE, c.ITEMCATEGORYNAME
ORDER BY SUM(t.AVERAGEVALUE) DESC";

        public const string GET_STOCK_BY_WAREHOUSE = @"
SELECT
    s.STOREID                                                   AS StoreId,
    s.STORECODE                                                 AS StoreCode,
    s.STORENAME                                                 AS StoreName,
    SUM(t.QUANTITY)                                             AS Quantity,
    SUM(t.AVERAGEVALUE)                                         AS Value
FROM   TSTOCKPOSITION t
JOIN   MSTORE s ON s.STOREID = t.STOREID
WHERE  t.OUID = @OuId AND t.QUANTITY <> 0
GROUP BY s.STOREID, s.STORECODE, s.STORENAME
ORDER BY SUM(t.AVERAGEVALUE) DESC";

        public const string GET_WIP_BY_STAGE = @"
SELECT
    t.SKUID                                                     AS SKUId,
    sku.SKUCODE                                                 AS SKUCode,
    sku.SKUNAME                                                 AS SKUName,
    SUM(t.WIPQUANTITY)                                          AS WipQuantity,
    SUM(t.WIPQUANTITY * t.AVERAGECOST)                          AS WipValue
FROM   TSTOCKPOSITION t
JOIN   MSKU sku ON sku.SKUID = t.SKUID
WHERE  t.OUID = @OuId AND t.WIPQUANTITY <> 0
GROUP BY t.SKUID, sku.SKUCODE, sku.SKUNAME
ORDER BY SUM(t.WIPQUANTITY * t.AVERAGECOST) DESC";

        // Alerts reuse the same reorder-breach predicate as GET_REORDER_BREACH_REPORT
        // (SOH < ROL) for consistency between the two tiles.
        public const string GET_STOCK_ALERTS = @"
SELECT
    SUM(CASE WHEN ISNULL(t.QUANTITY,0) - ISNULL(t.RESERVEDQUANTITY,0) <= 0 THEN 1 ELSE 0 END)               AS StockoutCount,
    SUM(CASE WHEN (ISNULL(t.QUANTITY,0) - ISNULL(t.RESERVEDQUANTITY,0)) < ISNULL(ist.ROL,0) THEN 1 ELSE 0 END) AS ReorderBreachCount,
    SUM(CASE WHEN ISNULL(t.QUANTITY,0) > ISNULL(iou.MAXLEVEL, i.MAXLEVEL) AND ISNULL(iou.MAXLEVEL, i.MAXLEVEL) > 0 THEN 1 ELSE 0 END) AS OverstockCount
FROM        TSTOCKPOSITION t
JOIN        MITEM i     ON i.ITEMID = t.ITEMID
LEFT JOIN   MITEMOU iou ON iou.ITEMID = t.ITEMID AND iou.OUID = t.OUID
OUTER APPLY (
    SELECT TOP 1 ist2.ROL
    FROM   MITEMSTOCK ist2
    WHERE  ist2.ITEMID = t.ITEMID AND ist2.SKUID = t.SKUID AND ist2.STOREID = t.STOREID
    ORDER  BY ist2.ROL DESC
) ist
WHERE  t.OUID = @OuId";

        // Stock Holding Trend tile -- running-balance-over-time query on TSTOCKLEDGER. Derived
        // table (not a leading CTE) for the daily net movement, an opening-balance derived table
        // for everything before the period start, and a window SUM() for the running total --
        // same technique as Finance's AccountLedger running-balance report. See
        // StockHoldingTrendDTO.cs for the full provenance note.
        //
        // Required indexes:
        //   TSTOCKLEDGER: (OUID, ITEMID, STOCKPOSTTYPE, STOCKLEDGERDATE)
        public const string GET_STOCK_HOLDING_TREND = @"
SELECT
    d.MovementDate,
    d.NetQuantity,
    d.NetValue,
    o.OpeningQty   + SUM(d.NetQuantity) OVER (ORDER BY d.MovementDate ROWS UNBOUNDED PRECEDING) AS RunningQuantity,
    o.OpeningValue + SUM(d.NetValue)    OVER (ORDER BY d.MovementDate ROWS UNBOUNDED PRECEDING) AS RunningValue
FROM (
    SELECT
        CAST(sl.STOCKLEDGERDATE AS DATE)                                                              AS MovementDate,
        SUM(CASE WHEN sl.STOCKPOSTTYPE = 0 THEN sl.GOODQUANTITY WHEN sl.STOCKPOSTTYPE = 1 THEN -sl.GOODQUANTITY ELSE 0 END) AS NetQuantity,
        SUM(CASE WHEN sl.STOCKPOSTTYPE = 0 THEN sl.POSTEDVALUE  WHEN sl.STOCKPOSTTYPE = 1 THEN -sl.POSTEDVALUE  ELSE 0 END) AS NetValue
    FROM   TSTOCKLEDGER sl
    WHERE  sl.OUID = @OuId
       AND (@ItemId IS NULL OR sl.ITEMID = @ItemId)
       AND sl.STOCKPOSTTYPE IN (0, 1)
       AND sl.STOCKLEDGERDATE >= @PeriodFromDate AND sl.STOCKLEDGERDATE <= @PeriodToDate
    GROUP BY CAST(sl.STOCKLEDGERDATE AS DATE)
) d
CROSS JOIN (
    SELECT
        ISNULL(SUM(CASE WHEN sl2.STOCKPOSTTYPE = 0 THEN sl2.GOODQUANTITY WHEN sl2.STOCKPOSTTYPE = 1 THEN -sl2.GOODQUANTITY ELSE 0 END), 0) AS OpeningQty,
        ISNULL(SUM(CASE WHEN sl2.STOCKPOSTTYPE = 0 THEN sl2.POSTEDVALUE  WHEN sl2.STOCKPOSTTYPE = 1 THEN -sl2.POSTEDVALUE  ELSE 0 END), 0) AS OpeningValue
    FROM   TSTOCKLEDGER sl2
    WHERE  sl2.OUID = @OuId
       AND (@ItemId IS NULL OR sl2.ITEMID = @ItemId)
       AND sl2.STOCKPOSTTYPE IN (0, 1)
       AND sl2.STOCKLEDGERDATE < @PeriodFromDate
) o
ORDER BY d.MovementDate";

        // Subcontractor Material Tracker tile -- net-movement query on TSTOCKLEDGER filtered to
        // LOCATIONTYPE=1 ("Site"), grouped by PARTYBRANCHID. NOT TRESERVATION -- see
        // SubcontractorMaterialTrackerDTO.cs for why. Same net-movement CASE expression as
        // GET_NON_MOVING_REPORT/GET_STOCK_HOLDING_TREND, just grouped by party instead of by
        // item/date.
        //
        // Required indexes:
        //   TSTOCKLEDGER: (OUID, LOCATIONTYPE, PARTYBRANCHID, STOCKPOSTTYPE)
        public const string GET_SUBCONTRACTOR_MATERIAL_TRACKER = @"
SELECT
    pb.PARTYBRANCHID                                                                          AS PartyBranchId,
    pb.PARTYBRANCHNAME                                                                         AS PartyBranchName,
    pa.PARTYNAME                                                                                AS PartyName,
    SUM(CASE WHEN sl.STOCKPOSTTYPE = 0 THEN sl.GOODQUANTITY WHEN sl.STOCKPOSTTYPE = 1 THEN -sl.GOODQUANTITY ELSE 0 END) AS CurrentAtSiteQuantity,
    SUM(CASE WHEN sl.STOCKPOSTTYPE = 0 THEN sl.POSTEDVALUE  WHEN sl.STOCKPOSTTYPE = 1 THEN -sl.POSTEDVALUE  ELSE 0 END) AS CurrentAtSiteValue,
    MAX(sl.STOCKLEDGERDATE)                                                                     AS LastMovementDate
FROM   TSTOCKLEDGER sl
JOIN   MPARTYBRANCH pb ON pb.PARTYBRANCHID = sl.PARTYBRANCHID
JOIN   MPARTY pa       ON pa.PARTYID = pb.PARTYID
WHERE  sl.OUID = @OuId
   AND sl.LOCATIONTYPE = 1
   AND sl.STOCKPOSTTYPE IN (0, 1)
GROUP BY pb.PARTYBRANCHID, pb.PARTYBRANCHNAME, pa.PARTYNAME
HAVING  SUM(CASE WHEN sl.STOCKPOSTTYPE = 0 THEN sl.GOODQUANTITY WHEN sl.STOCKPOSTTYPE = 1 THEN -sl.GOODQUANTITY ELSE 0 END) <> 0
ORDER BY SUM(CASE WHEN sl.STOCKPOSTTYPE = 0 THEN sl.POSTEDVALUE WHEN sl.STOCKPOSTTYPE = 1 THEN -sl.POSTEDVALUE ELSE 0 END) DESC";

        // KPI Summary tile -- the two numbers that don't come from another tile's BLL method.
        // See InventoryKPISummaryDTO.cs for why TotalStockValue is bespoke (balance, not a flow)
        // while movement value is read from the FINVENTORY fact posted by the generic DW engine.
        public const string GET_TOTAL_STOCK_VALUE = @"
SELECT ISNULL(SUM(t.AVERAGEVALUE), 0)
FROM   TSTOCKPOSITION t
WHERE  t.OUID = @OuId AND t.QUANTITY <> 0";

        // DATEID on the warehouse fact tables is DimDate's YYYYMMDD integer key (confirmed via
        // WarehouseFactPostingBLL's own "LASTPOSTEDDATEID = DimDate.DateKey format" convention) --
        // a plain integer range comparison, no DimDate join needed.
        public const string GET_MOVEMENT_VALUE_FROM_FACT = @"
SELECT
    ISNULL(SUM(f.STOCKMOVEMENTINVALUE), 0)  AS TotalMovementInValue,
    ISNULL(SUM(f.STOCKMOVEMENTOUTVALUE), 0) AS TotalMovementOutValue
FROM   FINVENTORY f
WHERE  f.OUID = @OuId AND f.TENANTID = @TenantId
   AND f.DATEID >= @FromDateId AND f.DATEID <= @ToDateId";
    }
}