"""Integration tests for Indexer with real database.

These tests require a running PostgreSQL database and verify:
- Index existence in real database
- Query performance with real data
- Index usage via EXPLAIN ANALYZE
"""

import pytest
from uuid import uuid4

from src.db.models import Document, ExtractedRecord as ExtractedRecordModel, SymbolDictionary
from src.db.session import get_db_session
from src.extraction.models import ExtractedRecord
from src.knowledge.indexer import Indexer
from src.knowledge.structured_store import StructuredStore


@pytest.fixture
async def db_session():
    """Get a real database session for integration tests."""
    async with get_db_session() as session:
        yield session
        await session.rollback()  # Clean up after test


@pytest.fixture
async def test_document(db_session):
    """Create a test document in the database."""
    document = Document(
        id=uuid4(),
        filename="indexer_test.xlsx",
        file_path="/tmp/indexer_test.xlsx",
        status="processing",
    )
    db_session.add(document)
    await db_session.commit()
    await db_session.refresh(document)
    return document


@pytest.fixture
async def test_records(db_session, test_document):
    """Create test records in the database."""
    # Create sample extracted records
    records = [
        ExtractedRecord(
            content={
                "Item Code": f"TEST-{i:04d}",
                "Description": f"Test Item {i}",
                "Screen": "○" if i % 2 == 0 else "×",
                "Report": "Applicable" if i % 3 == 0 else "Not Applicable",
            },
            headers=["Item Code", "Description", "Screen", "Report"],
            _source={
                "document_id": str(test_document.id),
                "filename": test_document.filename,
                "sheet": "TestSheet",
                "table_id": 1,
                "row": 10 + i,
                "col_range": "A:D",
            },
        )
        for i in range(100)  # 100 test records
    ]

    # Add resolved content to some records
    for i, record in enumerate(records):
        if i % 2 == 0:  # Every other record has resolved content
            record.resolved_content = {
                "Screen": record.content["Screen"],
                "Screen_resolved": "Applicable/Yes" if record.content["Screen"] == "○" else "Not Applicable/No",
            }

    # Store using StructuredStore
    structured_store = StructuredStore(db_session)
    count = await structured_store.store_records(records, test_document.id)

    # Also create some symbol dictionaries
    symbols = [
        SymbolDictionary(
            document_id=test_document.id,
            sheet_name="TestSheet",
            symbol="○",
            meaning="Applicable/Yes",
            context="Screen column",
        ),
        SymbolDictionary(
            document_id=test_document.id,
            sheet_name="TestSheet",
            symbol="×",
            meaning="Not Applicable/No",
            context="Screen column",
        ),
    ]
    db_session.add_all(symbols)
    await db_session.commit()

    return count


# ========================================
# Index Verification Tests
# ========================================


@pytest.mark.asyncio
async def test_verify_indexes_real_database(db_session):
    """Test verify_indexes with real database connection."""
    indexer = Indexer(db_session)

    # Execute
    report = await indexer.verify_indexes()

    # Assert
    assert report.all_indexes_exist is True, f"Missing indexes: {report.missing_indexes}"
    assert len(report.expected_indexes) == 6

    # Check specific indexes
    index_names = [idx.index_name for idx in report.expected_indexes]
    assert "idx_extracted_records_content" in index_names
    assert "idx_extracted_records_resolved_content" in index_names
    assert "idx_extracted_records_document_id" in index_names
    assert "idx_extracted_records_sheet_name" in index_names
    assert "idx_symbol_dictionaries_document_id" in index_names
    assert "idx_symbol_dictionaries_symbol" in index_names

    # All should exist
    assert all(idx.exists for idx in report.expected_indexes)

    # All should have definitions
    for idx in report.expected_indexes:
        assert idx.definition is not None
        assert "CREATE INDEX" in idx.definition


# ========================================
# Index Statistics Tests
# ========================================


@pytest.mark.asyncio
async def test_get_index_stats_real_database(db_session, test_records):
    """Test get_index_stats with real database."""
    indexer = Indexer(db_session)

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

    # Assert
    assert len(stats) > 0  # Should have stats for extracted_records indexes

    # Check that we have stats for expected indexes
    index_names = [stat.index_name for stat in stats]
    assert "idx_extracted_records_content" in index_names
    assert "idx_extracted_records_document_id" in index_names

    # Stats should have reasonable values
    for stat in stats:
        assert stat.scans >= 0
        assert stat.tuples_read >= 0
        assert stat.tuples_fetched >= 0


@pytest.mark.asyncio
async def test_get_index_stats_symbol_dictionaries(db_session, test_records):
    """Test get_index_stats for symbol_dictionaries table."""
    indexer = Indexer(db_session)

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

    # Assert
    assert len(stats) > 0

    index_names = [stat.index_name for stat in stats]
    assert "idx_symbol_dictionaries_document_id" in index_names
    assert "idx_symbol_dictionaries_symbol" in index_names


# ========================================
# Query Performance Tests
# ========================================


