spb/vrai-prix

1// Auteur : Simon-Pierre Boucher — contact@spboucher.ai2/**3 * Service d'estimation par la méthode du coût (serveur) :4 *  - préremplissage depuis une propriété du rôle (MAMH) ;5 *  - entrée par défaut (mode construction) ;6 *  - exécution (contexte de prix → quantités → moteur) + sauvegarde7 *    reproductible (estimation, lignes, instantané des prix) ;8 *  - relecture.9 */10import { randomUUID } from "crypto";11import { getUnit, type UnitRow } from "../db";12import { estimateByUnitId } from "../estimator";13import { costDatabaseVersion, getCostDb } from "./db";14import { buildContext, today } from "./catalog";15import { computeEstimate } from "./engine";16import { deriveQuantities } from "./geometry";17import { ASSEMBLY_VERSION } from "./seed/assemblies";18import { BUILDING_TYPES } from "./taxonomy";19import { sqftFromM2 } from "./units";20import type { AttributeSource, BuildingSpec, BuildingType, CostEstimate, CostInput, Quality } from "./types";21import { defaultInput } from "./estimate-defaults";2223export { defaultBuilding, defaultInput } from "./estimate-defaults";2425/* ------------------------------------------------- préremplissage MAMH */2627function buildingTypeFromUnit(u: UnitRow): BuildingType {28  switch (u.type_prop) {29    case "plex": return "plex";30    case "condo_ou_multi": return (u.nb_logements ?? 1) > 1 && (u.aire_etages_m2 ?? 0) > 400 ? "plex" : "condo";31    case "chalet": return "chalet";32    case "maison_mobile": return "mobile";33    case "unifamilial": {34      const lien = (u.lien_physique ?? "").toLowerCase();35      if (lien.includes("jumel")) return "semi_detached";36      if (lien.includes("rang")) return "row";37      if (lien.includes("intégré") || lien.includes("integre")) return "row";38      return "detached";39    }40    default: return "other";41  }42}4344export interface PropertyPrefill {45  input: CostInput;46  unit: { id: string; adresse: string | null; municipalite: string | null; typeProp: string; genre: string | null; lien: string | null; cubf: number | null; cubfLibelle: string | null; nbLogements: number | null; aireM2: number | null; terrainM2: number | null };47  missing: string[]; // champs à saisir manuellement48}4950/** Préremplit une entrée de coût depuis une unité d'évaluation (source MAMH). */51export function prefillFromProperty(id: string): PropertyPrefill | null {52  const u = getUnit(id);53  if (!u) return null;54  const input = defaultInput();55  const src: Partial<Record<string, AttributeSource>> = {};56  const missing: string[] = [];57  input.mode = "property";58  input.propertyId = u.id_provinc;59  input.address = [u.adresse, u.apt ? `app. ${u.apt}` : null].filter(Boolean).join(", ") || null;60  input.municipality = u.municipalite;61  input.lat = u.lat;62  input.lng = u.lng;63  const b = input.building;64  b.type = buildingTypeFromUnit(u);65  src.type = "MAMH";66  if (u.aire_etages_m2 && u.aire_etages_m2 > 15) { b.grossFloorAreaSqft = Math.round(sqftFromM2(u.aire_etages_m2)); src.grossFloorAreaSqft = "MAMH"; } else { missing.push("grossFloorAreaSqft"); src.grossFloorAreaSqft = "assumed"; }67  if (u.nb_etages && u.nb_etages > 0) { b.stories = Math.max(1, Math.round(u.nb_etages)); src.stories = "MAMH"; } else {68    const g = (u.genre_construction ?? "").toLowerCase();69    b.stories = g.includes("plain-pied") || g.includes("unimodulaire") ? 1 : g.includes("étages entiers") ? 2 : g.includes("mansard") ? 1.5 : g.includes("décal") ? 1.5 : 1;70    src.stories = g ? "derived" : "assumed";71    if (!g) missing.push("stories");72  }73  if (b.stories === 1.5) b.stories = 2; // étage mansardé / niveaux décalés : 2 niveaux de structure74  if (u.annee_construction && u.annee_construction > 1600) { b.yearBuilt = u.annee_construction; src.yearBuilt = u.annee_estimee === "E" ? "derived" : "MAMH"; } else missing.push("yearBuilt");75  b.units = Math.max(1, u.nb_logements ?? 1);76  src.units = u.nb_logements ? "MAMH" : "assumed";77  // hypothèses par défaut (à confirmer par l'utilisateur) — marquées « assumed »78  for (const k of ["basement", "garage", "siding", "roof", "heating", "bathrooms", "kitchenQuality", "quality", "windows", "foundation"]) { src[k] = "assumed"; missing.push(k); }79  if (b.type === "plex") { b.kitchens = b.units; b.bathrooms = b.units; b.quality = "standard"; }80  if (b.type === "mobile") { b.foundation = "piers"; b.basement = "none"; b.stories = 1; }81  if (b.type === "condo") { b.basement = "none"; }82  if (b.grossFloorAreaSqft > 2600 && b.type === "detached") { b.bathrooms = 2; b.powderRooms = 1; }83  if (b.type === "chalet") { b.quality = "standard"; }84  // terrain et rôle85  input.roll = { landValue: u.valeur_terrain, buildingValue: u.valeur_batiment, totalValue: u.valeur_role, year: 2026 };86  if (u.valeur_terrain && u.valeur_terrain > 0) input.land = { value: u.valeur_terrain, source: "role", rollYear: 2026, method: "Valeur du terrain au rôle d'évaluation foncière 2026 (MAMH)" };87  else { input.land = { value: null, source: "none", rollYear: null, method: "" }; missing.push("landValue"); }88  input.depreciation.economicLife = BUILDING_TYPES.find((t) => t.key === b.type)?.economicLife ?? 60;89  input.attributeSources = src;90  return {91    input,92    unit: { id: u.id_provinc, adresse: u.adresse, municipalite: u.municipalite, typeProp: u.type_prop, genre: u.genre_construction, lien: u.lien_physique, cubf: u.cubf, cubfLibelle: u.cubf_libelle, nbLogements: u.nb_logements, aireM2: u.aire_etages_m2, terrainM2: u.superficie_terrain_m2 },93    missing,94  };95}9697/* --------------------------------------------------------------- exécution */9899export interface RunOptions {100  save?: boolean;101  sessionId?: string | null;102  otherReadings?: CostEstimate["otherReadings"];103}104105export function runEstimate(input: CostInput, opts: RunOptions = {}): CostEstimate {106  const d = getCostDb();107  const asOf = input.priceDate ?? today();108  const ctx = buildContext({ asOf, locationCode: input.locationCode, municipality: input.municipality, lat: input.lat, lng: input.lng }, d);109  const derived = deriveQuantities(input.building, input.quantityOverrides, input.mode === "listing" ? "AI" : "user", input.excludedAssemblies);110  const id = randomUUID();111  const est = computeEstimate(input, derived.lines, ctx, { id, createdAt: new Date().toISOString(), costDatabaseVersion: costDatabaseVersion(d), assemblyVersion: String(ASSEMBLY_VERSION) });112  est.warnings.push(...derived.warnings, `localisation : ${ctx.locationMethod}`);113  est.otherReadings = opts.otherReadings ?? (input.propertyId ? otherReadingsForProperty(input.propertyId) : null);114  if (opts.save !== false) saveEstimate(est, ctx, opts.sessionId ?? null);115  return est;116}117118/** Les autres lectures de la valeur pour une propriété du rôle (hédonique, comparables, hybride, rôle). */119export function otherReadingsForProperty(id: string): CostEstimate["otherReadings"] {120  try {121    const e = estimateByUnitId(id);122    if (!e?.unit) return null;123    return { hedonic: e.result.modelEstimate ?? null, comparables: e.result.compsEstimate ?? null, hybrid: e.result.estimate ?? null, askingPrice: null, rollValue: e.unit.valeurRole, rollBuilding: e.unit.specs.valeurBatiment, rollLand: e.unit.specs.valeurTerrain };124  } catch {125    return null;126  }127}128129function saveEstimate(est: CostEstimate, ctx: ReturnType<typeof buildContext>, sessionId: string | null): void {130  const d = getCostDb();131  const tx = d.transaction(() => {132    d.prepare(`INSERT INTO cost_estimates(id,property_id,listing_uid,user_session_id,mode,location_code,municipality,estimate_date,price_date,building_type,quality_level,area_sqft,direct_cost,indirect_cost,contractor_overhead,contractor_profit,contingency,replacement_cost_new,rcn_low,rcn_high,physical_depreciation,functional_obsolescence,external_obsolescence,depreciated_improvement_value,land_value,cost_approach_value,confidence_score,confidence_letter,method_version,cost_database_version,assembly_version,input_json,result_json,assumptions_json)133      VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`).run(134      est.id, est.input.propertyId, est.input.listingUid, sessionId, est.input.mode, est.location.code, est.input.municipality, est.createdAt, est.priceDate, est.input.building.type, est.input.building.quality, est.input.building.grossFloorAreaSqft,135      est.directCost, est.indirectCost, est.contractorOverhead, est.contractorProfit, est.contingency, est.replacementCostNew, est.range.p10, est.range.p90, est.depreciation.physical, est.depreciation.functional, est.depreciation.external,136      est.depreciation.depreciatedImprovementValue, est.landValue, est.costApproachValue, est.confidence.total, est.confidence.letter, est.methodVersion, est.costDatabaseVersion, est.assemblyVersion,137      JSON.stringify(est.input), JSON.stringify(stripForStorage(est)), JSON.stringify({ params: est.input.params, depreciation: est.input.depreciation, land: est.input.land, attributeSources: est.input.attributeSources, warnings: est.warnings }),138    );139    const ins = d.prepare(`INSERT INTO cost_estimate_lines(estimate_id,assembly_code,category,quantity,unit,material_cost,labour_cost,equipment_cost,direct_cost,location_adjustment,adjusted_cost,source_summary,confidence_score,calculation_json) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)`);140    for (const l of est.lines) {141      ins.run(est.id, l.assemblyCode, l.category, l.quantity, l.unit, l.material, l.labour, l.equipment, l.direct, l.locationAdjustment, l.adjusted,142        [...new Set(l.unitDetail.components.map((c) => c.provenance.source))].join(" · "), l.confidence, JSON.stringify({ formula: l.quantityFormula, quantitySource: l.quantitySource, unitCost: l.unitCost, sigma: l.sigma, components: l.unitDetail.components }));143    }144    const used = new Set(est.lines.flatMap((l) => l.unitDetail.components.map((c) => c.itemCode).filter(Boolean)));145    const prices = Object.fromEntries([...ctx.prices.entries()].filter(([k]) => used.has(k)));146    d.prepare("INSERT INTO cost_estimate_snapshots(estimate_id,snapshot_date,prices_json,labour_json,location_json) VALUES(?,?,?,?,?)")147      .run(est.id, est.priceDate, JSON.stringify(prices), JSON.stringify(Object.fromEntries(ctx.labour)), JSON.stringify(ctx.location));148  });149  tx();150}151152/** Le résultat complet est stocké tel quel (JSON) — sans doublons volumineux. */153function stripForStorage(est: CostEstimate): CostEstimate {154  return est;155}156157export function getEstimate(id: string): CostEstimate | null {158  const r = getCostDb().prepare("SELECT result_json FROM cost_estimates WHERE id=?").get(id) as { result_json: string } | undefined;159  if (!r) return null;160  return JSON.parse(r.result_json) as CostEstimate;161}162163export function recentEstimatesForProperty(propertyId: string, limit = 5): { id: string; createdAt: string; rcn: number; value: number; letter: string }[] {164  return (getCostDb().prepare("SELECT id, created_at, replacement_cost_new rcn, cost_approach_value value, confidence_letter letter FROM cost_estimates WHERE property_id=? ORDER BY created_at DESC LIMIT ?").all(propertyId, limit) as Record<string, unknown>[])165    .map((r) => ({ id: String(r.id), createdAt: String(r.created_at), rcn: Number(r.rcn), value: Number(r.value), letter: String(r.letter) }));166}167168/** Normalise/valide une entrée reçue du client (bornes, valeurs par défaut). */169export function sanitizeInput(raw: unknown): CostInput {170  const base = defaultInput();171  if (!raw || typeof raw !== "object") return base;172  const r = raw as Partial<CostInput>;173  const b: BuildingSpec = { ...base.building, ...(r.building ?? {}) };174  b.grossFloorAreaSqft = clamp(Number(b.grossFloorAreaSqft) || 1800, 200, 30000);175  b.footprintSqft = b.footprintSqft != null ? clamp(Number(b.footprintSqft), 100, 20000) : null;176  b.stories = clamp(Number(b.stories) || 1, 1, 6);177  b.units = clamp(Number(b.units) || 1, 1, 12);178  b.kitchens = clamp(Number(b.kitchens) || 1, 0, 12);179  b.bathrooms = clamp(Number(b.bathrooms) || 0, 0, 12);180  b.powderRooms = clamp(Number(b.powderRooms) || 0, 0, 6);181  b.roofPitch = clamp(Number(b.roofPitch) || 6, 0, 18);182  b.deckSqft = clamp(Number(b.deckSqft) || 0, 0, 3000);183  b.drivewaySqft = clamp(Number(b.drivewaySqft) || 0, 0, 10000);184  b.fenceLinFt = clamp(Number(b.fenceLinFt) || 0, 0, 2000);185  b.landscapingSqft = clamp(Number(b.landscapingSqft) || 0, 0, 40000);186  b.basementFinishedPct = clamp(Number(b.basementFinishedPct) || 0, 0, 1);187  b.garage = { type: b.garage?.type ?? "none", spaces: clamp(Number(b.garage?.spaces) || 0, 0, 4), areaSqft: b.garage?.areaSqft != null ? clamp(Number(b.garage.areaSqft), 100, 2000) : null };188  b.yearBuilt = b.yearBuilt != null && Number(b.yearBuilt) > 1600 ? Math.round(Number(b.yearBuilt)) : null;189  b.windowCount = b.windowCount != null ? clamp(Math.round(Number(b.windowCount)), 0, 200) : null;190  b.siding = Object.fromEntries(Object.entries(b.siding ?? {}).filter(([, v]) => typeof v === "number" && v > 0));191  b.flooring = Object.fromEntries(Object.entries(b.flooring ?? {}).filter(([, v]) => typeof v === "number" && v > 0));192  const q = (x: unknown): Quality => (["economy", "standard", "superior", "prestige"].includes(String(x)) ? (x as Quality) : "standard");193  b.quality = q(b.quality); b.kitchenQuality = q(b.kitchenQuality); b.bathroomQuality = q(b.bathroomQuality);194  const params = { indirect: { ...base.params.indirect, ...(r.params?.indirect ?? {}) }, overheadPct: clamp(Number(r.params?.overheadPct ?? base.params.overheadPct), 0, 40), profitPct: clamp(Number(r.params?.profitPct ?? base.params.profitPct), 0, 40), contingencyPct: clamp(Number(r.params?.contingencyPct ?? base.params.contingencyPct), 0, 40) };195  for (const k of Object.keys(params.indirect) as (keyof typeof params.indirect)[]) params.indirect[k] = clamp(Number(params.indirect[k]) || 0, 0, 30);196  const dep = { ...base.depreciation, ...(r.depreciation ?? {}) };197  dep.economicLife = clamp(Number(dep.economicLife) || 60, 10, 150);198  dep.effectiveAge = dep.effectiveAge != null && dep.effectiveAge !== ("" as unknown) ? clamp(Number(dep.effectiveAge), 0, 200) : null;199  dep.functional = Array.isArray(dep.functional) ? dep.functional.slice(0, 20).map((f) => ({ id: String(f.id ?? randomUUID()), type: String(f.type ?? ""), curable: !!f.curable, costToCure: clamp(Number(f.costToCure) || 0, 0, 5e6), valueLoss: clamp(Number(f.valueLoss) || 0, 0, 5e6), notes: String(f.notes ?? "").slice(0, 500) })) : [];200  dep.externalValueLoss = clamp(Number(dep.externalValueLoss) || 0, 0, 5e6);201  dep.componentConditions = dep.componentConditions ?? {};202  const land = { ...base.land, ...(r.land ?? {}) };203  land.value = land.value != null && land.value !== ("" as unknown) ? clamp(Number(land.value), 0, 5e7) : null;204  const overrides: Record<string, number> = {};205  for (const [k, v] of Object.entries(r.quantityOverrides ?? {})) if (typeof v === "number" && v >= 0 && /^[A-Z0-9/.-]+$/.test(k)) overrides[k] = clamp(v, 0, 1e6);206  return {207    ...base, ...r, building: b, params, depreciation: dep, land, quantityOverrides: overrides,208    excludedAssemblies: Array.isArray(r.excludedAssemblies) ? r.excludedAssemblies.filter((x) => typeof x === "string").slice(0, 100) : [],209    attributeSources: r.attributeSources ?? {}, priceDate: r.priceDate && /^\d{4}-\d{2}-\d{2}$/.test(r.priceDate) ? r.priceDate : null,210    mode: r.mode === "property" || r.mode === "listing" ? r.mode : "construction",211    propertyId: r.propertyId ? String(r.propertyId).slice(0, 40) : null, listingUid: r.listingUid ? String(r.listingUid).slice(0, 80) : null,212    municipality: r.municipality ? String(r.municipality).slice(0, 120) : null, address: r.address ? String(r.address).slice(0, 200) : null,213    lat: typeof r.lat === "number" ? r.lat : null, lng: typeof r.lng === "number" ? r.lng : null, locationCode: r.locationCode ? String(r.locationCode).slice(0, 20) : null,214    roll: r.roll ?? null,215  };216}217218const clamp = (v: number, lo: number, hi: number) => (Number.isFinite(v) ? Math.min(hi, Math.max(lo, v)) : lo);219