forbidals

Back Back Up Advanced Editor

<?php

namespace App\Http\Controllers;

use App\Repositories\ClassSection\ClassSectionInterface;
use App\Repositories\FormField\FormFieldsInterface;
use App\Repositories\SessionYear\SessionYearInterface;
use App\Repositories\Student\StudentInterface;
use App\Repositories\User\UserInterface;
use App\Repositories\Fees\FeesInterface;
use App\Repositories\FeesPaid\FeesPaidInterface;
use App\Repositories\ExpenseCategory\ExpenseCategoryInterface;
use App\Repositories\Expense\ExpenseInterface;
use App\Repositories\Exam\ExamInterface;
use App\Repositories\Attendance\AttendanceInterface;
use App\Repositories\OnlineExam\OnlineExamInterface;
use App\Repositories\OnlineExamQuestion\OnlineExamQuestionInterface;
use App\Repositories\OnlineExamQuestionChoice\OnlineExamQuestionChoiceInterface;
use App\Repositories\OnlineExamQuestionOption\OnlineExamQuestionOptionInterface;
use App\Repositories\OnlineExamStudentAnswer\OnlineExamStudentAnswerInterface;
use App\Repositories\StudentOnlineExamStatus\StudentOnlineExamStatusInterface;
use App\Repositories\ClassSchool\ClassSchoolInterface;
use App\Repositories\Subject\SubjectInterface;
use App\Repositories\Medium\MediumInterface;
use App\Repositories\Grades\GradesInterface;
use App\Repositories\PromoteStudent\PromoteStudentInterface;
use App\Repositories\Timetable\TimetableInterface;
use App\Repositories\SchoolSetting\SchoolSettingInterface;
use App\Models\TransportationPayment;
use App\Models\RoutePickupPoint;
use App\Models\ClassSection;
use App\Models\Holiday;
use App\Models\StaffAttendance;
use App\Models\Leave;
use App\Models\LeaveMaster;
use App\Models\TransportationAttendance;
use App\Models\OnlineExamStudentAnswer;
use App\Models\StaffSalary;
use App\Services\FeaturesService;
use App\Services\ResponseService;
use App\Services\CachingService;
use Illuminate\Support\Facades\Auth;
use App\Repositories\Holiday\HolidayInterface;
use App\Services\BootstrapTableService;
use Illuminate\Support\Facades\Storage;
use Illuminate\Http\Request;
use Carbon\Carbon;
use \App\Repositories\ExamResult\ExamResultInterface;
use App\Services\GeneralService;
use Illuminate\Support\Facades\DB;
use App\Models\Vehicle;
use Throwable;

