Delimiter //
/**
 * @test
 * Test For LOADER_CONTACTS_COPY() 
 */
Drop Procedure if exists TEST_LOADER_CONTACTS_COPY_CONTACTS //
Create Procedure TEST_LOADER_CONTACTS_COPY_CONTACTS ()
Begin
    
    -- initial dataset
    Call LOADER_TEST_MOCK_INFO_CLEAN();
    Call LOADER_TEST_MOCK_CONTACTS_ACCOUNTS_CLEAN();
    Call LOADER_TEST_MOCK_CONTACTS_CLEAN();

    Call LOADER_TEST_MOCK_BUSINESS_CLEAN('CONTACTS');
    Call LOADER_TEST_MOCK_BUSINESS_CLEAN('ACCOUNTS');    
    Call LOADER_TEST_MOCK_BUSINESS_CLEAN('ENTITIES');

    CALL LOADER_TEST_MOCK_CONTACTS_PUSH(1, '`ACCOUNTS_ID_ACCOUNTS` = 1, `CONTACTS_REF` = ''CNTCT-REF-1'', `ID_CONTACTS` = 1, `ID_CONTACTS_NEW` = null, `IS_MAIN_CONTACT` = 0, `DF_LANGUAGES_ID_DF_LANGUAGES` = 1, `DF_SAND_STATE_ID_DF_SAND_STATE` = 2, `CONTACTS_TRASHED` = 0', 1, NULL);
    CALL LOADER_TEST_MOCK_CONTACTS_PUSH(2, '`ACCOUNTS_ID_ACCOUNTS` = 1, `CONTACTS_REF` = ''CNTCT-REF-2'', `ID_CONTACTS` = Null, `ID_CONTACTS_NEW` = 10, `IS_MAIN_CONTACT` = 0, `DF_LANGUAGES_ID_DF_LANGUAGES` = 1, `DF_SAND_STATE_ID_DF_SAND_STATE` = 1, `CONTACTS_TRASHED` = 0', 2, NULL);
    CALL LOADER_TEST_MOCK_CONTACTS_PUSH(3, '`ACCOUNTS_ID_ACCOUNTS` = 1, `CONTACTS_REF` = ''CNTCT-REF-3'', `ID_CONTACTS` = Null, `ID_CONTACTS_NEW` = 2, `IS_MAIN_CONTACT` = 0, `DF_LANGUAGES_ID_DF_LANGUAGES` = 1, `DF_SAND_STATE_ID_DF_SAND_STATE` = 1, `CONTACTS_TRASHED` = 0', 3, NULL);
    CALL LOADER_TEST_MOCK_CONTACTS_PUSH(4, '`ACCOUNTS_ID_ACCOUNTS` = 2, `CONTACTS_REF` = ''CNTCT-REF-4'', `ID_CONTACTS` = 4, `ID_CONTACTS_NEW` = null, `IS_MAIN_CONTACT` = 0, `DF_LANGUAGES_ID_DF_LANGUAGES` = 1, `DF_SAND_STATE_ID_DF_SAND_STATE` = 1, `CONTACTS_TRASHED` = 0', 4, NULL);
