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

namespace MMDAL.Query.BatchNumberMapping
{
    // Indexes recommended:
    //   MITEM:              IX_MITEM_BATCHFLAGS (ISBATCHSTOCK, ISUNIQUEBATCH)
    //   TPRODUCTIONDETAIL:  IX_TPRODUCTIONDETAIL_PRODUCTION (PRODUCTIONID, ITEMID, SKUID)
    //   TPRODUCTIONITEMS:   IX_TPRODUCTIONITEMS_DETAIL (PRODUCTIONDETAILID, ITEMID, SKUID)
    //   TLOT:               IX_TLOT_ITEM_SKU (ITEMID, SKUID)
    //   TLOTDETAIL:         IX_TLOTDETAIL_LOT_STOCKPOSTTYPE (LOTID, STOCKPOSTTYPE)
    public static class BatchNumberMappingQB
    {
        // ── #1 Input item picklist for Batch Number Mapping ──────────────────
        // Ported from GB4 Batch.svc INPUT_ITEM_PICKLIST_IN_BATCH_MAPPING.
        // Input items are linked to a production via TPRODUCTIONITEMS (the
        // "input" side of a production, as opposed to TPRODUCTIONDETAIL which
        // holds the "output" side — see OUTPUT_ITEM_PICKLIST_IN_BATCH_MAPPING).
        //
        // NOTE: TPRODUCTIONITEMS could not be independently corroborated against
        // any existing GB5 QB file or docs/GB5-Schema-Registry.md — it does not
        // appear anywhere else in this repo. Ported as-is from the GB4 ground
        // truth query; flag for schema verification once gb5-schema MCP/live DB
        // access is available.
        //
        // Criteria (appended by BatchNumberMappingDAL.ApplyItemPicklistCriteria):
        //   ProductionId -> c.PRODUCTIONID (equality)
        //   Code         -> a.ITEMCODE     (LIKE)
        //   Name         -> a.ITEMNAME     (LIKE)
        public const string INPUT_ITEM_PICKLIST_IN_BATCH_MAPPING = @"
            SELECT
                a.ITEMID   AS ItemId,
                a.ITEMCODE AS ItemCode,
                a.ITEMNAME AS ItemName,
                e.SKUID    AS SKUId,
                e.SKUCODE  AS SKUCode,
                e.SKUNAME  AS SKUName
            FROM MITEM a, TPRODUCTIONDETAIL b, TPRODUCTION c, TPRODUCTIONITEMS d, MSKU e
            WHERE a.ITEMID = d.ITEMID
              AND c.PRODUCTIONID = b.PRODUCTIONID
              AND d.PRODUCTIONDETAILID = b.PRODUCTIONDETAILID
              AND e.SKUID = d.SKUID
              AND a.ISBATCHSTOCK = 0
              AND a.ISUNIQUEBATCH = 0";

        // ── #2 Output item picklist for Batch Number Mapping ─────────────────
        // Ported from GB4 Batch.svc OUTPUT_ITEM_PICKLIST_IN_BATCH_MAPPING.
        // Output items are the production's own output line (TPRODUCTIONDETAIL),
        // unlike the input side above which goes through TPRODUCTIONITEMS.
        //
        // Criteria (appended by BatchNumberMappingDAL.ApplyItemPicklistCriteria):
        //   ProductionId -> c.PRODUCTIONID (equality)
        //   Code         -> a.ITEMCODE     (LIKE)
        //   Name         -> a.ITEMNAME     (LIKE)
        public const string OUTPUT_ITEM_PICKLIST_IN_BATCH_MAPPING = @"
            SELECT
                a.ITEMID   AS ItemId,
                a.ITEMCODE AS ItemCode,
                a.ITEMNAME AS ItemName,
                d.SKUID    AS SKUId,
                d.SKUCODE  AS SKUCode,
                d.SKUNAME  AS SKUName
            FROM MITEM a, TPRODUCTIONDETAIL b, TPRODUCTION c, MSKU d
            WHERE a.ITEMID = b.ITEMID
              AND c.PRODUCTIONID = b.PRODUCTIONID
              AND d.SKUID = b.SKUID
              AND a.ISBATCHSTOCK = 0
              AND a.ISUNIQUEBATCH = 0";

