#!/usr/bin/env python3
"""Manual test script for Excel to HTML Converter with Border Detection Preprocessing.

This script demonstrates and tests the complete Excel to HTML conversion pipeline:
    - Step 1: Format conversion (.xls/.xlsm → .xlsx)
    - Step 2: Image extraction and description embedding
    - Step 3: Shape annotation extraction and embedding
    - Step 4: Comment extraction and marker embedding
    - Step 5: Border detection and separator row insertion
    - Step 6: HTML conversion via Docling

Usage:
    python tests/manual/test_excel_to_html_converter.py [file_path]

Examples:
    # Test with default file
    python tests/manual/test_excel_to_html_converter.py

    # Test with specific file
    python tests/manual/test_excel_to_html_converter.py CustomerDocument/path/to/file.xlsx

    # Test with .xls file
    python tests/manual/test_excel_to_html_converter.py CustomerDocument/path/to/file.xls

    # Test with .xlsm file
    python tests/manual/test_excel_to_html_converter.py CustomerDocument/path/to/file.xlsm
"""

import sys
import time
from pathlib import Path
from datetime import datetime

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

from src.extraction_v2.excel_to_html_converter import ExcelToHtmlConverter


def print_header(title: str):
    """Print a formatted section header."""
    print("\n" + "=" * 80)
    print(f"  {title}")
    print("=" * 80)


def print_step(step_num: int, total_steps: int, description: str):
    """Print a step indicator."""
    print(f"\n[Step {step_num}/{total_steps}] {description}")
    print("-" * 80)


