"""Examples of using Indexer for index verification and performance monitoring.

This file demonstrates practical usage patterns for the Indexer class:
- Verifying PostgreSQL indexes exist
- Monitoring index usage statistics
- Benchmarking query performance
- Using EXPLAIN ANALYZE to verify index usage

Run this file from the project root:
    cd /home/neil/Documents/DSOL
    python examples/indexer_usage.py

Or run as a module:
    python -m examples.indexer_usage
"""

import asyncio
import os
import sys
from pathlib import Path
from uuid import uuid4

# Add project root to Python path
project_root = Path(__file__).parent.parent
sys.path.insert(0, str(project_root))

from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker

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


async def example_1_verify_indexes():
    """Example 1: Verify that all expected indexes exist."""
    print("\n" + "=" * 60)
    print("EXAMPLE 1: Verify Indexes")
    print("=" * 60)

    # Get database URL from environment or use default
    database_url = os.getenv(
        "DATABASE_URL", "postgresql+asyncpg://dsol:dsol_dev_password@localhost:5432/dsol"
    )

    print(f"📡 Connecting to: {database_url.split('@')[1]}")  # Hide password

    # Create async engine
    engine = create_async_engine(database_url, echo=False)

    # Create session
    async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

    async with async_session() as session:
        # Create indexer
        indexer = Indexer(session)

        # Verify all expected indexes exist
        report = await indexer.verify_indexes()

        print(f"\n✅ Index Verification Results:")
        print(f"   Total expected indexes: {len(report.expected_indexes)}")
        print(f"   All indexes exist: {report.all_indexes_exist}")

        if report.all_indexes_exist:
            print(f"\n   🎉 All indexes are properly configured!")
            print(f"\n   Existing indexes:")
            for idx in report.expected_indexes:
                print(f"      • {idx.index_name} ({idx.index_type}) on {idx.table_name}.{idx.column_name}")
        else:
            print(f"\n   ⚠️  Missing indexes detected:")
            for idx_name in report.missing_indexes:
                print(f"      • {idx_name}")

    await engine.dispose()


async def example_2_index_statistics():
    """Example 2: Get index usage statistics."""
    print("\n" + "=" * 60)
    print("EXAMPLE 2: Index Usage Statistics")
    print("=" * 60)

    database_url = os.getenv(
        "DATABASE_URL", "postgresql+asyncpg://dsol:dsol_dev_password@localhost:5432/dsol"
    )

    engine = create_async_engine(database_url, echo=False)
    async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

    async with async_session() as session:
        indexer = Indexer(session)

        # Get stats for extracted_records table
        print("\n📊 Index statistics for 'extracted_records':")
        stats = await indexer.get_index_stats("extracted_records")

        if stats:
            for stat in stats:
                print(f"\n   Index: {stat.index_name}")
                print(f"      Scans: {stat.scans:,}")
                print(f"      Tuples read: {stat.tuples_read:,}")
                print(f"      Tuples fetched: {stat.tuples_fetched:,}")
        else:
            print("   No statistics available yet (table may be empty)")

        # Get stats for symbol_dictionaries table
        print("\n📊 Index statistics for 'symbol_dictionaries':")
        stats = await indexer.get_index_stats("symbol_dictionaries")

        if stats:
            for stat in stats:
                print(f"\n   Index: {stat.index_name}")
                print(f"      Scans: {stat.scans:,}")
                print(f"      Tuples read: {stat.tuples_read:,}")
                print(f"      Tuples fetched: {stat.tuples_fetched:,}")
        else:
            print("   No statistics available yet (table may be empty)")

    await engine.dispose()


