namespace MMDAL.Query.StockValuation;

/// <summary>
/// SQL for perpetual "during-transaction" cost updates.
/// Replaces stored procs: STOCKCOSTUPDATE, INTERNALSTOCKCOSTUPDATE_COSTED,
/// INTERNALSTOCKCOSTUPDATE_COSTAFFECTING.
///
/// All parameters use @Name Dapper syntax — never string substitution.
/// Each multi-step operation runs in a single batched string so temp tables
/// remain in scope across all steps (same connection, same sp_executesql block).
///
/// Required indexes:
///   TSTOCKLEDGER:  (OUID, OBJECTTYPEID, OBJECTID)
///   TSTOCKLEDGER:  (OUID, STOREID, ITEMID, SKUID)
///   TSTOCKPOSITION: (OUID, STOREID, ITEMID, SKUID) — primary key
/// </summary>
public static class StockLedgerCostQB
{
    // ── OU-level storewise setting ───────────────────────────────────────
    // Returns 0 = storewise (per-store), 1 = consolidated.
    // Default 1 (consolidated) when row not found.
    public const string GET_STOREWISE_SETTING = @"
        SELECT ISNULL(ISSTOREWISEVALUATION, 1)
        FROM   MOULEVELSETTING
        WHERE  OUID = @OUID";


    // ── Costed transaction (non-transfer) ────────────────────────────────
    // Sets PostedCost = current AverageCost from TSTOCKPOSITION.
    // Groups same-item rows first to handle multiple lines of same item
    // in one document (fixes the legacy Jan-2025 bug).
    public const string APPLY_AVERAGE_COST_NON_TRANSFER = @"
        UPDATE  TSTOCKLEDGER
        SET     POSTEDCOST  = ROUND(pos.AVERAGECOST, 4),
                COST        = ROUND(pos.AVERAGECOST, 4),
                POSTEDVALUE = ROUND(pos.AVERAGECOST * TSTOCKLEDGER.QUANTITY, 4)
        FROM    TSTOCKLEDGER
        INNER JOIN TSTOCKPOSITION pos
            ON  pos.OUID    = TSTOCKLEDGER.OUID
            AND pos.ITEMID  = TSTOCKLEDGER.ITEMID
            AND pos.SKUID   = TSTOCKLEDGER.SKUID
            AND pos.STOREID = TSTOCKLEDGER.STOREID
        WHERE   TSTOCKLEDGER.OBJECTTYPEID = @ObjectTypeId
            AND TSTOCKLEDGER.OBJECTID     = @ObjectId
            AND TSTOCKLEDGER.OUID         = @OUID";

    // ── Costed transaction (stock transfer) ──────────────────────────────
    // TO-store rows pick up FROM-store's AverageCost.
    public const string APPLY_AVERAGE_COST_TRANSFER = @"
        UPDATE  TSTOCKLEDGER
        SET     POSTEDCOST  = ROUND(pos.AVERAGECOST, 4),
                COST        = ROUND(pos.AVERAGECOST, 4),
                POSTEDVALUE = ROUND(pos.AVERAGECOST * TSTOCKLEDGER.QUANTITY, 4)
        FROM    TSTOCKLEDGER
        INNER JOIN TSTOCKPOSITION pos
            ON  pos.OUID    = TSTOCKLEDGER.OUID
            AND pos.ITEMID  = TSTOCKLEDGER.ITEMID
            AND pos.SKUID   = TSTOCKLEDGER.SKUID
            AND pos.STOREID = @FromStoreId
        WHERE   TSTOCKLEDGER.OBJECTTYPEID = @ObjectTypeId
            AND TSTOCKLEDGER.OBJECTID     = @ObjectId
            AND TSTOCKLEDGER.OUID         = @OUID";

