20250506000001_account_functions.sql

-- Add account management functions

-- Function to update an account
CREATE OR REPLACE FUNCTION update_account(
  name TEXT,
  account_id UUID
) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER
AS $$
BEGIN
  UPDATE public.accounts
  SET 
    name = update_account.name,
    updated_at = now()
  WHERE id = update_account.account_id;
END;
$$;

-- Function to get all accounts for current user
CREATE OR REPLACE FUNCTION get_accounts()
RETURNS json
LANGUAGE plpgsql SECURITY DEFINER
AS $$
DECLARE
  current_user_id uuid;
  account_data json;
BEGIN
  -- Get the current user's ID
  current_user_id := auth.uid();
  
  -- Query for accounts
  SELECT json_agg(
    json_build_object(
      'id', a.id,
      'name', a.name,
      'slug', a.slug,
      'personal_account', a.id = current_user_id
    )
  ) INTO account_data
  FROM public.accounts a
  WHERE a.id = current_user_id;
  
  -- Return empty array if no results
  IF account_data IS NULL THEN
    RETURN '[]'::json;
  END IF;
  
  RETURN account_data;
END;
$$;