namespace MMDAL.Query.StockValuation;

/// <summary>
/// SQL for FIFO/LIFO lot-tracked item cost allocation.
/// Replaces PROCFIFOLOT, PROCBULKFIFOPOSTTOLOT, and PROCFIFOPRODUCTIONINPUT stored procedures.
///
/// All multi-step operations are batched into a single string so temp table scope survives
/// across the DAL call.  Stored-proc cursors are replaced by CTE window functions.
///
/// Required indexes:
///   TLOT:       (ITEMID, SKUID)
///   TLOTDETAIL: (LOTID, STOCKPOSTTYPE)  covering OUID, STOREID, GOODQUANTITY
///   TSTOCKLEDGER: (OBJECTID, OBJECTTYPEID, STOCKPOSTTYPE) covering ITEMID, SKUID, OUID, STOREID
/// </summary>
public static class FifoLotCostQB
{
    // ── Single-issue FIFO lot allocation ─────────────────────────────────
    // Replaces PROCFIFOLOT (FIFO direction).
    // Returns one row per lot slice: LotId, LotQuantity, PostedCost.
    // BLL blends these to get the weighted-average issue cost.
    public const string ALLOCATE_FIFO_LOTS = @"
        WITH lot_balance AS (
            SELECT  tl.LOTID,
                    tl.ITEMID,
                    tl.SKUID,
                    tld.OUID,
                    tl.LOTDATE,
                    tl.ITEMPOSTEDCOST,
                    SUM(CASE WHEN tld.STOCKPOSTTYPE = 0
                              THEN  tld.GOODQUANTITY
                              ELSE -tld.GOODQUANTITY END) AS STOCK
            FROM    TLOT       tl
            JOIN    TLOTDETAIL tld ON tld.LOTID         = tl.LOTID
            WHERE   tl.ITEMID           = @ItemId
                AND tl.SKUID            = @SKUId
                AND tld.OUID            = @OUID
                AND tld.STOREID         = @StoreId
                AND tld.STOCKPOSTTYPE  IN (0, 1)
            GROUP BY tl.LOTID, tl.ITEMID, tl.SKUID, tld.OUID, tl.LOTDATE, tl.ITEMPOSTEDCOST
            HAVING SUM(CASE WHEN tld.STOCKPOSTTYPE = 0
                             THEN  tld.GOODQUANTITY
                             ELSE -tld.GOODQUANTITY END) > 0
        ),
        lot_running AS (
            SELECT  LOTID, ITEMID, SKUID, OUID, LOTDATE, ITEMPOSTEDCOST, STOCK,
                    SUM(STOCK) OVER (
                        PARTITION BY ITEMID, SKUID, OUID
                        ORDER BY     LOTDATE ASC, LOTID ASC   -- oldest first = FIFO
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                    ) AS RUNSTOCK
            FROM    lot_balance
        )
        SELECT  vv.LOTID           AS LotId,
                vv.FINALQTY        AS LotQuantity,
                vv.ITEMPOSTEDCOST  AS PostedCost
        FROM   (VALUES (@ItemId, @SKUId, @OUID, @StoreId, @IssueQty))
                     req(ITEMID, SKUID, OUID, STOREID, GOODQUANTITY)
        OUTER APPLY (
            SELECT  aa.LOTID,
                    aa.ITEMPOSTEDCOST,
                    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    lot_running aa
            WHERE   aa.ITEMID  = req.ITEMID
                AND aa.SKUID   = req.SKUID
                AND aa.OUID    = req.OUID
        ) vv
        WHERE  vv.FINALQTY > 0
        ORDER BY vv.LOTID";

