# PUE Report Agent

Turns a DREEF **Productive Use of Energy (PUE) master-sheet** workbook into a
community PUE report, using LangChain for the *prose* and deterministic Python
for *every number*.

This skeleton was reverse-engineered from two real report/master-sheet pairs
(Jaji and Kwarua Tasha, both Kaduna State). It already extracts cleanly from
both.

---

## Why it's split the way it is

The single most important design decision: **the LLM never touches a number.**

When a report's figures are re-typed by a language model, they drift — that is
literally how one of the source reports ended up with budget and equipment
numbers that didn't match its own master sheet. So the architecture is:

```
master.xlsx
   │
   ▼  extractors.py          (pure Python, openpyxl — deterministic)
PUEReportData
   │
   ▼  calc.py                (recompute every total from line items)
PUEReportData (correct totals)
   │
   ▼  validate.py            (reconciliation; appends warnings)
PUEReportData (+warnings)
   │
   ├─►  agent.py  generate_narratives()    LLM — PROSE ONLY
   │      • Community Overview  ............  Gemini  (gemini-2.5-flash)
   │      • Demographic narrative  .........  Gemini
   │      • Processing Insights  ...........  Gemini
   │
   ▼  render.py              →  template-faithful .docx
```

* **`schema.py`** — Pydantic models. The contract between every stage.
* **`extractors.py`** — reads the workbook into the schema. Numbers come from
  here, never from an LLM. Budget extraction is header-aware (each section maps
  its own columns), so irregular blocks like "OTHERS" parse correctly.
* **`calc.py`** — recomputes every total from its line items: budget section
  totals, BOQ grand total, equipment kW/quantity totals, energy projections,
  financial-model monthly totals. The template shipped with a Grand Total of
  ₦30,756,000 when its line items summed to ₦37,256,000; the agent never
  reproduces that kind of error, and records any disagreement as a warning.
* **`validate.py`** — checks interview counts agree, flags stale template text.
* **`templates.py`** — the boilerplate sections, verbatim from the reference
  template (introduction, methodology, infrastructure specs, governance, safety,
  conditions for scale-up, MER, risks, SDGs, challenges, AI disclosure), filled
  with `str.format`. Never sent to a model.
* **`agent.py`** — the only LLM code. All three narratives (Community Overview,
  demographic, processing insights) are generated with **Gemini**
  (`gemini-2.5-flash`). The model is swappable per call, so individual chains can
  move to another provider later without changing the chains.
* **`render.py`** — produces a Word document that mirrors the reference
  template's structure exactly.

## What `render.py` reproduces from the template

* **Title page** in the template's layout.
* **Auto Table of Contents** — a real Word TOC field that builds and renumbers
  itself on open.
* **Auto List of Tables** — a Word Table-of-Figures field keyed to the "Table"
  caption sequence.
* **SEQ-field caption numbering** — every caption is `Table ` + a `SEQ Table`
  field (or `Figure ` + `SEQ Figure`). Word computes the numbers, so they are
  always sequential and correct no matter how many tables a community needs —
  nothing is hard-numbered.
* **Every section in template order** with the template's headings and tone.
* **Community-adaptive tables** — one machinery table per processed crop, one
  equipment table per section, one budget table per section, each with a
  recomputed TOTAL row. The skeleton stays identical; the rows follow the data.
* Fields are set to **update on open**, so the TOC, List of Tables and all
  caption numbers populate the first time the file is opened in Word.
  (LibreOffice's headless PDF convert doesn't run that update — open in Word, or
  press Ctrl+A then F9, to populate them.)

---

## Quick start

```bash
pip install -r requirements.txt

# Extraction + recompute + validation + render — no API key needed.
# Narratives are skipped with --no-llm, but the full template still renders:
python run.py "Jaji-_Kaduna_PUE_Master_Sheet.xlsx" --no-llm \
    --developer "Green Edge Consortium" --date "February 2026" \
    --solar-pv "6.45 MWp" --battery "10 MWh" \
    --docx-out jaji_report.docx

# Full run with AI narratives (all narratives use Gemini):
export GOOGLE_API_KEY=...
python run.py "Jaji-_Kaduna_PUE_Master_Sheet.xlsx" \
    --developer "Green Edge Consortium" --date "February 2026" \
    --docx-out jaji_report.docx --json-out jaji.json
```

Open the resulting `.docx` in Word; the Table of Contents, List of Tables and
all caption numbers populate automatically on first open.

---

## What the data layers in the master sheet mean

| Layer | Sheets | Role |
|-------|--------|------|
| Raw survey | `Minigrid Farmers Input`, `Processors`, `SME`, `E-Mobility`, `Market`, `ESG` | One row per respondent |
| Analysis | `* Analysis Sheet` | Pre-aggregated label/value blocks |
| **Key Findings** | `Key Findings` | Report-ready tables (the extractor's main source) |
| Equipment / Budget / Finance | `Proposed * PUE Equipment`, `Budget`, `Individual Model`, `Agrohub Model`, `Summary` | Plans & costs |

The extractor prefers raw/analysis sheets for survey facts and only uses Key
Findings for **developer-supplied** figures (capacities, revenue-per-user,
projected impact) that aren't derivable from survey data — because Key Findings
is where stale template text from other communities tends to survive.

### Things that are NOT in the survey data
* Mini-grid capacities (Solar PV / Battery / Annual Consumption) — developer input.
* Revenue-per-user table — developer input, often shared across communities.
Pass these in explicitly; the validator warns if they're missing.

---

## Extending to a new community / new sheet layout

1. Run `python run.py new_sheet.xlsx --no-llm`.
2. Read the warnings and eyeball the summary against the source.
3. If a block comes back empty, the label probably moved. The extractor finds
   blocks by **label text scanned across all columns** (`_label_col`) rather
   than fixed cell addresses, so usually only the label string needs updating.
4. Add a `validate.py` check for any new invariant you discover.

## Adapting to a community whose data is shaped differently

The renderer is data-driven, so new crops, new SME types, more or fewer budget
sections, etc. flow through automatically — the template skeleton stays fixed
while the rows follow the master sheet. The two things to supply per community
that are NOT in the survey data:

* **Mini-grid / micro capacities** (Solar PV, battery, annual consumption,
  distribution metrics) — developer figures; pass via the CLI flags or set
  `data.minigrid` / `data.microgrid` before rendering.
* **Report date** for the title page (`--date`).

---

## Tested against
* `Jaji-_Kaduna_PUE_Master_Sheet.xlsx` — clean extraction, 3 advisory warnings.
* `Kwarua_Tasha_-_Kaduna_PUE_Master_Sheet.xlsx` — clean extraction, 6 warnings
  correctly flagging real defects in that workbook.