<?php

namespace App\Http\Controllers\Api\Admin;

use App\Http\Controllers\Api\ApiController;
use App\Models\Student;
use App\Models\Teacher;
use App\Models\StudentAttendance;
use App\Models\FeePayment;
use App\Models\Exam;
use App\Models\Mark;
use App\Models\SchoolClass;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\DB;

class ReportController extends ApiController
{
    /**
     * Generate comprehensive student reports
     */
    public function students(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'report_type' => 'required|in:list,performance,demographic,attendance_summary,fee_summary,promotion,transfer',
            'class_id' => 'nullable|exists:school_classes,id',
            'section_id' => 'nullable|exists:sections,id',
            'academic_year_id' => 'nullable|exists:academic_years,id',
            'gender' => 'nullable|in:male,female,other',
            'start_date' => 'nullable|date',
            'end_date' => 'nullable|date|after_or_equal:start_date',
        ]);

        if ($validator->fails()) {
            return $this->errorResponse('Validation failed', 422, $validator->errors());
        }

        $reportType = $request->report_type;

        switch ($reportType) {
            case 'list':
                return $this->studentListReport($request);
            case 'performance':
                return $this->studentPerformanceReport($request);
            case 'demographic':
                return $this->studentDemographicReport($request);
            case 'attendance_summary':
                return $this->studentAttendanceSummaryReport($request);
            case 'fee_summary':
                return $this->studentFeeSummaryReport($request);
            case 'promotion':
                return $this->studentPromotionReport($request);
            case 'transfer':
                return $this->studentTransferReport($request);
            default:
                return $this->errorResponse('Invalid report type', 400);
        }
    }

    /**
     * Student list report with filters
     */
    private function studentListReport(Request $request)
    {
        $perPage = $request->get('per_page', 50);

        $query = Student::with(['class', 'section', 'academicYear'])
            ->where('is_active', true);

        if ($request->has('class_id') && $request->class_id) {
            $query->where('class_id', $request->class_id);
        }

        if ($request->has('section_id') && $request->section_id) {
            $query->where('section_id', $request->section_id);
        }

        if ($request->has('gender') && $request->gender) {
            $query->where('gender', $request->gender);
        }

        $query->orderBy('roll_number');

        return $this->paginatedResponse($query, $perPage);
    }

    /**
     * Student performance report
     */
    private function studentPerformanceReport(Request $request)
    {
        $query = Student::with(['class', 'section', 'marks.exam', 'marks.subject']);

        if ($request->has('class_id') && $request->class_id) {
            $query->where('class_id', $request->class_id);
        }

        if ($request->has('section_id') && $request->section_id) {
            $query->where('section_id', $request->section_id);
        }

        $students = $query->get()->map(function ($student) {
            $totalMarks = $student->marks->sum('marks_obtained');
            $totalPossible = $student->marks->sum('total_marks');
            $percentage = $totalPossible > 0 ? round(($totalMarks / $totalPossible) * 100, 2) : 0;

            return [
                'id' => $student->id,
                'name' => $student->full_name,
                'roll_number' => $student->roll_number,
                'class' => $student->class->name ?? 'N/A',
                'section' => $student->section->name ?? 'N/A',
                'total_marks' => $totalMarks,
                'total_possible' => $totalPossible,
                'percentage' => $percentage,
                'exam_count' => $student->marks->pluck('exam_id')->unique()->count(),
                'performance_grade' => $this->getGrade($percentage),
            ];
        });

        return $this->successResponse([
            'students' => $students,
            'summary' => [
                'total_students' => $students->count(),
                'average_percentage' => $students->avg('percentage'),
                'highest_percentage' => $students->max('percentage'),
                'lowest_percentage' => $students->min('percentage'),
            ],
        ]);
    }

    /**
     * Student demographic report
     */
    private function studentDemographicReport(Request $request)
    {
        $query = Student::query();

        if ($request->has('class_id') && $request->class_id) {
            $query->where('class_id', $request->class_id);
        }

        // Gender distribution
        $genderDistribution = (clone $query)
            ->select('gender', DB::raw('count(*) as count'))
            ->groupBy('gender')
            ->get();

        // Religion distribution
        $religionDistribution = (clone $query)
            ->select('religion', DB::raw('count(*) as count'))
            ->whereNotNull('religion')
            ->groupBy('religion')
            ->get();

        // Category distribution
        $categoryDistribution = (clone $query)
            ->select('category', DB::raw('count(*) as count'))
            ->whereNotNull('category')
            ->groupBy('category')
            ->get();

        // Age distribution
        $ageDistribution = (clone $query)
            ->select(DB::raw('YEAR(CURDATE()) - YEAR(date_of_birth) as age'), DB::raw('count(*) as count'))
            ->whereNotNull('date_of_birth')
            ->groupBy('age')
            ->orderBy('age')
            ->get();

        // Blood group distribution
        $bloodGroupDistribution = (clone $query)
            ->select('blood_group', DB::raw('count(*) as count'))
            ->whereNotNull('blood_group')
            ->groupBy('blood_group')
            ->get();

        return $this->successResponse([
            'total_students' => $query->count(),
            'gender_distribution' => $genderDistribution,
            'religion_distribution' => $religionDistribution,
            'category_distribution' => $categoryDistribution,
            'age_distribution' => $ageDistribution,
            'blood_group_distribution' => $bloodGroupDistribution,
        ]);
    }

    /**
     * Student attendance summary report
     */
    private function studentAttendanceSummaryReport(Request $request)
    {
        $startDate = $request->start_date ?? now()->startOfMonth();
        $endDate = $request->end_date ?? now()->endOfMonth();

        $query = Student::with(['class', 'section']);

        if ($request->has('class_id') && $request->class_id) {
            $query->where('class_id', $request->class_id);
        }

        if ($request->has('section_id') && $request->section_id) {
            $query->where('section_id', $request->section_id);
        }

        $students = $query->get()->map(function ($student) use ($startDate, $endDate) {
            $attendanceRecords = StudentAttendance::where('student_id', $student->id)
                ->whereBetween('date', [$startDate, $endDate])
                ->get();

            $totalDays = $attendanceRecords->count();
            $presentDays = $attendanceRecords->where('status', 'present')->count();
            $absentDays = $attendanceRecords->where('status', 'absent')->count();
            $lateDays = $attendanceRecords->where('status', 'late')->count();
            $percentage = $totalDays > 0 ? round(($presentDays / $totalDays) * 100, 2) : 0;

            return [
                'student_id' => $student->id,
                'name' => $student->full_name,
                'roll_number' => $student->roll_number,
                'class' => $student->class->name ?? 'N/A',
                'section' => $student->section->name ?? 'N/A',
                'total_days' => $totalDays,
                'present_days' => $presentDays,
                'absent_days' => $absentDays,
                'late_days' => $lateDays,
                'attendance_percentage' => $percentage,
                'status' => $this->getAttendanceStatus($percentage),
            ];
        });

        return $this->successResponse([
            'students' => $students,
            'period' => [
                'start_date' => $startDate,
                'end_date' => $endDate,
            ],
            'summary' => [
                'total_students' => $students->count(),
                'average_attendance' => $students->avg('attendance_percentage'),
                'excellent_attendance' => $students->where('attendance_percentage', '>=', 90)->count(),
                'poor_attendance' => $students->where('attendance_percentage', '<', 75)->count(),
            ],
        ]);
    }

    /**
     * Student fee summary report
     */
    private function studentFeeSummaryReport(Request $request)
    {
        $query = Student::with(['class', 'section', 'feePayments']);

        if ($request->has('class_id') && $request->class_id) {
            $query->where('class_id', $request->class_id);
        }

        if ($request->has('section_id') && $request->section_id) {
            $query->where('section_id', $request->section_id);
        }

        $students = $query->get()->map(function ($student) {
            $totalDue = $student->feePayments->sum('amount_due');
            $totalPaid = $student->feePayments->sum('amount_paid');
            $totalBalance = $student->feePayments->sum('balance_amount');

            return [
                'student_id' => $student->id,
                'name' => $student->full_name,
                'roll_number' => $student->roll_number,
                'class' => $student->class->name ?? 'N/A',
                'section' => $student->section->name ?? 'N/A',
                'total_due' => $totalDue,
                'total_paid' => $totalPaid,
                'total_balance' => $totalBalance,
                'payment_status' => $totalBalance > 0 ? 'Pending' : 'Paid',
            ];
        });

        return $this->successResponse([
            'students' => $students,
            'summary' => [
                'total_students' => $students->count(),
                'total_due_amount' => $students->sum('total_due'),
                'total_paid_amount' => $students->sum('total_paid'),
                'total_balance_amount' => $students->sum('total_balance'),
                'paid_students' => $students->where('total_balance', 0)->count(),
                'pending_students' => $students->where('total_balance', '>', 0)->count(),
            ],
        ]);
    }

    /**
     * Student promotion report
     */
    private function studentPromotionReport(Request $request)
    {
        // TODO: Implement based on promotion criteria
        return $this->successResponse([
            'message' => 'Student promotion report - To be implemented based on school promotion criteria',
        ]);
    }

    /**
     * Student transfer report
     */
    private function studentTransferReport(Request $request)
    {
        $startDate = $request->start_date ?? now()->startOfYear();
        $endDate = $request->end_date ?? now();

        $transfers = Student::onlyTrashed()
            ->whereBetween('deleted_at', [$startDate, $endDate])
            ->with(['class', 'section'])
            ->get();

        return $this->successResponse([
            'transfers' => $transfers,
            'period' => [
                'start_date' => $startDate,
                'end_date' => $endDate,
            ],
            'total_transfers' => $transfers->count(),
        ]);
    }

    /**
     * Generate attendance reports
     */
    public function attendance(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'report_type' => 'required|in:daily,monthly,class_wise,student_wise,defaulters',
            'date' => 'nullable|date',
            'start_date' => 'nullable|date',
            'end_date' => 'nullable|date|after_or_equal:start_date',
            'class_id' => 'nullable|exists:school_classes,id',
            'section_id' => 'nullable|exists:sections,id',
            'student_id' => 'nullable|exists:students,id',
        ]);

        if ($validator->fails()) {
            return $this->errorResponse('Validation failed', 422, $validator->errors());
        }

        $reportType = $request->report_type;

        switch ($reportType) {
            case 'daily':
                return $this->dailyAttendanceReport($request);
            case 'monthly':
                return $this->monthlyAttendanceReport($request);
            case 'class_wise':
                return $this->classWiseAttendanceReport($request);
            case 'student_wise':
                return $this->studentWiseAttendanceReport($request);
            case 'defaulters':
                return $this->attendanceDefaultersReport($request);
            default:
                return $this->errorResponse('Invalid report type', 400);
        }
    }

    /**
     * Daily attendance report
     */
    private function dailyAttendanceReport(Request $request)
    {
        $date = $request->date ?? now()->toDateString();

        $query = StudentAttendance::with(['student', 'class', 'section'])
            ->whereDate('date', $date);

        if ($request->has('class_id') && $request->class_id) {
            $query->where('class_id', $request->class_id);
        }

        if ($request->has('section_id') && $request->section_id) {
            $query->where('section_id', $request->section_id);
        }

        $attendance = $query->get();

        $summary = [
            'date' => $date,
            'total' => $attendance->count(),
            'present' => $attendance->where('status', 'present')->count(),
            'absent' => $attendance->where('status', 'absent')->count(),
            'late' => $attendance->where('status', 'late')->count(),
            'excused' => $attendance->where('status', 'excused')->count(),
            'percentage' => $attendance->count() > 0
                ? round(($attendance->where('status', 'present')->count() / $attendance->count()) * 100, 2)
                : 0,
        ];

        return $this->successResponse([
            'attendance' => $attendance,
            'summary' => $summary,
        ]);
    }

    /**
     * Monthly attendance report
     */
    private function monthlyAttendanceReport(Request $request)
    {
        $startDate = $request->start_date ?? now()->startOfMonth();
        $endDate = $request->end_date ?? now()->endOfMonth();

        $query = StudentAttendance::whereBetween('date', [$startDate, $endDate]);

        if ($request->has('class_id') && $request->class_id) {
            $query->where('class_id', $request->class_id);
        }

        // Daily breakdown
        $dailyBreakdown = (clone $query)
            ->select(
                DB::raw('DATE(date) as date'),
                DB::raw('COUNT(*) as total'),
                DB::raw('SUM(CASE WHEN status = "present" THEN 1 ELSE 0 END) as present'),
                DB::raw('SUM(CASE WHEN status = "absent" THEN 1 ELSE 0 END) as absent'),
                DB::raw('SUM(CASE WHEN status = "late" THEN 1 ELSE 0 END) as late')
            )
            ->groupBy('date')
            ->orderBy('date')
            ->get();

        $totalRecords = $query->count();
        $presentCount = (clone $query)->where('status', 'present')->count();

        return $this->successResponse([
            'daily_breakdown' => $dailyBreakdown,
            'period' => [
                'start_date' => $startDate,
                'end_date' => $endDate,
            ],
            'summary' => [
                'total_records' => $totalRecords,
                'total_present' => $presentCount,
                'total_absent' => (clone $query)->where('status', 'absent')->count(),
                'overall_percentage' => $totalRecords > 0 ? round(($presentCount / $totalRecords) * 100, 2) : 0,
            ],
        ]);
    }

    /**
     * Class-wise attendance report
     */
    private function classWiseAttendanceReport(Request $request)
    {
        $startDate = $request->start_date ?? now()->startOfMonth();
        $endDate = $request->end_date ?? now()->endOfMonth();

        $classWiseData = StudentAttendance::with('class')
            ->whereBetween('date', [$startDate, $endDate])
            ->get()
            ->groupBy('class_id')
            ->map(function ($classAttendance, $classId) {
                $total = $classAttendance->count();
                $present = $classAttendance->where('status', 'present')->count();
                $className = $classAttendance->first()->class->name ?? 'Unknown';

                return [
                    'class_id' => $classId,
                    'class_name' => $className,
                    'total_records' => $total,
                    'present' => $present,
                    'absent' => $classAttendance->where('status', 'absent')->count(),
                    'late' => $classAttendance->where('status', 'late')->count(),
                    'attendance_percentage' => $total > 0 ? round(($present / $total) * 100, 2) : 0,
                ];
            })
            ->values();

        return $this->successResponse([
            'class_wise_data' => $classWiseData,
            'period' => [
                'start_date' => $startDate,
                'end_date' => $endDate,
            ],
        ]);
    }

    /**
     * Student-wise attendance report
     */
    private function studentWiseAttendanceReport(Request $request)
    {
        if (!$request->has('student_id')) {
            return $this->errorResponse('student_id is required for student-wise report', 422);
        }

        $startDate = $request->start_date ?? now()->startOfMonth();
        $endDate = $request->end_date ?? now()->endOfMonth();

        $student = Student::with(['class', 'section'])->find($request->student_id);

        if (!$student) {
            return $this->errorResponse('Student not found', 404);
        }

        $attendance = StudentAttendance::where('student_id', $request->student_id)
            ->whereBetween('date', [$startDate, $endDate])
            ->orderBy('date', 'desc')
            ->get();

        $total = $attendance->count();
        $present = $attendance->where('status', 'present')->count();

        return $this->successResponse([
            'student' => [
                'id' => $student->id,
                'name' => $student->full_name,
                'roll_number' => $student->roll_number,
                'class' => $student->class->name ?? 'N/A',
                'section' => $student->section->name ?? 'N/A',
            ],
            'attendance_records' => $attendance,
            'period' => [
                'start_date' => $startDate,
                'end_date' => $endDate,
            ],
            'summary' => [
                'total_days' => $total,
                'present' => $present,
                'absent' => $attendance->where('status', 'absent')->count(),
                'late' => $attendance->where('status', 'late')->count(),
                'excused' => $attendance->where('status', 'excused')->count(),
                'percentage' => $total > 0 ? round(($present / $total) * 100, 2) : 0,
            ],
        ]);
    }

    /**
     * Attendance defaulters report
     */
    private function attendanceDefaultersReport(Request $request)
    {
        $startDate = $request->start_date ?? now()->startOfMonth();
        $endDate = $request->end_date ?? now()->endOfMonth();
        $threshold = $request->get('threshold', 75); // Default 75% attendance threshold

        $students = Student::with(['class', 'section'])->get();

        $defaulters = $students->map(function ($student) use ($startDate, $endDate) {
            $attendance = StudentAttendance::where('student_id', $student->id)
                ->whereBetween('date', [$startDate, $endDate])
                ->get();

            $total = $attendance->count();
            $present = $attendance->where('status', 'present')->count();
            $percentage = $total > 0 ? round(($present / $total) * 100, 2) : 0;

            return [
                'student_id' => $student->id,
                'name' => $student->full_name,
                'roll_number' => $student->roll_number,
                'class' => $student->class->name ?? 'N/A',
                'section' => $student->section->name ?? 'N/A',
                'total_days' => $total,
                'present_days' => $present,
                'absent_days' => $attendance->where('status', 'absent')->count(),
                'attendance_percentage' => $percentage,
            ];
        })->filter(function ($student) use ($threshold) {
            return $student['attendance_percentage'] < $threshold;
        })->sortBy('attendance_percentage')->values();

        return $this->successResponse([
            'defaulters' => $defaulters,
            'threshold' => $threshold,
            'period' => [
                'start_date' => $startDate,
                'end_date' => $endDate,
            ],
            'total_defaulters' => $defaulters->count(),
        ]);
    }

    /**
     * Generate exam results reports
     */
    public function examResults(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'report_type' => 'required|in:exam_wise,student_wise,class_wise,subject_wise,toppers,failures',
            'exam_id' => 'nullable|exists:exams,id',
            'class_id' => 'nullable|exists:school_classes,id',
            'section_id' => 'nullable|exists:sections,id',
            'student_id' => 'nullable|exists:students,id',
            'subject_id' => 'nullable|exists:subjects,id',
        ]);

        if ($validator->fails()) {
            return $this->errorResponse('Validation failed', 422, $validator->errors());
        }

        $reportType = $request->report_type;

        switch ($reportType) {
            case 'exam_wise':
                return $this->examWiseReport($request);
            case 'student_wise':
                return $this->examStudentWiseReport($request);
            case 'class_wise':
                return $this->examClassWiseReport($request);
            case 'subject_wise':
                return $this->examSubjectWiseReport($request);
            case 'toppers':
                return $this->examToppersReport($request);
            case 'failures':
                return $this->examFailuresReport($request);
            default:
                return $this->errorResponse('Invalid report type', 400);
        }
    }

    /**
     * Exam-wise results report
     */
    private function examWiseReport(Request $request)
    {
        if (!$request->has('exam_id')) {
            return $this->errorResponse('exam_id is required', 422);
        }

        $exam = Exam::with(['school', 'academicYear'])->find($request->exam_id);

        if (!$exam) {
            return $this->errorResponse('Exam not found', 404);
        }

        $marks = Mark::with(['student', 'subject'])
            ->where('exam_id', $request->exam_id)
            ->get();

        $studentResults = $marks->groupBy('student_id')->map(function ($studentMarks) {
            $student = $studentMarks->first()->student;
            $totalObtained = $studentMarks->sum('marks_obtained');
            $totalMarks = $studentMarks->sum('total_marks');
            $percentage = $totalMarks > 0 ? round(($totalObtained / $totalMarks) * 100, 2) : 0;

            return [
                'student_id' => $student->id,
                'name' => $student->full_name,
                'roll_number' => $student->roll_number,
                'total_obtained' => $totalObtained,
                'total_marks' => $totalMarks,
                'percentage' => $percentage,
                'grade' => $this->getGrade($percentage),
                'subjects' => $studentMarks->map(function ($mark) {
                    return [
                        'subject' => $mark->subject->name,
                        'obtained' => $mark->marks_obtained,
                        'total' => $mark->total_marks,
                    ];
                }),
            ];
        })->sortByDesc('percentage')->values();

        return $this->successResponse([
            'exam' => $exam,
            'results' => $studentResults,
            'summary' => [
                'total_students' => $studentResults->count(),
                'average_percentage' => $studentResults->avg('percentage'),
                'highest_percentage' => $studentResults->max('percentage'),
                'lowest_percentage' => $studentResults->min('percentage'),
                'pass_count' => $studentResults->where('percentage', '>=', 40)->count(),
                'fail_count' => $studentResults->where('percentage', '<', 40)->count(),
            ],
        ]);
    }

    /**
     * Student-wise exam report
     */
    private function examStudentWiseReport(Request $request)
    {
        if (!$request->has('student_id')) {
            return $this->errorResponse('student_id is required', 422);
        }

        $student = Student::with(['class', 'section'])->find($request->student_id);

        if (!$student) {
            return $this->errorResponse('Student not found', 404);
        }

        $marks = Mark::with(['exam', 'subject'])
            ->where('student_id', $request->student_id);

        if ($request->has('exam_id')) {
            $marks->where('exam_id', $request->exam_id);
        }

        $marks = $marks->get();

        $examResults = $marks->groupBy('exam_id')->map(function ($examMarks) {
            $exam = $examMarks->first()->exam;
            $totalObtained = $examMarks->sum('marks_obtained');
            $totalMarks = $examMarks->sum('total_marks');
            $percentage = $totalMarks > 0 ? round(($totalObtained / $totalMarks) * 100, 2) : 0;

            return [
                'exam_id' => $exam->id,
                'exam_name' => $exam->name,
                'exam_date' => $exam->start_date,
                'total_obtained' => $totalObtained,
                'total_marks' => $totalMarks,
                'percentage' => $percentage,
                'grade' => $this->getGrade($percentage),
                'subjects' => $examMarks->map(function ($mark) {
                    return [
                        'subject' => $mark->subject->name,
                        'obtained' => $mark->marks_obtained,
                        'total' => $mark->total_marks,
                        'percentage' => $mark->total_marks > 0
                            ? round(($mark->marks_obtained / $mark->total_marks) * 100, 2)
                            : 0,
                    ];
                }),
            ];
        })->values();

        return $this->successResponse([
            'student' => [
                'id' => $student->id,
                'name' => $student->full_name,
                'roll_number' => $student->roll_number,
                'class' => $student->class->name ?? 'N/A',
                'section' => $student->section->name ?? 'N/A',
            ],
            'exam_results' => $examResults,
            'overall_summary' => [
                'total_exams' => $examResults->count(),
                'average_percentage' => $examResults->avg('percentage'),
                'best_performance' => $examResults->max('percentage'),
                'worst_performance' => $examResults->min('percentage'),
            ],
        ]);
    }

    /**
     * Class-wise exam report
     */
    private function examClassWiseReport(Request $request)
    {
        if (!$request->has('exam_id')) {
            return $this->errorResponse('exam_id is required', 422);
        }

        $classWiseResults = Mark::with(['student.class'])
            ->where('exam_id', $request->exam_id)
            ->get()
            ->groupBy('student.class_id')
            ->map(function ($classMarks, $classId) {
                $className = $classMarks->first()->student->class->name ?? 'Unknown';
                $students = $classMarks->pluck('student_id')->unique();

                $studentResults = $students->map(function ($studentId) use ($classMarks) {
                    $studentMarks = $classMarks->where('student_id', $studentId);
                    $totalObtained = $studentMarks->sum('marks_obtained');
                    $totalMarks = $studentMarks->sum('total_marks');

                    return $totalMarks > 0 ? round(($totalObtained / $totalMarks) * 100, 2) : 0;
                });

                return [
                    'class_id' => $classId,
                    'class_name' => $className,
                    'total_students' => $students->count(),
                    'average_percentage' => $studentResults->avg(),
                    'highest_percentage' => $studentResults->max(),
                    'lowest_percentage' => $studentResults->min(),
                    'pass_count' => $studentResults->filter(fn($p) => $p >= 40)->count(),
                    'fail_count' => $studentResults->filter(fn($p) => $p < 40)->count(),
                ];
            })->values();

        return $this->successResponse([
            'class_wise_results' => $classWiseResults,
        ]);
    }

    /**
     * Subject-wise exam report
     */
    private function examSubjectWiseReport(Request $request)
    {
        if (!$request->has('exam_id')) {
            return $this->errorResponse('exam_id is required', 422);
        }

        $subjectWiseResults = Mark::with(['subject'])
            ->where('exam_id', $request->exam_id)
            ->get()
            ->groupBy('subject_id')
            ->map(function ($subjectMarks, $subjectId) {
                $subject = $subjectMarks->first()->subject;
                $percentages = $subjectMarks->map(function ($mark) {
                    return $mark->total_marks > 0
                        ? round(($mark->marks_obtained / $mark->total_marks) * 100, 2)
                        : 0;
                });

                return [
                    'subject_id' => $subjectId,
                    'subject_name' => $subject->name,
                    'total_students' => $subjectMarks->count(),
                    'average_percentage' => $percentages->avg(),
                    'highest_percentage' => $percentages->max(),
                    'lowest_percentage' => $percentages->min(),
                    'pass_count' => $percentages->filter(fn($p) => $p >= 40)->count(),
                    'fail_count' => $percentages->filter(fn($p) => $p < 40)->count(),
                ];
            })->values();

        return $this->successResponse([
            'subject_wise_results' => $subjectWiseResults,
        ]);
    }

    /**
     * Exam toppers report
     */
    private function examToppersReport(Request $request)
    {
        if (!$request->has('exam_id')) {
            return $this->errorResponse('exam_id is required', 422);
        }

        $limit = $request->get('limit', 10);

        $toppers = Mark::with(['student.class', 'student.section'])
            ->where('exam_id', $request->exam_id)
            ->get()
            ->groupBy('student_id')
            ->map(function ($studentMarks) {
                $student = $studentMarks->first()->student;
                $totalObtained = $studentMarks->sum('marks_obtained');
                $totalMarks = $studentMarks->sum('total_marks');
                $percentage = $totalMarks > 0 ? round(($totalObtained / $totalMarks) * 100, 2) : 0;

                return [
                    'student_id' => $student->id,
                    'name' => $student->full_name,
                    'roll_number' => $student->roll_number,
                    'class' => $student->class->name ?? 'N/A',
                    'section' => $student->section->name ?? 'N/A',
                    'total_obtained' => $totalObtained,
                    'total_marks' => $totalMarks,
                    'percentage' => $percentage,
                    'grade' => $this->getGrade($percentage),
                ];
            })
            ->sortByDesc('percentage')
            ->take($limit)
            ->values();

        return $this->successResponse([
            'toppers' => $toppers,
            'limit' => $limit,
        ]);
    }

    /**
     * Exam failures report
     */
    private function examFailuresReport(Request $request)
    {
        if (!$request->has('exam_id')) {
            return $this->errorResponse('exam_id is required', 422);
        }

        $passingPercentage = $request->get('passing_percentage', 40);

        $failures = Mark::with(['student.class', 'student.section'])
            ->where('exam_id', $request->exam_id)
            ->get()
            ->groupBy('student_id')
            ->map(function ($studentMarks) {
                $student = $studentMarks->first()->student;
                $totalObtained = $studentMarks->sum('marks_obtained');
                $totalMarks = $studentMarks->sum('total_marks');
                $percentage = $totalMarks > 0 ? round(($totalObtained / $totalMarks) * 100, 2) : 0;

                return [
                    'student_id' => $student->id,
                    'name' => $student->full_name,
                    'roll_number' => $student->roll_number,
                    'class' => $student->class->name ?? 'N/A',
                    'section' => $student->section->name ?? 'N/A',
                    'total_obtained' => $totalObtained,
                    'total_marks' => $totalMarks,
                    'percentage' => $percentage,
                    'failed_subjects' => $studentMarks->filter(function ($mark) use ($passingPercentage) {
                        $subjectPercentage = $mark->total_marks > 0
                            ? round(($mark->marks_obtained / $mark->total_marks) * 100, 2)
                            : 0;
                        return $subjectPercentage < $passingPercentage;
                    })->map(function ($mark) {
                        return $mark->subject->name;
                    })->values(),
                ];
            })
            ->filter(function ($student) use ($passingPercentage) {
                return $student['percentage'] < $passingPercentage || count($student['failed_subjects']) > 0;
            })
            ->sortBy('percentage')
            ->values();

        return $this->successResponse([
            'failures' => $failures,
            'passing_percentage' => $passingPercentage,
            'total_failures' => $failures->count(),
        ]);
    }

    /**
     * Helper: Get grade based on percentage
     */
    private function getGrade($percentage)
    {
        if ($percentage >= 90) return 'A+';
        if ($percentage >= 80) return 'A';
        if ($percentage >= 70) return 'B+';
        if ($percentage >= 60) return 'B';
        if ($percentage >= 50) return 'C';
        if ($percentage >= 40) return 'D';
        return 'F';
    }

    /**
     * Helper: Get attendance status
     */
    private function getAttendanceStatus($percentage)
    {
        if ($percentage >= 90) return 'Excellent';
        if ($percentage >= 75) return 'Good';
        if ($percentage >= 60) return 'Average';
        return 'Poor';
    }
}
