-- sessions — replaces Phase 2B's in-memory studentSessions/adminSessions Maps. A Platform API
-- restart no longer logs everyone out, and sessions are individually revocable/auditable.
--
-- Only a SHA-256 hash of the opaque bearer token is stored (token_hash) — never the raw token
-- itself, so a database read (backup, dump, compromised replica) can't be turned into working
-- session cookies.
--
-- expires_at is the absolute ceiling (set once, at creation). idle_expires_at slides forward on
-- every authenticated request (see src/sessions.js resolve*Session), capped at expires_at —
-- whichever of the two is hit first ends the session. session_type distinguishes student vs.
-- instructor sessions; the CHECK below keeps a session row from ever being ambiguous about
-- which kind it is, so a bug elsewhere can't accidentally treat one as the other.

CREATE TABLE IF NOT EXISTS sessions (
  session_id       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_type     TEXT NOT NULL CHECK (session_type IN ('student', 'instructor')),
  student_id       TEXT REFERENCES students(student_id),
  instructor_id    UUID REFERENCES instructors(instructor_id),
  token_hash       TEXT NOT NULL,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_seen_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at       TIMESTAMPTZ NOT NULL,
  idle_expires_at  TIMESTAMPTZ NOT NULL,
  revoked_at       TIMESTAMPTZ,
  metadata         JSONB,
  CHECK (
    (session_type = 'student' AND student_id IS NOT NULL AND instructor_id IS NULL) OR
    (session_type = 'instructor' AND instructor_id IS NOT NULL AND student_id IS NULL)
  )
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_sessions_token_hash ON sessions(token_hash);
CREATE INDEX IF NOT EXISTS idx_sessions_instructor ON sessions(instructor_id) WHERE instructor_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_sessions_student ON sessions(student_id) WHERE student_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_sessions_expiry ON sessions(expires_at);