"""
Shape Extractor - Extract shapes with text from Excel files.

Based on tmp/excel_shape_extraction_service.py but simplified for preprocessing pipeline.
"""

import logging
import os
import xml.etree.ElementTree as ET
import zipfile
from typing import List, Dict, Any, Optional
from openpyxl.utils import get_column_letter

logger = logging.getLogger(__name__)


class ShapeExtractor:
    """Extract shapes containing text from Excel files."""

    def extract_shapes_from_file(self, file_path: str) -> List[Dict[str, Any]]:
        """
        Extract shapes containing text from an Excel file.

        Args:
            file_path: Path to .xlsx file

        Returns:
            List of dictionaries with 'text', 'sheet_name', 'from_cell', etc.
        """
        try:
            shapes = self._extract_shapes_from_archive(file_path)

            # Extract connectors and shape ID map, then match to shapes
            connectors, shape_id_map = self._extract_connectors_and_shape_map(file_path)
            shapes_with_connectors = self._match_connectors_to_shapes(
                shapes, connectors, shape_id_map
            )

            return shapes_with_connectors
        except Exception as e:
            logger.error(f"Error extracting shapes from {file_path}: {e}")
            return []

    def extract_and_apply_annotations(
        self, file_path: str, temp_dir: str
    ) -> Optional[str]:
        """
        Extract shapes and apply them as annotations to cells.

        Uses ZIP-level modification to preserve ALL drawings/shapes while
        updating cell values.

        Args:
            file_path: Path to .xlsx file
            temp_dir: Temporary directory for output

        Returns:
            Path to annotated file, or None if no shapes found
        """
        import os
        import shutil

        # Extract shapes
        all_shapes = self.extract_shapes_from_file(file_path)

        if not all_shapes:
            logger.debug("No shapes with text found")
            return None

        # Filter to only keep annotation boxes (not decorative symbols)
        annotation_shapes = self._filter_annotation_shapes(all_shapes)

        if not annotation_shapes:
            logger.debug("No annotation shapes found (only decorative symbols)")
            return None

        logger.info(
            f"Found {len(annotation_shapes)} annotation shapes "
            f"(filtered from {len(all_shapes)} total shapes)"
        )

        # Organize annotations by sheet
        sheet_annotations = self._organize_annotations_by_sheet(annotation_shapes)

        # Create output path
        output_filename = os.path.basename(file_path).replace(".xlsx", "_with_shapes.xlsx")
        output_path = os.path.join(temp_dir, output_filename)

        # Apply annotations using ZIP-level modification to preserve drawings
        annotations_applied = self._apply_annotations_preserve_drawings(
            file_path, output_path, sheet_annotations
        )

        if annotations_applied == 0:
            logger.info("No shape annotations applied")
            return None

        logger.info(f"Applied {annotations_applied} shape annotations to {output_path}")
        logger.info("All drawings and shapes (including legends) have been preserved")
        return output_path

    def _organize_annotations_by_sheet(
        self, shapes: List[Dict[str, Any]]
    ) -> Dict[str, List[Dict[str, Any]]]:
        """
        Organize shapes by sheet name for efficient processing.

        Args:
            shapes: List of shape dictionaries

        Returns:
            Dict mapping sheet name to list of shapes in that sheet
        """
        sheet_annotations = {}
        for shape in shapes:
            sheet_name = shape.get("sheet_name", "")
            if not sheet_name:
                continue
            if sheet_name not in sheet_annotations:
                sheet_annotations[sheet_name] = []
            sheet_annotations[sheet_name].append(shape)
        return sheet_annotations

    def _apply_annotations_preserve_drawings(
        self, input_path: str, output_path: str, sheet_annotations: Dict[str, List[Dict[str, Any]]]
    ) -> int:
        """
        Apply annotations by directly modifying XML in the xlsx file.

        This approach:
        1. Copies the original xlsx file (preserves EVERYTHING)
        2. Directly modifies cell values in the XML
        3. Never uses openpyxl to save (which destroys formatting)

        Args:
            input_path: Original file path
            output_path: Output file path
            sheet_annotations: Dict of sheet name to annotations

        Returns:
            Number of annotations applied
        """
        import shutil
        import zipfile
        import tempfile
        import os
        import xml.etree.ElementTree as ET
        from openpyxl.utils import column_index_from_string

        # Get sheet name to sheet index mapping
        sheet_name_to_file = self._get_sheet_name_mapping(input_path)
        logger.info(f"Sheet mapping: {sheet_name_to_file}")

        # Build list of cell modifications
        cell_modifications = []  # List of (sheet_file, cell_ref, new_value)

        for sheet_name, shapes in sheet_annotations.items():
            if sheet_name not in sheet_name_to_file:
                logger.debug(f"Sheet '{sheet_name}' not found in mapping")
                continue

            sheet_file = sheet_name_to_file[sheet_name]

            for shape in shapes:
                shape_text = shape.get("text", "").strip()
                if not shape_text:
                    continue

                connector_target = shape.get("connector_target_cell")

                if connector_target:
                    # Apply annotation to connector target cell
                    cell_modifications.append((sheet_file, connector_target, f"[Annotation]: {shape_text}"))
                else:
                    # Apply to from_cell
                    from_cell = shape.get("from_cell")
                    if from_cell:
                        cell_modifications.append((sheet_file, from_cell, f"[Annotation]: {shape_text}"))

        if not cell_modifications:
            logger.info("No cell modifications to apply")
            shutil.copy2(input_path, output_path)
            return 0

        logger.info(f"Applying {len(cell_modifications)} cell modifications directly to XML...")

        # Get list of annotation shape IDs to delete (only red dashed boxes!)
        shapes_to_delete = self._get_annotation_shape_ids(input_path, sheet_annotations)

        # Apply modifications by directly editing XML in the ZIP
        annotations_applied = self._apply_xml_modifications(
            input_path, output_path, cell_modifications, shapes_to_delete
        )

        logger.info(
            f"Applied {annotations_applied} annotations, "
            f"deleted {len(shapes_to_delete)} annotation shapes, "
            f"preserved all other shapes and formatting"
        )
        return annotations_applied

    def _get_annotation_shape_ids(
        self, file_path: str, sheet_annotations: Dict[str, List[Dict[str, Any]]]
    ) -> Dict[str, List[str]]:
        """
        Get shape IDs to delete, organized by sheet.

        This includes:
        - The annotation shapes themselves
        - Connectors linked to annotations
        - Target shapes pointed to by connectors

        Args:
            file_path: Path to xlsx file
            sheet_annotations: Dict of sheet name to annotation shapes

        Returns:
            Dict of sheet_name to list of shape_ids to delete
        """
        # First, collect all annotation shape IDs
        annotation_shape_ids = set()
        for sheet_name, shapes in sheet_annotations.items():
            for shape in shapes:
                shape_id = shape.get("shape_id")
                if shape_id:
                    annotation_shape_ids.add(shape_id)

        # Now extract connector info and find what's connected to these annotations
        all_ids_to_delete = self._find_connected_shapes_and_connectors(
            file_path, annotation_shape_ids
        )

        # Organize by sheet (for now, just return all in one group since we process all drawings)
        shapes_to_delete = {"all": list(all_ids_to_delete)} if all_ids_to_delete else {}

        logger.info(
            f"Will delete {len(all_ids_to_delete)} total elements: "
            f"{len(annotation_shape_ids)} annotation shapes + connectors + target shapes"
        )
        return shapes_to_delete

    def _find_connected_shapes_and_connectors(
        self, file_path: str, annotation_shape_ids: set
    ) -> set:
        """
        Find all shapes and connectors connected to annotation shapes.

        Args:
            file_path: Path to xlsx file
            annotation_shape_ids: Set of annotation shape IDs

        Returns:
            Set of all IDs to delete (annotations + connectors + targets)
        """
        import zipfile
        import xml.etree.ElementTree as ET

        ids_to_delete = set(annotation_shape_ids)  # Start with annotation shapes

        try:
            with zipfile.ZipFile(file_path, 'r') as zip_ref:
                # Find all drawing XML files
                drawing_files = [
                    f for f in zip_ref.namelist()
                    if f.startswith("xl/drawings/drawing") and f.endswith(".xml")
                ]

                ns = {
                    'xdr': 'http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing',
                    'a': 'http://schemas.openxmlformats.org/drawingml/2006/main'
                }

                for drawing_file in drawing_files:
                    drawing_xml = zip_ref.read(drawing_file)
                    root = ET.fromstring(drawing_xml)

                    # Find all connectors
                    for anchor in root.findall('.//xdr:twoCellAnchor', ns) + root.findall('.//xdr:oneCellAnchor', ns):
                        # Check if this is a connector
                        cxnSp = anchor.find('.//xdr:cxnSp', ns)
                        if cxnSp is not None:
                            # Get connector ID
                            cNvPr = cxnSp.find('.//xdr:cNvPr', ns)
                            if cNvPr is None:
                                continue

                            connector_id = cNvPr.get('id')

                            # Look for connection info inside cNvCxnSpPr
                            cNvCxnSpPr = cxnSp.find('.//xdr:cNvCxnSpPr', ns)
                            if cNvCxnSpPr is None:
                                continue

                            # Check for stCxn (start connection) and endCxn (end connection)
                            stCxn = cNvCxnSpPr.find('.//a:stCxn', ns)
                            endCxn = cNvCxnSpPr.find('.//a:endCxn', ns)

                            start_shape_id = stCxn.get('id') if stCxn is not None else None
                            end_shape_id = endCxn.get('id') if endCxn is not None else None

                            logger.debug(
                                f"Connector {connector_id}: "
                                f"start={start_shape_id}, end={end_shape_id}"
                            )

                            # If connector starts from annotation shape, delete connector and target
                            if start_shape_id and start_shape_id in annotation_shape_ids:
                                if connector_id:
                                    ids_to_delete.add(connector_id)
                                    logger.info(f"Adding connector {connector_id} to delete list (from annotation)")
                                if end_shape_id:
                                    ids_to_delete.add(end_shape_id)
                                    logger.info(f"Adding target shape {end_shape_id} to delete list")

                            # If connector ends at annotation shape, delete connector and source
                            elif end_shape_id and end_shape_id in annotation_shape_ids:
                                if connector_id:
                                    ids_to_delete.add(connector_id)
                                    logger.info(f"Adding connector {connector_id} to delete list (to annotation)")
                                if start_shape_id:
                                    ids_to_delete.add(start_shape_id)
                                    logger.info(f"Adding source shape {start_shape_id} to delete list")

        except Exception as e:
            logger.warning(f"Error finding connected shapes: {e}")
            import traceback
            traceback.print_exc()

        logger.info(f"Total elements to delete: {len(ids_to_delete)}")
        return ids_to_delete

    def _get_sheet_name_mapping(self, file_path: str) -> Dict[str, str]:
        """Get mapping of sheet names to their XML file paths."""
        import zipfile
        import xml.etree.ElementTree as ET

        sheet_mapping = {}

        with zipfile.ZipFile(file_path, 'r') as z:
            # Read workbook.xml to get sheet names and rIds
            workbook_xml = z.read('xl/workbook.xml')
            wb_root = ET.fromstring(workbook_xml)

            ns = {
                'main': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
                'r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'
            }

            sheet_rid_map = {}
            for sheet in wb_root.findall('.//main:sheet', ns):
                name = sheet.get('name')
                rid = sheet.get('{http://schemas.openxmlformats.org/officeDocument/2006/relationships}id')
                if name and rid:
                    sheet_rid_map[rid] = name

            # Read workbook.xml.rels to get rId to file mapping
            rels_xml = z.read('xl/_rels/workbook.xml.rels')
            rels_root = ET.fromstring(rels_xml)

            rel_ns = {'r': 'http://schemas.openxmlformats.org/package/2006/relationships'}
            for rel in rels_root.findall('.//r:Relationship', rel_ns):
                rid = rel.get('Id')
                target = rel.get('Target')
                if rid in sheet_rid_map and target:
                    sheet_name = sheet_rid_map[rid]
                    # Convert relative path to full path
                    if not target.startswith('/'):
                        target = 'xl/' + target
                    sheet_mapping[sheet_name] = target

        return sheet_mapping

    def _apply_xml_modifications(
        self, input_path: str, output_path: str, cell_modifications: List[tuple],
        shapes_to_delete: Dict[str, List[str]]
    ) -> int:
        """
        Apply cell modifications and delete annotation shapes.

        Args:
            input_path: Source xlsx file
            output_path: Output xlsx file
            cell_modifications: List of (sheet_file, cell_ref, value) tuples
            shapes_to_delete: Dict of sheet_name to shape_ids to delete
        """
        import zipfile
        import xml.etree.ElementTree as ET

        # Read shared strings table first
        shared_strings = self._read_shared_strings(input_path)

        # Group modifications by sheet file
        mods_by_sheet = {}
        for sheet_file, cell_ref, value in cell_modifications:
            if sheet_file not in mods_by_sheet:
                mods_by_sheet[sheet_file] = []
            mods_by_sheet[sheet_file].append((cell_ref, value))

        annotations_applied = 0
        shapes_deleted = 0

        # Process the xlsx file
        with zipfile.ZipFile(input_path, 'r') as zip_in:
            with zipfile.ZipFile(output_path, 'w', zipfile.ZIP_DEFLATED) as zip_out:
                for item in zip_in.namelist():
                    content = zip_in.read(item)

                    # Check if this is a sheet we need to modify
                    if item in mods_by_sheet:
                        logger.debug(f"Modifying {item} with {len(mods_by_sheet[item])} annotations")
                        content, count = self._modify_sheet_xml(content, mods_by_sheet[item], shared_strings)
                        annotations_applied += count

                    # Check if this is a drawing file with shapes to delete
                    if item.startswith('xl/drawings/drawing') and item.endswith('.xml'):
                        if shapes_to_delete:
                            content, count = self._delete_shapes_from_drawing(content, shapes_to_delete)
                            shapes_deleted += count

                    zip_out.writestr(item, content)

        logger.info(f"Deleted {shapes_deleted} annotation shapes from drawings")
        return annotations_applied

    def _read_shared_strings(self, file_path: str) -> list:
        """
        Read shared strings table from xlsx file.

        Args:
            file_path: Path to xlsx file

        Returns:
            List of shared strings (index -> string value)
        """
        import zipfile
        import xml.etree.ElementTree as ET

        shared_strings = []

        try:
            with zipfile.ZipFile(file_path, 'r') as zf:
                if 'xl/sharedStrings.xml' not in zf.namelist():
                    return []

                content = zf.read('xl/sharedStrings.xml')
                root = ET.fromstring(content)

                ns = {'main': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}

                # Find all <si> elements (string items)
                for si in root.findall('.//main:si', ns):
                    # Get text from <t> elements
                    text_parts = []
                    for t in si.findall('.//main:t', ns):
                        if t.text:
                            text_parts.append(t.text)

                    shared_strings.append(''.join(text_parts))

                logger.debug(f"Read {len(shared_strings)} shared strings")

        except Exception as e:
            logger.warning(f"Error reading shared strings: {e}")

        return shared_strings

    def _delete_shapes_from_drawing(
        self, content: bytes, shapes_to_delete: Dict[str, List[str]]
    ) -> tuple:
        """
        Delete annotation shapes, connectors, and connected shapes from drawing XML.

        Deletes shapes by:
        1. Matching shape IDs in shapes_to_delete
        2. Matching red dashed line style (annotation boxes)
        3. Connectors connected to deleted shapes

        Args:
            content: Drawing XML content as bytes
            shapes_to_delete: Dict of sheet_name to list of shape_ids (includes connectors)

        Returns:
            Tuple of (modified_content, count_of_deletions)
        """
        import xml.etree.ElementTree as ET

        # Flatten the list of shape IDs to delete
        all_shape_ids = set()
        for shape_ids in shapes_to_delete.values():
            all_shape_ids.update(shape_ids)

        try:
            # Register namespaces
            ET.register_namespace('xdr', 'http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing')
            ET.register_namespace('a', 'http://schemas.openxmlformats.org/drawingml/2006/main')

            root = ET.fromstring(content)
            ns = {
                'xdr': 'http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing',
                'a': 'http://schemas.openxmlformats.org/drawingml/2006/main'
            }

            deleted_count = 0
            deleted_by_style = 0
            deleted_connectors = 0

            # Track all shape IDs that will be deleted (for connector cleanup)
            deleted_shape_ids = set(all_shape_ids)

            # PASS 1: Find and delete annotation shapes, track their IDs
            for anchor_type in ['twoCellAnchor', 'oneCellAnchor']:
                for anchor in list(root.findall(f'.//xdr:{anchor_type}', ns)):
                    # Get element ID
                    cNvPr = anchor.find('.//xdr:cNvPr', ns)
                    if cNvPr is None:
                        continue

                    element_id = cNvPr.get('id')

                    # Check if this is a shape (not connector)
                    sp = anchor.find('.//xdr:sp', ns)
                    if sp is not None:
                        should_delete = False
                        delete_reason = ""

                        # Delete by ID
                        if element_id in all_shape_ids:
                            should_delete = True
                            delete_reason = "ID match"
                        else:
                            # Also delete by style (red dashed lines)
                            if self._is_annotation_style(sp, ns):
                                should_delete = True
                                delete_reason = "annotation style"
                                deleted_by_style += 1
                                # Track this ID for connector cleanup
                                deleted_shape_ids.add(element_id)

                        if should_delete:
                            root.remove(anchor)
                            deleted_count += 1
                            logger.debug(f"Deleted shape ID={element_id} ({delete_reason})")

            # PASS 2: Delete connectors linked to deleted shapes OR with annotation style
            for anchor_type in ['twoCellAnchor', 'oneCellAnchor']:
                for anchor in list(root.findall(f'.//xdr:{anchor_type}', ns)):
                    # Get element ID
                    cNvPr = anchor.find('.//xdr:cNvPr', ns)
                    if cNvPr is None:
                        continue

                    element_id = cNvPr.get('id')

                    # Check if this is a connector
                    cxnSp = anchor.find('.//xdr:cxnSp', ns)
                    if cxnSp is not None:
                        should_delete = False
                        delete_reason = ""

                        # Check if connector connects to any shape we deleted
                        cNvCxnSpPr = cxnSp.find('.//xdr:cNvCxnSpPr', ns)
                        if cNvCxnSpPr is not None:
                            stCxn = cNvCxnSpPr.find('.//a:stCxn', ns)
                            endCxn = cNvCxnSpPr.find('.//a:endCxn', ns)

                            start_id = stCxn.get('id') if stCxn is not None else None
                            end_id = endCxn.get('id') if endCxn is not None else None

                            # Delete connector if connected to any deleted shape
                            if (element_id in deleted_shape_ids or
                                (start_id and start_id in deleted_shape_ids) or
                                (end_id and end_id in deleted_shape_ids)):
                                should_delete = True
                                delete_reason = "connected to deleted shape"

                        # Also check if connector has annotation style (red dashed)
                        # This catches orphaned annotation connectors with no connections
                        if not should_delete and self._is_annotation_style_connector(cxnSp, ns):
                            should_delete = True
                            delete_reason = "annotation style connector"

                        if should_delete:
                            root.remove(anchor)
                            deleted_count += 1
                            deleted_connectors += 1
                            logger.info(
                                f"Deleted connector ID={element_id} ({delete_reason})"
                            )

            # Convert back to bytes
            if deleted_count > 0:
                modified_content = ET.tostring(root, encoding='unicode')
                modified_content = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n' + modified_content
                logger.info(
                    f"Deleted {deleted_count} elements: "
                    f"{deleted_by_style} by style, {deleted_connectors} connectors"
                )
                return modified_content.encode('utf-8'), deleted_count
            else:
                return content, 0

        except Exception as e:
            logger.warning(f"Failed to delete shapes from drawing: {e}")
            import traceback
            traceback.print_exc()
            return content, 0

    def _is_annotation_style(self, shape_elem: ET.Element, ns: dict) -> bool:
        """
        Check if a shape has annotation style (red dashed lines).

        Args:
            shape_elem: Shape XML element
            ns: XML namespaces

        Returns:
            True if shape has red dashed lines
        """
        try:
            spPr = shape_elem.find('.//xdr:spPr', ns)
            if spPr is None:
                return False

            ln = spPr.find('.//a:ln', ns)
            if ln is None:
                return False

            # Check for dashed line
            prstDash = ln.find('.//a:prstDash', ns)
            if prstDash is None:
                return False

            dash_val = prstDash.get('val', '')
            if dash_val not in ['sysDot', 'dash', 'dashDot', 'lgDash', 'lgDashDot']:
                return False

            # Check for red color
            srgbClr = ln.find('.//a:srgbClr', ns)
            if srgbClr is None:
                return False

            color = srgbClr.get('val', '')
            if not self._is_red_color(color):
                return False

            return True
        except Exception as e:
            logger.debug(f"Error checking annotation style: {e}")
            return False

    def _is_annotation_style_connector(self, connector_elem: ET.Element, ns: dict) -> bool:
        """
        Check if a connector has annotation style (red dashed lines).

        Args:
            connector_elem: Connector XML element (cxnSp)
            ns: XML namespaces

        Returns:
            True if connector has red dashed lines
        """
        try:
            spPr = connector_elem.find('.//xdr:spPr', ns)
            if spPr is None:
                return False

            ln = spPr.find('.//a:ln', ns)
            if ln is None:
                return False

            # Check for dashed line
            prstDash = ln.find('.//a:prstDash', ns)
            if prstDash is None:
                return False

            dash_val = prstDash.get('val', '')
            if dash_val not in ['sysDot', 'dash', 'dashDot', 'lgDash', 'lgDashDot']:
                return False

            # Check for red color
            srgbClr = ln.find('.//a:srgbClr', ns)
            if srgbClr is None:
                return False

            color = srgbClr.get('val', '')
            if not self._is_red_color(color):
                return False

            return True
        except Exception as e:
            logger.debug(f"Error checking connector annotation style: {e}")
            return False

    def _modify_sheet_xml(self, content: bytes, modifications: List[tuple], shared_strings: list = None) -> tuple:
        """
        Modify cell values in a worksheet XML - APPEND annotation to existing value.

        Args:
            content: XML content as bytes
            modifications: List of (cell_ref, annotation_value) tuples
            shared_strings: List of shared string values (for resolving 's' type cells)

        Returns:
            Tuple of (modified_content, count_of_modifications)
        """
        import xml.etree.ElementTree as ET
        import re

        if shared_strings is None:
            shared_strings = []

        # Parse XML
        ET.register_namespace('', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main')
        ET.register_namespace('r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships')
        ET.register_namespace('mc', 'http://schemas.openxmlformats.org/markup-compatibility/2006')
        ET.register_namespace('x14ac', 'http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac')

        root = ET.fromstring(content)
        ns = {'main': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}

        count = 0

        for cell_ref, annotation_value in modifications:
            # Find the cell
            cell_elem = root.find(f".//main:c[@r='{cell_ref}']", ns)

            if cell_elem is not None:
                # Get existing cell value
                existing_value = ""
                cell_type = cell_elem.get('t')

                if cell_type == 'inlineStr':
                    # Inline string
                    is_elem = cell_elem.find('main:is', ns)
                    if is_elem is not None:
                        t_elem = is_elem.find('main:t', ns)
                        if t_elem is not None and t_elem.text:
                            existing_value = t_elem.text
                elif cell_type == 's':
                    # Shared string - resolve from shared strings table
                    v_elem = cell_elem.find('main:v', ns)
                    if v_elem is not None and v_elem.text:
                        try:
                            string_index = int(v_elem.text)
                            if 0 <= string_index < len(shared_strings):
                                existing_value = shared_strings[string_index]
                                logger.debug(f"Resolved shared string {string_index}: {existing_value[:50]}")
                            else:
                                logger.warning(f"Shared string index {string_index} out of range")
                        except ValueError:
                            logger.warning(f"Invalid shared string index: {v_elem.text}")
                else:
                    # Number or other type
                    v_elem = cell_elem.find('main:v', ns)
                    if v_elem is not None and v_elem.text:
                        existing_value = v_elem.text

                # Combine existing value with annotation
                if existing_value:
                    combined_value = f"{existing_value}\n{annotation_value}"
                else:
                    combined_value = annotation_value

                # Set as inline string
                cell_elem.set('t', 'inlineStr')

                # Remove old value elements
                for old_elem in ['v', 'is']:
                    old = cell_elem.find(f'main:{old_elem}', ns)
                    if old is not None:
                        cell_elem.remove(old)

                # Add new inline string
                is_elem = ET.SubElement(cell_elem, '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}is')
                t_elem = ET.SubElement(is_elem, '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}t')
                t_elem.text = combined_value

                count += 1
                logger.debug(f"Modified cell {cell_ref}: appended annotation to existing value")
            else:
                logger.debug(f"Cell {cell_ref} not found in sheet, skipping")

        # Convert back to bytes
        modified_content = ET.tostring(root, encoding='unicode')

        # Add XML declaration
        modified_content = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n' + modified_content

        return modified_content.encode('utf-8'), count

    def _restore_drawings_from_original(
        self, original_path: str, modified_path: str, output_path: str
    ):
        """
        Restore drawing files from original xlsx to modified xlsx using ZIP operations.

        Args:
            original_path: Path to original file with drawings
            modified_path: Path to modified file (lost drawings)
            output_path: Path to save final file with drawings restored
        """
        import zipfile

        # Better approach: Start with original, replace only worksheet XMLs
        # This preserves ALL drawings, relationships, and formatting

        try:
            # Step 1: Extract modified worksheet XMLs (have our cell changes)
            # BUT SKIP relationship files - we want to keep original relationships!
            modified_worksheets = {}
            modified_shared_strings = None

            with zipfile.ZipFile(modified_path, 'r') as zip_mod:
                for file in zip_mod.namelist():
                    # Extract worksheet XML files (NOT the _rels!)
                    if (file.startswith('xl/worksheets/sheet')
                        and file.endswith('.xml')
                        and '_rels' not in file):  # CRITICAL: Skip relationship files
                        modified_worksheets[file] = zip_mod.read(file)
                        logger.debug(f"Extracted modified: {file}")
                    # Also extract sharedStrings (contains cell text)
                    elif file == 'xl/sharedStrings.xml':
                        modified_shared_strings = zip_mod.read(file)
                        logger.debug("Extracted modified sharedStrings.xml")

            logger.info(f"Extracted {len(modified_worksheets)} modified worksheets (excluding relationships)")

            # Step 1.5: Extract <drawing> elements from ORIGINAL worksheets
            # openpyxl removes these, but we need them to connect worksheets to drawings!
            original_drawing_refs = {}
            with zipfile.ZipFile(original_path, 'r') as zip_orig:
                for file in zip_orig.namelist():
                    if (file.startswith('xl/worksheets/sheet')
                        and file.endswith('.xml')
                        and '_rels' not in file):
                        content = zip_orig.read(file).decode('utf-8')
                        # Find <drawing> element using regex (simpler than XML parsing)
                        import re
                        # Match <drawing ... /> (self-closing) or <drawing ...></drawing>
                        drawing_match = re.search(r'(<drawing[^>]*(?:/>|>.*?</drawing>))', content, re.DOTALL)
                        if drawing_match:
                            original_drawing_refs[file] = drawing_match.group(1)
                            logger.debug(f"Found drawing reference in original {file}: {drawing_match.group(1)[:50]}...")

            logger.info(f"Found {len(original_drawing_refs)} drawing references to restore")

            # Step 2: Create output from original, replacing only worksheets
            # AND injecting back the <drawing> references that openpyxl removed!
            import re

            with zipfile.ZipFile(original_path, 'r') as zip_orig:
                with zipfile.ZipFile(output_path, 'w', zipfile.ZIP_DEFLATED) as zip_out:
                    files_copied = 0
                    files_replaced = 0
                    drawings_restored = 0

                    for file in zip_orig.namelist():
                        # Replace worksheet XMLs with modified versions
                        if file in modified_worksheets:
                            modified_content = modified_worksheets[file]

                            # Check if we need to restore a <drawing> reference
                            if file in original_drawing_refs:
                                drawing_ref = original_drawing_refs[file]
                                # Decode if bytes
                                if isinstance(modified_content, bytes):
                                    modified_content = modified_content.decode('utf-8')

                                # Check if drawing element is missing
                                if '<drawing' not in modified_content:
                                    # Insert before </worksheet>
                                    modified_content = re.sub(
                                        r'(</worksheet>)',
                                        f'{drawing_ref}\\1',
                                        modified_content
                                    )
                                    drawings_restored += 1
                                    logger.info(f"Restored <drawing> element in {file}")

                                # Re-encode to bytes
                                modified_content = modified_content.encode('utf-8')

                            zip_out.writestr(file, modified_content)
                            files_replaced += 1
                            logger.debug(f"Replaced: {file}")
                        # Replace sharedStrings if we have a modified version
                        elif file == 'xl/sharedStrings.xml' and modified_shared_strings:
                            zip_out.writestr(file, modified_shared_strings)
                            files_replaced += 1
                            logger.debug("Replaced: sharedStrings.xml")
                        else:
                            # Copy everything else unchanged (drawings, relationships, etc)
                            zip_out.writestr(file, zip_orig.read(file))
                            files_copied += 1

            logger.info(
                f"Created output: {files_replaced} files replaced, "
                f"{files_copied} files preserved, {drawings_restored} <drawing> elements restored"
            )

        except Exception as e:
            logger.error(f"Error in drawing restoration: {e}")
            raise

        logger.info("Drawing restoration complete")

    def _filter_annotation_shapes(self, shapes: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
        """
        Filter shapes to only keep red dashed line boxes.

        Only shapes with:
        - Red line color (RGB close to red)
        - Dashed line style
        - Text content

        Args:
            shapes: List of all shapes

        Returns:
            Filtered list containing only red dashed line annotation boxes
        """
        annotation_shapes = []

        for shape in shapes:
            text = shape.get("text", "").strip()
            line_dash = shape.get("line_dash", "")
            line_color = shape.get("line_color", "")

            # Must have text
            if not text:
                logger.debug("Skipping shape without text")
                continue

            # Must have dashed line style
            if not line_dash or line_dash == "solid":
                logger.debug(
                    f"Skipping shape without dashed line: '{text[:30]}...' "
                    f"(line_dash: {line_dash})"
                )
                continue

            # Must have red line color
            if not self._is_red_color(line_color):
                logger.debug(
                    f"Skipping shape without red color: '{text[:30]}...' "
                    f"(line_color: {line_color})"
                )
                continue

            # This is a red dashed line box - keep it
            annotation_shapes.append(shape)
            logger.info(
                f"Keeping red dashed annotation: '{text[:50]}...' "
                f"(dash: {line_dash}, color: {line_color})"
            )

        return annotation_shapes

    def _is_red_color(self, color: str) -> bool:
        """
        Check if color is red or close to red.

        Args:
            color: Hex color string (e.g., "FF0000" or "FFFF0000")

        Returns:
            True if color is red-ish
        """
        if not color:
            return False

        # Skip theme colors (we can't determine exact RGB)
        if color.startswith("theme:"):
            return False

        # Remove alpha channel if present (ARGB format)
        if len(color) == 8:
            color = color[2:]  # Remove first 2 chars (alpha)

        try:
            # Parse RGB values
            r = int(color[0:2], 16)
            g = int(color[2:4], 16)
            b = int(color[4:6], 16)

            # Check if red is dominant and green/blue are low
            # Red should be high (>200), green and blue should be low (<100)
            is_red = r > 200 and g < 100 and b < 100

            if is_red:
                logger.debug(f"Color {color} (R:{r} G:{g} B:{b}) identified as red")

            return is_red
        except (ValueError, IndexError):
            logger.debug(f"Could not parse color: {color}")
            return False

    def _extract_shapes_from_archive(self, file_path: str) -> List[Dict[str, Any]]:
        """
        Extract shapes by parsing the .xlsx file's XML structure.

        Args:
            file_path: Path to .xlsx file

        Returns:
            List of dictionaries with shape data and metadata
        """
        shapes_data = []

        try:
            with zipfile.ZipFile(file_path, "r") as zip_ref:
                # Get sheet names
                sheet_names = self._get_sheet_names(zip_ref)

                # Build sheet-to-drawing mapping
                sheet_to_drawing = self._get_sheet_to_drawing_mapping(
                    zip_ref, len(sheet_names)
                )

                # Find drawing files
                drawing_files = [
                    f
                    for f in zip_ref.namelist()
                    if f.startswith("xl/drawings/drawing") and f.endswith(".xml")
                ]

                logger.debug(f"Found {len(drawing_files)} drawing files")

                for drawing_file in drawing_files:
                    try:
                        # Get drawing name
                        drawing_name = drawing_file.split("/")[-1]

                        # Find which sheet this drawing belongs to
                        sheet_idx = None
                        sheet_name = None

                        for sid, dname in sheet_to_drawing.items():
                            if dname == drawing_name:
                                sheet_idx = sid - 1  # 0-based
                                sheet_name = (
                                    sheet_names[sheet_idx]
                                    if sheet_idx < len(sheet_names)
                                    else f"Sheet{sid}"
                                )
                                break

                        # Fallback
                        if sheet_name is None:
                            sheet_idx = (
                                int(drawing_name.split("drawing")[-1].replace(".xml", ""))
                                - 1
                            )
                            sheet_name = (
                                sheet_names[sheet_idx]
                                if sheet_idx < len(sheet_names)
                                else f"Sheet{sheet_idx + 1}"
                            )

                        # Parse shapes
                        shapes = self._parse_shapes_from_drawing(
                            zip_ref, drawing_file, sheet_name, sheet_idx
                        )
                        shapes_data.extend(shapes)

                        logger.debug(
                            f"Extracted {len(shapes)} shapes from {drawing_file} (sheet: {sheet_name})"
                        )

                    except Exception as e:
                        logger.debug(f"Error parsing {drawing_file}: {e}")
                        continue

            return shapes_data

        except zipfile.BadZipFile:
            logger.error("File is not a valid ZIP archive")
            return []
        except Exception as e:
            logger.error(f"Error extracting shapes: {e}")
            return []

    def _get_sheet_to_drawing_mapping(
        self, zip_ref: zipfile.ZipFile, num_sheets: int
    ) -> Dict[int, str]:
        """
        Build mapping from sheet number to drawing filename.

        Args:
            zip_ref: ZipFile reference
            num_sheets: Number of sheets

        Returns:
            Dict mapping sheet number (1-based) to drawing filename
        """
        sheet_to_drawing = {}

        for sheet_num in range(1, num_sheets + 1):
            rels_path = f"xl/worksheets/_rels/sheet{sheet_num}.xml.rels"

            try:
                rels_content = zip_ref.read(rels_path)
                root = ET.fromstring(rels_content)

                ns = {"rel": "http://schemas.openxmlformats.org/package/2006/relationships"}
                for rel in root.findall(".//rel:Relationship", ns):
                    rel_type = rel.get("Type", "")
                    if "drawing" in rel_type.lower():
                        target = rel.get("Target", "")
                        drawing_name = target.split("/")[-1]

                        # Skip VML drawings
                        if "vml" in drawing_name.lower():
                            continue

                        sheet_to_drawing[sheet_num] = drawing_name
                        break

            except KeyError:
                continue
            except Exception as e:
                logger.debug(f"Error reading relationships for sheet {sheet_num}: {e}")
                continue

        return sheet_to_drawing

    def _get_sheet_names(self, zip_ref: zipfile.ZipFile) -> List[str]:
        """
        Extract sheet names from workbook.xml.

        Args:
            zip_ref: ZipFile reference

        Returns:
            List of sheet names in order
        """
        sheet_names = []
        try:
            wb_xml = zip_ref.read("xl/workbook.xml")
            wb_root = ET.fromstring(wb_xml)

            for sheet in wb_root.findall(
                ".//{http://schemas.openxmlformats.org/spreadsheetml/2006/main}sheet"
            ):
                sheet_name = sheet.get("name")
                if sheet_name:
                    sheet_names.append(sheet_name)

        except Exception as e:
            logger.debug(f"Could not parse sheet names: {e}")

        return sheet_names

    def _parse_shapes_from_drawing(
        self,
        zip_ref: zipfile.ZipFile,
        drawing_file: str,
        sheet_name: str,
        sheet_idx: int,
    ) -> List[Dict[str, Any]]:
        """
        Parse shapes from a drawing XML file.

        Args:
            zip_ref: ZipFile reference
            drawing_file: Path to drawing XML
            sheet_name: Sheet name
            sheet_idx: Sheet index (0-based)

        Returns:
            List of shape dictionaries
        """
        shapes = []

        try:
            drawing_xml = zip_ref.read(drawing_file)
            drawing_root = ET.fromstring(drawing_xml)

            namespaces = {
                "xdr": "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing",
                "a": "http://schemas.openxmlformats.org/drawingml/2006/main",
            }

            # Find shapes in twoCellAnchor
            for anchor in drawing_root.findall(".//xdr:twoCellAnchor", namespaces):
                shape = self._extract_shape_from_anchor(
                    anchor, namespaces, sheet_name, sheet_idx, "twoCellAnchor"
                )
                if shape:
                    shapes.append(shape)

            # Find shapes in oneCellAnchor
            for anchor in drawing_root.findall(".//xdr:oneCellAnchor", namespaces):
                shape = self._extract_shape_from_anchor(
                    anchor, namespaces, sheet_name, sheet_idx, "oneCellAnchor"
                )
                if shape:
                    shapes.append(shape)

        except Exception as e:
            logger.debug(f"Could not parse drawing file {drawing_file}: {e}")

        return shapes

    def _extract_shape_from_anchor(
        self,
        anchor: ET.Element,
        namespaces: Dict[str, str],
        sheet_name: str,
        sheet_idx: int,
        anchor_type: str,
    ) -> Optional[Dict[str, Any]]:
        """
        Extract shape data from anchor element.

        Args:
            anchor: XML anchor element
            namespaces: XML namespaces
            sheet_name: Sheet name
            sheet_idx: Sheet index (0-based)
            anchor_type: 'twoCellAnchor' or 'oneCellAnchor'

        Returns:
            Dict with shape data, or None if no text
        """
        # Look for shape element
        shape_elem = anchor.find(".//xdr:sp", namespaces)
        if shape_elem is None:
            return None

        # Extract text
        text_content = self._extract_text_from_shape(shape_elem, namespaces)
        if not text_content:
            return None

        # Extract position
        position = self._extract_position(anchor, namespaces, anchor_type)

        # Extract properties
        shape_props = self._extract_shape_properties(shape_elem, namespaces)

        return {
            "text": text_content,
            "sheet_name": sheet_name,
            "sheet_number": sheet_idx + 1,
            "from_cell": position.get("from_cell", ""),
            "from_row": position.get("from_row"),
            "from_col": position.get("from_col"),
            "to_cell": position.get("to_cell", ""),
            "to_row": position.get("to_row"),
            "to_col": position.get("to_col"),
            "emu_x": position.get("emu_x"),
            "emu_y": position.get("emu_y"),
            "shape_type": shape_props.get("shape_type", "unknown"),
            "shape_name": shape_props.get("name", ""),
            "shape_id": shape_props.get("id", ""),
            "line_dash": shape_props.get("line_dash", ""),
            "line_color": shape_props.get("line_color", ""),
            "anchor_type": anchor_type,
        }

    def _extract_text_from_shape(
        self, shape_elem: ET.Element, namespaces: Dict[str, str]
    ) -> Optional[str]:
        """
        Extract text content from shape element.

        Args:
            shape_elem: Shape XML element
            namespaces: XML namespaces

        Returns:
            Text content or None
        """
        text_parts = []

        tx_body = shape_elem.find(".//xdr:txBody", namespaces)
        if tx_body is None:
            return None

        paragraphs = tx_body.findall(".//a:p", namespaces)

        for paragraph in paragraphs:
            para_text_parts = []
            text_runs = paragraph.findall(".//a:r", namespaces)

            for text_run in text_runs:
                text_elem = text_run.find(".//a:t", namespaces)
                if text_elem is not None and text_elem.text:
                    para_text_parts.append(text_elem.text)

            if para_text_parts:
                text_parts.append(" ".join(para_text_parts))

        return "\n".join(text_parts) if text_parts else None

    def _extract_position(
        self, anchor: ET.Element, namespaces: Dict[str, str], anchor_type: str
    ) -> Dict[str, Any]:
        """
        Extract position information from anchor.

        Args:
            anchor: XML anchor element
            namespaces: XML namespaces
            anchor_type: Anchor type

        Returns:
            Dict with position data
        """
        position = {"anchor_type": anchor_type}

        # Extract "from" position
        from_elem = anchor.find(".//xdr:from", namespaces)
        if from_elem is not None:
            col_elem = from_elem.find("xdr:col", namespaces)
            row_elem = from_elem.find("xdr:row", namespaces)
            col_off_elem = from_elem.find("xdr:colOff", namespaces)
            row_off_elem = from_elem.find("xdr:rowOff", namespaces)

            if col_elem is not None and row_elem is not None:
                try:
                    from_col = int(col_elem.text)
                    from_row = int(row_elem.text) + 1  # 0-indexed in XML
                    position["from_col"] = get_column_letter(from_col + 1)
                    position["from_row"] = from_row
                    position["from_cell"] = f"{get_column_letter(from_col + 1)}{from_row}"

                    # Extract EMU offsets for precise positioning
                    col_off = int(col_off_elem.text) if col_off_elem is not None else 0
                    row_off = int(row_off_elem.text) if row_off_elem is not None else 0

                    # Calculate approximate absolute EMU position
                    # Standard Excel column width ≈ 64 pixels ≈ 914,400 EMUs (9144 * 100)
                    # Standard Excel row height ≈ 15 pixels ≈ 190,500 EMUs (1905 * 100)
                    position["emu_x"] = from_col * 914400 + col_off
                    position["emu_y"] = from_row * 190500 + row_off

                except (ValueError, TypeError) as e:
                    logger.debug(f"Error parsing from position: {e}")

        # Extract "to" position for twoCellAnchor
        if anchor_type == "twoCellAnchor":
            to_elem = anchor.find(".//xdr:to", namespaces)
            if to_elem is not None:
                col_elem = to_elem.find("xdr:col", namespaces)
                row_elem = to_elem.find("xdr:row", namespaces)
                if col_elem is not None and row_elem is not None:
                    try:
                        to_col = int(col_elem.text)
                        to_row = int(row_elem.text) + 1
                        position["to_col"] = get_column_letter(to_col + 1)
                        position["to_row"] = to_row
                        position["to_cell"] = f"{get_column_letter(to_col + 1)}{to_row}"
                    except (ValueError, TypeError) as e:
                        logger.debug(f"Error parsing to position: {e}")

        return position

    def _extract_shape_properties(
        self, shape_elem: ET.Element, namespaces: Dict[str, str]
    ) -> Dict[str, Any]:
        """
        Extract shape properties like id, name, type, and line properties.

        Args:
            shape_elem: Shape XML element
            namespaces: XML namespaces

        Returns:
            Dict with shape properties including line_color and line_dash
        """
        properties = {}

        # Extract id and name
        nv_sp_pr = shape_elem.find(".//xdr:nvSpPr", namespaces)
        if nv_sp_pr is not None:
            c_nv_pr = nv_sp_pr.find(".//xdr:cNvPr", namespaces)
            if c_nv_pr is not None:
                shape_id = c_nv_pr.get("id")
                if shape_id:
                    properties["id"] = shape_id

                name = c_nv_pr.get("name")
                if name:
                    properties["name"] = name

        # Extract shape type and line properties
        sp_pr = shape_elem.find(".//xdr:spPr", namespaces)
        if sp_pr is not None:
            prst_geom = sp_pr.find(".//a:prstGeom", namespaces)
            if prst_geom is not None:
                shape_type = prst_geom.get("prst")
                if shape_type:
                    properties["shape_type"] = shape_type

            # Extract line properties
            ln = sp_pr.find(".//a:ln", namespaces)
            if ln is not None:
                # Extract dash style
                prst_dash = ln.find(".//a:prstDash", namespaces)
                if prst_dash is not None:
                    dash_val = prst_dash.get("val")
                    if dash_val:
                        properties["line_dash"] = dash_val
                else:
                    properties["line_dash"] = "solid"

                # Extract line color
                # Check for srgbClr (RGB color)
                srgb_clr = ln.find(".//a:srgbClr", namespaces)
                if srgb_clr is not None:
                    color = srgb_clr.get("val")
                    if color:
                        properties["line_color"] = color

                # Check for schemeClr (theme color) - try to resolve
                scheme_clr = ln.find(".//a:schemeClr", namespaces)
                if scheme_clr is not None and "line_color" not in properties:
                    # Theme colors won't give us exact RGB, mark as unknown
                    properties["line_color"] = "theme:" + scheme_clr.get("val", "unknown")

        return properties

    def _extract_connectors_and_shape_map(
        self, file_path: str
    ) -> tuple[List[Dict[str, Any]], Dict[str, Dict[str, Any]]]:
        """
        Extract connectors and build shape ID map from Excel file.

        Args:
            file_path: Path to .xlsx file

        Returns:
            Tuple of (connectors list, shape_id_map dict)
        """
        connectors = []
        shape_id_map = {}

        try:
            with zipfile.ZipFile(file_path, "r") as zip_ref:
                # Get sheet names
                sheet_names = self._get_sheet_names(zip_ref)

                # Build sheet-to-drawing mapping
                sheet_to_drawing = self._get_sheet_to_drawing_mapping(
                    zip_ref, len(sheet_names)
                )

                # Find drawing files
                drawing_files = [
                    f
                    for f in zip_ref.namelist()
                    if f.startswith("xl/drawings/drawing") and f.endswith(".xml")
                ]

                for drawing_file in drawing_files:
                    try:
                        # Get sheet info
                        drawing_name = drawing_file.split("/")[-1]
                        sheet_name = None
                        for sid, dname in sheet_to_drawing.items():
                            if dname == drawing_name:
                                sheet_name = (
                                    sheet_names[sid - 1]
                                    if sid - 1 < len(sheet_names)
                                    else f"Sheet{sid}"
                                )
                                break

                        # Parse drawing XML
                        drawing_xml = zip_ref.read(drawing_file)
                        drawing_root = ET.fromstring(drawing_xml)

                        namespaces = {
                            "xdr": "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing",
                            "a": "http://schemas.openxmlformats.org/drawingml/2006/main",
                        }

                        # Build shape ID map for this drawing
                        drawing_shape_map = self._build_shape_id_map(
                            drawing_root, namespaces
                        )
                        shape_id_map.update(drawing_shape_map)

                        # Find connectors (twoCellAnchors containing cxnSp)
                        for anchor in drawing_root.findall(
                            ".//xdr:twoCellAnchor", namespaces
                        ):
                            connector = self._extract_connector_from_anchor(
                                anchor, namespaces, sheet_name
                            )
                            if connector:
                                connectors.append(connector)

                    except Exception as e:
                        logger.debug(f"Error parsing {drawing_file}: {e}")

        except Exception as e:
            logger.debug(f"Error extracting connectors and shape map: {e}")

        logger.info(
            f"Extracted {len(connectors)} connectors and {len(shape_id_map)} shape positions"
        )
        return connectors, shape_id_map

    def _extract_connector_from_anchor(
        self, anchor: ET.Element, namespaces: Dict[str, str], sheet_name: str
    ) -> Optional[Dict[str, Any]]:
        """
        Extract connector position and target information.

        Args:
            anchor: Connector anchor element (twoCellAnchor containing cxnSp)
            namespaces: XML namespaces
            sheet_name: Sheet name

        Returns:
            Dict with connector 'from'/'to' positions and connected_to_id
        """
        try:
            connector = anchor.find(".//xdr:cxnSp", namespaces)
            if connector is None:
                return None

            # Get connector's position from the anchor
            from_elem = anchor.find(".//xdr:from", namespaces)
            to_elem = anchor.find(".//xdr:to", namespaces)

            conn_from = None
            conn_to = None

            if from_elem is not None:
                col = from_elem.find("xdr:col", namespaces)
                row = from_elem.find("xdr:row", namespaces)
                if col is not None and row is not None:
                    conn_from = {"col": int(col.text), "row": int(row.text)}

            if to_elem is not None:
                col = to_elem.find("xdr:col", namespaces)
                row = to_elem.find("xdr:row", namespaces)
                if col is not None and row is not None:
                    conn_to = {"col": int(col.text), "row": int(row.text)}

            # Find connection target (shape ID this connector points to)
            connected_to_id = None
            endCxn = connector.find(".//a:endCxn", namespaces)
            if endCxn is not None:
                connected_to_id = endCxn.get("id")

            return {
                "from": conn_from,
                "to": conn_to,
                "connected_to_id": connected_to_id,
            }

        except Exception as e:
            logger.debug(f"Error extracting connector: {e}")
            return None

    def _build_shape_id_map(
        self, drawing_root: ET.Element, namespaces: Dict[str, str]
    ) -> Dict[str, Dict[str, Any]]:
        """
        Build a map of shape IDs to their positions.

        Args:
            drawing_root: Root element of the drawing XML
            namespaces: XML namespaces dictionary

        Returns:
            Dictionary mapping shape ID to position info {'from': {...}, 'to': {...}}
        """
        shape_id_map = {}

        try:
            # Find all twoCellAnchors
            for anchor in drawing_root.findall(".//xdr:twoCellAnchor", namespaces):
                # Get from/to positions
                from_elem = anchor.find(".//xdr:from", namespaces)
                to_elem = anchor.find(".//xdr:to", namespaces)

                from_pos = None
                to_pos = None

                if from_elem is not None:
                    col = from_elem.find("xdr:col", namespaces)
                    row = from_elem.find("xdr:row", namespaces)
                    if col is not None and row is not None:
                        from_pos = {"col": int(col.text), "row": int(row.text)}

                if to_elem is not None:
                    col = to_elem.find("xdr:col", namespaces)
                    row = to_elem.find("xdr:row", namespaces)
                    if col is not None and row is not None:
                        to_pos = {"col": int(col.text), "row": int(row.text)}

                # Get shape ID
                shape_id = None
                for cNvPr in anchor.findall(".//xdr:cNvPr", namespaces):
                    shape_id = cNvPr.get("id")
                    if shape_id:
                        break

                if shape_id and from_pos:
                    shape_id_map[shape_id] = {"from": from_pos, "to": to_pos}

        except Exception as e:
            logger.debug(f"Error building shape ID map: {e}")

        return shape_id_map

    def _match_connectors_to_shapes(
        self,
        shapes: List[Dict[str, Any]],
        connectors: List[Dict[str, Any]],
        shape_id_map: Dict[str, Dict[str, Any]],
    ) -> List[Dict[str, Any]]:
        """
        Match connectors to shapes using position-based matching with tolerance.

        Uses the metis-core approach:
        - For each shape, check if a connector starts near it (within 2 rows, 4 columns)
        - If found, use connector's connected_to_id to find target, or use connector's 'to' position

        Args:
            shapes: List of shapes
            connectors: List of connectors with from/to positions
            shape_id_map: Map of shape IDs to their positions

        Returns:
            Shapes with connector_target_cell added if connector found
        """
        logger.info(f"Matching {len(connectors)} connectors to {len(shapes)} shapes")
        matched_count = 0

        for shape in shapes:
            from_row = shape.get("from_row")
            from_col_str = shape.get("from_col")

            if from_row is None or from_col_str is None:
                continue

            # Convert column letter to number (0-based)
            from openpyxl.utils import column_index_from_string

            from_col = column_index_from_string(from_col_str) - 1  # Convert to 0-based
            from_row -= 1  # Convert to 0-based

            # Look for connectors starting near this shape
            for conn in connectors:
                if conn["from"]:
                    # Check if connector starts within 2 rows and 4 columns of the shape
                    row_diff = abs(conn["from"]["row"] - from_row)
                    col_diff = abs(conn["from"]["col"] - from_col)

                    if row_diff <= 2 and col_diff <= 4:
                        # Found a connector from this shape
                        target_pos = None

                        # Try to use connected_to_id first
                        if conn.get("connected_to_id") and shape_id_map:
                            target_shape = shape_id_map.get(conn["connected_to_id"])
                            if target_shape and target_shape.get("from"):
                                target_pos = target_shape["from"]
                                logger.debug(
                                    f"Found connector target via shape ID {conn['connected_to_id']}"
                                )

                        # Fall back to connector's 'to' position
                        if not target_pos and conn.get("to"):
                            target_pos = conn["to"]
                            logger.debug(
                                f"Using connector 'to' position as target"
                            )

                        if target_pos:
                            target_cell = f"{get_column_letter(target_pos['col'] + 1)}{target_pos['row'] + 1}"
                            shape["connector_target_cell"] = target_cell
                            matched_count += 1
                            logger.info(
                                f"Matched connector: '{shape.get('text', '')[:30]}' "
                                f"(from {from_col_str}{from_row + 1}) -> {target_cell}"
                            )
                            break  # Found connector for this shape, move to next shape

        logger.info(f"Successfully matched {matched_count} connectors to shapes")
        return shapes