from unittest.mock import MagicMock, patch, ANY, call

import pytest
from jinja2 import Undefined

from common import sql_tasks

import re


class SubstringMatcher:
    """Class which allows to check for SQL calls by the list of substrings."""

    def __init__(self, containing):
        """Initialise object.

        Args:
            containing (list): A list of substrings to check.
        """
        self.containing = [el.lower() for el in containing]

    def __eq__(self, sql):
        """Equal magic method.

        Check if all the substrings from the passed list are present in SQL.

        Args:
            sql (str): SQL statement from a call.
        """
        sql = re.sub('\\s+', ' ', sql.lower()).strip()
        return all(el in sql for el in self.containing)

    def __repr__(self):
        """Represent string magic method to play nice with py.test messages."""
        return 'SQL containing: {}'.format(', '.join(self.containing))


def test_prepare_query():
    template = """
{% set and_joiner = joiner(" AND ") -%}
DELETE FROM {{ table_name|sqlsafe }}
{%- if where %}
WHERE
{% for column,value in where.items() %}{{ and_joiner()|sqlsafe }}{{ column|sqlsafe }} = {{ value }}
{% endfor %}
{% endif %}
""".strip()
    params = {
        'table_name': 'some_table_without_quotes',
        'where': {
            'user': 'QWER123',
            'name': 'Vasya',
        }
    }
    expected = '''DELETE FROM some_table_without_quotes
WHERE
user = %(value_1)s
 AND name = %(value_2)s

'''
    expected_params = {'value_1': 'QWER123', 'value_2': 'Vasya'}
    result = sql_tasks.prepare_query(template, params)
    assert result == (expected, expected_params)


def test_prepare_query_compatible():
    template = """
INSERT INTO %(db)i.%(schema)i.%(staging_fact_analytics_table)i (
  %(storeid)s AS storeid,
  AND srs.licensor = %(licensor)s
  New way {{ storeid }}
  Missed from params should stay missed %(missed)s
  Another occuerence: %(licensor)s
"""
    params = {
        'staging_fact_analytics_table': 'some_table',
        'db': 'database',
        'schema': 'some_schema',
        'storeid': 177,
        'licensor': 'unknown',
        'extra_param': 'should not be returned',
    }
    expected = """
INSERT INTO database.some_schema.some_table (
  %(storeid_1)s AS storeid,
  AND srs.licensor = %(licensor_2)s
  New way %(storeid_3)s
  Missed from params should stay missed %(missed_4)s
  Another occuerence: %(licensor_5)s"""
    expected_params = {
        'storeid_1': 177,
        'licensor_2': 'unknown',
        'storeid_3': 177,
        'missed_4': Undefined(),
        'licensor_5': 'unknown',
    }
    result = sql_tasks.prepare_query(template, params)
    assert result == (expected, expected_params)


class TestSQLTemplateOperator:
    def test_execute_template_file(monkeypatch):
        operator = sql_tasks.SQLTemplateOperator(
            task_id='123',
            template='create_table.sql',
            parameters={
                'table_name': 'sample_table',
            },
        )
        with patch.object(operator, 'get_db_hook') as db_hook_mock:
            context = MagicMock()
            result = operator.execute(context)

            assert result
            assert db_hook_mock.return_value.run.call_args_list == [
                call(sql=SubstringMatcher(['CREATE', 'sample_table'], ), autocommit=True, parameters=ANY)
            ]
    def test_execute_sql(monkeypatch):
        with pytest.raises(ValueError):
            sql_tasks.SQLTemplateOperator(
                task_id='123',
                template='',
                sql='CREATE TABLE',
                parameters={},
            )