namespace MMDAL.Query.PartLevel
{
    // Index: UQ_TPARTPOSITION (ITEMID, PARTDIMID, STOREID, LOTID, TENANTID)
    public static class PartPositionQB
    {
        public const string GET_PARTPOSITION = @"
            SELECT pp.PARTPOSITIONID AS PartPositionId,
                   pp.ITEMID         AS ItemId,
                   i.ITEMCODE        AS ItemCode,
                   i.ITEMNAME        AS ItemName,
                   pp.PARTDIMID      AS PartDimId,
                   CONCAT_WS(' / ', dim.D1, dim.D2, dim.D3) AS DimensionLabel,
                   pp.STOREID        AS StoreId,
                   st.STORENAME      AS StoreName,
                   pp.LOTID          AS LotId,
                   pp.AVAILABLE      AS Available,
                   pp.RESERVED       AS Reserved,
                   pp.ISSUED         AS Issued,
                   pp.TENANTID
            FROM   TPARTPOSITION pp
            JOIN   MITEM i  ON i.ITEMID  = pp.ITEMID
            LEFT   JOIN MPARTDIMENSION dim ON dim.PARTDIMID = pp.PARTDIMID
            LEFT   JOIN MSTORE st ON st.STOREID = pp.STOREID
            WHERE  pp.PARTPOSITIONID = @PartPositionId";

        public const string GET_PARTPOSITION_BY_KEY = @"
            SELECT pp.POSITIONID AS PartPositionId,
                   pp.AVAILABLEQTY      AS Available,
                   pp.RESERVEDQTY       AS Reserved,
                   pp.ISSUEDQTY         AS Issued
            FROM   TPARTPOSITION pp
            WHERE  pp.ITEMID    = @ItemId
            AND    ISNULL(pp.PARTDIMID, 0) = ISNULL(@PartDimId, 0)
            AND    pp.STOREID   = @StoreId
            AND    ISNULL(pp.LOTID, 0)    = ISNULL(@LotId, 0)";

        public const string GET_PARTPOSITION_LIST = @"
            SELECT pp.PARTPOSITIONID AS PartPositionId,
                   pp.ITEMID         AS ItemId,
                   i.ITEMCODE        AS ItemCode,
                   i.ITEMNAME        AS ItemName,
                   pp.PARTDIMID      AS PartDimId,
                   CONCAT_WS(' / ', dim.D1, dim.D2, dim.D3) AS DimensionLabel,
                   pp.STOREID        AS StoreId,
                   st.STORENAME      AS StoreName,
                   pp.LOTID          AS LotId,
                   pp.AVAILABLE      AS Available,
                   pp.RESERVED       AS Reserved,
                   pp.ISSUED         AS Issued,
                   pp.TENANTID
            FROM   TPARTPOSITION pp
            JOIN   MITEM i  ON i.ITEMID  = pp.ITEMID  AND i.TENANTID = pp.TENANTID
            LEFT   JOIN MPARTDIMENSION dim ON dim.PARTDIMID = pp.PARTDIMID
            LEFT   JOIN MSTORE st ON st.STOREID = pp.STOREID AND st.TENANTID = pp.TENANTID
            WHERE  pp.TENANTID = @TenantId
            AND    pp.STOREID  = @StoreId
            ORDER BY i.ITEMCODE, pp.PARTDIMID, pp.LOTID
            OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";

        public const string GET_PART_SHORTAGE = @"
            SELECT pd.ITEMID              AS ItemId,
                   i.ITEMCODE             AS ItemCode,
                   i.ITEMNAME             AS ItemName,
                   pd.PARTDIMID           AS PartDimId,
                   CONCAT_WS(' / ', dim.D1, dim.D2, dim.D3) AS DimensionLabel,
                   SUM(pd.REQUIREDQTY - ISNULL(alloc.AllocatedQty, 0)) AS RequiredQty,
                   ISNULL(SUM(pp.AVAILABLE), 0)                        AS AvailableQty,
                   GREATEST(SUM(pd.REQUIREDQTY - ISNULL(alloc.AllocatedQty, 0))
                             - ISNULL(SUM(pp.AVAILABLE), 0), 0)        AS ShortageQty,
                   CASE WHEN pd.PRIORITY = 0 THEN 999 ELSE pd.PRIORITY END AS Priority,
                   pd.TENANTID
            FROM   TPARTDETAIL pd
            JOIN   MITEM i ON i.ITEMID = pd.ITEMID AND i.TENANTID = pd.TENANTID
            LEFT   JOIN MPARTDIMENSION dim ON dim.PARTDIMID = pd.PARTDIMID
            LEFT   JOIN (
                SELECT pa.PARTDETAILID, SUM(pa.ALLOCATEDQTY) AS AllocatedQty
                FROM   TPARTALLOCATION pa
                WHERE  pa.STATUS IN (0,1) AND pa.TENANTID = @TenantId
                GROUP  BY pa.PARTDETAILID
            ) alloc ON alloc.PARTDETAILID = pd.PARTDETAILID
            LEFT   JOIN TPARTPOSITION pp ON pp.ITEMID   = pd.ITEMID
                                         AND ISNULL(pp.PARTDIMID, 0) = ISNULL(pd.PARTDIMID, 0)
                                         AND pp.TENANTID = pd.TENANTID
            WHERE  pd.STATUS   IN (0, 1)
            AND    pd.TENANTID  = @TenantId
            GROUP BY pd.ITEMID, i.ITEMCODE, i.ITEMNAME,
                     pd.PARTDIMID, dim.D1, dim.D2, dim.D3, pd.PRIORITY, pd.TENANTID
            HAVING SUM(pd.REQUIREDQTY - ISNULL(alloc.AllocatedQty, 0)) > ISNULL(SUM(pp.AVAILABLE), 0)
            ORDER BY CASE WHEN pd.PRIORITY = 0 THEN 999 ELSE pd.PRIORITY END ASC";

