One Hat Cyber Team

View File Name : admin_courses.php

<?php

declare(strict_types=1);

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

function has_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;
}

/**
 * @param array<int, int|string> $rawIds
 * @return array<int, int>
 */
function normalize_geo_department_ids(array $rawIds): array
{
    $normalized = [];

    foreach ($rawIds as $rawId) {
        $id = (int) $rawId;
        if ($id > 0) {
            $normalized[] = $id;
        }
    }

    $normalized = array_values(array_unique($normalized));
    sort($normalized);

    return $normalized;
}

/**
 * @param array<int, int> $geoDepartmentIds
 */
function sync_course_geographic_departments(int $courseId, array $geoDepartmentIds): void
{
    if (!has_course_geo_assignments_table()) {
        return;
    }

    db_execute('DELETE FROM course_geographic_departments WHERE course_id = :course_id', [
        ':course_id' => $courseId,
    ]);

    foreach ($geoDepartmentIds as $geoDepartmentId) {
        db_execute(
            'INSERT INTO course_geographic_departments (course_id, geographic_department_id) VALUES (:course_id, :geo_department_id)',
            [
                ':course_id' => $courseId,
                ':geo_department_id' => $geoDepartmentId,
            ]
        );
    }
}

/**
 * @return array<int, array<string, mixed>>
 */
function get_admin_courses(?string $search = null, ?string $status = null): array
{
    $hasBridgeTable = has_course_geo_assignments_table();

    $conditions = ['1 = 1'];
    $params = [];

    if ($search !== null && trim($search) !== '') {
        $searchCondition = $hasBridgeTable
            ? '(c.title LIKE :search OR gd.name LIKE :search OR gdm.name LIKE :search)'
            : '(c.title LIKE :search OR gd.name LIKE :search)';

        $conditions[] = $searchCondition;
        $params[':search'] = '%' . trim($search) . '%';
    }

    if ($status !== null && in_array($status, ['draft', 'published', 'archived'], true)) {
        $conditions[] = 'c.status = :status';
        $params[':status'] = $status;
    }

    $sql = $hasBridgeTable ? "
        SELECT
            c.id,
            c.title,
            c.description,
            c.geographic_department_id,
            GROUP_CONCAT(DISTINCT cgd.geographic_department_id ORDER BY cgd.geographic_department_id SEPARATOR ',') AS geographic_department_ids_csv,
            c.start_date,
            c.end_date,
            c.status,
            c.created_at,
            gd.name AS geographic_department_name,
            COALESCE(
                GROUP_CONCAT(DISTINCT gdm.name ORDER BY gdm.name SEPARATOR ', '),
                gd.name
            ) AS geographic_department_names,
            COUNT(DISTINCT cl.id) AS class_count
        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
        LEFT JOIN classes cl ON cl.course_id = c.id
        WHERE " . implode(' AND ', $conditions) . "
        GROUP BY c.id
        ORDER BY c.title ASC
    " : "
        SELECT
            c.id,
            c.title,
            c.description,
            c.geographic_department_id,
            c.start_date,
            c.end_date,
            c.status,
            c.created_at,
            gd.name AS geographic_department_name,
            gd.name AS geographic_department_names,
            COUNT(DISTINCT cl.id) AS class_count
        FROM courses c
        LEFT JOIN geographic_departments gd ON gd.id = c.geographic_department_id
        LEFT JOIN classes cl ON cl.course_id = c.id
        WHERE " . implode(' AND ', $conditions) . "
        GROUP BY c.id
        ORDER BY c.title ASC
    ";

    return db_fetch_all($sql, $params);
}

/**
 * @return array<string, mixed>|null
 */
