#!/usr/bin/env python3
"""Manual test script for multi-pass extraction pipeline.

This script tests the complete 4-pass pipeline with a comprehensive test file:
    PASS 1: Symbol Detection - Detects symbol dictionary sheet
    PASS 2: Table Extraction - Extracts tables from all sheets
    PASS 3: Symbol Resolution - Resolves symbols in extracted content
    PASS 4: Cross-Reference Detection - Detects cross-sheet references

Usage:
    # From project root with virtual environment activated:
    python tests/manual/test_multipass_manual.py

    # Or with pytest:
    pytest tests/manual/test_multipass_manual.py -v -s
"""

import sys
from pathlib import Path
from uuid import uuid4

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

from unittest.mock import Mock

from src.extraction.pipeline import ExtractionPipeline, ExtractionSummary


def create_mock_session(file_path: str, filename: str):
    """Create a mock database session for testing."""
    from src.db import models as db_models

    session = Mock()
    session.commit = Mock()
    session.rollback = Mock()
    session.bulk_insert_mappings = Mock()

    # Create mock document
    doc = Mock(spec=db_models.Document)
    doc.id = uuid4()
    doc.filename = filename
    doc.file_path = file_path
    doc.status = "pending"
    doc.sheet_count = None
    doc.record_count = None
    doc.processed_at = None
    doc.error_message = None
    doc.api_key_id = uuid4()

    session.scalar = Mock(return_value=doc)

    # Mock query chain for symbol resolution and cross-ref detection
    mock_query = Mock()
    session.query = Mock(return_value=mock_query)
    mock_query.filter = Mock(return_value=mock_query)
    mock_query.order_by = Mock(return_value=mock_query)
    mock_query.all = Mock(return_value=[])
    mock_query.first = Mock(return_value=None)

    return session, doc


def print_summary(summary: ExtractionSummary):
    """Pretty print extraction summary."""
    print("\n" + "=" * 60)
    print("EXTRACTION SUMMARY")
    print("=" * 60)

    print(f"\nDocument ID: {summary.document_id}")
    print(f"Duration: {summary.duration_ms}ms")

    print("\n--- PASS 2: Table Extraction ---")
    print(f"  Sheets processed: {summary.sheets_processed}")
    print(f"  Tables detected: {summary.tables_detected}")
    print(f"  Records extracted: {summary.records_extracted}")

    print("\n--- PASS 1: Symbol Detection ---")
    print(f"  Symbols detected: {summary.symbols_detected}")

    print("\n--- PASS 3: Symbol Resolution ---")
    print(f"  Symbols resolved: {summary.symbols_resolved}")
    print(f"  Unresolved symbols: {summary.unresolved_symbols}")

    print("\n--- PASS 4: Cross-Reference Detection ---")
    print(f"  Cross-references found: {summary.cross_references_found}")
    print(f"  Cross-references resolved: {summary.cross_references_resolved}")
    print(f"  Cross-references unresolved: {summary.cross_references_unresolved}")

    if summary.warnings:
        print("\n--- Warnings ---")
        for warning in summary.warnings:
            print(f"  - {warning}")

    print("\n" + "=" * 60)


def test_multipass_comprehensive():
    """Test multi-pass pipeline with comprehensive test file."""
    # Get test file path
    test_file = project_root / "tests" / "fixtures" / "sample_excel" / "test_multipass_comprehensive.xlsx"

    if not test_file.exists():
        print(f"ERROR: Test file not found: {test_file}")
        print("Run the Excel file creation script first.")
        return False

    print(f"Testing with file: {test_file}")
    print(f"File size: {test_file.stat().st_size} bytes")

    # Create mock session
    session, doc = create_mock_session(str(test_file), test_file.name)

    # Run pipeline
    print("\nRunning multi-pass extraction pipeline...")
    pipeline = ExtractionPipeline(db_session=session)
    summary = pipeline.process_document(doc.id)

    # Print results
    print_summary(summary)

    # Verify results
    print("\n--- Verification ---")
    checks = []

    # Check basic extraction worked
    if summary.sheets_processed >= 5:
        checks.append(("Sheets processed >= 5", True))
    else:
        checks.append(("Sheets processed >= 5", False))

    if summary.tables_detected >= 4:
        checks.append(("Tables detected >= 4", True))
    else:
        checks.append(("Tables detected >= 4", False))

    if summary.records_extracted > 0:
        checks.append(("Records extracted > 0", True))
    else:
        checks.append(("Records extracted > 0", False))

    # Check document status
    if doc.status == "completed":
        checks.append(("Document status = completed", True))
    else:
        checks.append(("Document status = completed", False))

    # Check duration is reasonable
    if summary.duration_ms > 0:
        checks.append(("Duration > 0", True))
    else:
        checks.append(("Duration > 0", False))

    # Print check results
    all_passed = True
    for check_name, passed in checks:
        status = "PASS" if passed else "FAIL"
        print(f"  [{status}] {check_name}")
        if not passed:
            all_passed = False

    print("\n" + "=" * 60)
    if all_passed:
        print("ALL CHECKS PASSED!")
    else:
        print("SOME CHECKS FAILED!")
    print("=" * 60)

    return all_passed


def test_with_standard_table():
    """Test pipeline with standard table file (no symbols/cross-refs expected)."""
    test_file = project_root / "tests" / "fixtures" / "sample_excel" / "test_standard_table.xlsx"

    if not test_file.exists():
        print(f"SKIP: Test file not found: {test_file}")
        return True

    print(f"\nTesting with standard table: {test_file.name}")

    session, doc = create_mock_session(str(test_file), test_file.name)
    pipeline = ExtractionPipeline(db_session=session)
    summary = pipeline.process_document(doc.id)

    print(f"  Records extracted: {summary.records_extracted}")
    print(f"  Status: {doc.status}")

    return doc.status == "completed"


def test_with_merged_data():
    """Test pipeline with merged cells file."""
    test_file = project_root / "tests" / "fixtures" / "sample_excel" / "test_merged_data.xlsx"

    if not test_file.exists():
        print(f"SKIP: Test file not found: {test_file}")
        return True

    print(f"\nTesting with merged data: {test_file.name}")

    session, doc = create_mock_session(str(test_file), test_file.name)
    pipeline = ExtractionPipeline(db_session=session)
    summary = pipeline.process_document(doc.id)

    print(f"  Records extracted: {summary.records_extracted}")
    print(f"  Status: {doc.status}")

    return doc.status == "completed"


if __name__ == "__main__":
    print("=" * 60)
    print("MULTI-PASS PIPELINE MANUAL TEST")
    print("=" * 60)

    results = []

    # Test 1: Comprehensive test file
    print("\n[TEST 1] Comprehensive multi-pass test file")
    results.append(("Comprehensive test", test_multipass_comprehensive()))

    # Test 2: Standard table
    print("\n[TEST 2] Standard table test")
    results.append(("Standard table", test_with_standard_table()))

    # Test 3: Merged data
    print("\n[TEST 3] Merged data test")
    results.append(("Merged data", test_with_merged_data()))

    # Final summary
    print("\n" + "=" * 60)
    print("FINAL RESULTS")
    print("=" * 60)

    all_passed = True
    for test_name, passed in results:
        status = "PASS" if passed else "FAIL"
        print(f"  [{status}] {test_name}")
        if not passed:
            all_passed = False

    print()
    sys.exit(0 if all_passed else 1)