        public const string UPDATE_PARTPOSITION_ALLOCATE = @"
            UPDATE TPARTPOSITION
            SET    AVAILABLE = AVAILABLE - @Quantity,
                   RESERVED  = RESERVED  + @Quantity
            WHERE  PARTPOSITIONID = @PartPositionId
            AND    AVAILABLE      >= @Quantity
            AND    TENANTID       = @TenantId";

        public const string UPDATE_PARTPOSITION_ISSUE = @"
            UPDATE TPARTPOSITION
            SET    RESERVED = RESERVED - @Quantity,
                   ISSUED   = ISSUED   + @Quantity
            WHERE  PARTPOSITIONID = @PartPositionId
            AND    RESERVED       >= @Quantity
            AND    TENANTID       = @TenantId";

        public const string UPDATE_PARTPOSITION_CONSUME = @"
            UPDATE TPARTPOSITION
            SET    ISSUED = ISSUED - @Quantity
            WHERE  PARTPOSITIONID = @PartPositionId
            AND    ISSUED         >= @Quantity
            AND    TENANTID       = @TenantId";

        public const string UPDATE_PARTPOSITION_RELEASE = @"
            UPDATE TPARTPOSITION
            SET    RESERVED   = RESERVED   - @Quantity,
                   AVAILABLE  = AVAILABLE  + @Quantity
            WHERE  PARTPOSITIONID = @PartPositionId
            AND    RESERVED       >= @Quantity
            AND    TENANTID       = @TenantId";

        public const string INSERT_PARTPOSITION = @"
            INSERT INTO TPARTPOSITION (
                PARTPOSITIONID, ITEMID, PARTDIMID, STOREID, LOTID,
                AVAILABLEQTY, RESERVEDQTY, ISSUEDQTY)
            VALUES (
                @PartPositionId, @ItemId, @PartDimId, @StoreId, @LotId,
                @Available, 0, 0)";

        public const string INITIALISE_PARTPOSITION = @"
            MERGE TPARTPOSITION WITH (HOLDLOCK) AS target
            USING (
                SELECT sl.ITEMID, sl.PARTDIMID, sl.STOREID, sl.LOTID,
                       SUM(sl.QUANTITY) AS AVAILABLE, @TenantId AS TENANTID
                FROM   TSTOCKLEDGER sl
                WHERE  sl.TENANTID = @TenantId
                AND    (@ItemId IS NULL OR sl.ITEMID = @ItemId)
                AND    sl.STOREID   = @StoreId
                GROUP BY sl.ITEMID, sl.PARTDIMID, sl.STOREID, sl.LOTID
            ) AS src ON (   target.ITEMID    = src.ITEMID
                        AND ISNULL(target.PARTDIMID, 0) = ISNULL(src.PARTDIMID, 0)
                        AND target.STOREID   = src.STOREID
                        AND ISNULL(target.LOTID, 0) = ISNULL(src.LOTID, 0)
                        AND target.TENANTID  = src.TENANTID)
            WHEN MATCHED AND @ResetToZero = 1 THEN
                UPDATE SET AVAILABLE = src.AVAILABLE, RESERVED = 0, ISSUED = 0
            WHEN NOT MATCHED THEN
                INSERT (PARTPOSITIONID, ITEMID, PARTDIMID, STOREID, LOTID,
                        AVAILABLE, RESERVED, ISSUED, TENANTID)
                VALUES (@NewPartPositionId, src.ITEMID, src.PARTDIMID, src.STOREID, src.LOTID,
                        src.AVAILABLE, 0, 0, src.TENANTID);";
    }
}