backend/process-appel.mjs

1// process-appel — fires once per newly-inserted appel row. Invoked by the
2// `process-appel` workflow (triggered by tables.row.inserted on appels).
3//
4// Flow:
5//   1. Hydrate the appel (rows_get).
6//   2. Gate: skip if status != pending_transcript or no mirrored audio.
7//   3. For each audio_file (in order), get a signed storage URL.
8//   4. Transcribe each audio part via Deepgram (context.integration).
9//   5. Concatenate transcript pieces, save transcript_text, → pending_eval.
10//   6. Look up linked ventes (for writeback) + grille + criteres (for eval).
11//   7. Evaluate via Claude Haiku 4.5 using the inlined `evaluer-appel` skill
12//      as system prompt + the rendered grille. Parse the structured rapport
13//      back into evaluations[] + score_total/score_max.
14//   8. Insert the rapport row.
15//   9. Write Score + Rapport URL back to the originating Google Sheet (per
16//      sheet_mappings[*].tabs[*].writeback). Mode = 'fill-blanks' by default
17//      so human entries in those columns are never overwritten. Best-effort:
18//      sheet API failures are logged but don't fail the run.
19//   10. Flip appel.status → done.
20
21import { Composio } from '@composio/core';
22
23// ─── Composio ────────────────────────────────────────────────────────
24const composio = new Composio({
25  apiKey: 'ak_cpsw-4oewu9zim8vwS6K',
26  toolkitVersions: {
27    googlesheets: '20260506_00',
28    googledocs:   '20260506_00',
29    googledrive:  '20260506_00',
30  },
31});
32const COMPOSIO_USER = 'pg-test-2M6ZsbRqWTRFywqEAdRixc5ID9Ph7YaV';
33async function execComposio(slug, args) {
34  const r = await composio.tools.execute(slug, { userId: COMPOSIO_USER, arguments: args });
35  if (!r.successful) throw new Error(slug + ': ' + r.error);
36  return r.data;
37}
38
39// ─── Constants (override at deploy time if needed) ───────────────────
40const RAPPORT_URL_BASE = 'https://qualite.flexylead.com/appels/';
41// Default FILL-BLANKS: writes Score + Rapport URL only into empty cells,
42// never clobbers human entries or older URLs. Override per-invoke with
43// {"writeback_mode": "overwrite"} to refresh stale links, or "dry-run" to
44// simulate without touching sheets.
45const WRITEBACK_MODE_DEFAULT = 'fill-blanks';   // 'fill-blanks' | 'overwrite' | 'dry-run'
46
47// Per-grille Drive folder for Google Doc rapports. Mirrors the layout the
48// original lib/upload_rapport.js used — everything lives under ZC CALL's
49// drive, organized by criteres_grille_id. Grilles not in this map fall back
50// to the dashboard URL on the sheet (no Doc created).
51const GRILLE_TO_DRIVE_PATH = {
52  mint_energie_v1: '/ZC CALL - Mint Énergie - Enregistrements Ventes/Rapports/MINT ENERGIE',
53  ohm_v1:          '/ZC CALL - Mint Énergie - Enregistrements Ventes/Rapports/OHM',
54  free_v1:         '/ZC CALL - Mint Énergie - Enregistrements Ventes/Rapports/FREE',
55};
···
1214  }
1215
1216  // 2. Trailing score table: CODE \t name \t score \t bareme
1217  // Only accept rows whose CODE matches an actual critère — otherwise TOTAL
1218  // rows or stray tab-aligned text get summed into score_max (saw 200/100
1219  // in the wild before this filter, 2026-05-20).
1220  const tableRe = /^([A-Za-z0-9\-]+)\t([^\t]*)\t([0-9]+(?:[.,][0-9]+)?)\t([0-9]+(?:[.,][0-9]+)?)\s*$/gm;
1221  while ((m = tableRe.exec(text)) !== null) {
1222    const [, code, , scoreStr, baremeStr] = m;
1223    if (!byCode.has(code)) continue;
1224    const score = Number(scoreStr.replace(',', '.'));
1225    const bareme = Number(baremeStr.replace(',', '.'));
1226    if (evaluations[code]) {
1227      evaluations[code].score = score;
1228      evaluations[code].bareme = bareme;
1229    } else {
1230      const c = byCode.get(code);
1231      evaluations[code] = {
1232        critere_code: code, categorie: c.categorie ?? null, libelle: c.libelle ?? null,
1233        bareme, verdict: null, score, applicable: true, commentaire: '',
1234      };
1235    }
1236  }
1237
1238  // 3. Reconcile verdict ↔ score for every evaluation. Verdict-authoritative.
1239  for (const code of Object.keys(evaluations)) reconcileEvalScore(evaluations[code]);
1240
1241  // 4. Compute totals from reconciled evaluations + grille (single source of
1242  //    truth for score_max — avoids accidental double-counting from stray
1243  //    table rows like TOTAL).
1244  const score_total = Object.values(evaluations).reduce((s, e) => s + (Number(e.score) || 0), 0);
1245  const score_max = criteres.reduce((s, c) => s + (Number(c.bareme) || 0), 0);
1246  return { evaluations: Object.values(evaluations), score_total, score_max };
1247}
1248
1249// ─── writeBackToSheet ────────────────────────────────────────────────
1250// Pushes evaluation results back to the originating sheets. Idempotent and
1251// best-effort: never throws (caller wraps in try/catch and logs).
1252async function writeBackToSheet(appel, rapport, ventes, mode) {
1253  const result = {
1254    mode,
1255    groups: 0,
1256    candidates: 0,
1257    writes: 0,
1258    already_ok: 0,
1259    skipped_non_empty: 0,
1260    missing_column: 0,
1261    errors: 0,
1262    error_samples: [],
1263  };
1264
1265  // 1. Group target rows by (spreadsheet_id, tab). Only ventes that came
1266  //    from a sheet with a 'writeback' block are eligible.
1267  const groups = new Map();
1268  for (const v of ventes) {
1269    const src = v.source || {};
1270    if (!src.spreadsheet_id || !src.tab || !src.row_number) continue;
1271    const sheetCfg = SHEET_MAPPINGS[src.spreadsheet_id];
1272    if (!sheetCfg?.tabs) continue;
1273    const tabCfg = sheetCfg.tabs[src.tab];
1274    if (!tabCfg?.writeback) continue;
1275
1276    const cells = {};