DROP PROCEDURE IF EXISTS sp_groupwise_permissions;

CREATE PROCEDURE sp_groupwise_permissions ( group_id INT (11), user_id INT (11) ) 

BEGIN
	
	DROP TABLE IF EXISTS tmp_group_permissions;
	CREATE TABLE tmp_group_permissions  (
		parent_id int,
		child_id int,
		parent_type	VARCHAR(20), 
		perm_id	int, 
		perm_sub_id	int, 
		perm_comp_id	int, 
		perm_name	VARCHAR(1000), 
		perm_sub_name	VARCHAR(1000), 
		perm_comp_name	VARCHAR(1000)
		) ;

	Insert into tmp_group_permissions (parent_id, child_id, parent_type, perm_id, perm_sub_id, perm_comp_id, perm_name, perm_sub_name, perm_comp_name)
	SELECT
		0, 
		gp.perm_id,
		'perm' parent_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;

	
	Insert into tmp_group_permissions (parent_id, child_id, parent_type, perm_id, perm_sub_id, perm_comp_id, perm_name, perm_sub_name, perm_comp_name)
	SELECT
		perm.perm_id,
		gsp.perm_sub_id,
		'sub_perm' parent_type,
		perm.perm_id,
		gsp.perm_sub_id,
		0 perm_comp_id,
		perm.perm_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;

	

	Insert into tmp_group_permissions (parent_id, child_id, parent_type, perm_id, perm_sub_id, perm_comp_id, perm_name, perm_sub_name, perm_comp_name)
	SELECT
		perm_comp.perm_sub_id,
		perm_comp.perm_comp_id,
		'comp_perm' parent_type,
		perm.perm_id,
		perm_comp.perm_sub_id,
		perm_comp.perm_comp_id,
		perm.perm_name,
		perm_sub.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;

	SELECT JSON_OBJECT (perm_name, JSON_ARRAYAGG(comp_name)) permisson_dtl
	FROM (
		SELECT	pa.perm_name as perm_name, JSON_OBJECT(
						IFNULL(pa.perm_sub_name,pa.perm_name),
						JSON_ARRAYAGG( pb.perm_comp_name)
						)  comp_name
			FROM	tmp_group_permissions pa
			LEFT JOIN tmp_group_permissions pb ON pa.child_id = pb.parent_id  
			WHERE (pa.parent_id = 0 Or pa.perm_comp_id = 0) And (pb.perm_comp_id > 0)
			Group by pa.child_id
			Order by pa.perm_id
	) perm
	GROUP BY perm_name;
END;