"""Quick start guide for using MetadataService.

This demonstrates the most common use cases.
"""

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

# 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, ExtractedRecord as ExtractedRecordModel
from src.extraction.models import ExtractedRecord
from src.knowledge.metadata_service import MetadataService
from src.knowledge.structured_store import StructuredStore


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

    # Setup database connection
    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:
        # ========================================
        # Example 1: Store some records first
        # ========================================
        print("\n📝 Step 1: Creating test data...")

        # Create a document
        document = Document(
            id=uuid4(),
            filename="quickstart_example.xlsx",
            file_path="/tmp/quickstart_example.xlsx",
            status="completed",
        )
        session.add(document)
        await session.commit()
        await session.refresh(document)
        print(f"   ✅ Created document: {document.filename}")

        # Store some records
        records = [
            ExtractedRecord(
                content={
                    "Item Code": "QS-001",
                    "Description": "Quick Start Item 1",
                    "Status": "Active",
                },
                headers=["Item Code", "Description", "Status"],
                _source={
                    "document_id": str(document.id),
                    "filename": document.filename,
                    "sheet": "Data",
                    "table_id": 1,
                    "row": 10,
                    "col_range": "A:C",
                },
            ),
            ExtractedRecord(
                content={
                    "Item Code": "QS-002",
                    "Description": "Quick Start Item 2",
                    "Status": "Pending",
                },
                headers=["Item Code", "Description", "Status"],
                _source={
                    "document_id": str(document.id),
                    "filename": document.filename,
                    "sheet": "Data",
                    "table_id": 1,
                    "row": 11,
                    "col_range": "A:C",
                },
            ),
        ]

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

        # ========================================
        # Example 2: Get a source citation
        # ========================================
        print("\n🔍 Step 2: Getting source citation...")

        # Query a record
        stmt = select(ExtractedRecordModel).where(
            ExtractedRecordModel.document_id == document.id
        ).limit(1)
        result = await session.execute(stmt)
        record = result.scalars().first()

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

        if citation:
            print(f"\n   📄 Citation (Human-readable):")
            print(f"      {citation.to_human_readable()}")

            print(f"\n   📄 Citation (JSON):")
            import json
            print(f"      {json.dumps(citation.to_json(), indent=2)}")

        # ========================================
        # Example 3: Query by location
        # ========================================
        print("\n🔍 Step 3: Querying by location...")

        # Get all records from 'Data' sheet
        sheet_records = await service.get_records_by_location(
            document.id, sheet_name="Data"
        )
        print(f"\n   ✅ Found {len(sheet_records)} records in 'Data' sheet")

        for rec in sheet_records:
            item_code = rec.content.get("Item Code")
            print(f"      • Row {rec.row_number}: {item_code}")

        # Get records in a specific row range
        range_records = await service.get_records_by_location(
            document.id, sheet_name="Data", row_range=(10, 10)
        )
        print(f"\n   ✅ Found {len(range_records)} records in rows 10-10")

        # ========================================
        # Example 4: Validate completeness
        # ========================================
        print("\n✓ Step 4: Validating source metadata...")

        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"      With col_range: {report.records_with_col_range}")
        print(f"      With headers: {report.records_with_headers}")

        if report.is_valid:
            print(f"\n      ✅ All records have complete source metadata!")
        else:
            print(f"\n      ⚠️  Issues found: {len(report.missing_required_fields)}")

        # ========================================
        # Example 5: Batch citations
        # ========================================
        print("\n🔍 Step 5: Getting batch citations...")

        # Get record IDs
        stmt = select(ExtractedRecordModel).where(
            ExtractedRecordModel.document_id == document.id
        )
        result = await session.execute(stmt)
        all_records = result.scalars().all()
        record_ids = [r.id for r in all_records]

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

        print(f"\n   ✅ Retrieved {len(citations)} citations in one query")
        for i, (record_id, cit) in enumerate(citations.items(), 1):
            print(f"      {i}. {cit.to_human_readable()}")

    await engine.dispose()

    print("\n" + "=" * 60)
    print("✅ Quick Start Complete!")
    print("=" * 60)
    print("\nNext steps:")
    print("  1. Check the full examples: python examples/metadata_service_usage.py")
    print("  2. Run the unit tests: pytest tests/knowledge/test_metadata_service.py")
    print("  3. Read the documentation in the story file")
    print()


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