<?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="org.agt.module.license.dal.mysql.comment.CommentMapper">

    <select id="getCommentList" resultType="org.agt.module.license.controller.admin.comment.vo.CommentTreeRespVO">
        SELECT
            c.*,
            u.nickname author,
            u.avatar,
            pc.user_id reply_user_id,
            pu.nickname reply_user
        FROM
            crm_comment c
                LEFT JOIN system_users u ON c.user_id = u.id
                AND u.deleted = 0
                LEFT JOIN crm_comment pc ON c.parent_id = pc.id
                AND pc.deleted = 0
                LEFT JOIN system_users pu ON pc.user_id = pu.id
                AND pu.deleted = 0
        WHERE
            c.deleted = 0
            AND c.project_id = #{projectId}
        ORDER BY
            c.update_time DESC
    </select>

    <select id="getLatestCommentList" resultType="org.agt.module.license.controller.admin.comment.vo.CommentTreeRespVO">
        SELECT
        	c.*,
        	u.nickname author,
        	u.avatar,
        	p.`name` project_name,
        	p.status,
			u2.nickname business_owner,
			u3.nickname technical_owner_a,
			l.serial_no,
			cu.name customer_name
        FROM
        	(
        	SELECT
        		c.project_id,
        		max( c.update_time ) max_update_time
        	FROM
        		crm_comment c
        		LEFT JOIN system_users u ON c.user_id = u.id
        		AND u.deleted = 0
        	WHERE
        		c.deleted = 0
        	GROUP BY
        		c.project_id
        	) g
        	LEFT JOIN crm_comment c ON g.project_id = c.project_id
        	AND g.max_update_time = c.update_time
        	AND c.deleted = 0
        	LEFT JOIN system_users u ON c.user_id = u.id
        	AND u.deleted = 0
        	LEFT JOIN crm_project p ON c.project_id = p.id
        	AND p.deleted = 0
			LEFT JOIN crm_customer cu ON p.customer_id = cu.id
			AND cu.deleted = 0
			LEFT JOIN crm_license_server l ON p.id = l.project_id
			AND l.deleted = 0
			LEFT JOIN system_users u2 ON p.business_owner = u2.id
			AND u2.deleted = 0
			LEFT JOIN system_users u3 ON p.technical_owner_a = u3.id
			AND u3.deleted = 0
        where p.deleted = 0
		  and c.update_time >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK)
        ORDER BY
        	c.update_time DESC
    </select>

</mapper>