<?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.ruoyi.system.mapper.CustomerServiceReplyMapper">
    
    <resultMap type="CustomerServiceReply" id="CustomerServiceReplyResult">
        <result property="id"           column="id"           />
        <result property="keyword"      column="keyword"      />
        <result property="replyContent" column="reply_content"/>
        <result property="category"     column="category"     />
        <result property="priority"     column="priority"     />
        <result property="isActive"     column="is_active"    />
        <result property="matchType"    column="match_type"   />
        <result property="createdTime"  column="created_time" />
        <result property="updatedTime"  column="updated_time" />
        <result property="createdBy"    column="created_by"   />
        <result property="updatedBy"    column="updated_by"   />
    </resultMap>

    <sql id="selectCustomerServiceReplyVo">
        select id, keyword, reply_content, category, priority, is_active, match_type, created_time, updated_time, created_by, updated_by from customer_service_replies
    </sql>

    <select id="selectCustomerServiceReplyList" parameterType="CustomerServiceReply" resultMap="CustomerServiceReplyResult">
        <include refid="selectCustomerServiceReplyVo"/>
        <where>  
            <if test="keyword != null  and keyword != ''"> and keyword like concat('%', #{keyword}, '%')</if>
            <if test="category != null  and category != ''"> and category = #{category}</if>
            <if test="isActive != null"> and is_active = #{isActive}</if>
            <if test="matchType != null  and matchType != ''"> and match_type = #{matchType}</if>
        </where>
        order by priority desc, created_time desc
    </select>
    
    <select id="selectCustomerServiceReplyById" parameterType="Long" resultMap="CustomerServiceReplyResult">
        <include refid="selectCustomerServiceReplyVo"/>
        where id = #{id}
    </select>

    <select id="selectCustomerServiceReplyByKeyword" parameterType="String" resultMap="CustomerServiceReplyResult">
        <include refid="selectCustomerServiceReplyVo"/>
        where is_active = 1
        and (
            <if test="keyword != null and keyword != ''">
                (match_type = 'exact' and keyword = #{keyword})
                or (match_type = 'contains' and #{keyword} like concat('%', keyword, '%'))
                or (match_type = 'regex' and #{keyword} regexp keyword)
            </if>
        )
        order by priority desc, created_time desc
    </select>

    <select id="selectDefaultReply" resultMap="CustomerServiceReplyResult">
        <include refid="selectCustomerServiceReplyVo"/>
        where is_active = 1 and keyword = 'default'
        limit 1
    </select>
        
    <insert id="insertCustomerServiceReply" parameterType="CustomerServiceReply" useGeneratedKeys="true" keyProperty="id">
        insert into customer_service_replies
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="keyword != null and keyword != ''">keyword,</if>
            <if test="replyContent != null and replyContent != ''">reply_content,</if>
            <if test="category != null">category,</if>
            <if test="priority != null">priority,</if>
            <if test="isActive != null">is_active,</if>
            <if test="matchType != null">match_type,</if>
            <if test="createdBy != null">created_by,</if>
            <if test="updatedBy != null">updated_by,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="keyword != null and keyword != ''">#{keyword},</if>
            <if test="replyContent != null and replyContent != ''">#{replyContent},</if>
            <if test="category != null">#{category},</if>
            <if test="priority != null">#{priority},</if>
            <if test="isActive != null">#{isActive},</if>
            <if test="matchType != null">#{matchType},</if>
            <if test="createdBy != null">#{createdBy},</if>
            <if test="updatedBy != null">#{updatedBy},</if>
         </trim>
    </insert>

    <update id="updateCustomerServiceReply" parameterType="CustomerServiceReply">
        update customer_service_replies
        <trim prefix="SET" suffixOverrides=",">
            <if test="keyword != null and keyword != ''">keyword = #{keyword},</if>
            <if test="replyContent != null and replyContent != ''">reply_content = #{replyContent},</if>
            <if test="category != null">category = #{category},</if>
            <if test="priority != null">priority = #{priority},</if>
            <if test="isActive != null">is_active = #{isActive},</if>
            <if test="matchType != null">match_type = #{matchType},</if>
            <if test="updatedBy != null">updated_by = #{updatedBy},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteCustomerServiceReplyById" parameterType="Long">
        delete from customer_service_replies where id = #{id}
    </delete>

    <delete id="deleteCustomerServiceReplyByIds" parameterType="String">
        delete from customer_service_replies where id in 
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
</mapper> 