<?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.BbsTopicMapper">
	
	<select id="selectSelectTopics" resultType="com.tangguo.domain.vo.BbsTopicListVO">
		SELECT
			id, name
		FROM
			bbs_topic
		<where>
			<if test="name != null and name != ''">
				name LIKE CONCAT('%', #{name}, '%')
			</if>
		</where>
		ORDER BY
			is_top DESC, CASE WHEN is_top = 1 THEN top_time END DESC, hot_score DESC, sort ASC, create_time DESC
	</select>
	
	
	<update id="refreshRankingTopic">
		UPDATE
			bbs_topic t
		LEFT JOIN
			(
				SELECT
					t.id,
					t.`name`,
					IFNULL(SUM(m.like_count), 0) AS like_count,
					IFNULL(SUM(m.comment_count), 0) AS comment_count,
					ROUND(IFNULL(SUM(m.hot_score), 0), 3) AS hot_score
				FROM
					bbs_topic t
				LEFT JOIN (
					SELECT
						topic_ids,
						like_count,
						comment_count,
						(1 / (1 + 0.1 * TIMESTAMPDIFF(HOUR, create_time, NOW()))) AS time_score,
						(LN(1 + like_count) * 1 + LN(1 + comment_count) * 4
		    				+ (1 / (1 + 0.1 * TIMESTAMPDIFF(HOUR, create_time, NOW()))) * 15) * 100 AS hot_score
					FROM
						bbs_moment
				) m ON FIND_IN_SET(t.id, m.topic_ids)
				GROUP BY
					t.id, t.`name`
			) h ON h.id = t.id
		SET
			t.like_count = h.like_count, t.comment_count = h.comment_count, t.hot_score = h.hot_score
	</update>
	
	
	<select id="selectRankingTopic" resultType="com.tangguo.domain.vo.BbsTopicListVO">
		SELECT
			t.id, t.name, COUNT(m.id) AS moment_count
		FROM
			bbs_topic t
		LEFT JOIN
			bbs_moment m ON FIND_IN_SET(t.id, m.topic_ids)
		GROUP BY
			t.id, t.name
		ORDER BY
			t.is_top DESC, CASE WHEN t.is_top = 1 THEN t.top_time END DESC, t.hot_score DESC, t.create_time DESC
		LIMIT 10
	</select>

</mapper>