    // ── AverageValue update after costed posting (non-transfer) ──────────
    // Aggregates movement values from the posted document and adds to position.
    // STOCKPOSTTYPE: 0=IN (+), 1=OUT (-)
    public const string UPDATE_AVERAGE_VALUE_NON_TRANSFER = @"
        UPDATE  TSTOCKPOSITION
        SET     AVERAGEVALUE = AVERAGEVALUE + mvt.MOVEMENTVALUE
        FROM   (
            SELECT  OUID, STOREID, ITEMID, SKUID,
                    SUM(
                        ROUND(POSTEDCOST * QUANTITY, 4) *
                        CASE STOCKPOSTTYPE WHEN 0 THEN 1 WHEN 1 THEN -1 ELSE 0 END
                    ) AS MOVEMENTVALUE
            FROM    TSTOCKLEDGER
            WHERE   OBJECTTYPEID = @ObjectTypeId
                AND OBJECTID     = @ObjectId
            GROUP BY OUID, STOREID, ITEMID, SKUID
        ) mvt
        WHERE   TSTOCKPOSITION.OUID    = mvt.OUID
            AND TSTOCKPOSITION.ITEMID  = mvt.ITEMID
            AND TSTOCKPOSITION.SKUID   = mvt.SKUID
            AND TSTOCKPOSITION.STOREID = mvt.STOREID";

    // ── AverageValue update (transfer — FROM-store only) ─────────────────
    public const string UPDATE_AVERAGE_VALUE_TRANSFER_FROMSTORE = @"
        UPDATE  TSTOCKPOSITION
        SET     AVERAGEVALUE = AVERAGEVALUE + mvt.MOVEMENTVALUE
        FROM   (
            SELECT  OUID, STOREID, ITEMID, SKUID,
                    SUM(
                        ROUND(POSTEDCOST * QUANTITY, 4) *
                        CASE STOCKPOSTTYPE WHEN 0 THEN 1 WHEN 1 THEN -1 ELSE 0 END
                    ) AS MOVEMENTVALUE
            FROM    TSTOCKLEDGER
            WHERE   OBJECTTYPEID = @ObjectTypeId
                AND OBJECTID     = @ObjectId
                AND STOREID      = @FromStoreId
            GROUP BY OUID, STOREID, ITEMID, SKUID
        ) mvt
        WHERE   TSTOCKPOSITION.OUID    = mvt.OUID
            AND TSTOCKPOSITION.ITEMID  = mvt.ITEMID
            AND TSTOCKPOSITION.SKUID   = mvt.SKUID
            AND TSTOCKPOSITION.STOREID = mvt.STOREID";

    // ── Cost-affecting transaction — store-wise valuation ─────────────────
    // Updates AverageValue and recomputes AverageCost for the specific store.
    // Used for freight/duty/charge postings that affect stock value.
    // @IsStockTransfer: 0=transfer (use ToStoreId), 1=non-transfer (use ledger StoreId)
    public const string APPLY_COST_AFFECTING_STOREWISE = @"
        UPDATE  TSTOCKPOSITION
        SET     AVERAGEVALUE = ROUND(ISNULL(TSTOCKPOSITION.AVERAGEVALUE,0) + ISNULL(agg.POSTEDVALUE,0), 4),
                AVERAGECOST  = CASE
                    WHEN TSTOCKPOSITION.QUANTITY <> 0
                    THEN ROUND(
                        (ISNULL(TSTOCKPOSITION.AVERAGEVALUE,0) + ISNULL(agg.POSTEDVALUE,0))
                        / ISNULL(TSTOCKPOSITION.QUANTITY,0), 4)
                    ELSE ISNULL(TSTOCKPOSITION.AVERAGECOST,0)
                END
        FROM   (
            SELECT  OUID, STOREID, ITEMID, SKUID,
                    ISNULL(SUM(
                        CASE STOCKPOSTTYPE
                            WHEN 0 THEN  POSTEDVALUE
                            WHEN 1 THEN -POSTEDVALUE
                        END
                    ), 0) AS POSTEDVALUE
            FROM    TSTOCKLEDGER
            WHERE   OBJECTTYPEID = @ObjectTypeId
                AND OBJECTID     = @ObjectId
                AND STOREID = CASE WHEN @IsStockTransfer = 0 THEN @ToStoreId ELSE STOREID END
            GROUP BY OUID, STOREID, ITEMID, SKUID
        ) agg
        WHERE   TSTOCKPOSITION.OUID    = agg.OUID
            AND TSTOCKPOSITION.ITEMID  = agg.ITEMID
            AND TSTOCKPOSITION.SKUID   = agg.SKUID
            AND TSTOCKPOSITION.STOREID = agg.STOREID";

