CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY,
  sub TEXT UNIQUE NOT NULL,
  email TEXT,
  display_name TEXT,
  roles TEXT[] DEFAULT ARRAY['investigator'],
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION set_users_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_users_updated ON users;
CREATE TRIGGER trg_users_updated BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION set_users_updated_at();


