package com.spring.domain.schedule.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.spring.domain.schedule.dto.BatchJobAverageDurationProjection;
import com.spring.domain.schedule.dto.BatchJobExecutionProjection;
import com.spring.domain.schedule.dto.BatchJobHourProjection;
import com.spring.domain.schedule.dto.BatchJobStatusCountProjection;
import com.spring.domain.schedule.entity.BatchJobExecution;

public interface BatchJobExecutionRepository extends JpaRepository<BatchJobExecution, Long> {
        
        @Query("SELECT bji.jobName AS jobName, " +
                "bje.startTime AS startTime, " +
                "bje.endTime AS endTime " +
                "FROM BatchJobExecution bje " +
                "JOIN bje.jobInstance bji " +
                "WHERE bje.status = 'COMPLETED' " +
                "AND FUNCTION('YEAR', bje.startTime) = :year " +
                "AND FUNCTION('MONTH', bje.startTime) = :month " +
                "ORDER BY bje.startTime")
        List<BatchJobAverageDurationProjection> findJobAverageDurations(@Param("year") int year, @Param("month") int month);

        @Query("SELECT bji.jobName AS jobName, bje.status AS status, COUNT(bje) AS count " +
                "FROM BatchJobExecution bje " +
                "JOIN bje.jobInstance bji " +
                "WHERE FUNCTION('YEAR', bje.startTime) = :year " +
                "AND FUNCTION('MONTH', bje.startTime) = :month " +
                "GROUP BY bji.jobName, bje.status " +
                "ORDER BY bji.jobName, bje.status")
        List<BatchJobStatusCountProjection> findJobStatusCounts(@Param("year") int year, @Param("month") int month);

        @Query("SELECT bje.startTime AS executionDate, " +
                "bji.jobName AS jobName, " +
                "COUNT(bje) AS executionCount " +
                "FROM BatchJobExecution bje " +
                "JOIN bje.jobInstance bji " +
                "WHERE FUNCTION('YEAR', bje.startTime) = :year " +
                "AND FUNCTION('MONTH', bje.startTime) = :month " +
                "GROUP BY bje.startTime, bji.jobName " +
                "ORDER BY bje.startTime, bji.jobName")
        List<BatchJobExecutionProjection> findJobExecutionSummaryDays(@Param("year") int year, @Param("month") int month);

        @Query("SELECT bji.jobName AS jobName, " +
                "FUNCTION('HOUR', bje.startTime) AS hour, " +
                "COUNT(bje) AS count " +
                "FROM BatchJobExecution bje " +
                "JOIN bje.jobInstance bji " +
                "WHERE FUNCTION('YEAR', bje.startTime) = :year " +
                "AND FUNCTION('MONTH', bje.startTime) = :month " +
                "GROUP BY bji.jobName, FUNCTION('HOUR', bje.startTime) " +
                "ORDER BY bji.jobName, hour")
        List<BatchJobHourProjection> findHourlyJobExecutionDistribution(@Param("year") int year, @Param("month") int month);

}
