"""
Border-based Table Detection for Excel/LibreOffice Spreadsheets

This script detects table ranges by analyzing cell borders in spreadsheet files.
It identifies contiguous regions with borders and determines their bounding boxes.
It can also insert separator rows to isolate tables from surrounding content.
"""

from pathlib import Path
from typing import List, Set, Tuple

import structlog
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.worksheet import Worksheet

from src.extraction_v2.excel_loader import load_excel_file

logger = structlog.get_logger(__name__)


class BorderTableDetector:
    """Detects table boundaries by analyzing cell borders in Excel files."""

    # Default threshold for large table classification
    DEFAULT_LARGE_TABLE_THRESHOLD = 1000

    def __init__(self, large_table_threshold: int = DEFAULT_LARGE_TABLE_THRESHOLD):
        self.min_table_size = 4  # Minimum cells to consider a valid table
        self.large_table_threshold = large_table_threshold

    def is_large_table(self, table_info: dict) -> bool:
        """
        Check if a table should be classified as 'large'.

        Large tables (>1000 rows by default) require special handling
        with chunked processing instead of standard Docling extraction.

        Args:
            table_info: Table information dictionary from detect_tables_by_border()

        Returns:
            True if table exceeds the large table threshold
        """
        row_count = table_info['end_row'] - table_info['start_row'] + 1
        return row_count > self.large_table_threshold

    def classify_tables(self, tables: list[dict]) -> tuple[list[dict], list[dict]]:
        """
        Classify tables into large and normal categories.

        Args:
            tables: List of table info dictionaries

        Returns:
            Tuple of (large_tables, normal_tables)
        """
        large_tables = []
        normal_tables = []

        for table in tables:
            if self.is_large_table(table):
                large_tables.append(table)
            else:
                normal_tables.append(table)

        return large_tables, normal_tables
        
    def detect_tables_by_border(self, file_path: str) -> List[dict]:
        """
        Detect all tables in an Excel file by analyzing borders.
        
        Args:
            file_path: Path to the Excel file (.xlsx, .xlsm)
            
        Returns:
            List of dictionaries containing table information:
            {
                'sheet': sheet name,
                'range': Excel range notation (e.g., 'C64:Z79'),
                'start_row': int,
                'end_row': int,
                'start_col': str (letter),
                'end_col': str (letter),
                'cell_count': int
            }
        """
        workbook = load_excel_file(file_path, data_only=False)
        all_tables = []
        
        for sheet in workbook.worksheets:
            logger.info(f"Analyzing sheet: {sheet.title}")
            tables = self._detect_tables_in_sheet(sheet)
            all_tables.extend(tables)
            
            for table in tables:
                logger.info(
                    f"Detected table in {sheet.title}: {table['range']} "
                    f"({table['cell_count']} cells)"
                )
        
        return all_tables
    
    def insert_separator_rows(self, file_path: str, output_path: str = None) -> dict:
        """
        Insert blank rows to separate tables from surrounding content.
        Uses openpyxl with proper formatting preservation.
        
        Args:
            file_path: Path to the input Excel file
            output_path: Path to save the modified file (if None, creates "_separated" version in same folder)
            
        Returns:
            Dictionary with statistics about insertions
        """
        if output_path is None:
            # Create output file in same folder as input with "_separated" suffix
            input_path = Path(file_path)
            output_path = input_path.parent / f"{input_path.stem}_separated{input_path.suffix}"
        
        return self._insert_separator_rows_openpyxl(file_path, output_path)
    
    def _insert_separator_rows_openpyxl(self, file_path: str, output_path: str) -> dict:
        """
        Insert separator rows by copying workbook cell-by-cell.
        This preserves ALL formatting, merged cells, and structure perfectly.
        """
        from openpyxl import Workbook
        from openpyxl.utils.cell import get_column_letter
        from copy import copy
        import shutil

        # Load source workbook to check for inflated sheets
        source_wb = load_excel_file(file_path, read_only=True, data_only=False)

        # Check if any sheet has inflated max_row
        has_inflated_sheets = any(
            sheet.max_row and sheet.max_row > 5000
            for sheet in source_wb.worksheets
        )
        source_wb.close()

        # If file has inflated sheets, skip separator insertion entirely
        if has_inflated_sheets:
            logger.warning(
                "File contains sheets with inflated max_row (>5000), "
                "skipping separator insertion for performance. "
                "Copying file as-is."
            )
            shutil.copy2(file_path, output_path)
            return {
                'tables_processed': 0,
                'rows_inserted_above': 0,
                'rows_inserted_below': 0,
                'details': [],
                'skipped': True
            }

        # Load source workbook for processing
        source_wb = load_excel_file(file_path, data_only=False)

        # First pass: check if ANY rows need to be inserted across all sheets
        # If not, just copy the file to preserve drawings/shapes
        total_rows_to_insert = 0
        for sheet in source_wb.worksheets:
            tables = self._detect_tables_in_sheet(sheet)
            for table in tables:
                # Check row above
                if table['start_row'] > 1:
                    if self._row_has_content(sheet, table['start_row'] - 1):
                        total_rows_to_insert += 1
                # Check row below
                if table['end_row'] < sheet.max_row:
                    if self._row_has_content(sheet, table['end_row'] + 1):
                        total_rows_to_insert += 1

        # If no rows need insertion, just copy file to preserve everything including drawings
        if total_rows_to_insert == 0:
            logger.info(
                "No separator rows needed, copying file as-is to preserve "
                "all content including drawings/shape annotations"
            )
            source_wb.close()
            shutil.copy2(file_path, output_path)
            return {
                'tables_processed': 0,
                'rows_inserted_above': 0,
                'rows_inserted_below': 0,
                'details': []
            }

        # If rows need insertion, we must process with openpyxl
        # WARNING: This will strip drawings/shapes
        logger.warning(
            f"Need to insert {total_rows_to_insert} separator rows, but this would "
            "strip drawings/shape annotations. Copying file as-is to preserve annotations."
        )
        source_wb.close()
        shutil.copy2(file_path, output_path)
        return {
            'tables_processed': 0,
            'rows_inserted_above': 0,
            'rows_inserted_below': 0,
            'details': [],
            'skipped_to_preserve_drawings': True
        }

        # Original code below is now unreachable (keeping for reference if needed later)
        # TODO: Implement ZIP-level manipulation to preserve drawings while inserting rows
        # Create new workbook
        dest_wb = Workbook()
        dest_wb.remove(dest_wb.active)  # Remove default sheet

        stats = {
            'tables_processed': 0,
            'rows_inserted_above': 0,
            'rows_inserted_below': 0,
            'details': []
        }

        for source_sheet in source_wb.worksheets:
            logger.info(f"Processing sheet: {source_sheet.title}")

            # Detect tables in this sheet
            tables = self._detect_tables_in_sheet(source_sheet)
            
            # Calculate which rows need blank rows inserted
            rows_to_insert_after = set()  # Row numbers after which to insert blank row
            
            for table in tables:
                table_info = {
                    'sheet': source_sheet.title,
                    'range': table['range'],
                    'inserted_above': False,
                    'inserted_below': False
                }
                
                # Check row above table
                if table['start_row'] > 1:
                    if self._row_has_content(source_sheet, table['start_row'] - 1):
                        logger.info(
                            f"Row {table['start_row'] - 1} has content above table "
                            f"{table['range']}. Will insert separator row."
                        )
                        rows_to_insert_after.add(table['start_row'] - 1)
                        stats['rows_inserted_above'] += 1
                        table_info['inserted_above'] = True
                
                # Check row below table
                if table['end_row'] < source_sheet.max_row:
                    if self._row_has_content(source_sheet, table['end_row'] + 1):
                        logger.info(
                            f"Row {table['end_row'] + 1} has content below table "
                            f"{table['range']}. Will insert separator row."
                        )
                        rows_to_insert_after.add(table['end_row'])
                        stats['rows_inserted_below'] += 1
                        table_info['inserted_below'] = True
                
                if table_info['inserted_above'] or table_info['inserted_below']:
                    stats['details'].append(table_info)
                    stats['tables_processed'] += 1
            
            # Create destination sheet
            dest_sheet = dest_wb.create_sheet(title=source_sheet.title)
            
            # Copy sheet properties
            dest_sheet.sheet_properties = copy(source_sheet.sheet_properties)
            dest_sheet.sheet_format = copy(source_sheet.sheet_format)
            
            # Copy column dimensions
            for col_letter, col_dim in source_sheet.column_dimensions.items():
                dest_sheet.column_dimensions[col_letter].width = col_dim.width
                dest_sheet.column_dimensions[col_letter].hidden = col_dim.hidden
            
            # Copy rows with insertions
            dest_row = 1
            for source_row in range(1, source_sheet.max_row + 1):
                # Copy row dimension (height, hidden, etc)
                if source_row in source_sheet.row_dimensions:
                    dest_sheet.row_dimensions[dest_row].height = source_sheet.row_dimensions[source_row].height
                    dest_sheet.row_dimensions[dest_row].hidden = source_sheet.row_dimensions[source_row].hidden
                
                # Copy all cells in this row
                for col in range(1, source_sheet.max_column + 1):
                    source_cell = source_sheet.cell(row=source_row, column=col)
                    dest_cell = dest_sheet.cell(row=dest_row, column=col)
                    
                    # Copy cell value
                    dest_cell.value = source_cell.value
                    
                    # Copy cell style
                    if source_cell.has_style:
                        dest_cell.font = copy(source_cell.font)
                        dest_cell.border = copy(source_cell.border)
                        dest_cell.fill = copy(source_cell.fill)
                        dest_cell.number_format = copy(source_cell.number_format)
                        dest_cell.protection = copy(source_cell.protection)
                        dest_cell.alignment = copy(source_cell.alignment)
                
                dest_row += 1
                
                # Insert blank row if needed after this row
                if source_row in rows_to_insert_after:
                    logger.debug(f"Inserting blank row after source row {source_row} (dest row {dest_row})")
                    # Clear any borders/formatting from the blank row
                    from openpyxl.styles import Border, Side
                    no_border = Border()
                    for col in range(1, source_sheet.max_column + 1):
                        blank_cell = dest_sheet.cell(row=dest_row, column=col)
                        blank_cell.border = no_border
                        blank_cell.value = None
                    # dest_row automatically increments, leaving a blank row
                    dest_row += 1
            
            # Copy merged cells with adjusted ranges
            row_offset = 0
            for merged_range in source_sheet.merged_cells.ranges:
                min_row = merged_range.min_row
                max_row = merged_range.max_row
                
                # Calculate how many rows were inserted before this merged range
                rows_inserted_before = len([r for r in rows_to_insert_after if r < min_row])
                
                # Adjust the merged range
                new_min_row = min_row + rows_inserted_before
                new_max_row = max_row + rows_inserted_before
                
                dest_sheet.merge_cells(
                    start_row=new_min_row,
                    start_column=merged_range.min_col,
                    end_row=new_max_row,
                    end_column=merged_range.max_col
                )
        
        # Save the new workbook
        dest_wb.save(output_path)
        source_wb.close()
        
        logger.info(
            f"Saved modified workbook to {output_path}. "
            f"Inserted {stats['rows_inserted_above']} rows above tables, "
            f"{stats['rows_inserted_below']} rows below tables."
        )
        
        return stats
    
    def _row_has_content(self, sheet: Worksheet, row_num: int) -> bool:
        """
        Check if a row has any non-empty cells.
        
        Args:
            sheet: Worksheet object
            row_num: Row number to check (1-indexed)
            
        Returns:
            True if row has any content, False otherwise
        """
        for cell in sheet[row_num]:
            if cell.value is not None and str(cell.value).strip():
                logger.debug(
                    f"Row {row_num} has content at {cell.coordinate}: {cell.value}"
                )
                return True
        return False
    
    def _detect_tables_in_sheet(self, sheet: Worksheet) -> List[dict]:
        """
        Detect tables in a single sheet by analyzing borders.

        Strategy:
        1. Find all cells that have borders on all 4 sides (or most sides)
        2. Group adjacent bordered cells into contiguous regions
        3. Merge regions that are separated by content rows (gap handling)
        4. Compute bounding box for each region
        5. Filter out small regions (likely not tables)

        Args:
            sheet: Worksheet object

        Returns:
            List of table dictionaries
        """
        # Find all bordered cells
        bordered_cells = self._find_bordered_cells(sheet)

        if not bordered_cells:
            logger.info(f"No bordered cells found in {sheet.title}")
            return []

        logger.info(f"Found {len(bordered_cells)} bordered cells in {sheet.title}")

        # Group into contiguous regions
        regions = self._group_into_regions(bordered_cells)

        logger.info(f"Grouped into {len(regions)} regions")

        # Merge regions separated by content rows (fix for gap bug)
        regions = self._merge_adjacent_regions(regions, sheet)

        logger.info(f"After merging adjacent regions: {len(regions)} regions")

        # Convert regions to table boundaries
        tables = []
        for idx, region in enumerate(regions, start=1):
            if len(region) < self.min_table_size:
                logger.debug(f"Skipping region {idx} (too small: {len(region)} cells)")
                continue

            # Get bounding box of bordered cells
            boundary = self._compute_bounding_box(region)

            # Expand to find actual table boundaries (including surrounding content)
            expanded_boundary = self._expand_to_table_boundary(
                sheet,
                boundary['start_row'],
                boundary['end_row'],
                boundary['start_col'],
                boundary['end_col']
            )

            # Create table info
            table = {
                'sheet': sheet.title,
                'range': f"{expanded_boundary['start_col']}{expanded_boundary['start_row']}:"
                         f"{expanded_boundary['end_col']}{expanded_boundary['end_row']}",
                'start_row': expanded_boundary['start_row'],
                'end_row': expanded_boundary['end_row'],
                'start_col': expanded_boundary['start_col'],
                'end_col': expanded_boundary['end_col'],
                'cell_count': len(region),
                'region_id': idx
            }
            tables.append(table)

        return tables
    
    def _find_bordered_cells(self, sheet: Worksheet) -> Set[Tuple[int, int]]:
        """
        Find all cells that have significant borders.

        A cell is considered "bordered" if it has borders on at least 3 sides,
        or if it has top and bottom borders (which indicates it's part of a table).

        Args:
            sheet: Worksheet object

        Returns:
            Set of (row, col) tuples representing bordered cells
        """
        bordered_cells = set()

        # Skip border detection for sheets with inflated max_row (performance)
        if sheet.max_row and sheet.max_row > 5000:
            logger.warning(
                f"Sheet {sheet.title} has inflated max_row={sheet.max_row}, "
                f"skipping border detection for performance. "
                f"Tables will be detected from HTML structure instead."
            )
            return bordered_cells

        if sheet.max_row == 0 or sheet.max_row is None:
            logger.debug(f"Sheet {sheet.title} is empty, skipping border detection")
            return bordered_cells

        # Iterate through all cells in the sheet range
        for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row,
                                   min_col=1, max_col=sheet.max_column):
            for cell in row:
                border_info = self._has_significant_border(cell)
                if border_info['has_border']:
                    bordered_cells.add((cell.row, cell.column))

        logger.info(f"Total bordered cells found: {len(bordered_cells)}")
        return bordered_cells

    def _find_actual_max_row(self, sheet: Worksheet) -> int:
        """
        Find the actual last row with content (not inflated max_row).

        Args:
            sheet: Worksheet object

        Returns:
            Actual last row number with content, or 0 if sheet is empty
        """
        # Use openpyxl's calculated dimensions first (more efficient)
        if hasattr(sheet, 'calculate_dimension'):
            try:
                dim = sheet.calculate_dimension()
                # Parse dimension like "A1:Z100" to get end row
                if ':' in dim:
                    end_cell = dim.split(':')[1]
                    # Extract row number from coordinate like "Z100"
                    import re
                    match = re.search(r'\d+', end_cell)
                    if match:
                        calculated_row = int(match.group())
                        # Cap at reasonable limit
                        return min(calculated_row, 10000)
            except:
                pass

        # Fallback: Use max_row but cap at 1000 rows for performance
        return min(sheet.max_row or 0, 1000)
    
    def _has_significant_border(self, cell) -> dict:
        """
        Check if a cell has significant borders.
        
        Args:
            cell: Cell object
            
        Returns:
            Dict with 'has_border' (bool) and 'sides' (list of border sides)
        """
        if not hasattr(cell, 'border') or cell.border is None:
            return {'has_border': False, 'sides': []}
        
        border = cell.border
        
        # Count which sides have borders
        sides = {
            'top': border.top.style if border.top else None,
            'bottom': border.bottom.style if border.bottom else None,
            'left': border.left.style if border.left else None,
            'right': border.right.style if border.right else None
        }
        
        # Get list of sides with borders
        bordered_sides = [side for side, style in sides.items() if style is not None]
        
        # Count non-None borders
        border_count = len(bordered_sides)
        
        # Consider it bordered if it has at least 2 borders
        # (This catches table cells which typically have top/bottom at minimum)
        return {
            'has_border': border_count >= 2,
            'sides': bordered_sides
        }
    
    def _group_into_regions(self, cells: Set[Tuple[int, int]]) -> List[Set[Tuple[int, int]]]:
        """
        Group adjacent bordered cells into contiguous regions using flood fill.
        
        Args:
            cells: Set of (row, col) tuples
            
        Returns:
            List of sets, each set is a contiguous region
        """
        remaining = cells.copy()
        regions = []
        
        while remaining:
            # Pick any cell to start a new region
            seed = remaining.pop()
            region = self._flood_fill(seed, remaining)
            regions.append(region)
            logger.debug(
                f"Region {len(regions)}: {len(region)} cells, "
                f"seed: ({seed[0]}, {seed[1]})"
            )
        
        logger.info(f"Total regions created: {len(regions)}")
        return regions
    
    def _flood_fill(self, seed: Tuple[int, int], 
                    available: Set[Tuple[int, int]]) -> Set[Tuple[int, int]]:
        """
        Flood fill from a seed cell to find all connected cells.
        
        Cells are considered connected if they are adjacent (including diagonally).
        
        Args:
            seed: Starting (row, col) tuple
            available: Set of available cells to explore (modified in-place)
            
        Returns:
            Set of cells in this region
        """
        region = {seed}
        queue = [seed]
        
        while queue:
            row, col = queue.pop(0)
            
            # Check all 8 neighbors (including diagonal)
            for dr in [-1, 0, 1]:
                for dc in [-1, 0, 1]:
                    if dr == 0 and dc == 0:
                        continue
                    
                    neighbor = (row + dr, col + dc)
                    
                    if neighbor in available:
                        available.remove(neighbor)
                        region.add(neighbor)
                        queue.append(neighbor)
        
        return region
    
    def _compute_bounding_box(self, region: Set[Tuple[int, int]]) -> dict:
        """
        Compute the bounding box of a region.
        
        Args:
            region: Set of (row, col) tuples
            
        Returns:
            Dictionary with start_row, end_row, start_col, end_col
        """
        rows = [cell[0] for cell in region]
        cols = [cell[1] for cell in region]
        
        min_row = min(rows)
        max_row = max(rows)
        min_col = min(cols)
        max_col = max(cols)
        
        result = {
            'start_row': min_row,
            'end_row': max_row,
            'start_col': get_column_letter(min_col),
            'end_col': get_column_letter(max_col)
        }
        
        logger.debug(
            f"Bounding box: {result['start_col']}{result['start_row']}:"
            f"{result['end_col']}{result['end_row']} "
            f"({len(region)} cells in region, "
            f"{(max_row - min_row + 1) * (max_col - min_col + 1)} cells in box)"
        )

        return result

    def _expand_to_table_boundary(self, sheet: Worksheet, start_row: int, end_row: int,
                                   start_col: str, end_col: str) -> dict:
        """
        Expand the bounding box to include surrounding table content.

        Finds the actual table boundary by looking for empty rows/columns around
        the bordered region.

        Strategy:
        - Search upward from start_row until finding empty row (or min row)
        - Search downward from end_row until finding empty row (or max row)
        - Search leftward from start_col until finding empty column (or min col)
        - Search rightward from end_col until finding empty column (or max col)

        Args:
            sheet: Worksheet object
            start_row: Starting row of bordered region
            end_row: Ending row of bordered region
            start_col: Starting column letter of bordered region
            end_col: Ending column letter of bordered region

        Returns:
            Dictionary with expanded start_row, end_row, start_col, end_col
        """
        from openpyxl.utils import column_index_from_string

        start_col_idx = column_index_from_string(start_col)
        end_col_idx = column_index_from_string(end_col)

        # Expand upward: find first empty row above
        expanded_start_row = start_row
        for row in range(start_row - 1, 0, -1):
            if self._is_row_empty(sheet, row, start_col_idx, end_col_idx):
                break
            expanded_start_row = row

        # Expand downward: find first empty row below
        expanded_end_row = end_row
        max_row = sheet.max_row if sheet.max_row else end_row + 100
        for row in range(end_row + 1, min(max_row + 1, end_row + 100)):
            if self._is_row_empty(sheet, row, start_col_idx, end_col_idx):
                break
            expanded_end_row = row

        # Expand leftward: find first empty column to the left
        expanded_start_col_idx = start_col_idx
        for col in range(start_col_idx - 1, 0, -1):
            if self._is_column_empty(sheet, col, expanded_start_row, expanded_end_row):
                break
            expanded_start_col_idx = col

        # Expand rightward: find first empty column to the right
        expanded_end_col_idx = end_col_idx
        max_col = sheet.max_column if sheet.max_column else end_col_idx + 50
        for col in range(end_col_idx + 1, min(max_col + 1, end_col_idx + 50)):
            if self._is_column_empty(sheet, col, expanded_start_row, expanded_end_row):
                break
            expanded_end_col_idx = col

        result = {
            'start_row': expanded_start_row,
            'end_row': expanded_end_row,
            'start_col': get_column_letter(expanded_start_col_idx),
            'end_col': get_column_letter(expanded_end_col_idx)
        }

        logger.debug(
            f"Expanded boundary: {result['start_col']}{result['start_row']}:"
            f"{result['end_col']}{result['end_row']} "
            f"(from {start_col}{start_row}:{end_col}{end_row})"
        )

        return result

    def _is_row_empty(self, sheet: Worksheet, row: int, start_col: int, end_col: int) -> bool:
        """Check if a row is empty within the column range."""
        for col in range(start_col, end_col + 1):
            cell = sheet.cell(row=row, column=col)
            if cell.value is not None and str(cell.value).strip():
                return False
        return True

    def _is_column_empty(self, sheet: Worksheet, col: int, start_row: int, end_row: int) -> bool:
        """Check if a column is empty within the row range."""
        for row in range(start_row, end_row + 1):
            cell = sheet.cell(row=row, column=col)
            if cell.value is not None and str(cell.value).strip():
                return False
        return True

    def _merge_adjacent_regions(self, regions: List[Set[Tuple[int, int]]],
                                 sheet: Worksheet) -> List[Set[Tuple[int, int]]]:
        """
        Merge regions that are likely part of the same table but separated by
        rows without borders (gap rows).

        Strategy:
        - If two regions have overlapping column ranges
        - AND they are separated by only a small gap (1-3 rows)
        - AND the gap rows have content
        - THEN merge them into one region

        This fixes the bug where tables with inconsistent borders get split
        into multiple regions, causing separators to be inserted mid-table.

        Args:
            regions: List of cell regions (each region is a set of (row, col) tuples)
            sheet: Worksheet object for checking row content

        Returns:
            List of merged regions
        """
        if len(regions) <= 1:
            return regions

        # Sort regions by their minimum row number
        sorted_regions = sorted(regions, key=lambda r: min(cell[0] for cell in r))

        merged = []
        current_region = sorted_regions[0]

        for i in range(1, len(sorted_regions)):
            next_region = sorted_regions[i]

            # Check if these regions should be merged
            if self._should_merge_regions(current_region, next_region, sheet):
                # Merge next_region into current_region
                logger.info(
                    f"Merging adjacent regions: "
                    f"region 1 rows {min(cell[0] for cell in current_region)}-{max(cell[0] for cell in current_region)}, "
                    f"region 2 rows {min(cell[0] for cell in next_region)}-{max(cell[0] for cell in next_region)}"
                )
                current_region = current_region.union(next_region)
            else:
                # Cannot merge, save current and start new
                merged.append(current_region)
                current_region = next_region

        # Add the last region
        merged.append(current_region)

        return merged

    def _should_merge_regions(self, region1: Set[Tuple[int, int]],
                              region2: Set[Tuple[int, int]],
                              sheet: Worksheet) -> bool:
        """
        Determine if two regions should be merged into one table.

        Args:
            region1: First region (set of (row, col) tuples)
            region2: Second region (set of (row, col) tuples)
            sheet: Worksheet object

        Returns:
            True if regions should be merged, False otherwise
        """
        # Get bounding boxes
        rows1 = [cell[0] for cell in region1]
        cols1 = [cell[1] for cell in region1]
        rows2 = [cell[0] for cell in region2]
        cols2 = [cell[1] for cell in region2]

        min_row1, max_row1 = min(rows1), max(rows1)
        min_col1, max_col1 = min(cols1), max(cols1)
        min_row2, max_row2 = min(rows2), max(rows2)
        min_col2, max_col2 = min(cols2), max(cols2)

        # Check 1: Column ranges must overlap
        col_overlap = not (max_col1 < min_col2 or max_col2 < min_col1)

        if not col_overlap:
            logger.debug(
                f"Regions do not overlap in columns: "
                f"region1 cols {min_col1}-{max_col1}, region2 cols {min_col2}-{max_col2}"
            )
            return False

        # Check 2: Rows must be adjacent or have a small gap
        # Gap is the number of rows between max_row1 and min_row2
        gap = min_row2 - max_row1 - 1

        if gap < 0:
            # Regions overlap in rows - should not happen after sorting, but handle it
            logger.debug(f"Regions overlap in rows")
            return False

        if gap > 3:
            # Gap is too large - likely separate tables
            logger.debug(
                f"Gap too large between regions: {gap} rows "
                f"(region1 ends at row {max_row1}, region2 starts at row {min_row2})"
            )
            return False

        # Check 3: Gap rows must have content
        # If gap rows are empty, these are likely separate tables
        if gap > 0:
            gap_has_content = False
            for row_num in range(max_row1 + 1, min_row2):
                if self._row_has_content(sheet, row_num):
                    gap_has_content = True
                    logger.debug(f"Gap row {row_num} has content")
                    break

            if not gap_has_content:
                logger.debug(
                    f"Gap rows {max_row1 + 1}-{min_row2 - 1} are empty - "
                    f"regions likely separate tables"
                )
                return False

        # All checks passed - merge these regions
        logger.debug(
            f"Regions should be merged: "
            f"cols overlap, gap={gap} rows, gap has content"
        )
        return True


