-- Make saved, unfinished applications visible to authorized administrators.
-- This migration is independent of admin login/MFA and temporary identity KYC.
begin;
alter table marketplace_private.tokenization_requests add column if not exists wizard_step smallint check(wizard_step between 0 and 3);

create or replace function marketplace_private.tokenization_progress(p_id uuid) returns jsonb
language plpgsql stable security definer set search_path='' as $$
declare r marketplace_private.tokenization_requests;definition marketplace_private.tokenization_asset_types;
 total_fields integer;filled_fields integer;required_fields integer;completed_fields integer;uploaded integer;required_docs integer;uploaded_required integer;current_step integer;
begin
 select * into r from marketplace_private.tokenization_requests where id=p_id;
 if r.id is null then raise exception 'tokenization_not_found';end if;
 select * into definition from marketplace_private.tokenization_asset_types where id=r.asset_type;
 select count(*),count(*) filter(where length(trim(coalesce(r.fields->>(f->>'key'),'')))>0),
  count(*) filter(where coalesce((f->>'required')::boolean,false)),
  count(*) filter(where coalesce((f->>'required')::boolean,false) and length(trim(coalesce(r.fields->>(f->>'key'),'')))>0)
 into total_fields,filled_fields,required_fields,completed_fields from jsonb_array_elements(definition.fields) f;
 required_docs:=cardinality(definition.required_documents);
 select count(*),count(*) filter(where d.slot=any(definition.required_documents) and d.status in('uploaded','approved'))
 into uploaded,uploaded_required from marketplace_private.tokenization_documents d
 join storage.objects o on o.bucket_id='tokenization-documents' and o.name=d.path
 where d.request_id=r.id and d.upload_state='complete';
 -- Navigation metadata is not a review, submission, approval, or issuance signal.
 current_step:=case when r.status not in('draft','changes_requested') then case when r.tokenomics is not null then 4 else 3 end
  when r.wizard_step is not null then r.wizard_step when uploaded>0 then 2 when filled_fields>0 then 1 else 0 end;
 return jsonb_build_object('filled_fields',filled_fields,'total_fields',total_fields,'required_fields',required_fields,
  'completed_required_fields',completed_fields,'uploaded_documents',uploaded,'required_documents',required_docs,
  'uploaded_required_documents',uploaded_required,'current_step',current_step);
end$$;
revoke all on function marketplace_private.tokenization_progress(uuid) from public,anon,authenticated,service_role;

create or replace function marketplace_private.tokenization_view(p_id uuid,p_admin boolean default false) returns jsonb language plpgsql stable security definer set search_path='' as $$
declare r marketplace_private.tokenization_requests;j marketplace_private.tokenization_issuance_jobs;reviews jsonb;n_layer text;n marketplace_private.tokenization_internal_notes;
begin
 select * into r from marketplace_private.tokenization_requests where id=p_id;
 if r.id is null then raise exception 'tokenization_not_found';end if;reviews:=r.reviews;
 if p_admin then
  foreach n_layer in array array['legal','financial','technical'] loop
   select * into n from marketplace_private.tokenization_internal_notes where request_id=r.id and tokenization_internal_notes.layer=n_layer order by created_at desc,id desc limit 1;
   if n.id is not null then reviews:=jsonb_set(reviews,array[n_layer],coalesce(reviews->n_layer,'{}')||jsonb_build_object('internal_note',n.note,'reviewer_id',n.actor));end if;
  end loop;
 end if;
 select * into j from marketplace_private.tokenization_issuance_jobs where request_id=r.id;
 return jsonb_build_object('id',r.id,'asset_type',r.asset_type,'fields',r.fields,'status',r.status,'version',r.version,
  'wizard_step',r.wizard_step,'progress',marketplace_private.tokenization_progress(r.id),
  'terms_version',r.terms_version,'consented_at',r.consented_at,'created_at',r.created_at,'updated_at',r.updated_at,'submitted_at',r.submitted_at,'accepted_at',r.accepted_at,
  'message',r.message,'reviews',reviews,'tokenomics',r.tokenomics,'proposal_version',r.proposal_version,'accepted_proposal_version',r.accepted_proposal_version,
  'documents',coalesce((select jsonb_agg(jsonb_build_object('id',d.id,'slot',d.slot,'name',d.name,'mime',d.mime,'size',d.size,'status',d.status,'message',d.message,'created_at',d.created_at) order by d.created_at) from marketplace_private.tokenization_documents d where request_id=r.id and upload_state='complete'),'[]'),
  'issuance',case when j.id is null then null when j.status='confirmed' then j.receipt||jsonb_build_object('status','confirmed') else jsonb_build_object('status',j.status,'message','در انتظار صدور واقعی و تأیید رسید شبکه؛ تخصیص به کیف پول داخلی پس از تأیید انجام می‌شود.') end
 )||case when p_admin then jsonb_build_object('user_id',r.user_id) else '{}'::jsonb end;
end$$;

