"""Unit tests for Indexer class."""

import pytest
from datetime import datetime
from unittest.mock import AsyncMock, MagicMock
from uuid import uuid4

from src.knowledge.indexer import (
    Indexer,
    IndexInfo,
    IndexVerificationReport,
    IndexStats,
    QueryPerformanceReport,
)


@pytest.fixture
def mock_db_session():
    """Create a mock database session."""
    session = AsyncMock()
    return session


@pytest.fixture
def indexer(mock_db_session):
    """Create an Indexer instance with mocked session."""
    return Indexer(mock_db_session)


# ========================================
# IndexInfo Tests
# ========================================


def test_index_info_creation():
    """Test IndexInfo dataclass creation."""
    index = IndexInfo(
        index_name="idx_test",
        table_name="test_table",
        index_type="gin",
        column_name="test_column",
        exists=True,
        definition="CREATE INDEX idx_test ON test_table USING gin (test_column)",
    )

    assert index.index_name == "idx_test"
    assert index.table_name == "test_table"
    assert index.index_type == "gin"
    assert index.column_name == "test_column"
    assert index.exists is True
    assert "CREATE INDEX" in index.definition


# ========================================
# IndexVerificationReport Tests
# ========================================


def test_index_verification_report_all_exist():
    """Test IndexVerificationReport when all indexes exist."""
    indexes = [
        IndexInfo("idx_1", "table_1", "gin", "col_1", True),
        IndexInfo("idx_2", "table_1", "btree", "col_2", True),
    ]

    report = IndexVerificationReport(
        timestamp=datetime.now(),
        all_indexes_exist=True,
        expected_indexes=indexes,
        missing_indexes=[],
    )

    assert report.all_indexes_exist is True
    assert len(report.expected_indexes) == 2
    assert len(report.missing_indexes) == 0

    report_dict = report.to_dict()
    assert report_dict["all_indexes_exist"] is True
    assert report_dict["total_indexes"] == 2
    assert report_dict["missing_count"] == 0


def test_index_verification_report_missing_indexes():
    """Test IndexVerificationReport when some indexes are missing."""
    indexes = [
        IndexInfo("idx_1", "table_1", "gin", "col_1", True),
        IndexInfo("idx_2", "table_1", "btree", "col_2", False),
    ]

    report = IndexVerificationReport(
        timestamp=datetime.now(),
        all_indexes_exist=False,
        expected_indexes=indexes,
        missing_indexes=["idx_2"],
    )

    assert report.all_indexes_exist is False
    assert len(report.missing_indexes) == 1
    assert "idx_2" in report.missing_indexes

    report_dict = report.to_dict()
    assert report_dict["all_indexes_exist"] is False
    assert report_dict["missing_count"] == 1
    assert "idx_2" in report_dict["missing_indexes"]


# ========================================
# IndexStats Tests
# ========================================


def test_index_stats_creation():
    """Test IndexStats dataclass creation."""
    stats = IndexStats(
        index_name="idx_test",
        scans=100,
        tuples_read=1000,
        tuples_fetched=950,
    )

    assert stats.index_name == "idx_test"
    assert stats.scans == 100
    assert stats.tuples_read == 1000
    assert stats.tuples_fetched == 950


# ========================================
# QueryPerformanceReport Tests
# ========================================


def test_query_performance_report_within_targets():
    """Test QueryPerformanceReport when all queries meet targets."""
    report = QueryPerformanceReport(
        document_id=str(uuid4()),
        exact_lookup_ms=45.0,  # < 50ms ✓
        symbol_lookup_ms=15.0,  # < 20ms ✓
        jsonb_containment_ms=90.0,  # < 100ms ✓
        resolved_search_ms=180.0,  # < 200ms ✓
        all_within_targets=True,
    )

    assert report.all_within_targets is True

    report_dict = report.to_dict()
    assert report_dict["exact_lookup_ok"] is True
    assert report_dict["symbol_lookup_ok"] is True
    assert report_dict["jsonb_containment_ok"] is True
    assert report_dict["resolved_search_ok"] is True
    assert report_dict["all_within_targets"] is True


