Delimiter //
/**
 * Author:  armen
 * Created: 29 nov. 2017
 */
Drop Procedure if Exists BUSINESS_LINKED_ACCOUNTS_CHECK_CIRCULARE_REF //
Create Procedure BUSINESS_LINKED_ACCOUNTS_CHECK_CIRCULARE_REF(
            In idaccounts Int unsigned
            , In maxIeration Int 
            , InOut outputTable Varchar(50)
            , Out ciculareRefCount Int 
        )
Begin
    Set @blaccr_maxIeration = Greatest(Coalesce(maxIeration, 10), 3); -- on utilise Greatest pour eviter les négatif
    
--     Set @blaccr_accoutFilter = coalesce(idaccounts, '%');
    if idaccounts Is null Then
        Set @blaccr_accMinVal = 0;
        Set @blaccr_accMaxVal = 4e7;
    Else
        Set @blaccr_accMinVal = idaccounts;
        Set @blaccr_accMaxVal = idaccounts;
    End if;
    -- creation tables de travail
    
    Drop Temporary Table If Exists TMP_CIRCULAR_ACCOUNTS_0;
    Create Temporary Table TMP_CIRCULAR_ACCOUNTS_0 (
                ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null
                , Index IX_TMP_CIRCULAR_ACCOUNTS_1_1 (ACCOUNTS_ID_ACCOUNTS)
            )
        ;

    -- creation tables de travail
    Drop Temporary Table If Exists TMP_CIRCULAR_ACCOUNTS_1;
    Create Temporary Table TMP_CIRCULAR_ACCOUNTS_1 (
                ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null
                , ACCOUNTS_ID_ACCOUNTS_LINK Int Unsigned Not Null
                , TMP_CIRCULAR_ACCOUNTS_LEVEL Int Not Null Default 0
                , TMP_CIRCULAR_ACCOUNTS_PREFIX Varchar(2000) Not Null Default ''
                , Index IX_TMP_CIRCULAR_ACCOUNTS_1_1 (ACCOUNTS_ID_ACCOUNTS, ACCOUNTS_ID_ACCOUNTS_LINK)
                , Index IX_TMP_CIRCULAR_ACCOUNTS_1_2 (ACCOUNTS_ID_ACCOUNTS_LINK)
            )
        ;

    Drop Temporary Table If Exists TMP_CIRCULAR_ACCOUNTS_2;
    Create Temporary Table TMP_CIRCULAR_ACCOUNTS_2 (
                ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null
                , ACCOUNTS_ID_ACCOUNTS_LINK Int Unsigned Not Null
                , TMP_CIRCULAR_ACCOUNTS_LEVEL Int Not Null Default 0
                , TMP_CIRCULAR_ACCOUNTS_PREFIX Varchar(2000) Not Null Default ''
                , Index IX_TMP_CIRCULAR_ACCOUNTS_2_1(ACCOUNTS_ID_ACCOUNTS, ACCOUNTS_ID_ACCOUNTS_LINK)
                , Index IX_TMP_CIRCULAR_ACCOUNTS_2_2(ACCOUNTS_ID_ACCOUNTS_LINK)
            )
        ;
    
    -- prépartion des données initiales
    --  table des comptes à la foix compte de consolidation et compte consolidé 
    -- => c'est ces comptes qui peuvent potentiellement  être en référence circulaire
    Insert Into TMP_CIRCULAR_ACCOUNTS_0 (
            ACCOUNTS_ID_ACCOUNTS
        )
    Select acc.ID_ACCOUNTS
    From ACCOUNTS acc
    Where 
        acc.ID_ACCOUNTS Between @blaccr_accMinVal And @blaccr_accMaxVal
