<?php
declare(strict_types=1);

/**
 * School và Class chỉ là thông tin hồ sơ dùng để thống kê.
 * Grade vẫn là dữ liệu hệ thống riêng và không bị thay thế.
 */
final class StudentStatisticsProfileService
{
    private PDO $db;

    public function __construct(PDO $db)
    {
        $this->db = $db;
    }

    public function getByStudentId(int $studentId): ?array
    {
        $stmt = $this->db->prepare(
            'SELECT
                id,
                full_name,
                province,
                school_name,
                class_name,
                current_level_id AS grade
             FROM students
             WHERE id = :student_id
             LIMIT 1'
        );

        $stmt->execute([':student_id' => $studentId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        return $row ?: null;
    }

    public function update(
        int $studentId,
        ?string $schoolName,
        ?string $className
    ): bool {
        $schoolName = $this->clean($schoolName, 255);
        $className = $this->clean($className, 50);

        $stmt = $this->db->prepare(
            'UPDATE students
             SET school_name = :school_name,
                 class_name = :class_name
             WHERE id = :student_id'
        );

        return $stmt->execute([
            ':school_name' => $schoolName,
            ':class_name' => $className,
            ':student_id' => $studentId,
        ]);
    }

    public function getStatistics(): array
    {
        $schoolStats = $this->db->query(
            "SELECT
                school_name,
                COUNT(*) AS student_count
             FROM students
             WHERE school_name IS NOT NULL
               AND TRIM(school_name) <> ''
             GROUP BY school_name
             ORDER BY student_count DESC, school_name ASC"
        )->fetchAll(PDO::FETCH_ASSOC);

        $classStats = $this->db->query(
            "SELECT
                class_name,
                COUNT(*) AS student_count
             FROM students
             WHERE class_name IS NOT NULL
               AND TRIM(class_name) <> ''
             GROUP BY class_name
             ORDER BY student_count DESC, class_name ASC"
        )->fetchAll(PDO::FETCH_ASSOC);

        return [
            'schools' => $schoolStats,
            'classes' => $classStats,
        ];
    }

    private function clean(?string $value, int $maxLength): ?string
    {
        $value = trim((string)$value);

        if ($value === '') {
            return null;
        }

        return mb_substr($value, 0, $maxLength);
    }
}
