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

import com.its.op.dto.its.common.SttsHsErrCntInf;
import com.its.op.dto.its.facility.FcltSttsErrorInf;
import com.its.op.dto.its.report.ReportFcltSummaryInf;
import com.its.op.dto.its.statistics.FcltSttsStatErrorInf;
import com.its.op.entity.its.vms.TbVmsCtlrSttsHs;
import com.its.op.entity.its.vms.TbVmsCtlrSttsHsKey;
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 TbVmsCtlrSttsHsRepository extends JpaRepository<TbVmsCtlrSttsHs, TbVmsCtlrSttsHsKey>, JpaSpecificationExecutor<TbVmsCtlrSttsHs> {

    @Query("select p from TbVmsCtlrSttsHs p inner join fetch p.vms where p.crtnDt between :fromDt and :toDt and p.vmsCtlrNmbr in :ids")
    List<TbVmsCtlrSttsHs> findAllByDateRange(@Param("fromDt") String fromDt, @Param("toDt") String toDt, @Param("ids") List<Long> ids);

    @Query("select p.vmsCtlrNmbr as nmbr, count(p.vmsCtlrNmbr) as errCnt " +
            "  from TbVmsCtlrSttsHs p " +
            " where p.crtnDt     >= :fromDt " +
            "   and p.cmncSttsCd <> 'CMS0' " +
            " group by p.vmsCtlrNmbr")
    List<SttsHsErrCntInf> findAllCommErrorCount(@Param("fromDt") String fromDt);

    @Query("select p from TbVmsCtlrSttsHs p inner join fetch p.vms " +
           " where p.crtnDt between :fromDt and :toDt " +
           "   and (p.cmncSttsCd <> 'CMS0' or p.cboxDoorSttsCd <> 'CDS0' or p.cboxTmpr > 40)" +
           "   and p.vmsCtlrNmbr in :ids")
    List<TbVmsCtlrSttsHs> findAllFailByDateRange(@Param("fromDt") String fromDt, @Param("toDt") String toDt, @Param("ids") List<Long> ids);

    @Query(value =
            "SELECT NVL(SUM(CMS0CNT), 0) AS cms0Cnt, NVL(SUM(CMS1CNT), 0) AS cms1Cnt " +
            "  FROM (SELECT DECODE(CMNC_STTS_CD, 'CMS0', 1, 0) AS CMS0CNT, " +
            "               DECODE(CMNC_STTS_CD, 'CMS0', 0, 1) AS CMS1CNT  " +
            "          FROM TB_VMS_CTLR_STTS_HS " +
            "         WHERE CRTN_DT BETWEEN :fromDt AND :toDt" +
            "       )", nativeQuery = true)
    ReportFcltSummaryInf findAllReportSummary(@Param("fromDt") String fromDt, @Param("toDt") String toDt);


    @Query("select p from TbVmsCtlrSttsHs p inner join fetch p.vms " +
            " where p.crtnDt between :fromDt and :toDt " +
            "   and p.cmncSttsCd = 'CMS1' ")
    List<TbVmsCtlrSttsHs> findAllReportError(@Param("fromDt") String fromDt, @Param("toDt") String toDt);

    @Query(value =
            "SELECT A.VMS_CTLR_NMBR AS fcltNmbr, A.VMS_CTLR_ID AS fcltId, A.VMS_NM AS fcltNm, " +
            "               NVL(B.CNT_CMS1, 0) + NVL(B.CNT_CMS2, 0) AS totCnt, " +
            "               NVL(B.CNT_CMS1, 0) AS cms1Cnt, NVL(B.CNT_CMS2, 0) AS cms2Cnt " +
            "        FROM TB_VMS_CTLR A, " +
            "             (SELECT VMS_CTLR_NMBR, " +
            "                     SUM(DECODE(CMNC_STTS_CD, 'CMS1', 1, 0)) AS CNT_CMS1, " +
            "                     SUM(DECODE(CMNC_STTS_CD, 'CMS2', 1, 0)) AS CNT_CMS2 " +
            "              FROM TB_VMS_CTLR_STTS_HS " +
            "              WHERE CRTN_DT BETWEEN :fromDt AND :toDt " +
            "                AND CMNC_STTS_CD IN ('CMS1', 'CMS2') " +
            "              GROUP BY VMS_CTLR_NMBR) B " +
            "        WHERE A.VMS_CTLR_NMBR = B.VMS_CTLR_NMBR(+)"
            , nativeQuery = true)
    List<FcltSttsStatErrorInf> findAllStatisticsCommErrByDateRange(@Param("fromDt") String fromDt, @Param("toDt") String toDt);


    @Query(value =
            "SELECT 'VMS' AS fcltType, A.VMS_CTLR_NMBR AS fcltNmbr, A.VMS_ID AS fcltId, A.VMS_NM AS fcltNm,                  " +
            "       B.CMNC_STTS_CD AS commSttsCd, B.DOOR_STTS_CD AS doorSttsCd, NVL(B.CBOX_TMPR, 0) AS cboxTmpr, B.CRTN_DT AS crtnDt " +
            "  FROM TB_VMS_CTLR A,                                                                                           " +
            "       (SELECT VMS_CTLR_NMBR, CMNC_STTS_CD AS CMNC_STTS_CD,                                                     " +
            "               CBOX_DOOR_STTS_CD AS DOOR_STTS_CD, CBOX_TMPR, CRTN_DT                                        " +
            "          FROM TB_VMS_CTLR_STTS_HS                                                                                   " +
            "         WHERE CRTN_DT BETWEEN :fromDt AND :toDt                                                                " +
            "           AND (CMNC_STTS_CD          <> 'CMS0' OR                                                              " +
            "                CBOX_DOOR_STTS_CD <> 'CDS0' OR                                                              " +
            "                CBOX_TMPR > 40)                                                                                 " +
            "       ) B                                                                                                      " +
            " WHERE A.VMS_CTLR_NMBR = B.VMS_CTLR_NMBR "
            , nativeQuery = true)
    List<FcltSttsErrorInf> findAllSttsErrorByDateRange(@Param("fromDt") String fromDt, @Param("toDt") String toDt);

}
