-- Script para buscar as keys de todos os usuários que NÃO são estudantes/membros
-- Retorna apenas usuários com roles específicas (excluindo estudantes)

-- Versão 1: Buscar keys de usuários com roles específicas
SELECT
    u.id as 'ID',
    CONCAT(u.name, ' ', u.last_name) as 'Nome completo',
    u.email as 'E-mail',
    u.key as 'Key',
    r.name as 'Role',
    CASE
        WHEN u.key IS NULL
        OR u.key = '' THEN 'Precisa fazer cadastro de juiz'
        ELSE 'Tem key válida'
    END as 'Status da 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
    CASE
        WHEN u.key IS NULL
        OR u.key = '' THEN 1
        ELSE 0
    END,
    r.name,
    u.name,
    u.last_name;

-- Versão 2: Buscar apenas usuários que PRECISAM fazer cadastro de juiz
/*
SELECT 
u.id as 'ID',
CONCAT(u.name, ' ', u.last_name) as 'Nome completo',
u.email as 'E-mail',
u.phone as 'Telefone',
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')
AND (u.key IS NULL OR u.key = '')
ORDER BY 
r.name, 
u.name, 
u.last_name;
*/

-- Versão 3: Buscar apenas usuários que JÁ TÊM key válida
/*
SELECT 
u.id as 'ID',
CONCAT(u.name, ' ', u.last_name) as 'Nome completo',
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')
AND u.key IS NOT NULL 
AND u.key != ''
ORDER BY 
r.name, 
u.name, 
u.last_name;
*/

-- Versão 4: Estatísticas resumidas
/*
SELECT 
r.name as 'Role',
COUNT(*) as 'Total de Usuários',
COUNT(u.key) as 'Com Key',
COUNT(*) - COUNT(u.key) as 'Sem Key',
ROUND((COUNT(u.key) * 100.0 / COUNT(*)), 2) as '% Com 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')
GROUP BY 
r.name
ORDER BY 
COUNT(*) DESC;
*/
