package com.lesingle.edu.mapper;

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

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

/**
 * 课程包数据统计 Mapper
 * 用于超管端「数据统计」页面
 */
@Mapper
public interface CoursePackageStatsMapper {

    /**
     * 获取课程包概览统计（总授课、教师数、学生数、平均评分）
     *
     * @param coursePackageId 课程包 ID
     * @return {totalLessons, totalTeachers, totalStudents, avgRating}
     */
    @org.apache.ibatis.annotations.Select(
            "SELECT " +
            "  COUNT(l.id) AS totalLessons, " +
            "  COUNT(DISTINCT l.teacher_id) AS totalTeachers, " +
            "  COUNT(DISTINCT sr.student_id) AS totalStudents, " +
            "  ROUND(AVG(CASE WHEN lf.id IS NOT NULL THEN " +
            "    (COALESCE(lf.design_quality,0) + COALESCE(lf.participation,0) + COALESCE(lf.goal_achievement,0)) / 3.0 " +
            "  ELSE NULL END), 2) AS avgRating " +
            "FROM lesson l " +
            "LEFT JOIN student_record sr ON l.id = sr.lesson_id AND sr.deleted = 0 " +
            "LEFT JOIN lesson_feedback lf ON l.id = lf.lesson_id AND lf.deleted = 0 " +
            "WHERE l.course_id = #{coursePackageId} " +
            "  AND l.deleted = 0 " +
            "  AND l.status = 'COMPLETED'"
    )
    Map<String, Object> getOverviewStats(@Param("coursePackageId") Long coursePackageId);

    /**
     * 获取授课记录趋势（按日期分组，最近 7 天或 30 天）
     *
     * @param coursePackageId 课程包 ID
     * @param startDate       开始日期
     * @param endDate         结束日期
     * @return [{date, count}]
     */
    @org.apache.ibatis.annotations.Select(
            "SELECT DATE_FORMAT(l.lesson_date, '%Y-%m-%d') AS date, COUNT(l.id) AS count " +
            "FROM lesson l " +
            "WHERE l.course_id = #{coursePackageId} " +
            "  AND l.deleted = 0 " +
            "  AND l.status = 'COMPLETED' " +
            "  AND l.lesson_date >= #{startDate} " +
            "  AND l.lesson_date <= #{endDate} " +
            "GROUP BY l.lesson_date " +
            "ORDER BY l.lesson_date ASC"
    )
    List<Map<String, Object>> getLessonTrend(
            @Param("coursePackageId") Long coursePackageId,
            @Param("startDate") LocalDate startDate,
            @Param("endDate") LocalDate endDate);

    /**
     * 获取教师反馈分布统计
     *
     * @param coursePackageId 课程包 ID
     * @return {designQuality, participation, goalAchievement, totalFeedbacks}
     */
    @org.apache.ibatis.annotations.Select(
            "SELECT " +
            "  ROUND(AVG(lf.design_quality), 2) AS designQuality, " +
            "  ROUND(AVG(lf.participation), 2) AS participation, " +
            "  ROUND(AVG(lf.goal_achievement), 2) AS goalAchievement, " +
            "  COUNT(lf.id) AS totalFeedbacks " +
            "FROM lesson_feedback lf " +
            "INNER JOIN lesson l ON lf.lesson_id = l.id " +
            "WHERE l.course_id = #{coursePackageId} " +
            "  AND l.deleted = 0 " +
            "  AND lf.deleted = 0"
    )
    Map<String, Object> getFeedbackDistribution(@Param("coursePackageId") Long coursePackageId);

    /**
     * 获取最近授课记录（含教师、班级、时间、时长、状态）
     *
     * @param coursePackageId 课程包 ID
     * @param limit           数量限制
     * @return 授课记录列表
     */
    @org.apache.ibatis.annotations.Select(
            "SELECT " +
            "  l.id AS lessonId, " +
            "  l.teacher_id AS teacherId, " +
            "  t.name AS teacherName, " +
            "  l.class_id AS classId, " +
            "  c.name AS className, " +
            "  l.start_datetime AS startDatetime, " +
            "  l.actual_duration AS actualDuration, " +
            "  l.status AS status " +
            "FROM lesson l " +
            "LEFT JOIN teacher t ON l.teacher_id = t.id " +
            "LEFT JOIN clazz c ON l.class_id = c.id " +
            "WHERE l.course_id = #{coursePackageId} " +
            "  AND l.deleted = 0 " +
            "ORDER BY COALESCE(l.start_datetime, l.created_at) DESC, l.id DESC " +
            "LIMIT #{limit}"
    )
    List<Map<String, Object>> getRecentLessons(
            @Param("coursePackageId") Long coursePackageId,
            @Param("limit") int limit);

    /**
     * 获取学生表现统计（专注度、参与度、兴趣度、理解度）
     *
     * @param coursePackageId 课程包 ID
     * @return {avgFocus, avgParticipation, avgInterest, avgUnderstanding}
     */
    @org.apache.ibatis.annotations.Select(
            "SELECT " +
            "  ROUND(AVG(sr.focus), 2) AS avgFocus, " +
            "  ROUND(AVG(sr.participation), 2) AS avgParticipation, " +
            "  ROUND(AVG(sr.interest), 2) AS avgInterest, " +
            "  ROUND(AVG(sr.understanding), 2) AS avgUnderstanding " +
            "FROM student_record sr " +
            "INNER JOIN lesson l ON sr.lesson_id = l.id " +
            "WHERE l.course_id = #{coursePackageId} " +
            "  AND l.deleted = 0 " +
            "  AND l.status = 'COMPLETED' " +
            "  AND sr.deleted = 0"
    )
    Map<String, Object> getStudentPerformance(@Param("coursePackageId") Long coursePackageId);

    /**
     * 批量获取课程包概览统计（用于列表页数据统计列）
     *
     * @param coursePackageIds 课程包 ID 列表
     * @return [{coursePackageId, usageCount, teacherCount, avgRating}]
     */
    @org.apache.ibatis.annotations.Select("<script>" +
            "SELECT " +
            "  l.course_id AS coursePackageId, " +
            "  COUNT(l.id) AS usageCount, " +
            "  COUNT(DISTINCT l.teacher_id) AS teacherCount, " +
            "  ROUND(AVG(CASE WHEN lf.id IS NOT NULL THEN (COALESCE(lf.design_quality,0)+COALESCE(lf.participation,0)+COALESCE(lf.goal_achievement,0))/3.0 END), 2) AS avgRating " +
            "FROM lesson l " +
            "LEFT JOIN lesson_feedback lf ON l.id = lf.lesson_id AND lf.deleted = 0 " +
            "WHERE l.deleted = 0 AND l.status = 'COMPLETED' " +
            "  AND l.course_id IN " +
            "<foreach item='id' collection='coursePackageIds' open='(' separator=',' close=')'>#{id}</foreach> " +
            "GROUP BY l.course_id" +
            "</script>")
    List<Map<String, Object>> getBatchOverviewStats(
            @Param("coursePackageIds") List<Long> coursePackageIds);
}
