"""Unit tests for table boundary detection."""

import pytest
from unittest.mock import Mock, patch, MagicMock
from openpyxl import Workbook
from openpyxl.cell.cell import Cell

from src.extraction.table_detector import TableDetector
from src.extraction.models import TableBoundary


@pytest.fixture
def detector():
    """Create a TableDetector instance."""
    return TableDetector()


class TestTableBoundaryModel:
    """Test TableBoundary data model."""

    def test_table_boundary_creation(self):
        """Should create valid TableBoundary."""
        boundary = TableBoundary(
            sheet="Sheet1",
            table_id=1,
            start_row=3,
            end_row=150,
            start_col="A",
            end_col="F",
        )

        assert boundary.sheet == "Sheet1"
        assert boundary.table_id == 1
        assert boundary.start_row == 3
        assert boundary.end_row == 150
        assert boundary.start_col == "A"
        assert boundary.end_col == "F"

    def test_table_boundary_validation_start_greater_than_end(self):
        """Should raise ValueError if start_row > end_row."""
        with pytest.raises(ValueError, match="start_row.*must be <= end_row"):
            TableBoundary(
                sheet="Sheet1",
                table_id=1,
                start_row=150,
                end_row=3,
                start_col="A",
                end_col="F",
            )

    def test_table_boundary_validation_invalid_table_id(self):
        """Should raise ValueError if table_id < 1."""
        with pytest.raises(ValueError, match="table_id must be >= 1"):
            TableBoundary(
                sheet="Sheet1",
                table_id=0,
                start_row=3,
                end_row=150,
                start_col="A",
                end_col="F",
            )

    def test_table_boundary_to_dict(self):
        """Should convert TableBoundary to dictionary."""
        boundary = TableBoundary(
            sheet="Sheet1",
            table_id=1,
            start_row=3,
            end_row=150,
            start_col="A",
            end_col="F",
        )

        result = boundary.to_dict()

        assert result == {
            "sheet": "Sheet1",
            "table_id": 1,
            "start_row": 3,
            "end_row": 150,
            "start_col": "A",
            "end_col": "F",
            "title": None,
        }


class TestStandardTableDetection:
    """Test heuristic detection for standard table layouts."""

    def test_detect_standard_table(self, detector):
        """Should detect a standard table with header and data rows."""
        wb = Workbook()
        sheet = wb.active
        sheet.title = "Sheet1"

        # Add header row
        sheet.append(["Name", "Age", "City", "Country"])

        # Add data rows
        sheet.append(["Alice", 30, "New York", "USA"])
        sheet.append(["Bob", 25, "London", "UK"])
        sheet.append(["Charlie", 35, "Paris", "France"])

        tables = detector.detect_tables(wb)

        assert len(tables) == 1
        assert tables[0].sheet == "Sheet1"
        assert tables[0].table_id == 1
        assert tables[0].start_row == 1
        assert tables[0].end_row == 4
        assert tables[0].start_col == "A"
        assert tables[0].end_col == "D"

    def test_detect_table_with_empty_rows_above_header(self, detector):
        """Should skip empty rows and detect table starting at header."""
        wb = Workbook()
        sheet = wb.active
        sheet.title = "Sheet1"

        # Add empty rows
        sheet.append([None, None, None])
        sheet.append([None, None, None])

        # Add header row
        sheet.append(["Name", "Age", "City"])

        # Add data rows
        sheet.append(["Alice", 30, "New York"])
        sheet.append(["Bob", 25, "London"])

        tables = detector.detect_tables(wb)

        assert len(tables) == 1
        assert tables[0].start_row == 3  # Header row
        assert tables[0].end_row == 5


class TestMultipleTablesDetection:
    """Test detection of multiple tables on one sheet."""

    def test_detect_multiple_tables_separated_by_blank_rows(self, detector):
        """Should detect multiple tables separated by 2+ blank rows."""
        wb = Workbook()
        sheet = wb.active
        sheet.title = "Sheet1"

        # First table
        sheet.append(["Name", "Age"])
        sheet.append(["Alice", 30])
        sheet.append(["Bob", 25])

        # Blank rows separator
        sheet.append([None, None])
        sheet.append([None, None])

        # Second table
        sheet.append(["Product", "Price", "Stock"])
        sheet.append(["Widget", 10.99, 100])
        sheet.append(["Gadget", 25.50, 50])

        tables = detector.detect_tables(wb)

        assert len(tables) == 2

        # First table
        assert tables[0].table_id == 1
        assert tables[0].start_row == 1
        assert tables[0].end_row == 3

        # Second table
        assert tables[1].table_id == 2
        assert tables[1].start_row == 6
        assert tables[1].end_row == 8


