<?php

namespace App\Services\Expenses;

use Illuminate\Support\Facades\DB;

class ExpenseStaffService
{
    public function listStaffUsers(): array
    {
        $rows = DB::table('users')
            ->select('users.id', 'users.firstname', 'users.lastname', 'roles.name AS role_name')
            ->leftJoin('user_roles', 'user_roles.user_id', '=', 'users.id')
            ->leftJoin('roles', 'roles.id', '=', 'user_roles.role_id')
            ->whereNotNull('roles.name')
            ->get()
            ->map(fn ($row) => (array) $row)
            ->all();

        $excludedRoles = array_map('strtolower', ['parent', 'student', 'guest']);
        $staff = [];

        foreach ($rows as $row) {
            $roleName = strtolower((string) ($row['role_name'] ?? ''));
            $id = (int) ($row['id'] ?? 0);
            if ($id <= 0 || in_array($roleName, $excludedRoles, true)) {
                continue;
            }
            if (! isset($staff[$id])) {
                $staff[$id] = [
                    'id' => $id,
                    'firstname' => $row['firstname'] ?? '',
                    'lastname' => $row['lastname'] ?? '',
                ];
            }
        }

        uasort($staff, static function ($a, $b) {
            $nameA = trim(($a['firstname'] ?? '').' '.($a['lastname'] ?? ''));
            $nameB = trim(($b['firstname'] ?? '').' '.($b['lastname'] ?? ''));

            return strcasecmp($nameA, $nameB);
        });

        return array_values($staff);
    }
}