namespace PERMDAL.QueryBuilders;

// Required indexes:
//   MRATINGSCALE: (TENANTID, SLABFROM, SLABTO) — used by bell curve grade lookup
//   MRATINGSCALE: (TENANTID, STATUS) INCLUDE (CODE, NAME, GRADELETTER)
public static class RatingScaleQB
{
    public const string GET_BY_ID = @"
        SELECT R.RATINGSCALEID  AS RatingScaleId,
               R.CODE           AS Code,
               R.NAME           AS Name,
               R.SLABFROM       AS SlabFrom,
               R.SLABTO         AS SlabTo,
               R.GRADELETTER    AS GradeLetter,
               R.GRADENAME      AS GradeName,
               R.SORTORDER      AS SortOrder,
               R.STATUS         AS Status,
               R.TENANTID       AS TenantId
        FROM   MRATINGSCALE R
        WHERE  R.RATINGSCALEID = @RatingScaleId
        AND    R.TENANTID      = @TenantId";

    public const string GET_LIST = @"
        SELECT R.RATINGSCALEID  AS RatingScaleId,
               R.CODE           AS Code,
               R.NAME           AS Name,
               R.SLABFROM       AS SlabFrom,
               R.SLABTO         AS SlabTo,
               R.GRADELETTER    AS GradeLetter,
               R.GRADENAME      AS GradeName,
               R.SORTORDER      AS SortOrder,
               R.STATUS         AS Status,
               COUNT(1) OVER()  AS TotalCount
        FROM   MRATINGSCALE R
        WHERE  R.TENANTID = @TenantId
        AND    (@Status IS NULL OR R.STATUS = @Status)
        ORDER  BY R.SLABFROM
        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

    public const string GET_ALL_ACTIVE = @"
        SELECT R.RATINGSCALEID  AS RatingScaleId,
               R.CODE           AS Code,
               R.NAME           AS Name,
               R.SLABFROM       AS SlabFrom,
               R.SLABTO         AS SlabTo,
               R.GRADELETTER    AS GradeLetter,
               R.GRADENAME      AS GradeName,
               R.SORTORDER      AS SortOrder
        FROM   MRATINGSCALE R
        WHERE  R.TENANTID = @TenantId
        AND    R.STATUS   = 1
        {DYNAMIC_WHERE}
        ORDER  BY R.SLABFROM";

    public const string INSERT = @"
        INSERT INTO MRATINGSCALE
            (RATINGSCALEID, CODE, NAME, SLABFROM, SLABTO, GRADELETTER, GRADENAME, SORTORDER, STATUS, TENANTID, CREATEDBY, CREATEDON, MODIFIEDBY, MODIFIEDON)
        VALUES
            (@RatingScaleId, @Code, @Name, @SlabFrom, @SlabTo, @GradeLetter, @GradeName, @SortOrder, @Status, @TenantId, @CreatedBy, GETUTCDATE(), @ModifiedBy, GETUTCDATE())";

    public const string UPDATE = @"
        UPDATE MRATINGSCALE
        SET    CODE        = @Code,
               NAME        = @Name,
               SLABFROM    = @SlabFrom,
               SLABTO      = @SlabTo,
               GRADELETTER = @GradeLetter,
               GRADENAME   = @GradeName,
               SORTORDER   = @SortOrder,
               STATUS      = @Status,
               MODIFIEDBY  = @ModifiedBy,
               MODIFIEDON  = GETUTCDATE()
        WHERE  RATINGSCALEID = @RatingScaleId
        AND    TENANTID      = @TenantId";

    public const string DELETE = @"
        DELETE FROM MRATINGSCALE
        WHERE  RATINGSCALEID = @RatingScaleId
        AND    TENANTID      = @TenantId";

    public const string EXISTS_CODE = @"
        SELECT COUNT(1)
        FROM   MRATINGSCALE
        WHERE  CODE          = @Code
        AND    TENANTID      = @TenantId
        AND    RATINGSCALEID <> @RatingScaleId";

    public const string SLAB_OVERLAP = @"
        SELECT COUNT(1)
        FROM   MRATINGSCALE
        WHERE  TENANTID      = @TenantId
        AND    RATINGSCALEID <> @RatingScaleId
        AND    STATUS        = 1
        AND    @SlabFrom     < SLABTO
        AND    @SlabTo       > SLABFROM";

    public const string GET_GRADE_FOR_SCORE = @"
        SELECT TOP 1
               RATINGSCALEID  AS RatingScaleId,
               GRADELETTER    AS GradeLetter,
               GRADENAME      AS GradeName
        FROM   MRATINGSCALE
        WHERE  @Score BETWEEN SLABFROM AND SLABTO
        AND    TENANTID = @TenantId
        AND    STATUS   = 1
        ORDER BY SORTORDER";
}