Delimiter //
/**
 * Author:  armen
 * Created: 25 oct. 2018
 * recerche et recopiee les écritures des comptes liés abscant 
 * du compte de consolidation
 */
Drop Procedure if Exists BUSINESS_LINKED_ACCOUNTS_COPY_MISSING_ENTRIES //
Create Procedure BUSINESS_LINKED_ACCOUNTS_COPY_MISSING_ENTRIES(
            In idaccounts Int unsigned
            , In idaccountsLink Int unsigned
            , In maxIeration Int 
        )
Begin
    blacme_main: Begin
        -- on utilise Greatest pour eviter les négatifs + un least pour avoir une valeur max
        Set @blacme_maxIeration = Least(Greatest(Coalesce(maxIeration, 5), 3), 15); 
    --     -- 3 <= @blacme_maxIeration <= 15
    -- --     Set @blacme_accountFilter = coalesce(idaccounts, '%');
    -- --     Set @blacme_accountLinkFilter = coalesce(idaccountsLink, '%');
        Set @blacme_accountFilter = Case When idaccounts Is null Then '' else Concat(' And src.ACCOUNTS_ID_ACCOUNTS_0 = ', idaccounts , ' ') end ;
        Set @blacme_accountLinkFilter = Case When idaccountsLink Is null Then '' else Concat(' And eha.ACCOUNTS_ID_ACCOUNTS = ', idaccountsLink , ' ') end;

        call CASH_CHECK_TABLE_EXISTENCE('TMP_ACC_HAS_ACC', @blacme_fulltreetblexists); 
        if @blacme_fulltreetblexists = 0 Then
            call BUSINESS_LINKED_ACCOUNTS_CREATE_FULL_TREE(idaccounts, @blacme_maxIeration);
    --         call BUSINESS_LINKED_ACCOUNTS_CREATE_FULL_TREE(null, @blacme_maxIeration);
        End if;

        -- ici la table indexées des comptes liées est créé
        -- where sur plus d'une colonne ACCOUNTS_ID_ACCOUNTS fait chuter les perf
        -- le explain montre que mysql n'utilise plus l'index

        Drop Temporary Table If Exists TMP_ENTRIES_has_ACCOUNTS_REF;
        Create Temporary Table TMP_ENTRIES_has_ACCOUNTS_REF (
                    ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null
                    , ENTRIES_ID_ENTRIES Int Unsigned Not Null
                    , ENTRIES_ACC_EXT_REF Varchar(45) Not Null
                    , Index IX_TMP_ENTRIES_has_ACCOUNTS_TO_REF_1(ACCOUNTS_ID_ACCOUNTS)
                    , Index IX_TMP_ENTRIES_has_ACCOUNTS_TO_REF_2(ENTRIES_ID_ENTRIES)
                )
            ;
        Drop Temporary Table If Exists TMP_ENTRIES_has_ACCOUNTS_REF_FULL;
        Create Temporary Table  TMP_ENTRIES_has_ACCOUNTS_REF_FULL (
                    ACCOUNTS_ID_ACCOUNTS Int Unsigned Not Null
                    , ENTRIES_ID_ENTRIES Int Unsigned Not Null
                    , ENTRIES_ACC_EXT_REF Varchar(45) Not Null
                    , Index IX_TMP_ENTRIES_has_ACCOUNTS_TO_REF_1_FULL_1(ACCOUNTS_ID_ACCOUNTS)
                    , Index IX_TMP_ENTRIES_has_ACCOUNTS_TO_REF_2_FULL_2(ENTRIES_ID_ENTRIES)
                )
            ;

        -- on a pas besoin de tester ACCOUNTS_ID_ACCOUNTS_0 
        Set @blacme_it=1;
        Set @blacme_colCounts= @blacme_maxIeration + 1;
        inesertDataToRefLoop: Loop
            if @blacme_it > @blacme_colCounts then
                leave inesertDataToRefLoop;
            End if;

            Set @blacme_insertQuery = Concat(''
                            , 'Insert Into TMP_ENTRIES_has_ACCOUNTS_REF_FULL( '
                            , ' ACCOUNTS_ID_ACCOUNTS, ENTRIES_ID_ENTRIES, ENTRIES_ACC_EXT_REF '
                            , ') '
                            , 'Select '
                            , ' src.ACCOUNTS_ID_ACCOUNTS_0 ACCOUNTS_ID_ACCOUNTS '
                            , ' , eha.ENTRIES_ID_ENTRIES, eha.ENTRIES_ACC_EXT_REF '  
                            , 'From ENTRIES_has_ACCOUNTS eha '
                            , ' Inner Join TMP_ACC_HAS_ACC src '
                            , '  On eha.ACCOUNTS_ID_ACCOUNTS = src.ACCOUNTS_ID_ACCOUNTS_', @blacme_it, ' '
    --                         , '  And eha.ENTRIES_PERMANENT_ATTACHEMENT = 1 '
    --                         , @blacme_accountLinkFilter
                            , @blacme_accountFilter  
                            , '  Where eha.ENTRIES_PERMANENT_ATTACHEMENT = 1 '
                            , @blacme_accountLinkFilter
                    )
                ;
    --         select @blacme_insertQuery;
            PREPARE InsertRefDataStmt FROM @blacme_insertQuery; 
            execute InsertRefDataStmt ;

            Deallocate prepare InsertRefDataStmt;

            set @blacme_it = @blacme_it + 1;

        End loop inesertDataToRefLoop;


        -- dédoublonnage et détection des lignes manquantes
        Insert Into TMP_ENTRIES_has_ACCOUNTS_REF (
            ACCOUNTS_ID_ACCOUNTS, ENTRIES_ID_ENTRIES, ENTRIES_ACC_EXT_REF
        )
