-- nhl.mylife.education — registrations for program updates
-- SQLite (default). A PostgreSQL version is in schema.postgres.sql.
CREATE TABLE IF NOT EXISTS registrations (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name      TEXT NOT NULL,
  last_name       TEXT,
  email           TEXT NOT NULL UNIQUE COLLATE NOCASE,
  role            TEXT,                                  -- patient / caregiver / HCP / other (free text from the select)
  consent         INTEGER NOT NULL DEFAULT 1,            -- 1 = may email; 0 = unsubscribed
  source          TEXT NOT NULL DEFAULT 'nhl.mylife.education',
  ip_hash         TEXT,                                  -- salted SHA-256 prefix, never the raw IP
  user_agent      TEXT,
  created_at      TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  unsubscribed_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_registrations_created ON registrations (created_at);
CREATE INDEX IF NOT EXISTS idx_registrations_consent ON registrations (consent);

-- Placeholder for the launched program: anonymous learning-analytics events (see leverage plan, Section 7)
CREATE TABLE IF NOT EXISTS events (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  session_id  TEXT NOT NULL,                             -- anonymous, generated in the browser
  program_id  TEXT NOT NULL DEFAULT 'nhl',
  event       TEXT NOT NULL,                             -- program_start, node_view, choice_made, assessment_submit, ...
  props       TEXT,                                      -- JSON
  ts          TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_events_session ON events (session_id);
CREATE INDEX IF NOT EXISTS idx_events_event   ON events (event, ts);