        // ── #3 Lot picklist for Batch Number Mapping (paged) ──────────────────
        // Ported from GB4 Batch.svc NORMAL_LOT_NUMBER_PICKLIST_FOR_BATCH_MAPPING /
        // BatchNumberMappingDAL.GetLotSelectListForBatchNumber.
        //
        // GoodQuantity is a signed net-available quantity: TLOTDETAIL.STOCKPOSTTYPE = 0
        // rows add, anything else subtracts (matches the SUM(CASE ...) delta pattern
        // already used for TLOTDETAIL in MM\MMDAL\Query\StockValuation\FifoLotCostQB.cs).
        // Only lots with a net-positive available quantity are returned (HAVING > 0).
        //
        // The inner grouped query + outer ROW_NUMBER/BETWEEN paging wrapper follows the
        // same "WITH Paged... AS (...) SELECT ... WHERE (@firstnumber=-1 AND @maxresult=-1)
        // OR (RowNum BETWEEN @firstnumber AND @maxresult)" template used by
        // MM\MMDAL\Query\Item\ItemQB.cs (GET_SELECTLIST_ITEM_INNER / _OUTER).
        //
        // The "/*CRITERIA*/" marker is where BatchNumberMappingDAL.ApplyLotCriteria injects
        // optional, fully-parameterized AND-conditions before GROUP BY:
        //   LotNumber -> a.LOTNUMBER (LIKE)
        //   ItemId    -> a.ITEMID    (equality)
        //   SkuId     -> a.SKUID     (equality)
        public const string GET_LOT_SELECTLIST_FOR_BATCHMAPPING_SQL = @"
            WITH PagedLot AS (
                SELECT
                    a.LOTID     AS Id,
                    a.LOTNUMBER AS LotNumber,
                    b.PACKID    AS LotPicklistPackId,
                    b.LOTDETAILID AS LotDetailId,
                    a.SKUID     AS SkuId,
                    a.ITEMID    AS ItemId,
                    SUM(CASE WHEN b.STOCKPOSTTYPE = 0 THEN b.GOODQUANTITY ELSE b.GOODQUANTITY * -1 END) AS GoodQuantity,
                    ROW_NUMBER() OVER (ORDER BY a.LOTID) AS RowNum
                FROM TLOT a, TLOTDETAIL b
                WHERE a.LOTID = b.LOTID
                /*CRITERIA*/
                GROUP BY a.LOTID, a.ITEMID, a.SKUID, b.PACKID, b.LOTDETAILID, a.LOTNUMBER
                HAVING SUM(CASE WHEN b.STOCKPOSTTYPE = 0 THEN b.GOODQUANTITY ELSE b.GOODQUANTITY * -1 END) > 0
            )
            SELECT Id, LotNumber, LotPicklistPackId, LotDetailId, SkuId, ItemId, GoodQuantity
            FROM PagedLot
            WHERE (@firstnumber = -1 AND @maxresult = -1)
               OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_LOT_SELECTLIST_FOR_BATCHMAPPING_PG = @"
            WITH ""PagedLot"" AS (
                SELECT
                    a.LOTID     AS ""Id"",
                    a.LOTNUMBER AS ""LotNumber"",
                    b.PACKID    AS ""LotPicklistPackId"",
                    b.LOTDETAILID AS ""LotDetailId"",
                    a.SKUID     AS ""SkuId"",
                    a.ITEMID    AS ""ItemId"",
                    SUM(CASE WHEN b.STOCKPOSTTYPE = 0 THEN b.GOODQUANTITY ELSE b.GOODQUANTITY * -1 END) AS ""GoodQuantity"",
                    ROW_NUMBER() OVER (ORDER BY a.LOTID) AS ""RowNum""
                FROM TLOT a, TLOTDETAIL b
                WHERE a.LOTID = b.LOTID
                /*CRITERIA*/
                GROUP BY a.LOTID, a.ITEMID, a.SKUID, b.PACKID, b.LOTDETAILID, a.LOTNUMBER
                HAVING SUM(CASE WHEN b.STOCKPOSTTYPE = 0 THEN b.GOODQUANTITY ELSE b.GOODQUANTITY * -1 END) > 0
            )
            SELECT ""Id"", ""LotNumber"", ""LotPicklistPackId"", ""LotDetailId"", ""SkuId"", ""ItemId"", ""GoodQuantity""
            FROM ""PagedLot""
            WHERE (:firstnumber = -1 AND :maxresult = -1)
               OR (""RowNum"" BETWEEN :firstnumber AND :maxresult);";
    }
}