namespace MMDAL.Query.StockValuation;

/// <summary>
/// SQL for batch stock valuation: run management, period locking,
/// make-item cost application, position updates, and cost workings.
///
/// Required indexes:
///   TSTOCKVALUATIONRUN: (OUID, STATUS)
///   TSTOCKBALANCESNAPSHOT: (OUID, ITEMID, SKUID, STOREID, SNAPSHOTDATE DESC) WHERE ISCERTIFIED=1
///   MPRODUCTCOST: (OUID, ITEMID, SKUID, TYPE, FROMDATE)
/// </summary>
public static class StockValuationQB
{
    // ── Valuation run management ──────────────────────────────────────────

    public const string INSERT_VALUATION_RUN = @"
        INSERT INTO TSTOCKVALUATIONRUN
            (STOCKVALUATIONRUNID, OUID, RUNTYPE, PERIODFROM, PERIODTO,
             STOREID, ITEMID, ITEMCATEGORYID, ITEMSUBCATEGORYID,
             VALUATIONMETHODS, STATUS, STARTEDAT, CREATEDBYID, CREATEDON)
        VALUES
            (@StockValuationRunId, @OUID, @RunType, @PeriodFrom, @PeriodTo,
             @StoreId, @ItemId, @ItemCategoryId, @ItemSubCategoryId,
             @ValuationMethods, 1, GETUTCDATE(), @CreatedById, GETUTCDATE())";

    public const string UPDATE_RUN_DONE = @"
        UPDATE TSTOCKVALUATIONRUN
        SET    STATUS      = 2,
               COMPLETEDAT = GETUTCDATE()
        WHERE  STOCKVALUATIONRUNID = @StockValuationRunId";

    public const string UPDATE_RUN_FAILED = @"
        UPDATE TSTOCKVALUATIONRUN
        SET    STATUS       = 3,
               COMPLETEDAT  = GETUTCDATE(),
               ERRORMESSAGE = @ErrorMessage
        WHERE  STOCKVALUATIONRUNID = @StockValuationRunId";

    /// <summary>Health check: mark stuck Running records older than 2 hours as Failed.</summary>
    public const string MARK_STUCK_RUNS_FAILED = @"
        UPDATE TSTOCKVALUATIONRUN
        SET    STATUS       = 3,
               COMPLETEDAT  = GETUTCDATE(),
               ERRORMESSAGE = 'Timed out — process did not complete within 2 hours.'
        WHERE  STATUS     = 1
            AND STARTEDAT < DATEADD(HOUR, -2, GETUTCDATE())";

    /// <summary>Idempotency check: is a run already active for this OU/type/period?</summary>
    public const string CHECK_RUN_ACTIVE = @"
        SELECT COUNT(1)
        FROM   TSTOCKVALUATIONRUN
        WHERE  OUID       = @OUID
            AND RUNTYPE   = @RunType
            AND PERIODFROM = @PeriodFrom
            AND PERIODTO   = @PeriodTo
            AND STATUS    IN (0, 1)";

    public const string GET_VALUATION_RUNS = @"
        SELECT  r.STOCKVALUATIONRUNID  AS StockValuationRunId,
                r.OUID,
                r.RUNTYPE              AS RunType,
                r.PERIODFROM           AS PeriodFrom,
                r.PERIODTO             AS PeriodTo,
                r.STOREID              AS StoreId,
                ISNULL(s.STORENAME,'') AS StoreName,
                r.ITEMID               AS ItemId,
                ISNULL(i.ITEMNAME,'')  AS ItemName,
                r.VALUATIONMETHODS     AS ValuationMethods,
                r.STATUS               AS Status,
                r.STARTEDAT            AS StartedAt,
                r.COMPLETEDAT          AS CompletedAt,
                r.ERRORMESSAGE         AS ErrorMessage,
                r.CREATEDBYID          AS CreatedById,
                r.CREATEDON            AS CreatedOn,
                DATEDIFF(SECOND, r.STARTEDAT, ISNULL(r.COMPLETEDAT, GETUTCDATE()))
                                       AS DurationSeconds
        FROM    TSTOCKVALUATIONRUN r
        LEFT JOIN MSTORE      s ON s.STOREID = r.STOREID
        LEFT JOIN MITEM       i ON i.ITEMID  = r.ITEMID
        WHERE   r.OUID = @OUID
            AND (@PeriodFrom IS NULL OR r.PERIODFROM >= @PeriodFrom)
            AND (@PeriodTo   IS NULL OR r.PERIODTO   <= @PeriodTo)
        ORDER BY r.CREATEDON DESC";

