package com.lesingle.edu.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.lesingle.edu.dto.response.CourseUsageStatsVO;
import com.lesingle.edu.entity.Lesson;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

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

@Mapper
public interface LessonMapper extends BaseMapper<Lesson> {

    /**
     * 获取学校端教师活跃度排行
     * <p>
     * 统计逻辑：
     * 1. 统计周期：由 startTime 和 endTime 参数控制
     * 2. 授课次数：统计周期内 COMPLETED 状态的 lesson 数量
     * 3. 课程数：统计周期内上过的不同课程（course_id）数量
     * 4. 最后活跃时间：统计周期内最近一次授课完成时间
     * 5. 排除已停用教师（teacher.status != 'ACTIVE'）
     * 6. 包含负责班级名称（逗号分隔）
     *
     * @param tenantId  租户 ID
     * @param startTime 统计周期开始时间
     * @param endTime   统计周期结束时间
     * @param limit     返回数量限制
     * @return 教师活跃度排行列表
     */
    @Select("SELECT " +
            "  t.id AS teacherId, " +
            "  t.name AS teacherName, " +
            "  t.status AS teacherStatus, " +
            "  COUNT(l.id) AS lessonCount, " +
            "  COUNT(DISTINCT l.course_id) AS courseCount, " +
            "  MAX(l.end_datetime) AS lastActiveAt, " +
            "  GROUP_CONCAT(DISTINCT c.name ORDER BY c.name SEPARATOR ',') AS classNames " +
            "FROM teacher t " +
            "LEFT JOIN lesson l ON t.id = l.teacher_id " +
            "  AND l.status = 'COMPLETED' " +
            "  AND l.end_datetime >= #{startTime} " +
            "  AND l.end_datetime <= #{endTime} " +
            "LEFT JOIN class_teacher ct ON t.id = ct.teacher_id " +
            "LEFT JOIN clazz c ON ct.class_id = c.id " +
            "WHERE t.tenant_id = #{tenantId} " +
            "  AND t.deleted = 0 " +
            "  AND t.status = 'ACTIVE' " +
            "GROUP BY t.id, t.name, t.status " +
            "HAVING lessonCount > 0 " +
            "ORDER BY lessonCount DESC, lastActiveAt DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> getTeacherActivityRank(
            @Param("tenantId") Long tenantId,
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("limit") int limit
    );

    /**
     * 获取教师端课程使用统计
     * <p>
     * 统计逻辑：
     * 1. 基于 lesson 表实际授课记录统计
     * 2. 统计周期内 COMPLETED 状态的 lesson 数量
     * 3. 参与学生数：去重统计 student_record 中的 student_id
     * 4. 平均时长：计算 lesson 的 start_datetime 到 end_datetime 的分钟差平均值
     * 5. 最后使用时间：最近一次授课完成时间
     *
     * @param tenantId  租户 ID
     * @param teacherId 教师 ID（可选，为 null 时统计该租户下所有）
     * @param startTime 统计开始时间
     * @param endTime   统计结束时间
     * @return 课程使用统计列表（TOP 10）
     */
    @SelectProvider(type = SqlProvider.class, method = "getCourseUsageStats")
    List<CourseUsageStatsVO> getCourseUsageStats(
            @Param("tenantId") Long tenantId,
            @Param("teacherId") Long teacherId,
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime
    );

    /**
     * SQL 提供者类（静态内部类）
     */
    class SqlProvider {

        /**
         * 获取课程使用统计 SQL
         */
        public String getCourseUsageStats() {
            return "SELECT " +
                    "  cp.id AS coursePackageId, " +
                    "  cp.name AS coursePackageName, " +
                    "  COUNT(l.id) AS usageCount, " +
                    "  COUNT(DISTINCT sr.student_id) AS studentCount, " +
                    "  COALESCE(AVG(TIMESTAMPDIFF(MINUTE, l.start_datetime, l.end_datetime)), 0) AS avgDuration, " +
                    "  MAX(l.end_datetime) AS lastUsedAt " +
                    "FROM lesson l " +
                    "INNER JOIN course_package cp ON l.course_id = cp.id " +
                    "LEFT JOIN student_record sr ON l.id = sr.lesson_id " +
                    "WHERE l.tenant_id = #{tenantId} " +
                    "  AND l.end_datetime >= #{startTime} " +
                    "  AND l.end_datetime <= #{endTime} " +
                    "  AND l.status = 'COMPLETED' " +
                    "  AND (#{teacherId} IS NULL OR l.teacher_id = #{teacherId}) " +
                    "GROUP BY cp.id, cp.name " +
                    "ORDER BY usageCount DESC " +
                    "LIMIT 10";
        }
    }

