def test_suggested_multiple_column_names(completer):
    result = get_result(
        completer, 'SELECT id,  from custom.products', len('SELECT id, ')
    )
    assert completions_to_set(result) == completions_to_set(testdata.columns_functions_and_keywords(
        'products', 'custom'
    ))

def test_all_schema_objects_with_aliases(completer):
    text = ('SELECT * FROM ')
    result = get_result(completer, text)
    assert completions_to_set(result) >= completions_to_set(
        [table(x) for x in ('orders o', '"select" s', 'custom.shipments s')]
        + [function(x) for x in ('func2() f',)]
    )

def test_suggested_aliases_after_on_right_side(completer, text):
    position = len(
        'SELECT u.name, o.id FROM users u JOIN orders o ON o.user_id = '
    )
    result = get_result(completer, text, position)
    assert completions_to_set(result) == completions_to_set(
        [alias('u'), alias('o')])

def test_user_function_name_completion_matches_anywhere(completer):
    result = get_result(completer, 'SELECT om')
    assert completions_to_set(result) == completions_to_set([
        function('custom_fun()', -2),
        function('_custom_fun()', -2),
        function('custom_func1()', -2),
        function('custom_func2()', -2)])

def test_suggested_join_conditions(completer, text):
    result = get_result(completer, text)
    assert completions_to_set(result) == completions_to_set([
        alias('users'),
        alias('shipments'),
        name_join('shipments.id = users.id'),
        fk_join('shipments.user_id = users.id')])

def test_aliased_joins(completer, text):
    result = get_result(completer, text)
    assert completions_to_set(result) == completions_to_set(testdata.schemas() + aliased_rels + [
        join('"Users" U ON U.userid = Users.id'),
        join('users u ON u.id = Users.parentid'),
        join('users u ON u.parentid = Users.id'),
    ])

def test_suggest_columns_from_aliased_set_returning_function(completer):
    result = get_result(
        completer, 'select f. from set_returning_func() f', len('select f.')
    )
    assert completions_to_set(result) == completions_to_set(
        testdata.columns('set_returning_func', typ='functions')
    )

def test_cased_joins(completer, text):
    result = get_result(completer, text)
    assert completions_to_set(result) == completions_to_set([schema('PUBLIC')] + cased_rels + [
        join('"Users" ON "Users".UserID = Users.ID'),
        join('Users Users2 ON Users2.ID = Users.PARENTID'),
        join('Users Users2 ON Users2.PARENTID = Users.ID'),
    ])

def test_join_functions_using_suggests_common_columns(completer):
    text = '''SELECT * FROM set_returning_func() f1
              INNER JOIN set_returning_func() f2 USING ('''
    result = get_result(completer, text)
    assert completions_to_set(result) == completions_to_set(
        testdata.columns('set_returning_func', typ='functions')
    )

def test_suggested_column_names_from_schema_qualifed_table(completer):
    result = get_result(
        completer, 'SELECT  from custom.products', len('SELECT ')
    )
    assert completions_to_set(result) == completions_to_set(testdata.columns_functions_and_keywords(
        'products', 'custom'
    ))

def test_suggest_columns_from_set_returning_function(completer):
    result = get_result(
        completer, 'select  from set_returning_func()', len('select ')
    )
    assert completions_to_set(result) == completions_to_set(testdata.columns_functions_and_keywords(
        'set_returning_func', typ='functions'
    ))

def test_suggested_auto_qualified_column_names_two_tables(text, completer):
    position = text.index('  ') + 1
    cols = [column('U.' + c.lower()) for c in cased_users_col_names]
    cols += [column('"Users".' + c.lower()) for c in cased_users2_col_names]
    result = get_result(completer, text, position)
    assert completions_to_set(result) == completions_to_set(
        cols + testdata.functions_and_keywords())

def test_all_schema_objects_with_casing(completer):
    text = 'SELECT * FROM '
    result = get_result(completer, text)
    assert completions_to_set(result) >= completions_to_set(
        [table(x) for x in ('Orders', '"select"', 'CUSTOM.shipments')]
        + [function(x + '()') for x in ('func2',)]
    )

def test_function_alias_search_with_aliases(completer):
    text = 'SELECT blog.ee'
    result = get_result(completer, text)
    first = result[0]
    assert first.start_position == -2
    assert first.text == 'enter_entry(_title := , _text := )'
    assert first.display_text == 'enter_entry(_title, _text)'

def test_function_alias_search_without_aliases(completer):
    text = 'SELECT blog.ees'
    result = get_result(completer, text)
    first = result[0]
    assert first.start_position == -3
    assert first.text == 'extract_entry_symbols()'
    assert first.display_text == 'extract_entry_symbols(_entryid)'

def test_suggested_multiple_column_names_with_alias(completer):
    result = get_result(
        completer,
        'SELECT p.id, p. from custom.products p',
        len('SELECT u.id, u.')
    )
    assert completions_to_set(result) == completions_to_set(
        testdata.columns('products', 'custom'))

def test_suggested_aliases_after_on(completer, text):
    position = len('SELECT u.name, o.id FROM users u JOIN orders o ON ')
    result = get_result(completer, text, position)
    assert completions_to_set(result) == completions_to_set([
        alias('u'),
        name_join('o.id = u.id'),
        name_join('o.email = u.email'),
        alias('o')])

def test_table_aliases(completer, text):
    result = get_result(completer, text)
    assert completions_to_set(result) == completions_to_set(testdata.schemas() + [
        table('users u'),
        table('orders o' if text == 'SELECT * FROM ' else 'orders o2'),
        table('"select" s'),
        function('func1() f'),
        function('func2() f')])

def test_set_schema(completer):
    text = ('SET SCHEMA ')
    result = get_result(completer, text)
    assert completions_to_set(result) == completions_to_set([
        schema(u"'blog'"),
        schema(u"'Custom'"),
        schema(u"'custom'"),
        schema(u"'public'")])

def test_table_casing(completer, text):
    result = get_result(completer, text)
    assert completions_to_set(result) == completions_to_set(cased_schemas + [
        table('users'),
        table('Orders'),
        table('"select"'),
        function('Func1()'),
        function('func2()')])