namespace MMDAL.Query.InspectionParamSetEngine
{
    // Read-only resolution over MINSPECTIONTYPEPARAMETERSET — most-specific-wins with
    // wildcard sentinel columns (PARTYID/ITEMID, -1 = "any"), same mechanism as QMS's
    // MCHECKLISTAPPLICABILITY/ChecklistEngine. Requires index IX_MITPS_RESOLVE.
    public static class InspectionParamSetEngineQB
    {
        public const string GET_MATCHING_RULES = @"
            SELECT
                ITP.INSPECTIONTYPEPARAMETERSETID AS InspectionTypeParameterSetId,
                ITP.PARAMETERSETID               AS ParameterSetId,
                PS.PARAMETERSETCODE              AS ParameterSetCode,
                PS.PARAMETERSETNAME              AS ParameterSetName,
                ITP.ISREQUIRED                   AS IsRequired,
                ITP.SLNO                         AS SlNo,
                ITP.PRIORITY                      AS Priority,
                (CASE WHEN ITP.PARTYID <> -1 THEN 2 ELSE 0 END
               + CASE WHEN ITP.ITEMID  <> -1 THEN 1 ELSE 0 END) AS MatchedSpecificity
            FROM MINSPECTIONTYPEPARAMETERSET ITP
            INNER JOIN MPARAMETERSET PS ON PS.PARAMETERSETID = ITP.PARAMETERSETID AND PS.STATUS = 1
            WHERE ITP.STATUS = 1
              AND ITP.INSPECTIONTYPEID = @InspectionTypeId
              AND (ITP.EFFECTIVEFROM IS NULL OR ITP.EFFECTIVEFROM <= @AsOfDate)
              AND (ITP.EFFECTIVETO   IS NULL OR ITP.EFFECTIVETO   >= @AsOfDate)
              AND (ITP.PARTYID = @PartyId OR ITP.PARTYID = -1)
              AND (ITP.ITEMID  = @ItemId  OR ITP.ITEMID  = -1)
            ORDER BY MatchedSpecificity DESC, ITP.PRIORITY DESC, ITP.SLNO;";

        // Ultimate fallback — only reached when MINSPECTIONTYPEPARAMETERSET has zero
        // rows at all for the InspectionTypeId (not even a default row), mirroring the
        // FE's existing foundry.ItemParamter.Picklist fallback via MITEM.QUALITYPARAMETERSETID.
        public const string GET_ITEM_QUALITY_PARAMETERSET_DEFAULT = @"
            SELECT
                MI.QUALITYPARAMETERSETID AS ParameterSetId,
                PS.PARAMETERSETCODE      AS ParameterSetCode,
                PS.PARAMETERSETNAME      AS ParameterSetName
            FROM MITEM MI
            LEFT JOIN MPARAMETERSET PS ON PS.PARAMETERSETID = MI.QUALITYPARAMETERSETID
            WHERE MI.ITEMID = @ItemId
              AND MI.QUALITYPARAMETERSETID > 0;";
    }
}