namespace MMDAL.Query.Scheduling
{
    // All queries use StreamAsync<T> — demand sets can exceed 5,000 rows.
    // Indexes required:
    //   TINDENTDETAIL: (SCHEDULESTATUS, WORKCENTERID, STATUS, TENANTID)
    //   TINDENT:       (ISNPD, STATUS, OUID)
    //   TNESTINGPLAN:  (SCHEDULESTATUS, WORKCENTERID, TENANTID)
    public static class SchedulingDemandQB
    {
        // Open production indents with routing detail and workcenter info.
        // DurationMinutes priority: Subcontract → ManualOverride → BOR(Setup + CycleTime×Qty) → RoutingFixedLeadTime
        // BOR join: OUTER APPLY picks the first machine-resource row (TYPE=1) for the routing step.
        // Workcenter-level BOR rows (no ROUTINGDETAILID) are not picked up here — intentional.
        public const string STREAM_OPEN_INDENTS = @"
SELECT
    ID.INDENTDETAILID          AS SourceDetailId,
    I.INDENTID                 AS SourceDocumentId,
    ID.ITEMID                  AS ItemId,
    ID.SKUID                   AS SkuId,
    ID.ROUTINGDETAILID         AS RoutingVersionDetailId,
    I.WORKCENTERID             AS WorkCenterId,
    I.PROCESSID                AS ProcessId,
    ID.PLANNEDQUANTITY    AS Quantity,

    ID.PLANNEDQUANTITY*CASE
        WHEN RVD.EXECUTIONMODE = 1
            THEN RVD.SUBCONTRACTLEADTIMEDAYS * 480

        ELSE COALESCE(
            ID.MANUALDURATIONMINUTES,

            CASE WHEN BOR.CYCLETIME IS NOT NULL THEN
                -- Setup minutes
                (CASE BOR.TIMEUOM
                    WHEN 0 THEN BOR.SETUPTIME / 60.0
                    WHEN 1 THEN BOR.SETUPTIME * 1.0
                    WHEN 2 THEN BOR.SETUPTIME * 60.0
                    WHEN 3 THEN BOR.SETUPTIME * 1440.0
                    ELSE        BOR.SETUPTIME
                END)
                +
                -- CycleTime × Quantity (converted to minutes)
                (CASE BOR.TIMEUOM
                    WHEN 0 THEN BOR.CYCLETIME / 60.0
                    WHEN 1 THEN BOR.CYCLETIME * 1.0
                    WHEN 2 THEN BOR.CYCLETIME * 60.0
                    WHEN 3 THEN BOR.CYCLETIME * 1440.0
                    ELSE        BOR.CYCLETIME
                END) * ID.PLANNEDQUANTITY

            ELSE
                -- No BOR row: fall back to routing fixed lead time
                CASE RVD.LEADTIMEUOM
                    WHEN 0 THEN RVD.FIXEDLEADTIME / 60.0
                    WHEN 1 THEN RVD.FIXEDLEADTIME * 1.0
                    WHEN 2 THEN RVD.FIXEDLEADTIME * 60.0
                    WHEN 3 THEN RVD.FIXEDLEADTIME * 1440.0
                    WHEN 4 THEN RVD.FIXEDLEADTIME * 10080.0
                    ELSE        RVD.FIXEDLEADTIME * 60.0
                END
            END
        )
    END AS DurationMinutes,

    RVD.ISINTERRUPTIBLE        AS IsInterruptible,
    RVD.MINIMUMRUNMINUTES      AS MinimumRunMinutes,
    RVD.MAXWAITMINUTES         AS MaxWaitMinutes,
    RVD.EXECUTIONMODE          AS ExecutionMode,
    RVD.BATCHCONTINUITY        AS BatchContinuity,
    CASE WHEN RVD.LOCATIONTYPE = 5 THEN 0 ELSE 1 END AS IsMonitorOnly,
    CASE WHEN I.ISNPD = 1 THEN 2 ELSE 0 END          AS PlanNature,
    RVD.MONITORGROUP           AS MonitorGroup,
    RVD.ISGROUPOUTPUTSTEP      AS IsGroupOutputStep,
    COALESCE(ID.MACHINEID_OVERRIDE, NULL)             AS FixedMachineId,
    I.EXPECTEDCOMPLETIONDATE   AS Deadline,
    GETUTCDATE()               AS EarliestStart,
    COALESCE(RVD.PREDECESSOR, '') AS PredecessorStr,
    1                          AS Priority,
    CASE
        WHEN RVD.EXECUTIONMODE = 1              THEN 2
        WHEN ID.MANUALDURATIONMINUTES IS NOT NULL THEN 1
        ELSE 0
    END                        AS DurationSource,
    ISNULL(UM.NOOFDECIMALS, 2)    AS QuantityDecimalPlaces,
    ISNULL(PROCC.ISPATTERNREQUIRED, 1) AS IsPatternRequired,
    ISNULL(RVD.SPLITS, 1)              AS Splits
FROM  TINDENTDETAIL ID
INNER JOIN TINDENT I
    ON I.INDENTID = ID.INDENTID
LEFT JOIN MROUTINGVERSIONDETAIL RVD
    ON RVD.ROUTINGDETAILID = ID.ROUTINGDETAILID
LEFT JOIN MPROCESS PROCC
    ON PROCC.PROCESSID = I.PROCESSID
OUTER APPLY (
    SELECT TOP 1 CYCLETIME, SETUPTIME, TIMEUOM
    FROM   MBILLOFRESOURCE
    WHERE  ROUTINGDETAILID = RVD.ROUTINGDETAILID
    AND    TYPE            = 4
    ORDER  BY SLNO
) BOR
LEFT OUTER JOIN MITEM ITEM ON ITEM.ITEMID=ID.ITEMID
LEFT JOIN MUOM UM ON ITEM.STOCKUOMID = UM.UOMID
WHERE ID.SCHEDULESTATUS = 0
AND   ID.RELEASESTATUS  <> 3
AND   I.STATUS          <> 3
AND   ID.INDENTID<>-1
-- and  ID.INDENTDETAILID in(-1499999629,-1499999589)
AND   I.OUID             = @WorkOUId
AND   I.EXPECTEDFIRSTDELIVERYDATE BETWEEN @HorizonFrom AND @HorizonTo
ORDER BY I.EXPECTEDFIRSTDELIVERYDATE, ID.SLNO;";

        // Open nesting plans (SCHEDULESTATUS=0)
        public const string STREAM_OPEN_NESTING_PLANS = @"
SELECT
    NP.NESTINGPLANID           AS NestingPlanId,
    NP.CMSID                   AS CmsId,
    NP.WORKCENTERID            AS WorkCenterId,
   -- COALESCE(NP.NESTINGPLANNESTINGTIME + NP.SETUPTIME + NP.CYCLETIME, 0) AS DurationMinutes,
     COALESCE(  NP.SETUPTIME + NP.CYCLETIME, 0) AS DurationMinutes,
    GETUTCDATE()               AS EarliestStart,
    GETUTCDATE() + 30          AS Deadline,
    NP.TENANTID                AS TenantId
FROM  TNESTINGPLAN NP
WHERE NP.SCHEDULESTATUS = 0
AND   NP.WORKCENTERID IS NOT NULL
AND   NP.TENANTID       = @TenantId
ORDER BY NP.NESTINGPLANID;";
    }
}