package cn.com.qmth.stmms.ms.core.repository;

import cn.com.qmth.stmms.ms.core.domain.TaskPublishSetting;
import cn.com.qmth.stmms.ms.core.vo.Subject;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

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

@Repository
public interface TaskPublishSettingRepo extends JpaRepository<TaskPublishSetting, Long> {
    List<TaskPublishSetting> findByWorkIdAndSubject(Long workId, String subject);

    @Query(value = "select b.workId, b.subject, a.code,ifnull(b.totalCount,0) totalCount, ifnull(b.successCount,0) successCount, ifnull(b.waitCount,0) waitCount from level a " +
            "left join (select p.work_id workId, p.subject, p.level, count(1) as totalCount,SUM(IF(p.score is not null, 1, 0)) successCount,SUM(if(p.score is null, 1, 0)) waitCount from paper p " +
            "where p.work_id = ?1 and p.subject = ?2 and p.is_test = ?3 and p.level is not null and p.is_missing = false and p.is_active = true group by p.work_id, p.subject, p.level) b on a.work_id = b.workId and a.code = b.level  " +
            "where a.work_id = 32 order by a.code", nativeQuery = true)
    List<Map> findByWorkIdAndSubjectAndTest(Long workId, String subject, int ordinal);

    @Query("select max(t.publishCount) from TaskPublishSetting t where t.workId = ?1 and t.subject = ?2")
    Integer maxPublishCount(Long workId, String subject);

    @Query(value = "SELECT work_id workId, subject, code, sum(total_count) totalCount, sum(success_count) successCount, sum(wait_count) waitCount FROM task_publish_setting where work_id = ?1 and subject = ?2 and publish_count = ?3 group by work_id, subject, code", nativeQuery = true)
    List<Object[]> findAllByWorkIdAndSubjectAndPublishCount(Long workId, String subject, int maxCount);

    List<TaskPublishSetting> findByWorkIdAndQuestionIdAndSubjectAndPublishCount(Long workId, Long questionId, String name, int maxCount);

    @Query("select max(t.publishCount) from TaskPublishSetting t where t.workId = ?1 and t.questionId = ?2 and t.subject = ?3")
    Integer maxPublishCountByQuestionId(Long workId, Long questionId, String name);

    List<TaskPublishSetting> findByWorkIdAndSubjectAndPublishCount(Long workId, String name, int max);
}
