<?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="org.xyzh.usercenter.mapper.AchievementMapper">

    <!-- 结果映射 -->
    <resultMap id="BaseResultMap" type="org.xyzh.common.dto.usercenter.TbAchievement">
        <id column="id" property="id" />
        <result column="achievement_id" property="achievementId" />
        <result column="name" property="name" />
        <result column="description" property="description" />
        <result column="icon" property="icon" />
        <result column="type" property="type" />
        <result column="level" property="level" />
        <result column="condition_type" property="conditionType" />
        <result column="condition_value" property="conditionValue" />
        <result column="points" property="points" />
        <result column="order_num" property="orderNum" />
        <result column="creator" property="creator" />
        <result column="updater" property="updater" />
        <result column="create_time" property="createTime" />
        <result column="update_time" property="updateTime" />
        <result column="delete_time" property="deleteTime" />
        <result column="deleted" property="deleted" />
    </resultMap>

    <!-- 字段列表 -->
    <sql id="Base_Column_List">
        id, achievement_id, name, description, icon, type, level, condition_type, 
        condition_value, points, order_num, creator, updater, create_time, update_time, 
        delete_time, deleted
    </sql>

    <!-- 查询条件 -->
    <sql id="Base_Where_Clause">
        <where>
            <if test="filter != null">
                <if test="filter.id != null and filter.id != ''">
                    AND id = #{filter.id}
                </if>
                <if test="filter.achievementId != null and filter.achievementId != ''">
                    AND achievement_id = #{filter.achievementId}
                </if>
                <if test="filter.name != null and filter.name != ''">
                    AND name LIKE CONCAT('%', #{filter.name}, '%')
                </if>
                <if test="filter.type != null">
                    AND type = #{filter.type}
                </if>
                <if test="filter.level != null">
                    AND level = #{filter.level}
                </if>
                <if test="filter.conditionType != null">
                    AND condition_type = #{filter.conditionType}
                </if>
                <if test="filter.points != null">
                    AND points = #{filter.points}
                </if>
                <if test="filter.creator != null and filter.creator != ''">
                    AND creator = #{filter.creator}
                </if>
                <if test="filter.deleted != null">
                    AND deleted = #{filter.deleted}
                </if>
                <if test="filter.createTime != null">
                    AND create_time = #{filter.createTime}
                </if>
                <if test="filter.updateTime != null">
                    AND update_time = #{filter.updateTime}
                </if>
            </if>
        </where>
    </sql>

    <!-- 根据ID查询单条记录 -->
    <select id="selectByIdCustom" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_achievement
        WHERE id = #{id}
    </select>

    <!-- 根据条件查询单条记录 -->
    <select id="selectOne" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_achievement
        <include refid="Base_Where_Clause" />
        LIMIT 1
    </select>

    <!-- 根据条件查询列表 -->
    <select id="selectList" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_achievement
        <include refid="Base_Where_Clause" />
    </select>

    <!-- 根据条件分页查询 -->
    <select id="selectPage" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_achievement
        <include refid="Base_Where_Clause" />
    </select>

    <!-- 根据条件查询记录（支持排序） -->
    <select id="selectListWithOrder" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_achievement
        <include refid="Base_Where_Clause" />
        <if test="orderBy != null and orderBy != ''">
            ORDER BY ${orderBy}
            <if test="orderDirection != null and orderDirection != ''">
                ${orderDirection}
            </if>
        </if>
    </select>

    <!-- 根据条件分页查询（支持排序） -->
    <select id="selectPageWithOrder" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_achievement
        <include refid="Base_Where_Clause" />
        <if test="orderBy != null and orderBy != ''">
            ORDER BY ${orderBy}
            <if test="orderDirection != null and orderDirection != ''">
                ${orderDirection}
            </if>
        </if>
    </select>

    <!-- 插入单条记录 -->
    <insert id="insertOneCustom" parameterType="org.xyzh.common.dto.usercenter.TbAchievement">
        INSERT INTO tb_achievement (
            id, achievement_id, name, description, icon, type, level, condition_type,
            condition_value, points, order_num, creator, updater, create_time, update_time,
            delete_time, deleted
        ) VALUES (
            #{entity.id}, #{entity.achievementId}, #{entity.name}, #{entity.description}, #{entity.icon},
            #{entity.type}, #{entity.level}, #{entity.conditionType}, #{entity.conditionValue},
            #{entity.points}, #{entity.orderNum}, #{entity.creator}, #{entity.updater},
            #{entity.createTime}, #{entity.updateTime}, #{entity.deleteTime}, #{entity.deleted}
        )
    </insert>

    <!-- 批量插入记录 -->
    <insert id="batchInsert" parameterType="java.util.List">
        INSERT INTO tb_achievement (
            id, achievement_id, name, description, icon, type, level, condition_type,
            condition_value, points, order_num, creator, updater, create_time, update_time,
            delete_time, deleted
        ) VALUES
        <foreach collection="entities" item="entity" separator=",">
            (
                #{entity.id}, #{entity.achievementId}, #{entity.name}, #{entity.description}, #{entity.icon},
                #{entity.type}, #{entity.level}, #{entity.conditionType}, #{entity.conditionValue},
                #{entity.points}, #{entity.orderNum}, #{entity.creator}, #{entity.updater},
                #{entity.createTime}, #{entity.updateTime}, #{entity.deleteTime}, #{entity.deleted}
            )
        </foreach>
    </insert>

    <!-- 根据ID更新记录 -->
    <update id="updateByIdCustom" parameterType="org.xyzh.common.dto.usercenter.TbAchievement">
        UPDATE tb_achievement
        <set>
            <if test="entity.achievementId != null and entity.achievementId != ''">
                achievement_id = #{entity.achievementId},
            </if>
            <if test="entity.name != null and entity.name != ''">
                name = #{entity.name},
            </if>
            <if test="entity.description != null and entity.description != ''">
                description = #{entity.description},
            </if>
            <if test="entity.icon != null and entity.icon != ''">
                icon = #{entity.icon},
            </if>
            <if test="entity.type != null">
                type = #{entity.type},
            </if>
            <if test="entity.level != null">
                level = #{entity.level},
            </if>
            <if test="entity.conditionType != null">
                condition_type = #{entity.conditionType},
            </if>
            <if test="entity.conditionValue != null">
                condition_value = #{entity.conditionValue},
            </if>
            <if test="entity.points != null">
                points = #{entity.points},
            </if>
            <if test="entity.orderNum != null">
                order_num = #{entity.orderNum},
            </if>
            <if test="entity.creator != null and entity.creator != ''">
                creator = #{entity.creator},
            </if>
            <if test="entity.updater != null and entity.updater != ''">
                updater = #{entity.updater},
            </if>
            <if test="entity.updateTime != null">
                update_time = #{entity.updateTime},
            </if>
            <if test="entity.deleteTime != null">
                delete_time = #{entity.deleteTime},
            </if>
            <if test="entity.deleted != null">
                deleted = #{entity.deleted},
            </if>
        </set>
        WHERE id = #{entity.id}
    </update>

    <!-- 根据条件更新记录 -->
    <update id="updateByFilter" parameterType="org.xyzh.common.dto.usercenter.TbAchievement">
        UPDATE tb_achievement
        <set>
            <if test="entity.achievementId != null and entity.achievementId != ''">
                achievement_id = #{entity.achievementId},
            </if>
            <if test="entity.name != null and entity.name != ''">
                name = #{entity.name},
            </if>
            <if test="entity.description != null and entity.description != ''">
                description = #{entity.description},
            </if>
            <if test="entity.icon != null and entity.icon != ''">
                icon = #{entity.icon},
            </if>
            <if test="entity.type != null">
                type = #{entity.type},
            </if>
            <if test="entity.level != null">
                level = #{entity.level},
            </if>
            <if test="entity.conditionType != null">
                condition_type = #{entity.conditionType},
            </if>
            <if test="entity.conditionValue != null">
                condition_value = #{entity.conditionValue},
            </if>
            <if test="entity.points != null">
                points = #{entity.points},
            </if>
            <if test="entity.orderNum != null">
                order_num = #{entity.orderNum},
            </if>
            <if test="entity.creator != null and entity.creator != ''">
                creator = #{entity.creator},
            </if>
            <if test="entity.updater != null and entity.updater != ''">
                updater = #{entity.updater},
            </if>
            <if test="entity.updateTime != null">
                update_time = #{entity.updateTime},
            </if>
            <if test="entity.deleteTime != null">
                delete_time = #{entity.deleteTime},
            </if>
            <if test="entity.deleted != null">
                deleted = #{entity.deleted},
            </if>
        </set>
        <include refid="Base_Where_Clause" />
    </update>

    <!-- 删除方法使用MyBatis-Plus的BaseMapper提供的方法 -->
    <!-- deleteById(Serializable id) - 根据ID删除 -->
    <!-- deleteBatchIds(Collection<? extends Serializable> idList) - 批量删除 -->
    <!-- deleteByMap(Map<String, Object> columnMap) - 根据Map删除 -->
    <!-- delete(Wrapper<T> queryWrapper) - 根据条件删除 -->

    <!-- 根据条件统计记录数 -->
    <select id="countByFilter" resultType="long">
        SELECT COUNT(1)
        FROM tb_achievement
        <include refid="Base_Where_Clause" />
    </select>

    <!-- 检查记录是否存在 -->
    <select id="existsByFilter" resultType="boolean">
        SELECT COUNT(1) > 0
        FROM tb_achievement
        <include refid="Base_Where_Clause" />
    </select>

    <!-- 查询成就列表 -->
    <select id="selectAchievements" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_achievement
        <include refid="Base_Where_Clause" />
    </select>

    <!-- 根据成就ID查询成就信息 -->
    <select id="selectByAchievementId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_achievement
        WHERE achievement_id = #{achievementId}
    </select>

    <!-- 根据类型查询成就列表 -->
    <select id="selectByType" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_achievement
        WHERE type = #{type}
        ORDER BY order_num ASC, create_time DESC
    </select>

    <!-- 根据类型和等级查询成就列表 -->
    <select id="selectByTypeAndLevel" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_achievement
        WHERE type = #{type} AND level = #{level}
        ORDER BY order_num ASC, create_time DESC
    </select>

    <!-- 查询成就排行榜 -->
    <select id="selectAchievementRanking" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_achievement
        WHERE deleted = 0
        ORDER BY points DESC, level DESC, order_num ASC
        <if test="limit != null and limit > 0">
            LIMIT #{limit}
        </if>
    </select>

    <!-- 检查成就名称是否存在 -->
    <select id="countByName" resultType="int">
        SELECT COUNT(1)
        FROM tb_achievement
        WHERE name = #{name} AND deleted = 0
        <if test="excludeId != null and excludeId != ''">
            AND id != #{excludeId}
        </if>
    </select>

    <!-- 插入成就 -->
    <insert id="insertAchievement" parameterType="org.xyzh.common.dto.usercenter.TbAchievement">
        INSERT INTO tb_achievement (
            id, achievement_id, name, description, icon, type, level, condition_type,
            condition_value, points, order_num, creator, updater, create_time, update_time,
            delete_time, deleted
        ) VALUES (
            #{id}, #{achievementId}, #{name}, #{description}, #{icon}, #{type}, #{level}, 
            #{conditionType}, #{conditionValue}, #{points}, #{orderNum}, #{creator}, #{updater}, 
            #{createTime}, #{updateTime}, #{deleteTime}, #{deleted}
        )
    </insert>

    <!-- 更新成就 -->
    <update id="updateAchievement" parameterType="org.xyzh.common.dto.usercenter.TbAchievement">
        UPDATE tb_achievement
        <set>
            <if test="achievementId != null and achievementId != ''">
                achievement_id = #{achievementId},
            </if>
            <if test="name != null and name != ''">
                name = #{name},
            </if>
            <if test="description != null and description != ''">
                description = #{description},
            </if>
            <if test="icon != null and icon != ''">
                icon = #{icon},
            </if>
            <if test="type != null">
                type = #{type},
            </if>
            <if test="level != null">
                level = #{level},
            </if>
            <if test="conditionType != null">
                condition_type = #{conditionType},
            </if>
            <if test="conditionValue != null">
                condition_value = #{conditionValue},
            </if>
            <if test="points != null">
                points = #{points},
            </if>
            <if test="orderNum != null">
                order_num = #{orderNum},
            </if>
            <if test="creator != null and creator != ''">
                creator = #{creator},
            </if>
            <if test="updater != null and updater != ''">
                updater = #{updater},
            </if>
            <if test="updateTime != null">
                update_time = #{updateTime},
            </if>
            <if test="deleteTime != null">
                delete_time = #{deleteTime},
            </if>
            <if test="deleted != null">
                deleted = #{deleted},
            </if>
        </set>
        WHERE id = #{id}
    </update>

    <!-- 删除成就 -->
    <delete id="deleteAchievement" parameterType="org.xyzh.common.dto.usercenter.TbAchievement">
        DELETE FROM tb_achievement
        WHERE id = #{id}
    </delete>

    <!-- 批量插入成就 -->
    <insert id="batchInsertAchievements" parameterType="java.util.List">
        INSERT INTO tb_achievement (
            id, achievement_id, name, description, icon, type, level, condition_type,
            condition_value, points, order_num, creator, updater, create_time, update_time,
            delete_time, deleted
        ) VALUES
        <foreach collection="achievementList" item="item" separator=",">
            (
                #{item.id}, #{item.achievementId}, #{item.name}, #{item.description}, #{item.icon},
                #{item.type}, #{item.level}, #{item.conditionType}, #{item.conditionValue},
                #{item.points}, #{item.orderNum}, #{item.creator}, #{item.updater},
                #{item.createTime}, #{item.updateTime}, #{item.deleteTime}, #{item.deleted}
            )
        </foreach>
    </insert>

    <!-- 批量删除成就 -->
    <delete id="batchDeleteAchievements">
        DELETE FROM tb_achievement
        WHERE id IN
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <!-- 分页查询成就 -->
    <select id="selectAchievementsPage" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_achievement
        <include refid="Base_Where_Clause" />
        ORDER BY order_num ASC, create_time DESC
        LIMIT #{pageParam.pageSize} OFFSET #{pageParam.offset}
    </select>

    <!-- 统计成就总数 -->
    <select id="countAchievements" resultType="long">
        SELECT COUNT(1)
        FROM tb_achievement
        <include refid="Base_Where_Clause" />
    </select>

</mapper>