<?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="com.api.app.dao.message.AppMessageDao">
    <select id="findSysNoReadNumById" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from sys_sending where send_status = 1 and receiver_account = #{id}
    </select>

    <select id="findNewTitleById" parameterType="java.lang.Integer" resultType="java.lang.String">
        select sm.title from sys_message as sm,sys_sending as ss where sm.id = ss.message_id and ss.receiver_account = #{id} ORDER BY ss.send_date DESC LIMIT 1
    </select>

    <select id="sysMessageList" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppSysMessageVo">
        select sm.id,sm.title,sm.content,ss.send_status as status from sys_message as sm,sys_sending as ss where sm.id = ss.message_id and ss.receiver_account = #{id} ORDER BY ss.send_date DESC
    </select>

    <select id="sysCommentMessageList" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppCommentMessageVo">
        select scm.*,ur1.nick_name as create_name,ur2.nick_name as respondent_name from user_resident as ur1,sys_comment_message as scm
        LEFT JOIN user_resident as ur2 on scm.respondent_id = ur2.id
        where scm.create_id = ur1.id and scm.receiver_account = #{id} order by scm.create_date DESC
    </select>

    <select id="sysMessageDetail" parameterType="com.api.model.app.UserIdAndSysMessageId" resultType="com.api.vo.app.AppSysMessageDetail">
        select sm.id,sm.title,sm.content from sys_message as sm,sys_sending as ss where sm.id = ss.message_id and ss.receiver_account = #{id} and sm.id = #{sysMessageId}
    </select>

    <update id="readMessage" parameterType="com.api.model.app.UserIdAndSysMessageId">
        update sys_sending set send_status = 3 where message_id = #{sysMessageId} and receiver_account = #{id}
    </update>

    <update id="allReadSys" parameterType="java.lang.Integer">
        update sys_sending set send_status = 3 where receiver_account = #{id}
    </update>

    <update id="allReadComment" parameterType="java.lang.Integer">
        update sys_comment_message set send_status = 3 where receiver_account = #{id}
    </update>

    <update id="falseDelete" parameterType="com.api.model.app.UserIdAndSysMessageId">
        update sys_sendind set ???????? where message_id = #{sysMessageId} and receiver_account = #{id}
    </update>

    <select id="findCommentNoReadNumById" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from sys_comment_message where send_status = 1 and receiver_account = #{id}
    </select>

    <select id="findCommentNewTitleById" parameterType="java.lang.Integer" resultType="com.api.vo.app.AppCommentMessageVo">
        select scm.*,ur.nick_name as createName from sys_comment_message as scm,user_resident as ur
        where scm.create_id = ur.id and scm.receiver_account = #{id}
        order by create_date DESC limit 1
    </select>

    <insert id="insertCommentMessage" parameterType="com.api.model.app.AppCommentMessage">
        insert into sys_comment_message(
            gambit_theme_id,
            respondent_id,
            type,
            content,
            receiver_account,
            send_status,
            create_id,
            create_date
        )values (
            #{gambitThemeId},
            #{respondentId},
            #{type},
            #{content},
            #{receiverAccount},
            #{sendStatus},
            #{createId},
            #{createDate}
        )
    </insert>

    <delete id="deleteCommentMessage" parameterType="com.api.model.app.AppCommentMessage">
        delete from sys_comment_message
        where gambit_theme_id=#{gambitThemeId} and type = #{type} and receiver_account = #{receiverAccount} and create_id = #{createId}
    </delete>
</mapper>