Delimiter //
/**
 * Author:  armen
 * Created: 22 oct. 2018
 */
Drop Procedure if Exists BUSINESS_LINKED_ACCOUNTS_BUILD_PREFIX_TREE //
Create Procedure BUSINESS_LINKED_ACCOUNTS_BUILD_PREFIX_TREE(
            In idaccounts Int unsigned
            , In maxIeration Int 
        )
Begin
    -- on utilise Greatest pour eviter les négatifs + un least pour avoir une valeur max
    Set @blabpt_maxIeration = Least(Greatest(Coalesce(maxIeration, 5), 3), 15); 
    -- 3 <= @blabpt_maxIeration <= 15


--     Set @blabpt_accoutFilter = coalesce(idaccounts, '%');
    if idaccounts Is null Then
        Set @blabpt_accMinVal = 0;
        Set @blabpt_accMaxVal = 4e7;
    Else
        Set @blabpt_accMinVal = idaccounts;
        Set @blabpt_accMaxVal = idaccounts;
    End if;

    -- creation tables de travail
    Drop Temporary Table If Exists TMP_LINKED_ACCOUNTS_PREFIX_TREE_0;
    Create Temporary Table TMP_LINKED_ACCOUNTS_PREFIX_TREE_0 (
                ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null
                , TMP_LINKED_ACCOUNTS_PREFIX_TREE_PREFIX Varchar(2000) Not Null Default ''
                , Index IX_TMP_LINKED_ACCOUNTS_PREFIX_TREE_0_1 (ACCOUNTS_ID_ACCOUNTS)
                , Index IX_TMP_LINKED_ACCOUNTS_PREFIX_TREE_0_2 (TMP_LINKED_ACCOUNTS_PREFIX_TREE_PREFIX)
            )
        ;
    
    Drop Temporary Table If Exists TMP_LINKED_ACCOUNTS_PREFIX_TREE_1;
    Create Temporary Table TMP_LINKED_ACCOUNTS_PREFIX_TREE_1 (
                ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null
                , ACCOUNTS_ID_ACCOUNTS_LINK Int Unsigned Not Null
                , TMP_LINKED_ACCOUNTS_PREFIX_TREE_LEVEL Int Not Null Default 0
                , TMP_LINKED_ACCOUNTS_PREFIX_TREE_PREFIX Varchar(2000) Not Null Default ''
                , Index IX_TMP_LINKED_ACCOUNTS_PREFIX_TREE_1_1 (ACCOUNTS_ID_ACCOUNTS, ACCOUNTS_ID_ACCOUNTS_LINK)
                , Index IX_TMP_LINKED_ACCOUNTS_PREFIX_TREE_1_2 (ACCOUNTS_ID_ACCOUNTS_LINK)
            )
        ;

    Drop Temporary Table If Exists TMP_LINKED_ACCOUNTS_PREFIX_TREE_2;
    Create Temporary Table TMP_LINKED_ACCOUNTS_PREFIX_TREE_2 (
                ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null
                , ACCOUNTS_ID_ACCOUNTS_LINK Int Unsigned Not Null
                , TMP_LINKED_ACCOUNTS_PREFIX_TREE_LEVEL Int Not Null Default 0
                , TMP_LINKED_ACCOUNTS_PREFIX_TREE_PREFIX Varchar(2000) Not Null Default ''
                , Index IX_TMP_LINKED_ACCOUNTS_PREFIX_TREE_2_1(ACCOUNTS_ID_ACCOUNTS, ACCOUNTS_ID_ACCOUNTS_LINK)
                , Index IX_TMP_LINKED_ACCOUNTS_PREFIX_TREE_2_2(ACCOUNTS_ID_ACCOUNTS_LINK)
            )
        ;
    
    -- Initialisation des préfix de la table des linked accounts
    -- 
    Update ACCOUNTS_has_ACCOUNTS tar
    Set
        tar.ACCOUNTS_has_ACCOUNTS_LEVEL = null
        , tar.ACCOUNTS_has_ACCOUNTS_PREFIX = null
    Where
        (
            (tar.ACCOUNTS_ID_ACCOUNTS Between @blabpt_accMinVal And @blabpt_accMaxVal)
            Or (tar.ACCOUNTS_ID_ACCOUNTS_LINK Between @blabpt_accMinVal And @blabpt_accMaxVal)
        )