    // ── Single-issue LIFO lot allocation ─────────────────────────────────
    // Identical to ALLOCATE_FIFO_LOTS but window ORDER BY is reversed.
    public const string ALLOCATE_LIFO_LOTS = @"
        WITH lot_balance AS (
            SELECT  tl.LOTID,
                    tl.ITEMID,
                    tl.SKUID,
                    tld.OUID,
                    tl.LOTDATE,
                    tl.ITEMPOSTEDCOST,
                    SUM(CASE WHEN tld.STOCKPOSTTYPE = 0
                              THEN  tld.GOODQUANTITY
                              ELSE -tld.GOODQUANTITY END) AS STOCK
            FROM    TLOT       tl
            JOIN    TLOTDETAIL tld ON tld.LOTID        = tl.LOTID
            WHERE   tl.ITEMID           = @ItemId
                AND tl.SKUID            = @SKUId
                AND tld.OUID            = @OUID
                AND tld.STOREID         = @StoreId
                AND tld.STOCKPOSTTYPE  IN (0, 1)
            GROUP BY tl.LOTID, tl.ITEMID, tl.SKUID, tld.OUID, tl.LOTDATE, tl.ITEMPOSTEDCOST
            HAVING SUM(CASE WHEN tld.STOCKPOSTTYPE = 0
                             THEN  tld.GOODQUANTITY
                             ELSE -tld.GOODQUANTITY END) > 0
        ),
        lot_running AS (
            SELECT  LOTID, ITEMID, SKUID, OUID, LOTDATE, ITEMPOSTEDCOST, STOCK,
                    SUM(STOCK) OVER (
                        PARTITION BY ITEMID, SKUID, OUID
                        ORDER BY     LOTDATE DESC, LOTID DESC  -- newest first = LIFO
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                    ) AS RUNSTOCK
            FROM    lot_balance
        )
        SELECT  vv.LOTID           AS LotId,
                vv.FINALQTY        AS LotQuantity,
                vv.ITEMPOSTEDCOST  AS PostedCost
        FROM   (VALUES (@ItemId, @SKUId, @OUID, @StoreId, @IssueQty))
                     req(ITEMID, SKUID, OUID, STOREID, GOODQUANTITY)
        OUTER APPLY (
            SELECT  aa.LOTID,
                    aa.ITEMPOSTEDCOST,
                    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    lot_running aa
            WHERE   aa.ITEMID  = req.ITEMID
                AND aa.SKUID   = req.SKUID
                AND aa.OUID    = req.OUID
        ) vv
        WHERE  vv.FINALQTY > 0
        ORDER BY vv.LOTID";

    // ── Lot receipt (inward) ──────────────────────────────────────────────
    // Creates a TLOT entry for inbound stock.  Called at transaction posting time
    // for FIFO/LIFO lot-tracked items.  @LotId comes from the MAUTONUMBER sequence.
    public const string INSERT_LOT_RECEIPT = @"
        INSERT INTO TLOT
            (LOTID, LOTNUMBER, ITEMID, SKUID, LOTDATE, LOTEXPIRYDATE,
             REMARKS, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
             PROCESSID, LOTTYPEID, MRPRATE, ITEMPOSTEDCOST)
        VALUES
            (@LotId, @LotNumber, @ItemId, @SKUId, @LotDate, '9999-12-31',
             @Remarks, 0, 1, @CreatedById, GETUTCDATE(), @CreatedById, GETUTCDATE(),
             -1, -1, @MrpRate, @ItemPostedCost)";

