# Proposal: Flowchart Detection and Natural Language Conversion

## Overview

Implement automatic detection and conversion of flowcharts in Excel files to natural language descriptions, making them searchable and understandable by LLMs.

---

## Problem Statement

Your Excel files contain flowcharts made of drawing shapes (rectangles, ovals, diamonds) connected by arrows/connectors. Currently:
- These flowcharts are not extracted as text
- LLMs cannot understand or answer questions about the workflows
- The visual information is lost during processing

**Example from your file:**
- Shapes: "追従登録画面", "S0工事追従報告", "日付選択画面", etc.
- Connectors: Arrows with labels like "「追加」ボタン", "「日付」アンカー"
- Black dots: Connector anchor points on shapes

---

## Proposed Solution

### Phase 1: Detection - "Does this sheet have a flowchart?"

**Criteria to identify a flowchart:**

1. ✅ **Has drawing shapes** (not just tables/cells)
   - Rectangles, ovals, diamonds, etc.
   - Minimum: 2+ shapes with text content

2. ✅ **Has connectors** (arrows/lines between shapes)
   - Look for connector shapes in Excel drawing layer
   - Check for connection points (black dots you mentioned)
   - Minimum: 1+ connector

3. ✅ **Has anchor points** (connection indicators)
   - Black dots/circles that mark where connectors attach
   - Excel stores these as connection properties

4. ✅ **Shape arrangement** (spatial layout)
   - Shapes are arranged in a flow pattern (not random)
   - Vertical or horizontal alignment suggests workflow

**Detection Logic:**

```python
def has_flowchart(sheet, file_path) -> bool:
    """
    Determine if a sheet contains a flowchart.

    Returns True if ALL conditions met:
    1. Has 2+ shapes with text
    2. Has 1+ connectors (arrows/lines)
    3. Shapes have connection points (anchor dots)
    4. Shapes are connected in a flow pattern
    """

    # Extract shapes and connectors
    shapes = extract_shapes_from_sheet(sheet, file_path)
    connectors = extract_connectors_from_sheet(sheet, file_path)

    # Check minimum requirements
    if len(shapes) < 2:
        return False  # Need at least 2 shapes

    if len(connectors) < 1:
        return False  # Need at least 1 connector

    # Check if shapes have text (not just decorative)
    text_shapes = [s for s in shapes if s.text and s.text.strip()]
    if len(text_shapes) < 2:
        return False  # Need text content

    # Check for connection points (anchor dots)
    has_anchors = any(
        s.has_connection_points() for s in shapes
    )
    if not has_anchors:
        return False  # No anchor points = not a flowchart

    # Check if connectors actually connect shapes
    connected_shapes = get_connected_shapes(shapes, connectors)
    if len(connected_shapes) < 2:
        return False  # Shapes not connected

    return True  # This is a flowchart!
```

---

### Phase 2: Extraction - "What does the flowchart show?"

**Step 1: Extract Shapes**
- Use existing `tmp/excel_shape_extraction_service.py`
- Get shape properties:
  - ID, text content, type (rectangle/oval/diamond)
  - Position (cell range)
  - Connection points (anchors)

**Step 2: Extract Connectors**
- Parse connector shapes (arrows, lines)
- Identify connection endpoints:
  - `start_connection`: Which shape ID + anchor point
  - `end_connection`: Which shape ID + anchor point
- Extract connector labels (text on arrows like "「院内」アンカー")

**Step 3: Build Graph**
```python
# Example graph structure:
{
    "追従登録画面": [
        ("S0工事追従報告", "「追加」ボタン")
    ],
    "S0工事追従報告": [
        ("印刷委員画面", "「院内」アンカー"),
        ("日付選択画面", "「日付」アンカー"),
        ("ユーザID取得画面", "")
    ],
    # ...
}
```

**Step 4: Generate Natural Language**
```
## Workflow: 工事情報流通-S0工事追従報告部分

1. **Start**: 追従登録画面

2. User clicks **「追加」ボタン** → S0工事追従報告 (開始画面)

3. From S0工事追従報告, multiple paths:

   a) If **「院内」アンカー** → 印刷委員画面
      - Then → 印刷委員画面

   b) If **「日付」アンカー** → 日付選択画面
      - User selects date
      - Options: 「今日」, 「1」～「31」, 「開じる」ボタン

   c) If **「処理」ボタン** → ユーザID取得画面

4. Additional elements:
   - ※HK画面 (linked from 追従登録画面 via 「修正」ボタン)
   - 機能図作成済リスト・ボタン
   - ユーザID取得画面
```

