> Part of the PWA DevGuide (split from pwa_dev_style.md on 2026-07-02 — see that file for the index; ADR-099).

## 19. Report Generation & Google Slides (GAS)

This section covers the full output pipeline: what is generated after a recce is submitted, how it reaches the two destinations (VPS server report + Google Slides), and the exact data structures involved.

---

### 19.1 Overview — Two Output Targets

When a submitted entry is synced (`syncNow()` triggers on submit if online, or on manual tap):

| Target | How fired | What is produced | Response handling |
|---|---|---|---|
| **VPS `/slides-proxy/`** | `fetch` (awaited, JSON) | Server-hosted HTML report + permanent photo URLs | Awaited — URLs stored in IndexedDB |
| **Google Apps Script (GAS)** | `fetch` (fire-and-forget, `mode:'no-cors'`) | Google Drive photos + Google Sheets row + Google Slides PPT | No response read — truly fire-and-forget |

Both run inside `syncNow()` for every `pending` or `failed` entry. A **backfill pass** also re-fires GAS for any `synced` entry that has no `gasAt` timestamp (entries that reached Postgres but never reached GAS).

---

### 19.2 Sync Flow Step by Step

```
submitRecce()
  ↓ save to IndexedDB (status: 'pending')
  ↓ if online → syncNow()

syncNow()
  ↓ load all pending/failed entries from IndexedDB
  for each entry:
    ├─ fireGas(entry)          ← fire-and-forget (no-cors), no await
    │    └─ POST full JSON to GAS_URL
    │         GAS does: Drive upload → Sheet row → Slides generation
    │
    └─ syncToPostgres(entry)   ← awaited
         ├─ Step 1: POST /slides-proxy  {type:'save-photos', subId, photos{front/inside/brandings/addl/doc in b64}}
         │           → returns { success:true, photoUrls:{front:[], inside:[], brandings:[], addl:[], doc:null} }
         │
         ├─ Step 1.5: POST /slides-proxy  {type:'make-slides', gasUrl, subId, storeInfo, photoGroups{VPS URLs}}
         │           → fire-and-forget inner async (no await on result)
         │
         ├─ Step 2: generateRecceReportHtml(entry, photoUrls)  ← client-side
         │           → returns complete HTML string
         │           POST /slides-proxy  {type:'save-view', subId, html}
         │           → returns { success:true, url:'https://…/recce/views/RC_….html' }
         │
         └─ Step 3: POST /db/submissions  (PostgREST upsert)
                    → stores all metadata + photoUrls + view_url in Postgres
                    → returns { ok:true, viewUrl }
```

On success: entry in IndexedDB updated to `status:'synced'`, `syncedAt` and `viewUrl` fields added.

---

### 19.3 GAS Payload — What is Sent

The **entire IndexedDB submission object** is POST-ed to the GAS endpoint:

```json
{
  "id": "RC_1715000000000_A1B2",
  "timestamp": "2025-05-06T09:30:00.000Z",
  "status": "pending",
  "submittedBy": "harish",
  "submittedByName": "Harish Kumar",
  "gasAt": "2025-05-06T09:30:05.000Z",
  "data": {
    "storeName": "Sood Electronics & Electricals",
    "brand": "LIEBHERR",
    "address": "Railway Road, Doraha",
    "mobile": "8968800019",
    "city": "",
    "date": "2025-05-06",
    "gpsCoords": { "lat": 30.7946, "lng": 76.0345, "acc": 12 },
    "frontPhotos":  [ { "b64": "data:image/jpeg;base64,/9j/...", "cmt": "" } ],
    "insidePhotos": [ { "b64": "data:image/jpeg;base64,...",     "cmt": "" } ],
    "hasVideo": false,
    "videoNote": null,
    "videoCmt": "",
    "brandings": [
      {
        "type": "Glow Sign Board",
        "w": "48", "h": "24", "qty": "2",
        "sqft": "16 sq.in = 8.00 sq.ft",
        "photoB64": "data:image/jpeg;base64,..."
      }
    ],
    "addlPhotos": [ { "b64": "...", "desc": "Corner view" } ],
    "docB64": "data:image/jpeg;base64,..."
  }
}
```

> **Note**: All photos are full base64 data URIs (including MIME prefix `data:image/jpeg;base64,...`). GAS is responsible for decoding these and saving to Google Drive. The payload can be large — a single recce with 6 photos at compressed quality is typically 2–5 MB.

---

### 19.4 GAS Configuration

