spb/vrai-prix

1// Auteur : Simon-Pierre Boucher — contact@spboucher.ai2/**3 * Imports manuels (aucun scraping) :4 *  - Altus Group — Canadian Cost Guide : benchmarks $/pi² saisis après5 *    téléchargement légal du guide (formulaire) → cost_benchmarks ;6 *  - RSMeans / Gordian : données sous licence, import CSV/XLSX→CSV autorisé7 *    seulement → cost_item_prices (price_kind « reference », source rsmeans),8 *    jamais redistribuées telles quelles (seul le prix canonique agrégé sert).9 */10import type Database from "better-sqlite3";11import { getCostDb, sourceIdByKey } from "../db";12import { convertPrice } from "../units";13import type { CanonicalUnit } from "../types";1415export const BENCHMARK_TYPES = ["single_family", "townhouse", "plex", "condo"] as const;16export type BenchmarkType = (typeof BENCHMARK_TYPES)[number];1718export interface BenchmarkImportRow { source: string; building_type: BenchmarkType; market: string; unit?: string; low: number; high: number; year: number; notes?: string | null }1920/** CSV simple (séparateur , ou ;) → objets ; guillemets gérés. */21export function parseCsv(text: string): Record<string, string>[] {22  const lines = text.replace(/\r/g, "").split("\n").filter((l) => l.trim());23  if (lines.length < 2) return [];24  const sep = lines[0].includes(";") && !lines[0].includes(",") ? ";" : ",";25  const split = (l: string): string[] => {26    const out: string[] = [];27    let cur = "";28    let q = false;29    for (const ch of l) {30      if (ch === '"') q = !q;31      else if (ch === sep && !q) { out.push(cur.trim()); cur = ""; }32      else cur += ch;33    }34    out.push(cur.trim());35    return out;36  };37  const head = split(lines[0]).map((h) => h.toLowerCase().replace(/\s+/g, "_"));38  return lines.slice(1).map((l) => Object.fromEntries(split(l).map((v, i) => [head[i] ?? `c${i}`, v])));39}4041export function normalizeBenchmarkRows(input: unknown): { rows: BenchmarkImportRow[]; errors: string[] } {42  const raw: Record<string, unknown>[] = typeof input === "string" ? parseCsv(input) : Array.isArray(input) ? (input as Record<string, unknown>[]) : [];43  const rows: BenchmarkImportRow[] = [];44  const errors: string[] = [];45  raw.forEach((r, i) => {46    const bt = String(r.building_type ?? "").trim() as BenchmarkType;47    const low = Number(String(r.low ?? "").replace(",", "."));48    const high = Number(String(r.high ?? "").replace(",", "."));49    const year = Number(r.year);50    if (!BENCHMARK_TYPES.includes(bt)) { errors.push(`ligne ${i + 1} : building_type invalide « ${bt} »`); return; }51    if (!(low > 0 && high >= low && high < 5000)) { errors.push(`ligne ${i + 1} : bornes invalides`); return; }52    if (!(year >= 2015 && year <= 2035)) { errors.push(`ligne ${i + 1} : année invalide`); return; }53    if (!r.source || !r.market) { errors.push(`ligne ${i + 1} : source/market requis`); return; }54    rows.push({ source: String(r.source).trim(), building_type: bt, market: String(r.market).trim(), unit: String(r.unit ?? "$/pi2"), low, high, year, notes: r.notes ? String(r.notes) : null });55  });56  return { rows, errors };57}5859export function importBenchmarks(input: unknown, d: Database.Database = getCostDb()): { imported: number; errors: string[] } {60  const { rows, errors } = normalizeBenchmarkRows(input);61  const altusId = sourceIdByKey("altus", d);62  const rsId = sourceIdByKey("rsmeans", d);63  const st = d.prepare(`INSERT INTO cost_benchmarks(source,source_id,building_type,market,unit,low,high,midpoint,year,notes,is_demo) VALUES(?,?,?,?,?,?,?,?,?,?,0)64    ON CONFLICT(source,building_type,market,year) DO UPDATE SET low=excluded.low, high=excluded.high, midpoint=excluded.midpoint, unit=excluded.unit, notes=excluded.notes, source_id=excluded.source_id`);65  const tx = d.transaction(() => { for (const r of rows) st.run(r.source, /altus/i.test(r.source) ? altusId : /rsmeans|gordian/i.test(r.source) ? rsId : null, r.building_type, r.market, r.unit, r.low, r.high, (r.low + r.high) / 2, r.year, r.notes ?? null); });66  tx();67  if (rows.length) d.prepare("UPDATE cost_sources SET last_successful_sync=datetime('now') WHERE key IN ('altus') AND EXISTS (SELECT 1 FROM cost_benchmarks WHERE source_id=cost_sources.id)").run();68  return { imported: rows.length, errors };69}7071/** Import RSMeans/Gordian (CSV : item_code, price, unit, [pack_qty], [date], [notes]) → prix de référence licenciés. */72export function importRsmeans(input: unknown, d: Database.Database = getCostDb()): { imported: number; errors: string[] } {73  const raw: Record<string, unknown>[] = typeof input === "string" ? parseCsv(input) : Array.isArray(input) ? (input as Record<string, unknown>[]) : [];74  const sid = sourceIdByKey("rsmeans", d);75  const errors: string[] = [];76  let imported = 0;77  const tx = d.transaction(() => {78    raw.forEach((r, i) => {79      const code = String(r.item_code ?? r.canonical_code ?? "").trim();80      const item = d.prepare("SELECT id, unit FROM cost_items WHERE canonical_code=?").get(code) as { id: number; unit: CanonicalUnit } | undefined;81      if (!item) { errors.push(`ligne ${i + 1} : article inconnu ${code}`); return; }82      const price = Number(String(r.price ?? "").replace(",", "."));83      if (!(price > 0)) { errors.push(`ligne ${i + 1} : prix invalide`); return; }84      const date = String(r.date ?? new Date().toISOString().slice(0, 10));85      try {86        const conv = convertPrice(price, String(r.unit ?? item.unit), item.unit, r.pack_qty ? Number(r.pack_qty) : null);87        d.prepare(`INSERT INTO cost_item_prices(cost_item_id,source_id,location_code,observation_date,price_kind,material_cost,total_cost,currency,source_unit,conversion_factor,is_regular_price,confidence_score,source_url,verified) VALUES(?,?,'QC',?,'reference',?,?,'CAD',?,?,1,70,?,1)`)88          .run(item.id, sid, date, conv.price, conv.price, conv.conversion.sourceUnit, conv.conversion.factor, r.notes ? String(r.notes) : null);89        imported++;90      } catch (e) { errors.push(`ligne ${i + 1} : ${(e as Error).message}`); }91    });92  });93  tx();94  return { imported, errors };95}96