schema.sql

-- Group event scheduler schema (Cloudflare D1 / SQLite)

-- An admin is identified by a secret bearer key (a GUID). Admins create and
-- delete organizers. We store only the SHA-256 hash of the key, never the key.
CREATE TABLE IF NOT EXISTS admins (
  id         TEXT PRIMARY KEY,   -- sha256 hex of the admin key
  name       TEXT,
  created_at INTEGER NOT NULL
);

-- An organizer is created by an admin and identified by a secret bearer key
-- (a GUID). Organizers create and manage their own events. We store only the
-- SHA-256 hash of the key, never the key itself.
CREATE TABLE IF NOT EXISTS organizers (
  id              TEXT PRIMARY KEY,   -- sha256 hex of the organizer key
  name            TEXT,
  created_by      TEXT,               -- admin id that minted it (nullable)
  created_at      INTEGER NOT NULL
);

-- An event belongs to one organizer and is reached via a public GUID URL.
CREATE TABLE IF NOT EXISTS events (
  id             TEXT PRIMARY KEY,   -- public short base62 id used in /e/:id
  organizer_id   TEXT NOT NULL,
  title          TEXT NOT NULL,
  location       TEXT,
  description    TEXT,
  start_time     TEXT,               -- "HH:MM"
  end_time       TEXT,               -- "HH:MM"
  candidate_days TEXT NOT NULL,      -- JSON array of distinct "YYYY-MM-DD" (derived from slots)
  slots          TEXT,               -- JSON array of {id,date,start,end}; the time options. Null = legacy (derive from candidate_days + start/end, slot id = date)
  organizer_prefs TEXT,              -- JSON map slotId -> 'works'|'maybe' (organizer's own preference)
  questions      TEXT,               -- JSON array of {id,text} asked of attendees after availability
  mode           TEXT,               -- 'slots' (organizer picks dates/times) or 'range' (guests pick days in a range). Null = slots
  range_start    TEXT,               -- range mode: first day "YYYY-MM-DD"
  range_end      TEXT,               -- range mode: last day "YYYY-MM-DD"
  show_others    INTEGER,            -- range mode: 1 = guests see which days others picked (default 0)
  chosen_date    TEXT,               -- finalized: slot id (slots mode) or a "YYYY-MM-DD" (range mode)
  created_at     INTEGER NOT NULL,
  updated_at     INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_events_org ON events(organizer_id);

-- An attendee is a per-device identity (GUID in localStorage) with a display name.
CREATE TABLE IF NOT EXISTS attendees (
  id         TEXT PRIMARY KEY,   -- GUID from attendee localStorage
  name       TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

-- One row per (event, attendee, day) availability mark.
CREATE TABLE IF NOT EXISTS responses (
  event_id    TEXT NOT NULL,
  attendee_id TEXT NOT NULL,
  day         TEXT NOT NULL,      -- "YYYY-MM-DD"
  status      TEXT NOT NULL,      -- 'works' | 'maybe' | 'no'
  updated_at  INTEGER NOT NULL,
  PRIMARY KEY (event_id, attendee_id, day)
);
CREATE INDEX IF NOT EXISTS idx_responses_event ON responses(event_id);

-- One row per (event, attendee, question) free-text answer.
CREATE TABLE IF NOT EXISTS answers (
  event_id    TEXT NOT NULL,
  attendee_id TEXT NOT NULL,
  question_id TEXT NOT NULL,
  answer      TEXT,
  updated_at  INTEGER NOT NULL,
  PRIMARY KEY (event_id, attendee_id, question_id)
);
CREATE INDEX IF NOT EXISTS idx_answers_event ON answers(event_id);

-- Once an event is finalized it flips to RSVP mode; one head-count row per attendee.
CREATE TABLE IF NOT EXISTS rsvps (
  event_id    TEXT NOT NULL,
  attendee_id TEXT NOT NULL,
  count       INTEGER NOT NULL,
  updated_at  INTEGER NOT NULL,
  PRIMARY KEY (event_id, attendee_id)
);
CREATE INDEX IF NOT EXISTS idx_rsvps_event ON rsvps(event_id);

-- Web Push subscriptions belonging to an organizer (one row per device/endpoint).
CREATE TABLE IF NOT EXISTS push_subs (
  endpoint     TEXT PRIMARY KEY,
  organizer_id TEXT NOT NULL,
  p256dh       TEXT NOT NULL,
  auth         TEXT NOT NULL,
  created_at   INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_push_subs_org ON push_subs(organizer_id);