--         acc.ID_ACCOUNTS like @blaccr_accoutFilter
        And exists(
            Select
                aha1.ACCOUNTS_ID_ACCOUNTS
            From ACCOUNTS_has_ACCOUNTS aha1
            where acc.ID_ACCOUNTS = aha1.ACCOUNTS_ID_ACCOUNTS
            Limit 1
        )
        And exists(
            Select
                aha2.ACCOUNTS_ID_ACCOUNTS_LINK
            From ACCOUNTS_has_ACCOUNTS aha2
            where acc.ID_ACCOUNTS = aha2.ACCOUNTS_ID_ACCOUNTS_LINK
            Limit 1
        )
        
    ;

    Insert Into TMP_CIRCULAR_ACCOUNTS_1 (
            ACCOUNTS_ID_ACCOUNTS, ACCOUNTS_ID_ACCOUNTS_LINK
            , TMP_CIRCULAR_ACCOUNTS_LEVEL, TMP_CIRCULAR_ACCOUNTS_PREFIX
        )
    Select Distinct
        src.ACCOUNTS_ID_ACCOUNTS, src.ACCOUNTS_ID_ACCOUNTS_LINK
        , 0 LVL, Concat('.', src.ACCOUNTS_ID_ACCOUNTS, '.', src.ACCOUNTS_ID_ACCOUNTS_LINK, '.') PRFX
    From ACCOUNTS_has_ACCOUNTS src
        Inner Join TMP_CIRCULAR_ACCOUNTS_0 tmprootcirc
            On src.ACCOUNTS_ID_ACCOUNTS = tmprootcirc.ACCOUNTS_ID_ACCOUNTS
    ;
    Set @blaccr_levelLinkCount = (Select count(*) From TMP_CIRCULAR_ACCOUNTS_1);
    Set @blaccr_level = 0 ;

    -- boucle de traitement
    mainProcessLoop: Loop
--         if @blaccr_maxIeration <1 then
--             leave mainProcessLoop;
--         End if;
--         Set @blaccr_maxIeration = @blaccr_maxIeration - 1;
        -- select @blaccr_level, @blaccr_maxIeration, @blaccr_levelLinkCount;
        if @blaccr_level >= @blaccr_maxIeration Or @blaccr_levelLinkCount = 0 then 
            leave mainProcessLoop;
        End if;

        -- pas de truncate => autocomit
--         Truncate Table TMP_CIRCULAR_ACCOUNTS_2;
        Drop Temporary Table If Exists TMP_CIRCULAR_ACCOUNTS_2;
        Create Temporary Table TMP_CIRCULAR_ACCOUNTS_2 (
                    ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null
                    , ACCOUNTS_ID_ACCOUNTS_LINK Int Unsigned Not Null
                    , TMP_CIRCULAR_ACCOUNTS_LEVEL Int Not Null Default 0
                    , TMP_CIRCULAR_ACCOUNTS_PREFIX Varchar(2000) Not Null Default ''
                    , Index IX_TMP_CIRCULAR_ACCOUNTS_2_1(ACCOUNTS_ID_ACCOUNTS, ACCOUNTS_ID_ACCOUNTS_LINK)
                    , Index IX_TMP_CIRCULAR_ACCOUNTS_2_2(ACCOUNTS_ID_ACCOUNTS_LINK)
                )
            ;


        -- determinnation des lien niveau N+1
        Insert Into TMP_CIRCULAR_ACCOUNTS_2 (
            ACCOUNTS_ID_ACCOUNTS, ACCOUNTS_ID_ACCOUNTS_LINK
            , TMP_CIRCULAR_ACCOUNTS_LEVEL, TMP_CIRCULAR_ACCOUNTS_PREFIX
        )
        Select 
            src.ACCOUNTS_ID_ACCOUNTS, src.ACCOUNTS_ID_ACCOUNTS_LINK
            , tca1.TMP_CIRCULAR_ACCOUNTS_LEVEL + 1
            , Concat(tca1.TMP_CIRCULAR_ACCOUNTS_PREFIX, src.ACCOUNTS_ID_ACCOUNTS_LINK, '.')
        From TMP_CIRCULAR_ACCOUNTS_1 tca1
            Inner Join ACCOUNTS_has_ACCOUNTS src
                On tca1.ACCOUNTS_ID_ACCOUNTS_LINK = src.ACCOUNTS_ID_ACCOUNTS
        Where
            tca1.TMP_CIRCULAR_ACCOUNTS_LEVEL = @blaccr_level
        ;
        
        -- supression des liens deja présents
        -- si on supprime les lignes présentes on ne détectera plus les références circulaires
        -- => on ne suprimes les lignes existance que si la référence circulaire est détecté
        --      on regarde en plus le préfix pour le référence circulaire
        Delete tar 
        -- Select *
        From TMP_CIRCULAR_ACCOUNTS_2 tar
        Where
            exists (
                Select 
                    *
                From
                    TMP_CIRCULAR_ACCOUNTS_1 src
                Where
                    src.ACCOUNTS_ID_ACCOUNTS = tar.ACCOUNTS_ID_ACCOUNTS
                    And src.ACCOUNTS_ID_ACCOUNTS_LINK = tar.ACCOUNTS_ID_ACCOUNTS_LINK
                    -- uniquement si on a deja créer la référence circulaire
                    And (
                        src.TMP_CIRCULAR_ACCOUNTS_PREFIX like Concat('%.', src.ACCOUNTS_ID_ACCOUNTS, '.%.', src.ACCOUNTS_ID_ACCOUNTS, '.%')
                        Or src.TMP_CIRCULAR_ACCOUNTS_PREFIX like Concat('%.', src.ACCOUNTS_ID_ACCOUNTS_LINK, '.%.', src.ACCOUNTS_ID_ACCOUNTS_LINK, '.%')
                    )
            )
        ;

        -- insertion des nouveaux liens
        Insert Into TMP_CIRCULAR_ACCOUNTS_1 (
            ACCOUNTS_ID_ACCOUNTS, ACCOUNTS_ID_ACCOUNTS_LINK
            , TMP_CIRCULAR_ACCOUNTS_LEVEL, TMP_CIRCULAR_ACCOUNTS_PREFIX
        ) 
        Select distinct
            ACCOUNTS_ID_ACCOUNTS, ACCOUNTS_ID_ACCOUNTS_LINK
            , TMP_CIRCULAR_ACCOUNTS_LEVEL, TMP_CIRCULAR_ACCOUNTS_PREFIX
        From TMP_CIRCULAR_ACCOUNTS_2
        ;
