Delimiter //
/**
 * Author:  armen
 * Created: 8 nov. 2019
 * rulesRef identifiant de regle (lot) a ne pas supprimer (lot courrant)
 */
Drop Procedure if Exists BUSINESS_ATTACHED_MEMBERS_DELETE_PREPARED//
Create Procedure BUSINESS_ATTACHED_MEMBERS_DELETE_PREPARED(
    In rulesRefToPreserve Varchar(40)
    , In idEntities Int Unsigned
)
Begin
    SET @bamdp_OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
    SET @bamdp_OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

    bamic_main: Begin

        if rulesRefToPreserve Is Null Then
--             select 'leave main ref empty';
            leave bamic_main;
        End if;
        
        Set @bamdp_rulesRefToPreserve = rulesRefToPreserve ;

        Set @bamdp_idEntities = Coalesce(idEntities, 0);

--         Drop Temporary Table If Exists TMP_ACCOUNTS_has_MEMBERS_REF_TO_DEL;
--         Create Temporary Table TMP_ACCOUNTS_has_MEMBERS_REF_TO_DEL (
--                     ACCOUNTS_has_MEMBERS_REF Varchar(40)
--                     , Index(ACCOUNTS_has_MEMBERS_REF)
--                 )
--             ;
        bamic_autoref: Begin
            if @bamdp_rulesRefToPreserve like 'LOADER.%' Then
                -- on est dans le cas du post traitement de l'import avec ref fourni
                -- on sort du bloque
--                 select 'leave bamic_autoref BEFORE';
                Leave bamic_autoref;
            End If;


            -- on est dans le cas du traitement sans ref fournis (ref auto)



            if @bamdp_idEntities = 0 Then

--                 Insert Into TMP_ACCOUNTS_has_MEMBERS_REF_TO_DEL (ACCOUNTS_has_MEMBERS_REF)
--                 Select Distinct ACCOUNTS_has_MEMBERS_REF
--                 From ACCOUNTS_has_MEMBERS tar
--                 Where
--                     tar.ACCOUNTS_has_MEMBERS_STATUS = 0
--                     And tar.ACCOUNTS_has_MEMBERS_REF Is Not Null
--                     And tar.ACCOUNTS_has_MEMBERS_REF Like 'AUTO.%'
--                     And tar.ACCOUNTS_has_MEMBERS_REF Not Like 'LOADER.%'
--                 ;

--                 Delete ACCOUNTS_has_MEMBERS
--                 From ACCOUNTS_has_MEMBERS
--                 Where ACCOUNTS_has_MEMBERS_REF = @bamdp_rulesRefToPreserve
--                 ;

                -- explain
                Delete tar
                From ACCOUNTS_has_MEMBERS  tar
--                     Inner Join TMP_ACCOUNTS_has_MEMBERS_REF_TO_DEL treftodel
--                         On tar.ACCOUNTS_has_MEMBERS_REF = treftodel.ACCOUNTS_has_MEMBERS_REF
                Where 
                    tar.ACCOUNTS_has_MEMBERS_STATUS = 0
                    And tar.ACCOUNTS_has_MEMBERS_REF Is Not Null
                    And tar.ACCOUNTS_has_MEMBERS_REF Like 'AUTO.%'
                    And tar.ACCOUNTS_has_MEMBERS_REF <> @bamdp_rulesRefToPreserve
--                     And tar.ACCOUNTS_has_MEMBERS_REF Not Like 'LOADER.%'
                ;
--                 select 'leave bamic_autoref AFTER';
                Leave bamic_autoref;

            Else
