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

import com.its.op.dto.its.vms.TbVmsDsplSchInf;
import com.its.op.entity.its.vms.TbVmsDsplSch;
import com.its.op.entity.its.vms.TbVmsDsplSchKey;
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 TbVmsDsplSchRepository extends JpaRepository<TbVmsDsplSch, TbVmsDsplSchKey>, JpaSpecificationExecutor<TbVmsDsplSch> {

    @Query("select p from TbVmsDsplSch p inner join fetch p.form form")
    List<TbVmsDsplSch> findAll();

    @Query("select p from TbVmsDsplSch p inner join fetch p.form form where p.vmsCtlrNmbr = :id and p.vmsSchType = :vmsSchType and p.vmsSchFormType = :vmsSchFormType and p.phase = :phase")
    TbVmsDsplSch findScheduleForm(@Param("id") Long id, @Param("vmsSchType") String vmsSchType, @Param("vmsSchFormType") Integer vmsSchFormType, @Param("phase") Integer phase);

    @Modifying
    @Query("delete from TbVmsDsplSch p where p.vmsCtlrNmbr = :vmsCtlrNmbr and p.vmsSchType in :vmsSchType and p.vmsSchFormType in :vmsSchFormType")
    void deleteDataByVmsCtrlTrfcSituTypeCd(@Param("vmsCtlrNmbr") Long vmsCtlrNmbr, @Param("vmsSchType") List<String> vmsSchType, @Param("vmsSchFormType") List<Integer> vmsSchFormType);

    @Query("select p from TbVmsDsplSch p inner join fetch p.form where p.vmsCtlrNmbr = :vmsCtlrNmbr and p.vmsSchType = :vmsSchType and p.vmsSchFormType in :vmsSchFormType")
    List<TbVmsDsplSch> findAllByVmsCtrlTrfcSituTypeCd(@Param("vmsCtlrNmbr") Long vmsCtlrNmbr, @Param("vmsSchType") String vmsSchType, @Param("vmsSchFormType") List<Integer> vmsSchFormType);

    @Query("select nvl(max(p.phase), 0) + 1 as maxPhase from TbVmsDsplSch p where p.vmsCtlrNmbr = :vmsCtlrNmbr and p.vmsSchType = :vmsSchType")
    Integer findMaxPhaseByVmsSchType(@Param("vmsCtlrNmbr") Long vmsCtlrNmbr, @Param("vmsSchType") String vmsSchType);

    @Query("select p from TbVmsDsplSch p inner join fetch p.form where p.vmsSchType = :vmsSchType and p.vmsSchFormType = :vmsSchFormType and p.vmsFormId = :vmsFormId")
    List<TbVmsDsplSch> findAllByAllEditVmsList(@Param("vmsSchType") String vmsSchType, @Param("vmsSchFormType") Integer vmsSchFormType, @Param("vmsFormId") Integer vmsFormId);

    @Query(value =
            "SELECT A.VMS_CTLR_NMBR AS nmbr, A.VMS_SCH_TYPE AS trfc, COUNT(1) AS total, 0 AS used " +
            "  FROM TB_VMS_DSPL_SCH A,                        " +
            "       TB_VMS_FORM     B                         " +
            " WHERE A.VMS_FORM_ID = B.VMS_FORM_ID                       " +
            " GROUP BY A.VMS_CTLR_NMBR, A.VMS_SCH_TYPE             " +
            "UNION ALL " +
            "SELECT A.VMS_CTLR_NMBR AS nmbr, A.VMS_SCH_TYPE AS trfc, 0 AS total, COUNT(1) AS used " +
            "  FROM TB_VMS_DSPL_SCH A,                        " +
            "       TB_VMS_FORM     B                         " +
            " WHERE A.VMS_FORM_ID = B.VMS_FORM_ID                       " +
            "   AND A.USE_YN = 'Y'                                      " +
            "   AND TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') BETWEEN A.DSPL_STRT_HH AND A.DSPL_END_HH " +
            " GROUP BY A.VMS_CTLR_NMBR, A.VMS_SCH_TYPE"
            , nativeQuery = true)
    List<TbVmsDsplSchInf> findAllUsage();

    @Query("select count(p.vmsFormId) from TbVmsDsplSch p where p.vmsFormId = :formId")
    Integer findUsageCountByFormId(@Param("formId") Integer formId);
}