--         explain
        select src.ACCOUNTS_ID_ACCOUNTS, src.ENTRIES_ID_ENTRIES
            , min(src.ENTRIES_ACC_EXT_REF) ENTRIES_ACC_EXT_REF
        From TMP_ENTRIES_has_ACCOUNTS_REF_FULL src
            Left Join ENTRIES_has_ACCOUNTS eha1
                On src.ACCOUNTS_ID_ACCOUNTS = eha1.ACCOUNTS_ID_ACCOUNTS
                And src.ENTRIES_ID_ENTRIES = eha1.ENTRIES_ID_ENTRIES 
            Left Join ENTRIES_has_ACCOUNTS eha2
                On src.ACCOUNTS_ID_ACCOUNTS = eha2.ACCOUNTS_ID_ACCOUNTS
                And src.ENTRIES_ACC_EXT_REF = eha2.ENTRIES_ACC_EXT_REF 
        Where 1 = 1 
            And eha1.ACCOUNTS_ID_ACCOUNTS Is Null
            And eha2.ACCOUNTS_ID_ACCOUNTS Is Null
        group by src.ACCOUNTS_ID_ACCOUNTS, src.ENTRIES_ID_ENTRIES
        ;

--         --explain
--         select src.ACCOUNTS_ID_ACCOUNTS, src.ENTRIES_ID_ENTRIES
--             , min(ENTRIES_ACC_EXT_REF) ENTRIES_ACC_EXT_REF
--         From TMP_ENTRIES_has_ACCOUNTS_REF_FULL src
--         Where 1 = 1 
--             And Not Exists (
--                 Select eha.*
--                 From ENTRIES_has_ACCOUNTS eha
--                 Where
--                     eha.ACCOUNTS_ID_ACCOUNTS = src.ACCOUNTS_ID_ACCOUNTS
-- --                     And eha.ENTRIES_ID_ENTRIES = src.ENTRIES_ID_ENTRIES
--                     And (
--                         src.ENTRIES_ID_ENTRIES = eha.ENTRIES_ID_ENTRIES 
--                         Or src.ENTRIES_ACC_EXT_REF = eha.ENTRIES_ACC_EXT_REF 
--                     )
--                 Limit 1
--             )
--         group by src.ACCOUNTS_ID_ACCOUNTS, src.ENTRIES_ID_ENTRIES
--         ;

