<?php
class Appointment
{
    public static function book(int $studentId, int $slotId): array
    {
        Database::migrate();
        $pdo = Database::pdo();
        try {
            $pdo->beginTransaction();

            $stmt = $pdo->prepare('SELECT * FROM availability_slots WHERE id = ? AND is_active = 1 LIMIT 1');
            $stmt->execute([$slotId]);
            $slot = $stmt->fetch();
            if (!$slot) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Horário não encontrado ou indisponível.'];
            }

            if (strtotime($slot['start_at']) <= time()) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Não e possivel agendar horário passado.'];
            }

            $studentStmt = $pdo->prepare("SELECT uf, city, license_process FROM users WHERE id = ? AND role = 'student' AND deleted_at IS NULL LIMIT 1");
            $studentStmt->execute([$studentId]);
            $student = $studentStmt->fetch() ?: [];
            $holiday = Holiday::findForDate(substr($slot['start_at'], 0, 10), $student['uf'] ?? '', $student['city'] ?? '');
            if ($holiday) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Este dia está marcado como feriado no calendário: ' . ($holiday['name'] ?? 'Feriado') . '.'];
            }

            $stmt = $pdo->prepare("SELECT COUNT(*) FROM appointments WHERE active_slot_key = ? AND status = 'booked'");
            $stmt->execute([$slotId]);
            if ((int)$stmt->fetchColumn() > 0) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Esse horário acabou de ser ocupado por outro aluno. Escolha outro.'];
            }

            $lessonUnits = self::lessonUnitsFromRange($slot['start_at'] ?? null, $slot['end_at'] ?? null);
            $bookingSummary = self::studentBookingSummary($studentId);
            if (empty($bookingSummary['has_packages'])) {
                $pdo->rollBack();
                return ['ok'=>false,'message'=>'Nenhum pacote de aulas práticas está liberado para este aluno. Procure a administração.'];
            }
            $lessonCategory = self::firstBookableCategory($studentId,$lessonUnits);
            if($lessonCategory===''){
                $pdo->rollBack();
                return ['ok'=>false,'message'=>'Todas as aulas contratadas já foram concluídas ou estão agendadas. Procure a administração para contratar/liberar novas aulas.'];
            }
            $daily=self::validateDailyPracticalLimit($studentId,substr((string)$slot['start_at'],0,10),$lessonCategory,$lessonUnits,false);
            if(empty($daily['ok'])){
                $pdo->rollBack();
                return $daily;
            }
            $professionalCapacity = self::validateProfessionalDailyCapacity((int)$slot['professional_id'], substr((string)$slot['start_at'], 0, 10), $lessonUnits);
            if (empty($professionalCapacity['ok'])) {
                $pdo->rollBack();
                return $professionalCapacity;
            }
            $lessonProcess=trim((string)($student['license_process']??''));
            $stmt = $pdo->prepare('INSERT INTO appointments (student_id, professional_id, slot_id, active_slot_key, status, lesson_category, lesson_process, lesson_units, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)');
            $stmt->execute([$studentId, (int)$slot['professional_id'], $slotId, $slotId, 'booked', $lessonCategory, $lessonProcess!==''?$lessonProcess:null, $lessonUnits, date('Y-m-d H:i:s')]);
            $pdo->commit();
            QuickContact::upsertStudentForProfessional((int)$slot['professional_id'], $studentId);
            return ['ok' => true, 'message' => 'Aula agendada com sucesso. O contato interno entre aluno e instrutor foi liberado no sistema.'];
        } catch (PDOException $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            if ($e->getCode() === '23000') {
                return ['ok' => false, 'message' => 'Esse horário já está ocupado.'];
            }
            throw $e;
        }
    }

    public static function cancelByStudent(int $studentId, int $appointmentId): array
    {
        $stmt = Database::pdo()->prepare("SELECT a.*, s.start_at FROM appointments a INNER JOIN availability_slots s ON s.id = a.slot_id WHERE a.id = ? AND a.student_id = ? AND a.status = 'booked' LIMIT 1");
        $stmt->execute([$appointmentId, $studentId]);
        $appointment = $stmt->fetch();
        if (!$appointment) {
            return ['ok' => false, 'message' => 'Agendamento não encontrado.'];
        }

        $minHours = (int)config_value('cancel_min_hours', 24);
        $limitTs = strtotime($appointment['start_at']) - ($minHours * 3600);
        if (time() > $limitTs) {
            return ['ok' => false, 'message' => 'Cancelamento bloqueado. O prazo mínimo e de ' . $minHours . ' horas antes da aula.'];
        }

        $stmt = Database::pdo()->prepare("UPDATE appointments SET status = 'canceled', active_slot_key = NULL, canceled_at = ?, cancel_reason = ? WHERE id = ? AND student_id = ?");
        $stmt->execute([date('Y-m-d H:i:s'), 'Cancelado pelo aluno', $appointmentId, $studentId]);
        return ['ok' => true, 'message' => 'Agendamento cancelado com sucesso.'];
    }


    public static function notifyAbsenceByStudent(int $studentId, int $appointmentId, string $reason = ''): array
    {
        Database::migrate();
        $stmt = Database::pdo()->prepare("SELECT a.*, s.start_at FROM appointments a INNER JOIN availability_slots s ON s.id = a.slot_id WHERE a.id = ? AND a.student_id = ? AND a.status = 'booked' LIMIT 1");
        $stmt->execute([$appointmentId, $studentId]);
        $appointment = $stmt->fetch();
        if (!$appointment) {
            return ['ok' => false, 'message' => 'Agendamento não encontrado.'];
        }
        if (!empty($appointment['absence_notified_at'])) {
            return ['ok' => false, 'message' => 'Você já avisou que não vai comparecer a está aula.'];
        }
        if (!empty($appointment['student_no_show_at'])) {
            return ['ok' => false, 'message' => 'O instrutor já registrou falta para está aula.'];
        }
        if (strtotime($appointment['start_at']) < strtotime(date('Y-m-d 00:00:00'))) {
            return ['ok' => false, 'message' => 'Não e possivel avisar ausência em aula que já passou.'];
        }
        $stmt = Database::pdo()->prepare("UPDATE appointments SET absence_notified_at = ?, absence_reason = ? WHERE id = ? AND student_id = ? AND status = 'booked'");
        $stmt->execute([date('Y-m-d H:i:s'), trim($reason), $appointmentId, $studentId]);
        return ['ok' => true, 'message' => 'Aviso registrado. O Administrador e o Instrutor já conseguem ver que você não vai comparecer.'];
    }

    public static function markStudentNoShowByProfessional(int $professionalId, int $appointmentId, string $reason = ''): array
    {
        Database::migrate();
        $stmt = Database::pdo()->prepare("SELECT a.*, s.start_at FROM appointments a INNER JOIN availability_slots s ON s.id = a.slot_id WHERE a.id = ? AND a.professional_id = ? AND a.status = 'booked' LIMIT 1");
        $stmt->execute([$appointmentId, $professionalId]);
        $appointment = $stmt->fetch();
        if (!$appointment) {
            return ['ok' => false, 'message' => 'Aula não encontrada.'];
        }
        if (!empty($appointment['absence_notified_at'])) {
            return ['ok' => false, 'message' => 'O aluno já avisou que não vai comparecer.'];
        }
        if (!empty($appointment['student_no_show_at'])) {
            return ['ok' => false, 'message' => 'A falta deste aluno já foi registrada.'];
        }
        if (!empty($appointment['professional_absence_notified_at'])) {
            return ['ok' => false, 'message' => 'Você já avisou que não vai comparecer nesta aula.'];
        }
        if (!self::canRateByDate($appointment['start_at'])) {
            return ['ok' => false, 'message' => 'A falta do aluno só pode ser marcada a partir do dia da aula.'];
        }
        $stmt = Database::pdo()->prepare("UPDATE appointments SET student_no_show_at = ?, student_no_show_reason = ? WHERE id = ? AND professional_id = ? AND status = 'booked'");
        $stmt->execute([date('Y-m-d H:i:s'), trim($reason), $appointmentId, $professionalId]);
        return ['ok' => true, 'message' => 'Falta do aluno registrada. O administrador conseguira ver em vermelho no painel.'];
    }

    public static function notifyProfessionalAbsence(int $professionalId, int $appointmentId, string $reason = ''): array
    {
        Database::migrate();
        $stmt = Database::pdo()->prepare("SELECT a.*, s.start_at FROM appointments a INNER JOIN availability_slots s ON s.id = a.slot_id WHERE a.id = ? AND a.professional_id = ? AND a.status = 'booked' LIMIT 1");
        $stmt->execute([$appointmentId, $professionalId]);
        $appointment = $stmt->fetch();
        if (!$appointment) {
            return ['ok' => false, 'message' => 'Aula não encontrada.'];
        }
        if (!empty($appointment['professional_absence_notified_at'])) {
            return ['ok' => false, 'message' => 'Você já avisou que não vai comparecer nesta aula.'];
        }
        if (!empty($appointment['student_no_show_at'])) {
            return ['ok' => false, 'message' => 'Não e possivel avisar ausência depois de marcar falta do aluno nesta aula.'];
        }
        if (strtotime($appointment['start_at']) < strtotime(date('Y-m-d 00:00:00'))) {
            return ['ok' => false, 'message' => 'Não e possivel avisar ausência em aula que já passou.'];
        }
        $stmt = Database::pdo()->prepare("UPDATE appointments SET professional_absence_notified_at = ?, professional_absence_reason = ? WHERE id = ? AND professional_id = ? AND status = 'booked'");
        $stmt->execute([date('Y-m-d H:i:s'), trim($reason), $appointmentId, $professionalId]);
        return ['ok' => true, 'message' => 'Aviso registrado. O aluno e o administrador conseguirão ver que você não vai comparecer.'];
    }

    public static function myAppointments(int $studentId): array
    {
        Database::migrate();
        $stmt = Database::pdo()->prepare("SELECT a.*, s.start_at, s.end_at, p.name AS professional_name, p.type AS professional_type, p.internal_code AS professional_internal_code, p.profile_photo_path AS professional_photo_path
            FROM appointments a
            INNER JOIN availability_slots s ON s.id = a.slot_id
            INNER JOIN professionals p ON p.id = a.professional_id
            WHERE a.student_id = ?
            ORDER BY s.start_at DESC");
        $stmt->execute([$studentId]);
        return $stmt->fetchAll();
    }

    public static function all(array $filters = []): array
    {
        Database::migrate();
        $where = ['1=1'];
        $params = [];
        if (!empty($filters['date'])) {
            $where[] = 'DATE(s.start_at) = ?';
            $params[] = $filters['date'];
        }
        if (!empty($filters['professional_id'])) {
            $where[] = 'a.professional_id = ?';
            $params[] = (int)$filters['professional_id'];
        }
        if (!empty($filters['student'])) {
            $where[] = '(u.name LIKE ? OR u.email LIKE ? OR u.cpf LIKE ?)';
            $term = '%' . $filters['student'] . '%';
            $params[] = $term;
            $params[] = $term;
            $params[] = $term;
        }
        $sql = "SELECT a.*, u.name AS student_name, u.email AS student_email, u.whatsapp AS student_whatsapp, u.uf AS student_uf, u.city AS student_city,
                s.start_at, s.end_at, p.name AS professional_name, p.type AS professional_type
                FROM appointments a
                INNER JOIN users u ON u.id = a.student_id
                INNER JOIN availability_slots s ON s.id = a.slot_id
                INNER JOIN professionals p ON p.id = a.professional_id
                WHERE " . implode(' AND ', $where) . " ORDER BY s.start_at DESC, a.id DESC LIMIT 300";
        $stmt = Database::pdo()->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll();
        usort($rows, static function(array $a, array $b): int {
            $byProfessional = strcasecmp((string)($a['professional_name'] ?? ''), (string)($b['professional_name'] ?? ''));
            if ($byProfessional !== 0) return $byProfessional;
            $byDate = strcmp((string)($a['start_at'] ?? ''), (string)($b['start_at'] ?? ''));
            return $byDate !== 0 ? $byDate : ((int)($a['id'] ?? 0) <=> (int)($b['id'] ?? 0));
        });
        return $rows;
    }

    public static function ratings(array $filters = []): array
    {
        Database::migrate();
        $where = ['(a.student_rating IS NOT NULL OR a.professional_rating IS NOT NULL)'];
        $params = [];
        if (!empty($filters['professional_id'])) {
            $where[] = 'a.professional_id = ?';
            $params[] = (int)$filters['professional_id'];
        }
        if (!empty($filters['date_from'])) {
            $where[] = 'DATE(s.start_at) >= ?';
            $params[] = $filters['date_from'];
        }
        if (!empty($filters['date_to'])) {
            $where[] = 'DATE(s.start_at) <= ?';
            $params[] = $filters['date_to'];
        }
        $stmt = Database::pdo()->prepare("SELECT a.*, u.name AS student_name, u.email AS student_email,
                s.start_at, s.end_at, p.name AS professional_name
            FROM appointments a
            INNER JOIN users u ON u.id = a.student_id
            INNER JOIN availability_slots s ON s.id = a.slot_id
            INNER JOIN professionals p ON p.id = a.professional_id
            WHERE " . implode(' AND ', $where) . " ORDER BY CASE WHEN a.status='completed' THEN 1 ELSE 0 END ASC, s.start_at DESC LIMIT 500");
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public static function ratingStats(): array
    {
        Database::migrate();
        $row = Database::pdo()->query("SELECT
            COUNT(student_rating) AS total_student_reviews,
            ROUND(AVG(student_rating), 2) AS avg_professional_score,
            COUNT(professional_rating) AS total_professional_reviews,
            ROUND(AVG(professional_rating), 2) AS avg_student_score
            FROM appointments
            WHERE student_rating IS NOT NULL OR professional_rating IS NOT NULL")->fetch();
        return $row ?: [];
    }

    public static function countBooked(): int
    {
        Database::migrate();
        return (int)Database::pdo()->query("SELECT COALESCE(SUM(CASE WHEN lesson_units IS NULL OR lesson_units < 1 THEN 1 ELSE lesson_units END), 0) FROM appointments WHERE status = 'booked'")->fetchColumn();
    }

    public static function countCompleted(): int
    {
        Database::migrate();
        return (int)Database::pdo()->query("SELECT COALESCE(SUM(CASE WHEN lesson_units IS NULL OR lesson_units < 1 THEN 1 ELSE lesson_units END), 0) FROM appointments WHERE status = 'completed' OR lesson_confirmed_at IS NOT NULL")->fetchColumn();
    }

    public static function canRateByDate(string $startAt): bool
    {
        $classDay = date('Y-m-d', strtotime($startAt));
        return date('Y-m-d') >= $classDay;
    }

    /**
     * A confirmação da aula depende somente do horário de término registrado.
     * Não existe carência adicional de 50 minutos e não existe prazo máximo:
     * uma aula retroativa pode ser confirmada horas, dias ou meses depois,
     * desde que o término agendado já tenha passado.
     */
    public static function canConfirmAfterScheduledEnd(?string $endAt): bool
    {
        $endAt = trim((string)$endAt);
        if ($endAt === '') {
            return false;
        }

        try {
            $timezoneName = (string)config_value('timezone', date_default_timezone_get());
            $timezone = new DateTimeZone($timezoneName ?: date_default_timezone_get());
            $scheduledEnd = new DateTimeImmutable($endAt, $timezone);
            $now = new DateTimeImmutable('now', $timezone);
            return $now >= $scheduledEnd;
        } catch (Throwable $e) {
            return false;
        }
    }

    public static function rateProfessionalByStudent(int $studentId, int $appointmentId, int $rating, string $comment = ''): array
    {
        Database::migrate();
        $rating = max(1, min(5, $rating));
        $stmt = Database::pdo()->prepare("SELECT a.*, s.start_at FROM appointments a INNER JOIN availability_slots s ON s.id = a.slot_id WHERE a.id = ? AND a.student_id = ? AND a.status IN ('booked','completed') LIMIT 1");
        $stmt->execute([$appointmentId, $studentId]);
        $appointment = $stmt->fetch();
        if (!$appointment) {
            return ['ok' => false, 'message' => 'Aula não encontrada para avaliação.'];
        }
        if (!self::canRateByDate($appointment['start_at'])) {
            return ['ok' => false, 'message' => 'A avaliação so fica liberada a partir do dia da aula.'];
        }
        if (!empty($appointment['student_rating'])) {
            return ['ok' => false, 'message' => 'Você já avaliou esse profissional nesta aula.'];
        }
        $stmt = Database::pdo()->prepare('UPDATE appointments SET student_rating = ?, student_rating_comment = ?, student_rated_at = ? WHERE id = ? AND student_id = ?');
        $stmt->execute([$rating, trim($comment), date('Y-m-d H:i:s'), $appointmentId, $studentId]);
        return ['ok' => true, 'message' => 'Avaliação enviada com sucesso. Obrigado!'];
    }

    public static function rateStudentByProfessional(int $professionalId, int $appointmentId, int $rating, string $comment = ''): array
    {
        Database::migrate();
        $rating = max(1, min(5, $rating));
        $stmt = Database::pdo()->prepare("SELECT a.*, s.start_at FROM appointments a INNER JOIN availability_slots s ON s.id = a.slot_id WHERE a.id = ? AND a.professional_id = ? AND a.status IN ('booked','completed') LIMIT 1");
        $stmt->execute([$appointmentId, $professionalId]);
        $appointment = $stmt->fetch();
        if (!$appointment) {
            return ['ok' => false, 'message' => 'Aula não encontrada para avaliação.'];
        }
        if (!self::canRateByDate($appointment['start_at'])) {
            return ['ok' => false, 'message' => 'A avaliação só fica liberada a partir do dia da aula.'];
        }
        if (!empty($appointment['professional_rating'])) {
            return ['ok' => false, 'message' => 'Este aluno já foi avaliado nesta aula.'];
        }
        $stmt = Database::pdo()->prepare('UPDATE appointments SET professional_rating = ?, professional_rating_comment = ?, professional_rated_at = ? WHERE id = ? AND professional_id = ?');
        $stmt->execute([$rating, trim($comment), date('Y-m-d H:i:s'), $appointmentId, $professionalId]);
        return ['ok' => true, 'message' => 'Avaliação do aluno registrada.'];
    }

    public static function bookManualByAdmin(int $studentId, int $professionalId, string $date, string $time, int $durationMinutes = 50, string $lessonColor = 'blue', bool $allowHoliday = false, string $lessonCategory = '', string $lessonSubject = '', string $lessonProcess = '', bool $allowDailyLimitOverride = false): array
    {
        Database::migrate();
        $durationMinutes = max(30, min(240, $durationMinutes));
        $lessonColor = normalize_lesson_color($lessonColor);
        $lessonCategory = normalize_driving_category($lessonCategory);
        $lessonSubject = trim($lessonSubject);
        $lessonProcess = trim($lessonProcess);
        if(!array_key_exists($lessonProcess,license_process_options())) $lessonProcess='';
        $date = trim($date);
        $time = trim($time);

        if ($studentId <= 0 || $professionalId <= 0 || $date === '' || $time === '') {
            return ['ok' => false, 'message' => 'Informe aluno, profissional, data e horário.'];
        }
        if ($lessonCategory === '' || $lessonSubject === '' || $lessonProcess==='') {
            return ['ok' => false, 'message' => 'Informe a categoria, o tipo (primeira habilitação, adição ou classificação) e o conteúdo da aula.'];
        }
        if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date) || !preg_match('/^\d{2}:\d{2}$/', $time)) {
            return ['ok' => false, 'message' => 'Data ou horário invalido.'];
        }

        $start = DateTime::createFromFormat('Y-m-d H:i', $date . ' ' . $time);
        if (!$start) {
            return ['ok' => false, 'message' => 'Não foi possivel montar a data do agendamento.'];
        }
        $end = clone $start;
        $end->modify('+' . $durationMinutes . ' minutes');
        $startAt = $start->format('Y-m-d H:i:s');
        $endAt = $end->format('Y-m-d H:i:s');

        $pdo = Database::pdo();
        try {
            $pdo->beginTransaction();

            $studentStmt = $pdo->prepare("SELECT id, name, student_status FROM users WHERE id = ? AND role = 'student' AND deleted_at IS NULL LIMIT 1");
            $studentStmt->execute([$studentId]);
            $student = $studentStmt->fetch();
            if (!$student) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Aluno não encontrado.'];
            }
            if (($student['student_status'] ?? '') !== 'active') {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Este aluno não está ativo. Reative o cadastro antes de agendar.'];
            }

            $studentPlaceStmt = $pdo->prepare("SELECT uf, city FROM users WHERE id = ? LIMIT 1");
            $studentPlaceStmt->execute([$studentId]);
            $studentPlace = $studentPlaceStmt->fetch() ?: [];
            $holiday = Holiday::findForDate($date, $studentPlace['uf'] ?? '', $studentPlace['city'] ?? '');
            if ($holiday && !$allowHoliday) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Este dia está marcado como feriado/data comemorativa: ' . ($holiday['name'] ?? 'Feriado') . '. Se for necessário agendar mesmo assim, marque a opção de desbloqueio no formulário.'];
            }

            $profStmt = $pdo->prepare('SELECT id FROM professionals WHERE id = ? AND is_active = 1 LIMIT 1');
            $profStmt->execute([$professionalId]);
            if (!$profStmt->fetch()) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Profissional não encontrado ou inativo.'];
            }

            $lessonUnitsForCapacity = self::lessonUnitsFromRange($startAt, $endAt);
            $professionalCapacity = self::validateProfessionalDailyCapacity($professionalId, $date, $lessonUnitsForCapacity);
            if (empty($professionalCapacity['ok'])) {
                $pdo->rollBack();
                return $professionalCapacity;
            }

            $overlapProfessional = $pdo->prepare("SELECT COUNT(*)
                FROM appointments a
                INNER JOIN availability_slots s ON s.id = a.slot_id
                WHERE a.professional_id = ? AND a.status = 'booked' AND s.start_at < ? AND s.end_at > ?");
            $overlapProfessional->execute([$professionalId, $endAt, $startAt]);
            if ((int)$overlapProfessional->fetchColumn() > 0) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Esse profissional já possui aula nesse período.'];
            }

            $overlapStudent = $pdo->prepare("SELECT COUNT(*)
                FROM appointments a
                INNER JOIN availability_slots s ON s.id = a.slot_id
                WHERE a.student_id = ? AND a.status = 'booked' AND s.start_at < ? AND s.end_at > ?");
            $overlapStudent->execute([$studentId, $endAt, $startAt]);
            if ((int)$overlapStudent->fetchColumn() > 0) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Esse aluno já possui aula nesse período.'];
            }

            $slotStmt = $pdo->prepare('SELECT * FROM availability_slots WHERE professional_id = ? AND start_at = ? LIMIT 1');
            $slotStmt->execute([$professionalId, $startAt]);
            $slot = $slotStmt->fetch();

            if ($slot) {
                $slotId = (int)$slot['id'];
                $updateSlot = $pdo->prepare('UPDATE availability_slots SET end_at = ?, is_active = 1 WHERE id = ?');
                $updateSlot->execute([$endAt, $slotId]);
            } else {
                $insertSlot = $pdo->prepare('INSERT INTO availability_slots (professional_id, start_at, end_at, is_active, created_at) VALUES (?, ?, ?, 1, ?)');
                $insertSlot->execute([$professionalId, $startAt, $endAt, date('Y-m-d H:i:s')]);
                $slotId = (int)$pdo->lastInsertId();
            }

            $slotTaken = $pdo->prepare("SELECT COUNT(*) FROM appointments WHERE active_slot_key = ? AND status = 'booked'");
            $slotTaken->execute([$slotId]);
            if ((int)$slotTaken->fetchColumn() > 0) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Esse horário já está ocupado.'];
            }

            $lessonUnits = self::lessonUnitsFromRange($startAt, $endAt);
            $balance=self::bookingBalanceForCategory($studentId,$lessonCategory);
            if(empty($balance['exists'])){
                $pdo->rollBack();
                return ['ok'=>false,'message'=>'O aluno não possui pacote contratado para a categoria '.$lessonCategory.'. Atualize o cadastro antes de agendar.'];
            }
            if((int)$balance['available_to_schedule']<$lessonUnits){
                $pdo->rollBack();
                return ['ok'=>false,'message'=>'Quantidade contratada já completada ou totalmente agendada na categoria '.$lessonCategory.'. Disponível para novo agendamento: '.max(0,(int)$balance['available_to_schedule']).' aula(s).'];
            }
            $daily=self::validateDailyPracticalLimit($studentId,$date,$lessonCategory,$lessonUnits,$allowDailyLimitOverride);
            if(empty($daily['ok'])){
                $pdo->rollBack();
                return $daily;
            }
            $insertAppointment = $pdo->prepare('INSERT INTO appointments (student_id, professional_id, slot_id, active_slot_key, status, lesson_color, lesson_category, lesson_subject, lesson_process, lesson_units, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');
            $insertAppointment->execute([$studentId, $professionalId, $slotId, $slotId, 'booked', $lessonColor, $lessonCategory, $lessonSubject, $lessonProcess, $lessonUnits, date('Y-m-d H:i:s')]);

            $pdo->commit();
            QuickContact::upsertStudentForProfessional($professionalId, $studentId);
            return ['ok' => true, 'message' => 'Agendamento manual criado. O contato interno entre aluno e instrutor foi liberado no sistema.'];
        } catch (PDOException $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            if ($e->getCode() === '23000') {
                return ['ok' => false, 'message' => 'Esse horário já está cadastrado ou ocupado.'];
            }
            throw $e;
        }
    }


    public static function rescheduleByAdmin(int $appointmentId, string $date, string $time, int $durationMinutes = 50, bool $allowHoliday = false, bool $allowDailyLimitOverride = false): array
    {
        Database::migrate();
        $date=trim($date); $time=trim($time); $durationMinutes=max(30,min(240,$durationMinutes));
        if($appointmentId<=0 || !preg_match('/^\d{4}-\d{2}-\d{2}$/',$date) || !preg_match('/^\d{2}:\d{2}$/',$time)){
            return ['ok'=>false,'message'=>'Informe uma data e um horário válidos.'];
        }
        $start=DateTime::createFromFormat('Y-m-d H:i',$date.' '.$time);
        if(!$start) return ['ok'=>false,'message'=>'Não foi possível montar a nova data da aula.'];
        $end=clone $start; $end->modify('+'.$durationMinutes.' minutes');
        $startAt=$start->format('Y-m-d H:i:s'); $endAt=$end->format('Y-m-d H:i:s');
        $pdo=Database::pdo();
        try{
            $pdo->beginTransaction();
            $stmt=$pdo->prepare("SELECT a.*, s.start_at AS old_start_at, s.end_at AS old_end_at, u.uf AS student_uf, u.city AS student_city
                FROM appointments a INNER JOIN availability_slots s ON s.id=a.slot_id INNER JOIN users u ON u.id=a.student_id
                WHERE a.id=? LIMIT 1");
            $stmt->execute([$appointmentId]); $a=$stmt->fetch();
            if(!$a || ($a['status']??'')!=='booked'){
                $pdo->rollBack(); return ['ok'=>false,'message'=>'A aula não foi encontrada ou já foi concluída/cancelada.'];
            }
            $holiday=Holiday::findForDate($date,$a['student_uf']??'',$a['student_city']??'');
            if($holiday && !$allowHoliday){ $pdo->rollBack(); return ['ok'=>false,'message'=>'A nova data está marcada como feriado/data comemorativa. Marque “Desbloquear feriado” para confirmar.']; }
            $units=self::lessonUnitsFromRange($startAt,$endAt);
            $category=normalize_driving_category((string)($a['lesson_category']??''));
            $balance=self::bookingBalanceForCategory((int)$a['student_id'],$category,$appointmentId);
            if(empty($balance['exists']) || (int)$balance['available_to_schedule']<$units){ $pdo->rollBack(); return ['ok'=>false,'message'=>'O pacote contratado não possui saldo suficiente para a nova duração.']; }
            $studentDaily=self::validateDailyPracticalLimit((int)$a['student_id'],$date,$category,$units,$allowDailyLimitOverride,$appointmentId);
            if(empty($studentDaily['ok'])){ $pdo->rollBack(); return $studentDaily; }
            $capacity=self::validateProfessionalDailyCapacity((int)$a['professional_id'],$date,$units,$appointmentId);
            if(empty($capacity['ok'])){ $pdo->rollBack(); return $capacity; }
            $overlap=$pdo->prepare("SELECT COUNT(*) FROM appointments ax INNER JOIN availability_slots sx ON sx.id=ax.slot_id
                WHERE ax.id<>? AND ax.professional_id=? AND ax.status='booked' AND sx.start_at<? AND sx.end_at>?");
            $overlap->execute([$appointmentId,(int)$a['professional_id'],$endAt,$startAt]);
            if((int)$overlap->fetchColumn()>0){ $pdo->rollBack(); return ['ok'=>false,'message'=>'O instrutor já possui outra aula nesse novo período.']; }
            $overlapStudent=$pdo->prepare("SELECT COUNT(*) FROM appointments ax INNER JOIN availability_slots sx ON sx.id=ax.slot_id
                WHERE ax.id<>? AND ax.student_id=? AND ax.status='booked' AND sx.start_at<? AND sx.end_at>?");
            $overlapStudent->execute([$appointmentId,(int)$a['student_id'],$endAt,$startAt]);
            if((int)$overlapStudent->fetchColumn()>0){ $pdo->rollBack(); return ['ok'=>false,'message'=>'O aluno já possui outra aula nesse novo período.']; }
            $slot=$pdo->prepare('SELECT id FROM availability_slots WHERE professional_id=? AND start_at=? LIMIT 1');
            $slot->execute([(int)$a['professional_id'],$startAt]); $targetSlotId=(int)($slot->fetchColumn()?:0);
            if($targetSlotId>0 && $targetSlotId!==(int)$a['slot_id']){
                $taken=$pdo->prepare("SELECT COUNT(*) FROM appointments WHERE id<>? AND active_slot_key=? AND status='booked'");
                $taken->execute([$appointmentId,$targetSlotId]);
                if((int)$taken->fetchColumn()>0){ $pdo->rollBack(); return ['ok'=>false,'message'=>'O novo horário já está ocupado.']; }
                $pdo->prepare('UPDATE availability_slots SET end_at=?, is_active=1 WHERE id=?')->execute([$endAt,$targetSlotId]);
            } elseif($targetSlotId===(int)$a['slot_id']) {
                $pdo->prepare('UPDATE availability_slots SET start_at=?, end_at=?, is_active=1 WHERE id=?')->execute([$startAt,$endAt,(int)$a['slot_id']]);
            } else {
                $ins=$pdo->prepare('INSERT INTO availability_slots (professional_id,start_at,end_at,is_active,created_at) VALUES (?,?,?,1,?)');
                $ins->execute([(int)$a['professional_id'],$startAt,$endAt,date('Y-m-d H:i:s')]); $targetSlotId=(int)$pdo->lastInsertId();
            }
            $upd=$pdo->prepare("UPDATE appointments SET slot_id=?, active_slot_key=?, lesson_units=? WHERE id=? AND status='booked'");
            $upd->execute([$targetSlotId,$targetSlotId,$units,$appointmentId]);
            $pdo->commit();
            return ['ok'=>true,'message'=>'Data e horário da aula alterados sem cancelar o agendamento.'];
        }catch(PDOException $e){
            if($pdo->inTransaction()) $pdo->rollBack();
            if($e->getCode()==='23000') return ['ok'=>false,'message'=>'O novo horário já está ocupado.'];
            throw $e;
        }
    }


    public static function weeklyAdminBoard(string $weekStart): array
    {
        Database::migrate();
        $start=DateTime::createFromFormat('Y-m-d H:i:s',$weekStart.' 00:00:00') ?: new DateTime('monday this week 00:00:00');
        $end=clone $start; $end->modify('+7 days');
        $stmt=Database::pdo()->prepare("SELECT a.*, u.name AS student_name, u.category AS student_category, u.uf AS student_uf, u.city AS student_city, s.start_at, s.end_at, p.name AS professional_name, p.type AS professional_type FROM appointments a INNER JOIN users u ON u.id=a.student_id INNER JOIN availability_slots s ON s.id=a.slot_id INNER JOIN professionals p ON p.id=a.professional_id WHERE s.start_at>=? AND s.start_at<? AND a.status IN ('booked','completed') ORDER BY p.name, s.start_at");
        $stmt->execute([$start->format('Y-m-d H:i:s'),$end->format('Y-m-d H:i:s')]); return $stmt->fetchAll();
    }

    public static function byStudentHistory(int $studentId): array
    {
        Database::migrate();
        if ($studentId <= 0) {
            return [];
        }
        $stmt = Database::pdo()->prepare("SELECT a.*, u.name AS student_name, u.email AS student_email, u.whatsapp AS student_whatsapp, u.cpf AS student_cpf, u.uf AS student_uf, u.city AS student_city,
                s.start_at, s.end_at, p.name AS professional_name, p.type AS professional_type
            FROM appointments a
            LEFT JOIN users u ON u.id = a.student_id
            INNER JOIN availability_slots s ON s.id = a.slot_id
            INNER JOIN professionals p ON p.id = a.professional_id
            WHERE a.student_id = ?
            ORDER BY s.start_at DESC, a.id DESC");
        $stmt->execute([$studentId]);
        return $stmt->fetchAll();
    }

    public static function byProfessionalMonth(int $professionalId, string $month): array
    {
        Database::migrate();
        if ($professionalId <= 0) {
            return [];
        }
        if (!preg_match('/^\d{4}-\d{2}$/', $month)) {
            $month = date('Y-m');
        }
        $from = DateTime::createFromFormat('Y-m-d H:i:s', $month . '-01 00:00:00') ?: new DateTime('first day of this month 00:00:00');
        $to = clone $from;
        $to->modify('first day of next month');
        $stmt = Database::pdo()->prepare("SELECT a.*, u.name AS student_name, u.email AS student_email, u.whatsapp AS student_whatsapp, u.cpf AS student_cpf, u.category AS student_category, u.student_level, u.student_level_note, u.student_level_updated_at, u.profile_photo_path, u.internal_code AS student_internal_code,
                s.start_at, s.end_at, p.name AS professional_name, p.type AS professional_type
            FROM appointments a
            LEFT JOIN users u ON u.id = a.student_id
            INNER JOIN availability_slots s ON s.id = a.slot_id
            INNER JOIN professionals p ON p.id = a.professional_id
            WHERE a.professional_id = ? AND s.start_at >= ? AND s.start_at < ?
            ORDER BY CASE WHEN a.status='completed' OR a.lesson_confirmed_at IS NOT NULL THEN 1 ELSE 0 END ASC, s.start_at ASC, a.id ASC");
        $stmt->execute([$professionalId, $from->format('Y-m-d H:i:s'), $to->format('Y-m-d H:i:s')]);
        return $stmt->fetchAll();
    }

    public static function lessonUnitsFromRange(?string $startAt, ?string $endAt): int
    {
        if (!$startAt || !$endAt) {
            return 1;
        }
        $start = strtotime($startAt);
        $end = strtotime($endAt);
        if (!$start || !$end || $end <= $start) {
            return 1;
        }
        $minutes = max(1, (int)ceil(($end - $start) / 60));
        return max(1, (int)ceil($minutes / 50));
    }

    public static function professionalCounters(int $professionalId, ?string $month = null): array
    {
        Database::migrate();
        $now = date('Y-m-d H:i:s');

        $sql = "SELECT a.status, a.lesson_units, a.lesson_confirmed_at, a.absence_notified_at, a.student_no_show_at, a.professional_absence_notified_at, s.start_at, s.end_at
            FROM appointments a
            INNER JOIN availability_slots s ON s.id = a.slot_id
            WHERE a.professional_id = ?";
        $params = [$professionalId];

        if ($month !== null) {
            if (!preg_match('/^\d{4}-\d{2}$/', $month)) {
                $month = date('Y-m');
            }
            $from = DateTime::createFromFormat('Y-m-d H:i:s', $month . '-01 00:00:00') ?: new DateTime('first day of this month 00:00:00');
            $to = clone $from;
            $to->modify('first day of next month');
            $sql .= " AND s.start_at >= ? AND s.start_at < ?";
            $params[] = $from->format('Y-m-d H:i:s');
            $params[] = $to->format('Y-m-d H:i:s');
        }

        $stmt = Database::pdo()->prepare($sql);
        $stmt->execute($params);

        $done = 0;
        $notDone = 0;
        $future = 0;
        $total = 0;

        foreach ($stmt->fetchAll() as $row) {
            $units = max(1, (int)($row['lesson_units'] ?: self::lessonUnitsFromRange($row['start_at'] ?? null, $row['end_at'] ?? null)));
            $total += $units;

            $hasOccurrence = !empty($row['absence_notified_at']) || !empty($row['student_no_show_at']) || !empty($row['professional_absence_notified_at']);
            if (($row['status'] ?? '') === 'completed' || !empty($row['lesson_confirmed_at'])) {
                $done += $units;
            } elseif (($row['status'] ?? '') === 'canceled' || (($row['start_at'] ?? '') <= $now && ($hasOccurrence || ($row['status'] ?? '') === 'booked'))) {
                $notDone += $units;
            } elseif (($row['status'] ?? '') === 'booked' && ($row['start_at'] ?? '') > $now) {
                $future += $units;
            }
        }

        return [
            'done_count' => $done,
            'not_done_count' => $notDone,
            'future_count' => $future,
            'total_count' => $total,
        ];
    }




    public static function bookingBalanceForCategory(int $studentId, string $category, int $excludeAppointmentId=0): array
    {
        Database::migrate();
        $category=normalize_driving_category($category);
        $stmt=Database::pdo()->prepare('SELECT contracted_lessons,used_lessons FROM student_lesson_packages WHERE student_id=? AND UPPER(TRIM(category))=? LIMIT 1');
        $stmt->execute([$studentId,$category]);
        $package=$stmt->fetch();
        if(!$package) return ['exists'=>false,'contracted'=>0,'used'=>0,'scheduled'=>0,'available_to_schedule'=>0];
        $sql="SELECT COALESCE(SUM(CASE WHEN COALESCE(a.lesson_units,0)>0 THEN a.lesson_units ELSE CAST(((strftime('%s',s.end_at)-strftime('%s',s.start_at))+2999)/3000 AS INTEGER) END),0)
            FROM appointments a INNER JOIN availability_slots s ON s.id=a.slot_id
            WHERE a.student_id=? AND a.status='booked' AND UPPER(TRIM(COALESCE(a.lesson_category,'')))=?";
        if(Database::driver()==='mysql'){
            $sql="SELECT COALESCE(SUM(CASE WHEN COALESCE(a.lesson_units,0)>0 THEN a.lesson_units ELSE CEIL(TIMESTAMPDIFF(MINUTE,s.start_at,s.end_at)/50) END),0)
                FROM appointments a INNER JOIN availability_slots s ON s.id=a.slot_id
                WHERE a.student_id=? AND a.status='booked' AND UPPER(TRIM(COALESCE(a.lesson_category,'')))=?";
        }
        $params=[$studentId,$category];
        if($excludeAppointmentId>0){ $sql.=' AND a.id<>?'; $params[]=$excludeAppointmentId; }
        $reserved=Database::pdo()->prepare($sql);$reserved->execute($params);
        $scheduled=max(0,(int)$reserved->fetchColumn());
        $contracted=max(0,(int)$package['contracted_lessons']);$used=max(0,(int)$package['used_lessons']);
        return ['exists'=>true,'contracted'=>$contracted,'used'=>$used,'scheduled'=>$scheduled,'available_to_schedule'=>max(0,$contracted-$used-$scheduled)];
    }

    public static function studentBookingSummary(int $studentId): array
    {
        $rows=[];$totalAvailable=0;
        foreach(StudentLessonPackage::forStudent($studentId) as $package){
            $balance=self::bookingBalanceForCategory($studentId,(string)$package['category']);
            $rows[]=['category'=>$package['category']]+$balance;
            $totalAvailable+=(int)$balance['available_to_schedule'];
        }
        $hasPackages = !empty($rows);
        return ['categories'=>$rows,'available_to_schedule'=>$totalAvailable,'has_packages'=>$hasPackages,'completed_or_scheduled'=>$hasPackages && $totalAvailable<=0];
    }

    private static function firstBookableCategory(int $studentId, int $units): string
    {
        foreach(StudentLessonPackage::forStudent($studentId) as $package){
            $balance=self::bookingBalanceForCategory($studentId,(string)$package['category']);
            if((int)$balance['available_to_schedule']>=$units) return (string)$package['category'];
        }
        return '';
    }

    public static function validateDailyPracticalLimit(int $studentId, string $date, string $category, int $newUnits, bool $override=false, int $excludeAppointmentId=0): array
    {
        if($override) return ['ok'=>true,'message'=>'Exceção diária autorizada pelo administrador.'];
        $category=normalize_driving_category($category);
        $sql="SELECT UPPER(TRIM(COALESCE(a.lesson_category,''))) AS category,
                COALESCE(SUM(CASE WHEN COALESCE(a.lesson_units,0)>0 THEN a.lesson_units ELSE 1 END),0) AS units
            FROM appointments a INNER JOIN availability_slots s ON s.id=a.slot_id
            WHERE a.student_id=? AND a.status IN ('booked','completed') AND DATE(s.start_at)=?";
        $params=[$studentId,$date];
        if($excludeAppointmentId>0){ $sql.=' AND a.id<>?'; $params[]=$excludeAppointmentId; }
        $sql.=" GROUP BY UPPER(TRIM(COALESCE(a.lesson_category,'')))";
        $stmt=Database::pdo()->prepare($sql);
        $stmt->execute($params);
        $by=[];foreach($stmt->fetchAll() as $row) $by[(string)$row['category']]=(int)$row['units'];
        $by[$category]=($by[$category]??0)+max(1,$newUnits);
        if(($by[$category]??0)>3){
            return ['ok'=>false,'message'=>'Limite diário atingido: são permitidas no máximo 3 aulas práticas da mesma categoria por aluno. Para uma exceção, use o botão “Permitir mais aulas no dia”.','needs_override'=>true];
        }
        $a=(int)($by['A']??0);$b=(int)($by['B']??0);
        if($a>0&&$b>0&&($a>2||$b>2||($a+$b)>4)){
            return ['ok'=>false,'message'=>'Quando houver categorias A e B no mesmo dia, o máximo é 4 aulas: até 2 da categoria A e 2 da categoria B. Use a autorização de exceção se necessário.','needs_override'=>true];
        }
        return ['ok'=>true,'message'=>'Limite diário disponível.'];
    }

    public static function professionalDailyUsedUnits(int $professionalId, string $date, int $excludeAppointmentId = 0): int
    {
        Database::migrate();
        $sql = "SELECT COALESCE(SUM(CASE WHEN COALESCE(a.lesson_units,0)>0 THEN a.lesson_units ELSE 1 END),0)
            FROM appointments a INNER JOIN availability_slots s ON s.id=a.slot_id
            WHERE a.professional_id=? AND a.status IN ('booked','completed') AND DATE(s.start_at)=?";
        $params = [$professionalId, $date];
        if ($excludeAppointmentId > 0) { $sql .= ' AND a.id<>?'; $params[] = $excludeAppointmentId; }
        $stmt = Database::pdo()->prepare($sql);
        $stmt->execute($params);
        return max(0, (int)$stmt->fetchColumn());
    }

    public static function validateProfessionalDailyCapacity(int $professionalId, string $date, int $newUnits = 1, int $excludeAppointmentId = 0): array
    {
        Database::migrate();
        $off = ProfessionalDayOff::isOff($professionalId, $date);
        if ($off) {
            $reason = trim((string)($off['note'] ?? ''));
            return ['ok' => false, 'message' => 'Este instrutor está de folga em ' . date('d/m/Y', strtotime($date)) . ($reason !== '' ? ': ' . $reason : '.') , 'is_off' => true];
        }
        $stmt = Database::pdo()->prepare('SELECT name, daily_lesson_limit FROM professionals WHERE id=? AND is_active=1 LIMIT 1');
        $stmt->execute([$professionalId]);
        $professional = $stmt->fetch();
        if (!$professional) return ['ok'=>false,'message'=>'Instrutor não encontrado ou inativo.'];
        $limit = max(0, (int)($professional['daily_lesson_limit'] ?? 0));
        $used = self::professionalDailyUsedUnits($professionalId, $date, $excludeAppointmentId);
        $newUnits = max(1, $newUnits);
        if ($limit > 0 && ($used + $newUnits) > $limit) {
            return ['ok'=>false,'message'=>'Carga diária do instrutor atingida. Limite: '.$limit.' aula(s); já ocupadas: '.$used.'.','limit'=>$limit,'used'=>$used,'remaining'=>max(0,$limit-$used)];
        }
        return ['ok'=>true,'message'=>'Instrutor disponível.','limit'=>$limit,'used'=>$used,'remaining'=>$limit>0?max(0,$limit-$used):null];
    }

    public static function professionalOptionsForDate(string $date, int $requiredUnits = 1): array
    {
        Database::migrate();
        if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) return [];
        $items=[];
        foreach (Professional::allActive() as $professional) {
            $capacity=self::validateProfessionalDailyCapacity((int)$professional['id'],$date,max(1,$requiredUnits));
            $items[]=[
                'id'=>(int)$professional['id'], 'name'=>(string)$professional['name'], 'type'=>(string)($professional['type']??''),
                'daily_lesson_limit'=>(int)($professional['daily_lesson_limit']??0), 'used'=>(int)($capacity['used']??self::professionalDailyUsedUnits((int)$professional['id'],$date)),
                'remaining'=>$capacity['remaining']??null, 'available'=>!empty($capacity['ok']), 'is_off'=>!empty($capacity['is_off']),
                'message'=>(string)($capacity['message']??'')
            ];
        }
        return $items;
    }

    public static function overdueConfirmationCount(int $professionalId): int
    {
        Database::migrate();
        $stmt=Database::pdo()->prepare("SELECT COUNT(*) FROM appointments a
            INNER JOIN availability_slots s ON s.id=a.slot_id
            WHERE a.professional_id=? AND a.status='booked' AND a.lesson_confirmed_at IS NULL
              AND s.end_at < ? AND a.absence_notified_at IS NULL AND a.student_no_show_at IS NULL
              AND a.professional_absence_notified_at IS NULL");
        $stmt->execute([$professionalId,date('Y-m-d H:i:s')]);
        return (int)$stmt->fetchColumn();
    }

    public static function updateLessonByAdmin(int $appointmentId, string $category, string $subject, string $process = ''): array
    {
        Database::migrate();
        $category = normalize_driving_category($category);
        $subject = trim($subject);
        $process = trim($process);
        if(!array_key_exists($process,license_process_options())) $process='';
        if ($category === '' || $subject === '' || $process==='') {
            return ['ok' => false, 'message' => 'Informe a categoria, o tipo e o conteúdo da aula.'];
        }
        $stmt = Database::pdo()->prepare("SELECT a.student_id, a.status, a.lesson_units, s.start_at, s.end_at FROM appointments a INNER JOIN availability_slots s ON s.id = a.slot_id WHERE a.id = ? LIMIT 1");
        $stmt->execute([$appointmentId]);
        $appointment = $stmt->fetch();
        if (!$appointment || ($appointment['status'] ?? '') !== 'booked') {
            return ['ok' => false, 'message' => 'Aula não encontrada ou já confirmada/cancelada.'];
        }
        $units = max(1, (int)($appointment['lesson_units'] ?: self::lessonUnitsFromRange($appointment['start_at'], $appointment['end_at'])));
        $balance = self::bookingBalanceForCategory((int)$appointment['student_id'], $category, $appointmentId);
        if (empty($balance['exists'])) {
            return ['ok' => false, 'message' => 'O aluno não possui pacote contratado na categoria ' . $category . '.'];
        }
        if ((int)$balance['available_to_schedule'] < $units) {
            return ['ok' => false, 'message' => 'Quantidade contratada já completada ou totalmente agendada na categoria ' . $category
                . '. Disponível para esta alteração: ' . max(0, (int)$balance['available_to_schedule']) . ' aula(s).'];
        }
        $update = Database::pdo()->prepare('UPDATE appointments SET lesson_category = ?, lesson_subject = ?, lesson_process = ?, lesson_units = ? WHERE id = ? AND status = \'booked\'');
        $update->execute([$category, $subject, $process, $units, $appointmentId]);
        return ['ok' => true, 'message' => 'Conteúdo e categoria da aula atualizados.'];
    }

    public static function confirmLessonByProfessional(int $professionalId, int $appointmentId, int $confirmedUnits = 0): array
    {
        Database::migrate();
        $pdo = Database::pdo();
        try {
            // No MySQL, FOR UPDATE trava as linhas do agendamento e do pacote.
            // O UPDATE do saldo também usa comparação otimista para impedir desconto duplicado.
            $pdo->beginTransaction();

            $appointmentSql = "SELECT a.*, s.start_at, s.end_at
                FROM appointments a
                INNER JOIN availability_slots s ON s.id = a.slot_id
                WHERE a.id = ? AND a.professional_id = ?
                LIMIT 1";
            if (Database::driver() === 'mysql') {
                $appointmentSql .= ' FOR UPDATE';
            }
            $stmt = $pdo->prepare($appointmentSql);
            $stmt->execute([$appointmentId, $professionalId]);
            $appointment = $stmt->fetch();

            if (!$appointment) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Aula não encontrada.'];
            }
            if (($appointment['status'] ?? '') === 'completed' || !empty($appointment['lesson_confirmed_at'])) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Esta aula já foi confirmada e descontada.'];
            }
            if (($appointment['status'] ?? '') !== 'booked') {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Aula cancelada não pode ser confirmada.'];
            }
            // A confirmação não aguarda mais um bloco adicional de 50 minutos.
            // Ela é liberada exatamente depois do término que foi salvo no agendamento,
            // inclusive para aulas cadastradas retroativamente muito tempo depois.
            if (!self::canConfirmAfterScheduledEnd($appointment['end_at'] ?? null)) {
                $pdo->rollBack();
                $endLabel = !empty($appointment['end_at'])
                    ? date('d/m/Y H:i', strtotime((string)$appointment['end_at']))
                    : 'horário não informado';
                return ['ok' => false, 'message' => 'A confirmação só fica disponível após o término agendado da aula (' . $endLabel . '). Não existe espera adicional de 50 minutos.'];
            }
            if (!empty($appointment['absence_notified_at']) || !empty($appointment['student_no_show_at']) || !empty($appointment['professional_absence_notified_at'])) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Aula com ocorrência de ausência não pode ser confirmada como realizada.'];
            }

            $category = normalize_driving_category($appointment['lesson_category'] ?? '');
            $lessonProcess = trim((string)($appointment['lesson_process'] ?? ''));
            if ($category === '' || trim((string)($appointment['lesson_subject'] ?? '')) === '' || !array_key_exists($lessonProcess, license_process_options())) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'O administrador precisa informar a categoria, o tipo e o conteúdo desta aula antes da confirmação.'];
            }

            $scheduledUnits = max(1, (int)($appointment['lesson_units'] ?: self::lessonUnitsFromRange($appointment['start_at'], $appointment['end_at'])));
            $maximumConfirmable = min(2, $scheduledUnits);
            $units = $confirmedUnits > 0 ? $confirmedUnits : $maximumConfirmable;
            if (!in_array($units, [1, 2], true) || $units > $maximumConfirmable) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Selecione corretamente se foi ministrada 1 aula ou 2 aulas.'];
            }
            $packageSql = 'SELECT id, COALESCE(contracted_lessons, 0) AS contracted_lessons, COALESCE(used_lessons, 0) AS used_lessons
                FROM student_lesson_packages
                WHERE student_id = ? AND UPPER(TRIM(category)) = ?
                ORDER BY id ASC
                LIMIT 1';
            if (Database::driver() === 'mysql') {
                $packageSql .= ' FOR UPDATE';
            }
            $packageStmt = $pdo->prepare($packageSql);
            $packageStmt->execute([(int)$appointment['student_id'], $category]);
            $package = $packageStmt->fetch();
            if (!$package) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Pacote da categoria ' . $category . ' não encontrado para o aluno.'];
            }

            $contracted = max(0, (int)$package['contracted_lessons']);
            $usedBefore = max(0, (int)$package['used_lessons']);
            $remaining = $contracted - $usedBefore;
            if ($remaining < $units) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Saldo insuficiente. Restam ' . max(0, $remaining) . ' aula(s) na categoria ' . $category . '.'];
            }

            $confirmedAt = date('Y-m-d H:i:s');

            // Primeiro reserva a confirmação do agendamento. A condição no WHERE
            // impede processamento duplicado, inclusive quando há dois cliques rápidos.
            $updateAppointment = $pdo->prepare("UPDATE appointments
                SET status = 'completed', active_slot_key = NULL, lesson_units = ?, lesson_confirmed_at = ?, lesson_confirmed_by = ?
                WHERE id = ? AND professional_id = ? AND status = 'booked' AND lesson_confirmed_at IS NULL");
            $updateAppointment->execute([$units, $confirmedAt, $professionalId, $appointmentId, $professionalId]);
            if ($updateAppointment->rowCount() <= 0) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Esta aula já foi processada. O saldo não foi descontado novamente.'];
            }

            // Grava o saldo final de forma absoluta. A linha do pacote já foi
            // validada e, no MySQL, está travada com FOR UPDATE. Evitamos depender
            // de rowCount() e de expressões aritméticas no WHERE, pois algumas
            // hospedagens/bancos legados retornavam zero alterações mesmo havendo
            // saldo, causando o falso erro exibido ao instrutor.
            $usedAfter = $usedBefore + $units;
            $updatePackage = $pdo->prepare('UPDATE student_lesson_packages
                SET used_lessons = ?, updated_at = ?
                WHERE id = ?');
            $updatePackage->execute([$usedAfter, $confirmedAt, (int)$package['id']]);

            // Confirma pelo valor realmente persistido, e não pelo rowCount do PDO.
            $verifyPackage = $pdo->prepare('SELECT COALESCE(contracted_lessons, 0) AS contracted_lessons,
                    COALESCE(used_lessons, 0) AS used_lessons
                FROM student_lesson_packages
                WHERE id = ?
                LIMIT 1');
            $verifyPackage->execute([(int)$package['id']]);
            $persistedPackage = $verifyPackage->fetch();
            $persistedContracted = (int)($persistedPackage['contracted_lessons'] ?? -1);
            $persistedUsed = (int)($persistedPackage['used_lessons'] ?? -1);

            if (!$persistedPackage || $persistedUsed !== $usedAfter || $persistedUsed > $persistedContracted) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Não foi possível atualizar o contador da categoria ' . $category
                    . '. Contratadas: ' . $contracted . ', realizadas antes: ' . $usedBefore
                    . ', tentativa de desconto: ' . $units . '. Atualize a página e tente novamente.'];
            }

            $pdo->commit();
            return ['ok' => true, 'message' => 'Aula confirmada e salva. Foram descontadas ' . $units . ' aula(s) da categoria ' . $category . '.'];
        } catch (Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            return ['ok' => false, 'message' => 'Não foi possível confirmar a aula: ' . $e->getMessage()];
        }
    }

    public static function reopenCompletionByAdmin(int $appointmentId): array
    {
        Database::migrate();
        $pdo = Database::pdo();
        try {
            $pdo->beginTransaction();
            $stmt = $pdo->prepare("SELECT * FROM appointments WHERE id = ? AND status = 'completed' AND lesson_confirmed_at IS NOT NULL LIMIT 1");
            $stmt->execute([$appointmentId]);
            $appointment = $stmt->fetch();
            if (!$appointment) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Aula confirmada não encontrada.'];
            }
            $units = max(1, (int)($appointment['lesson_units'] ?? 1));
            $category = normalize_driving_category($appointment['lesson_category'] ?? '');
            if ($category === '') {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'A categoria da aula não está definida; o saldo não pode ser devolvido automaticamente.'];
            }
            $package = $pdo->prepare('UPDATE student_lesson_packages SET used_lessons = CASE WHEN used_lessons >= ? THEN used_lessons - ? ELSE 0 END, updated_at = ? WHERE student_id = ? AND category = ?');
            $package->execute([$units, $units, date('Y-m-d H:i:s'), (int)$appointment['student_id'], $category]);
            if ($package->rowCount() <= 0) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'O pacote de aulas do aluno não foi encontrado.'];
            }
            $update = $pdo->prepare("UPDATE appointments SET status = 'booked', active_slot_key = slot_id, lesson_confirmed_at = NULL, lesson_confirmed_by = NULL WHERE id = ? AND status = 'completed'");
            $update->execute([$appointmentId]);
            if ($update->rowCount() <= 0) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'A confirmação já foi alterada por outro processo.'];
            }
            $pdo->commit();
            return ['ok' => true, 'message' => 'Confirmação desfeita e saldo devolvido ao aluno.'];
        } catch (Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            return ['ok' => false, 'message' => 'Não foi possível desfazer a confirmação.'];
        }
    }


    public static function deleteHistoricalByAdmin(int $appointmentId): array
    {
        Database::migrate(); $pdo=Database::pdo();
        try{$pdo->beginTransaction(); $q=$pdo->prepare("SELECT * FROM appointments WHERE id=? AND status IN ('completed','canceled') LIMIT 1");$q->execute([$appointmentId]);$a=$q->fetch(); if(!$a){$pdo->rollBack();return ['ok'=>false,'message'=>'Somente aulas concluídas ou canceladas podem ser apagadas.'];}
            if(($a['status']??'')==='completed'){ $cat=normalize_driving_category($a['lesson_category']??'');$units=max(1,(int)($a['lesson_units']??1));$u=$pdo->prepare('UPDATE student_lesson_packages SET used_lessons=CASE WHEN used_lessons>=? THEN used_lessons-? ELSE 0 END, updated_at=? WHERE student_id=? AND category=?');$u->execute([$units,$units,date('Y-m-d H:i:s'),(int)$a['student_id'],$cat]); }
            $d=$pdo->prepare("DELETE FROM appointments WHERE id=? AND status IN ('completed','canceled')");$d->execute([$appointmentId]);$pdo->commit();return ['ok'=>true,'message'=>'Registro histórico apagado.'];
        }catch(Throwable $e){if($pdo->inTransaction())$pdo->rollBack();return ['ok'=>false,'message'=>'Não foi possível apagar o registro histórico.'];}
    }

    public static function deleteHistoricalForStudentByAdmin(int $studentId): array
    {
        Database::migrate(); $pdo=Database::pdo();
        try{$pdo->beginTransaction();$q=$pdo->prepare("SELECT * FROM appointments WHERE student_id=? AND status IN ('completed','canceled')");$q->execute([$studentId]);$rows=$q->fetchAll(); if(!$rows){$pdo->rollBack();return ['ok'=>false,'message'=>'Não há registros concluídos/cancelados para apagar.'];}
            foreach($rows as $a){if(($a['status']??'')==='completed'){ $cat=normalize_driving_category($a['lesson_category']??'');$units=max(1,(int)($a['lesson_units']??1));$u=$pdo->prepare('UPDATE student_lesson_packages SET used_lessons=CASE WHEN used_lessons>=? THEN used_lessons-? ELSE 0 END, updated_at=? WHERE student_id=? AND category=?');$u->execute([$units,$units,date('Y-m-d H:i:s'),$studentId,$cat]);}}
            $d=$pdo->prepare("DELETE FROM appointments WHERE student_id=? AND status IN ('completed','canceled')");$d->execute([$studentId]);$pdo->commit();return ['ok'=>true,'message'=>'Todo o histórico concluído/cancelado do aluno foi apagado.'];
        }catch(Throwable $e){if($pdo->inTransaction())$pdo->rollBack();return ['ok'=>false,'message'=>'Não foi possível apagar o histórico.'];}
    }

    public static function updateColorByAdmin(int $appointmentId, string $lessonColor): array
    {
        Database::migrate();
        $lessonColor = normalize_lesson_color($lessonColor);
        $stmt = Database::pdo()->prepare("UPDATE appointments SET lesson_color = ? WHERE id = ? AND status = 'booked'");
        $stmt->execute([$lessonColor, $appointmentId]);
        if ($stmt->rowCount() <= 0) {
            return ['ok' => false, 'message' => 'Aula não encontrada ou já finalizada/cancelada.'];
        }
        return ['ok' => true, 'message' => 'Status da aula atualizada.'];
    }

    public static function unlockByAdmin(int $appointmentId): array
    {
        Database::migrate();
        $stmt = Database::pdo()->prepare("SELECT * FROM appointments WHERE id = ? AND status = 'booked' LIMIT 1");
        $stmt->execute([$appointmentId]);
        $appointment = $stmt->fetch();
        if (!$appointment) {
            return ['ok' => false, 'message' => 'Aula bloqueada/agendada não encontrada.'];
        }
        $stmt = Database::pdo()->prepare("UPDATE appointments SET status = 'canceled', active_slot_key = NULL, canceled_at = ?, cancel_reason = ? WHERE id = ? AND status = 'booked'");
        $stmt->execute([date('Y-m-d H:i:s'), 'Desbloqueado pelo administrador', $appointmentId]);
        return ['ok' => true, 'message' => 'Aula desbloqueada. Agora o horário ficou livre e pode ser removido na disponibilidade.'];
    }


}
