namespace MMDAL.Query.InspectionType
{
    public static class InspectionTypeQB
    {
        public const string GET_INSPECTION_TYPE = @"
            SELECT
                IT.INSPECTIONTYPEID   AS InspectionTypeId,
                IT.INSPECTIONTYPECODE AS InspectionTypeCode,
                IT.INSPECTIONTYPENAME AS InspectionTypeName,
                IT.DESCRIPTION        AS Description,
                IT.STATUS             AS Status,
                IT.VERSION            AS Version,
                IT.CREATEDBYID        AS CreatedById,
                IT.CREATEDON          AS CreatedOn,
                UC.USERNAME           AS CreatedByName,
                IT.MODIFIEDBYID       AS ModifiedById,
                IT.MODIFIEDON         AS ModifiedOn,
                UM.USERNAME           AS ModifiedByName,
                -- child: MINSPECTIONTYPEPARAMETERSET
                ITP.INSPECTIONTYPEPARAMETERSETID AS InspectionTypeParameterSetId,
                ITP.INSPECTIONTYPEID             AS InspectionTypeId,
                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,
                ITP.ITEMID                       AS ItemId,
                ITP.PRIORITY                     AS Priority,
                ITP.EFFECTIVEFROM                AS EffectiveFrom,
                ITP.EFFECTIVETO                  AS EffectiveTo
            FROM MINSPECTIONTYPE IT
            -- Only the default tier (PartyId=-1, ItemId=-1) belongs to this editor —
            -- customer/item override rows are managed by InspectionParamSetOverride
            -- and must never be shown/touched here.
            LEFT JOIN MINSPECTIONTYPEPARAMETERSET ITP
                ON ITP.INSPECTIONTYPEID = IT.INSPECTIONTYPEID
                AND ITP.PARTYID = -1 AND ITP.ITEMID = -1
            LEFT JOIN MPARAMETERSET PS ON PS.PARAMETERSETID = ITP.PARAMETERSETID
            LEFT JOIN MUSER UC ON UC.USERID = IT.CREATEDBYID
            LEFT JOIN MUSER UM ON UM.USERID = IT.MODIFIEDBYID
            WHERE IT.INSPECTIONTYPEID = @InspectionTypeId
            ORDER BY ITP.SLNO;";

        public const string GET_PICKLIST_INSPECTION_TYPE = @"
WITH InspectionType AS (
    SELECT
        IT.INSPECTIONTYPEID     AS InspectionTypeId,
        IT.INSPECTIONTYPECODE   AS InspectionTypeCode,
        IT.INSPECTIONTYPENAME   AS InspectionTypeName,
        MIN(PSD.SECTION)        AS Section,
        ROW_NUMBER() OVER (ORDER BY IT.INSPECTIONTYPEID) AS RowNum
    FROM MINSPECTIONTYPE IT
    LEFT JOIN TINSPECTION TI
        ON TI.INSPECTIONTYPEID = IT.INSPECTIONTYPEID
    LEFT JOIN TINSPECTIONDETAIL TID
        ON TID.INSPECTIONID = TI.INSPECTIONID
    LEFT JOIN MPARAMETERSETDETAIL PSD
        ON PSD.PARAMETERSETID = TID.PARAMETERSETID
    WHERE IT.STATUS = 1
      AND (@searchtext IS NULL OR @searchtext = ''
           OR IT.INSPECTIONTYPECODE LIKE '%' + @searchtext + '%'
           OR IT.INSPECTIONTYPENAME LIKE '%' + @searchtext + '%')
    GROUP BY IT.INSPECTIONTYPEID, IT.INSPECTIONTYPECODE, IT.INSPECTIONTYPENAME
)
SELECT
    InspectionTypeId,
    InspectionTypeCode,
    InspectionTypeName,
    Section
FROM InspectionType
WHERE
    (@firstnumber = -1 AND @maxresult = -1)
    OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        // Default-tier only (PartyId=-1, ItemId=-1) — preserves this endpoint's existing
        // callers' behavior exactly. Customer/item-aware resolution goes through the new
        // InspectionParamSetEngine/ResolveInspectionParameterSet endpoint instead.
        public const string GET_PARAMETERSETS_BY_INSPECTION_TYPE = @"
            SELECT
                ITP.INSPECTIONTYPEPARAMETERSETID AS InspectionTypeParameterSetId,
                ITP.INSPECTIONTYPEID             AS InspectionTypeId,
                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,
                ITP.ITEMID                       AS ItemId,
                ITP.PRIORITY                     AS Priority,
                ITP.EFFECTIVEFROM                AS EffectiveFrom,
                ITP.EFFECTIVETO                  AS EffectiveTo
            FROM MINSPECTIONTYPEPARAMETERSET ITP
            INNER JOIN MPARAMETERSET PS ON PS.PARAMETERSETID = ITP.PARAMETERSETID
            WHERE ITP.INSPECTIONTYPEID = @InspectionTypeId
              AND ITP.PARTYID = -1 AND ITP.ITEMID = -1
              AND ITP.STATUS <> 2
            ORDER BY ITP.SLNO;";

        public const string SAVE_INSPECTION_TYPE = @"
            INSERT INTO MINSPECTIONTYPE
                (INSPECTIONTYPEID, INSPECTIONTYPECODE, INSPECTIONTYPENAME, DESCRIPTION,
                 STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@InspectionTypeId, @InspectionTypeCode, @InspectionTypeName, @Description,
                 @Status, @Version, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn);";

        public const string UPDATE_INSPECTION_TYPE = @"
            UPDATE MINSPECTIONTYPE
            SET
                INSPECTIONTYPECODE = @InspectionTypeCode,
                INSPECTIONTYPENAME = @InspectionTypeName,
                DESCRIPTION        = @Description,
                STATUS             = @Status,
                VERSION            = @Version,
                MODIFIEDBYID       = @ModifiedById,
                MODIFIEDON         = @ModifiedOn
            WHERE INSPECTIONTYPEID = @InspectionTypeId;";

        public const string DELETE_INSPECTION_TYPE = @"
            UPDATE MINSPECTIONTYPE
            SET STATUS = 2, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn
            WHERE INSPECTIONTYPEID = @InspectionTypeId;";

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

        // Full soft-delete of every bridge row for a type — used only when the whole
        // InspectionType is being deleted (overrides tied to a deleted type are moot).
        public const string DELETE_INSPECTION_TYPE_PARAMETERSET_BY_TYPE = @"
            UPDATE MINSPECTIONTYPEPARAMETERSET
            SET STATUS = 2, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn
            WHERE INSPECTIONTYPEID = @InspectionTypeId;";

        // Default-tier-only soft-delete — used when SAVING (updating) an InspectionType's
        // own parameter-set array, so customer/item override rows for the same type
        // survive the type edit untouched.
        public const string DELETE_INSPECTION_TYPE_PARAMETERSET_DEFAULT_BY_TYPE = @"
            UPDATE MINSPECTIONTYPEPARAMETERSET
            SET STATUS = 2, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn
            WHERE INSPECTIONTYPEID = @InspectionTypeId
              AND PARTYID = -1 AND ITEMID = -1;";

        public const string GET_INSPECTION_TYPE_COUNT = @"
            SELECT COUNT(1)
            FROM MINSPECTIONTYPE
            WHERE INSPECTIONTYPECODE = @InspectionTypeCode
              AND INSPECTIONTYPEID  <> @InspectionTypeId
              AND STATUS <> 2;";
    }
}