import { and, eq, or, desc, sql, asc } from "drizzle-orm";
import { db } from "@/db/client";
import { inquiries, messages, viewingRequests, listings, properties, users, listingMedia } from "@/db/schema";
import { AuthError } from "@/lib/auth/session";
import { audit } from "@/lib/auth/audit";
import { sendMail, notifyMail } from "@/lib/auth/email";
import { mediaUrl } from "@/lib/listings/media";

const BASE = () => (process.env.APP_BASE_URL ?? "https://www.toveli.ca").replace(/\/$/, "");
/** Masque courriels, téléphones et liens suspects dans les messages (les coordonnées passent par Toveli, pas par le texte). */
export function sanitizeBody(s: string): string {
  return s.replace(/[\w.+-]+@[\w-]+\.[\w.]+/g, "[courriel masqué]").replace(/(\+?1[\s.-]?)?\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}/g, "[téléphone masqué]").replace(/https?:\/\/[^\s]+/gi, (m) => /toveli\.ca/i.test(m) ? m : "[lien retiré]").slice(0, 4000);
}
async function notify(userId: string, key: "inquiries" | "viewings", title: string, body: string, path: string, template: string) {
  const [u] = await db().select({ email: users.email, prefs: users.prefs }).from(users).where(eq(users.id, userId)).limit(1); if (!u) return;
  const n = (u.prefs as { notifications?: Record<string, boolean> }).notifications ?? {}; if (n[key] === false) return;
  try { await sendMail(notifyMail(u.email, title, body, `${BASE()}${path}`, "Ouvrir sur Toveli", template)); } catch { /* journalisé */ }
}

