namespace MMDAL.Query.StockValuation;

/// <summary>
/// SQL for FIFO/LIFO cost-layer management for non-lot-tracked items.
/// New in GB5 — replaces the implicit lot logic for items where ISBATCHSTOCK=1
/// (not lot-tracked), using the new TCOSTLAYER + TCOSTLAYERDETAIL tables.
///
/// Same FIFO/LIFO math as FifoLotCostQB but sources layers from TCOSTLAYER
/// instead of TLOT/TLOTDETAIL.
///
/// Required indexes:
///   TCOSTLAYER: (OUID, STOREID, ITEMID, SKUID, LAYERDATE, ISFULLYCONSUMED)
///               WHERE ISFULLYCONSUMED = 0 (filtered index for open layers)
///   TCOSTLAYERDETAIL: (COSTLAYERID), (ISSUESTOCKLEDGERID)
/// </summary>
public static class FifoLayerCostQB
{
    // ── Layer creation (inward stock) ─────────────────────────────────────
    // Called at transaction posting time when stock comes in for a FIFO/LIFO
    // non-lot item.  One layer per TSTOCKLEDGER receipt row.
    public const string CREATE_COST_LAYER = @"
        INSERT INTO TCOSTLAYER
            (COSTLAYERID, OUID, STOREID, ITEMID, SKUID, LAYERDATE,
             ORIGINALQUANTITY, REMAININGQUANTITY, UNITCOST,
             OBJECTTYPEID, OBJECTID, STOCKLEDGERID, ISFULLYCONSUMED, CREATEDON)
        VALUES
            (@CostLayerId, @OUID, @StoreId, @ItemId, @SKUId, @LayerDate,
             @Quantity, @Quantity, @UnitCost,
             @ObjectTypeId, @ObjectId, @StockLedgerId, 0, GETUTCDATE())";

    // ── Single-issue FIFO layer allocation ───────────────────────────────
    // Returns one row per layer slice consumed by the issue.
    // Identical math to FifoLotCostQB.ALLOCATE_FIFO_LOTS but from TCOSTLAYER.
    // BLL iterates slices to build blended cost and calls CONSUME_LAYERS.
    public const string ALLOCATE_FIFO_LAYERS = @"
        WITH open_layers AS (
            SELECT  COSTLAYERID,
                    ITEMID,
                    SKUID,
                    OUID,
                    STOREID,
                    LAYERDATE,
                    UNITCOST,
                    REMAININGQUANTITY AS STOCK
            FROM    TCOSTLAYER
            WHERE   ITEMID           = @ItemId
                AND SKUID            = @SKUId
                AND OUID             = @OUID
                AND STOREID          = @StoreId
                AND ISFULLYCONSUMED  = 0
        ),
        layer_running AS (
            SELECT  COSTLAYERID, ITEMID, SKUID, OUID, STOREID, LAYERDATE, UNITCOST, STOCK,
                    SUM(STOCK) OVER (
                        PARTITION BY ITEMID, SKUID, OUID, STOREID
                        ORDER BY     LAYERDATE ASC, COSTLAYERID ASC   -- oldest first = FIFO
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                    ) AS RUNSTOCK
            FROM    open_layers
        )
        SELECT  vv.COSTLAYERID  AS CostLayerId,
                vv.FINALQTY     AS ConsumedQuantity,
                vv.UNITCOST     AS UnitCost
        FROM   (VALUES (@ItemId, @SKUId, @OUID, @StoreId, @IssueQty))
                     req(ITEMID, SKUID, OUID, STOREID, GOODQUANTITY)
        OUTER APPLY (
            SELECT  aa.COSTLAYERID,
                    aa.UNITCOST,
                    CASE
                        WHEN aa.RUNSTOCK - req.GOODQUANTITY <= 0
                            THEN aa.STOCK
                        WHEN aa.RUNSTOCK - req.GOODQUANTITY >= aa.STOCK
                            THEN 0
                        ELSE aa.STOCK - (aa.RUNSTOCK - req.GOODQUANTITY)
                    END AS FINALQTY
            FROM    layer_running aa
            WHERE   aa.ITEMID   = req.ITEMID
                AND aa.SKUID    = req.SKUID
                AND aa.OUID     = req.OUID
                AND aa.STOREID  = req.STOREID
        ) vv
        WHERE  vv.FINALQTY > 0
        ORDER BY vv.COSTLAYERID";

