One Hat Cyber Team

Edit File: student.php

<?php

declare(strict_types=1);

require_once ROOT_PATH . '/config/database.php';

function has_student_course_geo_assignments_table(): bool
{
    static $checked = false;
    static $exists = false;

if ($checked) {
        return $exists;
    }

try {
        db_query('SELECT 1 FROM course_geographic_departments LIMIT 1');
        $exists = true;
    } catch (Throwable $e) {
        $exists = false;
    }

$checked = true;

return $exists;
}

/**
 * Fetch all non-archived courses. All courses are visible to all students.
 *
 * @param string $search Optional partial match on course title.
 * @return array<int, array<string, mixed>>
 */
function get_student_courses(string $search = ''): array
{
    $hasBridgeTable = has_student_course_geo_assignments_table();

$conditions = ["c.status <> 'archived'"];
    $params     = [];

if ($search !== '') {
        $conditions[] = $hasBridgeTable
            ? '(c.title LIKE :search OR gd.name LIKE :search OR gdm.name LIKE :search)'
            : '(c.title LIKE :search OR gd.name LIKE :search)';
        $params[':search'] = '%' . $search . '%';
    }

$where = implode(' AND ', $conditions);

$sql = $hasBridgeTable ? "
        SELECT
            c.id                AS course_id,
            c.title,
            c.slug,
            c.short_description,
            c.description,
            c.start_date,
            c.end_date,
            c.status            AS course_status,
            gd.id               AS geographic_department_id,
            gd.name             AS geographic_department_name,
            COALESCE(
                GROUP_CONCAT(DISTINCT cgd.geographic_department_id ORDER BY cgd.geographic_department_id SEPARATOR ','),
                CAST(gd.id AS CHAR)
            )                   AS geographic_department_ids_csv,
            COALESCE(
                GROUP_CONCAT(DISTINCT gdm.name ORDER BY gdm.name SEPARATOR ', '),
                gd.name
            )                   AS geographic_department_names,
            COALESCE(
                GROUP_CONCAT(DISTINCT gdm.name ORDER BY gdm.name SEPARATOR '||'),
                gd.name
            )                   AS geographic_department_names_pipe,
            COALESCE(
                GROUP_CONCAT(DISTINCT gdm.name ORDER BY gdm.name SEPARATOR ', '),
                gd.name
            )                   AS department_name
        FROM  courses c
        LEFT JOIN geographic_departments gd ON gd.id = c.geographic_department_id
        LEFT JOIN course_geographic_departments cgd ON cgd.course_id = c.id
        LEFT JOIN geographic_departments gdm ON gdm.id = cgd.geographic_department_id
        WHERE {$where}
        GROUP BY c.id
        ORDER BY c.id DESC
    " : "
        SELECT
            c.id                AS course_id,
            c.title,
            c.slug,
            c.short_description,
            c.description,
            c.start_date,
            c.end_date,
            c.status            AS course_status,
            gd.id               AS geographic_department_id,
            gd.name             AS geographic_department_name,
            CAST(gd.id AS CHAR) AS geographic_department_ids_csv,
            gd.name             AS geographic_department_names,
            gd.name             AS geographic_department_names_pipe,
            gd.name             AS department_name
        FROM  courses c
        LEFT JOIN geographic_departments gd ON gd.id = c.geographic_department_id
        WHERE {$where}
        ORDER BY c.id DESC
    ";

return db_fetch_all($sql, $params);
}

/**
 * Return Bootstrap badge CSS class and human-readable label for an enrollment status.
 *
 * @return array{class: string, label: string}
 */
function enrollment_badge_info(string $status): array
{
    switch ($status) {
        case 'enrolled':
            return ['class' => 'text-bg-success', 'label' => 'En curso'];
        case 'completed':
            return ['class' => 'text-bg-secondary', 'label' => 'Completado'];
        case 'dropped':
            return ['class' => 'text-bg-warning', 'label' => 'Abandonado'];
        default:
            return ['class' => 'text-bg-light', 'label' => ucfirst($status)];
    }
}

/**
 * Return a safe, truncated excerpt from short_description or description.
 * Avoids mbstring dependency per project conventions.
 */
function course_excerpt(?string $short, ?string $long, int $limit = 110): string
{
    $text = ($short !== null && $short !== '') ? $short : ((string) ($long ?? ''));

if ($text === '') {
        return '';
    }

if (strlen($text) <= $limit) {
        return $text;
    }

return rtrim(substr($text, 0, $limit)) . '…';
}

/**
 * Fetch course detail by id. All non-archived courses are accessible to all students.
 *
 * @param int $courseId Course id from GET request.
 * @return array<string, mixed>|null Null if course does not exist or is archived.
 */
