<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zioinfo.cms.ugc.mapper.UgcMapper">

  <resultMap id="ugcMap" type="com.zioinfo.cms.ugc.CmsUgc">
    <id property="id" column="id"/>
    <result property="ugcType" column="ugc_type"/>
    <result property="targetContentId" column="target_content_id"/>
    <result property="targetRef" column="target_ref"/>
    <result property="parentId" column="parent_id"/>
    <result property="authorName" column="author_name"/>
    <result property="authorEmail" column="author_email_enc"/>
    <result property="body" column="body"/>
    <result property="rating" column="rating"/>
    <result property="moderationStatus" column="moderation_status"/>
    <result property="aiRisk" column="ai_risk"/>
    <result property="aiReason" column="ai_reason"/>
    <result property="sentiment" column="sentiment"/>
    <result property="itsmSrId" column="itsm_sr_id"/>
    <result property="createdAt" column="created_at"/>
    <result property="moderatedAt" column="moderated_at"/>
  </resultMap>

  <select id="findAll" resultMap="ugcMap">
    SELECT * FROM cms_ugc
    <where>
      <if test="ugcType != null and ugcType != ''">AND ugc_type = #{ugcType}</if>
      <if test="moderationStatus != null and moderationStatus != ''">AND moderation_status = #{moderationStatus}</if>
      <if test="targetContentId != null">AND target_content_id = #{targetContentId}</if>
    </where>
    ORDER BY id DESC
  </select>

  <select id="findById" resultMap="ugcMap">SELECT * FROM cms_ugc WHERE id = #{id}</select>

  <select id="findApprovedReviews" resultMap="ugcMap">
    SELECT * FROM cms_ugc
    WHERE ugc_type = 'REVIEW' AND moderation_status = 'APPROVED'
    <if test="targetRef != null and targetRef != ''">AND target_ref = #{targetRef}</if>
    ORDER BY id DESC LIMIT 100
  </select>

  <insert id="insert" parameterType="com.zioinfo.cms.ugc.CmsUgc" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO cms_ugc
      (ugc_type, target_content_id, target_ref, parent_id, author_name, author_email_enc, body, rating,
       moderation_status, ai_risk, ai_reason, sentiment, itsm_sr_id)
    VALUES
      (#{ugcType}, #{targetContentId}, #{targetRef}, #{parentId}, #{authorName}, #{authorEmail}, #{body}, #{rating},
       #{moderationStatus}, #{aiRisk}, #{aiReason}, #{sentiment}, #{itsmSrId})
  </insert>

  <update id="updateModeration">
    UPDATE cms_ugc SET moderation_status = #{status}, ai_risk = #{aiRisk}, ai_reason = #{aiReason}, moderated_at = NOW()
    WHERE id = #{id}
  </update>

  <update id="updateSentiment">
    UPDATE cms_ugc SET sentiment = #{sentiment} WHERE id = #{id}
  </update>

  <update id="updateSrId">
    UPDATE cms_ugc SET itsm_sr_id = #{srId} WHERE id = #{id}
  </update>

  <delete id="delete">DELETE FROM cms_ugc WHERE id = #{id}</delete>

</mapper>