class TestMergedCellHandling:
    """Test handling of merged cells in title rows."""

    def test_skip_merged_title_row(self, detector):
        """Should skip merged title rows and detect table below."""
        wb = Workbook()
        sheet = wb.active
        sheet.title = "Sheet1"

        # Add title row with merged cells
        sheet.append(["REPORT TITLE"])
        sheet.merge_cells("A1:D1")

        # Add header row
        sheet.append(["Name", "Age", "City", "Country"])

        # Add data rows
        sheet.append(["Alice", 30, "New York", "USA"])

        tables = detector.detect_tables(wb)

        assert len(tables) == 1
        # Should start at header row, not title row
        assert tables[0].start_row == 2


class TestEdgeCases:
    """Test edge case handling."""

    def test_empty_sheet_returns_empty_list(self, detector):
        """Should return empty list for empty sheet."""
        wb = Workbook()
        sheet = wb.active
        sheet.title = "Empty"

        tables = detector.detect_tables(wb)

        assert tables == []

    def test_single_row_sheet_treated_as_header_only(self, detector):
        """Should treat single-row sheet as header-only table."""
        wb = Workbook()
        sheet = wb.active
        sheet.title = "Sheet1"

        sheet.append(["Name", "Age", "City"])

        tables = detector.detect_tables(wb)

        assert len(tables) == 1
        assert tables[0].start_row == 1
        assert tables[0].end_row == 1

    def test_blank_sheet_returns_empty_list(self, detector):
        """Should return empty list for fully blank sheet."""
        wb = Workbook()
        sheet = wb.active
        sheet.title = "Blank"

        # Add blank rows
        sheet.append([None, None, None])
        sheet.append([None, None, None])

        tables = detector.detect_tables(wb)

        assert tables == []

    def test_no_clear_structure_best_effort_detection(self, detector):
        """Should attempt best-effort detection for unclear structure."""
        wb = Workbook()
        sheet = wb.active
        sheet.title = "Sheet1"

        # Add rows with no clear header pattern (all numbers)
        sheet.append([1, 2, 3])
        sheet.append([4, 5, 6])
        sheet.append([7, 8, 9])

        tables = detector.detect_tables(wb)

        # Should return best-effort: entire sheet as one table
        assert len(tables) == 1
        assert tables[0].start_row == 1
        assert tables[0].end_row == 3


