Delimiter //
/**
 * Test for LOADER_CONTACTS_NORMALIZE_MAP_MAIN_PROVIDED()
 * @test
 */
Drop Procedure If Exists TEST_LOADER_CONTACTS_NORMALIZE_MAP_MAIN_FROMACCOUNT_PROVIDED//
Create Procedure TEST_LOADER_CONTACTS_NORMALIZE_MAP_MAIN_FROMACCOUNT_PROVIDED()
Begin

    -- environment
    Call LOADER_TEST_MOCK_INFO_CLEAN();
    Call LOADER_TEST_MOCK_INFO_PUSH('LOADER_TYPE', 'ACCOUNTS');

    -- business account table
    Call LOADER_TEST_MOCK_BUSINESS_CLEAN('ACCOUNTS');
        -- main contact exists
    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'
    );
        -- main contact doesn't exist
    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'
    );
    
    -- business contact table
    Call LOADER_TEST_MOCK_BUSINESS_CLEAN('CONTACTS');
    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()'
    );
    
    -- loader contacts and loader_contacts_accounts tables
    Call LOADER_TEST_MOCK_CONTACTS_CLEAN();
    Call LOADER_TEST_MOCK_CONTACTS_PUSH(1, 'IS_MAIN_CONTACT = 1, CONTACTS_REF = ''TEST1''', 1, Null); 
    Call LOADER_TEST_MOCK_CONTACTS_PUSH(2, 'IS_MAIN_CONTACT = 1, CONTACTS_REF = ''TEST2''', 2, Null); 
    Call LOADER_TEST_MOCK_CONTACTS_PUSH(3, 'IS_MAIN_CONTACT = 1, CONTACTS_REF = ''TEST3''', 3, Null); 
    Call LOADER_TEST_MOCK_CONTACTS_ACCOUNTS_CLEAN();

    -- loader_accounts_replication table
    Call LOADER_TEST_MOCK_ACCOUNTS_REPLICATION_CLEAN();
    Call LOADER_TEST_MOCK_ACCOUNTS_REPLICATION_PUSH(1, 'ID_ACCOUNTS = 2, ID_ACCOUNTS_NEW = Null', 1, Null);
    Call LOADER_TEST_MOCK_ACCOUNTS_REPLICATION_PUSH(2, 'ID_ACCOUNTS = 1, ID_ACCOUNTS_NEW = Null', 2, Null);
    Call LOADER_TEST_MOCK_ACCOUNTS_REPLICATION_PUSH(3, 'ID_ACCOUNTS = Null, ID_ACCOUNTS_NEW = 1', 3, Null);

    -- tested procedure
    Call LOADER_CONTACTS_NORMALIZE_MAP_MAIN_FROMACCOUNT_PROVIDED(1);

    -- assertions
    Call utAssert_eq('When contact is declared as main contact, and when updating account hasn''t already a main contact, this contact become the main contact for the account'
        , 1
        , (Select LOADER_STATUS_ID_LOADER_STATUS From LOADER_CONTACTS_ACCOUNTS Where ACCOUNTS_ID_ACCOUNTS = 2)
    );

    Call utAssert_eq('When contact is declared as main contact, but when updating account has already a main contact, row receives a warning'
        , 1
        , (Select LOADER_STATUS_WARNCOUNT From LOADER_STATUS Where ID_LOADER_STATUS = 2)
    );

    Call utAssert_eq('When contact is declared as main contact of a new account, this contact become the main contact for the account'
        , 3
        , (Select LOADER_STATUS_ID_LOADER_STATUS From LOADER_CONTACTS_ACCOUNTS Where ACCOUNTS_ID_ACCOUNTS_NEW = 1)
    );
    
    -- cleans
    Call LOADER_TEST_MOCK_ACCOUNTS_REPLICATION_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_INFO_CLEAN();

End//
Delimiter ;