# Architecture Improvements Design

**Last Updated:** 2025-11-20

## Overview

This document outlines the enhanced architecture for the Excel template generator, including schema enhancements, Python integration design, and advanced features support.

## Schema Enhancements

### 1. Row/Column Grouping

**New Schema Properties:**

```json
{
  "grouping": {
    "rows": [
      {
        "start_row": 2,
        "end_row": 10,
        "outline_level": 1,
        "summary_row": "above",
        "collapsed": false
      }
    ],
    "columns": [
      {
        "start_column": "B",
        "end_column": "D",
        "outline_level": 1,
        "summary_column": "left",
        "collapsed": false
      }
    ]
  }
}
```

**Implementation:**

- Add to sheet definition
- Support multiple outline levels (1-8)
- Summary row/column positions
- Collapse/expand state

### 2. Sparklines

**New Schema Properties:**

```json
{
  "sparklines": [
    {
      "type": "line|column|winloss",
      "location": "F2",
      "data_range": "A2:E2",
      "options": {
        "color": "#4D8EF3",
        "negative_color": "#DC2626",
        "markers": true,
        "high_point": true,
        "low_point": true
      }
    }
  ]
}
```

**Implementation:**

- Add to sheet definition
- Support all sparkline types
- Customization options
- Python openpyxl integration

### 3. Enhanced Chart Definitions

**Current Schema Enhancement:**

```json
{
  "charts": [
    {
      "type": "bar|line|pie|scatter|area|column|combo",
      "title": "Chart Title",
      "data_range": "A1:B10",
      "category_range": "A1:A10",
      "position": {
        "cell": "F2",
        "width": 600,
        "height": 400
      },
      "style": {
        "colors": ["#4D8EF3", "#10B981", "#F59E0B"],
        "legend_position": "bottom|top|left|right",
        "show_data_labels": true,
        "secondary_axis": false
      },
      "axes": {
        "x_axis": {
          "title": "X Axis Title",
          "min": null,
          "max": null
        },
        "y_axis": {
          "title": "Y Axis Title",
          "min": null,
          "max": null
        }
      }
    }
  ]
}
```

**Enhancements:**

- Combo charts support
- Secondary axis support
- Enhanced customization
- Python openpyxl integration

### 4. Pivot Table Definitions

**New Schema Properties:**

```json
{
  "pivot_tables": [
    {
      "name": "PivotTable1",
      "source_range": "A1:E100",
      "position": {
        "cell": "G1"
      },
      "rows": [
        {
          "field": "Category",
          "subtotal": "sum"
        }
      ],
      "columns": [
        {
          "field": "Month"
        }
      ],
      "values": [
        {
          "field": "Sales",
          "function": "sum",
          "format": "#,##0.00 €"
        }
      ],
      "filters": [
        {
          "field": "Region"
        }
      ],
      "style": "PivotStyleMedium9"
    }
  ]
}
```

**Implementation:**

- Add to sheet definition
- Full pivot table configuration
- Python openpyxl integration

### 5. Excel Tables

**New Schema Properties:**

```json
{
  "tables": [
    {
      "name": "SalesData",
      "range": "A1:E100",
      "style": "TableStyleMedium2",
      "show_total_row": true,
      "total_row_functions": {
        "Sales": "sum",
        "Quantity": "sum"
      },
      "auto_filter": true,
      "banded_rows": true,
      "banded_columns": false
    }
  ]
}
```

**Implementation:**

- Add to sheet definition
- Table styling options
- Total row configuration
- Python openpyxl integration

### 6. Advanced Conditional Formatting

**Enhanced Schema:**

```json
{
  "conditional_formatting": [
    {
      "range": "B2:B100",
      "type": "colorScale",
      "color_scale": {
        "type": "2color|3color",
        "min_type": "min|num|percent|formula|percentile",
        "min_value": 0,
        "min_color": "#DCFCE7",
        "mid_type": "num|percent|formula|percentile",
        "mid_value": 50,
        "mid_color": "#FEF3C7",
        "max_type": "max|num|percent|formula|percentile",
        "max_value": 100,
        "max_color": "#FEE2E2"
      }
    },
    {
      "range": "C2:C100",
      "type": "dataBar",
      "data_bar": {
        "bar_color": "#4D8EF3",
        "bar_direction": "leftToRight|rightToLeft",
        "show_value": true,
        "min_type": "min|num|percent|formula|percentile",
        "min_value": 0,
        "max_type": "max|num|percent|formula|percentile",
        "max_value": 100
      }
    },
    {
      "range": "D2:D100",
      "type": "iconSet",
      "icon_set": {
        "type": "3Arrows|3ArrowsGray|3Flags|3TrafficLights1|3TrafficLights2|3Signs|3Symbols|3Symbols2|4Arrows|4ArrowsGray|4RedToBlack|4Rating|4TrafficLights|5Arrows|5ArrowsGray|5Rating|5Quarters|3Stars|5Boxes",
        "reverse": false,
        "show_value": true,
        "percent": false,
        "values": [
          {
            "type": "num|percent|formula|percentile",
            "value": 33
          },
          {
            "type": "num|percent|formula|percentile",
            "value": 67
          }
        ]
      }
    }
  ]
}
```

