-- D1 schema for the booking site (calendar-site skill).
-- One row per public booking submission. Captured at the edge so a submission
-- survives a brief Pi outage; the platform reconcile pass later reads eligible
-- rows (kind='booking') whose meeting is absent and writes each into Neo4j as a
-- :Meeting.
CREATE TABLE IF NOT EXISTS bookings (
  id        INTEGER PRIMARY KEY AUTOINCREMENT,
  bookingId TEXT UNIQUE NOT NULL,
  slotStart TEXT NOT NULL,
  slotEnd   TEXT NOT NULL,
  name      TEXT NOT NULL,
  email     TEXT NOT NULL,
  note      TEXT,
  status    TEXT NOT NULL DEFAULT 'accepted',
  createdAt TEXT NOT NULL,
  swept     INTEGER NOT NULL DEFAULT 0,
  -- Task 1806 — 'booking' is a row that must become a :Meeting; 'block' only
  -- reserves a slot and has no meeting by design. The reconcile sweep and its
  -- audit both qualify on this, so a block row is never counted as a backlog
  -- and never reconciled into a meeting.
  kind      TEXT NOT NULL DEFAULT 'booking',
  -- Task 1807 — count of sweep claims for this row. Incremented before each
  -- write attempt, so a row being retried and failing is distinguishable in the
  -- journal from one never attempted. Never a gate; nothing reads it to decide.
  attempts  INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX IF NOT EXISTS bookings_unswept ON bookings (status, swept);

-- Single-row cache of the account's computed free/busy, published by the device
-- on a short timer (platform/ui server → publish-availability). The booking
-- page's edge free/busy Function serves open times from this snapshot minus any
-- accepted booking, so the public page keeps showing availability while the
-- device is offline. id is pinned to 1: one account per booking site, one row.
CREATE TABLE IF NOT EXISTS availability_snapshot (
  id        INTEGER PRIMARY KEY CHECK (id = 1),
  payload   TEXT,
  updatedAt TEXT
);
