math_cms/app/Services/StudentProgressService.php

<?php

namespace App\Services;

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

class StudentProgressService
{
    /**
     * 计算学生学习进度（总体掌握度）
     * 公式：所有子知识点掌握度累加值 / 所有子知识点掌握度的累加值
     *
     * @param string $studentId
     * @return array
     */
    public function calculateLearningProgress(string $studentId): array
    {
        try {
            // 1. 获取所有父知识点代码（需要排除的）
            $parentKpCodes = $this->getParentKnowledgePointCodes();

            // 2. 获取学生所有知识点的掌握度数据（合并两个表）
            $allMasteryData = $this->getMergedMasteryData($studentId);

            // 3. 过滤掉父知识点，只保留子知识点
            $childMasteryData = $allMasteryData->filter(function ($item) use ($parentKpCodes) {
                return !in_array($item['kp_code'], $parentKpCodes);
            });

            if ($childMasteryData->isEmpty()) {
                return [
                    'success' => false,
                    'error' => '该学生没有子知识点的掌握度数据'
                ];
            }

            // 4. 计算总体掌握度
            $totalMasterySum = $childMasteryData->sum('mastery_level');
            $masteryCount = $childMasteryData->count();
            $overallMastery = $masteryCount > 0 ? $totalMasterySum / $masteryCount : 0.0;

            // 5. 统计信息
            $statistics = [
                'total_child_kps' => $masteryCount,
                'average_mastery' => round($overallMastery, 4),
                'max_mastery' => round($childMasteryData->max('mastery_level'), 4),
                'min_mastery' => round($childMasteryData->min('mastery_level'), 4),
                'data_source' => $this->getDataSourceInfo($allMasteryData),
                'parent_kp_excluded' => count($parentKpCodes),
                'mastery_distribution' => $this->getMasteryDistribution($childMasteryData)
            ];

            $result = [
                'student_id' => $studentId,
                'overall_mastery' => round($overallMastery, 4),
                'child_knowledge_points' => $childMasteryData->values()->toArray(),
                'statistics' => $statistics,
                'calculated_at' => now()->toISOString()
            ];

            Log::info('学生学习进度计算成功', [
                'student_id' => $studentId,
                'overall_mastery' => $overallMastery,
                'child_kp_count' => $masteryCount,
                'parent_kp_excluded' => count($parentKpCodes)
            ]);

            return [
                'success' => true,
                'data' => $result
            ];

        } catch (\Exception $e) {
            Log::error('计算学生学习进度失败', [
                'student_id' => $studentId,
                'error' => $e->getMessage()
            ]);

            return [
                'success' => false,
                'error' => '计算学习进度时发生错误: ' . $e->getMessage()
            ];
        }
    }

    /**
     * 获取学生知识点掌握度详情
     *
     * @param string $studentId
     * @return array
     */
    public function getKnowledgePointDetails(string $studentId): array
    {
        try {
            // 获取合并的掌握度数据
            $masteryData = $this->getMergedMasteryData($studentId);

            // 获取父知识点列表
            $parentKpCodes = $this->getParentKnowledgePointCodes();

            // 分类数据
            $childData = [];
            $parentData = [];

            foreach ($masteryData as $item) {
                if (in_array($item['kp_code'], $parentKpCodes)) {
                    $parentData[] = $item;
                } else {
                    $childData[] = $item;
                }
            }

            // 获取知识点详细信息
            $knowledgePointDetails = $this->getKnowledgePointDetailsByCodes(
                array_column($masteryData->toArray(), 'kp_code')
            );

            // 合并知识点信息
            $enhancedData = [];
            foreach ($masteryData as $item) {
                $kpCode = $item['kp_code'];
                $detail = $knowledgePointDetails[$kpCode] ?? null;

                $enhancedData[] = array_merge($item, [
                    'knowledge_point_name' => $detail['name'] ?? '未知知识点',
                    'subject' => $detail['subject'] ?? null,
                    'grade' => $detail['grade'] ?? null,
                    'is_parent' => in_array($kpCode, $parentKpCodes),
                    'parent_kp_code' => $detail['parent_kp_code'] ?? null
                ]);
            }

            return [
                'student_id' => $studentId,
                'mastery_data' => $enhancedData,
                'summary' => [
                    'total_kps' => count($enhancedData),
                    'child_kps' => count($childData),
                    'parent_kps' => count($parentData),
                    'overall_child_mastery' => count($childData) > 0 ?
                        round(array_sum(array_column($childData, 'mastery_level')) / count($childData), 4) : 0.0
                ]
            ];

        } catch (\Exception $e) {
            Log::error('获取学生知识点掌握度详情失败', [
                'student_id' => $studentId,
                'error' => $e->getMessage()
            ]);

            throw $e;
        }
    }