--     CALL LOADER_TEST_MOCK_CONTACTS_PUSH(3, '`ACCOUNTS_ID_ACCOUNTS` = 1, `CONTACTS_REF` = ''CNTCT-REF-3'', `ID_CONTACTS` = Null, `ID_CONTACTS_NEW` = 2, `IS_MAIN_CONTACT` = 0, `DF_COUNTRY_LABEL` = Null, `DF_COUNTRY_SHORT_CODE` = Null, `DF_COUNTRY_LONG_CODE` = Null, `DF_COUNTRY_ID_DF_COUNTRY` = Null, `CONTACTS_ADDRESSES_LINE_1` = Null, `CONTACTS_ADDRESSES_LINE_2` = Null, `CONTACTS_ADDRESSES_LINE_3` = Null, `CONTACTS_ADDRESSES_LINE_4` = Null, `CONTACTS_ADDRESSES_ZIP_CODE` = ''************'', `CONTACTS_ADDRESSES_CITY` = Null, `DF_CHECK_STAT_ID_DF_CHECK_STAT` = Null, `DF_TITLES_SHORT_TITLE` = Null, `DF_TITLES_FULL_TITLE` = Null, `DF_TITLES_ID_DF_TITLES` = Null, `CONTACTS_INDIVIDUAL_FIRST_NAME` = Null, `CONTACTS_INDIVIDUAL_LAST_NAME` = Null, `CONTACTS_INDIVIDUAL_BIRTH_NAME` = Null, `CONTACTS_PHONE1` = Null, `CONTACTS_PHONE2` = Null, `CONTACTS_PHONE3` = Null, `CONTACTS_EMAILS_EMAIL` = Null, `CONTACTS_BUSINESS_COMPANY_NAME` = Null, `CONTACTS_BUSINESS_BRAND_NAME` = Null, `CONTACTS_BUSINESS_SIRET` = Null, `DF_LANGUAGES_CODE` = Null, `DF_LANGUAGES_LABEL` = Null, `DF_LANGUAGES_ID_DF_LANGUAGES` = 1, `DF_FUNCTIONS_SHORT_LABEL` = Null, `DF_FUNCTIONS_ID_DF_FUNCTIONS` = Null, `DF_SAND_STATE_ID_DF_SAND_STATE` = 1, `CONTACTS_TRASHED` = Null, `CONTACTS_LAST_UPDATE` = Null', 3, NULL);

    Call LOADER_TEST_MOCK_PUSH('CONTACTS_ACCOUNTS', 2
            , '`ACCOUNTS_ID_ACCOUNTS` = 1
            , `ACCOUNTS_ID_ACCOUNTS_NEW` = null'
            ,2, null
    );
    Call LOADER_TEST_MOCK_PUSH('CONTACTS_ACCOUNTS', 4
            , '`ACCOUNTS_ID_ACCOUNTS` = 2
            , `ACCOUNTS_ID_ACCOUNTS_NEW` = null'
            ,4, null
    );
--     Call LOADER_TEST_MOCK_PUSH('CONTACTS_ACCOUNTS', 1, '`ACCOUNTS_ID_ACCOUNTS` = Null, `ACCOUNTS_ID_ACCOUNTS_NEW` = Null',1, null)

-- 
    Call LOADER_TEST_MOCK_INFO_PUSH('ID_ENTITIES', 1);

    Call LOADER_TEST_MOCK_BUSINESS_PUSH('ENTITIES', 
            'ID_ENTITIES = 1
            , DF_CURRENCIES_ID_DF_CURR = 1
            , ADMIN_ID_ADMIN = 1
            , CONT_BUSINESS_ID_CONT_BUSINESS = 1
            , ENTITIES_LOCK_STATE = 0
            , ENTITIES_CODE = 1'
    );
    Call LOADER_TEST_MOCK_BUSINESS_PUSH('ACCOUNTS', 
            'ID_ACCOUNTS = 1
            , ENTITIES_ID_ENTITIES = 1
            , DF_CURRENCIES_ID_DF_CURRENCIES = 1
            , DF_ACC_CATEG_ID_DF_ACC_CATEG = 3
            , DF_PRIORITY_ID_DF_PRIORITY = 2
            , DF_SAND_STATE_ID_DF_SAND_STATE = 2
            , ACCOUNTS_TYPE = 1
            , CONTACTS_ID_CONTACTS = 1
            , ACCOUNTS_REF = ''ACCNT-REF-1''
            , ACCOUNTS_CREATED = NOW()
            , ACCOUNTS_LAST_EVENT_UPDATE = NOW()
            , ACCOUNTS_PROCESSUS_SCAN_TYPE = 1
            , ACCOUNTS_ACC_MNGT_LAST_LVL = 0'
    );
    Call LOADER_TEST_MOCK_BUSINESS_PUSH('ACCOUNTS', 
            'ID_ACCOUNTS = 2
            , ENTITIES_ID_ENTITIES = 1
            , DF_CURRENCIES_ID_DF_CURRENCIES = 1
            , DF_ACC_CATEG_ID_DF_ACC_CATEG = 3
            , DF_PRIORITY_ID_DF_PRIORITY = 2
            , DF_SAND_STATE_ID_DF_SAND_STATE = 2
            , ACCOUNTS_TYPE = 1
            , CONTACTS_ID_CONTACTS = null
            , ACCOUNTS_REF = ''ACCNT-REF-2''
            , ACCOUNTS_CREATED = NOW()
            , ACCOUNTS_LAST_EVENT_UPDATE = NOW()
            , ACCOUNTS_PROCESSUS_SCAN_TYPE = 1
            , ACCOUNTS_ACC_MNGT_LAST_LVL = 0'
    );

    Call LOADER_TEST_MOCK_BUSINESS_PUSH('CONTACTS', 
            'ID_CONTACTS = 1
            , ACCOUNTS_ID_ACCOUNTS = 1
            , DF_LANGUAGES_ID_DF_LANGUAGE = 1
            , DF_SAND_STATE_ID_DF_SAND_STATE = 2
            , CONTACTS_REF = ''CNTCT-REF-1''
            , CONTACTS_TRASHED = 0
            , CONTACTS_LAST_UPDATE = NOW()'
    );

    Call LOADER_TEST_MOCK_BUSINESS_PUSH('CONTACTS', 
            'ID_CONTACTS = 4
            , ACCOUNTS_ID_ACCOUNTS = 2
            , DF_LANGUAGES_ID_DF_LANGUAGE = 1
            , DF_SAND_STATE_ID_DF_SAND_STATE = 2
            , CONTACTS_REF = ''CNTCT-REF-4''
            , CONTACTS_TRASHED = 0
            , CONTACTS_LAST_UPDATE = NOW()'
    );


    call LOADER_CONTACTS_UPDATE(1);
    call LOADER_CONTACTS_INSERT(1, 1);

    Call utAssert_eq('New contacts are Added to existants contacts'
            , 4
            , (Select count(*) From CONTACTS)
        );
