<?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.achievement.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 ua.id = #{filter.ID}
                </if>
                <if test="filter.userID != null and filter.userID != ''">
                    AND ua.user_id = #{filter.userID}
                </if>
                <if test="filter.achievementID != null and filter.achievementID != ''">
                    AND ua.achievement_id = #{filter.achievementID}
                </if>
            </if>
        </where>
    </sql>

    <!-- 查询用户成就列表 -->
    <select id="selectUserAchievements" resultMap="BaseResultMap">
        SELECT
        ua.id, ua.user_id, ua.achievement_id, ua.obtain_time
        FROM tb_user_achievement ua
        <include refid="Base_Where_Clause" />
        ORDER BY ua.obtain_time DESC
    </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}
        LIMIT 1
    </select>

    <!-- 根据用户ID和成就类型查询成就记录 -->
    <select id="selectByUserIdAndType" resultMap="BaseResultMap">
        SELECT
        ua.id, ua.user_id, ua.achievement_id, ua.obtain_time
        FROM tb_user_achievement ua
        INNER JOIN tb_achievement a ON ua.achievement_id = a.achievement_id
        WHERE ua.user_id = #{userId} AND a.type = #{type} AND a.deleted = 0
        ORDER BY ua.obtain_time DESC
    </select>

    <!-- 查询用户成就统计 -->
    <select id="selectAchievementStatistics" resultType="map">
        SELECT
            COUNT(*) AS totalAchievements,
            IFNULL(SUM(a.points), 0) AS totalPoints,
            COUNT(DISTINCT a.type) AS achievementTypes,
            MAX(ua.obtain_time) AS latestObtainTime
        FROM tb_user_achievement ua
        INNER JOIN tb_achievement a ON ua.achievement_id = a.achievement_id
        WHERE ua.user_id = #{userId} AND a.deleted = 0
    </select>

    <!-- 查询成就的最近获得者 -->
    <select id="selectRecentAchievers" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM tb_user_achievement
        WHERE achievement_id = #{achievementId}
        ORDER BY obtain_time DESC
        <if test="limit != null and limit > 0">
            LIMIT #{limit}
        </if>
    </select>

    <!-- 查询成就排行榜（按用户获得成就数量） -->
    <select id="selectUserAchievementRanking" resultType="map">
        SELECT
            ua.user_id AS userID,
            u.username,
            u.nickname,
            COUNT(*) AS achievementCount,
            IFNULL(SUM(a.points), 0) AS totalPoints,
            MAX(ua.obtain_time) AS latestObtainTime
        FROM tb_user_achievement ua
        INNER JOIN tb_achievement a ON ua.achievement_id = a.achievement_id
        INNER JOIN tb_sys_user u ON ua.user_id = u.id
        WHERE a.deleted = 0
        GROUP BY ua.user_id, u.username, u.nickname
        ORDER BY achievementCount DESC, totalPoints DESC, latestObtainTime DESC
        <if test="limit != null and limit > 0">
            LIMIT #{limit}
        </if>
    </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">
        DELETE FROM tb_user_achievement
        WHERE id = #{id}
    </delete>

    <!-- 根据用户ID和成就ID删除 -->
    <delete id="deleteByUserIdAndAchievementId">
        DELETE FROM tb_user_achievement
        WHERE user_id = #{userId} AND achievement_id = #{achievementId}
    </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>

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

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

</mapper>
