"""Tests for src/connectors/mysql/utils.py - MySQL utility functions."""

from pathlib import Path
from unittest.mock import MagicMock

from src.connectors.mysql.utils import get_max_query_bytes, load_from_file, load_from_s3


class TestGetMaxQueryBytes:
    """Test get_max_query_bytes function."""

    def test_get_max_query_bytes_success(self):
        """Test fetching max_allowed_packet successfully."""
        mock_cursor = MagicMock()
        mock_cursor.execute.return_value = None
        mock_cursor.fetchone.return_value = ('max_allowed_packet', 67108864)

        result = get_max_query_bytes(mock_cursor)

        assert result == 67108864
        mock_cursor.execute.assert_called_once_with(
            "SHOW VARIABLES LIKE 'max_allowed_packet'"
        )

    def test_get_max_query_bytes_none_result(self):
        """Test returns None when no result from database."""
        mock_cursor = MagicMock()
        mock_cursor.execute.return_value = None
        mock_cursor.fetchone.return_value = None

        result = get_max_query_bytes(mock_cursor)

        assert result is None

    def test_get_max_query_bytes_converts_to_int(self):
        """Test result is converted to integer."""
        mock_cursor = MagicMock()
        mock_cursor.execute.return_value = None
        mock_cursor.fetchone.return_value = ('max_allowed_packet', '16777216')

        result = get_max_query_bytes(mock_cursor)

        assert result == 16777216
        assert isinstance(result, int)


class TestLoadFromFile:
    """Test load_from_file function."""

    def test_load_from_file_with_header(self):
        """Test loading CSV file with header."""
        mock_cursor = MagicMock()
        mock_cursor.execute.return_value = None
        mock_cursor.rowcount = 100

        file_path = '/tmp/test.csv'
        table_name = 'test_table'
        columns = ['id', 'name', 'email']

        result = load_from_file(
            mock_cursor, file_path, table_name, columns, has_header=True
        )

        assert result == 100
        mock_cursor.execute.assert_called_once()
        sql = mock_cursor.execute.call_args[0][0]

        # Verify SQL components
        assert f"LOAD DATA LOCAL INFILE '{file_path}'" in sql
        assert f'INTO TABLE `{table_name}`' in sql
        assert 'IGNORE 1 LINES' in sql
        assert '`id`, `name`, `email`' in sql
        assert "FIELDS TERMINATED BY ','" in sql
        assert "ENCLOSED BY '\"'" in sql
        assert "LINES TERMINATED BY '\n'" in sql

    def test_load_from_file_without_header(self):
        """Test loading CSV file without header."""
        mock_cursor = MagicMock()
        mock_cursor.execute.return_value = None
        mock_cursor.rowcount = 50

        file_path = '/data/users.csv'
        table_name = 'users'
        columns = ['user_id', 'username']

        result = load_from_file(
            mock_cursor, file_path, table_name, columns, has_header=False
        )

        assert result == 50
        sql = mock_cursor.execute.call_args[0][0]
        assert 'IGNORE 0 LINES' in sql

    def test_load_from_file_with_path_object(self):
        """Test loading file using Path object."""
        mock_cursor = MagicMock()
        mock_cursor.execute.return_value = None
        mock_cursor.rowcount = 25

        file_path = Path('/tmp/data.csv')
        table_name = 'data_table'
        columns = ['col1', 'col2']

        result = load_from_file(mock_cursor, file_path, table_name, columns)

        assert result == 25
        sql = mock_cursor.execute.call_args[0][0]
        assert f"LOAD DATA LOCAL INFILE '{file_path}'" in sql

    def test_load_from_file_single_column(self):
        """Test loading file with single column."""
        mock_cursor = MagicMock()
        mock_cursor.execute.return_value = None
        mock_cursor.rowcount = 10

        result = load_from_file(mock_cursor, '/tmp/single.csv', 'single_col', ['value'])

        assert result == 10
        sql = mock_cursor.execute.call_args[0][0]
        assert '`value`' in sql

    def test_load_from_file_multiple_columns(self):
        """Test loading file with multiple columns."""
        mock_cursor = MagicMock()
        mock_cursor.execute.return_value = None
        mock_cursor.rowcount = 200

        columns = ['id', 'first_name', 'last_name', 'email', 'phone', 'address']
        result = load_from_file(mock_cursor, '/tmp/multi.csv', 'contacts', columns)

        assert result == 200
        sql = mock_cursor.execute.call_args[0][0]
        for col in columns:
            assert f'`{col}`' in sql


