One Hat Cyber Team

View File Name : admin_geographic_departments.php

<?php

declare(strict_types=1);

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

/**
 * @return array<int, array<string, mixed>>
 */
function get_admin_geographic_departments(?string $search = null, ?string $status = null): array
{
    $conditions = ['1 = 1'];
    $params = [];

    if ($search !== null && trim($search) !== '') {
        $conditions[] = '(name LIKE :search OR code LIKE :search)';
        $params[':search'] = '%' . trim($search) . '%';
    }

    if ($status !== null && in_array($status, ['active', 'inactive'], true)) {
        $conditions[] = 'status = :status';
        $params[':status'] = $status;
    }

    $sql = '
        SELECT
            id,
            name,
            code,
            status,
            created_at,
            updated_at
        FROM geographic_departments
        WHERE ' . implode(' AND ', $conditions) . '
        ORDER BY name ASC
    ';

    return db_fetch_all($sql, $params);
}

/**
 * @return array<string, mixed>|null
 */
function get_admin_geographic_department_by_id(int $id): ?array
{
    $sql = '
        SELECT id, name, code, status
        FROM geographic_departments
        WHERE id = :id
        LIMIT 1
    ';

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

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

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

    $sql = '
        SELECT id
        FROM geographic_departments
        WHERE name = :name
        LIMIT 1
    ';

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

function geographic_department_code_exists(string $code, int $excludeId = 0): bool
{
    if ($code === '') {
        return false;
    }

    if ($excludeId > 0) {
        $sql = '
            SELECT id
            FROM geographic_departments
            WHERE code = :code
              AND id <> :exclude_id
            LIMIT 1
        ';

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

    $sql = '
        SELECT id
        FROM geographic_departments
        WHERE code = :code
        LIMIT 1
    ';

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

/**
 * @param array<string, mixed> $data
 */
function create_admin_geographic_department(array $data): bool
{
    $sql = '
        INSERT INTO geographic_departments (name, code, status)
        VALUES (:name, :code, :status)
    ';

    $code = trim((string) ($data['code'] ?? ''));

    return db_execute($sql, [
        ':name' => trim((string) ($data['name'] ?? '')),
        ':code' => $code !== '' ? strtoupper($code) : null,
        ':status' => (string) ($data['status'] ?? 'active'),
    ]);
}

/**
 * @param array<string, mixed> $data
 */
function update_admin_geographic_department(int $id, array $data): bool
{
    $sql = '
        UPDATE geographic_departments
        SET
            name = :name,
            code = :code,
            status = :status,
            updated_at = NOW()
        WHERE id = :id
    ';

    $code = trim((string) ($data['code'] ?? ''));

    return db_execute($sql, [
        ':id' => $id,
        ':name' => trim((string) ($data['name'] ?? '')),
        ':code' => $code !== '' ? strtoupper($code) : null,
        ':status' => (string) ($data['status'] ?? 'active'),
    ]);
}

/**
 * @return array{class: string, label: string}
 */
function geographic_department_status_badge(string $status): array
{
    switch ($status) {
        case 'active':
            return ['class' => 'text-bg-success', 'label' => 'Activo'];
        case 'inactive':
            return ['class' => 'text-bg-secondary', 'label' => 'Inactivo'];
        default:
            return ['class' => 'text-bg-light', 'label' => ucfirst($status)];
    }
}

/**
 * @return array<int, array<string, mixed>>
 */
function get_courses_for_geographic_assignment(): array
{
    $sql = '
        SELECT
            c.id,
            c.title,
            c.status,
            a.name AS academic_department_name,
            gd.id  AS geographic_department_id,
            gd.name AS geographic_department_name
        FROM courses c
        INNER JOIN departments a ON a.id = c.department_id
        LEFT JOIN geographic_departments gd ON gd.id = c.geographic_department_id
        ORDER BY c.title ASC
    ';

    return db_fetch_all($sql);
}

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

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

function assign_course_to_geographic_department(int $courseId, ?int $geographicDepartmentId): bool
{
    $sql = '
        UPDATE courses
        SET geographic_department_id = :geographic_department_id,
            updated_at = NOW()
        WHERE id = :course_id
    ';

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

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