package com.lesingle.edu.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.lesingle.edu.entity.StudentRecord;
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;

@Mapper
public interface StudentRecordMapper extends BaseMapper<StudentRecord> {

    /**
     * 按学生 ID 批量统计：阅读次数、平均得分（专注度+参与度 1-5 转 0-100）
     */
    @Select("<script>" +
            "SELECT sr.student_id AS studentId, " +
            "  COUNT(sr.id) AS readingCount, " +
            "  ROUND(COALESCE(AVG((COALESCE(sr.focus,0)+COALESCE(sr.participation,0))/2.0), 0) * 20) AS avgScore " +
            "FROM student_record sr " +
            "INNER JOIN lesson l ON sr.lesson_id = l.id AND l.status = 'COMPLETED' AND l.deleted = 0 " +
            "WHERE sr.deleted = 0 AND sr.student_id IN " +
            "<foreach collection='studentIds' item='id' open='(' separator=',' close=')'>#{id}</foreach> " +
            "GROUP BY sr.student_id" +
            "</script>")
    List<Map<String, Object>> selectStudentStats(@Param("studentIds") List<Long> studentIds);

    /**
     * 统计指定时间段内有上课记录的去重学生数
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 活跃学生数
     */
    @Select("SELECT COUNT(DISTINCT sr.student_id) " +
            "FROM student_record sr " +
            "INNER JOIN lesson l ON sr.lesson_id = l.id " +
            "WHERE l.status = 'COMPLETED' " +
            "  AND l.end_datetime >= #{startTime} " +
            "  AND l.end_datetime < #{endTime} " +
            "  AND l.deleted = 0 " +
            "  AND sr.deleted = 0")
    Long countActiveStudents(@Param("startTime") LocalDateTime startTime,
                             @Param("endTime") LocalDateTime endTime);

    /**
     * 统计指定租户、指定时间段内有上课记录的去重学生数
     * @param tenantId 租户 ID
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 活跃学生数
     */
    @Select("SELECT COUNT(DISTINCT sr.student_id) " +
            "FROM student_record sr " +
            "INNER JOIN lesson l ON sr.lesson_id = l.id " +
            "WHERE l.tenant_id = #{tenantId} " +
            "  AND l.status = 'COMPLETED' " +
            "  AND l.end_datetime >= #{startTime} " +
            "  AND l.end_datetime < #{endTime} " +
            "  AND l.deleted = 0 " +
            "  AND sr.deleted = 0")
    Long countActiveStudentsByTenant(@Param("tenantId") Long tenantId,
                                      @Param("startTime") LocalDateTime startTime,
                                      @Param("endTime") LocalDateTime endTime);
}
