"""Service for extracting images from Excel documents with metadata and AI descriptions."""

import base64
import io
import os
import tempfile
import xml.etree.ElementTree as ET
import zipfile
from functools import lru_cache
from pathlib import Path
from typing import Any, Dict, List, Optional

import aiofiles
from openpyxl import load_workbook
from openpyxl.drawing.image import Image as OpenpyxlImage
from openpyxl.utils import get_column_letter
from PIL import Image

import logging
from src.services.azure_chat_service import AzureChatService

logger = logging.getLogger(__name__)


class ExcelImageExtractionService:
    """Service for extracting images from Excel files with metadata and AI-generated descriptions."""

    def __init__(self, azure_chat_service: AzureChatService):
        """Initialize the Excel image extraction service.

        Args:
            azure_chat_service: Service for LLM chat completions and image analysis
        """
        self.azure_chat_service = azure_chat_service

    async def extract_images_from_file(
        self,
        file_content: bytes,
        file_id: str,
        original_filename: Optional[str] = None,
        generate_descriptions: bool = False,
        description_prompt: Optional[str] = None,
        description_detail: str = "auto",
    ) -> Dict[str, Any]:
        """Extract images from an Excel file with metadata and optional AI descriptions.

        Args:
            file_content: Binary content of the Excel file
            file_id: File ID (UUID) for tracking
            original_filename: Original filename (optional)
            generate_descriptions: Whether to generate AI descriptions for images
            description_prompt: Custom prompt for image description (optional)
            description_detail: Level of detail for image analysis ('low', 'high', or 'auto')

        Returns:
            Dictionary containing:
                - source_file: Original filename
                - file_id: File ID
                - total_images: Number of images extracted
                - images: List of image metadata dictionaries

        Raises:
            Exception: If extraction fails
        """
        temp_file_path = None
        temp_output_dir = None

        try:
            # Determine file extension
            suffix = ".xlsx"
            if original_filename:
                _, ext = os.path.splitext(original_filename)
                if ext in [".xlsx", ".xls", ".xlsm", ".xlsb"]:
                    suffix = ext

            # Create temporary file
            temp_fd, temp_file_path = tempfile.mkstemp(suffix=suffix)
            os.close(temp_fd)

            # Write content
            async with aiofiles.open(temp_file_path, mode='wb') as temp_file:
                await temp_file.write(file_content)

            # Create temporary output directory
            temp_output_dir = tempfile.mkdtemp()
            output_path = Path(temp_output_dir)

            logger.info(f"Extracting images from: {original_filename or 'uploaded_file'}")

            # Extract images using archive method (more reliable)
            images_metadata = await self._extract_images_from_archive(
                temp_file_path, output_path
            )

            # Generate descriptions if requested
            if generate_descriptions and images_metadata:
                logger.info(f"Generating descriptions for {len(images_metadata)} images...")
                images_metadata = await self._generate_descriptions_for_all_images(
                    images_metadata,
                    file_path=temp_file_path,
                    prompt=description_prompt,
                    detail=description_detail,
                )

            # Prepare response
            result = {
                "source_file": original_filename or "uploaded_file.xlsx",
                "file_id": file_id,
                "total_images": len(images_metadata),
                "images": images_metadata,
            }

            logger.info(f"Successfully extracted {len(images_metadata)} images")
            return result

        finally:
            # Clean up temporary files
            if temp_file_path:
                try:
                    os.remove(temp_file_path)
                except Exception as e:
                    logger.debug(f"Failed to remove temp file: {e}")

            if temp_output_dir:
                try:
                    import shutil
                    shutil.rmtree(temp_output_dir)
                except Exception as e:
                    logger.debug(f"Failed to remove temp directory: {e}")

    async def _extract_images_from_archive(
        self, file_path: str, output_path: Path
    ) -> List[Dict[str, Any]]:
        """Extract images by unzipping the .xlsx file.

        Args:
            file_path: Path to the Excel file
            output_path: Directory to save extracted images

        Returns:
            List of dictionaries containing image metadata
        """
        output_path.mkdir(parents=True, exist_ok=True)
        images_metadata = []

        try:
            # .xlsx files are ZIP archives
            with zipfile.ZipFile(file_path, 'r') as zip_ref:
                # Extract images from drawings to get proper sheet mapping
                # Note: We iterate through drawings instead of media files because
                # the same image can appear in multiple sheets
                logger.info("Parsing images from drawings...")
                images_metadata = self._extract_images_from_drawings(zip_ref, output_path)

            return images_metadata

        except zipfile.BadZipFile:
            logger.error("File is not a valid ZIP archive (not a .xlsx file?)")
            raise
        except Exception as e:
            logger.error(f"Error extracting images: {e}")
            raise

    def _extract_images_from_drawings(
        self, zip_ref: zipfile.ZipFile, output_path: Path
    ) -> List[Dict[str, Any]]:
        """Extract images by iterating through drawing files.

        This approach ensures each image occurrence gets mapped to the correct sheet,
        even if the same image file appears in multiple sheets.

        Args:
            zip_ref: ZipFile reference to the Excel file
            output_path: Directory to save extracted images

        Returns:
            List of dictionaries containing image metadata
        """
        images_metadata = []
        image_counter = 1

        try:
            # Get sheet names from workbook.xml
            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')
                    sheet_names.append(sheet_name)
            except Exception as e:
                logger.debug(f"Could not parse sheet names: {e}")

            # Build mapping from drawing files to sheet names
            drawing_to_sheet_map = {}
            try:
                for sheet_idx in range(1, len(sheet_names) + 1):
                    sheet_rels_file = f'xl/worksheets/_rels/sheet{sheet_idx}.xml.rels'
                    try:
                        sheet_rels_xml = zip_ref.read(sheet_rels_file)
                        sheet_rels_root = ET.fromstring(sheet_rels_xml)

                        for rel in sheet_rels_root.findall(
                            './/{http://schemas.openxmlformats.org/package/2006/relationships}Relationship'
                        ):
                            target = rel.get('Target', '')
                            # Look for drawing XML files (not vmlDrawing files)
                            if 'drawing' in target and target.endswith('.xml') and 'vml' not in target.lower():
                                drawing_name = target.replace('../drawings/', '')
                                drawing_file = f'xl/drawings/{drawing_name}'
                                drawing_to_sheet_map[drawing_file] = {
                                    'sheet_name': sheet_names[sheet_idx - 1],
                                    'sheet_number': sheet_idx
                                }
                                # Don't break - there might be multiple drawings per sheet
                    except:
                        continue
            except Exception as e:
                logger.debug(f"Could not build drawing to sheet mapping: {e}")

            # Get all drawing files
            drawing_files = sorted([
                f for f in zip_ref.namelist()
                if f.startswith('xl/drawings/drawing') and f.endswith('.xml') and '_rels' not in f
            ])

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

            # Process each drawing file
            for drawing_file in drawing_files:
                try:
                    # Get sheet info for this drawing
                    sheet_info = drawing_to_sheet_map.get(drawing_file, {})
                    sheet_name = sheet_info.get('sheet_name', 'Unknown')
                    sheet_number = sheet_info.get('sheet_number', 0)

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

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

                    # Find all image anchors
                    for anchor in drawing_root.findall('.//xdr:twoCellAnchor', namespaces):
                        try:
                            # Get position information
                            position = {}
                            from_elem = anchor.find('.//xdr:from', namespaces)
                            to_elem = anchor.find('.//xdr:to', namespaces)

                            if from_elem is not None:
                                col_elem = from_elem.find('xdr:col', namespaces)
                                row_elem = from_elem.find('xdr:row', namespaces)
                                if col_elem is not None and row_elem is not None:
                                    from_col = int(col_elem.text)
                                    from_row = int(row_elem.text) + 1
                                    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}"

                            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:
                                    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}"

                            # Get image reference
                            blip = anchor.find('.//a:blip', namespaces)
                            if blip is not None:
                                embed = blip.get(
                                    '{http://schemas.openxmlformats.org/officeDocument/2006/relationships}embed'
                                )
                                if embed:
                                    # Get image path from drawing relationships
                                    drawing_rels_file = drawing_file.replace(
                                        'drawings/', 'drawings/_rels/'
                                    ).replace('.xml', '.xml.rels')

                                    try:
                                        drawing_rels_xml = zip_ref.read(drawing_rels_file)
                                        drawing_rels_root = ET.fromstring(drawing_rels_xml)

                                        for rel in drawing_rels_root.findall(
                                            './/{http://schemas.openxmlformats.org/package/2006/relationships}Relationship'
                                        ):
                                            if rel.get('Id') == embed:
                                                image_target = rel.get('Target')
                                                # Construct full path
                                                if image_target.startswith('../'):
                                                    media_file = 'xl/' + image_target.replace('../', '')
                                                else:
                                                    media_file = 'xl/drawings/' + image_target

                                                # Extract and process the image
                                                image_data = zip_ref.read(media_file)

                                                # Get original filename and extension
                                                original_filename = Path(media_file).name
                                                file_ext = Path(media_file).suffix

                                                # Generate output filename
                                                output_filename = f"image_{image_counter:03d}{file_ext}"
                                                output_file = output_path / output_filename

                                                # Save image
                                                output_file.write_bytes(image_data)

                                                # Get image dimensions
                                                try:
                                                    img = Image.open(io.BytesIO(image_data))
                                                    width, height = img.size
                                                except Exception:
                                                    width, height = None, None

                                                # Convert image to base64
                                                base64_image = base64.b64encode(image_data).decode('utf-8')

                                                # Create metadata
                                                metadata = {
                                                    "image_number": image_counter,
                                                    "filename": output_filename,
                                                    "original_filename": original_filename,
                                                    "format": file_ext.replace('.', '').upper(),
                                                    "width": width,
                                                    "height": height,
                                                    "file_path": str(output_file),
                                                    "archive_path": media_file,
                                                    "base64_data": base64_image,
                                                    "mime_type": self._get_mime_type(file_ext),
                                                    "sheet_name": sheet_name,
                                                    "sheet_number": sheet_number,
                                                    "position": position,
                                                }

                                                images_metadata.append(metadata)

                                                # Log extraction
                                                log_msg = f"Extracted image {image_counter}: {output_filename}"
                                                if sheet_name:
                                                    log_msg += f" from sheet '{sheet_name}'"
                                                    if position.get('from_cell'):
                                                        log_msg += f" at cell {position['from_cell']}"
                                                logger.info(log_msg)

                                                image_counter += 1
                                                break  # Found the image for this embed ID
                                    except Exception as e:
                                        logger.debug(f"Could not process image from {drawing_file}: {e}")
                        except Exception as e:
                            logger.debug(f"Error processing anchor in {drawing_file}: {e}")
                            continue

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

        except Exception as e:
            logger.error(f"Error extracting images from drawings: {e}")

        logger.info(f"Extracted {len(images_metadata)} images total")
        return images_metadata

    def _parse_drawing_relationships(
        self, zip_ref: zipfile.ZipFile
    ) -> Dict[str, Dict[str, Any]]:
        """Parse drawing relationships to map images to sheets and positions.

        Returns:
            Dictionary mapping image path to sheet and position info
        """
        image_map = {}

        try:
            # Get sheet names from workbook.xml
            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')
                    sheet_names.append(sheet_name)
            except Exception as e:
                logger.debug(f"Could not parse sheet names: {e}")

            # Build mapping from drawing files to sheet names by checking sheet relationships
            drawing_to_sheet_map = {}
            try:
                for sheet_idx in range(1, len(sheet_names) + 1):
                    sheet_rels_file = f'xl/worksheets/_rels/sheet{sheet_idx}.xml.rels'
                    try:
                        sheet_rels_xml = zip_ref.read(sheet_rels_file)
                        sheet_rels_root = ET.fromstring(sheet_rels_xml)

                        for rel in sheet_rels_root.findall(
                            './/{http://schemas.openxmlformats.org/package/2006/relationships}Relationship'
                        ):
                            target = rel.get('Target', '')
                            if 'drawing' in target:
                                # Extract drawing filename
                                drawing_name = target.replace('../drawings/', '')
                                drawing_file = f'xl/drawings/{drawing_name}'
                                # Map drawing to sheet name (sheet_idx is 1-based, sheet_names is 0-based)
                                drawing_to_sheet_map[drawing_file] = sheet_names[sheet_idx - 1]
                                break
                    except:
                        continue
            except Exception as e:
                logger.debug(f"Could not build drawing to sheet mapping: {e}")

            # Parse drawing files to find image positions
            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 name from the drawing-to-sheet mapping
                    sheet_name = drawing_to_sheet_map.get(drawing_file)
                    if not sheet_name:
                        # Fallback to old method if mapping not found
                        drawing_num = int(drawing_file.split('drawing')[-1].replace('.xml', ''))
                        sheet_idx = drawing_num - 1
                        sheet_name = (
                            sheet_names[sheet_idx]
                            if sheet_idx < len(sheet_names)
                            else f"Sheet{sheet_idx + 1}"
                        )
                        logger.debug(f"Using fallback mapping for {drawing_file}: {sheet_name}")

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

                    # Find all images in this drawing
                    namespaces = {
                        'xdr': 'http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing',
                        'a': 'http://schemas.openxmlformats.org/drawingml/2006/main',
                    }

                    for anchor in drawing_root.findall('.//xdr:twoCellAnchor', namespaces):
                        # Get position
                        from_elem = anchor.find('.//xdr:from', namespaces)
                        to_elem = anchor.find('.//xdr:to', namespaces)

                        position = {}
                        if from_elem is not None:
                            col_elem = from_elem.find('xdr:col', namespaces)
                            row_elem = from_elem.find('xdr:row', namespaces)
                            if col_elem is not None and row_elem is not None:
                                from_col = int(col_elem.text)
                                from_row = int(row_elem.text) + 1
                                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}"

                        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:
                                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}"

                        # Get image reference
                        blip = anchor.find('.//a:blip', namespaces)
                        if blip is not None:
                            embed = blip.get(
                                '{http://schemas.openxmlformats.org/officeDocument/2006/relationships}embed'
                            )
                            if embed:
                                # Parse drawing relationships to get actual image file
                                drawing_rels_file = drawing_file.replace(
                                    'drawings/', 'drawings/_rels/'
                                ).replace('.xml', '.xml.rels')
                                try:
                                    drawing_rels_xml = zip_ref.read(drawing_rels_file)
                                    drawing_rels_root = ET.fromstring(drawing_rels_xml)

                                    for rel in drawing_rels_root.findall(
                                        './/{http://schemas.openxmlformats.org/package/2006/relationships}Relationship'
                                    ):
                                        if rel.get('Id') == embed:
                                            image_target = rel.get('Target')
                                            # Construct full path
                                            if image_target.startswith('../'):
                                                image_path = 'xl/' + image_target.replace('../', '')
                                            else:
                                                image_path = 'xl/drawings/' + image_target

                                            image_map[image_path] = {
                                                'sheet_name': sheet_name,
                                                'sheet_number': sheet_idx + 1,
                                                'position': position,
                                            }
                                except Exception as e:
                                    logger.debug(
                                        f"Could not parse drawing relationships for {drawing_file}: {e}"
                                    )

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

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

        return image_map

    async def _generate_descriptions_for_all_images(
        self,
        images_metadata: List[Dict[str, Any]],
        file_path: str,
        prompt: Optional[str] = None,
        detail: str = "auto",
    ) -> List[Dict[str, Any]]:
        """Generate AI descriptions for all extracted images.

        Args:
            images_metadata: List of image metadata dictionaries
            file_path: Path to the Excel file for extracting surrounding text context
            prompt: Custom prompt for image description (optional)
            detail: Level of detail for image analysis ('low', 'high', or 'auto')

        Returns:
            Updated metadata list with descriptions added
        """
        if not images_metadata:
            return []

        # Default prompt if none provided
        if prompt is None:
            prompt = (
                "Describe this image from an Excel spreadsheet. Focus only on what is present:\n"
                "- For charts/graphs: Identify the type, describe axes, data series, trends, and key insights\n"
                "- For tables: Describe the structure, headers, and data patterns\n"
                "- For diagrams/illustrations: Describe the visual elements and their relationships\n"
                "- For text/labels: Include any visible text or annotations\n\n"
                "Provide a direct, factual description without preambles or conversational phrases. "
                "Do not mention what the image lacks or does not contain."
            )

        logger.info(f"Generating descriptions for {len(images_metadata)} images...")

        for idx, img_meta in enumerate(images_metadata, 1):
            try:
                logger.info(
                    f"Processing image {idx}/{len(images_metadata)}: {img_meta.get('filename')}"
                )

                # Extract surrounding text context for better description
                context_text = self._extract_surrounding_text(
                    file_path=file_path,
                    sheet_name=img_meta.get('sheet_name'),
                    position=img_meta.get('position', {}),
                    context_rows=20,
                    context_cols=20,
                )

                # Generate description using base64 data with context
                description = await self._generate_image_description(
                    base64_image=img_meta['base64_data'],
                    mime_type=img_meta['mime_type'],
                    prompt=prompt,
                    detail=detail,
                    context_text=context_text,
                )

                # Add description to metadata
                img_meta['description'] = description

            except Exception as e:
                logger.error(f"Failed to generate description for image {idx}: {e}")
                img_meta['description'] = "[Error generating description]"
                img_meta['description_error'] = str(e)

        logger.info("Finished generating descriptions for all images")
        return images_metadata

    async def _generate_image_description(
        self,
        base64_image: str,
        mime_type: str,
        prompt: str,
        detail: str = "auto",
        context_text: Optional[str] = None,
    ) -> str:
        """Generate description for a single image using Azure Chat Service.

        Args:
            base64_image: Base64 encoded image data
            mime_type: MIME type of the image
            prompt: Prompt for image description
            detail: Level of detail for image analysis ('low', 'high', or 'auto')
            context_text: Surrounding cell text from Excel for context (optional)

        Returns:
            Generated description of the image
        """
        # Build prompt with context if available
        full_prompt = prompt
        if context_text:
            full_prompt = (
                f"{prompt}\n\n"
                f"Context - Surrounding cell text from the Excel spreadsheet (organized by position):\n"
                f"```\n{context_text}\n```\n\n"
                f"Use this positional context to provide more accurate descriptions.\n"
                f"Incorporate relevant context naturally into your description.\n"
                f"Only provide description based on what is present in the image."
            )

        # Create message with image
        messages = [
            {
                "role": "system",
                "content": (
                    "You are a precise image analysis assistant. Provide direct, factual descriptions "
                    "without conversational phrases, preambles, or statements about what is absent. "
                    "Start immediately with the description."
                ),
            },
            {
                "role": "user",
                "content": [
                    {"type": "text", "text": full_prompt},
                    {
                        "type": "image_url",
                        "image_url": {
                            "url": f"data:{mime_type};base64,{base64_image}",
                            "detail": detail,
                        },
                    },
                ],
            }
        ]
        try:
            # Generate description using Azure Chat Service
            description = await self.azure_chat_service.ainvoke(messages)
            return description
        except Exception as e:
            logger.error(f"Error generating image description: {e}")
            raise

    def _extract_surrounding_text(
        self,
        file_path: str,
        sheet_name: Optional[str],
        position: Dict[str, Any],
        context_rows: int = 20,
        context_cols: int = 20,
    ) -> Optional[str]:
        """Extract surrounding cell text from Excel for context, organized by position.

        This method extracts text from four regions around an image and applies intelligent
        truncation to keep the most relevant text (closest to the image) within a 500 char
        limit per region.

        Truncation Strategy:
        - ABOVE: Keep bottom rows (closest to image), truncate top rows
        - LEFT: Keep rightmost columns (closest to image), truncate leftmost columns
        - RIGHT: Keep leftmost columns (closest to image), truncate rightmost columns
        - BELOW: Keep top rows (closest to image), truncate bottom rows

        Args:
            file_path: Path to the Excel file
            sheet_name: Name of the sheet containing the image
            position: Position dictionary with from_row, from_col, to_row, to_col
            context_rows: Number of rows to extract before and after image (default: 20)
            context_cols: Number of columns to extract before and after image (default: 20)

        Returns:
            Formatted string with surrounding cell values organized by position with
            positional labels [ABOVE IMAGE], [LEFT OF IMAGE], [RIGHT OF IMAGE], [BELOW IMAGE],
            or None if extraction fails
        """
        if not sheet_name or not position:
            return None

        try:
            # Load workbook in read-only mode for performance
            workbook = load_workbook(file_path, read_only=True, data_only=True)

            if sheet_name not in workbook.sheetnames:
                logger.warning(f"Sheet '{sheet_name}' not found in workbook")
                return None

            sheet = workbook[sheet_name]

            # Get image bounds
            from_row = position.get('from_row', 1)
            from_col_letter = position.get('from_col', 'A')
            to_row = position.get('to_row', from_row)
            to_col_letter = position.get('to_col', from_col_letter)

            # Convert column letters to numbers
            from openpyxl.utils import column_index_from_string
            from_col = column_index_from_string(from_col_letter)
            to_col = column_index_from_string(to_col_letter)

            # Calculate context window boundaries
            above_start_row = max(1, from_row - context_rows)
            above_end_row = from_row - 1
            below_start_row = to_row + 1
            below_end_row = to_row + context_rows
            left_start_col = max(1, from_col - context_cols)
            left_end_col = from_col - 1
            right_start_col = to_col + 1
            right_end_col = to_col + context_cols

            # Helper function to extract and format text from a region with character limit
            def extract_region(start_r, end_r, start_c, end_c, max_chars=500, truncate_from_end=False):
                """
                Extract text from a region with character limit.
                Uses iter_rows for optimized performance on large Excel files.

                Args:
                    max_chars: Maximum number of characters to extract
                    truncate_from_end: If True, truncate from end to keep text closest to image.
                                      If False, truncate from start to keep text closest to image.
                """
                if truncate_from_end:
                    # For ABOVE and LEFT: keep text closest to image (from end)
                    # Use iter_rows for efficiency, then process in reverse
                    all_rows = []
                    total_chars = 0

                    # Collect rows using efficient iter_rows, then reverse
                    rows_list = list(sheet.iter_rows(min_row=start_r, max_row=end_r,
                                                      min_col=start_c, max_col=end_c,
                                                      values_only=True))

                    # Process in reverse to keep text closest to image
                    for row_cells in reversed(rows_list):
                        row_data = []
                        for cell_value in row_cells:
                            # Process cell value
                            if cell_value is not None and str(cell_value).strip():
                                row_data.append(str(cell_value).strip())
                            else:
                                row_data.append("")

                        # Only include rows with at least one non-empty cell
                        if any(cell for cell in row_data):
                            # Filter out empty cells at the end
                            while row_data and not row_data[-1]:
                                row_data.pop()
                            if row_data:
                                row_line = " | ".join(row_data)
                                row_len = len(row_line) + 1  # +1 for newline

                                # Check character limit
                                if total_chars + row_len > max_chars and all_rows:
                                    all_rows.insert(0, "... (truncated)")
                                    break

                                all_rows.insert(0, row_line)
                                total_chars += row_len

                    return all_rows
                else:
                    # For RIGHT and BELOW: keep text closest to image (from start)
                    # Use iter_rows for efficient extraction
                    region_data = []
                    total_chars = 0

                    # Use iter_rows for efficient cell access
                    for row_cells in sheet.iter_rows(min_row=start_r, max_row=end_r,
                                                      min_col=start_c, max_col=end_c,
                                                      values_only=True):
                        row_data = []
                        for cell_value in row_cells:
                            # Process cell value
                            if cell_value is not None and str(cell_value).strip():
                                row_data.append(str(cell_value).strip())
                            else:
                                row_data.append("")

                        # Only include rows with at least one non-empty cell
                        if any(cell for cell in row_data):
                            # Filter out empty cells at the end
                            while row_data and not row_data[-1]:
                                row_data.pop()
                            if row_data:
                                row_line = " | ".join(row_data)
                                row_len = len(row_line) + 1  # +1 for newline

                                # Check character limit
                                if total_chars + row_len > max_chars and region_data:
                                    region_data.append("... (truncated)")
                                    break

                                region_data.append(row_line)
                                total_chars += row_len

                    return region_data

            # Extract text from each region
            context_sections = []

            # Above the image (full width of context window)
            # Truncate from end to keep rows closest to image
            if above_end_row >= above_start_row:
                above_text = extract_region(
                    above_start_row, above_end_row,
                    left_start_col, right_end_col,
                    max_chars=500,
                    truncate_from_end=True  # Keep text closest to image (bottom rows)
                )
                if above_text:
                    context_sections.append("[ABOVE IMAGE]\n" + "\n".join(above_text))

            # Left of the image (same row range as image)
            # Truncate from end to keep columns closest to image
            if left_end_col >= left_start_col:
                left_text = extract_region(
                    from_row, to_row,
                    left_start_col, left_end_col,
                    max_chars=500,
                    truncate_from_end=True  # Keep text closest to image (rightmost columns)
                )
                if left_text:
                    context_sections.append("[LEFT OF IMAGE]\n" + "\n".join(left_text))

            # Right of the image (same row range as image)
            # Truncate from start to keep columns closest to image
            if right_end_col >= right_start_col:
                right_text = extract_region(
                    from_row, to_row,
                    right_start_col, right_end_col,
                    max_chars=500,
                    truncate_from_end=False  # Keep text closest to image (leftmost columns)
                )
                if right_text:
                    context_sections.append("[RIGHT OF IMAGE]\n" + "\n".join(right_text))

            # Below the image (full width of context window)
            # Truncate from start to keep rows closest to image
            if below_end_row >= below_start_row:
                below_text = extract_region(
                    below_start_row, below_end_row,
                    left_start_col, right_end_col,
                    max_chars=500,
                    truncate_from_end=False  # Keep text closest to image (top rows)
                )
                if below_text:
                    context_sections.append("[BELOW IMAGE]\n" + "\n".join(below_text))

            workbook.close()

            if not context_sections:
                return None

            # Combine all sections
            context_text = "\n\n".join(context_sections)

            logger.debug(
                f"Extracted context from {len(context_sections)} regions "
                f"(total {len(context_text)} characters)"
            )
            return context_text

        except Exception as e:
            logger.warning(f"Could not extract surrounding text: {e}")
            return None

    @staticmethod
    def _get_mime_type(file_ext: str) -> str:
        """Get MIME type from file extension.

        Args:
            file_ext: File extension (e.g., '.png', '.jpg')

        Returns:
            MIME type string
        """
        mime_type_map = {
            '.png': 'image/png',
            '.jpg': 'image/jpeg',
            '.jpeg': 'image/jpeg',
            '.gif': 'image/gif',
            '.webp': 'image/webp',
            '.bmp': 'image/bmp',
            '.tiff': 'image/tiff',
            '.tif': 'image/tiff',
        }
        return mime_type_map.get(file_ext.lower(), 'image/png')


@lru_cache()
def get_excel_image_extraction_service() -> ExcelImageExtractionService:
    """Get a cached Excel image extraction service instance.

    Returns:
        ExcelImageExtractionService instance
    """
    from src.services.azure_chat_service import get_azure_chat_service

    return ExcelImageExtractionService(
        azure_chat_service=get_azure_chat_service()
    )