export async function openInquiry(listingId: string, senderId: string, category: string, body: string, idem?: string) {
  const d = db();
  const [l] = await d.select({ id: listings.id, ownerUserId: listings.ownerUserId, status: listings.status, title: listings.title }).from(listings).where(eq(listings.id, listingId)).limit(1);
  if (!l || l.status !== "PUBLISHED" || !l.ownerUserId) throw new AuthError(404, "Annonce introuvable ou non publiée");
  if (l.ownerUserId === senderId) throw new AuthError(409, "Vous ne pouvez pas vous écrire à vous-même");
  return d.transaction(async (tx) => {
    let [inq] = await tx.select().from(inquiries).where(and(eq(inquiries.listingId, listingId), eq(inquiries.senderUserId, senderId))).limit(1);
    if (!inq) [inq] = await tx.insert(inquiries).values({ listingId, senderUserId: senderId, recipientUserId: l.ownerUserId!, category }).returning();
    if (inq.status === "BLOCKED") throw new AuthError(403, "Conversation bloquée");
    const [m] = await tx.insert(messages).values({ inquiryId: inq.id, senderId, body: sanitizeBody(body), idempotencyKey: idem ?? null }).onConflictDoNothing().returning();
    if (m) { await tx.update(inquiries).set({ lastMessageAt: new Date(), status: "OPEN" }).where(eq(inquiries.id, inq.id)); await audit("inquiry.message", { actorId: senderId, objectType: "inquiry", objectId: inq.id }); await notify(l.ownerUserId!, "inquiries", "Nouvelle demande pour votre annonce", `Un membre Toveli vous a écrit au sujet de « ${l.title ?? "votre annonce"} ». Le contenu complet est disponible dans votre messagerie Toveli.`, `/membre/messages/${inq.id}`, "inquiry_new"); }
    return inq;
  });
}
export async function myInquiries(userId: string) {
  return db().select({ inq: inquiries, listingTitle: listings.title, listingSlug: listings.slug, listingType: listings.listingType, city: properties.city, 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)`, unread: sql<number>`(select count(*) from ${messages} mm where mm.inquiry_id = ${inquiries.id} and mm.read_at is null and mm.sender_id <> ${userId})::int`, lastBody: sql<string | null>`(select left(mm.body, 120) from ${messages} mm where mm.inquiry_id = ${inquiries.id} order by mm.created_at desc limit 1)`, otherName: sql<string | null>`(select coalesce(p.public_name, u.display_name) from ${users} u left join user_profiles p on p.user_id = u.id where u.id = case when ${inquiries.senderUserId} = ${userId} then ${inquiries.recipientUserId} else ${inquiries.senderUserId} end)` })
    .from(inquiries).innerJoin(listings, eq(listings.id, inquiries.listingId)).innerJoin(properties, eq(properties.id, listings.propertyId)).where(or(eq(inquiries.senderUserId, userId), eq(inquiries.recipientUserId, userId))).orderBy(desc(inquiries.lastMessageAt)).then((rows) => rows.map((r) => ({ ...r, cover: r.cover ? mediaUrl(r.cover, 320) : null })));
}
export async function getThread(inquiryId: string, userId: string) {
  const d = db(); const [inq] = await d.select().from(inquiries).where(eq(inquiries.id, inquiryId)).limit(1);
  if (!inq || (inq.senderUserId !== userId && inq.recipientUserId !== userId)) throw new AuthError(404, "Conversation introuvable"); // 404 volontaire : ne révèle pas l'existence
  const msgs = await d.select().from(messages).where(eq(messages.inquiryId, inquiryId)).orderBy(asc(messages.createdAt)).limit(500);
  await d.update(messages).set({ readAt: new Date() }).where(and(eq(messages.inquiryId, inquiryId), sql`${messages.senderId} <> ${userId}`, sql`${messages.readAt} is null`));
  const [l] = await d.select({ id: listings.id, slug: listings.slug, title: listings.title, status: listings.status, ownerUserId: listings.ownerUserId }).from(listings).where(eq(listings.id, inq.listingId)).limit(1);
  const otherId = inq.senderUserId === userId ? inq.recipientUserId : inq.senderUserId;
  const [other] = await d.select({ name: sql<string | null>`coalesce((select public_name from user_profiles where user_id = ${otherId}), ${users.displayName})` }).from(users).where(eq(users.id, otherId)).limit(1);
  return { inquiry: inq, messages: msgs, listing: l, other: { id: otherId, name: other?.name ?? "Membre Toveli" }, me: userId };
}
export async function reply(inquiryId: string, userId: string, body: string, idem?: string) {
  const d = db(); const [inq] = await d.select().from(inquiries).where(eq(inquiries.id, inquiryId)).limit(1);
  if (!inq || (inq.senderUserId !== userId && inq.recipientUserId !== userId)) throw new AuthError(404, "Conversation introuvable");
  if (inq.status === "BLOCKED") throw new AuthError(403, "Conversation bloquée");
  const [m] = await d.insert(messages).values({ inquiryId, senderId: userId, body: sanitizeBody(body), idempotencyKey: idem ?? null }).onConflictDoNothing().returning();
  if (m) { await d.update(inquiries).set({ lastMessageAt: new Date() }).where(eq(inquiries.id, inquiryId)); const to = inq.senderUserId === userId ? inq.recipientUserId : inq.senderUserId; await notify(to, "inquiries", "Nouveau message", "Vous avez reçu un nouveau message dans votre messagerie Toveli.", `/membre/messages/${inquiryId}`, "message_new"); }
  return m ?? null;
}
export async function setInquiryStatus(inquiryId: string, userId: string, status: "OPEN" | "ARCHIVED" | "BLOCKED") {
  const d = db(); const [inq] = await d.select().from(inquiries).where(eq(inquiries.id, inquiryId)).limit(1);
  if (!inq || (inq.senderUserId !== userId && inq.recipientUserId !== userId)) throw new AuthError(404, "Conversation introuvable");
  if (status === "BLOCKED" && inq.recipientUserId !== userId) throw new AuthError(403, "Seul le destinataire peut bloquer");
  await d.update(inquiries).set({ status }).where(eq(inquiries.id, inquiryId)); await audit(`inquiry.${status.toLowerCase()}`, { actorId: userId, objectId: inquiryId });
}

export async function requestViewing(listingId: string, requesterId: string, slots: string[], note?: string) {
  const d = db(); const [l] = await d.select({ ownerUserId: listings.ownerUserId, status: listings.status, title: listings.title }).from(listings).where(eq(listings.id, listingId)).limit(1);
  if (!l || l.status !== "PUBLISHED" || !l.ownerUserId) throw new AuthError(404, "Annonce introuvable ou non publiée");
  if (l.ownerUserId === requesterId) throw new AuthError(409, "Vous êtes le propriétaire de cette annonce");
  const inq = await openInquiry(listingId, requesterId, "VISIT", `Demande de visite · créneaux proposés : ${slots.map((s) => new Date(s).toLocaleString("fr-CA", { timeZone: "America/Toronto", dateStyle: "medium", timeStyle: "short" })).join(" ; ")}${note ? ` · ${note}` : ""}`, `visit:${slots.join("|")}`);
  const [v] = await d.insert(viewingRequests).values({ listingId, inquiryId: inq.id, requesterId, ownerId: l.ownerUserId, proposedSlots: slots, note: note ? sanitizeBody(note) : null }).returning();
  await audit("viewing.request", { actorId: requesterId, objectType: "viewing", objectId: v.id });
  await notify(l.ownerUserId, "viewings", "Demande de visite", `Un membre souhaite visiter « ${l.title ?? "votre propriété"} ». Confirmez un créneau dans Toveli.`, `/membre/visites`, "viewing_request");
  return v;
}
export async function myViewings(userId: string) {
  return db().select({ v: viewingRequests, title: listings.title, slug: listings.slug, city: properties.city }).from(viewingRequests).innerJoin(listings, eq(listings.id, viewingRequests.listingId)).innerJoin(properties, eq(properties.id, listings.propertyId)).where(or(eq(viewingRequests.requesterId, userId), eq(viewingRequests.ownerId, userId))).orderBy(desc(viewingRequests.createdAt));
}
export async function updateViewing(id: string, userId: string, action: "confirm" | "cancel" | "done", slot?: string) {
  const d = db(); const [v] = await d.select().from(viewingRequests).where(eq(viewingRequests.id, id)).limit(1);
  if (!v || (v.requesterId !== userId && v.ownerId !== userId)) throw new AuthError(404, "Demande introuvable");
  if (action === "confirm") { if (v.ownerId !== userId) throw new AuthError(403, "Seul le propriétaire confirme"); if (v.status !== "REQUESTED") throw new AuthError(409, "Déjà traitée"); if (!slot || !(v.proposedSlots as string[]).includes(slot)) throw new AuthError(400, "Créneau invalide"); const conflict = await d.select({ id: viewingRequests.id }).from(viewingRequests).where(and(eq(viewingRequests.ownerId, userId), eq(viewingRequests.status, "CONFIRMED"), sql`abs(extract(epoch from (${viewingRequests.confirmedSlot} - ${new Date(slot).toISOString()}::timestamptz))) < 1800`)).limit(1); if (conflict.length) throw new AuthError(409, "Conflit avec une visite déjà confirmée (30 min)"); await d.update(viewingRequests).set({ status: "CONFIRMED", confirmedSlot: new Date(slot), updatedAt: new Date() }).where(eq(viewingRequests.id, id)); await notify(v.requesterId, "viewings", "Visite confirmée", `Votre visite est confirmée le ${new Date(slot).toLocaleString("fr-CA", { timeZone: "America/Toronto", dateStyle: "full", timeStyle: "short" })}.`, "/membre/visites", "viewing_confirmed"); }
  if (action === "cancel") { if (["CANCELLED", "DONE"].includes(v.status)) throw new AuthError(409, "Déjà clôturée"); await d.update(viewingRequests).set({ status: "CANCELLED", updatedAt: new Date() }).where(eq(viewingRequests.id, id)); await notify(v.requesterId === userId ? v.ownerId : v.requesterId, "viewings", "Visite annulée", "Une demande de visite a été annulée.", "/membre/visites", "viewing_cancelled"); }
  if (action === "done") { if (v.ownerId !== userId || v.status !== "CONFIRMED") throw new AuthError(409, "Impossible"); await d.update(viewingRequests).set({ status: "DONE", updatedAt: new Date() }).where(eq(viewingRequests.id, id)); }
  await audit(`viewing.${action}`, { actorId: userId, objectId: id });
}