173 lines
15 KiB
PL/PgSQL
173 lines
15 KiB
PL/PgSQL
-- Contributions reuse the private upload pipeline. Only root maps can be published.
|
||
alter table public.datasets
|
||
add column parent_dataset_id uuid references public.datasets(id),
|
||
add column allow_contributions boolean not null default false,
|
||
add column contribution_status text check (contribution_status in ('pending','submitted','accepted','rejected')),
|
||
add column contribution_count integer not null default 0 check (contribution_count between 0 and 24),
|
||
add column update_state text check (update_state in ('draft','review','rejected'));
|
||
create index datasets_contribution_parent on public.datasets(parent_dataset_id,owner_id) where parent_dataset_id is not null;
|
||
|
||
create function public.contribution_capacity(p_dataset uuid,p_owner uuid) returns jsonb
|
||
language plpgsql security invoker set search_path='' as $$
|
||
declare d public.datasets; m jsonb; reserved integer; used integer; pool integer;
|
||
begin
|
||
select * into d from public.datasets where id=p_dataset for update;
|
||
if not found or d.parent_dataset_id is not null or d.state not in ('draft','rejected','published') then raise exception 'Map does not accept contributions at this time'; end if;
|
||
if d.owner_id<>p_owner and not d.allow_contributions then raise exception 'The owner has disabled player contributions'; end if;
|
||
select manifest into m from public.dataset_inputs where dataset_id=p_dataset;
|
||
pool:=jsonb_array_length(coalesce(m->'availableViewpoints',m->'viewpoints'));
|
||
select coalesce(sum(contribution_count) filter(where contribution_status in ('pending','submitted') and state<>'failed'),0),
|
||
coalesce(sum(contribution_count) filter(where owner_id=p_owner and (contribution_status='accepted' or (contribution_status in ('pending','submitted') and state<>'failed'))),0)
|
||
into reserved,used from public.datasets where parent_dataset_id=p_dataset;
|
||
return jsonb_build_object('id',d.id,'name',d.name,'bounds',d.bounds,'remaining',greatest(0,24-pool-reserved),
|
||
'playerRemaining',greatest(0,case when d.owner_id=p_owner then 24 else 12 end-used),'isOwner',d.owner_id=p_owner);
|
||
end; $$;
|
||
|
||
create function public.create_upload(p_owner uuid,p_manifest jsonb) returns uuid
|
||
language plpgsql security invoker set search_path='' as $$
|
||
declare target uuid; capacity jsonb; server uuid; result uuid; count integer; b jsonb;
|
||
begin
|
||
target:=(p_manifest->>'contributionTo')::uuid;
|
||
count:=jsonb_array_length(p_manifest->'viewpoints');
|
||
if count<1 or count>24 or (target is null and count<5) then raise exception 'Maps require 5–24 panoramas'; end if;
|
||
if exists(select 1 from jsonb_array_elements(p_manifest->'viewpoints') p where p ?| array['assetRoot','assetId','contributorId','source']) or p_manifest ? 'availableViewpoints' then raise exception 'Upload contains server-only media provenance'; end if;
|
||
if target is not null then
|
||
capacity:=public.contribution_capacity(target,p_owner); b:=capacity->'bounds';
|
||
if count>(capacity->>'remaining')::integer then raise exception 'Map limit is 24 panoramas, including pending uploads'; end if;
|
||
if count>(capacity->>'playerRemaining')::integer then raise exception 'Each player may contribute at most 12 panoramas per map'; end if;
|
||
if exists(select 1 from jsonb_array_elements(p_manifest->'viewpoints') p where
|
||
(p->>'x')::numeric<(b->>'minX')::numeric or (p->>'x')::numeric>(b->>'maxX')::numeric or
|
||
(p->>'z')::numeric<(b->>'minZ')::numeric or (p->>'z')::numeric>(b->>'maxZ')::numeric) then raise exception 'Contributed panoramas must be inside the original map bounds'; end if;
|
||
select server_id into server from public.datasets where id=target;
|
||
else
|
||
select id into server from public.catalog_servers where name=p_manifest->>'serverName' limit 1;
|
||
if server is null then insert into public.catalog_servers(name,address) values(p_manifest->>'serverName',p_manifest->>'serverAddress') returning id into server; end if;
|
||
end if;
|
||
insert into public.datasets(owner_id,server_id,name,description,source,bounds,parent_dataset_id,contribution_status,contribution_count)
|
||
values(p_owner,server,p_manifest->>'name',coalesce(p_manifest->>'description',''),p_manifest->>'source',p_manifest->'bounds',target,
|
||
case when target is not null then 'pending' end,case when target is not null then count else 0 end) returning id into result;
|
||
insert into public.dataset_inputs(dataset_id,manifest) values(result,p_manifest);
|
||
return result;
|
||
end; $$;
|
||
|
||
create or replace function public.complete_job(p_job uuid,p_worker text,p_count integer,p_cover text) returns void
|
||
language plpgsql security invoker set search_path='' as $$
|
||
declare j public.upload_jobs; d public.datasets;
|
||
begin
|
||
select * into j from public.upload_jobs where id=p_job and lease_owner=p_worker and state='running' and lease_until>now() for update;
|
||
if not found then raise exception 'Worker lease lost'; end if;
|
||
select * into d from public.datasets where id=j.dataset_id for update;
|
||
if p_count<(case when d.parent_dataset_id is null then 5 else 1 end) or p_count>24 then raise exception 'Invalid panorama count'; end if;
|
||
update public.datasets set state='draft',panorama_count=p_count,map_path=j.dataset_id::text||'/map.webp',cover_path=j.dataset_id::text||'/'||p_cover||'/preview.webp',updated_at=now() where id=j.dataset_id and state='processing';
|
||
if not found then raise exception 'Dataset processing cancelled'; end if;
|
||
update public.dataset_inputs set validated=true where dataset_id=j.dataset_id;
|
||
update public.upload_jobs set state='complete',progress=100,lease_until=null,error=null,updated_at=now() where id=p_job;
|
||
end; $$;
|
||
|
||
-- Lock before selecting locations, so acceptance cannot race with owner edits.
|
||
create function public.save_draft(p_dataset uuid,p_owner uuid,p_name text,p_description text,p_ids jsonb) returns void
|
||
language plpgsql security invoker set search_path='' as $$
|
||
declare d public.datasets; m jsonb; available jsonb; selected jsonb; count integer; first jsonb;
|
||
begin
|
||
select * into d from public.datasets where id=p_dataset and owner_id=p_owner for update;
|
||
if not found then raise exception 'Dataset not found'; end if;
|
||
if d.state not in ('draft','rejected','published') or d.update_state='review' or d.contribution_status in ('submitted','accepted','rejected') then raise exception 'Draft cannot be edited while awaiting approval'; end if;
|
||
if length(p_name)<3 or length(p_name)>80 or length(p_description)>2000 then raise exception 'Invalid map details'; end if;
|
||
select manifest into m from public.dataset_inputs where dataset_id=p_dataset and validated;
|
||
if m is null then raise exception 'Dataset not validated'; end if;
|
||
available:=coalesce(m->'availableViewpoints',m->'viewpoints');
|
||
select coalesce(jsonb_agg(p order by n),'[]') into selected from jsonb_array_elements(available) with ordinality as pts(p,n) where p_ids ? (p->>'id');
|
||
count:=jsonb_array_length(selected);
|
||
if count<>jsonb_array_length(p_ids) or count<(case when d.parent_dataset_id is null then 5 else 1 end) or count>24 then raise exception 'Choose unique captured locations within the panorama limit'; end if;
|
||
m:=m||jsonb_build_object('name',p_name,'description',p_description,'availableViewpoints',available,'viewpoints',selected);
|
||
update public.dataset_inputs set manifest=m where dataset_id=p_dataset;
|
||
first:=selected->0;
|
||
update public.datasets set name=case when state='published' then name else p_name end,
|
||
description=case when state='published' then description else p_description end,
|
||
panorama_count=case when state='published' then panorama_count else count end,
|
||
cover_path=case when state='published' then cover_path else coalesce(first->>'assetRoot',id::text)||'/'||coalesce(first->>'assetId',first->>'id')||'/preview.webp' end,
|
||
state=case when state='published' then state else 'draft' end,
|
||
update_state=case when state='published' then 'draft' else null end,rejection_reason=null,updated_at=now() where id=p_dataset;
|
||
end; $$;
|
||
|
||
create function public.submit_draft(p_dataset uuid,p_owner uuid) returns void
|
||
language plpgsql security invoker set search_path='' as $$
|
||
declare d public.datasets; count integer;
|
||
begin
|
||
select * into d from public.datasets where id=p_dataset and owner_id=p_owner for update;
|
||
if not found then raise exception 'Dataset not found'; end if;
|
||
if d.state not in ('draft','rejected','published') or (d.state='published' and coalesce(d.update_state,'') not in ('draft','rejected')) or d.contribution_status in ('submitted','accepted','rejected') then raise exception 'Draft is not ready for submission'; end if;
|
||
select jsonb_array_length(manifest->'viewpoints') into count from public.dataset_inputs where dataset_id=p_dataset and validated;
|
||
if count is null or count<(case when d.parent_dataset_id is null then 5 else 1 end) or count>24 then raise exception 'Invalid panorama count'; end if;
|
||
update public.datasets set state=case when state='published' then state else 'review' end,
|
||
update_state=case when state='published' then 'review' end,
|
||
contribution_status=case when parent_dataset_id is not null then 'submitted' end,updated_at=now() where id=p_dataset;
|
||
end; $$;
|
||
|
||
create function public.set_contributions(p_dataset uuid,p_owner uuid,p_allowed boolean) returns void
|
||
language plpgsql security invoker set search_path='' as $$
|
||
begin
|
||
perform 1 from public.datasets where id=p_dataset and owner_id=p_owner and parent_dataset_id is null for update;
|
||
if not found then raise exception 'Map owner permission required'; end if;
|
||
update public.datasets set allow_contributions=p_allowed,updated_at=now() where id=p_dataset;
|
||
end; $$;
|
||
|
||
create function public.review_contribution(p_dataset uuid,p_contribution uuid,p_owner uuid,p_decision text,p_reason text default null) returns void
|
||
language plpgsql security invoker set search_path='' as $$
|
||
declare d public.datasets; c public.datasets; m jsonb; cm jsonb; added jsonb; pool jsonb; chosen jsonb;
|
||
begin
|
||
select * into d from public.datasets where id=p_dataset and owner_id=p_owner and parent_dataset_id is null for update;
|
||
if not found then raise exception 'Map owner permission required'; end if;
|
||
if d.state not in ('draft','rejected','published') or d.update_state='review' then raise exception 'Wait until the current map review finishes'; end if;
|
||
select * into c from public.datasets where id=p_contribution and parent_dataset_id=p_dataset for update;
|
||
if not found or c.contribution_status<>'submitted' or c.state<>'review' then raise exception 'Contribution is not awaiting owner approval'; end if;
|
||
if p_decision='reject' then
|
||
if coalesce(length(trim(p_reason)),0)=0 then raise exception 'Provide feedback for the contributor'; end if;
|
||
update public.datasets set state='rejected',contribution_status='rejected',rejection_reason=p_reason,updated_at=now() where id=c.id;
|
||
return;
|
||
end if;
|
||
if p_decision<>'accept' then raise exception 'Invalid contribution decision'; end if;
|
||
select manifest into cm from public.dataset_inputs where dataset_id=c.id and validated;
|
||
select manifest into m from public.dataset_inputs where dataset_id=d.id and validated;
|
||
if cm is null or m is null then raise exception 'Dataset not validated'; end if;
|
||
select jsonb_agg(p||jsonb_build_object('id','c'||replace(c.id::text,'-','')||'_'||n,'assetRoot',c.id,'assetId',p->>'id','contributorId',c.owner_id,'source',c.source) order by n)
|
||
into added from jsonb_array_elements(cm->'viewpoints') with ordinality as pts(p,n);
|
||
pool:=coalesce(m->'availableViewpoints',m->'viewpoints')||added;
|
||
if jsonb_array_length(pool)>24 then raise exception 'Map limit is 24 panoramas'; end if;
|
||
if c.owner_id<>d.owner_id and (select coalesce(sum(contribution_count),0) from public.datasets where parent_dataset_id=d.id and owner_id=c.owner_id and contribution_status='accepted')+jsonb_array_length(added)>12 then raise exception 'Each player may contribute at most 12 panoramas per map'; end if;
|
||
chosen:=(m->'viewpoints')||added;
|
||
update public.dataset_inputs set manifest=m||jsonb_build_object('availableViewpoints',pool,'viewpoints',chosen) where dataset_id=d.id;
|
||
update public.datasets set state=case when state='published' then state else 'draft' end,
|
||
update_state=case when state='published' then 'draft' end,
|
||
panorama_count=case when state='published' then panorama_count else jsonb_array_length(chosen) end,rejection_reason=null,updated_at=now() where id=d.id;
|
||
update public.datasets set contribution_status='accepted',contribution_count=jsonb_array_length(added),updated_at=now() where id=c.id;
|
||
end; $$;
|
||
|
||
create or replace function public.publish_dataset(p_dataset uuid,p_reviewer uuid,p_decision text,p_reason text default null) returns void
|
||
language plpgsql security invoker set search_path='' as $$
|
||
declare d public.datasets; m jsonb; rev uuid; first jsonb; count integer;
|
||
begin
|
||
if not exists(select 1 from public.profiles where id=p_reviewer and role='moderator') then raise exception 'Moderator permission required'; end if;
|
||
select * into d from public.datasets where id=p_dataset and parent_dataset_id is null for update;
|
||
if not found then raise exception 'Map not found'; end if;
|
||
if p_decision='approve' then
|
||
if not(d.state='review' or (d.state='published' and coalesce(d.update_state,'')='review')) then raise exception 'Map is not ready for publication'; end if;
|
||
select manifest into m from public.dataset_inputs where dataset_id=p_dataset and validated;
|
||
count:=jsonb_array_length(m->'viewpoints'); first:=m->'viewpoints'->0;
|
||
if m is null or count<5 or count>24 then raise exception 'Map requires 5–24 validated panoramas'; end if;
|
||
insert into public.revisions(dataset_id,manifest,asset_root) values(p_dataset,m,p_dataset::text) returning id into rev;
|
||
update public.datasets set state='published',revision_id=rev,update_state=null,name=m->>'name',description=m->>'description',panorama_count=count,
|
||
cover_path=coalesce(first->>'assetRoot',id::text)||'/'||coalesce(first->>'assetId',first->>'id')||'/preview.webp',rejection_reason=null,updated_at=now() where id=p_dataset;
|
||
elsif p_decision='reject' and (d.state='review' or (d.state='published' and d.update_state='review')) then
|
||
if coalesce(length(trim(p_reason)),0)=0 then raise exception 'Provide a rejection reason'; end if;
|
||
update public.datasets set state=case when state='published' then state else 'rejected' end,update_state=case when state='published' then 'rejected' end,rejection_reason=p_reason,updated_at=now() where id=p_dataset;
|
||
elsif p_decision='unpublish' and d.state='published' then
|
||
update public.datasets set state='unpublished',update_state=null,updated_at=now() where id=p_dataset;
|
||
else raise exception 'Invalid review transition'; end if;
|
||
insert into public.review_decisions(dataset_id,reviewer_id,decision,reason) values(p_dataset,p_reviewer,p_decision,p_reason);
|
||
end; $$;
|
||
|
||
revoke execute on function public.contribution_capacity(uuid,uuid),public.create_upload(uuid,jsonb),public.save_draft(uuid,uuid,text,text,jsonb),public.submit_draft(uuid,uuid),public.set_contributions(uuid,uuid,boolean),public.review_contribution(uuid,uuid,uuid,text,text) from public,anon,authenticated;
|
||
grant execute on function public.contribution_capacity(uuid,uuid),public.create_upload(uuid,jsonb),public.save_draft(uuid,uuid,text,text,jsonb),public.submit_draft(uuid,uuid),public.set_contributions(uuid,uuid,boolean),public.review_contribution(uuid,uuid,uuid,text,text) to service_role;
|
||
|