def test_query_performance_report_exceeds_targets():
    """Test QueryPerformanceReport when some queries exceed targets."""
    report = QueryPerformanceReport(
        document_id=str(uuid4()),
        exact_lookup_ms=55.0,  # > 50ms ✗
        symbol_lookup_ms=25.0,  # > 20ms ✗
        jsonb_containment_ms=90.0,  # < 100ms ✓
        resolved_search_ms=180.0,  # < 200ms ✓
        all_within_targets=False,
    )

    assert report.all_within_targets is False

    report_dict = report.to_dict()
    assert report_dict["exact_lookup_ok"] is False
    assert report_dict["symbol_lookup_ok"] is False
    assert report_dict["jsonb_containment_ok"] is True
    assert report_dict["resolved_search_ok"] is True
    assert report_dict["all_within_targets"] is False


# ========================================
# Indexer.verify_indexes() Tests
# ========================================


@pytest.mark.asyncio
async def test_verify_indexes_all_exist(indexer, mock_db_session):
    """Test verify_indexes when all expected indexes exist."""
    # Mock pg_indexes query result
    mock_result = MagicMock()
    mock_result.fetchall.return_value = [
        (
            "idx_extracted_records_content",
            "extracted_records",
            "CREATE INDEX idx_extracted_records_content ON extracted_records USING gin (content)",
        ),
        (
            "idx_extracted_records_resolved_content",
            "extracted_records",
            "CREATE INDEX idx_extracted_records_resolved_content ON extracted_records USING gin (resolved_content)",
        ),
        (
            "idx_extracted_records_document_id",
            "extracted_records",
            "CREATE INDEX idx_extracted_records_document_id ON extracted_records (document_id)",
        ),
        (
            "idx_extracted_records_sheet_name",
            "extracted_records",
            "CREATE INDEX idx_extracted_records_sheet_name ON extracted_records (sheet_name)",
        ),
        (
            "idx_symbol_dictionaries_document_id",
            "symbol_dictionaries",
            "CREATE INDEX idx_symbol_dictionaries_document_id ON symbol_dictionaries (document_id)",
        ),
        (
            "idx_symbol_dictionaries_symbol",
            "symbol_dictionaries",
            "CREATE INDEX idx_symbol_dictionaries_symbol ON symbol_dictionaries (symbol)",
        ),
    ]
    mock_db_session.execute = AsyncMock(return_value=mock_result)

    # Execute
    report = await indexer.verify_indexes()

    # Assert
    assert report.all_indexes_exist is True
    assert len(report.expected_indexes) == 6
    assert len(report.missing_indexes) == 0
    assert all(idx.exists for idx in report.expected_indexes)


@pytest.mark.asyncio
async def test_verify_indexes_some_missing(indexer, mock_db_session):
    """Test verify_indexes when some indexes are missing."""
    # Mock pg_indexes query result (missing 2 indexes)
    mock_result = MagicMock()
    mock_result.fetchall.return_value = [
        (
            "idx_extracted_records_content",
            "extracted_records",
            "CREATE INDEX idx_extracted_records_content ON extracted_records USING gin (content)",
        ),
        (
            "idx_extracted_records_document_id",
            "extracted_records",
            "CREATE INDEX idx_extracted_records_document_id ON extracted_records (document_id)",
        ),
        (
            "idx_symbol_dictionaries_document_id",
            "symbol_dictionaries",
            "CREATE INDEX idx_symbol_dictionaries_document_id ON symbol_dictionaries (document_id)",
        ),
        (
            "idx_symbol_dictionaries_symbol",
            "symbol_dictionaries",
            "CREATE INDEX idx_symbol_dictionaries_symbol ON symbol_dictionaries (symbol)",
        ),
    ]
    mock_db_session.execute = AsyncMock(return_value=mock_result)

    # Execute
    report = await indexer.verify_indexes()

    # Assert
    assert report.all_indexes_exist is False
    assert len(report.missing_indexes) == 2
    assert "idx_extracted_records_resolved_content" in report.missing_indexes
    assert "idx_extracted_records_sheet_name" in report.missing_indexes


@pytest.mark.asyncio
async def test_verify_indexes_empty_database(indexer, mock_db_session):
    """Test verify_indexes when no indexes exist."""
    # Mock empty pg_indexes query result
    mock_result = MagicMock()
    mock_result.fetchall.return_value = []
    mock_db_session.execute = AsyncMock(return_value=mock_result)

    # Execute
    report = await indexer.verify_indexes()

    # Assert
    assert report.all_indexes_exist is False
    assert len(report.missing_indexes) == 6
    assert all(not idx.exists for idx in report.expected_indexes)


