package com.gkhy.labRiskManage.domain.experiment.repository.jpa;

import com.gkhy.labRiskManage.domain.basic.entity.BasicExperimentDevice;
import com.gkhy.labRiskManage.domain.experiment.entity.ExperimentInfo;
import com.gkhy.labRiskManage.domain.experiment.model.dto.ExperimentInfoDTO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
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 org.springframework.stereotype.Repository;

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

/**
 * 实验信息
 */
@Repository
public interface ExperimentInfoRepository extends JpaRepository<ExperimentInfo, Long>, JpaSpecificationExecutor<ExperimentInfo> {

    @Query(value = "SELECT count(1) FROM experiment_info e WHERE DATE_FORMAT(e.create_time,'%Y-%m-%d') = :time",
            nativeQuery = true)
    Integer countByCreateTime(@Param("time") String time);

    @Query(value = "SELECT * FROM experiment_info e WHERE e.id = :id and e.delete_status = 0" ,nativeQuery = true)
    ExperimentInfo getExperimentInfoById(@Param("id") Long id);

    @Query(value = "SELECT * FROM experiment_info e WHERE e.id in :idList and e.delete_status = 0" ,nativeQuery = true)
    List<ExperimentInfo> getExperimentInfoByIds(List<Long> idList);

    @Query(value = "SELECT * FROM experiment_info e WHERE e.experiment_name = :experimentName and e.delete_status = 0" ,nativeQuery = true)
    ExperimentInfo getExperimentInfoByName(@Param("experimentName") String experimentName);
    /**
     * 实验信息 - 修改实验评估状态
     */
    @Query(value = "update experiment_info t set t.stage = :stage where t.id = :id and t.delete_status = 0" ,nativeQuery = true)
    ExperimentInfo updateExperimentStage(Long id, Integer stage);

    @Query(value = "select e.* from experiment_info e LEFT JOIN risk_assess_plan r on e.id = r.experiment_id  where e.delete_status = 0 and e.status = 2 and e.stage in(1,2) and e.liability_user_id = :currentUserId or r.evaluate_user_id = :currentUserId or r.identification_user_id = :currentUserId group by e.id",nativeQuery = true)
    List<ExperimentInfo> getExperimentInfoList(Long currentUserId);

    @Query(value = "select * from experiment_info e where e.delete_status = 0 and e.liability_user_id = :currentUserId ",nativeQuery = true)
    List<ExperimentInfo> getExperimentByUser(Long currentUserId);
}