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

  <sql id="mailReceiveBasicSearchCondition">
    <if test="keyword != null and keyword != ''">
      AND (SUBJECT ILIKE '%' || #{keyword} || '%'
           OR FROM_ADDRESS ILIKE '%' || #{keyword} || '%')
    </if>
    <if test="is_read != null and is_read != ''">
      AND IS_READ = #{is_read}
    </if>
    <if test="start_date != null and start_date != ''">
      AND RECEIVED_DATE &gt;= #{start_date}::TIMESTAMP
    </if>
    <if test="end_date != null and end_date != ''">
      AND RECEIVED_DATE &lt;= #{end_date}::TIMESTAMP
    </if>
  </sql>

  <select id="getMailReceiveBasicList" parameterType="map" resultType="map">
    SELECT
        ID
        , COMPANY_CODE
        , ACCOUNT_ID
        , SEQNO
        , SUBJECT
        , FROM_ADDRESS
        , TO_ADDRESS
        , CC_ADDRESS
        , RECEIVED_DATE
        , IS_READ
        , HAS_ATTACHMENT
        , CREATED_DATE
    FROM MAIL_RECEIVE_BASIC
    WHERE ACCOUNT_ID = #{account_id}
      AND DELETED_DATE IS NULL
    <include refid="common.companyCodeFilter"/>
    <include refid="mailReceiveBasicSearchCondition"/>
    <include refid="common.dynamicOrderBy"/>
    <include refid="common.pagination"/>
  </select>

  <select id="getMailReceiveBasicListCnt" parameterType="map" resultType="int">
    SELECT COUNT(*)
    FROM MAIL_RECEIVE_BASIC
    WHERE ACCOUNT_ID = #{account_id}
      AND DELETED_DATE IS NULL
    <include refid="common.companyCodeFilter"/>
    <include refid="mailReceiveBasicSearchCondition"/>
  </select>

  <select id="getMailReceiveBasicTodayCount" parameterType="map" resultType="int">
    SELECT COUNT(*)
    FROM MAIL_RECEIVE_BASIC
    WHERE DATE(RECEIVED_DATE) = CURRENT_DATE
      AND DELETED_DATE IS NULL
    <include refid="common.companyCodeFilter"/>
  </select>

  <select id="getMailReceiveBasicInfo" parameterType="map" resultType="map">
    SELECT
        ID
        , COMPANY_CODE
        , ACCOUNT_ID
        , SEQNO
        , SUBJECT
        , FROM_ADDRESS
        , TO_ADDRESS
        , CC_ADDRESS
        , BCC_ADDRESS
        , RECEIVED_DATE
        , IS_READ
        , HAS_ATTACHMENT
        , BODY_TEXT
        , BODY_HTML
        , CREATED_DATE
    FROM MAIL_RECEIVE_BASIC
    WHERE ACCOUNT_ID = #{account_id}
      AND SEQNO = #{seqno}
      AND DELETED_DATE IS NULL
    <include refid="common.companyCodeFilter"/>
  </select>

  <update id="updateMailReceiveBasicAsRead" parameterType="map">
    UPDATE MAIL_RECEIVE_BASIC
    SET
        IS_READ = TRUE
        , UPDATED_DATE = NOW()
    WHERE ACCOUNT_ID = #{account_id}
      AND SEQNO = #{seqno}
      AND DELETED_DATE IS NULL
    <include refid="common.companyCodeFilter"/>
  </update>

  <update id="deleteMailReceiveBasic" parameterType="map">
    UPDATE MAIL_RECEIVE_BASIC
    SET
        DELETED_DATE = NOW()
        , UPDATED_DATE = NOW()
    WHERE ACCOUNT_ID = #{account_id}
      AND SEQNO = #{seqno}
    <include refid="common.companyCodeFilter"/>
  </update>

  <select id="getMailReceiveBasicAttachment" parameterType="map" resultType="map">
    SELECT
        A.ID
        , A.MAIL_ID
        , A.FILE_NAME
        , A.CONTENT_TYPE
        , A.FILE_SIZE
        , A.CONTENT_DATA
        , A.ATTACH_INDEX
    FROM MAIL_RECEIVE_ATTACHMENT A
    INNER JOIN MAIL_RECEIVE_BASIC M ON A.MAIL_ID = M.ID
    WHERE M.ACCOUNT_ID = #{account_id}
      AND M.SEQNO = #{seqno}
      AND A.ATTACH_INDEX = #{attach_index}
      AND M.DELETED_DATE IS NULL
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (M.COMPANY_CODE = #{company_code} OR M.COMPANY_CODE = '*')
    </if>
  </select>

  <select id="getMailReceiveBasicAccountInfo" parameterType="map" resultType="map">
    SELECT
        ID
        , COMPANY_CODE
        , EMAIL_ADDRESS
        , IMAP_HOST
        , IMAP_PORT
        , IMAP_SSL
        , IMAP_USERNAME
        , IMAP_PASSWORD
    FROM MAIL_ACCOUNTS
    WHERE ID = #{account_id}
    <include refid="common.companyCodeFilter"/>
  </select>

</mapper>
