spb/toveli

1// Tâches périodiques Toveli (PM2 cron, toutes les heures) : alertes de recherches sauvegardées, rappels d'expiration,2// expiration automatique, purge des défis OTP et jetons d'upload orphelins, nettoyage des médias en attente.3import "dotenv/config";4import { and, eq, lt, sql, isNull } from "drizzle-orm";5import { db } from "../src/db/client";6import { savedSearches, users, listings, otpChallenges, listingMedia, emailDeliveries } from "../src/db/schema";7import { unifiedSearch } from "../src/lib/search";8import { fromUrl } from "../src/lib/search-params";9import { sendMail, notifyMail } from "../src/lib/auth/email";10import { fmtPrice } from "../src/lib/format";11import { removeFiles } from "../src/lib/listings/media";1213const BASE = (process.env.APP_BASE_URL ?? "https://www.toveli.ca").replace(/\/$/, "");14const log = (m: string) => console.log(`[jobs ${new Date().toISOString().slice(11, 19)}] ${m}`);1516async function alerts() {17  const d = db(); const now = Date.now();18  const rows = await d.select({ s: savedSearches, email: users.email, prefs: users.prefs, status: users.status }).from(savedSearches).innerJoin(users, eq(users.id, savedSearches.userId)).where(and(eq(savedSearches.alertEnabled, true), sql`${savedSearches.alertFrequency} <> 'OFF'`));19  let sent = 0;20  for (const r of rows) {21    if (r.status !== "ACTIVE" || (r.prefs as { notifications?: { alerts?: boolean } }).notifications?.alerts === false) continue;22    const period = r.s.alertFrequency === "INSTANT" ? 3600000 : r.s.alertFrequency === "DAILY" ? 86400000 : 7 * 86400000;23    const last = r.s.lastAlertAt?.getTime() ?? r.s.createdAt.getTime(); if (now - last < period) continue;24    const p = fromUrl(r.s.query as Record<string, string>, 50); const res = await unifiedSearch({ ...p, sort: "recent" });25    const fresh = res.items.filter((i) => i.firstSeen && Date.parse(i.firstSeen) > last).slice(0, 10);26    await d.update(savedSearches).set({ lastAlertAt: new Date() }).where(eq(savedSearches.id, r.s.id));27    if (!fresh.length) continue;28    const lines = fresh.map((i) => `• ${fmtPrice(i.price, { rent: i.market === "rent" })} — ${i.address ?? i.typeLabel}, ${i.city} (${BASE}/annonce/${encodeURIComponent(i.uid)})`).join("<br/>");29    try { await sendMail(notifyMail(r.email, `${fresh.length} nouvelle${fresh.length > 1 ? "s" : ""} annonce${fresh.length > 1 ? "s" : ""} : ${r.s.label}`, lines, `${BASE}/recherche?${new URLSearchParams(r.s.query as Record<string, string>)}`, "Voir les résultats", "alert_digest")); sent++; } catch { /* journalisé */ }30  }31  log(`alertes : ${rows.length} recherches, ${sent} courriels`);32}33async function expiry() {34  const d = db(); const soon = new Date(Date.now() + 7 * 86400000);35  const rows = await d.select({ l: listings, email: users.email }).from(listings).innerJoin(users, eq(users.id, listings.ownerUserId)).where(and(eq(listings.status, "PUBLISHED"), lt(listings.expiresAt, soon)));36  for (const r of rows) {37    if (r.l.expiresAt && r.l.expiresAt.getTime() < Date.now()) { await d.update(listings).set({ status: "PAUSED", moderationNote: "Expirée après 90 jours sans renouvellement", updatedAt: new Date() }).where(eq(listings.id, r.l.id)); try { await sendMail(notifyMail(r.email, "Votre annonce a expiré", `« ${r.l.title ?? "Votre annonce"} » a été mise en pause après 90 jours. Réactivez-la en un clic si elle est toujours d'actualité.`, `${BASE}/membre/annonces`, "Mes annonces", "listing_expired")); } catch { /* */ } continue; }38    const [already] = await d.select({ id: emailDeliveries.id }).from(emailDeliveries).where(and(eq(emailDeliveries.template, `expiry_soon:${r.l.id}`), sql`created_at > now() - interval '8 days'`)).limit(1); if (already) continue;39    try { await sendMail({ ...notifyMail(r.email, "Votre annonce expire bientôt", `« ${r.l.title ?? "Votre annonce"} » expirera le ${r.l.expiresAt?.toLocaleDateString("fr-CA")}. Mettez-la à jour pour la prolonger de 90 jours, ou marquez-la vendue/louée.`, `${BASE}/membre/annonces/${r.l.id}`, "Mettre à jour", "expiry_soon"), template: `expiry_soon:${r.l.id}` }); } catch { /* */ }40  }41  log(`expiration : ${rows.length} annonces traitées`);42}43async function purge() {44  const d = db();45  const otp = await d.delete(otpChallenges).where(lt(otpChallenges.expiresAt, new Date(Date.now() - 86400000))).returning({ id: otpChallenges.id });46  const stale = await d.select().from(listingMedia).where(and(eq(listingMedia.status, "UPLOADING"), lt(listingMedia.uploadedAt, new Date(Date.now() - 3600000))));47  for (const m of stale) { await d.update(listingMedia).set({ status: "DELETED", uploadToken: null }).where(eq(listingMedia.id, m.id)); if (!m.storageKey.startsWith("pending/")) removeFiles(m.storageKey); }48  await d.execute(sql`delete from rate_limits where window_start < now() - interval '2 days'`);49  await d.execute(sql`delete from email_deliveries where created_at < now() - interval '90 days'`);50  await d.execute(sql`update sessions set revoked_at = now() where revoked_at is null and expires_at < now()`);51  log(`purge : ${otp.length} défis OTP, ${stale.length} uploads orphelins`);52  void isNull;53}54(async () => { try { await purge(); await expiry(); await alerts(); } catch (e) { console.error("[jobs] échec", e); process.exitCode = 1; } finally { const { rawSql } = await import("../src/db/client"); await rawSql().end({ timeout: 5 }); } })();55