"""Tests for src/utils/db_utils.py - Database utility functions."""

from unittest.mock import MagicMock, call

import pytest

from src.utils.db_utils import (
    DBResultsIterable,
    calculate_batch_size,
    count_query_rows,
    count_table_rows,
    drop_table,
    get_result_dict,
    get_select_clause,
    merge_tables,
)


class TestDBResultsIterable:
    """Test DBResultsIterable class."""

    def test_init_invalid_batch_size_zero(self):
        """Test initialization with zero batch_size raises ValueError."""
        mock_conn = MagicMock()
        with pytest.raises(ValueError, match='batch_size must be positive'):
            DBResultsIterable(mock_conn, 'SELECT * FROM table', 0)

    def test_init_invalid_batch_size_negative(self):
        """Test initialization with negative batch_size raises ValueError."""
        mock_conn = MagicMock()
        with pytest.raises(ValueError, match='batch_size must be positive'):
            DBResultsIterable(mock_conn, 'SELECT * FROM table', -5)

    def test_init_valid_batch_size(self):
        """Test initialization with valid batch_size."""
        mock_conn = MagicMock()
        iterable = DBResultsIterable(mock_conn, 'SELECT * FROM table', 100)
        assert iterable._batch_size == 100
        assert iterable._conn == mock_conn
        assert iterable._query == 'SELECT * FROM table'
        assert iterable._cursor is None

    def test_iteration_with_results(self):
        """Test iteration through query results in batches."""
        mock_conn = MagicMock()
        mock_cursor = MagicMock()
        mock_conn.cursor.return_value = mock_cursor

        # Simulate two batches of results
        mock_cursor.fetchmany.side_effect = [
            [(1, 'a'), (2, 'b')],  # First batch
            [(3, 'c')],  # Second batch
            [],  # Empty signals end
        ]

        iterable = DBResultsIterable(mock_conn, 'SELECT * FROM table', 2)
        results = list(iterable)

        assert len(results) == 2
        assert results[0] == [(1, 'a'), (2, 'b')]
        assert results[1] == [(3, 'c')]
        mock_cursor.execute.assert_called_once_with('SELECT * FROM table')
        assert mock_cursor.fetchmany.call_count == 3
        mock_cursor.close.assert_called_once()

    def test_iteration_empty_results(self):
        """Test iteration with no results."""
        mock_conn = MagicMock()
        mock_cursor = MagicMock()
        mock_conn.cursor.return_value = mock_cursor
        mock_cursor.fetchmany.return_value = []

        iterable = DBResultsIterable(mock_conn, 'SELECT * FROM table', 10)
        results = list(iterable)

        assert results == []
        mock_cursor.execute.assert_called_once()
        mock_cursor.close.assert_called_once()

    def test_destroy_with_no_cursor(self):
        """Test _destroy when cursor is None."""
        mock_conn = MagicMock()
        iterable = DBResultsIterable(mock_conn, 'SELECT * FROM table', 10)
        iterable._destroy()  # Should not raise

    def test_destroy_with_cursor_exception(self):
        """Test _destroy handles cursor.close() exception."""
        mock_conn = MagicMock()
        mock_cursor = MagicMock()
        mock_cursor.close.side_effect = Exception('Close failed')

        iterable = DBResultsIterable(mock_conn, 'SELECT * FROM table', 10)
        iterable._cursor = mock_cursor
        iterable._destroy()  # Should not raise

        assert iterable._cursor is None