-- leave blacme_main;
        -- supression des reference de compte en doublons
        Drop Temporary Table If Exists TMP_DBL_ACC_EXT_REF;
        Create Temporary Table TMP_DBL_ACC_EXT_REF As
        Select ACCOUNTS_ID_ACCOUNTS, ENTRIES_ACC_EXT_REF
--             , count(*) nbr
        From TMP_ENTRIES_has_ACCOUNTS_REF
        Group By ACCOUNTS_ID_ACCOUNTS, ENTRIES_ACC_EXT_REF
        Having Count(*) > 1
        ;
        Alter Table TMP_DBL_ACC_EXT_REF 
                add index (ACCOUNTS_ID_ACCOUNTS)
                , add index (ENTRIES_ACC_EXT_REF)
            ;
        -- Supression des doublons
        Delete tar 
        -- select tar.*
        From TMP_ENTRIES_has_ACCOUNTS_REF tar
        Where
            Exists (
                Select ACCOUNTS_ID_ACCOUNTS, ENTRIES_ACC_EXT_REF
                From TMP_DBL_ACC_EXT_REF  dbls
                Where
                    tar.ACCOUNTS_ID_ACCOUNTS = dbls.ACCOUNTS_ID_ACCOUNTS 
                    And tar.ENTRIES_ACC_EXT_REF = dbls.ENTRIES_ACC_EXT_REF 
            )
        ;



        -- recopie des ectitures manquantes
        Insert Into ENTRIES_has_ACCOUNTS (
            ENTRIES_ID_ENTRIES, ACCOUNTS_ID_ACCOUNTS, ENTRIES_ACC_EXT_REF, ENTRIES_PERMANENT_ATTACHEMENT
        )
        -- drop temporary table if exists TMP_NEW_ENT_HAS_ENT; Create temporary Table TMP_NEW_ENT_HAS_ENT AS
        Select 
--             distinct
            src.ENTRIES_ID_ENTRIES, src.ACCOUNTS_ID_ACCOUNTS, 
            src.ENTRIES_ACC_EXT_REF, 0 ENTRIES_PERMANENT_ATTACHEMENT
        From
            TMP_ENTRIES_has_ACCOUNTS_REF src
        Where
            Not Exists (
                Select eha.ENTRIES_ID_ENTRIES 
                From ENTRIES_has_ACCOUNTS eha
                Where 
                    src.ACCOUNTS_ID_ACCOUNTS = eha.ACCOUNTS_ID_ACCOUNTS
                    And src.ENTRIES_ID_ENTRIES = eha.ENTRIES_ID_ENTRIES 
--                     And (
--                         src.ENTRIES_ID_ENTRIES = eha.ENTRIES_ID_ENTRIES 
--                         Or src.ENTRIES_ACC_EXT_REF = eha.ENTRIES_ACC_EXT_REF 
--                     )
    --                 And (
    --                     src.ENTRIES_ID_ENTRIES = eha.ENTRIES_ID_ENTRIES 
    --                     Or src.ENTRIES_ACC_EXT_REF = eha.ENTRIES_ACC_EXT_REF 
    --                 )
                Limit 1
            )
        ;
        -- alter table TMP_NEW_ENT_HAS_ENT add index(ENTRIES_ID_ENTRIES), add index(ACCOUNTS_ID_ACCOUNTS, ENTRIES_ACC_EXT_REF)
        -- purge 
        drop temporary table if exists TMP_ENTRIES_has_ACCOUNTS_REF_FULL;
        -- on ne supprime pas pour consultation 
    --     drop temporary table if exists TMP_ENTRIES_has_ACCOUNTS_REF;

    end;

end //
Delimiter ;