# focus_1.xlsx Extraction Pipeline Issues Report

**Date:** 2025-11-28
**File Tested:** `CustomerDocument/IFф╗ХцзШцЫ╕(BB-CASTAR)/focus_1.xlsx`
**Sheets:** 4 sheets (工事結果情報流通, 別紙1_設定条件内容, 別紙2_タグ有無一覧, 別紙3_繰返し回数項目)
**Records Extracted:** 132

---

## Executive Summary

The extraction pipeline successfully processes the file and extracts records, but with **3 critical issues** that affect data quality and usability for Q&A.

| Issue | Severity | Component | Status |
|-------|----------|-----------|--------|
| Header Repetition (same value at every level) | 🔴 Critical | `HeaderExtractor` | Open |
| Missing Column Data (項目名 = None) | 🔴 Critical | `RecordBuilder` | Open |
| Symbols Not Resolved (○, ×, － remain raw) | 🟡 Medium | `SymbolResolver` | In Progress |
| Cross-References Not Detected | 🟡 Medium | `CrossRefDetector` | Open |

---

## Issue 1: Header Repetition (Critical)

### Symptom
Headers are extracted with the same value repeated at every level:
```
'項番 > 項番 > 項番 > 項番'
'階層 > 階層 > 階層 > 階層'  
'項目名 > 項目名 > 項目名 > 項目名'
```

**Expected:**
```
'項番'
'階層'
'項目名'
```

For hierarchical headers:
```
# Actual (wrong)
'流通項目 > 工事結果情報流通\n（ひかり電話工事） > 電文ID：2900 > 番ポ工事結果種別：null'

# Expected (correct multi-level)
'流通項目 > 工事結果情報流通（ひかり電話工事） > 電文ID：2900'
```

### Root Cause
In `HeaderExtractor._build_header_info()`:
1. For vertically merged headers, `_resolve_merged_headers` already propagates the top-left value into every row of the merge
2. The loop appends the same value again at each level → repetition
3. Empty cells cause `last_value` to be appended as a new level instead of being inherited

### Fix Required
In `src/extraction/header_extractor.py`:
```python
def _build_header_info(...):
    # After building raw_levels, compress consecutive duplicates:
    levels = []
    for v in raw_levels:
        if not v:
            continue
        if not levels or levels[-1] != v:
            levels.append(v)
```

---

## Issue 2: Missing Column Data (Critical)

### Symptom
The `項目名` column shows `None` for most records despite having data in the original Excel:
```python
Record 80:
    Content: {'項目名 > 項目名 > 項目名 > 項目名': None, ...}
```

### Root Cause
In `RecordBuilder.build_records()`:
```python
col_letters = [
    get_column_letter(start_col_idx + i) for i in range(len(headers))
]
```
If `_build_header_info` skipped some physical columns (no non-empty headers), the column mapping shifts and values are attributed to wrong headers.

### Fix Required
In `src/extraction/record_builder.py`:
```python
# Derive col_letters from physical table boundary, not len(headers)
start_col_idx = column_index_from_string(table_boundary.start_col)
end_col_idx = column_index_from_string(table_boundary.end_col)
col_letters = [get_column_letter(c) for c in range(start_col_idx, end_col_idx + 1)]

for col_idx, col_letter in enumerate(col_letters):
    if col_letter not in headers:
        continue  # column has no header, skip
    header_name = headers[col_letter].display
    raw_value = row_values[col_idx] if col_idx < len(row_values) else None
```

---

## Issue 3: Symbols Not Resolved (Medium)

### Symptom
Records contain raw symbols but no `resolved_content`:
```python
Content: {'流通項目 > ...': '○', '流通項目 > ...': '－', ...}
# No resolved_content field shown
```

### Expected
```python
Content: {'流通項目 > ...': '○', ...}
Resolved: {'流通項目 > ...': 'タグあり', ...}  # Based on legend row
```

### Evidence of Legend Row
Record 123 contains the symbol legend:
```python
Content: {'項番 > ...': '凡例）○：タグあり、－：タグなし', ...}
```

### Root Cause
1. `SymbolResolver` runs but results are not persisted to `resolved_content`
2. Symbol detection may not be recognizing the in-table legend format

### Fix Required
1. Update `ExtractedRecord` model to include `resolved_content` field
2. In `SymbolResolver`, populate `resolved_content` with resolved values
3. Persist `resolved_content` in `_store_records()`

---

## Issue 4: Cross-References Not Detected (Medium)

### Symptom
```
Total cross-references: 0
```

### Evidence of Cross-References in Data
Multiple records contain explicit references:
```python
Content: {'備考 > ...': '別紙3_繰返し回数項目　参照', ...}
```

### Root Cause
`CrossRefDetector` likely uses English patterns (e.g., "See Appendix X") instead of Japanese patterns.

### Fix Required
Add Japanese cross-reference patterns:
```python
PATTERNS = [
    r"別紙\s*([0-9０-９]+)[\-_＿]?\s*([^\s　、。()（）]*?)\s*(?:を)?参照",
    r"（?別紙\s*([0-9０-９]+)[^)\s]*?）?\s*(?:を)?参照",
]
```

---

## Prioritized Fix Plan

### Priority 1: Header Repetition + Column Alignment (1-2 days)
1. Modify `HeaderExtractor._build_header_info()` to compress consecutive duplicates
2. Fix `RecordBuilder.build_records()` to use physical column range
3. Add tests with `focus_1.xlsx` header patterns

### Priority 2: Symbol Resolution (1 day)
1. Add `resolved_content` field to models
2. Update `SymbolResolver` to populate resolved values
3. Detect in-table legend patterns (凡例）

### Priority 3: Japanese Cross-Reference Patterns (0.5 day)
1. Add Japanese regex patterns to `CrossRefDetector`
2. Map "別紙X" to sheet names
3. Create cross-reference records even if target row is unresolved

---

## Test Validation Criteria

After fixes, the extraction of `focus_1.xlsx` should produce:

| Metric | Current | Expected |
|--------|---------|----------|
| Records with duplicate headers | 100% | 0% |
| Records with missing 項目名 | ~90% | <5% |
| Symbols resolved | 0 | >50 |
| Cross-references detected | 0 | >10 |

---

## Files to Modify

1. `src/extraction/header_extractor.py` - Fix header repetition
2. `src/extraction/record_builder.py` - Fix column alignment
3. `src/extraction/models.py` - Add `resolved_content` field
4. `src/extraction/pipeline.py` - Persist `resolved_content`
5. Symbol/CrossRef detectors - Add Japanese patterns

---

_Report generated by Amp based on oracle analysis of test output._