class TestCalculateBatchSize:
    """Test calculate_batch_size function."""

    def test_invalid_bytes_per_entry_zero(self):
        """Test with zero bytes_per_entry raises ValueError."""
        with pytest.raises(ValueError, match='bytes_per_entry must be positive'):
            calculate_batch_size(0, 100, 1000)

    def test_invalid_bytes_per_entry_negative(self):
        """Test with negative bytes_per_entry raises ValueError."""
        with pytest.raises(ValueError, match='bytes_per_entry must be positive'):
            calculate_batch_size(-10, 100, 1000)

    def test_invalid_base_query_length_negative(self):
        """Test with negative base_query_length raises ValueError."""
        with pytest.raises(ValueError, match='base_query_length must be nonnegative'):
            calculate_batch_size(10, -5, 1000)

    def test_invalid_safety_margin_below_zero(self):
        """Test with safety_margin_pct < 0 raises ValueError."""
        with pytest.raises(ValueError, match='safety_margin_pct must be within'):
            calculate_batch_size(10, 100, 1000, safety_margin_pct=-0.1)

    def test_invalid_safety_margin_above_one(self):
        """Test with safety_margin_pct > 1 raises ValueError."""
        with pytest.raises(ValueError, match='safety_margin_pct must be within'):
            calculate_batch_size(10, 100, 1000, safety_margin_pct=1.5)

    def test_invalid_max_query_length_zero(self):
        """Test with zero max_query_length raises ValueError."""
        with pytest.raises(ValueError, match='max_query_length must be positive'):
            calculate_batch_size(10, 100, 0)

    def test_invalid_max_query_length_negative(self):
        """Test with negative max_query_length raises ValueError."""
        with pytest.raises(ValueError, match='max_query_length must be positive'):
            calculate_batch_size(10, 100, -1000)

    def test_valid_calculation_no_max_batch_size(self):
        """Test valid calculation without max_batch_size constraint."""
        # max_query_length=1000, safety_margin=1.0, base_query=100
        # avail_bytes = 1000 * 1.0 - 100 = 900
        # batch_size = (900 + 1) / (10 + 1) = 901 / 11 = 81
        result = calculate_batch_size(10, 100, 1000, safety_margin_pct=1.0)
        assert result == 81

    def test_valid_calculation_with_max_batch_size(self):
        """Test valid calculation with max_batch_size constraint."""
        # Without constraint would be 81, but max_batch_size=50
        result = calculate_batch_size(
            10, 100, 1000, safety_margin_pct=1.0, max_batch_size=50
        )
        assert result == 50

    def test_valid_calculation_max_batch_size_higher(self):
        """Test max_batch_size higher than calculated doesn't affect result."""
        result = calculate_batch_size(
            10, 100, 1000, safety_margin_pct=1.0, max_batch_size=100
        )
        assert result == 81  # Calculated value is lower, so use it

    def test_calculation_with_safety_margin(self):
        """Test calculation with safety margin < 1.0."""
        # max_query_length=1000, safety_margin=0.8, base_query=100
        # avail_bytes = 1000 * 0.8 - 100 = 700
        # batch_size = (700 + 1) / (10 + 1) = 701 / 11 = 63
        result = calculate_batch_size(10, 100, 1000, safety_margin_pct=0.8)
        assert result == 63

    def test_calculation_returns_zero_when_insufficient_space(self):
        """Test returns 0 when base query is too large."""
        # base_query=1000, max_query=1000, safety=1.0
        # avail_bytes = 1000 - 1000 = 0
        # batch_size = 1 / 11 = 0 (truncated)
        result = calculate_batch_size(10, 1000, 1000, safety_margin_pct=1.0)
        assert result == 0


class TestCountQueryRows:
    """Test count_query_rows function."""

    def test_count_query_rows_with_results(self):
        """Test counting rows from query with results."""
        mock_cursor = MagicMock()
        mock_cursor.execute.return_value = mock_cursor
        mock_cursor.fetchone.return_value = (42,)

        result = count_query_rows(mock_cursor, 'SELECT * FROM users WHERE active=1')

        assert result == 42
        mock_cursor.execute.assert_called_once_with(
            'SELECT COUNT(*) FROM (SELECT * FROM users WHERE active=1) AS t'
        )

    def test_count_query_rows_empty_result(self):
        """Test counting rows when query returns None."""
        mock_cursor = MagicMock()
        mock_cursor.execute.return_value = mock_cursor
        mock_cursor.fetchone.return_value = None

        result = count_query_rows(mock_cursor, 'SELECT * FROM empty_table')

        assert result == 0


class TestCountTableRows:
    """Test count_table_rows function."""

    def test_count_table_rows_with_results(self):
        """Test counting rows from table with results."""
        mock_cursor = MagicMock()
        mock_cursor.execute.return_value = mock_cursor
        mock_cursor.fetchone.return_value = (100,)

        result = count_table_rows(mock_cursor, 'users')

        assert result == 100
        mock_cursor.execute.assert_called_once_with('SELECT COUNT(*) FROM "users"')

    def test_count_table_rows_empty_result(self):
        """Test counting rows when table returns None."""
        mock_cursor = MagicMock()
        mock_cursor.execute.return_value = mock_cursor
        mock_cursor.fetchone.return_value = None

        result = count_table_rows(mock_cursor, 'empty_table')

        assert result == 0


class TestDropTable:
    """Test drop_table function."""

    def test_drop_table_executes_both_commands(self):
        """Test drop_table executes DROP TABLE and DROP VIEW."""
        mock_cursor = MagicMock()

        drop_table(mock_cursor, 'test_table')

        assert mock_cursor.execute.call_count == 2
        calls = mock_cursor.execute.call_args_list
        assert calls[0] == call('DROP TABLE IF EXISTS "test_table"')
        assert calls[1] == call('DROP VIEW IF EXISTS "test_table"')


