using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MMDAL.Query.Lot
{
    public class LotQB
    {
        public const string GET_SELECTLIST_LOT = @"
            WITH PagedLot AS (
                SELECT
                    L.LOTID               AS Id,
                    L.LOTNUMBER           AS LotNumber,
                    L.LOTDATE             AS LotDate,
                    ROW_NUMBER() OVER (ORDER BY L.LOTID) AS RowNum
                FROM 
                    TLOT L
                WHERE 
                    L.STATUS = 1
            )
            SELECT
                Id,
                LotNumber,
                LotDate
            FROM PagedLot
            WHERE
                (@firstnumber = -1 AND @maxresult = -1)
                OR (RowNum BETWEEN @firstnumber AND @maxresult)";

        // ── INSERT LOT ────────────────────────────────────────────────
        // Inserts a single row into TLOT.
        // LotId and LotNumber are pre-assigned by BLL via AutoNumber.
        // PROCESSID / LOTTYPEID / MRPRATE / ITEMPOSTEDCOST / ALLOCATIONID
        // are optional — passed as -1 / 0 when not applicable.
        // ─────────────────────────────────────────────────────────────
        public const string INSERT_LOT = @"
    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,
        @lotexpirydate,
        @remarks,
        @status,
        @version,
        @createdbyid,
        @createdon,
        @modifiedbyid,
        @modifiedon,
        @processid,
        @lottypeid,
        @mrprate,
        @itempostedcost
    )";

        public const string GET_SELECTLIST_LOT_RESERVATION = @"SELECT
    -- Lot Header
    L.LOTID                         AS LotId,
    L.LOTNUMBER                     AS LotNumber,
    L.LOTDATE                       AS LotDate,
    L.LOTEXPIRYDATE                 AS LotExpiryDate,

    L.ITEMID                        AS ItemId,
    I.ITEMCODE                      AS ItemCode,
    I.ITEMNAME                      AS ItemName,

    L.SKUID                         AS SkuId,
    S.SKUCODE                       AS SkuCode,
    S.SKUNAME                       AS SkuName,

    L.PROCESSID                     AS ProcessId,
    L.ALLOCATIONID                  AS AllocationId,

    -- Lot Detail
    LD.LOTDETAILID                  AS LotDetailId,
    LD.SLNO                         AS SlNo,

    LD.BIZTRANSACTIONTYPEID         AS BizTransactionTypeId,
    MB.BIZTRANSACTIONTYPECODE       AS BizTransactionTypeCode,
    MB.BIZTRANSACTIONTYPENAME       AS BizTransactionTypeName,

    LD.OBJECTHEADERTYPEID           AS ObjectHeaderTypeId,
    LD.OBJECTHEADERID               AS ObjectHeaderId,
    LD.OBJECTTYPEID                 AS ObjectTypeId,
    LD.OBJECTID                     AS ObjectId,

    LD.REFERENCENUMBER              AS ReferenceNumber,
    LD.REFERENCEDATE                AS ReferenceDate,

    LD.STOCKLEDGERNUMBER            AS StockLedgerNumber,
    LD.STOCKLEDGERDATE              AS StockLedgerDate,

    LD.OUID                         AS OUId,
    OU.ORGANIZATIONUNITNAME         AS OUName,

    LD.STOREID                      AS StoreId,
    ST.STORECODE                    AS StoreCode,
    ST.STORENAME                    AS StoreName,

    LD.BINID                        AS BinId,

    -- Quantities
    LD.QUANTITY                     AS TotalQuantity,
    LD.GOODQUANTITY                 AS GoodQuantity,
    LD.REJECTEDQUANTITY             AS RejectedQuantity,
    LD.REWORKQUANTITY               AS ReworkQuantity,
    LD.OTHERQUANTITY                AS OtherQuantity,
    LD.MARKEDGOODQUANTITY           AS MarkedGoodQuantity,
    LD.PLANNEDQUANTITY              AS PlannedQuantity,

    -- Reservation
    ISNULL(R.RESERVEDQUANTITY,0)    AS ReservedQuantity,
    ISNULL(R.USEDQUANTITY,0)        AS UsedQuantity,
    ISNULL(R.BALANCEQUANTITY,0)     AS BalanceQuantity,

    -- Available Quantity
    (
        LD.GOODQUANTITY
        - ISNULL(R.RESERVEDQUANTITY,0)
    )                               AS AvailableQuantity,

    ROW_NUMBER() OVER
    (
        ORDER BY
            L.LOTDATE DESC,
            L.LOTNUMBER,
            LD.SLNO
    )                               AS RowNum

FROM TLOT L

INNER JOIN TLOTDETAIL LD
    ON LD.LOTID = L.LOTID

LEFT JOIN TRESERVATION R
    ON R.LOTID = L.LOTID
   AND R.ITEMID = L.ITEMID
   AND ISNULL(R.SKUID,-1)=ISNULL(L.SKUID,-1)
   AND R.STATUS = 1

-- TLOT/TLOTDETAIL have no TENANTID column — tenant is scoped via this
-- mandatory MITEM join instead (same pattern used in QMS's Matrix module).
INNER JOIN MITEM I
    ON I.ITEMID = L.ITEMID
   AND I.TENANTID = @TenantId

LEFT JOIN MSKU S
    ON S.SKUID = L.SKUID

LEFT JOIN MBIZTRANSACTIONTYPE MB
    ON MB.BIZTRANSACTIONTYPEID = LD.BIZTRANSACTIONTYPEID

LEFT JOIN MORGANIZATIONUNIT OU
    ON OU.OUID = LD.OUID

LEFT JOIN MSTORE ST
    ON ST.STOREID = LD.STOREID

WHERE
    L.STATUS = 1
{DYNAMIC_WHERE};";

        // GB4 parity: mms/Lot.svc/LotDetail/SelectList (default Type=0, FifoQty=1 path) —
        // LotBLL.GetLotSelectListForQuantity -> LotDAL.GetLotSelectListForQuantity
        // -> LotQueryBuilder.GET_LOTSELECTLIST_NEW.
        // Available-quantity lot picklist: GoodQuantity is sign-flipped by STOCKPOSTTYPE
        // (0=inbound, 1=issue), netted against MARKEDGOODQUANTITY reservations.
        // Filters (all optional, -1/NULL = not applied): ItemId, SkuId, ProcessId, LotTypeId,
        // LotNumber, ObjectHeaderId, ObjectId, StoreId. IsStock=1 skips the >0 balance filter.
        // Note: does not port GB4's FifoQty=0 cursor-based FIFO allocation variant — that logic
        // is procedural (DECLARE CURSOR) and needs a dedicated rewrite, not a straight SQL port.
        // Required index: TLOT(ITEMID, SKUID, LOTNUMBER), TLOTDETAIL(LOTID, OUID, STOCKPOSTTYPE)
        public const string GET_LOTSELECTLIST_NEW = @"
            SELECT
                a.LOTID                                                              AS LotPicklistId,
                a.LOTNUMBER                                                          AS LotPicklistNumber,
                b.PACKID                                                             AS LotPicklistPackId,
                a.SKUID                                                              AS SkuId,
                a.ITEMID                                                             AS ItemId,
                a.MRPRATE                                                            AS MRPRate,
                a.ITEMPOSTEDCOST                                                     AS LotPostedCost,
                a.LOTDATE                                                            AS LotPicklistDate,
                a.LOTEXPIRYDATE                                                      AS LotPicklistExpiryDate,
                SUM(CASE WHEN b.STOCKPOSTTYPE = 0 THEN b.GOODQUANTITY ELSE b.GOODQUANTITY * -1 END) AS GoodQuantity,
                SUM(ISNULL(b.MARKEDGOODQUANTITY, 0))                                 AS MarkedGoodQuantity,
                (SUM(CASE WHEN b.STOCKPOSTTYPE = 0 THEN b.GOODQUANTITY ELSE b.GOODQUANTITY * -1 END)
                    - SUM(ISNULL(b.MARKEDGOODQUANTITY, 0)))                          AS AvailableLotQuantity
            FROM TLOT a
            JOIN TLOTDETAIL b ON a.LOTID = b.LOTID
            WHERE b.OUID = @ouid
            AND   b.STOCKPOSTTYPE IN (0, 1)
            AND (@itemid           = -1   OR a.ITEMID           = @itemid)
            AND (@skuid            = -1   OR a.SKUID            = @skuid)
            AND (@processid        = -1   OR a.PROCESSID        = @processid)
            AND (@lottypeid        = -1   OR a.LOTTYPEID        = @lottypeid)
            AND (@lotnumber IS NULL OR a.LOTNUMBER = @lotnumber)
            AND (@objectheaderid   = -1   OR b.OBJECTHEADERID   = @objectheaderid)
            AND (@objectid         = -1   OR b.OBJECTID         = @objectid)
            AND (@storeid          = -1   OR b.STOREID          = @storeid)
            GROUP BY a.LOTID, a.ITEMID, a.SKUID, b.PACKID, a.LOTNUMBER, a.MRPRATE, a.ITEMPOSTEDCOST, a.LOTDATE, a.LOTEXPIRYDATE
            HAVING (@isstock = 1 OR SUM(CASE WHEN b.STOCKPOSTTYPE = 0 THEN b.GOODQUANTITY ELSE b.GOODQUANTITY * -1 END) > 0);";

        // GB4 parity: Type=1 (LoadingSheet) variant of the same LotDetail select list —
        // LotQueryBuilder.GET_LOTSELECTLIST_NEW_FORLOADINGSHEET. Adds OUId to the result shape.
        public const string GET_LOTSELECTLIST_NEW_FORLOADINGSHEET = @"
            SELECT
                b.OUID                                                               AS OUId,
                a.ITEMID                                                             AS ItemId,
                a.SKUID                                                              AS SkuId,
                b.PACKID                                                             AS LotPicklistPackId,
                a.LOTNUMBER                                                          AS LotPicklistNumber,
                a.LOTID                                                              AS LotPicklistId,
                SUM(CASE WHEN b.STOCKPOSTTYPE = 0 THEN b.GOODQUANTITY ELSE b.GOODQUANTITY * -1 END) AS GoodQuantity,
                SUM(ISNULL(b.MARKEDGOODQUANTITY, 0))                                 AS MarkedGoodQuantity,
                (SUM(CASE WHEN b.STOCKPOSTTYPE = 0 THEN b.GOODQUANTITY ELSE b.GOODQUANTITY * -1 END)
                    - SUM(ISNULL(b.MARKEDGOODQUANTITY, 0)))                          AS AvailableLotQuantity
            FROM TLOT a
            JOIN TLOTDETAIL b ON a.LOTID = b.LOTID
            WHERE b.OUID = @ouid
            AND   b.STOCKPOSTTYPE IN (0, 1)
            AND (@itemid           = -1   OR a.ITEMID           = @itemid)
            AND (@skuid            = -1   OR a.SKUID            = @skuid)
            AND (@processid        = -1   OR a.PROCESSID        = @processid)
            AND (@lottypeid        = -1   OR a.LOTTYPEID        = @lottypeid)
            AND (@lotnumber IS NULL OR a.LOTNUMBER = @lotnumber)
            AND (@objectheaderid   = -1   OR b.OBJECTHEADERID   = @objectheaderid)
            AND (@objectid         = -1   OR b.OBJECTID         = @objectid)
            AND (@storeid          = -1   OR b.STOREID          = @storeid)
            GROUP BY b.OUID, a.ITEMID, a.SKUID, b.PACKID, a.LOTNUMBER, a.LOTID
            HAVING SUM(CASE WHEN b.STOCKPOSTTYPE = 0 THEN b.GOODQUANTITY ELSE b.GOODQUANTITY * -1 END) > 0;";

        // GB4 parity: Type=2 (DeliveryFeedback) variant — LotQueryBuilder.GET_LOTSELECTLIST_NEW_FORDELIVERYFEEDBACK.
        // Adds OtherQuantity and excludes lots already fully consumed (B.OTHERQUANTITY=0).
        public const string GET_LOTSELECTLIST_NEW_FORDELIVERYFEEDBACK = @"
            SELECT
                b.OUID                                                               AS OUId,
                a.ITEMID                                                             AS ItemId,
                a.SKUID                                                              AS SkuId,
                b.PACKID                                                             AS LotPicklistPackId,
                a.LOTNUMBER                                                          AS LotPicklistNumber,
                a.LOTID                                                              AS LotPicklistId,
                SUM(CASE WHEN b.STOCKPOSTTYPE = 0 THEN b.GOODQUANTITY ELSE b.GOODQUANTITY * -1 END) AS GoodQuantity,
                SUM(ISNULL(b.MARKEDGOODQUANTITY, 0))                                 AS MarkedGoodQuantity,
                (SUM(CASE WHEN b.STOCKPOSTTYPE = 0 THEN b.GOODQUANTITY ELSE b.GOODQUANTITY * -1 END)
                    - SUM(ISNULL(b.MARKEDGOODQUANTITY, 0)))                          AS AvailableLotQuantity,
                SUM(ISNULL(b.OTHERQUANTITY, 0))                                      AS OtherQuantity
            FROM TLOT a
            JOIN TLOTDETAIL b ON a.LOTID = b.LOTID
            WHERE b.OUID = @ouid
            AND   b.STOCKPOSTTYPE IN (0, 1)
            AND   b.OTHERQUANTITY = 0
            AND (@itemid           = -1   OR a.ITEMID           = @itemid)
            AND (@skuid            = -1   OR a.SKUID            = @skuid)
            AND (@processid        = -1   OR a.PROCESSID        = @processid)
            AND (@lottypeid        = -1   OR a.LOTTYPEID        = @lottypeid)
            AND (@lotnumber IS NULL OR a.LOTNUMBER = @lotnumber)
            AND (@objectheaderid   = -1   OR b.OBJECTHEADERID   = @objectheaderid)
            AND (@objectid         = -1   OR b.OBJECTID         = @objectid)
            AND (@storeid          = -1   OR b.STOREID          = @storeid)
            GROUP BY b.OUID, a.ITEMID, a.SKUID, b.PACKID, a.LOTNUMBER, a.LOTID
            HAVING SUM(CASE WHEN b.STOCKPOSTTYPE = 0 THEN b.GOODQUANTITY ELSE b.GOODQUANTITY * -1 END) > 0;";
    }
}