spb/vrai-prix

1// Auteur : Simon-Pierre Boucher — contact@spboucher.ai2// Données agrégées par municipalité pour les pages programmatiques SEO.3// Les agrégats ne changent qu'au rebuild de la DB : cache mémoire par processus.45import { getDb } from "./db";6import { slugify } from "./seo";78export interface MunSummary {9  nom: string;10  slug: string;11  n: number;12  lat: number;13  lng: number;14}1516export interface TypeStats {17  type: string;18  n: number;19  total2026: number;20  mediane2026: number | null;21}2223export interface MunStats {24  nom: string;25  slug: string;26  n: number;27  total2026: number;28  total2021: number;29  mediane2026: number | null;30  croissancePct: number | null;31  parType: TypeStats[];32}3334export interface SampleUnit {35  id_provinc: string;36  adresse: string | null;37  apt: string | null;38  type_prop: string;39  est_2026: number | null;40  annee_construction: number | null;41  aire_etages_m2: number | null;42}4344let munList: MunSummary[] | null = null;45let munBySlugMap: Map<string, MunSummary> | null = null;46const statsCache = new Map<string, MunStats>();4748/** Toutes les municipalités (nom, slug, nb d'unités, centroïde) — triées par taille. */49export function allMunicipalities(): MunSummary[] {50  if (!munList) {51    const rows = getDb()52      .prepare(53        `SELECT municipalite AS nom, COUNT(*) AS n,54                AVG(lat) AS lat, AVG(lng) AS lng55         FROM units WHERE municipalite IS NOT NULL AND municipalite != ''56         GROUP BY municipalite ORDER BY n DESC`57      )58      .all() as { nom: string; n: number; lat: number; lng: number }[];59    munList = rows.map((r) => ({ ...r, slug: slugify(r.nom) }));60    munBySlugMap = new Map(munList.map((m) => [m.slug, m]));61  }62  return munList;63}6465export function munBySlug(slug: string): MunSummary | undefined {66  allMunicipalities();67  return munBySlugMap!.get(slug);68}6970function median2026(nom: string, type?: string): number | null {71  const db = getDb();72  const where = type73    ? "municipalite = ? AND type_prop = ?"74    : "municipalite = ?";75  const args: (string | number)[] = type ? [nom, type] : [nom];76  const cnt = (77    db.prepare(`SELECT COUNT(*) AS c FROM units WHERE ${where} AND est_2026 IS NOT NULL`).get(...args) as { c: number }78  ).c;79  if (!cnt) return null;80  const row = db81    .prepare(82      `SELECT est_2026 AS v FROM units WHERE ${where} AND est_2026 IS NOT NULL83       ORDER BY est_2026 LIMIT 1 OFFSET ?`84    )85    .get(...args, Math.floor(cnt / 2)) as { v: number } | undefined;86  return row?.v ?? null;87}8889/** Statistiques complètes d'une municipalité (cache mémoire). */90export function munStats(nom: string): MunStats | null {91  const cached = statsCache.get(nom);92  if (cached) return cached;93  const db = getDb();94  const base = db95    .prepare(96      `SELECT COUNT(*) AS n, SUM(est_2026) AS t26, SUM(est_2021) AS t2197       FROM units WHERE municipalite = ?`98    )99    .get(nom) as { n: number; t26: number | null; t21: number | null };100  if (!base.n) return null;101  const types = db102    .prepare(103      `SELECT type_prop AS type, COUNT(*) AS n, SUM(est_2026) AS total2026104       FROM units WHERE municipalite = ?105       GROUP BY type_prop ORDER BY n DESC`106    )107    .all(nom) as { type: string; n: number; total2026: number }[];108  const stats: MunStats = {109    nom,110    slug: slugify(nom),111    n: base.n,112    total2026: base.t26 ?? 0,113    total2021: base.t21 ?? 0,114    mediane2026: median2026(nom),115    croissancePct:116      base.t21 && base.t26 ? ((base.t26 - base.t21) / base.t21) * 100 : null,117    parType: types.map((t) => ({118      ...t,119      mediane2026: median2026(nom, t.type),120    })),121  };122  statsCache.set(nom, stats);123  return stats;124}125126/** Échantillon de propriétés adressées (les plus valorisées) pour le maillage interne. */127export function sampleUnits(128  nom: string,129  type: string | null,130  limit = 24131): SampleUnit[] {132  const where = type133    ? "municipalite = ? AND type_prop = ?"134    : "municipalite = ?";135  const args: string[] = type ? [nom, type] : [nom];136  return getDb()137    .prepare(138      `SELECT id_provinc, adresse, apt, type_prop, est_2026,139              annee_construction, aire_etages_m2140       FROM units141       WHERE ${where} AND adresse IS NOT NULL AND est_2026 IS NOT NULL142       ORDER BY est_2026 DESC LIMIT ?`143    )144    .all(...args, limit) as SampleUnit[];145}146147let typePairs: { nom: string; type: string }[] | null = null;148149/** Paires municipalité × type existantes (pour le sitemap des pages types). */150export function allMunTypePairs(): { nom: string; type: string }[] {151  if (!typePairs) {152    typePairs = getDb()153      .prepare(154        `SELECT municipalite AS nom, type_prop AS type155         FROM units WHERE municipalite IS NOT NULL AND municipalite != ''156         GROUP BY municipalite, type_prop`157      )158      .all() as { nom: string; type: string }[];159  }160  return typePairs;161}162163/** Les municipalités les plus proches du centroïde (maillage entre pages voisines). */164export function neighborMunicipalities(nom: string, k = 10): MunSummary[] {165  const all = allMunicipalities();166  const me = all.find((m) => m.nom === nom);167  if (!me) return [];168  return all169    .filter((m) => m.nom !== nom)170    .map((m) => ({171      m,172      d: (m.lat - me.lat) ** 2 + 0.49 * (m.lng - me.lng) ** 2,173    }))174    .sort((a, b) => a.d - b.d)175    .slice(0, k)176    .map((x) => x.m);177}178