--         tar.ACCOUNTS_ID_ACCOUNTS like @blabpt_accoutFilter
--         Or tar.ACCOUNTS_ID_ACCOUNTS_LINK like @blabpt_accoutFilter
    ;
    -- prépartion des données initiales
    -- liste des comptes de consolidation RACINE 
    -- compte consolidation RACINE = Compte de consolidation n'etant pas lui même lié 
    -- à une autre compte
    Insert Into TMP_LINKED_ACCOUNTS_PREFIX_TREE_0 (
            ACCOUNTS_ID_ACCOUNTS, TMP_LINKED_ACCOUNTS_PREFIX_TREE_PREFIX
        )
    Select Distinct
        src1.ACCOUNTS_ID_ACCOUNTS
        , Concat('.', src1.ACCOUNTS_ID_ACCOUNTS, '.') PRFX
    From ACCOUNTS_has_ACCOUNTS src1
    Where
        (
            (src1.ACCOUNTS_ID_ACCOUNTS Between @blabpt_accMinVal And @blabpt_accMaxVal)
            Or (src1.ACCOUNTS_ID_ACCOUNTS_LINK Between @blabpt_accMinVal And @blabpt_accMaxVal)
--             src1.ACCOUNTS_ID_ACCOUNTS like @blabpt_accoutFilter
--             Or src1.ACCOUNTS_ID_ACCOUNTS_LINK like @blabpt_accoutFilter
        )
        And Not Exists (
            Select src2.ACCOUNTS_ID_ACCOUNTS
            From ACCOUNTS_has_ACCOUNTS src2
            Where
                src2.ACCOUNTS_ID_ACCOUNTS_LINK = src1.ACCOUNTS_ID_ACCOUNTS
            Limit 1
        )
    ;

    -- liste des compte de consolidation de niveau 1
        Insert Into TMP_LINKED_ACCOUNTS_PREFIX_TREE_1 (
            ACCOUNTS_ID_ACCOUNTS, ACCOUNTS_ID_ACCOUNTS_LINK
            , TMP_LINKED_ACCOUNTS_PREFIX_TREE_LEVEL, TMP_LINKED_ACCOUNTS_PREFIX_TREE_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_LINKED_ACCOUNTS_PREFIX_TREE_0 tmprootacc
            On src.ACCOUNTS_ID_ACCOUNTS = tmprootacc.ACCOUNTS_ID_ACCOUNTS

        ;

    -- Set @blabpt_levelLinkCount = ROW_COUNT(); -- ne fonctionne visiblement pas sur les tables temporaires
    Set @blabpt_levelLinkCount = (Select count(*) From TMP_LINKED_ACCOUNTS_PREFIX_TREE_1);
    Set @blabpt_level = 0 ;


    -- boucle de traitement
    mainProcessLoop: Loop
--         if @blabpt_maxIeration <1 then
--             leave mainProcessLoop;
--         End if;
--         Set @blabpt_maxIeration = @blabpt_maxIeration - 1;
        -- select @blabpt_level, @blabpt_maxIeration, @blabpt_levelLinkCount;
        if @blabpt_level >= @blabpt_maxIeration Or @blabpt_levelLinkCount = 0 then 
            leave mainProcessLoop;
        End if;

--         Truncate Table TMP_LINKED_ACCOUNTS_PREFIX_TREE_2; -- pas de truncate => autocomit
        Drop Temporary Table If Exists TMP_LINKED_ACCOUNTS_PREFIX_TREE_2;
        Create Temporary Table TMP_LINKED_ACCOUNTS_PREFIX_TREE_2 (
                    ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null
                    , ACCOUNTS_ID_ACCOUNTS_LINK Int Unsigned Not Null
                    , TMP_LINKED_ACCOUNTS_PREFIX_TREE_LEVEL Int Not Null Default 0
                    , TMP_LINKED_ACCOUNTS_PREFIX_TREE_PREFIX Varchar(2000) Not Null Default ''
                    , Index IX_TMP_LINKED_ACCOUNTS_PREFIX_TREE_2_1(ACCOUNTS_ID_ACCOUNTS, ACCOUNTS_ID_ACCOUNTS_LINK)
                    , Index IX_TMP_LINKED_ACCOUNTS_PREFIX_TREE_2_2(ACCOUNTS_ID_ACCOUNTS_LINK)
                )
            ;
        -- determinnation des lien niveau N+1
        Insert Into TMP_LINKED_ACCOUNTS_PREFIX_TREE_2 (
            ACCOUNTS_ID_ACCOUNTS, ACCOUNTS_ID_ACCOUNTS_LINK
            , TMP_LINKED_ACCOUNTS_PREFIX_TREE_LEVEL, TMP_LINKED_ACCOUNTS_PREFIX_TREE_PREFIX
        )
        Select 
            src.ACCOUNTS_ID_ACCOUNTS
            , src.ACCOUNTS_ID_ACCOUNTS_LINK
            , tca1.TMP_LINKED_ACCOUNTS_PREFIX_TREE_LEVEL + 1
            , Concat(tca1.TMP_LINKED_ACCOUNTS_PREFIX_TREE_PREFIX, src.ACCOUNTS_ID_ACCOUNTS_LINK, '.')
        From TMP_LINKED_ACCOUNTS_PREFIX_TREE_1 tca1
            Inner Join ACCOUNTS_has_ACCOUNTS src
                On tca1.ACCOUNTS_ID_ACCOUNTS_LINK = src.ACCOUNTS_ID_ACCOUNTS
        Where
            tca1.TMP_LINKED_ACCOUNTS_PREFIX_TREE_LEVEL = @blabpt_level
        ;
        
        -- supression des liens deja présents
        Delete tar 
        -- select *
        From TMP_LINKED_ACCOUNTS_PREFIX_TREE_2 tar
        Where
            exists (
                Select 
                    *
                From
                    TMP_LINKED_ACCOUNTS_PREFIX_TREE_1 src
                Where
                    src.ACCOUNTS_ID_ACCOUNTS = tar.ACCOUNTS_ID_ACCOUNTS
                    And src.ACCOUNTS_ID_ACCOUNTS_LINK = tar.ACCOUNTS_ID_ACCOUNTS_LINK
            )
            -- ou références circulaires
            Or tar.TMP_LINKED_ACCOUNTS_PREFIX_TREE_PREFIX like Concat('%.', tar.ACCOUNTS_ID_ACCOUNTS, '.%.', tar.ACCOUNTS_ID_ACCOUNTS, '.%')
            Or tar.TMP_LINKED_ACCOUNTS_PREFIX_TREE_PREFIX like Concat('%.', tar.ACCOUNTS_ID_ACCOUNTS_LINK, '.%.', tar.ACCOUNTS_ID_ACCOUNTS_LINK, '.%')
        ;

        -- insertion des nouveaux liens
        Insert Into TMP_LINKED_ACCOUNTS_PREFIX_TREE_1 (
            ACCOUNTS_ID_ACCOUNTS, ACCOUNTS_ID_ACCOUNTS_LINK
            , TMP_LINKED_ACCOUNTS_PREFIX_TREE_LEVEL, TMP_LINKED_ACCOUNTS_PREFIX_TREE_PREFIX
        ) 
        Select distinct
            ACCOUNTS_ID_ACCOUNTS, ACCOUNTS_ID_ACCOUNTS_LINK
            , TMP_LINKED_ACCOUNTS_PREFIX_TREE_LEVEL, TMP_LINKED_ACCOUNTS_PREFIX_TREE_PREFIX
        From TMP_LINKED_ACCOUNTS_PREFIX_TREE_2
        ;
        Set @blabpt_level = @blabpt_level + 1;
--         Set @blabpt_levelLinkCount = ROW_COUNT(); -- ne fonctionne visiblement pas sur les tables temporaires
        Set @blabpt_levelLinkCount = (Select count(*) From TMP_LINKED_ACCOUNTS_PREFIX_TREE_2);
    End loop mainProcessLoop;


    -- ici les préfixes sont déterminés

    Update ACCOUNTS_has_ACCOUNTS tar
        Inner Join TMP_LINKED_ACCOUNTS_PREFIX_TREE_1 src
        On tar.ACCOUNTS_ID_ACCOUNTS = src.ACCOUNTS_ID_ACCOUNTS 
        And tar.ACCOUNTS_ID_ACCOUNTS_LINK = src.ACCOUNTS_ID_ACCOUNTS_LINK 
    Set
        tar.ACCOUNTS_has_ACCOUNTS_LEVEL = src.TMP_LINKED_ACCOUNTS_PREFIX_TREE_LEVEL
        , tar.ACCOUNTS_has_ACCOUNTS_PREFIX = src.TMP_LINKED_ACCOUNTS_PREFIX_TREE_PREFIX
    ;
--     
    -- purge
    Drop Temporary Table If Exists TMP_LINKED_ACCOUNTS_PREFIX_TREE_0;
    Drop Temporary Table If Exists TMP_LINKED_ACCOUNTS_PREFIX_TREE_1;
    Drop Temporary Table If Exists TMP_LINKED_ACCOUNTS_PREFIX_TREE_2;

end //
Delimiter ;


