<?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.base.mapper.RobotOpsFaqMapper">

    <resultMap type="RobotOpsFaq" id="RobotOpsFaqResult">
        <result property="id"    column="id"    />
        <result property="categoryType"    column="category_type"    />
        <result property="question"    column="question"    />
        <result property="answer"    column="answer"    />
        <result property="sortNo"    column="sort_no"    />
        <result property="status"    column="status"    />
        <result property="similarCount"    column="similar_count"    />
        <result property="remark"    column="remark"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateTime"    column="update_time"    />
        <result property="createBy"    column="create_by"    />
        <result property="updateBy"    column="update_by"    />
    </resultMap>

    <resultMap id="RobotOpsFaqRobotOpsFaqSimilarResult" type="RobotOpsFaq" extends="RobotOpsFaqResult">
        <collection property="robotOpsFaqSimilarList" ofType="RobotOpsFaqSimilar" column="id" select="selectRobotOpsFaqSimilarList" />
    </resultMap>

    <resultMap type="RobotOpsFaqSimilar" id="RobotOpsFaqSimilarResult">
        <result property="id"    column="id"    />
        <result property="faqId"    column="faq_id"    />
        <result property="similarQuestion"    column="similar_question"    />
        <result property="sortNo"    column="sort_no"    />
        <result property="createBy"    column="create_by"    />
        <result property="updateBy"    column="update_by"    />
    </resultMap>

    <sql id="selectRobotOpsFaqVo">
        select f.id, f.category_type, f.question, f.answer, f.sort_no, f.status, f.remark, f.create_time, f.update_time, f.create_by, f.update_by,
               (select count(*) from robot_ops_faq_similar s where s.faq_id = f.id) as similar_count
        from robot_ops_faq f
    </sql>

    <select id="selectRobotOpsFaqList" parameterType="RobotOpsFaq" resultMap="RobotOpsFaqResult">
        <include refid="selectRobotOpsFaqVo"/>
        <where>
            <if test="categoryType != null  and categoryType != ''"> and category_type = #{categoryType}</if>
            <if test="question != null  and question != ''"> and question like concat('%', #{question}, '%')</if>
            <if test="answer != null  and answer != ''"> and answer = #{answer}</if>
            <if test="sortNo != null "> and sort_no = #{sortNo}</if>
            <if test="status != null  and status != ''"> and status = #{status}</if>
        </where>
    </select>

    <select id="selectRobotOpsFaqById" parameterType="Long" resultMap="RobotOpsFaqRobotOpsFaqSimilarResult">
        select f.id, f.category_type, f.question, f.answer, f.sort_no, f.status, f.remark, f.create_time, f.update_time, f.create_by, f.update_by,
               (select count(*) from robot_ops_faq_similar s where s.faq_id = f.id) as similar_count
        from robot_ops_faq f
        where f.id = #{id}
    </select>

    <select id="selectRobotOpsFaqSimilarList" resultMap="RobotOpsFaqSimilarResult">
        select id, faq_id, similar_question, sort_no, create_by, update_by
        from robot_ops_faq_similar
        where faq_id = #{faq_id}
    </select>

    <insert id="insertRobotOpsFaq" parameterType="RobotOpsFaq" useGeneratedKeys="true" keyProperty="id">
        insert into robot_ops_faq
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="categoryType != null">category_type,</if>
            <if test="question != null and question != ''">question,</if>
            <if test="answer != null and answer != ''">answer,</if>
            <if test="sortNo != null">sort_no,</if>
            <if test="status != null and status != ''">status,</if>
            <if test="remark != null">remark,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="createBy != null">create_by,</if>
            <if test="updateBy != null">update_by,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="categoryType != null">#{categoryType},</if>
            <if test="question != null and question != ''">#{question},</if>
            <if test="answer != null and answer != ''">#{answer},</if>
            <if test="sortNo != null">#{sortNo},</if>
            <if test="status != null and status != ''">#{status},</if>
            <if test="remark != null">#{remark},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="updateBy != null">#{updateBy},</if>
         </trim>
    </insert>

    <update id="updateRobotOpsFaq" parameterType="RobotOpsFaq">
        update robot_ops_faq
        <trim prefix="SET" suffixOverrides=",">
            <if test="categoryType != null">category_type = #{categoryType},</if>
            <if test="question != null and question != ''">question = #{question},</if>
            <if test="answer != null and answer != ''">answer = #{answer},</if>
            <if test="sortNo != null">sort_no = #{sortNo},</if>
            <if test="status != null and status != ''">status = #{status},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteRobotOpsFaqById" parameterType="Long">
        delete from robot_ops_faq where id = #{id}
    </delete>

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

    <delete id="deleteRobotOpsFaqSimilarByFaqIds" parameterType="String">
        delete from robot_ops_faq_similar where faq_id in
        <foreach item="faqId" collection="array" open="(" separator="," close=")">
            #{faqId}
        </foreach>
    </delete>

    <delete id="deleteRobotOpsFaqSimilarByFaqId" parameterType="Long">
        delete from robot_ops_faq_similar where faq_id = #{faqId}
    </delete>

    <insert id="batchRobotOpsFaqSimilar">
        insert into robot_ops_faq_similar( id, faq_id, similar_question, sort_no, create_by, update_by) values
        <foreach item="item" index="index" collection="list" separator=",">
            ( #{item.id}, #{item.faqId}, #{item.similarQuestion}, #{item.sortNo}, #{item.createBy}, #{item.updateBy})
        </foreach>
    </insert>
</mapper>
