<?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="mailAccountFile">

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

  <select id="getMailAccountFileList" parameterType="map" resultType="map">
    SELECT
        ID
        , COMPANY_CODE
        , NAME
        , EMAIL
        , SMTP_HOST
        , SMTP_PORT
        , SMTP_SECURE
        , SMTP_USERNAME
        , DAILY_LIMIT
        , STATUS
        , CREATED_DATE
        , UPDATED_DATE
    FROM MAIL_ACCOUNT_FILE
    WHERE 1=1
    <include refid="common.companyCodeFilter"/>
    <include refid="mailAccountFileSearchCondition"/>
    <include refid="common.dynamicOrderBy"/>
    <include refid="common.pagination"/>
  </select>

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

  <select id="getMailAccountFileInfo" parameterType="map" resultType="map">
    SELECT
        ID
        , COMPANY_CODE
        , NAME
        , EMAIL
        , SMTP_HOST
        , SMTP_PORT
        , SMTP_SECURE
        , SMTP_USERNAME
        , SMTP_PASSWORD
        , DAILY_LIMIT
        , STATUS
        , CREATED_DATE
        , UPDATED_DATE
    FROM MAIL_ACCOUNT_FILE
    WHERE ID = #{id}
    <include refid="common.companyCodeFilter"/>
  </select>

  <insert id="insertMailAccountFile" parameterType="map" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO MAIL_ACCOUNT_FILE (
        COMPANY_CODE
        , NAME
        , EMAIL
        , SMTP_HOST
        , SMTP_PORT
        , SMTP_SECURE
        , SMTP_USERNAME
        , SMTP_PASSWORD
        , DAILY_LIMIT
        , STATUS
        , CREATED_DATE
        , UPDATED_DATE
    ) VALUES (
        #{company_code}
        , #{name,           jdbcType=VARCHAR}
        , #{email,          jdbcType=VARCHAR}
        , #{smtp_host,       jdbcType=VARCHAR}
        , #{smtp_port,       jdbcType=INTEGER}
        , #{smtp_secure,     jdbcType=BOOLEAN}
        , #{smtp_username,   jdbcType=VARCHAR}
        , #{smtp_password,   jdbcType=VARCHAR}
        , #{daily_limit,     jdbcType=INTEGER}
        , #{status,         jdbcType=VARCHAR}
        , NOW()
        , NOW()
    )
  </insert>

  <update id="updateMailAccountFile" parameterType="map">
    UPDATE MAIL_ACCOUNT_FILE
    SET
        NAME          = COALESCE(#{name,         jdbcType=VARCHAR}, NAME)
        , EMAIL         = COALESCE(#{email,        jdbcType=VARCHAR}, EMAIL)
        , SMTP_HOST     = COALESCE(#{smtp_host,     jdbcType=VARCHAR}, SMTP_HOST)
        , SMTP_PORT     = COALESCE(#{smtp_port,     jdbcType=INTEGER}, SMTP_PORT)
        , SMTP_SECURE   = COALESCE(#{smtp_secure,   jdbcType=BOOLEAN}, SMTP_SECURE)
        , SMTP_USERNAME = COALESCE(#{smtp_username, jdbcType=VARCHAR}, SMTP_USERNAME)
        , SMTP_PASSWORD = COALESCE(#{smtp_password, jdbcType=VARCHAR}, SMTP_PASSWORD)
        , DAILY_LIMIT   = COALESCE(#{daily_limit,   jdbcType=INTEGER}, DAILY_LIMIT)
        , STATUS        = COALESCE(#{status,       jdbcType=VARCHAR}, STATUS)
        , UPDATED_DATE  = NOW()
    WHERE ID = #{id}
    <include refid="common.companyCodeFilter"/>
  </update>

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

</mapper>
