spb/toveli

1// Projection publique d'une annonce directe Toveli → ListingCard / fiche, avec respect du niveau de confidentialité de l'adresse.2import { and, eq, desc, sql, gte, lte, inArray } from "drizzle-orm";3import { db, pgAvailable } from "@/db/client";4import { listings, properties, listingMedia, users, userProfiles } from "@/db/schema";5import type { ListingCard, Market, SearchParams } from "@/lib/types";6import { mediaUrl } from "./media";7import { PROPERTY_TYPE_LABEL } from "./types";89export const DIRECT_PREFIX = "toveli:";10export function toCategory(pt: string): string { return ({ appartement: "logement", condo: "condo", maison: "maison", jumele: "maison", plex: "plex", chalet: "chalet", terrain: "terrain", commercial: "commercial", autre: "autre" } as Record<string, string>)[pt] ?? "autre"; }11export function publicAddress(p: { canonicalAddress: string | null; privacyLevel: string; city: string | null }): string | null {12  if (!p.canonicalAddress) return null;13  if (p.privacyLevel === "AREA_ONLY") return null;14  if (p.privacyLevel === "STREET_ONLY") return p.canonicalAddress.replace(/^\s*\d+[a-zA-Z]?(\s*-\s*\d+[a-zA-Z]?)?\s+/, "");15  return p.canonicalAddress;16}17export function jitter(lat: number, lng: number, level: string): [number, number] { if (level === "EXACT") return [lat, lng]; const r = level === "STREET_ONLY" ? 0.0015 : 0.006; const h = (lat * 1e4 + lng * 1e4) % 1; return [lat + (h - 0.5) * r, lng + ((h * 7) % 1 - 0.5) * r * 1.4]; }1819type Row = { l: typeof listings.$inferSelect; p: typeof properties.$inferSelect; cover: string | null; mediaCount: number; sellerType: string | null; publicName: string | null };20function toCard(r: Row): ListingCard {21  const [lat, lng] = r.p.lat != null && r.p.lng != null ? jitter(r.p.lat, r.p.lng, r.p.privacyLevel) : [null, null];22  const pro = r.l.sourceType === "DIRECT_AGENT";23  return { uid: DIRECT_PREFIX + r.l.slug, propertyId: `toveli:${r.p.id}`, market: r.l.listingType === "RENT" ? "rent" : "sale", price: r.l.priceAmount != null ? Number(r.l.priceAmount) : null, address: publicAddress(r.p) ?? (r.p.privacyLevel === "AREA_ONLY" ? `Secteur ${r.p.city ?? ""}`.trim() : null), city: r.p.city, region: r.p.region, category: toCategory(r.p.propertyType), typeLabel: PROPERTY_TYPE_LABEL[r.p.propertyType] ?? r.p.propertyType, bedrooms: r.p.bedrooms != null ? Number(r.p.bedrooms) : null, bathrooms: r.p.bathrooms != null ? Number(r.p.bathrooms) : null, areaSqft: r.p.areaSqft, lat, lng, image: r.cover ? mediaUrl(r.cover, 800) : null, imageCount: r.mediaCount, connector: pro ? "professionnel" : "proprietaire", sourceId: "toveli", url: null, firstSeen: r.l.publicationAt?.toISOString() ?? r.l.createdAt.toISOString(), estimate: null, ratio: null, verdict: null, confidence: null };24}25const baseSelect = () => db().select({ l: listings, p: properties, cover: sql<string | null>`(select m.storage_key from ${listingMedia} m where m.listing_id = ${listings.id} and m.status = 'READY' order by m.position limit 1)`, mediaCount: sql<number>`(select count(*) from ${listingMedia} m where m.listing_id = ${listings.id} and m.status = 'READY')::int`, sellerType: userProfiles.sellerType, publicName: userProfiles.publicName }).from(listings).innerJoin(properties, eq(properties.id, listings.propertyId)).leftJoin(userProfiles, eq(userProfiles.userId, listings.ownerUserId));2627/** Annonces directes publiées correspondant aux filtres (volume faible → fusionnées en mémoire avec la base canonique). */28export async function searchDirect(p: SearchParams): Promise<ListingCard[]> {29  if (!pgAvailable()) return [];30  const market: Market = p.market ?? "sale"; const conds = [eq(listings.status, "PUBLISHED"), eq(listings.listingType, market === "rent" ? "RENT" : "SALE")];31  if (p.city) conds.push(sql`lower(${properties.city}) = lower(${p.city})`);32  if (p.region) conds.push(eq(properties.region, p.region));33  if (p.priceMin) conds.push(gte(listings.priceAmount, String(p.priceMin))); if (p.priceMax) conds.push(lte(listings.priceAmount, String(p.priceMax)));34  if (p.bedroomsMin) conds.push(gte(properties.bedrooms, String(p.bedroomsMin))); if (p.bathroomsMin) conds.push(gte(properties.bathrooms, String(p.bathroomsMin)));35  if (p.areaMin) conds.push(gte(properties.areaSqft, p.areaMin)); if (p.yearMin) conds.push(gte(properties.yearBuilt, p.yearMin));36  if (p.bbox) { const [w, s, e, n] = p.bbox; conds.push(sql`${properties.lng} between ${w} and ${e} and ${properties.lat} between ${s} and ${n}`); }37  if (p.q) conds.push(sql`(${listings.title} ilike ${"%" + p.q + "%"} or ${properties.canonicalAddress} ilike ${"%" + p.q + "%"} or ${properties.city} ilike ${"%" + p.q + "%"})`);38  let rows = await baseSelect().where(and(...conds)).orderBy(desc(listings.publicationAt)).limit(500);39  if (p.category?.length) rows = rows.filter((r) => p.category!.includes(toCategory(r.p.propertyType)));40  return rows.map(toCard);41}42export async function getDirectBySlug(slug: string) {43  if (!pgAvailable()) return null;44  const [r] = await baseSelect().where(eq(listings.slug, slug)).limit(1); if (!r) return null;45  const media = await db().select().from(listingMedia).where(and(eq(listingMedia.listingId, r.l.id), eq(listingMedia.status, "READY"))).orderBy(listingMedia.position);46  const [owner] = r.l.ownerUserId ? await db().select({ id: users.id, displayName: users.displayName, prefs: users.prefs }).from(users).where(eq(users.id, r.l.ownerUserId)).limit(1) : [];47  return { ...r, card: toCard(r), media, owner: owner ?? null };48}49export async function directCardsByUids(uids: string[]): Promise<ListingCard[]> {50  const slugs = uids.filter((u) => u.startsWith(DIRECT_PREFIX)).map((u) => u.slice(DIRECT_PREFIX.length)); if (!slugs.length || !pgAvailable()) return [];51  const rows = await baseSelect().where(and(inArray(listings.slug, slugs), eq(listings.status, "PUBLISHED"))); return rows.map(toCard);52}53export async function directCount(): Promise<{ sale: number; rent: number }> { if (!pgAvailable()) return { sale: 0, rent: 0 }; try { const r = await db().select({ t: listings.listingType, n: sql<number>`count(*)::int` }).from(listings).where(eq(listings.status, "PUBLISHED")).groupBy(listings.listingType); return { sale: r.find((x) => x.t === "SALE")?.n ?? 0, rent: r.find((x) => x.t === "RENT")?.n ?? 0 }; } catch { return { sale: 0, rent: 0 }; } }54