CREATE TABLE registration  (
  id                   INTEGER PRIMARY KEY AUTOINCREMENT,
  event_id             INTEGER NOT NULL,
  first_name           TEXT    NOT NULL,
  last_name            TEXT    NOT NULL,
  -- COLLATE NOCASE makes the database compare emails case-insensitively,
  -- so the unique index below also blocks duplicates that differ only in
    -- case (A@example.com vs a@example.com).
    email                TEXT    NOT NULL COLLATE NOCASE,
  phone                TEXT,
  -- optional contact number
    organisation         TEXT,
  -- optional company/college
    attendee_type        TEXT    NOT NULL DEFAULT 'Professional'
                         CHECK (attendee_type IN ('Professional', 'Student', 'Other')),
  dietary_requirements TEXT,
  -- optional,
  for catering
    -- Cancelling sets status to 'cancelled' instead of deleting the row,
  so
    -- the organisers keep a record and the place is freed automatically.
    status               TEXT    NOT NULL DEFAULT 'confirmed'
                         CHECK (status IN ('confirmed', 'cancelled')),
  created_at           TEXT    NOT NULL DEFAULT (datetime('now')),
  updated_at           TEXT    NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (event_id) REFERENCES event (id)
)