supabase/apps/studio/hooks/analytics/useDbQuery.tsx

import { type SafeSqlFragment } from '@supabase/pg-meta/src/pg-format'
import { useQuery } from '@tanstack/react-query'

import { DEFAULT_QUERY_PARAMS } from '@/components/interfaces/Reports/Reports.constants'
import {
  BaseReportParams,
  MetaQueryResponse,
  ReportQueryDb,
} from '@/components/interfaces/Reports/Reports.types'
import { useReadReplicasQuery } from '@/data/read-replicas/replicas-query'
import { executeSql } from '@/data/sql/execute-sql-query'
import { useSelectedProjectQuery } from '@/hooks/misc/useSelectedProject'
import { useDatabaseSelectorStateSnapshot } from '@/state/database-selector'

export interface DbQueryHook<T = any> {
  isLoading: boolean
  isRefetching: boolean
  error: string
  data: T[]
  params: BaseReportParams
  logData?: never
  runQuery: () => void
  setParams?: never
  changeQuery?: never
  resolvedSql: string
}

// [Joshen] Atm this is being used only in query performance
const useDbQuery = ({
  sql,
  params = DEFAULT_QUERY_PARAMS,
  where,
  orderBy,
}: {
  sql: ReportQueryDb['safeSql'] | SafeSqlFragment
  params?: BaseReportParams
  where?: string
  orderBy?: string
}): DbQueryHook => {
  const { data: project } = useSelectedProjectQuery()
  const state = useDatabaseSelectorStateSnapshot()

  const { data: databases } = useReadReplicasQuery({ projectRef: project?.ref })
  const connectionString = (databases || []).find(
    (db) => db.identifier === state.selectedDatabaseId
  )?.connectionString
  const identifier = state.selectedDatabaseId

  const resolvedSql = typeof sql === 'function' ? sql([]) : sql

  const {
    data,
    error: rqError,
    isPending,
    isRefetching,
    refetch,
  } = useQuery({
    queryKey: [
      'projects',
      project?.ref,
      'db',
      { ...params, sql: resolvedSql, identifier },
      where,
      orderBy,
    ],
    queryFn: ({ signal }) => {
      return executeSql(
        {
          projectRef: project?.ref,
          connectionString: connectionString || project?.connectionString,
          sql: resolvedSql,
        },
        signal
      ).then((res) => res.result) as Promise<MetaQueryResponse>
    },
    enabled: Boolean(resolvedSql),
    refetchOnWindowFocus: false,
    refetchOnReconnect: false,
  })

  const error = rqError || (typeof data === 'object' ? data?.error : '')
  return {
    error,
    data,
    isLoading: isPending,
    isRefetching,
    params,
    runQuery: refetch,
    resolvedSql,
  }
}

export default useDbQuery