<?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="post">

	<resultMap id="commentResult" type="comment">
		<id property="commentId" column="COMMENT_ID" />
		<result property="upperCommentId" column="UPPER_COMMENT_ID" />
		<result property="commentSequence" column="COMMENT_SEQUENCE" />
		<result property="commentDetail" column="COMMENT_DETAIL" />
		<result property="regrId" column="USER_ID" />
		<result property="regrName" column="USER_NAME" />
		<result property="regDate" column="FIRST_REG_DATETIME" />
		<result property="consentType" column="CONSENT_TYPE" />
		<result property="agreeCount" column="AGREE_COUNT" />
		<result property="disagreeCount" column="DISAGREE_COUNT" />
		<collection javaType="ArrayList" property="replyComments" select="selectCommentList" column="{upperCommentId=COMMENT_ID}" />
	</resultMap>
	
	<sql id="tableComment">TN_CF_COMMENT</sql>
	<sql id="tableCommentConsent">TN_CF_COMMENT_CONSENT</sql>
	
	<sql id="columnsComment">
	C.COMMENT_ID, C.UPPER_COMMENT_ID, C.COMMENT_SEQUENCE, C.COMMENT_DETAIL,
	U.USER_ID, U.USER_NAME, C.FIRST_REG_DATETIME, CC.CONSENT_TYPE
	</sql>
	
	<select id="selectCommentList" parameterType="java.util.HashMap" resultMap="commentResult">
		SELECT <include refid="columnsComment" />,
		       (SELECT COUNT(USER_ID) CNT
		        FROM   <include refid="tableCommentConsent" /> CA
		        WHERE  CA.COMMENT_ID = C.COMMENT_ID
		        AND    CA.CONSENT_TYPE = 'AGREE') AGREE_COUNT,
		       (SELECT COUNT(USER_ID) CNT
		        FROM   <include refid="tableCommentConsent" /> CD
		        WHERE  CD.COMMENT_ID = C.COMMENT_ID
		        AND    CD.CONSENT_TYPE = 'DISAGREE') DISAGREE_COUNT
		FROM   <include refid="tableComment" /> C
		       LEFT OUTER JOIN <include refid="tableCommentConsent" /> CC ON C.COMMENT_ID = CC.COMMENT_ID AND C.FIRST_REGR_ID = CC.USER_ID
		       LEFT JOIN <include refid="user.tableUser" /> U ON C.FIRST_REGR_ID = U.USER_ID
		WHERE  C.DELETE_YN = '0'
		<if test="@com.samsung.SdlComparator@isNotEmpty(postId)">
		AND    UPPER_COMMENT_ID IS NULL
		AND    C.POST_ID = #{postId}
		</if>
		<if test="@com.samsung.SdlComparator@isNotEmpty(upperCommentId)">
		AND    C.UPPER_COMMENT_ID = #{upperCommentId}
		</if>
		ORDER BY C.COMMENT_SEQUENCE ASC
	</select>
	
	<insert id="insertComment" parameterType="comment">
		INSERT INTO <include refid="tableComment" />
		(POST_ID, COMMENT_ID, UPPER_COMMENT_ID, COMMENT_SEQUENCE, COMMENT_DETAIL, DELETE_YN,
		 FIRST_REG_DATETIME, FIRST_REGR_ID, LAST_MOD_DATETIME, LAST_MODR_ID)
		VALUES
		(#{postId}, #{commentId}, #{upperCommentId}, #{commentSequence}, #{commentDetail}, '0',
		 #{firstRegDatetime, jdbcType=TIMESTAMP}, #{firstRegrId}, #{lastModDatetime, jdbcType=TIMESTAMP}, #{lastModrId})
	</insert>
	
	<select id="selectCommentRegrId" parameterType="java.util.HashMap" resultType="string">
		SELECT FIRST_REGR_ID
		FROM   <include refid="tableComment" />
		WHERE  COMMENT_ID = #{commentId}
	</select>
	
	<update id="updateComment" parameterType="comment">
		UPDATE <include refid="tableComment" />
		SET    COMMENT_DETAIL = #{commentDetail},
		       LAST_MOD_DATETIME = #{lastModDatetime, jdbcType=TIMESTAMP},
		       LAST_MODR_ID = #{lastModrId}
		WHERE  POST_ID = #{postId}
		AND    COMMENT_ID = #{commentId}
	</update>
	
	<update id="deleteComment" parameterType="java.util.HashMap">
		UPDATE <include refid="tableComment" />
		SET    DELETE_YN = '1'
		WHERE  POST_ID = #{postId}
		AND    COMMENT_ID = #{commentId}
	</update>
	
	<insert id="insertCommentConsent" parameterType="java.util.HashMap">
		INSERT INTO <include refid="tableCommentConsent" />
		(COMMENT_ID, USER_ID, CONSENT_TYPE, CONSENT_DATETIME)
		VALUES
		(#{commentId}, #{userId}, #{consentType}, NOW())
	</insert>
	
	<delete id="deleteCommentConsent" parameterType="java.util.HashMap">
		DELETE FROM <include refid="tableCommentConsent" />
		WHERE  COMMENT_ID = #{commentId}
		AND    USER_ID = #{userId}
	</delete>
	
	<select id="selectCommentConsentCount" parameterType="java.util.HashMap" resultType="java.util.HashMap">
		SELECT (SELECT COUNT(USER_ID) CNT
		        FROM   <include refid="tableCommentConsent" />
		        WHERE  COMMENT_ID = #{commentId}
		        AND    CONSENT_TYPE = 'AGREE') AS "agreeCount",
		       (SELECT COUNT(USER_ID) CNT
		        FROM   <include refid="tableCommentConsent" />
		        WHERE  COMMENT_ID = #{commentId}
		        AND    CONSENT_TYPE = 'DISAGREE') AS "disagreeCount"
	</select>
	
</mapper>