208 lines
9.0 KiB
Markdown
208 lines
9.0 KiB
Markdown
# Copilot Instruction Set – **Google‑Sheet → Photo‑PDF** Wizard (SvelteKit)
|
||
|
||
> **Goal**
|
||
> A privacy‑first SvelteKit SPA that guides a user through importing a Google Sheet, mapping its columns, validating/culling rows, reviewing & cropping photos, and finally exporting two A4‑PDFs (text sheet + photo sheet). No personal data ever leaves the browser after the tab closes.
|
||
|
||
---
|
||
|
||
## 0. Tech Stack & Design Constraints
|
||
|
||
| Area | Decision |
|
||
| ---------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------- |
|
||
| Front‑end | **SvelteKit** (latest) – SPA mode (`prerender = false`) |
|
||
| State | **Svelte Stores** (writable & derived). No persistence beyond sessionStorage/IndexedDB. |
|
||
| Styling | **Tailwind CSS** + `@tailwind/typography`, base theme. Page background `bg-gray-50`. Use DaisyUI or shadcn‑ui components if needed. |
|
||
| Auth & API | Google Identity Services (popup flow). Scopes: `spreadsheets`, `drive` (read/write). |
|
||
| Data storage | • Structured data → **IndexedDB** via `idb` helper. |
|
||
| • Image blobs → **Cache Storage** (`caches.open'photos-v1') a | |
|
||
| Face detection | **Mediapipe BlazeFace via TF.js (lite)** in a Web Worker. Confidence ≥ 0.8. |
|
||
| Virtual list | `svelte-virtual` (react‑window equivalent). |
|
||
| PDF | **pdf-lib** (via ESM import). Embed custom font **Lato** (ttf provided in `/static/fonts`). |
|
||
---
|
||
|
||
## 1. User Stories & Acceptance Criteria
|
||
|
||
1. **US‑01 Splash** – As a visitor I see a logo + “Start” button (no data loaded). Clicking starts the wizard.
|
||
2. **US‑02 Sign‑In** – I authenticate with Google in a popup and grant the requested scopes. On success, token is cached in memory only.
|
||
3. **US‑03 Sheet Search** – I type part of a Sheet name; a fuzzy list of my sheets appears (max 20). I select one.
|
||
4. **US‑04 Column Mapping** – Columns A–F preview (first 3 data rows). Under each header is a dropdown: Name / Surname / Nationality / Birthday / Picture URL / Ignore. All five fields must be mapped exactly once. Previous mapping is remembered for future sessions in `localStorage` (key: `columnHints`).
|
||
5. **US‑05 Row Validate & Select** – I see rows in a virtualized table with validation states. Invalid rows (missing data, age < 18 or > 40) are pre‑flagged. I can tick checkboxes to include/exclude. No in‑app editing.
|
||
6. **US‑06 Photo Gallery** – Lazy virtual grid of thumbnails. Auto face‑crop overlay (dashed rectangle). Pictures with 0 or >1 faces are floated to top & flagged “Need manual crop”. I can drag/resize a fixed‑ratio rectangle to override. Cropped area is stored as `{x,y,w,h}` in store.
|
||
7. **US‑07 Progress** – While downloading or processing images / faces, I see a determinate progress bar (0‑100%). Each stage (download, face detect, PDF gen).
|
||
8. **US‑08 Generate PDFs** – I click “Generate”. Two PDFs download:
|
||
|
||
* `people_data.pdf` – text‑only; grid fills as many boxes per A4 as fit (auto calculate cols/rows). Each box uses provided coordinates (see §4).
|
||
* `people_photos.pdf` – same grid but with cropped headshot @ modest DPI, name beneath.
|
||
9. **US‑09 Cleanup** – On window/tab unload, all IndexedDB records and CacheStorage entries are cleared.
|
||
|
||
---
|
||
|
||
## 2. Component Tree (Svelte)
|
||
|
||
```
|
||
+Layout.svelte (Tailwind container)
|
||
├─ Splash.svelte
|
||
├─ Wizard.svelte (stateful)
|
||
├─ StepAuth.svelte
|
||
├─ StepSheetSearch.svelte
|
||
├─ StepColumnMap.svelte
|
||
├─ StepRowFilter.svelte
|
||
├─ StepGallery.svelte
|
||
└─ StepGenerate.svelte
|
||
```
|
||
|
||
### Shared Stores (`src/lib/stores.ts`)
|
||
|
||
```ts
|
||
export const session = writable<{
|
||
token?: string;
|
||
user?: { name: string; email: string };
|
||
}>({});
|
||
|
||
export const sheetData = writable<RowData[]>(); // after mapping
|
||
export const pictures = writable<Record<RowID, PictureBlobInfo>>();
|
||
export const cropRects = writable<Record<RowID, Crop>>();
|
||
```
|
||
|
||
---
|
||
|
||
## 3. Data Flow
|
||
|
||
1. **Auth** ⇒ `session.token` set.
|
||
2. **Sheet fetch** (Google Sheets API) ⇒ raw 2D array.
|
||
3. **Mapping** ⇒ transform to `RowData` objects.
|
||
4. **Validation** ⇒ add `valid` flag; excluded rows removed from downstream.
|
||
5. **Download IMG** (Drive/HTTP) ⇒ cache → `pictures` store.
|
||
6. **Face Detect** (Worker) ⇒ proposed crop rectangle.
|
||
7. **Manual Crop** ⇒ update `cropRects`.
|
||
8. **Generate PDFs** ⇒ read `RowData`, `pictures`, `cropRects`.
|
||
|
||
---
|
||
|
||
## 4. PDF Layout Spec (mm → points helper)
|
||
|
||
* **Font**: Lato Regular, 10 pt. Embed once per doc via `pdfDoc.embedFont()`.
|
||
* **Grid**: auto compute `colWidth = 70 mm`, `rowHeight = 35 mm` for text; `imageBox = 30 mm × 30 mm` centred.
|
||
* **Coordinates (text sheet)** – within each cell (0,0 is cell top‑left):
|
||
|
||
* Full Name – x 5 mm, y 5 mm
|
||
* Nationality – x 5 mm, y 12 mm
|
||
* Birthday – x 5 mm, y 19 mm
|
||
* **Coordinates (photo sheet)**
|
||
|
||
* Image top‑left x 5 mm, y 5 mm (square)
|
||
* Name text centre below at y (5 + imageBox + 3 mm)
|
||
* Fit rows top→bottom, left→right. When page full, add new page.
|
||
|
||
---
|
||
|
||
## 5. Key Helpers
|
||
|
||
### `googleClient.ts`
|
||
|
||
* `initAuth(): Promise<Token>`
|
||
* `listSheets(query): Promise<SheetMeta[]>` (fuzzy)
|
||
* `getSheetValues(id, range): Promise<string[][]>`
|
||
|
||
### `faceWorker.ts` (Web Worker)
|
||
|
||
* Loads BlazeFace lite model once.
|
||
* `detectFaces(arrayBuffer) → {faces: Face[], blobURL}`
|
||
|
||
### `pdfGenerator.ts`
|
||
|
||
* `generateTextPDF(rows: RowData[]): Uint8Array`
|
||
* `generatePhotoPDF(rows: RowData[], crops: CropMap): Uint8Array`
|
||
|
||
---
|
||
|
||
## 6. Progress Bus (simple derived store)
|
||
|
||
```ts
|
||
export const progress = writable<{ stage: string; done: number; total: number }>();
|
||
```
|
||
|
||
Set from download loop and face detect worker.
|
||
|
||
---
|
||
|
||
## 7. Error Handling
|
||
|
||
* Auth popup closed ⇒ toast “Sign‑in cancelled”.
|
||
* Sheet fetch 404 ⇒ return to StepSheetSearch with error banner.
|
||
* Image download error ⇒ mark row `pictureError = true`, bubble to top.
|
||
|
||
---
|
||
|
||
## 8. Folder Structure Skeleton
|
||
|
||
```
|
||
src/
|
||
lib/
|
||
stores.ts
|
||
googleClient.ts
|
||
faceWorker.ts
|
||
pdfGenerator.ts
|
||
routes/
|
||
+layout.svelte
|
||
index.svelte (Splash)
|
||
wizard/
|
||
+page.svelte (mount Wizard.svelte)
|
||
components/ (each Step*.svelte)
|
||
workers/
|
||
faceWorker.js
|
||
static/
|
||
fonts/Lato-Regular.ttf
|
||
```
|
||
|
||
---
|
||
|
||
## 9. Copilot Prompt Comments (templates)
|
||
|
||
### Example: `googleClient.ts`
|
||
|
||
```ts
|
||
/**
|
||
* Google API wrapper – Browser‑only, no refresh tokens.
|
||
* Prereqs: `<script src="https://accounts.google.com/gsi/client" async defer></script>` in root.
|
||
*
|
||
* Helper responsibilities:
|
||
* 1. Launch popup OAuth (scopes: spreadsheets, drive).
|
||
* 2. List up to 20 spreadsheets matching fuzzy query.
|
||
* 3. Download a specified range (A:Z) as 2‑D array.
|
||
* 4. Use in‑memory token, auto‑refresh not required.
|
||
*/
|
||
```
|
||
|
||
### Example: `StepGallery.svelte`
|
||
|
||
```svelte
|
||
<script lang="ts">
|
||
/**
|
||
* Renders a virtual grid of downloaded photos.
|
||
* Highlights items needing manual crop (no face/multiple faces).
|
||
* When user drags the crop rectangle:
|
||
* – Persist to cropRects store.
|
||
* – Overlay rectangle turns green (valid).
|
||
*/
|
||
</script>
|
||
```
|
||
|
||
Copy‑paste these comment headers into the top of each file to prime GitHub Copilot with context.
|
||
|
||
---
|
||
|
||
## 10. Outstanding TODOs (owner = you)
|
||
|
||
* [ ] Provide exact mm → point conversion util if different spec required.
|
||
* [ ] Supply precise image/text box sizes & page margins.
|
||
* [ ] Drop in `Lato-Regular.ttf` into `/static/fonts`.
|
||
* [ ] Decide on logo / brand in Splash.
|
||
* [ ] Accessibility pass once UI stabilises.
|
||
|
||
---
|
||
|
||
### End of base instruction set
|
||
|
||
Feel free to ask for tweaks or deeper code snippets!
|