Report Generation

## Problem Statement

No way to generate PDF/Excel reports from query results or dashboard analytics. Users can only copy CSV to clipboard. There is no way to create professional reports for sharing with stakeholders.

## Proposed Solution

Implement report generation with PDF export, scheduled digest emails, and customizable report templates.

## Acceptance Criteria

- [ ] PDF export of query results with charts
- [ ] Excel export with multiple sheets
- [ ] Report templates (standard, detailed, executive summary)
- [ ] Scheduled digest emails (daily/weekly)
- [ ] Custom report builder (add/remove sections)
- [ ] Report branding (logo, colors, footer)
- [ ] Report sharing via link
- [ ] Report history and versioning
- [ ] Batch export (multiple queries in one report)

## Technical Approach

### Backend Changes

**1. Report generator service** (`backend/app/services/reports.py`):

```python
from weasyprint import HTML
from openpyxl import Workbook
from jinja2 import Template

class ReportGenerator:
    def __init__(self, db, llm):
        self.db = db
        self.llm = llm
    
    async def generate_pdf(self, report_config: dict) -> bytes:
        """Generate PDF report from config."""
        # Gather data
        sections = []
        for section in report_config.get("sections", []):
            data = await self._get_section_data(section)
            sections.append(data)
        
        # Render HTML template
        html = self._render_template(sections, report_config.get("branding"))
        
        # Convert to PDF
        pdf = HTML(string=html).write_pdf()
        return pdf
    
    async def generate_excel(self, report_config: dict) -> bytes:
        """Generate Excel report with multiple sheets."""
        wb = Workbook()
        
        for i, section in enumerate(report_config.get("sections", [])):
            if i == 0:
                ws = wb.active
                ws.title = section.get("title", "Sheet1")
            else:
                ws = wb.create_sheet(section.get("title", f"Sheet{i+1}"))
            
            data = await self._get_section_data(section)
            self._write_sheet(ws, data)
        
        # Save to bytes
        buffer = BytesIO()
        wb.save(buffer)
        return buffer.getvalue()
    
    async def _get_section_data(self, section: dict) -> dict:
        """Get data for a report section."""
        if section["type"] == "query":
            result = await self._execute_query(section["query"])
            return {
                "title": section["title"],
                "type": "table",
                "columns": result["columns"],
                "rows": result["rows"]
            }
        elif section["type"] == "chart":
            # Generate chart image
            chart_image = await self._generate_chart(section)
            return {
                "title": section["title"],
                "type": "chart",
                "image": chart_image
            }
        elif section["type"] == "text":
            return {
                "title": section["title"],
                "type": "text",
                "content": section["content"]
            }
    
    def _render_template(self, sections: list, branding: dict = None) -> str:
        """Render HTML template for PDF."""
        template = Template(REPORT_TEMPLATE)
        return template.render(
            sections=sections,
            branding=branding or DEFAULT_BRANDING,
            generated_at=datetime.utcnow()
        )
```

**2. Report templates** (`backend/app/services/report_templates.py`):

```python
REPORT_TEMPLATE = """
<!DOCTYPE html>
<html>
<head>
    <style>
        body { font-family: Arial, sans-serif; margin: 40px; }
        .header { border-bottom: 2px solid {{ branding.color }}; padding-bottom: 20px; }
        .section { margin-bottom: 30px; }
        table { width: 100%; border-collapse: collapse; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
        th { background-color: {{ branding.color }}; color: white; }
        .chart { text-align: center; margin: 20px 0; }
        .footer { margin-top: 40px; font-size: 12px; color: #666; }
    </style>
</head>
<body>
    <div class="header">
        <h1>{{ branding.title or 'BoloDB Report' }}</h1>
        <p>Generated: {{ generated_at.strftime('%Y-%m-%d %H:%M') }}</p>
    </div>
    
    {% for section in sections %}
    <div class="section">
        <h2>{{ section.title }}</h2>
        {% if section.type == 'table' %}
        <table>
            <thead>
                <tr>
                    {% for col in section.columns %}
                    <th>{{ col }}</th>
                    {% endfor %}
                </tr>
            </thead>
            <tbody>
                {% for row in section.rows %}
                <tr>
                    {% for cell in row %}
                    <td>{{ cell }}</td>
                    {% endfor %}
                </tr>
                {% endfor %}
            </tbody>
        </table>
        {% elif section.type == 'chart' %}
        <div class="chart">
            <img src="data:image/png;base64,{{ section.image }}" />
        </div>
        {% elif section.type == 'text' %}
        <p>{{ section.content }}</p>
        {% endif %}
    </div>
    {% endfor %}
    
    <div class="footer">
        <p>{{ branding.footer or 'Generated by BoloDB' }}</p>
    </div>
</body>
</html>
"""

DEFAULT_BRANDING = {
    "title": "BoloDB Report",
    "color": "#3b82f6",
    "footer": "Generated by BoloDB"
}
```