    /**
     * 按租户 ID 和课程 ID 列表统计课程使用次数（已完成状态）
     * 注意：此方法统计的是指定课程 ID 在指定租户下的使用次数
     * @param tenantId 租户 ID
     * @param courseIds 课程 ID 列表
     * @return 课程 ID 和使用次数的映射列表
     */
    @Select("<script>" +
            "SELECT l.course_id, COUNT(l.id) AS usageCount " +
            "FROM lesson l " +
            "INNER JOIN course_package cp ON l.course_id = cp.id " +  // 确保只统计存在的课程包
            "WHERE l.tenant_id = #{tenantId} " +
            "AND l.status = 'COMPLETED' " +
            "AND l.course_id IN " +
            "<foreach item='id' collection='courseIds' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "GROUP BY l.course_id" +
            "</script>")
    List<Map<String, Object>> countUsageByTenantAndCourseIds(
            @Param("tenantId") Long tenantId,
            @Param("courseIds") List<Long> courseIds);

    /**
     * 导出授课记录查询
     * <p>
     * 查询指定时间范围内的授课记录，包含班级、教师、课程、学生人数、平均参与度等信息
     *
     * @param tenantId  租户 ID
     * @param startDate 开始日期
     * @param endDate   结束日期
     * @return 授课记录导出列表
     */
    @Select("<script>" +
            "SELECT " +
            "  l.lesson_date, " +
            "  l.start_time, " +
            "  l.end_time, " +
            "  c.name AS className, " +
            "  t.name AS teacherName, " +
            "  cp.name AS courseName, " +
            "  COALESCE(cl.lesson_type, '集体') AS lessonType, " +
            "  COUNT(DISTINCT sr.student_id) AS studentCount, " +
            "  AVG(sr.participation) AS avgParticipation, " +
            "  l.notes AS feedbackContent " +
            "FROM lesson l " +
            "LEFT JOIN clazz c ON l.class_id = c.id " +
            "LEFT JOIN teacher t ON l.teacher_id = t.id " +
            "LEFT JOIN course_package cp ON l.course_id = cp.id " +
            "LEFT JOIN student_record sr ON l.id = sr.lesson_id " +
            "LEFT JOIN course_lesson cl ON l.course_id = cl.course_id AND cl.sort_order = 1 " +
            "WHERE l.tenant_id = #{tenantId} " +
            "  AND l.deleted = 0 " +
            "<if test='startDate != null'>" +
            "  AND l.lesson_date &gt;= #{startDate} " +
            "</if>" +
            "<if test='endDate != null'>" +
            "  AND l.lesson_date &lt;= #{endDate} " +
            "</if>" +
            "GROUP BY l.id, l.lesson_date, l.start_time, l.end_time, c.name, t.name, cp.name, cl.lesson_type, l.notes " +
            "ORDER BY l.lesson_date DESC, l.start_time DESC" +
            "</script>")
    List<Map<String, Object>> selectExportData(
            @Param("tenantId") Long tenantId,
            @Param("startDate") LocalDate startDate,
            @Param("endDate") LocalDate endDate);

