math_cms/app/Services/StudentProgressService.php

<?php

namespace App\Services;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Cache;

class StudentProgressService
{
    /**
     * 获取单个学生的学习进度
     */
    public function getProgress(string $studentId): array
    {
        Log::info('获取学生学习进度', ['student_id' => $studentId]);

        // 获取知识图谱结构
        $graphData = $this->getKnowledgeGraphStructure();
        $leafKpCodes = $graphData['leafKpCodes'];
        $leafKpCodesSet = $graphData['leafKpCodesSet'];
        $kpCodes = $graphData['kpCodes'];
        $maxChildScore = count($leafKpCodes) * 1.0;

        // 获取学生掌握度数据
        $mergedData = $this->getStudentMasteryData($studentId);

        // 如果没有数据，返回空结构（和批量接口行为一致）
        if (empty($mergedData)) {
            return [
                'success' => true,
                'data' => [
                    'student_id' => $studentId,
                    'learning_progress' => 0,
                    'learning_progress_percentage' => 0,
                    'mastered_child_count' => 0,
                    'total_child_count' => count($leafKpCodes),
                    'child_mastery_sum' => 0,
                    'has_data' => false,
                    'calculated_at' => now()->toISOString()
                ],
                'message' => '该学生暂无学习数据'
            ];
        }

        // 筛选叶子节点
        $childMasteryData = collect($mergedData)->filter(function ($item) use ($leafKpCodesSet) {
            return isset($leafKpCodesSet[$item['kp_code']]);
        });

        // 如果没有子知识点数据，也返回空结构
        if ($childMasteryData->isEmpty()) {
            return [
                'success' => true,
                'data' => [
                    'student_id' => $studentId,
                    'learning_progress' => 0,
                    'learning_progress_percentage' => 0,
                    'mastered_child_count' => 0,
                    'total_child_count' => count($leafKpCodes),
                    'child_mastery_sum' => 0,
                    'has_data' => false,
                    'calculated_at' => now()->toISOString()
                ],
                'message' => '该学生暂无子知识点学习数据'
            ];
        }

        // 计算学习进度
        $totalChildMasterySum = $childMasteryData->sum('mastery_level');
        $learningProgress = $maxChildScore > 0 ? ($totalChildMasterySum / $maxChildScore) : 0.0;

        // 统计信息
        $statistics = [
            'total_knowledge_points' => count($kpCodes),
            'child_knowledge_points' => count($leafKpCodes),
            'student_mastered_child_count' => $childMasteryData->count(),
            'student_mastered_child_percentage' => round(($childMasteryData->count() / count($leafKpCodes)) * 100, 2),
            'child_mastery_sum' => round($totalChildMasterySum, 4),
            'max_child_score' => round($maxChildScore, 4),
            'learning_progress_percentage' => round($learningProgress * 100, 2),
            'data_source' => implode(', ', collect($mergedData)->pluck('source_table')->unique()->toArray()),
            'child_mastery_max' => round($childMasteryData->max('mastery_level'), 4),
            'child_mastery_min' => round($childMasteryData->min('mastery_level'), 4),
            'child_mastery_avg' => round($childMasteryData->avg('mastery_level'), 4),
        ];

        Log::info('学生学习进度计算成功', [
            'student_id' => $studentId,
            'learning_progress' => $learningProgress,
        ]);

        return [
            'success' => true,
            'data' => [
                'student_id' => $studentId,
                'learning_progress' => round($learningProgress, 6),
                'learning_progress_percentage' => round($learningProgress * 100, 2),
                'mastered_child_count' => $childMasteryData->count(),
                'total_child_count' => count($leafKpCodes),
                'child_mastery_sum' => round($totalChildMasterySum, 4),
                'has_data' => true,
                'child_knowledge_points' => $childMasteryData->values()->toArray(),
                'statistics' => $statistics,
                'calculated_at' => now()->toISOString()
            ],
            'message' => '学习进度计算成功'
        ];
    }