async def example_3_query_performance():
    """Example 3: Benchmark query performance."""
    print("\n" + "=" * 60)
    print("EXAMPLE 3: Query Performance Benchmarking")
    print("=" * 60)

    database_url = os.getenv(
        "DATABASE_URL", "postgresql+asyncpg://dsol:dsol_dev_password@localhost:5432/dsol"
    )

    engine = create_async_engine(database_url, echo=False)
    async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

    async with async_session() as session:
        # Find a document with records
        stmt = select(Document).where(Document.status == "completed").limit(1)
        result = await session.execute(stmt)
        document = result.scalars().first()

        if not document:
            print("⚠️  No completed documents found. Upload and process a document first!")
            await engine.dispose()
            return

        print(f"📄 Testing with document: {document.filename} (ID: {document.id})")

        # Benchmark query performance
        indexer = Indexer(session)
        report = await indexer.benchmark_query_performance(document.id)

        print(f"\n⏱️  Query Performance Results:")
        print(f"\n   1. Exact item code lookup: {report.exact_lookup_ms:.2f}ms")
        print(f"      Target: < 50ms")
        print(f"      Status: {'✅ PASS' if report.exact_lookup_ms < 50 else '❌ FAIL'}")

        print(f"\n   2. Symbol lookup: {report.symbol_lookup_ms:.2f}ms")
        print(f"      Target: < 20ms")
        print(f"      Status: {'✅ PASS' if report.symbol_lookup_ms < 20 else '❌ FAIL'}")

        print(f"\n   3. JSONB containment query: {report.jsonb_containment_ms:.2f}ms")
        print(f"      Target: < 100ms")
        print(f"      Status: {'✅ PASS' if report.jsonb_containment_ms < 100 else '❌ FAIL'}")

        print(f"\n   4. Resolved content search: {report.resolved_search_ms:.2f}ms")
        print(f"      Target: < 200ms")
        print(f"      Status: {'✅ PASS' if report.resolved_search_ms < 200 else '❌ FAIL'}")

        if report.all_within_targets:
            print(f"\n   🎉 All queries meet performance targets!")
        else:
            print(f"\n   ⚠️  Some queries exceed performance targets")

    await engine.dispose()


async def example_4_explain_query():
    """Example 4: Use EXPLAIN ANALYZE to verify index usage."""
    print("\n" + "=" * 60)
    print("EXAMPLE 4: EXPLAIN ANALYZE - Verify Index Usage")
    print("=" * 60)

    database_url = os.getenv(
        "DATABASE_URL", "postgresql+asyncpg://dsol:dsol_dev_password@localhost:5432/dsol"
    )

    engine = create_async_engine(database_url, echo=False)
    async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

    async with async_session() as session:
        # Find a document
        stmt = select(Document).where(Document.status == "completed").limit(1)
        result = await session.execute(stmt)
        document = result.scalars().first()

        if not document:
            print("⚠️  No completed documents found. Upload and process a document first!")
            await engine.dispose()
            return

        indexer = Indexer(session)

        # Test 1: Exact item code lookup
        print("\n🔍 Test 1: Exact item code lookup")
        query = """
            SELECT * FROM extracted_records
            WHERE document_id = :document_id
            AND content->>'Item Code' IS NOT NULL
            LIMIT 1
        """
        plan = await indexer.explain_query(query, {"document_id": str(document.id)})

        print(f"   Uses index: {plan['uses_index']}")
        print(f"   Uses sequential scan: {plan['uses_sequential_scan']}")
        print(f"\n   Query plan:")
        for line in plan["plan_text"].split("\n")[:5]:  # Show first 5 lines
            print(f"      {line}")

        # Test 2: JSONB containment query
        print("\n🔍 Test 2: JSONB containment query")
        query = """
            SELECT * FROM extracted_records
            WHERE document_id = :document_id
            AND content IS NOT NULL
            LIMIT 10
        """
        plan = await indexer.explain_query(query, {"document_id": str(document.id)})

        print(f"   Uses index: {plan['uses_index']}")
        print(f"   Uses sequential scan: {plan['uses_sequential_scan']}")

    await engine.dispose()


