"""Debug script to investigate xlsx structure and understand where drawings are lost."""

import zipfile
import os
import sys
import xml.etree.ElementTree as ET

def analyze_xlsx(file_path: str, label: str):
    """Analyze xlsx structure and print drawing-related elements."""
    print(f"\n{'='*80}")
    print(f"ANALYZING: {label}")
    print(f"File: {file_path}")
    print('='*80)

    if not os.path.exists(file_path):
        print(f"  ERROR: File not found!")
        return

    with zipfile.ZipFile(file_path, 'r') as z:
        all_files = z.namelist()

        # 1. Check for drawing files
        drawing_files = [f for f in all_files if 'drawings/' in f]
        print(f"\n[1] Drawing files ({len(drawing_files)}):")
        for f in drawing_files:
            info = z.getinfo(f)
            print(f"    - {f} ({info.file_size} bytes)")

        # 2. Check worksheet relationship files
        rels_files = [f for f in all_files if 'worksheets/_rels/' in f]
        print(f"\n[2] Worksheet relationship files ({len(rels_files)}):")
        for f in rels_files:
            content = z.read(f).decode('utf-8')
            print(f"    - {f}")
            # Check if it has drawing relationship
            if 'drawing' in content.lower():
                print(f"      ✓ HAS drawing relationship")
                # Extract the relationship
                try:
                    root = ET.fromstring(content)
                    ns = {'r': 'http://schemas.openxmlformats.org/package/2006/relationships'}
                    for rel in root.findall('.//r:Relationship', ns):
                        if 'drawing' in rel.get('Type', '').lower():
                            print(f"        → {rel.get('Id')}: {rel.get('Target')}")
                except:
                    pass
            else:
                print(f"      ✗ NO drawing relationship")

        # 3. Check worksheet XML files for drawing references
        sheet_files = [f for f in all_files if f.startswith('xl/worksheets/sheet') and f.endswith('.xml') and '_rels' not in f]
        print(f"\n[3] Worksheet XML files ({len(sheet_files)}):")
        for f in sheet_files:
            content = z.read(f).decode('utf-8')
            print(f"    - {f}")
            # Check for <drawing> element
            if '<drawing' in content:
                print(f"      ✓ HAS <drawing> element in XML")
                # Try to extract the reference
                try:
                    # Find drawing reference
                    import re
                    match = re.search(r'<drawing[^>]*r:id="([^"]+)"', content)
                    if match:
                        print(f"        → Drawing reference: {match.group(1)}")
                except:
                    pass
            else:
                print(f"      ✗ NO <drawing> element in XML")

def main():
    # Check preprocessed files directory
    base_dir = "airflow/logs/preprocessed_files"

    if not os.path.exists(base_dir):
        print(f"Directory not found: {base_dir}")
        print("Looking for test files...")
        # Try to find any xlsx files
        for root, dirs, files in os.walk('.'):
            for f in files:
                if f.endswith('.xlsx') and 'no_images' in f:
                    analyze_xlsx(os.path.join(root, f), "Found test file")
                    return
        return

    # Find pairs of files to compare
    files = os.listdir(base_dir)

    # Find no_images and after_shapes pairs
    for f in files:
        if '_01_no_images.xlsx' in f:
            original = os.path.join(base_dir, f)
            after_shapes = original.replace('_01_no_images.xlsx', '_02_after_shapes.xlsx')

            analyze_xlsx(original, "ORIGINAL (01_no_images)")
            if os.path.exists(after_shapes):
                analyze_xlsx(after_shapes, "AFTER SHAPES (02_after_shapes)")
            break

if __name__ == "__main__":
    main()