"""Tests for sql_templating utility."""
import jinja2
import pytest

from feed_ingestion.util import sql_templating


class TestAthenaEngine:
    def test_identifier_double_quoted(self):
        """Test that Athena identifiers are double-quoted."""
        query, params = sql_templating.render(
            'SELECT * FROM {{ db | identifier }}.{{ table | identifier }}',
            engine='athena',
            params={'db': 'mydb', 'table': 'my_table'},
        )
        assert query == 'SELECT * FROM "mydb"."my_table"'
        assert params == ()

    def test_identifier_and_value_expansion(self):
        """Test full query with identifier and value."""
        query, params = sql_templating.render(
            'SELECT * FROM {{ db | identifier }}.{{ table | identifier }}'
            ' WHERE date = {{ date }}',
            engine='athena',
            params={'db': 'my_db', 'table': 'my_table', 'date': '2024-01-15'},
        )
        assert query == 'SELECT * FROM "my_db"."my_table" WHERE date = ?'
        assert params == ("'2024-01-15'",)

    def test_value_becomes_qmark(self):
        """Test that Athena values become ? placeholders."""
        query, params = sql_templating.render(
            'WHERE x = {{ x }} AND y = {{ y }}',
            engine='athena',
            params={'x': 'hello', 'y': 42},
        )
        assert query == 'WHERE x = ? AND y = ?'
        assert params == ("'hello'", '42')

    def test_params_ordered(self):
        """Test that bind params are returned in template order."""
        query, params = sql_templating.render(
            'WHERE a = {{ a }} AND b = {{ b }} AND c = {{ c }}',
            engine='athena',
            params={'a': 1, 'b': 2, 'c': 3},
        )
        assert params == ('1', '2', '3')

    @pytest.mark.parametrize(
        'partition,expected_params,expected_clauses,absent',
        [
            (
                {'cadence': 'daily', 'region': 'us-east-1'},
                ("'2024-01-15'", "'daily'", "'us-east-1'"),
                ['AND "cadence" = ?', 'AND "region" = ?'],
                [],
            ),
            ({}, ("'2024-01-15'",), [], ['AND']),
        ],
    )
    def test_partition_loop(
        self, partition, expected_params, expected_clauses, absent
    ):
        """Test partition loop with and without partition keys."""
        template = (
            'WHERE date = {{ date }}\n'
            '{% for key, val in partition.items() %}\n'
            '    AND {{ key | identifier }} = {{ val }}\n'
            '{% endfor %}'
        )
        query, params = sql_templating.render(
            template,
            engine='athena',
            params={'date': '2024-01-15', 'partition': partition},
        )
        for clause in expected_clauses:
            assert clause in query
        for s in absent:
            assert s not in query
        assert params == expected_params

    @pytest.mark.parametrize(
        'value,expected_param',
        [
            ('hello', "'hello'"),
            ("it's", "'it''s'"),
            (42, '42'),
            (3.14, '3.14'),
            (True, 'true'),
            (False, 'false'),
            (None, 'NULL'),
        ],
    )
    def test_bind_filter(self, value, expected_param):
        query, params = sql_templating.render(
            'EXECUTE my_query USING {{ val }}',
            engine='athena',
            params={'val': value},
        )
        assert query == 'EXECUTE my_query USING ?'
        assert params == (expected_param,)


class TestSnowflakeEngine:
    @pytest.mark.parametrize(
        'identifier,expected_sql',
        [
            ('mydb', 'SELECT * FROM mydb'),
            ('"My Table"', 'SELECT * FROM "My Table"'),
            ('"my""table"', 'SELECT * FROM "my""table"'),
        ],
    )
    def test_identifier_valid(self, identifier, expected_sql):
        """Test that valid Snowflake identifiers pass through unchanged."""
        query, params = sql_templating.render(
            'SELECT * FROM {{ table | identifier }}',
            engine='snowflake',
            params={'table': identifier},
        )
        assert query == expected_sql
        assert params == {}

    @pytest.mark.parametrize(
        'identifier,error_match',
        [
            ('123bad', 'invalid Snowflake identifier'),
            ('', 'must not be empty'),
            ('""', 'must not be empty'),
            ('"bad"quote"', 'unescaped double quote'),
            ('a' * 256, 'exceeds Snowflake maximum length'),
        ],
    )
    def test_identifier_invalid(self, identifier, error_match):
        """Test that invalid Snowflake identifiers raise ValueError."""
        with pytest.raises(ValueError, match=error_match):
            sql_templating.render(
                'SELECT * FROM {{ table | identifier }}',
                engine='snowflake',
                params={'table': identifier},
            )

    @pytest.mark.parametrize(
        'params, error_match',
        [
            ({'value': '1'}, "'table' is undefined"),
            ({}, "'table' is undefined"),
        ],
    )
    def test_identifier_undefined(self, params, error_match):
        with pytest.raises(jinja2.exceptions.UndefinedError,
                           match=error_match):
            sql_templating.render(
                'SELECT * FROM {{ table | identifier }} WHERE c = {{ value }}',
                engine='snowflake',
                params=params,
            )

    def test_identifier_and_value_expansion(self):
        query, params = sql_templating.render(
            'SELECT * FROM {{ db | identifier }}.{{ table | identifier }}'
            ' WHERE date = {{ date }}',
            engine='snowflake',
            params={'db': 'my_db', 'table': 'my_table', 'date': '2024-01-15'},
        )
        assert query == 'SELECT * FROM my_db.my_table WHERE date = %(date_1)s'
        assert params == {'date_1': '2024-01-15'}

    def test_value_becomes_pyformat(self):
        """Test that Snowflake values become %(name)s placeholders."""
        query, params = sql_templating.render(
            'WHERE x = {{ x }} AND y = {{ y }}',
            engine='snowflake',
            params={'x': 'hello', 'y': 42},
        )
        assert query == 'WHERE x = %(x_1)s AND y = %(y_2)s'
        assert params == {'x_1': 'hello', 'y_2': 42}


def test_render_invalid_engine():
    """Test that an unsupported engine raises ValueError."""
    with pytest.raises(ValueError, match='Unsupported engine'):
        sql_templating.render('SELECT 1', engine='mysql', params={})