"""
Idea for this test is to validate SqlTemplate output.

Test Cases store generated SQL files in this directory:
- filename: query_<query_name_without_sql>_<test_case_name>.actual.sql
Then it compares the output with expected file content:
- filename: query_<query_name_without_sql>_<test_case_name>.expected.sql

"""
from pathlib import Path
from unittest.mock import MagicMock

import boto3
import pytest

from common import sql_tasks

THIS_DIR = Path(__file__).parent

aws_creds = MagicMock()
aws_creds.access_key = 'AKEY'
aws_creds.secret_key = 'SECRETKEY'
aws_creds.token = None

file_format = r'''
    SKIP_HEADER=1
    FIELD_DELIMITER=','
    RECORD_DELIMITER='\n'
    TRIM_SPACE=False
    EMPTY_FIELD_AS_NULL=True
    FIELD_OPTIONALLY_ENCLOSED_BY='"'
'''


@pytest.mark.parametrize(
    'template_name, test_case_name, template_params, expected_params',
    [
        [
            'create_table',
            'with_columns',
            dict(
                table_name='sample_table',
                or_replace=True,
                cluster_by='id, name',
                comment='Test table',
                columns=[
                    dict(
                        name='id',
                        type='number',
                    ),
                    dict(
                        name='name',
                        type='number',
                        comment='User name',
                    ),
                ],
            ),
            {
                'column.comment_1': 'User name',
                'comment_2': 'Test table'
            }
        ],
        [
            'create_table',
            'like',
            dict(
                table_name='sample_table',
                like='other_table',
                or_replace=False,
                transient=True,
            ),
            {}
        ],
        [
            'create_table',
            'clone',
            dict(
                table_name='sample_table',
                clone='cloned_table',
                or_replace=True,
                transient=True,
            ),
            {}
        ],
        [
            'copy_into',
            'with_format',
            dict(
                table_name='sample_table',
                from_s3='s3://bucket/path/',
                files=['file1.cvs.gz', 'file2.csv'],
                file_format=file_format,
                aws_creds=aws_creds,
                validation_mode='RETURN_ERRORS'
            ),
            {
                'aws_creds.access_key_2': 'AKEY',
                'aws_creds.secret_key_3': 'SECRETKEY',
                'from_s3_1': 's3://bucket/path/',
                'inclause_4': 'file1.cvs.gz',
                'inclause_5': 'file2.csv'
            }
        ],
        [
            'delete_from_table',
            'with_where',
            dict(
                table_name='cleaned_table',
                where={
                    'col1': 'str',
                    'col2': 15,
                }
            ),
            {'value_1': 'str', 'value_2': 15}
        ],
        [
            'delete_from_table',
            'with_where_in',
            dict(
                table_name='cleaned_table',
                where={
                    'col1': ['str', 'str2'],
                    'col2': [15, 18],
                }
            ),
            {'inclause_1': 'str', 'inclause_2': 'str2', 'inclause_3': 15, 'inclause_4': 18}
        ],
        [
            'delete_from_table',
            'all_data',
            dict(
                table_name='cleaned_table',
            ),
            {}
        ],
        [
            'drop_table',
            'default',
            dict(
                table_name='dropped_table',
            ),
            {}
        ],
        [
            'drop_table',
            'with_if_exists',
            dict(
                table_name='dropped_table',
                if_exists=True,
            ),
            {}
        ],
    ]
)
def test_query_template(template_name, test_case_name, template_params, expected_params):
    query_file_name_template = 'query_{template_name}_{test_case_name}.{kind}.sql'
    format_args = dict(
        template_name=template_name,
        test_case_name=test_case_name,
    )
    query_actual_path = THIS_DIR / query_file_name_template.format(kind='actual', **format_args)
    query_expected_path = THIS_DIR / query_file_name_template.format(kind='expected', **format_args)

    template_name = sql_tasks.COMMON_QUERIES_DIR / f'{template_name}.sql'
    template = template_name.read_text()
    result = sql_tasks.prepare_query(template, template_params)
    query_actual = result[0]
    query_actual_path.write_text(query_actual)

    assert query_expected_path.exists(), f"Expected file missing. You can copy from {query_actual_path.name} and fix it if required"
    query_expected = query_expected_path.read_text()
    assert query_actual == query_expected

    query_params = result[1]
    assert query_params == expected_params

    query_actual_path.unlink()