<?php
class StudentLessonPackage
{
    public static function forStudent(int $studentId): array
    {
        Database::migrate();
        $stmt = Database::pdo()->prepare("SELECT category, contracted_lessons, used_lessons,
                CASE WHEN contracted_lessons - used_lessons < 0 THEN 0 ELSE contracted_lessons - used_lessons END AS remaining_lessons,
                updated_at
            FROM student_lesson_packages
            WHERE student_id = ?
            ORDER BY category");
        $stmt->execute([$studentId]);
        return $stmt->fetchAll();
    }

    public static function mapForStudent(int $studentId): array
    {
        $map = [];
        foreach (self::forStudent($studentId) as $row) {
            $map[$row['category']] = $row;
        }
        return $map;
    }

    public static function mapForStudents(array $studentIds): array
    {
        Database::migrate();
        $studentIds = array_values(array_unique(array_filter(array_map('intval', $studentIds))));
        if (!$studentIds) {
            return [];
        }
        $placeholders = implode(',', array_fill(0, count($studentIds), '?'));
        $stmt = Database::pdo()->prepare("SELECT student_id, category, contracted_lessons, used_lessons,
                CASE WHEN contracted_lessons - used_lessons < 0 THEN 0 ELSE contracted_lessons - used_lessons END AS remaining_lessons
            FROM student_lesson_packages WHERE student_id IN ({$placeholders}) ORDER BY student_id, category");
        $stmt->execute($studentIds);
        $map = [];
        foreach ($stmt->fetchAll() as $row) {
            $map[(int)$row['student_id']][$row['category']] = $row;
        }
        return $map;
    }

    public static function saveProcessAndPackages(int $studentId, array $data): array
    {
        Database::migrate();
        $process = trim((string)($data['license_process'] ?? ''));
        if (!array_key_exists($process, license_process_options())) {
            return ['ok' => false, 'message' => 'Selecione o tipo de habilitação do aluno.'];
        }

        $packages = [];
        $licenseCategory = '';
        if ($process === 'first_license') {
            $qtyA = max(0, (int)($data['lessons_a'] ?? 0));
            $qtyB = max(0, (int)($data['lessons_b'] ?? 0));
            if ($qtyA <= 0 && $qtyB <= 0) {
                return ['ok' => false, 'message' => 'Na primeira habilitação, informe a quantidade contratada para A e/ou B.'];
            }
            $packages = ['A' => $qtyA, 'B' => $qtyB];
            $licenseCategory = $qtyA > 0 && $qtyB > 0 ? 'AB' : ($qtyA > 0 ? 'A' : 'B');
        } elseif ($process === 'addition') {
            $category = normalize_driving_category($data['addition_category'] ?? '');
            if (!in_array($category, ['A', 'B'], true)) {
                return ['ok' => false, 'message' => 'Na adição, selecione a categoria A ou B.'];
            }
            $qty = max(0, (int)($data['addition_lessons'] ?? 0));
            if ($qty <= 0) {
                return ['ok' => false, 'message' => 'Informe a quantidade de aulas contratadas para a adição.'];
            }
            $packages = [$category => $qty];
            $licenseCategory = $category;
        } else {
            $category = normalize_driving_category($data['classification_category'] ?? '');
            if (!in_array($category, ['C', 'D', 'E'], true)) {
                return ['ok' => false, 'message' => 'Na classificação, selecione a categoria C, D ou E.'];
            }
            $qty = max(0, (int)($data['classification_lessons'] ?? 0));
            if ($qty <= 0) {
                return ['ok' => false, 'message' => 'Informe a quantidade de aulas contratadas para a classificação.'];
            }
            $packages = [$category => $qty];
            $licenseCategory = $category;
        }

        $pdo = Database::pdo();
        try {
            $pdo->beginTransaction();
            $studentStmt = $pdo->prepare("SELECT id FROM users WHERE id = ? AND role = 'student' AND deleted_at IS NULL LIMIT 1");
            $studentStmt->execute([$studentId]);
            if (!$studentStmt->fetch()) {
                $pdo->rollBack();
                return ['ok' => false, 'message' => 'Aluno não encontrado.'];
            }

            $updateUser = $pdo->prepare('UPDATE users SET license_process = ?, license_category = ?, category = ? WHERE id = ?');
            $updateUser->execute([$process, $licenseCategory, $licenseCategory, $studentId]);

            $allowedCategories = array_keys($packages);
            if ($allowedCategories) {
                $marks = implode(',', array_fill(0, count($allowedCategories), '?'));
                // Preserva o histórico já realizado, mas zera o saldo de categorias que
                // não pertencem mais ao processo atual do aluno.
                $closeOld = $pdo->prepare("UPDATE student_lesson_packages SET contracted_lessons = used_lessons, updated_at = ? WHERE student_id = ? AND category NOT IN ({$marks})");
                $closeOld->execute(array_merge([date('Y-m-d H:i:s'), $studentId], $allowedCategories));
                $delete = $pdo->prepare("DELETE FROM student_lesson_packages WHERE student_id = ? AND category NOT IN ({$marks}) AND used_lessons = 0");
                $delete->execute(array_merge([$studentId], $allowedCategories));
            }

            foreach ($packages as $category => $contracted) {
                $existingStmt = $pdo->prepare('SELECT id, used_lessons FROM student_lesson_packages WHERE student_id = ? AND category = ? LIMIT 1');
                $existingStmt->execute([$studentId, $category]);
                $existing = $existingStmt->fetch();
                $used = (int)($existing['used_lessons'] ?? 0);
                if ($contracted < $used) {
                    $pdo->rollBack();
                    return ['ok' => false, 'message' => "A categoria {$category} já possui {$used} aula(s) confirmada(s). O contratado não pode ser menor que o realizado."];
                }
                if ($existing) {
                    $stmt = $pdo->prepare('UPDATE student_lesson_packages SET contracted_lessons = ?, updated_at = ? WHERE id = ?');
                    $stmt->execute([$contracted, date('Y-m-d H:i:s'), (int)$existing['id']]);
                } else {
                    $stmt = $pdo->prepare('INSERT INTO student_lesson_packages (student_id, category, contracted_lessons, used_lessons, updated_at) VALUES (?, ?, ?, 0, ?)');
                    $stmt->execute([$studentId, $category, $contracted, date('Y-m-d H:i:s')]);
                }
            }

            $pdo->commit();
            return ['ok' => true, 'message' => 'Tipo de habilitação e quantidade de aulas atualizados.'];
        } catch (Throwable $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            return ['ok' => false, 'message' => 'Não foi possível salvar o contrato de aulas: ' . $e->getMessage()];
        }
    }

    public static function firstAvailableCategory(int $studentId): string
    {
        Database::migrate();
        $stmt = Database::pdo()->prepare("SELECT category FROM student_lesson_packages WHERE student_id = ? AND contracted_lessons > used_lessons ORDER BY category LIMIT 1");
        $stmt->execute([$studentId]);
        return (string)($stmt->fetchColumn() ?: '');
    }
}