---

### Phase 3: Integration - "Where does this fit?"

**Option A: Preprocessing Step (Recommended)**

```python
# In airflow/dags/document_extraction_dag.py:

with DAG(...) as dag:
    validate = ValidateEventOperator(...)
    fetch = FetchDocumentOperator(...)
    branch = detect_format(validate.output)

    # NEW: Conditional flowchart detection
    detect_flowcharts = detect_and_describe_flowcharts_task.override(
        task_id='detect_flowcharts_excel',
    )(fetch_result=fetch.output)

    # Parse task uses flowchart result
    parse_excel = parse_excel_document.override(
        task_id='parse_excel',
    )(fetch_result=detect_flowcharts)

    # Dependencies
    branch >> detect_flowcharts >> parse_excel >> ...
```

**Flow:**
```
Fetch File
    ↓
┌─────────────────┐
│ IF has flowchart│ ← Check: shapes + connectors + anchors
└────┬────────────┘
     │
     ├─ YES → Extract & describe → Add to file → Continue parsing
     │
     └─ NO  → Skip → Continue parsing (unchanged)
```

---

## Implementation Plan

### Files to Create

1. **`src/extraction_v2/flowchart_detector.py`**
   - `FlowchartDetector` class
   - `has_flowchart(sheet, file_path) -> bool`
   - Detection logic (shapes + connectors + anchors)

2. **`src/extraction_v2/flowchart_extractor.py`**
   - `FlowchartExtractor` class
   - Extract shapes, connectors, build graph
   - Generate natural language description

3. **`src/extraction_v2/connector_parser.py`** (NEW)
   - Parse Excel connector objects
   - Extract connection points (anchor dots)
   - Identify start/end shapes
   - Extract connector labels

4. **`airflow/dags/tasks/flowchart_tasks.py`**
   - `detect_and_describe_flowcharts_task`
   - Airflow external_python task
   - Conditional: only process if flowchart detected

5. **`tests/extraction_v2/test_flowchart_*.py`**
   - Unit tests for detection
   - Tests for extraction
   - Integration tests

---

## Key Features

### ✅ Conditional Processing
```python
# Only process files with actual flowcharts
if has_flowchart(sheet, file_path):
    flowchart = extract_flowchart(sheet, file_path)
    description = generate_description(flowchart)
    add_description_to_file(file_path, description)
else:
    # Skip - no flowchart detected
    return original_file_path
```

### ✅ Anchor Point Detection
```python
# Detect black dots (connection points) you mentioned
def has_connection_points(shape) -> bool:
    """Check if shape has anchor dots for connectors."""
    return (
        hasattr(shape, 'start_connection') or
        hasattr(shape, 'end_connection') or
        len(shape.connection_sites) > 0
    )
```

### ✅ Connector Label Extraction
```python
# Extract text from connectors (like "「日付」アンカー")
def extract_connector_label(connector) -> str:
    """Get text label from connector shape."""
    if hasattr(connector, 'text') and connector.text:
        return connector.text.strip()
    return ""
```

### ✅ Multi-language Support
- Your flowchart has Japanese text
- Description will maintain original language
- No translation needed

---

## Expected Output

### Input File
Your Excel file with flowchart (as shown in image)

### Output File
Same Excel file + new column with description:

**Column "BL" (Flowchart Description):**

```markdown
## 工事情報流通-S0工事追従報告部分

### Workflow Steps:

1. **Start Point**: 追従登録画面
   - User can click: 「追加」ボタン or 「修正」ボタン

2. **Main Screen**: S0工事追従報告 (開始画面)
   - Accessed via: 「追加」ボタン from 追従登録画面

3. **Decision Point** - Multiple paths from S0工事追従報告:

   **Path A - Hospital Internal:**
   - Trigger: 「院内」アンカー
   - Destination: 印刷委員画面
   - Next: 印刷委員画面

   **Path B - Date Selection:**
   - Trigger: 「日付」アンカー
   - Destination: 日付選択画面
   - Options: 「戻月」,「次月」,「次月」,「開じる」ボタン

   **Path C - User ID:**
   - Trigger: 「処理」ボタン
   - Destination: ユーザID取得画面

4. **Additional Screens**:
   - ※HK画面 (via 「修正」ボタン)
   - 機能図作成済リスト・ボタン

### Navigation Summary:
- Entry point: 追従登録画面
- Main hub: S0工事追従報告
- Three possible flows: 院内, 日付, 処理
- Multiple end points based on user choice
```

