<?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.dora.mapper.WechatMessageTemplateMapper">

    <resultMap id="BaseResultMap" type="com.dora.entity.WechatMessageTemplate">
        <id column="id" property="id" />
        <result column="template_key" property="templateKey" />
        <result column="template_name" property="templateName" />
        <result column="template_type" property="templateType" />
        <result column="trigger_type" property="triggerType" />
        <result column="trigger_value" property="triggerValue" />
        <result column="content" property="content" />
        <result column="media_id" property="mediaId" />
        <result column="title" property="title" />
        <result column="description" property="description" />
        <result column="pic_url" property="picUrl" />
        <result column="url" property="url" />
        <result column="music_url" property="musicUrl" />
        <result column="hq_music_url" property="hqMusicUrl" />
        <result column="thumb_media_id" property="thumbMediaId" />
        <result column="is_enabled" property="isEnabled" />
        <result column="priority" property="priority" />
        <result column="create_time" property="createTime" />
        <result column="update_time" property="updateTime" />
    </resultMap>

    <sql id="Base_Column_List">
        id, template_key, template_name, template_type, trigger_type, trigger_value, content,
        media_id, title, description, pic_url, url, music_url, hq_music_url, thumb_media_id,
        is_enabled, priority, create_time, update_time
    </sql>

    <select id="selectById" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List" />
        FROM wechat_message_template
        WHERE id = #{id}
    </select>

    <select id="selectByTemplateKey" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List" />
        FROM wechat_message_template
        WHERE template_key = #{templateKey}
        LIMIT 1
    </select>

    <select id="selectByTrigger" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List" />
        FROM wechat_message_template
        WHERE trigger_type = #{triggerType}
        <if test="triggerValue != null and triggerValue != ''">
            AND trigger_value = #{triggerValue}
        </if>
        AND is_enabled = 1
        ORDER BY priority DESC
    </select>

    <select id="selectAllEnabled" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List" />
        FROM wechat_message_template
        WHERE is_enabled = 1
        ORDER BY priority DESC
    </select>

    <select id="selectEnabledByTriggerType" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List" />
        FROM wechat_message_template
        WHERE trigger_type = #{triggerType}
        AND is_enabled = 1
        ORDER BY priority DESC
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO wechat_message_template (
            template_key, template_name, template_type, trigger_type, trigger_value, content,
            media_id, title, description, pic_url, url, music_url, hq_music_url, thumb_media_id,
            is_enabled, priority
        ) VALUES (
            #{templateKey}, #{templateName}, #{templateType}, #{triggerType}, #{triggerValue}, #{content},
            #{mediaId}, #{title}, #{description}, #{picUrl}, #{url}, #{musicUrl}, #{hqMusicUrl}, #{thumbMediaId},
            #{isEnabled}, #{priority}
        )
    </insert>

    <update id="updateById">
        UPDATE wechat_message_template
        SET template_name = #{templateName},
            template_type = #{templateType},
            trigger_type = #{triggerType},
            trigger_value = #{triggerValue},
            content = #{content},
            media_id = #{mediaId},
            title = #{title},
            description = #{description},
            pic_url = #{picUrl},
            url = #{url},
            music_url = #{musicUrl},
            hq_music_url = #{hqMusicUrl},
            thumb_media_id = #{thumbMediaId},
            is_enabled = #{isEnabled},
            priority = #{priority},
            update_time = CURRENT_TIMESTAMP
        WHERE id = #{id}
    </update>

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

    <update id="updateStatusBatch">
        UPDATE wechat_message_template
        SET is_enabled = #{isEnabled}, update_time = CURRENT_TIMESTAMP
        WHERE id IN
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <select id="existsByTemplateKey" resultType="boolean">
        SELECT COUNT(*) > 0 FROM wechat_message_template
        WHERE template_key = #{templateKey}
        <if test="excludeId != null">
            AND id != #{excludeId}
        </if>
    </select>

    <select id="selectTemplateList" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List" />
        FROM wechat_message_template
        <where>
            <if test="templateName != null and templateName != ''">
                AND template_name LIKE CONCAT('%', #{templateName}, '%')
            </if>
            <if test="templateType != null and templateType != ''">
                AND template_type = #{templateType}
            </if>
            <if test="triggerType != null and triggerType != ''">
                AND trigger_type = #{triggerType}
            </if>
            <if test="isEnabled != null">
                AND is_enabled = #{isEnabled}
            </if>
        </where>
        ORDER BY priority DESC, create_time DESC
        LIMIT #{offset}, #{size}
    </select>

    <select id="countTemplateList" resultType="int">
        SELECT COUNT(*) FROM wechat_message_template
        <where>
            <if test="templateName != null and templateName != ''">
                AND template_name LIKE CONCAT('%', #{templateName}, '%')
            </if>
            <if test="templateType != null and templateType != ''">
                AND template_type = #{templateType}
            </if>
            <if test="triggerType != null and triggerType != ''">
                AND trigger_type = #{triggerType}
            </if>
            <if test="isEnabled != null">
                AND is_enabled = #{isEnabled}
            </if>
        </where>
    </select>

    <select id="selectDefaultReplyTemplate" resultMap="BaseResultMap">
        SELECT <include refid="Base_Column_List" />
        FROM wechat_message_template
        WHERE trigger_type = 'default'
        AND is_enabled = 1
        ORDER BY priority DESC
        LIMIT 1
    </select>

    <select id="selectByKeyword" resultMap="BaseResultMap">
        SELECT t.<include refid="Base_Column_List" />
        FROM wechat_message_template t
        INNER JOIN wechat_keyword_reply k ON t.id = k.template_id
        WHERE k.keyword = #{keyword}
        AND t.is_enabled = 1
        AND k.is_enabled = 1
        ORDER BY k.priority DESC, t.priority DESC
    </select>

</mapper>