    // ── Single-issue LIFO layer allocation ───────────────────────────────
    // Identical to ALLOCATE_FIFO_LAYERS but window ORDER BY is reversed.
    public const string ALLOCATE_LIFO_LAYERS = @"
        WITH open_layers AS (
            SELECT  COSTLAYERID,
                    ITEMID,
                    SKUID,
                    OUID,
                    STOREID,
                    LAYERDATE,
                    UNITCOST,
                    REMAININGQUANTITY AS STOCK
            FROM    TCOSTLAYER
            WHERE   ITEMID           = @ItemId
                AND SKUID            = @SKUId
                AND OUID             = @OUID
                AND STOREID          = @StoreId
                AND ISFULLYCONSUMED  = 0
        ),
        layer_running AS (
            SELECT  COSTLAYERID, ITEMID, SKUID, OUID, STOREID, LAYERDATE, UNITCOST, STOCK,
                    SUM(STOCK) OVER (
                        PARTITION BY ITEMID, SKUID, OUID, STOREID
                        ORDER BY     LAYERDATE DESC, COSTLAYERID DESC  -- newest first = LIFO
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                    ) AS RUNSTOCK
            FROM    open_layers
        )
        SELECT  vv.COSTLAYERID  AS CostLayerId,
                vv.FINALQTY     AS ConsumedQuantity,
                vv.UNITCOST     AS UnitCost
        FROM   (VALUES (@ItemId, @SKUId, @OUID, @StoreId, @IssueQty))
                     req(ITEMID, SKUID, OUID, STOREID, GOODQUANTITY)
        OUTER APPLY (
            SELECT  aa.COSTLAYERID,
                    aa.UNITCOST,
                    CASE
                        WHEN aa.RUNSTOCK - req.GOODQUANTITY <= 0
                            THEN aa.STOCK
                        WHEN aa.RUNSTOCK - req.GOODQUANTITY >= aa.STOCK
                            THEN 0
                        ELSE aa.STOCK - (aa.RUNSTOCK - req.GOODQUANTITY)
                    END AS FINALQTY
            FROM    layer_running aa
            WHERE   aa.ITEMID   = req.ITEMID
                AND aa.SKUID    = req.SKUID
                AND aa.OUID     = req.OUID
                AND aa.STOREID  = req.STOREID
        ) vv
        WHERE  vv.FINALQTY > 0
        ORDER BY vv.COSTLAYERID";

    // ── Layer consumption update ──────────────────────────────────────────
    // Decrements RemainingQuantity and marks fully-consumed layers.
    // Called for each layer slice returned by ALLOCATE_FIFO/LIFO_LAYERS.
    public const string CONSUME_LAYER = @"
        UPDATE TCOSTLAYER
        SET    REMAININGQUANTITY = REMAININGQUANTITY - @ConsumedQty,
               ISFULLYCONSUMED  = CASE
                   WHEN REMAININGQUANTITY - @ConsumedQty <= 0 THEN 1
                   ELSE 0
               END
        WHERE  COSTLAYERID = @CostLayerId
            AND ISFULLYCONSUMED = 0";

    // ── Consumption audit ─────────────────────────────────────────────────
    // Records each layer slice consumption in TCOSTLAYERDETAIL.
    public const string INSERT_LAYER_CONSUMPTION = @"
        INSERT INTO TCOSTLAYERDETAIL
            (COSTLAYERDETAILID, COSTLAYERID, ISSUESTOCKLEDGERID,
             CONSUMEDQUANTITY, UNITCOST, CONSUMEDON)
        VALUES
            (@CostLayerDetailId, @CostLayerId, @IssueStockLedgerId,
             @ConsumedQuantity, @UnitCost, GETUTCDATE())";

