Delimiter //
/**
 * Author:  armen
 * Created: 8 mars 2018
 */
Drop Procedure If Exists BUSINESS_DELETE_UNUSED_DOCUMENTS //
Create Procedure BUSINESS_DELETE_UNUSED_DOCUMENTS(
    In useTransaction TinyInt
)
Begin
    bdud_main: Begin
        Declare transactionStarted int default 0;
        set @bdud_useTransaction = Coalesce(useTransaction, 1);
        bdud_deletionMain: Begin

            Declare Exit Handler For Sqlexception
            Begin
                If transactionStarted  <> 0 then
                    rollback;
                    set transactionStarted  = 0;
                End if;
                Resignal;
            End;

            Set useTransaction = Coalesce(useTransaction, 1);
            If useTransaction <> 0 Then
                Start Transaction;
                Set transactionStarted  = 1;
            End If;

            bdud_deltAttachedDocs: Begin
                Delete attd
                From 
                    ATTACHED_DOCUMENTS attd
                    Left Join ACCOUNTS_has_ATTACHED_DOCUMENT accattd
                        On attd.ID_ATTACHED_DOCUMENTS = accattd.ATTACHED_DOC_ID_ATTACHED_DOC
                    Left Join ENTRIES_has_ATTACHED_DOCUMENTS entattd
                        On attd.ID_ATTACHED_DOCUMENTS = entattd.ATTACHED_DOC_ID_ATTACHED_DOC
                    Left Join MESSAGES_has_ATTACHED_DOC msgattd
                        On attd.ID_ATTACHED_DOCUMENTS = msgattd.ATTACHED_DOC_ID_ATTACHED_DOC
                    Left Join SUMM_has_ATTACHED_DOCS smattd
                        On attd.ID_ATTACHED_DOCUMENTS = smattd.ATTACH_DOC_ID_ATTACH_DOC

                Where Coalesce(accattd.ATTACHED_DOC_ID_ATTACHED_DOC, entattd.ATTACHED_DOC_ID_ATTACHED_DOC
                            , msgattd.ATTACHED_DOC_ID_ATTACHED_DOC, smattd.ATTACH_DOC_ID_ATTACH_DOC
                        ) Is Null

                ;
            end;

            bdud_delDocuments: Begin
                
                Delete dct
                From DOCUMENTS dct
                    Left Join ATTACHED_DOCUMENTS attd
                        On dct.ID_DOCUMENTS = attd.DOCUMENTS_ID_DOCUMENTS
                    Left Join ENTITIES ent1
                        On dct.ID_DOCUMENTS = ent1.ENTITIES_FOOTER_PICTURE
                    Left Join ENTITIES ent2
                        On dct.ID_DOCUMENTS = ent2.ENTITIES_HEADER_PICTURE
                    Left Join ENTITIES ent3
                        On dct.ID_DOCUMENTS = ent3.ENTITIES_PICTURE1
                    Left Join ENTITIES ent4
                        On dct.ID_DOCUMENTS = ent4.ENTITIES_PICTURE2
                    Left Join ENTITIES ent5
                        On dct.ID_DOCUMENTS = ent5.ENTITIES_PICTURE3
                    Left Join ENTITIES_AGENCIES engs1
                        On dct.ID_DOCUMENTS = engs1.ENTITIES_AGENCIES_HEADER_PICTURE
                    Left Join ENTITIES_AGENCIES engs2
                        On dct.ID_DOCUMENTS = engs2.ENTITIES_AGENCIES_FOOTER_PICTURE
                    Left Join ENTITIES_AGENCIES engs3
                        On dct.ID_DOCUMENTS = engs3.ENTITIES_AGENCIES_EXTRA_PICTURE
                Where
                    Coalesce(attd.DOCUMENTS_ID_DOCUMENTS, ent1.ENTITIES_FOOTER_PICTURE, ent2.ENTITIES_HEADER_PICTURE
                            , ent3.ENTITIES_PICTURE1, ent4.ENTITIES_PICTURE2, ent5.ENTITIES_PICTURE3
                            , engs1.ENTITIES_AGENCIES_HEADER_PICTURE, engs2.ENTITIES_AGENCIES_FOOTER_PICTURE
                            , engs3.ENTITIES_AGENCIES_EXTRA_PICTURE
                        ) Is Null

                ;
            end;

            If transactionStarted <> 0 Then
                Commit ;
            End If;
            
        End ;

    End ;
End //
Delimiter ;