package com.aksenov.monitor.repositories;


import com.aksenov.monitor.data.Sales;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

public interface SalesRepository extends JpaRepository<Sales, Long>, JpaSpecificationExecutor<Sales> {
    @Query("SELECT s FROM Sales s WHERE FUNCTION('MONTH', s.saleDate) = :month AND FUNCTION('YEAR', s.saleDate) = :year")
    List<Sales> findAllBySaleDateMonthAndYear(@Param("month") int month, @Param("year") int year);

    @Query("SELECT SUM(s.totalPrice) FROM Sales s WHERE FUNCTION('MONTH', s.saleDate) = :month AND FUNCTION('YEAR', s.saleDate) = :year")
    Double findTotalSalesByMonthAndYear(@Param("month") int month, @Param("year") int year);

    @Query("SELECT m.model, SUM(s.totalPrice) FROM Sales s JOIN s.monitor m GROUP BY m.model")
    List<Object[]> getSalesStatisticsByModel();
}
