# Story 3.3: Merged Cell Resolution

Status: review

## Story

As a **system**,
I want **to propagate merged cell values to all covered cells**,
So that **every row has complete context**.

## Acceptance Criteria

1. **AC3.3.1:** Detect all merged cell ranges in a sheet using openpyxl's `merged_cells` API
   - Access `sheet.merged_cells.ranges` to get all merged ranges
   - Each range provides: min_row, max_row, min_col, max_col, top-left cell coordinate

2. **AC3.3.2:** For each merged range, propagate the top-left cell value to all cells within the range
   - Example: If A2:A4 is merged with value "Group A"
   - Set A2="Group A", A3="Group A", A4="Group A"
   - Value propagates from top-left (master) cell to all covered cells

3. **AC3.3.3:** Resolve merged cells within a specified TableBoundary region
   - Accept TableBoundary as input parameter (from Story 3.1)
   - Only process merged ranges that intersect with the table boundary
   - Skip merged ranges outside the table region

4. **AC3.3.4:** Return resolved cell grid as 2D array with all values populated
   - Input: sheet + TableBoundary
   - Output: 2D list where resolved[row_idx][col_idx] = cell value
   - All merged cells have their values filled in (no gaps/blanks from merged regions)

5. **AC3.3.5:** Track merge metadata for downstream processing
   - Store which cells were propagated from merged ranges
   - Format: `{"_merged_from": "A2:A4"}` annotation
   - Allows Record Builder (Story 3.4) to know which values were inferred

6. **AC3.3.6:** Handle edge cases gracefully
   - Empty merged cells → propagate empty string
   - Merged cells in headers → already handled by HeaderExtractor (Story 3.2)
   - Merged cells spanning entire rows/columns → propagate correctly
   - Overlapping merged ranges → Log warning, use first encountered value

## Tasks / Subtasks

- [x] **Task 1: Create MergedCellResolver class** (AC: 3.3.1, 3.3.3)
  - [x] Create `src/extraction/merged_cell_resolver.py`
  - [x] Implement `MergedCellResolver` class
  - [x] Accept TableBoundary as input parameter
  - [x] Access sheet.merged_cells.ranges for merge detection
  - [x] Filter merged ranges to only those intersecting table boundary

- [x] **Task 2: Implement merge resolution algorithm** (AC: 3.3.2, 3.3.4)
  - [x] Implement `resolve_merged_cells(sheet, table_boundary) -> tuple[list[list[str]], list[MergeMetadata]]` method
  - [x] For each merged range in boundary:
    - [x] Extract top-left (master) cell value
    - [x] Propagate value to all cells in range (min_row:max_row, min_col:max_col)
  - [x] Build 2D array with all values populated (no gaps)
  - [x] Return resolved grid: resolved[row_idx][col_idx]

- [x] **Task 3: Add merge metadata tracking** (AC: 3.3.5)
  - [x] Create MergeMetadata dataclass in models.py
  - [x] Fields: cell_coordinate (str), merged_from_range (str), original_value (str)
  - [x] Track which cells were propagated during resolution
  - [x] Return metadata alongside resolved grid

- [x] **Task 4: Edge case handling** (AC: 3.3.6)
  - [x] Handle empty merged cells → propagate empty string
  - [x] Handle merged ranges outside boundary → skip them
  - [x] Handle overlapping merged ranges → log warning, use first value
  - [x] Handle single-cell "merged" ranges (edge case) → treat as normal cell
  - [x] Add comprehensive error handling with ExtractionError

- [x] **Task 5: Integration with TableBoundary** (AC: 3.3.3)
  - [x] Use boundary.start_row, end_row, start_col, end_col to filter ranges
  - [x] Convert column letters to indices for range checking
  - [x] Add structured logging (sheet_name, table_id, merged_range_count)

