supabase/e2e/studio/utils/db/queries.ts

import { query } from './client.js'

/**
 * Check if a table exists in the specified schema.
 *
 * @param schema - The schema name (e.g., 'public')
 * @param tableName - The table name to check
 * @returns true if the table exists, false otherwise
 */
export async function tableExists(schema: string, tableName: string): Promise<boolean> {
  const result = await query<{ exists: boolean }>(
    `SELECT EXISTS (
       SELECT 1 FROM information_schema.tables
       WHERE table_schema = $1 AND table_name = $2
     ) as exists`,
    [schema, tableName]
  )
  return result[0]?.exists ?? false
}

/**
 * Create a table with a single text column and optionally insert initial rows.
 *
 * @param tableName - The table name to create
 * @param columnName - The name of the text column
 * @param initialData - Optional array of rows to insert (each row is a key-value map)
 */
export async function createTable(
  tableName: string,
  columnName: string,
  initialData?: Array<Record<string, string>>
) {
  await query(
    `CREATE TABLE IF NOT EXISTS ${tableName} (
      id bigint generated by default as identity primary key,
      created_at timestamp with time zone null default now(),
      ${columnName} text
    )`
  )

  if (initialData && initialData.length > 0) {
    const placeholders = initialData.map((_, i) => `($${i + 1})`).join(', ')
    const values = initialData.map((row) => row[columnName])
    await query(`INSERT INTO ${tableName} (${columnName}) VALUES ${placeholders}`, values)
  }
}

/**
 * Create a table with a single text column with RLS enabled (as if created through UI) and optionally insert initial rows.
 *
 * @param tableName - The table name to create
 * @param columnName - The name of the text column
 * @param initialData - Optional array of rows to insert (each row is a key-value map)
 */
export async function createTableWithRLS(
  tableName: string,
  columnName: string,
  initialData?: Array<Record<string, string>>
) {
  await createTable(tableName, columnName, initialData)
  await query(`alter table public.${tableName} enable row level security;`)
}

/**
 * Drop a table if it exists.
 *
 * @param tableName - The table name to drop
 */
export async function dropTable(tableName: string) {
  await query(`DROP TABLE IF EXISTS ${tableName} CASCADE`)
}

/**
 * Create a view in the public schema. Assumes the underlying table already exists.
 *
 * @param viewName - The view name to create
 * @param selectSql - The SELECT statement that defines the view (without trailing semicolon)
 */
export async function createView(viewName: string, selectSql: string) {
  await query(`CREATE OR REPLACE VIEW public.${viewName} AS ${selectSql}`)
}

/**
 * Drop a view if it exists.
 *
 * @param viewName - The view name to drop
 */
export async function dropView(viewName: string) {
  await query(`DROP VIEW IF EXISTS public.${viewName} CASCADE`)
}

/**
 * Create a materialized view in the public schema.
 *
 * @param viewName - The materialized view name to create
 * @param selectSql - The SELECT statement that defines the view (without trailing semicolon)
 */
export async function createMaterializedView(viewName: string, selectSql: string) {
  await query(`CREATE MATERIALIZED VIEW IF NOT EXISTS public.${viewName} AS ${selectSql}`)
}

/**
 * Drop a materialized view if it exists.
 *
 * @param viewName - The materialized view name to drop
 */
export async function dropMaterializedView(viewName: string) {
  await query(`DROP MATERIALIZED VIEW IF EXISTS public.${viewName} CASCADE`)
}