--         Select * From TMP_CIRCULAR_ACCOUNTS_1 where TMP_CIRCULAR_ACCOUNTS_LEVEL = @blaccr_level;
--         Select * From TMP_CIRCULAR_ACCOUNTS_2;
--         Select @blaccr_level, @blaccr_levelLinkCount;
        Set @blaccr_level = @blaccr_level + 1;
--         Set @blaccr_levelLinkCount = ROW_COUNT(); -- ne fonctionne visiblement pas sur les tables temporaires
        Set @blaccr_levelLinkCount = (Select count(*) From TMP_CIRCULAR_ACCOUNTS_2);
    End loop mainProcessLoop;

    -- ici les références ciculaires sont determinées (si il y en a)
    Set ciculareRefCount = (
                Select count(*) 
                -- select *
                from TMP_CIRCULAR_ACCOUNTS_1 
                Where 
                    (TMP_CIRCULAR_ACCOUNTS_PREFIX like Concat('%.', ACCOUNTS_ID_ACCOUNTS, '.%.', ACCOUNTS_ID_ACCOUNTS, '.%') )
                    Or (TMP_CIRCULAR_ACCOUNTS_PREFIX like Concat('%.', ACCOUNTS_ID_ACCOUNTS_LINK, '.%.', ACCOUNTS_ID_ACCOUNTS_LINK, '.%'))
            )
        ;

    
    Set @blaccr_outputTableName = Coalesce(outputTable, 'TMP_CIRCULAR_ACCOUNTS');

    if outputTable Is Null then
        Set outputTable =  @blaccr_outputTableName;

        set @blaccr_queryDropTable = Concat(
                    'Drop Temporary Table If Exists '
                    , '`', @blaccr_outputTableName, '` '
                )
            ;
        PREPARE dropTblStmt FROM @blaccr_queryDropTable; 
        execute dropTblStmt ;
        Deallocate prepare dropTblStmt;

        -- create table sortie si nécéssaire
        set @blaccr_queryCreateTable = Concat(
                    'Create Temporary Table If Not Exists '
                    , '`', @blaccr_outputTableName, '` '
                    , '( '
                    , 'ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null '
                    , ', ACCOUNTS_ID_ACCOUNTS_LINK Int Unsigned Not Null '
    --                 , ', CIRCULAR_ACCOUNTS_LEVEL Int Not Null Default 0 '
                    , ', CIRCULAR_ACCOUNTS_PREFIX Varchar(2000) Not Null Default '''' '
                    , ', Index `IX_', @blaccr_outputTableName , '_1` (ACCOUNTS_ID_ACCOUNTS, ACCOUNTS_ID_ACCOUNTS_LINK) '
                    , ', Index `IX_', @blaccr_outputTableName , '_2` (ACCOUNTS_ID_ACCOUNTS_LINK) '
                    , ') '
                )
            ;

        PREPARE credTblStmt FROM @blaccr_queryCreateTable; 
        execute credTblStmt 
            ;
        Deallocate prepare credTblStmt; 				

    End if;

    
    if ciculareRefCount > 0 then
        set @blaccr_queryInsertTable = Concat(
                    'Insert Into '
                    , '`', @blaccr_outputTableName, '` ( '
                        , 'ACCOUNTS_ID_ACCOUNTS, ACCOUNTS_ID_ACCOUNTS_LINK '
    --                     , ', CIRCULAR_ACCOUNTS_LEVEL, CIRCULAR_ACCOUNTS_PREFIX '
                        , ', CIRCULAR_ACCOUNTS_PREFIX '
                    , ') '
                    , 'Select '
                        , 'ACCOUNTS_ID_ACCOUNTS, ACCOUNTS_ID_ACCOUNTS_LINK '
    --                     , ', TMP_CIRCULAR_ACCOUNTS_LEVEL, TMP_CIRCULAR_ACCOUNTS_PREFIX '
                        , ', max(TMP_CIRCULAR_ACCOUNTS_PREFIX) '
                    , 'From TMP_CIRCULAR_ACCOUNTS_1 '
                    , 'where '
                    , '(TMP_CIRCULAR_ACCOUNTS_PREFIX like Concat(''%.'', ACCOUNTS_ID_ACCOUNTS, ''.%.'', ACCOUNTS_ID_ACCOUNTS, ''.%'')) '
                    , ' Or (TMP_CIRCULAR_ACCOUNTS_PREFIX like Concat(''%.'', ACCOUNTS_ID_ACCOUNTS_LINK, ''.%.'', ACCOUNTS_ID_ACCOUNTS_LINK, ''.%'')) '
                    , 'Group By ACCOUNTS_ID_ACCOUNTS, ACCOUNTS_ID_ACCOUNTS_LINK '
                )
            ;

        PREPARE insertStmt FROM @blaccr_queryInsertTable; 
        execute insertStmt ;

        Deallocate prepare insertStmt; 				

        -- supression de la table TMP_CIRCULAR_ACCOUNTS_1 des référence ciculaire 
        -- afin de maj les prefix de l'arbre dans la table 
        Delete TMP_CIRCULAR_ACCOUNTS_1
        From TMP_CIRCULAR_ACCOUNTS_1
        Where 
             (TMP_CIRCULAR_ACCOUNTS_PREFIX like Concat('%.', ACCOUNTS_ID_ACCOUNTS, '.%.', ACCOUNTS_ID_ACCOUNTS, '.%')) 
             Or (TMP_CIRCULAR_ACCOUNTS_PREFIX like Concat('%.', ACCOUNTS_ID_ACCOUNTS_LINK, '.%.', ACCOUNTS_ID_ACCOUNTS_LINK, '.%'))
        ;
    End if;
    -- purge
--     Drop Temporary Table If Exists TMP_LINKED_ACCOUNTS_PREFIXES;
    Drop Temporary Table If Exists TMP_CIRCULAR_ACCOUNTS_0;
    Drop Temporary Table If Exists TMP_CIRCULAR_ACCOUNTS_1;
    Drop Temporary Table If Exists TMP_CIRCULAR_ACCOUNTS_2;

end //
Delimiter ;

