package cn.iocoder.yudao.module.im.dal.mysql.statistics;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * IM 数据看板 Mapper
 * <p>
 * 独立于业务 Mapper：所有统计 SQL 集中在此，仅服务于 manager 看板，不被其它业务调用，保持各业务 Mapper / Service 不受统计需求污染。
 *
 * @author 芋道源码
 */
@Mapper
public interface ImStatisticsManagerMapper {

    String NORMAL_MESSAGE_CONDITION = "type IN (101,102,103,104,105,107,108,115,125) AND status <> 2";

    // ==================== 用户 ====================

    /**
     * 用户总数（system_users）
     */
    @Select("SELECT COUNT(*) FROM system_users WHERE deleted = 0")
    Long selectTotalUserCount();

    /**
     * 区间内新增用户数
     */
    @Select("SELECT COUNT(*) FROM system_users " +
            "WHERE deleted = 0 AND create_time >= #{beginTime} AND create_time < #{endTime}")
    Long selectNewUserCount(@Param("beginTime") LocalDateTime beginTime,
                            @Param("endTime") LocalDateTime endTime);

    /**
     * 区间内活跃用户数：私聊或群聊发过消息的去重用户数
     */
    @Select("SELECT COUNT(DISTINCT user_id) FROM (" +
            "  SELECT sender_id AS user_id FROM im_private_message " +
            "  WHERE deleted = 0 AND " + NORMAL_MESSAGE_CONDITION +
            "  AND send_time >= #{beginTime} AND send_time < #{endTime}" +
            "  UNION ALL " +
            "  SELECT sender_id AS user_id FROM im_group_message " +
            "  WHERE deleted = 0 AND " + NORMAL_MESSAGE_CONDITION +
            "  AND send_time >= #{beginTime} AND send_time < #{endTime}" +
            ") t")
    Long selectActiveUserCount(@Param("beginTime") LocalDateTime beginTime,
                               @Param("endTime") LocalDateTime endTime);

    /**
     * 区间内每日新增用户数（按天分组）
     *
     * @return [{date: "yyyy-MM-dd", count: 123}, ...]
     */
    @Select("SELECT DATE(create_time) AS date, COUNT(*) AS count FROM system_users " +
            "WHERE deleted = 0 AND create_time >= #{beginTime} AND create_time < #{endTime} " +
            "GROUP BY DATE(create_time)")
    List<Map<String, Object>> selectNewUserDailyCount(@Param("beginTime") LocalDateTime beginTime,
                                                      @Param("endTime") LocalDateTime endTime);

    /**
     * 区间内每日活跃用户数（按天分组、跨私聊+群聊去重）
     */
    @Select("SELECT day AS date, COUNT(DISTINCT user_id) AS count FROM (" +
            "  SELECT DATE(send_time) AS day, sender_id AS user_id FROM im_private_message " +
            "  WHERE deleted = 0 AND " + NORMAL_MESSAGE_CONDITION +
            "  AND send_time >= #{beginTime} AND send_time < #{endTime}" +
            "  UNION ALL " +
            "  SELECT DATE(send_time) AS day, sender_id AS user_id FROM im_group_message " +
            "  WHERE deleted = 0 AND " + NORMAL_MESSAGE_CONDITION +
            "  AND send_time >= #{beginTime} AND send_time < #{endTime}" +
            ") t GROUP BY day")
    List<Map<String, Object>> selectActiveUserDailyCount(@Param("beginTime") LocalDateTime beginTime, @Param("endTime") LocalDateTime endTime);

    // ==================== 群 ====================

    /**
     * 当前有效群总数
     */
    @Select("SELECT COUNT(*) FROM im_group WHERE deleted = 0 AND status = 0")
    Long selectTotalGroupCount();

