spb/valoplex Public

1// Auteur : Simon-Pierre Boucher — contact@spboucher.ai2// Service d'estimation : relie la base SQLite au moteur (engine.ts).3import {4  getCandidates,5  getMarketIndex,6  getUnit,7  municipalityCenter,8  type TxRow,9  type UnitRow,10} from "./db";11import { estimate, type CompInput, type EstimateResult, type Subject } from "./engine";1213export interface UnitSpecs {14  cubf: number | null;15  cubfLibelle: string | null;16  anneeEstimee: string | null;17  frontTerrainM: number | null;18  nbEtages: number | null;19  nbLocauxNonResid: number | null;20  nbChambresLocatives: number | null;21  lienPhysique: string | null;22  genreConstruction: string | null;23  matricule: string | null;24  uniteVoisinage: string | null;25  nAdresses: number | null;26  datCondMarche: string | null;27  valeurTerrain: number | null;28  valeurBatiment: number | null;29  valeurAnterieure: number | null;30  arrond: string | null;31  apt: string | null;32  portesAdresses: string[];33}3435export interface UnitEstimate {36  unit: {37    id: string;38    adresse: string | null;39    municipalite: string | null;40    typeProp: string;41    cubfLibelle: string | null;42    anneeConstruction: number | null;43    aireEtagesM2: number | null;44    superficieTerrainM2: number | null;45    nbLogements: number | null;46    valeurRole: number | null;47    lat: number;48    lng: number;49    history: { year: number; value: number | null }[];50    specs: UnitSpecs;51  } | null;52  result: EstimateResult;53}5455/** Type de comparable attendu pour le type de tx du marché de l'indice. */56export function indexType(): string {57  return "plex"; // ValoPlex : un seul segment de marché58}5960function toComps(rows: TxRow[]): CompInput[] {61  return rows.map((r) => ({62    id: r.id,63    date: r.date,64    amount: r.amount,65    lat: r.lat,66    lng: r.lng,67    propertyType: r.property_type,68    doors: r.portes,69    yearBuilt: r.year_built,70    floorArea: r.floor_area,71    street: r.street,72    city: r.city,73  }));74}7576function nowISO(): string {77  return new Date().toISOString().slice(0, 10);78}7980function since(months: number): string {81  const d = new Date();82  d.setMonth(d.getMonth() - months);83  return d.toISOString().slice(0, 10);84}8586function candidatesAround(lat: number, lng: number): TxRow[] {87  // ~2,2 km puis élargit à ~11 km si marché mince88  for (const halfDeg of [0.02, 0.1]) {89    const rows = getCandidates(lat, lng, halfDeg, since(30), 600);90    if (rows.length >= 30) return rows;91  }92  return getCandidates(lat, lng, 0.25, since(36), 600);93}9495export function estimateByUnitId(id: string): UnitEstimate | null {96  const u = getUnit(id);97  if (!u) return null;98  const subject: Subject = {99    lat: u.lat,100    lng: u.lng,101    typeProp: "plex",102    doors: u.nb_logements,103    floorArea: u.aire_etages_m2,104    yearBuilt: u.annee_construction,105    landArea: u.superficie_terrain_m2,106    modelEstimate: u.est_2026,107    modelP10: u.p10,108    modelP90: u.p90,109  };110  const result = estimate(111    subject,112    toComps(candidatesAround(u.lat, u.lng)),113    getMarketIndex(indexType()),114    nowISO()115  );116  return {117    unit: {118      id: u.id_provinc,119      adresse: u.adresse,120      municipalite: u.municipalite,121      typeProp: u.type_prop,122      cubfLibelle: u.cubf_libelle,123      anneeConstruction: u.annee_construction,124      aireEtagesM2: u.aire_etages_m2,125      superficieTerrainM2: u.superficie_terrain_m2,126      nbLogements: u.nb_logements,127      valeurRole: u.valeur_role,128      lat: u.lat,129      lng: u.lng,130      history: ([2021, 2022, 2023, 2024, 2025, 2026] as const).map((y) => ({131        year: y,132        value: u[`est_${y}` as keyof UnitRow] as number | null,133      })),134      specs: {135        cubf: u.cubf,136        cubfLibelle: u.cubf_libelle,137        anneeEstimee: u.annee_estimee,138        frontTerrainM: u.front_terrain_m,139        nbEtages: u.nb_etages,140        nbLocauxNonResid: u.nb_locaux_non_resid,141        nbChambresLocatives: u.nb_chambres_locatives,142        lienPhysique: u.lien_physique,143        genreConstruction: u.genre_construction,144        matricule: u.matricule,145        uniteVoisinage: u.unite_voisinage,146        nAdresses: u.n_adresses,147        datCondMarche: u.dat_cond_marche,148        valeurTerrain: u.valeur_terrain,149        valeurBatiment: u.valeur_batiment,150        valeurAnterieure: u.valeur_anterieure,151        arrond: u.arrond,152        apt: u.apt,153        portesAdresses: (() => {154          try {155            return u.adresses_json ? (JSON.parse(u.adresses_json) as string[]) : [];156          } catch {157            return [];158          }159        })(),160      },161    },162    result,163  };164}165166export interface PortfolioAggregates {167  count: number;168  totalEstimate: number;169  totalLow: number;170  totalHigh: number;171  totalRole: number;172  ecartRolePct: number | null;173  confidencePct: number; // moyenne pondérée par valeur174  confidenceLevel: "A" | "B" | "C" | "D";175  totalFloorArea: number;176  totalLandArea: number;177  totalDwellings: number;178  avgYearBuilt: number | null;179  municipalities: { name: string; count: number; total: number }[];180  types: { type: string; count: number; total: number }[];181  history: { year: number; total: number; nCovered: number }[];182  growthPct: number | null; // croissance du parc 2021→2026 (unités couvertes)183}184185export interface PortfolioResult {186  items: UnitEstimate[];187  aggregates: PortfolioAggregates;188}189190export function estimatePortfolio(ids: string[]): PortfolioResult {191  const items = ids192    .slice(0, 40)193    .map((id) => estimateByUnitId(id))194    .filter((x): x is UnitEstimate => x !== null && x.unit !== null);195196  const val = (n: number | null | undefined) => n ?? 0;197  const totalEstimate = items.reduce((s, i) => s + val(i.result.estimate), 0);198  const totalRole = items.reduce((s, i) => s + val(i.unit!.valeurRole), 0);199  const wConf =200    totalEstimate > 0201      ? items.reduce((s, i) => s + i.result.confidencePct * val(i.result.estimate), 0) /202        totalEstimate203      : 0;204  const level = wConf >= 75 ? "A" : wConf >= 60 ? "B" : wConf >= 45 ? "C" : "D";205206  const byKey = (key: (i: UnitEstimate) => string) => {207    const m = new Map<string, { count: number; total: number }>();208    for (const i of items) {209      const k = key(i);210      const e = m.get(k) ?? { count: 0, total: 0 };211      e.count += 1;212      e.total += val(i.result.estimate);213      m.set(k, e);214    }215    return [...m.entries()]216      .map(([name, v]) => ({ name, ...v }))217      .sort((a, b) => b.total - a.total);218  };219220  const years = [2021, 2022, 2023, 2024, 2025, 2026] as const;221  const history = years.map((y) => {222    let total = 0;223    let n = 0;224    for (const i of items) {225      const h = i.unit!.history.find((hh) => hh.year === y);226      if (h?.value != null) {227        total += h.value;228        n += 1;229      }230    }231    return { year: y, total, nCovered: n };232  });233  // croissance : somme 2026 vs 2021 sur les unités couvertes aux deux bornes234  let g21 = 0;235  let g26 = 0;236  for (const i of items) {237    const a = i.unit!.history.find((h) => h.year === 2021)?.value;238    const b = i.unit!.history.find((h) => h.year === 2026)?.value;239    if (a != null && b != null) {240      g21 += a;241      g26 += b;242    }243  }244245  const yb = items246    .map((i) => i.unit!.anneeConstruction)247    .filter((v): v is number => v != null && v > 1600);248249  return {250    items,251    aggregates: {252      count: items.length,253      totalEstimate,254      totalLow: items.reduce((s, i) => s + val(i.result.low), 0),255      totalHigh: items.reduce((s, i) => s + val(i.result.high), 0),256      totalRole,257      ecartRolePct: totalRole > 0 ? (totalEstimate / totalRole - 1) * 100 : null,258      confidencePct: Math.round(wConf),259      confidenceLevel: level,260      totalFloorArea: Math.round(items.reduce((s, i) => s + val(i.unit!.aireEtagesM2), 0)),261      totalLandArea: Math.round(items.reduce((s, i) => s + val(i.unit!.superficieTerrainM2), 0)),262      totalDwellings: items.reduce((s, i) => s + val(i.unit!.nbLogements), 0),263      avgYearBuilt: yb.length ? Math.round(yb.reduce((s, v) => s + v, 0) / yb.length) : null,264      municipalities: byKey((i) => i.unit!.municipalite ?? "—"),265      types: byKey((i) => i.unit!.typeProp).map(({ name, ...v }) => ({ type: name, ...v })),266      history,267      growthPct: g21 > 0 ? (g26 / g21 - 1) * 100 : null,268    },269  };270}271272export interface ManualParams {273  municipality: string;274  typeProp: string;275  portes?: number;276  floorArea?: number;277  yearBuilt?: number;278  landArea?: number;279}280281export function estimateManual(p: ManualParams): UnitEstimate | null {282  const center = municipalityCenter(p.municipality);283  if (!center || !center.n) return null;284  const subject: Subject = {285    lat: center.lat,286    lng: center.lng,287    typeProp: "plex",288    doors: p.portes ?? null,289    floorArea: p.floorArea ?? null,290    yearBuilt: p.yearBuilt ?? null,291    landArea: p.landArea ?? null,292    modelEstimate: null,293    modelP10: null,294    modelP90: null,295  };296  const result = estimate(297    subject,298    toComps(candidatesAround(center.lat, center.lng)),299    getMarketIndex(indexType()),300    nowISO()301  );302  if (!Number.isFinite(result.estimate)) return null;303  return { unit: null, result };304}305