class ReportsController extends Controller
{
    private ClassSectionInterface $classSection;
    private FormFieldsInterface $formFields;
    private SessionYearInterface $sessionYear;
    private StudentInterface $student;
    private FeesInterface $fees;
    private FeesPaidInterface $feesPaid;
    private UserInterface $user;
    private AttendanceInterface $attendance;
    private CachingService $cache;
    private HolidayInterface $holiday;
    private ExamResultInterface $examResult;
    private ExamInterface $exam;
    private OnlineExamInterface $onlineExam;
    private OnlineExamQuestionChoiceInterface $onlineExamQuestionChoice;
    private OnlineExamQuestionInterface $onlineExamQuestion;
    private OnlineExamQuestionOptionInterface $onlineExamQuestionOption;
    private OnlineExamStudentAnswerInterface $onlineExamStudentAnswer;
    private StudentOnlineExamStatusInterface $studentOnlineExamStatus;
    private ClassSchoolInterface $class;
    private SubjectInterface $subject;
    private MediumInterface $mediums;
    private GradesInterface $grade;
    private GeneralService $generalService;
    private StudentInterface $students;
    private PromoteStudentInterface $promoteStudent;
    private ExpenseCategoryInterface $expenseCategory;
    private ExpenseInterface $expense;
    private TimetableInterface $timetable;
    private SchoolSettingInterface $schoolSettings;

public function __construct(
        ClassSectionInterface $classSection,
        FormFieldsInterface $formFields,
        SessionYearInterface $sessionYear,
        StudentInterface $student,
        FeesInterface $fees,
        FeesPaidInterface $feesPaid,
        UserInterface $user,
        AttendanceInterface $attendance,
        CachingService $cachingService,
        HolidayInterface $holiday,
        ExamResultInterface $examResult,
        ExamInterface $exam,
        OnlineExamInterface $onlineExam,
        OnlineExamQuestionChoiceInterface $onlineExamQuestionChoice,
        OnlineExamQuestionInterface $onlineExamQuestion,
        OnlineExamQuestionOptionInterface $onlineExamQuestionOption,
        OnlineExamStudentAnswerInterface $onlineExamStudentAnswer,
        StudentOnlineExamStatusInterface $studentOnlineExamStatus,
        ClassSchoolInterface $class,
        SubjectInterface $subject,
        MediumInterface $mediums,
        GradesInterface $grade,
        GeneralService $generalService,
        StudentInterface $students,
        PromoteStudentInterface $promoteStudent,
        ExpenseCategoryInterface $expenseCategory,
        ExpenseInterface $expense,
        TimetableInterface $timetable,
        SchoolSettingInterface $schoolSettings
    ) {
        $this->student = $student;
        $this->user = $user;
        $this->classSection = $classSection;
        $this->formFields = $formFields;
        $this->sessionYear = $sessionYear;
        $this->fees = $fees;
        $this->feesPaid = $feesPaid;
        $this->attendance = $attendance;
        $this->cache = $cachingService;
        $this->holiday = $holiday;
        $this->examResult = $examResult;
        $this->exam = $exam;
        $this->onlineExam = $onlineExam;
        $this->onlineExamQuestionChoice = $onlineExamQuestionChoice;
        $this->onlineExamQuestion = $onlineExamQuestion;
        $this->onlineExamQuestionOption = $onlineExamQuestionOption;
        $this->onlineExamStudentAnswer = $onlineExamStudentAnswer;
        $this->studentOnlineExamStatus = $studentOnlineExamStatus;
        $this->students = $students;
        $this->promoteStudent = $promoteStudent;

$this->class = $class;
        $this->subject = $subject;
        $this->mediums = $mediums;
        $this->grade = $grade;
        $this->generalService = $generalService;
        $this->expenseCategory = $expenseCategory;
        $this->expense = $expense;
        $this->timetable = $timetable;
        $this->schoolSettings = $schoolSettings;
    }

public function student_reports()
    {
        ResponseService::noPermissionThenRedirect('reports-student');
        // ResponseService::noFeatureThenRedirect('Reports Management');
        // ResponseService::noPermissionThenRedirect('student-list');
        $class_sections = $this->classSection->all(['*'], ['class', 'class.stream', 'class.shift', 'section', 'medium']);

if (Auth::user()->school_id) {
            $extraFields = $this->formFields->defaultModel()->where('user_type', 1)->orderBy('rank')->get();
        } else {
            $extraFields = $this->formFields->defaultModel()->orderBy('rank')->get();
        }

$features = FeaturesService::getFeatures();

return view('reports.student.student-reports', compact('class_sections', 'extraFields', 'features'));
    }

public function student_reports_show(Request $request)
    {
        ResponseService::noPermissionThenRedirect('reports-student');

$offset = request('offset', 0);
        $limit = request('limit', 10);
        $sort = request('sort', 'id');
        $order = request('order', 'ASC');
        $search = request('search');
        $classId = request('class_id');
        $sessionYearID = $this->cache->getSessionYear()->id ?? null;

// Get promoted students if both filters are applied
        $promotedMap = collect();
        if ($classId && $sessionYearID) {
            $promotedMap = $this->promoteStudent->builder()->where('class_section_id', $classId)
                ->where('session_year_id', $sessionYearID)
                ->get(['student_id', 'class_section_id', 'session_year_id'])
                ->keyBy('student_id');
        }

// Main student query
        $sql = $this->student->builder()
            ->where(function ($query) {
                $query->where('application_type', 'offline')
                    ->orWhere(function ($q) {
                        $q->where('application_type', 'online')
                            ->where('application_status', 1);
                    });
            })
            ->with([
                'user.extra_student_details.form_field',
                'guardian',
                'class_section.class.stream',
                'class_section.class.shift',
                'class_section.section',
                'class_section.medium'
            ])
            ->where(function ($query) use ($search) {
                $query->when($search, function ($query) use ($search) {
                    $query->where(function ($query) use ($search) {
                        $query->where('user_id', 'LIKE', "%$search%")
                            ->orWhere('class_section_id', 'LIKE', "%$search%")
                            ->orWhere('admission_no', 'LIKE', "%$search%")
                            ->orWhere('roll_number', 'LIKE', "%$search%")
                            ->orWhere('admission_date', 'LIKE', date('Y-m-d', strtotime("%$search%")))
                            ->orWhereHas('user', function ($q) use ($search) {
                                $q->where('first_name', 'LIKE', "%$search%")
                                    ->orWhere('last_name', 'LIKE', "%$search%")
                                    ->orWhere('email', 'LIKE', "%$search%")
                                    ->orWhere('dob', 'LIKE', "%$search%")
                                    ->orWhereRaw("concat(first_name,' ',last_name) LIKE '%" . $search . "%'");
                            })->orWhereHas('guardian', function ($q) use ($search) {
                                $q->where('first_name', 'LIKE', "%$search%")
                                    ->orWhere('last_name', 'LIKE', "%$search%")
                                    ->orWhere('email', 'LIKE', "%$search%")
                                    ->orWhere('dob', 'LIKE', "%$search%")
                                    ->orWhereRaw("concat(first_name,' ',last_name) LIKE '%" . $search . "%'");
                            });
                    });
                });
            })
            ->when($classId, function ($query) use ($classId, $promotedMap) {
                $query->where(function ($q) use ($classId, $promotedMap) {
                    $q->where('class_section_id', $classId);
                    if ($promotedMap->isNotEmpty()) {
                        $q->orWhereIn('user_id', $promotedMap->keys());
                    }
                });
            })
            ->when($sessionYearID, function ($query) use ($sessionYearID, $promotedMap) {
                $query->where(function ($q) use ($sessionYearID, $promotedMap) {
                    $q->where('session_year_id', $sessionYearID);
                    if ($promotedMap->isNotEmpty()) {
                        $q->orWhereIn('user_id', $promotedMap->keys());
                    }
                });
            });

$total = $sql->count();

if (!empty($request->class_id)) {
            $sql = $sql->orderBy('roll_number', 'ASC');
        } else {
            $sql = $sql->orderBy($sort, $order);
        }
        if ($offset >= $total && $total > 0) {
            $lastPage = floor(($total - 1) / $limit) * $limit; // calculate last page offset
            $offset = $lastPage;
        }
        $sql->skip($offset)->take($limit);
        $res = $sql->get();

$bulkData = [];
        $bulkData['total'] = $total;
        $rows = [];
        $no = 1;

foreach ($res as $row) {
            $userId = $row->user_id;

// Override class_section_id and session_year_id from promotion if available
            if ($promotedMap->has($userId)) {
                $promoted = $promotedMap->get($userId);
                $row->class_section_id = $promoted->class_section_id;
                $row->session_year_id = $promoted->session_year_id;

$classSection = $this->classSection->builder()->with('class.stream', 'class.shift', 'section', 'medium')
                    ->find($promoted->class_section_id);

// ⚠️ Replace loaded relation
                $row->setRelation('class_section', $classSection);
            }

$operate = BootstrapTableService::viewButton(
                route('reports.student.student-view-reports', [$row->user->id]),
                [],
                [
                    'title' => trans('View Student Details'),
                    'target' => ''
                ]
            );

$student_gender = $row->user->gender;
            $guardian_gender = $row->guardian->gender ?? '';

$row->user->gender = trans(strtolower($row->user->gender));
            $row->guardian->gender = trans(strtolower($row->guardian->gender ?? ''));

$tempRow = $row->toArray();
            $tempRow['no'] = $no++;
            $tempRow['eng_student_gender'] = $student_gender;
            $tempRow['eng_guardian_gender'] = $guardian_gender;
            $tempRow['extra_fields'] = $row->user->extra_student_details;

foreach ($row->user->extra_student_details as $field) {
                $data = '';
                if ($field->form_field->type == 'checkbox') {
                    $data = json_decode($field->data);
                } elseif ($field->form_field->type == 'file') {
                    $data = '<a href="' . Storage::url($field->data) . '" target="_blank">DOC</a>';
                } elseif ($field->form_field->type == 'dropdown') {
                    $data = $field->data ?? '';
                } else {
                    $data = $field->data;
                }

$tempRow[$field->form_field->name] = $data;
            }

$tempRow['operate'] = $operate;
            $rows[] = $tempRow;
        }

$bulkData['rows'] = $rows;
        return response()->json($bulkData);
    }

public function student_view_reports($id)
    {
        ResponseService::noPermissionThenRedirect('reports-student');
        $schoolSettings = $this->cache->getSchoolSettings();
        $session_year_id = $this->cache->getSessionYear()->id ?? null;
        // Check if the student has a promotion for the requested session_year_id
        $promoted = \App\Models\PromoteStudent::where('student_id', $id)
            ->where('session_year_id', $session_year_id)
            ->first();

if ($promoted) {
            // Load the promoted class_section with relations
            $classSection = $this->classSection->builder()->with('class.stream', 'class.shift', 'section', 'medium')
                ->find($promoted->class_section_id);

// Load the promoted session year
            $promotedSessionYear = $this->sessionYear->builder()->select('id', 'name', 'start_date', 'end_date')
                ->find($promoted->session_year_id);

// Load the student with basic relations (excluding original session_year and class_section)
            $student = $this->student->builder()->where('user_id', $id)
                ->with([
                    'user',
                    'guardian',
                ])
                ->first();

// Override class_section_id and session_year_id
            $student->class_section_id = $promoted->class_section_id;
            $student->session_year_id = $promoted->session_year_id;

// Override the relations
            $student->setRelation('class_section', $classSection);
            $student->setRelation('session_year', $promotedSessionYear);
        } else {
            // No promotion: load full relations normally
            $student = $this->student->builder()->where('user_id', $id)
                ->with([
                    'user',
                    'guardian',
                    'class_section.class.stream',
                    'class_section.class.shift',
                    'class_section.section',
                    'class_section.medium',
                    'session_year:id,name,start_date,end_date'
                ])
                ->first();
        }

$transportation = null;

$transportationPayments = TransportationPayment::with(['transportationFee', 'paymentTransaction', 'pickupPoint', 'routeVehicle', 'shift'])
            ->where('user_id', $id)
            ->where('status', 'paid')
            ->orderBy('id', 'desc')
            ->first();

if ($transportationPayments) {
            $routePickupPoint = null;

if ($transportationPayments->pickupPoint && $transportationPayments->routeVehicle?->route) {
                $routePickupPoint = RoutePickupPoint::where('pickup_point_id', $transportationPayments->pickupPoint->id)
                    ->where('route_id', $transportationPayments->routeVehicle->route->id)
                    ->first();
            }

$transportation = [
                'plan' => [
                    'plan_status' => ($transportationPayments->expiry_date && $transportationPayments->expiry_date >= date('Y-m-d')) ? 'Active' : 'Expired',
                    'vehicle_assignment' => ($transportationPayments->routeVehicle) ? 'Assigned' : 'Pending',
                    'expiry_date' => $transportationPayments->expiry_date,
                    'date' => $transportationPayments->date,
                    'paid_amount' => $schoolSettings['currency_symbol'] . " " . $transportationPayments->amount,
                    'payment_mode' => $transportationPayments->paymentTransaction->payment_gateway ?? null,
                ],
                'shift' => [
                    'name' => $transportationPayments->shift->name ?? null,
                    'start_time' => Carbon::parse($transportationPayments->shift->start_time)->format($schoolSettings['time_format']) ?? null,
                    'end_time' => Carbon::parse($transportationPayments->shift->end_time)->format($schoolSettings['time_format']) ?? null,
                ],
                'fee' => [
                    'duration' => $transportationPayments->transportationFee->duration ?? null,
                    'amount' => $schoolSettings['currency_symbol'] . " " . $transportationPayments->transportationFee->fee_amount ?? null,
                ],
                'route' => [
                    'route_name' => $transportationPayments->routeVehicle->route->name ?? null,
                    'pickup_point_name' => $transportationPayments->pickupPoint->name ?? null,
                    'pickup_time' => $routePickupPoint?->pickup_time
                        ? Carbon::parse($routePickupPoint->pickup_time)->format($schoolSettings['time_format'])
                        : null,
                    'drop_time' => $routePickupPoint?->drop_time
                        ? Carbon::parse($routePickupPoint->drop_time)->format($schoolSettings['time_format'])
                        : null,
                ],
                'vehicle' => [
                    'name' => $transportationPayments->routeVehicle->vehicle->name ?? null,
                    'number' => $transportationPayments->routeVehicle->vehicle->vehicle_number ?? null,
                    'capacity' => $transportationPayments->routeVehicle->vehicle->capacity ?? null,
                    'driver' => $transportationPayments->routeVehicle->driver ?? null,
                    'helper' => $transportationPayments->routeVehicle->helper ?? null
                ],
            ];
        }

// Get all fees for this student
        $studentFees = $this->getStudentFees($id, $session_year_id);

// Get all session years for exam tab
        $sessionYears = $this->sessionYear->all();
        $sessionYear = $this->cache->getSessionYear();

// Generate months for attendance tab based on session year range
        $attendanceMonths = [];
        if ($sessionYear) {
            try {
                $settings = $this->cache->getSchoolSettings();
                $start = Carbon::createFromFormat($settings['date_format'], $sessionYear->start_date)->startOfMonth();
                $end = Carbon::createFromFormat($settings['date_format'], $sessionYear->end_date)->startOfMonth();

// Clone to avoid modifying the original carbon object in the loop
                $current = $start->copy();
                while ($current <= $end) {
                    $attendanceMonths[] = (object)[
                        'id' => $current->month,
                        'year' => $current->year,
                        'name' => $current->format('F Y')
                    ];
                    $current->addMonth();
                }
            } catch (\Exception $e) {
                // Fallback or ignore if date format issue
            }
        }

// return $attendanceMonths;

return view('reports.student.student-view-reports', compact('student', 'studentFees', 'sessionYears', 'sessionYear', 'session_year_id', 'transportation', 'attendanceMonths'));
    }

public function getStudentFees($student_id, $session_year_id)
    {
        ResponseService::noPermissionThenRedirect('reports-student');
        // Check if promoted for the session year, else fallback to original
        $promoted = null;
        if ($session_year_id) {
            $promoted = $this->promoteStudent->builder()->where('student_id', $student_id)
                ->where('session_year_id', $session_year_id)
                ->first();
        }

if ($promoted) {
            // Get promoted class_section
            $classSectionId = $promoted->class_section_id;
        } else {
            // Load student to get original class_section
            $student = $this->student->builder()
                ->where('user_id', $student_id)
                ->with('class_section:id,class_id')
                ->first();

if (!$student) {
                return collect([]);
            }

$classSectionId = $student->class_section_id;
        }

// Get the class_id from class_section
        $classSection = $this->classSection->builder()->find($classSectionId);
        if (!$classSection) {
            return collect([]);
        }
        $classId = $classSection->class_id;

// Now get all fees assigned to this class
        $allClassFees = $this->fees->builder()
            ->where('class_id', $classId)
            ->with([
                'fees_class_type.fees_type',
                'installments:id,name,due_date,due_charges,fees_id'
            ])
            ->get();

// Get paid fees for student (session_year filter can be added if relevant)
        $paidFees = $this->feesPaid->builder()
            ->where('student_id', $student_id)
            ->with([
                'fees.fees_class_type.fees_type',
                'compulsory_fee.installment_fee:id,name',
                'optional_fee' => function ($q) {
                    $q->with([
                        'fees_class_type' => function ($q) {
                            $q->select('id', 'fees_type_id')->with('fees_type:id,name');
                        }
                    ]);
                }
            ])
            ->get();

$paidFeesIds = $paidFees->pluck('fees_id')->toArray();

$paidFees->transform(function ($paidFee) {
            $paidFee->status = $paidFee->is_fully_paid ? 'paid' : 'partial';
            return $paidFee;
        });

$unpaidFees = $allClassFees->filter(function ($fee) use ($paidFeesIds) {
            return !in_array($fee->id, $paidFeesIds);
        })->map(function ($fee) use ($student_id) {
            $virtualRecord = new \stdClass();
            $virtualRecord->id = null;
            $virtualRecord->fees_id = $fee->id;
            $virtualRecord->student_id = $student_id;
            $virtualRecord->is_fully_paid = 0;
            $virtualRecord->is_used_installment = 0;
            $virtualRecord->amount = $fee->total_compulsory_fees;
            $virtualRecord->date = null;
            $virtualRecord->fees = $fee;
            $virtualRecord->compulsory_fee = [];
            $virtualRecord->optional_fee = [];
            $virtualRecord->status = 'unpaid';

if (isset($fee->due_date)) {
                try {
                    $today = \Carbon\Carbon::now()->startOfDay();
                    $dueDate = \Carbon\Carbon::createFromFormat('d-m-Y', $fee->due_date)->startOfDay();
                    if ($dueDate->lt($today)) {
                        $virtualRecord->status = 'overdue';
                    }
                } catch (\Exception $e) {
                    // ignore parse errors, keep status as unpaid
                }
            }
            return $virtualRecord;
        });

$allFees = $paidFees->concat($unpaidFees);

return $allFees->sortBy(function ($fee) {
            return match ($fee->status) {
                'overdue' => 1,
                'unpaid' => 2,
                'partial' => 3,
                'paid' => 4,
                default => 5,
            };
        });
    }

public function getStudentAttendanceReport(Request $request)
    {
        ResponseService::noPermissionThenRedirect('reports-student');
        // Validate request parameters
        $request->validate([
            'month' => 'required|numeric|between:1,12',
            'attendance_year' => 'required|numeric',
            'student_id' => 'required|exists:users,id',
            'session_year_id' => 'required|exists:session_years,id',
        ]);

// Get session year from request
        $sessionYear = $this->sessionYear->findById($request->session_year_id);
        $settings = $this->cache->getSchoolSettings();

// Get student information including class section
        $student = $this->student->builder()
            ->where('user_id', $request->student_id)
            ->with('class_section')
            ->first();

if (!$student) {
            return response()->json([
                'success' => false,
                'message' => 'Student not found'
            ], 404);
        }

$year = $request->attendance_year;
        $startDate = Carbon::createFromDate($year, $request->month, 1)->startOfMonth();
        $endDate = Carbon::createFromDate($year, $request->month, 1)->endOfMonth();

// Get attendance records for this student in the specified month
        $attendanceRecords = $this->attendance->builder()
            ->where('student_id', $request->student_id)
            ->where('class_section_id', $student->class_section_id)
            ->where('session_year_id', $sessionYear->id)
            ->whereBetween('date', [$startDate->format('Y-m-d'), $endDate->format('Y-m-d')])
            ->get();

$holidayAttendance = [];

$holidays = Holiday::whereDate('date', '>=', $startDate->format('Y-m-d'))
            ->whereDate('date', '<=', $endDate->format('Y-m-d'))
            ->get();

foreach ($holidays as $holiday) {
            $holidayAttendance[] = [
                'date' => $holiday->getRawOriginal('date'),
                'title' => $holiday->title
            ];
        }

$leaveMaster = $this->cache->getDefaultSessionYearLeaveMaster();
        $holiday_days = $leaveMaster && $leaveMaster->holiday ? explode(',', $leaveMaster->holiday) : [];
        if ($leaveMaster) {
            $period = Carbon::parse($startDate)->daysUntil(Carbon::parse($endDate)->addDay());
            foreach ($period as $day) {
                if (in_array($day->format('l'), $holiday_days)) {
                    $holidayAttendance[] = [
                        'date' => $day->format('Y-m-d'),
                        'title' => "Weekly Holiday"
                    ];
                }
            }
        }

// Calculate attendance percentage
        $totalDays = $presentCount + $absentCount;
        $attendancePercentage = $totalDays > 0 ? round(($presentCount / $totalDays) * 100) : 0;

// Prepare the response data
        $responseData = [
            'success' => true,
            'attendance' => $attendanceRecords,
            'holiday' => $holidayAttendance,
            'summary' => [
                'present_count' => $presentCount,
                'absent_count' => $absentCount,
                'holiday_count' => $holidayCount,
                'attendance_percentage' => $attendancePercentage,
                'total_days' => $totalDays
            ]
        ];

return response()->json($responseData);
    }

public function getStudentTransportationAttendanceReport(Request $request)
    {
        ResponseService::noPermissionThenRedirect('reports-student');

$request->validate([
            'month' => 'required|numeric|between:1,12',
            'attendance_year' => 'required|numeric',
            'student_id' => 'required|exists:users,id',
            'session_year_id' => 'required|exists:session_years,id',
            'type' => 'nullable|in:pickup,drop'
        ]);

$typeValue = $request->type === 'drop' ? 1 : 0;

$sessionYear = $this->sessionYear->findById($request->session_year_id);
        $settings = $this->cache->getSchoolSettings();

// Get student information
        $student = $this->student->builder()
            ->where('user_id', $request->student_id)
            ->first();

if (!$student) {
            return response()->json([
                'success' => false,
                'message' => 'Student not found'
            ], 404);
        }

// Get transportation attendance records
        $attendanceRecords = TransportationAttendance::with(['routeVehicle', 'pickupPoint', 'shift'])
            ->where('user_id', $request->student_id)
            ->where('pickup_drop', $typeValue)
            ->whereBetween('date', [$startDate->format('Y-m-d'), $endDate->format('Y-m-d')])
            ->get();

// Handle holiday attendance
        $holidayAttendance = array();

// Use regular holidays
        $holidays = $this->holiday->builder()
            ->where('date', '>=', $startDate->format('Y-m-d'))
            ->where('date', '<=', $endDate->format('Y-m-d'))
            ->get();

foreach ($holidays as $h) {
            $holidayAttendance[] = [
                'date' => Carbon::createFromFormat($settings['date_format'], $h->date)->format('Y-m-d'),
                'title' => $h->title,
            ];
        }

// Handle leave master weekly holidays
        $leaveMaster = $this->cache->getDefaultSessionYearLeaveMaster();
        $holiday_days = $leaveMaster && $leaveMaster->holiday ? explode(',', $leaveMaster->holiday) : [];
        if ($leaveMaster) {
            $period = Carbon::parse($startDate)->daysUntil(Carbon::parse($endDate)->addDay());
            foreach ($period as $day) {
                if (in_array($day->format('l'), $holiday_days)) {
                    $holidayAttendance[] = [
                        'date' => $day->format('Y-m-d'),
                        'title' => "Weekly Holiday"
                    ];
                }
            }
        }

$holidayAttendanceCollection = collect($holidayAttendance);

// Count logic: 
        // We only consider dates up to MIN(today, endDate) as working days.
        $cutOffDate = Carbon::now()->endOfDay() < $endDate ? Carbon::now()->endOfDay() : $endDate;
        $workingDaysCount = 0;

$periodCounter = Carbon::parse($startDate)->daysUntil($cutOffDate->copy());

// foreach ($periodCounter as $day) {
        // $dayFormatted = $day->format('Y-m-d');
        // $isHoliday = $holidayAttendanceCollection->contains('date', $dayFormatted);
        // if (!$isHoliday) {
        //     $workingDaysCount++;
        // }
        // }

$workingDaysCount = $attendanceRecords->count();

// Present count: Total distinct dates with at least one record (Pickup or Drop)
        $presentDates = $attendanceRecords->pluck('date')->unique();

// Remove holidays from present dates if we want to be strict, but usually present trumps holiday.
        // Let's count present days which are actually in the working days or just all present dates.
        $presentCount = $presentDates->count();
        $holidayCount = $holidayAttendanceCollection->unique('date')->count();

// Absent count
        $absentCount = max(0, $workingDaysCount - $presentCount);

$attendancePercentage = $workingDaysCount > 0 ? round(($presentCount / $workingDaysCount) * 100) : 0;

// Process data for UI
        $processedAttendance = $attendanceRecords->groupBy('date')->map(function ($dayRecords, $date) {
            return [
                'date' => $date,
                'records' => $dayRecords->map(function ($r) {
                    return [
                        'type' => $r->pickup_drop == 0 ? 'Pickup' : 'Drop',
                        'time' => $r->created_at->format('H:i'), // Adjust as per need, using created_at for time
                        // 'status' => 'Present' // For now, if record exists, it's present
                        'status' => $r->status // For now, if record exists, it's present
                    ];
                })
            ];
        })->values();

// The UI needs get_date_original mapped exactly like attendance, so we add type = 1 for present
        // $mappedAttendance = $presentDates->map(function ($date) {
        //     return [
        //         'get_date_original' => $date,
        //         'type' => 1 // Present
        //     ];
        // })->values();

$mappedAttendance = [];
        $presentCount = 0;
        foreach ($attendanceRecords as $key => $row) {
            $mappedAttendance[] = [
                'get_date_original' => $row->getRawOriginal('date'),
                'type' => $row->status == 'present' ? 1 : 0
            ];

if ($row->status == 'present') {
                $presentCount++;
            }
        }

$absentCount = max(0, $workingDaysCount - $presentCount);
        $attendancePercentage = $workingDaysCount > 0 ? round(($presentCount / $workingDaysCount) * 100) : 0;

$responseData = [
            'success' => true,
            'attendance' => $mappedAttendance, // Simple mapping for calendar UI
            'details' => $processedAttendance, // More details if needed
            'holiday' => $holidayAttendance,
            'summary' => [
                'present_count' => $presentCount,
                'absent_count' => $absentCount,
                'holiday_count' => $holidayCount,
                'attendance_percentage' => $attendancePercentage,
                'total_days' => $workingDaysCount
            ]
        ];

return response()->json($responseData);
    }

public function getStudentExamReport(Request $request)
    {
        ResponseService::noPermissionThenRedirect('reports-student');
        // Validate request parameters
        $request->validate([
            'student_id' => 'required|exists:users,id',
            'session_year_id' => 'nullable|exists:session_years,id'
        ]);

// Get current session year if not provided
        $sessionYearId = $this->cache->getSessionYear()->id;

// Get student information including class section
        $student = $this->student->builder()
            ->where('user_id', $request->student_id)
            ->with(['class_section.class', 'class_section.class.shift', 'class_section.section', 'user'])
            ->first();

if (!$student) {
            return response()->json([
                'success' => false,
                'message' => 'Student not found'
            ], 404);
        }

// Get offline exam results
        $offlineExamResults = $this->getOfflineExamResults($request->student_id, $sessionYearId);

// Get online exam results
        $onlineExamResults = $this->getOnlineExamResults($request->student_id, $sessionYearId);

// Get all available exams for this class section
        $availableExams = $this->exam->builder()
            ->with('timetable.class_subject', 'timetable.exam_marks.user.student')
            ->where('publish', 1)
            ->where('session_year_id', $sessionYearId)
            ->get();

// Check if there are any exams available
        if ($availableExams->isEmpty() && empty($offlineExamResults) && empty($onlineExamResults)) {
            return response()->json([
                'success' => true,
                'message' => 'No exams available for this student',
                'student' => [
                    'id' => $student->user_id,
                    'name' => $student->user->full_name,
                    'admission_no' => $student->admission_no,
                    'roll_number' => $student->roll_number,
                    'class' => $student->class_section->class->name ?? 'N/A',
                    'section' => $student->class_section->section->name ?? 'N/A'
                ],
                'exams' => []
            ]);
        }

// Process offline exam results
        $offlineExams = collect($offlineExamResults)->map(function ($result) use ($student) {
            // Make sure result is an object
            if (!is_object($result)) {
                return null;
            }

try {
                // Get subjects for this exam
                $subjects = $this->getExamSubjectsWithMarks($result->exam_id, $student->user_id);

// Get rank in class
                $classRank = $this->getStudentExamRank($result->exam_id, $student->class_section_id, $student->user_id);

// Determine division based on percentage
                $division = null;
                if ($result->percentage >= 75) {
                    $division = 'First';
                } elseif ($result->percentage >= 60) {
                    $division = 'Second';
                } elseif ($result->percentage >= 33) {
                    $division = 'Third';
                }

return [
                    'id' => $result->exam_id,
                    'name' => $result->exam->name,
                    'description' => $result->exam->description,
                    'published' => (bool) $result->exam->publish,
                    'subjects' => $subjects,
                    'exam_type' => 'Offline Exam',
                    'summary' => [
                        'max_marks' => $result->total_marks,
                        'obtained_marks' => $result->obtained_marks,
                        'percentage' => $result->percentage,
                        'result' => $result->status ? 'Pass' : 'Fail',
                        'division' => $division,
                        'rank' => $classRank,
                    ]
                ];
            } catch (\Exception $e) {
                \Log::error('Error processing exam result: ' . $e->getMessage());
                return null;
            }
        })->filter()->values(); // Remove any null values and reindex

// Process online exam results
        $onlineExams = collect($onlineExamResults)->map(function ($result) {
            return [
                'id' => $result['id'] ?? null,
                'name' => $result['exam_title'] ?? 'Online Exam',
                'subject_name' => $result['subject_name'] ?? null,
                'total_marks' => $result['total_marks'] ?? 0,
                'exam_total_marks' => $result['exam_total_marks'] ?? 0,
                'total_obtained_marks' => $result['total_obtained_marks'] ?? 0,
                'percentage' => $result['percentage'] ?? 0,
                'status' => $result['status'] ?? 'Not Available',
                'exam_type' => 'Online Exam',
                'subject_type' => $result['subjects'][0]['type'] ?? null,
                'created_at' => $result['created_at'] ?? null,
                'grade' => $result['grade'] ?? null,
            ];
        })->values();

// Add missing exams with not attempted status
        $attemptedExamIds = $offlineExams->pluck('id')->toArray();
        $missingExams = $availableExams->filter(function ($exam) use ($attemptedExamIds) {
            return !in_array($exam->id, $attemptedExamIds);
        })->map(function ($exam) use ($student) {
            // Get subjects for this exam even though it wasn't attempted
            $subjects = $this->getExamSubjectsWithMarks($exam->id, $student->user_id);

if (!$subjects) {
                return [];
            }

return [
                'id' => $exam->id,
                'name' => $exam->name,
                'description' => $exam->description,
                'published' => true,
                'subjects' => $subjects,
                'exam_type' => 'Offline Exam',
                'summary' => [
                    'max_marks' => $subjects->sum('max_marks') ?? 0,
                    'obtained_marks' => 0,
                    'percentage' => 0,
                    'result' => 'Not Attempted',
                    'division' => null,
                    'rank' => 'N/A',
                    'pdf_url' => null,
                ]
            ];
        })->values();

// Combine all exam results
        $allExams = $offlineExams->concat($missingExams)->concat($onlineExams);

// Prepare response
        $responseData = [
            'success' => true,
            'student' => [
                'id' => $student->user_id,
                'name' => $student->user->full_name,
                'admission_no' => $student->admission_no,
                'roll_number' => $student->roll_number,
                'class' => $student->class_section->class->name ?? 'N/A',
                'section' => $student->class_section->section->name ?? 'N/A',
                'photo' => $this->formatImageUrl($student->user->image)
            ],
            'exams' => $allExams->sortByDesc('id')->values()
        ];

return response()->json($responseData);
    }

// get online exam results
    private function getOnlineExamResults($studentId, $sessionYearId)
    {
        ResponseService::noPermissionThenRedirect('reports-student');

$attempts = $this->studentOnlineExamStatus->builder()
            ->with([
                'online_exam' => function ($q) {
                    $q->with(['class_subject.subject']);
                },
                'student_data',
            ])
            ->where([
                'status' => 2,
                'student_id' => $studentId
            ])
            ->whereHas('online_exam', function ($q) use ($sessionYearId) {
                $q->where('session_year_id', $sessionYearId);
            })
            ->get();

$rows = [];
        $grades = $this->grade->builder()->orderBy('starting_range', 'ASC')->get();

foreach ($attempts as $student_attempt) {
            // Calculate Obtained Marks: Sum of question_marks where true_answer is 1
            $total_obtained_marks = OnlineExamStudentAnswer::where('student_id', $student_attempt->student_id)
                ->where('online_exam_id', $student_attempt->online_exam_id)
                ->where('true_answer', 1)
                ->sum('question_marks');

// Get Total Marks for the exam
            $total_marks_data = $this->onlineExamQuestionChoice->builder()
                ->select(DB::raw("sum(marks)"))
                ->where('online_exam_id', $student_attempt->online_exam_id)
                ->first();
            $total_marks = $total_marks_data['sum(marks)'] ?? 0;

// Calculate percentage
            $percentage = $total_marks > 0 ? ($total_obtained_marks / $total_marks) * 100 : 0;

// Calculate grade
            $grade = $this->generalService->getGradeByPercentage($percentage, $grades);

$rows[] = [
                'id' => $student_attempt->online_exam_id,
                'exam_title' => $student_attempt->online_exam->title,
                'subject_name' => $student_attempt->online_exam->class_subject->subject->name ?? '',
                'subject_code' => $student_attempt->online_exam->class_subject->subject->code ?? '',
                'subject_type' => $student_attempt->online_exam->class_subject->type ?? '',
                'total_marks' => $total_marks,
                'exam_total_marks' => $total_marks,
                'total_obtained_marks' => $total_obtained_marks,
                'percentage' => $percentage,
                'created_at' => $student_attempt->created_at, // This uses the accessor from the model which formats the date
                'grade' => $grade,
                'exam_type' => 'Online Exam'
            ];
        }

// dd($rows);

return $rows;
    }

// get offline exam results
    private function getOfflineExamResults($studentId, $sessionYearId)
    {
        ResponseService::noPermissionThenRedirect('reports-student');
        // Get exam results for this student directly from exam_results table
        $examResults = $this->examResult->builder()
            ->with(['exam:id,name,description,publish', 'user:id,first_name,last_name,image'])
            ->where('student_id', $studentId)
            ->where('session_year_id', $sessionYearId)
            ->get();

return $examResults;
    }
    /**
     * Get subjects with marks for a specific exam and student
     * 
     * @param int $examId
     * @param int $studentId
     * @return \Illuminate\Support\Collection
     */
    private function getExamSubjectsWithMarks($examId, $studentId)
    {
        try {
            // Get student information first to have access to class_section_id
            $student = $this->student->builder()
                ->where('user_id', $studentId)
                ->select('class_section_id')
                ->first();

if (!$student) {
                return collect([]);
            }

// First attempt: Get exam marks with subject information
            $examMarks = DB::table('exam_marks')
                ->join('exam_timetables', 'exam_timetables.id', '=', 'exam_marks.exam_timetable_id')
                ->join('class_subjects', 'class_subjects.id', '=', 'exam_timetables.class_subject_id')
                ->join('subjects', 'subjects.id', '=', 'class_subjects.subject_id')
                ->where('exam_timetables.exam_id', $examId)
                ->where('exam_marks.student_id', $studentId)
                ->select(
                    'subjects.id as subject_id',
                    'subjects.name as subject_name',
                    'subjects.type as subject_type',
                    'subjects.code as subject_code',
                    'exam_timetables.total_marks as max_marks',
                    'exam_timetables.passing_marks as min_marks',
                    'exam_marks.obtained_marks',
                    'exam_marks.grade',
                    'exam_marks.passing_status as is_pass'
                )
                ->get();

// If no subjects are found, try another approach
            if ($examMarks->isEmpty()) {
                // Second attempt: Get subjects from exam timetables
                $examMarks = DB::table('exam_timetables')
                    ->join('class_subjects', 'class_subjects.id', '=', 'exam_timetables.class_subject_id')
                    ->join('subjects', 'subjects.id', '=', 'class_subjects.subject_id')
                    ->where('exam_timetables.exam_id', $examId)
                    ->where(function ($query) use ($student) {
                        // Either match by class_section_id or class_id
                        $query->where('class_subjects.class_section_id', $student->class_section_id)
                            ->orWhereExists(function ($subquery) use ($student) {
                                $subquery->select(DB::raw(1))
                                    ->from('class_sections')
                                    ->join('classes', 'classes.id', '=', 'class_sections.class_id')
                                    ->whereRaw('class_sections.id = ' . $student->class_section_id)
                                    ->whereRaw('classes.id = class_subjects.class_id');
                            });
                    })
                    ->select(
                        'subjects.id as subject_id',
                        'subjects.name as subject_name',
                        'subjects.type as subject_type',
                        'subjects.code as subject_code',
                        'exam_timetables.total_marks as max_marks',
                        'exam_timetables.passing_marks as min_marks',
                        DB::raw('0 as obtained_marks'),
                        DB::raw('NULL as grade'),
                        DB::raw('0 as is_pass')
                    )
                    ->get();
            }

// If still no subjects, try a third approach with just subjects from the class
            if ($examMarks->isEmpty()) {
                // Third attempt: Get subjects from class_subjects directly
                $examMarks = DB::table('class_subjects')
                    ->join('subjects', 'subjects.id', '=', 'class_subjects.subject_id')
                    ->where('class_subjects.class_section_id', $student->class_section_id)
                    ->select(
                        'subjects.id as subject_id',
                        'subjects.name as subject_name',
                        'subjects.type as subject_type',
                        'subjects.code as subject_code',
                        DB::raw('100 as max_marks'), // Default value
                        DB::raw('33 as min_marks'),  // Default value
                        DB::raw('0 as obtained_marks'),
                        DB::raw('NULL as grade'),
                        DB::raw('0 as is_pass')
                    )
                    ->get();
            }

// If still no subjects, create some dummy data based on the exam results
            if ($examMarks->isEmpty()) {
                // Get exam result to at least show summary
                $examResult = DB::table('exam_results')
                    ->where('exam_id', $examId)
                    ->where('student_id', $studentId)
                    ->first();

if ($examResult) {
                    // Create a single dummy subject as placeholder
                    $examMarks = collect([
                        (object) [
                            'subject_id' => 0,
                            'subject_name' => 'Overall Result',
                            'subject_type' => 'General',
                            'subject_code' => 'ALL',
                            'max_marks' => $examResult->total_marks,
                            'min_marks' => $examResult->total_marks * 0.33, // Assuming 33% passing
                            'obtained_marks' => $examResult->obtained_marks,
                            'grade' => null,
                            'is_pass' => $examResult->percentage >= 33
                        ]
                    ]);
                }
            }

if ($examMarks->isEmpty()) {
                return collect([]);
            }

// Format and return the results
            return $examMarks->map(function ($mark) {
                return (object) [
                    'id' => $mark->subject_id,
                    'name' => $mark->subject_name,
                    'code' => $mark->subject_code ?? '',
                    'type' => $mark->subject_type,
                    'max_marks' => $mark->max_marks,
                    'min_marks' => $mark->min_marks,
                    'obtained_marks' => $mark->obtained_marks,
                    'grade' => $mark->grade,
                    'is_pass' => (bool) $mark->is_pass
                ];
            });
        } catch (\Exception $e) {
            // Log the error and return empty collection to prevent breaking the response
            \Log::error('Error fetching exam subject marks: ' . $e->getMessage());
            \Log::error('Stack trace: ' . $e->getTraceAsString());
            return collect([]);
        }
    }

/**
     * Get student's rank in the class for a specific exam
     * 
     * @param int $examId
     * @param int $classSectionId
     * @param int $studentId
     * @return string Rank (e.g. "1", "2", "N/A")
     */
    private function getStudentExamRank($examId, $classSectionId, $studentId)
    {
        // Get all students' total marks for this exam in this class
        $results = $this->examResult->builder()
            ->select(
                'student_id',
                DB::raw('SUM(obtained_marks) as total_marks')
            )
            ->where('exam_id', $examId)
            ->where('class_section_id', $classSectionId)
            ->where('school_id', Auth::user()->school_id)
            ->groupBy('student_id')
            ->orderByDesc('total_marks')
            ->get();

if ($results->isEmpty()) {
            return 'N/A';
        }

// Find the student's position
        $studentIndex = $results->search(function ($result) use ($studentId) {
            return $result->student_id == $studentId;
        });

if ($studentIndex === false) {
            return 'N/A';
        }

// Return position (1-based index)
        return ($studentIndex + 1);
    }

/**
     * Format image URL correctly
     * 
     * @param string|null $imagePath
     * @return string
     */
    private function formatImageUrl($imagePath)
    {
        if (empty($imagePath)) {
            return url('images/default-user.png');
        }

// Check if the image already contains a URL
        if (filter_var($imagePath, FILTER_VALIDATE_URL)) {
            return $imagePath;
        }

// Check if it's already prefixed with storage
        if (strpos($imagePath, 'storage/') === 0) {
            return url($imagePath);
        }

// Standard path, prepend storage
        return url('storage/' . $imagePath);
    }

/**
     * Get the division based on percentage
     * 
     * @param float $percentage
     * @return string|null
     */
    private function getDivisionByPercentage($percentage)
    {
        if ($percentage >= 75) {
            return 'First';
        } elseif ($percentage >= 60) {
            return 'Second';
        } elseif ($percentage >= 33) {
            return 'Third';
        }

return null;
    }

// get exam reports
    public function exam_reports()
    {
        ResponseService::noPermissionThenRedirect('reports-exam');
        $sessionYear = $this->cache->getSessionYear();

$classes = $this->class->all(['*'], ['stream', 'medium', 'stream']);
        $subjects = $this->subject->builder()->orderBy('id', 'DESC')->get();
        $mediums = $this->mediums->builder()->pluck('name', 'id');

$sessionYears = $this->sessionYear->all();

$exams = $this->exam->builder()->where('publish', 1)->where('session_year_id', $sessionYear->id)->get();
        $classSections = $this->classSection->builder()->with('class.stream', 'class.shift', 'section', 'medium')->get();

// total exam count
        $totalExamCount = $exams->count();

return view('reports.exam.exam-reports', compact('classes', 'subjects', 'mediums', 'sessionYears', 'exams', 'classSections', 'totalExamCount'));
    }

// get exam reports show
    public function exam_reports_show(Request $request)
    {
        // ResponseService::noFeatureThenRedirect('Reports Management');
        ResponseService::noPermissionThenRedirect('reports-exam');
        $class_sections = $this->classSection->all(['*'], ['class', 'class.stream', 'class.shift', 'section', 'medium']);
        return view('reports.exam.exam-reports-show', compact('class_sections'));
    }

// get exam reports view
    public function exam_view_reports($id)
    {
        // ResponseService::noFeatureThenRedirect('Reports Management');
        ResponseService::noPermissionThenRedirect('reports-exam');
        return view('reports.exam.exam-view-reports', compact('id'));
    }

// get yearly result show
    public function yearlyResultShow(Request $request)
    {
        ResponseService::noFeatureThenRedirect('Exam Management');
        ResponseService::noPermissionThenSendJson('exam-result');
        $offset = request('offset', 0);
        $limit = request('limit', 10);
        $sort = request('sort', 'id');
        $order = request('order', 'DESC');
        $search = request('search');

$sessionYears = $this->cache->getSessionYear()->id;
        $class_section_id = request('class_section_id');

$sql = $this->students->builder()
            ->has('exam_result')
            ->with([
                'user:id,first_name,last_name,school_id',
                'user.exam_marks' => function ($q) {
                    $q->with(['timetable', 'subject']);
                }
            ])
            ->whereHas('exam_result.exam', function ($q) {
                $q->where('publish', 1);
            })
            ->whereHas('exam_result', function ($q) use ($sessionYears) {
                $q->where('session_year_id', $sessionYears);
            })
            ->when($search, function ($q) use ($search) {
                $q->where(function ($q) use ($search) {
                    $q->whereHas('user', function ($q) use ($search) {
                        $q->whereRaw("concat(first_name,' ',last_name) LIKE '%" . $search . "%'");
                    });
                });
            })
            ->withSum('exam_result', 'obtained_marks')
            ->withSum('exam_result', 'total_marks');

if ($class_section_id) {
            $sql = $sql->whereHas('exam_result', function ($q) use ($class_section_id) {
                $q->where('class_section_id', $class_section_id);
            });
        }

$total = $sql->count();
        if ($offset >= $total && $total > 0) {
            $lastPage = floor(($total - 1) / $limit) * $limit; // calculate last page offset
            $offset = $lastPage;
        }
        $sql->orderBy($sort, $order)->skip($offset)->take($limit);
        $res = $sql->get();

$bulkData = array();
        $bulkData['total'] = $total;
        $rows = array();
        $no = 1;
        $grades = $this->grade->builder()->orderBy('starting_range', 'ASC')->get();
        foreach ($res as $row) {
            // Calculate total marks across all exams
            $totalMarks = $row->exam_result_sum_total_marks;
            $obtainedMarks = $row->exam_result_sum_obtained_marks;

// Calculate percentage
            $percentage = $totalMarks > 0 ? ($obtainedMarks / $totalMarks) * 100 : 0;

// Calculate grade
            $grade = '';
            $grade = $this->generalService->getGradeByPercentage($percentage, $grades);

$operate = '';
            if (Auth::user()->can('exam-result-edit')) {
                $operate .= BootstrapTableService::button(
                    'fa fa-file-pdf-o',
                    url('reports/exam/rank-wise-result/' . $row->user_id . '/' . $sessionYears),
                    ['btn-gradient-info', 'btn-xs', 'btn-rounded', 'btn-icon'],
                    ['title' => __('view_result'), 'target' => '_blank']
                );
            }

$tempRow = $row->toArray();
            $tempRow['no'] = $no++;
            $tempRow['total_marks'] = number_format($totalMarks, 2);
            $tempRow['obtained_marks'] = number_format($obtainedMarks, 2);
            $tempRow['percentage'] = number_format($percentage, 2);
            $tempRow['grade'] = $grade;
            $tempRow['operate'] = $operate;
            $rows[] = $tempRow;
        }

$bulkData['rows'] = $rows;
        return response()->json($bulkData);
    }

public function yearlyExamResultPdf($student_id)
    {
        ResponseService::noFeatureThenRedirect('Exam Management');
        ResponseService::noPermissionThenRedirect('exam-result');
        try {
            // get school settings
            $settings = $this->cache->getSchoolSettings();

$schoolName = $settings['school_name'];
            $schoolLogo = $settings['horizontal_logo'];

// get exams
            $exams = $this->exam->builder()
                ->with([
                    'timetable' => function ($q) {
                        $q->with('exam_marks');
                    }
                ])
                ->where('publish', 1)
                ->get();

$results = $this->examResult->builder()->with([
                'exam',
                'session_year',
                'user' => function ($q) use ($student_id) {
                    $q->with([
                        'student' => function ($q) {
                            $q->with([
                                'guardian',
                                'class_section.class.stream',
                                'class_section.class.shift',
                                'class_section.section',
                                'class_section.medium'
                            ]);
                        },
                        'exam_marks' => function ($q) use ($student_id) {
                            $q->whereHas('timetable', function ($q) use ($student_id) {
                                $q->where('student_id', $student_id);
                            })
                                ->with([
                                    'class_subject' => function ($q) {
                                        $q->withTrashed()->with('subject:id,name,type');
                                    },
                                    'timetable'
                                ]);
                        }
                    ]);
                }
            ])
                ->where('student_id', $student_id)
                ->select('exam_results.*')
                ->get();

// Convert the results to a collection
            $results = collect($results);

// Add rank calculation to each item in the collection
            $results = $results->map(function ($result) {
                $rank = DB::table('exam_results as er2')
                    ->where('er2.class_section_id', $result->class_section_id)
                    ->where('er2.obtained_marks', '>', $result->obtained_marks)
                    ->where('er2.exam_id', $result->exam_id)
                    ->where('er2.status', 1)
                    ->distinct('er2.obtained_marks')
                    ->count() + 1;

$result->rank = $rank;
                return $result;
            });

// Filter the collection based on student ID
            $result = $results->where('student_id', $student_id)->first();

// $result->rank = $rank;

// ====================================================================
            if (!$result) {
                return redirect()->back()->with('error', trans('no_records_found'));
            }

$grades = $this->grade->builder()->orderBy('starting_range', 'ASC')->get();

// get student attendance count
            $studentAttendanceCount = $this->attendance->builder()->where('student_id', $student_id)->where('type', 1)->where('session_year_id', $result->session_year_id)->count();

$attendanceTotal = $this->attendance->builder()->where('student_id', $student_id)->where('type', 1)->where('session_year_id', $result->session_year_id)->count();

return view('reports.exam.yearly-exam-result-pdf', compact('settings', 'result', 'grades', 'exams', 'studentAttendanceCount', 'attendanceTotal'));
        } catch (Throwable $e) {
            ResponseService::logErrorResponse($e);
            ResponseService::errorResponse();
        }
    }

public function yearlyResultStatistics(Request $request)
    {
        ResponseService::noFeatureThenRedirect('Exam Management');
        ResponseService::noPermissionThenSendJson('exam-result');
        $sessionYearId = $this->cache->getSessionYear()->id;
        $classSectionId = $request->get('class_section_id');

try {
            // Base query for exam results
            $query = $this->examResult->builder()
                ->where('session_year_id', $sessionYearId);

// Filter by class section if provided
            if ($classSectionId) {
                $query->where('class_section_id', $classSectionId);
            }

// Get all results for calculations
            $results = $query->get();

// Calculate statistics
            $totalStudents = $results->count();
            $totalPass = $results->where('status', 1)->count();
            $totalFail = $results->where('status', 0)->count();
            $passPercentage = $totalStudents > 0 ? round(($totalPass / $totalStudents) * 100, 2) : 0;

return response()->json([
                'success' => true,
                'total_students' => $totalStudents,
                'total_pass' => $totalPass,
                'total_fail' => $totalFail,
                'pass_percentage' => $passPercentage
            ]);
        } catch (\Exception $e) {
            \Log::error('Error fetching yearly result statistics: ' . $e->getMessage());
            return response()->json([
                'success' => false,
                'message' => 'Error fetching statistics',
                'total_students' => 0,
                'total_pass' => 0,
                'total_fail' => 0,
                'pass_percentage' => 0
            ]);
        }
    }

public function subjectWiseResultShow(Request $request)
    {
        ResponseService::noFeatureThenRedirect('Exam Management');
        ResponseService::noPermissionThenSendJson('exam-result');
        $offset = request('offset', 0);
        $limit = request('limit', 10);
        $sort = request('sort', 'id');
        $order = request('order', 'DESC');
        $search = request('search');

$sessionYearId = $this->cache->getSessionYear()->id;
        $classSectionId = request('class_section_id');
        $examId = request('exam_id');

if (!$sessionYearId) {
            return response()->json([
                'total' => 0,
                'rows' => []
            ]);
        }

// 2. Subquery: total obtained marks per student, with class_id and class_section_id
        $marksSub = DB::table('students')
            ->join('exam_results', function ($join) use ($sessionYearId, $examId) {
                $join->on('students.user_id', '=', 'exam_results.student_id')
                    ->where('exam_results.session_year_id', $sessionYearId);
                if ($examId) {
                    $join->where('exam_results.exam_id', $examId);
                }
            })
            ->join('class_sections', 'students.class_section_id', '=', 'class_sections.id')
            ->select(
                'students.user_id',
                'students.class_section_id',
                'class_sections.class_id',
                DB::raw('SUM(exam_results.obtained_marks) as total_obtained_marks')
            )
            ->groupBy('students.user_id', 'students.class_section_id', 'class_sections.class_id');

// Fetch and sort the data for ranking
        $rankedData = DB::table(DB::raw("({$marksSub->toSql()}) as ranked"))
            ->mergeBindings($marksSub)
            ->orderBy('class_id')
            ->orderByDesc('total_obtained_marks')
            ->get();

// Calculate ranks in PHP
        $classRanks = [];
        $sectionRanks = [];
        $lastClassMarks = [];
        $lastSectionMarks = [];
        $classRankValue = [];
        $sectionRankValue = [];

foreach ($rankedData as $row) {
            // Class rank
            if (!isset($classRanks[$row->class_id])) {
                $classRanks[$row->class_id] = 1;
                $lastClassMarks[$row->class_id] = $row->total_obtained_marks;
                $classRankValue[$row->class_id] = 1;
            } else {
                if ($lastClassMarks[$row->class_id] != $row->total_obtained_marks) {
                    $classRankValue[$row->class_id]++;
                }
                $lastClassMarks[$row->class_id] = $row->total_obtained_marks;
            }
            $row->class_rank = $classRankValue[$row->class_id];

// Section rank
            if (!isset($sectionRanks[$row->class_section_id])) {
                $sectionRanks[$row->class_section_id] = 1;
                $lastSectionMarks[$row->class_section_id] = $row->total_obtained_marks;
                $sectionRankValue[$row->class_section_id] = 1;
            } else {
                if ($lastSectionMarks[$row->class_section_id] != $row->total_obtained_marks) {
                    $sectionRankValue[$row->class_section_id]++;
                }
                $lastSectionMarks[$row->class_section_id] = $row->total_obtained_marks;
            }
            $row->section_rank = $sectionRankValue[$row->class_section_id];
        }

// Now $rankedData contains class_rank and section_rank for each user_id
        $ranks = collect($rankedData)->keyBy('user_id');

// 5. Main query: fetch students with marks (withSum as before)
        $sql = $this->students->builder()
            ->whereHas('exam_result.exam', function ($q) {
                $q->where('publish', 1);
            })
            ->with([
                'user:id,first_name,last_name,school_id',
                'user.exam_marks' => function ($q) {
                    $q->with([
                        'timetable.class_subject.subject:id,name,type,code',
                        'class_subject.subject:id,name,type,code'
                    ]);
                }
            ])
            ->whereHas('exam_result', function ($q) use ($sessionYearId) {
                $q->where('session_year_id', $sessionYearId);
            })
            ->when($classSectionId, function ($q) use ($classSectionId) {
                $q->whereHas('exam_result', function ($q) use ($classSectionId) {
                    $q->where('class_section_id', $classSectionId);
                });
            })
            ->when($examId, function ($q) use ($examId) {
                $q->whereHas('exam_result', function ($q) use ($examId) {
                    $q->where('exam_id', $examId);
                });
            })
            ->when($search, function ($q) use ($search) {
                $q->where(function ($q) use ($search) {
                    $q->whereHas('user', function ($q) use ($search) {
                        $q->whereRaw("concat(first_name,' ',last_name) LIKE '%" . $search . "%'");
                    });
                });
            });

if ($examId) {
            $sql = $sql->withSum([
                'exam_result' => function ($q) use ($examId) {
                    $q->where('exam_id', $examId);
                }
            ], 'obtained_marks')
                ->withSum([
                    'exam_result' => function ($q) use ($examId) {
                        $q->where('exam_id', $examId);
                    }
                ], 'total_marks');
        } else {
            $sql = $sql->withSum('exam_result', 'obtained_marks')
                ->withSum('exam_result', 'total_marks');
        }

$bulkData = array();
        $bulkData['total'] = $total;
        $rows = array();
        $no = 1;
        $grades = $this->grade->builder()->orderBy('starting_range', 'ASC')->get();

foreach ($res as $row) {
            $totalMarks = $row->exam_result_sum_total_marks;
            $obtainedMarks = $row->exam_result_sum_obtained_marks;
            $percentage = $totalMarks > 0 ? ($obtainedMarks / $totalMarks) * 100 : 0;
            $grade = $this->generalService->getGradeByPercentage($percentage, $grades);

// Lookup ranks
            $classRank = $ranks[$row->user_id]->class_rank ?? null;
            $sectionRank = $ranks[$row->user_id]->section_rank ?? null;

$operate = '';
            if (Auth::user()->can('exam-result-edit')) {
                $operate .= BootstrapTableService::button(
                    'fa fa-file-pdf-o',
                    url('reports/exam/rank-wise-result/' . $row->user_id . '/' . $sessionYearId),
                    ['btn-gradient-info', 'btn-xs', 'btn-rounded', 'btn-icon'],
                    ['title' => __('view_result'), 'target' => '_blank']
                );
            }

$tempRow = $row->toArray();
            $tempRow['no'] = $no++;
            $tempRow['total_marks'] = number_format($totalMarks, 2);
            $tempRow['obtained_marks'] = number_format($obtainedMarks, 2);
            $tempRow['percentage'] = number_format($percentage, 2);
            $tempRow['grade'] = $grade;
            $tempRow['class_rank'] = $classRank;
            $tempRow['section_rank'] = $sectionRank;
            $tempRow['operate'] = $operate;
            $rows[] = $tempRow;
        }

$bulkData['rows'] = $rows;
        return response()->json($bulkData);
    }

public function subjectWiseResultStatistics(Request $request)
    {
        ResponseService::noFeatureThenRedirect('Exam Management');
        ResponseService::noPermissionThenSendJson('exam-result');
        $sessionYearId = $this->cache->getSessionYear()->id;
        $classSectionId = $request->get('class_section_id');
        $subjectId = $request->get('subject_id');

try {
            // Base query to get all subjects for the class section
            $query = DB::table('class_subjects')
                ->join('subjects', 'subjects.id', '=', 'class_subjects.subject_id')
                ->join('exam_timetables', 'exam_timetables.class_subject_id', '=', 'class_subjects.id')
                ->join('exam_marks', 'exam_marks.exam_timetable_id', '=', 'exam_timetables.id')
                ->join('exam_results', 'exam_results.student_id', '=', 'exam_marks.student_id')
                ->where('exam_results.session_year_id', $sessionYearId);

// Apply class section filter if provided
            if ($classSectionId) {
                $query->where('class_subjects.class_section_id', $classSectionId);
            }

// Apply subject filter if provided
            if ($subjectId) {
                $query->where('subjects.id', $subjectId);
            }

// Get total unique subjects
            $totalSubjects = $query->distinct('subjects.id')->count('subjects.id');

if ($totalSubjects == 0) {
                return response()->json([
                    'success' => true,
                    'total_subjects' => 0,
                    'subjects_passed' => 0,
                    'subjects_failed' => 0,
                    'pass_percentage' => 0
                ]);
            }

// Get subject results with passing criteria
            $subjectResults = $query->select(
                'subjects.id as subject_id',
                'subjects.name as subject_name',
                'exam_marks.obtained_marks',
                'exam_timetables.passing_marks',
                'exam_timetables.total_marks'
            )->get();

// Group results by subject
            $subjectStats = collect($subjectResults)->groupBy('subject_id')->map(function ($attempts) {
                $totalAttempts = $attempts->count();
                $passedAttempts = $attempts->filter(function ($attempt) {
                    return $attempt->obtained_marks >= $attempt->passing_marks;
                })->count();

return [
                    'total_attempts' => $totalAttempts,
                    'passed_attempts' => $passedAttempts,
                    'is_pass' => ($passedAttempts / $totalAttempts) >= 0.33 // Consider subject passed if 33% or more students passed
                ];
            });

// Calculate overall statistics
            $subjectsPassed = $subjectStats->filter(function ($stat) {
                return $stat['is_pass'];
            })->count();

$subjectsFailed = $totalSubjects - $subjectsPassed;

// Calculate pass percentage - ensure it cannot exceed 100%
            $passPercentage = min(100, ($totalSubjects > 0) ? round(($subjectsPassed / $totalSubjects) * 100, 2) : 0);

return response()->json([
                'success' => true,
                'total_subjects' => $totalSubjects,
                'subjects_passed' => $subjectsPassed,
                'subjects_failed' => $subjectsFailed,
                'pass_percentage' => $passPercentage
            ]);
        } catch (\Exception $e) {
            \Log::error('Error fetching subject wise result statistics: ' . $e->getMessage());
            return response()->json([
                'success' => false,
                'message' => 'Error fetching subject wise result statistics',
                'total_subjects' => 0,
                'subjects_passed' => 0,
                'subjects_failed' => 0,
                'pass_percentage' => 0
            ]);
        }
    }

public function subjectWiseResultPdf($student_id)
    {
        ResponseService::noFeatureThenRedirect('Exam Management');
        ResponseService::noPermissionThenRedirect('exam-result');
        try {
            // get school settings
            $settings = $this->cache->getSchoolSettings();

$schoolName = $settings['school_name'];
            $schoolLogo = $settings['horizontal_logo'];

// Convert the results to a collection
            $results = collect($results);

$result->rank = $rank;
                return $result;
            });

// Filter the collection based on student ID
            $result = $results->where('student_id', $student_id)->first();

// ====================================================================
            if (!$result) {
                return redirect()->back()->with('error', trans('no_records_found'));
            }

$grades = $this->grade->builder()->orderBy('starting_range', 'ASC')->get();

$attendanceTotal = $this->attendance->builder()->where('student_id', $student_id)->where('type', 1)->where('session_year_id', $result->session_year_id)->count();

// dd($exams->toArray());
            return view('reports.exam.subject-wise-exam-result-pdf', compact('settings', 'result', 'grades', 'exams', 'studentAttendanceCount', 'attendanceTotal'));
        } catch (\Exception $e) {
            return redirect()->back()->with('error', $e->getMessage());
        }
    }

