Delimiter //
/**
 * Author:  armen
 * Created: 25 oct. 2018
 * creation de la table TMP_ACC_HAS_ACC 
 * contient l'arobrescence complete des comptes liés dans des colonnes indéxées
 */
Drop Procedure if Exists BUSINESS_LINKED_ACCOUNTS_CREATE_FULL_TREE //
Create Procedure BUSINESS_LINKED_ACCOUNTS_CREATE_FULL_TREE(
            In idaccounts Int unsigned
            , In maxIeration Int 
        )
Begin
    blacft_main: begin
        blacft_init: begin
            
            -- on utilise Greatest pour eviter les négatifs + un least pour avoir une valeur max
            Set @blacft_maxIeration = Least(Greatest(Coalesce(maxIeration, 5), 3), 15); 
            Set @blacft_idaccounts = idaccounts; 

            -- suite au difficultes d'optimisation 
            -- on construit une reque dynamique

        end ;

        blacft_buildquery: begin

            Set @blacft_it=1;
            Set @blacft_querySelectPart = Concat('Select '
                        , 'aha0.ACCOUNTS_ID_ACCOUNTS ACCOUNTS_ID_ACCOUNTS_0 '
                        , ', aha0.ACCOUNTS_ID_ACCOUNTS_LINK ACCOUNTS_ID_ACCOUNTS_1 ' -- il n'a pas forcémant unsous-niveau => on doit utiliser ACCOUNTS_ID_ACCOUNTS_LINK
                    )
                ;
            Set @blacft_queryFromPart = Concat('From '
                        , 'ACCOUNTS_has_ACCOUNTS aha0 '
                        , 'Left Join ACCOUNTS_has_ACCOUNTS aha1 '
                        , ' On aha0.ACCOUNTS_ID_ACCOUNTS_LINK = aha1.ACCOUNTS_ID_ACCOUNTS '
                        , ' And aha0.ACCOUNTS_has_ACCOUNTS_LEVEL = 0 '
                        , ' And aha1.ACCOUNTS_has_ACCOUNTS_LEVEL = 1 '
                    )
                ;

            Set @blacft_queryCreateTable = Concat('Create Temporary Table TMP_ACC_HAS_ACC  ( '
                        , ' ACCOUNTS_ID_ACCOUNTS_0 Int Unsigned '
                        , ' , ACCOUNTS_ID_ACCOUNTS_1 Int Unsigned '
                    )
                ;
            Set @blacft_queryInsertCols = Concat(''
                        , ' ACCOUNTS_ID_ACCOUNTS_0'
                        , ' , ACCOUNTS_ID_ACCOUNTS_1'
                    )
                ;


        --     Set @blacft_queryCreateIndex = Concat('Alter Table TMP_ACC_HAS_ACC '
        --                 'Add Index IX_TMP_ACC_HAS_ACC_0(ACCOUNTS_ID_ACCOUNTS_0) '
        --                 , ', Add Index IX_TMP_ACC_HAS_ACC_1(ACCOUNTS_ID_ACCOUNTS_1) '
        --             )
        --         ;
            Set @blacft_queryCreateIndex = Concat(' '
                        ', Index IX_TMP_ACC_HAS_ACC_0(ACCOUNTS_ID_ACCOUNTS_0) '
                        , ', Index IX_TMP_ACC_HAS_ACC_1(ACCOUNTS_ID_ACCOUNTS_1) '
                    )
                ;

            buildQueryLoop: Loop
                set @blacft_it_prev = @blacft_it;
                set @blacft_it = @blacft_it + 1;

                if @blacft_it > @blacft_maxIeration  then
                    leave buildQueryLoop;
                End if;
                Set @blacft_querySelectPart = Concat(@blacft_querySelectPart, ' '
                            -- il n'a pas forcémant unsous-niveau => on doit utiliser ACCOUNTS_ID_ACCOUNTS_LINK du prev
        --                     , ', aha', @blacft_it, '.ACCOUNTS_ID_ACCOUNTS ACCOUNTS_ID_ACCOUNTS_', @blacft_it, ' '
                            , ', aha', @blacft_it_prev, '.ACCOUNTS_ID_ACCOUNTS_LINK ACCOUNTS_ID_ACCOUNTS_', @blacft_it, ' '
                        )
                    ;
                Set @blacft_queryFromPart=Concat(@blacft_queryFromPart, ' '
                            , 'Left Join ACCOUNTS_has_ACCOUNTS aha', @blacft_it, ' '
                            , ' On aha', @blacft_it_prev, '.ACCOUNTS_ID_ACCOUNTS_LINK = aha', @blacft_it, '.ACCOUNTS_ID_ACCOUNTS '
                            , ' And aha', @blacft_it, '.ACCOUNTS_has_ACCOUNTS_LEVEL = ', @blacft_it, ' '
                        )
                    ;
                Set @blacft_queryCreateTable = Concat(@blacft_queryCreateTable, ' '
                        , ', ACCOUNTS_ID_ACCOUNTS_', @blacft_it, ' Int Unsigned '
                    )
                ;

                Set @blacft_queryInsertCols = Concat(@blacft_queryInsertCols, ' '
                        , ', ACCOUNTS_ID_ACCOUNTS_', @blacft_it, ' '
                    )
                ;

                Set @blacft_queryCreateIndex = Concat(@blacft_queryCreateIndex, ' '
        --                 , ', Add Index IX_TMP_ACC_HAS_ACC_', @blacft_it, '(ACCOUNTS_ID_ACCOUNTS_', @blacft_it, ') '
                        , ', Index IX_TMP_ACC_HAS_ACC_', @blacft_it, '(ACCOUNTS_ID_ACCOUNTS_', @blacft_it, ') '
                    )
                ;
            End loop buildQueryLoop;

            Set @blacft_querySelectPart = Concat(@blacft_querySelectPart, ' '
                        , ', aha', @blacft_it_prev, '.ACCOUNTS_ID_ACCOUNTS_LINK ACCOUNTS_ID_ACCOUNTS_', @blacft_it, ' '
                    )
                ;
            Set @blacft_queryCreateTable = Concat(@blacft_queryCreateTable, ' '
                        , ', ACCOUNTS_ID_ACCOUNTS_', @blacft_it, ' Int Unsigned '
                    )
                ;
            Set @blacft_queryInsertCols = Concat(@blacft_queryInsertCols, ' '
                        , ', ACCOUNTS_ID_ACCOUNTS_', @blacft_it, ' '
                    )
                ;

            Set @blacft_queryCreateIndex = Concat(@blacft_queryCreateIndex, ' '
                        , ', Index IX_TMP_ACC_HAS_ACC_', @blacft_it, '(ACCOUNTS_ID_ACCOUNTS_', @blacft_it, ') '
        --                 , ', Add Index IX_TMP_ACC_HAS_ACC_', @blacft_it, '(ACCOUNTS_ID_ACCOUNTS_', @blacft_it, ') '
                    )
                ;
        
            Set @blacft_queryWherePart = Case When Trim(Coalesce(@blacft_idaccounts, '')) = '' Then '' else Concat(' Where ( aha0.ACCOUNTS_ID_ACCOUNTS = ', @blacft_idaccounts  ,' ) ') End ;
        end ;

        blacft_pocessing : begin
            Drop Temporary Table If exists TMP_ACC_HAS_ACC;