**Enhancements:**

- Full color scale support (2-color, 3-color)
- Data bar customization
- Icon set options
- Python openpyxl integration

### 7. Dashboard Definitions

**New Schema Properties:**

```json
{
  "dashboards": [
    {
      "name": "Overview Dashboard",
      "sheet": "Übersicht",
      "components": [
        {
          "type": "kpi",
          "position": "B2",
          "label": "Gesamtumsatz",
          "formula": "SUMME(Sales!E:E)",
          "format": "#,##0.00 €",
          "style": {
            "background": "#4D8EF3",
            "text_color": "#FFFFFF",
            "font_size": 24
          }
        },
        {
          "type": "chart",
          "chart_id": "sales_chart",
          "position": "B5"
        },
        {
          "type": "table",
          "table_id": "summary_table",
          "position": "F5"
        }
      ],
      "navigation": [
        {
          "type": "hyperlink",
          "text": "Zur Detailansicht",
          "target": "Details!A1"
        }
      ]
    }
  ]
}
```

**Implementation:**

- Dashboard component definitions
- KPI displays
- Navigation elements
- Layout management

## Python Integration Architecture

### 1. Enhancement Script Structure

**File:** `v2/scripts/enhance-template.py`

```python
#!/usr/bin/env python3
"""
Excel Template Enhancement Script

Enhances PhpSpreadsheet-generated templates with advanced features:
- Charts
- Pivot tables
- Sparklines
- Advanced conditional formatting
- Excel Tables
"""

import json
import sys
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.chart import BarChart, LineChart, PieChart, ScatterChart, AreaChart, ColumnChart
from openpyxl.chart.pivot import PivotTable
from openpyxl.formatting.rule import ColorScaleRule, DataBarRule, IconSetRule
from openpyxl.worksheet.table import Table, TableStyleInfo

class TemplateEnhancer:
    def __init__(self, template_path, enhancements_path):
        self.template_path = template_path
        self.enhancements_path = enhancements_path
        self.workbook = None
        self.enhancements = None

    def load_template(self):
        """Load PhpSpreadsheet-generated template"""
        self.workbook = load_workbook(self.template_path)

    def load_enhancements(self):
        """Load enhancement definitions from JSON"""
        with open(self.enhancements_path, 'r', encoding='utf-8') as f:
            self.enhancements = json.load(f)

    def enhance(self):
        """Apply all enhancements"""
        if 'sheets' not in self.enhancements:
            return

        for sheet_def in self.enhancements['sheets']:
            sheet_name = sheet_def['name']
            if sheet_name not in self.workbook.sheetnames:
                continue

            worksheet = self.workbook[sheet_name]

            # Apply enhancements
            if 'charts' in sheet_def:
                self.add_charts(worksheet, sheet_def['charts'])

            if 'pivot_tables' in sheet_def:
                self.add_pivot_tables(worksheet, sheet_def['pivot_tables'])

            if 'sparklines' in sheet_def:
                self.add_sparklines(worksheet, sheet_def['sparklines'])

            if 'advanced_conditional_formatting' in sheet_def:
                self.add_advanced_conditional_formatting(
                    worksheet,
                    sheet_def['advanced_conditional_formatting']
                )

            if 'tables' in sheet_def:
                self.add_tables(worksheet, sheet_def['tables'])

    def add_charts(self, worksheet, charts):
        """Add charts to worksheet"""
        for chart_def in charts:
            chart = self.create_chart(chart_def)
            if chart:
                cell = chart_def['position']['cell']
                worksheet.add_chart(chart, cell)

    def create_chart(self, chart_def):
        """Create chart from definition"""
        chart_type = chart_def['type']

        if chart_type == 'bar':
            chart = BarChart()
        elif chart_type == 'line':
            chart = LineChart()
        elif chart_type == 'pie':
            chart = PieChart()
        elif chart_type == 'scatter':
            chart = ScatterChart()
        elif chart_type == 'area':
            chart = AreaChart()
        elif chart_type == 'column':
            chart = ColumnChart()
        else:
            return None

        # Configure chart
        if 'title' in chart_def:
            chart.title = chart_def['title']

        # Add data
        # ... chart configuration code ...

        return chart

    def add_pivot_tables(self, worksheet, pivot_tables):
        """Add pivot tables to worksheet"""
        # Implementation for pivot tables
        pass

    def add_sparklines(self, worksheet, sparklines):
        """Add sparklines to worksheet"""
        # Implementation for sparklines
        pass

    def add_advanced_conditional_formatting(self, worksheet, formatting):
        """Add advanced conditional formatting"""
        # Implementation for advanced conditional formatting
        pass

    def add_tables(self, worksheet, tables):
        """Add Excel tables to worksheet"""
        # Implementation for tables
        pass

    def save(self, output_path):
        """Save enhanced template"""
        self.workbook.save(output_path)

def main():
    if len(sys.argv) < 3:
        print("Usage: enhance-template.py <template.xlsx> <enhancements.json> [output.xlsx]")
        sys.exit(1)

    template_path = sys.argv[1]
    enhancements_path = sys.argv[2]
    output_path = sys.argv[3] if len(sys.argv) > 3 else template_path.replace('.xlsx', '_enhanced.xlsx')

    enhancer = TemplateEnhancer(template_path, enhancements_path)
    enhancer.load_template()
    enhancer.load_enhancements()
    enhancer.enhance()
    enhancer.save(output_path)

    print(f"Enhanced template saved to: {output_path}")

if __name__ == '__main__':
    main()
```

