#!/usr/bin/env python3
"""Check if a document exists in the database"""

import sys
from aumentum_browser_service import DEFAULT_DB_CONFIG
from boundary_document_service import BoundaryDocumentService
from aumentum_browser_service import DEFAULT_CONTENTSTORE_BASE

document_number = "BND-DOC-20251205-010247-0142"

try:
    # Initialize service
    service = BoundaryDocumentService(
        contentstore_base=DEFAULT_CONTENTSTORE_BASE,
        db_config=DEFAULT_DB_CONFIG
    )
    
    # Get database connection
    conn = service._get_db_connection()
    cursor = conn.cursor()
    
    # Check in boundary_document table
    query = """
        SELECT 
            id, document_number, document_type, title, description,
            file_name, file_size, page_count, uploaded_date, uploaded_by,
            related_boundary_id, related_dispute_id, related_survey_id, related_treaty_id,
            status, created_at, content_path
        FROM boundary_document
        WHERE document_number = %s
    """
    
    cursor.execute(query, (document_number,))
    result = cursor.fetchone()
    
    if result:
        columns = [col[0] for col in cursor.description]
        doc_dict = dict(zip(columns, result))
        
        print("✅ DOCUMENT FOUND in boundary_document table:")
        print(f"   ID: {doc_dict['id']}")
        print(f"   Document Number: {doc_dict['document_number']}")
        print(f"   Document Type: {doc_dict['document_type']}")
        print(f"   Title: {doc_dict['title']}")
        print(f"   Description: {doc_dict['description'] or 'N/A'}")
        print(f"   File Name: {doc_dict['file_name']}")
        print(f"   File Size: {doc_dict['file_size']} bytes ({doc_dict['file_size']/1024:.2f} KB)")
        print(f"   Page Count: {doc_dict['page_count']}")
        print(f"   Content Path: {doc_dict['content_path']}")
        print(f"   Uploaded Date: {doc_dict['uploaded_date']}")
        print(f"   Uploaded By: {doc_dict['uploaded_by'] or 'N/A'}")
        print(f"   Related Boundary ID: {doc_dict['related_boundary_id'] or 'None'}")
        print(f"   Related Dispute ID: {doc_dict['related_dispute_id'] or 'None'}")
        print(f"   Related Survey ID: {doc_dict['related_survey_id'] or 'None'}")
        print(f"   Related Treaty ID: {doc_dict['related_treaty_id'] or 'None'}")
        print(f"   Status: {doc_dict['status']}")
        print(f"   Created At: {doc_dict['created_at']}")
    else:
        print(f"❌ DOCUMENT NOT FOUND: {document_number}")
        print("\nChecking for similar documents...")
        
        # Check for similar document numbers
        similar_query = """
            SELECT document_number, title, uploaded_date
            FROM boundary_document
            WHERE document_number LIKE %s
            ORDER BY uploaded_date DESC
            LIMIT 5
        """
        cursor.execute(similar_query, (f"%{document_number[:20]}%",))
        similar = cursor.fetchall()
        
        if similar:
            print(f"\nFound {len(similar)} similar documents:")
            for row in similar:
                print(f"   - {row[0]} (Title: {row[1]}, Uploaded: {row[2]})")
        else:
            print("   No similar documents found.")
    
    # Also check total count
    cursor.execute("SELECT COUNT(*) FROM boundary_document")
    total_count = cursor.fetchone()[0]
    print(f"\n📊 Total documents in boundary_document table: {total_count}")
    
    # List recent documents
    cursor.execute("""
        SELECT document_number, title, uploaded_date
        FROM boundary_document
        ORDER BY uploaded_date DESC
        LIMIT 5
    """)
    recent = cursor.fetchall()
    if recent:
        print(f"\n📋 Recent documents (last 5):")
        for row in recent:
            print(f"   - {row[0]}: {row[1]} (Uploaded: {row[2]})")
    
    cursor.close()
    conn.close()
    
except Exception as e:
    print(f"❌ Error checking database: {e}")
    import traceback
    traceback.print_exc()
    sys.exit(1)