    /**
     * 区间内新建群数
     */
    @Select("SELECT COUNT(*) FROM im_group " +
            "WHERE deleted = 0 AND create_time >= #{beginTime} AND create_time < #{endTime}")
    Long selectNewGroupCount(@Param("beginTime") LocalDateTime beginTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 群规模分布（按群成员数分桶）
     *
     * @return [{range: "1-9 人", count: 123}, ...]
     */
    @Select("SELECT " +
            "  CASE " +
            "    WHEN cnt < 10 THEN '1-9 人' " +
            "    WHEN cnt < 50 THEN '10-49 人' " +
            "    WHEN cnt < 200 THEN '50-199 人' " +
            "    ELSE '200+ 人' " +
            "  END AS `range`, COUNT(*) AS count " +
            "FROM (" +
            "  SELECT g.id, COUNT(m.id) AS cnt " +
            "  FROM im_group g " +
            "  LEFT JOIN im_group_member m ON m.group_id = g.id AND m.status = 0 AND m.deleted = 0 " +
            "  WHERE g.deleted = 0 AND g.status = 0 " +
            "  GROUP BY g.id" +
            ") t " +
            "GROUP BY `range`")
    List<Map<String, Object>> selectGroupSizeDistribution();

    // ==================== 消息 ====================

    /**
     * 区间内私聊消息数
     */
    @Select("SELECT COUNT(*) FROM im_private_message " +
            "WHERE deleted = 0 AND " + NORMAL_MESSAGE_CONDITION +
            " AND send_time >= #{beginTime} AND send_time < #{endTime}")
    Long selectPrivateMessageCount(@Param("beginTime") LocalDateTime beginTime,
                                   @Param("endTime") LocalDateTime endTime);

    /**
     * 区间内群聊消息数
     */
    @Select("SELECT COUNT(*) FROM im_group_message " +
            "WHERE deleted = 0 AND " + NORMAL_MESSAGE_CONDITION +
            " AND send_time >= #{beginTime} AND send_time < #{endTime}")
    Long selectGroupMessageCount(@Param("beginTime") LocalDateTime beginTime,
                                 @Param("endTime") LocalDateTime endTime);

    /**
     * 区间内每日私聊消息数（按天分组）
     */
    @Select("SELECT DATE(send_time) AS date, COUNT(*) AS count FROM im_private_message " +
            "WHERE deleted = 0 AND " + NORMAL_MESSAGE_CONDITION +
            " AND send_time >= #{beginTime} AND send_time < #{endTime} " +
            "GROUP BY DATE(send_time)")
    List<Map<String, Object>> selectPrivateMessageDailyCount(@Param("beginTime") LocalDateTime beginTime,
                                                             @Param("endTime") LocalDateTime endTime);

    /**
     * 区间内每日群聊消息数（按天分组）
     */
    @Select("SELECT DATE(send_time) AS date, COUNT(*) AS count FROM im_group_message " +
            "WHERE deleted = 0 AND " + NORMAL_MESSAGE_CONDITION +
            " AND send_time >= #{beginTime} AND send_time < #{endTime} " +
            "GROUP BY DATE(send_time)")
    List<Map<String, Object>> selectGroupMessageDailyCount(@Param("beginTime") LocalDateTime beginTime,
                                                           @Param("endTime") LocalDateTime endTime);

    /**
     * 区间内内容类型分布（私聊+群聊合并）
     *
     * @return [{type: 0, count: 123}, ...]
     */
    @Select("SELECT type, COUNT(*) AS count FROM (" +
            "  SELECT type FROM im_private_message " +
            "  WHERE deleted = 0 AND " + NORMAL_MESSAGE_CONDITION +
            "  AND send_time >= #{beginTime} AND send_time < #{endTime}" +
            "  UNION ALL " +
            "  SELECT type FROM im_group_message " +
            "  WHERE deleted = 0 AND " + NORMAL_MESSAGE_CONDITION +
            "  AND send_time >= #{beginTime} AND send_time < #{endTime}" +
            ") t GROUP BY type")
    List<Map<String, Object>> selectMessageTypeDistribution(@Param("beginTime") LocalDateTime beginTime,
                                                            @Param("endTime") LocalDateTime endTime);

    /**
     * 区间内 TOP 发送者（私聊+群聊合并，按消息数倒序）
     *
     * @return [{userId: 1024, messageCount: 1500}, ...]
     */
    @Select("SELECT user_id AS userId, COUNT(*) AS messageCount FROM (" +
            "  SELECT sender_id AS user_id FROM im_private_message " +
            "  WHERE deleted = 0 AND " + NORMAL_MESSAGE_CONDITION +
            "  AND send_time >= #{beginTime} AND send_time < #{endTime}" +
            "  UNION ALL " +
            "  SELECT sender_id AS user_id FROM im_group_message " +
            "  WHERE deleted = 0 AND " + NORMAL_MESSAGE_CONDITION +
            "  AND send_time >= #{beginTime} AND send_time < #{endTime}" +
            ") t GROUP BY user_id ORDER BY messageCount DESC LIMIT #{limit}")
    List<Map<String, Object>> selectTopSenders(@Param("beginTime") LocalDateTime beginTime,
                                               @Param("endTime") LocalDateTime endTime,
                                               @Param("limit") int limit);

}