package nl.homeserver.energie;

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

import javax.transaction.Transactional;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

@Transactional
public interface MeterstandRepository extends JpaRepository<Meterstand, Long> {

    // Please note that BETWEEN in HQL is INCLUSIVE!

    @Query(value = "SELECT m FROM Meterstand m WHERE m.dateTime = (SELECT MAX(mostrecent.dateTime) FROM Meterstand mostrecent)")
    Meterstand getMostRecent();

    @Query(value = "SELECT m FROM Meterstand m WHERE m.dateTime = (SELECT MIN(oldest.dateTime) FROM Meterstand oldest)")
    Meterstand getOldest();

    @Query(value = "SELECT m FROM Meterstand m WHERE m.dateTime = (SELECT MAX(dateTime) from Meterstand m where m.dateTime BETWEEN :van AND :totEnMet)")
    Meterstand getMostRecentInPeriod(@Param("van") LocalDateTime start, @Param("totEnMet") LocalDateTime end);

    @Query(value = "SELECT m FROM Meterstand m WHERE m.dateTime = (SELECT MIN(dateTime) from Meterstand m where m.dateTime BETWEEN :van AND :totEnMet)")
    Meterstand getOldestInPeriod(@Param("van") LocalDateTime start, @Param("totEnMet") LocalDateTime end);

    List<Meterstand> findByDateTimeBetween(LocalDateTime start, LocalDateTime end);

    @Query(value = "SELECT date FROM (" +
                   "  SELECT PARSEDATETIME(FORMATDATETIME(date_time, 'dd-MM-yyyy'), 'dd-MM-yyyy') AS date, " +
                   "         COUNT(id) AS nr_of_records " +
                   "    FROM meterstand " +
                   "   WHERE date_time >= :fromDate " +
                   "     AND date_time < :toDate " +
                   "   GROUP BY date " +
                   "     HAVING nr_of_records > :maxNrOfRowsPerDay " +
                   "   ORDER BY nr_of_records DESC " +
                   " )", nativeQuery = true)
    List<Timestamp> findDatesBeforeToDateWithMoreRowsThan(@Param("fromDate") LocalDate fromDate,
                                                          @Param("toDate") LocalDate toDate,
                                                          @Param("maxNrOfRowsPerDay") int maxNrOfRowsPerDay);
}

@@ -25,16 +25,202 @@
 ergie;%0A%0A
+import java.sql.Timestamp;%0Aimport java.time.LocalDate;%0Aimport java.time.LocalDateTime;%0Aimport java.util.List;%0A%0Aimport javax.annotation.Nullable;%0Aimport javax.transaction.Transactional;%0A%0A
 import o
@@ -388,159 +388,8 @@
 m;%0A%0A
-import javax.transaction.Transactional;%0Aimport java.sql.Timestamp;%0Aimport java.time.LocalDate;%0Aimport java.time.LocalDateTime;%0Aimport java.util.List;%0A%0A
 @Tra
@@ -534,16 +534,30 @@
 USIVE!%0A%0A
+    @Nullable%0A
     @Que
@@ -706,24 +706,38 @@
 tRecent();%0A%0A
+    @Nullable%0A
     @Query(v
@@ -870,24 +870,38 @@
 tOldest();%0A%0A
+    @Nullable%0A
     @Query(v
@@ -1145,32 +1145,46 @@
 DateTime end);%0A%0A
+    @Nullable%0A
     @Query(value
@@ -1424,24 +1424,38 @@
 Time end);%0A%0A
+    @Nullable%0A
     List%3CMet
@@ -1523,24 +1523,38 @@
 Time end);%0A%0A
+    @Nullable%0A
     @Query(v

Add nullable annotations