-- 
    Call utAssert_eq('Existing contacts Are preserved'
            , 1
            , (Select ID_CONTACTS From CONTACTS Where ACCOUNTS_ID_ACCOUNTS = 1 And CONTACTS_REF = 'CNTCT-REF-1')
        );
-- 
    Call utAssert_eq('Not null Fields on existing Accounts are updated'
            , 1
            , (Select DF_LANGUAGES_ID_DF_LANGUAGE From CONTACTS Where ACCOUNTS_ID_ACCOUNTS = 1 And CONTACTS_REF = 'CNTCT-REF-1')
        );

    Call utAssert_eq('Null Fields on existing Accounts are unchanged'
            , 1
            , (Select DF_LANGUAGES_ID_DF_LANGUAGE From CONTACTS Where ACCOUNTS_ID_ACCOUNTS = 1 And CONTACTS_REF = 'CNTCT-REF-1')
        );

    Call utAssert_eq('new Contacts ID are expected values (1)'
            , 11
            , (Select ID_CONTACTS From CONTACTS Where ACCOUNTS_ID_ACCOUNTS = 1 And CONTACTS_REF = 'CNTCT-REF-2')
        );
    Call utAssert_eq('new Accounts ID are expected values (2)'
            , 3
            , (Select ID_CONTACTS From CONTACTS Where ACCOUNTS_ID_ACCOUNTS = 1 And CONTACTS_REF = 'CNTCT-REF-3')
        );


    Call utAssert_eq('Existing accounts main contact updated whith new contact'
            , 11
            , (Select CONTACTS_ID_CONTACTS From ACCOUNTS Where ID_ACCOUNTS = 1)
        );
    Call utAssert_eq('Existing accounts main contact update with existing contact'
            , 4
            , (Select CONTACTS_ID_CONTACTS From ACCOUNTS Where ID_ACCOUNTS = 2)
        );
--     Call utAssert_eq('New accounts main contact update'
--             , 11
--             , (Select CONTACTS_ID_CONTACTS From ACCOUNTS Where ID_ACCOUNTS = 1)
--         );


    -- clean
    call LOADER_TEST_MOCK_CONTACTS_ACCOUNTS_CLEAN();
    Call LOADER_TEST_MOCK_CONTACTS_CLEAN();
    Call LOADER_TEST_MOCK_INFO_CLEAN();

    Call LOADER_TEST_MOCK_BUSINESS_CLEAN('CONTACTS');
    Call LOADER_TEST_MOCK_BUSINESS_CLEAN('ACCOUNTS');
    Call LOADER_TEST_MOCK_BUSINESS_CLEAN('ENTITIES');
    

End //

Delimiter ;