WebWork/backend/services/studentService.js

const Score = require('../models/Score');
const Student = require('../models/Student');
const Course = require('../models/Course');
const db = require('../config/database');

class StudentService {
    static async getStudentCourses(userId) {
        const student = await Student.findById(userId);
        if (!student) {
            throw new Error('学生信息不存在');
        }

        const courses = await Course.findByStudentId(userId);
        return courses;
    }

    static async getCourseDetails(courseId) {
        const course = await Course.findDetailsById(courseId);
        if (!course) {
            throw new Error('课程不存在');
        }
        return course;
    }

    static async getStudentGrades(userId) {
        // 确认学生是否存在
        const student = await Student.findById(userId);
        if (!student) {
            throw new Error('学生信息不存在');
        }

        const grades = await Score.findByStudentId(userId);

        // 计算统计信息
        let totalCredits = 0;
        let totalGradePoints = 0;
        const totalCourses = grades.length;

        grades.forEach(grade => {
            const credit = parseFloat(grade.credit);
            totalCredits += credit;
            if (grade.grade_point) {
                totalGradePoints += parseFloat(grade.grade_point) * credit;
            }
        });

        const gpa = totalCredits > 0 ? (totalGradePoints / totalCredits).toFixed(2) : 0;
        const averageScore = totalCourses > 0 ? 
            (grades.reduce((sum, g) => sum + parseFloat(g.score || 0), 0) / totalCourses).toFixed(1) : 0;

        return {
            grades,
            statistics: {
                totalCourses,
                totalCredits,
                gpa,
                averageScore
            }
        };
    }

    static async getGradeDetails(scoreId, userId) {
        const grade = await Score.findDetailsById(scoreId, userId);
        if (!grade) {
            throw new Error('成绩不存在');
        }
        return grade;
    }

    static async getGradeStatistics(userId) {
        const student = await Student.findById(userId);
        if (!student) {
            throw new Error('学生信息不存在');
        }

        const sql = `
            SELECT g.total_score as score, g.grade_point, g.grade_level, 
                   c.course_name, CAST(c.credit AS CHAR) as credit, c.semester, c.academic_year, c.category
            FROM grades g
            JOIN courses c ON g.course_id = c.id
            WHERE g.student_id = ?
            ORDER BY c.semester ASC
        `;
        const grades = await db.query(sql, [userId]);

        // 1. 学期 GPA 趋势
        const semesterGPA = {};
        // 5. 课程类别分析
        const categoryStats = {};

        grades.forEach(g => {
            // console.log('Processing grade:', g);
            // 学期统计
            const semester = g.semester || '未知学期';
            if (!semesterGPA[semester]) {
                semesterGPA[semester] = { totalGP: 0, totalCredits: 0 };
            }
            const credit = parseFloat(g.credit || 0);
            semesterGPA[semester].totalGP += parseFloat(g.grade_point || 0) * credit;
            semesterGPA[semester].totalCredits += credit;

            // 类别统计
            const catName = g.category || '其他';
            if (!categoryStats[catName]) {
                categoryStats[catName] = { totalGP: 0, totalCredits: 0 };
            }
            categoryStats[catName].totalGP += parseFloat(g.grade_point || 0) * credit;
            categoryStats[catName].totalCredits += credit;
        });

        const trend = Object.keys(semesterGPA).map(semester => ({
            semester,
            gpa: (semesterGPA[semester].totalGP / semesterGPA[semester].totalCredits).toFixed(2)
        }));

        const categories = Object.keys(categoryStats).map(cat => {
            const stats = categoryStats[cat];
            const gpa = stats.totalCredits > 0 ? (stats.totalGP / stats.totalCredits).toFixed(2) : '0.00';
            return {
                category: cat,
                gpa: gpa,
                totalCredits: Number(stats.totalCredits) || 0
            };
        });

        // 2. 成绩等第分布
        const distribution = {
            'A': 0, 'B': 0, 'C': 0, 'D': 0, 'F': 0
        };
        grades.forEach(g => {
            if (distribution[g.grade_level] !== undefined) {
                distribution[g.grade_level]++;
            }
        });

        // 3. 学分进度
        let earnedCredits = 0;
        grades.forEach(g => {
            const score = parseFloat(g.score || 0);
            const credit = parseFloat(g.credit || 0);
            if (score >= 60) {
                earnedCredits += credit;
            }
        });

        // 4. 每学期学分详情
        const semesterCredits = Object.keys(semesterGPA).map(semester => ({
            semester,
            credits: semesterGPA[semester].totalCredits
        }));

        return {
            trend,
            distribution,
            categories,
            credits: {
                earned: earnedCredits,
                target: 160 // 假设目标学分为 160
            },
            semesterCredits
        };
    }
}

module.exports = StudentService;