# ========================================
# Indexer.get_index_stats() Tests
# ========================================


@pytest.mark.asyncio
async def test_get_index_stats_success(indexer, mock_db_session):
    """Test get_index_stats returns correct statistics."""
    # Mock pg_stat_user_indexes query result
    mock_result = MagicMock()
    mock_result.fetchall.return_value = [
        ("idx_extracted_records_content", 150, 1500, 1400),
        ("idx_extracted_records_document_id", 200, 2000, 1950),
        ("idx_extracted_records_sheet_name", 100, 1000, 950),
    ]
    mock_db_session.execute = AsyncMock(return_value=mock_result)

    # Execute
    stats = await indexer.get_index_stats("extracted_records")

    # Assert
    assert len(stats) == 3
    assert stats[0].index_name == "idx_extracted_records_content"
    assert stats[0].scans == 150
    assert stats[0].tuples_read == 1500
    assert stats[0].tuples_fetched == 1400

    assert stats[1].index_name == "idx_extracted_records_document_id"
    assert stats[1].scans == 200


@pytest.mark.asyncio
async def test_get_index_stats_no_usage(indexer, mock_db_session):
    """Test get_index_stats with indexes that have no usage."""
    # Mock pg_stat_user_indexes query result with null values
    mock_result = MagicMock()
    mock_result.fetchall.return_value = [
        ("idx_extracted_records_content", None, None, None),
    ]
    mock_db_session.execute = AsyncMock(return_value=mock_result)

    # Execute
    stats = await indexer.get_index_stats("extracted_records")

    # Assert
    assert len(stats) == 1
    assert stats[0].scans == 0
    assert stats[0].tuples_read == 0
    assert stats[0].tuples_fetched == 0


@pytest.mark.asyncio
async def test_get_index_stats_different_table(indexer, mock_db_session):
    """Test get_index_stats for symbol_dictionaries table."""
    # Mock pg_stat_user_indexes query result
    mock_result = MagicMock()
    mock_result.fetchall.return_value = [
        ("idx_symbol_dictionaries_document_id", 50, 500, 480),
        ("idx_symbol_dictionaries_symbol", 80, 800, 790),
    ]
    mock_db_session.execute = AsyncMock(return_value=mock_result)

    # Execute
    stats = await indexer.get_index_stats("symbol_dictionaries")

    # Assert
    assert len(stats) == 2
    assert stats[0].index_name == "idx_symbol_dictionaries_document_id"
    assert stats[1].index_name == "idx_symbol_dictionaries_symbol"


# ========================================
# Indexer.benchmark_query_performance() Tests
# ========================================


@pytest.mark.asyncio
async def test_benchmark_query_performance_within_targets(indexer, mock_db_session):
    """Test benchmark_query_performance when all queries meet targets."""
    # Mock query execution (all fast)
    mock_result = MagicMock()
    mock_db_session.execute = AsyncMock(return_value=mock_result)

    document_id = uuid4()

    # Execute
    report = await indexer.benchmark_query_performance(document_id)

    # Assert
    assert report.document_id == str(document_id)
    assert isinstance(report.exact_lookup_ms, float)
    assert isinstance(report.symbol_lookup_ms, float)
    assert isinstance(report.jsonb_containment_ms, float)
    assert isinstance(report.resolved_search_ms, float)

    # Verify queries were executed (4 benchmark queries)
    assert mock_db_session.execute.call_count == 4


# ========================================
# Indexer.explain_query() Tests
# ========================================


@pytest.mark.asyncio
async def test_explain_query_uses_index(indexer, mock_db_session):
    """Test explain_query when query uses an index."""
    # Mock EXPLAIN ANALYZE result showing index usage
    mock_result = MagicMock()
    mock_result.fetchall.return_value = [
        ("Index Scan using idx_extracted_records_content on extracted_records",),
        ("  Index Cond: ((content ->> 'Item Code'::text) = '2024-4_0019'::text)",),
        ("  Rows: 1",),
        ("Planning Time: 0.5 ms",),
        ("Execution Time: 2.3 ms",),
    ]
    mock_db_session.execute = AsyncMock(return_value=mock_result)

    # Execute
    plan = await indexer.explain_query(
        "SELECT * FROM extracted_records WHERE content->>'Item Code' = :code",
        {"code": "2024-4_0019"},
    )

    # Assert
    assert plan["uses_index"] is True
    assert plan["uses_sequential_scan"] is False
    assert "Index Scan" in plan["plan_text"]
    assert "query_preview" in plan