- [x] **Task 6: Unit tests** (AC: All)
  - [x] Create `tests/extraction/test_merged_cell_resolver.py`
  - [x] Test basic merged cell propagation (A2:A4 with "Group A")
  - [x] Test merged cells spanning multiple rows and columns
  - [x] Test merged cells at table boundaries (edge cases)
  - [x] Test empty merged cells propagation
  - [x] Test merge metadata tracking
  - [x] Test overlapping merged ranges (warning case)
  - [x] Test integration with TableBoundary filtering

- [x] **Task 7: Integration tests with sample Excel files** (AC: All)
  - [x] Add sample Excel with merged cells in data rows (test_merged_data.xlsx)
  - [x] Add sample Excel with complex merged regions (test_complex_merges.xlsx)
  - [x] Test full pipeline: TableDetector → MergedCellResolver
  - [x] Verify all merged values are propagated correctly

## Dev Notes

### Architecture Patterns and Constraints

From architecture.md:
- **Module location:** `src/extraction/merged_cell_resolver.py` (follows extraction module pattern)
- **Naming conventions:**
  - Module: `merged_cell_resolver.py` (snake_case)
  - Class: `MergedCellResolver` (PascalCase)
  - Functions: `resolve_merged_cells()`, `_get_master_cell_value()` (snake_case)
- **Excel library:** openpyxl 3.1.5 for merged cell detection via `sheet.merged_cells.ranges`
- **Error handling:** Use `ExtractionError(DSOLError)` with code/message/details
- **Logging:** structlog with context (sheet_name, table_id, merged_range_count, duration_ms)
- **Type hints:** Full type annotations required

From epics.md Story 3.3:
- **Prerequisite:** Story 3.1 (Table Boundary Detection) - provides TableBoundary input
- **openpyxl API:** Use `sheet.merged_cells.ranges` to access all merged ranges
- **Propagation pattern:** Top-left cell value propagates to all cells in merged range

### Learnings from Previous Story

**From Story 3.2: Multi-Level Header Extraction (Status: review)**

- **Merged Cell Handling Already Implemented in Headers**: Story 3.2's `HeaderExtractor._resolve_merged_headers()` (src/extraction/header_extractor.py:191-233) handles merged cells in HEADER ROWS specifically. Story 3.3 focuses on merged cells in DATA ROWS.

- **Key Pattern to Reuse**: The approach in Story 3.2 for finding merged cell values:
  ```python
  for merged_range in sheet.merged_cells.ranges:
      if cell.coordinate in merged_range:
          master_cell = sheet.cell(row=merged_range.min_row, column=merged_range.min_col)
          value = master_cell.value
  ```
  This pattern should be adapted for data row processing in Story 3.3.

- **Integration Pattern Established**: Story 3.2 successfully integrated with TableBoundary from Story 3.1:
  - Uses `boundary.start_row`, `end_row`, `start_col`, `end_col` for region filtering
  - Converts column letters to indices using `column_index_from_string()`
  - Pattern: `TableDetector → HeaderExtractor` → now extends to `→ MergedCellResolver`

- **Testing Pattern**: Story 3.2 achieved 100% pass rate with:
  - 21 unit tests (model validation, core functionality, edge cases)
  - 11 integration tests with real Excel files
  - Sample Excel files in `tests/fixtures/sample_excel/`
  - Follow same testing structure for Story 3.3

