namespace PERMDAL.QueryBuilders;

// Required indexes:
//   MKRA: (TENANTID, STATUS) INCLUDE (KRANAME, NATURE)
public static class KRAQB
{
    public const string GET_BY_ID = @"
        SELECT K.KRAID        AS KRAId,
               K.KRANAME      AS KRAName,
               K.DESCRIPTION  AS Description,
               K.SECTION      AS Section,
               K.NATURE       AS Nature,
               K.DEPARTMENTID AS DepartmentId,
               K.REMARKS      AS Remarks,
               K.CREATEDBYID  AS CreatedById,
               K.CREATEDON    AS CreatedOn,
               K.MODIFIEDBYID AS ModifiedById,
               K.MODIFIEDON   AS ModifiedOn,
               K.SORTORDER    AS SortOrder,
               K.STATUS       AS Status,
               K.VERSION      AS Version,
               K.SOURCETYPE   AS SourceType,
               K.TENANTID     AS TenantId
        FROM   MKRA K
        WHERE  K.KRAID    = @KRAId
        AND    K.TENANTID = @TenantId";

    public const string GET_LIST = @"
        SELECT K.KRAID     AS KRAId,
               K.KRANAME   AS KRAName,
               K.NATURE    AS Nature,
               K.STATUS    AS Status,
               COUNT(1) OVER() AS TotalCount
        FROM   MKRA K
        WHERE  K.TENANTID = @TenantId
        AND    (@Status IS NULL OR K.STATUS = @Status)
        AND    (@Nature IS NULL OR K.NATURE = @Nature)
        ORDER  BY K.KRANAME
        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

    public const string GET_SELECT_LIST = @"
        SELECT K.KRAID    AS KRAId,
               K.KRANAME  AS KRAName,
               K.NATURE   AS Nature
        FROM   MKRA K
        WHERE  K.TENANTID = @TenantId
        AND    K.STATUS   = 1
        {DYNAMIC_WHERE}
        ORDER  BY K.KRANAME";

    public const string INSERT = @"
        INSERT INTO MKRA
            (KRAID, KRANAME, DESCRIPTION, SECTION, NATURE, DEPARTMENTID, REMARKS,
             CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, STATUS, VERSION, SOURCETYPE, TENANTID)
        VALUES
            (@KRAId, @KRAName, @Description, @Section, @Nature, @DepartmentId, @Remarks,
             @CreatedById, GETUTCDATE(), @ModifiedById, GETUTCDATE(), @SortOrder, @Status, 1, @SourceType, @TenantId)";

    public const string UPDATE = @"
        UPDATE MKRA
        SET    KRANAME      = @KRAName,
               DESCRIPTION  = @Description,
               SECTION      = @Section,
               NATURE       = @Nature,
               DEPARTMENTID = @DepartmentId,
               REMARKS      = @Remarks,
               STATUS       = @Status,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = GETUTCDATE(),
               VERSION      = VERSION + 1
        WHERE  KRAID    = @KRAId
        AND    TENANTID = @TenantId";

    public const string DELETE = @"
        DELETE FROM MKRA
        WHERE  KRAID    = @KRAId
        AND    TENANTID = @TenantId";

    public const string EXISTS_NAME = @"
        SELECT COUNT(1)
        FROM   MKRA
        WHERE  KRANAME  = @KRAName
        AND    TENANTID = @TenantId
        AND    KRAID   <> @KRAId";
}