    // ── Cost-affecting transaction — consolidated (cross-store) ──────────
    // When IsStorewiseValuation=1: single AverageCost across all stores.
    public const string APPLY_COST_AFFECTING_CONSOLIDATED = @"
        WITH agg AS (
            SELECT  OUID, ITEMID, SKUID,
                    ISNULL(SUM(
                        CASE STOCKPOSTTYPE
                            WHEN 0 THEN  POSTEDVALUE
                            WHEN 1 THEN -POSTEDVALUE
                        END
                    ), 0) AS POSTEDVALUE
            FROM    TSTOCKLEDGER
            WHERE   OBJECTTYPEID = @ObjectTypeId
                AND OBJECTID     = @ObjectId
            GROUP BY OUID, ITEMID, SKUID
        ),
        calc AS (
            SELECT  a.OUID, a.ITEMID, a.SKUID,
                    SUM(ISNULL(p.QUANTITY, 0))    AS TOTALQTY,
                    SUM(ISNULL(a.POSTEDVALUE,0) + ISNULL(p.AVERAGEVALUE,0)) AS TOTALVALUE,
                    CASE WHEN SUM(ISNULL(p.QUANTITY,0)) > 0
                        THEN ROUND(
                            SUM(ISNULL(a.POSTEDVALUE,0) + ISNULL(p.AVERAGEVALUE,0))
                            / SUM(ISNULL(p.QUANTITY,0)), 4)
                        ELSE MAX(ISNULL(p.AVERAGECOST,0))
                    END AS AVERAGECOST
            FROM    agg a
            JOIN    TSTOCKPOSITION p
                ON  p.OUID   = a.OUID
                AND p.ITEMID = a.ITEMID
                AND p.SKUID  = a.SKUID
            GROUP BY a.OUID, a.ITEMID, a.SKUID
        )
        UPDATE  TSTOCKPOSITION
        SET     AVERAGECOST  = ROUND(ISNULL(c.AVERAGECOST, 0), 4),
                AVERAGEVALUE = ROUND(ISNULL(c.AVERAGECOST, 0) * ISNULL(TSTOCKPOSITION.QUANTITY,0), 4)
        FROM    calc c
        WHERE   TSTOCKPOSITION.OUID   = c.OUID
            AND TSTOCKPOSITION.ITEMID = c.ITEMID
            AND TSTOCKPOSITION.SKUID  = c.SKUID";

