"""Quick start guide for using the Indexer class.

This demonstrates the most common use cases for index verification
and query performance monitoring.
"""

import asyncio
import os
import sys
from pathlib import Path

# Add project root to 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
from src.knowledge.indexer import Indexer


async def main():
    """Quick start examples."""
    print("\n" + "=" * 60)
    print("Indexer Quick Start Guide")
    print("=" * 60)

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

    print(f"\n📡 Connecting to database...")
    engine = create_async_engine(database_url, echo=False)
    async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

    async with async_session() as session:
        # ========================================
        # Example 1: Verify Indexes
        # ========================================
        print("\n📝 Step 1: Verifying indexes...")

        indexer = Indexer(session)
        verification = await indexer.verify_indexes()

        print(f"\n   Total indexes expected: {len(verification.expected_indexes)}")
        print(f"   All indexes exist: {verification.all_indexes_exist}")

        if verification.all_indexes_exist:
            print(f"\n   ✅ All indexes are configured correctly!")
            print(f"\n   Indexes verified:")
            for idx in verification.expected_indexes:
                print(f"      • {idx.index_name} ({idx.index_type}) on {idx.table_name}")
        else:
            print(f"\n   ⚠️  Missing indexes:")
            for idx_name in verification.missing_indexes:
                print(f"      • {idx_name}")

        # ========================================
        # Example 2: Get Index Statistics
        # ========================================
        print("\n📊 Step 2: Getting index usage statistics...")

        stats = await indexer.get_index_stats("extracted_records")

        if stats:
            print(f"\n   Found {len(stats)} indexes on extracted_records table:")
            for stat in stats[:3]:  # Show first 3
                print(f"\n      {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)")

        # ========================================
        # Example 3: Benchmark Query Performance
        # ========================================
        print("\n⏱️  Step 3: Benchmarking query performance...")

        # Find a document to test with
        stmt = select(Document).limit(1)
        result = await session.execute(stmt)
        document = result.scalars().first()

        if document:
            print(f"\n   Testing with document: {document.filename}")

            performance = await indexer.benchmark_query_performance(document.id)

            print(f"\n   Performance Results:")
            print(f"      1. Exact lookup: {performance.exact_lookup_ms:.2f}ms")
            print(f"         Target: < 50ms {'✅' if performance.exact_lookup_ms < 50 else '❌'}")

            print(f"      2. Symbol lookup: {performance.symbol_lookup_ms:.2f}ms")
            print(f"         Target: < 20ms {'✅' if performance.symbol_lookup_ms < 20 else '❌'}")

            print(f"      3. JSONB containment: {performance.jsonb_containment_ms:.2f}ms")
            print(f"         Target: < 100ms {'✅' if performance.jsonb_containment_ms < 100 else '❌'}")

            print(f"      4. Resolved search: {performance.resolved_search_ms:.2f}ms")
            print(f"         Target: < 200ms {'✅' if performance.resolved_search_ms < 200 else '❌'}")

            if performance.all_within_targets:
                print(f"\n   🎉 All queries meet performance targets!")
            else:
                print(f"\n   ⚠️  Some queries need optimization")
        else:
            print("   No documents found. Upload some documents first!")

        # ========================================
        # Example 4: Use EXPLAIN ANALYZE
        # ========================================
        if document:
            print("\n🔍 Step 4: Verifying index usage with EXPLAIN ANALYZE...")

            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"\n   Query plan analysis:")
            print(f"      Uses index: {plan['uses_index']}")
            print(f"      Uses sequential scan: {plan['uses_sequential_scan']}")

            if plan['uses_index']:
                print(f"\n   ✅ Query is using indexes efficiently!")
            else:
                print(f"\n   ⚠️  Query may benefit from index optimization")

    await engine.dispose()

    print("\n" + "=" * 60)
    print("✅ Quick Start Complete!")
    print("=" * 60)
    print("\nNext steps:")
    print("  1. Check full examples: python examples/indexer_usage.py")
    print("  2. Run unit tests: pytest tests/knowledge/test_indexer.py -v")
    print("  3. Run integration tests: pytest tests/knowledge/test_indexer_integration.py -v")
    print()


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