namespace MMDAL.Query.Production
{
    // Feeds the Production Entry screen's WO+stage grid (all 4 modes — Accordion/JIT/Process/
    // Supervisor Entry — share this one queue). Reads TPOSTEDINDENTDETAIL directly: GB5 already
    // maintains one row per (WorkOrder x RoutingStage) occurrence there via stored-procedure/
    // trigger posting, including the pre-computed AVAILABLETOWORK/MMSENT/MMRECEIVED figures —
    // this endpoint does not recompute availability or subcontract status itself.
    //
    // Index notes: IX_TPOSTEDINDENTDETAIL_OUID_WORKCENTER (OUID, WORKCENTERID, RELEASESTATUS)
    // would cover the WHERE clause below; confirm/add if missing.
    public static class ProductionEntryQueueQB
    {
        public const string GET_PRODUCTION_ENTRY_QUEUE_SQL = @"
            SELECT
                pdtl.INDENTID                 AS IndentId,
                pdtl.COMBINEINDENTID          AS CombineIndentId,
                h.INDENTNUMBER                AS WoNo,
                pdtl.ITEMID                   AS ItemId,
                ISNULL(i.ITEMCODE, '')        AS ItemCode,
                ISNULL(i.ITEMNAME, '')        AS ItemName,
                pdtl.SKUID                    AS SkuId,
                ISNULL(sk.SKUCODE, '')        AS SkuCode,
                -1                            AS UomId, -- TODO: source from TINDENTDETAIL.UOMID once confirmed
                pdtl.INDENTDETAILID           AS IndentDetailId,
                ISNULL(id.NESTINGPLANID, -1)  AS NestingPlanId,
                pdtl.ROUTINGDETAILID          AS RoutingDetailId,
                ISNULL(rv.ROUTINGID, -1)      AS RoutingId,
                ISNULL(r.ROUTINGCODE, '')     AS RoutingCode,
                ISNULL(r.ROUTINGNAME, '')     AS RoutingName,
                pdtl.ROUTINGDETAILSLNO        AS StageSlNo,
                ISNULL(rvd.STAGE, '')         AS Stage,
                ISNULL(rvd.STEP, '')          AS Step,
                pdtl.PROCESSID                AS ProcessId,
                ISNULL(p.PROCESSCODE, '')     AS ProcessCode,
                ISNULL(p.PROCESSNAME, '')     AS ProcessName,
                ISNULL(rvd.EXECUTIONMODE, 0)  AS ExecutionMode,
                pdtl.WORKCENTERID             AS WorkCenterId,
                ISNULL(wc.WORKCENTERCODE, '') AS WorkCenterCode,
                ISNULL(wc.WORKCENTERNAME, '') AS WorkCenterName,
                pdtl.PLANNED                  AS Planned,
                pdtl.PRODUCED                 AS Produced,
                pdtl.GOODQUANTITY             AS GoodQuantity,
                pdtl.REJECTEDQUANTITY         AS RejectedQuantity,
                pdtl.REWORKQUANTITY           AS ReworkQuantity,
                pdtl.OTHERQUANTITY            AS OtherQuantity,
                pdtl.AVAILABLETOWORK          AS AvailableToWork,
                pdtl.MMSENT                   AS MmSent,
                pdtl.MMRECEIVED               AS MmReceived,
                pdtl.WIPSTOCK                 AS WipStock,
                pdtl.RELEASESTATUS            AS ReleaseStatus,
                pdtl.ISLASTPROCESS            AS IsLastProcess,
                ISNULL(latestpd.ProductionDetailId, 0) AS ProductionDetailId,
                ISNULL(stopc.StoppageCount, 0)         AS StoppageCount
            FROM   TPOSTEDINDENTDETAIL pdtl
            JOIN   TINDENT               h   ON h.INDENTID       = pdtl.INDENTID
            LEFT   JOIN MITEM            i   ON i.ITEMID         = pdtl.ITEMID
            LEFT   JOIN MSKU             sk  ON sk.SKUID         = pdtl.SKUID
            LEFT   JOIN TINDENTDETAIL    id  ON id.INDENTDETAILID = pdtl.INDENTDETAILID
            LEFT   JOIN MROUTINGVERSIONDETAIL rvd ON rvd.ROUTINGDETAILID = pdtl.ROUTINGDETAILID
            LEFT   JOIN MROUTINGVERSION  rv  ON rv.ROUTINGVERSIONID = rvd.ROUTINGVERSIONID
            LEFT   JOIN MROUTING         r   ON r.ROUTINGID      = rv.ROUTINGID
            LEFT   JOIN MPROCESS         p   ON p.PROCESSID      = pdtl.PROCESSID
            LEFT   JOIN MWORKCENTER      wc  ON wc.WORKCENTERID  = pdtl.WORKCENTERID
            OUTER  APPLY (
                       SELECT TOP 1 pd.PRODUCTIONDETAILID AS ProductionDetailId
                       FROM   TPRODUCTIONDETAIL pd
                       WHERE  pd.INDENTDETAILID = pdtl.INDENTDETAILID
                       ORDER  BY pd.PRODUCTIONDETAILID DESC
                   ) latestpd
            LEFT   JOIN (
                       SELECT ps.PRODUCTIONDETAILID, COUNT(*) AS StoppageCount
                       FROM   TPRODUCTIONSTOPPAGE ps
                       GROUP  BY ps.PRODUCTIONDETAILID
                   ) stopc ON stopc.PRODUCTIONDETAILID = latestpd.ProductionDetailId
            WHERE  (@OUId = -1 OR pdtl.OUID = @OUId)
            AND    (@WorkCenterId = -1 OR pdtl.WORKCENTERID = @WorkCenterId)
            AND    (@ShowCompleted = 1 OR pdtl.RELEASESTATUS NOT IN (4, 5))
            AND    (@NestingPlanId = -1 OR id.NESTINGPLANID = @NestingPlanId)
            ORDER  BY pdtl.COMBINEINDENTID, pdtl.ROUTINGDETAILSLNO;";

