TypeScript 90.1%
JavaScript 3.2%
Python 3.2%
CSS 1.8%
HTML 1.8%
1// UQO Éval — laboratoire pédagogique d'évaluation immobilière2import 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 // Index requis par les requêtes ci-dessous (COLLATE NOCASE : l'index BINARY14 // idx_units_mun est inutilisable → scan de 3,7 M de lignes qui gelait15 // l'event loop sous trafic, panne du 2026-08-25). No-op si déjà présents.16 db.exec(`17 CREATE INDEX IF NOT EXISTS idx_units_mun_nocase_geo18 ON units(municipalite COLLATE NOCASE, lat, lng);19 CREATE INDEX IF NOT EXISTS idx_units_mun_nocase_est20 ON units(municipalite COLLATE NOCASE, est_2026);21 CREATE INDEX IF NOT EXISTS idx_tx_recent22 ON transactions(date DESC, amount DESC)23 WHERE id_provinc IS NOT NULL AND street IS NOT NULL AND amount >= 50000;24 `);25 }26 return db;27}2829export interface UnitRow {30 id_provinc: string;31 adresse: string | null;32 apt: string | null;33 municipalite: string | null;34 arrond: string | null;35 code_mun: string | null;36 lat: number;37 lng: number;38 cubf: number | null;39 cubf_libelle: string | null;40 type_prop: string;41 annee_construction: number | null;42 annee_estimee: string | null;43 aire_etages_m2: number | null;44 superficie_terrain_m2: number | null;45 front_terrain_m: number | null;46 nb_etages: number | null;47 nb_logements: number | null;48 nb_locaux_non_resid: number | null;49 nb_chambres_locatives: number | null;50 lien_physique: string | null;51 genre_construction: string | null;52 matricule: string | null;53 unite_voisinage: string | null;54 n_adresses: number | null;55 dat_cond_marche: string | null;56 valeur_terrain: number | null;57 valeur_batiment: number | null;58 valeur_role: number | null;59 valeur_anterieure: number | null;60 est_2021: number | null;61 est_2022: number | null;62 est_2023: number | null;63 est_2024: number | null;64 est_2025: number | null;65 est_2026: number | null;66 p10: number | null;67 p90: number | null;68 est_hedo: number | null;69}7071export interface TxRow {72 id: string;73 date: string;74 amount: number;75 street: string | null;76 city: string | null;77 lat: number;78 lng: number;79 property_type: string | null;80 year_built: number | null;81 floor_area: number | null;82 building_type: string | null;83 id_provinc: string | null;84 valeur_role: number | null;85 land_area: number | null;86}8788export function getUnit(id: string): UnitRow | undefined {89 return getDb()90 .prepare("SELECT * FROM units WHERE id_provinc = ?")91 .get(id) as UnitRow | undefined;92}9394/** Candidats comparables dans une boîte englobante autour du point. */95export function getCandidates(96 lat: number,97 lng: number,98 halfDeg: number,99 sinceDate: string,100 limit = 500101): TxRow[] {102 return getDb()103 .prepare(104 `SELECT * FROM transactions105 WHERE lat BETWEEN ? AND ? AND lng BETWEEN ? AND ?106 AND date >= ? AND amount >= 50000107 LIMIT ?`108 )109 .all(lat - halfDeg, lat + halfDeg, lng - halfDeg, lng + halfDeg, sinceDate, limit) as TxRow[];110}111112export function getMarketIndex(typeProp: string): { month: string; idx: number }[] {113 return getDb()114 .prepare(115 "SELECT month, idx FROM market_index WHERE type_prop = ? ORDER BY month"116 )117 .all(typeProp) as { month: string; idx: number }[];118}119120export function searchUnits(q: string, limit = 8): (UnitRow & { score: number })[] {121 const cleaned = q122 .replace(/[^\p{L}\p{N}\s'-]/gu, " ")123 .trim()124 .split(/\s+/)125 .filter((t) => t.length > 0)126 .map((t) => `"${t}"*`)127 .join(" ");128 if (!cleaned) return [];129 return getDb()130 .prepare(131 `SELECT u.*, bm25(units_fts) AS score132 FROM units_fts JOIN units u ON u.rowid = units_fts.rowid133 WHERE units_fts MATCH ?134 ORDER BY score LIMIT ?`135 )136 .all(cleaned, limit) as (UnitRow & { score: number })[];137}138139export interface NearbyUnitRow {140 id_provinc: string;141 adresse: string | null;142 apt: string | null;143 municipalite: string | null;144 type_prop: string;145 lat: number;146 lng: number;147 est_2026: number | null;148}149150/** Unités d'évaluation dans une boîte englobante (recherche par carte). */151export function unitsNear(152 lat: number,153 lng: number,154 halfLat: number,155 halfLng: number,156 limit = 300157): NearbyUnitRow[] {158 // ORDER BY distance au centre : sans tri, l'index (lat, lng) renverrait159 // les N unités les plus au sud de la boîte (bande horizontale).160 return getDb()161 .prepare(162 `SELECT id_provinc, adresse, apt, municipalite, type_prop, lat, lng, est_2026163 FROM units164 WHERE lat BETWEEN ? AND ? AND lng BETWEEN ? AND ?165 ORDER BY (lat - ?) * (lat - ?) + 0.49 * (lng - ?) * (lng - ?)166 LIMIT ?`167 )168 .all(169 lat - halfLat,170 lat + halfLat,171 lng - halfLng,172 lng + halfLng,173 lat,174 lat,175 lng,176 lng,177 limit178 ) as NearbyUnitRow[];179}180181export function municipalityCenter(182 name: string183): { lat: number; lng: number; n: number } | undefined {184 return getDb()185 .prepare(186 `SELECT AVG(lat) AS lat, AVG(lng) AS lng, COUNT(*) AS n187 FROM units WHERE municipalite = ? COLLATE NOCASE`188 )189 .get(name) as { lat: number; lng: number; n: number } | undefined;190}191192/** Rang de la valeur dans la municipalité (percentile sur est_2026). */193export function municipalityPercentile(194 municipalite: string,195 value: number196): { below: number; total: number } | null {197 const row = getDb()198 .prepare(199 `SELECT COUNT(*) AS total,200 SUM(CASE WHEN est_2026 < ? THEN 1 ELSE 0 END) AS below201 FROM units202 WHERE municipalite = ? COLLATE NOCASE203 AND est_2026 IS NOT NULL AND est_2026 > 0`204 )205 .get(value, municipalite) as { total: number; below: number | null } | undefined;206 if (!row || row.total < 30) return null;207 return { below: row.below ?? 0, total: row.total };208}209210export interface RecentSaleRow {211 id: string;212 date: string;213 amount: number;214 street: string | null;215 city: string | null;216 property_type: string | null;217 id_provinc: string | null;218 valeur_role: number | null;219}220221/** Dernières ventes publiées appariées à une unité (fil « ventes récentes »). */222export function recentSales(limit = 6): RecentSaleRow[] {223 return getDb()224 .prepare(225 `SELECT id, date, amount, street, city, property_type, id_provinc, valeur_role226 FROM transactions227 WHERE id_provinc IS NOT NULL AND street IS NOT NULL AND amount >= 50000228 ORDER BY date DESC, amount DESC229 LIMIT ?`230 )231 .all(limit) as RecentSaleRow[];232}233234export function salesCountSince(sinceDate: string): number {235 const row = getDb()236 .prepare("SELECT COUNT(*) AS n FROM transactions WHERE date >= ?")237 .get(sinceDate) as { n: number };238 return row.n;239}240241export function saveLead(email: string, unitId: string | null, estimate: number | null): void {242 getDb()243 .prepare("INSERT INTO leads (email, unit_id, estimate) VALUES (?, ?, ?)")244 .run(email, unitId, estimate);245}246