# Story 3.5: Symbol Dictionary Detection

Status: review

## Story

As a **system**,
I want **to automatically detect sheets that define symbol meanings**,
So that **I can resolve symbols before indexing content**.

## Acceptance Criteria

1. **AC3.5.1:** Detect dictionary sheets by name patterns
   - Identify sheets where name contains: "legend", "dictionary", "symbols", "codes", "key", "glossary"
   - Case-insensitive matching
   - Log detected dictionary sheets

2. **AC3.5.2:** Detect dictionary sheets by structural patterns
   - Identify sheets with 2-3 columns matching pattern: Symbol | Meaning | (optional Context)
   - First column: short values (1-10 characters)
   - Second column: longer descriptions (> 10 characters typically)
   - At least 3 rows of data (symbol-meaning pairs)
   - No complex merged cells or multi-level headers

3. **AC3.5.3:** Extract symbol dictionary data
   - For each detected dictionary sheet, extract:
     ```json
     {
       "sheet": "Legend",
       "symbols": [
         {"symbol": "○", "meaning": "Applicable/Yes", "context": null},
         {"symbol": "×", "meaning": "Not applicable/No", "context": null},
         {"symbol": "07", "meaning": "No required fields", "context": "error codes"}
       ]
     }
     ```
   - Handle common symbols: ○, ×, △, ◎, -, *, etc.
   - Handle alphanumeric codes: "07", "A1", "ERR01"
   - Preserve exact symbol strings (case-sensitive)

4. **AC3.5.4:** Store in symbol_dictionaries table
   - Create SymbolDictionary ORM records
   - Fields: document_id, sheet_name, symbol, meaning, context
   - Batch insert for performance
   - Handle duplicates (same symbol in same document) - keep first occurrence

5. **AC3.5.5:** Return detection summary
   - List of detected dictionary sheets
   - Total symbols extracted per sheet
   - Any warnings (e.g., ambiguous sheets, parsing issues)

6. **AC3.5.6:** Integrate with extraction pipeline (optional - for future story)
   - Symbol detection runs as PASS 1 of multi-pass extraction
   - Detected symbols available before content extraction
   - Note: Full pipeline integration deferred to Story 3.6

## Tasks / Subtasks

- [x] **Task 1: Create SymbolDetector class** (AC: 3.5.1, 3.5.2)
  - [x] Create `src/extraction/symbol_detector.py`
  - [x] Implement `SymbolDetector` class
  - [x] Implement `detect_dictionaries(workbook: Workbook) -> list[DetectedDictionary]` method
  - [x] Implement `_is_dictionary_by_name(sheet_name: str) -> bool` helper
  - [x] Implement `_is_dictionary_by_structure(sheet: Worksheet) -> bool` helper

- [x] **Task 2: Implement name-based detection** (AC: 3.5.1)
  - [x] Define dictionary name patterns: ["legend", "dictionary", "symbols", "codes", "key", "glossary"]
  - [x] Case-insensitive substring matching
  - [x] Log sheets detected by name pattern
  - [x] Return sheet reference for extraction

- [x] **Task 3: Implement structure-based detection** (AC: 3.5.2)
  - [x] Analyze first 20 rows of sheet
  - [x] Check column count (2-3 columns)
  - [x] Check first column values: short strings (1-10 chars)
  - [x] Check second column values: longer descriptions
  - [x] Require minimum 3 data rows
  - [x] Skip sheets with merged cells or complex headers
  - [x] Return confidence score for detection

- [x] **Task 4: Implement symbol extraction** (AC: 3.5.3)
  - [x] Create `extract_symbols(sheet: Worksheet) -> list[SymbolEntry]` method
  - [x] Parse each row: column 1 = symbol, column 2 = meaning, column 3 = context (optional)
  - [x] Strip whitespace, handle empty cells
  - [x] Validate symbol length (1-10 chars)
  - [x] Handle Unicode symbols (○, ×, △, etc.)
  - [x] Create SymbolEntry dataclass with symbol, meaning, context