function get_admin_course_by_id(int $id): ?array
{
    $hasBridgeTable = has_course_geo_assignments_table();

    $sql = $hasBridgeTable ? "
        SELECT
            c.id,
            c.title,
            c.description,
            c.geographic_department_id,
            GROUP_CONCAT(DISTINCT cgd.geographic_department_id ORDER BY cgd.geographic_department_id SEPARATOR ',') AS geographic_department_ids_csv,
            c.start_date,
            c.end_date,
            c.status,
            c.created_at,
            c.updated_at,
            gd.name AS geographic_department_name,
            COALESCE(
                GROUP_CONCAT(DISTINCT gdm.name ORDER BY gdm.name SEPARATOR ', '),
                gd.name
            ) AS geographic_department_names,
            COUNT(DISTINCT cl.id) AS class_count
        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
        LEFT JOIN classes cl ON cl.course_id = c.id
        WHERE c.id = :id
        GROUP BY c.id
        LIMIT 1
    " : "
        SELECT
            c.id,
            c.title,
            c.description,
            c.geographic_department_id,
            c.start_date,
            c.end_date,
            c.status,
            c.created_at,
            c.updated_at,
            gd.name AS geographic_department_name,
            gd.name AS geographic_department_names,
            COUNT(DISTINCT cl.id) AS class_count
        FROM courses c
        LEFT JOIN geographic_departments gd ON gd.id = c.geographic_department_id
        LEFT JOIN classes cl ON cl.course_id = c.id
        WHERE c.id = :id
        GROUP BY c.id
        LIMIT 1
    ";

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

function admin_course_exists(int $courseId): bool
{
    $sql = 'SELECT id FROM courses WHERE id = :id LIMIT 1';
    return db_fetch_one($sql, [':id' => $courseId]) !== null;
}

function course_slug_exists(string $slug, int $excludeId = 0): bool
{
    if ($excludeId > 0) {
        $sql = '
            SELECT id
            FROM courses
            WHERE slug = :slug
              AND id <> :exclude_id
            LIMIT 1
        ';

        return db_fetch_one($sql, [
            ':slug' => $slug,
            ':exclude_id' => $excludeId,
        ]) !== null;
    }

    $sql = '
        SELECT id
        FROM courses
        WHERE slug = :slug
        LIMIT 1
    ';

    return db_fetch_one($sql, [
        ':slug' => $slug,
    ]) !== null;
}

function generate_slug(string $title): string
{
    $slug = strtolower(trim($title));
    $slug = preg_replace('/[^a-z0-9]+/', '-', $slug) ?? '';
    $slug = trim($slug, '-');
    return $slug;
}

/**
 * Opciones de departamentos geográficos de Catamarca (tabla geographic_departments).
 *
 * @return array<int, array<string, mixed>>
 */
function get_geo_department_options(): array
{
    $sql = "
        SELECT id, name
        FROM geographic_departments
        WHERE status = 'active'
        ORDER BY name ASC
    ";

    return db_fetch_all($sql);
}

function admin_geo_department_exists(int $id): bool
{
    $sql = 'SELECT id FROM geographic_departments WHERE id = :id LIMIT 1';
    return db_fetch_one($sql, [':id' => $id]) !== null;
}

function generate_unique_course_slug(string $title, int $excludeId = 0): string
{
    $baseSlug = generate_slug($title);
    if ($baseSlug === '') {
        $baseSlug = 'curso';
    }

    $slug = $baseSlug;
    $suffix = 2;

    while (course_slug_exists($slug, $excludeId)) {
        $slug = $baseSlug . '-' . $suffix;
        $suffix++;
    }

    return $slug;
}

/**
 * @param array<string, mixed> $data
 */
function create_admin_course(array $data): bool
{
    $sql = "
        INSERT INTO courses (
            title,
            slug,
            description,
            geographic_department_id,
            start_date,
            end_date,
            status
        ) VALUES (
            :title,
            :slug,
            :description,
            :geographic_department_id,
            :start_date,
            :end_date,
            :status
        )
    ";

    $startDate = $data['start_date'] !== '' ? (string) $data['start_date'] : null;
    $endDate = $data['end_date'] !== '' ? (string) $data['end_date'] : null;
    $geoDepartmentIds = normalize_geo_department_ids((array) ($data['geographic_department_ids'] ?? []));
    $geoDeptId = isset($geoDepartmentIds[0]) ? (int) $geoDepartmentIds[0] : null;
    $slug = generate_unique_course_slug((string) $data['title']);

    $created = db_execute($sql, [
        ':title' => (string) $data['title'],
        ':slug' => $slug,
        ':description' => (string) $data['description'],
        ':geographic_department_id' => $geoDeptId,
        ':start_date' => $startDate,
        ':end_date' => $endDate,
        ':status' => (string) $data['status'],
    ]);

    if (!$created) {
        return false;
    }

    $courseId = (int) db()->lastInsertId();
    if ($courseId <= 0) {
        return false;
    }

    sync_course_geographic_departments($courseId, $geoDepartmentIds);
    return true;
}

/**
 * @param array<string, mixed> $data
 */
function update_admin_course(int $id, array $data): bool
{
    $sql = "
        UPDATE courses
        SET
            title = :title,
            slug = :slug,
            description = :description,
            geographic_department_id = :geographic_department_id,
            start_date = :start_date,
            end_date = :end_date,
            status = :status,
            updated_at = NOW()
        WHERE id = :id
    ";

    $startDate = $data['start_date'] !== '' ? (string) $data['start_date'] : null;
    $endDate = $data['end_date'] !== '' ? (string) $data['end_date'] : null;
    $geoDepartmentIds = normalize_geo_department_ids((array) ($data['geographic_department_ids'] ?? []));
    $geoDeptId = isset($geoDepartmentIds[0]) ? (int) $geoDepartmentIds[0] : null;
    $slug = generate_unique_course_slug((string) $data['title'], $id);

    $updated = db_execute($sql, [
        ':id' => $id,
        ':title' => (string) $data['title'],
        ':slug' => $slug,
        ':description' => (string) $data['description'],
        ':geographic_department_id' => $geoDeptId,
        ':start_date' => $startDate,
        ':end_date' => $endDate,
        ':status' => (string) $data['status'],
    ]);

    if (!$updated) {
        return false;
    }

    sync_course_geographic_departments($id, $geoDepartmentIds);
    return true;
}

function delete_admin_course(int $id): bool
{
    $sql = 'DELETE FROM courses WHERE id = :id';
    return db_execute($sql, [':id' => $id]);
}

/**
 * @return array{class: string, label: string}
 */
function admin_course_status_badge(string $status): array
{
    switch ($status) {
        case 'published':
            return ['class' => 'text-bg-success', 'label' => 'Publicado'];
        case 'draft':
            return ['class' => 'text-bg-secondary', 'label' => 'Borrador'];
        case 'archived':
            return ['class' => 'text-bg-dark', 'label' => 'Archivado'];
        default:
            return ['class' => 'text-bg-light', 'label' => ucfirst($status)];
    }
}