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

import com.its.op.entity.its.facility.TbFcltInfr;
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.util.List;

@Repository
public interface TbFcltInfrRepository extends JpaRepository<TbFcltInfr, Long>, JpaSpecificationExecutor<TbFcltInfr> {

    @Query("select p from TbFcltInfr p left outer join p.mnfcCmpy  a " +
            "                          left outer join p.istlCmpy  b " +
            "                          left outer join p.maintCmpy c " +
            "                          left outer join p.mainUser  d " +
            "                          left outer join p.subUser   e")
    List<TbFcltInfr> findAll();

    @Query("select p from TbFcltInfr p left outer join p.mnfcCmpy  a " +
           "                           left outer join p.istlCmpy  b " +
           "                           left outer join p.maintCmpy c " +
           "                           left outer join p.mainUser  d " +
           "                           left outer join p.subUser   e " +
           " where p.delYn = 'N'")
    List<TbFcltInfr> findAllList();

    @Query("select p from TbFcltInfr p left outer join p.mnfcCmpy   a " +
            "                           left outer join p.istlCmpy  b " +
            "                           left outer join p.maintCmpy c " +
            "                           left outer join p.mainUser  d " +
            "                           left outer join p.subUser   e " +
            " where p.delYn = 'N' and p.fcltType = :fcltType")

    List<TbFcltInfr> findAllByFcltType(@Param("fcltType") String fcltType);

    @Query(value = "SELECT NVL(MAX(FCLT_NMBR), 0) + 1 AS NEWID FROM TB_FCLT_INFR", nativeQuery = true)
    Long getNewNmbr();

}
