<?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="cn.axzo.msg.center.dal.mapper.MessageRecordMapper">

  <insert id="batchInsertWithId">
    INSERT INTO message_record(
    id, `from_id`, `to_id`, `person_id`, `type`, `relation_id`, `module_id`, `title`, `content`, `receive_type`, `terminal_type`,
    `terminal_id`, `terminal_name`, `state`, `biz_id`, `router_params`, `extra`, `retry_count`, `callback_url`, `fail_cause`,
    `event_source`, `tenant_id`, `is_delete`, `create_at`, `update_at`, `old_type_id`
    )
    VALUES
    <foreach collection="records" item="i" separator=",">
      (
      #{i.id}, #{i.fromId}, #{i.toId}, #{i.personId}, COALESCE(#{i.type}, 0), #{i.relationId}, #{i.moduleId}, #{i.title}, #{i.content}, COALESCE(#{i.receiveType}, 0), COALESCE(#{i.terminalType}, 0),
      #{i.terminalId}, #{i.terminalName}, COALESCE(#{i.state}, 0), #{i.bizId}, #{i.routerParams}, #{i.extra}, #{i.retryCount}, #{i.callbackUrl}, #{i.failCause},
      #{i.eventSource}, #{i.tenantId}, #{i.isDelete}, #{i.createAt}, #{i.updateAt}, #{i.oldTypeId}
      )
    </foreach>
  </insert>

  <delete id="deleteRecordsPhysically">
    DELETE FROM message_record
    WHERE id IN
    <foreach collection="ids" item="id" separator="," open="(" close=")">
      #{id}
    </foreach>
  </delete>

  <select id="statisticsMsg"
    resultType="cn.axzo.msg.center.domain.dto.MsgStatisticsDTO">
    select type,
           count(1) as num
    from message_record
    where state in (2,3)
    and is_delete = 0
    <if test="bizType != null">
      <if test="bizType == 1">
        and to_id = #{identityId}
        and receive_type = #{receiveType}
      </if>
      <if test="bizType == 2">
        and person_id = #{personId}
      </if>
      and relation_id in (
        select id from message_relation where is_delete = 0 and module_id in (
            select id from message_module where is_delete = 0 and biz_type = #{bizType}
        )
      )
    </if>
    group by type
  </select>

  <select id="pageMsgInfo"
    resultType="cn.axzo.msg.center.domain.dto.MessageNewResDTO">
    select record.id msgId,
    record.title as msgTitle,
    record.content as content,
    record.receive_type as receiveType,
    record.terminal_id as terminalId,
    record.terminal_type as terminalType,
    record.terminal_name as terminalName,
    record.state,
    record.create_at as msgDate,
    record.extra as ext,
    record.router_params as routerParam,
    record.relation_id as relationId,
    record.old_type_id as oldTypeId,
    record.create_at as createAt
    from message_record record
    <if test="req.msgType == 2">
    join message_relation relation
    on record.relation_id = relation.id and relation.is_delete = 0
    join message_module moud
    on relation.module_id = moud.id and moud.is_delete = 0
      <if test="req.moduleId != null and req.moduleId > 0">
        and moud.id = #{req.moduleId}
      </if>
    </if>
    where record.type = #{req.msgType}
    and record.is_delete = 0
    and (
            <foreach collection="identifies" item="item" separator="OR">
                (record.to_id = #{item.identity.id} AND record.receive_type = #{item.receiveType.code})
            </foreach>
        )
    <if test="states != null and states.size > 0">
      and record.state in
      <foreach close=")" collection="states" index="index" item="item" open="(" separator=",">
        #{item}
      </foreach>
    </if>
    <if test="req.relationIds != null and req.relationIds.size > 0">
      and record.relation_id in
      <foreach close=")" collection="req.relationIds" index="index" item="item" open="(" separator=",">
        #{item}
      </foreach>
    </if>
    <if test="req.excludeMsgIds != null and req.excludeMsgIds.size > 0">
      and record.id not in
      <foreach close=")" collection="req.excludeMsgIds" index="index" item="item" open="(" separator=",">
        #{item}
      </foreach>
    </if>
    and record.create_at > DATE_FORMAT((CURDATE() - INTERVAL 3 YEAR),'%Y-%m-%d')
        order by record.create_at desc,record.id desc
  </select>
  <select id="mixIdentityPageMsgInfo"
          resultType="cn.axzo.msg.center.domain.dto.MessageNewResDTO">
    select record.id msgId,
    record.title as msgTitle,
    record.content as content,
    record.receive_type as receiveType,
    record.terminal_id as terminalId,
    record.terminal_type as terminalType,
    record.terminal_name as terminalName,
    record.state,
    record.create_at as msgDate,
    record.extra as ext,
    record.router_params as routerParam,
    record.relation_id as relationId,
    record.old_type_id as oldTypeId
    from message_record record
    <if test="req.msgType == 2">
      join message_relation relation
      on record.relation_id = relation.id and relation.is_delete = 0
      join message_module moud
      on relation.module_id = moud.id and moud.is_delete = 0
      <if test="req.moduleId != null and req.moduleId > 0">
        and moud.id = #{req.moduleId}
      </if>
    </if>
    where record.type = #{req.msgType}
    and record.state in (2,3,4,5)
    and record.is_delete = 0
    and (
    (<if test="identityIds != null and identityIds.size > 0 ">
       record.to_id in
           <foreach open="(" close=")" separator="," collection="identityIds" index="index" item="identityId">
             #{identityId}
           </foreach>
     </if>
            and record.receive_type in (1,2)) or
        (record.to_id = 0 and record.receive_type = 9 and person_id = #{personId})
        )
    <if test="states != null and states.size > 0">
      and record.state in
      <foreach close=")" collection="states" index="index" item="item" open="(" separator=",">
        #{item}
      </foreach>
    </if>

    and record.create_at > DATE_FORMAT((CURDATE() - INTERVAL 3 YEAR),'%Y-%m-%d')
    order by record.create_at desc,record.id desc
  </select>
  <select id="pageMsgInfo4WechatAndTrade"
    resultType="cn.axzo.msg.center.domain.dto.MessageNewResDTO">
    select record.id msgId,
    record.title as msgTitle,
    record.content as content,
    record.receive_type as receiveType,
    record.terminal_id as terminalId,
    record.terminal_type as terminalType,
    record.terminal_name as terminalName,
    record.state,
    record.create_at as msgDate,
    record.extra as ext,
    record.router_params as routerParam,
    record.relation_id as relationId,
    record.old_type_id as oldTypeId
    from message_record record
    <if test="req.msgType == 2">
      join message_relation relation
      on record.relation_id = relation.id and relation.is_delete = 0
      join message_module moud
      on relation.module_id = moud.id and moud.is_delete = 0
      <if test="req.moduleId != null and req.moduleId > 0">
        and moud.id = #{req.moduleId}
      </if>
    </if>
    WHERE record.person_id = #{personId}
    <if test="req.msgType == 1 ">
      <if test="moduleIds != null and moduleIds.size > 0">
        and record.module_id in
        <foreach close=")" collection="moduleIds" index="index" item="item" open="(" separator=",">
          #{item}
        </foreach>
      </if>
    </if>
    <if test="states != null and states.size > 0">
      and record.state in
      <foreach close=")" collection="states" index="index" item="item" open="(" separator=",">
        #{item}
      </foreach>
    </if>

    and record.create_at > DATE_FORMAT((CURDATE() - INTERVAL 3 YEAR),'%Y-%m-%d')
    order by record.create_at desc,record.id desc
  </select>

  <update id="readAllMsg" >
    update message_record set state = 4
    where id in
    <foreach collection="ids" item="id" open="(" close=")" separator=",">
      #{id}
    </foreach>
  </update>

  <!---->
  <update id="updateHasRead" parameterType="cn.axzo.msg.center.domain.dto.UpdateReadDTO">
    update message_record set state=4
    where is_delete=0
    <if test="req.msgIdList != null">
      AND id IN
      <foreach close=")" collection="req.msgIdList" item="msgId" open="(" separator=",">
        #{msgId}
      </foreach>
    </if>
    <if test="req.moduleId != null">
      AND module_id = #{req.moduleId}
    </if>
    <if test="req.msgType != null">
      AND type = #{req.msgType}
    </if>
    <if test="req.tenantId != null">
      AND tenant_id = #{req.tenantId}
    </if>
    <if test="req.toId != null and req.fromId==null ">
      AND to_id=#{req.toId}
    </if>
    <if test="req.fromId != null and req.toId == null">
      AND from_id=#{req.fromId}
    </if>
  </update>
  <!--查询未读消息-->
  <select id="queryUnReadList" resultType="cn.axzo.msg.center.domain.dto.MessageNewResDTO">
    select record.id msgId,
           record.title as msgTitle,
           record.content as content,
           record.receive_type as receiveType,
           record.terminal_id as terminalId,
           record.terminal_type as terminalType,
           record.terminal_name as terminalName,
           record.state,
           record.create_at as msgDate,
           record.extra as ext,
           record.router_params as routerParam,
           record.relation_id as relationId,
           record.old_type_id as oldTypeId
    from message_record AS record
    where to_id=#{req.toId}
    <if test="req.msgType != null and req.msgType > 0">
      AND type = #{req.msgType}
    </if>
    <if test="req.fromId != null and req.fromId > 0">
      AND from_id = #{req.fromId}
    </if>
    <if test="req.tenantId != null and req.tenantId > 0">
      AND tenant_id = #{req.tenantId}
    </if>
    and module_id = #{req.moduleId}
    and state in (2,3)
    and is_delete = 0
    order by create_at desc,id desc
  </select>
  <!--查询已发送消息-->
  <select id="querySent" resultType="cn.axzo.msg.center.api.response.MessageRecordRes"
          parameterType="cn.axzo.msg.center.api.request.QueryMessageRecordReq">
    select record.id msgId,
    record.title as msgTitle,
    record.content as content,
    record.receive_type as receiveType,
    record.terminal_id as terminalId,
    record.terminal_type as terminalType,
    record.terminal_name as terminalName,
    record.state,
    record.create_at as msgDate,
    record.extra as ext,
    record.router_params as routerParam,
    record.relation_id as relationId,
    record.old_type_id as oldTypeId,
    record.tenant_id AS tenantId,
    record.from_id AS fromId,
    record.to_id AS toId,
    record.create_at AS createAt
    from message_record AS record
    where to_id=#{req.toId}
    <if test="req.msgType != null and req.msgType > 0">
      AND type = #{req.msgType}
    </if>
    <if test="req.fromId != null and req.fromId > 0">
      AND from_id = #{req.fromId}
    </if>
    <if test="req.tenantId != null and req.tenantId > 0">
      AND tenant_id = #{req.tenantId}
    </if>
    <if test="req.moduleId != null and req.moduleId > 0">
      AND module_id = #{req.moduleId}
    </if>
    and state=2
    and is_delete = 0
    order by create_at desc,id desc
  </select>


</mapper>

