#!/usr/bin/env python3
"""Test V2 pipeline with various customer files."""

import sys
from pathlib import Path
from src.db.session import get_sync_session
from src.db import models as db_models
from src.extraction_v2.pipeline import ExtractionPipelineV2

# Sample files representing different scenarios
test_files = [
    # .xls legacy format (recently fixed)
    ("AIHackathonFolder/Phase2/CustomerDocument/画面設計書/01_共通/02_画面遷移図/01.01.02.02.07_画面遷移図_帳票情報流通-ＢＯ状況出力部分.xls", "Legacy .xls"),
    
    # .xlsx standard
    ("AIHackathonFolder/Phase2/CustomerDocument/画面設計書/02_画面仕様/01_送信確認画面/削除_01.02.01.03.05_帳票送信運用チェック.xlsx", "Standard .xlsx"),
    
    # .xlsm with macros
    ("AIHackathonFolder/Phase2/CustomerDocument/システム定義/1.1 JP1スケジュール定義.xlsx", "Multi-sheet"),
    
    # Already tested successfully
    ("storage/011c1a18-0438-4111-b91a-1869999cee60/帳票管理情報.xlsx", "Known good (inflated sheets)"),
]

def test_file(file_path: str, description: str):
    """Test a single file with V2 pipeline."""
    print(f"\n{'='*80}")
    print(f"Testing: {description}")
    print(f"File: {file_path}")
    print(f"{'='*80}")
    
    full_path = Path(file_path)
    if not full_path.exists():
        print(f"✗ SKIP: File not found")
        return None
    
    try:
        # Create test document
        with get_sync_session() as session:
            doc = db_models.Document(
                filename=full_path.name,
                file_path=str(full_path.absolute()),
                status="pending"
            )
            session.add(doc)
            session.commit()
            doc_id = doc.id
            
            print(f"Document ID: {doc_id}")
            
            # Run V2 pipeline
            print(f"\nRunning V2 extraction...")
            v2_pipeline = ExtractionPipelineV2()
            result = v2_pipeline.process_document_sync(doc_id, str(full_path.absolute()))
            
            if result.success:
                print(f"\n✓ SUCCESS")
                print(f"  Records: {result.record_count}")
                print(f"  Tables: {result.table_count}")
                
                # Show sheet distribution
                from collections import Counter
                sheet_counts = Counter(r['_source']['sheet'] for r in result.records)
                print(f"  Sheets:")
                for sheet, count in sheet_counts.items():
                    print(f"    - {sheet}: {count} records")
                    
                return {
                    'status': 'success',
                    'records': result.record_count,
                    'tables': result.table_count,
                    'sheets': len(sheet_counts)
                }
            else:
                print(f"\n✗ FAILED")
                print(f"  Error: {result.error}")
                return {
                    'status': 'failed',
                    'error': result.error
                }
                
    except Exception as e:
        print(f"\n✗ EXCEPTION")
        print(f"  Error: {e}")
        import traceback
        traceback.print_exc()
        return {
            'status': 'exception',
            'error': str(e)
        }

def main():
    print("="*80)
    print("V2 PIPELINE - CUSTOMER FILE TESTING")
    print("="*80)
    
    results = []
    for file_path, description in test_files:
        result = test_file(file_path, description)
        results.append({
            'file': file_path,
            'description': description,
            'result': result
        })
    
    # Summary
    print(f"\n\n{'='*80}")
    print(f"SUMMARY")
    print(f"{'='*80}")
    
    success_count = sum(1 for r in results if r['result'] and r['result']['status'] == 'success')
    total_count = len([r for r in results if r['result'] is not None])
    
    print(f"\nFiles tested: {total_count}")
    print(f"Successful: {success_count}/{total_count} ({success_count/total_count*100:.1f}%)")
    
    print(f"\nResults:")
    for r in results:
        if r['result'] is None:
            status_icon = "⊘"
            status_text = "SKIPPED"
        elif r['result']['status'] == 'success':
            status_icon = "✓"
            status_text = f"OK ({r['result']['records']} records, {r['result']['tables']} tables)"
        else:
            status_icon = "✗"
            status_text = f"FAILED: {r['result'].get('error', 'Unknown')[:50]}"
        
        print(f"  {status_icon} {r['description']}")
        print(f"      {status_text}")

if __name__ == "__main__":
    main()