class TestGetResultDict:
    """Test get_result_dict function."""

    def test_get_result_dict_with_results(self):
        """Test converting cursor result to dictionary."""
        mock_cursor = MagicMock()
        mock_cursor.fetchone.return_value = (1, 'John', 'john@example.com')
        mock_cursor.description = [
            ('id', None, None, None, None, None, None),
            ('name', None, None, None, None, None, None),
            ('email', None, None, None, None, None, None),
        ]

        result = get_result_dict(mock_cursor)

        assert result == {'id': 1, 'name': 'John', 'email': 'john@example.com'}

    def test_get_result_dict_none_result(self):
        """Test get_result_dict with None result returns empty dict."""
        mock_cursor = MagicMock()
        mock_cursor.fetchone.return_value = None
        mock_cursor.description = [('id', None, None, None, None, None, None)]

        result = get_result_dict(mock_cursor)

        assert result == {}

    def test_get_result_dict_none_description(self):
        """Test get_result_dict with None description returns empty dict."""
        mock_cursor = MagicMock()
        mock_cursor.fetchone.return_value = (1,)
        mock_cursor.description = None

        result = get_result_dict(mock_cursor)

        assert result == {}


class TestGetSelectClause:
    """Test get_select_clause function."""

    def test_get_select_clause_no_alias_map(self):
        """Test returns '*' when alias_map is None."""
        result = get_select_clause(None)
        assert result == '*'

    def test_get_select_clause_empty_alias_map(self):
        """Test returns '*' when alias_map is empty."""
        result = get_select_clause({})
        assert result == '*'

    def test_get_select_clause_with_aliases(self):
        """Test builds SELECT clause with column aliases."""
        alias_map = {'user_id': 'id', 'user_name': 'name', 'user_email': 'email'}

        result = get_select_clause(alias_map)

        # Check each mapping is present
        assert '"id" AS "user_id"' in result
        assert '"name" AS "user_name"' in result
        assert '"email" AS "user_email"' in result
        assert result.count(', ') == 2  # Two commas for three items

    def test_get_select_clause_with_none_source(self):
        """Test handles None source columns as NULL."""
        alias_map = {'optional_field': None, 'name': 'full_name'}

        result = get_select_clause(alias_map)

        assert 'CAST(NULL AS VARCHAR) AS "optional_field"' in result
        assert '"full_name" AS "name"' in result


class TestMergeTables:
    """Test merge_tables function."""

    def test_merge_tables_empty_list(self):
        """Test merge_tables with empty sub_tables list."""
        mock_cursor = MagicMock()

        result = merge_tables(mock_cursor, 'merged', [])

        assert result == 0
        mock_cursor.execute.assert_not_called()

    def test_merge_tables_single_table(self):
        """Test merge_tables with single sub-table."""
        mock_cursor = MagicMock()
        mock_cursor.execute.return_value = mock_cursor
        mock_cursor.fetchone.return_value = (10,)

        result = merge_tables(mock_cursor, 'merged', ['temp1'])

        assert result == 10
        calls = mock_cursor.execute.call_args_list
        # Should create table from first sub-table
        assert 'CREATE TABLE "merged"' in calls[0][0][0]
        assert 'SELECT * FROM "temp1"' in calls[0][0][0]
        # Should drop the sub-table
        assert calls[1] == call('DROP TABLE "temp1"')
        # Should count rows
        assert calls[2] == call('SELECT COUNT(*) FROM "merged"')

    def test_merge_tables_multiple_tables(self):
        """Test merge_tables with multiple sub-tables."""
        mock_cursor = MagicMock()
        mock_cursor.execute.return_value = mock_cursor
        mock_cursor.fetchone.return_value = (100,)

        result = merge_tables(mock_cursor, 'merged', ['temp1', 'temp2', 'temp3'])

        assert result == 100
        calls = mock_cursor.execute.call_args_list

        # Should create table from first sub-table
        assert 'CREATE TABLE "merged"' in calls[0][0][0]
        assert 'SELECT * FROM "temp1"' in calls[0][0][0]
        assert calls[1] == call('DROP TABLE "temp1"')

        # Should insert from second table
        assert 'INSERT INTO "merged"' in calls[2][0][0]
        assert 'SELECT * FROM "temp2"' in calls[2][0][0]
        assert calls[3] == call('DROP TABLE "temp2"')

        # Should insert from third table
        assert 'INSERT INTO "merged"' in calls[4][0][0]
        assert 'SELECT * FROM "temp3"' in calls[4][0][0]
        assert calls[5] == call('DROP TABLE "temp3"')

        # Should count rows
        assert calls[6] == call('SELECT COUNT(*) FROM "merged"')