        public const string GET_PRODUCTION_ENTRY_QUEUE_PG = @"
            SELECT
                pdtl.INDENTID                 AS ""IndentId"",
                pdtl.COMBINEINDENTID          AS ""CombineIndentId"",
                h.INDENTNUMBER                AS ""WoNo"",
                pdtl.ITEMID                   AS ""ItemId"",
                COALESCE(i.ITEMCODE, '')      AS ""ItemCode"",
                COALESCE(i.ITEMNAME, '')      AS ""ItemName"",
                pdtl.SKUID                    AS ""SkuId"",
                COALESCE(sk.SKUCODE, '')      AS ""SkuCode"",
                -1                            AS ""UomId"",
                pdtl.INDENTDETAILID           AS ""IndentDetailId"",
                COALESCE(id.NESTINGPLANID, -1) AS ""NestingPlanId"",
                pdtl.ROUTINGDETAILID          AS ""RoutingDetailId"",
                COALESCE(rv.ROUTINGID, -1)    AS ""RoutingId"",
                COALESCE(r.ROUTINGCODE, '')   AS ""RoutingCode"",
                COALESCE(r.ROUTINGNAME, '')   AS ""RoutingName"",
                pdtl.ROUTINGDETAILSLNO        AS ""StageSlNo"",
                COALESCE(rvd.STAGE, '')       AS ""Stage"",
                COALESCE(rvd.STEP, '')        AS ""Step"",
                pdtl.PROCESSID                AS ""ProcessId"",
                COALESCE(p.PROCESSCODE, '')   AS ""ProcessCode"",
                COALESCE(p.PROCESSNAME, '')   AS ""ProcessName"",
                COALESCE(rvd.EXECUTIONMODE, 0) AS ""ExecutionMode"",
                pdtl.WORKCENTERID             AS ""WorkCenterId"",
                COALESCE(wc.WORKCENTERCODE, '') AS ""WorkCenterCode"",
                COALESCE(wc.WORKCENTERNAME, '') AS ""WorkCenterName"",
                pdtl.PLANNED                  AS ""Planned"",
                pdtl.PRODUCED                 AS ""Produced"",
                pdtl.GOODQUANTITY             AS ""GoodQuantity"",
                pdtl.REJECTEDQUANTITY         AS ""RejectedQuantity"",
                pdtl.REWORKQUANTITY           AS ""ReworkQuantity"",
                pdtl.OTHERQUANTITY            AS ""OtherQuantity"",
                pdtl.AVAILABLETOWORK          AS ""AvailableToWork"",
                pdtl.MMSENT                   AS ""MmSent"",
                pdtl.MMRECEIVED               AS ""MmReceived"",
                pdtl.WIPSTOCK                 AS ""WipStock"",
                pdtl.RELEASESTATUS            AS ""ReleaseStatus"",
                pdtl.ISLASTPROCESS            AS ""IsLastProcess"",
                COALESCE(latestpd.ProductionDetailId, 0) AS ""ProductionDetailId"",
                COALESCE(stopc.StoppageCount, 0)         AS ""StoppageCount""
            FROM   TPOSTEDINDENTDETAIL pdtl
            JOIN   TINDENT               h   ON h.INDENTID       = pdtl.INDENTID
            LEFT   JOIN MITEM            i   ON i.ITEMID         = pdtl.ITEMID
            LEFT   JOIN MSKU             sk  ON sk.SKUID         = pdtl.SKUID
            LEFT   JOIN TINDENTDETAIL    id  ON id.INDENTDETAILID = pdtl.INDENTDETAILID
            LEFT   JOIN MROUTINGVERSIONDETAIL rvd ON rvd.ROUTINGDETAILID = pdtl.ROUTINGDETAILID
            LEFT   JOIN MROUTINGVERSION  rv  ON rv.ROUTINGVERSIONID = rvd.ROUTINGVERSIONID
            LEFT   JOIN MROUTING         r   ON r.ROUTINGID      = rv.ROUTINGID
            LEFT   JOIN MPROCESS         p   ON p.PROCESSID      = pdtl.PROCESSID
            LEFT   JOIN MWORKCENTER      wc  ON wc.WORKCENTERID  = pdtl.WORKCENTERID
            LEFT   JOIN LATERAL (
                       SELECT pd.PRODUCTIONDETAILID AS ProductionDetailId
                       FROM   TPRODUCTIONDETAIL pd
                       WHERE  pd.INDENTDETAILID = pdtl.INDENTDETAILID
                       ORDER  BY pd.PRODUCTIONDETAILID DESC
                       LIMIT  1
                   ) latestpd ON true
            LEFT   JOIN (
                       SELECT ps.PRODUCTIONDETAILID, COUNT(*) AS StoppageCount
                       FROM   TPRODUCTIONSTOPPAGE ps
                       GROUP  BY ps.PRODUCTIONDETAILID
                   ) stopc ON stopc.PRODUCTIONDETAILID = latestpd.ProductionDetailId
            WHERE  (:OUId = -1 OR pdtl.OUID = :OUId)
            AND    (:WorkCenterId = -1 OR pdtl.WORKCENTERID = :WorkCenterId)
            AND    (:ShowCompleted = true OR pdtl.RELEASESTATUS NOT IN (4, 5))
            AND    (:NestingPlanId = -1 OR id.NESTINGPLANID = :NestingPlanId)
            ORDER  BY pdtl.COMBINEINDENTID, pdtl.ROUTINGDETAILSLNO;";
    }
}