alrahma_sunday_school_api/app/Services/Users/UserListService.php

<?php

namespace App\Services\Users;

use App\Models\User;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class UserListService
{
    private const SORT_FIELDS = [
        'id',
        'firstname',
        'lastname',
        'email',
        'status',
        'updated_at',
    ];

    public function list(?string $sort, ?string $order): array
    {
        $sort = in_array($sort, self::SORT_FIELDS, true) ? $sort : 'id';
        $order = strtolower((string) $order) === 'desc' ? 'desc' : 'asc';

        $users = User::query()
            ->with('roles')
            ->orderBy($sort, $order)
            ->get();

        $guardianMap = $this->guardianTypeMap();
        [$secondByUserId, $secondByEmail] = $this->secondParentMaps();

        $usersWithRoles = [];

        foreach ($users as $user) {
            $roles = $user->roles->map(fn ($role) => (string) $role->name)->values()->all();
            $roleIds = $user->roles->map(fn ($role) => (int) $role->id)->values()->all();

            $userId = (int) $user->id;
            $parentType = $guardianMap[$userId] ?? '';

            $isSecond = false;
            if ($userId > 0 && ! empty($secondByUserId[$userId])) {
                $isSecond = true;
            } else {
                $email = trim(strtolower((string) $user->email));
                if ($email !== '' && ! empty($secondByEmail[$email])) {
                    $isSecond = true;
                }
            }

            $usersWithRoles[] = [
                'user' => $user->toArray(),
                'roles' => $roles,
                'role_ids' => $roleIds,
                'parent_type' => $parentType,
                'second_from_parents' => $isSecond ? 'Yes' : '',
            ];
        }

        return $usersWithRoles;
    }

    public function getUsersWithRoles(?string $sort = 'id', ?string $order = 'asc'): array
    {
        $sort = (string) ($sort ?? 'id');
        $order = (string) ($order ?? 'asc');

        $map = [
            'id' => 'users.id',
            'firstname' => 'users.firstname',
            'lastname' => 'users.lastname',
            'email' => 'users.email',
            'role' => 'role',
        ];

        $sortField = $map[$sort] ?? 'users.id';

        return User::getUsersWithRoles($sortField, $order);
    }

    public function getUnverifiedUsers(): array
    {
        return User::getUnverifiedUsers();
    }

    public function getUsersByRole(string $role): array
    {
        return User::getUsersByRole($role);
    }

    private function guardianTypeMap(): array
    {
        if (! Schema::hasTable('family_guardians')) {
            return [];
        }

        $rows = DB::table('family_guardians')
            ->selectRaw('user_id, MAX(is_primary) AS max_primary, COUNT(*) AS cnt')
            ->groupBy('user_id')
            ->get();

        $map = [];
        foreach ($rows as $row) {
            $userId = (int) ($row->user_id ?? 0);
            if ($userId <= 0) {
                continue;
            }
            $count = (int) ($row->cnt ?? 0);
            if ($count <= 0) {
                continue;
            }
            $maxPrimary = (int) ($row->max_primary ?? 0);
            $map[$userId] = $maxPrimary > 0 ? 'Primary' : 'Secondary';
        }

        return $map;
    }

    private function secondParentMaps(): array
    {
        if (! Schema::hasTable('parents')) {
            return [[], []];
        }

        $byUserId = [];
        $byEmail = [];

        if (Schema::hasColumn('parents', 'secondparent_user_id')) {
            $rows = DB::table('parents')
                ->selectRaw('secondparent_user_id AS uid, COUNT(*) AS cnt')
                ->where('secondparent_user_id', '!=', 0)
                ->groupBy('secondparent_user_id')
                ->get();

            foreach ($rows as $row) {
                $userId = (int) ($row->uid ?? 0);
                if ($userId > 0) {
                    $byUserId[$userId] = true;
                }
            }
        }

        if (Schema::hasColumn('parents', 'secondparent_email')) {
            $rows = DB::table('parents')
                ->selectRaw('LOWER(TRIM(secondparent_email)) AS em, COUNT(*) AS cnt')
                ->where('secondparent_email', '!=', '')
                ->groupBy('secondparent_email')
                ->get();

            foreach ($rows as $row) {
                $email = trim(strtolower((string) ($row->em ?? '')));
                if ($email !== '') {
                    $byEmail[$email] = true;
                }
            }
        }

        return [$byUserId, $byEmail];
    }
}