spb/valoplex 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.VALOPLEX_DB ?? path.join(process.cwd(), "data", "valoplex.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  adresses_json: string | null;49  est_2021: number | null;50  est_2022: number | null;51  est_2023: number | null;52  est_2024: number | null;53  est_2025: number | null;54  est_2026: number | null;55  p10: number | null;56  p90: number | null;57  est_hedo: number | null;58}5960export interface TxRow {61  id: string;62  date: string;63  amount: number;64  street: string | null;65  city: string | null;66  lat: number;67  lng: number;68  property_type: string | null;69  year_built: number | null;70  floor_area: number | null;71  building_type: string | null;72  id_provinc: string | null;73  valeur_role: number | null;74  land_area: number | null;75  portes: number | null;76}7778export function getUnit(id: string): UnitRow | undefined {79  return getDb()80    .prepare("SELECT * FROM units WHERE id_provinc = ?")81    .get(id) as UnitRow | undefined;82}8384/** Candidats comparables dans une boîte englobante autour du point. */85export function getCandidates(86  lat: number,87  lng: number,88  halfDeg: number,89  sinceDate: string,90  limit = 50091): TxRow[] {92  return getDb()93    .prepare(94      `SELECT * FROM transactions95       WHERE lat BETWEEN ? AND ? AND lng BETWEEN ? AND ?96         AND date >= ? AND amount >= 5000097       LIMIT ?`98    )99    .all(lat - halfDeg, lat + halfDeg, lng - halfDeg, lng + halfDeg, sinceDate, limit) as TxRow[];100}101102export function getMarketIndex(typeProp: string): { month: string; idx: number }[] {103  return getDb()104    .prepare(105      "SELECT month, idx FROM market_index WHERE type_prop = ? ORDER BY month"106    )107    .all(typeProp) as { month: string; idx: number }[];108}109110export function searchUnits(q: string, limit = 8): (UnitRow & { score: number })[] {111  const cleaned = q112    .replace(/[^\p{L}\p{N}\s'-]/gu, " ")113    .trim()114    .split(/\s+/)115    .filter((t) => t.length > 0)116    .map((t) => `"${t}"*`)117    .join(" ");118  if (!cleaned) return [];119  return getDb()120    .prepare(121      `SELECT u.*, bm25(units_fts) AS score122       FROM units_fts JOIN units u ON u.rowid = units_fts.rowid123       WHERE units_fts MATCH ?124       ORDER BY score LIMIT ?`125    )126    .all(cleaned, limit) as (UnitRow & { score: number })[];127}128129export function municipalityCenter(130  name: string131): { lat: number; lng: number; n: number } | undefined {132  return getDb()133    .prepare(134      `SELECT AVG(lat) AS lat, AVG(lng) AS lng, COUNT(*) AS n135       FROM units WHERE municipalite = ? COLLATE NOCASE`136    )137    .get(name) as { lat: number; lng: number; n: number } | undefined;138}139140export function saveLead(email: string, unitId: string | null, estimate: number | null): void {141  getDb()142    .prepare("INSERT INTO leads (email, unit_id, estimate) VALUES (?, ?, ?)")143    .run(email, unitId, estimate);144}145