    // ── Perpetual rerun — 3 steps in one batch (temp table scope safe) ────
    // Step 1: compute weighted average into #tmpavg.
    // Step 2: join to ledger rows for the target document → #tmpresolved.
    // Step 3: update TSTOCKLEDGER from #tmpresolved with overflow guard.
    // Step 4/5: drop temp tables (always, even on error, via cleanup call).
    //
    // @IsStorewiseValuation: 0=per-store, 1=consolidated (StoreId=-1)
    // @FromDate / @ToDate: the period being reprocessed
    // @DocumentId: the specific document being valued (-1 for batch)
    public const string PERPET_RERUN_STEP1_CALC_AVG = @"
        SELECT  stkledger.ITEMID,
                stkledger.SKUID,
                CASE WHEN @IsStorewiseValuation = 0 THEN stkledger.STOREID ELSE -1 END AS STOREID,
                ROUND(
                    SUM(CASE WHEN stkledger.STOCKPOSTTYPE = 0
                              THEN  stkledger.POSTEDVALUE
                              ELSE -stkledger.POSTEDVALUE END) /
                    NULLIF(SUM(CASE WHEN stkledger.STOCKPOSTTYPE = 0
                                    THEN  stkledger.ACTUALQUANTITY
                                    ELSE -stkledger.ACTUALQUANTITY END), 0)
                , 4) AS AVGCOST,
                ROUND(
                    SUM(CASE WHEN stkledger.STOCKPOSTTYPE = 0
                              THEN  stkledger.POSTEDVALUE * stkledger.GCCURRENCYCONVERSION
                              ELSE -stkledger.POSTEDVALUE * stkledger.GCCURRENCYCONVERSION END) /
                    NULLIF(SUM(CASE WHEN stkledger.STOCKPOSTTYPE = 0
                                    THEN  stkledger.ACTUALQUANTITY
                                    ELSE -stkledger.ACTUALQUANTITY END), 0)
                , 4) AS AVGCOSTGC
        INTO    #tmpavg
        FROM    TSTOCKLEDGER stkledger
        JOIN    MBIZTRANSACTIONTYPE biztype
            ON  biztype.BIZTRANSACTIONTYPEID = stkledger.BIZTRANSACTIONTYPEID
        JOIN    MBIZTRANSACTIONCLASS bizclass
            ON  bizclass.BIZTRANSACTIONCLASSID = biztype.BIZTRANSACTIONCLASSID
        JOIN    MITEM item
            ON  item.ITEMID = stkledger.ITEMID
        WHERE   stkledger.OUID = @OUID
            AND item.STOCKVALUATIONTYPE IN (0, 3)
            AND (
                (   stkledger.STOCKLEDGERDATE BETWEEN @FromDate AND @ToDate
                    AND stkledger.STOCKPOSTTYPE = 0
                    AND bizclass.STOCKVALUEAFFECTING = 0
                    AND bizclass.BIZTRANSACTIONCLASSID NOT IN (-1799999914, -1799999909)
                )
                OR
                (   stkledger.STOCKLEDGERDATE < @FromDate
                    AND stkledger.STOCKPOSTTYPE IN (0, 1)
                    AND bizclass.STOCKVALUEAFFECTING IN (0, 1)
                )
            )
        GROUP BY
                stkledger.ITEMID,
                stkledger.SKUID,
                CASE WHEN @IsStorewiseValuation = 0 THEN stkledger.STOREID ELSE -1 END
        HAVING  ROUND(
                    SUM(CASE WHEN stkledger.STOCKPOSTTYPE = 0
                              THEN  stkledger.POSTEDVALUE
                              ELSE -stkledger.POSTEDVALUE END) /
                    NULLIF(SUM(CASE WHEN stkledger.STOCKPOSTTYPE = 0
                                    THEN  stkledger.ACTUALQUANTITY
                                    ELSE -stkledger.ACTUALQUANTITY END), 0)
                , 4) <> 0";