    // Creates a TLOTDETAIL entry for inbound stock (StockPostType=0).
    public const string INSERT_LOT_DETAIL_INBOUND = @"
        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, REMARKS,
             MARKEDGOODQUANTITY, D3, D4, ITEMPOSTEDCOST)
        VALUES
            (@LotDetailId, @BizTransactionTypeId, @ObjectTypeId, @ObjectId,
             @ObjectDetailTypeId, @ObjectDetailId, 1, @LotId, @Quantity, @GoodQuantity,
             @RejectedQuantity, @ReworkQuantity, @OtherQuantity, -1, 0,
             @OUID, @StoreId, @StockLedgerNumber, @StockLedgerDate, @ReferenceNumber,
             @ReferenceDate, 0, -1, 0,
             0, -1, -1, @Remarks,
             0, 0, 0, @ItemPostedCost)";

    // Creates a TLOTDETAIL entry for a FIFO/LIFO issue allocation slice (StockPostType=1).
    // Called once per lot slice returned by ALLOCATE_FIFO_LOTS / ALLOCATE_LIFO_LOTS.
    public const string INSERT_LOT_DETAIL_ISSUE = @"
        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, REMARKS,
             MARKEDGOODQUANTITY, D3, D4)
        VALUES
            (@LotDetailId, @BizTransactionTypeId, @ObjectTypeId, @ObjectId,
             @ObjectDetailTypeId, @ObjectDetailId, @SlNo, @LotId, @Quantity, @Quantity,
             0, 0, 0, -1, 0,
             @OUID, @StoreId, @StockLedgerNumber, @StockLedgerDate, @ReferenceNumber,
             @ReferenceDate, 0, -1, 1,
             0, -1, -1, @Remarks,
             0, 0, 0)";

    // ── Batch re-run: fetch issue lines needing lot allocation ────────────
    // Returns all issue TSTOCKLEDGER rows for a document that are lot-tracked
    // and need FIFO/LIFO allocation (batch re-run scenario).
    // BLL iterates these in date/id order and calls ALLOCATE_FIFO_LOTS for each.
    public const string GET_ISSUE_LINES_FOR_FIFO_RERUN = @"
        SELECT  sl.STOCKLEDGERID,
                sl.ITEMID,
                sl.SKUID,
                sl.OUID,
                sl.STOREID,
                sl.GOODQUANTITY,
                sl.STOCKLEDGERDATE,
                sl.STOCKLEDGERNUMBER,
                sl.OBJECTID,
                sl.OBJECTTYPEID,
                sl.OBJECTDETAILID,
                sl.OBJECTDETAILTYPEID,
                sl.BIZTRANSACTIONTYPEID,
                sl.REFERENCENUMBER,
                sl.REFERENCEDATE
        FROM    TSTOCKLEDGER  sl
        JOIN    MITEM         i ON i.ITEMID = sl.ITEMID
        WHERE   sl.OUID           = @OUID
            AND sl.OBJECTID       = @ObjectId
            AND sl.OBJECTTYPEID   = @ObjectTypeId
            AND sl.STOCKPOSTTYPE  = 1        -- issues only
            AND i.ISBATCHSTOCK    = 0        -- lot-tracked (0=yes in legacy)
        ORDER BY sl.STOCKLEDGERDATE, sl.STOCKLEDGERID";

    // Cleans up existing TLOTDETAIL for a document before FIFO re-run.
    // Mirrors the DELETE at the start of PROCBULKFIFOPOSTTOLOT.
    // Also removes orphan TLOT rows (no associated TLOTDETAIL remaining).
    public const string DELETE_LOT_DETAILS_FOR_RERUN = @"
        DELETE TLOTDETAIL
        FROM   TLOTDETAIL
        JOIN   MBIZTRANSACTIONTYPE btt
            ON btt.BIZTRANSACTIONTYPEID = TLOTDETAIL.BIZTRANSACTIONTYPEID
        WHERE  TLOTDETAIL.OUID                = @OUID
            AND TLOTDETAIL.OBJECTHEADERID     = @ObjectId
            AND TLOTDETAIL.OBJECTHEADERTYPEID = @ObjectTypeId
            AND TLOTDETAIL.STOCKPOSTTYPE      IN (0, 1);

        DELETE TLOT
        FROM   TLOT t
        LEFT JOIN TLOTDETAIL td ON td.LOTID = t.LOTID
        WHERE  td.LOTDETAILID IS NULL
            AND t.LOTID <> -1";

    // ── Open lot balances for cost workings display ───────────────────────
    public const string GET_OPEN_LOT_BALANCES = @"
        SELECT  tl.LOTID                                            AS LotId,
                tl.LOTNUMBER                                        AS LotNumber,
                tl.LOTDATE                                          AS LotDate,
                tl.ITEMPOSTEDCOST                                   AS UnitCost,
                SUM(CASE WHEN tld.STOCKPOSTTYPE = 0
                          THEN  tld.GOODQUANTITY
                          ELSE -tld.GOODQUANTITY END)               AS RemainingQty
        FROM    TLOT       tl
        JOIN    TLOTDETAIL tld ON tld.LOTID        = tl.LOTID
        WHERE   tl.ITEMID          = @ItemId
            AND tl.SKUID           = @SKUId
            AND tld.OUID           = @OUID
            AND tld.STOREID        = @StoreId
            AND tld.STOCKPOSTTYPE IN (0, 1)
        GROUP BY tl.LOTID, tl.LOTNUMBER, tl.LOTDATE, tl.ITEMPOSTEDCOST
        HAVING  SUM(CASE WHEN tld.STOCKPOSTTYPE = 0
                          THEN  tld.GOODQUANTITY
                          ELSE -tld.GOODQUANTITY END) > 0
        ORDER BY tl.LOTDATE ASC, tl.LOTID ASC";
}