spb/vrai-prix

1// Auteur : Simon-Pierre Boucher — contact@spboucher.ai2/**3 * Administration de la base de coûts (serveur) : authentification par jeton,4 * état des connecteurs, anomalies, mappings, lancement en arrière-plan.5 */6import { NextResponse } from "next/server";7import type Database from "better-sqlite3";8import { getCostDb } from "../db";9import { clientKey, rateLimit } from "../cache";10import { CONNECTORS, IMPORT_ONLY, isRunnable, type RunnableKey } from "./registry";11import { isDue, runConnector } from "./run";12import type { RunStats } from "./types";1314export function adminAuth(req: Request): NextResponse | null {15  const token = process.env.COST_ADMIN_TOKEN;16  if (!token) return NextResponse.json({ error: "COST_ADMIN_TOKEN non configuré" }, { status: 503 });17  if (!rateLimit(`admin:${clientKey(req)}`, 60, 60_000)) return NextResponse.json({ error: "Trop de requêtes" }, { status: 429 });18  const h = req.headers.get("authorization") ?? "";19  const bearer = h.toLowerCase().startsWith("bearer ") ? h.slice(7).trim() : null;20  const q = new URL(req.url).searchParams.get("token");21  if (bearer !== token && q !== token) return NextResponse.json({ error: "non autorisé" }, { status: 401 });22  return null;23}2425type Row = Record<string, unknown>;2627export function connectorsStatus(d: Database.Database = getCostDb()) {28  const sources = d.prepare(`SELECT s.key, s.name, s.source_type type, s.license_status license, s.is_active active, s.priority, s.refresh_frequency frequency, s.last_successful_sync lastSync, s.last_error lastError,29      (SELECT COUNT(*) FROM cost_item_prices p WHERE p.source_id=s.id) prices,30      (SELECT COUNT(*) FROM cost_item_prices p WHERE p.source_id=s.id AND p.observation_date >= date('now','-7 days')) pricesWeek,31      (SELECT COUNT(DISTINCT cost_item_id) FROM cost_item_prices p WHERE p.source_id=s.id AND p.observation_date >= date('now','-120 days')) itemsRecent,32      (SELECT COUNT(*) FROM labour_rates l WHERE l.source_id=s.id) labour,33      (SELECT COUNT(*) FROM cost_raw_observations o WHERE o.source_id=s.id) raw,34      (SELECT COUNT(*) FROM cost_raw_observations o WHERE o.source_id=s.id AND o.status='rejected') rejected,35      (SELECT COUNT(*) FROM cost_item_sources c WHERE c.source_id=s.id AND c.active=1) mappedItems,36      (SELECT COUNT(*) FROM product_mappings m WHERE m.source_id=s.id AND m.status='pending') pendingMappings37    FROM cost_sources s ORDER BY s.priority, s.name`).all() as Row[];38  const runs = d.prepare("SELECT id, connector, status, started_at startedAt, finished_at finishedAt, pages, observations, accepted, rejected, unchanged, errors, duration_ms durationMs FROM connector_runs ORDER BY started_at DESC LIMIT 40").all() as Row[];39  const indices = d.prepare("SELECT index_code code, geography, building_type buildingType, division, MAX(period) last, COUNT(*) n, MAX(retrieved_at) retrievedAt FROM construction_cost_indices GROUP BY index_code ORDER BY index_code").all() as Row[];40  const running = [...RUNNING.entries()].map(([k, v]) => ({ connector: k, runId: v.runId, startedAt: v.startedAt }));41  return {42    sources: sources.map((s) => ({ ...s, runnable: isRunnable(String(s.key)), importOnly: IMPORT_ONLY.includes(s.key as never), due: isRunnable(String(s.key)) ? isDue(s.key as RunnableKey, d) : false, refreshDays: isRunnable(String(s.key)) ? CONNECTORS[s.key as RunnableKey].config.refreshDays : null })),43    runs, indices, running,44    metrics: {45      syncSuccessRate: successRate(d), observationsPerSource: Object.fromEntries(sources.map((s) => [String(s.key), Number(s.prices) + Number(s.labour)])),46      ageOfLatestPriceDays: ageDays((d.prepare("SELECT MAX(observation_date) m FROM cost_item_prices WHERE price_kind IN ('observed','official')").get() as { m: string | null }).m),47      mappingFailureRate: mappingFailureRate(d), outlierRate: outlierRate(d),48      aiUsage: d.prepare("SELECT purpose, COUNT(*) n, SUM(input_tokens) input, SUM(output_tokens) output, ROUND(SUM(estimated_cost_usd),4) usd FROM ai_usage GROUP BY purpose").all(),49    },50  };51}5253function successRate(d: Database.Database): number | null {54  const r = d.prepare("SELECT SUM(CASE WHEN status IN ('ok','partial') THEN 1 ELSE 0 END) ok, COUNT(*) n FROM connector_runs WHERE started_at >= datetime('now','-30 days') AND status != 'running'").get() as { ok: number | null; n: number };55  return r.n ? Math.round((100 * (r.ok ?? 0)) / r.n) : null;56}57function mappingFailureRate(d: Database.Database): number | null {58  const r = d.prepare("SELECT SUM(CASE WHEN status IN ('pending','rejected') THEN 1 ELSE 0 END) bad, COUNT(*) n FROM product_mappings").get() as { bad: number | null; n: number };59  return r.n ? Math.round((100 * (r.bad ?? 0)) / r.n) : null;60}61function outlierRate(d: Database.Database): number | null {62  const r = d.prepare("SELECT (SELECT COUNT(*) FROM cost_raw_observations WHERE status='rejected' AND reject_reason LIKE 'aberration%') a, (SELECT COUNT(*) FROM cost_item_prices) n").get() as { a: number; n: number };63  return r.n ? Math.round((1000 * r.a) / (r.n + r.a)) / 10 : null;64}65function ageDays(iso: string | null): number | null {66  return iso ? Math.round((Date.now() - new Date(iso).getTime()) / 86400000) : null;67}6869export function anomalies(d: Database.Database = getCostDb()) {70  const outliers = d.prepare(`SELECT o.id, s.name source, o.source_url url, o.raw_title title, o.raw_price price, o.raw_unit unit, o.retrieved_at retrievedAt, o.reject_reason reason FROM cost_raw_observations o JOIN cost_sources s ON s.id=o.source_id WHERE o.status='rejected' ORDER BY o.retrieved_at DESC LIMIT 100`).all();71  const pending = d.prepare(`SELECT m.id, s.name source, m.product_url url, m.product_title title, m.proposed_item_code itemCode, m.method, m.confidence, m.rationale, m.pack_qty packQty, m.created_at createdAt FROM product_mappings m JOIN cost_sources s ON s.id=m.source_id WHERE m.status='pending' ORDER BY m.created_at DESC LIMIT 200`).all();72  const stale = d.prepare(`SELECT s.key, s.name, s.last_successful_sync lastSync FROM cost_sources s WHERE s.is_active=1 AND s.source_type IN ('retail','labour','index') AND (s.last_successful_sync IS NULL OR s.last_successful_sync < datetime('now','-14 days'))`).all();73  // écarts > 30 % entre deux observations consécutives d'un même (article, source)74  const jumps = d.prepare(`SELECT i.canonical_code code, s.name source, p1.observation_date d1, p1.total_cost v1, p2.observation_date d2, p2.total_cost v2, ROUND(100.0*(p2.total_cost/p1.total_cost-1),1) pct75    FROM cost_item_prices p2 JOIN cost_item_prices p1 ON p1.cost_item_id=p2.cost_item_id AND p1.source_id=p2.source_id AND p1.observation_date = (SELECT MAX(observation_date) FROM cost_item_prices x WHERE x.cost_item_id=p2.cost_item_id AND x.source_id=p2.source_id AND x.observation_date < p2.observation_date)76    JOIN cost_items i ON i.id=p2.cost_item_id JOIN cost_sources s ON s.id=p2.source_id77    WHERE ABS(p2.total_cost/p1.total_cost-1) > 0.3 ORDER BY p2.observation_date DESC LIMIT 100`).all();78  const noPrice = d.prepare(`SELECT i.canonical_code code, i.name_fr name, i.reference_price ref FROM cost_items i WHERE i.active=1 AND i.retail_query IS NOT NULL AND NOT EXISTS (SELECT 1 FROM cost_item_prices p WHERE p.cost_item_id=i.id AND p.observation_date >= date('now','-120 days')) ORDER BY i.canonical_code`).all();79  return { outliers, pendingMappings: pending, staleSources: stale, priceJumps: jumps, itemsWithoutObservation: noPrice };80}8182/* ------------------------------------------------ lancement en arrière-plan */8384const RUNNING = new Map<RunnableKey, { runId: number; startedAt: string; promise: Promise<RunStats> }>();8586/** Démarre un connecteur sans bloquer la requête ; renvoie tout de suite l'identifiant du run. */87export function launchConnector(key: RunnableKey, force: boolean, onlyItems?: string[]): { started: boolean; runId: number | null; message: string } {88  if (RUNNING.has(key)) return { started: false, runId: RUNNING.get(key)!.runId, message: "déjà en cours" };89  const d = getCostDb();90  const before = (d.prepare("SELECT COALESCE(MAX(id),0) m FROM connector_runs").get() as { m: number }).m;91  const promise = runConnector(key, { force, onlyItems, db: d }).finally(() => RUNNING.delete(key));92  RUNNING.set(key, { runId: before + 1, startedAt: new Date().toISOString(), promise });93  return { started: true, runId: before + 1, message: "démarré en arrière-plan" };94}95