"""
Analyze the flowchart Excel file to understand its structure.

This script will:
1. Convert .xls to .xlsx
2. Extract shapes and their properties
3. Extract connectors and connection points
4. Document the structure for implementation
"""

import sys
import os
import tempfile
import zipfile
from pathlib import Path

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

from src.extraction_v2.excel_loader import convert_xls_to_xlsx
from openpyxl import load_workbook


def analyze_file(xls_path: str):
    """Analyze Excel file structure."""

    print(f"=" * 80)
    print(f"ANALYZING: {os.path.basename(xls_path)}")
    print(f"=" * 80)

    # Step 1: Convert to xlsx using LibreOffice (preserves shapes!)
    print("\n[1/5] Converting .xls to .xlsx with LibreOffice...")
    temp_dir = tempfile.mkdtemp()

    # Use LibreOffice conversion to preserve drawing objects
    from src.extraction_v2.document_converter import DocumentToHtmlConverter
    converter = DocumentToHtmlConverter()
    xlsx_path = converter._convert_to_xlsx_via_libreoffice(xls_path)

    if not xlsx_path:
        print("❌ LibreOffice conversion failed!")
        print("💡 Trying pandas conversion (will lose shapes)...")
        xlsx_path = os.path.join(temp_dir, "converted.xlsx")
        converted = convert_xls_to_xlsx(xls_path, xlsx_path)
        if not converted:
            print("❌ Pandas conversion also failed!")
            return
        print("⚠ WARNING: Shapes may be lost with pandas conversion")

    print(f"✓ Converted to: {xlsx_path}")

    # Step 2: Load workbook
    print("\n[2/5] Loading workbook...")
    wb = load_workbook(xlsx_path, data_only=False)
    print(f"✓ Sheets: {wb.sheetnames}")

    # Step 3: Analyze each sheet
    print("\n[3/5] Analyzing sheets for shapes and connectors...")

    for sheet_name in wb.sheetnames:
        sheet = wb[sheet_name]
        print(f"\n  Sheet: '{sheet_name}'")
        print(f"  " + "-" * 60)

        # Check for shapes via the shape extraction approach
        # Look for drawing relationships
        if hasattr(sheet, '_rels') and sheet._rels:
            print(f"  ✓ Has drawing relationships: {len(sheet._rels)} rels")

            # Try to find drawing files
            for rel in sheet._rels:
                if hasattr(rel, 'target') and 'drawing' in rel.target.lower():
                    print(f"    - Drawing file: {rel.target}")
        else:
            print(f"  ✗ No drawing relationships found")

        # Check merged cells (sometimes flowcharts use these)
        if sheet.merged_cells:
            print(f"  ✓ Merged cells: {len(sheet.merged_cells.ranges)} ranges")
            for merged in list(sheet.merged_cells.ranges)[:3]:  # Show first 3
                min_col, min_row, max_col, max_row = merged.bounds
                cell = sheet.cell(min_row, min_col)
                value = str(cell.value)[:30] if cell.value else "(empty)"
                print(f"    - {merged}: {value}")

        # Check for images
        if hasattr(sheet, '_images') and sheet._images:
            print(f"  ✓ Images: {len(sheet._images)}")

    # Step 4: Inspect XML structure for connectors
    print("\n[4/5] Inspecting XML structure for connectors...")

    try:
        with zipfile.ZipFile(xlsx_path, 'r') as zip_ref:
            # List all files
            all_files = zip_ref.namelist()

            # Find drawing files
            drawing_files = [f for f in all_files if 'xl/drawings/' in f and f.endswith('.xml')]
            print(f"  ✓ Found {len(drawing_files)} drawing XML files")

            for drawing_file in drawing_files:
                print(f"\n  Analyzing: {drawing_file}")
                print(f"  " + "─" * 60)

                # Read XML content
                xml_content = zip_ref.read(drawing_file).decode('utf-8')

                # Look for connector shapes
                if '<xdr:cxnSp' in xml_content or 'cxnSp' in xml_content:
                    connector_count = xml_content.count('<xdr:cxnSp')
                    print(f"  ✓ Connectors (cxnSp): {connector_count}")

                # Look for regular shapes
                if '<xdr:sp' in xml_content:
                    shape_count = xml_content.count('<xdr:sp>')
                    print(f"  ✓ Shapes (sp): {shape_count}")

                # Look for connection points
                if 'stCxn' in xml_content or 'endCxn' in xml_content:
                    start_conn = xml_content.count('stCxn')
                    end_conn = xml_content.count('endCxn')
                    print(f"  ✓ Start connections: {start_conn}")
                    print(f"  ✓ End connections: {end_conn}")

                # Extract a sample connector for analysis
                if '<xdr:cxnSp' in xml_content:
                    print(f"\n  Sample connector XML:")
                    print(f"  " + "┄" * 60)

                    # Find first connector
                    start = xml_content.find('<xdr:cxnSp')
                    if start != -1:
                        end = xml_content.find('</xdr:cxnSp>', start) + len('</xdr:cxnSp>')
                        sample = xml_content[start:end]

                        # Pretty print (simple indent)
                        lines = sample.split('>')
                        for line in lines[:10]:  # First 10 lines
                            if line.strip():
                                print(f"    {line.strip()}>")

                        if len(lines) > 10:
                            print(f"    ... ({len(lines) - 10} more lines)")

    except Exception as e:
        print(f"  ❌ Error inspecting XML: {e}")

    # Step 5: Try using shape extraction service
    print("\n[5/5] Testing shape extraction service...")

    try:
        from src.extraction_v2.shape_extractor import ShapeExtractor

        # Extract shapes
        extractor = ShapeExtractor()

        print(f"  Running shape extraction...")
        shapes = extractor.extract_shapes_from_file(xlsx_path)

        print(f"  ✓ Extracted {len(shapes)} shapes")

        # Show details of each shape
        for i, shape in enumerate(shapes, 1):
            print(f"\n  Shape {i}:")
            print(f"    Text: {shape.get('text', '')[:50] if shape.get('text') else '(no text)'}")
            print(f"    Type: {shape.get('shape_type', 'unknown')}")
            print(f"    Sheet: {shape.get('sheet_name', 'unknown')}")
            print(f"    Name: {shape.get('shape_name', 'N/A')}")

            from_cell = shape.get('from_cell', '')
            to_cell = shape.get('to_cell', '')
            if from_cell or to_cell:
                print(f"    Position: {from_cell} to {to_cell}")

    except ImportError:
        print(f"  ⚠ Shape extraction service not available")
    except Exception as e:
        print(f"  ❌ Error: {e}")
        import traceback
        traceback.print_exc()

    print("\n" + "=" * 80)
    print("ANALYSIS COMPLETE")
    print("=" * 80)

    # Cleanup
    wb.close()


if __name__ == "__main__":
    file_path = "./CustomerDocument/чФ╗щЭвшиншиИч╖и/01_хЕ▒щАЪ/02_чФ╗щЭвщБ╖чз╗хЫ│/01.01.02.02.04_чФ╗щЭвщБ╖чз╗хЫ│_х╖еф║ЛцГЕха▒ц╡БщАЪ-я╝│я╝пх╖еф║ЛщА▓цНЧцГЕха▒щГихИЖ.xls"

    if not os.path.exists(file_path):
        print(f"❌ File not found: {file_path}")
        sys.exit(1)

    analyze_file(file_path)