Delimiter //
/**
 * Author:  armen
 * Created: 3 janv. 2020
 * Supression des contacts 
 * V1 : ne supprime que les concacts lies aux comptes !!!!
 * 
 * @param contactId identifiant du contact à supprimer 
 * @param useTransaction flag indiquant si 'il faut gerer la transaction
 *   - 1 : gerer les transaction
 *   - 0 : ne pas gérer la transaction. Implique que la transaction est gérée en externe 
 * @param tmpTmTableNameOfCtcToDel nom de la table temporaire contentant les concatcs à supprimer
 *   - n'est pris compte que si useTransaction = 0 (gestion des transaction externe)
 *     et si contactId = 0 (ne correspond à aucun contacts car l'auto icrement des contacts
 *     commence à 1)
 * la table temporaire doit avoir une colonne CONTACTS_ID_CONTACTS
 * la table ne dois pas s'appeler TMP_CONTACTS_TO_DELETE
 */

Drop Procedure If Exists BUSINESS_DELETE_CONTACTS //
Create Procedure BUSINESS_DELETE_CONTACTS(
    In contactId int    
    , In useTransaction TinyInt
    , In tmpTmTableNameOfCtcToDel Varchar(50)
)
Begin
    bdelctc_main : Begin
    Declare ctc_transactionStarted int default 0;
        
        set @bdelctc_contactId = Coalesce(contactId, 0);
        set @bdelctc_useTransaction = Coalesce(useTransaction, 1);
        set @bdelctc_tmpTmTableNameOfCtcToDel = trim(Coalesce(tmpTmTableNameOfCtcToDel, ''));

        bdelctc_singleOrMultipleDelete: Begin
            Drop Temporary Table If Exists TMP_CONTACTS_TO_DELETE;
            Create Temporary Table TMP_CONTACTS_TO_DELETE (
                ID_CONTACTS_TO_DEL Int Unsigned Not Null Primary Key
            );



            bdelctc_multipleDelete: Begin
                -- est ce une supression mutliple ou simple ?
                -- on test 
                --  - si la transaction est extene (useTransaction)
                --  - si l'id compte vaut 0 (ne correponspond à aucun comptes 0)
                --  - si tmpTmTableNameOfCtcToDel  est fourni

               
                if @bdelctc_contactId <> 0 Or @bdelctc_useTransaction = 1 Or @bdelctc_tmpTmTableNameOfCtcToDel = '' Then
                    leave bdelctc_multipleDelete;
                End If;
                bdelctc_multipleDelete_copycontacts: Begin
                    -- on test la copie des comptes depuis la table temporaire indiquée
                    -- en cas d'erreur on propage l'exeception
                    -- autre solution à affiner on fait une pseudo copie
-- 
--                     Declare Continue Handler for SQLEXCEPTION 
                    Declare Continue Handler For SQLSTATE '42S02'
--                     Declare Exit Handler For Sqlexception 
--                     Declare Exit Handler For SQLSTATE '42S02'
                    Begin
--                         If ctc_transactionStarted  <> 0 then
--                             rollback;
--                             set ctc_transactionStarted  = 0;
--                         End if;
--                         leave bdelctc_multipleDelete_copycontacts;
                        Resignal;
                    End ;
                    Set @bdelctc_copyQuery = Concat(
                                'Insert Into TMP_CONTACTS_TO_DELETE (ID_CONTACTS_TO_DEL) '
                                , 'Select CONTACTS_ID_CONTACTS From ' 
                                ,  @bdelctc_tmpTmTableNameOfCtcToDel
                            )
                        ;
                    PREPARE bdelctc_copyContactstStmt FROM @bdelctc_copyQuery; 
                    execute bdelctc_copyContactstStmt 
                        ;
                    Deallocate prepare bdelctc_copyContactstStmt; 		

                End ;

            end ;

        End ;

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

--             Set @ctc_useTransaction = Coalesce(useTransaction, 1);
            If @bdelctc_useTransaction <> 0 Then
                Start Transaction;
                Set ctc_transactionStarted  = 1;
            End If;

            bdelacc_singleDelete_copycontacts: Begin
                -- en mode multiple @bdelacc_accountId = 0
                -- donc pas de doublons d'entries
                Insert Into TMP_CONTACTS_TO_DELETE (ID_CONTACTS_TO_DEL)
                Select ID_CONTACTS
                From CONTACTS 
                Where 
                    ID_CONTACTS = @bdelctc_contactId
                ;
            End ;


            Delete TAR
            From TMP_CONTACTS_TO_DELETE SRC
            Inner Join CONTACTS
                On CONTACTS.ID_CONTACTS = ID_CONTACTS_TO_DEL
            Inner Join CONTACTS_ADDRESSES TAR
                On TAR.CONTACTS_ID_CONTACTS = CONTACTS.ID_CONTACTS
            ;
            Delete TAR
            From TMP_CONTACTS_TO_DELETE SRC
            Inner Join CONTACTS
                On CONTACTS.ID_CONTACTS = ID_CONTACTS_TO_DEL
            Inner Join CONTACTS_PHONES TAR
                On TAR.CONTACTS_ID_CONTACTS = CONTACTS.ID_CONTACTS
            ;
            Delete TAR
            From TMP_CONTACTS_TO_DELETE SRC
            Inner Join CONTACTS
                On CONTACTS.ID_CONTACTS = ID_CONTACTS_TO_DEL
            Inner Join CONTACTS_EMAILS TAR
                On TAR.CONTACTS_ID_CONTACTS = CONTACTS.ID_CONTACTS
            ;
            Delete TAR
            From TMP_CONTACTS_TO_DELETE SRC
            Inner Join CONTACTS
                On CONTACTS.ID_CONTACTS = ID_CONTACTS_TO_DEL
            Inner Join CONTACTS_PHONES TAR
                On TAR.CONTACTS_ID_CONTACTS = CONTACTS.ID_CONTACTS
            ;
            Delete TAR
            From TMP_CONTACTS_TO_DELETE SRC
            Inner Join CONTACTS
                On CONTACTS.ID_CONTACTS = ID_CONTACTS_TO_DEL
            Inner Join CONTACTS_RIB_IBAN TAR
                On TAR.CONTACTS_ID_CONTACTS = CONTACTS.ID_CONTACTS
            ;
            Delete TAR
            From TMP_CONTACTS_TO_DELETE SRC
            Inner Join CONTACTS
                On CONTACTS.ID_CONTACTS = ID_CONTACTS_TO_DEL
            Inner Join CONTACTS_AVAILABLE TAR
                On TAR.CONTACTS_ID_CONTACTS = CONTACTS.ID_CONTACTS
            ;


            Delete TAR
            From TMP_CONTACTS_TO_DELETE SRC
            Inner Join CONTACTS
                On CONTACTS.ID_CONTACTS = ID_CONTACTS_TO_DEL
            Inner Join CONTACTS_BUSINESS TAR
                On TAR.CONTACTS_ID_CONTACTS = CONTACTS.ID_CONTACTS
            ;
            Delete TAR
            From TMP_CONTACTS_TO_DELETE SRC
            Inner Join CONTACTS
                On CONTACTS.ID_CONTACTS = ID_CONTACTS_TO_DEL
            Inner Join CONTACTS_INDIVIDUAL TAR
                On TAR.CONTACTS_ID_CONTACTS = CONTACTS.ID_CONTACTS
            ;
            Delete TAR
            From TMP_CONTACTS_TO_DELETE SRC
            Inner Join CONTACTS TAR
                On TAR.ID_CONTACTS = ID_CONTACTS_TO_DEL
            ;

        End ;


    End ;
End //
Delimiter ;