public function rankWiseResultShow(Request $request)
    {
        ResponseService::noFeatureThenRedirect('Exam Management');
        ResponseService::noPermissionThenSendJson('exam-result');
        $offset = request('offset', 0);
        $limit = request('limit', 10);
        $sort = request('sort', 'percentage');
        $order = request('order', 'DESC');
        $search = request('search');

$sessionYearId = $this->cache->getSessionYear()->id;
        $classSectionId = request('class_section_id');
        $subjectId = request('subject_id');

if (!$sessionYearId) {
            return response()->json([
                'total' => 0,
                'rows' => []
            ]);
        }

// 1. Build the base query for all students (with rank)
        $baseQuery = $this->examResult->builder()
            ->whereHas('exam', function ($q) {
                $q->where('publish', 1);
            })
            ->where('session_year_id', $sessionYearId)
            ->when($classSectionId, function ($q) use ($classSectionId) {
                $q->where('class_section_id', $classSectionId);
            })
            ->when($subjectId, function ($q) use ($subjectId) {
                $q->whereHas('user.exam_marks', function ($q) use ($subjectId) {
                    $q->whereHas('class_subject', function ($q) use ($subjectId) {
                        $q->where('subject_id', $subjectId);
                    });
                });
            })
            ->select([
                'exam_results.*',
                DB::raw("RANK() OVER (ORDER BY obtained_marks DESC) AS rank")
            ])->with('user');

// 2. Use the base query as a subquery
        $rankedSub = DB::table(DB::raw("({$baseQuery->toSql()}) as ranked"))
            ->mergeBindings($baseQuery->getQuery());

// 3. Apply search and pagination on the outer query
        if ($search) {
            $rankedSub->where(function ($q) use ($search) {
                $q->where('id', 'LIKE', "%$search%")
                    ->orWhere('total_marks', 'LIKE', "%$search%")
                    ->orWhere('grade', 'LIKE', "%$search%")
                    ->orWhere('obtained_marks', 'LIKE', "%$search%")
                    ->orWhere('percentage', 'LIKE', "%$search%")
                    ->orWhere('rank', 'LIKE', "%$search%")
                    ->orWhereExists(function ($sub) use ($search) {
                        $sub->select(DB::raw(1))
                            ->from('users')
                            ->whereRaw('users.id = ranked.student_id')
                            ->where(function ($q) use ($search) {
                                $q->where('users.first_name', 'LIKE', "%$search%")
                                    ->orWhere('users.last_name', 'LIKE', "%$search%");
                            });
                    });
            });
        }

$total = $rankedSub->count();
        if ($offset >= $total && $total > 0) {
            $lastPage = floor(($total - 1) / $limit) * $limit; // calculate last page offset
            $offset = $lastPage;
        }
        $results = $rankedSub
            ->orderBy($sort, $order)
            ->skip($offset)
            ->take($limit)
            ->get();

// Hydrate Eloquent models for relationships
        $examResultIds = $results->pluck('id')->toArray();
        $examResults = $this->examResult->builder()
            ->with([
                'user:id,first_name,last_name,school_id',
                'user.exam_marks' => function ($q) use ($subjectId) {
                    $q->with([
                        'timetable.class_subject.subject:id,name,type,code',
                        'class_subject.subject:id,name,type,code'
                    ]);
                    if ($subjectId) {
                        $q->whereHas('class_subject', function ($q) use ($subjectId) {
                            $q->where('subject_id', $subjectId);
                        });
                    }
                }
            ])
            ->whereIn('id', $examResultIds)
            ->get()
            ->keyBy('id');

$bulkData = array();
        $bulkData['total'] = $total;
        $rows = array();
        $no = 1;
        $grades = $this->grade->builder()->orderBy('starting_range', 'ASC')->get();

foreach ($results as $row) {
            $eloRow = $examResults[$row->id] ?? null;
            if (!$eloRow)
                continue;
            // Calculate marks and percentage based on subject filter
            if ($subjectId) {
                $examMarks = collect($eloRow->user->exam_marks)->filter(function ($mark) use ($subjectId) {
                    return $mark->class_subject->subject_id == $subjectId;
                });
                $totalMarks = $examMarks->sum(function ($mark) {
                    return $mark->timetable->total_marks;
                });
                $obtainedMarks = $examMarks->sum('obtained_marks');
                $percentage = $totalMarks > 0 ? ($obtainedMarks / $totalMarks) * 100 : 0;
            } else {
                $totalMarks = $eloRow->total_marks;
                $obtainedMarks = $eloRow->obtained_marks;
                $percentage = $eloRow->percentage;
            }
            // Calculate grade
            $grade = $this->generalService->getGradeByPercentage($percentage, $grades);
            // Add PDF view button if user has permission
            $operate = '';
            if (Auth::user()->can('exam-result-edit')) {
                $operate .= BootstrapTableService::button(
                    'fa fa-file-pdf-o',
                    url('reports/exam/rank-wise-result/' . $eloRow->student_id . '/' . $sessionYearId),
                    ['btn-gradient-info', 'btn-xs', 'btn-rounded', 'btn-icon'],
                    ['title' => __('view_result'), 'target' => '_blank']
                );
            }
            $tempRow = $eloRow->toArray();
            $tempRow['no'] = $no++;
            $tempRow['total_marks'] = number_format($totalMarks, 2);
            $tempRow['obtained_marks'] = number_format($obtainedMarks, 2);
            $tempRow['percentage'] = number_format($percentage, 2);
            $tempRow['grade'] = $grade;
            $tempRow['rank'] = $row->rank ?? '-';
            $tempRow['operate'] = $operate;
            $rows[] = $tempRow;
        }

$bulkData['rows'] = $rows;
        return response()->json($bulkData);
    }

