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

import com.its.op.entity.its.vms.TbVmsCtlr;
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 org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Repository
public interface TbVmsCtlrRepository extends JpaRepository<TbVmsCtlr, Long>, JpaSpecificationExecutor<TbVmsCtlr> {

    @Query("select p from TbVmsCtlr p inner join fetch p.type tp left outer join fetch p.state state")
    List<TbVmsCtlr> findAll();

    @Query("select p from TbVmsCtlr p inner join fetch p.type tp left outer join fetch p.state state where p.delYn = 'N'")
    List<TbVmsCtlr> findAllList();

    @Query("select count(p.vmsCtlrNmbr) as total from TbVmsCtlr p where p.delYn = 'N'")
    Integer findAllListCount();

    @Query("select p from TbVmsCtlr p inner join fetch p.type tp left outer join fetch p.state state where p.vmsCtlrNmbr in :vmsIds and p.delYn = 'N'")
    List<TbVmsCtlr> findAllListByIds(@Param("vmsIds") List<Long> vmsIds);

    @Query("select p from TbVmsCtlr p inner join fetch p.type tp left outer join fetch p.state state where p.vmsTypeCd = :vmsTypeCd and p.delYn = 'N'")
    List<TbVmsCtlr> findAllListByTypeCd(@Param("vmsTypeCd") String vmsTypeCd);

    @Query(value = "SELECT NVL(MAX(VMS_CTLR_NMBR), 1000) + 1 AS NEWID FROM TB_VMS_CTLR", nativeQuery = true)
    Long getNewNmbr();

    @Transactional
    @Modifying
    @Query(value =
            "UPDATE TB_VMS_CTLR              " +
            "   SET PANL_ON_TIME  = :onTime, " +
            "       PANL_OFF_TIME = :offTime " +
            " WHERE DEL_YN = 'N'             ",
            nativeQuery = true)
    Integer updatePanlOnOffTime(@Param("onTime") String onTime, @Param("offTime") String offTime);

    @Transactional
    @Modifying
    @Query(value =
            "UPDATE TB_VMS_CTLR              " +
            "   SET PANL_ON_TIME  = :onTime, " +
            "       PANL_OFF_TIME = :offTime " +
            " WHERE VMS_CTLR_NMBR = :id      ",
            nativeQuery = true)
    void updateOnOffTime(@Param("id") Long id, @Param("onTime") String onTime, @Param("offTime") String offTime);

    @Transactional
    @Modifying
    @Query(value =
            "UPDATE TB_VMS_CTLR                                  " +
                    "   SET FAN_RUN_TMPR  = :fanRunTmpr,    " +
                    "       HETR_RUN_TMPR = :hetrRunTmpr, " +
                    "       PANL_ON_TIME  = :onTime,  " +
                    "       PANL_OFF_TIME = :offTime  " +
                    " WHERE VMS_CTLR_NMBR = :id                  ",
            nativeQuery = true)
    void updateParam(@Param("id") Long id, @Param("fanRunTmpr") Integer fanRunTmpr, @Param("hetrRunTmpr") Integer hetrRunTmpr, @Param("onTime") String onTime, @Param("offTime") String offTime);

    @Transactional
    @Modifying
    @Query(value =
            "UPDATE TB_VMS_CTLR              " +
            "   SET OPER_MODE     = :oprMode " +
            " WHERE VMS_CTLR_NMBR = :id      ",
            nativeQuery = true)
    void updateOprMode(@Param("id") Long id, @Param("oprMode") String oprMode);

}
