spb/vrai-prix

1// Auteur : Simon-Pierre Boucher — contact@spboucher.ai2/**3 * Persistance de l'analyse IA des annonces (SERVEUR) : analyses versionnées4 * (jamais écrasées), images, overrides, conflits, vecteurs, usage API.5 * Colonnes additionnelles ajoutées par `ensureAiSchema` (ALTER tolérant) —6 * db.ts n'est pas modifié.7 */8import type Database from "better-sqlite3";9import { getCostDb } from "../db";10import type { Conflict } from "./merge";1112let ensured = false;13export function ensureAiSchema(d: Database.Database = getCostDb()): Database.Database {14  if (ensured) return d;15  const cols: [string, string][] = [16    ["listing_ai_analyses", "merged_json TEXT"], ["listing_ai_analyses", "mapping_json TEXT"], ["listing_ai_analyses", "usage_json TEXT"], ["listing_ai_analyses", "issues_json TEXT"],17    ["listing_ai_analyses", "images_json TEXT"], ["listing_ai_analyses", "listing_json TEXT"], ["listing_ai_analyses", "combined_json TEXT"], ["listing_ai_analyses", "started_at TEXT"], ["listing_ai_analyses", "merged_base_json TEXT"],18    ["listing_images", "analysis_id TEXT"],19  ];20  for (const [t, c] of cols) { try { d.exec(`ALTER TABLE ${t} ADD COLUMN ${c}`); } catch { /* déjà présent */ } }21  d.exec("CREATE INDEX IF NOT EXISTS idx_ai_usage_created ON ai_usage(created_at)");22  ensured = true;23  return d;24}2526export type AnalysisStatus = "queued" | "fetching_images" | "analyzing" | "validating" | "embedding" | "mapping_assemblies" | "pricing" | "completed" | "failed";27export const STAGES: AnalysisStatus[] = ["queued", "fetching_images", "analyzing", "validating", "embedding", "mapping_assemblies", "pricing", "completed"];2829export interface AnalysisRow {30  id: string; listing_uid: string; version: number; model: string; prompt_version: string; schema_version: string; input_hash: string; status: AnalysisStatus; stage: string | null; error: string | null;31  image_count: number; output_json: string | null; compact_json: string | null; vector_json: string | null; confidence: number | null; estimate_id: string | null; cost_snapshot_date: string | null;32  created_at: string; completed_at: string | null; merged_json: string | null; merged_base_json: string | null; mapping_json: string | null; usage_json: string | null; issues_json: string | null; images_json: string | null; listing_json: string | null; combined_json: string | null; started_at: string | null;33}3435export function db(): Database.Database { return ensureAiSchema(getCostDb()); }3637export function findCompleted(listingUid: string, inputHash: string, promptVersion: string, model: string): AnalysisRow | null {38  return (db().prepare("SELECT * FROM listing_ai_analyses WHERE listing_uid=? AND input_hash=? AND prompt_version=? AND model=? AND status='completed' ORDER BY version DESC LIMIT 1").get(listingUid, inputHash, promptVersion, model) as AnalysisRow | undefined) ?? null;39}40export function findActive(listingUid: string): AnalysisRow | null {41  return (db().prepare("SELECT * FROM listing_ai_analyses WHERE listing_uid=? AND status NOT IN ('completed','failed') ORDER BY version DESC LIMIT 1").get(listingUid) as AnalysisRow | undefined) ?? null;42}43export function latestForListing(listingUid: string): AnalysisRow | null {44  return (db().prepare("SELECT * FROM listing_ai_analyses WHERE listing_uid=? ORDER BY version DESC LIMIT 1").get(listingUid) as AnalysisRow | undefined) ?? null;45}46export function latestCompletedForListing(listingUid: string): AnalysisRow | null {47  return (db().prepare("SELECT * FROM listing_ai_analyses WHERE listing_uid=? AND status='completed' ORDER BY version DESC LIMIT 1").get(listingUid) as AnalysisRow | undefined) ?? null;48}49export function getAnalysis(id: string): AnalysisRow | null {50  return (db().prepare("SELECT * FROM listing_ai_analyses WHERE id=?").get(id) as AnalysisRow | undefined) ?? null;51}52export function listVersions(listingUid: string): { id: string; version: number; status: string; created_at: string; confidence: number | null; model: string; prompt_version: string }[] {53  return db().prepare("SELECT id, version, status, created_at, confidence, model, prompt_version FROM listing_ai_analyses WHERE listing_uid=? ORDER BY version DESC").all(listingUid) as { id: string; version: number; status: string; created_at: string; confidence: number | null; model: string; prompt_version: string }[];54}55/** Analyses complètes par annonce (badges de cartes) — une requête pour une liste d'uids. */56export function completedSummaries(uids: string[]): Map<string, { analysisId: string; rcn: number | null; confidence: number | null }> {57  const out = new Map<string, { analysisId: string; rcn: number | null; confidence: number | null }>();58  if (!uids.length) return out;59  const d = db();60  const rows = d.prepare(`SELECT a.listing_uid, a.id, a.confidence, e.replacement_cost_new rcn FROM listing_ai_analyses a LEFT JOIN cost_estimates e ON e.id=a.estimate_id WHERE a.status='completed' AND a.listing_uid IN (${uids.map(() => "?").join(",")}) ORDER BY a.version ASC`).all(...uids) as { listing_uid: string; id: string; confidence: number | null; rcn: number | null }[];61  for (const r of rows) out.set(r.listing_uid, { analysisId: r.id, rcn: r.rcn, confidence: r.confidence });62  return out;63}6465export function createAnalysis(a: { id: string; listingUid: string; model: string; promptVersion: string; schemaVersion: string; inputHash: string; listingJson: string }): AnalysisRow {66  const d = db();67  const v = (d.prepare("SELECT COALESCE(MAX(version),0)+1 v FROM listing_ai_analyses WHERE listing_uid=?").get(a.listingUid) as { v: number }).v;68  d.prepare("INSERT INTO listing_ai_analyses(id,listing_uid,version,model,prompt_version,schema_version,input_hash,status,stage,listing_json,started_at) VALUES(?,?,?,?,?,?,?,'queued','queued',?,datetime('now'))").run(a.id, a.listingUid, v, a.model, a.promptVersion, a.schemaVersion, a.inputHash, a.listingJson);69  return getAnalysis(a.id)!;70}71export function setStage(id: string, status: AnalysisStatus): void {72  db().prepare("UPDATE listing_ai_analyses SET status=?, stage=? WHERE id=?").run(status, status, id);73}74export function setFailed(id: string, error: string): void {75  db().prepare("UPDATE listing_ai_analyses SET status='failed', stage='failed', error=?, completed_at=datetime('now') WHERE id=?").run(error.slice(0, 2000), id);76}77/**78 * Analyses orphelines : une analyse tourne dans le processus du serveur ; un79 * redémarrage (pm2 restart, déploiement) l'interrompt sans transition vers80 * `failed`, et l'interface resterait sur « analyse en cours » indéfiniment.81 * Toute analyse non terminale plus vieille que `maxAgeMin` est marquée échouée82 * (les plus longues observées : ≈ 2 min pour 27-36 photos). Renvoie le nombre corrigé.83 */84export function failOrphans(maxAgeMin = 20): number {85  const r = db().prepare(86    "UPDATE listing_ai_analyses SET status='failed', stage='failed', error=?, completed_at=datetime('now') WHERE status NOT IN ('completed','failed') AND COALESCE(started_at, created_at) < datetime('now', ?)",87  ).run("Analyse interrompue (redémarrage du serveur pendant le traitement) — relancez l'analyse.", `-${Math.max(1, Math.round(maxAgeMin))} minutes`);88  return r.changes;89}90export function saveOutput(id: string, o: { outputJson: string; imageCount: number; usageJson: string; issuesJson: string; imagesJson: string }): void {91  db().prepare("UPDATE listing_ai_analyses SET output_json=?, image_count=?, usage_json=?, issues_json=?, images_json=? WHERE id=?").run(o.outputJson, o.imageCount, o.usageJson, o.issuesJson, o.imagesJson, id);92}93/** Faits fusionnés de BASE (avant corrections manuelles) + vecteur. */94export function saveMergedAndVector(id: string, mergedJson: string, vectorJson: string): void {95  db().prepare("UPDATE listing_ai_analyses SET merged_base_json=?, merged_json=?, vector_json=? WHERE id=?").run(mergedJson, mergedJson, vectorJson, id);96}97export function saveMergedBase(id: string, mergedJson: string): void {98  db().prepare("UPDATE listing_ai_analyses SET merged_base_json=? WHERE id=?").run(mergedJson, id);99}100/** Faits EFFECTIFS (corrections appliquées) tels que présentés et chiffrés. */101export function saveMergedEffective(id: string, mergedJson: string): void {102  db().prepare("UPDATE listing_ai_analyses SET merged_json=? WHERE id=?").run(mergedJson, id);103}104export function saveMappingAndEstimate(id: string, o: { mappingJson: string; compactJson: string; estimateId: string; snapshotDate: string; combinedJson: string; confidence: number; completed: boolean }): void {105  db().prepare(`UPDATE listing_ai_analyses SET mapping_json=?, compact_json=?, estimate_id=?, cost_snapshot_date=?, combined_json=?, confidence=?${o.completed ? ", status='completed', stage='completed', completed_at=datetime('now')" : ""} WHERE id=?`)106    .run(o.mappingJson, o.compactJson, o.estimateId, o.snapshotDate, o.combinedJson, o.confidence, id);107}108109/** Hashes déjà connus des photos d'une annonce (URL → hash) — permet de recalculer l'empreinte sans retélécharger. */110export function knownImageHashes(listingUid: string): Map<string, string> {111  return new Map((db().prepare("SELECT source_url, hash FROM listing_images WHERE listing_uid=? AND hash IS NOT NULL").all(listingUid) as { source_url: string; hash: string }[]).map((r) => [r.source_url, r.hash]));112}113114export function saveImages(listingUid: string, analysisId: string, imgs: { id: string; sourceUrl: string; position: number; width: number | null; height: number | null; bytes: number; hash: string; mediaType: string; roomHint: string; qualityScore: number; selected: boolean }[]): void {115  const d = db();116  const up = d.prepare(`INSERT INTO listing_images(listing_uid,photo_id,source_url,position,width,height,bytes,hash,media_type,room_guess,quality_score,selected_for_ai,fetched_at,analysis_id) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,datetime('now'),?)117    ON CONFLICT(listing_uid, source_url) DO UPDATE SET photo_id=excluded.photo_id, width=excluded.width, height=excluded.height, bytes=excluded.bytes, hash=excluded.hash, media_type=excluded.media_type, room_guess=excluded.room_guess, quality_score=excluded.quality_score, selected_for_ai=excluded.selected_for_ai, fetched_at=excluded.fetched_at, analysis_id=excluded.analysis_id`);118  const tx = d.transaction(() => { for (const i of imgs) up.run(listingUid, i.id, i.sourceUrl, i.position, i.width, i.height, i.bytes, i.hash, i.mediaType, i.roomHint, i.qualityScore, i.selected ? 1 : 0, analysisId); });119  tx();120}121122export function saveConflicts(analysisId: string, conflicts: Conflict[]): void {123  const d = db();124  d.prepare("DELETE FROM analysis_conflicts WHERE analysis_id=?").run(analysisId);125  const ins = d.prepare("INSERT INTO analysis_conflicts(analysis_id,field,source_a,value_a,source_b,value_b,severity,status) VALUES(?,?,?,?,?,?,?,'open')");126  const tx = d.transaction(() => { for (const c of conflicts) ins.run(analysisId, c.field, c.sourceA, c.valueA, c.sourceB, c.valueB, c.severity); });127  tx();128}129export function listConflicts(analysisId: string): (Conflict & { id: number; status: string })[] {130  return (db().prepare("SELECT id, field, source_a, value_a, source_b, value_b, severity, status FROM analysis_conflicts WHERE analysis_id=?").all(analysisId) as { id: number; field: string; source_a: string; value_a: string; source_b: string; value_b: string; severity: string; status: string }[])131    .map((r) => ({ id: r.id, field: r.field, sourceA: r.source_a as Conflict["sourceA"], valueA: r.value_a, sourceB: r.source_b as Conflict["sourceB"], valueB: r.value_b, severity: r.severity as Conflict["severity"], status: r.status }));132}133134export function saveEmbedding(listingUid: string, analysisId: string, model: string, vector: number[], text: string): void {135  db().prepare("INSERT INTO property_embeddings(listing_uid,analysis_id,embedding_model,embedding_dimension,embedding_json,canonical_text) VALUES(?,?,?,?,?,?) ON CONFLICT(analysis_id, embedding_model) DO UPDATE SET embedding_json=excluded.embedding_json, canonical_text=excluded.canonical_text, embedding_dimension=excluded.embedding_dimension")136    .run(listingUid, analysisId, model, vector.length, JSON.stringify(vector), text);137}138export function allEmbeddings(model: string): { listingUid: string; analysisId: string; vector: number[]; text: string | null }[] {139  return (db().prepare(`SELECT e.listing_uid, e.analysis_id, e.embedding_json, e.canonical_text FROM property_embeddings e JOIN listing_ai_analyses a ON a.id=e.analysis_id WHERE e.embedding_model=? AND a.status='completed'140    AND a.version = (SELECT MAX(version) FROM listing_ai_analyses b WHERE b.listing_uid=a.listing_uid AND b.status='completed')`).all(model) as { listing_uid: string; analysis_id: string; embedding_json: string; canonical_text: string | null }[])141    .map((r) => ({ listingUid: r.listing_uid, analysisId: r.analysis_id, vector: JSON.parse(r.embedding_json) as number[], text: r.canonical_text }));142}143144export function addOverride(analysisId: string, fieldPath: string, original: unknown, value: unknown, userId: string | null): void {145  db().prepare("INSERT INTO listing_ai_overrides(analysis_id,field_path,original_value,new_value,user_id) VALUES(?,?,?,?,?)").run(analysisId, fieldPath, JSON.stringify(original ?? null), JSON.stringify(value ?? null), userId);146}147export function listOverrides(analysisId: string): { fieldPath: string; original: unknown; value: unknown; createdAt: string }[] {148  const rows = db().prepare("SELECT field_path, original_value, new_value, created_at FROM listing_ai_overrides WHERE analysis_id=? ORDER BY id").all(analysisId) as { field_path: string; original_value: string; new_value: string; created_at: string }[];149  // la dernière correction d'un champ gagne150  const map = new Map<string, { fieldPath: string; original: unknown; value: unknown; createdAt: string }>();151  for (const r of rows) map.set(r.field_path, { fieldPath: r.field_path, original: map.get(r.field_path)?.original ?? JSON.parse(r.original_value), value: JSON.parse(r.new_value), createdAt: r.created_at });152  return [...map.values()];153}154155export function recordUsage(u: { analysisId: string | null; purpose: string; model: string; images: number; inputTokens: number; outputTokens: number; cacheRead: number; costUsd: number; latencyMs: number }): void {156  db().prepare("INSERT INTO ai_usage(analysis_id,purpose,model,input_images,input_tokens,output_tokens,cache_read_tokens,estimated_cost_usd,latency_ms) VALUES(?,?,?,?,?,?,?,?,?)").run(u.analysisId, u.purpose, u.model, u.images, u.inputTokens, u.outputTokens, u.cacheRead, u.costUsd, u.latencyMs);157}158export function usageToday(): { count: number; costUsd: number } {159  const r = db().prepare("SELECT COUNT(*) n, COALESCE(SUM(estimated_cost_usd),0) c FROM ai_usage WHERE created_at >= date('now')").get() as { n: number; c: number };160  return { count: r.n, costUsd: r.c };161}162export function analysesStartedToday(): number {163  return (db().prepare("SELECT COUNT(*) n FROM listing_ai_analyses WHERE created_at >= date('now')").get() as { n: number }).n;164}165