    // ── Period lock check ─────────────────────────────────────────────────

    public const string CHECK_PERIOD_LOCKED = @"
        SELECT COUNT(a.BIZTRANSACTIONTYPEID)
        FROM   MBIZTRANSACTIONTYPE a
        JOIN   MLOCKSETTING        b ON b.BIZTRANSACTIONCLASSID = a.BIZTRANSACTIONCLASSID
                                    AND b.OUID = a.OUID
        JOIN   TDAYLOCK            lk ON lk.OUID   = a.OUID
                                      AND lk.PERIODID = a.PERIODID
        WHERE  a.OUID          = @OUID
            AND a.PERIODID     = @PeriodId
            AND b.MONTHLOCKID <> -1
            AND b.MONTHLOCKID  = lk.LOCKID
            AND lk.CLOSEMONTHDATE = @CheckDate
            AND a.STOCKPOSTTYPE   = 0";

    // ── Make item standard cost application ───────────────────────────────
    // Replaces UPDATE_STOCK_VALUATION_SQL_QUERY_TO_STOKCLEDGER_FOR_MAKE_ITEM.
    // Sources TotalCost from MPRODUCTCOST (Type=0=Make, from a standard MCOSTANALYSIS).
    // Excludes items excluded from std cost (IsExcludeFromStandardCost handled at
    // the TMMDETAIL / ProductCost level, not here — stock ledger always gets updated).
    public const string APPLY_MAKE_ITEM_COST = @"
        UPDATE  TSTOCKLEDGER
        SET     POSTEDCOST    = prodcost.TOTALCOST,
                POSTEDVALUE   = CASE
                    WHEN prodcost.TOTALCOST * TSTOCKLEDGER.GOODQUANTITY <= 99999999999999.9999
                    THEN ROUND(prodcost.TOTALCOST * TSTOCKLEDGER.GOODQUANTITY, 4)
                    ELSE TSTOCKLEDGER.POSTEDVALUE
                END,
                MATERIALCOST  = prodcost.MATERIALCOST,
                PROCESSCOST   = prodcost.PROCESSCOST,
                CHARGESCOST   = prodcost.CHARGESCOST
        FROM   (
            SELECT  pc.ITEMID,
                    pc.SKUID,
                    pc.STOREID,
                    pc.TOTALCOST,
                    pc.MATERIALCOST,
                    pc.PROCESSCOST,
                    pc.CHARGESCOST
            FROM    MPRODUCTCOST pc
            WHERE   pc.OUID       = @OUID
                AND pc.TYPE       = 0          -- Make
                AND pc.FROMDATE  >= @FromDate
                AND pc.TODATE     = @ToDate
        ) prodcost
        JOIN    MBIZTRANSACTIONTYPE biztype
            ON  biztype.BIZTRANSACTIONTYPEID = TSTOCKLEDGER.BIZTRANSACTIONTYPEID
        JOIN    MBIZTRANSACTIONCLASS bizclass
            ON  bizclass.BIZTRANSACTIONCLASSID = biztype.BIZTRANSACTIONCLASSID
        JOIN    MITEM item
            ON  item.ITEMID = TSTOCKLEDGER.ITEMID
        WHERE   TSTOCKLEDGER.STOCKLEDGERDATE BETWEEN @FromDate AND @ToDate
            AND prodcost.ITEMID = TSTOCKLEDGER.ITEMID
            AND prodcost.SKUID  = TSTOCKLEDGER.SKUID
            AND TSTOCKLEDGER.OUID = @OUID
            AND item.ISMAKEORBUY  = 0          -- Make items only
            AND bizclass.STOCKVALUEAFFECTING = 1";

