BEGIN;

CREATE TABLE IF NOT EXISTS student_menu_controls (
  student_id INTEGER PRIMARY KEY REFERENCES students(id) ON DELETE CASCADE,
  controls JSONB NOT NULL DEFAULT '{}'::jsonb,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_student_menu_controls_updated_at
  ON student_menu_controls(updated_at DESC);

CREATE TABLE IF NOT EXISTS student_activity_sessions (
  id BIGSERIAL PRIMARY KEY,
  student_id INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  session_key TEXT NOT NULL,
  activity_type TEXT NOT NULL DEFAULT 'login',
  started_at TIMESTAMPTZ NOT NULL,
  last_activity_at TIMESTAMPTZ NOT NULL,
  ended_at TIMESTAMPTZ,
  subject TEXT NOT NULL DEFAULT 'General Learning',
  note TEXT NOT NULL DEFAULT 'Student login session',
  assignment_id TEXT,
  assignment_title TEXT,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (student_id, session_key)
);

CREATE INDEX IF NOT EXISTS idx_student_activity_sessions_student_time
  ON student_activity_sessions(student_id, started_at DESC);

COMMIT;
