<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mailTemplateFile">

  <sql id="mailTemplateFileSearchCondition">
    <if test="keyword != null and keyword != ''">
      AND (NAME ILIKE '%' || #{keyword} || '%')
    </if>
    <if test="category != null and category != ''">
      AND CATEGORY = #{category}
    </if>
  </sql>

  <select id="getMailTemplateFileList" parameterType="map" resultType="map">
    SELECT
        ID
        , COMPANY_CODE
        , NAME
        , SUBJECT
        , CATEGORY
        , CREATED_DATE
        , UPDATED_DATE
    FROM MAIL_TEMPLATE_FILE
    WHERE 1=1
    <include refid="common.companyCodeFilter"/>
    <include refid="mailTemplateFileSearchCondition"/>
    <include refid="common.dynamicOrderBy"/>
    <include refid="common.pagination"/>
  </select>

  <select id="getMailTemplateFileListCnt" parameterType="map" resultType="int">
    SELECT COUNT(*)
    FROM MAIL_TEMPLATE_FILE
    WHERE 1=1
    <include refid="common.companyCodeFilter"/>
    <include refid="mailTemplateFileSearchCondition"/>
  </select>

  <select id="getMailTemplateFileInfo" parameterType="map" resultType="map">
    SELECT
        ID
        , COMPANY_CODE
        , NAME
        , SUBJECT
        , COMPONENTS
        , QUERY_CONFIG
        , RECIPIENT_CONFIG
        , CATEGORY
        , CREATED_DATE
        , UPDATED_DATE
    FROM MAIL_TEMPLATE_FILE
    WHERE ID = #{id}
    <include refid="common.companyCodeFilter"/>
  </select>

  <insert id="insertMailTemplateFile" parameterType="map" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO MAIL_TEMPLATE_FILE (
        COMPANY_CODE
        , NAME
        , SUBJECT
        , COMPONENTS
        , QUERY_CONFIG
        , RECIPIENT_CONFIG
        , CATEGORY
        , CREATED_DATE
        , UPDATED_DATE
    ) VALUES (
        #{company_code}
        , #{name}
        , #{subject}
        , CAST(#{components} AS JSONB)
        , CAST(#{query_config} AS JSONB)
        , CAST(#{recipient_config} AS JSONB)
        , #{category}
        , NOW()
        , NOW()
    )
  </insert>

  <update id="updateMailTemplateFile" parameterType="map">
    UPDATE MAIL_TEMPLATE_FILE
    <set>
      <if test="name != null">NAME = #{name},</if>
      <if test="subject != null">SUBJECT = #{subject},</if>
      <if test="components != null">COMPONENTS = CAST(#{components} AS JSONB),</if>
      <if test="query_config != null">QUERY_CONFIG = CAST(#{query_config} AS JSONB),</if>
      <if test="recipient_config != null">RECIPIENT_CONFIG = CAST(#{recipient_config} AS JSONB),</if>
      <if test="category != null">CATEGORY = #{category},</if>
      UPDATED_DATE = NOW()
    </set>
    WHERE ID = #{id}
    <include refid="common.companyCodeFilter"/>
  </update>

  <delete id="deleteMailTemplateFile" parameterType="map">
    DELETE FROM MAIL_TEMPLATE_FILE
    WHERE ID = #{id}
    <include refid="common.companyCodeFilter"/>
  </delete>

</mapper>