    // ── TSTOCKPOSITION update after batch ────────────────────────────────
    // Recalculates AverageCost and AverageValue from TSTOCKLEDGER after
    // the period's PostedCost/PostedValue have been updated.
    public const string SYNC_STOCK_POSITION_FROM_LEDGER = @"
        WITH ledger_totals AS (
            SELECT  stkl.OUID,
                    CASE WHEN @IsStorewiseValuation = 0 THEN stkl.STOREID ELSE -1 END AS STOREID,
                    stkl.ITEMID,
                    stkl.SKUID,
                    SUM(CASE WHEN stkl.STOCKPOSTTYPE = 0
                              THEN  stkl.ACTUALQUANTITY
                              ELSE -stkl.ACTUALQUANTITY END)   AS QTY,
                    SUM(CASE WHEN stkl.STOCKPOSTTYPE = 0
                              THEN  stkl.POSTEDVALUE
                              ELSE -stkl.POSTEDVALUE END)      AS VALUE
            FROM    TSTOCKLEDGER stkl
            JOIN    MBIZTRANSACTIONTYPE bt  ON bt.BIZTRANSACTIONTYPEID   = stkl.BIZTRANSACTIONTYPEID
            JOIN    MBIZTRANSACTIONCLASS bc ON bc.BIZTRANSACTIONCLASSID  = bt.BIZTRANSACTIONCLASSID
            WHERE   stkl.OUID = @OUID
                AND bc.STOCKVALUEAFFECTING IN (0,1)
                AND stkl.STOCKLEDGERDATE <= @ToDate
            GROUP BY
                    stkl.OUID,
                    CASE WHEN @IsStorewiseValuation = 0 THEN stkl.STOREID ELSE -1 END,
                    stkl.ITEMID, stkl.SKUID
        )
        UPDATE  TSTOCKPOSITION
        SET     AVERAGEVALUE = ROUND(lt.VALUE, 4),
                AVERAGECOST  = CASE WHEN lt.QTY <> 0
                                    THEN ROUND(lt.VALUE / lt.QTY, 4)
                                    ELSE TSTOCKPOSITION.AVERAGECOST END
        FROM    ledger_totals lt
        WHERE   TSTOCKPOSITION.OUID    = lt.OUID
            AND TSTOCKPOSITION.ITEMID  = lt.ITEMID
            AND TSTOCKPOSITION.SKUID   = lt.SKUID
            AND TSTOCKPOSITION.STOREID = lt.STOREID";

    // ── Snapshot management ───────────────────────────────────────────────

    public const string INSERT_BALANCE_SNAPSHOT = @"
        INSERT INTO TSTOCKBALANCESNAPSHOT
            (SNAPSHOTID, OUID, STOREID, ITEMID, SKUID, SNAPSHOTDATE,
             CLOSINGQTY, CLOSINGVALUE, CLOSINGCOST, VALUATIONMETHOD, ISCERTIFIED, CREATEDON)
        SELECT
            @BaseSnapshotId + ROW_NUMBER() OVER (ORDER BY STOREID, ITEMID, SKUID),
            OUID, STOREID, ITEMID, SKUID,
            @SnapshotDate,
            QUANTITY,
            ROUND(AVERAGEVALUE, 4),
            ROUND(AVERAGECOST,  4),
            @ValuationMethod,
            0,
            GETUTCDATE()
        FROM    TSTOCKPOSITION
        WHERE   OUID = @OUID
            AND QUANTITY <> 0";

    public const string CERTIFY_SNAPSHOT = @"
        UPDATE TSTOCKBALANCESNAPSHOT
        SET    ISCERTIFIED = 1
        WHERE  OUID         = @OUID
            AND SNAPSHOTDATE = @SnapshotDate";

