-- Profile avatars
--
-- Adds an `avatar_url` column to `profiles` and provisions a public Supabase
-- Storage bucket named `avatars` that each user can write to under their own
-- user-id prefix (e.g. `avatars/<uid>/<filename>`).
--
-- Why "public" bucket?
--   - Profile photos need to be visible to the stranger on the other side of
--     the call who does NOT have a session on this site (and may not even
--     be signed in). A public read bucket lets us embed the CDN URL directly
--     in the video-chat UI without generating signed URLs every render.
--   - Write access is still tightly restricted: only the authenticated owner
--     of the object prefix can upload or delete.
-- ---------------------------------------------------------------------------

-- 1. Schema change -----------------------------------------------------------
alter table public.profiles
  add column if not exists avatar_url text;

-- 2. Storage bucket ----------------------------------------------------------
insert into storage.buckets (id, name, public)
values ('avatars', 'avatars', true)
on conflict (id) do update set public = excluded.public;

-- 3. Storage RLS policies ----------------------------------------------------
-- These policies sit on the `storage.objects` table (Supabase built-in).
-- Path convention: `avatars/<user_id>/<filename>`. We extract <user_id> via
-- storage.foldername() and compare against the caller's auth.uid().

-- SELECT: anyone (including anon) can read avatar files. This is what makes
-- the photo visible to strangers during a call without any auth round-trip.
drop policy if exists "avatars_public_read" on storage.objects;
create policy "avatars_public_read"
  on storage.objects for select
  using (bucket_id = 'avatars');

-- INSERT: a signed-in user may upload ONLY into their own folder.
drop policy if exists "avatars_insert_own" on storage.objects;
create policy "avatars_insert_own"
  on storage.objects for insert
  with check (
    bucket_id = 'avatars'
    and auth.uid() is not null
    and auth.uid()::text = (storage.foldername(name))[1]
  );

-- UPDATE: same rule (re-uploading the same path, changing metadata, etc).
drop policy if exists "avatars_update_own" on storage.objects;
create policy "avatars_update_own"
  on storage.objects for update
  using (
    bucket_id = 'avatars'
    and auth.uid() is not null
    and auth.uid()::text = (storage.foldername(name))[1]
  )
  with check (
    bucket_id = 'avatars'
    and auth.uid() is not null
    and auth.uid()::text = (storage.foldername(name))[1]
  );

-- DELETE: a signed-in user may delete ONLY their own files.
drop policy if exists "avatars_delete_own" on storage.objects;
create policy "avatars_delete_own"
  on storage.objects for delete
  using (
    bucket_id = 'avatars'
    and auth.uid() is not null
    and auth.uid()::text = (storage.foldername(name))[1]
  );