function get_student_course_detail(int $courseId): ?array
{
    $sql = "
        SELECT
            c.id,
            c.title,
            c.slug,
            c.short_description,
            c.description,
            c.status              AS course_status,
            c.start_date,
            c.end_date,
            gd.id                 AS geographic_department_id,
            gd.name               AS geographic_department_name,
            gd.name               AS department_name
        FROM  courses c
        LEFT JOIN geographic_departments gd ON gd.id = c.geographic_department_id
        WHERE c.id = :course_id
          AND c.status <> 'archived'
        LIMIT 1
    ";

return db_fetch_one($sql, [':course_id' => $courseId]);
}

/**
 * Fetch all published classes for a course, with file counts by type.
 *
 * Returns classes ordered by class_order ASC, plus computed fields:
 * - file_count_study_material
 * - file_count_exam
 * - file_count_extra
 * - file_count_total
 *
 * @param int $courseId Course id.
 * @return array<int, array<string, mixed>>
 */
function get_student_course_classes(int $courseId): array
{
    $sql = "
        SELECT
            cl.id,
            cl.course_id,
            cl.title,
            cl.description,
            cl.class_order,
            cl.webex_url,
            cl.class_view_url,
            cl.scheduled_at,
            cl.duration_minutes,
            cl.status,
            COALESCE(SUM(CASE WHEN cf.file_type = 'study_material' THEN 1 ELSE 0 END), 0) AS file_count_study_material,
            COALESCE(SUM(CASE WHEN cf.file_type = 'exam' THEN 1 ELSE 0 END), 0)           AS file_count_exam,
            COALESCE(SUM(CASE WHEN cf.file_type = 'extra' THEN 1 ELSE 0 END), 0)          AS file_count_extra,
            COALESCE(COUNT(cf.id), 0)                                                     AS file_count_total
        FROM  classes cl
        LEFT JOIN class_files cf ON cf.class_id = cl.id AND cf.status = 'active'
        WHERE cl.course_id = :course_id
          AND cl.status = 'published'
        GROUP BY cl.id
        ORDER BY cl.class_order ASC
    ";

return db_fetch_all($sql, [':course_id' => $courseId]);
}

/**
 * Format a class status (draft, published, hidden) to a human-readable label and badge class.
 *
 * @return array{label: string, class: string}
 */
function class_status_badge(string $status): array
{
    switch ($status) {
        case 'published':
            return ['label' => 'Disponible', 'class' => 'text-bg-success'];
        case 'draft':
            return ['label' => 'Borrador', 'class' => 'text-bg-secondary'];
        case 'hidden':
            return ['label' => 'Oculta', 'class' => 'text-bg-danger'];
        default:
            return ['label' => ucfirst($status), 'class' => 'text-bg-light'];
    }
}

/**
 * Fetch class detail by id. All published classes are accessible to all students.
 *
 * Validates:
 * - Class exists and is published
 * - Course is not archived
 *
 * @param int $classId Class id from GET request.
 * @return array<string, mixed>|null Null if class does not exist, is not published, or course is archived.
 */
function get_student_class_detail(int $classId): ?array
{
    $sql = "
        SELECT
            cl.id,
            cl.course_id,
            cl.title,
            cl.description,
            cl.class_order,
            cl.webex_url,
            cl.class_view_url,
            cl.scheduled_at,
            cl.duration_minutes,
            cl.status              AS class_status,
            c.title                AS course_title,
            c.slug                 AS course_slug,
            gd.id                  AS geographic_department_id,
            gd.name                AS geographic_department_name,
            gd.name                AS department_name
        FROM  classes cl
        INNER JOIN courses c ON c.id = cl.course_id
        LEFT JOIN geographic_departments gd ON gd.id = c.geographic_department_id
        WHERE cl.id = :class_id
          AND cl.status = 'published'
          AND c.status <> 'archived'
        LIMIT 1
    ";

return db_fetch_one($sql, [':class_id' => $classId]);
}

/**
 * Fetch all active files for a class, grouped by type.
 *
 * Returns keyed array with 'study_material', 'exam', 'extra' keys,
 * each containing an array of file records ordered by id DESC (newest first).
 *
 * @param int $classId Class id.
 * @return array{study_material: array, exam: array, extra: array}
 */
function get_student_class_files(int $classId): array
{
    $sql = "
        SELECT
            id,
            class_id,
            file_type,
            original_name,
            stored_name,
            file_extension,
            file_size_bytes,
            mime_type,
            status
        FROM class_files
        WHERE class_id = :class_id
          AND status = 'active'
        ORDER BY file_type ASC, id DESC
    ";

$allFiles = db_fetch_all($sql, [':class_id' => $classId]);

$grouped = [
        'study_material' => [],
        'exam'           => [],
        'extra'          => [],
    ];

foreach ($allFiles as $file) {
        $fileType = (string) ($file['file_type'] ?? '');
        if (isset($grouped[$fileType])) {
            $grouped[$fileType][] = $file;
        }
    }

return $grouped;
}

/**
 * Fetch previous and next published classes for navigation.
 *
 * Returns array with 'prev' and 'next' keys, each containing a class record or null.
 * Validates: both prev and next are published, belong to the same course, within correct order.
 *
 * @param int $courseId Course id (from current class).
 * @param int $currentClassOrder Current class's class_order value.
 * @return array{prev: array<string, mixed>|null, next: array<string, mixed>|null}
 */
