namespace MMDAL.Query.InspectionParamSetOverride
{
    // Single-row CRUD over MINSPECTIONTYPEPARAMETERSET's override tier (rows where
    // at least one of PARTYID/ITEMID is concrete). The default tier (PARTYID=-1 AND
    // ITEMID=-1) belongs exclusively to InspectionTypeQB/InspectionTypeBLL — never
    // touched here.
    public static class InspectionParamSetOverrideQB
    {
        public const string GET_INSPECTIONPARAMSETOVERRIDE = @"
            SELECT
                ITP.INSPECTIONTYPEPARAMETERSETID AS InspectionTypeParameterSetId,
                ITP.INSPECTIONTYPEPARAMETERSETCODE AS InspectionTypeParameterSetCode,
                ITP.INSPECTIONTYPEPARAMETERSETNAME AS InspectionTypeParameterSetName,
                ITP.INSPECTIONTYPEID             AS InspectionTypeId,
                IT.INSPECTIONTYPECODE            AS InspectionTypeCode,
                IT.INSPECTIONTYPENAME            AS InspectionTypeName,
                ITP.PARAMETERSETID               AS ParameterSetId,
                PS.PARAMETERSETCODE              AS ParameterSetCode,
                PS.PARAMETERSETNAME              AS ParameterSetName,
                ITP.ISREQUIRED                   AS IsRequired,
                ITP.SLNO                         AS SlNo,
                ITP.STATUS                       AS Status,
                ITP.VERSION                      AS Version,
                ITP.CREATEDBYID                  AS CreatedById,
                ITP.CREATEDON                    AS CreatedOn,
                ITP.MODIFIEDBYID                 AS ModifiedById,
                ITP.MODIFIEDON                   AS ModifiedOn,
                ITP.PARTYID                      AS PartyId,
                PT.PARTYCODE                     AS PartyCode,
                PT.PARTYNAME                     AS PartyName,
                ITP.ITEMID                       AS ItemId,
                IM.ITEMCODE                      AS ItemCode,
                IM.ITEMNAME                      AS ItemName,
                ITP.PRIORITY                     AS Priority,
                ITP.EFFECTIVEFROM                AS EffectiveFrom,
                ITP.EFFECTIVETO                  AS EffectiveTo
            FROM MINSPECTIONTYPEPARAMETERSET ITP
            INNER JOIN MINSPECTIONTYPE IT ON IT.INSPECTIONTYPEID = ITP.INSPECTIONTYPEID
            INNER JOIN MPARAMETERSET PS ON PS.PARAMETERSETID = ITP.PARAMETERSETID
            LEFT JOIN MPARTY PT ON PT.PARTYID = ITP.PARTYID
            LEFT JOIN MITEM IM ON IM.ITEMID = ITP.ITEMID
            WHERE ITP.INSPECTIONTYPEPARAMETERSETID = @InspectionTypeParameterSetId;";