---

## Advantages

1. ✅ **Accurate Detection** - Only processes actual flowcharts
2. ✅ **No False Positives** - Checks for connectors + anchors
3. ✅ **Preserves Original** - Adds description alongside, doesn't remove shapes
4. ✅ **Searchable** - Descriptions are indexed by Milvus
5. ✅ **LLM-friendly** - Natural language format
6. ✅ **Multi-language** - Works with Japanese/English/etc.
7. ✅ **Conditional** - Only adds overhead when flowcharts present

---

## Technical Approach for Connector Detection

### How to detect connectors and anchor points:

Excel stores connectors in the drawing layer with these properties:

```xml
<!-- In xl/drawings/drawing1.xml -->
<xdr:cxnSp>  <!-- Connector shape -->
  <xdr:nvCxnSpPr>
    <xdr:cNvPr id="3" name="Straight Connector 2"/>
  </xdr:nvCxnSpPr>
  <xdr:spPr>
    <a:xfrm>
      <a:off x="3200400" y="1828800"/>
      <a:ext cx="914400" cy="0"/>
    </a:xfrm>
    <a:prstGeom prst="line">
      <a:avLst/>
    </a:prstGeom>
  </xdr:spPr>
  <!-- Connection points (black dots) -->
  <xdr:style>
    <a:lnRef idx="1">
      <a:schemeClr val="accent1"/>
    </a:lnRef>
  </xdr:style>
</xdr:cxnSp>
```

**Detection code:**

```python
def extract_connectors_from_xml(drawing_xml):
    """Parse connector shapes from Excel XML."""
    connectors = []

    # Find all connector shapes
    cxn_shapes = drawing_xml.findall('.//xdr:cxnSp', namespaces=NS)

    for cxn in cxn_shapes:
        # Get connector properties
        connector = {
            'id': cxn.find('.//xdr:cNvPr').get('id'),
            'name': cxn.find('.//xdr:cNvPr').get('name'),
            'type': 'connector',
        }

        # Look for connection endpoints (anchor dots)
        # These link the connector to specific shapes
        start_conn = cxn.find('.//a:stCxn', namespaces=NS)
        end_conn = cxn.find('.//a:endCxn', namespaces=NS)

        if start_conn is not None:
            connector['start_shape_id'] = start_conn.get('id')
            connector['start_anchor'] = start_conn.get('idx')  # Anchor point index

        if end_conn is not None:
            connector['end_shape_id'] = end_conn.get('id')
            connector['end_anchor'] = end_conn.get('idx')

        connectors.append(connector)

    return connectors
```

---

## Questions for You

Before I implement, please confirm:

1. **Detection criteria**: Is my logic correct?
   - ✅ Must have 2+ shapes with text
   - ✅ Must have 1+ connectors
   - ✅ Must have anchor points (black dots)
   - Any other requirements?

2. **Output format**: Is the natural language format good?
   - Step-by-step workflow
   - Decision points with branches
   - Connector labels included
   - Should I change anything?

3. **Placement**: Where to add the description?
   - Option A: Adjacent column (recommended)
   - Option B: New sheet "Flowchart Descriptions"
   - Option C: Replace the flowchart (remove shapes)
   - Your preference?

4. **Integration point**: When should this run?
   - Option A: Before parse_excel (recommended)
   - Option B: After parse_excel
   - Option C: Separate optional task
   - Which do you prefer?

5. **Connector labels**: Should I extract text from:
   - ✅ Arrow shapes with labels (like "「日付」アンカー")
   - ✅ Text boxes near connectors
   - ✅ Both
   - Other sources?

---

## Once Approved, I Will:

1. ✅ Implement connector parser (with anchor detection)
2. ✅ Implement flowchart detector (with your conditions)
3. ✅ Implement flowchart extractor (with graph building)
4. ✅ Create Airflow task (with conditional logic)
5. ✅ Write comprehensive tests
6. ✅ Create integration guide
7. ✅ Test with your sample file
8. ✅ Commit with proper documentation

**Estimated time:** ~2-3 hours of implementation + testing

Please review and let me know:
- ✅ Approve as-is
- 🔧 Changes needed (specify what)
- ❓ Questions/concerns