| Item | Value / Location |
|---|---|
| Default GAS URL | `DEFAULT_GAS_URL` constant — hardcoded Google Apps Script exec URL |
| User-configurable URL | Settings screen → "GAS URL" input → saved as `gas-url` in cfg store |
| Runtime URL | `(await cfg('gas-url')) || DEFAULT_GAS_URL` — user value overrides default |
| Request mode | `mode:'no-cors'` — response is never readable; any HTTP status is swallowed |
| Error handling | `.catch(()=>{})` — failures are silent; there is no retry on GAS failure |
| GAS responsibilities | Photos → Google Drive folder, Row → Google Sheet, Slides → Google Slides PPT |

---

### 19.5 VPS Proxy — `/slides-proxy/` Endpoint

Used inside `syncToPostgres()`. Two call types, both are JSON POST:

#### Call 1 — Save Photos
```json
POST /slides-proxy
{
  "type": "save-photos",
  "subId": "RC_1715000000000_A1B2",
  "photos": {
    "front":    [ { "b64": "data:image/jpeg;base64,...", "cmt": "" } ],
    "inside":   [ { "b64": "data:image/jpeg;base64,...", "cmt": "" } ],
    "brandings":[ { "b64": "data:image/jpeg;base64,..." } ],
    "addl":     [ { "b64": "data:image/jpeg;base64,...", "desc": "Corner view" } ],
    "doc":      "data:image/jpeg;base64,..."
  }
}
```
Response:
```json
{ "success": true, "photoUrls": {
    "front":    ["https://…/recce/uploads/RC_…/front_0.jpg"],
    "inside":   ["https://…/recce/uploads/RC_…/inside_0.jpg"],
    "brandings":["https://…/recce/uploads/RC_…/brand_0.jpg"],
    "addl":     ["https://…/recce/uploads/RC_…/addl_0.jpg"],
    "doc":      "https://…/recce/uploads/RC_…/doc.jpg"
}}
```

#### Call 2 — Save HTML Report
```json
POST /slides-proxy
{
  "type": "save-view",
  "subId": "RC_1715000000000_A1B2",
  "html": "<!DOCTYPE html>…full report HTML string…"
}
```
Response:
```json
{ "success": true, "url": "https://srv….hstgr.cloud/recce/views/RC_….html" }
```
This `url` becomes `entry.viewUrl` — the permanent shareable link.

---

### 19.6 HTML Report — Rich Server Version (`generateRecceReportHtml`)

Generated **client-side** using permanent VPS photo URLs, then saved server-side.

#### Layout Structure

```
┌─────────────────────────────────────────┐
│  HERO (gradient navy → dark blue)        │
│  ┌─────────────────────────────────────┐│
│  │ 📍 Branding Recce Report  [badge]   ││
│  │ Store Name (h1)                     ││
│  │ Address (muted)                     ││
│  │ ┌──────┐┌──────┐┌──────┐┌──────┐   ││
│  │ │Brand ││Visit ││Agent ││Mobile│   ││
│  │ └──────┘└──────┘└──────┘└──────┘   ││
│  │ [GPS → Maps] [Submitted timestamp]  ││
│  └─────────────────────────────────────┘│
├──────── SECTION: Front View Photos ──────┤
├──────── SECTION: Inside View Photos ─────┤
├──────── SECTION: Video Recording ────────┤  (conditional)
├──────── SECTION: Branding Items ─────────┤  (conditional)
├──────── SECTION: Additional Photos ──────┤  (conditional)
├──────── SECTION: Submission Document ────┤  (conditional)
│  [🖨 Print / Save as PDF]  [Close]       │
└─────────────────────────────────────────┘
```

#### Photo Grid System

| Photo count | CSS class | Grid layout |
|---|---|---|
| 1 | `pg1` | 1 column, max-width 480px |
| 2 | `pg2` | 2 equal columns |
| 3–6 | `pg3` | 3 columns |
| 7+ | `pgn` | Auto-fill, minmax(160px, 1fr) |

Each photo cell (`pi`): aspect-ratio 4:3, border-radius 8px, hover zoom effect, caption below in italic if present.

#### Branding Item Row Layout

```
┌────────────────────────────────────┬──────────────┐
│ [1]  Glow Sign Board               │  [Photo 1:1] │
│ 48 × 24 in  · Qty 2  · 8.00 sq.ft │              │
└────────────────────────────────────┴──────────────┘
```
Grid: `1fr 170px`. Photo aspect-ratio 1:1 (square). Falls back to "No photo" placeholder if photo missing.

#### Hero Meta Grid

Auto-fill grid of "hm" tiles (minmax 148px). Shown tiles:

