<?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.tangguo.mapper.BbsMomentCommentMapper">
    
    <select id="selectBbsMomentCommentList" resultType="com.tangguo.domain.BbsMomentComment">
        SELECT
            m.content AS moment_content,
            c.*
        FROM
            bbs_moment_comment c
        LEFT JOIN
            bbs_moment m ON m.id = c.moment_id
        <where>
            <if test="1==1">
                status = 1
            </if>
            <if test="comment.momentContent != null and comment.momentContent != ''">
                AND c.content LIKE CONCAT('%', #{comment.momentContent}, '%')
            </if>
            <if test="comment.nickName != null and comment.nickName != ''">
                AND c.nick_name LIKE CONCAT('%', #{comment.nickName}, '%')
            </if>
            <if test="comment.userName != null and comment.userName != ''">
                AND c.user_name LIKE CONCAT('%', #{comment.userName}, '%')
            </if>
        </where>
        ORDER BY
            c.is_top DESC, c.top_time DESC, c.create_time DESC
    </select>
    
    
    <select id="selectBbsMomentComments" resultType="com.tangguo.domain.BbsMomentComment">
        SELECT
            r.*
        FROM
            bbs_moment_comment r
        LEFT JOIN
            bbs_moment_comment c ON c.id = r.parent_id
        WHERE
            status = 1 AND FIND_IN_SET(#{commentId}, r.ancestor_path) AND r.id != #{commentId}
        ORDER BY
	        r.is_top DESC, r.top_time DESC, r.create_time
    </select>
    
    
    <select id="selectBbsComments" resultType="com.tangguo.domain.BbsMomentComment">
        SELECT
            *
        FROM
            bbs_moment_comment
        WHERE
            status = 1 AND moment_id = #{momentId}
        ORDER BY
	        is_top DESC, top_time DESC, create_time
    </select>
    
    
	<select id="selectMomentLimitComments" resultType="com.tangguo.domain.BbsMomentComment">
        SELECT c.* FROM (
            SELECT
                c.id,
                c.create_time,
                c.moment_id,
                c.nick_name,
                c.user_name,
                c.content,
                c.parent_id,
                c.reply_nick_name,
                c.is_top,
                IF(c.user_name = #{userName}, 1, 0) AS is_self,
                ROW_NUMBER() OVER (PARTITION BY moment_id ORDER BY create_time) AS rn
            FROM
                bbs_moment_comment c
            INNER JOIN
                (
                    SELECT
                        id, user_name, is_enable_featured_comment
                    FROM
                        bbs_moment
                    WHERE
                        id IN
                        <foreach collection="momentIds" item="id" open="(" separator="," close=")">
                            #{id}
                        </foreach>
                ) m ON m.id = c.moment_id
            WHERE
                c.parent_id IS NULL
            AND
                c.status = 1
            AND
                (
                    m.user_name = #{userName}
                    OR m.is_enable_featured_comment = 0
                    OR (m.is_enable_featured_comment = 1 AND (c.user_name = #{userName} OR c.is_featured = 1))
                )
            ORDER BY
				c.is_top DESC, c.top_time DESC, c.create_time
        ) AS c
        WHERE rn &lt;= #{rows};
    </select>
    
    
    <select id="selectMomentComments" resultType="com.tangguo.domain.vo.BbsCommentDetailVO">
        SELECT
            c.id,
            c.create_time,
            c.moment_id,
            c.nick_name,
            c.user_name,
            c.content,
            c.parent_id,
            c.reply_nick_name,
            c.is_featured,
            c.is_top,
            IF(c.user_name = #{userName}, 1, 0) AS is_self
        FROM
            bbs_moment_comment c
        INNER JOIN
            bbs_moment m ON m.id = c.moment_id
        WHERE
            m.id = #{momentId}
        AND
            c.status = 1
        AND
            (
                m.user_name = #{userName}
                OR m.is_enable_featured_comment = 0
                OR (m.is_enable_featured_comment = 1 AND (c.user_name = #{userName} OR c.is_featured = 1))
            )
        ORDER BY
	        c.is_top DESC, c.top_time DESC, c.create_time
    </select>

</mapper>
