-- Private customer photos; no custody or wallet provisioning is enabled by this migration.
begin;
alter table marketplace_private.accounts add column if not exists avatar_path text;
alter table marketplace_private.accounts drop constraint if exists account_avatar_own_path;
alter table marketplace_private.accounts add constraint account_avatar_own_path check (
 avatar_path is null or (
 split_part(avatar_path,'/',1)=user_id::text and
 avatar_path ~ '^[0-9a-f-]{36}/[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}\.(jpg|png|webp)$'
 ));

insert into storage.buckets(id,name,public,file_size_limit,allowed_mime_types)
 values('customer-avatars','customer-avatars',false,2097152,array['image/jpeg','image/png','image/webp'])
 on conflict(id) do update set public=false,file_size_limit=excluded.file_size_limit,allowed_mime_types=excluded.allowed_mime_types;

drop policy if exists customer_avatar_read on storage.objects;
create policy customer_avatar_read on storage.objects for select to authenticated using (
 bucket_id='customer-avatars' and split_part(name,'/',1)=(select auth.uid())::text);
drop policy if exists customer_avatar_insert on storage.objects;
create policy customer_avatar_insert on storage.objects for insert to authenticated with check (
 bucket_id='customer-avatars' and split_part(name,'/',1)=(select auth.uid())::text and
 name ~ '^[0-9a-f-]{36}/[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}\.(jpg|png|webp)$');
drop policy if exists customer_avatar_delete on storage.objects;
create policy customer_avatar_delete on storage.objects for delete to authenticated using (
 bucket_id='customer-avatars' and split_part(name,'/',1)=(select auth.uid())::text);

create or replace function public.marketplace_avatar() returns jsonb
language plpgsql security definer set search_path='' as $$
declare uid uuid:=auth.uid(); photo text;
begin
 if uid is null then raise exception 'authentication_required'; end if;
 select avatar_path into photo from marketplace_private.accounts where user_id=uid;
 return jsonb_build_object('path',photo);
end $$;

create or replace function public.marketplace_avatar_set(new_path text) returns jsonb
language plpgsql security definer set search_path='' as $$
declare uid uuid:=auth.uid(); previous text;
begin
 if uid is null then raise exception 'authentication_required'; end if;
 if new_path is null or split_part(new_path,'/',1)<>uid::text or
   new_path !~ '^[0-9a-f-]{36}/[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}\.(jpg|png|webp)$'
 then raise exception 'invalid_avatar_path'; end if;
 if not exists(select 1 from storage.objects where bucket_id='customer-avatars' and name=new_path)
 then raise exception 'avatar_not_uploaded'; end if;
 perform pg_advisory_xact_lock(hashtextextended('avatar:'||uid::text,0));
 select avatar_path into previous from marketplace_private.accounts where user_id=uid for update;
 insert into marketplace_private.accounts(user_id,first_name,last_name,avatar_path)
 select uid,left(coalesce(raw_user_meta_data->>'first_name',''),80),left(coalesce(raw_user_meta_data->>'last_name',''),80),new_path
 from auth.users where id=uid
 on conflict(user_id) do update set avatar_path=excluded.avatar_path,updated_at=now();
 insert into marketplace_private.dashboard_audit(user_id,action) values(uid,'avatar_updated');
 return jsonb_build_object('path',new_path,'previous_path',previous);
end $$;

revoke all on function public.marketplace_avatar() from public,anon;
revoke all on function public.marketplace_avatar_set(text) from public,anon;
grant execute on function public.marketplace_avatar() to authenticated;
grant execute on function public.marketplace_avatar_set(text) to authenticated;
notify pgrst,'reload schema';
commit;