**3. API routes** (`backend/app/routes/reports.py`):

- `POST /api/reports/generate` - Generate report
- `GET /api/reports/templates` - List templates
- `POST /api/reports/templates` - Create template
- `GET /api/reports/history` - Report generation history
- `GET /api/reports/{id}/download` - Download report

### Frontend Changes

**1. Report builder** (`frontend/src/routes/reports/builder/+page.svelte`):

```svelte
<script>
    let reportConfig = {
        title: '',
        sections: [],
        branding: {
            title: 'BoloDB Report',
            color: '#3b82f6',
            footer: 'Generated by BoloDB'
        }
    };
    
    function addSection(type) {
        reportConfig.sections = [...reportConfig.sections, {
            type,
            title: `Section ${reportConfig.sections.length + 1}`,
            query: '',
            content: ''
        }];
    }
    
    async function generatePDF() {
        const response = await apiCall('/api/reports/generate', {
            method: 'POST',
            body: JSON.stringify({ ...reportConfig, format: 'pdf' })
        });
        // Download PDF
    }
    
    async function generateExcel() {
        const response = await apiCall('/api/reports/generate', {
            method: 'POST',
            body: JSON.stringify({ ...reportConfig, format: 'excel' })
        });
        // Download Excel
    }
</script>

<div class="report-builder">
    <header>
        <input bind:value={reportConfig.title} placeholder="Report Title" />
        <button on:click={generatePDF}>Export PDF</button>
        <button on:click={generateExcel}>Export Excel</button>
    </header>
    
    <div class="sections">
        {#each reportConfig.sections as section, i}
            <div class="section">
                <input bind:value={section.title} placeholder="Section Title" />
                {#if section.type === 'query'}
                    <textarea bind:value={section.query} placeholder="SQL Query"></textarea>
                {:else if section.type === 'text'}
                    <textarea bind:value={section.content} placeholder="Content"></textarea>
                {/if}
                <button on:click={() => removeSection(i)}>Remove</button>
            </div>
        {/each}
    </div>
    
    <div class="add-section">
        <button on:click={() => addSection('query')}>Add Query Section</button>
        <button on:click={() => addSection('text')}>Add Text Section</button>
        <button on:click={() => addSection('chart')}>Add Chart Section</button>
    </div>
    
    <section class="branding">
        <h3>Branding</h3>
        <input bind:value={reportConfig.branding.title} placeholder="Report Title" />
        <input type="color" bind:value={reportConfig.branding.color} />
        <input bind:value={reportConfig.branding.footer} placeholder="Footer" />
    </section>
</div>
```

**2. Template gallery** (`frontend/src/routes/reports/templates/+page.svelte`):
- Pre-built templates
- Custom template management

**3. Report history** (`frontend/src/routes/reports/history/+page.svelte`):
- List of generated reports
- Download/regenerate options

### Key Files
- `backend/app/services/reports.py` - Report generator (new)
- `backend/app/services/report_templates.py` - Templates (new)
- `backend/app/routes/reports.py` - API routes (new)
- `frontend/src/routes/reports/builder/+page.svelte` - Report builder
- `frontend/src/routes/reports/templates/+page.svelte` - Templates
- `frontend/src/routes/reports/history/+page.svelte` - History

## Related Issues
- Part of Feature Roadmap (#71)
- Related to Dashboard Builder (#234) for dashboard export
- Related to Scheduled Queries (#225) for scheduled reports


Navigation Menu

Uh oh!

Report Generation #241

Problem Statement

Proposed Solution

Acceptance Criteria

Technical Approach

Backend Changes

Frontend Changes

Key Files

Related Issues

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Uh oh!

Report Generation #241

Description

Problem Statement

Proposed Solution

Acceptance Criteria

Technical Approach

Backend Changes

Frontend Changes

Key Files

Related Issues

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions