package ru.mcs.sopds.repository;


import ru.mcs.sopds.entity.Genre;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface GenreRepository extends JpaRepository<Genre, Long> {

    @Query(value = "SELECT g.*, COUNT(bg.book_id) as book_count FROM genres g " +
            "LEFT JOIN book_genre bg ON g.id = bg.genre_id " +
            "GROUP BY g.id ORDER BY book_count DESC LIMIT ?1",
            nativeQuery = true)
    List<Genre> findPopularGenres(int limit);

    @Query("SELECT g FROM Genre g LEFT JOIN FETCH g.books WHERE g.id = ?1")
    Genre findByIdWithBooks(Long id);
}