Vrai-Prix — l'évaluation du vrai prix des propriétés résidentielles au Québec.
TypeScript 90.2%
JavaScript 3.5%
Python 3.4%
CSS 1.9%
HTML 0.6%
1// Auteur : Simon-Pierre Boucher — contact@spboucher.ai2/**3 * Accès à la copie de la base Immo-Ka (data/immoka.db) : annonces réellement4 * à vendre au Québec + table `vp_eval` (estimation du moteur pour chaque5 * annonce, produite par scripts/eval-immoka.ts → build-marche-stats.mjs).6 *7 * Sert l'onglet « À vendre » : recherche/filtres, fiche complète, comparables8 * choisis par l'utilisateur, comparaison avec la mesure Vrai-Prix.9 */10import Database from "better-sqlite3";11import path from "path";1213let db: Database.Database | null = null;1415/** Filtre « annonce vivante » — identique à celui du pipeline eval-immoka.ts. */16const LIVE =17 "l.status = 'a-vendre' AND l.published = 1 AND l.active = 1 AND l.dup_hidden = 0 AND l.price > 0";1819export function getImmoDb(): Database.Database {20 if (!db) {21 const p = process.env.IMMOKA_DB ?? path.join(process.cwd(), "data", "immoka.db");22 db = new Database(p, { fileMustExist: true });23 db.pragma("journal_mode = WAL");24 ensureImmoIndexes(db);25 }26 return db;27}2829/**30 * Index et table FTS nécessaires aux requêtes de l'onglet (no-op si présents).31 * Appelé à l'ouverture et par le pipeline de rafraîchissement.32 */33export function ensureImmoIndexes(d: Database.Database): void {34 d.exec(`35 CREATE INDEX IF NOT EXISTS idx_listings_live36 ON listings(status, published, active, dup_hidden, price);37 CREATE INDEX IF NOT EXISTS idx_listings_city_nocase ON listings(city COLLATE NOCASE);38 CREATE INDEX IF NOT EXISTS idx_listings_last_seen ON listings(last_seen);39 `);40 const hasEval = d41 .prepare("SELECT 1 FROM sqlite_master WHERE type='table' AND name='vp_eval'")42 .get();43 if (hasEval) {44 d.exec("CREATE INDEX IF NOT EXISTS idx_vp_eval_muni ON vp_eval(municipalite)");45 }46 const hasFts = d47 .prepare("SELECT 1 FROM sqlite_master WHERE type='table' AND name='listings_fts'")48 .get();49 if (!hasFts) rebuildListingsFts(d);50}5152/** Table FTS5 des annonces vivantes (adresse, ville, secteur, titre, MLS). */53export function rebuildListingsFts(d: Database.Database): void {54 d.exec(`55 DROP TABLE IF EXISTS listings_fts;56 CREATE VIRTUAL TABLE listings_fts USING fts5(57 uid UNINDEXED, address, city, sector, title, mls,58 tokenize = 'unicode61 remove_diacritics 2'59 );60 INSERT INTO listings_fts(uid, address, city, sector, title, mls)61 SELECT l.uid, COALESCE(l.address,''), COALESCE(l.city,''), COALESCE(l.sector,''),62 COALESCE(l.title,''), COALESCE(l.mls,'')63 FROM listings l WHERE ${LIVE};64 `);65}6667/* ------------------------------------------------------------------ types */6869export interface ListingRow {70 uid: string;71 source: string;72 external_id: string;73 url: string | null;74 title: string | null;75 address: string | null;76 sector: string | null;77 city: string | null;78 region: string | null;79 property_type: string | null;80 price: number;81 price_label: string | null;82 bedrooms: number | null;83 bathrooms: number | null;84 powder_rooms: number | null;85 area_sqft: number | null;86 lot_sqft: number | null;87 year_built: number | null;88 mls: string | null;89 status: string;90 broker_name: string | null;91 agency: string | null;92 description: string | null;93 features: string | null;94 details: string | null;95 images: string | null;96 lat: number | null;97 lng: number | null;98 first_seen: number | null;99 last_seen: number | null;100 updated_at: number | null;101 quality_score: number | null;102}103104export interface EvalRow {105 uid: string;106 unit_id: string | null;107 match_m: number | null;108 price: number | null;109 est: number | null;110 low: number | null;111 high: number | null;112 confidence_pct: number | null;113 confidence: string | null;114 model_est: number | null;115 comps_est: number | null;116 model_weight: number | null;117 n_comps: number | null;118 cost_est: number | null;119 role_est: number | null;120 ens_est: number | null;121 valeur_role: number | null;122 type_prop: string | null;123 municipalite: string | null;124 ratio: number | null;125 diff: number | null;126 evaluated_at: string | null;127}128129/** Carte d'annonce (liste, cartes, voisins). */130export interface ListingCard {131 uid: string;132 source: string;133 url: string | null;134 address: string | null;135 city: string | null;136 sector: string | null;137 propertyType: string | null;138 group: TypeGroupKey;139 price: number;140 bedrooms: number | null;141 bathrooms: number | null;142 areaSqft: number | null;143 lotSqft: number | null;144 yearBuilt: number | null;145 image: string | null;146 nImages: number;147 lat: number | null;148 lng: number | null;149 firstSeen: number | null;150 est: number | null;151 low: number | null;152 high: number | null;153 confidence: string | null;154 ratio: number | null; // est / prix demandé155 municipalite: string | null;156 distanceM?: number;157}158159/* ----------------------------------------------------- groupes de types */160// (module pur src/lib/listing-types.ts — réexporté ici pour le serveur)161import { TYPE_GROUPS, groupToTypeProp, typeGroup, type TypeGroupKey } from "./listing-types";162export { TYPE_GROUPS, groupToTypeProp, typeGroup, type TypeGroupKey };163164let typeMap: Map<TypeGroupKey, string[]> | null = null;165/** Types Immo-Ka bruts rattachés à chaque groupe (calculé une fois sur la copie). */166export function rawTypesByGroup(): Map<TypeGroupKey, string[]> {167 if (!typeMap) {168 const rows = getImmoDb()169 .prepare(`SELECT DISTINCT l.property_type AS t FROM listings l WHERE ${LIVE}`)170 .all() as { t: string | null }[];171 typeMap = new Map();172 for (const { t } of rows) {173 const g = typeGroup(t);174 if (!typeMap.has(g)) typeMap.set(g, []);175 typeMap.get(g)!.push(t ?? "");176 }177 }178 return typeMap;179}180181/* --------------------------------------------------------------- helpers */182183function firstImage(images: string | null): { first: string | null; n: number } {184 if (!images) return { first: null, n: 0 };185 try {186 const arr = JSON.parse(images);187 if (Array.isArray(arr) && arr.length) {188 const urls = arr.filter((x): x is string => typeof x === "string" && /^https?:/.test(x));189 return { first: urls[0] ?? null, n: urls.length };190 }191 } catch {}192 return { first: null, n: 0 };193}194195export function parseImages(images: string | null): string[] {196 if (!images) return [];197 try {198 const arr = JSON.parse(images);199 return Array.isArray(arr) ? arr.filter((x): x is string => typeof x === "string" && /^https?:/.test(x)) : [];200 } catch {201 return [];202 }203}204205type CardSrc = ListingRow & Partial<EvalRow> & { e_est?: number | null };206207function toCard(r: CardSrc & { est?: number | null; low?: number | null; high?: number | null; confidence?: string | null; ratio?: number | null; municipalite?: string | null }): ListingCard {208 const img = firstImage(r.images);209 return {210 uid: r.uid,211 source: r.source,212 url: r.url,213 address: r.address,214 city: r.city,215 sector: r.sector,216 propertyType: r.property_type,217 group: typeGroup(r.property_type),218 price: r.price,219 bedrooms: r.bedrooms,220 bathrooms: r.bathrooms,221 areaSqft: r.area_sqft,222 lotSqft: r.lot_sqft,223 yearBuilt: r.year_built,224 image: img.first,225 nImages: img.n,226 lat: r.lat,227 lng: r.lng,228 firstSeen: r.first_seen,229 est: r.est ?? null,230 low: r.low ?? null,231 high: r.high ?? null,232 confidence: r.confidence ?? null,233 ratio: r.ratio ?? null,234 municipalite: r.municipalite ?? null,235 };236}237238const CARD_COLS = `l.uid, l.source, l.url, l.address, l.city, l.sector, l.property_type, l.price,239 l.bedrooms, l.bathrooms, l.area_sqft, l.lot_sqft, l.year_built, l.images, l.lat, l.lng, l.first_seen,240 e.est, e.low, e.high, e.confidence, e.ratio, e.municipalite`;241242/* -------------------------------------------------------------- recherche */243244export type SortKey = "recent" | "price_asc" | "price_desc" | "deal" | "premium" | "est_desc";245246export interface SearchParams {247 q?: string;248 muni?: string;249 group?: TypeGroupKey | "";250 min?: number;251 max?: number;252 beds?: number;253 evalOnly?: boolean;254 sort?: SortKey;255 page?: number;256 per?: number;257}258259export interface SearchResult {260 total: number;261 page: number;262 per: number;263 items: ListingCard[];264 facets: {265 groups: { key: TypeGroupKey; n: number }[];266 munis: { name: string; n: number }[];267 };268 summary: { medianPrice: number | null; medianRatio: number | null; nEval: number };269}270271function ftsQuery(q: string): string | null {272 const terms = q273 .replace(/[^\p{L}\p{N}\s'-]/gu, " ")274 .trim()275 .split(/\s+/)276 .filter((t) => t.length > 0)277 .map((t) => `"${t.replace(/"/g, "")}"*`);278 return terms.length ? terms.join(" ") : null;279}280281export function searchListings(p: SearchParams): SearchResult {282 const d = getImmoDb();283 const per = Math.min(Math.max(p.per ?? 24, 6), 60);284 const page = Math.max(p.page ?? 1, 1);285 const where: string[] = [LIVE];286 const args: unknown[] = [];287288 if (p.q && p.q.trim()) {289 const fq = ftsQuery(p.q);290 if (fq) {291 where.push("l.uid IN (SELECT uid FROM listings_fts WHERE listings_fts MATCH ?)");292 args.push(fq);293 }294 }295 if (p.muni) {296 where.push("(e.municipalite = ? COLLATE NOCASE OR l.city = ? COLLATE NOCASE)");297 args.push(p.muni, p.muni);298 }299 if (p.min && p.min > 0) {300 where.push("l.price >= ?");301 args.push(p.min);302 }303 if (p.max && p.max > 0) {304 where.push("l.price <= ?");305 args.push(p.max);306 }307 if (p.beds && p.beds > 0) {308 where.push("l.bedrooms >= ?");309 args.push(p.beds);310 }311 if (p.evalOnly) where.push("e.est IS NOT NULL AND e.est > 0");312313 // le filtre de groupe s'applique via la liste des types bruts du groupe314 const groupWhere: string[] = [];315 const groupArgs: unknown[] = [];316 if (p.group) {317 const raws = rawTypesByGroup().get(p.group) ?? [];318 if (raws.length) {319 const hasNull = raws.includes("");320 const nn = raws.filter((r) => r !== "");321 const parts: string[] = [];322 if (nn.length) {323 parts.push(`l.property_type IN (${nn.map(() => "?").join(",")})`);324 groupArgs.push(...nn);325 }326 if (hasNull) parts.push("l.property_type IS NULL OR l.property_type = ''");327 groupWhere.push(`(${parts.join(" OR ")})`);328 } else {329 groupWhere.push("0");330 }331 }332333 const base = `FROM listings l LEFT JOIN vp_eval e ON e.uid = l.uid WHERE ${where.join(" AND ")}`;334 const full = groupWhere.length ? `${base} AND ${groupWhere.join(" AND ")}` : base;335 const fullArgs = [...args, ...groupArgs];336337 const total = (d.prepare(`SELECT COUNT(*) AS n ${full}`).get(...fullArgs) as { n: number }).n;338339 const order: Record<SortKey, string> = {340 recent: "l.first_seen DESC",341 price_asc: "l.price ASC",342 price_desc: "l.price DESC",343 deal: "CASE WHEN e.ratio IS NULL THEN 1 ELSE 0 END, e.ratio DESC", // estimation ≫ prix demandé344 premium: "CASE WHEN e.ratio IS NULL THEN 1 ELSE 0 END, e.ratio ASC",345 est_desc: "CASE WHEN e.est IS NULL THEN 1 ELSE 0 END, e.est DESC",346 };347 const rows = d348 .prepare(`SELECT ${CARD_COLS} ${full} ORDER BY ${order[p.sort ?? "recent"]}, l.uid LIMIT ? OFFSET ?`)349 .all(...fullArgs, per, (page - 1) * per) as CardSrc[];350351 // facettes : groupes (sans le filtre de groupe) et municipalités (avec)352 const typeCounts = d353 .prepare(`SELECT l.property_type AS t, COUNT(*) AS n ${base} GROUP BY l.property_type`)354 .all(...args) as { t: string | null; n: number }[];355 const gmap = new Map<TypeGroupKey, number>();356 for (const r of typeCounts) {357 const g = typeGroup(r.t);358 gmap.set(g, (gmap.get(g) ?? 0) + r.n);359 }360 const groups = TYPE_GROUPS.map((g) => ({ key: g.key, n: gmap.get(g.key) ?? 0 })).filter((g) => g.n > 0);361362 const munis = d363 .prepare(364 `SELECT COALESCE(e.municipalite, l.city) AS name, COUNT(*) AS n ${full}365 AND COALESCE(e.municipalite, l.city) IS NOT NULL AND COALESCE(e.municipalite, l.city) != ''366 GROUP BY name ORDER BY n DESC LIMIT 60`367 )368 .all(...fullArgs) as { name: string; n: number }[];369370 const summary = d.prepare(`SELECT COUNT(e.est) AS nEval ${full}`).get(...fullArgs) as { nEval: number };371372 return {373 total,374 page,375 per,376 items: rows.map(toCard),377 facets: { groups, munis },378 summary: {379 medianPrice: medianOf(d, `SELECT l.price AS v ${full}`, fullArgs, total),380 medianRatio: medianOf(d, `SELECT e.ratio AS v ${full} AND e.ratio IS NOT NULL`, fullArgs, summary.nEval),381 nEval: summary.nEval,382 },383 };384}385386/** Médiane par OFFSET (évite de charger la colonne entière). */387function medianOf(d: Database.Database, sql: string, args: unknown[], n: number): number | null {388 if (!n) return null;389 const row = d390 .prepare(`${sql} ORDER BY v LIMIT 1 OFFSET ?`)391 .get(...args, Math.floor(n / 2)) as { v: number } | undefined;392 return row?.v ?? null;393}394395/* ------------------------------------------------------------------ fiche */396397export interface PricePoint {398 ts: number;399 price: number;400}401402export interface ListingDetail {403 listing: ListingRow;404 images: string[];405 features: string[];406 details: Record<string, unknown>;407 priceLog: PricePoint[];408 eval: EvalRow | null;409 group: TypeGroupKey;410}411412export function getListing(uid: string): ListingDetail | null {413 const d = getImmoDb();414 const l = d.prepare("SELECT * FROM listings l WHERE l.uid = ?").get(uid) as ListingRow | undefined;415 if (!l) return null;416 const ev = (d.prepare("SELECT * FROM vp_eval WHERE uid = ?").get(uid) as EvalRow | undefined) ?? null;417 const priceLog = d418 .prepare("SELECT ts, price FROM price_log WHERE uid = ? AND price IS NOT NULL ORDER BY ts")419 .all(uid) as PricePoint[];420 let features: string[] = [];421 try {422 const f = l.features ? JSON.parse(l.features) : [];423 features = Array.isArray(f) ? f.filter((x): x is string => typeof x === "string") : [];424 } catch {}425 let details: Record<string, unknown> = {};426 try {427 const dd = l.details ? JSON.parse(l.details) : {};428 if (dd && typeof dd === "object" && !Array.isArray(dd)) details = dd as Record<string, unknown>;429 } catch {}430 return { listing: l, images: parseImages(l.images), features, details, priceLog, eval: ev, group: typeGroup(l.property_type) };431}432433/** Annonces vivantes autour d'un point (même groupe optionnel), triées par distance. */434export function listingsNear(435 lat: number,436 lng: number,437 radiusKm: number,438 opts: { group?: TypeGroupKey; excludeUid?: string; limit?: number } = {}439): ListingCard[] {440 const d = getImmoDb();441 const halfLat = radiusKm / 111;442 const halfLng = radiusKm / (111 * Math.cos((lat * Math.PI) / 180));443 const rows = d444 .prepare(445 `SELECT ${CARD_COLS} FROM listings l LEFT JOIN vp_eval e ON e.uid = l.uid446 WHERE ${LIVE} AND l.lat BETWEEN ? AND ? AND l.lng BETWEEN ? AND ?447 ORDER BY (l.lat - ?) * (l.lat - ?) + ${Math.cos((lat * Math.PI) / 180) ** 2} * (l.lng - ?) * (l.lng - ?)448 LIMIT ?`449 )450 .all(lat - halfLat, lat + halfLat, lng - halfLng, lng + halfLng, lat, lat, lng, lng, (opts.limit ?? 40) * 4) as CardSrc[];451 const out: ListingCard[] = [];452 for (const r of rows) {453 if (opts.excludeUid && r.uid === opts.excludeUid) continue;454 const c = toCard(r);455 if (opts.group && c.group !== opts.group) continue;456 if (c.lat == null || c.lng == null) continue;457 c.distanceM = Math.round(haversine(lat, lng, c.lat, c.lng));458 if (c.distanceM > radiusKm * 1000) continue;459 out.push(c);460 if (out.length >= (opts.limit ?? 40)) break;461 }462 return out;463}464465function haversine(lat1: number, lng1: number, lat2: number, lng2: number): number {466 const R = 6371000;467 const toRad = (x: number) => (x * Math.PI) / 180;468 const dLat = toRad(lat2 - lat1);469 const dLng = toRad(lng2 - lng1);470 const a = Math.sin(dLat / 2) ** 2 + Math.cos(toRad(lat1)) * Math.cos(toRad(lat2)) * Math.sin(dLng / 2) ** 2;471 return 2 * R * Math.asin(Math.sqrt(a));472}473474/* ------------------------------------------------------- vue d'ensemble */475476export interface AVendreOverview {477 total: number;478 evaluated: number;479 medianPrice: number | null;480 medianRatio: number | null;481 copiedAt: string | null;482 groups: { key: TypeGroupKey; n: number }[];483 topMunis: { name: string; n: number }[];484}485486let overviewCache: { at: number; v: AVendreOverview } | null = null;487export function avendreOverview(): AVendreOverview {488 if (overviewCache && Date.now() - overviewCache.at < 10 * 60 * 1000) return overviewCache.v;489 const d = getImmoDb();490 const base = `FROM listings l LEFT JOIN vp_eval e ON e.uid = l.uid WHERE ${LIVE}`;491 const tot = d.prepare(`SELECT COUNT(*) AS n, COUNT(e.est) AS ne ${base}`).get() as { n: number; ne: number };492 const typeCounts = d.prepare(`SELECT l.property_type AS t, COUNT(*) AS n ${base} GROUP BY 1`).all() as {493 t: string | null;494 n: number;495 }[];496 const gmap = new Map<TypeGroupKey, number>();497 for (const r of typeCounts) gmap.set(typeGroup(r.t), (gmap.get(typeGroup(r.t)) ?? 0) + r.n);498 const evAt = d.prepare("SELECT MAX(evaluated_at) AS at FROM vp_eval").get() as { at: string | null } | undefined;499 const topMunis = d500 .prepare(501 `SELECT COALESCE(e.municipalite, l.city) AS name, COUNT(*) AS n ${base}502 AND COALESCE(e.municipalite, l.city) != '' GROUP BY name ORDER BY n DESC LIMIT 12`503 )504 .all() as { name: string; n: number }[];505 const v: AVendreOverview = {506 total: tot.n,507 evaluated: tot.ne,508 medianPrice: medianOf(d, `SELECT l.price AS v ${base}`, [], tot.n),509 medianRatio: medianOf(d, `SELECT e.ratio AS v ${base} AND e.ratio IS NOT NULL`, [], tot.ne),510 copiedAt: evAt?.at ?? null,511 groups: TYPE_GROUPS.map((g) => ({ key: g.key, n: gmap.get(g.key) ?? 0 })).filter((g) => g.n > 0),512 topMunis,513 };514 overviewCache = { at: Date.now(), v };515 return v;516}517