BE-LightningReport/README.md

# Lightning Report (n8n)

Automated lightning activity reports for wind farms. An **n8n** workflow detects strikes via the [iklim.co](https://iklim.co) API, generates a Word report through a **Python report service**, routes it through **Slack** for human approval, and emails the approved report to customers via **SendGrid**.

## Architecture

```text
┌─────────────┐     ┌──────────────────┐     ┌─────────────────────┐
│ n8n workflow│────▶│ iklim.co API     │     │ Report service      │
│ (scheduled) │     │ lightnings +     │     │ FastAPI /generate   │
│             │     │ thunderstorms    │     │ → DOCX              │
└──────┬──────┘     └──────────────────┘     └──────────▲──────────┘
       │                                                  │
       │  Slack upload + approve/reject buttons           │
       ▼                                                  │
┌─────────────┐     storm_logs datatable (pending report) │
│ Slack       │───────────────────────────────────────────┘
└──────┬──────┘
       │ on approve
       ▼
┌─────────────┐
│ SendGrid    │──▶ customer contact_email
└─────────────┘
```

## Repository layout

| Path | Description |
|------|-------------|
| `Lightning_Report_Automatic.json` | n8n workflow export (import in n8n) |
| `report_service/` | FastAPI wrapper around report generation |
| `report_service/adapter.py` | Maps n8n JSON payload → pandas DataFrames |
| `src/reporting/docx.py` | Main DOCX builder |
| `src/analysis/` | Risk, histogram, geospatial, statistics |
| `src/visualization/` | Maps and storm-cell plots |
| `src/config.py` | Default analysis parameters |

## Prerequisites

- **Python 3.12+** (local or Docker)
- **n8n** (self-hosted, with Data Tables enabled)
- **iklim.co API** credentials (test: `https://api-test.iklim.co`)
- **Slack** app/bot for uploads and approval buttons
- **SendGrid** API key with Mail Send permission
- Optional: **Gemini API key** if commentary is generated inside the report service (otherwise pass `gemini_text` from n8n)

## Report service (local)

### 1. Install dependencies

```bash
cd Lightning_Report_n8n
python -m venv .venv
source .venv/bin/activate   # Windows: .venv\Scripts\activate
pip install -r requirements.txt -r report_service/requirements.txt
```

### 2. Environment variables

Create a `.env` file in the project root (never commit it):

```env
REPORT_SERVICE_TOKEN=your-long-random-secret
GEMINI_API_KEY=optional-if-not-supplied-by-n8n
GEMINI_MODEL=gemini-2.5-flash-lite
LOG_LEVEL=INFO
```

`REPORT_SERVICE_TOKEN` is required. The service refuses `/generate` if it is unset.

### 3. Run the server

```bash
export REPORT_SERVICE_TOKEN="your-long-random-secret"
uvicorn report_service.main:app --host 0.0.0.0 --port 8000
```

Health check: `GET http://localhost:8000/health`

### 4. Expose to n8n (development)

If n8n runs elsewhere, tunnel the service:

```bash
ngrok http 8000
```

Point the n8n **Generate Report** HTTP Request node at `https://<your-ngrok-host>/generate` and set header `X-Report-Token` to the same value as `REPORT_SERVICE_TOKEN`.

## Report service (Docker)

```bash
cd Lightning_Report_n8n
export REPORT_SERVICE_TOKEN=your-long-random-secret
docker compose -f report_service/docker-compose.yml up --build -d
```

From n8n on the same Docker network: `http://report-service:8000/generate`

## API

### `GET /health`

Liveness probe. Returns `{"ok": true, ...}`.

### `POST /generate`

| Header | Required | Description |
|--------|----------|-------------|
| `X-Report-Token` | Yes | Must match `REPORT_SERVICE_TOKEN` |
| `Content-Type` | Yes | `application/json` |

**Body** (built by n8n **Build Report Payload** node):

| Field | Description |
|-------|-------------|
| `customer_name` | Wind farm / customer label |
| `t_start`, `t_end` | Storm window (epoch ms) |
| `centroid_lat`, `centroid_lon` | Farm centroid |
| `boundary_m` | Monitoring radius (m) |
| `rings` | `{ r1, r2, r3, r4 }` distance rings |
| `timezone` | e.g. `Europe/Istanbul` |
| `turbines` | Array of turbine objects |
| `strikes` | Lightning strike records (`captured` ISO time preferred) |
| `storm_records` | Thunderstorm polygons from `/v1/thunderstorms/within` |
| `gemini_text` | Optional pre-generated commentary |
| `language` | Optional report language: `en` (default) or `tr` |

**Response:** DOCX file (`application/vnd.openxmlformats-officedocument.wordprocessingml.document`).

## n8n workflow

Import `Lightning_Report_Automatic.json` into n8n and configure:

### Credentials

| Integration | Used for |
|-------------|----------|
| iklim.co login | `Login to iklim.co` / token refresh |
| Slack (`n8n_lightning_report_bot`) | Report upload, approval buttons |
| Slack (`Tarla Slack Account`) | Error / email-sent notifications |
| SendGrid | Customer email after approval |

### Data tables

**`customers`**

| Column | Purpose |
|--------|---------|
| `customer_name` | Display name |
| `contact_email` | SendGrid recipient |
| `language` | Report language: `en` or `tr` (also accepts `turkish`, `Türkçe`) |
| `id` | Linked from wind turbines |

**`wind_turbine_farm`**

| Column | Purpose |
|--------|---------|
| `customer_id` | FK to customers |
| `latitude`, `longitude` | Turbine positions |
| `name` | Turbine label |

**`storm_logs`**

| Column | Purpose |
|--------|---------|
| `customer_name`, `storm_key`, `storm_start`, `storm_end` | Storm metadata |
| `total_strikes`, `status` | Count; `waiting for confirmation` → `confirmed` / `rejected` |
| `pending_contact_email` | Email snapshot at report time |
| `pending_email_file_name` | DOCX filename |
| `pending_report_base64` | Queued report (survives n8n restarts) |
| `pending_report_mime_type` | MIME type for attachment |

### Main flow (simplified)

1. Daily trigger → authenticate → loop customers.
2. Query lightnings within farm boundary; detect storm window.
3. Insert/update `storm_logs`, fetch thunderstorms, build payload (include `language` from the customer row).
4. **Generate Report** → upload DOCX to Slack → approval buttons.
5. Persist pending report fields on `storm_logs`.
6. On Slack **Approve** → load queued report → **SendGrid** email → clear pending fields.

Email is sent only after approval, not when the report is first generated.

## Troubleshooting

### `channel_not_found` (Slack)

The Slack channel ID in the node does not exist or the bot is not invited. Re-select the channel in n8n and run `/invite @YourBot` in that channel.

### `Account Is not Found For User …` (`/v1/thunderstorms/within`)

Login succeeded but the iklim user has no **account** on the API environment (e.g. test). Ask iklim.co to provision the user, or use a service account that has thunderstorm access. Lightning-only reports can continue if the workflow skips failed thunderstorm calls (optional branch).

### ngrok `503` / `ERR_NGROK_3004` on **Generate Report**

ngrok closes long-lived HTTP connections when the report service takes several minutes to respond (Kaleido chart export). The HTML `503` page with `ERR_NGROK_3004` means ngrok never received a complete response from uvicorn.

**Fix:** Re-import `Lightning_Report_Automatic.json`. Report generation uses separate n8n nodes (not one long Code node):

1. **Start Async Report** — `POST /generate/async`
2. **Wait for Report** — 15s pause
3. **Poll Report Status** — `GET /generate/async/{job_id}` (loops until complete)
4. **Download Report** — `GET /generate/async/{job_id}/download`

Restart uvicorn after pulling report-service changes. Update the ngrok URL in the three HTTP nodes when your tunnel URL changes.

### `Task execution timed out after 60 seconds` on report generation

n8n Code nodes are capped at **60 seconds**. Do not put polling loops inside a single Code node. Use the multi-node async flow above instead.

**Alternative:** Skip ngrok and call the service directly (`http://host.docker.internal:8000` from Docker n8n, or `http://127.0.0.1:8000` on the same host).

### `timeout of 300000ms exceeded` on **Generate Report**

The report service can take several minutes when many lightning strikes produce multiple Kaleido chart exports (maps, histograms, heatmaps). n8n’s HTTP Request node defaults to **5 minutes (300000 ms)**.

**Fixes (use together):**

1. Re-import `Lightning_Report_Automatic.json` — the **Generate Report** node sets `timeout: 900000` (15 min).
2. On self-hosted n8n, raise the global ceiling if needed:
   ```bash
   N8N_DEFAULT_REQUEST_TIMEOUT=900000
   EXECUTIONS_TIMEOUT=3600
   EXECUTIONS_TIMEOUT_MAX=3600
   ```
3. Prefer **Docker network** (`http://report-service:8000/generate`) over ngrok when n8n and the report service run on the same host — fewer proxy timeouts.
4. Watch report-service logs: each `kaleido` export spawns Chrome briefly; very large datasets are capped by `histogram_params.max_periods` (default 8 activity periods).

### Histogram shows `01-01-1970` / wrong period

Strike timestamps were parsed incorrectly when `timestamp` (epoch ms) was preferred over `captured` (ISO). Fixed in `report_service/adapter.py` — restart the report service after pulling updates.

### SendGrid: attachment must be base64 encoded

Use the **Prepare SendGrid Email Payload** node path; ensure `pending_report_base64` in `storm_logs` is populated and not truncated by datatable size limits.

### Binary missing at **Upload Report to Slack**

The datatable persist step must run **after** Slack upload, not before (persist nodes drop binary data).

### Slack `invalid_payload` on approval follow-up

Do not double-`JSON.stringify` the body when posting to `response_url`. Slack response URLs expire after ~30 minutes (optional confirmation message only).

## Development notes

- Per-farm settings (`rings`, centroid, dates, `language`) come from the n8n payload via `apply_farm_config()`, not hardcoded in `src/config.py`.
- Set `customers.language` to `tr` for Turkish DOCX output; omit or use `en` for English.
- Prefer strike field **`captured`** (ISO) for `local_time`; numeric **`timestamp`** is supported as epoch milliseconds.
- HMAC signing for iklim API calls is implemented in n8n Code nodes (`Calculate Lightning Headers`, `Calculate Thunderstorm Headers`).

## License

Internal use — iklim.co lightning reporting pipeline.