@pytest.mark.asyncio
async def test_explain_query_uses_sequential_scan(indexer, mock_db_session):
    """Test explain_query when query uses sequential scan."""
    # Mock EXPLAIN ANALYZE result showing sequential scan
    mock_result = MagicMock()
    mock_result.fetchall.return_value = [
        ("Seq Scan on extracted_records",),
        ("  Filter: (content IS NOT NULL)",),
        ("  Rows: 100",),
        ("Planning Time: 0.3 ms",),
        ("Execution Time: 15.2 ms",),
    ]
    mock_db_session.execute = AsyncMock(return_value=mock_result)

    # Execute
    plan = await indexer.explain_query(
        "SELECT * FROM extracted_records WHERE content IS NOT NULL"
    )

    # Assert
    assert plan["uses_index"] is False
    assert plan["uses_sequential_scan"] is True
    assert "Seq Scan" in plan["plan_text"]


@pytest.mark.asyncio
async def test_explain_query_bitmap_index_scan(indexer, mock_db_session):
    """Test explain_query when query uses bitmap index scan."""
    # Mock EXPLAIN ANALYZE result showing bitmap index scan
    mock_result = MagicMock()
    mock_result.fetchall.return_value = [
        ("Bitmap Heap Scan on extracted_records",),
        ("  Recheck Cond: (content @> '{\"Screen\": \"○\"}'::jsonb)",),
        ("  -> Bitmap Index Scan on idx_extracted_records_content",),
        ("       Index Cond: (content @> '{\"Screen\": \"○\"}'::jsonb)",),
        ("Planning Time: 0.4 ms",),
        ("Execution Time: 5.1 ms",),
    ]
    mock_db_session.execute = AsyncMock(return_value=mock_result)

    # Execute
    plan = await indexer.explain_query(
        "SELECT * FROM extracted_records WHERE content @> '{\"Screen\": \"○\"}'::jsonb"
    )

    # Assert
    assert plan["uses_index"] is True
    assert "Bitmap Index Scan" in plan["plan_text"]


# ========================================
# Edge Cases and Error Handling Tests
# ========================================


@pytest.mark.asyncio
async def test_verify_indexes_with_extra_indexes(indexer, mock_db_session):
    """Test verify_indexes when database has extra indexes not in expected list."""
    # Mock pg_indexes query result with extra indexes
    mock_result = MagicMock()
    mock_result.fetchall.return_value = [
        (
            "idx_extracted_records_content",
            "extracted_records",
            "CREATE INDEX idx_extracted_records_content ON extracted_records USING gin (content)",
        ),
        (
            "idx_extracted_records_resolved_content",
            "extracted_records",
            "CREATE INDEX idx_extracted_records_resolved_content ON extracted_records USING gin (resolved_content)",
        ),
        (
            "idx_extracted_records_document_id",
            "extracted_records",
            "CREATE INDEX idx_extracted_records_document_id ON extracted_records (document_id)",
        ),
        (
            "idx_extracted_records_sheet_name",
            "extracted_records",
            "CREATE INDEX idx_extracted_records_sheet_name ON extracted_records (sheet_name)",
        ),
        (
            "idx_symbol_dictionaries_document_id",
            "symbol_dictionaries",
            "CREATE INDEX idx_symbol_dictionaries_document_id ON symbol_dictionaries (document_id)",
        ),
        (
            "idx_symbol_dictionaries_symbol",
            "symbol_dictionaries",
            "CREATE INDEX idx_symbol_dictionaries_symbol ON symbol_dictionaries (symbol)",
        ),
        # Extra index not in expected list
        (
            "idx_extra_custom",
            "extracted_records",
            "CREATE INDEX idx_extra_custom ON extracted_records (created_at)",
        ),
    ]
    mock_db_session.execute = AsyncMock(return_value=mock_result)

    # Execute
    report = await indexer.verify_indexes()

    # Assert - should still pass (we only care about expected indexes)
    assert report.all_indexes_exist is True
    assert len(report.missing_indexes) == 0