Delimiter //
/**
 * @test
 * Test For LOADER_CONTACTS_NORMALIZE_MAP_TITLE_PROVIDED()
 */
Drop Procedure IF EXISTS TEST_LOADER_CONTACTS_PHONE_PROVIDED_OR_NOT//

Create Procedure TEST_LOADER_CONTACTS_PHONE_PROVIDED_OR_NOT()
Begin

    -- initial dataset
    Call LOADER_TEST_MOCK_CONTACTS_CLEAN();
    Call LOADER_TEST_MOCK_BUSINESS_CLEAN('CONTACTS_PHONES');

    Call LOADER_TEST_MOCK_CONTACTS_PUSH(1, 'ID_CONTACTS = 1, CONTACTS_PHONE1 = ''NEW PHONE1''', 1, Null);
    Call LOADER_TEST_MOCK_CONTACTS_PUSH(2, 'ID_CONTACTS = 1, CONTACTS_PHONE2 = ''NEW PHONE2''', 2, Null);
    Call LOADER_TEST_MOCK_CONTACTS_PUSH(3, 'ID_CONTACTS = 1, CONTACTS_PHONE3 = ''NEW PHONE3''', 3, Null);
    Call LOADER_TEST_MOCK_CONTACTS_PUSH(4, 'ID_CONTACTS = 1, CONTACTS_PHONE1 = ''EXISTS''', 4, Null);
    Call LOADER_TEST_MOCK_CONTACTS_PUSH(5, 'ID_CONTACTS = 1, CONTACTS_PHONE1 = ''''', 5, Null);

    Call LOADER_TEST_MOCK_BUSINESS_PUSH('CONTACTS_PHONES',
            'ID_CONTACTS_PHONES = 1
            , CONTACTS_ID_CONTACTS = 1
            , DF_PHONE_TYP_ID_DF_PHONE_TYP =1
            , DF_CHECK_STAT_ID_DF_CHECK_STAT =1
            , CONTACTS_PHONE_NUMBER =''EXISTS''
            , CONTACTS_PHONE_NUMERIC_ONLY = ''EXISTS''
            , CONTACTS_PHONE_PREFERENCE = 1
            , CONTACTS_PHONES_TRASHED = 0
            , CONTACTS_PHONES_LAST_UPDATE = NOW()'
    );

    -- Tested procedure
    call LOADER_CONTACTS_NORMALIZE_MAP_PHONE_PROVIDED(1);        

     -- Assertions
    Set @default_phone_state = 1; -- DOCHECK
    Set @default_phone_type = 5; -- RELOADED

    CALL utAssert_eq('When an existent phone is provided, nothing is changed'
        , NULL
        , (SELECT ID_NEW_CONTACTS_PHONES FROM LOADER_CONTACTS_PHONES WHERE LOADER_STATUS_ID_LOADER_STATUS = 4)
    );
    CALL utAssert_eq('When an new phone is provided, it is created'
        , 1
        , (SELECT ID_NEW_CONTACTS_PHONES FROM LOADER_CONTACTS_PHONES WHERE LOADER_STATUS_ID_LOADER_STATUS = 1)
    );
    CALL utAssert_eq('When an new phone is provided, it is created'
        , 2
        , (SELECT ID_NEW_CONTACTS_PHONES FROM LOADER_CONTACTS_PHONES WHERE LOADER_STATUS_ID_LOADER_STATUS = 2)
    );
    CALL utAssert_eq('When an new phone is provided, it is created with the state "DOCHECK" (1)'
        , @default_phone_state
        , (SELECT DF_CHECK_STAT_ID_DF_CHECK_STAT  FROM LOADER_CONTACTS_PHONES WHERE 
            CONTACTS_PHONES_TRASHED = 0 
            AND LOADER_STATUS_ID_LOADER_STATUS = 1
            AND CONTACTS_PHONE_NUMBER = 'NEW PHONE1')
    );
    CALL utAssert_eq('When an new phone is provided, it is created with the type "RELOADED" (5)'
        , @default_phone_type
        , (SELECT DF_PHONE_TYP_ID_DF_PHONE_TYP  FROM LOADER_CONTACTS_PHONES WHERE 
            CONTACTS_PHONES_TRASHED = 0 
            AND LOADER_STATUS_ID_LOADER_STATUS = 1
            AND CONTACTS_PHONE_NUMBER = 'NEW PHONE1')
    );
    CALL utAssert_eq('3 phones are created when 3 phones are given'
        , 3
        , (SELECT count(*) FROM LOADER_CONTACTS_PHONES WHERE LOADER_STATUS_ID_LOADER_STATUS in (1,2,3))
    );

    -- Clean
    Call LOADER_TEST_MOCK_BUSINESS_CLEAN('CONTACTS_PHONES');
    Call LOADER_TEST_MOCK_CONTACTS_CLEAN();

End //
Delimiter ;
