formations-app/ops/README.md

# ops/README.md — Moodle web-service provisioning

## provision-ws.php

Idempotent script that bootstraps the read-only WS integration on the live Moodle.

**What it does:**
1. Enables Moodle web services + REST protocol + built-in mobile service
   (`enablewebservices`, `enablemobilewebservice`, `webserviceprotocols`).
2. Creates the service user `wsreader` (WS Reader, wsreader@saillant.cc)
   if it does not already exist.
3. Enrols `wsreader` as a student in every real course (id > 1) so that
   `core_course_get_contents` returns content for each course.

Script is safe to re-run: user creation is skipped if the username exists;
enrolment is idempotent via the manual enrolment plugin.

**How to run (from Tower, where Moodle containers live):**

```bash
WSPASS=$(grep WSREADER_PASSWORD ~/formations-app.env | cut -d= -f2-)
docker cp ops/provision-ws.php moodle:/tmp/provision-ws.php
docker exec -e WSREADER_PASSWORD="$WSPASS" moodle php /tmp/provision-ws.php
```

Expected output:
```
user created: <id>   (or "user exists: <id>" on re-run)
enrolled: freertos
enrolled: kicad-makers
enrolled: esp32-ia
enrolled: docker-selfhosting
enrolled: iot-az
enrolled: llm-locaux
OK
```

## Env file — Tower: `/home/clems/formations-app.env`

```
WSREADER_PASSWORD=<generated on first run>
MOODLE_WS_TOKEN=<issued by Moodle mobile service>
```

**Never commit this file.** It lives on Tower only.

## Token renewal

The mobile-service token does not expire by default in Moodle 4.5.
To revoke and renew:

```bash
# Revoke current token via Moodle admin UI:
# Site admin → Server → Web services → Manage tokens → delete wsreader token
# Then re-issue:
WSPASS=$(grep WSREADER_PASSWORD ~/formations-app.env | cut -d= -f2-)
TOKEN=$(curl -s "https://moodle.saillant.cc/login/token.php" \
  --data-urlencode "username=wsreader" \
  --data-urlencode "password=$WSPASS" \
  --data-urlencode "service=moodle_mobile_app" \
  | python3 -c 'import json,sys; print(json.load(sys.stdin)["token"])')
sed -i "s/^MOODLE_WS_TOKEN=.*/MOODLE_WS_TOKEN=$TOKEN/" ~/formations-app.env
echo "New token: $TOKEN"
```

## Fixture provenance — fixtures/course-contents-3.json