| Tile | Always shown | Conditional |
|---|---|---|
| Brand | ✓ | |
| Visit Date | ✓ | |
| Field Agent | ✓ | |
| Store Mobile | | only if `d.mobile` is truthy |
| GPS Location | | only if `d.gpsCoords` exists → clickable Maps link |
| Submitted | ✓ | timestamp in `en-IN` locale |

#### Report CSS Notes

- **Self-contained**: all CSS is inline `<style>` — no external dependencies
- **Responsive**: `@media(max-width:600px)` — 3-col grid drops to 2-col, branding goes single-column
- **Print-ready**: `@media print` — white background, no box-shadows, `page-break-inside:avoid` on sections, print button hidden

---

### 19.7 HTML Report — Offline Fallback Version (`viewRecce`)

When `entry.viewUrl` is **null** (entry not yet synced, or sync failed), `viewRecce()` generates a **simpler offline report** from base64 blobs stored in IndexedDB.

| Aspect | Server report | Offline fallback |
|---|---|---|
| Source | Permanent VPS URLs | `URL.createObjectURL` from IndexedDB base64 blobs |
| CSS | Full design system | Minimal inline styles |
| Branding items | Grid with specs + photo | Single-column with inline image |
| Photo grid | Responsive grid class system | `auto-fill minmax(140px,1fr)` |
| GPS | Maps link | Coordinates text only |
| Persistence | URL is permanent, shareable | Blob URLs revoked after 30 seconds |
| Open method | `window.open(entry.viewUrl, '_blank')` | `window.open(blobUrl, '_blank')` |

`b64ToUrl()` helper (local to `viewRecce`): converts `data:image/jpeg;base64,...` string → fetch → Blob → `createObjectURL`. All conversions run in parallel via `Promise.all`.

---

### 19.8 `viewRecce()` Decision Logic

```
viewRecce(id)
  ↓ load entry from IndexedDB
  ↓ if entry.viewUrl exists
      → window.open(entry.viewUrl, '_blank')   ← server report
  ↓ else
      → convert all b64 photos to blob URLs
      → build offline HTML string
      → Blob → createObjectURL
      → window.open(blobUrl, '_blank')
      → setTimeout(revokeObjectURL, 30000)
```

---

### 19.9 PostgREST — What is Stored

Table: `recce.submissions` (upsert key: `sub_id`, conflict resolution: `merge-duplicates`)

| Column | Type | Source |
|---|---|---|
| `sub_id` | text PK | `entry.id` (`RC_…`) |
| `store_name` | text | `d.storeName` |
| `brand` | text | `d.brand` |
| `visit_date` | date | `d.date` |
| `address` | text | `d.address` |
| `mobile` | text | `d.mobile` |
| `city` | text | `d.city` |
| `has_branding` | bool | `d.brandings.length > 0` |
| `branding_count` | int | `d.brandings.length` |
| `has_video` | bool | `d.hasVideo` |
| `front_photos` | int | count |
| `inside_photos` | int | count |
| `addl_photos` | int | count |
| `gps_lat` | float | `d.gpsCoords.lat` |
| `gps_lng` | float | `d.gpsCoords.lng` |
| `submitted_by` | text | `hub.empId` (from `lm360-session`) |
| `submitted_at` | timestamptz | `entry.timestamp` |
| `photo_urls` | jsonb | full `photoUrls` object from Step 1 |
| `view_url` | text | permanent report URL from Step 2 |
| `meta` | jsonb | `{ brandings:[{type,w,h,qty,sqft}], videoNote, videoCmt }` |
| `status` | text | `'submitted'` |

> **Note**: Individual photo base64 blobs are **not** stored in Postgres — only the permanent VPS URLs (`photo_urls` jsonb). Base64 is only held in IndexedDB locally and in the GAS payload.

---

### 19.10 Entry Lifecycle (all status fields)

| Field | When set | Value |
|---|---|---|
| `status` | On local save | `'pending'` |
| `status` | After successful Postgres sync | `'synced'` |
| `status` | After Postgres sync failure | `'failed'` |
| `syncedAt` | On Postgres success | ISO timestamp |
| `gasAt` | When GAS is fired | ISO timestamp |
| `viewUrl` | On Step 2 success | permanent URL string |
| `editedAt` | On edit + re-submit | ISO timestamp (replaces original timestamp) |

An entry can be `synced` (reached Postgres) but have no `gasAt` (GAS never fired). The backfill pass in `syncNow()` catches these and re-fires GAS.

---

### 19.11 Submission ID Format

```
RC_{Date.now()}_{Math.random().toString(36).slice(2,6).toUpperCase()}

Example:  RC_1715000000000_A1B2
```