- [x] **Task 5: Create data models** (AC: 3.5.3, 3.5.5)
  - [x] Add `DetectedDictionary` dataclass to models.py
    - Fields: sheet_name, detection_method ("name" | "structure"), confidence, symbols
  - [x] Add `SymbolEntry` dataclass to models.py
    - Fields: symbol, meaning, context (optional)
  - [x] Add `SymbolDetectionSummary` dataclass
    - Fields: dictionaries_found, total_symbols, warnings

- [x] **Task 6: Database storage** (AC: 3.5.4)
  - [x] Verify SymbolDictionary ORM model exists in db/models.py
  - [x] Create `store_symbols(symbols: list[SymbolEntry], document_id: UUID, sheet_name: str, session: Session)` method
  - [x] Use batch insert with session.bulk_insert_mappings()
  - [x] Handle duplicate symbols (same symbol, same document) - skip or update
  - [x] Return count of symbols stored

- [x] **Task 7: Implement detection summary** (AC: 3.5.5)
  - [x] Create `SymbolDetectionSummary.to_dict()` method
  - [x] Include list of detected sheets with counts
  - [x] Include warnings for ambiguous detections
  - [x] Log summary with structlog

- [x] **Task 8: Unit tests** (AC: All)
  - [x] Create `tests/extraction/test_symbol_detector.py`
  - [x] Test name-based detection (positive and negative cases)
  - [x] Test structure-based detection (valid and invalid structures)
  - [x] Test symbol extraction (various symbol types, Unicode)
  - [x] Test edge cases (empty sheets, single row, missing columns)
  - [x] Test duplicate handling

- [x] **Task 9: Integration tests** (AC: All)
  - [x] Create test Excel file with symbol dictionary sheet
  - [x] Test full detection → extraction → storage flow
  - [x] Test with multiple dictionary sheets in one workbook
  - [x] Test with no dictionary sheets (returns empty)

## Dev Notes

### Architecture Patterns and Constraints

From architecture.md:
- **Module location:** `src/extraction/symbol_detector.py` (architecture.md:66)
- **Naming conventions:**
  - Module: `symbol_detector.py` (snake_case)
  - Class: `SymbolDetector` (PascalCase)
  - Functions: `detect_dictionaries()`, `extract_symbols()` (snake_case)
- **Error handling:** Use structured logging for warnings, not exceptions for non-critical issues
- **Logging:** structlog with context (document_id, sheet_name, symbols_found)
- **Type hints:** Full type annotations required
- **Database:** `symbol_dictionaries` table with fields: id, document_id, sheet_name, symbol, meaning, context, created_at

From epics.md Story 3.5:
- **Prerequisites:** Story 3.1 (Table Boundary Detection) - for basic sheet analysis patterns
- **Run BEFORE content extraction:** Symbol detection is PASS 1 of multi-pass extraction
- **Consider LLM for ambiguous cases:** Optional enhancement for sheets that don't clearly match patterns
- **Module:** `src/extraction/symbol_detector.py`

### Learnings from Previous Story

**From Story 3.4a: Extraction Pipeline Orchestrator (Status: review)**

- **Pipeline Architecture**: `ExtractionPipeline` at `src/extraction/pipeline.py` orchestrates all extraction
  - Symbol detection will eventually integrate as PASS 1 before table extraction
  - Current pipeline: TableDetector → HeaderExtractor → MergedCellResolver → RecordBuilder
  - Future: SymbolDetector (PASS 1) → TableDetector → ... → SymbolResolver (PASS 3)

- **Extraction Component Pattern**: All components follow same structure:
  - Class with main public method: `detect_*()`, `extract_*()`, `resolve_*()`
  - Use structlog with context parameters
  - Return dataclass results with to_dict() method
  - Handle errors gracefully, log warnings for non-critical issues