Captured: 2026-06-10 from live Moodle (https://moodle.saillant.cc)
Course: id=3 (`kicad-makers` — "KiCad pour Makers")
Command:
```bash
TOKEN=$(grep MOODLE_WS_TOKEN ~/formations-app.env | cut -d= -f2-)
curl -s "https://moodle.saillant.cc/webservice/rest/server.php" \
  --data-urlencode "wstoken=$TOKEN" \
  --data-urlencode "wsfunction=core_course_get_contents" \
  --data-urlencode "moodlewsrestformat=json" \
  --data-urlencode "courseid=3" > fixtures/course-contents-3.json
```

**No tokens are embedded in the fixture.** `pluginfile.php` URLs in the
fixture do NOT include the token — authentication is added at fetch-time
by appending `?token=<TOKEN>` to the URL.

## Where chapter titles live (CRITICAL for Task 3 parser)

The fixture is a JSON array of **sections**. Each section has a `modules[]`
array. Book modules have `modname == "book"`.

Each book module's `contents[]` array contains two entry types:

### 1. The `structure` entry (type=`"content"`, filename=`"structure"`, filepath=`"/"`)

`contents[0]` is always a special entry with:
- `type: "content"`
- `filename: "structure"`
- `filepath: "/"`
- `content`: a **JSON string** encoding an array of chapter objects:
  ```json
  [
    {
      "title": "Introduction",
      "href": "242/index.html",
      "level": 0,
      "hidden": "0",
      "subitems": [...]
    },
    ...
  ]
  ```
  Each object has `title` (human-readable chapter title), `href` (chapter
  id from the path, e.g. `242`), `level` (0=top-level, 1=subchapter),
  `hidden`, and `subitems` (nested subchapters with the same shape).

  **This is the authoritative TOC with hierarchy** — use this to build
  the navigation tree.

### 2. Per-chapter `file` entries (type=`"file"`, filename=`"index.html"`)

Each subsequent entry has:
- `type: "file"`
- `filename: "index.html"`
- `filepath: "/<chapterId>/"` — e.g. `"/242/"` (chapter id is the path segment)
- `content`: the **plain-text chapter title** (e.g. `"Introduction"`)
- `fileurl`: pluginfile URL to fetch the chapter HTML, e.g.:
  `https://moodle.saillant.cc/webservice/pluginfile.php/69/mod_book/chapter/242/index.html`
  Fetch with `?token=<TOKEN>` appended.

**Parser recipe for Task 3:**
1. Filter `contents` where `type == "file"` and `filename == "index.html"`.
2. Extract `chapterId` from `filepath` (strip slashes → integer).
3. Use `content` as the chapter title.
4. Use `fileurl + "?token=" + TOKEN` to fetch HTML.
5. Optionally parse the `structure` entry for hierarchy (level/subitems).

Example (from module "Module 1 : Découverte de KiCad"):
| filepath | content (title)                          | fileurl (fetch with ?token=…)                                |
|----------|------------------------------------------|--------------------------------------------------------------|
| /242/    | Introduction                             | …/pluginfile.php/69/mod_book/chapter/242/index.html         |
| /243/    | 1.1 Introduction et installation        | …/pluginfile.php/69/mod_book/chapter/243/index.html         |
| /244/    | Pourquoi KiCad plutôt qu'Eagle, …       | …/pluginfile.php/69/mod_book/chapter/244/index.html         |
| /254/    | Récapitulatif                            | …/pluginfile.php/69/mod_book/chapter/254/index.html         |

## SP2 — Keycloak SSO, Grist progression, Moodle wssync

### New env vars — Tower: `/home/clems/formations-app.env`

```
OIDC_ISSUER=https://auth.saillant.cc/realms/electron_rare
OIDC_CLIENT_ID=formations-app
OIDC_CLIENT_SECRET=<Keycloak client secret, see below>
SESSION_SECRET=<openssl rand -hex 32>
APP_BASE_URL=https://formations.saillant.cc
GRIST_BASE_URL=https://grist.saillant.cc
GRIST_API_KEY=<Grist API key>
GRIST_DOC_ID=bPfitC7Poc4r2xGRx7FgHL
WSSYNC_PASSWORD=<generated on first provision run>
MOODLE_SYNC_TOKEN=<issued against the formations_sync service, see below>
```

**Never commit this file.** It lives on Tower only.

### Keycloak client (realm `electron_rare`)

Provisioned 2026-06-10 with kcadm inside the `suite-keycloak` container on
electron-server (admin creds come from the container env
`KC_BOOTSTRAP_ADMIN_USERNAME` / `KC_BOOTSTRAP_ADMIN_PASSWORD`):

```bash
docker exec suite-keycloak bash -c '
  /opt/keycloak/bin/kcadm.sh config credentials --server http://localhost:8080 \
    --realm master --user "$KC_BOOTSTRAP_ADMIN_USERNAME" --password "$KC_BOOTSTRAP_ADMIN_PASSWORD"
  /opt/keycloak/bin/kcadm.sh create clients -r electron_rare \
    -s clientId=formations-app -s protocol=openid-connect \
    -s publicClient=false -s standardFlowEnabled=true -s directAccessGrantsEnabled=false \
    -s "redirectUris=[\"https://formations.saillant.cc/auth/callback\"]" \
    -s "webOrigins=[\"https://formations.saillant.cc\"]"
  # client uuid: 120bb8ff-5dc5-4790-be0e-724a5f485da7
  /opt/keycloak/bin/kcadm.sh get clients/120bb8ff-5dc5-4790-be0e-724a5f485da7/client-secret -r electron_rare'
```

Discovery (verified): `https://auth.saillant.cc/realms/electron_rare/.well-known/openid-configuration`

| endpoint | URL |
|---|---|
| authorization | https://auth.saillant.cc/realms/electron_rare/protocol/openid-connect/auth |
| token | https://auth.saillant.cc/realms/electron_rare/protocol/openid-connect/token |
| userinfo | https://auth.saillant.cc/realms/electron_rare/protocol/openid-connect/userinfo |
| end_session | https://auth.saillant.cc/realms/electron_rare/protocol/openid-connect/logout |

### Grist progression doc

- Doc `formations-progression`, id **`bPfitC7Poc4r2xGRx7FgHL`**, org
  `electron-rare` (id 3), workspace `Home` (id 3) on grist.saillant.cc.
- Table `Progression`: `user_sub` Text, `email` Text, `course_slug` Text,
  `module_n` Int, `chapitre_n` Int, `lu_le` DateTime (epoch seconds),
  `moodle_synced` Bool.
- Smoke-tested add/list/delete via
  `/api/docs/bPfitC7Poc4r2xGRx7FgHL/tables/Progression/records`.
- CAUTION: Grist Bool filters are quirky — filter booleans in app code; only
  simple equality filters on Text columns are reliable as API filter params.

### provision-sync-role.php (Moodle wssync)

Idempotent script that provisions the privileged sync side:

1. Role `wssync` (system context, assignable at system level) with caps:
   `moodle/user:create`, `moodle/user:viewdetails`, `moodle/user:viewalldetails`,
   `moodle/site:viewuseridentity`, `moodle/user:update`, `enrol/manual:enrol`,
   `moodle/course:overridecompletion`, `moodle/course:viewparticipants`,
   `webservice/rest:use`, `moodle/webservice:createtoken`.
   - `viewalldetails` is required or `core_user_get_users_by_field` silently
     returns `[]` (the WS only returns users whose queried field is visible).
   - `site:viewuseridentity` is required for `field=email` lookups (outside a
     course, email is only exposed through the site identity fields).
   - `webservice:createtoken` is required by `login/token.php` for any
     non-mobile service.
2. User `wssync` (password from env `WSSYNC_PASSWORD`), role assigned at
   system context.
3. Dedicated external service **`formations_sync`** (restricted users,
   wssync authorised) with functions: `core_user_get_users_by_field`,
   `core_user_create_users`, `core_user_update_users`,
   `enrol_manual_enrol_users`, `core_course_get_contents`,
   `core_completion_override_activity_completion_status`.
   The stock `moodle_mobile_app` service does NOT include the completion
   override function — tokens must be issued against `formations_sync`.

**How to run (from Tower):**

```bash
WSPASS=$(grep WSSYNC_PASSWORD ~/formations-app.env | cut -d= -f2-)
docker cp ops/provision-sync-role.php moodle:/tmp/provision-sync-role.php
docker exec -e WSSYNC_PASSWORD="$WSPASS" moodle php /tmp/provision-sync-role.php
```

### MOODLE_SYNC_TOKEN issue / renewal

```bash
WSPASS=$(grep WSSYNC_PASSWORD ~/formations-app.env | cut -d= -f2-)
TOKEN=$(curl -s "https://moodle.saillant.cc/login/token.php" \
  --data-urlencode "username=wssync" \
  --data-urlencode "password=$WSPASS" \
  --data-urlencode "service=formations_sync" \
  | python3 -c 'import json,sys; print(json.load(sys.stdin)["token"])')
sed -i "s/^MOODLE_SYNC_TOKEN=.*/MOODLE_SYNC_TOKEN=$TOKEN/" ~/formations-app.env
```

### Capability proofs (run after provisioning; both verified 2026-06-10)

```bash
TOKEN=$(grep MOODLE_SYNC_TOKEN ~/formations-app.env | cut -d= -f2-)

# 1) user lookup (must return the user, not [] nor an exception)
curl -s "https://moodle.saillant.cc/webservice/rest/server.php" \
  --data-urlencode "wstoken=$TOKEN" \
  --data-urlencode "wsfunction=core_user_get_users_by_field" \
  --data-urlencode "moodlewsrestformat=json" \
  --data-urlencode "field=username" --data-urlencode "values[0]=wsreader"

# 2) completion override on course 3's first book (cmid 27), then revert
for STATE in 1 0; do
curl -s "https://moodle.saillant.cc/webservice/rest/server.php" \
  --data-urlencode "wstoken=$TOKEN" \
  --data-urlencode "wsfunction=core_completion_override_activity_completion_status" \
  --data-urlencode "moodlewsrestformat=json" \
  --data-urlencode "cmid=27" --data-urlencode "userid=3" \
  --data-urlencode "newstate=$STATE"; echo; done
# expected: {"cmid":27,"userid":3,"state":<STATE>,...,"overrideby":<wssync id>}
```

## SP3 — Quiz bank export → Grist (`QuizBank`)

### export-quiz-bank.php (read-only)

Dumps the whole quiz question bank (all courses id > 1) as ONE JSON object
on stdout. Pure `$DB` reads, never writes. Moodle 4.5 schema chain (verified
against the live DB — `quiz_slots` no longer carries `questionid`):

```
quiz_slots.id
  → question_references (component='mod_quiz', questionarea='slot',
                         itemid = slot.id)
  → questionbankentryid
  → question_versions (MAX(version); reference.version is NULL = "always latest")
  → question (+ qtype_multichoice_options.single, question_answers)
```

`gradepass` comes from `grade_items` (`itemmodule='quiz'`,
`iteminstance=quiz.id`) and may be 0/NULL. `correct = fraction > 0` on
`question_answers`. Quizzes are ordered by name ("Quiz N : …" = module
order); `quiz_n` is parsed from the name.

### Re-export / re-push procedure (from Tower)

```bash
docker cp ops/export-quiz-bank.php moodle:/tmp/export-quiz-bank.php
docker exec moodle php /tmp/export-quiz-bank.php > /tmp/quizbank.json
set -a; . /home/clems/formations-app.env; set +a
python3 ops/push-quiz-bank.py /tmp/quizbank.json
```

`push-quiz-bank.py` is idempotent: it creates the `QuizBank` table
(AddTable apply) if missing; otherwise it deletes ALL rows and re-inserts.
Expected: 24 rows (6 courses × 4 quizzes), 5 questions per quiz.

### Grist table `QuizBank` (doc `bPfitC7Poc4r2xGRx7FgHL`)

| column | type | content |
|---|---|---|
| `course_slug` | Text | app slug (id→slug map mirrors `src/config/courses.ts`) |
| `quiz_n` | Int | 1..4, parsed from the quiz name |
| `cmid` | Int | quiz course-module id (completion override target) |
| `name` | Text | quiz name, e.g. "Quiz 1 : Découverte" |
| `gradepass` | Numeric | pass threshold in % (0 = unset → app defaults to 50 %) |
| `payload` | Text | JSON string `{"questions": [{id, text, single, choices: [{id, text, correct, feedback}]}]}` |

### ⚠️ Sanitization warning — repo is PUBLIC

The export and the Grist `payload` contain the **correct flags and the
feedback**. **Answers live ONLY in Grist** (API-key protected). Never
commit a raw export to this repository. The committed fixture
`fixtures/quizbank-3.json` (course 3, kicad-makers) is **sanitized**: every
`correct` and `feedback` key stripped recursively. Proof after any
regeneration (must print 0):

```bash
grep -c '"correct"\|"feedback"' fixtures/quizbank-3.json
```

### Grist table `QuizAttempts` (doc `bPfitC7Poc4r2xGRx7FgHL`)

Created one-shot via the Grist API (`/apply` AddTable) on 2026-06-10 — the
app never creates tables. Columns: `user_sub` Text, `email` Text,
`course_slug` Text, `quiz_n` Int, `score` Int, `total` Int, `reponses`
Text (JSON `{questionId: [choiceIds]}`), `date` DateTime (epoch seconds),
`moodle_synced` Bool. One row per submitted attempt (retakes unlimited,
best wins); same Bool-filter caveat as Progression — filter `quiz_n` /
`moodle_synced` in app code, only Text equality filters via the API.

## SP4 — Certificate issuing (`provision-certs.php` + cron)

### provision-certs.php (idempotent)

For each real course (id > 1):

1. Locks the `customcert` activity (cmids 55-60, one per course) behind
   **AND-completion of the course's 4 quizzes**:
   `course_modules.availability` =
   ```json
   {"op":"&","c":[{"type":"completion","cm":<quiz1>,"e":1},
                  {"type":"completion","cm":<quiz2>,"e":1},
                  {"type":"completion","cm":<quiz3>,"e":1},
                  {"type":"completion","cm":<quiz4>,"e":1}],
    "showc":[true,true,true,true]}
   ```
   (`e:1` = activity marked COMPLETE; quiz cmids derived from the DB in
   quiz-name order, same convention as `export-quiz-bank.php`), then
   `rebuild_course_cache($courseid, true)`. Closes the "any enrolled user
   can download the certificate" hole.
2. Sets `customcert.emailstudents = 1` (issue task emails the PDF).
3. Configures outgoing SMTP from env: `smtphosts=<SMTP_HOST>:<SMTP_PORT>`,
   `smtpsecure=tls`, `smtpuser`, `smtppass`,
   `noreplyaddress=<SMTP_FROM>`, `allowedemaildomains=''`.

### SMTP source — the iCloud 550 trap

SMTP values come from electron-server
`/home/clems/electron-rare-site/mail-api.env` (`SMTP_HOST`, `SMTP_PORT`,
`SMTP_USER`, `SMTP_PASS`) at provisioning time — never stored in this
repo. **`SMTP_FROM` (→ `noreplyaddress`) MUST be the iCloud-owned
account address (= `SMTP_USER`), NOT `contact@lelectronrare.fr`** —
iCloud 550-rejects any From the account does not own (same trap as the
lelectronrare.fr contact form). The script warns if `SMTP_FROM`
differs from `SMTP_USER`.

**How to run (from Tower):**

```bash
docker cp ops/provision-certs.php moodle:/tmp/provision-certs.php
docker exec \
  -e SMTP_HOST=... -e SMTP_PORT=587 \
  -e SMTP_USER=<icloud-address> -e SMTP_PASS=... \
  -e SMTP_FROM=<icloud-address> \
  moodle php /tmp/provision-certs.php
```

### Cron (Tower, clems crontab — installed 2026-06-11)

The restored Moodle compose is web+db only (no cron runner), so scheduled
tasks (certificate issuing/emailing) need a host crontab line:

```
*/2 * * * * docker exec moodle php /var/www/html/admin/cli/cron.php >/dev/null 2>&1
```

Idempotent re-install:

```bash
CRON_LINE='*/2 * * * * docker exec moodle php /var/www/html/admin/cli/cron.php >/dev/null 2>&1'
(crontab -l 2>/dev/null | grep -vF "admin/cli/cron.php"; echo "$CRON_LINE") | crontab -
```

The certificate task is `\mod_customcert\task\issue_certificates_task`
(this customcert version renamed the historical `email_certificate_task`;
it both issues and emails). Verify enabled:

```bash
docker exec moodle-db sh -c 'psql -U $POSTGRES_USER -d $POSTGRES_DB -c \
  "SELECT classname, disabled FROM mdl_task_scheduled WHERE classname LIKE \$\$%customcert%\$\$;"'
```

### Proof procedure (run 2026-06-11, then reverted)

1. Gate check: cron run BEFORE any override → 0 rows in
   `mdl_customcert_issues` (availability really gates; wsreader is
   enrolled everywhere with no quiz completions).
2. Override the 4 freertos quiz completions (course 5, cmids 43-46) for
   wsreader (userid 3) via `MOODLE_SYNC_TOKEN` +
   `core_completion_override_activity_completion_status` (newstate=1).
3. Cron run → `mdl_customcert_issues` row (userid 3, emailed=1) = issuance
   + email send work end-to-end.
4. Revert: same overrides with newstate=0, then
   `DELETE FROM mdl_customcert_issues WHERE userid=3;` and one more cron
   run to confirm no re-issue.

### Rollback

- Unlock certificates: `UPDATE mdl_course_modules SET availability=NULL
  WHERE id IN (55,56,57,58,59,60);` + purge caches
  (`docker exec moodle php /var/www/html/admin/cli/purge_caches.php`).
- Stop emailing: `UPDATE mdl_customcert SET emailstudents=0;` or remove
  the crontab line (`crontab -l | grep -vF "admin/cli/cron.php" | crontab -`).
- Unset SMTP: `php admin/cli/cfg.php --name=smtphosts --unset` (idem
  smtpuser/smtppass/noreplyaddress).