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

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

import java.util.List;

public interface FileLogsRepository extends JpaRepository<ThAttachFileLog, Long> {

    @Query(value = "SELECT COUNT(DISTINCT TO_CHAR(access_dt, 'YYYY-MM-DD')) "
            + "FROM th_attach_file_log "
            + "WHERE access_dt BETWEEN TO_DATE(:startDate, 'YYYY-MM-DD') AND TO_DATE(:endDate, 'YYYY-MM-DD')", nativeQuery = true)
    long countDistinctFileIds(@Param("startDate") String startDate, @Param("endDate") String endDate);

    @Query(value = "SELECT TO_CHAR(access_dt, 'YYYY-MM-DD') as log_dt,"
            + " COUNT(CASE WHEN group_cur_cd = '10' THEN 1 ELSE NULL END) AS Code10Cnt,"
            + " COUNT(CASE WHEN group_cur_cd = '20' THEN 1 ELSE NULL END) AS Code20Cnt,"
            + " COUNT(CASE WHEN group_cur_cd = '40' THEN 1 ELSE NULL END) AS Code40Cnt,"
            + " COUNT(CASE WHEN group_cur_cd = '50' THEN 1 ELSE NULL END) AS Code50Cnt,"
            + " COUNT(CASE WHEN group_cur_cd = '60' THEN 1 ELSE NULL END) AS Code60Cnt,"
            + " COUNT(CASE WHEN group_cur_cd = '70' THEN 1 ELSE NULL END) AS Code70Cnt,"
            + " COUNT(CASE WHEN group_cur_cd = '80' THEN 1 ELSE NULL END) AS Code80Cnt,"
            + " COUNT(CASE WHEN group_cur_cd = '90' THEN 1 ELSE NULL END) AS Code90Cnt,"
            + " COUNT(CASE WHEN group_cur_cd = 'O2' THEN 1 ELSE NULL END) AS CodeO2Cnt,"
            + " COUNT(CASE WHEN group_cur_cd = 'O3' THEN 1 ELSE NULL END) AS CodeO3Cnt,"
            + " COUNT(CASE WHEN group_cur_cd = 'O4' THEN 1 ELSE NULL END) AS CodeO4Cnt,"
            + " COUNT(CASE WHEN group_cur_cd IN ('O5', 'O6', 'O7') THEN 1 ELSE NULL END) AS CodeO5Cnt "
            + "FROM th_attach_file_log "
            + "WHERE access_dt BETWEEN TO_DATE(:startDate, 'YYYY-MM-DD') AND TO_DATE(:endDate, 'YYYY-MM-DD') "
            + "GROUP BY TO_CHAR(access_dt, 'YYYY-MM-DD') "
            + "ORDER BY log_dt asc", nativeQuery = true)
    List<Object[]> selectCountFile(@Param("startDate") String startDate, @Param("endDate") String endDate);


}
