Delimiter //
/**
	créer la table temporaire des profil en fonction des membre délégué
*/
Drop Procedure if Exists BUSINESS_COMPUTE_MEMBERS_CREDENTIALS_DELECATED_MEMBERS//
 
Create Procedure BUSINESS_COMPUTE_MEMBERS_CREDENTIALS_DELECATED_MEMBERS(
	In memberId Int Unsigned 
)
Begin
    memberAccCredentialAllDelectedMbrsInit: Begin
		Set @user_id = memberId;
		Drop Temporary Table If Exists TMP_MBR_DELECATED_MBR;
		Drop Temporary Table If Exists TMP_MBR_DELECATED_MBR0;
	End;
    memberAccCredentialAllDelectedMbrsMain: Begin
		-- utilisation d'une table tmp séparé pour de raison de perf issu du DISTINCT
		-- pour eviter un distinct avec une jointure sur la tables des comptes
		Create Temporary Table TMP_MBR_DELECATED_MBR (
			ENTITIES_ID_ENTITIES Int Unsigned Not Null
			, ID_ENTITIES_has_MEMBERS Int Unsigned Null
			, ID_ENTITIES_has_TEAMS Int Unsigned Null
			, Index IX_TMP_MBR_DELECATED_MBR_1(ENTITIES_ID_ENTITIES)
			, Index IX_TMP_MBR_DELECATED_MBR_2(ID_ENTITIES_has_MEMBERS)
			, Index IX_TMP_MBR_DELECATED_MBR_3(ID_ENTITIES_has_TEAMS)
		);

		Create Temporary Table TMP_MBR_DELECATED_MBR0 (
			ENTITIES_ID_ENTITIES Int Unsigned Not Null
			, ID_ENTITIES_has_MEMBERS Int Unsigned Null
			, ID_ENTITIES_has_TEAMS Int Unsigned Null
		);
		Insert Into TMP_MBR_DELECATED_MBR0 (
			ENTITIES_ID_ENTITIES, ID_ENTITIES_has_MEMBERS, ID_ENTITIES_has_TEAMS
		)
		Select 
			EHM.ENTITIES_ID_ENTITIES, EHM.ID_ENTITIES_has_MEMBERS, null
		From 
			ENTITIES_has_MEMBERS EHM
			Inner Join V_MEMBERS_DELEGATED VMD
-- 				On EHM.MEMBERS_ID_MEMBERS = VMD.MEMBERS_DELEGATED
				On EHM.MEMBERS_ID_MEMBERS = VMD.ID_MEMBERS

		where
-- 			VMD.ID_MEMBERS = @user_id
			VMD.MEMBERS_DELEGATED = @user_id
		    And VMD.ON_ACCESS = 1
# 		Union All
# 		Select
# 			EHT1.ENTITIES_ID_ENTITIES, null, THM1.ID_TEAMS_has_MEMBERS
#
# 		From
# 			TEAMS_has_MEMBERS THM1
# 			Inner Join V_MEMBERS_DELEGATED VMD1
# 				On THM1.MEMBERS_ID_MEMBERS = VMD1.ID_MEMBERS
# 			Inner Join ENTITIES_has_TEAMS EHT1
# 				On THM1.TEAMS_ID_TEAMS = EHT1.TEAMS_ID_TEAMS
# 		Where
# 			VMD1.MEMBERS_DELEGATED = @user_id

		;

		Insert Into TMP_MBR_DELECATED_MBR (
			ENTITIES_ID_ENTITIES, ID_ENTITIES_has_MEMBERS, ID_ENTITIES_has_TEAMS
		)
		Select Distinct -- IMPORTANT !!!!
			ENTITIES_ID_ENTITIES, ID_ENTITIES_has_MEMBERS, ID_ENTITIES_has_TEAMS
		From TMP_MBR_DELECATED_MBR0
		;
		
		Drop Temporary Table If Exists TMP_MBR_DELECATED_MBR0;

	End;
end //
Delimiter ;

