spb/vrai-prix

1// Auteur : Simon-Pierre Boucher — contact@spboucher.ai2/**3 * scripts/build-stats-v2.mjs — agrégats v2 du module Stats commun Groupe KA.4 * Lit data/vraiprix.db (rôle d'évaluation + corpus de ventes + indice marché)5 * et écrit src/data/stats-v2.json : tout ce que /api/stats/dashboard v2 sert6 * en plus des agrégats historiques de src/data/stats.json.7 * Données 100 % réelles — aucune valeur inventée. À relancer quand la DB change :8 *   node scripts/build-stats-v2.mjs9 */10import Database from "better-sqlite3";11import { writeFileSync } from "fs";12import path from "path";1314const db = new Database(path.join(process.cwd(), "data", "vraiprix.db"), { readonly: true });15const out = {};16out.generated = new Date().toISOString();1718/* ---------------- unités (rôle d'évaluation, millésime 2026) ---------------- */19console.time("units");20out.units = db.prepare(`21  SELECT COUNT(*) AS total,22         SUM(lat IS NOT NULL AND lng IS NOT NULL AND lat != 0) AS geoloc,23         SUM(valeur_role IS NOT NULL AND valeur_role > 0) AS with_role,24         SUM(est_2026 IS NOT NULL AND est_2026 > 0) AS with_est2026,25         SUM(annee_construction IS NOT NULL AND annee_construction > 1500) AS with_year,26         SUM(aire_etages_m2 IS NOT NULL AND aire_etages_m2 > 0) AS with_area,27         CAST(AVG(CASE WHEN est_2026 > 0 THEN est_2026 END) AS INTEGER) AS avg_est2026,28         CAST(AVG(CASE WHEN valeur_role > 0 THEN valeur_role END) AS INTEGER) AS avg_role,29         CAST(SUM(CASE WHEN valeur_role > 0 THEN valeur_role ELSE 0 END) AS INTEGER) AS sum_role,30         SUM(COALESCE(nb_logements, 0)) AS logements31  FROM units`).get();32console.timeEnd("units");3334/* ------------- distribution des valeurs estimées 2026 (histogramme) ------------- */35console.time("bins");36const BINS = [37  [0, 100e3, "< 100 k$"], [100e3, 200e3, "100-200 k$"], [200e3, 300e3, "200-300 k$"],38  [300e3, 400e3, "300-400 k$"], [400e3, 500e3, "400-500 k$"], [500e3, 750e3, "500-750 k$"],39  [750e3, 1e6, "750 k-1 M$"], [1e6, 2e6, "1-2 M$"], [2e6, 5e6, "2-5 M$"], [5e6, Infinity, "5 M$ +"],40];41const caseExpr = BINS.map(([lo, hi], i) =>42  hi === Infinity ? `WHEN est_2026 >= ${lo} THEN ${i}` : `WHEN est_2026 >= ${lo} AND est_2026 < ${hi} THEN ${i}`43).join(" ");44const binRows = db.prepare(`45  SELECT CASE ${caseExpr} END AS b, COUNT(*) AS n46  FROM units WHERE est_2026 > 0 GROUP BY b ORDER BY b`).all();47out.valeur_bins = BINS.map(([, , label], i) => ({48  label, value: binRows.find((r) => r.b === i)?.n ?? 0,49}));50console.timeEnd("bins");5152/* ------------- parc par tranche d'année de construction ------------- */53console.time("construction");54const ERAS = [55  [0, 1900, "Avant 1900"], [1900, 1946, "1900-1945"], [1946, 1961, "1946-1960"],56  [1961, 1976, "1961-1975"], [1976, 1991, "1976-1990"], [1991, 2006, "1991-2005"],57  [2006, 2016, "2006-2015"], [2016, 3000, "2016 +"],58];59const eraExpr = ERAS.map(([lo, hi], i) => `WHEN annee_construction >= ${lo} AND annee_construction < ${hi} THEN ${i}`).join(" ");60const eraRows = db.prepare(`61  SELECT CASE ${eraExpr} END AS b, COUNT(*) AS n,62         CAST(SUM(CASE WHEN est_2026 > 0 THEN est_2026 ELSE 0 END) AS INTEGER) AS total,63         CAST(AVG(CASE WHEN est_2026 > 0 THEN est_2026 END) AS INTEGER) AS moyenne64  FROM units WHERE annee_construction > 1500 GROUP BY b ORDER BY b`).all();65out.construction = ERAS.map(([, , label], i) => {66  const r = eraRows.find((x) => x.b === i);67  return { label, n: r?.n ?? 0, total: r?.total ?? 0, moyenne: r?.moyenne ?? null };68});69console.timeEnd("construction");7071/* ---------------- records réels du rôle ---------------- */72console.time("records");73out.rec = {};74out.rec.max_role = db.prepare(`75  SELECT municipalite, valeur_role AS v, cubf_libelle FROM units76  WHERE valeur_role IS NOT NULL ORDER BY valeur_role DESC LIMIT 1`).get();77out.rec.max_est = db.prepare(`78  SELECT municipalite, est_2026 AS v FROM units79  WHERE est_2026 IS NOT NULL ORDER BY est_2026 DESC LIMIT 1`).get();80out.rec.max_terrain = db.prepare(`81  SELECT municipalite, superficie_terrain_m2 AS m2 FROM units82  WHERE superficie_terrain_m2 IS NOT NULL ORDER BY superficie_terrain_m2 DESC LIMIT 1`).get();83out.rec.max_logements = db.prepare(`84  SELECT municipalite, nb_logements AS n FROM units85  WHERE nb_logements IS NOT NULL ORDER BY nb_logements DESC LIMIT 1`).get();86const oldest = db.prepare(`87  SELECT MIN(annee_construction) AS y FROM units WHERE annee_construction > 1600`).get();88out.rec.oldest = {89  y: oldest.y,90  n: db.prepare("SELECT COUNT(*) AS n FROM units WHERE annee_construction = ?").get(oldest.y).n,91};92console.timeEnd("records");9394/* ---------------- transactions (corpus de ventes réelles 2021-2026) ---------------- */95console.time("tx");96const TX_TYPE_FR = { unifamilial: "Unifamiliale", condo: "Condo", plex: "Plex", "indéterminé": "Indéterminé" };97const txTypeKeys = ["unifamilial", "condo", "plex", "indéterminé"];98const monthly = new Map(); // m -> { n, total, amounts[], byType: [4] }99for (const r of db.prepare("SELECT substr(date,1,7) AS m, amount, property_type FROM transactions").iterate()) {100  let e = monthly.get(r.m);101  if (!e) { e = { n: 0, total: 0, amounts: [], byType: [0, 0, 0, 0] }; monthly.set(r.m, e); }102  e.n++; e.total += r.amount; e.amounts.push(r.amount);103  const ti = txTypeKeys.indexOf(r.property_type ?? "indéterminé");104  e.byType[ti === -1 ? 3 : ti]++;105}106const months = [...monthly.keys()].sort();107const median = (a) => { const s = [...a].sort((x, y) => x - y); return s[Math.floor(s.length / 2)]; };108out.tx = {109  monthly: months.map((m) => {110    const e = monthly.get(m);111    return { m, n: e.n, total: Math.round(e.total), median: Math.round(median(e.amounts)) };112  }),113  monthly_by_type: {114    keys: txTypeKeys.map((k) => TX_TYPE_FR[k]),115    points: months.map((m) => ({ t: m, values: monthly.get(m).byType })),116  },117};118// bins des montants de vente119const TXB = [120  [0, 200e3, "< 200 k$"], [200e3, 300e3, "200-300 k$"], [300e3, 400e3, "300-400 k$"],121  [400e3, 500e3, "400-500 k$"], [500e3, 700e3, "500-700 k$"], [700e3, 1e6, "700 k-1 M$"],122  [1e6, 2e6, "1-2 M$"], [2e6, Infinity, "2 M$ +"],123];124const txCase = TXB.map(([lo, hi], i) =>125  hi === Infinity ? `WHEN amount >= ${lo} THEN ${i}` : `WHEN amount >= ${lo} AND amount < ${hi} THEN ${i}`).join(" ");126const txBinRows = db.prepare(`SELECT CASE ${txCase} END AS b, COUNT(*) AS n FROM transactions GROUP BY b`).all();127out.tx.amount_bins = TXB.map(([, , label], i) => ({ label, value: txBinRows.find((r) => r.b === i)?.n ?? 0 }));128// activité quotidienne (26 dernières semaines du corpus) pour le calendrier129const maxDate = db.prepare("SELECT MAX(date) AS d FROM transactions").get().d;130const since = new Date(maxDate + "T12:00:00");131since.setDate(since.getDate() - 26 * 7);132out.tx.daily = db.prepare(`133  SELECT date, COUNT(*) AS n FROM transactions WHERE date >= ? GROUP BY date ORDER BY date`)134  .all(since.toISOString().slice(0, 10)).map((r) => ({ date: r.date, n: r.n }));135out.tx.max_date = maxDate;136out.tx.max = db.prepare("SELECT amount, city, date FROM transactions ORDER BY amount DESC LIMIT 1").get();137const best = out.tx.monthly.reduce((a, b) => (b.n > a.n ? b : a));138out.tx.best_month = { m: best.m, n: best.n };139// top villes par nombre de ventes140out.tx.top_villes = db.prepare(`141  SELECT city, COUNT(*) AS n, CAST(AVG(amount) AS INTEGER) AS moyenne142  FROM transactions WHERE city IS NOT NULL GROUP BY city ORDER BY n DESC LIMIT 20`).all();143console.timeEnd("tx");144145/* ---------------- indice de marché mensuel par type ---------------- */146console.time("market");147const MI_TYPE_FR = { unifamilial: "Unifamiliale", condo: "Condo", plex: "Plex", "indéterminé": "Ensemble" };148const miTypes = db.prepare("SELECT DISTINCT type_prop FROM market_index ORDER BY type_prop").all().map((r) => r.type_prop);149out.market = {150  idx_by_type: miTypes.map((t) => ({151    label: MI_TYPE_FR[t] ?? t,152    points: db.prepare("SELECT month, idx FROM market_index WHERE type_prop = ? ORDER BY month").all(t)153      .map((r) => ({ t: r.month, v: Math.round(r.idx * 1000) / 1000 })),154  })),155  ppm2_by_type: miTypes.filter((t) => t !== "indéterminé").map((t) => ({156    label: MI_TYPE_FR[t] ?? t,157    points: db.prepare("SELECT month, ppm2 FROM market_index WHERE type_prop = ? AND ppm2 IS NOT NULL ORDER BY month").all(t)158      .map((r) => ({ t: r.month, v: Math.round(r.ppm2) })),159  })),160};161console.timeEnd("market");162163writeFileSync(path.join(process.cwd(), "src", "data", "stats-v2.json"), JSON.stringify(out));164console.log("→ src/data/stats-v2.json écrit,", JSON.stringify(out).length, "octets");165