-- Modify the original implementations behind existing access wrappers. Never
-- replace those wrappers, so suspension/closure checks remain effective.
do $$declare fn regprocedure;definition text;needle text;begin
 fn:=coalesce(to_regprocedure('marketplace_private.access_base_marketplace_tokenization_state(boolean)'),to_regprocedure('public.marketplace_tokenization_state(boolean)'));
 if fn is null then raise exception 'tokenization_state_prerequisite_missing';end if;
 definition:=pg_get_functiondef(fn);
 needle:=$n$(p_admin and status<>'draft')$n$;
 if position(needle in definition)>0 then definition:=replace(definition,needle,'p_admin');execute definition;
 elsif position('where p_admin or' in definition)=0 then raise exception 'tokenization_state_definition_changed';end if;

 fn:=coalesce(to_regprocedure('marketplace_private.access_base_marketplace_tokenization_customer(text,jsonb)'),to_regprocedure('public.marketplace_tokenization_customer(text,jsonb)'));
 if fn is null then raise exception 'tokenization_customer_prerequisite_missing';end if;
 definition:=pg_get_functiondef(fn);
 if position('wizard_step' in definition)=0 then
  needle:=$n$if p_action='start' then$n$;
  if position(needle in definition)=0 then raise exception 'tokenization_customer_definition_changed';end if;
  definition:=replace(definition,needle,$n$if p_action in('start','save') and p_payload ? 'wizard_step' and (jsonb_typeof(p_payload->'wizard_step') is distinct from 'number' or (p_payload->>'wizard_step')!~'^[0-3]$') then raise exception 'tokenization_invalid';end if;
 if p_action='start' then$n$);
  needle:=$n$insert into marketplace_private.tokenization_requests(user_id,asset_type,terms_version) values(uid,typ,'2026-09-24-v1')$n$;
  if position(needle in definition)=0 then raise exception 'tokenization_start_definition_changed';end if;
  definition:=replace(definition,needle,$n$insert into marketplace_private.tokenization_requests(user_id,asset_type,terms_version,wizard_step) values(uid,typ,'2026-09-24-v1',coalesce((p_payload->>'wizard_step')::smallint,0))$n$);
  needle:=$n$set asset_type=typ,fields=p_payload->'fields',tokenomics=null,version=version+1$n$;
  if position(needle in definition)=0 then raise exception 'tokenization_save_definition_changed';end if;
  definition:=replace(definition,needle,$n$set asset_type=typ,fields=p_payload->'fields',wizard_step=coalesce((p_payload->>'wizard_step')::smallint,wizard_step),tokenomics=null,version=version+1$n$);
  execute definition;
 end if;
end$$;

-- Record saved navigation changes in the same retained customer activity stream.
do $$declare definition text;begin
 definition:=pg_get_functiondef('marketplace_private.customer_activity_safe(text,jsonb)'::regprocedure);
 if position($n$'asset_type','fields','wizard_step','status'$n$ in definition)=0 then
  if position($n$'asset_type','fields','status'$n$ in definition)=0 then raise exception 'tokenization_activity_definition_changed';end if;
  definition:=replace(definition,$n$'asset_type','fields','status'$n$,$n$'asset_type','fields','wizard_step','status'$n$);execute definition;
 end if;
end$$;

-- Preserve whichever profile implementation is installed (with or without
-- optional temporary KYC) and its existing guards/projections.
do $$declare f record;definition text;matched boolean:=false;begin
 for f in select p.oid from pg_proc p join pg_namespace n on n.oid=p.pronamespace
 where (n.nspname='public' and p.proname='marketplace_admin_customer_profile') or (n.nspname='marketplace_private' and p.proname='temporary_base_marketplace_admin_customer_profile') loop
  definition:=pg_get_functiondef(f.oid);
  if position($n$'identity_requests',coalesce$n$ in definition)>0 and position($n$'tokenization_requests',coalesce$n$ in definition)=0 then
   definition:=replace(definition,$n$'identity_requests',coalesce$n$,$n$'tokenization_requests',coalesce((select jsonb_agg(marketplace_private.tokenization_view(t.id,true) order by t.updated_at desc,t.id desc) from marketplace_private.tokenization_requests t where t.user_id=p_user),'[]'),
 'identity_requests',coalesce$n$);execute definition;
  end if;
  if position($n$'tokenization_requests',coalesce$n$ in definition)>0 then matched:=true;end if;
 end loop;
 if not matched then raise exception 'tokenization_profile_definition_changed';end if;
end$$;

-- Drafts are visible as drafts in the latest feed, not counted as reviewable
-- submissions. Optional dashboard installation is not required for this repair.
do $$declare fn regprocedure;definition text;needle text;begin
 fn:=to_regprocedure('public.marketplace_admin_control_dashboard()');
 if fn is not null then
  definition:=pg_get_functiondef(fn);
  needle:=$n$status,coalesce(submitted_at,created_at) from marketplace_private.tokenization_requests where status<>'draft' order by coalesce(submitted_at,created_at) desc,id desc limit 5$n$;
  if position(needle in definition)>0 then
   definition:=replace(definition,needle,$n$status,case when status='draft' then updated_at else coalesce(submitted_at,created_at) end from marketplace_private.tokenization_requests order by case when status='draft' then updated_at else coalesce(submitted_at,created_at) end desc,id desc limit 5$n$);execute definition;
  end if;
 end if;
end$$;
notify pgrst,'reload schema';
commit;