EVOLUTION-NINJA

Edit File: ClassRoomController.php

<?php

namespace App\Http\Controllers;

use App\Models\ClassRoom;
use App\Models\employee;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Validation\Rule;

class ClassRoomController extends Controller
{
    // List all classes
    public function index()
    {
        return response()->json(
            ClassRoom::orderBy('name')->orderBy('section')->get()
        );
    }

// Create a class (teacher_name must exist in employees table)
    public function class_insert(Request $request)
    {
        $data = $request->validate([
            'name'          => ['required','string','max:150'],
            'section'       => ['nullable','string','max:50'],
            'teacher_name'  => ['required','string','max:200', Rule::exists('employees','name')],
        ]);

// Save snapshot of teacher_name
        $class = ClassRoom::create([
            'name'               => $data['name'],
            'section'            => $data['section'] ?? null,
            'class_teacher_name' => $data['teacher_name'],
        ]);

return response()->json(['message' => 'Class created', 'data' => $class], 201);
    }

// Update a class
    public function class_update(Request $request, $id)
    {
        $class = ClassRoom::findOrFail($id);

$data = $request->validate([
            'name'          => ['required','string','max:150'],
            'section'       => ['nullable','string','max:50'],
            'teacher_name'  => ['required','string','max:200', Rule::exists('employees','name')],
        ]);

$class->update([
            'name'               => $data['name'],
            'section'            => $data['section'] ?? null,
            'class_teacher_name' => $data['teacher_name'],
        ]);

return response()->json(['message' => 'Class updated', 'data' => $class]);
    }

// Delete a class
    public function class_delete($id)
    {
        $class = ClassRoom::findOrFail($id);
        $class->delete();

return response()->json(['message' => 'Class deleted']);
    }

// 🔎 Typeahead search for employees by name (to power your dropdown)
   public function searchEmployees(Request $request)
{
    $q = $request->q;

$query = Employee::query()
        ->select('employee_id', 'name')
        ->orderBy('name')
        ->limit(50); // optional limit

if (!empty($q)) {
        $query->where('name', 'like', "%{$q}%");
    }

return response()->json($query->get());
}
public function stats()
    {
        // Note: NULL section matches only NULL section in students
        // Gender values tolerated: Male/M, Boy and Female/F, Girl (case-insensitive)
        $rows = DB::table('classes as c')
            ->select([
                'c.id',
                'c.name',
                'c.section',
                'c.class_teacher_name',
                DB::raw("
                    (
                        SELECT COUNT(*) FROM add_students s
                        WHERE s.currentClass = c.name
                          AND (
                                (c.section IS NULL AND s.section IS NULL)
                             OR (c.section IS NOT NULL AND s.section = c.section)
                          )
                          AND UPPER(s.Gender) IN ('MALE','M','BOY')
                    ) as boys
                "),
                DB::raw("
                    (
                        SELECT COUNT(*) FROM add_students s
                        WHERE s.currentClass = c.name
                          AND (
                                (c.section IS NULL AND s.section IS NULL)
                             OR (c.section IS NOT NULL AND s.section = c.section)
                          )
                          AND UPPER(s.Gender) IN ('FEMALE','F','GIRL')
                    ) as girls
                "),
                DB::raw("
                    (
                        SELECT COUNT(*) FROM add_students s
                        WHERE s.currentClass = c.name
                          AND (
                                (c.section IS NULL AND s.section IS NULL)
                             OR (c.section IS NOT NULL AND s.section = c.section)
                          )
                    ) as total
                "),
            ])
            ->orderBy('c.name')
            ->orderBy('c.section')
            ->get();

// Map to the frontend-friendly shape
        $data = $rows->map(function ($r) {
            return [
                'id'      => (int) $r->id,
                'class'   => $r->name,
                'section' => $r->section,
                'teacher' => $r->class_teacher_name,
                'boys'    => (int) $r->boys,
                'girls'   => (int) $r->girls,
                'total'   => (int) $r->total,
            ];
        });

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

}