class TestLoadFromS3:
    """Test load_from_s3 function."""

    def test_load_from_s3_with_header(self):
        """Test loading CSV from S3 with header."""
        mock_cursor = MagicMock()
        mock_cursor.execute.return_value = None
        mock_cursor.rowcount = 500

        s3_bucket = 'my-bucket'
        s3_key = 'data/file.csv'
        table_name = 's3_table'
        columns = ['id', 'value']

        result = load_from_s3(
            mock_cursor, s3_bucket, s3_key, table_name, columns, has_header=True
        )

        assert result == 500
        mock_cursor.execute.assert_called_once()
        sql = mock_cursor.execute.call_args[0][0]

        # Verify SQL components
        assert "LOAD DATA FROM S3 's3://my-bucket/data/file.csv'" in sql
        assert f'INTO TABLE `{table_name}`' in sql
        assert 'IGNORE 1 LINES' in sql
        assert '`id`, `value`' in sql

    def test_load_from_s3_without_header(self):
        """Test loading CSV from S3 without header."""
        mock_cursor = MagicMock()
        mock_cursor.execute.return_value = None
        mock_cursor.rowcount = 1000

        result = load_from_s3(
            mock_cursor,
            'bucket',
            'path/to/data.csv',
            'table',
            ['col1'],
            has_header=False,
        )

        assert result == 1000
        sql = mock_cursor.execute.call_args[0][0]
        assert 'IGNORE 0 LINES' in sql

    def test_load_from_s3_key_with_special_chars(self):
        """Test S3 key with single quotes is properly escaped."""
        mock_cursor = MagicMock()
        mock_cursor.execute.return_value = None
        mock_cursor.rowcount = 10

        s3_key = "data/file's_name.csv"  # Contains single quote

        result = load_from_s3(mock_cursor, 'bucket', s3_key, 'table', ['col'])

        assert result == 10
        sql = mock_cursor.execute.call_args[0][0]
        # Single quote should be escaped
        assert "file\\'s_name.csv" in sql

    def test_load_from_s3_nested_path(self):
        """Test S3 key with nested path."""
        mock_cursor = MagicMock()
        mock_cursor.execute.return_value = None
        mock_cursor.rowcount = 75

        result = load_from_s3(
            mock_cursor,
            'data-bucket',
            'year=2024/month=01/day=15/data.csv',
            'daily_data',
            ['metric', 'value'],
        )

        assert result == 75
        sql = mock_cursor.execute.call_args[0][0]
        assert 's3://data-bucket/year=2024/month=01/day=15/data.csv' in sql

    def test_load_from_s3_multiple_columns(self):
        """Test loading from S3 with multiple columns."""
        mock_cursor = MagicMock()
        mock_cursor.execute.return_value = None
        mock_cursor.rowcount = 300

        columns = ['user_id', 'order_id', 'product', 'quantity', 'price']
        result = load_from_s3(
            mock_cursor, 'orders-bucket', 'orders.csv', 'orders', columns
        )

        assert result == 300
        sql = mock_cursor.execute.call_args[0][0]
        expected_cols = '`user_id`, `order_id`, `product`, `quantity`, `price`'
        assert expected_cols in sql

    def test_load_from_s3_verifies_encoding(self):
        """Test that UTF-8 encoding is specified."""
        mock_cursor = MagicMock()
        mock_cursor.execute.return_value = None
        mock_cursor.rowcount = 0

        load_from_s3(mock_cursor, 'bucket', 'key', 'table', ['col'])

        sql = mock_cursor.execute.call_args[0][0]
        assert 'CHARACTER SET utf8mb4' in sql