        public const string GET_INSPECTIONPARAMSETOVERRIDE_LIST = @"
            SELECT
                ITP.INSPECTIONTYPEPARAMETERSETID AS InspectionTypeParameterSetId,
                ITP.INSPECTIONTYPEPARAMETERSETCODE AS InspectionTypeParameterSetCode,
                ITP.INSPECTIONTYPEPARAMETERSETNAME AS InspectionTypeParameterSetName,
                ITP.INSPECTIONTYPEID             AS InspectionTypeId,
                IT.INSPECTIONTYPECODE            AS InspectionTypeCode,
                IT.INSPECTIONTYPENAME            AS InspectionTypeName,
                ITP.PARAMETERSETID               AS ParameterSetId,
                PS.PARAMETERSETCODE              AS ParameterSetCode,
                PS.PARAMETERSETNAME              AS ParameterSetName,
                ITP.ISREQUIRED                   AS IsRequired,
                ITP.SLNO                         AS SlNo,
                ITP.STATUS                       AS Status,
                ITP.VERSION                      AS Version,
                ITP.CREATEDBYID                  AS CreatedById,
                ITP.CREATEDON                    AS CreatedOn,
                ITP.MODIFIEDBYID                 AS ModifiedById,
                ITP.MODIFIEDON                   AS ModifiedOn,
                ITP.PARTYID                      AS PartyId,
                PT.PARTYCODE                     AS PartyCode,
                PT.PARTYNAME                     AS PartyName,
                ITP.ITEMID                       AS ItemId,
                IM.ITEMCODE                      AS ItemCode,
                IM.ITEMNAME                      AS ItemName,
                ITP.PRIORITY                     AS Priority,
                ITP.EFFECTIVEFROM                AS EffectiveFrom,
                ITP.EFFECTIVETO                  AS EffectiveTo
            FROM MINSPECTIONTYPEPARAMETERSET ITP
            INNER JOIN MINSPECTIONTYPE IT ON IT.INSPECTIONTYPEID = ITP.INSPECTIONTYPEID
            INNER JOIN MPARAMETERSET PS ON PS.PARAMETERSETID = ITP.PARAMETERSETID
            LEFT JOIN MPARTY PT ON PT.PARTYID = ITP.PARTYID
            LEFT JOIN MITEM IM ON IM.ITEMID = ITP.ITEMID
            WHERE ITP.STATUS <> 2
              AND NOT (ITP.PARTYID = -1 AND ITP.ITEMID = -1)
              AND (@PartyId IS NULL OR ITP.PARTYID = @PartyId)
              AND (@ItemId IS NULL OR ITP.ITEMID = @ItemId)
              AND (@InspectionTypeId IS NULL OR ITP.INSPECTIONTYPEID = @InspectionTypeId)
            ORDER BY ITP.INSPECTIONTYPEPARAMETERSETID DESC;";

        // Criteria-driven variant — DAL appends dynamic WHERE conditions built from
        // CriteriaDTO (e.g. AND ITP.PARTYID = 123), then wraps in CTE for pagination.
        // Mirrors PartyQB.GET_PARTYDETAILS_QUALITY_INNER/OUTER pattern.
        public const string GET_INSPECTIONPARAMSETOVERRIDE_CRITERIA_INNER = @"
            SELECT
                ROW_NUMBER() OVER (ORDER BY ITP.INSPECTIONTYPEPARAMETERSETID DESC) AS RowNum,
                ITP.INSPECTIONTYPEPARAMETERSETID AS InspectionTypeParameterSetId,
                ITP.INSPECTIONTYPEPARAMETERSETCODE AS InspectionTypeParameterSetCode,
                ITP.INSPECTIONTYPEPARAMETERSETNAME AS InspectionTypeParameterSetName,
                ITP.INSPECTIONTYPEID             AS InspectionTypeId,
                IT.INSPECTIONTYPECODE            AS InspectionTypeCode,
                IT.INSPECTIONTYPENAME            AS InspectionTypeName,
                ITP.PARAMETERSETID               AS ParameterSetId,
                PS.PARAMETERSETCODE              AS ParameterSetCode,
                PS.PARAMETERSETNAME              AS ParameterSetName,
                ITP.ISREQUIRED                   AS IsRequired,
                ITP.SLNO                         AS SlNo,
                ITP.STATUS                       AS Status,
                ITP.VERSION                      AS Version,
                ITP.CREATEDBYID                  AS CreatedById,
                ITP.CREATEDON                    AS CreatedOn,
                ITP.MODIFIEDBYID                 AS ModifiedById,
                ITP.MODIFIEDON                   AS ModifiedOn,
                ITP.PARTYID                      AS PartyId,
                PT.PARTYCODE                     AS PartyCode,
                PT.PARTYNAME                     AS PartyName,
                ITP.ITEMID                       AS ItemId,
                IM.ITEMCODE                      AS ItemCode,
                IM.ITEMNAME                      AS ItemName,
                ITP.PRIORITY                     AS Priority,
                ITP.EFFECTIVEFROM                AS EffectiveFrom,
                ITP.EFFECTIVETO                  AS EffectiveTo
            FROM MINSPECTIONTYPEPARAMETERSET ITP
            INNER JOIN MINSPECTIONTYPE IT ON IT.INSPECTIONTYPEID = ITP.INSPECTIONTYPEID
            INNER JOIN MPARAMETERSET PS ON PS.PARAMETERSETID = ITP.PARAMETERSETID
            LEFT JOIN MPARTY PT ON PT.PARTYID = ITP.PARTYID
            LEFT JOIN MITEM IM ON IM.ITEMID = ITP.ITEMID
            WHERE ITP.STATUS <> 2
              AND NOT (ITP.PARTYID = -1 AND ITP.ITEMID = -1)";

