TanStack Query 预订暂占：15分钟过期策略，无幽灵库存

-- migrations/0001_booking_holds.sql
CREATE TABLE available_slots (
  id          TEXT PRIMARY KEY,          -- 'slot_2026-08-12T14:00'
  resource_id TEXT NOT NULL,             -- the bookable resource (room, car, stylist)
  starts_at   INTEGER NOT NULL,          -- unix epoch seconds, UTC
  ends_at     INTEGER NOT NULL,
  status      TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'closed'))
);
CREATE INDEX idx_slots_resource_time ON available_slots(resource_id, starts_at);

CREATE TABLE holds (
  id           TEXT PRIMARY KEY,         -- 'hold_<ulid>'
  slot_id      TEXT NOT NULL,
  customer_id  TEXT NOT NULL,
  hold_version INTEGER NOT NULL DEFAULT 1,
  expires_at   INTEGER NOT NULL,         -- server clock, NOT browser clock
  created_at   INTEGER NOT NULL,
  FOREIGN KEY (slot_id) REFERENCES available_slots(id)
);
CREATE INDEX idx_holds_slot ON holds(slot_id);
CREATE INDEX idx_holds_expiry ON holds(expires_at);

CREATE TABLE reservations (
  id          TEXT PRIMARY KEY,          -- 'rsv_<ulid>'
  hold_id     TEXT NOT NULL UNIQUE,      -- one reservation per hold
  slot_id     TEXT NOT NULL,
  customer_id TEXT NOT NULL,
  stripe_pi   TEXT NOT NULL,             -- Stripe PaymentIntent id
  amount_cents INTEGER NOT NULL,
  created_at  INTEGER NOT NULL,
  FOREIGN KEY (hold_id) REFERENCES holds(id),
  FOREIGN KEY (slot_id) REFERENCES available_slots(id)
);
CREATE INDEX idx_reservations_slot ON reservations(slot_id);

// hooks/useHoldSlot.ts
import { useMutation, useQueryClient } from "@tanstack/react-query";

export function useHoldSlot(slotId: string) {
  const qc = useQueryClient();
  return useMutation({
    mutationFn: async () => {
      const res = await fetch(`/api/booking/hold`, {
        method: "POST",
        body: JSON.stringify({ slotId }),
        headers: { "Content-Type": "application/json" },
      });
      if (!res.ok) throw new Error(`Hold rejected: ${res.status}`);
      return (await res.json()) as { holdId: string; holdVersion: number; expiresAt: number };
    },
    onMutate: async () => {
      await qc.cancelQueries({ queryKey: ["availability", slotId] });
      const previous = qc.getQueryData<Availability>(["availability", slotId]);
      qc.setQueryData<Availability>(["availability", slotId], (old) => ({
        ...old!,
        slots: old!.slots.map((s) =>
          s.id === slotId ? { ...s, status: "held-by-me" as const } : s
        ),
      }));
      return { previous };
    },
    onError: (_err, _vars, ctx) => {
      // 关键：回滚到之前的可用性视图。
      // 根据 TanStack Query 文档，onError 处理器是
      // 恢复之前缓存快照的唯一安全位置。
      if (ctx?.previous) {
        qc.setQueryData(["availability", slotId], ctx.previous);
      }
    },
    onSettled: () => {
      qc.invalidateQueries({ queryKey: ["availability", slotId] });
    },
  });
}

// lib/booking/commit-hold.ts
import { getCloudflareContext } from "@tanstack/start/server";

export async function commitHold(slotId: string, customerId: string) {
  const { env } = getCloudflareContext();
  const now = Math.floor(Date.now() / 1000);
  const expiresAt = now + 15 * 60; // 15分钟暂占

  // 原子插入：一个时段最多只能有一个未过期的暂占。
  // 部分 UNIQUE 索引在存储层强制执行此约束。
  try {
    const { holdId, holdVersion } = await env.DB.prepare(
      `INSERT INTO holds (id, slot_id, customer_id, hold_version, expires_at, created_at)
       VALUES (?1, ?2, ?3, 1, ?4, ?5)
       RETURNING id AS holdId, hold_version AS holdVersion`
    )
      .bind(`hold_${crypto.randomUUID()}`, slotId, customerId, expiresAt, now)
      .first<{ holdId: string; holdVersion: number }>();
    return { holdId, holdVersion, expiresAt };
  } catch (e) {
    if (String(e).includes("UNIQUE")) {
      throw new Error("SLOT_ALREADY_HELD");
    }
    throw e;
  }
}

// 在 onMutate 中，乐观更新之前：
const live = qc.getQueryData<Availability>(["availability", slotId]);
if (live?.slots.find((s) => s.id === slotId)?.status === "held-by-other") {
  throw new Error("SLOT_ALREADY_HELD_LOCALLY");
}

-- migrations/0002_holds_expiry_sweep.sql
DELETE FROM holds
WHERE expires_at < strftime('%s', 'now');

// routes/api/booking/confirm.ts
export async function confirmReservation(holdId: string, paymentIntentId: string) {
  const { env } = getCloudflareContext();
  const hold = await env.DB.prepare(
    `SELECT slot_id, customer_id, hold_version, expires_at FROM holds WHERE id = ?1`
  )
    .bind(holdId)
    .first<{ slot_id: string; customer_id: string; hold_version: number; expires_at: number }>();
  if (!hold) throw new Error("HOLD_EXPIRED");
  if (hold.expires_at < Math.floor(Date.now() / 1000)) {
    await env.DB.prepare(`DELETE FROM holds WHERE id = ?1`).bind(holdId).run();
    throw new Error("HOLD_EXPIRED");
  }
  // 插入预订；如果因任何原因失败，暂占在 catch 中被删除。
  try {
    await env.DB.prepare(
      `INSERT INTO reservations (id, hold_id, slot_id, customer_id, stripe_pi, amount_cents, created_at)
       VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)`
    )
      .bind(
        `rsv_${crypto.randomUUID()}`,
        holdId,
        hold.slot_id,
        hold.customer_id,
        paymentIntentId,
        4900,
        Math.floor(Date.now() / 1000)
      )
      .run();
  } catch (e) {
    await env.DB.prepare(`DELETE FROM holds WHERE id = ?1`).bind(holdId).run();
    throw e;
  }
  return { slotId: hold.slot_id };
}