class TestLLMAssistedDetection:
    """Test LLM-assisted boundary detection."""

    @patch("src.extraction.table_detector.AzureOpenAI")
    @patch("src.extraction.table_detector.settings")
    def test_llm_detection_success(self, mock_settings, mock_azure_client, detector):
        """Should use LLM to detect boundaries when called."""
        # Setup mock settings
        mock_settings.azure_openai_api_key = "test-key"
        mock_settings.azure_openai_endpoint = "https://test.openai.azure.com/"
        mock_settings.azure_openai_api_version = "2024-02-15-preview"
        mock_settings.azure_openai_deployment = "gpt-4o"

        # Setup mock LLM response
        mock_client_instance = MagicMock()
        mock_azure_client.return_value = mock_client_instance

        mock_response = MagicMock()
        mock_response.choices = [
            MagicMock(
                message=MagicMock(
                    content='{"tables": [{"header_row": 2, "data_start_row": 3, "estimated_end": "continue to end", "start_col": "A", "end_col": "D"}]}'
                )
            )
        ]
        mock_response.usage = MagicMock(
            prompt_tokens=100, completion_tokens=50, total_tokens=150
        )
        mock_client_instance.chat.completions.create.return_value = mock_response

        # Create test workbook
        wb = Workbook()
        sheet = wb.active
        sheet.title = "Sheet1"
        sheet.append(["Name", "Age", "City", "Country"])
        sheet.append(["Alice", 30, "New York", "USA"])

        # Call LLM detection
        result = detector._llm_detect_boundaries(sheet, document_id="test-doc")

        assert result is not None
        assert len(result) == 1
        assert result[0]["start_row"] == 2
        assert result[0]["start_col"] == "A"
        assert result[0]["end_col"] == "D"

    @patch("src.extraction.table_detector.settings")
    def test_llm_detection_fallback_when_no_config(self, mock_settings, detector):
        """Should return None when Azure OpenAI not configured."""
        # No API key configured
        mock_settings.azure_openai_api_key = ""
        mock_settings.azure_openai_endpoint = ""

        wb = Workbook()
        sheet = wb.active
        sheet.title = "Sheet1"

        result = detector._llm_detect_boundaries(sheet)

        assert result is None

    @patch("src.extraction.table_detector.AzureOpenAI")
    @patch("src.extraction.table_detector.settings")
    def test_llm_detection_fallback_on_timeout(
        self, mock_settings, mock_azure_client, detector
    ):
        """Should return None and fall back to heuristics on timeout."""
        mock_settings.azure_openai_api_key = "test-key"
        mock_settings.azure_openai_endpoint = "https://test.openai.azure.com/"

        # Simulate timeout
        mock_client_instance = MagicMock()
        mock_azure_client.return_value = mock_client_instance
        mock_client_instance.chat.completions.create.side_effect = TimeoutError(
            "Request timed out"
        )

        wb = Workbook()
        sheet = wb.active
        sheet.title = "Sheet1"

        result = detector._llm_detect_boundaries(sheet)

        assert result is None

    @patch("src.extraction.table_detector.AzureOpenAI")
    @patch("src.extraction.table_detector.settings")
    def test_llm_detection_fallback_on_connection_error(
        self, mock_settings, mock_azure_client, detector
    ):
        """Should return None and fall back to heuristics on connection error."""
        mock_settings.azure_openai_api_key = "test-key"
        mock_settings.azure_openai_endpoint = "https://test.openai.azure.com/"

        # Simulate connection error
        mock_client_instance = MagicMock()
        mock_azure_client.return_value = mock_client_instance
        mock_client_instance.chat.completions.create.side_effect = ConnectionError(
            "Connection failed"
        )

        wb = Workbook()
        sheet = wb.active
        sheet.title = "Sheet1"

        result = detector._llm_detect_boundaries(sheet)

        assert result is None


class TestHelperMethods:
    """Test helper methods."""

    def test_is_blank_row(self, detector):
        """Should correctly identify blank rows."""
        wb = Workbook()
        sheet = wb.active

        # Blank row
        sheet.append([None, None, None])
        blank_row = list(sheet.iter_rows(min_row=1, max_row=1))[0]
        assert detector._is_blank_row(blank_row) is True

        # Non-blank row
        sheet.append(["A", "B", "C"])
        non_blank_row = list(sheet.iter_rows(min_row=2, max_row=2))[0]
        assert detector._is_blank_row(non_blank_row) is False

    def test_is_header_row(self, detector):
        """Should correctly identify header rows."""
        wb = Workbook()
        sheet = wb.active

        # Header row (text-heavy)
        sheet.append(["Name", "Age", "City"])
        header_row = list(sheet.iter_rows(min_row=1, max_row=1))[0]
        assert detector._is_header_row(header_row) is True

        # Data row (number-heavy)
        sheet.append([123, 456, 789])
        data_row = list(sheet.iter_rows(min_row=2, max_row=2))[0]
        assert detector._is_header_row(data_row) is False

    def test_get_first_populated_col(self, detector):
        """Should find first populated column."""
        wb = Workbook()
        sheet = wb.active

        sheet.append([None, None, "C", "D", "E"])
        row = list(sheet.iter_rows(min_row=1, max_row=1))[0]

        assert detector._get_first_populated_col(row) == "C"

    def test_get_last_populated_col(self, detector):
        """Should find last populated column."""
        wb = Workbook()
        sheet = wb.active

        sheet.append(["A", "B", "C", None, None])
        row = list(sheet.iter_rows(min_row=1, max_row=1))[0]

        assert detector._get_last_populated_col(row) == "C"