<?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.UserAchievementMapper">

    <!-- 结果映射 -->
    <resultMap id="BaseResultMap" type="org.xyzh.common.dto.usercenter.TbUserAchievement">
        <id column="id" property="id" />
        <result column="user_id" property="userId" />
        <result column="achievement_id" property="achievementId" />
        <result column="obtain_time" property="obtainTime" />
    </resultMap>

    <!-- 字段列表 -->
    <sql id="Base_Column_List">
        id, user_id, achievement_id, obtain_time
    </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.userId != null and filter.userId != ''">
                    AND user_id = #{filter.userId}
                </if>
                <if test="filter.achievementId != null and filter.achievementId != ''">
                    AND achievement_id = #{filter.achievementId}
                </if>
                <if test="filter.obtainTime != null">
                    AND obtain_time = #{filter.obtainTime}
                </if>
            </if>
        </where>
    </sql>

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

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

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

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

    <!-- 根据条件查询记录（支持排序） -->
    <select id="selectListWithOrder" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_user_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_user_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="insertOne" parameterType="org.xyzh.common.dto.usercenter.TbUserAchievement">
        INSERT INTO tb_user_achievement (
            id, user_id, achievement_id, obtain_time
        ) VALUES (
            #{entity.id}, #{entity.userId}, #{entity.achievementId}, #{entity.obtainTime}
        )
    </insert>

    <!-- 批量插入记录 -->
    <insert id="batchInsert" parameterType="java.util.List">
        INSERT INTO tb_user_achievement (
            id, user_id, achievement_id, obtain_time
        ) VALUES
        <foreach collection="entities" item="entity" separator=",">
            (
                #{entity.id}, #{entity.userId}, #{entity.achievementId}, #{entity.obtainTime}
            )
        </foreach>
    </insert>

    <!-- 根据ID更新记录 -->
    <update id="updateById" parameterType="org.xyzh.common.dto.usercenter.TbUserAchievement">
        UPDATE tb_user_achievement
        <set>
            <if test="entity.userId != null and entity.userId != ''">
                user_id = #{entity.userId},
            </if>
            <if test="entity.achievementId != null and entity.achievementId != ''">
                achievement_id = #{entity.achievementId},
            </if>
            <if test="entity.obtainTime != null">
                obtain_time = #{entity.obtainTime},
            </if>
        </set>
        WHERE id = #{entity.id}
    </update>

    <!-- 根据条件更新记录 -->
    <update id="updateByFilter" parameterType="org.xyzh.common.dto.usercenter.TbUserAchievement">
        UPDATE tb_user_achievement
        <set>
            <if test="entity.userId != null and entity.userId != ''">
                user_id = #{entity.userId},
            </if>
            <if test="entity.achievementId != null and entity.achievementId != ''">
                achievement_id = #{entity.achievementId},
            </if>
            <if test="entity.obtainTime != null">
                obtain_time = #{entity.obtainTime},
            </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_user_achievement
        <include refid="Base_Where_Clause" />
    </select>

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

    <!-- 查询用户成就列表 -->
    <select id="selectUserAchievements" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_user_achievement
        <include refid="Base_Where_Clause" />
    </select>

    <!-- 根据用户ID查询成就记录 -->
    <select id="selectByUserId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_user_achievement
        WHERE user_id = #{userId}
        ORDER BY obtain_time DESC
    </select>

    <!-- 根据用户ID和成就ID查询成就记录 -->
    <select id="selectByUserIdAndAchievementId" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_user_achievement
        WHERE user_id = #{userId} AND achievement_id = #{achievementId}
        ORDER BY obtain_time DESC
    </select>

    <!-- 根据用户ID查询成就记录（包含用户基本信息） -->
    <select id="selectUserAchievementsWithUser" resultMap="BaseResultMap">
        SELECT
        ua.id, ua.user_id, ua.achievement_id, ua.obtain_time
        FROM tb_user_achievement ua
        LEFT JOIN tb_sys_user u ON ua.user_id = u.id
        WHERE ua.user_id = #{userId}
        ORDER BY ua.obtain_time DESC
    </select>

    <!-- 查询用户成就统计 -->
    <select id="selectAchievementStatistics" resultMap="BaseResultMap">
        SELECT
        user_id,
        COUNT(*) as achievement_id,
        MAX(obtain_time) as obtain_time
        FROM tb_user_achievement
        WHERE user_id = #{userId}
        GROUP BY user_id
    </select>

    <!-- 插入用户成就 -->
    <insert id="insertUserAchievement" parameterType="org.xyzh.common.dto.usercenter.TbUserAchievement">
        INSERT INTO tb_user_achievement (
            id, user_id, achievement_id, obtain_time
        ) VALUES (
            #{id}, #{userId}, #{achievementId}, #{obtainTime}
        )
    </insert>

    <!-- 更新用户成就 -->
    <update id="updateUserAchievement" parameterType="org.xyzh.common.dto.usercenter.TbUserAchievement">
        UPDATE tb_user_achievement
        <set>
            <if test="userId != null and userId != ''">
                user_id = #{userId},
            </if>
            <if test="achievementId != null and achievementId != ''">
                achievement_id = #{achievementId},
            </if>
            <if test="obtainTime != null">
                obtain_time = #{obtainTime},
            </if>
        </set>
        WHERE id = #{id}
    </update>

    <!-- 删除用户成就 -->
    <delete id="deleteUserAchievement" parameterType="org.xyzh.common.dto.usercenter.TbUserAchievement">
        DELETE FROM tb_user_achievement
        WHERE id = #{id}
    </delete>

    <!-- 批量插入用户成就 -->
    <insert id="batchInsertUserAchievements" parameterType="java.util.List">
        INSERT INTO tb_user_achievement (
            id, user_id, achievement_id, obtain_time
        ) VALUES
        <foreach collection="userAchievementList" item="item" separator=",">
            (
                #{item.id}, #{item.userId}, #{item.achievementId}, #{item.obtainTime}
            )
        </foreach>
    </insert>

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

    <!-- 分页查询用户成就 -->
    <select id="selectUserAchievementsPage" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_user_achievement
        <include refid="Base_Where_Clause" />
        ORDER BY obtain_time DESC
        LIMIT #{pageParam.pageSize} OFFSET #{pageParam.offset}
    </select>

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

</mapper>