"""Examples of using MetadataService for source metadata queries.

This file demonstrates practical usage patterns for querying source metadata
and formatting citations using the MetadataService class.

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

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

import asyncio
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.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker

from src.db.models import Document, ExtractedRecord as ExtractedRecordModel
from src.knowledge.metadata_service import MetadataService


async def example_1_get_source_citation():
    """Example 1: Get source citation for a record."""
    print("\n" + "=" * 60)
    print("EXAMPLE 1: Get Source Citation")
    print("=" * 60)

    # Get database URL from environment or use default
    import os

    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:
        # Find an existing record (assuming Story 4.1 has stored some records)
        from sqlalchemy import select

        stmt = select(ExtractedRecordModel).limit(1)
        result = await session.execute(stmt)
        record = result.scalars().first()

        if not record:
            print("⚠️  No records found. Run Story 4.1 structured_store_usage.py first!")
            await engine.dispose()
            return

        print(f"📄 Found record ID: {record.id}")

        # Get source citation
        service = MetadataService(session)
        citation = await service.get_source_citation(record.id)

        if citation:
            print(f"\n✅ Retrieved source citation:")
            print(f"   Human-readable: {citation.to_human_readable()}")
            print(f"\n   JSON format:")
            import json

            print(f"   {json.dumps(citation.to_json(), indent=2)}")
        else:
            print("❌ Citation not found")

    await engine.dispose()


async def example_2_query_by_location():
    """Example 2: Query records by location (document, sheet, row range)."""
    print("\n" + "=" * 60)
    print("EXAMPLE 2: Query Records by Location")
    print("=" * 60)

    import os

    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
        from sqlalchemy import select

        stmt = select(Document).limit(1)
        result = await session.execute(stmt)
        document = result.scalars().first()

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

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

        service = MetadataService(session)

        # Query 1: All records from document
        all_records = await service.get_records_by_location(document.id)
        print(f"\n✅ All records: {len(all_records)} records found")

        if all_records:
            # Get unique sheet names
            sheets = set(r.sheet_name for r in all_records)
            print(f"   Sheets: {', '.join(sheets)}")

            # Query 2: Records from first sheet
            first_sheet = list(sheets)[0] if sheets else None
            if first_sheet:
                sheet_records = await service.get_records_by_location(
                    document.id, sheet_name=first_sheet
                )
                print(
                    f"\n✅ Records from sheet '{first_sheet}': {len(sheet_records)} records"
                )

                # Query 3: Records from row range
                if len(sheet_records) > 5:
                    row_range_records = await service.get_records_by_location(
                        document.id, sheet_name=first_sheet, row_range=(1, 5)
                    )
                    print(
                        f"\n✅ Records from rows 1-5 in '{first_sheet}': {len(row_range_records)} records"
                    )
                    for record in row_range_records[:3]:  # Show first 3
                        print(f"   Row {record.row_number}: {list(record.content.keys())}")

    await engine.dispose()


async def example_3_validate_source_completeness():
    """Example 3: Validate source metadata completeness for a document."""
    print("\n" + "=" * 60)
    print("EXAMPLE 3: Validate Source Metadata Completeness")
    print("=" * 60)

    import os

    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
        from sqlalchemy import select

        stmt = select(Document).limit(1)
        result = await session.execute(stmt)
        document = result.scalars().first()

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

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

        # Validate source completeness
        service = MetadataService(session)
        report = await service.validate_source_completeness(document.id)

        print(f"\n✅ Validation Report:")
        print(f"   Total records: {report.total_records}")
        print(f"   Complete records: {report.complete_records}")
        print(f"   Completeness: {report.completeness_percentage:.1f}%")
        print(f"   Records with col_range: {report.records_with_col_range}")
        print(f"   Records with headers: {report.records_with_headers}")

        if report.is_valid:
            print(f"\n   ✅ All records have complete source metadata!")
        else:
            print(
                f"\n   ⚠️  {len(report.missing_required_fields)} records have missing fields:"
            )
            for issue in report.missing_required_fields[:5]:  # Show first 5
                print(
                    f"      Record {issue['record_id']}: missing {issue['missing_fields']}"
                )

    await engine.dispose()


async def example_4_batch_citations():
    """Example 4: Get citations for multiple records in batch."""
    print("\n" + "=" * 60)
    print("EXAMPLE 4: Batch Citation Retrieval")
    print("=" * 60)

    import os

    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:
        # Get some record IDs
        from sqlalchemy import select

        stmt = select(ExtractedRecordModel).limit(5)
        result = await session.execute(stmt)
        records = result.scalars().all()

        if not records:
            print("⚠️  No records found. Run Story 4.1 structured_store_usage.py first!")
            await engine.dispose()
            return

        record_ids = [r.id for r in records]
        print(f"📄 Getting citations for {len(record_ids)} records")

        # Get batch citations
        service = MetadataService(session)
        citations = await service.get_citation_batch(record_ids)

        print(f"\n✅ Retrieved {len(citations)} citations:")
        for i, (record_id, citation) in enumerate(citations.items(), 1):
            print(f"\n   {i}. {citation.to_human_readable()}")
            if i >= 3:  # Show first 3
                print(f"   ... and {len(citations) - 3} more")
                break

    await engine.dispose()


async def example_5_integration_with_structured_store():
    """Example 5: Integration pattern - Store records then query metadata."""
    print("\n" + "=" * 60)
    print("EXAMPLE 5: Integration Pattern (Store → Query Metadata)")
    print("=" * 60)

    import os

    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 (prerequisite)
        document = Document(
            id=uuid4(),
            filename="metadata_example.xlsx",
            file_path="/tmp/metadata_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 (from Story 4.1)
        from src.extraction.models import ExtractedRecord
        from src.knowledge.structured_store import StructuredStore

        records = [
            ExtractedRecord(
                content={
                    "Item Code": "EXAMPLE-001",
                    "Description": "Example Item 1",
                    "Status": "Active",
                },
                headers=["Item Code", "Description", "Status"],
                _source={
                    "document_id": str(document.id),
                    "filename": document.filename,
                    "sheet": "Examples",
                    "table_id": 1,
                    "row": 5,
                    "col_range": "A:C",
                },
            ),
            ExtractedRecord(
                content={
                    "Item Code": "EXAMPLE-002",
                    "Description": "Example Item 2",
                    "Status": "Pending",
                },
                headers=["Item Code", "Description", "Status"],
                _source={
                    "document_id": str(document.id),
                    "filename": document.filename,
                    "sheet": "Examples",
                    "table_id": 1,
                    "row": 6,
                    "col_range": "A:C",
                },
            ),
        ]

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

        # Step 3: Query metadata using MetadataService
        metadata_service = MetadataService(session)

        # Get records by location
        stored_records = await metadata_service.get_records_by_location(
            document.id, sheet_name="Examples"
        )
        print(f"\n✅ Found {len(stored_records)} records in 'Examples' sheet")

        # Get citations for each record
        for record in stored_records:
            citation = await metadata_service.get_source_citation(record.id)
            if citation:
                print(f"   • {citation.to_human_readable()}")

        # Validate completeness
        report = await metadata_service.validate_source_completeness(document.id)
        print(f"\n✅ Validation: {report.complete_records}/{report.total_records} complete")
        print(f"   Completeness: {report.completeness_percentage:.1f}%")

    await engine.dispose()


async def main():
    """Run all examples."""
    print("\n" + "🚀" * 30)
    print("MetadataService 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 records stored (run Story 4.1 examples first)")
    print()

    try:
        # Run examples sequentially
        await example_1_get_source_citation()
        await example_2_query_by_location()
        await example_3_validate_source_completeness()
        await example_4_batch_citations()
        await example_5_integration_with_structured_store()

        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. Store some records first:")
        print("     python examples/structured_store_usage.py")
        print()
        raise


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