    public const string PERPET_RERUN_STEP2_JOIN_TO_LEDGER = @"
        SELECT  stkledger.STOCKLEDGERID,
                tmp.AVGCOST,
                tmp.AVGCOSTGC
        INTO    #tmpresolved
        FROM    TSTOCKLEDGER stkledger
        JOIN    #tmpavg tmp
            ON  tmp.ITEMID = stkledger.ITEMID
            AND tmp.SKUID  = stkledger.SKUID
            AND tmp.STOREID = CASE WHEN @IsStorewiseValuation = 0
                                   THEN stkledger.STOREID
                                   ELSE tmp.STOREID END
        JOIN    MBIZTRANSACTIONTYPE biztype
            ON  biztype.BIZTRANSACTIONTYPEID = stkledger.BIZTRANSACTIONTYPEID
        JOIN    MBIZTRANSACTIONCLASS bizclass
            ON  bizclass.BIZTRANSACTIONCLASSID = biztype.BIZTRANSACTIONCLASSID
        WHERE   stkledger.OUID = @OUID
            AND stkledger.STOCKLEDGERDATE BETWEEN @FromDate AND @ToDate
            AND stkledger.OBJECTTYPEID = -1899997952
            AND stkledger.OBJECTID     = @DocumentId
            AND (
                bizclass.STOCKVALUEAFFECTING IN (1)
                OR (bizclass.BIZTRANSACTIONCLASSID = -1399999911 AND stkledger.STOCKPOSTTYPE = 1)
                OR bizclass.BIZTRANSACTIONCLASSID IN (-1399999943, -1399999942, -1399999941)
            )";

    public const string PERPET_RERUN_STEP3_UPDATE_LEDGER = @"
        UPDATE  TSTOCKLEDGER
        SET     POSTEDCOST   = tmp.AVGCOST,
                POSTEDVALUE  = CASE
                    WHEN tmp.AVGCOST * TSTOCKLEDGER.ACTUALQUANTITY <= 99999999999999.9999
                    THEN ROUND(tmp.AVGCOST * TSTOCKLEDGER.ACTUALQUANTITY, 4)
                    ELSE TSTOCKLEDGER.POSTEDVALUE
                END,
                MATERIALCOST = TSTOCKLEDGER.POSTEDCOST
                               - TSTOCKLEDGER.PROCESSCOST
                               - TSTOCKLEDGER.CHARGESCOST
                               + TSTOCKLEDGER.REVENUE,
                POSTEDCOSTGC  = tmp.AVGCOSTGC,
                POSTEDVALUEGC = ROUND(tmp.AVGCOSTGC * TSTOCKLEDGER.ACTUALQUANTITY, 4)
        FROM    TSTOCKLEDGER
        JOIN    #tmpresolved tmp
            ON  tmp.STOCKLEDGERID = TSTOCKLEDGER.STOCKLEDGERID";

    public const string PERPET_RERUN_DROP_TEMP_1 = "IF OBJECT_ID('TEMPDB..#tmpavg',      'U') IS NOT NULL DROP TABLE #tmpavg";
    public const string PERPET_RERUN_DROP_TEMP_2 = "IF OBJECT_ID('TEMPDB..#tmpresolved', 'U') IS NOT NULL DROP TABLE #tmpresolved";

