namespace MMDAL.Query.StockValuation;

/// <summary>
/// SQL for TSTOCKPOSITION reads, upserts, and quantity updates.
/// The AverageValue / AverageCost updates live in StockLedgerCostQB
/// (APPLY_COST_AFFECTING_* constants) and are called separately.
///
/// Required indexes:
///   TSTOCKPOSITION: (OUID, ITEMID, SKUID, STOREID) — unique, covering QUANTITY, AVERAGECOST, AVERAGEVALUE
/// </summary>
public static class StockPositionQB
{
    public const string GET_STOCK_POSITION = @"
        SELECT  sp.STOCKPOSITIONID   AS StockPositionId,
                sp.OUID,
                sp.STOREID           AS StoreId,
                sp.ITEMID            AS ItemId,
                sp.SKUID             AS SKUId,
                sp.QUANTITY          AS Quantity,
                sp.AVERAGEVALUE      AS AverageValue,
                sp.AVERAGECOST       AS AverageCost,
                ISNULL(mi.ISMAKEIT, 0)       AS IsMakeOrBuy,
                ISNULL(mi.VALUATIONMETHOD, 0) AS ValuationMethod,
                0                            AS IsStorewiseValuation
        FROM    TSTOCKPOSITION sp
        JOIN    MITEM mi ON mi.ITEMID = sp.ITEMID
        WHERE   sp.OUID    = @OUID
            AND sp.ITEMID  = @ItemId
            AND sp.SKUID   = @SKUId
            AND sp.STOREID = @StoreId";

    // Inserts a zero-balance row if no position record exists.
    // Called by StockLedgerBLL.PostAsync for the first transaction on an item.
    public const string INSERT_POSITION_IF_MISSING = @"
        IF NOT EXISTS (
            SELECT 1 FROM TSTOCKPOSITION
            WHERE OUID = @OUID AND ITEMID = @ItemId AND SKUID = @SKUId AND STOREID = @StoreId
        )
        BEGIN
            INSERT INTO TSTOCKPOSITION
                (OUID, STOREID, ITEMID, SKUID, QUANTITY, SHADOWQUANTITY,
                 REJECTEDQUANTITY, REWORKQUANTITY, OTHERQUANTITY, WIPQUANTITY,
                 UNLINKEDWIPGOODQUANTITY, UNLINKEDWIPREWORKQUANTITY,
                 AVERAGEVALUE, AVERAGECOST)
            VALUES
                (@OUID, @StoreId, @ItemId, @SKUId, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
        END";

    // Updates all quantity fields after a stock transaction is posted.
    // @PostSign: +1 for receipts (StockPostType=0), -1 for issues (StockPostType=1).
    public const string UPDATE_POSITION_QUANTITIES = @"
        UPDATE TSTOCKPOSITION
        SET    QUANTITY                   = QUANTITY                   + @GoodQuantity          * @PostSign,
               SHADOWQUANTITY            = SHADOWQUANTITY            + @ShadowGoodQuantity     * (@PostSign * -1),
               REJECTEDQUANTITY          = REJECTEDQUANTITY          + @RejectedQuantity       * @PostSign,
               REWORKQUANTITY            = REWORKQUANTITY            + @ReworkQuantity         * @PostSign,
               OTHERQUANTITY             = OTHERQUANTITY             + @OtherQuantity          * @PostSign,
               WIPQUANTITY               = WIPQUANTITY               + @WipQuantity            * @PostSign,
               UNLINKEDWIPGOODQUANTITY   = UNLINKEDWIPGOODQUANTITY   + @UnlinkedWipGoodQty    * @PostSign,
               UNLINKEDWIPREWORKQUANTITY = UNLINKEDWIPREWORKQUANTITY + @UnlinkedWipReworkQty  * @PostSign
        WHERE  OUID    = @OUID
           AND ITEMID  = @ItemId
           AND SKUID   = @SKUId
           AND STOREID = @StoreId";

    // Direct cost override for cost revaluation document.
    // Sets AverageCost and AverageValue without recalculating from ledger.
    // @NewAverageCost and @NewAverageValue come from the revaluation entry.
    public const string UPDATE_DIRECT_AVERAGE_COST = @"
        UPDATE TSTOCKPOSITION
        SET    AVERAGECOST  = @NewAverageCost,
               AVERAGEVALUE = @NewAverageValue
        WHERE  OUID    = @OUID
           AND ITEMID  = @ItemId
           AND SKUID   = @SKUId
           AND STOREID = @StoreId";
}