<?php

namespace App\Services\Communication;

use Illuminate\Support\Facades\DB;

class CommunicationStudentService
{
    public function listStudents(): array
    {
        return DB::table('students')
            ->select('id', 'firstname', 'lastname', 'registration_grade')
            ->orderBy('lastname', 'asc')
            ->orderBy('firstname', 'asc')
            ->get()
            ->map(fn ($row) => (array) $row)
            ->all();
    }

    public function getStudentBasic(int $studentId): ?array
    {
        $row = DB::table('students as s')
            ->leftJoin('student_class as sc', 'sc.student_id', '=', 's.id')
            ->leftJoin('classSection as cs', 'cs.class_section_id', '=', 'sc.class_section_id')
            ->select(
                's.id',
                's.firstname',
                's.lastname',
                's.registration_grade',
                'cs.class_section_name as class_section_name'
            )
            ->where('s.id', $studentId)
            ->orderBy('sc.id', 'desc')
            ->first();

        if (!$row) {
            return null;
        }

        $row = (array) $row;
        $grade = $row['registration_grade'] ?? '';
        if (!$grade && !empty($row['class_section_name'])) {
            $grade = $row['class_section_name'];
        }

        return [
            'id' => (int) $row['id'],
            'firstname' => (string) ($row['firstname'] ?? ''),
            'lastname' => (string) ($row['lastname'] ?? ''),
            'grade' => (string) ($grade ?? ''),
        ];
    }
}
