#!/usr/bin/env python3
"""Manual test script for Excel file conversion and parsing.

This script demonstrates how to use the ExcelParserService to:
    - Convert .xls files to .xlsx (using LibreOffice or xlrd fallback)
    - Convert .xlsm files to .xlsx (removing macros)
    - Parse Excel files to Markdown using Docling
    - Extract and process comments from Excel files
"""

import sys
from pathlib import Path

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

from src.extraction.excel_parser import get_excel_parser_service


def main():
    """Run the Excel conversion and parsing test."""

    # Test file - macro-enabled Excel file for conversion testing
    default_test_file = "CustomerDocument/уВ╖уВ╣уГЖуГауГХуГнуГ╝хЫ│/01.04.02.01.02.04_уВ╖уВ╣уГЖуГауГХуГнуГ╝хЫ│ц│ищЗИф╕Ашжзя╝ИцЬмчФ│ш╛╝я╝ИцЦ░шиня╝Йя╝Й.xlsm"

    print("=" * 70)
    print("EXCEL CONVERSION & PARSING TEST")
    print("=" * 70)
    print(f"\nTest file: {default_test_file}")

    test_file = Path(default_test_file)

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

    print("=" * 70)

    # Read file content
    print(f"\n[1/4] Reading file...")
    with open(test_file, 'rb') as f:
        file_content = f.read()

    file_size_mb = len(file_content) / (1024 * 1024)
    print(f"  File size: {len(file_content):,} bytes ({file_size_mb:.2f} MB)")
    print(f"  Extension: {test_file.suffix}")

    # Detect file type
    is_xls = test_file.suffix.lower() == '.xls'
    is_xlsm = test_file.suffix.lower() == '.xlsm'
    is_xlsx = test_file.suffix.lower() == '.xlsx'

    if is_xls:
        print(f"  Type: Legacy Excel format (.xls) - will be converted")
    elif is_xlsm:
        print(f"  Type: Macro-enabled Excel (.xlsm) - macros will be removed")
    elif is_xlsx:
        print(f"  Type: Standard Excel format (.xlsx)")
    else:
        print(f"  Type: Unknown - will attempt to parse")

    # Get parser service
    print(f"\n[2/4] Initializing Excel parser service...")
    parser_service = get_excel_parser_service()
    print(f"  ✓ Parser service initialized")

    # Parse the file
    print(f"\n[3/4] Parsing Excel file...")
    print("=" * 70)

    try:
        import time
        start_time = time.time()

        markdown_content = parser_service.parse_excel_file(
            file_content=file_content,
            original_filename=test_file.name,
        )

        end_time = time.time()
        duration_ms = (end_time - start_time) * 1000

        print("=" * 70)
        print(f"\n[4/5] Parsing complete!")
        print(f"  Duration: {duration_ms:.0f}ms ({duration_ms/1000:.2f}s)")
        print(f"  Output size: {len(markdown_content):,} characters")

        # Save markdown to ./tmp folder
        print(f"\n[5/5] Saving markdown to ./tmp folder...")
        from datetime import datetime

        # Create ./tmp directory if it doesn't exist
        tmp_dir = Path("./tmp")
        tmp_dir.mkdir(exist_ok=True)

        # Generate output filename
        output_filename = f"markdown_{datetime.now().strftime('%Y%m%d_%H%M%S')}.md"
        output_path = tmp_dir / output_filename

        # Save markdown content
        with open(output_path, 'w', encoding='utf-8') as f:
            f.write(markdown_content)

        print(f"  ✓ Saved to: {output_path}")
        print(f"  File size: {output_path.stat().st_size:,} bytes")

        # Display preview
        print("\n" + "=" * 70)
        print("MARKDOWN OUTPUT (Preview - first 2000 characters)")
        print("=" * 70)
        preview = markdown_content[:2000]
        print(preview)
        if len(markdown_content) > 2000:
            print(f"\n... ({len(markdown_content) - 2000:,} more characters)")
        print("=" * 70)

        print("\n" + "=" * 70)
        print("Test completed successfully!")
        print("=" * 70)

    except Exception as e:
        print("\n" + "=" * 70)
        print("PARSING FAILED!")
        print("=" * 70)
        print(f"\nError: {e}")

        import traceback
        print("\nFull traceback:")
        traceback.print_exc()


if __name__ == "__main__":
    main()