function get_student_class_navigation(int $courseId, int $currentClassOrder): array
{
    $sqlPrev = "
        SELECT id, title, class_order, course_id
        FROM classes
        WHERE course_id = :course_id
          AND class_order < :current_order
          AND status = 'published'
        ORDER BY class_order DESC
        LIMIT 1
    ";

$sqlNext = "
        SELECT id, title, class_order, course_id
        FROM classes
        WHERE course_id = :course_id
          AND class_order > :current_order
          AND status = 'published'
        ORDER BY class_order ASC
        LIMIT 1
    ";

$params = [
        ':course_id'     => $courseId,
        ':current_order' => $currentClassOrder,
    ];

return [
        'prev' => db_fetch_one($sqlPrev, $params),
        'next' => db_fetch_one($sqlNext, $params),
    ];
}

/**
 * Get readable file extension label and Font Awesome icon class.
 *
 * @return array{label: string, icon: string}
 */
function file_extension_info(string $ext): array
{
    $ext = strtolower(trim($ext, '.'));

switch ($ext) {
        case 'pdf':
            return ['label' => 'PDF', 'icon' => 'fa-solid fa-file-pdf'];
        case 'doc':
        case 'docx':
            return ['label' => 'Word', 'icon' => 'fa-solid fa-file-word'];
        case 'xls':
        case 'xlsx':
            return ['label' => 'Excel', 'icon' => 'fa-solid fa-file-excel'];
        case 'ppt':
        case 'pptx':
            return ['label' => 'Presentación', 'icon' => 'fa-solid fa-file-powerpoint'];
        case 'zip':
            return ['label' => 'ZIP', 'icon' => 'fa-solid fa-file-archive'];
        case 'mp4':
        case 'webm':
        case 'mov':
            return ['label' => 'Video', 'icon' => 'fa-solid fa-file-video'];
        case 'mp3':
        case 'wav':
            return ['label' => 'Audio', 'icon' => 'fa-solid fa-file-audio'];
        case 'jpg':
        case 'jpeg':
        case 'png':
        case 'gif':
            return ['label' => 'Imagen', 'icon' => 'fa-solid fa-file-image'];
        case 'txt':
            return ['label' => 'Texto', 'icon' => 'fa-solid fa-file-lines'];
        default:
            return ['label' => strtoupper($ext), 'icon' => 'fa-solid fa-file'];
    }
}

/**
 * Format file size in bytes to human-readable format (avoiding mbstring dependency).
 *
 * @param int $bytes File size in bytes.
 * @return string Human-readable file size (e.g., "2.5 MB", "512 KB").
 */
function format_file_size(int $bytes): string
{
    if ($bytes <= 0) {
        return '0 B';
    }

$units = ['B', 'KB', 'MB', 'GB'];
    $bytes = (float) $bytes;
    $unitIndex = 0;

while ($bytes >= 1024 && $unitIndex < count($units) - 1) {
        $bytes /= 1024;
        $unitIndex++;
    }

return ($unitIndex === 0 ? (int) $bytes : round($bytes, 1)) . ' ' . $units[$unitIndex];
}

/**
 * Convert common video URLs to embeddable iframe URLs.
 *
 * Supported:
 * - YouTube watch/shorts/share links → converts to embed format
 * - Webex recordings → returns empty (not embeddable, fallback used)
 * - Already embeddable links are returned unchanged
 *
 * Returns empty string when URL cannot be parsed or is not embeddable.
 */
function get_embeddable_video_url(string $url): string
{
    $url = trim($url);
    if ($url === '') {
        return '';
    }

$parts = parse_url($url);
    if (!is_array($parts) || empty($parts['host'])) {
        return '';
    }

$host = strtolower((string) $parts['host']);
    $path = (string) ($parts['path'] ?? '');

// YouTube: convert to embed format
    if (str_contains($host, 'youtu.be')) {
        $videoId = trim($path, '/');
        return $videoId !== '' ? 'https://www.youtube.com/embed/' . rawurlencode($videoId) : '';
    }

if (str_contains($host, 'youtube.com')) {
        if (str_starts_with($path, '/embed/')) {
            return $url;
        }

if ($path === '/watch' && !empty($parts['query'])) {
            parse_str((string) $parts['query'], $query);
            $videoId = (string) ($query['v'] ?? '');
            if ($videoId !== '') {
                return 'https://www.youtube.com/embed/' . rawurlencode($videoId);
            }
        }

if (str_starts_with($path, '/shorts/')) {
            $videoId = trim(substr($path, strlen('/shorts/')), '/');
            return $videoId !== '' ? 'https://www.youtube.com/embed/' . rawurlencode($videoId) : '';
        }
    }

// Webex: not embeddable (X-Frame-Options blocking), return empty for fallback
    if (str_contains($host, 'webex.com')) {
        return '';
    }

return $url;
}