20250506000000_initial_setup.sql

-- Create required schemas
CREATE SCHEMA IF NOT EXISTS auth;
CREATE SCHEMA IF NOT EXISTS storage;
CREATE SCHEMA IF NOT EXISTS basejump;

-- Create basic roles
CREATE ROLE IF NOT EXISTS anon NOLOGIN;
GRANT USAGE ON SCHEMA public TO anon;
GRANT USAGE ON SCHEMA auth TO anon;
GRANT USAGE ON SCHEMA basejump TO anon;

-- Create a basic users table if it doesn't exist
CREATE TABLE IF NOT EXISTS auth.users (
  id uuid PRIMARY KEY,
  email text UNIQUE,
  encrypted_password text,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now()
);

-- Add Basejump configuration
CREATE TABLE IF NOT EXISTS basejump.config (
  enable_team_accounts boolean DEFAULT true,
  enable_personal_account_billing boolean DEFAULT true,
  enable_team_account_billing boolean DEFAULT true
);

-- Insert default config if table is empty
INSERT INTO basejump.config (enable_team_accounts, enable_personal_account_billing, enable_team_account_billing)
SELECT true, true, true
WHERE NOT EXISTS (SELECT 1 FROM basejump.config);

-- Create accounts table for Suna
CREATE TABLE IF NOT EXISTS public.accounts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  slug text UNIQUE NOT NULL,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- Create projects table for Suna
CREATE TABLE IF NOT EXISTS public.projects (
  project_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  description text,
  account_id uuid REFERENCES public.accounts(id) ON DELETE CASCADE,
  sandbox jsonb DEFAULT NULL,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- Create a function to create accounts
CREATE OR REPLACE FUNCTION create_account(
  name TEXT,
  slug TEXT
) RETURNS json
LANGUAGE plpgsql SECURITY DEFINER
AS $$
DECLARE
  account_id uuid;
  existing_account_id uuid;
  return_data json;
BEGIN
  -- Check if slug is already taken
  SELECT id INTO existing_account_id FROM public.accounts WHERE accounts.slug = create_account.slug;
  
  IF existing_account_id IS NOT NULL THEN
    RETURN json_build_object('error', 'Slug already taken');
  END IF;

  -- Insert account
  INSERT INTO public.accounts (name, slug)
  VALUES (create_account.name, create_account.slug)
  RETURNING id INTO account_id;
  
  return_data := json_build_object(
    'id', account_id,
    'name', name,
    'slug', slug
  );
  
  RETURN return_data;
END;
$$;