public function rankWiseResultStatistics(Request $request)
    {
        ResponseService::noFeatureThenRedirect('Exam Management');
        ResponseService::noPermissionThenSendJson('exam-result');
        $sessionYearId = $this->cache->getSessionYear()->id;
        $classSectionId = $request->get('class_section_id');
        $subjectId = $request->get('subject_id');

try {
            // Base query for exam marks
            $query = DB::table('exam_marks')
                ->join('exam_timetables', 'exam_timetables.id', '=', 'exam_marks.exam_timetable_id')
                ->join('class_subjects', 'class_subjects.id', '=', 'exam_timetables.class_subject_id')
                ->join('exam_results', 'exam_results.student_id', '=', 'exam_marks.student_id')
                ->where('exam_results.session_year_id', $sessionYearId);

// Apply class section filter if provided
            if ($classSectionId) {
                $query->where('exam_results.class_section_id', $classSectionId);
            }

// Apply subject filter if provided
            if ($subjectId) {
                $query->where('class_subjects.subject_id', $subjectId);
            }

// Get total unique students
            $totalStudents = $query->distinct('exam_marks.student_id')->count('exam_marks.student_id');

if ($totalStudents == 0) {
                return response()->json([
                    'success' => true,
                    'total_students' => 0,
                    'total_pass' => 0,
                    'total_fail' => 0,
                    'pass_percentage' => 0
                ]);
            }

// Get passing marks and total marks from timetable
            $results = $query->select(
                'exam_marks.student_id',
                'exam_marks.obtained_marks',
                'exam_timetables.passing_marks',
                'exam_timetables.total_marks'
            )->get();

// Group results by student for subject-specific calculations
            $studentResults = collect($results)->groupBy('student_id')->map(function ($marks) {
                $totalMarks = $marks->sum('total_marks');
                $obtainedMarks = $marks->sum('obtained_marks');
                $passingMarks = $marks->sum('passing_marks');

return [
                    'total_marks' => $totalMarks,
                    'obtained_marks' => $obtainedMarks,
                    'passing_marks' => $passingMarks,
                    'is_pass' => $obtainedMarks >= $passingMarks
                ];
            });

$totalPass = $studentResults->where('is_pass', true)->count();
            $totalFail = $studentResults->where('is_pass', false)->count();

// Calculate pass percentage - ensure it cannot exceed 100%
            $passPercentage = min(100, ($totalStudents > 0 ? round(($totalPass / $totalStudents) * 100, 2) : 0));

return response()->json([
                'success' => true,
                'total_students' => $totalStudents,
                'total_pass' => $totalPass,
                'total_fail' => $totalFail,
                'pass_percentage' => $passPercentage
            ]);
        } catch (\Exception $e) {
            \Log::error('Error fetching rank wise result statistics: ' . $e->getMessage());
            return response()->json([
                'success' => false,
                'message' => 'Error fetching rank wise result statistics',
                'total_students' => 0,
                'total_pass' => 0,
                'total_fail' => 0,
                'pass_percentage' => 0
            ]);
        }
    }

