// Tâches périodiques Toveli (PM2 cron, toutes les heures) : alertes de recherches sauvegardées, rappels d'expiration,
// expiration automatique, purge des défis OTP et jetons d'upload orphelins, nettoyage des médias en attente.
import "dotenv/config";
import { and, eq, lt, sql, isNull } from "drizzle-orm";
import { db } from "../src/db/client";
import { savedSearches, users, listings, otpChallenges, listingMedia, emailDeliveries } from "../src/db/schema";
import { unifiedSearch } from "../src/lib/search";
import { fromUrl } from "../src/lib/search-params";
import { sendMail, notifyMail } from "../src/lib/auth/email";
import { fmtPrice } from "../src/lib/format";
import { removeFiles } from "../src/lib/listings/media";

const BASE = (process.env.APP_BASE_URL ?? "https://www.toveli.ca").replace(/\/$/, "");
const log = (m: string) => console.log(`[jobs ${new Date().toISOString().slice(11, 19)}] ${m}`);

async function alerts() {
  const d = db(); const now = Date.now();
  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'`));
  let sent = 0;
  for (const r of rows) {
    if (r.status !== "ACTIVE" || (r.prefs as { notifications?: { alerts?: boolean } }).notifications?.alerts === false) continue;
    const period = r.s.alertFrequency === "INSTANT" ? 3600000 : r.s.alertFrequency === "DAILY" ? 86400000 : 7 * 86400000;
    const last = r.s.lastAlertAt?.getTime() ?? r.s.createdAt.getTime(); if (now - last < period) continue;
    const p = fromUrl(r.s.query as Record<string, string>, 50); const res = await unifiedSearch({ ...p, sort: "recent" });
    const fresh = res.items.filter((i) => i.firstSeen && Date.parse(i.firstSeen) > last).slice(0, 10);
    await d.update(savedSearches).set({ lastAlertAt: new Date() }).where(eq(savedSearches.id, r.s.id));
    if (!fresh.length) continue;
    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/>");
    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é */ }
  }
  log(`alertes : ${rows.length} recherches, ${sent} courriels`);
}
async function expiry() {
  const d = db(); const soon = new Date(Date.now() + 7 * 86400000);
  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)));
  for (const r of rows) {
    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; }
    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;
    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 { /* */ }
  }
  log(`expiration : ${rows.length} annonces traitées`);
}
async function purge() {
  const d = db();
  const otp = await d.delete(otpChallenges).where(lt(otpChallenges.expiresAt, new Date(Date.now() - 86400000))).returning({ id: otpChallenges.id });
  const stale = await d.select().from(listingMedia).where(and(eq(listingMedia.status, "UPLOADING"), lt(listingMedia.uploadedAt, new Date(Date.now() - 3600000))));
  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); }
  await d.execute(sql`delete from rate_limits where window_start < now() - interval '2 days'`);
  await d.execute(sql`delete from email_deliveries where created_at < now() - interval '90 days'`);
  await d.execute(sql`update sessions set revoked_at = now() where revoked_at is null and expires_at < now()`);
  log(`purge : ${otp.length} défis OTP, ${stale.length} uploads orphelins`);
  void isNull;
}
(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 }); } })();