Repository Public
1// Auteur : Simon-Pierre Boucher — contact@spboucher.ai2// Registre des ventes récentes : lecture de la table `transactions`, alimentée3// en continu par le connecteur scripts/ingest-jdm.mjs (flux « Transactions4// immobilières » du Journal de Québec / Journal de Montréal — api.qub.ca,5// même feed pour les deux quotidiens). Chaque vente est appariée à son unité6// d'évaluation (id_provinc) par le connecteur : le registre mène donc à la7// fiche d'estimation de la propriété.8import { getDb } from "./db";910export const VENTE_TYPES = ["unifamilial", "condo", "plex", "indéterminé"] as const;11export type VenteType = (typeof VENTE_TYPES)[number];1213/** Libellés français des types de la source (regroupement du rôle, cf. méthodologie JdQ). */14export const VENTE_TYPE_LABELS: Record<string, string> = {15 unifamilial: "Unifamiliale",16 condo: "Condo",17 plex: "Plex",18 indéterminé: "Autre",19};2021/** Périodes proposées (jours) ; `all` = tout le registre. */22export const VENTE_PERIODES: Record<string, number | null> = {23 "30": 30,24 "90": 90,25 "365": 365,26 all: null,27};28export const PERIODE_LABELS: Record<string, string> = {29 "30": "30 derniers jours",30 "90": "90 derniers jours",31 "365": "12 derniers mois",32 all: "Tout le registre (depuis 2021)",33};3435export const VENTES_PAGE_SIZE = 50;36const MAX_PAGE_SIZE = 200;3738export interface VenteFilters {39 /** municipalité (nom exact, insensible à la casse) ou début de nom */40 q: string;41 type: string;42 min: number | null;43 max: number | null;44 periode: string;45 page: number;46 limit: number;47}4849export interface VenteRow {50 id: string;51 date: string;52 amount: number;53 street: string | null;54 city: string | null;55 property_type: string | null;56 year_built: number | null;57 floor_area: number | null;58 land_area: number | null;59 id_provinc: string | null;60 valeur_role: number | null;61}6263export interface VentesPage {64 rows: VenteRow[];65 total: number;66 page: number;67 pages: number;68 limit: number;69 since: string | null;70 filters: VenteFilters;71}7273export interface VentesOverview {74 maxDate: string | null;75 n30: number;76 n90: number;77 n365: number;78 median30: number | null;79 byType30: { type: string; n: number; median: number | null }[];80}8182function isoDaysAgo(days: number): string {83 const d = new Date();84 d.setUTCDate(d.getUTCDate() - days);85 return d.toISOString().slice(0, 10);86}8788function toInt(v: unknown, fallback: number): number {89 const n = typeof v === "string" ? parseInt(v.replace(/[^\d]/g, ""), 10) : Number(v);90 return Number.isFinite(n) && n >= 0 ? n : fallback;91}9293/** Normalise des paramètres d'URL (page ou API) en filtres sûrs. */94export function parseVenteFilters(95 sp: Record<string, string | string[] | undefined> | URLSearchParams96): VenteFilters {97 const get = (k: string): string => {98 const v = sp instanceof URLSearchParams ? sp.get(k) : sp[k];99 const s = Array.isArray(v) ? v[0] : v;100 return (s ?? "").toString().trim();101 };102 const type = get("type");103 const periode = get("periode");104 const minS = get("min");105 const maxS = get("max");106 return {107 q: get("q").slice(0, 80),108 type: (VENTE_TYPES as readonly string[]).includes(type) ? type : "",109 min: minS ? toInt(minS, 0) || null : null,110 max: maxS ? toInt(maxS, 0) || null : null,111 periode: periode in VENTE_PERIODES ? periode : "90",112 page: Math.max(1, toInt(get("page"), 1)),113 limit: Math.min(MAX_PAGE_SIZE, Math.max(1, toInt(get("limit"), VENTES_PAGE_SIZE))),114 };115}116117/** Clause WHERE + paramètres pour un jeu de filtres. */118function whereFor(f: VenteFilters): { where: string; args: (string | number)[]; since: string | null } {119 const conds: string[] = ["amount > 0"];120 const args: (string | number)[] = [];121 const days = VENTE_PERIODES[f.periode] ?? 90;122 const since = days == null ? null : isoDaysAgo(days);123 if (since) {124 conds.push("date >= ?");125 args.push(since);126 }127 if (f.q) {128 // nom exact d'abord (index city NOCASE) ; sinon début de nom129 const exact = (130 getDb()131 .prepare("SELECT 1 AS ok FROM transactions WHERE city = ? COLLATE NOCASE LIMIT 1")132 .get(f.q) as { ok: number } | undefined133 )?.ok;134 if (exact) {135 conds.push("city = ? COLLATE NOCASE");136 args.push(f.q);137 } else {138 conds.push("city LIKE ? COLLATE NOCASE");139 args.push(`${f.q.replace(/[%_]/g, "")}%`);140 }141 }142 if (f.type) {143 conds.push("property_type = ?");144 args.push(f.type);145 }146 if (f.min != null) {147 conds.push("amount >= ?");148 args.push(f.min);149 }150 if (f.max != null) {151 conds.push("amount <= ?");152 args.push(f.max);153 }154 return { where: conds.join(" AND "), args, since };155}156157/** Page du registre (tri : date décroissante, puis montant). */158export function ventesRecentes(f: VenteFilters): VentesPage {159 const db = getDb();160 const { where, args, since } = whereFor(f);161 const total = (162 db.prepare(`SELECT COUNT(*) AS n FROM transactions WHERE ${where}`).get(...args) as { n: number }163 ).n;164 const pages = Math.max(1, Math.ceil(total / f.limit));165 const page = Math.min(f.page, pages);166 const rows = db167 .prepare(168 `SELECT id, date, amount, street, city, property_type, year_built, floor_area,169 land_area, id_provinc, valeur_role170 FROM transactions WHERE ${where}171 ORDER BY date DESC, amount DESC172 LIMIT ? OFFSET ?`173 )174 .all(...args, f.limit, (page - 1) * f.limit) as VenteRow[];175 return { rows, total, page, pages, limit: f.limit, since, filters: { ...f, page } };176}177178function medianAmount(where: string, args: (string | number)[]): number | null {179 const db = getDb();180 const n = (db.prepare(`SELECT COUNT(*) AS n FROM transactions WHERE ${where}`).get(...args) as { n: number }).n;181 if (!n) return null;182 const row = db183 .prepare(`SELECT amount FROM transactions WHERE ${where} ORDER BY amount LIMIT 1 OFFSET ?`)184 .get(...args, Math.floor(n / 2)) as { amount: number } | undefined;185 return row?.amount ?? null;186}187188let overviewCache: { at: number; value: VentesOverview } | null = null;189const OVERVIEW_TTL_MS = 10 * 60 * 1000;190191/** Tuiles de tête du registre (cache mémoire 10 min — le connecteur est quotidien). */192export function ventesOverview(): VentesOverview {193 if (overviewCache && Date.now() - overviewCache.at < OVERVIEW_TTL_MS) return overviewCache.value;194 const db = getDb();195 const count = (since: string) =>196 (db.prepare("SELECT COUNT(*) AS n FROM transactions WHERE date >= ? AND amount > 0").get(since) as { n: number }).n;197 const d30 = isoDaysAgo(30);198 const maxDate = (db.prepare("SELECT MAX(date) AS d FROM transactions").get() as { d: string | null }).d;199 const byType30 = (200 db201 .prepare(202 `SELECT property_type AS type, COUNT(*) AS n FROM transactions203 WHERE date >= ? AND amount > 0 AND property_type IS NOT NULL204 GROUP BY property_type ORDER BY n DESC`205 )206 .all(d30) as { type: string; n: number }[]207 ).map((t) => ({208 ...t,209 median: medianAmount("date >= ? AND amount > 0 AND property_type = ?", [d30, t.type]),210 }));211 const value: VentesOverview = {212 maxDate,213 n30: count(d30),214 n90: count(isoDaysAgo(90)),215 n365: count(isoDaysAgo(365)),216 median30: medianAmount("date >= ? AND amount > 0", [d30]),217 byType30,218 };219 overviewCache = { at: Date.now(), value };220 return value;221}222223export interface VentesMunicipalite {224 rows: VenteRow[];225 n365: number;226 median365: number | null;227}228229/** Dernières ventes d'une municipalité (nom du rôle ; la source emploie les mêmes noms à 99 %). */230export function ventesParMunicipalite(nom: string, limit = 12): VentesMunicipalite {231 const db = getDb();232 const rows = db233 .prepare(234 `SELECT id, date, amount, street, city, property_type, year_built, floor_area,235 land_area, id_provinc, valeur_role236 FROM transactions237 WHERE city = ? COLLATE NOCASE AND amount > 0238 ORDER BY date DESC, amount DESC LIMIT ?`239 )240 .all(nom, limit) as VenteRow[];241 const since = isoDaysAgo(365);242 const w = "city = ? COLLATE NOCASE AND amount > 0 AND date >= ?";243 const n365 = (db.prepare(`SELECT COUNT(*) AS n FROM transactions WHERE ${w}`).get(nom, since) as { n: number }).n;244 return { rows, n365, median365: n365 ? medianAmount(w, [nom, since]) : null };245}246247/** « 7 oct. 2026 » */248export function dateFrLong(iso: string): string {249 const d = new Date(`${iso}T12:00:00Z`);250 if (Number.isNaN(d.getTime())) return iso;251 return d.toLocaleDateString("fr-CA", { day: "numeric", month: "short", year: "numeric", timeZone: "UTC" });252}253254/** Écart prix de vente / valeur au rôle, en % (null si rôle absent ou aberrant). */255export function vsRolePct(amount: number, valeurRole: number | null): number | null {256 if (valeurRole == null || valeurRole <= 0) return null;257 const p = (amount / valeurRole - 1) * 100;258 return Math.abs(p) < 400 ? p : null;259}260