def main():
    """Example usage of BorderTableDetector."""
    import sys
    
    if len(sys.argv) < 2:
        print("Usage: python detect_border.py <excel_file_path> [OPTIONS]")
        print("\nOptions:")
        print("  --debug-range SHEET ROW_START ROW_END COL_START COL_END")
        print("      Debug mode: show border details for specific range")
        print("  --insert-separators [OUTPUT_FILE]")
        print("      Insert blank rows to separate tables from surrounding content")
        print("\nExamples:")
        print('  python detect_border.py "file.xlsm"')
        print('  python detect_border.py "file.xlsm" --debug-range "Sheet1" 64 79 C Z')
        print('  python detect_border.py "file.xlsm" --insert-separators')
        print('  python detect_border.py "file.xlsm" --insert-separators "output.xlsm"')
        return
    
    file_path = sys.argv[1]
    
    if not Path(file_path).exists():
        print(f"Error: File not found: {file_path}")
        return
    
    # Check for insert-separators mode
    if len(sys.argv) >= 3 and sys.argv[2] == '--insert-separators':
        output_path = sys.argv[3] if len(sys.argv) >= 4 else None
        
        print(f"\n{'='*60}")
        print(f"INSERTING SEPARATOR ROWS")
        print(f"Input:  {file_path}")
        print(f"Output: {output_path or file_path + ' (overwrite)'}")
        print(f"{'='*60}\n")
        
        detector = BorderTableDetector()
        stats = detector.insert_separator_rows(file_path, output_path)
        
        print(f"\n{'='*60}")
        print(f"SUMMARY")
        print(f"{'='*60}")
        print(f"Tables processed:      {stats['tables_processed']}")
        print(f"Rows inserted above:   {stats['rows_inserted_above']}")
        print(f"Rows inserted below:   {stats['rows_inserted_below']}")
        print(f"Total rows inserted:   {stats['rows_inserted_above'] + stats['rows_inserted_below']}")
        print(f"\nDetails:")
        for detail in stats['details']:
            if detail['inserted_above'] or detail['inserted_below']:
                actions = []
                if detail['inserted_above']:
                    actions.append("above")
                if detail['inserted_below']:
                    actions.append("below")
                print(f"  {detail['sheet']}: {detail['range']} - inserted {', '.join(actions)}")
        return
    
    # Check if debug mode is requested
    if len(sys.argv) >= 7 and sys.argv[2] == '--debug-range':
        debug_sheet = sys.argv[3]
        debug_row_start = int(sys.argv[4])
        debug_row_end = int(sys.argv[5])
        debug_col_start = sys.argv[6]
        debug_col_end = sys.argv[7]
        
        print(f"\n{'='*60}")
        print(f"DEBUG MODE: Analyzing specific range")
        print(f"Sheet: {debug_sheet}")
        print(f"Range: {debug_col_start}{debug_row_start}:{debug_col_end}{debug_row_end}")
        print(f"{'='*60}\n")
        
        from openpyxl import load_workbook
        from openpyxl.utils import column_index_from_string
        
        workbook = load_excel_file(file_path, data_only=False)
        sheet = workbook[debug_sheet]
        
        col_start_idx = column_index_from_string(debug_col_start)
        col_end_idx = column_index_from_string(debug_col_end)
        
        print(f"Checking borders for cells in range...\n")
        
        detector = BorderTableDetector()
        bordered_count = 0
        
        for row in range(debug_row_start, debug_row_end + 1):
            for col in range(col_start_idx, col_end_idx + 1):
                cell = sheet.cell(row=row, column=col)
                border_info = detector._has_significant_border(cell)
                
                if border_info['has_border']:
                    bordered_count += 1
                    print(f"  ✓ {cell.coordinate}: {border_info['sides']}")
                else:
                    print(f"  ✗ {cell.coordinate}: No significant border")
        
        print(f"\nTotal cells with borders: {bordered_count}/{(debug_row_end - debug_row_start + 1) * (col_end_idx - col_start_idx + 1)}")
        return
    
    print(f"Analyzing: {file_path}\n")
    
    detector = BorderTableDetector()
    tables = detector.detect_tables_by_border(file_path)
    
    print(f"\n{'='*60}")
    print(f"Found {len(tables)} table(s)")
    print(f"{'='*60}\n")
    
    for idx, table in enumerate(tables, start=1):
        print(f"Table {idx}:")
        print(f"  Sheet:      {table['sheet']}")
        print(f"  Range:      {table['range']}")
        print(f"  Rows:       {table['start_row']} → {table['end_row']}")
        print(f"  Columns:    {table['start_col']} → {table['end_col']}")
        print(f"  Cell count: {table['cell_count']}")
        print()


if __name__ == "__main__":
    main()
