Delimiter //
/**
 * Author:  armen
 * Created: 4 juin 2019
 */
Drop Procedure if Exists BUSINESS_COMPUTE_MEMBERS_CREDENTIALS_CREATE_PROFILE_TABLE//
Create Procedure BUSINESS_COMPUTE_MEMBERS_CREDENTIALS_CREATE_PROFILE_TABLE(
	In memberId Int Unsigned 
	, In user_lng Int Unsigned 
	, In ref_type Int Unsigned 
    , out member_table_name varchar(100)
)
Begin
    memberAccCredentialFillClearOld: Begin
        -- supression des anciennes accredition du membres au dernier moment
        -- de maniere à limiter le temps ou le membre ne peux plus intervenir sur le compte
        if Coalesce(ref_type, 0) = 0 then
			set @bcmccpt_ref_type  = (Select ID_ENTITIES_REF_TYPES from ENTITIES_REF_TYPES Where ENTITIES_REF_TYPES_CODE = 'ACCRED_ACCOUNTS');
		Else
			set @bcmccpt_ref_type  = ref_type;
		End if ;

        set @bcmccpt_user_lng  = user_lng;
        Set @bcmccpt_user_id = memberId;

        -- supression des précalcul
        -- même si supprimé à l'aide de BUSINESS_COMPUTE_MEMBERS_CREDENTIALS_CLEAR_ALL
        -- -> permet d'appeler cette procédure en solo (maj de membre par exemple)
        call BUSINESS_COMPUTE_MEMBERS_CREDENTIALS_CLEAR_MEMBER_CRENDENTIALS(@bcmccpt_user_id,'ACCOUNTS');

        Set @bcmccpt_tblName = Concat('TMP_ACCOUNTS_CREDENTIALS_MEMBER__', @bcmccpt_user_id);

        Set @bcmccpt_query = concat(
                        'Create table If Not Exists ', @bcmccpt_tblName,' ( '
                        , ' ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null Primary Key '
                        , ' , DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES Int Unsigned Null '
                        -- l'id membre ci-dessous a jouté  même si non nécéssare
                        -- pour optimisation et simplification des requêtes lié au accreditations
                        -- lors nottamant de la substition 
                        , ' , MEMBERS_ID_MEMBERS Int Unsigned Not Null default ', @bcmccpt_user_id  
                        , ' , Index IX_', @bcmccpt_tblName, '_1  (MEMBERS_ID_MEMBERS) '
                        , ' , Index IX_', @bcmccpt_tblName, '_2  (DF_ACCESS_PROFILES_ID_DF_ACCESS_PROFILES) '
                        , '	) '
                    )
                ;

        PREPARE bcmccpt_credTblCreateStm FROM @bcmccpt_query; 
        execute bcmccpt_credTblCreateStm ;
        Deallocate prepare bcmccpt_credTblCreateStm; 

    End;
    set member_table_name = @bcmccpt_tblName;
End //
Delimiter ;

