SETUP_STORAGE_AND_RPC.sql

-- 1. Create Buckets
INSERT INTO storage.buckets (id, name, public) 
VALUES ('avatars', 'avatars', true)
ON CONFLICT (id) DO NOTHING;

INSERT INTO storage.buckets (id, name, public) 
VALUES ('item-images', 'item-images', true)
ON CONFLICT (id) DO NOTHING;

-- 2. Storage Policies for Avatars
-- Allow public read
CREATE POLICY "Avatar Public Read" ON storage.objects 
FOR SELECT USING ( bucket_id = 'avatars' );

-- Allow authenticated upload to their own folder (folder name must match user ID)
CREATE POLICY "Avatar User Upload" ON storage.objects 
FOR INSERT WITH CHECK (
  bucket_id = 'avatars' 
  AND auth.role() = 'authenticated'
  AND (storage.foldername(name))[1] = auth.uid()::text
);

-- Allow user to update/delete their own avatar
CREATE POLICY "Avatar User Update" ON storage.objects 
FOR UPDATE USING (
  bucket_id = 'avatars' 
  AND auth.role() = 'authenticated'
  AND (storage.foldername(name))[1] = auth.uid()::text
);

CREATE POLICY "Avatar User Delete" ON storage.objects 
FOR DELETE USING (
  bucket_id = 'avatars' 
  AND auth.role() = 'authenticated'
  AND (storage.foldername(name))[1] = auth.uid()::text
);

-- 3. Storage Policies for Item Images
-- Allow public read
CREATE POLICY "Item Images Public Read" ON storage.objects 
FOR SELECT USING ( bucket_id = 'item-images' );

-- Allow authenticated upload
CREATE POLICY "Item Images User Upload" ON storage.objects 
FOR INSERT WITH CHECK (
  bucket_id = 'item-images' 
  AND auth.role() = 'authenticated'
  -- We'll assume the client names files with user_id prefix for consistency, but less strict here
);

-- 4. Delete Account RPC
-- This function allows a user to delete their own account from auth.users
-- It requires SECURITY DEFINER to access auth.users
CREATE OR REPLACE FUNCTION delete_own_account()
RETURNS void AS $$
BEGIN
  -- Delete the user (Cascade should handle profile/items if configured, else we delete manually)
  DELETE FROM public.profiles WHERE id = auth.uid();
  DELETE FROM auth.users WHERE id = auth.uid();
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;