# Excel Template Generator System Architecture

**Last Updated:** 2025-12-03

## System Architecture Diagram

```mermaid
graph TB
    %% Entry Points
    subgraph "Entry Points"
        API[API Endpoints<br/>generate.php<br/>generate_excel.php<br/>customize.php]
        CLI[CLI Scripts<br/>template-cli.php<br/>generate-template-workflow.php]
        Pages[Template Pages<br/>templates_index.php<br/>templates_dienstplan.php]
    end

    %% Core Engine
    subgraph "Core Engine"
        Generator[OrdioTemplateGenerator<br/>Main Generator Engine]
        Parameterizer[TemplateParameterizer<br/>Parameter Validation]
        ComponentLoader[ComponentLoader<br/>Reusable Components]
        Inheritance[TemplateInheritance<br/>Base Templates]
    end

    %% Supporting Systems
    subgraph "Supporting Systems"
        Branding[Branding System<br/>Validator & Auto-Fixer]
        Formulas[Formula System<br/>Recommender & Optimizer]
        Charts[Chart System<br/>Selector & Placer]
        Cache[Cache System<br/>Performance Optimization]
        Progress[Progress Tracking<br/>Real-time Updates]
    end

    %% Data Layer
    subgraph "Data Layer"
        Definitions[Template Definitions<br/>JSON Schema<br/>30+ Templates]
        Components[Reusable Components<br/>53 Components<br/>Charts, Forms, Tables]
        FormulasData[Formula Libraries<br/>HR, Industry, Advanced<br/>Lookup, Benchmarks]
        Config[Branding Config<br/>Colors, Fonts, Styles<br/>Presets]
        Registry[Template Registry<br/>Metadata & Status]
    end

    %% Analysis & Quality
    subgraph "Analysis & Quality"
        Validator[Template Validator<br/>Schema Validation]
        Quality[Quality Gates<br/>Scoring & Suggestions]
        Analytics[Branding Analytics<br/>Consistency Scoring]
        Audit[Audit System<br/>Comprehensive Analysis]
    end

    %% Output
    subgraph "Output"
        Excel[Excel Files<br/>.xlsx Format<br/>PhpSpreadsheet]
        Reports[Analysis Reports<br/>JSON, CSV<br/>Quality Metrics]
    end

    %% Flow Connections
    API --> Generator
    CLI --> Generator
    Pages --> API

    Generator --> Parameterizer
    Generator --> ComponentLoader
    Generator --> Inheritance
    Generator --> Branding
    Generator --> Formulas
    Generator --> Charts
    Generator --> Cache
    Generator --> Progress

    Parameterizer --> Definitions
    ComponentLoader --> Components
    Inheritance --> Definitions
    Branding --> Config
    Formulas --> FormulasData
    Charts --> FormulasData

    Generator --> Excel
    Generator --> Reports

    Validator --> Definitions
    Quality --> Generator
    Analytics --> Branding
    Audit --> Generator

    Cache --> Excel
    Progress --> API

    %% Styling
    classDef entryPoint fill:#e1f5ff,stroke:#01579b,stroke-width:2px
    classDef core fill:#fff3e0,stroke:#e65100,stroke-width:2px
    classDef support fill:#f3e5f5,stroke:#4a148c,stroke-width:2px
    classDef data fill:#e8f5e9,stroke:#1b5e20,stroke-width:2px
    classDef analysis fill:#fce4ec,stroke:#880e4f,stroke-width:2px
    classDef output fill:#fff9c4,stroke:#f57f17,stroke-width:2px

    class API,CLI,Pages entryPoint
    class Generator,Parameterizer,ComponentLoader,Inheritance core
    class Branding,Formulas,Charts,Cache,Progress support
    class Definitions,Components,FormulasData,Config,Registry data
    class Validator,Quality,Analytics,Audit analysis
    class Excel,Reports output
```

## Data Flow Diagram

