<?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.iflytek.astron.console.commons.mapper.bot.BotFavoriteMapper">
    <sql id="check_from_where">
        FROM
        `bot_favorite` bf
        LEFT JOIN chat_bot_base a on bf.bot_id = a.id
        LEFT JOIN chat_bot_market b ON a.id = b.bot_id
        LEFT JOIN chat_list c ON a.id = c.bot_id and c.is_botweb = 0 and c.is_delete = 0 and c.root_flag = 1 and c.uid = #{uid}
        WHERE
        bf.uid = #{uid}
        AND a.is_delete = 0
        AND (b.is_delete is null or b.is_delete = 0)
    </sql>

    <select id="selectBotPage" resultType="com.iflytek.astron.console.commons.dto.bot.ChatBotMarketPage">
        SELECT
        bf.id,
        a.uid,
        c.id as chatId,
        a.id AS botId,
        b.id as marketBotId,
        a.bot_name AS botName,
        a.bot_desc AS botDesc,
        a.bot_type AS botType,
        a.bot_name_en AS botNameEn,
        b.hot_num as hotNum,
        a.prompt,
        a.avatar,
        CASE
        WHEN (b.bot_status IS NULL OR b.bot_status=0 OR b.bot_status=3) THEN
        -9
        ELSE
        b.bot_status
        END AS botStatus,
        a.create_time AS createTime,
        a.support_context AS supportContext,
        b.create_time As applyTime,
        ifnull(b.hot_num, 0) as hotNum
        FROM
            `bot_favorite` bf
                LEFT JOIN chat_bot_base a on bf.bot_id = a.id
                LEFT JOIN chat_bot_market b ON a.id = b.bot_id
                LEFT JOIN chat_list c ON a.id = c.bot_id and c.is_botweb = 0 and c.is_delete = 0 and c.root_flag = 1 and c.uid = #{uid}
        WHERE
            bf.uid = #{uid}
          AND a.is_delete = 0
          AND (b.is_delete is null or b.is_delete = 0)
        order by bf.id desc
        Limit #{offset},#{pageSize}
    </select>
    <select id="countBotPage" resultType="java.lang.Long">
        SELECT
            count(bf.id)
        FROM
            `bot_favorite` bf
                LEFT JOIN chat_bot_base a on bf.bot_id = a.id
                LEFT JOIN chat_bot_market b ON a.id = b.bot_id
                LEFT JOIN chat_list c ON a.id = c.bot_id AND c.is_botweb = 0 AND c.is_delete = 0 and c.root_flag = 1 and  c.uid = #{uid}
        WHERE
            bf.uid = #{uid}
          AND a.is_delete = 0
          AND (b.is_delete is null or b.is_delete = 0)
    </select>

</mapper>
