package com.dbnt.kcscbackend.admin.logs.repository;

import com.dbnt.kcscbackend.admin.logs.entity.TnDailyMenuLog;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.time.LocalDate;
import java.util.List;

public interface MenuLogsRepository extends JpaRepository<TnDailyMenuLog, Long> {

    @Query(value = "SELECT COUNT(DISTINCT menu_id) "
            + "FROM tn_daily_menu_log "
            + "WHERE log_dt BETWEEN TO_DATE(:startDate, 'YYYY-MM-DD') AND TO_DATE(:endDate, 'YYYY-MM-DD')", nativeQuery = true)
    long countDistinctMenuIds(@Param("startDate") String startDate, @Param("endDate") String endDate);

    @Query(value = "SELECT menu_id, MAX(menu_title) as menu_title, sum(log_cnt) as log_cnt "
            + "FROM tn_daily_menu_log "
            + "WHERE log_dt BETWEEN TO_DATE(:startDate, 'YYYY-MM-DD') AND TO_DATE(:endDate, 'YYYY-MM-DD') "
            + "GROUP BY menu_id "
            + "ORDER BY log_cnt desc", nativeQuery = true)
    List<Object[]> selectCountMenu(@Param("startDate") String startDate, @Param("endDate") String endDate);


}