    /**
     * 批量获取学生学习进度
     */
    public function getBatchProgress(array $studentIds): array
    {
        if (empty($studentIds)) {
            return [
                'success' => false,
                'error' => 'student_ids 不能为空'
            ];
        }

        if (count($studentIds) > 100) {
            return [
                'success' => false,
                'error' => '单次最多查询 100 个学生'
            ];
        }

        Log::info('批量获取学生学习进度', ['count' => count($studentIds)]);

        // 获取知识图谱结构（所有学生共用）
        $graphData = $this->getKnowledgeGraphStructure();
        $leafKpCodes = $graphData['leafKpCodes'];
        $leafKpCodesSet = $graphData['leafKpCodesSet'];
        $maxChildScore = count($leafKpCodes) * 1.0;

        // 批量获取掌握度数据
        $batchData = $this->getBatchStudentMasteryData($studentIds);
        $detailedData = $batchData['detailed'];
        $simpleData = $batchData['simple'];

        // 为每个学生计算学习进度
        $results = [];

        foreach ($studentIds as $studentId) {
            $studentId = (string) $studentId;
            $mergedData = [];

            // 从 student_knowledge_mastery 获取
            if (isset($detailedData[$studentId])) {
                foreach ($detailedData[$studentId] as $item) {
                    $mergedData[$item->kp_code] = [
                        'kp_code' => $item->kp_code,
                        'mastery_level' => (float) $item->mastery_level,
                    ];
                }
            }

            // 从 student_mastery 补充或更新
            if (isset($simpleData[$studentId])) {
                foreach ($simpleData[$studentId] as $item) {
                    $kpCode = $item->kp_code;
                    $masteryLevel = (float) $item->mastery;

                    if (isset($mergedData[$kpCode])) {
                        if ($masteryLevel > $mergedData[$kpCode]['mastery_level']) {
                            $mergedData[$kpCode]['mastery_level'] = $masteryLevel;
                        }
                    } else {
                        $mergedData[$kpCode] = [
                            'kp_code' => $kpCode,
                            'mastery_level' => $masteryLevel,
                        ];
                    }
                }
            }

            // 计算学习进度
            if (empty($mergedData)) {
                $results[$studentId] = [
                    'student_id' => $studentId,
                    'learning_progress' => 0,
                    'learning_progress_percentage' => 0,
                    'mastered_child_count' => 0,
                    'total_child_count' => count($leafKpCodes),
                    'has_data' => false,
                ];
                continue;
            }

            // 筛选叶子节点并计算
            $childMasterySum = 0;
            $masteredChildCount = 0;

            foreach ($mergedData as $item) {
                if (isset($leafKpCodesSet[$item['kp_code']])) {
                    $childMasterySum += $item['mastery_level'];
                    $masteredChildCount++;
                }
            }

            $learningProgress = $maxChildScore > 0 ? ($childMasterySum / $maxChildScore) : 0.0;

            $results[$studentId] = [
                'student_id' => $studentId,
                'learning_progress' => round($learningProgress, 6),
                'learning_progress_percentage' => round($learningProgress * 100, 2),
                'mastered_child_count' => $masteredChildCount,
                'total_child_count' => count($leafKpCodes),
                'child_mastery_sum' => round($childMasterySum, 4),
                'has_data' => true,
            ];
        }

        Log::info('批量学习进度计算完成', ['count' => count($results)]);

        return [
            'success' => true,
            'data' => $results,
            'meta' => [
                'total_students' => count($studentIds),
                'total_child_knowledge_points' => count($leafKpCodes),
                'calculated_at' => now()->toISOString(),
            ]
        ];
    }

    /**
     * 获取知识图谱结构（缓存 5 分钟）
     */
    private function getKnowledgeGraphStructure(): array
    {
        return Cache::remember('knowledge_graph_structure', 300, function () {
            $allKps = DB::connection('remote_mysql')
                ->table('knowledge_points')
                ->select(['kp_code', 'parent_kp_code'])
                ->get();

            $kpCodes = $allKps->pluck('kp_code')->toArray();
            $parentCodes = $allKps->whereNotNull('parent_kp_code')->pluck('parent_kp_code')->unique()->toArray();
            $leafKpCodes = array_values(array_diff($kpCodes, $parentCodes));

            return [
                'kpCodes' => $kpCodes,
                'leafKpCodes' => $leafKpCodes,
                'leafKpCodesSet' => array_flip($leafKpCodes),
            ];
        });
    }

    /**
     * 获取单个学生的掌握度数据
     */
    private function getStudentMasteryData(string $studentId): array
    {
        $mergedData = [];

        try {
            $detailedData = DB::connection('remote_mysql')
                ->table('student_knowledge_mastery')
                ->where('student_id', $studentId)
                ->select(['kp_code', 'mastery_level', 'total_attempts', 'correct_attempts', 'updated_at'])
                ->get();

            foreach ($detailedData as $item) {
                $mergedData[$item->kp_code] = [
                    'kp_code' => $item->kp_code,
                    'mastery_level' => (float) $item->mastery_level,
                    'total_attempts' => $item->total_attempts,
                    'correct_attempts' => $item->correct_attempts,
                    'source_table' => 'student_knowledge_mastery',
                    'updated_at' => $item->updated_at
                ];
            }
        } catch (\Exception $e) {
            Log::warning('从 student_knowledge_mastery 获取数据失败', ['error' => $e->getMessage()]);
        }

        try {
            $simpleData = DB::connection('remote_mysql')
                ->table('student_mastery')
                ->where('student_id', $studentId)
                ->select(['kp as kp_code', 'mastery', 'attempts as total_attempts', 'correct as correct_attempts', 'updated_at'])
                ->get();

            foreach ($simpleData as $item) {
                $kpCode = $item->kp_code;
                $masteryLevel = (float) $item->mastery;

                if (isset($mergedData[$kpCode])) {
                    if ($masteryLevel > $mergedData[$kpCode]['mastery_level']) {
                        $mergedData[$kpCode]['mastery_level'] = $masteryLevel;
                        $mergedData[$kpCode]['source_table'] = 'student_mastery (updated)';
                    }
                } else {
                    $mergedData[$kpCode] = [
                        'kp_code' => $kpCode,
                        'mastery_level' => $masteryLevel,
                        'total_attempts' => $item->total_attempts ?? 0,
                        'correct_attempts' => $item->correct_attempts ?? 0,
                        'source_table' => 'student_mastery',
                        'updated_at' => $item->updated_at ?? null
                    ];
                }
            }
        } catch (\Exception $e) {
            Log::warning('从 student_mastery 获取数据失败', ['error' => $e->getMessage()]);
        }

        return $mergedData;
    }

    /**
     * 批量获取学生掌握度数据
     */
    private function getBatchStudentMasteryData(array $studentIds): array
    {
        $detailedData = DB::connection('remote_mysql')
            ->table('student_knowledge_mastery')
            ->whereIn('student_id', $studentIds)
            ->select(['student_id', 'kp_code', 'mastery_level'])
            ->get()
            ->groupBy('student_id');

        $simpleData = DB::connection('remote_mysql')
            ->table('student_mastery')
            ->whereIn('student_id', $studentIds)
            ->select(['student_id', 'kp as kp_code', 'mastery'])
            ->get()
            ->groupBy('student_id');

        return [
            'detailed' => $detailedData->toArray(),
            'simple' => $simpleData->toArray(),
        ];
    }
}