Repository Public
1import { getDb, getSetting, setSetting, tx } from "../db";2import { decrypt, encrypt, randomToken, sha256 } from "../crypto";3import { addDays, localToUtc, startOfLocalDay, todayLocal, weekdayOf } from "./tz";45/**6 * Moteur de rendez-vous interne.7 * - Créneaux calculés à partir des heures de travail (heure locale America/Toronto),8 * des exceptions (plages bloquées, congés, ouvertures ponctuelles), du délai minimum,9 * de l'horizon de réservation et des cellules déjà occupées.10 * - Double réservation impossible : chaque rendez-vous occupe des cellules de 15 min11 * (durée + marge) dont la clé primaire est unique ; l'insertion se fait dans une12 * transaction BEGIN IMMEDIATE et échoue si une seule cellule est déjà prise.13 */14export const CELL_MS = 15 * 60_000;1516export interface AppointmentType {17 id: number;18 slug: string;19 title: string;20 description: string;21 duration_min: number;22 location_kind: "visio" | "telephone" | "presentiel";23 location_detail: string;24 min_notice_hours: number;25 buffer_min: number;26 max_days_ahead: number;27 active: number;28 sort: number;29}3031export type AppointmentStatus = "requested" | "confirmed" | "cancelled" | "completed";3233export interface Appointment {34 id: number;35 type_id: number;36 user_id: number | null;37 start_utc: number;38 end_utc: number;39 status: AppointmentStatus;40 name: string;41 email: string;42 phone: string;43 organization: string;44 reason: string;45 notes: string;46 admin_notes: string;47 consent_at: number;48 manage_token_hash: string;49 manage_token_enc: string;50 token_expires_at: number;51 reminder_sent_at: number | null;52 anonymized_at: number | null;53 cancel_reason: string;54 created_at: number;55 updated_at: number;56}5758export interface BookingSettings {59 /** true = confirmation automatique ; false = validation manuelle par l'administrateur. */60 autoConfirm: boolean;61 /** Rappel au client N heures avant (0 = aucun rappel). */62 reminderHours: number;63 /** Anonymisation des données personnelles N jours après le rendez-vous (0 = jamais). */64 retentionDays: number;65 /** Jeton du flux iCalendar privé (abonnement depuis Google Agenda / Outlook). */66 icsToken: string;67}6869const DEFAULT_SETTINGS: BookingSettings = { autoConfirm: false, reminderHours: 24, retentionDays: 365, icsToken: "" };7071export function bookingSettings(): BookingSettings {72 return { ...DEFAULT_SETTINGS, ...getSetting<Partial<BookingSettings>>("booking", {}) };73}74export function saveBookingSettings(patch: Partial<BookingSettings>) {75 setSetting("booking", { ...bookingSettings(), ...patch });76}7778/** Types et heures de travail par défaut à la première utilisation (modifiables dans l'administration). */79export function ensureBookingSeed() {80 const db = getDb();81 if (getSetting<boolean>("booking_seeded", false)) return;82 tx(db, () => {83 const n = (db.prepare("SELECT count(*) AS n FROM appointment_types").get() as { n: number }).n;84 if (n === 0) {85 const ins = db.prepare(86 "INSERT INTO appointment_types (slug, title, description, duration_min, location_kind, location_detail, min_notice_hours, buffer_min, max_days_ahead, active, sort) VALUES (?,?,?,?,?,?,?,?,?,1,?)",87 );88 ins.run("rencontre-decouverte", "Rencontre découverte", "Un premier échange pour comprendre votre projet et vos besoins.", 30, "visio", "Le lien de visioconférence vous sera transmis par courriel.", 24, 15, 60, 1);89 ins.run("consultation-projet", "Consultation de projet", "Une rencontre de travail sur un projet ou une demande en particulier.", 60, "visio", "Le lien de visioconférence vous sera transmis par courriel.", 48, 15, 60, 2);90 const rule = db.prepare("INSERT INTO availability_rules (weekday, start_min, end_min) VALUES (?,?,?)");91 for (let wd = 1; wd <= 5; wd++) {92 rule.run(wd, 9 * 60, 12 * 60);93 rule.run(wd, 13 * 60, 17 * 60);94 }95 }96 db.prepare("INSERT INTO settings (key, value) VALUES ('booking_seeded','true') ON CONFLICT(key) DO UPDATE SET value = 'true'").run();97 });98 if (!bookingSettings().icsToken) saveBookingSettings({ icsToken: randomToken(24) });99}100101export function listTypes(onlyActive = false): AppointmentType[] {102 ensureBookingSeed();103 return getDb()104 .prepare(`SELECT * FROM appointment_types ${onlyActive ? "WHERE active = 1" : ""} ORDER BY sort, id`)105 .all() as unknown as AppointmentType[];106}107export function typeBySlug(slug: string): AppointmentType | undefined {108 ensureBookingSeed();109 return getDb().prepare("SELECT * FROM appointment_types WHERE slug = ?").get(slug) as AppointmentType | undefined;110}111export function typeById(id: number): AppointmentType | undefined {112 return getDb().prepare("SELECT * FROM appointment_types WHERE id = ?").get(id) as AppointmentType | undefined;113}114115export interface Rule {116 id: number;117 weekday: number;118 start_min: number;119 end_min: number;120}121export interface Exception {122 id: number;123 kind: "block" | "open";124 start_utc: number;125 end_utc: number;126 note: string;127}128export function listRules(): Rule[] {129 ensureBookingSeed();130 return getDb().prepare("SELECT * FROM availability_rules ORDER BY weekday, start_min").all() as unknown as Rule[];131}132export function listExceptions(fromUtc = 0): Exception[] {133 return getDb().prepare("SELECT * FROM availability_exceptions WHERE end_utc > ? ORDER BY start_utc").all(fromUtc) as unknown as Exception[];134}135136function cellsFor(startUtc: number, endUtc: number, bufferMin: number): number[] {137 const out: number[] = [];138 const first = Math.floor(startUtc / CELL_MS) * CELL_MS;139 const last = endUtc + bufferMin * 60_000;140 for (let c = first; c < last; c += CELL_MS) out.push(c);141 return out;142}143144function stepFor(duration: number): number {145 return duration % 30 === 0 ? 30 : 15;146}147148export interface DaySlots {149 date: string;150 slots: number[]; // instants UTC de début151}152153/**154 * Créneaux réellement disponibles pour un type, sur `days` jours locaux à partir de `fromDate`.155 * `ignoreAppointmentId` : exclut les cellules d'un rendez-vous (report).156 */157export function availableSlots(158 type: AppointmentType,159 fromDate: string,160 days: number,161 now = Date.now(),162 ignoreAppointmentId?: number,163): DaySlots[] {164 const db = getDb();165 const rules = listRules();166 const rangeStart = startOfLocalDay(fromDate);167 const rangeEnd = startOfLocalDay(addDays(fromDate, days)) + 24 * 3600_000;168 const exceptions = db169 .prepare("SELECT * FROM availability_exceptions WHERE end_utc > ? AND start_utc < ?")170 .all(rangeStart, rangeEnd) as unknown as Exception[];171 const blocks = exceptions.filter((e) => e.kind === "block");172 const opens = exceptions.filter((e) => e.kind === "open");173 const taken = new Set(174 (175 db176 .prepare("SELECT cell_utc FROM appointment_cells WHERE cell_utc >= ? AND cell_utc < ? AND appointment_id != ?")177 .all(rangeStart - 24 * 3600_000, rangeEnd + 24 * 3600_000, ignoreAppointmentId ?? -1) as { cell_utc: number }[]178 ).map((r) => r.cell_utc),179 );180 const earliest = now + type.min_notice_hours * 3600_000;181 const latest = now + type.max_days_ahead * 24 * 3600_000;182 const durMs = type.duration_min * 60_000;183 const step = stepFor(type.duration_min);184 const out: DaySlots[] = [];185186 for (let i = 0; i < days; i++) {187 const date = addDays(fromDate, i);188 const wd = weekdayOf(date);189 const starts = new Set<number>();190 // Heures de travail hebdomadaires (heure locale → UTC, heures inexistantes ignorées)191 for (const r of rules) {192 if (r.weekday !== wd) continue;193 for (let m = r.start_min; m + type.duration_min <= r.end_min; m += step) {194 const s = localToUtc(date, m);195 if (s !== null) starts.add(s);196 }197 }198 // Ouvertures ponctuelles (instants UTC)199 const dayStart = startOfLocalDay(date);200 const dayEnd = startOfLocalDay(addDays(date, 1));201 for (const o of opens) {202 const from = Math.max(o.start_utc, dayStart);203 const to = Math.min(o.end_utc, dayEnd);204 for (let s = Math.ceil(from / (step * 60_000)) * step * 60_000; s + durMs <= to; s += step * 60_000) starts.add(s);205 }206 const slots = [...starts]207 .filter((s) => s >= earliest && s <= latest)208 .filter((s) => !blocks.some((b) => s < b.end_utc && s + durMs > b.start_utc))209 .filter((s) => cellsFor(s, s + durMs, type.buffer_min).every((c) => !taken.has(c)))210 // la marge du rendez-vous PRÉCÉDENT est déjà dans ses cellules ; on vérifie aussi la nôtre ci-dessus211 .sort((a, b) => a - b);212 out.push({ date, slots });213 }214 return out;215}216217export class SlotUnavailableError extends Error {218 constructor() {219 super("Ce créneau n'est plus disponible.");220 this.name = "SlotUnavailableError";221 }222}223224function isConstraint(e: unknown): boolean {225 const err = e as { errcode?: number; message?: string };226 return err?.errcode === 1555 || err?.errcode === 2067 || /UNIQUE constraint failed|PRIMARY KEY/i.test(err?.message ?? "");227}228229function slotIsOffered(type: AppointmentType, startUtc: number, now: number, ignoreId?: number): boolean {230 const date = todayLocal(startUtc);231 return availableSlots(type, date, 1, now, ignoreId)[0].slots.includes(startUtc);232}233234export interface BookingInput {235 typeSlug: string;236 startUtc: number;237 name: string;238 email: string;239 phone?: string;240 organization?: string;241 reason: string;242 notes?: string;243 userId?: number | null;244 now?: number;245}246247/** Réserve un créneau de façon atomique. Lève SlotUnavailableError si le créneau n'est pas (ou plus) libre. */248export function createBooking(input: BookingInput): { appointment: Appointment; manageToken: string } {249 const db = getDb();250 const now = input.now ?? Date.now();251 const type = typeBySlug(input.typeSlug);252 if (!type || !type.active) throw new SlotUnavailableError();253 const settings = bookingSettings();254 const endUtc = input.startUtc + type.duration_min * 60_000;255 const token = randomToken(32);256 try {257 const id = tx(db, () => {258 if (!slotIsOffered(type, input.startUtc, now)) throw new SlotUnavailableError();259 const r = db260 .prepare(261 "INSERT INTO appointments (type_id, user_id, start_utc, end_utc, status, name, email, phone, organization, reason, notes, consent_at, manage_token_hash, manage_token_enc, token_expires_at, created_at, updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",262 )263 .run(264 type.id,265 input.userId ?? null,266 input.startUtc,267 endUtc,268 settings.autoConfirm ? "confirmed" : "requested",269 input.name,270 input.email,271 input.phone ?? "",272 input.organization ?? "",273 input.reason,274 input.notes ?? "",275 now,276 sha256(token),277 encrypt(token),278 input.startUtc,279 now,280 now,281 );282 const apptId = Number(r.lastInsertRowid);283 const ins = db.prepare("INSERT INTO appointment_cells (cell_utc, appointment_id) VALUES (?, ?)");284 for (const c of cellsFor(input.startUtc, endUtc, type.buffer_min)) ins.run(c, apptId);285 return apptId;286 });287 return { appointment: appointmentById(id)!, manageToken: token };288 } catch (e) {289 if (e instanceof SlotUnavailableError || isConstraint(e)) throw new SlotUnavailableError();290 throw e;291 }292}293294export function appointmentById(id: number): Appointment | undefined {295 return getDb().prepare("SELECT * FROM appointments WHERE id = ?").get(id) as Appointment | undefined;296}297298/** Rendez-vous associé à un lien de gestion (jeton haché, expirant au début du rendez-vous). */299export function appointmentByToken(token: string, now = Date.now()): Appointment | null {300 if (!token || token.length < 20) return null;301 const a = getDb().prepare("SELECT * FROM appointments WHERE manage_token_hash = ?").get(sha256(token)) as Appointment | undefined;302 if (!a || a.token_expires_at <= now || a.anonymized_at) return null;303 return a;304}305306export function setStatus(id: number, status: AppointmentStatus, cancelReason = ""): Appointment | null {307 const db = getDb();308 const a = appointmentById(id);309 if (!a) return null;310 tx(db, () => {311 db.prepare("UPDATE appointments SET status = ?, cancel_reason = ?, updated_at = ? WHERE id = ?").run(312 status,313 status === "cancelled" ? cancelReason.slice(0, 300) : "",314 Date.now(),315 id,316 );317 if (status === "cancelled") db.prepare("DELETE FROM appointment_cells WHERE appointment_id = ?").run(id);318 });319 return appointmentById(id)!;320}321322/** Report atomique : libère les anciennes cellules et prend les nouvelles dans la même transaction. */323export function reschedule(id: number, newStartUtc: number, opts: { byAdmin: boolean; now?: number }): Appointment {324 const db = getDb();325 const now = opts.now ?? Date.now();326 const a = appointmentById(id);327 if (!a || a.status === "cancelled" || a.status === "completed") throw new SlotUnavailableError();328 const type = typeById(a.type_id);329 if (!type) throw new SlotUnavailableError();330 const endUtc = newStartUtc + type.duration_min * 60_000;331 const settings = bookingSettings();332 try {333 tx(db, () => {334 // L'administrateur peut déroger au délai minimum, pas aux conflits.335 const offered = opts.byAdmin336 ? slotIsOffered({ ...type, min_notice_hours: 0, max_days_ahead: 3650 }, newStartUtc, now, id)337 : slotIsOffered(type, newStartUtc, now, id);338 if (!offered) throw new SlotUnavailableError();339 db.prepare("DELETE FROM appointment_cells WHERE appointment_id = ?").run(id);340 const ins = db.prepare("INSERT INTO appointment_cells (cell_utc, appointment_id) VALUES (?, ?)");341 for (const c of cellsFor(newStartUtc, endUtc, type.buffer_min)) ins.run(c, id);342 db.prepare(343 "UPDATE appointments SET start_utc = ?, end_utc = ?, token_expires_at = ?, status = ?, reminder_sent_at = NULL, updated_at = ? WHERE id = ?",344 ).run(newStartUtc, endUtc, newStartUtc, opts.byAdmin || settings.autoConfirm ? "confirmed" : "requested", now, id);345 });346 } catch (e) {347 if (e instanceof SlotUnavailableError || isConstraint(e)) throw new SlotUnavailableError();348 throw e;349 }350 return appointmentById(id)!;351}352353export function listAppointments(filter: { fromUtc?: number; toUtc?: number; status?: AppointmentStatus | "all"; q?: string; userId?: number; email?: string } = {}) {354 const where: string[] = [];355 const args: (string | number)[] = [];356 const add = (clause: string, ...values: (string | number)[]) => {357 where.push(clause);358 args.push(...values);359 };360 if (filter.fromUtc !== undefined) add("a.start_utc >= ?", filter.fromUtc);361 if (filter.toUtc !== undefined) add("a.start_utc < ?", filter.toUtc);362 if (filter.status && filter.status !== "all") add("a.status = ?", filter.status);363 if (filter.q) {364 where.push("(lower(a.name) LIKE ? OR lower(a.email) LIKE ? OR lower(a.organization) LIKE ?)");365 const q = `%${filter.q.toLowerCase()}%`;366 args.push(q, q, q);367 }368 if (filter.userId !== undefined && filter.email) add("(a.user_id = ? OR lower(a.email) = ?)", filter.userId, filter.email.toLowerCase());369 return getDb()370 .prepare(371 `SELECT a.*, t.title AS type_title, t.location_kind, t.location_detail, t.duration_min FROM appointments a JOIN appointment_types t ON t.id = a.type_id ${372 where.length ? "WHERE " + where.join(" AND ") : ""373 } ORDER BY a.start_utc ASC LIMIT 1000`,374 )375 .all(...args) as unknown as (Appointment & { type_title: string; location_kind: string; location_detail: string; duration_min: number })[];376}377378/** Rappels à envoyer : rendez-vous confirmés débutant dans la fenêtre de rappel. */379export function dueReminders(now = Date.now()): Appointment[] {380 const h = bookingSettings().reminderHours;381 if (h <= 0) return [];382 return getDb()383 .prepare("SELECT * FROM appointments WHERE status = 'confirmed' AND reminder_sent_at IS NULL AND anonymized_at IS NULL AND start_utc > ? AND start_utc <= ?")384 .all(now, now + h * 3600_000) as unknown as Appointment[];385}386export function markReminderSent(id: number) {387 getDb().prepare("UPDATE appointments SET reminder_sent_at = ? WHERE id = ?").run(Date.now(), id);388}389390/** Efface les renseignements personnels d'un rendez-vous (la plage horaire reste pour les statistiques). */391export function anonymize(id: number) {392 getDb()393 .prepare(394 "UPDATE appointments SET name = '(supprimé)', email = '', phone = '', organization = '', reason = '', notes = '', admin_notes = '', manage_token_enc = '', user_id = NULL, anonymized_at = ?, token_expires_at = 0 WHERE id = ?",395 )396 .run(Date.now(), id);397}398399/** Conservation : anonymise les rendez-vous terminés depuis plus de `retentionDays` jours et purge les vieux journaux. */400export function applyRetention(now = Date.now()): number {401 const days = bookingSettings().retentionDays;402 const db = getDb();403 let n = 0;404 if (days > 0) {405 const cutoff = now - days * 24 * 3600_000;406 const rows = db.prepare("SELECT id FROM appointments WHERE anonymized_at IS NULL AND end_utc < ?").all(cutoff) as { id: number }[];407 for (const r of rows) {408 anonymize(r.id);409 n++;410 }411 db.prepare("DELETE FROM outbox WHERE created_at < ?").run(cutoff);412 db.prepare("DELETE FROM contact_messages WHERE created_at < ? AND status = 'archived'").run(cutoff);413 }414 db.prepare("DELETE FROM sessions WHERE expires_at < ?").run(now);415 db.prepare("DELETE FROM user_tokens WHERE expires_at < ?").run(now - 7 * 24 * 3600_000);416 db.prepare("DELETE FROM appointment_cells WHERE cell_utc < ?").run(now - 7 * 24 * 3600_000);417 return n;418}419420/** Jeton de gestion en clair (déchiffré) pour les courriels ultérieurs ; null s'il a expiré ou été effacé. */421export function manageTokenOf(a: Appointment, now = Date.now()): string | null {422 if (!a.manage_token_enc || a.token_expires_at <= now) return null;423 try {424 return decrypt(a.manage_token_enc);425 } catch {426 return null;427 }428}429