    // ── Batch weighted average (period-based) ─────────────────────────────
    // Same 3-step logic but targets ALL documents in the period (no @DocumentId filter).
    // Used by the monthly/daily batch run.
    public const string BATCH_STEP1_CALC_AVG = @"
        SELECT  stkledger.ITEMID,
                stkledger.SKUID,
                CASE WHEN @IsStorewiseValuation = 0 THEN stkledger.STOREID ELSE -1 END AS STOREID,
                ROUND(
                    SUM(CASE WHEN stkledger.STOCKPOSTTYPE = 0
                              THEN  stkledger.POSTEDVALUE
                              ELSE -stkledger.POSTEDVALUE END) /
                    NULLIF(SUM(CASE WHEN stkledger.STOCKPOSTTYPE = 0
                                    THEN  stkledger.ACTUALQUANTITY
                                    ELSE -stkledger.ACTUALQUANTITY END), 0)
                , 4) AS AVGCOST,
                ROUND(
                    SUM(CASE WHEN stkledger.STOCKPOSTTYPE = 0
                              THEN  stkledger.POSTEDVALUE * stkledger.GCCURRENCYCONVERSION
                              ELSE -stkledger.POSTEDVALUE * stkledger.GCCURRENCYCONVERSION END) /
                    NULLIF(SUM(CASE WHEN stkledger.STOCKPOSTTYPE = 0
                                    THEN  stkledger.ACTUALQUANTITY
                                    ELSE -stkledger.ACTUALQUANTITY END), 0)
                , 4) AS AVGCOSTGC
        INTO    #tmpavg
        FROM    TSTOCKLEDGER stkledger
        JOIN    MBIZTRANSACTIONTYPE biztype
            ON  biztype.BIZTRANSACTIONTYPEID = stkledger.BIZTRANSACTIONTYPEID
        JOIN    MBIZTRANSACTIONCLASS bizclass
            ON  bizclass.BIZTRANSACTIONCLASSID = biztype.BIZTRANSACTIONCLASSID
        JOIN    MITEM item
            ON  item.ITEMID = stkledger.ITEMID
        WHERE   stkledger.OUID = @OUID
            AND item.STOCKVALUATIONTYPE IN (0, 3)
            AND (
                (   stkledger.STOCKLEDGERDATE BETWEEN @FromDate AND @ToDate
                    AND stkledger.STOCKPOSTTYPE = 0
                    AND bizclass.STOCKVALUEAFFECTING = 0
                    AND bizclass.BIZTRANSACTIONCLASSID NOT IN (-1799999914, -1799999909)
                )
                OR
                (   stkledger.STOCKLEDGERDATE < @FromDate
                    AND stkledger.STOCKPOSTTYPE IN (0, 1)
                    AND bizclass.STOCKVALUEAFFECTING IN (0, 1)
                )
            )
        GROUP BY
                stkledger.ITEMID,
                stkledger.SKUID,
                CASE WHEN @IsStorewiseValuation = 0 THEN stkledger.STOREID ELSE -1 END
        HAVING  ROUND(
                    SUM(CASE WHEN stkledger.STOCKPOSTTYPE = 0
                              THEN  stkledger.POSTEDVALUE
                              ELSE -stkledger.POSTEDVALUE END) /
                    NULLIF(SUM(CASE WHEN stkledger.STOCKPOSTTYPE = 0
                                    THEN  stkledger.ACTUALQUANTITY
                                    ELSE -stkledger.ACTUALQUANTITY END), 0)
                , 4) <> 0";

    public const string BATCH_STEP2_JOIN_TO_LEDGER = @"
        SELECT  stkledger.STOCKLEDGERID,
                tmp.AVGCOST,
                tmp.AVGCOSTGC
        INTO    #tmpresolved
        FROM    TSTOCKLEDGER stkledger
        JOIN    #tmpavg tmp
            ON  tmp.ITEMID  = stkledger.ITEMID
            AND tmp.SKUID   = stkledger.SKUID
            AND tmp.STOREID = CASE WHEN @IsStorewiseValuation = 0
                                   THEN stkledger.STOREID
                                   ELSE tmp.STOREID END
        JOIN    MBIZTRANSACTIONTYPE biztype
            ON  biztype.BIZTRANSACTIONTYPEID = stkledger.BIZTRANSACTIONTYPEID
        JOIN    MBIZTRANSACTIONCLASS bizclass
            ON  bizclass.BIZTRANSACTIONCLASSID = biztype.BIZTRANSACTIONCLASSID
        WHERE   stkledger.OUID = @OUID
            AND stkledger.STOCKLEDGERDATE BETWEEN @FromDate AND @ToDate
            AND (
                bizclass.STOCKVALUEAFFECTING IN (1)
                OR (bizclass.BIZTRANSACTIONCLASSID = -1399999911 AND stkledger.STOCKPOSTTYPE = 1)
                OR bizclass.BIZTRANSACTIONCLASSID IN (-1399999943, -1399999942, -1399999941)
            )";

    // Shared with perpetual rerun
    public const string BATCH_STEP3_UPDATE_LEDGER = PERPET_RERUN_STEP3_UPDATE_LEDGER;
}