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

import com.its.op.dto.its.analysis.TbClctSystSttsHsClctInf;
import com.its.op.dto.its.analysis.TbClctSystSttsHsPrcnInf;
import com.its.op.entity.its.link.TbClctSystSttsHs;
import com.its.op.entity.its.link.TbClctSystSttsHsKey;
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 TbClctSystSttsHsRepository extends JpaRepository<TbClctSystSttsHs, TbClctSystSttsHsKey>, JpaSpecificationExecutor<TbClctSystSttsHs> {

    @Query("select p from TbClctSystSttsHs p")
    List<TbClctSystSttsHs> findAll();

    @Query( "SELECT p.prcnDt AS prcnDt, " +
            "      nvl(sum(case when p.clctSystCd = 'OPER01' then p.clctNum end), 0) AS oper01, " +
            "      nvl(sum(case when p.clctSystCd = 'UTIS01' then p.clctNum end), 0) AS utis01, " +
            "      nvl(sum(case when p.clctSystCd = 'UTIS02' then p.clctNum end), 0) AS utis02, " +
            "      nvl(sum(case when p.clctSystCd = 'VDS01'  then p.clctNum end), 0) AS vds01,  " +
            "      nvl(sum(case when p.clctSystCd = 'DSRC01' then p.clctNum end), 0) AS dsrc01, " +
            "      nvl(sum(case when p.clctSystCd = 'GITS01' then p.clctNum end), 0) AS gits01, " +
            "      nvl(sum(case when p.clctSystCd = 'MOCT01' then p.clctNum end), 0) AS moct01  " +
            "  FROM TbClctSystSttsHs p " +
            " WHERE p.prcnDt BETWEEN :fromDt AND :toDt " +
            " GROUP BY p.prcnDt")
    List<TbClctSystSttsHsClctInf> findAllClctSystByDateRange(@Param("fromDt") String fromDt, @Param("toDt") String toDt);

    @Query(value =
            "SELECT a.prcnDt as prcnDt, " +
            "       a.oper01 as oper01, " +
            "       a.utis01 as utis01, " +
            "       a.utis02 as utis02, " +
            "       a.vds01  as vds01,  " +
            "       a.dsrc01 as dsrc01, " +
            "       a.gits01 as gits01, " +
            "       a.moct01 as moct01, " +
            "       b.missNum as missNum, " +
            "       b.adjsNum as adjsNum, " +
            "       b.smthNum as smthNum " +
            "  FROM " +
            "(SELECT p.PRCN_DT AS prcnDt, " +
            "      nvl(sum(case when p.CLCT_SYST_CD = 'OPER01' then p.FUSN_NUM end), 0) AS oper01, " +
            "      nvl(sum(case when p.CLCT_SYST_CD = 'UTIS01' then p.FUSN_NUM end), 0) AS utis01, " +
            "      nvl(sum(case when p.CLCT_SYST_CD = 'UTIS02' then p.FUSN_NUM end), 0) AS utis02, " +
            "      nvl(sum(case when p.CLCT_SYST_CD = 'VDS01'  then p.FUSN_NUM end), 0) AS vds01,  " +
            "      nvl(sum(case when p.CLCT_SYST_CD = 'DSRC01' then p.FUSN_NUM end), 0) AS dsrc01, " +
            "      nvl(sum(case when p.CLCT_SYST_CD = 'GITS01' then p.FUSN_NUM end), 0) AS gits01, " +
            "      nvl(sum(case when p.CLCT_SYST_CD = 'MOCT01' then p.FUSN_NUM end), 0) AS moct01  " +
            "  FROM TB_CLCT_SYST_STTS_HS p " +
            " WHERE p.PRCN_DT BETWEEN :fromDt AND :toDt " +
            " GROUP BY p.PRCN_DT ) a, " +
            "(SELECT t.PRCN_DT as prcnDt, t.MISS_NUM as missNum, t.ADJS_NUM as adjsNum, t.SMTH_NUM as smthNum " +
            "  FROM TB_TRAF_PRCN_STAT t " +
            " WHERE t.PRCN_DT BETWEEN :fromDt AND :toDt ) b " +
            " WHERE a.prcnDt = b.prcnDt ",
            nativeQuery = true
    )
    List<TbClctSystSttsHsPrcnInf> findAllPrcnSystByDateRange(@Param("fromDt") String fromDt, @Param("toDt") String toDt);

/*
    @Query( "SELECT a.prcnDt as prcnDt, " +
            "       a.oper01 as oper01, " +
            "       a.utis01 as utis01, " +
            "       a.utis02 as utis02, " +
            "       a.vds01  as vds01, " +
            "       a.moct01 as moct01, " +
            "       b.missNum as missNum, " +
            "       b.adjsNum as adjsNum, " +
            "       b.smthNum as smthNum " +
            "  FROM " +
            "(SELECT p.prcnDt AS prcnDt, " +
            "      nvl(sum(case when p.clctSystCd = 'OPER01' then p.fusnNum end), 0) AS oper01, " +
            "      nvl(sum(case when p.clctSystCd = 'UTIS01' then p.fusnNum end), 0) AS utis01, " +
            "      nvl(sum(case when p.clctSystCd = 'UTIS02' then p.fusnNum end), 0) AS utis02, " +
            "      nvl(sum(case when p.clctSystCd = 'VDS01'  then p.fusnNum end), 0) AS vds01,  " +
            "      nvl(sum(case when p.clctSystCd = 'MOCT01' then p.fusnNum end), 0) AS moct01  " +
            "  FROM TbClctSystSttsHs p " +
            " WHERE p.prcnDt BETWEEN :fromDt AND :toDt " +
            " GROUP BY p.prcnDt ) a, " +
            "(SELECT t.prcnDt as prcnDt, t.missNum as missNum, t.adjsNum as adjsNum, t.smthNum as smthNum " +
            "  FROM TbTrfcPrcnStat t " +
            " WHERE t.prcnDt BETWEEN :fromDt AND :toDt ) b " +
            " WHERE a.prcnDt = b.prcnDt "
    )
*/

    /*@Query(value =
            "SELECT a.prcnDt as prcnDt, " +
                    "       a.oper01 as oper01, " +
                    "       a.utis01 as utis01, " +
                    "       a.utis02 as utis02, " +
                    "       a.vds01  as vds01, " +
                    "       a.moct01 as moct01, " +
                    "       b.missNum as missNum, " +
                    "       b.adjsNum as adjsNum, " +
                    "       b.smthNum as smthNum " +
                    "  FROM " +
                    "(SELECT p.PRCN_DT AS prcnDt, " +
                    "      nvl(sum(case when p.CLCT_SYST_CD = 'OPER01' then p.FUSN_NUM end), 0) AS oper01, " +
                    "      nvl(sum(case when p.CLCT_SYST_CD = 'UTIS01' then p.FUSN_NUM end), 0) AS utis01, " +
                    "      nvl(sum(case when p.CLCT_SYST_CD = 'UTIS02' then p.FUSN_NUM end), 0) AS utis02, " +
                    "      nvl(sum(case when p.CLCT_SYST_CD = 'VDS01'  then p.FUSN_NUM end), 0) AS vds01,  " +
                    "      nvl(sum(case when p.CLCT_SYST_CD = 'MOCT01' then p.FUSN_NUM end), 0) AS moct01  " +
                    "  FROM TB_CLCT_SYST_STTS_HS p " +
                    " WHERE p.PRCN_DT BETWEEN :fromDt AND :toDt " +
                    " GROUP BY p.PRCN_DT ) a, " +
                    "(SELECT t.PRCN_DT as prcnDt, t.MISS_NUM as missNum, t.ADJS_NUM as adjsNum, t.SMTH_NUM as smthNum " +
                    "  FROM TB_TRFC_PRCN_STAT t " +
                    " WHERE t.PRCN_DT BETWEEN :fromDt AND :toDt ) b " +
                    " WHERE a.prcnDt = b.prcnDt ", nativeQuery = true
*/
}