        public const string GET_INSPECTIONPARAMSETOVERRIDE_CRITERIA_OUTER = @"
            SELECT
                InspectionTypeParameterSetId, InspectionTypeParameterSetCode, InspectionTypeParameterSetName,
                InspectionTypeId, InspectionTypeCode, InspectionTypeName,
                ParameterSetId, ParameterSetCode, ParameterSetName,
                IsRequired, SlNo, Status, Version,
                CreatedById, CreatedOn, ModifiedById, ModifiedOn,
                PartyId, PartyCode, PartyName,
                ItemId, ItemCode, ItemName,
                Priority, EffectiveFrom, EffectiveTo
            FROM PagedInspectionParamSetOverride
            WHERE (@firstnumber = -1 AND @maxresult = -1)
               OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        // Server-computed SlNo — scoped to INSPECTIONTYPEID ONLY, matching the live
        // UK_MINSPECTIONTYPEPARAMETERSET_TYPE_SLNO constraint on (INSPECTIONTYPEID, SLNO).
        // NOTE: that constraint predates the PartyId/Item override scope and does not
        // give each party its own SlNo sequence — every party/item override for the
        // same InspectionTypeId shares one counter. A migration
        // (20260806_InspectionTypeParameterSet_RescopeSlNoUnique.sql) exists to rescope
        // this to (INSPECTIONTYPEID, PARTYID, ITEMID) for true per-party independence;
        // run it and widen this query's WHERE back to include PartyId/ItemId if/when
        // that lands. Client no longer supplies SlNo on Save; the BLL calls this and
        // uses MAX+1.
        public const string GET_MAX_SLNO = @"
            SELECT ISNULL(MAX(ITP.SLNO), 0)
            FROM MINSPECTIONTYPEPARAMETERSET ITP
            WHERE ITP.STATUS <> 2
              AND ITP.INSPECTIONTYPEID = @InspectionTypeId;";

        public const string SAVE_INSPECTIONPARAMSETOVERRIDE = @"
            INSERT INTO MINSPECTIONTYPEPARAMETERSET
                (INSPECTIONTYPEPARAMETERSETID, INSPECTIONTYPEPARAMETERSETCODE, INSPECTIONTYPEPARAMETERSETNAME, INSPECTIONTYPEID, PARAMETERSETID,
                 ISREQUIRED, SLNO, STATUS, VERSION,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
                 PARTYID, ITEMID, PRIORITY, EFFECTIVEFROM, EFFECTIVETO)
            VALUES
                (@InspectionTypeParameterSetId, @InspectionTypeParameterSetCode, @InspectionTypeParameterSetName,
                 @InspectionTypeId, @ParameterSetId,
                 @IsRequired, @SlNo, @Status, @Version,
                 @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn,
                 @PartyId, @ItemId, @Priority, @EffectiveFrom, @EffectiveTo);";

        public const string UPDATE_INSPECTIONPARAMSETOVERRIDE = @"
            UPDATE MINSPECTIONTYPEPARAMETERSET
            SET
                INSPECTIONTYPEPARAMETERSETCODE = @InspectionTypeParameterSetCode,
                INSPECTIONTYPEPARAMETERSETNAME = @InspectionTypeParameterSetName,
                INSPECTIONTYPEID = @InspectionTypeId,
                PARAMETERSETID   = @ParameterSetId,
                ISREQUIRED       = @IsRequired,
                SLNO             = @SlNo,
                STATUS           = @Status,
                VERSION          = @Version,
                MODIFIEDBYID     = @ModifiedById,
                MODIFIEDON       = @ModifiedOn,
                PARTYID          = @PartyId,
                ITEMID           = @ItemId,
                PRIORITY         = @Priority,
                EFFECTIVEFROM    = @EffectiveFrom,
                EFFECTIVETO      = @EffectiveTo
            WHERE INSPECTIONTYPEPARAMETERSETID = @InspectionTypeParameterSetId
              AND NOT (PARTYID = -1 AND ITEMID = -1);";