    // ── Open layers for cost workings display ────────────────────────────
    public const string GET_OPEN_COST_LAYERS = @"
        SELECT  cl.COSTLAYERID                                         AS CostLayerId,
                cl.LAYERDATE                                           AS LayerDate,
                cl.ORIGINALQUANTITY                                    AS OriginalQty,
                cl.REMAININGQUANTITY                                   AS RemainingQty,
                cl.UNITCOST                                            AS UnitCost,
                ROUND(cl.REMAININGQUANTITY * cl.UNITCOST, 4)           AS RemainingValue,
                cl.OBJECTTYPEID                                        AS ObjectTypeId,
                cl.OBJECTID                                            AS ObjectId,
                ISNULL(mmh.DOCUMENTNUMBER, '')                        AS DocNumber
        FROM    TCOSTLAYER    cl
        LEFT JOIN TMMHEAD     mmh ON mmh.DOCUMENTID   = cl.OBJECTID
                                 AND cl.OBJECTTYPEID  = -1899997952
        WHERE   cl.ITEMID          = @ItemId
            AND cl.SKUID           = @SKUId
            AND cl.OUID            = @OUID
            AND cl.STOREID         = @StoreId
            AND cl.ISFULLYCONSUMED = 0
        ORDER BY cl.LAYERDATE ASC, cl.COSTLAYERID ASC";

    // ── Blended cost for next issue (FIFO) ───────────────────────────────
    // Returns what the blended unit cost would be if an issue of @IssueQty were made now.
    // Used by cost workings display — never for actual posting.
    public const string GET_FIFO_BLENDED_COST_PREVIEW = @"
        WITH open_layers AS (
            SELECT  COSTLAYERID, LAYERDATE, UNITCOST,
                    REMAININGQUANTITY AS STOCK
            FROM    TCOSTLAYER
            WHERE   ITEMID  = @ItemId AND SKUID = @SKUId
                AND OUID    = @OUID   AND STOREID = @StoreId
                AND ISFULLYCONSUMED = 0
        ),
        layer_running AS (
            SELECT  COSTLAYERID, LAYERDATE, UNITCOST, STOCK,
                    SUM(STOCK) OVER (
                        ORDER BY LAYERDATE ASC, COSTLAYERID ASC
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                    ) AS RUNSTOCK
            FROM    open_layers
        ),
        slices AS (
            SELECT  vv.FINALQTY,
                    vv.UNITCOST
            FROM   (VALUES (@IssueQty)) req(GOODQUANTITY)
            OUTER APPLY (
                SELECT  aa.UNITCOST,
                        CASE
                            WHEN aa.RUNSTOCK - req.GOODQUANTITY <= 0
                                THEN aa.STOCK
                            WHEN aa.RUNSTOCK - req.GOODQUANTITY >= aa.STOCK
                                THEN 0
                            ELSE aa.STOCK - (aa.RUNSTOCK - req.GOODQUANTITY)
                        END AS FINALQTY
                FROM    layer_running aa
            ) vv
            WHERE  vv.FINALQTY > 0
        )
        SELECT  CASE WHEN SUM(FINALQTY) > 0
                     THEN ROUND(SUM(FINALQTY * UNITCOST) / SUM(FINALQTY), 4)
                     ELSE 0
                END AS BlendedCost,
                SUM(FINALQTY)         AS AllocatedQty
        FROM    slices";

    // ── Consumption history for audit ────────────────────────────────────
    public const string GET_LAYER_CONSUMPTION_HISTORY = @"
        SELECT  cld.COSTLAYERDETAILID   AS CostLayerDetailId,
                cld.COSTLAYERID         AS CostLayerId,
                cld.ISSUESTOCKLEDGERID  AS IssueStockLedgerId,
                cld.CONSUMEDQUANTITY    AS ConsumedQuantity,
                cld.UNITCOST            AS UnitCost,
                cld.CONSUMEDON          AS ConsumedOn,
                sl.STOCKLEDGERDATE      AS IssueDate,
                ISNULL(mmh.DOCUMENTNUMBER, '') AS IssueDocNumber
        FROM    TCOSTLAYERDETAIL cld
        JOIN    TCOSTLAYER       cl  ON cl.COSTLAYERID   = cld.COSTLAYERID
        JOIN    TSTOCKLEDGER     sl  ON sl.STOCKLEDGERID = cld.ISSUESTOCKLEDGERID
        LEFT JOIN TMMHEAD        mmh ON mmh.DOCUMENTID   = sl.OBJECTID
                                    AND sl.OBJECTTYPEID  = -1899997952
        WHERE   cl.COSTLAYERID = @CostLayerId
        ORDER BY cld.CONSUMEDON";
}