TypeScript 50.6%
JavaScript 46.1%
SQL 2.1%
Shell 0.6%
CSS 0.5%
1import { getDb } from "./db";2import type { ListingCard, ListingRow, Market, MarketMetricRow, PropertyRow, SearchParams, SearchResult, ValuationRow } from "./types";3import { stripAccents } from "./normalize";45function toCard(r: ListingRow & { estimate?: number | null; ratio?: number | null; verdict?: string | null; confidence?: string | null }): ListingCard {6 let image: string | null = null;7 try { const a = JSON.parse(r.images ?? "[]"); image = Array.isArray(a) && a.length ? a[0] : null; } catch { /* ignore */ }8 return {9 uid: r.uid, propertyId: r.property_id, market: r.market, price: r.price, address: r.address, city: r.city, region: r.region, category: r.category, typeLabel: r.type_label,10 bedrooms: r.bedrooms, bathrooms: r.bathrooms, areaSqft: r.area_sqft, lat: r.lat, lng: r.lng, image, imageCount: r.image_count ?? 0, connector: r.connector, sourceId: r.source_id,11 url: r.url, firstSeen: r.first_seen, estimate: r.estimate ?? null, ratio: r.ratio ?? null, verdict: r.verdict ?? null, confidence: r.confidence ?? null,12 };13}1415const CARD_COLS = `l.uid, l.property_id, l.source_id, l.connector, l.external_id, l.market, l.url, l.status, l.title, NULL AS description, l.price, l.price_label, l.address, l.unit, l.city, l.region, l.sector,16 l.lat, l.lng, l.category, l.type_label, l.bedrooms, l.bathrooms, l.powder_rooms, l.area_sqft, l.lot_sqft, l.year_built, NULL AS features, NULL AS details, l.images, l.image_count,17 l.agency, l.broker, l.mls, l.availability, l.furnished, l.pets, l.first_seen, l.last_seen, l.updated_at, l.quality_score, l.dup_of, NULL AS provenance,18 v.estimate, v.ratio, v.verdict, v.confidence`;1920function ftsQuery(q: string): string {21 const toks = stripAccents(q).toLowerCase().replace(/[^a-z0-9\s]/g, " ").split(/\s+/).filter((t) => t.length >= 2);22 return toks.map((t) => `"${t}"*`).join(" ");23}2425export function searchListings(p: SearchParams): SearchResult {26 const db = getDb();27 const where: string[] = ["l.price > 0"]; const args: Record<string, unknown> = {};28 const market: Market = p.market ?? "sale";29 where.push("l.market = @market"); args.market = market;30 if (p.city) { where.push("l.city = @city COLLATE NOCASE"); args.city = p.city; }31 if (p.region) { where.push("l.region = @region"); args.region = p.region; }32 if (p.category?.length) { where.push(`l.category IN (${p.category.map((_, i) => `@cat${i}`).join(",")})`); p.category.forEach((c, i) => (args[`cat${i}`] = c)); }33 if (p.priceMin) { where.push("l.price >= @pmin"); args.pmin = p.priceMin; }34 if (p.priceMax) { where.push("l.price <= @pmax"); args.pmax = p.priceMax; }35 if (p.bedroomsMin) { where.push("l.bedrooms >= @bmin"); args.bmin = p.bedroomsMin; }36 if (p.bathroomsMin) { where.push("l.bathrooms >= @bamin"); args.bamin = p.bathroomsMin; }37 if (p.areaMin) { where.push("l.area_sqft >= @amin"); args.amin = p.areaMin; }38 if (p.yearMin) { where.push("l.year_built >= @ymin"); args.ymin = p.yearMin; }39 if (p.withImages) where.push("l.image_count > 0");40 if (p.bbox) { const [w, s, e, n] = p.bbox; where.push("l.lng BETWEEN @w AND @e AND l.lat BETWEEN @s AND @n"); Object.assign(args, { w, s, e, n }); }41 let join = "LEFT JOIN valuation v ON v.listing_uid = l.uid";42 if (p.q && p.q.trim()) {43 const fq = ftsQuery(p.q);44 if (fq) { join += " JOIN listing_fts f ON f.uid = l.uid"; where.push("listing_fts MATCH @fq"); args.fq = fq; }45 }46 const order = {47 recent: "l.first_seen DESC", price_asc: "l.price ASC", price_desc: "l.price DESC", area_desc: "l.area_sqft DESC NULLS LAST",48 deal: "CASE WHEN v.ratio IS NULL THEN 1 ELSE 0 END, v.ratio DESC",49 }[p.sort ?? "recent"];50 const limit = Math.min(Math.max(p.limit ?? 24, 1), 500); const offset = Math.max(p.offset ?? 0, 0);51 const sqlWhere = where.join(" AND ");52 const total = (db.prepare(`SELECT count(*) c FROM listing l ${join} WHERE ${sqlWhere}`).get(args) as { c: number }).c;53 const rows = db.prepare(`SELECT ${CARD_COLS} FROM listing l ${join} WHERE ${sqlWhere} ORDER BY ${order}, l.uid LIMIT @limit OFFSET @offset`).all({ ...args, limit, offset }) as ListingRow[];54 return { items: rows.map(toCard), total, limit, offset };55}5657/** Points pour la carte : jusqu'à `limit` annonces dans la bbox (allégé). */58export function mapPoints(p: SearchParams): { uid: string; lat: number; lng: number; price: number; category: string; propertyId: string }[] {59 const r = searchListings({ ...p, limit: Math.min(p.limit ?? 1500, 3000), offset: 0 });60 return r.items.filter((i) => i.lat != null && i.lng != null && i.price != null).map((i) => ({ uid: i.uid, lat: i.lat!, lng: i.lng!, price: i.price!, category: i.category, propertyId: i.propertyId }));61}6263export function getListing(uid: string): (ListingRow & { valuation: ValuationRow | null; priceEvents: { ts: string; price: number }[] }) | null {64 const db = getDb();65 const l = db.prepare("SELECT * FROM listing WHERE uid = ?").get(uid) as ListingRow | undefined;66 if (!l) return null;67 const valuation = (db.prepare("SELECT * FROM valuation WHERE listing_uid = ?").get(uid) as ValuationRow | undefined) ?? null;68 const priceEvents = db.prepare("SELECT ts, price FROM price_event WHERE listing_uid = ? AND price IS NOT NULL ORDER BY ts").all(uid) as { ts: string; price: number }[];69 return { ...l, valuation, priceEvents };70}7172export function getProperty(id: string): (PropertyRow & { listings: ListingCard[] }) | null {73 const db = getDb();74 const p = db.prepare("SELECT * FROM property WHERE id = ?").get(id) as PropertyRow | undefined;75 if (!p) return null;76 const rows = db.prepare(`SELECT ${CARD_COLS} FROM listing l LEFT JOIN valuation v ON v.listing_uid = l.uid WHERE l.property_id = ? ORDER BY l.market, l.price`).all(id) as ListingRow[];77 return { ...p, listings: rows.map(toCard) };78}7980export function nearbyListings(lat: number, lng: number, market: Market, excludeUid: string | null, limit = 8, radiusDeg = 0.02): ListingCard[] {81 const db = getDb();82 const rows = db.prepare(`SELECT ${CARD_COLS}, ((l.lat-@lat)*(l.lat-@lat) + (l.lng-@lng)*(l.lng-@lng)*0.52) AS d2 FROM listing l LEFT JOIN valuation v ON v.listing_uid = l.uid83 WHERE l.market = @market AND l.uid <> @ex AND l.price > 0 AND l.lat BETWEEN @lat-@r AND @lat+@r AND l.lng BETWEEN @lng-@r*1.4 AND @lng+@r*1.4 ORDER BY d2 LIMIT @limit`)84 .all({ lat, lng, market, ex: excludeUid ?? "", r: radiusDeg, limit }) as ListingRow[];85 return rows.map(toCard);86}8788export function neighbourhoodScore(lat: number, lng: number) {89 const db = getDb();90 return db.prepare(`SELECT walk, transit, bike, calm, services, global, version, computed_at FROM neighbourhood_score91 WHERE lat BETWEEN ?-0.004 AND ?+0.004 AND lng BETWEEN ?-0.006 AND ?+0.006 ORDER BY (lat-?)*(lat-?)+(lng-?)*(lng-?)*0.52 LIMIT 1`)92 .get(lat, lat, lng, lng, lat, lat, lng, lng) as { walk: number | null; transit: number | null; bike: number | null; calm: number | null; services: number | null; global: number | null; version: string; computed_at: string } | undefined ?? null;93}9495export function featured(market: Market, limit = 8): ListingCard[] {96 const db = getDb();97 const rows = db.prepare(`SELECT ${CARD_COLS} FROM listing l LEFT JOIN valuation v ON v.listing_uid = l.uid98 WHERE l.market = @market AND l.image_count >= 4 AND l.price > 0 AND l.lat IS NOT NULL AND l.quality_score >= 80 ${market === "sale" ? "AND v.confidence IN ('A','B') AND v.ratio BETWEEN 0.9 AND 1.3" : ""}99 ORDER BY l.first_seen DESC LIMIT @limit`).all({ market, limit }) as ListingRow[];100 return rows.map(toCard);101}102103export function suggestPlaces(q: string, limit = 8): { kind: string; name: string; region: string | null; nSale: number; nRent: number }[] {104 const db = getDb();105 const like = `${stripAccents(q).toLowerCase()}%`;106 const rows = db.prepare(`SELECT kind, name, region, n_sale, n_rent FROM place ORDER BY (n_sale + n_rent) DESC`).all() as { kind: string; name: string; region: string | null; n_sale: number; n_rent: number }[];107 return rows.filter((r) => { const n = stripAccents(r.name).toLowerCase(); return n.startsWith(like.slice(0, -1)) || n.split(/[\s-]+/).some((w) => w.startsWith(like.slice(0, -1))); })108 .slice(0, limit).map((r) => ({ kind: r.kind, name: r.name, region: r.region, nSale: r.n_sale, nRent: r.n_rent }));109}110111export function suggestAddresses(q: string, limit = 6): { uid: string; address: string; city: string; market: Market; propertyId: string }[] {112 const fq = ftsQuery(q); if (!fq) return [];113 const db = getDb();114 return db.prepare(`SELECT l.uid, l.address, l.city, l.market, l.property_id AS propertyId FROM listing_fts f JOIN listing l ON l.uid = f.uid WHERE listing_fts MATCH ? AND l.address IS NOT NULL ORDER BY rank LIMIT ?`).all(fq, limit) as { uid: string; address: string; city: string; market: Market; propertyId: string }[];115}116117export function searchRoleUnits(q: string, limit = 8) {118 const fq = ftsQuery(q); if (!fq) return [];119 const db = getDb();120 return db.prepare(`SELECT u.id, u.address, u.unit, u.city, u.type_prop, u.year_built, u.floor_area_m2, u.role_value, u.est FROM role_unit_fts f JOIN role_unit u ON u.id = f.id WHERE role_unit_fts MATCH ? ORDER BY rank LIMIT ?`).all(fq, limit) as121 { id: string; address: string; unit: string | null; city: string; type_prop: string; year_built: number | null; floor_area_m2: number | null; role_value: number | null; est: number | null }[];122}123124export function marketMetrics(scope: string, value: string, market: Market): MarketMetricRow[] {125 return getDb().prepare("SELECT * FROM market_metric WHERE scope = ? AND scope_value = ? AND market = ? ORDER BY n DESC").all(scope, value, market) as MarketMetricRow[];126}127export function topPlaces(kind: "city" | "region", market: Market, limit = 20) {128 const col = market === "sale" ? "n_sale" : "n_rent";129 return getDb().prepare(`SELECT kind, name, region, lat, lng, n_sale, n_rent FROM place WHERE kind = ? AND ${col} > 0 ORDER BY ${col} DESC LIMIT ?`).all(kind, limit) as { kind: string; name: string; region: string | null; lat: number; lng: number; n_sale: number; n_rent: number }[];130}131export function regionMetrics(market: Market, category = "all"): MarketMetricRow[] {132 return getDb().prepare("SELECT * FROM market_metric WHERE scope = 'region' AND market = ? AND category = ? ORDER BY n DESC").all(market, category) as MarketMetricRow[];133}134export function cityMetrics(market: Market, category = "all", limit = 30): MarketMetricRow[] {135 return getDb().prepare("SELECT * FROM market_metric WHERE scope = 'city' AND market = ? AND category = ? ORDER BY n DESC LIMIT ?").all(market, category, limit) as MarketMetricRow[];136}137export function marketIndex(typeProp: string): { month: string; idx: number; ppm2: number; n: number }[] {138 return getDb().prepare("SELECT month, idx, ppm2, n FROM market_index WHERE type_prop = ? ORDER BY month").all(typeProp) as { month: string; idx: number; ppm2: number; n: number }[];139}140export function recentTransactions(lat: number, lng: number, limit = 10) {141 return getDb().prepare(`SELECT id, date, amount, street, city, lat, lng, property_type, year_built, floor_area FROM sale_transaction142 WHERE lat BETWEEN ?-0.01 AND ?+0.01 AND lng BETWEEN ?-0.014 AND ?+0.014 AND street IS NOT NULL ORDER BY date DESC LIMIT ?`).all(lat, lat, lng, lng, limit) as143 { id: string; date: string; amount: number; street: string; city: string; lat: number; lng: number; property_type: string | null; year_built: number | null; floor_area: number | null }[];144}145