@pytest.mark.asyncio
async def test_benchmark_query_performance_real_database(db_session, test_document, test_records):
    """Test benchmark_query_performance with real data."""
    indexer = Indexer(db_session)

    # Execute
    report = await indexer.benchmark_query_performance(test_document.id)

    # Assert
    assert report.document_id == str(test_document.id)

    # Check that timing values are reasonable
    assert report.exact_lookup_ms >= 0
    assert report.symbol_lookup_ms >= 0
    assert report.jsonb_containment_ms >= 0
    assert report.resolved_search_ms >= 0

    # Log performance results for analysis
    print(f"\n=== Query Performance Results ===")
    print(f"Exact lookup: {report.exact_lookup_ms}ms (target: < 50ms)")
    print(f"Symbol lookup: {report.symbol_lookup_ms}ms (target: < 20ms)")
    print(f"JSONB containment: {report.jsonb_containment_ms}ms (target: < 100ms)")
    print(f"Resolved search: {report.resolved_search_ms}ms (target: < 200ms)")
    print(f"All within targets: {report.all_within_targets}")

    # Performance should meet targets with small dataset
    # Note: These may fail on slow systems, but should pass on typical dev machines
    assert report.exact_lookup_ms < 100, f"Exact lookup too slow: {report.exact_lookup_ms}ms"
    assert report.symbol_lookup_ms < 50, f"Symbol lookup too slow: {report.symbol_lookup_ms}ms"
    assert report.jsonb_containment_ms < 200, f"JSONB containment too slow: {report.jsonb_containment_ms}ms"
    assert report.resolved_search_ms < 300, f"Resolved search too slow: {report.resolved_search_ms}ms"


# ========================================
# EXPLAIN ANALYZE Tests
# ========================================


@pytest.mark.asyncio
async def test_explain_query_exact_lookup(db_session, test_document, test_records):
    """Test EXPLAIN ANALYZE for exact item code lookup."""
    indexer = Indexer(db_session)

    # Execute
    query = """
        SELECT * FROM extracted_records
        WHERE document_id = :document_id
        AND content->>'Item Code' = 'TEST-0001'
    """
    plan = await indexer.explain_query(query, {"document_id": str(test_document.id)})

    # Assert
    assert "plan_text" in plan
    assert "query_preview" in plan

    # Should use index (either GIN on content or B-tree on document_id)
    assert plan["uses_index"] is True or plan["uses_sequential_scan"] is False

    print(f"\n=== EXPLAIN ANALYZE: Exact Lookup ===")
    print(plan["plan_text"])


@pytest.mark.asyncio
async def test_explain_query_jsonb_containment(db_session, test_document, test_records):
    """Test EXPLAIN ANALYZE for JSONB containment query."""
    indexer = Indexer(db_session)

    # Execute
    query = """
        SELECT * FROM extracted_records
        WHERE document_id = :document_id
        AND content @> '{"Screen": "○"}'::jsonb
    """
    plan = await indexer.explain_query(query, {"document_id": str(test_document.id)})

    # Assert
    assert "plan_text" in plan

    # Should use GIN index on content
    # Note: PostgreSQL may choose sequential scan for small tables
    print(f"\n=== EXPLAIN ANALYZE: JSONB Containment ===")
    print(plan["plan_text"])
    print(f"Uses index: {plan['uses_index']}")
    print(f"Uses sequential scan: {plan['uses_sequential_scan']}")


@pytest.mark.asyncio
async def test_explain_query_symbol_lookup(db_session, test_document, test_records):
    """Test EXPLAIN ANALYZE for symbol lookup."""
    indexer = Indexer(db_session)

    # Execute
    query = """
        SELECT * FROM symbol_dictionaries
        WHERE document_id = :document_id
        AND symbol = '○'
    """
    plan = await indexer.explain_query(query, {"document_id": str(test_document.id)})

    # Assert
    assert "plan_text" in plan

    # Should use index on symbol or document_id
    print(f"\n=== EXPLAIN ANALYZE: Symbol Lookup ===")
    print(plan["plan_text"])
    print(f"Uses index: {plan['uses_index']}")


@pytest.mark.asyncio
async def test_explain_query_resolved_content_search(db_session, test_document, test_records):
    """Test EXPLAIN ANALYZE for resolved content search."""
    indexer = Indexer(db_session)

    # Execute
    query = """
        SELECT * FROM extracted_records
        WHERE document_id = :document_id
        AND resolved_content->>'Screen_resolved' LIKE '%Applicable%'
    """
    plan = await indexer.explain_query(query, {"document_id": str(test_document.id)})

    # Assert
    assert "plan_text" in plan

    print(f"\n=== EXPLAIN ANALYZE: Resolved Content Search ===")
    print(plan["plan_text"])
    print(f"Uses index: {plan['uses_index']}")


# ========================================
# Integration with StructuredStore Tests
# ========================================


@pytest.mark.asyncio
async def test_indexer_after_structured_store(db_session, test_document):
    """Test Indexer integration with StructuredStore workflow."""
    # Step 1: Store records using StructuredStore (from Story 4.1)
    records = [
        ExtractedRecord(
            content={"Item Code": "INTEGRATION-001", "Status": "Active"},
            headers=["Item Code", "Status"],
            _source={
                "document_id": str(test_document.id),
                "filename": test_document.filename,
                "sheet": "Integration",
                "table_id": 1,
                "row": 5,
                "col_range": "A:B",
            },
        )
    ]

    structured_store = StructuredStore(db_session)
    count = await structured_store.store_records(records, test_document.id)
    assert count == 1

    # Step 2: Verify indexes (from Story 4.3)
    indexer = Indexer(db_session)
    verification = await indexer.verify_indexes()
    assert verification.all_indexes_exist is True

    # Step 3: Check query performance
    performance = await indexer.benchmark_query_performance(test_document.id)
    assert performance.document_id == str(test_document.id)

    # Step 4: Verify EXPLAIN shows index usage
    query = """
        SELECT * FROM extracted_records
        WHERE document_id = :document_id
        AND content->>'Item Code' = 'INTEGRATION-001'
    """
    plan = await indexer.explain_query(query, {"document_id": str(test_document.id)})
    assert "plan_text" in plan

    print(f"\n=== Integration Test Results ===")
    print(f"Records stored: {count}")
    print(f"All indexes exist: {verification.all_indexes_exist}")
    print(f"Query performance OK: {performance.all_within_targets}")
    print(f"Index usage: {plan['uses_index']}")