- **Testing Pattern Established:**
  - Unit tests in `tests/extraction/test_{component}.py`
  - Integration tests in `tests/extraction/test_integration.py`
  - Sample Excel files in `tests/fixtures/sample_excel/`
  - 130 tests all passing - maintain this standard

- **Database Pattern:**
  - ORM models in `src/db/models.py`
  - SymbolDictionary model already exists (created in Story 1.2)
  - Use batch insert with `session.bulk_insert_mappings()` for performance

- **Data Models Pattern:**
  - Dataclasses in `src/extraction/models.py`
  - Include `to_dict()` method for JSON serialization
  - Use Optional[] for nullable fields

[Source: docs/sprint-artifacts/3-4a-extraction-pipeline-orchestrator.md#Dev-Agent-Record]

### Project Structure Notes

Expected module structure after Story 3.5:
```
src/extraction/
├── __init__.py
├── models.py              # ADD: DetectedDictionary, SymbolEntry, SymbolDetectionSummary
├── table_detector.py      # ✓ Story 3.1
├── header_extractor.py    # ✓ Story 3.2
├── merged_cell_resolver.py # ✓ Story 3.3
├── record_builder.py      # ✓ Story 3.4
├── pipeline.py            # ✓ Story 3.4a
└── symbol_detector.py     # NEW: Story 3.5 - SymbolDetector class

tests/extraction/
├── test_symbol_detector.py    # NEW: Story 3.5 unit tests
├── test_integration.py        # MODIFY: Add symbol detection tests

tests/fixtures/sample_excel/
├── test_symbol_dictionary.xlsx  # NEW: Test file with dictionary sheet
```

### Database Schema Reference

From Story 1.2 (already implemented):
```sql
CREATE TABLE symbol_dictionaries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
    sheet_name VARCHAR(255),
    symbol VARCHAR(50) NOT NULL,
    meaning TEXT NOT NULL,
    context VARCHAR(255),  -- Where this symbol is used
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_symbols_document ON symbol_dictionaries(document_id);
CREATE INDEX idx_symbols_symbol ON symbol_dictionaries(symbol);
```

### Detection Algorithm

**Name-based detection (high confidence):**
```python
DICTIONARY_NAME_PATTERNS = ["legend", "dictionary", "symbols", "codes", "key", "glossary"]

def _is_dictionary_by_name(sheet_name: str) -> bool:
    name_lower = sheet_name.lower()
    return any(pattern in name_lower for pattern in DICTIONARY_NAME_PATTERNS)
```

**Structure-based detection (medium confidence):**
```python
def _is_dictionary_by_structure(sheet: Worksheet) -> bool:
    # Check column count (2-3)
    if sheet.max_column < 2 or sheet.max_column > 3:
        return False

    # Sample first 20 rows
    short_count = 0
    long_count = 0
    for row in sheet.iter_rows(min_row=1, max_row=20, max_col=2):
        col1_val = str(row[0].value or "").strip()
        col2_val = str(row[1].value or "").strip()

        if 1 <= len(col1_val) <= 10:
            short_count += 1
        if len(col2_val) > 10:
            long_count += 1

    # Heuristic: majority of rows follow pattern
    return short_count >= 3 and long_count >= 3
```

### References

- [Source: docs/epics.md#Story-3.5] - Original story definition with acceptance criteria
- [Source: docs/architecture.md#Project-Structure] - Module location (line 66)
- [Source: docs/architecture.md#PostgreSQL-Schema] - symbol_dictionaries table schema
- [Source: docs/sprint-artifacts/3-4a-extraction-pipeline-orchestrator.md] - Pipeline patterns, component integration

## Dev Agent Record

### Context Reference

<!-- Path(s) to story context XML will be added here by context workflow -->

### Agent Model Used

{{agent_model_name_version}}

### Debug Log References

### Completion Notes List

### File List