<?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.ylx.order.mapper.TCommentUserMapper">

    <resultMap type="com.ylx.order.domain.TCommentUser" id="TCommentUserMap">
        <result property="id" column="id" jdbcType="VARCHAR"/>
        <result property="text" column="text" jdbcType="VARCHAR"/>
        <result property="orderId" column="order_id" jdbcType="VARCHAR"/>
        <result property="merchantId" column="merchant_id" jdbcType="INTEGER"/>
        <result property="user_id" column="user_id" jdbcType="VARCHAR"/>
        <result property="commentTime" column="comment_time" jdbcType="TIMESTAMP"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
        <result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
        <result property="isDelete" column="is_delete" jdbcType="INTEGER"/>
    </resultMap>

    <!-- 批量插入 -->
    <insert id="insertBatch" keyProperty="id" useGeneratedKeys="true">
        insert into ry-vue.t_comment_user(textorder_nojs_idopen_idcomment_timenamecreate_timeupdate_timeis_delete)
        values
        <foreach collection="entities" item="entity" separator=",">
            (#{entity.text}#{entity.orderNo}#{entity.jsId}#{entity.openId}#{entity.commentTime}#{entity.name}#{entity.createTime}#{entity.updateTime}#{entity.isDelete})
        </foreach>
    </insert>
    <!-- 批量插入或按主键更新 -->
    <insert id="insertOrUpdateBatch" keyProperty="id" useGeneratedKeys="true">
        insert into ry-vue.t_comment_user(textorder_nojs_idopen_idcomment_timenamecreate_timeupdate_timeis_delete)
        values
        <foreach collection="entities" item="entity" separator=",">
            (#{entity.text}#{entity.orderNo}#{entity.jsId}#{entity.openId}#{entity.commentTime}#{entity.name}#{entity.createTime}#{entity.updateTime}#{entity.isDelete})
        </foreach>
        on duplicate key update
        text = values(text) order_no = values(order_no) js_id = values(js_id) open_id = values(open_id) comment_time =
        values(comment_time) name = values(name) create_time = values(create_time) update_time = values(update_time)
        is_delete = values(is_delete)
    </insert>

    <sql id="CommentUserAuditBaseWhere">
        tcu.is_delete = 0
        AND tcua.is_delete = 0
        <!-- 技师姓名查询 -->
        <if test="tCommentUser.merchantName != null and tCommentUser.merchantName != ''">
            AND tcu.merchant_name = #{tCommentUser.merchantName}
        </if>
        <!--敏感词查询-->
        <if test="tCommentUser.sensitiveWord != null">
            AND tcu.sensitive_word = #{tCommentUser.sensitiveWord}
        </if>
    </sql>

    <!-- 分页查询用户评论 -->
    <select id="selectAll" resultType="com.ylx.order.domain.TCommentUser">
        SELECT
            tcu.id as id,
            tcu.nick_name as nickName,
            tcu.text as text,
            tcu.star as star,
            tcu.comment_time as commentTime,
            tcu.merchant_id as merchantId,
            tcu.merchant_name as merchantName,
            tcu.sensitive_word as sensitiveWord,
            tcu.experience_comment as experienceComment,
            tcu.price_comment as priceComment,
            tcu.attitude_comment as attitudeComment,
            tcu.grooming_comment as groomingComment,
            tcua.audit_status as auditStatus,
            tcua.auditor_name as auditorName,
            tcua.audit_time as auditTime
        FROM
            t_comment_user tcu
                LEFT JOIN t_comment_user_audit tcua ON tcu.id = tcua.comment_id
        WHERE
        <include refid="CommentUserAuditBaseWhere"/>
        <!-- 审核状态查询 -->
        <if test="tCommentUser.auditStatus != null">
            AND tcua.audit_status = #{tCommentUser.auditStatus}
        </if>
    </select>

    <!-- 查询用户评论审核状态统计 -->
    <select id="selectAuditStatusCount" resultType="com.ylx.order.domain.vo.TCommentUserAuditStatusCountVO">
        SELECT
            COALESCE(SUM(CASE WHEN tcua.audit_status = 0 THEN 1 ELSE 0 END), 0) AS pendingCount,
            COALESCE(SUM(CASE WHEN tcua.audit_status = 1 THEN 1 ELSE 0 END), 0) AS approvedCount,
            COALESCE(SUM(CASE WHEN tcua.audit_status = 2 THEN 1 ELSE 0 END), 0) AS rejectedCount
        FROM
            t_comment_user tcu
                LEFT JOIN t_comment_user_audit tcua ON tcu.id = tcua.comment_id
        WHERE
        <include refid="CommentUserAuditBaseWhere"/>
    </select>

    <select id="getMerchantComment" resultType="com.ylx.merchant.domain.vo.MerchantCommentVO">
        SELECT
            c.id,
            c.nick_name,
            c.user_icon,
            c.star,
            c.text,
            o.project_name,
            o.create_time,
            o.final_amount,
            o.project_duration
        FROM
            t_comment_user c
        INNER JOIN t_comment_user_audit a ON c.id = a.comment_id
        INNER JOIN t_order o ON c.order_id = o.id
        WHERE
            c.merchant_id = #{dto.id}
        AND a.audit_status = 1
        AND a.is_published = 1
        AND c.is_delete = 0
        AND a.is_delete = 0
        ORDER BY
            c.comment_time DESC
    </select>

    <select id="selectMerchantMyCommentPage" resultType="com.ylx.order.domain.vo.MerchantMyCommentVO">
        SELECT
            c.id,
            o.project_name AS projectName,
            CASE
                WHEN c.experience_comment IS NULL
                    OR c.price_comment IS NULL
                    OR c.attitude_comment IS NULL
                    OR c.grooming_comment IS NULL
                THEN NULL
                ELSE ROUND((
                    c.experience_comment
                    + c.price_comment
                    + c.attitude_comment
                    + c.grooming_comment
                ) / 4.0, 1)
            END AS score,
            c.text,
            c.nick_name AS nickName,
            c.user_icon AS userIcon,
            c.comment_time AS commentTime
        FROM
            t_comment_user c
        INNER JOIN t_comment_user_audit a ON c.id = a.comment_id
        LEFT JOIN t_order o ON c.order_id = o.id
        WHERE
            c.merchant_id = #{merchantId}
        AND a.audit_status = 1
        AND c.is_delete = 0
        AND a.is_delete = 0
        ORDER BY
            c.comment_time DESC
    </select>

</mapper>