--             drop Temporary table if exists TMP_ACC_HAS_ACC;
            set @blacft_fullCreateTbleQuery=Concat(''
                                        , @blacft_queryCreateTable, ' '
                                        , @blacft_queryCreateIndex, ' '
                                        , ' )' -- fermeture de la parenthse du create table
                                    )
                                ;

        --     set @blacft_fullCreateTbleQuery=Concat('Create Temporary table TMP_ACC_HAS_ACC AS '
        --                                 , @blacft_querySelectPart, ' '
        --                                 , @blacft_queryFromPart, ' '
        --                                 , @blacft_queryWherePart 
        --                             )

--             select @blacft_fullCreateTbleQuery;
            PREPARE createTblStmt FROM @blacft_fullCreateTbleQuery; 
            execute createTblStmt ;
        
            Deallocate prepare createTblStmt;


            Set  @blacft_queryInsert = Concat(''
                        , 'Insert Into TMP_ACC_HAS_ACC ( '
                        , @blacft_queryInsertCols, ' '
                        , ') '
                        , @blacft_querySelectPart, ' '
                        , @blacft_queryFromPart, ' '
                        , @blacft_queryWherePart 
                    )
                ;
--             Select @blacft_queryInsert;

            PREPARE InsertStmt FROM @blacft_queryInsert; 
            execute InsertStmt ;
        
            Deallocate prepare InsertStmt;
        -- -- 
        -- 
        -- 
        -- 
        -- --     select @blacft_queryCreateIndex;
        --     PREPARE alterTblStmt FROM @blacft_queryCreateIndex; 
        --     execute alterTblStmt ;
        -- 
        --     Deallocate prepare alterTblStmt;
        End ;
    End ;

end //
Delimiter ;