package com.lesingle.edu.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.lesingle.edu.dto.response.CourseReportResponse;
import com.lesingle.edu.dto.response.StudentReportResponse;
import com.lesingle.edu.dto.response.TeacherReportResponse;
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.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 数据报告 Mapper
 */
@Mapper
public interface ReportMapper extends BaseMapper<Lesson> {

    /**
     * 获取报告概览统计数据
     *
     * @param tenantId  租户 ID
     * @param startTime 统计开始时间
     * @param endTime   统计结束时间
     * @return 概览统计数据
     */
    @Select("<script>" +
            "SELECT " +
            "  (SELECT COUNT(*) FROM teacher WHERE tenant_id = #{tenantId} AND deleted = 0 AND status = 'ACTIVE') AS totalTeachers, " +
            "  (SELECT COUNT(*) FROM student WHERE tenant_id = #{tenantId} AND deleted = 0) AS totalStudents, " +
            "  (SELECT COUNT(*) FROM clazz WHERE tenant_id = #{tenantId} AND deleted = 0) AS totalClasses, " +
            "  (SELECT COUNT(*) FROM lesson WHERE tenant_id = #{tenantId} AND deleted = 0 AND status = 'COMPLETED' " +
            "    AND end_datetime &gt;= #{startTime} AND end_datetime &lt;= #{endTime}) AS monthlyLessons, " +
            "  (SELECT COUNT(*) FROM task_completion tc " +
            "    INNER JOIN task t ON tc.task_id = t.id " +
            "    WHERE t.tenant_id = #{tenantId} AND tc.deleted = 0 " +
            "    AND tc.created_at &gt;= #{startTime} AND tc.created_at &lt;= #{endTime}) AS monthlyTasksCompleted " +
            "FROM DUAL" +
            "</script>")
    Map<String, Object> getOverviewStats(
            @Param("tenantId") Long tenantId,
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime
    );

    /**
     * 获取教师报告统计
     *
     * @param tenantId  租户 ID
     * @param startTime 统计开始时间
     * @param endTime   统计结束时间
     * @param limit     返回数量限制
     * @return 教师报告列表
     */
    @Select("<script>" +
            "SELECT " +
            "  t.id AS teacherId, " +
            "  t.name AS teacherName, " +
            "  COUNT(DISTINCT l.id) AS lessonCount, " +
            "  COALESCE(AVG(sr.participation), 0) AS averageRating, " +
            "  MAX(l.end_datetime) AS lastLessonTime, " +
            "  COUNT(DISTINCT tc.id) AS taskCount " +
            "FROM teacher t " +
            "LEFT JOIN lesson l ON t.id = l.teacher_id " +
            "  AND l.deleted = 0 AND l.status = 'COMPLETED' " +
            "  AND l.end_datetime &gt;= #{startTime} AND l.end_datetime &lt;= #{endTime} " +
            "LEFT JOIN student_record sr ON l.id = sr.lesson_id " +
            "  AND sr.deleted = 0 " +
            "LEFT JOIN student s ON sr.student_id = s.id " +
            "  AND s.deleted = 0 " +
            "LEFT JOIN parent_student ps ON s.id = ps.student_id " +
            "  AND ps.deleted = 0 " +
            "LEFT JOIN task_completion tc ON ps.student_id = tc.student_id " +
            "  AND tc.deleted = 0 " +
            "  AND tc.created_at &gt;= #{startTime} AND tc.created_at &lt;= #{endTime} " +
            "WHERE t.tenant_id = #{tenantId} " +
            "  AND t.deleted = 0 " +
            "  AND t.status = 'ACTIVE' " +
            "GROUP BY t.id, t.name " +
            "ORDER BY lessonCount DESC, lastLessonTime DESC " +
            "LIMIT #{limit}" +
            "</script>")
    List<Map<String, Object>> getTeacherReports(
            @Param("tenantId") Long tenantId,
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("limit") int limit
    );

    /**
     * 获取课程报告统计
     *
     * @param tenantId  租户 ID
     * @param startTime 统计开始时间
     * @param endTime   统计结束时间
     * @param limit     返回数量限制
     * @return 课程报告列表
     */
    @Select("<script>" +
            "SELECT " +
            "  cp.id AS courseId, " +
            "  cp.name AS courseName, " +
            "  COUNT(DISTINCT l.id) AS lessonCount, " +
            "  COUNT(DISTINCT sr.student_id) AS studentCount, " +
            "  COALESCE(AVG(sr.participation), 0) AS averageRating, " +
            "  CAST(COUNT(DISTINCT CASE WHEN l.status = 'COMPLETED' THEN l.id END) AS DECIMAL(10,2)) / " +
            "    NULLIF(COUNT(DISTINCT l.id), 0) * 100 AS completionRate " +
            "FROM course_package cp " +
            "LEFT JOIN lesson l ON cp.id = l.course_id " +
            "  AND l.deleted = 0 " +
            "  AND l.end_datetime &gt;= #{startTime} AND l.end_datetime &lt;= #{endTime} " +
            "LEFT JOIN student_record sr ON l.id = sr.lesson_id " +
            "  AND sr.deleted = 0 " +
            "WHERE cp.tenant_id = #{tenantId} " +
            "  AND cp.deleted = 0 " +
            "GROUP BY cp.id, cp.name " +
            "ORDER BY lessonCount DESC " +
            "LIMIT #{limit}" +
            "</script>")
    List<Map<String, Object>> getCourseReports(
            @Param("tenantId") Long tenantId,
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("limit") int limit
    );

    /**
     * 获取学生报告统计
     *
     * @param tenantId  租户 ID
     * @param startTime 统计开始时间
     * @param endTime   统计结束时间
     * @param limit     返回数量限制
     * @return 学生报告列表
     */
    @Select("<script>" +
            "SELECT " +
            "  s.id AS studentId, " +
            "  s.name AS studentName, " +
            "  s.grade AS className, " +
            "  COUNT(DISTINCT tc.id) AS taskCount, " +
            "  0 AS readingCount, " +
            "  COUNT(DISTINCT gr.id) AS growthRecordCount, " +
            "  CAST(COUNT(DISTINCT CASE WHEN sr.attendance = 'present' THEN sr.id END) AS DECIMAL(10,2)) / " +
            "    NULLIF(COUNT(DISTINCT sr.id), 0) * 100 AS attendanceRate " +
            "FROM student s " +
            "LEFT JOIN task_completion tc ON s.id = tc.student_id " +
            "  AND tc.deleted = 0 " +
            "  AND tc.created_at &gt;= #{startTime} AND tc.created_at &lt;= #{endTime} " +
            "LEFT JOIN growth_record gr ON s.id = gr.student_id " +
            "  AND gr.deleted = 0 " +
            "  AND gr.created_at &gt;= #{startTime} AND gr.created_at &lt;= #{endTime} " +
            "LEFT JOIN student_record sr ON s.id = sr.student_id " +
            "  AND sr.deleted = 0 " +
            "WHERE s.tenant_id = #{tenantId} " +
            "  AND s.deleted = 0 " +
            "GROUP BY s.id, s.name, s.grade " +
            "ORDER BY taskCount DESC " +
            "LIMIT #{limit}" +
            "</script>")
    List<Map<String, Object>> getStudentReports(
            @Param("tenantId") Long tenantId,
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("limit") int limit
    );
}
