namespace MMDAL.Query.Scheduling
{
    // Indexes required:
    //   MMACHINE: (WORKCENTERID, STATUS, TENANTID)
    //   MKEYCOMPONENT: (MACHINEID, PROCESSID, TENANTID)
    //   MKEYCOMPONENTDETAIL: (KEYCOMPONENTID, PATTERNSTATUS, TENANTID)
    public static class CandidateQB
    {
        // Active machines for a given workcenter
        public const string GET_MACHINES_FOR_WORKCENTER = @"
SELECT
    M.MACHINEID          AS MachineId,
    M.MACHINECODE        AS MachineCode,
    M.MACHINENAME        AS MachineName,
    M.WORKCENTERID       AS WorkCenterId,
    M.CAPACITY          AS MachineCapacity,
    M.TENANTID           AS TenantId
FROM  MMACHINE M
WHERE M.WORKCENTERID = @WorkCenterId
AND   M.STATUS       = 1
AND   M.TENANTID     = @TenantId
ORDER BY M.MACHINEID;";

        // Patterns compatible with a machine + process + item/SKU combination.
        // Joins MPATTERNDETAIL to filter by the item being scheduled and its SKU variant.
        // MPATTERNDETAIL.QUANTITY = cavities per shot for this item+SKU combination.
        // Only Approved patterns (PATTERNSTATUS=1) with remaining life (BALANCECOUNT > 0).
        // Index required: MPATTERNDETAIL (PATTERNID, ITEMID, SKUID)
        public const string GET_PATTERN_COMPATIBILITY = @"
SELECT
    KCD.PATTERNID             AS PatternId,
    PATT.PATTERNCODE          AS PatternCode,
    KCD.PATTERNSTATUS         AS PatternStatus,
    KCD.BALANCECOUNT          AS PatternBalanceCount,
    KC.MACHINEID              AS MachineId,
    KC.PROCESSID              AS ProcessId,
    PD.QUANTITY               AS QuantityPerShot
FROM  MKEYCOMPONENT       KC
INNER JOIN MKEYCOMPONENTDETAIL KCD ON KCD.KEYCOMPONENTID = KC.KEYCOMPONENTID
INNER JOIN MPATTERN            PATT ON PATT.PATTERNID     = KCD.PATTERNID
INNER JOIN MPATTERNDETAIL      PD   ON PD.PATTERNID       = KCD.PATTERNID
                                    AND PD.ITEMID          = @ItemId
                                    AND PD.SKUID           = @SkuId
WHERE KC.MACHINEID      = @MachineId
AND   KC.PROCESSID      = @ProcessId
AND   KCD.PATTERNSTATUS = 1
AND   KCD.BALANCECOUNT  > 0
AND   KC.TENANTID       = @TenantId
ORDER BY KCD.REPLACEDON DESC;";

        // Last committed pattern on a machine — reads from TRESOURCEPLAN (committed plans), not master config.
        // Returns PATTERNID (matches GET_PATTERN_COMPATIBILITY) so dict lookups in PatternCurrentMachine work correctly.
        // Current-run plans are in memory only; this only returns prior-run state, which is correct.
        public const string GET_LAST_MOUNTED_PATTERN = @"
SELECT TOP 1
    PATTERNID AS PatternId
FROM  TRESOURCEPLAN
WHERE MACHINEID  = @MachineId
AND   PATTERNID  IS NOT NULL
AND   TENANTID   = @TenantId
ORDER BY SCHEDULEDSTARTDATE DESC;";
    }
}