CREATE TABLE IF NOT EXISTS auth_users (
  id text PRIMARY KEY,
  email text NOT NULL UNIQUE,
  password_hash text,
  session_version integer NOT NULL DEFAULT 0,
  roles text NOT NULL DEFAULT '["user"]',
  status text NOT NULL DEFAULT 'active',
  created_at timestamptz NOT NULL
);

CREATE TABLE IF NOT EXISTS user_sessions (
  id text PRIMARY KEY,
  user_id text NOT NULL REFERENCES auth_users(id),
  refresh_token_hash text NOT NULL,
  previous_refresh_token_hash text,
  last_refreshed_at timestamptz,
  expires_at timestamptz NOT NULL,
  status text NOT NULL DEFAULT 'active'
);

-- Opaque machine/agent credential (PAT shape) — mirrors @platform-modules/auth
-- engine-custom serviceTokens. Independent of user_sessions and non-rotating.
-- expires_at NULL means non-expiring. Revocation is per-token (status=revoked).
CREATE TABLE IF NOT EXISTS service_tokens (
  id text PRIMARY KEY,
  user_id text NOT NULL REFERENCES auth_users(id),
  token_hash text NOT NULL UNIQUE,
  label text NOT NULL,
  expires_at timestamptz,
  status text NOT NULL DEFAULT 'active',
  created_at timestamptz NOT NULL,
  last_used_at timestamptz
);
