-- SQL para listar usuários com roles específicas (NÃO estudantes)
-- Retorna: Nome completo, Telefone, E-mail, Key, Role
-- Inclui apenas usuários com roles: Admin, Super Admin, Afiliado, Diretor de Clube, Supervisor Regional, Juiz, Sem Função

SELECT CONCAT(u.name, ' ', u.last_name) as 'Nome completo', u.phone as 'Telefone', u.email as 'E-mail', u.key as 'Key', r.name as 'Role'
FROM
    users u
    INNER JOIN model_has_roles mhr ON mhr.model_id = u.id
    AND mhr.model_type = 'App\\Models\\User'
    INNER JOIN roles r ON r.id = mhr.role_id
WHERE
    r.name IN (
        'Admin',
        'Super Admin',
        'Afiliado',
        'Diretor de Clube',
        'Supervisor Regional',
        'Juiz',
        'Sem Função'
    )
ORDER BY r.name, u.name, u.last_name;

-- Versão sem mostrar a role (apenas as 4 colunas solicitadas):
/*
SELECT 
CONCAT(u.name, ' ', u.last_name) as 'Nome completo',
u.phone as 'Telefone',
u.email as 'E-mail',
u.key as 'Key'

FROM users u
INNER JOIN model_has_roles mhr ON mhr.model_id = u.id 
AND mhr.model_type = 'App\\Models\\User'
INNER JOIN roles r ON r.id = mhr.role_id

WHERE 
r.name IN (
'Admin', 
'Super Admin', 
'Afiliado', 
'Diretor de Clube', 
'Supervisor Regional', 
'Juiz', 
'Sem Função'
)

ORDER BY u.name, u.last_name;
*/