        public const string DELETE_INSPECTIONPARAMSETOVERRIDE = @"
            UPDATE MINSPECTIONTYPEPARAMETERSET
            SET STATUS = 2, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn
            WHERE INSPECTIONTYPEPARAMETERSETID = @InspectionTypeParameterSetId
              AND NOT (PARTYID = -1 AND ITEMID = -1);";

        public const string GET_PICKLIST_INSPECTIONPARAMSETOVERRIDE = @"
WITH InspectionParamSetOverride AS (
    SELECT
        ITP.INSPECTIONTYPEPARAMETERSETID AS InspectionTypeParameterSetId,
        ITP.INSPECTIONTYPEPARAMETERSETCODE AS InspectionTypeParameterSetCode,
        ITP.INSPECTIONTYPEPARAMETERSETNAME AS InspectionTypeParameterSetName,
        ITP.INSPECTIONTYPEID             AS InspectionTypeId,
        IT.INSPECTIONTYPECODE            AS InspectionTypeCode,
        IT.INSPECTIONTYPENAME            AS InspectionTypeName,
        ITP.PARAMETERSETID               AS ParameterSetId,
        PS.PARAMETERSETCODE              AS ParameterSetCode,
        PS.PARAMETERSETNAME              AS ParameterSetName,
        ITP.PARTYID                      AS PartyId,
        PT.PARTYCODE                     AS PartyCode,
        PT.PARTYNAME                     AS PartyName,
        ITP.ITEMID                       AS ItemId,
        IM.ITEMCODE                      AS ItemCode,
        IM.ITEMNAME                      AS ItemName,
        ITP.PRIORITY                     AS Priority,
        ROW_NUMBER() OVER (ORDER BY ITP.INSPECTIONTYPEPARAMETERSETID DESC) AS RowNum
    FROM MINSPECTIONTYPEPARAMETERSET ITP
    INNER JOIN MINSPECTIONTYPE IT ON IT.INSPECTIONTYPEID = ITP.INSPECTIONTYPEID
    INNER JOIN MPARAMETERSET PS ON PS.PARAMETERSETID = ITP.PARAMETERSETID
    LEFT JOIN MPARTY PT ON PT.PARTYID = ITP.PARTYID
    LEFT JOIN MITEM IM ON IM.ITEMID = ITP.ITEMID
    WHERE ITP.STATUS <> 2
      AND NOT (ITP.PARTYID = -1 AND ITP.ITEMID = -1)
      AND (@searchtext IS NULL OR @searchtext = ''
           OR ITP.INSPECTIONTYPEPARAMETERSETCODE LIKE '%' + @searchtext + '%'
           OR ITP.INSPECTIONTYPEPARAMETERSETNAME LIKE '%' + @searchtext + '%'
           OR PT.PARTYCODE LIKE '%' + @searchtext + '%'
           OR PT.PARTYNAME LIKE '%' + @searchtext + '%'
           OR IM.ITEMCODE LIKE '%' + @searchtext + '%'
           OR IM.ITEMNAME LIKE '%' + @searchtext + '%'
           OR IT.INSPECTIONTYPECODE LIKE '%' + @searchtext + '%'
           OR IT.INSPECTIONTYPENAME LIKE '%' + @searchtext + '%')
)
SELECT
    InspectionTypeParameterSetId,
    InspectionTypeParameterSetCode,
    InspectionTypeParameterSetName,
    InspectionTypeId,
    InspectionTypeCode,
    InspectionTypeName,
    ParameterSetId,
    ParameterSetCode,
    ParameterSetName,
    PartyId,
    PartyCode,
    PartyName,
    ItemId,
    ItemCode,
    ItemName,
    Priority
FROM InspectionParamSetOverride
WHERE
    (@firstnumber = -1 AND @maxresult = -1)
    OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        // Parameter-level detail — one row per MPARAMETERSETDETAIL under the override's
        // ParameterSetId, shaped to match ItemDetailDTO (ItemId/Code/Name carry the
        // PARTY's identity, same convention as PartyQB.GET_PARTYDETAILS_QUALITY_INNER).
        // DAL appends dynamic WHERE conditions from CriteriaDTO (e.g. AND ITP.PARTYID = @PartyId),
        // then wraps in CTE for pagination.
        public const string GET_INSPECTIONPARAMSETOVERRIDE_DETAIL_INNER = @"
            SELECT
                ROW_NUMBER() OVER (ORDER BY PSD.SLNO)   AS RowNum,
                ITP.INSPECTIONTYPEPARAMETERSETID        AS ItemDetailId,
                ITP.PARTYID                              AS ItemId,
                PT.PARTYCODE                             AS ItemCode,
                PT.PARTYNAME                              AS ItemName,
                ISNULL(PT.PARTYSHORTNAME, '')            AS ItemShortName,
                PSD.SLNO                                 AS ItemDetailSlNo,
                PARAM.PARAMETERID                        AS ParameterId,
                PARAM.PARAMETERCODE                      AS ParameterCode,
                PARAM.PARAMETERNAME                      AS ParameterName,
                PARAM.PARAMETERDATATYPE                  AS ParameterDataType,
                CAST(PARAM.MINVALUE AS FLOAT)            AS ParameterMinValue,
                CAST(PARAM.MAXVALUE AS FLOAT)            AS ParameterMaxValue,
                PARAM.LOVTYPEID                          AS ParameterLovTypeId,
                PARAM.UOMID                               AS UOMId,
                UOM.UOMCODE                               AS UOMCode,
                UOM.UOMNAME                                AS UOMName,
                NULL                                       AS ItemDetailMinimum,
                NULL                                       AS ItemDetailMaximum,
                NULL                                       AS ItemDetailInputText,
                NULL                                       AS ItemDetailInputDate,
                NULL                                       AS InputId,
                NULL                                       AS InputCode,
                NULL                                       AS InputName,
                NULL                                       AS Score,
                NULL                                       AS ItemDetailInputValue,
                NULL                                       AS ItemDetailFinalValue,
                PSD.REJECTIONTYPE                         AS RejectionType,
                NULL                                       AS ItemDetailStandardText,
                NULL                                       AS ItemDetailType,
                NULL                                       AS ItemDetailTollerancePlusPercent,
                NULL                                       AS ItemDetailTolleranceMinusPercent,
                NULL                                       AS SKUId,
                NULL                                       AS SKUCode,
                NULL                                       AS SKUName
            FROM MINSPECTIONTYPEPARAMETERSET ITP
            INNER JOIN MPARAMETERSETDETAIL PSD  ON PSD.PARAMETERSETID = ITP.PARAMETERSETID
            INNER JOIN MPARAMETER          PARAM ON PARAM.PARAMETERID = PSD.PARAMETERID
            INNER JOIN MUOM                 UOM   ON UOM.UOMID         = PARAM.UOMID
            LEFT JOIN  MPARTY                PT    ON PT.PARTYID       = ITP.PARTYID
            WHERE ITP.STATUS <> 2
              AND NOT (ITP.PARTYID = -1 AND ITP.ITEMID = -1)";

        public const string GET_INSPECTIONPARAMSETOVERRIDE_DETAIL_OUTER = @"
            SELECT ItemDetailId, ItemId, ItemCode, ItemName, ItemShortName,
                   ItemDetailSlNo, ParameterId, ParameterCode, ParameterName, ParameterDataType,
                   ParameterMinValue, ParameterMaxValue, ParameterLovTypeId,
                   UOMId, UOMCode, UOMName,
                   ItemDetailMinimum, ItemDetailMaximum, ItemDetailInputText, ItemDetailInputDate,
                   InputId, InputCode, InputName, Score,
                   ItemDetailInputValue, ItemDetailFinalValue, RejectionType,
                   ItemDetailStandardText, ItemDetailType,
                   ItemDetailTollerancePlusPercent, ItemDetailTolleranceMinusPercent,
                   SKUId, SKUCode, SKUName
            FROM PagedInspectionParamSetOverrideDetail
            WHERE (@firstnumber = -1 AND @maxresult = -1)
               OR (RowNum BETWEEN @firstnumber AND @maxresult);";
    }
}