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

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.cctv.TbCctvCtlrSttsHs;
import com.its.op.entity.its.cctv.TbCctvCtlrSttsHsKey;
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 TbCctvCtlrSttsHsRepository extends JpaRepository<TbCctvCtlrSttsHs, TbCctvCtlrSttsHsKey>, JpaSpecificationExecutor<TbCctvCtlrSttsHs> {

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

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

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

    @Query("select p from TbCctvCtlrSttsHs p inner join fetch p.cctv cctv " +
           " where p.crtnDt between :fromDt and :toDt " +
           "   and p.cmncSttsCd <> 'CMS0' " +
           "   and p.cctvCtlrNmbr in :ids")
    List<TbCctvCtlrSttsHs> 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_CCTV_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 TbCctvCtlrSttsHs p inner join fetch p.cctv cctv " +
            " where p.crtnDt between :fromDt and :toDt " +
            "   and p.cmncSttsCd = 'CMS1' ")
    List<TbCctvCtlrSttsHs> findAllReportError(@Param("fromDt") String fromDt, @Param("toDt") String toDt);

    @Query(value =
            "SELECT A.CCTV_CTLR_NMBR AS fcltNmbr, A.CCTV_CTLR_ID AS fcltId, A.ISTL_LCTN_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_CCTV_CTLR A, " +
                    "             (SELECT CCTV_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_CCTV_STTS_HS " +
                    "              WHERE CRTN_DT BETWEEN :fromDt AND :toDt " +
                    "                AND CMNC_STTS_CD IN ('CMS1', 'CMS2') " +
                    "              GROUP BY CCTV_CTLR_NMBR) B " +
                    "        WHERE A.CCTV_CTLR_NMBR = B.CCTV_CTLR_NMBR(+)"
            , nativeQuery = true)
    List<FcltSttsStatErrorInf> findAllStatisticsCommErrByDateRange(@Param("fromDt") String fromDt, @Param("toDt") String toDt);

    @Query(value =
            "SELECT 'CCTV' AS fcltType, A.CCTV_CTLR_NMBR AS fcltNmbr, A.CCTV_CTLR_ID AS fcltId, A.ISTL_LCTN_NM AS fcltNm,    " +
            "       B.CONN_STTS_CD AS commSttsCd, B.DOOR_STTS_CD AS doorSttsCd, NVL(B.CBOX_TMPR, 0) AS cboxTmpr, B.CRTN_DT AS crtnDt " +
            "  FROM TB_CCTV_CTLR A,                                                                                          " +
            "       (SELECT CCTV_CTLR_NMBR, CMNC_STTS_CD AS CONN_STTS_CD,                                                    " +
            "               CBOX_DOOR_STTS_CD AS DOOR_STTS_CD, CBOX_TMPR, CRTN_DT                                            " +
            "          FROM TB_CCTV_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.CCTV_CTLR_NMBR = B.CCTV_CTLR_NMBR  "
            , nativeQuery = true)
    List<FcltSttsErrorInf> findAllSttsErrorByDateRange(@Param("fromDt") String fromDt, @Param("toDt") String toDt);

}
