supabase/apps/studio/data/auth/users-infinite-query.ts

import { useInfiniteQuery, UseInfiniteQueryOptions } from '@tanstack/react-query'

import type { components } from 'data/api'
import { executeSql, ExecuteSqlError } from 'data/sql/execute-sql-query'
import { authKeys } from './keys'

export type Filter = 'verified' | 'unverified' | 'anonymous'

export type UsersVariables = {
  projectRef?: string
  connectionString?: string
  page?: number
  keywords?: string
  filter?: Filter
  providers?: string[]
  sort?: 'created_at' | 'email' | 'phone'
  order?: 'asc' | 'desc'
}

export const USERS_PAGE_LIMIT = 50
export type User = components['schemas']['UserBody']

const getUsersSQL = ({
  page = 0,
  verified,
  keywords,
  providers,
  sort,
  order,
}: {
  page: number
  verified?: Filter
  keywords?: string
  providers?: string[]
  sort: string
  order: string
}) => {
  const offset = page * USERS_PAGE_LIMIT
  const hasValidKeywords = keywords && keywords !== ''

  const conditions: string[] = []
  const baseQueryUsers = `select * from auth.users`

  if (hasValidKeywords) {
    conditions.push(
      `id::text ilike '%${keywords}%' or email ilike '%${keywords}%' or phone ilike '%${keywords}%`
    )
  }

  if (verified === 'verified') {
    conditions.push(`email_confirmed_at IS NOT NULL or phone_confirmed_at IS NOT NULL`)
  } else if (verified === 'anonymous') {
    conditions.push(`is_anonymous is true`)
  } else if (verified === 'unverified') {
    conditions.push(`email_confirmed_at IS NULL AND phone_confirmed_at IS NULL`)
  }

  if (providers && providers.length > 0) {
    conditions.push(
      `(raw_app_meta_data->>'providers')::jsonb ?| array[${providers.map((p) => `'${p}'`).join(', ')}]`
    )
  }

  const combinedConditions = conditions.map((x) => `(${x})`).join(' and ')
  const sortOn = sort ?? 'created_at'
  const sortOrder = order ?? 'desc'

  return `${baseQueryUsers}${conditions.length > 0 ? ` where ${combinedConditions}` : ''} order by "${sortOn}" ${sortOrder} limit ${USERS_PAGE_LIMIT} offset ${offset};`
}

export type UsersData = { result: User[] }
export type UsersError = ExecuteSqlError

export const useUsersInfiniteQuery = <TData = UsersData>(
  { projectRef, connectionString, keywords, filter, providers, sort, order }: UsersVariables,
  { enabled = true, ...options }: UseInfiniteQueryOptions<UsersData, UsersError, TData> = {}
) =>
  useInfiniteQuery<UsersData, UsersError, TData>(
    authKeys.usersInfinite(projectRef, { keywords, filter, providers, sort, order }),
    ({ signal, pageParam }) => {
      return executeSql(
        {
          projectRef,
          connectionString,
          sql: getUsersSQL({
            page: pageParam,
            verified: filter,
            keywords,
            providers,
            sort: sort ?? 'created_at',
            order: order ?? 'desc',
          }),
          queryKey: authKeys.usersInfinite(projectRef),
        },
        signal
      )
    },
    {
      staleTime: 0,
      enabled: enabled && typeof projectRef !== 'undefined',

      getNextPageParam(lastPage, pages) {
        const page = pages.length
        const hasNextPage = lastPage.result.length <= USERS_PAGE_LIMIT
        if (!hasNextPage) return undefined
        return page
      },
      ...options,
    }
  )