async def example_5_integration_workflow():
    """Example 5: Integration with StructuredStore - Complete workflow."""
    print("\n" + "=" * 60)
    print("EXAMPLE 5: Integration Workflow (Store → Verify → Benchmark)")
    print("=" * 60)

    database_url = os.getenv(
        "DATABASE_URL", "postgresql+asyncpg://dsol:dsol_dev_password@localhost:5432/dsol"
    )

    engine = create_async_engine(database_url, echo=False)
    async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

    async with async_session() as session:
        # Step 1: Create a document
        document = Document(
            id=uuid4(),
            filename="indexer_example.xlsx",
            file_path="/tmp/indexer_example.xlsx",
            status="processing",
        )
        session.add(document)
        await session.commit()
        await session.refresh(document)

        print(f"📄 Created document: {document.filename}")

        # Step 2: Store records using StructuredStore (Story 4.1)
        records = [
            ExtractedRecord(
                content={
                    "Item Code": f"EXAMPLE-{i:03d}",
                    "Description": f"Example Item {i}",
                    "Status": "Active" if i % 2 == 0 else "Inactive",
                },
                headers=["Item Code", "Description", "Status"],
                _source={
                    "document_id": str(document.id),
                    "filename": document.filename,
                    "sheet": "Examples",
                    "table_id": 1,
                    "row": 5 + i,
                    "col_range": "A:C",
                },
            )
            for i in range(50)  # 50 example records
        ]

        structured_store = StructuredStore(session)
        count = await structured_store.store_records(records, document.id)
        print(f"✅ Stored {count} records")

        # Step 3: Verify indexes (Story 4.3)
        indexer = Indexer(session)
        verification = await indexer.verify_indexes()

        print(f"\n✅ Index verification:")
        print(f"   All indexes exist: {verification.all_indexes_exist}")
        if not verification.all_indexes_exist:
            print(f"   Missing: {verification.missing_indexes}")

        # Step 4: Check query performance
        performance = await indexer.benchmark_query_performance(document.id)

        print(f"\n✅ Query performance:")
        print(f"   Exact lookup: {performance.exact_lookup_ms:.2f}ms")
        print(f"   Symbol lookup: {performance.symbol_lookup_ms:.2f}ms")
        print(f"   JSONB containment: {performance.jsonb_containment_ms:.2f}ms")
        print(f"   Resolved search: {performance.resolved_search_ms:.2f}ms")
        print(f"   All within targets: {performance.all_within_targets}")

        # Step 5: Check index usage with EXPLAIN
        query = """
            SELECT * FROM extracted_records
            WHERE document_id = :document_id
            AND content->>'Item Code' = 'EXAMPLE-001'
        """
        plan = await indexer.explain_query(query, {"document_id": str(document.id)})

        print(f"\n✅ Index usage verification:")
        print(f"   Uses index: {plan['uses_index']}")
        print(f"   Uses sequential scan: {plan['uses_sequential_scan']}")

        # Cleanup
        await session.delete(document)
        await session.commit()

    await engine.dispose()


async def main():
    """Run all examples."""
    print("\n" + "🚀" * 30)
    print("Indexer Usage Examples")
    print("🚀" * 30)

    print("\n⚠️  Prerequisites:")
    print("  1. PostgreSQL running (docker-compose up -d postgres)")
    print("  2. Database migrations applied (alembic upgrade head)")
    print("  3. DATABASE_URL environment variable set")
    print("  4. Some documents processed (for performance testing)")
    print()

    try:
        # Run examples sequentially
        await example_1_verify_indexes()
        await example_2_index_statistics()
        await example_3_query_performance()
        await example_4_explain_query()
        await example_5_integration_workflow()

        print("\n" + "✅" * 30)
        print("All examples completed!")
        print("✅" * 30 + "\n")

    except Exception as e:
        print("\n" + "❌" * 30)
        print(f"Error running examples: {e}")
        print("❌" * 30)
        print("\n💡 Troubleshooting:")
        print("  1. Check if PostgreSQL is running:")
        print("     docker ps | grep postgres")
        print("  2. Check database credentials:")
        print("     echo $DATABASE_URL")
        print("  3. Run migrations:")
        print("     alembic upgrade head")
        print("  4. Process some documents:")
        print("     python examples/structured_store_usage.py")
        print()
        raise


if __name__ == "__main__":
    # Run examples
    asyncio.run(main())