CREATE OR REPLACE VIEW view_all_users AS
SELECT 
    u.id_user AS id,
    u.nome COLLATE utf8mb4_general_ci AS nome,
    u.apelido COLLATE utf8mb4_general_ci AS apelido,
    e.nome COLLATE utf8mb4_general_ci AS nome_empresa,
    u.email COLLATE utf8mb4_general_ci AS email,
    u.telefone COLLATE utf8mb4_general_ci AS telefone,
    'utilizador' AS tipo,
    COALESCE(
        JSON_OBJECTAGG(
            perms.dkey,
            CASE 
                WHEN perms.dvalue = 1 THEN 'true'
                ELSE 'false'
            END
        ),
        JSON_OBJECT()
    ) AS perms,
		CASE 
			WHEN u.active = 1 THEN 'true'
			ELSE 'false'
		END AS active
FROM utilizadores u
JOIN empresas e ON e.id_empresa = u.id_empresa
LEFT JOIN permissoes perms ON perms.id_user = u.id_user
GROUP BY u.id_user