package com.dbnt.kcscbackend.commonCode.repository;


import com.dbnt.kcscbackend.commonCode.entity.TnCmtEvent;
import com.dbnt.kcscbackend.commonCode.entity.TnCmtOrg;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.query.Param;

import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;
import java.util.Map;


public interface TnCmtEventRepository extends JpaRepository<TnCmtEvent, TnCmtEvent.TnCmtEventId> {

  @Query(value = "CALL sp_add_tn_cmt_event (" +
          ":_evt_type, " +
          ":_up_cmt_seq, " +
          ":_cmt_seq, " +
          ":_evt_title, " +
          ":_evt_location, " +
          ":_evt_contents, " +
          "TO_TIMESTAMP(" +
          "    :_evt_start_dt," +
          "    'YYYYMMDDHH24MISS'" +
          ")::::timestamptz AT TIME ZONE 'UTC', " +
          "TO_TIMESTAMP(" +
          "    :_evt_end_dt," +
          "    'YYYYMMDDHH24MISS'" +
          ")::::timestamptz AT TIME ZONE 'UTC', " +
          ":_modi_id, " +
          ":_evt_seq, " +
          ":_result_count, " +
          ":_result_code, " +
          ":_error_message)",
          nativeQuery = true)
  Map<String, Object> spAddTnCmtEvent(
          @Param("_evt_type")         String        evtType,
          @Param("_up_cmt_seq")       Integer       upCmtSeq,
          @Param("_cmt_seq")          Integer       cmtSeq,
          @Param("_evt_title")        String        evtTitle,
          @Param("_evt_location")     String        evtLocation,
          @Param("_evt_contents")     String        evtContents,
          @Param("_evt_start_dt")     String        evtStartDt,
          @Param("_evt_end_dt")       String        evtEndDt,
          @Param("_modi_id")          String        modiId,
          @Param("_evt_seq")          Integer       evtSeq,
          @Param("_result_count")     Integer       resultCount,
          @Param("_result_code")      String        resultCode,
          @Param("_error_message")    String        errorMessage
  );


  @Query(value = "CALL sp_update_tn_cmt_event (" +
          ":_evt_seq, " +
          ":_evt_type, " +
          ":_up_cmt_seq, " +
          ":_cmt_seq, " +
          ":_evt_title, " +
          ":_evt_location, " +
          ":_evt_contents, " +
          "TO_TIMESTAMP(" +
          "    :_evt_start_dt," +
          "    'YYYYMMDDHH24MISS'" +
          ")::::timestamptz AT TIME ZONE 'UTC', " +
          "TO_TIMESTAMP(" +
          "    :_evt_end_dt," +
          "    'YYYYMMDDHH24MISS'" +
          ")::::timestamptz AT TIME ZONE 'UTC', " +
          ":_modi_id, " +
          ":_result_count, " +
          ":_result_code, " +
          ":_error_message)",
          nativeQuery = true)
  Map<String, Object> spUpdateTnCmtEvent(
          @Param("_evt_seq")          Integer       evtSeq,
          @Param("_evt_type")         String        evtType,
          @Param("_up_cmt_seq")       Integer       upCmtSeq,
          @Param("_cmt_seq")          Integer       cmtSeq,
          @Param("_evt_title")        String        evtTitle,
          @Param("_evt_location")     String        evtLocation,
          @Param("_evt_contents")     String        evtContents,
          @Param("_evt_start_dt")     String        evtStartDt,
          @Param("_evt_end_dt")       String        evtEndDt,
          @Param("_modi_id")          String        modiId,
          @Param("_result_count")     Integer       resultCount,
          @Param("_result_code")      String        resultCode,
          @Param("_error_message")    String        errorMessage
  );



  @Query(value = "CALL sp_delete_tn_cmt_event (" +
          ":_evt_seq, " +
          ":_modi_id, " +
          ":_result_count, " +
          ":_result_code, " +
          ":_error_message)",
          nativeQuery = true)
  Map<String, Object> spDeleteTnCmtEvent(
          @Param("_evt_seq")          Integer       evtSeq,
          @Param("_modi_id")          String        modiId,
          @Param("_result_count")     Integer       resultCount,
          @Param("_result_code")      String        resultCode,
          @Param("_error_message")    String        errorMessage
  );

  @Procedure("sp_is_valid_tn_cmt_event_id")
  int spIsValidTnCmtEventId( Integer       evtSeq );




  TnCmtEvent findByEvtSeq(Long evtSeq);

  @Query(value = "\n" +
          "SELECT" + " \n" +
          "   *" + " \n" +
          "FROM" + " \n" +
          "   tn_cmt_event tce" + " \n" +
          "WHERE" + " " +
          "   tce.use_yn = 'Y'" + " \n" +
          "   AND" + " \n" +
          "   TO_TIMESTAMP(" + " \n" +
          "     :sSearchDate," + " \n" +
          "     'YYYYMM'" + " \n" +
          "   ) AT TIME ZONE 'UTC+9' <= tce.evt_start_dt" + " \n" +
          "   AND" + " \n" +
          "   tce.evt_start_dt < TO_TIMESTAMP(" + " \n" +
          "     :sSearchDate," + " \n" +
          "     'YYYYMM'" + " \n" +
          "   ) AT TIME ZONE 'UTC+9' + INTERVAL '1 MONTH' ;\n",
          nativeQuery = true)
  List<TnCmtEvent> getByYyyyMm(
          @Param("sSearchDate")         String        sSearchDate
  );




}
