SPB Git

Repository Public

service.ts 17.6 KB 429 lines typescript

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
Files main
Menu

Theme