--                 Insert Into TMP_ACCOUNTS_has_MEMBERS_REF_TO_DEL (ACCOUNTS_has_MEMBERS_REF)
--                 Select Distinct ACCOUNTS_has_MEMBERS_REF
--                 From ACCOUNTS_has_MEMBERS tar
--                     Inner Join ACCOUNTS acc
--                         On tar.ACCOUNTS_ID_ACCOUNTS = acc.ID_ACCOUNTS
--                 Where
--                     tar.ACCOUNTS_has_MEMBERS_STATUS = 0
--                     And acc.ENTITIES_ID_ENTITIES = @bamdp_idEntities
--                     And tar.ACCOUNTS_has_MEMBERS_REF Is Not Null
--                     And tar.ACCOUNTS_has_MEMBERS_REF Like 'AUTO.%'
--                     And tar.ACCOUNTS_has_MEMBERS_REF <> @bamdp_rulesRefToPreserve
--                 ;
                
--                 Delete ACCOUNTS_has_MEMBERS
--                 From ACCOUNTS_has_MEMBERS
--                 Where ACCOUNTS_has_MEMBERS_REF = @bamdp_rulesRefToPreserve
--                 ;

                -- explain
                Delete tar
                From ACCOUNTS_has_MEMBERS  tar
                    Inner Join ACCOUNTS acc
                        On tar.ACCOUNTS_ID_ACCOUNTS = acc.ID_ACCOUNTS
--                     Inner Join TMP_ACCOUNTS_has_MEMBERS_REF_TO_DEL treftodel
--                         On tar.ACCOUNTS_has_MEMBERS_REF = treftodel.ACCOUNTS_has_MEMBERS_REF
                Where 
                    tar.ACCOUNTS_has_MEMBERS_STATUS = 0
                    And acc.ENTITIES_ID_ENTITIES = @bamdp_idEntities
                    And tar.ACCOUNTS_has_MEMBERS_REF Is Not Null
                    And tar.ACCOUNTS_has_MEMBERS_REF Like 'AUTO.%'
                    And tar.ACCOUNTS_has_MEMBERS_REF <> @bamdp_rulesRefToPreserve
                    -- on exclu les refs fournis de la supression
--                     And tar.ACCOUNTS_has_MEMBERS_REF Not Like 'LOADER.%'
                ;
            End if ;

--             select 'leave MAIN auto ref';
            leave bamic_main;
        End ;

        bamic_loaderprefix: begin
            if Coalesce(@bamdp_rulesRefToPreserve, '') Not like 'LOADER.%' Then
                -- on est dans le cas d'un traitement sans ref founi (ref auto)
                -- on sort du bloqu
--                 select 'leave prefix BEFORE';
                Leave bamic_loaderprefix;
            End If;

            -- on est dans le cas du traitement d'une ref fourni
            -- -> n'est existant que dans le dernier traitement de l'import 

            if @bamdp_idEntities = 0 Then
                -- incorect, ça ne devrait pas arriver car on dans le cas de l'import
                -- on sort
--                 select 'leave prefix empty entity';
                leave bamic_loaderprefix;
            End if ;


                Delete tar
                From ACCOUNTS_has_MEMBERS  tar
                    Inner Join ACCOUNTS acc
                        On tar.ACCOUNTS_ID_ACCOUNTS = acc.ID_ACCOUNTS
                Where 
                    tar.ACCOUNTS_has_MEMBERS_STATUS = 0
                    And acc.ENTITIES_ID_ENTITIES = @bamdp_idEntities
                    And tar.ACCOUNTS_has_MEMBERS_REF Is Not Null
                    -- comme on est dans le dernier traitement de l'import 
                    -- on ne purge que la référence en question 
                    And tar.ACCOUNTS_has_MEMBERS_REF = @bamdp_rulesRefToPreserve
    --                     And tar.ACCOUNTS_has_MEMBERS_REF Like 'LOADER.%' -- implicite
                ;

        End ;

    End;

    SET FOREIGN_KEY_CHECKS=@bamdp_OLD_UNIQUE_CHECKS;
    SET UNIQUE_CHECKS=@bamdp_OLD_FOREIGN_KEY_CHECKS;


End //
Delimiter ;