public function rankWiseResultPdf($student_id, $session_year_id)
    {
        ResponseService::noFeatureThenRedirect('Exam Management');
        ResponseService::noPermissionThenRedirect('exam-result');
        try {
            // get school settings
            $settings = $this->cache->getSchoolSettings();

$schoolName = $settings['school_name'];
            $schoolLogo = $settings['horizontal_logo'];

$results = $this->examResult->builder()
                ->with([
                    'session_year',
                    'class_section.class.stream',
                    'class_section.class.shift',
                    'class_section.section',
                    'class_section.medium',

'user' => function ($q) use ($student_id) {
                        $q->with([
                            'student.guardian',
                            'exam_marks' => function ($q) use ($student_id) {
                                $q->whereHas('timetable', function ($q) use ($student_id) {
                                    $q->where('student_id', $student_id);
                                })
                                    ->with([
                                        'class_subject' => function ($q) {
                                            $q->withTrashed()->with('subject:id,name,type');
                                        },
                                        'timetable'
                                    ])
                                    ->withSum('timetable', 'total_marks');
                            }
                        ]);
                    }
                ])
                ->with(['exam' => function ($q) use ($session_year_id) {
                    $q->where('publish', 1)->where('session_year_id', $session_year_id);
                }])
                ->whereHas('exam', function ($q) {
                    $q->where('publish', 1);
                })
                ->where('session_year_id', $session_year_id)
                ->where('student_id', $student_id)
                ->select('exam_results.*')
                ->get();
            // return $results->sum('total_marks');
            // Convert the results to a collection
            $results = collect($results);

// Calculate Overall Rank based on total obtained marks for the session using SQL
            // 1. Calculate total marks for each student in the class/section for the given session year
            $studentTotalMarks = DB::table('exam_results')
                ->select('student_id', DB::raw('SUM(obtained_marks) as total_obtained'))
                ->join('exams', 'exam_results.exam_id', '=', 'exams.id')
                ->where('exam_results.class_section_id', $results->first()->class_section_id)
                ->where('exam_results.session_year_id', $session_year_id)
                ->where('exams.publish', 1)
                ->groupBy('student_id')
                ->orderByDesc('total_obtained')
                ->get();

// 2. Find the rank of the current student
            $rank = $studentTotalMarks->search(function ($item) use ($student_id) {
                return $item->student_id == $student_id;
            });

// 3. Assign overall rank (rank + 1 because search returns 0-based index)
            $overallRank = ($rank !== false) ? $rank + 1 : 'N/A';

// Assign this single overall rank to the student's result object (which is used for the header)
            // We just need one instance to pass to the view, so we pick the match for the current student
            $result = $results->where('student_id', $student_id)->first();
            if ($result) {
                $result->rank = $overallRank;
            }

$examIds = $result->pluck('exam_id')->toArray();
            $examIds = array_unique($examIds);

$classIds = $results->pluck('class_section.class_id')->toArray();
            $classIds = array_unique($classIds);

$exams = $this->exam->builder()
                ->with([
                    'timetable' => function ($q) {
                        $q->with('exam_marks');
                    }
                ])
                ->where('session_year_id', $session_year_id)
                ->whereIn('class_id', $classIds)
                ->whereIn('id', $examIds)
                ->where('publish', 1)
                ->get();

// ====================================================================
            if (!$result) {
                return redirect()->back()->with('error', trans('no_records_found'));
            }

$grades = $this->grade->builder()->orderBy('starting_range', 'ASC')->get();

$attendanceTotal = $this->attendance->builder()->where('student_id', $student_id)->where('session_year_id', $result->session_year_id)->count();

return view('reports.exam.rank-wise-exam-result-pdf', compact('settings', 'result', 'grades', 'exams', 'studentAttendanceCount', 'attendanceTotal', 'results'));
        } catch (\Exception $e) {
            return $e;
            return redirect()->back()->with('error', $e->getMessage());
        }

return response()->json($results);
    }

