CREATE EXTENSION IF NOT EXISTS citext;
-- nhl.mylife.education — registrations for program updates
-- PostgreSQL version. Requires: CREATE EXTENSION IF NOT EXISTS citext;
CREATE TABLE IF NOT EXISTS registrations (
  id              BIGSERIAL PRIMARY KEY,
  first_name      TEXT NOT NULL,
  last_name       TEXT,
  email           CITEXT NOT NULL UNIQUE,
  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      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  unsubscribed_at TIMESTAMPTZ
);
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          BIGSERIAL PRIMARY KEY,
  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       JSONB,                                      -- JSON
  ts          TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_events_session ON events (session_id);
CREATE INDEX IF NOT EXISTS idx_events_event   ON events (event, ts);