package com.its.op.dao.repository.its.facility;

import com.its.op.entity.its.facility.TbFcltFail;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface TbFcltFailRepository extends JpaRepository<TbFcltFail, String>, JpaSpecificationExecutor<TbFcltFail> {

    @Query("select p from TbFcltFail p inner join fetch p.fclt f")
    List<TbFcltFail> findAll();

    @Query("select p from TbFcltFail p inner join fetch p.fclt f where p.useYn = 'Y'")
    List<TbFcltFail> findAllList();

    @Query(value = "SELECT TO_CHAR(sysdate,'YYYYMM') || NVL(MAX(SUBSTR(FAIL_NO, 7, 4)), '0000')+1  AS NEWID " +
                   "  FROM TB_FCLT_FAIL                                                                     " +
                   " WHERE SUBSTR(FAIL_NO, 1, 6) = TO_CHAR(sysdate, 'YYYYMM')"
            , nativeQuery = true)
    String getNewNmbr();

    @Query("select p from TbFcltFail p where p.strDt between :fromDt and :toDt")
    List<TbFcltFail> findListAll(@Param("fromDt") String fromDt, @Param("toDt") String toDt);

    @Query("select p from TbFcltFail p where p.stepCd <> 'ACT2' and p.strDt between :fromDt and :toDt")
    List<TbFcltFail> findListContinuous(@Param("fromDt") String fromDt, @Param("toDt") String toDt);

    @Modifying
    @Query(value = "UPDATE TB_FCLT_FAIL SET STEP_CD = :stepCd WHERE FAIL_NO = :failNo", nativeQuery = true)
    Integer updateStepCd(@Param("failNo") String failNo, @Param("stepCd") String stepCd);

    @Modifying
    @Query(value = "UPDATE TB_FCLT_FAIL SET STEP_CD = 'ACT2', END_DT = :endDt WHERE FAIL_NO = :failNo", nativeQuery = true)
    Integer updateComplete(@Param("failNo") String failNo, @Param("endDt") String endDt);
}