def test_excel_to_html_conversion(file_path: str):
    """
    Test the complete Excel to HTML conversion pipeline.

    Args:
        file_path: Path to Excel file to test
    """
    print_header("EXCEL TO HTML CONVERTER - COMPREHENSIVE TEST")

    test_file = Path(file_path)

    # Validate file exists
    if not test_file.exists():
        print(f"❌ Error: File not found: {test_file}")
        return

    print(f"\n📁 Test File: {test_file}")
    print(f"   Extension: {test_file.suffix}")

    # Get file size
    file_size_mb = test_file.stat().st_size / (1024 * 1024)
    print(f"   Size: {test_file.stat().st_size:,} bytes ({file_size_mb:.2f} MB)")

    # Identify file type
    ext = test_file.suffix.lower()
    if ext == '.xls':
        print(f"   Type: Legacy Excel (.xls) - Will use pandas+xlrd conversion")
    elif ext == '.xlsm':
        print(f"   Type: Macro-enabled Excel (.xlsm) - Will use LibreOffice conversion")
    elif ext == '.xlsx':
        print(f"   Type: Standard Excel (.xlsx) - No conversion needed")
    else:
        print(f"   Type: Unknown format - May fail")

    # Create converter
    print_step(1, 6, "Initializing ExcelToHtmlConverter")
    try:
        converter = ExcelToHtmlConverter()
        print("✅ Converter initialized successfully")
    except Exception as e:
        print(f"❌ Failed to initialize converter: {e}")
        return

    # Run conversion
    print_step(2, 6, "Running Complete Conversion Pipeline")
    print("\nPipeline stages:")
    print("  1️⃣  Format conversion (.xls/.xlsm → .xlsx)")
    print("  2️⃣  Image extraction and description embedding")
    print("  3️⃣  Shape annotation extraction and embedding")
    print("  4️⃣  Comment extraction and marker embedding")
    print("  5️⃣  Border detection and separator row insertion")
    print("  6️⃣  HTML conversion via Docling")
    print("\n⏱️  Starting conversion...")

    start_time = time.time()

    try:
        html_content = converter.convert_to_html(str(test_file))

        end_time = time.time()
        duration_sec = end_time - start_time

        if html_content is None:
            print("\n❌ Conversion failed - returned None")
            return

        print(f"\n✅ Conversion completed successfully!")
        print(f"   Duration: {duration_sec:.2f}s ({duration_sec * 1000:.0f}ms)")
        print(f"   Output size: {len(html_content):,} characters")

    except Exception as e:
        print(f"\n❌ Conversion failed with exception: {e}")
        import traceback
        print("\nFull traceback:")
        traceback.print_exc()
        return

    # Analyze HTML output
    print_step(3, 6, "Analyzing HTML Output")

    # Count tables
    table_count = html_content.count('<table')
    print(f"   Tables detected: {table_count}")

    # Count images (if any descriptions were embedded)
    image_desc_count = html_content.count('[Image Description]')
    if image_desc_count > 0:
        print(f"   Image descriptions: {image_desc_count}")

    # Count comments (if any were embedded)
    comment_count = html_content.count('[COMMENT:')
    if comment_count > 0:
        print(f"   Comments embedded: {comment_count}")

    # Count annotations (if any were embedded)
    annotation_count = html_content.count('<annotation')
    if annotation_count > 0:
        print(f"   Shape annotations: {annotation_count}")

    # Check for separator rows (empty rows between tables)
    empty_row_count = html_content.count('<tr></tr>')
    if empty_row_count > 0:
        print(f"   Empty separator rows: {empty_row_count}")

    # Save HTML output
    print_step(4, 6, "Saving HTML Output")

    # Create output directory
    output_dir = Path("./tmp")
    output_dir.mkdir(exist_ok=True)

    # Generate output filename
    timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
    safe_filename = test_file.stem.replace('/', '_').replace('\\', '_')
    output_filename = f"html_{safe_filename}_{timestamp}.html"
    output_path = output_dir / output_filename

    # Save HTML
    try:
        with open(output_path, 'w', encoding='utf-8') as f:
            f.write(html_content)

        print(f"✅ HTML saved to: {output_path}")
        print(f"   File size: {output_path.stat().st_size:,} bytes")
    except Exception as e:
        print(f"❌ Failed to save HTML: {e}")
        return

    # Display preview
    print_step(5, 6, "HTML Preview (First 2000 characters)")
    print()
    preview = html_content[:2000]
    print(preview)
    if len(html_content) > 2000:
        print(f"\n... ({len(html_content) - 2000:,} more characters)")

    # Test sheet name retrieval
    print_step(6, 6, "Testing Sheet Name Retrieval")

    if hasattr(converter, '_last_sheet_names') and converter._last_sheet_names:
        print(f"   Sheet names extracted: {len(converter._last_sheet_names)}")
        for idx, sheet_name in enumerate(converter._last_sheet_names):
            print(f"     [{idx}] {sheet_name}")

        # Test getting sheet name for first table
        if table_count > 0:
            sheet_name = converter.get_sheet_name_for_table(0)
            if sheet_name:
                print(f"\n   First table is from sheet: '{sheet_name}'")
            else:
                print("\n   Could not determine sheet name for first table")
    else:
        print("   No sheet names available")

    # Summary
    print_header("TEST SUMMARY")
    print(f"\n✅ Test completed successfully!")
    print(f"\n📊 Conversion Statistics:")
    print(f"   • Input file: {test_file.name}")
    print(f"   • Input size: {file_size_mb:.2f} MB")
    print(f"   • Output size: {len(html_content):,} characters")
    print(f"   • Duration: {duration_sec:.2f}s")
    print(f"   • Tables detected: {table_count}")
    if image_desc_count > 0:
        print(f"   • Image descriptions: {image_desc_count}")
    if comment_count > 0:
        print(f"   • Comments embedded: {comment_count}")
    if annotation_count > 0:
        print(f"   • Shape annotations: {annotation_count}")
    print(f"\n📄 Output saved to: {output_path}")
    print("\n" + "=" * 80)


def main():
    """Main entry point for the test script."""

    # Default test file
    default_file = "CustomerDocument/уВ╖уВ╣уГЖуГауГХуГнуГ╝хЫ│/01.04.02.01.04.04_уВ╖уВ╣уГЖуГауГХуГнуГ╝хЫ│ц│ищЗИф╕Ашжзя╝ИцЬмчТ│ш╛╝я╝Йхх╗Гцнвя╝Й.xlsm"

    # Get file path from command line or use default
    if len(sys.argv) > 1:
        test_file = sys.argv[1]
    else:
        test_file = default_file
        print(f"ℹ️  No file specified, using default: {test_file}")
        print(f"   Usage: python {sys.argv[0]} [file_path]\n")

    # Run the test
    try:
        test_excel_to_html_conversion(test_file)
    except KeyboardInterrupt:
        print("\n\n⚠️  Test interrupted by user")
        sys.exit(1)
    except Exception as e:
        print(f"\n\n❌ Test failed with unexpected error: {e}")
        import traceback
        traceback.print_exc()
        sys.exit(1)


if __name__ == "__main__":
    main()