package org.pwd.domain.audit;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @author bartosz.walacik
 */
public interface WebsiteAuditRepository extends JpaRepository<WebsiteAudit, Integer> {

    List<WebsiteAudit> findByAudit(Audit audit);

    WebsiteAudit findByAuditAndWebsiteId(Audit audit, int websiteId);

    List<WebsiteAudit> findByWebsiteId(int websiteId);

    @Query(nativeQuery = true, value = "SELECT * " +
            " FROM   website_audit wa, website w" +
            " WHERE  w.id = wa.website_fk" +
            " AND    CAST(json_extract_path_text(audit_report,'score') as Integer) > 0 " +
            " AND    wa.audit_fk = (SELECT MAX(id) FROM audit where process_status = 'DONE')" +
            " ORDER BY to_number(json_extract_path_text(audit_report,'score'),'999') DESC" +
            " LIMIT :maxRecords")
    List<WebsiteAudit> getTop(@Param("maxRecords") Integer maxRecords);

    @Query(nativeQuery = true, value = "SELECT * " +
            " FROM   website_audit wa, website w" +
            " WHERE  w.id = wa.website_fk" +
            " AND    CAST(json_extract_path_text(audit_report,'score') as Integer) > 0 " +
            " AND    wa.audit_fk = (SELECT MAX(id) FROM audit where process_status = 'DONE')" +
            " ORDER BY to_number(json_extract_path_text(audit_report,'score'),'999') DESC")
    List<WebsiteAudit> getSorted();

    @Query(nativeQuery = true, value = "SELECT * " +
            " FROM   website_audit wa, website w" +
            " WHERE  w.id = wa.website_fk" +
            " AND    CAST(json_extract_path_text(audit_report,'score') as Integer) > 0 " +
            " AND    wa.audit_fk = (SELECT MAX(id) FROM audit where process_status = 'DONE')" +
            " AND    w.id = :websiteId")
    WebsiteAudit getCurrentScore(@Param("websiteId") int websiteId);
}

@@ -675,99 +675,8 @@
 %22 +%0A
-            %22 AND    CAST(json_extract_path_text(audit_report,'score') as Integer) %3E 0 %22 +%0A
     
@@ -759,32 +759,32 @@
 us = 'DONE')%22 +%0A
+
             %22 OR
@@ -1121,99 +1121,8 @@
 %22 +%0A
-            %22 AND    CAST(json_extract_path_text(audit_report,'score') as Integer) %3E 0 %22 +%0A
     
@@ -1439,32 +1439,32 @@
 a, website w%22 +%0A
+
             %22 WH
@@ -1496,99 +1496,8 @@
 %22 +%0A
-            %22 AND    CAST(json_extract_path_text(audit_report,'score') as Integer) %3E 0 %22 +%0A
     

Update WebsiteAuditRepository.java