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

import com.its.op.dto.its.vms.TbVmsFormDsplTxtInf;
import com.its.op.entity.its.vms.TbVmsForm;
import com.its.op.entity.its.vms.TbVmsFormObjectDetlInf;
import com.its.op.entity.its.vms.TbVmsFormSimple;
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 TbVmsFormRepository extends JpaRepository<TbVmsForm, Integer>, JpaSpecificationExecutor<TbVmsForm> {

    @Query("select p from TbVmsForm p order by p.vmsFormTypeCd, p.vmsFormId")
    List<TbVmsForm> findAll();

    @Query("select p from TbVmsForm p where p.vmsTypeCd = :vmsTypeCd order by p.vmsFormTypeCd, p.vmsFormId")
    List<TbVmsForm> findAllByVmsType(@Param("vmsTypeCd") String vmsTypeCd);

    @Query("select p from TbVmsFormSimple p where p.vmsTypeCd = :vmsTypeCd order by p.vmsFormTypeCd, p.vmsFormId")
    List<TbVmsFormSimple> findAllByVmsTypeSimple(@Param("vmsTypeCd") String vmsTypeCd);

    @Query(value =
            "SELECT A.VMS_FORM_ID AS vmsFormId,                                                             " +
            "       LISTAGG(B.VMS_DSPL_TXT, ' ') WITHIN GROUP (ORDER BY B.VMS_FORM_OBJECT_ID) AS vmsDsplTxt " +
            "  FROM TB_VMS_FORM A, TB_VMS_FORM_OBJECT B                                                     " +
            " WHERE A.VMS_TYPE_CD = :vmsTypeCd " +
            "   AND A.VMS_FORM_ID = B.VMS_FORM_ID " +
            "   AND b.VMS_FORM_OBJECT_TYPE_CD = '0'                                                         " +
            " GROUP BY A.VMS_FORM_ID ", nativeQuery = true)
    List<TbVmsFormDsplTxtInf> findAllByVmsTypeFormDsplTxt(@Param("vmsTypeCd") String vmsTypeCd);

    @Query("select p from TbVmsForm p where p.vmsTypeCd = :vmsTypeCd and p.vmsFormTypeCd in :formTypeCd order by p.vmsFormTypeCd, p.vmsFormId")
    List<TbVmsForm> findAllByVmsTypeAndFormType(@Param("vmsTypeCd") String vmsTypeCd, @Param("formTypeCd") List<Integer> formTypeCd);

    @Query("select a.vmsFormObjectId as vmsFormObjectId, " +
        "      a.vmsFormId as vmsFormId, " +
        "      a.vmsFormDsplRow as vmsFormDsplRow, " +
        "      a.vmsFormDsplClmn as vmsFormDsplClmn, " +
        "      a.vmsFormObjectTypeCd as vmsFormObjectTypeCd, " +
        "      a.vmsFontNameCd as vmsFontNameCd, " +
        "      a.vmsFontColrCd as vmsFontColrCd, " +
        "      a.vmsFontBold as vmsFontBold, " +
        "      a.vmsFontSize as vmsFontSize, " +
        "      a.vmsFontAlign as vmsFontAlign, " +
        "      a.vmsDsplTxt as vmsDsplTxt, " +
        "      a.vmsDsplFigr as vmsDsplFigr, " +
        "      a.vmsDsplXcrdn as vmsDsplXcrdn, " +
        "      a.vmsDsplYcrdn as vmsDsplYcrdn, " +
        "      a.vmsDsplWidth as vmsDsplWidth, " +
        "      a.vmsDsplHeight as vmsDsplHeight, " +
        "      a.vmsDsplBlinking as vmsDsplBlinking, " +
        "      a.vmsDsplBkcolor as vmsDsplBkcolor, " +
        "      a.vmsDsplSize as vmsDsplSize, " +
        "      a.trfcFillCd as trfcFillCd, " +
        "      a.symbLibNmbr as symbLibNmbr, " +
        "      a.vmsIfscId as vmsIfscId, " +
        "      c.vmsFontName as vmsFontName,  " +
        "      b.imagData as imagData,  " +
        "      b.symbExpl as symbExpl, " +
        "      d.vmsTypeCd as vmsTypeCd, " +
        "      d.vmsFormTypeCd as vmsFormTypeCd, " +
        "      d.vmsFormColrCd as vmsFormColrCd, " +
        "      d.vmsFormNm as vmsFormNm " +
        "  from      TbVmsFormObject a " +
        "  left join TbVmsSymbLib    b " +
        "         on a.symbLibNmbr = b.symbLibNmbr " +
        "  left join TbVmsFontName   c " +
        "         on a.vmsFontNameCd = c.vmsFontNameCd " +
        "       join TbVmsForm       d " +
        "         on a.vmsFormId = d.vmsFormId " +
        " where d.vmsFormId = :formId" +
        " order by a.vmsFormObjectId asc")
    List<TbVmsFormObjectDetlInf> findByVmsFormObject(@Param("formId") Integer formId);

    @Query(value = "SELECT NVL(MAX(VMS_FORM_ID), 9999)+1 AS NEWID FROM TB_VMS_FORM", nativeQuery = true)
    Integer getNewVmsFormId();

    @Transactional
    @Modifying
    @Query(value =
            "INSERT INTO TB_VMS_FORM (VMS_FORM_ID, " +
            "              VMS_TYPE_CD,            " +
            "              VMS_FORM_TYPE_CD,       " +
            "              VMS_FORM_COLR_CD,       " +
            "              VMS_FORM_NM,            " +
            "              VMS_FORM_EXPL,          " +
            "              VMS_FORM_DSPL_DRCT_CD,  " +
            "              VMS_FORM_DSPL_MTHD_CD,  " +
            "              SYMB_LIB_NMBR,          " +
            "              VMS_FORM_IMAG,          " +
            "              UPDT_DT,                " +
            "              VALID_YN)               " +
            "SELECT :newFormId,                    " +
            "       VMS_TYPE_CD,                   " +
            "       VMS_FORM_TYPE_CD,              " +
            "       VMS_FORM_COLR_CD,              " +
            "       :vmsFormNm,                    " +
            "       VMS_FORM_EXPL,                 " +
            "       VMS_FORM_DSPL_DRCT_CD,         " +
            "       VMS_FORM_DSPL_MTHD_CD,         " +
            "       SYMB_LIB_NMBR,                 " +
            "       VMS_FORM_IMAG,                 " +
            "       TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'), " +
            "       VALID_YN                       " +
            "  FROM TB_VMS_FORM                    " +
            " WHERE VMS_FORM_ID = :orgFormId       ",
            nativeQuery = true)
    void copyForm(@Param("newFormId") Integer newFormId, @Param("orgFormId") Integer orgFormId, @Param("vmsFormNm") String vmsFormNm);

}
