package hozza.network.models.twitch.repositories;

import hozza.network.models.twitch.result.IStreamerStat;
import hozza.network.models.twitch.result.IWatchingDayOfWeek;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.time.LocalDateTime;
import java.util.List;

import hozza.network.models.twitch.entities.MariaWatching;

public interface MariaWatchingRepository extends JpaRepository<MariaWatching, Integer>
{
    @Query(value = "SELECT COUNT(*) FROM watching", nativeQuery = true)
    long getWatchingCount();

    @Query(value = "SELECT COUNT(*) FROM watching WHERE date_time_watched BETWEEN NOW() - INTERVAL (HOUR(NOW())*60*60+MINUTE(NOW())*60+SECOND(NOW())) SECOND AND NOW()", nativeQuery = true)
    long getWatchingCountToday();

    @Query(value = "SELECT WEEKDAY(date_time_watched) AS dayOfWeek, HOUR(date_time_watched) AS hourOfDay, COUNT(date_time_watched) as count FROM watching GROUP BY dayOfWeek, hourOfDay ORDER BY WEEKDAY(date_time_watched)", nativeQuery = true)
    List<IWatchingDayOfWeek> countAllStatisticsByDayOfWeek();

    @Query(value = "SELECT date_time_watched FROM watching", nativeQuery = true)
    List<LocalDateTime> findAllWatchingDates();

    @Query(value = "SELECT k1.streamer as streamerOne, k2.streamer as streamerTwo, count(k1.person) as count FROM watching AS k1 INNER JOIN watching AS k2 ON k1.streamer != k2.streamer AND k1.person = k2.person WHERE k2.streamer > k1.streamer GROUP BY k1.streamer,k2.streamer ORDER BY NULL", nativeQuery = true)
    List<IStreamerStat> getStreamerStats();
}
