<?php
class StudentLearningLevel
{
    public static function save(int $professionalId, int $studentId, string $category, string $level, string $note = ''): array
    {
        Database::migrate();
        $category = trim($category);
        $level = trim($level);
        $note = trim($note);
        if (!array_key_exists($category, student_learning_category_options())) {
            return ['ok' => false, 'message' => 'Selecione uma categoria de aprendizado válida.'];
        }
        if (!array_key_exists($level, student_level_options()) || $level === '') {
            return ['ok' => false, 'message' => 'Selecione o nível do aluno nesta categoria.'];
        }
        $link = Database::pdo()->prepare('SELECT COUNT(*) FROM appointments WHERE professional_id=? AND student_id=?');
        $link->execute([$professionalId, $studentId]);
        if ((int)$link->fetchColumn() <= 0) {
            return ['ok' => false, 'message' => 'Este aluno não possui aula vinculada ao seu cadastro.'];
        }
        $now = date('Y-m-d H:i:s');
        if (Database::driver() === 'mysql') {
            $stmt = Database::pdo()->prepare("INSERT INTO student_learning_levels (student_id,professional_id,category_key,level_value,note,updated_at)
                VALUES (?,?,?,?,?,?) ON DUPLICATE KEY UPDATE level_value=VALUES(level_value),note=VALUES(note),updated_at=VALUES(updated_at)");
        } else {
            $stmt = Database::pdo()->prepare("INSERT INTO student_learning_levels (student_id,professional_id,category_key,level_value,note,updated_at)
                VALUES (?,?,?,?,?,?) ON CONFLICT(student_id,professional_id,category_key) DO UPDATE SET level_value=excluded.level_value,note=excluded.note,updated_at=excluded.updated_at");
        }
        $stmt->execute([$studentId, $professionalId, $category, $level, $note !== '' ? $note : null, $now]);

        // Mantém o resumo antigo para não quebrar nenhuma tela ou integração já existente.
        $legacy = Database::pdo()->prepare("UPDATE users SET student_level=?,student_level_note=?,student_level_updated_at=?,student_level_professional_id=? WHERE id=? AND role='student' AND deleted_at IS NULL");
        $legacy->execute([$level, $note !== '' ? $note : null, $now, $professionalId, $studentId]);
        return ['ok' => true, 'message' => 'Nível salvo na categoria ' . student_learning_category_label($category) . '.'];
    }

    public static function forStudent(int $studentId): array
    {
        Database::migrate();
        $stmt = Database::pdo()->prepare("SELECT l.*,p.name AS professional_name
            FROM student_learning_levels l
            INNER JOIN professionals p ON p.id=l.professional_id
            WHERE l.student_id=? ORDER BY l.category_key,p.name");
        $stmt->execute([$studentId]);
        return $stmt->fetchAll();
    }

    public static function groupedForStudents(array $studentIds): array
    {
        Database::migrate();
        $studentIds = array_values(array_unique(array_filter(array_map('intval', $studentIds), static fn(int $id): bool => $id > 0)));
        if (!$studentIds) return [];
        $placeholders = implode(',', array_fill(0, count($studentIds), '?'));
        $stmt = Database::pdo()->prepare("SELECT l.*,p.name AS professional_name
            FROM student_learning_levels l INNER JOIN professionals p ON p.id=l.professional_id
            WHERE l.student_id IN ({$placeholders}) ORDER BY l.student_id,l.category_key,p.name");
        $stmt->execute($studentIds);
        $map = [];
        foreach ($stmt->fetchAll() as $row) {
            $map[(int)$row['student_id']][] = $row;
        }
        return $map;
    }
}