- Prefix: `RC_`
- Epoch ms timestamp (13 digits)
- Underscore
- 4-char alphanumeric random suffix (uppercase)

On edit (`F.editingId` set): the original `id` is reused — the upsert in Postgres replaces the existing row via `merge-duplicates`.

---

### 19.12 `_recceHubUser()` — Agent Identity

Reads the active hub session from `localStorage`:
```js
JSON.parse(localStorage.getItem('lm360-session') || '{}')
```
Returns: `{ empId, name, loginAt, ... }` (same session object written by `/hub/` on login).

Used in:
- `submitRecce()` → sets `submittedBy` and `submittedByName` on the entry
- `syncToPostgres()` → writes `submitted_by` to Postgres
- `loadHome()` → filters server rows to show only current agent's submissions

---

### 19.13 Two GAS Endpoints — Key Distinction

| Config key | Settings label | Fired by | Mechanism | Payload | Purpose |
|---|---|---|---|---|---|
| `gas-url` | "Sheet Sync" | `fireGas(entry)` inside `syncNow()` | `mode:'no-cors'` POST, no await | Full IndexedDB entry with base64 photos | Uploads photos to Drive + appends row to Google Sheet |
| `slides-gas-url` | "Slides Generator" | `makeSlides(id)` (manual 📊 button) + Step 1.5 in `syncToPostgres()` | Awaited POST via `/slides-proxy` intermediary | VPS photo URLs + store info (no base64) | Creates Google Slides PPT, returns `slideUrl` |

**Legacy vs current:** `gas-url` sends raw base64 — GAS decodes and saves. `slides-gas-url` receives permanent VPS URLs — GAS just fetches them. Use `slides-gas-url` for new PWAs (lighter GAS, reliable).

Both URLs are admin-locked. Runtime selection: `(await cfg('slides-gas-url')) || (await cfg('gas-url')) || DEFAULT_GAS_URL`.

---

### 19.14 Google Sheet — 107-Column Layout

**Sheet ID:** `1qRbmD-MuCkGHpas0F_4Klecu7NUVH6sfS2TIrTbUc8I`
**Drive parent folder ID:** `1uAF07gJ5a7dNGpxsmfEWDAV05WHwYsg2` → creates subfolder "Branding Recce Photos" → per-submission subfolder named by `entry.id`

| Cols | Count | Content |
|---|---|---|
| 1–3 | 3 | `sub_id` · `syncedAt` · `editedAt` |
| 4–10 | 7 | `date` · `brand` · `storeName` · `address` · `mobile` · `gps_lat` · `gps_lng` |
| 11–22 | 12 | Front photo 1–3 (Drive URL + comment each) · Inside photo 1–3 (Drive URL + comment each) |
| 23–25 | 3 | `hasVideo` (Yes/No) · `videoNote` · `videoCmt` |
| 26–27 | 2 | `hasBranding` (Yes/No) · `branding_count` |
| 28–87 | 60 | 10 branding slots × 6 cols: `type` · `w` · `h` · `qty` · `sqft` · Drive URL |
| 88 | 1 | Submission doc Drive URL |
| 89–104 | 16 | 8 additional photos × 2 cols: Drive URL · description |
| 105 | 1 | Sync status (`synced`) |
| 106–107 | 2 | Document Studio auto-fill (left empty by GAS) |

**For new PWA:** Design column headers first, update `SS_ID` and `PARENT_ID` in `gas_slides.gs`, build the `row` array to match. Keep typed-item blocks as N-col groups (e.g. 6 cols per item) for Document Studio compatibility.

**Photo sharing:** Each file uploaded to Drive is set `ANYONE_WITH_LINK, VIEW` so Document Studio can embed it.

---

### 19.15 slides_proxy.py — Request Type Matrix

Python proxy at port `8768`. Five `type` values dispatched by `data.type`:

| `type` | Called by | Action |
|---|---|---|
| `save-photos` | `syncToPostgres()` Step 1 | Decodes base64 → saves to `/recce/{subId}/` on disk → returns `photoUrls` object |
| `save-view` | `syncToPostgres()` Step 2 | Saves HTML string to `/recce/views/{subId}.html` → returns permanent URL |
| `make-slides` | `syncToPostgres()` Step 1.5 (fire-and-forget inner async) | Forwards `storeInfo` + `photoGroups` (with VPS URLs) to GAS `makeRecceSlides` |
| `recce` | `makeSlides(id)` manual 📊 button | Saves base64 photos + calls GAS `makeRecceSlides` in one round trip |
| `installation` | Installation PWA | Calls GAS `makeInstallationSlides` with `job` + `counters` payload |