    /// <summary>
    /// Find the last certified snapshot before a given date.
    /// Used as opening balance for recosting runs.
    /// </summary>
    public const string GET_LAST_CERTIFIED_SNAPSHOT = @"
        SELECT  SNAPSHOTID       AS SnapshotId,
                OUID,
                STOREID          AS StoreId,
                ITEMID           AS ItemId,
                SKUID            AS SKUId,
                SNAPSHOTDATE     AS SnapshotDate,
                CLOSINGQTY       AS ClosingQty,
                CLOSINGVALUE     AS ClosingValue,
                CLOSINGCOST      AS ClosingCost,
                VALUATIONMETHOD  AS ValuationMethod,
                ISCERTIFIED      AS IsCertified,
                CREATEDON        AS CreatedOn
        FROM    TSTOCKBALANCESNAPSHOT
        WHERE   OUID         = @OUID
            AND ITEMID       = @ItemId
            AND SKUID        = @SKUId
            AND STOREID      = @StoreId
            AND ISCERTIFIED  = 1
            AND SNAPSHOTDATE < @BeforeDate
        ORDER BY SNAPSHOTDATE DESC";

    // ── Cost workings — weighted average ─────────────────────────────────

    public const string GET_COST_WORKINGS_WA = @"
        SELECT  stkl.STOCKLEDGERID,
                stkl.STOCKLEDGERDATE   AS Date,
                ISNULL(mmh.DOCUMENTNUMBER, '') AS DocNumber,
                btc.BIZTRANSACTIONCLASSNAME     AS TxnType,
                stkl.STOCKPOSTTYPE,
                CASE WHEN stkl.STOCKPOSTTYPE = 0
                     THEN  stkl.ACTUALQUANTITY
                     ELSE -stkl.ACTUALQUANTITY END AS Qty,
                stkl.POSTEDCOST        AS UnitCost,
                CASE WHEN stkl.STOCKPOSTTYPE = 0
                     THEN  stkl.POSTEDVALUE
                     ELSE -stkl.POSTEDVALUE END    AS Value
        FROM    TSTOCKLEDGER stkl
        JOIN    MBIZTRANSACTIONTYPE bt   ON bt.BIZTRANSACTIONTYPEID  = stkl.BIZTRANSACTIONTYPEID
        JOIN    MBIZTRANSACTIONCLASS btc ON btc.BIZTRANSACTIONCLASSID = bt.BIZTRANSACTIONCLASSID
        LEFT JOIN TMMHEAD mmh            ON mmh.DOCUMENTID = stkl.OBJECTID
                                        AND stkl.OBJECTTYPEID = -1899997952
        WHERE   stkl.OUID    = @OUID
            AND stkl.ITEMID  = @ItemId
            AND stkl.SKUID   = @SKUId
            AND stkl.STOREID = @StoreId
            AND stkl.STOCKLEDGERDATE BETWEEN @FromDate AND @ToDate
            AND btc.STOCKVALUEAFFECTING IN (0, 1)
        ORDER BY stkl.STOCKLEDGERDATE, stkl.STOCKLEDGERID";

    // ── Make item BOM cost breakdown (for cost workings) ─────────────────

    public const string GET_MAKE_ITEM_BOM_COST_BREAKDOWN = @"
        SELECT  pc.PRODUCTCOSTID,
                pc.ITEMID,
                i.ITEMCODE,
                i.ITEMNAME,
                pc.SKUID,
                pc.QUANTITY,
                pc.MATERIALCOST,
                pc.MATERIALPROCESSCOST,
                pc.MATERIALCHARGESCOST,
                pc.MATERIALINHOUSEPROCESSCOST,
                pc.MATERIALSUBCONTRACTPROCESSCOST,
                pc.PROCESSCOST,
                pc.INHOUSEPROCESSCOST,
                pc.SUBCONTRACTPROCESSCOST,
                pc.CHARGESCOST,
                pc.TOTALCOST,
                ca.CODE  AS CostAnalysisCode,
                ca.PERIODFROM,
                ca.PERIODTO
        FROM    MPRODUCTCOST pc
        JOIN    MITEM        i  ON i.ITEMID           = pc.ITEMID
        JOIN    MCOSTANALYSIS ca ON ca.COSTANALYSISID = pc.COSTANALYSISID
        WHERE   pc.OUID             = @OUID
            AND pc.ITEMID           = @ItemId
            AND pc.SKUID            = @SKUId
            AND pc.TYPE             = 0     -- Make / Standard
            AND pc.FROMDATE        <= @AsOfDate
            AND pc.TODATE          >= @AsOfDate
        ORDER BY ca.PERIODFROM DESC";
}