-- OIDC phase 2: revocable Hub sessions + session-bound refresh tokens
-- (docs/design/2026-10-13-files-oidc-sso.md security note: logout must
-- propagate past the Hub cookie; refresh tokens inherit the Hub session's
-- lifetime so an oCIS session can never outlive the sign-in that minted it).

-- One row per issued Hub session. Written at login/reissue, revoked at
-- logout. Stateless request validation (RbacGuard) is unchanged: rows are
-- consulted only by /oidc refresh and by reissue's liveness check.
CREATE TABLE auth_sessions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id uuid NOT NULL REFERENCES workspaces (id) ON DELETE CASCADE,
  user_id uuid NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  created_at timestamptz NOT NULL DEFAULT now(),
  expires_at timestamptz NOT NULL,
  revoked_at timestamptz
);

CREATE POLICY tenant_isolation ON auth_sessions
  USING (workspace_id = NULLIF(current_setting('app.workspace_id', true), '')::uuid);

CREATE INDEX auth_sessions_ws_user ON auth_sessions (workspace_id, user_id);
CREATE INDEX auth_sessions_expiry ON auth_sessions (expires_at) WHERE revoked_at IS NULL;

-- Rotation chains share family_id; presenting a consumed token revokes the
-- whole family (OAuth BCP reuse detection). Rows cascade away with their
-- session, so a logged-out session takes its tokens with it.
CREATE TABLE oidc_refresh_tokens (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id uuid NOT NULL REFERENCES workspaces (id) ON DELETE CASCADE,
  user_id uuid NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  sid uuid NOT NULL REFERENCES auth_sessions (id) ON DELETE CASCADE,
  client_id text NOT NULL,
  scope text NOT NULL,
  family_id uuid NOT NULL,
  token_hash text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now(),
  consumed_at timestamptz
);

CREATE POLICY tenant_isolation ON oidc_refresh_tokens
  USING (workspace_id = NULLIF(current_setting('app.workspace_id', true), '')::uuid);

CREATE INDEX oidc_refresh_family ON oidc_refresh_tokens (family_id);
CREATE INDEX oidc_refresh_sid ON oidc_refresh_tokens (sid);

-- POST /oidc/token is unauthenticated: the refresh lookup runs before any
-- tenant context exists, so it goes through a definer-rights function (the
-- audit_append pattern).
CREATE FUNCTION oidc_refresh_lookup(p_token_hash text) RETURNS TABLE (
  id uuid, workspace_id uuid, user_id uuid, sid uuid,
  client_id text, scope text, family_id uuid,
  created_at timestamptz, consumed_at timestamptz
) LANGUAGE sql SECURITY DEFINER SET search_path = public AS $$
  SELECT id, workspace_id, user_id, sid, client_id, scope, family_id, created_at, consumed_at
  FROM oidc_refresh_tokens
  WHERE token_hash = p_token_hash
$$;