-- Script de estatísticas: Resumo de keys por role
-- Mostra quantos usuários têm/sem key por função

SELECT r.name as 'Role', COUNT(*) as 'Total de Usuários', COUNT(
        CASE
            WHEN u.key IS NOT NULL
            AND u.key != ''
            AND u.key != 'Precisa fazer cadastro de juiz' THEN 1
        END
    ) as 'Com Key Válida', COUNT(
        CASE
            WHEN u.key IS NULL
            OR u.key = ''
            OR u.key = 'Precisa fazer cadastro de juiz' THEN 1
        END
    ) as 'Sem Key', ROUND(
        (
            COUNT(
                CASE
                    WHEN u.key IS NOT NULL
                    AND u.key != ''
                    AND u.key != 'Precisa fazer cadastro de juiz' THEN 1
                END
            ) * 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;

-- Resumo geral
SELECT 'TOTAL GERAL' as 'Role', COUNT(*) as 'Total de Usuários', COUNT(
        CASE
            WHEN u.key IS NOT NULL
            AND u.key != ''
            AND u.key != 'Precisa fazer cadastro de juiz' THEN 1
        END
    ) as 'Com Key Válida', COUNT(
        CASE
            WHEN u.key IS NULL
            OR u.key = ''
            OR u.key = 'Precisa fazer cadastro de juiz' THEN 1
        END
    ) as 'Sem Key', ROUND(
        (
            COUNT(
                CASE
                    WHEN u.key IS NOT NULL
                    AND u.key != ''
                    AND u.key != 'Precisa fazer cadastro de juiz' THEN 1
                END
            ) * 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'
    );
