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

namespace MMDAL.Query.LotDetail
{
    /// <summary>
    /// SQL for the LotDetail select list (lot stock picklist).
    ///
    /// Migrated from GB4 <c>LotQueryBuilder.GET_LOTSELECTLIST_NEW</c>, served by
    /// <c>POST /mms/Lot.svc/LotDetail/SelectList</c>. Returns one row per lot with the
    /// net on-hand quantity aggregated from TLOTDETAIL movements, so a picklist only
    /// offers lots that still carry stock.
    ///
    /// GB4's single DAL method also dispatched to three other query variants driven by
    /// magic flags in the criteria body (type=1 Loading Sheet, type=2 Delivery Feedback,
    /// fifoqty=0 cursor-based FIFO allocation). Those are intentionally NOT migrated here —
    /// if they are ever needed in GB5 they belong in their own endpoints, not behind flags.
    ///
    /// REQUIRED INDEX: TLOTDETAIL(LOTID, OUID, STOCKPOSTTYPE) INCLUDE (GOODQUANTITY, MARKEDGOODQUANTITY, PACKID)
    /// </summary>
    public class LotDetailQB
    {
        // ── SELECT + FROM + WHERE ─────────────────────────────────────────────
        // Deliberately ends at the WHERE clause: LotDetailDAL appends the CriteriaDTO
        // conditions here and then concatenates GET_SELECTLIST_LOTDETAIL_GROUPBY.
        // Criteria must land BEFORE the GROUP BY so filtering happens on base rows
        // rather than on the aggregate (this is why the shared {DYNAMIC_WHERE} helper,
        // which wraps the query in a derived table, cannot be used for this query).
        //
        // @ouid          — OU scope. Defaults to LoginDTO.WorkOUId, overridable by an
        //                  `ouid` / `OUId` criteria attribute (GB4 parity).
        // STOCKPOSTTYPE  — 0 = inward (adds stock), 1 = outward (subtracts stock).
        //
        // TLOTINFO exposes only LOTID, KRM, PRODUCTCODE, DGSETSLNO, ITEMID, SKUID, STOREID
        // as standard columns (see GB4 LotQueryBuilder.INSERT_LOT_INFO). GB4's version of
        // this query also selected ENGINE / ALTERNATER / CPNL, but those are per-client
        // add-on columns provisioned through MADDONFIELDS + INSERT_LOT_INFO_DYNAMIC, so
        // they are omitted here — selecting them would break on a standard database.
        public const string GET_SELECTLIST_LOTDETAIL_SELECT = @"
            SELECT
                L.LOTID                                     AS LotPicklistId,
                L.LOTNUMBER                                 AS LotPicklistNumber,
                LD.PACKID                                   AS LotPicklistPackId,
                L.SKUID                                     AS SkuId,
                L.ITEMID                                    AS ItemId,
                L.MRPRATE                                   AS MRPRate,
                L.ITEMPOSTEDCOST                            AS LotPostedCost,
                L.LOTDATE                                   AS LotPicklistDate,
                L.LOTEXPIRYDATE                             AS LotPicklistExpiryDate,

                SUM(CASE WHEN LD.STOCKPOSTTYPE = 0
                         THEN LD.GOODQUANTITY
                         ELSE LD.GOODQUANTITY * -1 END)     AS GoodQuantity,

                SUM(ISNULL(LD.MARKEDGOODQUANTITY, 0))       AS MarkedGoodQuantity,

                ( ISNULL(SUM(CASE WHEN LD.STOCKPOSTTYPE = 0
                                  THEN LD.GOODQUANTITY
                                  ELSE LD.GOODQUANTITY * -1 END), 0)
                  - SUM(ISNULL(LD.MARKEDGOODQUANTITY, 0)) ) AS AvailableLotQuantity,

                MAX(ISNULL(LI.PRODUCTCODE, ''))             AS ProductCode,
                MAX(ISNULL(LI.DGSETSLNO,   ''))             AS DGSETSLNO,
                MAX(ISNULL(LI.KRM,         ''))             AS KRM,

                ROW_NUMBER() OVER (ORDER BY L.LOTID)        AS RowNum

            FROM TLOT L
            LEFT  JOIN TLOTINFO   LI ON LI.LOTID = L.LOTID
            INNER JOIN TLOTDETAIL LD ON LD.LOTID = L.LOTID
            WHERE LD.OUID = @ouid
              AND LD.STOCKPOSTTYPE IN (0, 1)";

        // Appended after the criteria conditions. The HAVING clause is what hides
        // fully-consumed lots — GB4 applied the same filter.
        public const string GET_SELECTLIST_LOTDETAIL_GROUPBY = @"
            GROUP BY L.LOTID, L.ITEMID, L.SKUID, LD.PACKID, L.LOTNUMBER,
                     L.MRPRATE, L.ITEMPOSTEDCOST, L.LOTDATE, L.LOTEXPIRYDATE
            HAVING SUM(CASE WHEN LD.STOCKPOSTTYPE = 0
                            THEN LD.GOODQUANTITY
                            ELSE LD.GOODQUANTITY * -1 END) > 0";

        // Paging wrapper. @firstnumber/@maxresult are absolute row bounds (not page
        // size) — the convention shared with LotQB.GET_SELECTLIST_LOT and
        // ItemQB.GET_SELECTLIST_ITEM_OUTER. -1/-1 returns every row, which is how the
        // endpoint's Total count call requests an unpaged result.
        public const string GET_SELECTLIST_LOTDETAIL_OUTER = @"
            SELECT
                LotPicklistId,
                LotPicklistNumber,
                LotPicklistPackId,
                SkuId,
                ItemId,
                MRPRate,
                LotPostedCost,
                LotPicklistDate,
                LotPicklistExpiryDate,
                GoodQuantity,
                MarkedGoodQuantity,
                AvailableLotQuantity,
                ProductCode,
                DGSETSLNO,
                KRM
            FROM PagedLotDetail
            WHERE (@firstnumber = -1 AND @maxresult = -1)
               OR (RowNum BETWEEN @firstnumber AND @maxresult);";
    }
}