public function expenseReport()
    {
        ResponseService::noAnyPermissionThenRedirect(['reports-expense']);

$expenseCategory = $this->expenseCategory->builder()->pluck('name', 'id')->toArray();
        $vehicles = Vehicle::where('status', 1)->get(['name', 'id', 'vehicle_number']);
        $months = sessionYearWiseMonthYear();
        return view('reports.expense.index', compact('expenseCategory', 'vehicles', 'months'));
    }

public function expenseReportShow()
    {
        ResponseService::noPermissionThenRedirect('transportationexpense-list');
        $offset = request('offset', 0);
        $limit = request('limit', 10);
        $sort = request('sort', 'date');
        $order = request('order', 'DESC');
        $search = request('search');
        $category_id = request('category_id');
        $vehicle_id = request('vehicle_id');
        $month = request('month');
        $schoolSettings = $this->cache->getSchoolSettings();

$sessionYearId = $this->cache->getSessionYear()->id;
        $sql = $this->expense->builder()->where('session_year_id', $sessionYearId)->with('category', 'vehicle', 'created_by')->where(function ($query) use ($search) {
            $query->when($search, function ($query) use ($search) {
                $query->where(function ($query) use ($search) {
                    $query->where('title', 'LIKE', "%$search%")->orWhere('ref_no', 'LIKE', "%$search%")->orWhere('amount', 'LIKE', "%$search%")->orWhere('date', 'LIKE', "%$search%")->orWhere('description', 'LIKE', "%$search%")->orWhereHas('category', function ($q) use ($search) {
                        $q->Where('name', 'LIKE', "%$search%");
                    });
                });
            });
        });

if ($category_id) {
            if ($category_id != 'salary' && $category_id != 'transportation') {
                $sql->where('category_id', $category_id)->whereNull('staff_id');
            } else if ($category_id == 'transportation') {
                $sql->whereNotNull('vehicle_id');
            } else {
                $sql->whereNotNull('staff_id');
            }
        }

if ($vehicle_id) {
            $sql->where('vehicle_id', $vehicle_id);
        }

if ($month) {
            $monthData = explode('_', $month);
            if (count($monthData) == 2) {
                $sql->whereMonth('date', $monthData[0])->whereYear('date', $monthData[1]);
            } else {
                $sql->whereMonth('date', $month);
            }
        }

foreach ($res as $row) {
            $operate = '';
            if (!$row->month) {
                $operate .= BootstrapTableService::editButton(route('transportation-expense.update', $row->id));
                $operate .= BootstrapTableService::deleteButton(route('expense.destroy', $row->id));
            }

$tempRow = $row->toArray();
            $tempRow['no'] = $no++;
            $tempRow['amount'] = $row->amount;
            if ($row->category_id != null) {
                $tempRow['category_name'] = $row->category->name;
            } else {
                $tempRow['category_name'] = "Salary";
            }
            if (isset($row->vehicle->name) && isset($row->vehicle->vehicle_number)) {
                $tempRow['vehicle'] = $row->vehicle->name . " (" . $row->vehicle->vehicle_number . ")";

$fileUrl = $row->file ?? null;
                $fileExtension = '';
                if (!empty($fileUrl)) {
                    $fileExtension = strtolower(pathinfo($fileUrl, PATHINFO_EXTENSION));
                }
                $previewHtml = '';
                if ($fileExtension) {
                    if (in_array($fileExtension, ['jpg', 'jpeg', 'png', 'gif', 'bmp', 'webp'])) {
                        $previewHtml = '
                    <a href="' . $fileUrl . '" target="_blank" class="btn btn-sm btn-outline-info w-100 mt-2">
                        View Image
                    </a>';
                    } elseif ($fileExtension === 'pdf') {
                        $previewHtml = '
                    <a href="' . $fileUrl . '" target="_blank" class="btn btn-sm btn-outline-info w-100 mt-2">
                        View PDF
                    </a>';
                    } else {
                        $previewHtml = '<span class="text-danger">Unsupported file type</span>';
                    }
                }
                $tempRow['file'] = $previewHtml;
            }
            $tempRow['date'] = Carbon::parse($row->getRawOriginal('created_at'))->format($schoolSettings['date_format']);
            $tempRow['operate'] = $operate;
            $rows[] = $tempRow;
        }

$bulkData['rows'] = $rows;
        return response()->json($bulkData);
    }

