# Extraction V2 - Docling-based Pipeline

This directory contains the V2 extraction pipeline that uses Docling for Excel-to-HTML conversion with LLM-based header detection.

## Architecture

```
Excel File (.xlsx/.xls/.xlsm)
    ↓
[LibreOffice CLI] ← (if .xls/.xlsm)
    ↓
.xlsx File
    ↓
[Border Detection] ← Insert separator rows between tables
    ↓
Preprocessed .xlsx
    ↓
[Docling Converter]
    ↓
HTML
    ↓
[HTML Parser] ← Extract <table> elements
    ↓
Tables (first 10 rows each)
    ↓
[LLM Header Detector] ← GPT-4o analyzes headers
    ↓
Headers (hierarchical: "Parent -> Child")
    ↓
[Record Builder] ← Map headers to cell values
    ↓
JSON Records
```

**Key Innovation:** Border detection preprocessing ensures Docling can accurately identify individual tables by inserting blank separator rows between tables and surrounding content.

## Modules

### 0. `detect_border.py` (Preprocessing)
Detects table boundaries by analyzing cell borders and inserts separator rows.

**Key Class:** `BorderTableDetector`
**Main Methods:**
- `detect_tables_by_border(file_path)` - Find all tables
- `insert_separator_rows(file_path, output_path)` - Insert blank rows between tables

**Purpose:** Prepares Excel files for accurate table extraction by Docling

### 1. `excel_to_html_converter.py`
Converts Excel files to HTML using the Docling library.

**Processing Pipeline:**
1. Convert .xls/.xlsm → .xlsx (if needed, via LibreOffice)
2. **Preprocess:** Detect borders, insert separator rows
3. Convert preprocessed .xlsx → HTML (via Docling)

**Supported Formats:**
- `.xlsx` - Border detection → Docling
- `.xls` - LibreOffice → Border detection → Docling
- `.xlsm` - LibreOffice → Border detection → Docling

**Key Class:** `ExcelToHtmlConverter`
**Main Method:** `convert_to_html(file_path: str) -> str | None`

**Requirements:**
- LibreOffice must be installed for .xls/.xlsm support
- Install: https://www.libreoffice.org/download/

### 2. `html_table_extractor.py`
Extracts table structures from HTML.

**Key Class:** `HtmlTableExtractor`
**Main Method:** `extract_tables(html: str) -> List[HtmlTable]`

### 3. `llm_header_detector.py`
Uses LLM to detect multi-level headers from first 10 rows.

**Key Class:** `LlmHeaderDetector`
**Main Method:** `detect_headers(first_10_rows: List[List]) -> HeaderStructure`

### 4. `record_builder.py`
Builds structured JSON records from HTML tables with detected headers.

**Key Class:** `RecordBuilder`
**Main Method:** `build_records(table: HtmlTable, headers: HeaderStructure) -> List[Record]`

### 5. `pipeline.py`
Orchestrates the V2 extraction workflow.

**Key Class:** `ExtractionPipelineV2`
**Main Method:** `process_document(document_id: UUID) -> ExtractionResult`

## Dependencies

```toml
docling = "^1.0.0"         # Excel to HTML conversion
beautifulsoup4 = "^4.12.0" # HTML parsing
lxml = "^5.0.0"            # Fast HTML parser backend
```

## Usage

```python
from src.extraction_v2.pipeline import ExtractionPipelineV2

# Initialize pipeline
pipeline = ExtractionPipelineV2()

# Process document
result = await pipeline.process_document(document_id)

# Handle result
if result.success:
    print(f"Extracted {result.record_count} records")
else:
    # Fallback to V1 pipeline
    from src.extraction.pipeline import ExtractionPipeline
    v1_pipeline = ExtractionPipeline()
    result = await v1_pipeline.process_document(document_id)
```

## Fallback Strategy

V2 pipeline attempts Docling conversion first. On failure:
1. Log error with details
2. Return `None` to signal fallback needed
3. Main orchestrator invokes V1 pipeline (openpyxl-based)

## Header Format

Multi-level headers use `->` separator:
- Single level: `"Product Name"`
- Two levels: `"Sales -> Q1"`
- Three levels: `"Inventory -> Current -> Warehouse A"`

## Implementation Status

**Story 3.4b:** Docling-Based Excel to HTML Converter
- [ ] `excel_to_html_converter.py` - Docling wrapper
- [ ] `html_table_extractor.py` - HTML table parsing
- [ ] `llm_header_detector.py` - LLM header detection
- [ ] `record_builder.py` - Record builder
- [ ] `pipeline.py` - V2 orchestrator
- [ ] Integration with main pipeline router
- [ ] Unit tests
- [ ] Integration tests

## Testing

```bash
# Run V2-specific tests
pytest tests/test_extraction_v2/

# Test Docling conversion
pytest tests/test_extraction_v2/test_excel_to_html_converter.py

# Test LLM header detection
pytest tests/test_extraction_v2/test_llm_header_detector.py
```

## Performance Targets

- **Conversion time:** ≤10 seconds per Excel file (< 10MB)
- **Header detection accuracy:** ≥85% on test cases
- **Memory usage:** Stream large files to avoid OOM

## Troubleshooting

### Docling fails to convert
- Check file is valid .xlsx/.xls/.xlsm
- Verify Docling version compatibility
- Check system dependencies (libxml2, etc.)
- Falls back to V1 automatically

### LibreOffice not found (.xls/.xlsm files)
- Install LibreOffice: https://www.libreoffice.org/download/
- **macOS:** Install via Homebrew `brew install --cask libreoffice`
- **Linux:** `sudo apt-get install libreoffice` or `sudo yum install libreoffice`
- **Windows:** Download installer from libreoffice.org
- Falls back to V1 automatically if not installed

### LLM header detection inaccurate
- Review first 10 rows sent to LLM
- Adjust prompt template in `src/llm/prompts.py`
- Increase temperature for more creative detection
- Add examples to few-shot prompt

### Performance issues
- Enable streaming for large files
- Batch process multiple tables in parallel
- Cache LLM responses for identical headers
- Profile with `cProfile` to identify bottlenecks

---

**Implemented as part of Sprint Change Proposal: 2025-11-28**
**Epic 3, Story 3.4b**