<?php
namespace App\Services\Reports;

use App\Models\Result\Exam;
use App\Models\Result\Grade;
use Illuminate\Http\Request;
use App\Models\Academic\Group;
use App\Models\Academic\Shift;
use App\Models\Result\MarkHead;
use App\Models\Academic\Section;
use App\Models\Result\ExamRecord;
use Illuminate\Support\Facades\DB;
use App\Models\Student\StudentInfo;
use App\Models\Academic\AcademicYear;
use App\Models\Academic\StudentClass;
use App\Models\Result\MarkHeadAssign;
use App\Models\Academic\SubjectsAssign;

class ExamResultReportService
{

    public function classRankReport(Request $request)
    {
        if(empty($request->all())){
            return [[], [], [], []];
        }

        // dd($request->all());

        $exam_id = $request->exam_id;
        $academic_year = Exam::where('id', $exam_id)->first()->year;
        $shift_id = $request->shift_id;

        $section_id = $request->section_id;
        $group_id = $request->group_id;
        $student_class_id = $request->student_class_id;
        $subjects_assigns = SubjectsAssign::where('student_class_id',$student_class_id)->select('id','student_class_id', 'subject_id')->get();
        
        $assignedMarksHead = MarkHeadAssign::where('exam_id', $exam_id)
            ->where('student_class_id', $student_class_id)
            ->pluck('head_marks', 'subject_id');

        // Start with exam records instead of students
        $examRecords = ExamRecord::where('exam_id', $exam_id)
            ->where('year', $academic_year)
            ->where('class_id', $student_class_id)
            ->when(!empty($shift_id), function ($query) use ($shift_id) {
                $query->where('shift_id', $shift_id);
            })
            ->when(!empty($section_id), function ($query) use ($section_id) {
                $query->where('section_id', $section_id);
            })
            ->when(!empty($group_id), function ($query) use ($group_id) {
                $query->where('group_id', $group_id);
            });
        
        // Get unique student IDs from exam records
        $studentIds = $examRecords->pluck('student_id')->unique();
        // dd($studentIds );
        // Get students with their exam records
        $students = StudentInfo::whereIn('id', $studentIds)->where('status','active')
            ->with(['examRecords' => function ($query) use ($exam_id, $academic_year, $student_class_id) {
                $query->where('exam_id', $exam_id)
                    ->where('year', $academic_year)
                    ->where('class_id', $student_class_id)
                    ->orderBy('subject_id');
            }, 'examRecords.subject'])
            ->orderBy('class_roll', 'ASC')
            ->get();

        // Calculate highest marks
        $highestMarks = ExamRecord::where('class_id', $student_class_id)
            ->where('exam_id', $exam_id)
            ->where('year', $academic_year)
            ->when(!empty($shift_id), function ($query) use ($shift_id) {
                $query->where('shift_id', $shift_id);
            })
            ->when(!empty($section_id), function ($query) use ($section_id) {
                $query->where('section_id', $section_id);
            })
            ->when(!empty($group_id), function ($query) use ($group_id) {
                $query->where('group_id', $group_id);
            })
            ->groupBy('subject_id')
            ->select('subject_id', DB::raw('MAX(total_marks) as highest_marks'))
            ->get();

        $highest_marks_array = $highestMarks->pluck('highest_marks', 'subject_id')->toArray();
        // dd($highest_marks_array );
        // dd($students, $assignedMarksHead, $highest_marks_array, $subjects_assigns);
        return [$students, $assignedMarksHead, $highest_marks_array, $subjects_assigns];
    }


    public function getRelatedData()
    {
        return [
            'studentClasses' => StudentClass::pluck('class_name', 'id'),
            'groups' => Group::pluck('group_name', 'id'),
            'shifts' => Shift::pluck('shift_name', 'id'),
            'sections' => Section::pluck('section_name', 'id'),
            'academicYears' => AcademicYear::pluck('year', 'id'),
            'exams' => Exam::pluck('exam_name','id'),
            'markHeads' => MarkHead::all(),
            'grades' => Grade::orderBy('mark_from', 'desc')->get(),
        ];
    }

}