public function teacher_reports()
    {
        ResponseService::noPermissionThenRedirect('reports-teacher');

$class_sections = $this->classSection->all(['*'], ['class', 'class.stream', 'class.shift', 'section', 'medium']);

if (Auth::user()->school_id) {
            $extraFields = $this->formFields->defaultModel()->where('user_type', 2)->orderBy('rank')->get();
        } else {
            $extraFields = $this->formFields->defaultModel()->orderBy('rank')->get();
        }

return view('reports.teacher.teacher-reports', compact('class_sections', 'extraFields'));
    }

public function teacher_reports_show(Request $request)
    {
        ResponseService::noPermissionThenRedirect('reports-teacher');

$offset = request('offset', 0);
        $limit = request('limit', 10);
        $sort = request('sort', 'id');
        $order = request('order', 'ASC');
        $search = request('search');

// Main student query
        $sql = $this->user->builder()->withTrashed()
            ->role('Teacher')
            ->with('staff', 'staff.staffSalary', 'extra_student_details.form_field')
            ->where(function ($query) use ($search) {

$query->when($search, function ($query) use ($search) {
                    $query->where('id', 'LIKE', "%{$search}%")
                        ->orWhere('first_name', 'LIKE', "%{$search}%")
                        ->orWhere('last_name', 'LIKE', "%{$search}%")
                        ->orWhere(DB::raw("CONCAT(first_name,' ',last_name)"), 'LIKE', "%{$search}%")  // FIXED
                        ->orWhere('gender', 'LIKE', "%{$search}%")
                        ->orWhere('email', 'LIKE', "%{$search}%")
                        ->orWhere('current_address', 'LIKE', "%{$search}%")
                        ->orWhere('permanent_address', 'LIKE', "%{$search}%");
                });
                // staff conditions in SAME group, not separate
                $query->orWhereHas('staff', function ($q) use ($search) {
                    $q->where('staffs.qualification', 'LIKE', "%{$search}%");
                });
            });

foreach ($res as $row) {
            $userId = $row->user_id;

$operate = BootstrapTableService::viewButton(
                route('reports.teacher.teacher-view-reports', [$row->id]),
                [],
                [
                    'title' => trans('View Teacher Details'),
                    'target' => ''
                ]
            );

$tempRow = $row->toArray();
            $tempRow['no'] = $no++;
            $tempRow['dob_org'] = $row->getRawOriginal('dob');
            $tempRow['joining_date_org'] = $row->staff->getRawOriginal('joining_date');
            $tempRow['extra_fields'] = $row->extra_student_details;

foreach ($row->extra_student_details as $field) {
                $data = '';
                if ($field->form_field->type == 'checkbox') {
                    $data = json_decode($field->data);
                } elseif ($field->form_field->type == 'file') {
                    $data = '<a href="' . Storage::url($field->data) . '" target="_blank">DOC</a>';
                } elseif ($field->form_field->type == 'dropdown') {
                    $data = $field->data ?? '';
                } else {
                    $data = $field->data;
                }

$tempRow[$field->form_field->name] = $data;
            }
            $tempRow['operate'] = $operate;
            $rows[] = $tempRow;
        }

$bulkData['rows'] = $rows;
        return response()->json($bulkData);
    }

