spb/vrai-prix Public

1// Auteur : Simon-Pierre Boucher — contact@spboucher.ai2import Database from "better-sqlite3";3import path from "path";45let db: Database.Database | null = null;67export function getDb(): Database.Database {8  if (!db) {9    const p =10      process.env.VRAIPRIX_DB ?? path.join(process.cwd(), "data", "vraiprix.db");11    db = new Database(p, { fileMustExist: true });12    db.pragma("journal_mode = WAL");13  }14  return db;15}1617export interface UnitRow {18  id_provinc: string;19  adresse: string | null;20  apt: string | null;21  municipalite: string | null;22  arrond: string | null;23  code_mun: string | null;24  lat: number;25  lng: number;26  cubf: number | null;27  cubf_libelle: string | null;28  type_prop: string;29  annee_construction: number | null;30  annee_estimee: string | null;31  aire_etages_m2: number | null;32  superficie_terrain_m2: number | null;33  front_terrain_m: number | null;34  nb_etages: number | null;35  nb_logements: number | null;36  nb_locaux_non_resid: number | null;37  nb_chambres_locatives: number | null;38  lien_physique: string | null;39  genre_construction: string | null;40  matricule: string | null;41  unite_voisinage: string | null;42  n_adresses: number | null;43  dat_cond_marche: string | null;44  valeur_terrain: number | null;45  valeur_batiment: number | null;46  valeur_role: number | null;47  valeur_anterieure: number | null;48  est_2021: number | null;49  est_2022: number | null;50  est_2023: number | null;51  est_2024: number | null;52  est_2025: number | null;53  est_2026: number | null;54  p10: number | null;55  p90: number | null;56  est_hedo: number | null;57}5859export interface TxRow {60  id: string;61  date: string;62  amount: number;63  street: string | null;64  city: string | null;65  lat: number;66  lng: number;67  property_type: string | null;68  year_built: number | null;69  floor_area: number | null;70  building_type: string | null;71  id_provinc: string | null;72  valeur_role: number | null;73  land_area: number | null;74}7576export function getUnit(id: string): UnitRow | undefined {77  return getDb()78    .prepare("SELECT * FROM units WHERE id_provinc = ?")79    .get(id) as UnitRow | undefined;80}8182/** Candidats comparables dans une boîte englobante autour du point. */83export function getCandidates(84  lat: number,85  lng: number,86  halfDeg: number,87  sinceDate: string,88  limit = 50089): TxRow[] {90  return getDb()91    .prepare(92      `SELECT * FROM transactions93       WHERE lat BETWEEN ? AND ? AND lng BETWEEN ? AND ?94         AND date >= ? AND amount >= 5000095       LIMIT ?`96    )97    .all(lat - halfDeg, lat + halfDeg, lng - halfDeg, lng + halfDeg, sinceDate, limit) as TxRow[];98}99100export function getMarketIndex(typeProp: string): { month: string; idx: number }[] {101  return getDb()102    .prepare(103      "SELECT month, idx FROM market_index WHERE type_prop = ? ORDER BY month"104    )105    .all(typeProp) as { month: string; idx: number }[];106}107108export function searchUnits(q: string, limit = 8): (UnitRow & { score: number })[] {109  const cleaned = q110    .replace(/[^\p{L}\p{N}\s'-]/gu, " ")111    .trim()112    .split(/\s+/)113    .filter((t) => t.length > 0)114    .map((t) => `"${t}"*`)115    .join(" ");116  if (!cleaned) return [];117  return getDb()118    .prepare(119      `SELECT u.*, bm25(units_fts) AS score120       FROM units_fts JOIN units u ON u.rowid = units_fts.rowid121       WHERE units_fts MATCH ?122       ORDER BY score LIMIT ?`123    )124    .all(cleaned, limit) as (UnitRow & { score: number })[];125}126127export function municipalityCenter(128  name: string129): { lat: number; lng: number; n: number } | undefined {130  return getDb()131    .prepare(132      `SELECT AVG(lat) AS lat, AVG(lng) AS lng, COUNT(*) AS n133       FROM units WHERE municipalite = ? COLLATE NOCASE`134    )135    .get(name) as { lat: number; lng: number; n: number } | undefined;136}137138export function saveLead(email: string, unitId: string | null, estimate: number | null): void {139  getDb()140    .prepare("INSERT INTO leads (email, unit_id, estimate) VALUES (?, ?, ?)")141    .run(email, unitId, estimate);142}143