    /**
     * 批量计算学生学习进度
     *
     * @param array $studentIds
     * @return array
     */
    public function batchCalculateLearningProgress(array $studentIds): array
    {
        $results = [];

        foreach ($studentIds as $studentId) {
            $result = $this->calculateLearningProgress($studentId);
            $results[] = [
                'student_id' => $studentId,
                'success' => $result['success'],
                'data' => $result['success'] ? $result['data'] : null,
                'error' => $result['success'] ? null : $result['error']
            ];
        }

        return $results;
    }

    /**
     * 获取所有父知识点代码
     *
     * @return array
     */
    private function getParentKnowledgePointCodes(): array
    {
        try {
            $parentCodes = DB::connection('remote_mysql')
                ->table('knowledge_points')
                ->whereNotNull('parent_kp_code')
                ->distinct()
                ->pluck('parent_kp_code')
                ->toArray();

            return array_filter($parentCodes);
        } catch (\Exception $e) {
            Log::error('获取父知识点代码失败', ['error' => $e->getMessage()]);
            return [];
        }
    }

    /**
     * 获取合并的学生掌握度数据（从两个表）
     *
     * @param string $studentId
     * @return Collection
     */
    private function getMergedMasteryData(string $studentId): Collection
    {
        $mergedData = [];

        try {
            // 从 student_knowledge_mastery 表获取数据
            $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()
                ->toArray();

            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 表获取数据失败', [
                'student_id' => $studentId,
                'error' => $e->getMessage()
            ]);
        }

        try {
            // 从 student_mastery 表获取数据（补充或覆盖）
            $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()
                ->toArray();

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

                // 如果已存在，优先使用 mastery_level 更高的数据
                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 表获取数据失败', [
                'student_id' => $studentId,
                'error' => $e->getMessage()
            ]);
        }

        return collect($mergedData)->values();
    }

    /**
     * 获取知识点详细信息
     *
     * @param array $kpCodes
     * @return array
     */
    private function getKnowledgePointDetailsByCodes(array $kpCodes): array
    {
        if (empty($kpCodes)) {
            return [];
        }

        try {
            $details = DB::connection('remote_mysql')
                ->table('knowledge_points')
                ->whereIn('kp_code', $kpCodes)
                ->select(['kp_code', 'name', 'subject', 'grade', 'parent_kp_code'])
                ->get()
                ->keyBy('kp_code')
                ->toArray();

            return $details;
        } catch (\Exception $e) {
            Log::warning('获取知识点详细信息失败', [
                'kp_codes' => $kpCodes,
                'error' => $e->getMessage()
            ]);
            return [];
        }
    }

    /**
     * 获取数据源信息
     *
     * @param Collection $masteryData
     * @return string
     */
    private function getDataSourceInfo(Collection $masteryData): string
    {
        $sourceTables = $masteryData->pluck('source_table')->unique()->toArray();

        if (count($sourceTables) === 1) {
            return $sourceTables[0];
        }

        return 'merged (' . implode(', ', $sourceTables) . ')';
    }

    /**
     * 获取掌握度分布
     *
     * @param Collection $masteryData
     * @return array
     */
    private function getMasteryDistribution(Collection $masteryData): array
    {
        $distribution = [
            'excellent' => 0,  // >= 0.9
            'good' => 0,       // 0.7 - 0.89
            'fair' => 0,       // 0.5 - 0.69
            'poor' => 0,       // < 0.5
            'unknown' => 0     // 无数据
        ];

        foreach ($masteryData as $item) {
            $mastery = $item['mastery_level'];

            if ($mastery >= 0.9) {
                $distribution['excellent']++;
            } elseif ($mastery >= 0.7) {
                $distribution['good']++;
            } elseif ($mastery >= 0.5) {
                $distribution['fair']++;
            } elseif ($mastery > 0) {
                $distribution['poor']++;
            } else {
                $distribution['unknown']++;
            }
        }

        return $distribution;
    }
}