public function teacher_view_reports($id)
    {
        ResponseService::noPermissionThenRedirect('reports-teacher');
        $schoolSettings = $this->cache->getSchoolSettings();
        $teacher = $this->user->builder()
            ->where('id', $id)
            ->with([
                'staff',
                'staff.staffSalary',
                'extra_student_details.form_field',
                'session_year:id,name',

'staff.subjects:id,class_section_id,teacher_id,subject_id',
                'staff.subjects.subject:id,name,type',

'staff.class_teacher:id,class_section_id,teacher_id',
            ])
            ->withTrashed()
            ->first();

if (!$teacher || !$teacher->staff) {
            return back()->with('error', 'Teacher not found');
        }

$classSectionIds = collect([
            ...$teacher->staff->subjects->pluck('class_section_id')->toArray(),
            ...$teacher->staff->class_teacher->pluck('class_section_id')->toArray(),
        ])
            ->unique()
            ->values();

$classSections = ClassSection::whereIn('id', $classSectionIds)
            ->with([
                'class',
                'class.stream',
                'class.shift',
                'section',
                'medium',
            ])
            ->get()
            ->keyBy('id');
        $teacher->staff->subjects->each(function ($item) use ($classSections) {
            $item->setRelation(
                'class_section',
                $classSections[$item->class_section_id] ?? null
            );
        });
        $teacher->staff->class_teacher->each(function ($item) use ($classSections) {
            $item->setRelation(
                'class_section',
                $classSections[$item->class_section_id] ?? null
            );
        });

$transportation = null;

if ($transportationPayments) {
            $routePickupPoint = null;

$transportation = [
                'plan' => [
                    'plan_status' => ($transportationPayments->expiry_date && $transportationPayments->expiry_date >= date('Y-m-d')) ? 'Active' : 'Expired',
                    'vehicle_assignment' => ($transportationPayments->routeVehicle) ? 'Assigned' : 'Pending',
                    'expiry_date' => $transportationPayments->expiry_date,
                    'paid_amount' => ($schoolSettings['currency_symbol'] ?? '$') . " " . $transportationPayments->amount,
                    'payment_mode' => $transportationPayments->paymentTransaction->payment_gateway ?? null,
                ],
                'shift' => [
                    'name' => isset($transportationPayments->shift->name) ? $transportationPayments->shift->name : null,
                    'start_time' => isset($transportationPayments->shift->start_time) ? Carbon::parse($transportationPayments->shift->start_time)->format($schoolSettings['time_format'] ?? 'H:i A') : null,
                    'end_time' => isset($transportationPayments->shift->end_time) ? Carbon::parse($transportationPayments->shift->end_time)->format($schoolSettings['time_format'] ?? 'H:i A') : null,
                ],
                'fee' => [
                    'duration' => optional($transportationPayments->transportationFee)->duration ?? null,
                    'amount' => ($schoolSettings['currency_symbol'] ?? '$') . " " . optional($transportationPayments->transportationFee)->fee_amount ?? null,
                ],
                'route' => [
                    'route_name' => $transportationPayments->routeVehicle->route->name ?? null,
                    'pickup_point_name' => $transportationPayments->pickupPoint->name ?? null,
                    'pickup_time' => $routePickupPoint?->pickup_time
                        ? Carbon::parse($routePickupPoint->pickup_time)->format($schoolSettings['time_format'] ?? 'H:i A')
                        : null,
                    'drop_time' => $routePickupPoint?->drop_time
                        ? Carbon::parse($routePickupPoint->drop_time)->format($schoolSettings['time_format'] ?? 'H:i A')
                        : null,
                ],
                'vehicle' => [
                    'name' => $transportationPayments->routeVehicle->vehicle->name ?? null,
                    'number' => $transportationPayments->routeVehicle->vehicle->vehicle_number ?? null,
                    'capacity' => $transportationPayments->routeVehicle->vehicle->capacity ?? null,
                    'driver' => $transportationPayments->routeVehicle->driver ?? null,
                    'helper' => $transportationPayments->routeVehicle->helper ?? null
                ],
            ];
        }
        $teacherID = $teacher->id;
        $timetables = $this->timetable->builder()->whereHas('subject_teacher', function ($q) use ($teacherID) {
            $q->where('teacher_id', $teacherID);
        })->with('subject:id,name,bg_color,type', 'class_section.class', 'class_section.class.shift', 'class_section.section', 'class_section.medium')->get();

// Get Timetable Settings Data
        $timetableSettingsData = $this->schoolSettings->getBulkData([
            'timetable_start_time',
            'timetable_end_time',
            'timetable_duration'
        ]);

$salaries = StaffSalary::where('staff_id', $teacher->staff->id)
            ->with('payrollSetting')
            ->get();

$basic_salary = $teacher->staff->salary;

$total_allowance = 0;
        $total_deduction = 0;

$allowance_details = [];
        $deduction_details = [];

foreach ($salaries as $salary) {

$setting = $salary->payrollSetting;
            if (!$setting)
                continue;

// Calculate value (amount or percentage)
            $value = $salary->amount ?? 0;

if ($setting->percentage) {
                $value = ($basic_salary * $setting->percentage) / 100;
            }

if ($setting->type === 'allowance') {

$total_allowance += $value;

$allowance_details[] = [
                    'name' => $setting->name,
                    'amount' => $value,
                    'type' => 'Allowance',
                ];
            } elseif ($setting->type === 'deduction') {

$total_deduction += $value;

$deduction_details[] = [
                    'name' => $setting->name,
                    'amount' => $value,
                    'type' => 'Deduction',
                ];
            }
        }

$net_salary = $basic_salary + $total_allowance - $total_deduction;

$salary_structure = [
            'basic_salary' => $basic_salary,
            'total_allowance' => $total_allowance,
            'total_deduction' => $total_deduction,
            'net_salary' => $net_salary,

'allowances' => $allowance_details,
            'deductions' => $deduction_details,
        ];

$leaves = Leave::where('user_id', $teacher->id)
            ->where('status', 1)
            ->whereYear('from_date', date('Y'))
            ->with('leave_detail')
            ->get();

// Get all session years for exam tab
        $sessionYears = $this->sessionYear->all();
        $sessionYear = $this->cache->getSessionYear();

$current = $start->copy();
                while ($current <= $end) {
                    $attendanceMonths[] = (object)[
                        'id' => $current->month,
                        'year' => $current->year,
                        'name' => $current->format('F Y')
                    ];
                    $current->addMonth();
                }
            } catch (\Exception $e) {
            }
        }

return view('reports.teacher.teacher-view-reports', compact('teacher', 'transportation', 'timetables', 'timetableSettingsData', 'salary_structure', 'sessionYear', 'attendanceMonths'));
    }

public function getTeacherAttendanceReport(Request $request)
    {
        ResponseService::noPermissionThenRedirect('reports-teacher');
        // Validate request parameters
        $request->validate([
            'month' => 'required|numeric|between:1,12',
            'attendance_year' => 'required|numeric',
            'teacher_id' => 'required|exists:users,id',
            'session_year_id' => 'required|exists:session_years,id',
        ]);

// Get session year from request
        $sessionYear = $this->sessionYear->findById($request->session_year_id);
        $schoolSettings = $this->cache->getSchoolSettings();

$year = $request->attendance_year;
        // Create a Carbon date for the first day of the month
        $startDate = Carbon::createFromDate($year, $request->month, 1)->startOfMonth();
        $endDate = Carbon::createFromDate($year, $request->month, 1)->endOfMonth();

// Get attendance records for this student in the specified month
        $attendanceRecords = StaffAttendance::where('staff_id', $request->teacher_id)
            ->whereBetween('date', [$startDate->format('Y-m-d'), $endDate->format('Y-m-d')])
            ->get();

$leaves = Leave::where('user_id', $request->teacher_id)->with('leave_detail')
            ->where('status', 1)
            ->where(function ($q) use ($startDate, $endDate) {
                $q->whereBetween('from_date', [$startDate, $endDate])
                    ->orWhereBetween('to_date', [$startDate, $endDate])
                    ->orWhere(function ($sub) use ($startDate, $endDate) {
                        $sub->where('from_date', '<=', $startDate)
                            ->where('to_date', '>=', $endDate);
                    });
            })
            ->whereHas('leave_detail', function ($q) {
                $q->where('type', 'Full');
            })
            ->get();
        // Convert leaves to a Y-m-d array for fast lookup
        $leaveDays = [];
        $finalFormat = $schoolSettings['date_format'] . ' ' . $schoolSettings['time_format'];
        foreach ($leaves as $leave) {
            // $from = Carbon::createFromFormat($finalFormat, $leave->from_date)->startOfDay();
            // $to = Carbon::createFromFormat($finalFormat, $leave->to_date)->startOfDay();

// $period = $from->daysUntil($to->copy()->addDay());

foreach ($leave->leave_detail as $leaveDetail) {
                $leaveDays[$leaveDetail->date] = true;
            }
        }

// handle holiday attendance
        $holidayAttendance = $this->holiday->builder()
            ->whereBetween('date', [$startDate->format('Y-m-d'), $endDate->format('Y-m-d')])
            ->get()
            ->map(function ($h) {
                return Carbon::parse($h->dmyFormat)->format('Y-m-d');
            });

foreach ($period as $day) {
                if (in_array($day->format('l'), $holiday_days)) {
                    $holidayAttendance->push($day->format('Y-m-d'));
                }
            }
        }

// Count present, absent and holiday days
        $presentCount = $attendanceRecords->where('type', 1)->count();
        $absentCount = $attendanceRecords->where('type', 0)->count();
        $holidayCount = $attendanceRecords->where('type', 3)->count();
        $holidayCount += $holidayAttendance->count();
        $halfCount = $attendanceRecords->where('type', 4)->count();
        $halfCount += $attendanceRecords->where('type', 5)->count();

// Calculate attendance percentage
        $totalDays = $presentCount + $absentCount;
        $attendancePercentage = $totalDays > 0 ? round(($presentCount / $totalDays) * 100) : 0;

// Prepare the response data
        $responseData = [
            'success' => true,
            'attendance' => $attendanceRecords,
            'leaves' => $leaveDays,
            'holiday' => $holidayAttendance,
            'summary' => [
                'present_count' => $presentCount,
                'absent_count' => $absentCount,
                'holiday_count' => $holidayCount,
                'half_count' => $halfCount,
                'attendance_percentage' => $attendancePercentage,
                'total_days' => $totalDays
            ]
        ];

return response()->json($responseData);
    }

public function teacherLeaves(Request $request)
    {
        ResponseService::noPermissionThenRedirect('reports-teacher');
        $teacherId = $request->teacher_id;
        $month = (int) $request->month;
        $year = (int) $request->year;

$startDate = Carbon::create($year, $month, 1)->startOfMonth();
        $endDate = Carbon::create($year, $month, 1)->endOfMonth();

// STEP 1 — Get leaves overlapping the month
        $leaves = Leave::where('user_id', $teacherId)
            ->where(function ($q) use ($startDate, $endDate) {
                $q->whereBetween('from_date', [$startDate, $endDate])
                    ->orWhereBetween('to_date', [$startDate, $endDate])
                    ->orWhere(function ($q2) use ($startDate, $endDate) {
                        $q2->where('from_date', '<=', $startDate)
                            ->where('to_date', '>=', $endDate);
                    });
            })
            ->with(['leave_detail']) // load leave type if needed
            ->orderBy('from_date', 'ASC')
            ->get();

// STEP 2 — Transform for frontend
        $final = [];

foreach ($leaves as $leave) {

// Filter leave_detail rows belonging to selected month
            $details = $leave->leave_detail
                ->filter(
                    fn($d) =>
                    Carbon::parse($d->date)->month == $month &&
                        Carbon::parse($d->date)->year == $year
                )
                ->map(fn($d) => [
                    'date' => $d->date,
                    'date_formatted' => Carbon::parse($d->date)->format('d M Y'),
                    'type' => $d->type,    // Full, First Half, Second Half
                ])
                ->values();

foreach ($details as $day) {
                $final[] = [
                    'leave_id' => $leave->id,
                    'status' => $leave->status,
                    'date' => $day['date'],
                    'date_formatted' => $day['date_formatted'],
                    'type' => $day['type'],
                    'reason' => $leave->reason,
                ];
            }
        }

return response()->json([
            'success' => true,
            'total' => count($final),
            'leaves' => $final,
        ]);
    }
}