Repository Public
1import { sql, type Tx } from "../db";2import { ApiError } from "../http";3import type { Counted, EngineInput, ExceptionInput, SlotParams, Taken, WeeklyRule } from "../engine/slots";45type Q = Tx | typeof sql;67export interface EventTypeRow {8 id: string; user_id: string; slug: string; title: string; title_en: string; description: string; description_en: string;9 duration_min: number; color: string; location_kind: "video" | "phone" | "in_person"; location_detail: string;10 instructions: string; instructions_en: string; languages: string[]; approval: "manual" | "auto"; hold_policy: "hold" | "none";11 hold_ttl_hours: number; buffer_before_min: number; buffer_after_min: number; slot_step_min: number; min_notice_min: number;12 horizon_days: number; daily_limit: number | null; weekly_limit: number | null;13 questions: { key: string; label: string; label_en?: string; kind: "text" | "textarea"; required: boolean }[];14 ask_organization: boolean; require_reason: boolean; allow_visitor_cancel: boolean; allow_visitor_reschedule: boolean;15 reminder_hours: number | null; custom_availability: boolean; active: boolean; position: number;16}1718export interface SettingsRow {19 user_id: string; timezone: string; locale: "fr" | "en"; public_name: string; public_headline: string; public_headline_en: string;20 public_bio: string; public_bio_en: string; avatar_mime: string | null; brand_color: string; notify_email: string | null; reply_to: string | null;21 retention_days: number; allowed_email_domains: string[]; blocked_email_domains: string[];22 external_stale_minutes: number; external_stale_policy: "conservative" | "ignore"; has_feed: boolean;23}2425export async function getSettings(userId: string, q: Q = sql): Promise<SettingsRow> {26 const [s] = await q<SettingsRow[]>`27 SELECT user_id, timezone, locale, public_name, public_headline, public_headline_en, public_bio, public_bio_en, avatar_mime, brand_color,28 notify_email, reply_to, retention_days, allowed_email_domains, blocked_email_domains, external_stale_minutes, external_stale_policy,29 (feed_token_hash IS NOT NULL) AS has_feed30 FROM settings WHERE user_id = ${userId}`;31 if (!s) throw new ApiError(500, "no_settings", "Owner settings are missing");32 return s;33}3435export async function getEventType(where: { id?: string; slug?: string }, q: Q = sql): Promise<EventTypeRow | null> {36 const rows = where.id37 ? await q<EventTypeRow[]>`SELECT * FROM event_types WHERE id = ${where.id}`38 : await q<EventTypeRow[]>`SELECT * FROM event_types WHERE slug = ${where.slug ?? ""}`;39 return rows[0] ?? null;40}4142export const paramsOf = (et: EventTypeRow, timezone: string): SlotParams => ({43 timezone, durationMin: et.duration_min, slotStepMin: et.slot_step_min, bufferBeforeMin: et.buffer_before_min, bufferAfterMin: et.buffer_after_min,44 minNoticeMin: et.min_notice_min, horizonDays: et.horizon_days, dailyLimit: et.daily_limit, weeklyLimit: et.weekly_limit,45});4647const pad = (n: number) => String(n).padStart(2, "0");48/** node-postgres style Date for a "timestamp without time zone" is built in the process zone; read the fields back as text instead. */49const localText = (v: string) => v.replace(" ", "T").slice(0, 16);5051export async function loadRules(userId: string, et: EventTypeRow | null, q: Q = sql): Promise<WeeklyRule[]> {52 const rows = et?.custom_availability53 ? await q<{ weekday: number; start_minute: number; end_minute: number }[]>`SELECT weekday, start_minute, end_minute FROM availability_rules WHERE user_id = ${userId} AND event_type_id = ${et.id}`54 : await q<{ weekday: number; start_minute: number; end_minute: number }[]>`SELECT weekday, start_minute, end_minute FROM availability_rules WHERE user_id = ${userId} AND event_type_id IS NULL`;55 return rows.map((r) => ({ weekday: r.weekday, startMinute: r.start_minute, endMinute: r.end_minute }));56}5758export async function loadExceptions(userId: string, eventTypeId: string | null, q: Q = sql): Promise<ExceptionInput[]> {59 const rows = await q<{ kind: "open" | "closed"; mode: "range" | "weekly"; starts_local: string | null; ends_local: string | null; all_day: boolean; weekdays: number[] | null; start_minute: number | null; duration_min: number | null; valid_from: string | null; valid_until: string | null }[]>`60 SELECT kind, mode, starts_local::text, ends_local::text, all_day, weekdays, start_minute, duration_min, valid_from::text, valid_until::text61 FROM availability_exceptions62 WHERE user_id = ${userId} AND (event_type_id IS NULL OR event_type_id = ${eventTypeId})`;63 return rows.map((r): ExceptionInput => r.mode === "range"64 ? { kind: r.kind, mode: "range", startsLocal: localText(r.starts_local!), endsLocal: localText(r.ends_local!), allDay: r.all_day }65 : { kind: r.kind, mode: "weekly", weekdays: r.weekdays!, startMinute: r.start_minute!, durationMin: r.duration_min!, validFrom: r.valid_from!, validUntil: r.valid_until });66}6768export interface StateOpts { ignoreRequestId?: string | null; ignoreBookingId?: string | null; now: number }6970/**71 * Everything that occupies the owner's time between two instants:72 * - manual private events and busy periods read from external calendars that are set to block;73 * - confirmed bookings (with their buffers);74 * - requests that currently HOLD a slot: pending with an unexpired hold, or a proposal awaiting the visitor.75 * Requests without a hold (policy "none", or hold expired) do not block anything.76 */77export async function loadTaken(userId: string, fromMs: number, toMs: number, opts: StateOpts, q: Q = sql): Promise<Taken[]> {78 const from = new Date(fromMs - 86_400_000), to = new Date(toMs + 86_400_000), now = new Date(opts.now);79 const blocks = await q<{ starts_at: Date; ends_at: Date }[]>`80 SELECT b.starts_at, b.ends_at FROM busy_blocks b81 LEFT JOIN external_calendars c ON c.id = b.external_calendar_id82 WHERE b.user_id = ${userId} AND b.ends_at > ${from} AND b.starts_at < ${to}83 AND (b.source = 'manual' OR c.blocks_availability)`;84 const bookings = await q<{ id: string; starts_at: Date; ends_at: Date; buffer_before_min: number; buffer_after_min: number }[]>`85 SELECT id, starts_at, ends_at, buffer_before_min, buffer_after_min FROM bookings86 WHERE user_id = ${userId} AND status = 'confirmed' AND ends_at > ${from} AND starts_at < ${to}`;87 const holds = await q<{ id: string; s: Date; e: Date; buffer_before_min: number; buffer_after_min: number }[]>`88 SELECT r.id,89 CASE WHEN r.status = 'reschedule_requested' THEN r.proposed_starts_at ELSE r.starts_at END AS s,90 CASE WHEN r.status = 'reschedule_requested' THEN r.proposed_ends_at ELSE r.ends_at END AS e,91 t.buffer_before_min, t.buffer_after_min92 FROM booking_requests r JOIN event_types t ON t.id = r.event_type_id93 WHERE r.user_id = ${userId}94 AND ((r.status = 'pending' AND r.hold_expires_at > ${now})95 OR (r.status = 'reschedule_requested' AND r.proposal_expires_at > ${now}))`;96 const out: Taken[] = blocks.map((b) => ({ start: b.starts_at.getTime(), end: b.ends_at.getTime() }));97 for (const b of bookings) if (b.id !== opts.ignoreBookingId) out.push({ start: b.starts_at.getTime(), end: b.ends_at.getTime(), bufferBeforeMin: b.buffer_before_min, bufferAfterMin: b.buffer_after_min });98 for (const h of holds) {99 if (h.id === opts.ignoreRequestId || !h.s || !h.e) continue;100 if (h.e.getTime() <= from.getTime() || h.s.getTime() >= to.getTime()) continue;101 out.push({ start: h.s.getTime(), end: h.e.getTime(), bufferBeforeMin: h.buffer_before_min, bufferAfterMin: h.buffer_after_min });102 }103 return out;104}105106/** Appointments of one type that count towards its daily / weekly limits. */107export async function loadCounted(userId: string, eventTypeId: string, fromMs: number, toMs: number, opts: StateOpts, q: Q = sql): Promise<Counted[]> {108 const from = new Date(fromMs - 8 * 86_400_000), to = new Date(toMs + 8 * 86_400_000), now = new Date(opts.now);109 const rows = await q<{ id: string; kind: string; starts_at: Date }[]>`110 SELECT id, 'booking' AS kind, starts_at FROM bookings111 WHERE user_id = ${userId} AND event_type_id = ${eventTypeId} AND status = 'confirmed' AND starts_at >= ${from} AND starts_at < ${to}112 UNION ALL113 SELECT id, 'request', starts_at FROM booking_requests114 WHERE user_id = ${userId} AND event_type_id = ${eventTypeId} AND status = 'pending' AND hold_expires_at > ${now}115 AND reschedule_of_booking_id IS NULL AND starts_at >= ${from} AND starts_at < ${to}`;116 return rows117 .filter((r) => !(r.kind === "booking" && r.id === opts.ignoreBookingId) && !(r.kind === "request" && r.id === opts.ignoreRequestId))118 .map((r) => ({ start: r.starts_at.getTime() }));119}120121export async function engineInput(et: EventTypeRow, timezone: string, fromMs: number, toMs: number, opts: StateOpts, q: Q = sql): Promise<Omit<EngineInput, "fromDate" | "toDate">> {122 const [rules, exceptions, taken, counted] = [123 await loadRules(et.user_id, et, q),124 await loadExceptions(et.user_id, et.id, q),125 await loadTaken(et.user_id, fromMs, toMs, opts, q),126 await loadCounted(et.user_id, et.id, fromMs, toMs, opts, q),127 ];128 return { params: paramsOf(et, timezone), rules, exceptions, taken, counted, now: opts.now };129}130131/**132 * Freshness of the external calendars that block availability. With the conservative policy, a stale or failing133 * calendar withholds automatic confirmations (they become requests to approve).134 */135export async function externalFreshness(userId: string, settings: SettingsRow, q: Q = sql): Promise<{ stale: boolean; calendars: number; staleNames: string[] }> {136 const rows = await q<{ name: string; last_success_at: Date | null; status: string }[]>`137 SELECT c.name, s.last_success_at, a.status138 FROM external_calendars c JOIN external_calendar_accounts a ON a.id = c.account_id139 LEFT JOIN sync_state s ON s.calendar_id = c.id140 WHERE c.user_id = ${userId} AND c.blocks_availability AND a.status <> 'disconnected'`;141 const limit = Date.now() - settings.external_stale_minutes * 60_000;142 const staleNames = rows.filter((r) => !r.last_success_at || r.last_success_at.getTime() < limit).map((r) => r.name);143 return { stale: staleNames.length > 0, calendars: rows.length, staleNames };144}145146export const ymd = (d: Temporal.PlainDate) => `${d.year}-${pad(d.month)}-${pad(d.day)}`;147