"""Unit tests for VEVO SQL executor."""
from unittest import mock
from unittest.mock import MagicMock
from unittest.mock import patch

import moto
import pytest
from snowflake import connector

from feed_ingestion.flows.vevo.snowflake_executor import VevoTheOrchard
from tests.conftest import SubstringMatcher


@pytest.fixture
def mock_executor(sf_config_mock, monkeypatch):
    """Yield executor context."""
    connect_mock = MagicMock()
    monkeypatch.setattr(connector, 'connect', connect_mock)
    executor = VevoTheOrchard(sf_config_mock)
    with patch.object(executor, 'execute', wraps=executor.execute) as \
            executor.ex_mock:
        executor.fetchall = MagicMock()
        yield executor


@pytest.fixture
def mock_boto3():
    """Mock boto3."""
    boto3_path = 'feed_ingestion.flows.vevo.snowflake_executor.boto3'

    with patch(boto3_path) as boto3:
        mock_session = MagicMock()
        mock_session.Session = MagicMock()
        boto3.Session.return_value = mock_session
        yield boto3


def test_create_temp_staging_raw_table(mock_executor):
    """Test create_temp_staging_raw_table method."""
    mock_executor.create_temp_staging_raw_table('temp_table_sme_20250101')

    mock_executor.ex_mock.assert_any_call(
        SubstringMatcher(
            containing=['test_db.test_schema', 'temp_table_sme_20250101']),
        params={})


@moto.mock_aws
def test_load_temp_staging_raw_table(
        mock_executor):
    """Test load_temp_staging_raw_table method."""
    kwargs = {'error_limit': 10}
    mock_executor.load_temp_staging_raw_table(
        'temp_table_20250101', None, 's3://somepath', **kwargs)

    mock_executor.fetchall.assert_any_call(
        SubstringMatcher(
            containing=[
                'COPY INTO', 'test_db.test_schema', 'temp_table_20250101']),
        params={'aws_key_id': mock.ANY, 'aws_secret_key': mock.ANY,
                'aws_token': mock.ANY, 's3_path': 's3://somepath'})


def test_load_staging_raw_table(mock_executor):
    """Test load_staging_raw_table method."""
    mock_executor.load_staging_raw_table(
        'temp_staging_raw_table_20171116', 'staging_raw_table', '2017-11-16')

    mock_executor.ex_mock.assert_any_call(
        SubstringMatcher(
            containing=[
                'test_db.test_schema', 'temp_staging_raw_table_20171116',
                'MERGE INTO test_db.test_schema.staging_raw_table']),
        params={'download_date': '2017-11-16', 'licensor': 'theorchard'})


def test_clean_staging_raw_table(mock_executor):
    """Test clean_staging_raw_table."""
    mock_executor.clean_staging_raw_table(
        'staging_raw_table', '2017-11-16')
    mock_executor.ex_mock.assert_any_call(
        SubstringMatcher(
            containing=[
                'DELETE FROM test_db.test_schema.staging_raw_table']),
        params={'date': '2017-11-16', 'licensor': 'theorchard'})