DROP PROCEDURE IF EXISTS sp_group_permissions;
CREATE PROCEDURE `sp_group_permissions`( group_id INT (11), user_id INT (11) )
BEGIN

-- permissions --
SELECT
	'perm' type,
	gp.perm_id,
	0 perm_sub_id,
	0 perm_comp_id,
	perm.perm_name,
	null perm_sub_name,
	null perm_comp_name
FROM
	users_groups ug
	INNER JOIN group_perm gp ON gp.group_id = ug.group_id
	INNER JOIN permissions perm ON perm.perm_id = gp.perm_id 
WHERE
	ug.group_id = group_id 
	AND ug.user_id = user_id

UNION

-- Sub Permissions --
SELECT
	'sub_perm' type,
	perm.perm_id,
	gsp.perm_sub_id,
	0 perm_comp_id,
	perm.perm_name,
	IFNULL(perm_sub.perm_sub_sort_name, perm_sub.perm_sub_name) perm_sub_name,
	null perm_comp_name
FROM
	users_groups ug
	INNER JOIN group_sub_perm gsp ON gsp.group_id = ug.group_id 
	INNER JOIN permission_submodules perm_sub ON perm_sub.perm_sub_id = gsp.perm_sub_id
	INNER JOIN permissions perm ON perm.perm_id = perm_sub.perm_id
WHERE
	ug.group_id = group_id
	AND ug.user_id = user_id

UNION

-- Permissions Complonent --
SELECT
	'comp_perm' type,
	perm.perm_id,
	perm_comp.perm_sub_id,
	perm_comp.perm_comp_id,
	perm.perm_name,
	IFNULL(perm_sub.perm_sub_sort_name, perm_sub.perm_sub_name) perm_sub_name,
	perm_comp.perm_comp_name
FROM
	users_groups ug
	INNER JOIN group_comp_perm gcp ON gcp.group_id = ug.group_id
	INNER JOIN permission_components perm_comp ON perm_comp.perm_comp_id = gcp.perm_comp_id
	INNER JOIN permission_submodules perm_sub ON perm_sub.perm_sub_id = perm_comp.perm_sub_id
	INNER JOIN permissions perm ON perm.perm_id = perm_sub.perm_id
WHERE
	ug.group_id = group_id
	AND ug.user_id = user_id
	
ORDER BY perm_id, perm_sub_id asc;

END