### 2. Integration Workflow

**PHP Generation:**

```php
// Generate base template
$generator = new OrdioTemplateGenerator($templateDefinition);
$spreadsheet = $generator->generate();

// Save base template
$writer = new Xlsx($spreadsheet);
$basePath = '/tmp/base_template.xlsx';
$writer->save($basePath);

// Extract enhancement definitions
$enhancements = extractEnhancements($templateDefinition);
file_put_contents('/tmp/enhancements.json', json_encode($enhancements, JSON_PRETTY_PRINT));
```

**Python Enhancement:**

```bash
python3 v2/scripts/enhance-template.py \
    /tmp/base_template.xlsx \
    /tmp/enhancements.json \
    /tmp/enhanced_template.xlsx
```

**Final Output:**

- Enhanced template with all advanced features
- Ready for download

### 3. Enhancement Definition Format

**File:** `enhancements.json`

```json
{
  "sheets": [
    {
      "name": "Report",
      "charts": [
        {
          "type": "bar",
          "title": "Monatlicher Umsatz",
          "data_range": "B2:B13",
          "category_range": "A2:A13",
          "position": {
            "cell": "D2",
            "width": 600,
            "height": 400
          },
          "style": {
            "colors": ["#4D8EF3"],
            "legend_position": "bottom"
          }
        }
      ],
      "pivot_tables": [
        {
          "name": "SalesPivot",
          "source_range": "A1:E100",
          "position": { "cell": "G1" },
          "rows": [{ "field": "Category" }],
          "values": [{ "field": "Sales", "function": "sum" }]
        }
      ],
      "sparklines": [
        {
          "type": "line",
          "location": "F2",
          "data_range": "B2:E2"
        }
      ],
      "advanced_conditional_formatting": [
        {
          "range": "B2:B100",
          "type": "colorScale",
          "color_scale": {
            "type": "3color",
            "min_color": "#DCFCE7",
            "mid_color": "#FEF3C7",
            "max_color": "#FEE2E2"
          }
        }
      ],
      "tables": [
        {
          "name": "SalesData",
          "range": "A1:E100",
          "style": "TableStyleMedium2"
        }
      ]
    }
  ]
}
```

## Formula Library Expansion

### 1. Advanced Formula Patterns

**New Categories:**

- Array formulas (legacy CSE)
- Dynamic arrays (Excel 365)
- Lookup formulas (advanced)
- Statistical formulas
- Date/time formulas (German)
- Text manipulation formulas

### 2. Formula Templates

**Reusable Patterns:**

- Error handling templates
- Conditional calculation templates
- Aggregation templates
- Lookup templates
- Date calculation templates

## Implementation Plan

### Phase 1: Schema Enhancement

1. Update `schema.json` with new properties
2. Add grouping definitions
3. Add sparkline definitions
4. Enhance chart definitions
5. Add pivot table definitions
6. Add table definitions
7. Enhance conditional formatting definitions

### Phase 2: Python Integration

1. Create `enhance-template.py` script
2. Implement chart generation
3. Implement pivot table generation
4. Implement sparkline generation
5. Implement advanced conditional formatting
6. Implement table generation
7. Test integration workflow

### Phase 3: PhpSpreadsheet Enhancements

1. Implement row/column grouping
2. Test Excel Tables support
3. Enhance conditional formatting (basic types)
4. Improve performance
5. Add more style presets

### Phase 4: Formula Library

1. Add advanced formula patterns
2. Create formula templates
3. Document formula optimization
4. Add array formula support

### Phase 5: Documentation

1. Update Cursor rules
2. Create best practices guide
3. Document Python integration
4. Create quality checklist
5. Update API documentation

## Next Steps

1. Update schema.json with enhancements
2. Create Python enhancement script
3. Implement PhpSpreadsheet grouping
4. Expand formula library
5. Update documentation