    /**
     * 导出教师绩效统计查询
     * <p>
     * 统计指定时间范围内教师的授课情况
     *
     * @param tenantId  租户 ID
     * @param startDate 开始日期
     * @param endDate   结束日期
     * @return 教师绩效导出列表
     */
    @Select("<script>" +
            "SELECT " +
            "  t.name AS teacherName, " +
            "  GROUP_CONCAT(DISTINCT c.name ORDER BY c.name SEPARATOR ',') AS classNames, " +
            "  COUNT(l.id) AS lessonCount, " +
            "  COUNT(DISTINCT l.course_id) AS courseCount, " +
            "  CASE " +
            "    WHEN COUNT(l.id) >= 20 THEN 'HIGH' " +
            "    WHEN COUNT(l.id) >= 10 THEN 'MEDIUM' " +
            "    WHEN COUNT(l.id) >= 1 THEN 'LOW' " +
            "    ELSE 'INACTIVE' " +
            "  END AS activityLevel, " +
            "  MAX(l.end_datetime) AS lastActiveAt, " +
            "  AVG(sr.participation) AS avgParticipation " +
            "FROM teacher t " +
            "LEFT JOIN lesson l ON t.id = l.teacher_id " +
            "  AND l.status = 'COMPLETED' " +
            "  AND l.deleted = 0 " +
            "<if test='startDate != null'>" +
            "  AND l.lesson_date &gt;= #{startDate} " +
            "</if>" +
            "<if test='endDate != null'>" +
            "  AND l.lesson_date &lt;= #{endDate} " +
            "</if>" +
            "LEFT JOIN class_teacher ct ON t.id = ct.teacher_id " +
            "LEFT JOIN clazz c ON ct.class_id = c.id " +
            "LEFT JOIN student_record sr ON l.id = sr.lesson_id " +
            "WHERE t.tenant_id = #{tenantId} " +
            "  AND t.deleted = 0 " +
            "  AND t.status = 'ACTIVE' " +
            "GROUP BY t.id, t.name " +
            "ORDER BY lessonCount DESC, lastActiveAt DESC" +
            "</script>")
    List<Map<String, Object>> selectTeacherExportData(
            @Param("tenantId") Long tenantId,
            @Param("startDate") LocalDate startDate,
            @Param("endDate") LocalDate endDate);

    /**
     * 导出学生统计查询
     * <p>
     * 统计指定时间范围内学生的参与情况
     *
     * @param tenantId  租户 ID
     * @param classId   班级 ID（可选，为 null 时统计全校）
     * @return 学生统计导出列表
     */
    @Select("<script>" +
            "SELECT " +
            "  s.name AS studentName, " +
            "  s.gender, " +
            "  s.grade AS className, " +
            "  COUNT(DISTINCT l.id) AS lessonCount, " +
            "  AVG(sr.participation) AS avgParticipation, " +
            "  AVG(sr.focus) AS avgFocus " +
            "FROM student s " +
            "LEFT JOIN student_record sr ON s.id = sr.student_id " +
            "LEFT JOIN lesson l ON sr.lesson_id = l.id " +
            "  AND l.status = 'COMPLETED' " +
            "  AND l.deleted = 0 " +
            "WHERE s.tenant_id = #{tenantId} " +
            "  AND s.deleted = 0 " +
            "GROUP BY s.id, s.name, s.gender, s.grade " +
            "ORDER BY s.name" +
            "</script>")
    List<Map<String, Object>> selectStudentExportData(
            @Param("tenantId") Long tenantId,
            @Param("classId") Long classId);

    /**
     * 获取教师今日课程（带课程名称和班级名称）
     *
     * @param teacherId 教师 ID
     * @param today     今日日期
     * @return 今日课程列表
     */
    @Select("<script>" +
            "SELECT " +
            "  l.id, " +
            "  l.tenant_id, " +
            "  l.course_id, " +
            "  l.class_id, " +
            "  l.teacher_id, " +
            "  l.title, " +
            "  l.lesson_date, " +
            "  l.start_time, " +
            "  l.end_time, " +
            "  l.location, " +
            "  l.status, " +
            "  l.notes, " +
            "  l.created_at, " +
            "  l.updated_at, " +
            "  cp.name AS courseName, " +
            "  c.name AS className " +
            "FROM lesson l " +
            "LEFT JOIN course_package cp ON l.course_id = cp.id " +
            "LEFT JOIN clazz c ON l.class_id = c.id " +
            "WHERE l.teacher_id = #{teacherId} " +
            "  AND l.lesson_date = #{today} " +
            "  AND l.deleted = 0 " +
            "ORDER BY l.start_time ASC" +
            "</script>")
    List<Map<String, Object>> selectTodayLessonsWithDetails(
            @Param("teacherId") Long teacherId,
            @Param("today") LocalDate today);
}
