src/main/java/com/pt5/pthouduan/mapper/RecommendMapper.java

package com.pt5.pthouduan.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.pt5.pthouduan.entity.UserBehavior;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;


@Repository
public interface RecommendMapper extends BaseMapper<UserBehavior> {


    @Insert("INSERT INTO user_behavior(user_id, torrent_id, behavior_type, weight, timestamp) " +
            "VALUES(#{userId}, #{torrentId}, #{behaviorType}, #{weight}, #{timestamp})")
    int insertUserBehavior(UserBehavior userBehavior);

    @Select("SELECT COALESCE(SUM(weight), 0) FROM user_behavior WHERE user_id = #{userId}")
    double sumWeightsByUserId(@Param("userId") Long userId);
    /**
     * 查找用户最喜欢的分类
     */
    @Select("SELECT torrent.categoryid as categoryId, SUM(ub.weight) as totalWeight " +
            "FROM user_behavior ub " +
            "JOIN torrent ON ub.torrent_id = torrent.torrentid " +
            "WHERE ub.user_id = #{userId} " +
            "GROUP BY torrent.categoryid " +
            "ORDER BY totalWeight DESC ")
    List<Map<String, Object>> findFavoriteCategories(@Param("userId") Long userId);

    /**
     * 统计用户行为总数
     */
    @Select("SELECT COUNT(*) FROM user_behavior WHERE user_id = #{userId}")
    long countByUserId(@Param("userId") Long userId);

    /**
     * 获取用户最近的行为记录
     */
    @Select("SELECT * FROM user_behavior WHERE user_id = #{userId} ORDER BY timestamp DESC LIMIT #{limit}")
    List<UserBehavior> findRecentBehaviors(@Param("userId") Long userId, @Param("limit") int limit);

    /**
     * 获取用户对特定资源的行为记录
     */
    @Select("SELECT * FROM user_behavior WHERE user_id = #{userId} AND torrent_id = #{torrentId}")
    List<UserBehavior> findByUserAndTorrent(@Param("userId") Long userId, @Param("torrentId") Long torrentId);

    /**
     * 获取用户的高权重行为
     */
    @Select("SELECT * FROM user_behavior WHERE user_id = #{userId} AND weight >= #{minWeight}")
    List<UserBehavior> findHighWeightBehaviors(@Param("userId") Long userId, @Param("minWeight") Integer minWeight);

    /**
     * 获取用户最近交互的资源ID
     */
    @Select("SELECT DISTINCT torrent_id FROM user_behavior " +
            "WHERE user_id = #{userId} AND timestamp >= DATE_SUB(NOW(), INTERVAL #{days} DAY)")
    List<Long> findRecentTorrentIds(@Param("userId") Long userId, @Param("days") int days);



    /**
     * 删除旧的行为记录
     */
    @Delete("DELETE FROM user_behavior WHERE timestamp < DATE_SUB(NOW(), INTERVAL #{days} DAY)")
    int deleteOldBehaviorsForAllUsers(@Param("days") int days);
}