#!/usr/bin/env python3
"""Manual test script for table extraction from Excel files.

This script demonstrates the full pipeline:
1. Convert Excel file (.xlsm/.xls/.xlsx) to Markdown
2. Extract tables from the Markdown content
3. Display table structure and data
"""

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
from src.extraction.markdown_table_extractor import get_table_extractor


def main():
    """Run the table extraction test."""

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

    print("=" * 70)
    print("EXCEL TO MARKDOWN TABLE EXTRACTION 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)

    # Step 1: Read file
    print(f"\n[1/4] Reading Excel 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}")

    # Step 2: Convert to Markdown
    print(f"\n[2/4] Converting Excel to Markdown...")
    parser_service = get_excel_parser_service()

    import time
    start_time = time.time()

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

    conversion_time = time.time() - start_time
    print(f"  ✓ Conversion complete ({conversion_time:.2f}s)")
    print(f"  Markdown size: {len(markdown_content):,} characters")

    # Step 3: Extract tables
    print(f"\n[3/4] Extracting tables from Markdown...")
    table_extractor = get_table_extractor()

    start_time = time.time()
    tables = table_extractor.extract_tables(markdown_content)
    extraction_time = time.time() - start_time

    print(f"  ✓ Extraction complete ({extraction_time:.2f}s)")
    print(f"  Tables found: {len(tables)}")

    # Step 4: Save tables to JSON
    print(f"\n[4/5] Saving tables to JSON...")

    if not tables:
        print("No tables found in the markdown content.")
        return

    import json
    from datetime import datetime

    # Prepare tables data for JSON serialization (tables only, no metadata)
    tables_data = []
    for i, table in enumerate(tables, 1):
        table_dict = {
            "table_number": i,
            "headers": table.headers,
            "rows": table.to_dict_list()
        }
        tables_data.append(table_dict)

    # Summary statistics for display
    total_rows = sum(table.num_rows for table in tables)
    total_cells = sum(table.num_rows * table.num_columns for table in tables)

    # Save to ./tmp folder
    output_filename = f"extracted_tables_{datetime.now().strftime('%Y%m%d_%H%M%S')}.json"
    output_path = Path("./tmp") / output_filename

    with open(output_path, 'w', encoding='utf-8') as f:
        json.dump(tables_data, f, ensure_ascii=False, indent=2)

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

    # Step 5: Display results
    print(f"\n[5/5] Table Analysis")
    print("=" * 70)

    print(f"\nSummary:")
    print(f"  Total tables: {len(tables)}")
    print(f"  Total data rows: {total_rows:,}")
    print(f"  Total cells: {total_cells:,}")

    # Show details for each table
    print(f"\nTable Details:")
    print("-" * 70)

    for i, table in enumerate(tables, 1):
        print(f"\nTable {i}:")
        print(f"  Location: Lines {table.source_start_line}-{table.source_end_line}")
        print(f"  Dimensions: {table.num_columns} columns × {table.num_rows} rows")
        print(f"  Headers: {table.headers}")

        # Show first few rows as preview
        preview_rows = min(3, table.num_rows)
        if preview_rows > 0:
            print(f"  Preview (first {preview_rows} rows):")
            for row_idx in range(preview_rows):
                row = table.rows[row_idx]
                # Truncate long cell values
                cells_preview = [
                    cell[:30] + "..." if len(cell) > 30 else cell
                    for cell in row.cells
                ]
                print(f"    Row {row_idx + 1}: {cells_preview}")

        if table.num_rows > preview_rows:
            print(f"    ... ({table.num_rows - preview_rows} more rows)")

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


if __name__ == "__main__":
    main()