spb/vrai-prix

1// Auteur : Simon-Pierre Boucher — contact@spboucher.ai2/**3 * Correspondance produit détaillant → article canonique.4 *   produit brut → normalisation déterministe → (si ambigu) classification par5 *   Claude Haiku 4.5 → confiance → product_mappings → cost_item_sources6 * L'IA ne fixe JAMAIS un prix : elle choisit au plus un candidat parmi les7 * résultats de recherche et explique son choix.8 */9import Anthropic from "@anthropic-ai/sdk";10import type Database from "better-sqlite3";11import { getCostDb, sourceIdByKey } from "../db";12import type { ConnectorKey } from "./types";1314export const MATCH_AUTO_APPROVE = 0.8;15const MODEL = "claude-haiku-4-5";1617export const fold = (s: string) => s.replace(/œ/g, "oe").normalize("NFD").replace(/[̀-ͯ]/g, "").toLowerCase().replace(/[×x]/g, " x ").replace(/["'’]/g, " ").replace(/[^a-z0-9./\s]/g, " ").replace(/\s+/g, " ").trim();1819/** Jetons : dimensions (2, 6, 8, 1/2), unités (po, pi, pi², mm) et mots ≥ 3 lettres (sans mots vides). */20export function tokens(s: string): { nums: string[]; words: string[] } {21  const f = fold(s).replace(/(\d)\s*po\b/g, "$1 po").replace(/(\d)\s*pi\b/g, "$1 pi");22  const nums = [...f.matchAll(/\b\d+(?:[./]\d+)?\b/g)].map((m) => m[0]);23  const STOP = new Set(["de", "des", "du", "la", "le", "les", "et", "en", "pour", "avec", "sans", "par", "sur", "au", "aux", "un", "une", "the", "and", "of", "for", "with", "sec", "kd"]);24  const words = f.split(" ").filter((w) => /^[a-z]{3,}$/.test(w) && !STOP.has(w));25  return { nums, words };26}2728/** Score déterministe 0-1 : tous les nombres de la requête doivent apparaître ; puis recouvrement des mots. */29export function scoreCandidate(query: string, title: string): number {30  const q = tokens(query);31  const t = tokens(title);32  if (!t.words.length && !t.nums.length) return 0;33  const numOk = q.nums.every((n) => t.nums.includes(n));34  if (q.nums.length && !numOk) return Math.min(0.35, wordOverlap(q.words, t.words) * 0.5);35  const wo = wordOverlap(q.words, t.words);36  return Math.min(1, (q.nums.length ? 0.45 : 0) + wo * (q.nums.length ? 0.55 : 1));37}3839function wordOverlap(a: string[], b: string[]): number {40  if (!a.length) return 1;41  const bs = new Set(b.flatMap((w) => [w, w.replace(/s$/, ""), w.slice(0, 5)]));42  const hit = a.filter((w) => bs.has(w) || bs.has(w.replace(/s$/, "")) || bs.has(w.slice(0, 5))).length;43  return hit / a.length;44}4546export interface Candidate { url: string; title: string; description: string }47export interface MatchDecision { candidate: Candidate | null; confidence: number; method: "deterministic" | "ai" | "none"; rationale: string }4849/** Détaillant : mots qui trahissent un produit non comparable (accessoire, lot, service). */50const BAD_WORDS = /\b(ensemble de|kit de|lot de|échantillon|echantillon|service|installation|location|garantie|carte cadeau|pièce de rechange)\b/i;5152export function deterministicMatch(query: string, candidates: Candidate[]): MatchDecision {53  const scored = candidates.filter((c) => !BAD_WORDS.test(c.title)).map((c) => ({ c, s: scoreCandidate(query, c.title) })).sort((a, b) => b.s - a.s);54  if (!scored.length) return { candidate: null, confidence: 0, method: "none", rationale: "aucun candidat" };55  const best = scored[0];56  const margin = scored.length > 1 ? best.s - scored[1].s : best.s;57  if (best.s >= 0.85 && margin >= 0.1) return { candidate: best.c, confidence: Math.min(0.97, best.s), method: "deterministic", rationale: `score ${best.s.toFixed(2)}, marge ${margin.toFixed(2)}` };58  if (best.s < 0.35) return { candidate: null, confidence: best.s, method: "none", rationale: `meilleur score ${best.s.toFixed(2)} trop faible` };59  return { candidate: best.c, confidence: best.s, method: "deterministic", rationale: `ambigu : score ${best.s.toFixed(2)}, marge ${margin.toFixed(2)}` };60}6162const TOOL: Anthropic.Tool = {63  name: "choose_product",64  description: "Choisit le produit du détaillant qui correspond à l'article canonique demandé, ou aucun.",65  strict: true,66  input_schema: {67    type: "object", additionalProperties: false,68    properties: {69      index: { type: ["integer", "null"], description: "Indice (0-based) du candidat retenu, null si aucun ne correspond au même produit économique (mêmes dimensions, même matériau, même usage)" },70      confidence: { type: "number", description: "0-1" },71      pack_qty: { type: ["number", "null"], description: "Contenu de l'emballage dans l'unité canonique si le titre l'indique (ex. feuille 4x8 → 32 pi²), sinon null" },72      rationale: { type: "string" },73    },74    required: ["index", "confidence", "pack_qty", "rationale"],75  },76};7778export async function aiMatch(itemLabel: string, unit: string, query: string, candidates: Candidate[], d: Database.Database = getCostDb()): Promise<MatchDecision & { packQty: number | null }> {79  const client = new Anthropic();80  const t0 = Date.now();81  const list = candidates.map((c, i) => `${i}. ${c.title}\n   ${c.description.slice(0, 160)}\n   ${c.url}`).join("\n");82  const res = await client.messages.create({83    model: MODEL, max_tokens: 400,84    system: "Tu classes des produits de quincaillerie. Tu ne donnes jamais de prix. Tu choisis le candidat qui est le MÊME produit économique que l'article canonique (dimensions nominales identiques, matériau et usage équivalents ; la marque n'importe pas). En cas de doute réel, index = null.",85    tools: [TOOL], tool_choice: { type: "tool", name: "choose_product" },86    messages: [{ role: "user", content: `Article canonique : « ${itemLabel} » (unité ${unit}). Requête : « ${query} ».\nCandidats :\n${list}` }],87  });88  try {89    d.prepare("INSERT INTO ai_usage(analysis_id,purpose,model,input_images,input_tokens,output_tokens,cache_read_tokens,estimated_cost_usd,latency_ms) VALUES(NULL,'product_matching',?,0,?,?,?,?,?)")90      .run(MODEL, res.usage.input_tokens, res.usage.output_tokens, res.usage.cache_read_input_tokens ?? 0, (res.usage.input_tokens * 1 + res.usage.output_tokens * 5) / 1e6, Date.now() - t0);91  } catch { /* test */ }92  const tu = res.content.find((b): b is Anthropic.ToolUseBlock => b.type === "tool_use");93  const inp = (tu?.input ?? {}) as { index?: number | null; confidence?: number; pack_qty?: number | null; rationale?: string };94  const idx = typeof inp.index === "number" && inp.index >= 0 && inp.index < candidates.length ? inp.index : null;95  return { candidate: idx == null ? null : candidates[idx], confidence: Math.max(0, Math.min(1, Number(inp.confidence) || 0)), method: "ai", rationale: `IA : ${inp.rationale ?? ""}`.slice(0, 500), packQty: typeof inp.pack_qty === "number" && inp.pack_qty > 0 ? inp.pack_qty : null };96}9798/* ------------------------------------------------------------ persistance */99100export function upsertMapping(connector: ConnectorKey, itemCode: string, dec: MatchDecision, packQty: number | null, packUnit: string | null, d: Database.Database = getCostDb()): "approved" | "pending" | "rejected" {101  const sid = sourceIdByKey(connector, d);102  if (!dec.candidate) return "rejected";103  const status = dec.confidence >= MATCH_AUTO_APPROVE ? "auto_approved" : "pending";104  d.prepare(`INSERT INTO product_mappings(source_id,product_url,product_title,proposed_item_code,pack_qty,pack_unit,method,confidence,rationale,status)105    VALUES(?,?,?,?,?,?,?,?,?,?) ON CONFLICT(source_id,product_url) DO UPDATE SET product_title=excluded.product_title, proposed_item_code=excluded.proposed_item_code, pack_qty=excluded.pack_qty, pack_unit=excluded.pack_unit,106      method=excluded.method, confidence=excluded.confidence, rationale=excluded.rationale, status=CASE WHEN product_mappings.status IN ('approved','rejected') THEN product_mappings.status ELSE excluded.status END`)107    .run(sid, dec.candidate.url, dec.candidate.title, itemCode, packQty, packUnit, dec.method, dec.confidence, dec.rationale, status);108  const row = d.prepare("SELECT status FROM product_mappings WHERE source_id=? AND product_url=?").get(sid, dec.candidate.url) as { status: string };109  if (row.status === "approved" || row.status === "auto_approved") {110    const iid = (d.prepare("SELECT id FROM cost_items WHERE canonical_code=?").get(itemCode) as { id: number } | undefined)?.id;111    if (iid) d.prepare(`INSERT INTO cost_item_sources(cost_item_id,source_id,product_url,product_title,pack_qty,pack_unit,match_method,match_confidence,approved,active) VALUES(?,?,?,?,?,?,?,?,1,1)112      ON CONFLICT(source_id,product_url) DO UPDATE SET cost_item_id=excluded.cost_item_id, product_title=excluded.product_title, pack_qty=COALESCE(excluded.pack_qty, cost_item_sources.pack_qty), pack_unit=COALESCE(excluded.pack_unit, cost_item_sources.pack_unit), match_confidence=excluded.match_confidence, approved=1, active=1`)113      .run(iid, sid, dec.candidate.url, dec.candidate.title, packQty, packUnit, dec.method, dec.confidence);114    return "approved";115  }116  return row.status === "rejected" ? "rejected" : "pending";117}118119/** Approuve/rejette un mapping (admin) et synchronise cost_item_sources. */120export function reviewMapping(id: number, decision: "approved" | "rejected", d: Database.Database = getCostDb()): boolean {121  const m = d.prepare("SELECT * FROM product_mappings WHERE id=?").get(id) as { id: number; source_id: number; product_url: string; product_title: string | null; proposed_item_code: string | null; pack_qty: number | null; pack_unit: string | null; method: string; confidence: number } | undefined;122  if (!m) return false;123  d.prepare("UPDATE product_mappings SET status=?, reviewed_at=datetime('now') WHERE id=?").run(decision, id);124  if (decision === "approved" && m.proposed_item_code) {125    const iid = (d.prepare("SELECT id FROM cost_items WHERE canonical_code=?").get(m.proposed_item_code) as { id: number } | undefined)?.id;126    if (iid) d.prepare(`INSERT INTO cost_item_sources(cost_item_id,source_id,product_url,product_title,pack_qty,pack_unit,match_method,match_confidence,approved,active) VALUES(?,?,?,?,?,?,?,?,1,1)127      ON CONFLICT(source_id,product_url) DO UPDATE SET cost_item_id=excluded.cost_item_id, approved=1, active=1`).run(iid, m.source_id, m.product_url, m.product_title, m.pack_qty, m.pack_unit, m.method, m.confidence);128  } else {129    d.prepare("UPDATE cost_item_sources SET active=0, approved=0 WHERE source_id=? AND product_url=?").run(m.source_id, m.product_url);130  }131  return true;132}133