**For new PWA:** Add a new `type` handler in `slides_proxy.py` and a matching `action` in `gas_slides.gs`.

---

### 19.16 GAS Script — Action Routing (`gas_slides.gs`)

```
doPost(e)
  data.action = ?
  ├─ 'makeRecceSlides'
  │     Input: { storeInfo:{storeName,brand,date,address,mobile,gpsCoords},
  │              photoGroups:{front:[{url,cmt}], inside:[{url,cmt}],
  │                           addl:[{url,cmt}], brandings:[{url,meta:{type,w,h,qty,sqft}}]} }
  │     Output: { slideUrl: 'https://docs.google.com/presentation/d/…' }
  │
  ├─ 'makeInstallationSlides'
  │     Input: { job:{client,tour}, counters:[{counter_name,status,city,state,
  │                                            item_type,size,qty,remarks_inst,slide_number}] }
  │     Output: { slideUrl: '…' }
  │
  └─ 'syncSheet'
        Input: { submission: <full IndexedDB entry with base64 photos> }
        Action: uploads all photos to Drive folder → appends 107-col row to Sheet
        Output: { success:true, sheetUrl: '…' }
```

All created Slides files are shared `ANYONE_WITH_LINK, VIEW` before returning.

---

### 19.17 Google Slides — Recce Layout Template

Canvas: 720 × 405 (16:9). All text via `addText(slide, text, x, y, w, h, {size, color, bold, align})`. All images via `insertImg(slide, url, x, y, w, h)` (fetches via `UrlFetchApp`).

| Slide | Background | Key elements |
|---|---|---|
| **1 — Title** | `#0f172a` | Brand 13 px orange bold (y=20) · Store name 36 px white bold (y=55) · Address/mobile/date 13 px grey (y=160) · GPS green 11 px (y=275) |
| **Photo group slides** | `#1e293b` | Section label orange 11 px bold · 2 photos per slide at (x=20,y=42,340×280) and (x=360,y=42,340×280) · comment 9 px grey below each |
| **Branding header** | `#0f172a` | "Existing Branding" 30 px orange centered (y=130) · item count 15 px grey (y=210) |
| **Branding item slides** | `#1e293b` | "Branding N: Type" 12 px orange bold · dims/qty/sqft 13 px white · photo centered 175,80,370×270 |

Photo groups rendered in order: front → inside → addl → brandings. Each group only if `photos.length > 0`.

---

### 19.18 Google Slides — Installation Layout Template

Dark theme: `#0a0a0a` (title) / `#161616` (content). Accent: `#FF6B35`.

| Slide | Content |
|---|---|
| **1 — Title** | "INSTALLATION REPORT" 11 px orange · Client 34 px white bold · Tour 18 px orange · N counters / N ready / N pending stats · Date |
| **2 — Status Summary** | One row per status: label (15 px white) + count right-aligned (20 px orange bold) |
| **Counter slides** | 3 counters per slide · Per counter: name bold white, location grey, specs white, remarks grey, status badge right (colored) · thin divider line between counters |

Status colors: ready=`#2ecc71` · recce_pending=`#f39c12` · mockup_pending=`#3498db` · unclear=`#888888`

**Proxy call:** `POST /slides-proxy` with `{type:'installation', gasUrl, job:{client,tour}, counters:[…]}`.

---

### 19.19 New PWA — Google Sheet + Slides Setup Checklist

1. Design sheet columns (decide entity fields + photo groups + item blocks)
2. Create Google Sheet → note **Sheet ID** from URL
3. Create Drive parent folder → note **Folder ID**
4. In `gas_slides.gs`: update `SS_ID`, `PARENT_ID`; add new `action` handler if needed
5. In `slides_proxy.py`: add new `type` handler if needed; update `RECCE_DIR` / `WEB_BASE`
6. In PWA settings view: add two admin-locked URL inputs → `gas-url` + `slides-gas-url`
7. Add to IndexedDB cfg keys: `gas-url`, `slides-gas-url`
8. **Sheet sync pattern** (`fireGas`): `fetch(gasUrl, {method:'POST', mode:'no-cors', body: JSON.stringify(entry)})`  — no-cors, no response check, silent `.catch(()=>{})`
9. **Slides pattern** (`makeSlides`): `fetch('/slides-proxy', {method:'POST', body: JSON.stringify({type:'recce', gasUrl, subId, storeInfo, photoGroups})})` → await → `result.slideUrl` → `window.open`
10. Re-deploy GAS after any script change → copy new exec URL → update PWA settings

---