- **Known Limitation from Story 3.2**: Table detector treats rows with merged cells as title rows and skips them. This ONLY affects merged cells in the first detected row. Data row merged cells (Story 3.3's focus) are NOT affected by this limitation.

- **Existing Models**: `src/extraction/models.py` already has:
  - `TableBoundary` dataclass (Story 3.1)
  - `HeaderInfo` dataclass (Story 3.2)
  - Add `MergeMetadata` dataclass for Story 3.3

- **Files Created in Story 3.2**:
  - src/extraction/header_extractor.py - reference for openpyxl patterns
  - tests/extraction/test_header_extractor.py - reference for test structure
  - tests/extraction/test_integration.py - add Story 3.3 integration tests here

[Source: docs/sprint-artifacts/3-2-multi-level-header-extraction.md#Dev-Agent-Record]

### Project Structure Notes

Expected module structure after Story 3.3:
```
src/extraction/
├── __init__.py
├── models.py              # ✓ Story 3.1, 3.2 | ADD: MergeMetadata
├── table_detector.py      # ✓ Story 3.1
├── header_extractor.py    # ✓ Story 3.2
├── merged_cell_resolver.py # NEW: Story 3.3 - MergedCellResolver class
└── pipeline.py            # FUTURE: Story 3.4a

tests/extraction/
├── __init__.py
├── test_table_detector.py         # ✓ Story 3.1
├── test_header_extractor.py       # ✓ Story 3.2
├── test_merged_cell_resolver.py   # NEW: Story 3.3 unit tests
└── test_integration.py            # MODIFY: Add Story 3.3 integration tests

tests/fixtures/sample_excel/
├── test_standard_table.xlsx       # ✓ Story 3.1
├── test_two_level_headers.xlsx    # ✓ Story 3.2
├── test_three_level_headers.xlsx  # ✓ Story 3.2
├── test_merged_data.xlsx          # NEW: Story 3.3 - merged cells in data rows
└── test_complex_merges.xlsx       # NEW: Story 3.3 - complex merged regions
```

### Prerequisites and Dependencies

**Prerequisites:** Story 3.1 (Table Boundary Detection) - ✓ REVIEW status

**Dependencies:**
- openpyxl 3.1.5: Excel file reading with `sheet.merged_cells.ranges` API
- structlog: Structured logging
- TableBoundary from Story 3.1

**Database:** No database changes required for Story 3.3

**API:** No API changes required for Story 3.3 (internal extraction module)

### Implementation Strategy

**Merged Cell Resolution Flow:**

```python
# 1. Input: Sheet + TableBoundary from Story 3.1
boundary = TableBoundary(sheet="Sheet1", start_row=1, end_row=100, start_col="A", end_col="F")

# 2. Get all merged ranges in sheet
resolver = MergedCellResolver()
merged_ranges = sheet.merged_cells.ranges

# 3. Filter to ranges within boundary
relevant_ranges = []
for merged_range in merged_ranges:
    if _intersects_boundary(merged_range, boundary):
        relevant_ranges.append(merged_range)

# 4. Build resolved 2D grid
resolved_grid = [[None] * num_cols for _ in range(num_rows)]

# 5. Fill grid with cell values
for row_idx in range(num_rows):
    for col_idx in range(num_cols):
        cell = sheet.cell(row=row_idx + boundary.start_row, column=col_idx + start_col_idx)
        resolved_grid[row_idx][col_idx] = cell.value

# 6. Propagate merged cell values
for merged_range in relevant_ranges:
    master_cell = sheet.cell(row=merged_range.min_row, column=merged_range.min_col)
    value = master_cell.value

    for row in range(merged_range.min_row, merged_range.max_row + 1):
        for col in range(merged_range.min_col, merged_range.max_col + 1):
            # Convert to grid coordinates
            grid_row = row - boundary.start_row
            grid_col = col - start_col_idx
            resolved_grid[grid_row][grid_col] = value

            # Track metadata
            metadata[f"{row}:{col}"] = {"merged_from": merged_range.coord}

return resolved_grid, metadata
```

**Difference from Story 3.2:**
- Story 3.2: Merged cells in HEADERS (columns B1:C1 spanning "Sales")
- Story 3.3: Merged cells in DATA ROWS (rows A2:A4 spanning "Group A")

### References

- [Source: docs/epics.md#Story-3.3] - Original story definition with examples
- [Source: docs/architecture.md#Project-Structure] - Module location and naming patterns
- [Source: docs/architecture.md#Implementation-Patterns] - Error handling, logging, type hints
- [Source: docs/sprint-artifacts/3-1-table-boundary-detection.md] - TableBoundary integration
- [Source: docs/sprint-artifacts/3-2-multi-level-header-extraction.md] - Merged cell handling patterns in headers

## Dev Agent Record

### Context Reference

N/A - Story context will be generated by story-context workflow

### Agent Model Used

Claude Sonnet 4.5 (claude-sonnet-4-5-20250929)

### Debug Log References

N/A

### Completion Notes List

**Implementation Summary:**
- Successfully implemented merged cell resolution for data rows
- Created MergeMetadata dataclass with validation
- Implemented merge resolution algorithm with propagation
- All edge cases handled gracefully
- All 7 tasks completed successfully
- **Test Results:** 34/34 tests passed (18 unit + 16 integration)
  - 100% pass rate
  - Comprehensive test coverage for all acceptance criteria

**Key Implementation Details:**

1. **MergeMetadata Model (src/extraction/models.py:108-147):**
   - Dataclass with cell_coordinate, merged_from_range, original_value fields
   - Validation: non-empty coordinate, range must contain colon separator
   - to_dict() method for JSON serialization

2. **MergedCellResolver Class (src/extraction/merged_cell_resolver.py:13-294):**
   - resolve_merged_cells() main public API - returns tuple of (resolved_grid, metadata)
   - _filter_merged_ranges() filters merged ranges to boundary region
   - _build_initial_grid() creates 2D grid with all cell values
   - _propagate_merged_values() propagates master cell values to merged ranges
   - _has_overlapping_values() detects conflicts with warning logs
   - Comprehensive error handling with ExtractionError
   - Structured logging with sheet_name, table_id, merged_range_count

3. **Integration with TableBoundary:**
   - Uses boundary.start_row, end_row, start_col, end_col for region filtering
   - Converts column letters to indices using column_index_from_string()
   - Seamless pipeline: TableDetector → HeaderExtractor → MergedCellResolver

4. **Edge Cases Handled:**
   - Empty merged cells → propagate empty string
   - Merged ranges outside boundary → skipped via intersection check
   - Overlapping merged ranges → log warning, use first value
   - Single-cell "merged" ranges → treated as normal cells
   - Partial intersection with boundary → propagate only cells within boundary

5. **Merged Cell Resolution Flow:**
   - Detect all merged ranges in sheet via sheet.merged_cells.ranges
   - Filter to ranges intersecting table boundary
   - Build initial 2D grid from all cell values
   - For each merged range: propagate top-left (master) cell value to all cells
   - Track metadata for all propagated cells (excludes master cell)
   - Return resolved grid + metadata list

6. **Difference from Story 3.2:**
   - Story 3.2: Merged cells in HEADERS (columns B1:C1 spanning "Sales")
   - Story 3.3: Merged cells in DATA ROWS (rows A2:A4 spanning "Group A")
   - Both use openpyxl's merged_cells.ranges API with similar propagation patterns

**Test Coverage:**
- 18 unit tests covering MergeMetadata model, vertical/horizontal/block merges, boundary filtering, empty cells, edge cases
- 16 integration tests including 5 new tests for merged cell resolution with real Excel files
- 2 sample Excel files created for realistic testing (test_merged_data.xlsx, test_complex_merges.xlsx)

**Known Limitations:**
- Table detector treats ANY row with merged cells as potential title row
- This affects detection but NOT resolution (resolver works correctly when given proper boundary)
- Impact: Minimal for typical Excel files with data row merges (not header merges)

### File List

| Status | File Path |
|--------|-----------|
| ✅ MODIFIED | src/extraction/models.py (added MergeMetadata dataclass) |
| ✅ CREATED | src/extraction/merged_cell_resolver.py (294 lines) |
| ✅ CREATED | tests/extraction/test_merged_cell_resolver.py (469 lines, 18 tests) |
| ✅ MODIFIED | tests/extraction/test_integration.py (added 5 merged cell integration tests) |
| ✅ CREATED | tests/fixtures/sample_excel/test_merged_data.xlsx |
| ✅ CREATED | tests/fixtures/sample_excel/test_complex_merges.xlsx |
| ✅ CREATED | tests/fixtures/sample_excel/create_merged_samples.py |