```mermaid
flowchart TD
    Start([User Request]) --> Input{Input Type}

    Input -->|API| APIInput[API Endpoint<br/>POST JSON]
    Input -->|CLI| CLIInput[CLI Command<br/>Template File]
    Input -->|Page| PageInput[Web Page<br/>Form Submission]

    APIInput --> Validate[Validate Input<br/>Schema Check]
    CLIInput --> Validate
    PageInput --> Validate

    Validate -->|Valid| LoadDef[Load Template Definition<br/>JSON File]
    Validate -->|Invalid| Error([Error Response])

    LoadDef --> LoadComponents[Load Reusable Components<br/>53 Components Available]
    LoadDef --> LoadFormulas[Load Formula Libraries<br/>HR, Industry, Advanced]
    LoadDef --> LoadBranding[Load Branding Config<br/>Colors, Fonts, Styles]

    LoadComponents --> ApplyInheritance[Apply Template Inheritance<br/>Base Templates]
    LoadFormulas --> ApplyInheritance
    LoadBranding --> ApplyInheritance

    ApplyInheritance --> GenerateSheets[Generate Sheets<br/>Create PhpSpreadsheet]

    GenerateSheets --> ProcessCells[Process Cells<br/>Values, Formulas, Styles]
    ProcessCells --> ApplyValidation[Apply Data Validation<br/>Rules & Constraints]
    ApplyValidation --> ApplyFormatting[Apply Formatting<br/>Branding Presets]
    ApplyFormatting --> AddCharts[Add Charts<br/>If Defined]
    AddCharts --> AddComponents[Add Reusable Components<br/>Forms, Tables, Dashboards]

    AddComponents --> Optimize[Optimize Performance<br/>Batch Operations]
    Optimize --> CacheCheck{Cache Available?}

    CacheCheck -->|Yes| ReturnCache[Return Cached File]
    CacheCheck -->|No| GenerateExcel[Generate Excel File<br/>.xlsx Format]

    GenerateExcel --> StoreCache[Store in Cache]
    StoreCache --> ReturnFile[Return Excel File]
    ReturnCache --> ReturnFile

    ReturnFile --> End([User Receives File])

    %% Error Handling
    GenerateSheets -->|Error| Error
    ProcessCells -->|Error| Error
    ApplyValidation -->|Error| Error
    GenerateExcel -->|Error| Error

    %% Styling
    classDef process fill:#e3f2fd,stroke:#1565c0,stroke-width:2px
    classDef decision fill:#fff3e0,stroke:#e65100,stroke-width:2px
    classDef data fill:#e8f5e9,stroke:#2e7d32,stroke-width:2px
    classDef output fill:#fff9c4,stroke:#f57f17,stroke-width:2px
    classDef error fill:#ffebee,stroke:#c62828,stroke-width:2px

    class Validate,LoadDef,LoadComponents,LoadFormulas,LoadBranding,ApplyInheritance,GenerateSheets,ProcessCells,ApplyValidation,ApplyFormatting,AddCharts,AddComponents,Optimize,StoreCache process
    class Input,CacheCheck decision
    class APIInput,CLIInput,PageInput,ReturnFile data
    class ReturnCache,GenerateExcel,End output
    class Error error
```

## Component Relationships

```mermaid
graph LR
    subgraph "Core Generator"
        TG[TemplateGenerator]
    end

    subgraph "Input Processing"
        TP[Parameterizer]
        TL[ComponentLoader]
        TI[Inheritance]
    end

    subgraph "Content Systems"
        BV[Branding Validator]
        BA[Branding Auto-Fixer]
        FR[Formula Recommender]
        FO[Formula Optimizer]
        CS[Chart Selector]
        CP[Chart Placer]
    end

    subgraph "Performance"
        TC[Template Cache]
        TP2[Progress Tracker]
    end

    TG --> TP
    TG --> TL
    TG --> TI
    TG --> BV
    TG --> BA
    TG --> FR
    TG --> FO
    TG --> CS
    TG --> CP
    TG --> TC
    TG --> TP2

    BV --> BA
    FR --> FO
    CS --> CP

    TC --> TG
    TP2 --> TG

    classDef core fill:#fff3e0,stroke:#e65100,stroke-width:3px
    classDef input fill:#e1f5ff,stroke:#01579b,stroke-width:2px
    classDef content fill:#f3e5f5,stroke:#4a148c,stroke-width:2px
    classDef perf fill:#e8f5e9,stroke:#2e7d32,stroke-width:2px

    class TG core
    class TP,TL,TI input
    class BV,BA,FR,FO,CS,CP content
    class TC,TP2 perf
```

## System Capabilities

### Core Features

- **30+ Template Definitions** - JSON-based template definitions
- **53 Reusable Components** - Charts, forms, tables, dashboards
- **Formula Libraries** - HR, industry-specific, advanced calculations
- **Branding System** - Automatic Ordio branding with validation
- **Template Inheritance** - Base templates for consistency
- **Performance Optimization** - Caching, batch operations, streaming

### Quality Assurance

- **Schema Validation** - JSON schema validation
- **Quality Gates** - Automated quality scoring
- **Branding Analytics** - Consistency tracking
- **Comprehensive Audits** - Template analysis and recommendations

### Analysis Tools

- **Individual Template Analysis** - Deep dive into each template
- **Visualization Opportunities** - Chart recommendations
- **Formula Quality Checks** - Performance and correctness
- **Competitive Analysis** - Benchmarking and gap analysis