-- Additive asset application workflow. No mint, receipt or balance is fabricated.
begin;
create table if not exists marketplace_private.tokenization_asset_types(
 id text primary key, fields jsonb not null, document_slots text[] not null, required_documents text[] not null
);
create table if not exists marketplace_private.tokenization_requests(
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id),
 asset_type text not null references marketplace_private.tokenization_asset_types(id),fields jsonb not null default '{}',
 status text not null default 'draft' check(status in('draft','submitted','under_review','changes_requested','awaiting_acceptance','issuance_pending','issued','rejected')),
 version integer not null default 1,terms_version text not null,consented_at timestamptz not null default now(),
 submitted_at timestamptz,accepted_at timestamptz,created_at timestamptz not null default now(),updated_at timestamptz not null default now(),
 reviews jsonb not null default '{"legal":{"status":"pending","message":""},"financial":{"status":"pending","message":""},"technical":{"status":"pending","message":""}}',
 message text not null default '',tokenomics jsonb,proposal_version integer not null default 0,accepted_proposal_version integer,
 accepted_tokenomics jsonb,accepted_fields jsonb,accepted_documents jsonb
);
create index if not exists tokenization_owner_list on marketplace_private.tokenization_requests(user_id,created_at desc);
create index if not exists tokenization_admin_queue on marketplace_private.tokenization_requests(status,updated_at desc);
create table if not exists marketplace_private.tokenization_documents(
 id uuid primary key default gen_random_uuid(),request_id uuid not null references marketplace_private.tokenization_requests(id),
 slot text not null,name text not null check(length(name) between 1 and 180),mime text not null check(mime in('application/pdf','image/jpeg','image/png')),
 size integer not null check(size between 1 and 10485760),path text unique not null,base_version integer not null,
 upload_state text not null default 'pending' check(upload_state in('pending','complete','deleted')),
 status text not null default 'uploaded' check(status in('uploaded','approved','changes_requested','rejected')),
 message text not null default '',created_at timestamptz not null default now(),reviewed_at timestamptz
);
create unique index if not exists tokenization_document_slot on marketplace_private.tokenization_documents(request_id,slot) where upload_state='complete';
create table if not exists marketplace_private.tokenization_internal_notes(
 id uuid primary key default gen_random_uuid(),request_id uuid not null references marketplace_private.tokenization_requests(id),
 layer text not null,actor uuid not null references auth.users(id),note text not null check(length(note)<=4000),created_at timestamptz not null default now()
);
create table if not exists marketplace_private.tokenization_proposals(
 request_id uuid not null references marketplace_private.tokenization_requests(id),version integer not null,tokenomics jsonb not null,
 actor uuid not null references auth.users(id),created_at timestamptz not null default now(),primary key(request_id,version)
);
create table if not exists marketplace_private.tokenization_issuance_jobs(
 id uuid primary key default gen_random_uuid(),request_id uuid unique not null references marketplace_private.tokenization_requests(id),
 user_id uuid not null references auth.users(id),proposal_version integer not null,tokenomics jsonb not null,
 status text not null default 'pending' check(status in('pending','confirmed','failed')),
 expected_chain_id text,expected_contract text,expected_recipient text,receipt jsonb,
 created_at timestamptz not null default now(),updated_at timestamptz not null default now()
);
create table if not exists marketplace_private.tokenization_allocation_ledger(
 request_id uuid primary key references marketplace_private.tokenization_requests(id),job_id uuid unique not null references marketplace_private.tokenization_issuance_jobs(id),
 user_id uuid not null references auth.users(id),position_id uuid not null references marketplace_private.asset_positions(id),
 transaction_id uuid unique not null references marketplace_private.transactions(id),proposal_version integer not null,
 chain_id text not null,contract_address text not null,transaction_hash text not null,log_index integer not null,
 quantity numeric(38,18) not null check(quantity>0),receipt jsonb not null,created_at timestamptz not null default now(),
 unique(chain_id,transaction_hash,log_index),unique(chain_id,contract_address)
);
do $$declare t text;begin
 foreach t in array array['tokenization_asset_types','tokenization_requests','tokenization_documents','tokenization_internal_notes','tokenization_proposals','tokenization_issuance_jobs','tokenization_allocation_ledger'] loop
  execute format('alter table marketplace_private.%I enable row level security',t);
  execute format('revoke all on marketplace_private.%I from public,anon,authenticated',t);
  execute format('grant all on marketplace_private.%I to service_role',t);
 end loop;
end$$;

create or replace function public.marketplace_tokenization_upload_slot(p_id uuid,p_version integer,p_slot text,p_name text,p_mime text,p_size integer) returns jsonb language plpgsql security definer set search_path='' as $$
declare r marketplace_private.tokenization_requests;did uuid:=gen_random_uuid();path text;ext text;
begin
 select * into r from marketplace_private.tokenization_requests where id=p_id and user_id=auth.uid() for update;
 if r.id is null then raise exception 'tokenization_not_found';end if;
 if r.version is distinct from p_version then raise exception 'tokenization_conflict';end if;
 if r.status not in('draft','changes_requested') then raise exception 'tokenization_state';end if;
 if p_slot is null or not exists(select 1 from marketplace_private.tokenization_asset_types where id=r.asset_type and p_slot=any(document_slots)) or p_name is null or length(p_name) not between 1 and 180 or p_name~'[[:cntrl:]/\\]' or p_mime is null or p_mime not in('application/pdf','image/jpeg','image/png') or p_size is null or p_size not between 1 and 10485760 then raise exception 'tokenization_invalid';end if;
 if (select count(*) from marketplace_private.tokenization_documents where request_id=r.id)>=100 then raise exception 'tokenization_limit';end if;
 ext:=case p_mime when 'application/pdf' then 'pdf' when 'image/jpeg' then 'jpg' else 'png' end;
 path:=r.user_id::text||'/'||r.id::text||'/'||did::text||'.'||ext;
 insert into marketplace_private.tokenization_documents(id,request_id,slot,name,mime,size,path,base_version) values(did,r.id,p_slot,p_name,p_mime,p_size,path,p_version);
 return jsonb_build_object('document_id',did,'path',path);
end$$;

create or replace function public.marketplace_tokenization_upload_complete(p_id uuid,p_version integer,p_document uuid) returns jsonb language plpgsql security definer set search_path='' as $$
declare r marketplace_private.tokenization_requests;d marketplace_private.tokenization_documents;old_paths jsonb;
begin
 select * into r from marketplace_private.tokenization_requests where id=p_id and user_id=auth.uid() for update;
 if r.id is null then raise exception 'tokenization_not_found';end if;
 if r.version is distinct from p_version then raise exception 'tokenization_conflict';end if;
 if r.status not in('draft','changes_requested') then raise exception 'tokenization_state';end if;
 select * into d from marketplace_private.tokenization_documents where id=p_document and request_id=r.id and upload_state='pending' and base_version=p_version for update;
 if d.id is null or not exists(select 1 from storage.objects where bucket_id='tokenization-documents' and name=d.path) then raise exception 'tokenization_documents';end if;
 select coalesce(jsonb_agg(path),'[]') into old_paths from marketplace_private.tokenization_documents where request_id=r.id and slot=d.slot and upload_state='complete';
 update marketplace_private.tokenization_documents set upload_state='deleted' where request_id=r.id and slot=d.slot and upload_state='complete';
 update marketplace_private.tokenization_documents set upload_state='complete' where id=d.id;
 update marketplace_private.tokenization_requests set version=version+1,updated_at=now() where id=r.id;
 return jsonb_build_object('request',marketplace_private.tokenization_view(r.id),'deleted_paths',old_paths);
end$$;

create or replace function public.marketplace_tokenization_upload_abort(p_id uuid,p_document uuid) returns jsonb language plpgsql security definer set search_path='' as $$
begin
 if not exists(select 1 from marketplace_private.tokenization_requests where id=p_id and user_id=auth.uid()) then raise exception 'tokenization_not_found';end if;
 update marketplace_private.tokenization_documents set upload_state='deleted' where id=p_document and request_id=p_id and upload_state='pending';
 return jsonb_build_object('ok',true);
end$$;

create or replace function public.marketplace_tokenization_document(p_id uuid,p_document uuid,p_admin boolean default false) returns jsonb language plpgsql stable security definer set search_path='' as $$
declare d marketplace_private.tokenization_documents;
begin
 if auth.uid() is null then raise exception 'not_authenticated' using errcode='42501';end if;
 if p_admin and public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 select doc.* into d from marketplace_private.tokenization_documents doc join marketplace_private.tokenization_requests r on r.id=doc.request_id where r.id=p_id and doc.id=p_document and doc.upload_state='complete' and (p_admin or r.user_id=auth.uid());
 if d.id is null then raise exception 'tokenization_not_found';end if;
 return jsonb_build_object('path',d.path,'name',d.name,'mime',d.mime,'size',d.size);
end$$;

create or replace function public.marketplace_tokenization_storage(p_path text,p_action text) returns boolean language sql stable security definer set search_path='' as $$
 select exists(select 1 from marketplace_private.tokenization_documents d join marketplace_private.tokenization_requests r on r.id=d.request_id where d.path=p_path and (
  (p_action='read' and d.upload_state='complete' and (r.user_id=auth.uid() or public.marketplace_role()='owner')) or
  (p_action='insert' and r.user_id=auth.uid() and r.status in('draft','changes_requested') and r.version=d.base_version and d.upload_state='pending') or
  (p_action='delete' and r.user_id=auth.uid() and d.upload_state='deleted')
 ))
$$;

insert into storage.buckets(id,name,public,file_size_limit,allowed_mime_types) values('tokenization-documents','tokenization-documents',false,10485760,array['application/pdf','image/jpeg','image/png']) 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 tokenization_document_read on storage.objects;
-- Owners must be able to select their retired object for Storage DELETE cleanup.
-- Application downloads still resolve only current complete documents.
create policy tokenization_document_read on storage.objects for select to authenticated using(bucket_id='tokenization-documents' and (public.marketplace_tokenization_storage(name,'read') or public.marketplace_tokenization_storage(name,'delete')));
drop policy if exists tokenization_document_insert on storage.objects;
create policy tokenization_document_insert on storage.objects for insert to authenticated with check(bucket_id='tokenization-documents' and public.marketplace_tokenization_storage(name,'insert'));
drop policy if exists tokenization_document_delete on storage.objects;
create policy tokenization_document_delete on storage.objects for delete to authenticated using(bucket_id='tokenization-documents' and public.marketplace_tokenization_storage(name,'delete'));

-- Only the trusted receipt verifier can bind and finalize issuance. No browser role.
create or replace function public.marketplace_tokenization_issuance_job(p_id uuid) returns jsonb language plpgsql stable security definer set search_path='' as $$
declare j marketplace_private.tokenization_issuance_jobs;
begin
 select * into j from marketplace_private.tokenization_issuance_jobs where request_id=p_id;
 if j.id is null then raise exception 'tokenization_not_found';end if;
 return jsonb_build_object('job',to_jsonb(j));
end$$;
create or replace function public.marketplace_tokenization_issuance_bind(p_id uuid,p_proposal_version integer,p_chain_id text,p_contract text,p_recipient text) returns jsonb language plpgsql security definer set search_path='' as $$
declare j marketplace_private.tokenization_issuance_jobs;
begin
 select * into j from marketplace_private.tokenization_issuance_jobs where request_id=p_id for update;
 if j.id is null or j.proposal_version is distinct from p_proposal_version or j.status<>'pending' then raise exception 'tokenization_conflict';end if;
 if p_chain_id is distinct from j.tokenomics->>'chain_id' or coalesce(p_contract,'')!~'^0x[0-9a-f]{40}$' or p_contract='0x0000000000000000000000000000000000000000' or coalesce(p_recipient,'')!~'^0x[0-9a-f]{40}$' or p_recipient='0x0000000000000000000000000000000000000000' then raise exception 'tokenization_proposal';end if;
 if j.expected_contract is not null and (j.expected_chain_id is distinct from p_chain_id or j.expected_contract is distinct from p_contract or j.expected_recipient is distinct from p_recipient) then raise exception 'tokenization_conflict';end if;
 update marketplace_private.tokenization_issuance_jobs set expected_chain_id=p_chain_id,expected_contract=p_contract,expected_recipient=p_recipient,updated_at=now() where id=j.id;
 return jsonb_build_object('ok',true);
end$$;
create or replace function public.marketplace_tokenization_issuance_finalize(p_id uuid,p_proposal_version integer,p_receipt jsonb) returns jsonb language plpgsql security definer set search_path='' as $$
declare r marketplace_private.tokenization_requests;j marketplace_private.tokenization_issuance_jobs;qty numeric(38,18);position uuid;tx uuid;verified_receipt jsonb;sym text;
begin
 select * into r from marketplace_private.tokenization_requests where id=p_id for update;
 select * into j from marketplace_private.tokenization_issuance_jobs where request_id=p_id for update;
 if r.id is null or j.id is null then raise exception 'tokenization_not_found';end if;
 if j.status='confirmed' then
  if j.proposal_version=p_proposal_version and j.receipt->>'transaction_hash'=p_receipt->>'transaction_hash' and j.receipt->>'log_index'=p_receipt->>'log_index' and j.receipt->>'contract_address'=p_receipt->>'contract_address' then return jsonb_build_object('request',marketplace_private.tokenization_view(r.id));end if;
  raise exception 'tokenization_conflict';
 end if;
 if r.status<>'issuance_pending' or j.status<>'pending' or r.accepted_proposal_version is distinct from p_proposal_version or j.proposal_version is distinct from p_proposal_version or r.accepted_tokenomics is distinct from j.tokenomics then raise exception 'tokenization_conflict';end if;
 if j.expected_contract is null or p_receipt->>'chain_id' is distinct from j.expected_chain_id or p_receipt->>'contract_address' is distinct from j.expected_contract or p_receipt->>'recipient_address' is distinct from j.expected_recipient or p_receipt->>'amount' is distinct from j.tokenomics->>'total_supply' or p_receipt->>'decimals' is distinct from j.tokenomics->>'decimals' or p_receipt->>'verified_by' is distinct from 'rpc-mint-receipt-v1' or coalesce(p_receipt->>'transaction_hash','')!~'^0x[0-9a-f]{64}$' or coalesce(p_receipt->>'block_hash','')!~'^0x[0-9a-f]{64}$' or coalesce(p_receipt->>'log_index','')!~'^[0-9]{1,9}$' or coalesce(p_receipt->>'confirmations','')!~'^[0-9]{1,10}$' or (p_receipt->>'confirmations')::bigint<1 then raise exception 'tokenization_proposal';end if;
 qty:=(j.tokenomics->>'total_supply')::numeric;sym:=j.tokenomics->>'symbol';
 perform pg_advisory_xact_lock(hashtextextended('tokenization-symbol:'||r.user_id::text||':'||sym,0));
 -- Holdings currently identify positions by symbol. Never merge unrelated contracts.
 if exists(select 1 from marketplace_private.asset_positions where user_id=r.user_id and asset_symbol=sym) then raise exception 'tokenization_asset_conflict';end if;
 insert into marketplace_private.asset_positions(user_id,asset_symbol,asset_name,quantity,locked_quantity) values(r.user_id,sym,jsonb_build_object('fa',j.tokenomics->>'name'),qty,qty) returning id into position;
 insert into marketplace_private.transactions(user_id,reference,kind,asset_symbol,amount,status,network,tx_hash,completed_at) values(r.user_id,'tokenization:'||r.id::text||':'||j.proposal_version::text,'deposit',sym,qty,'confirmed',j.tokenomics->>'network',p_receipt->>'transaction_hash',now()) returning id into tx;
 verified_receipt:=jsonb_build_object('verified_by',p_receipt->'verified_by','transaction_hash',p_receipt->'transaction_hash','contract_address',p_receipt->'contract_address','recipient_address',p_receipt->'recipient_address','amount',p_receipt->'amount','decimals',p_receipt->'decimals','block_hash',p_receipt->'block_hash','block_number',p_receipt->'block_number','log_index',p_receipt->'log_index','confirmations',p_receipt->'confirmations')||jsonb_build_object('status','confirmed','chain_id',(j.tokenomics->>'chain_id')::bigint,'confirmed_at',now(),'message','توکن‌ها پس از تأیید صدور به کیف پول داخلی تخصیص یافتند و تا فعال‌سازی فرایند آزادسازی مطابق شرایط توکنومیکس، قفل هستند.');
 insert into marketplace_private.tokenization_allocation_ledger(request_id,job_id,user_id,position_id,transaction_id,proposal_version,chain_id,contract_address,transaction_hash,log_index,quantity,receipt) values(r.id,j.id,r.user_id,position,tx,j.proposal_version,j.expected_chain_id,j.expected_contract,p_receipt->>'transaction_hash',(p_receipt->>'log_index')::integer,qty,verified_receipt);
 update marketplace_private.tokenization_issuance_jobs set status='confirmed',receipt=verified_receipt,updated_at=now() where id=j.id;
 update marketplace_private.tokenization_requests set status='issued',version=version+1,updated_at=now() where id=r.id;
 insert into marketplace_private.audit_log(actor,entity,record_id,operation) values(null,'tokenization',r.id::text,'verified_issuance_credit');
 return jsonb_build_object('request',marketplace_private.tokenization_view(r.id));
end$$;

create or replace function marketplace_private.tokenization_text_valid(s text) returns boolean language sql immutable set search_path='' as $$
 select s is not null and translate(s,chr(9)||chr(10)||chr(13),'')!~'[[:cntrl:]]' and s!~U&'[\202A-\202E\2066-\2069]'
$$;

create or replace function marketplace_private.tokenization_fields_valid(p_type text,p_fields jsonb,p_partial boolean) returns boolean language plpgsql stable security definer set search_path='' as $$
declare definition jsonb;f jsonb;k text;v jsonb;s text;
begin
 select fields into definition from marketplace_private.tokenization_asset_types where id=p_type;
 if definition is null or p_fields is null or jsonb_typeof(p_fields)<>'object' or octet_length(p_fields::text)>60000 then return false;end if;
 for k,v in select * from jsonb_each(p_fields) loop
  select value into f from jsonb_array_elements(definition) where value->>'key'=k;
  if f is null or jsonb_typeof(v)<>'string' then return false;end if;s:=trim(v#>>'{}');
  if not marketplace_private.tokenization_text_valid(s) or length(s)>coalesce((f->>'maxLength')::integer,4000) then return false;end if;
  if s<>'' then
   if f->>'type' in('number','decimal','integer') then
    if s!~'^[0-9]+(\.[0-9]+)?$' or length(s)>60 then return false;end if;
    if coalesce((f->>'integer')::boolean,false) and s!~'^[0-9]+$' then return false;end if;
    if length(split_part(s,'.',1))>coalesce((f->>'maxIntegerDigits')::integer,24) or length(split_part(s,'.',2))>coalesce((f->>'maxFractionDigits')::integer,0) then return false;end if;
    if f ? 'min' and s::numeric<(f->>'min')::numeric then return false;end if;
    if f ? 'max' and s::numeric>(f->>'max')::numeric then return false;end if;
    if coalesce((f->>'exclusiveMin')::boolean,false) and s::numeric<=coalesce((f->>'min')::numeric,0) then return false;end if;
   end if;
   if f->>'type'='date' then
    if s!~'^[0-9]{4}-[0-9]{2}-[0-9]{2}$' then return false;end if;
    begin if to_char(s::date,'YYYY-MM-DD')<>s then return false;end if;exception when others then return false;end;
   end if;
   if f ? 'options' and not exists(select 1 from jsonb_array_elements(f->'options') o where coalesce(o->>'value',o#>>'{}')=s) then return false;end if;
  end if;
 end loop;
 if not p_partial then
  for f in select value from jsonb_array_elements(definition) loop
   if coalesce((f->>'required')::boolean,false) and length(trim(coalesce(p_fields->>(f->>'key'),'')))=0 then return false;end if;
  end loop;
 end if;return true;
end$$;

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,
  '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$$;

create or replace function public.marketplace_tokenization_state(p_admin boolean default false) returns jsonb language plpgsql stable security definer set search_path='' as $$
begin
 if auth.uid() is null then raise exception 'not_authenticated' using errcode='42501';end if;
 if p_admin and public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 return jsonb_build_object('requests',coalesce((select jsonb_agg(marketplace_private.tokenization_view(id,p_admin) order by updated_at desc) from (select id,updated_at from marketplace_private.tokenization_requests where (p_admin and status<>'draft') or (not p_admin and user_id=auth.uid()) order by updated_at desc limit 100) q),'[]'));
end$$;

create or replace function marketplace_private.tokenization_documents_ready(p_id uuid,p_approved boolean default false) returns boolean language sql stable security definer set search_path='' as $$
 select not exists(select 1 from marketplace_private.tokenization_requests r join marketplace_private.tokenization_asset_types t on t.id=r.asset_type cross join lateral unnest(t.required_documents) as required(slot_name) where r.id=p_id and not exists(select 1 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.slot=required.slot_name and d.upload_state='complete' and d.status in('uploaded','approved') and (not p_approved or d.status='approved')))
$$;

create or replace function marketplace_private.tokenization_proposal_valid(t jsonb) returns boolean language plpgsql immutable set search_path='' as $$
declare k text;a jsonb;total numeric:=0;labels text[]:=array[]::text[];
begin
 if t is null or jsonb_typeof(t)<>'object' or octet_length(t::text)>60000 then return false;end if;
 for k in select jsonb_object_keys(t) loop if k not in('name','symbol','total_supply','decimals','unit_price_rial','network','chain_id','rights','transfer_policy','redemption_policy','risk_disclosure','allocations','wallet_destination') then return false;end if;end loop;
 foreach k in array array['name','symbol','total_supply','unit_price_rial','network','rights','transfer_policy','redemption_policy','risk_disclosure','wallet_destination'] loop
  if jsonb_typeof(t->k) is distinct from 'string' or not marketplace_private.tokenization_text_valid(t->>k) or length(trim(t->>k)) not between 1 and 4000 then return false;end if;
 end loop;
 if t->>'wallet_destination'<>'internal' or t->>'symbol'!~'^[A-Z][A-Z0-9]{0,11}$' or length(t->>'name')>100 or length(t->>'network')>100 or length(t->>'transfer_policy')>3000 or length(t->>'redemption_policy')>3000 then return false;end if;
 if t->>'total_supply'!~'^[0-9]{1,20}(\.[0-9]{1,18})?$' or (t->>'total_supply')::numeric<=0 or (t->>'total_supply')::numeric>=1e20 then return false;end if;
 if jsonb_typeof(t->'decimals') is distinct from 'number' or t->>'decimals'!~'^[0-9]{1,2}$' or (t->>'decimals')::integer>18 or length(split_part(t->>'total_supply','.',2))>(t->>'decimals')::integer then return false;end if;
 if t->>'unit_price_rial'!~'^[0-9]{1,24}$' or (t->>'unit_price_rial')::numeric<=0 then return false;end if;
 if jsonb_typeof(t->'chain_id') is distinct from 'number' or t->>'chain_id'!~'^[0-9]{1,16}$' or (t->>'chain_id')::numeric not between 1 and 9007199254740991 then return false;end if;
 if jsonb_typeof(t->'allocations') is distinct from 'array' or jsonb_array_length(t->'allocations') not between 1 and 20 then return false;end if;
 for a in select value from jsonb_array_elements(t->'allocations') loop
  if jsonb_typeof(a) is distinct from 'object' or jsonb_typeof(a->'label') is distinct from 'string' or length(trim(a->>'label')) not between 1 and 100 or jsonb_typeof(a->'percent') is distinct from 'number' or coalesce(a->>'percent','')!~'^[0-9]{1,3}(\.[0-9]{1,2})?$' or (a->>'percent')::numeric<=0 or (a->>'percent')::numeric>100 or jsonb_typeof(a->'lock_months') is distinct from 'number' or coalesce(a->>'lock_months','')!~'^[0-9]{1,4}$' or (a->>'lock_months')::integer>1200 then return false;end if;
  if not marketplace_private.tokenization_text_valid(a->>'label') or trim(a->>'label')=any(labels) or exists(select 1 from jsonb_object_keys(a) q where q not in('label','percent','lock_months')) then return false;end if;
  labels:=array_append(labels,trim(a->>'label'));total:=total+(a->>'percent')::numeric;
 end loop;return total=100;
end$$;

create or replace function public.marketplace_tokenization_customer(p_action text,p_payload jsonb) returns jsonb language plpgsql security definer set search_path='' as $$
declare uid uuid:=auth.uid();r marketplace_private.tokenization_requests;d marketplace_private.tokenization_documents;rid uuid;v integer;typ text;path text;
begin
 if uid is null then raise exception 'not_authenticated' using errcode='42501';end if;
 if p_payload is null or jsonb_typeof(p_payload)<>'object' or octet_length(p_payload::text)>64000 then raise exception 'tokenization_invalid';end if;
 if p_action='start' then
  if p_payload->>'terms_version' is distinct from '2026-09-24-v1' or p_payload->'consent' is distinct from 'true'::jsonb then raise exception 'tokenization_terms';end if;
  typ:=p_payload->>'asset_type';if not exists(select 1 from marketplace_private.tokenization_asset_types where id=typ) then raise exception 'tokenization_invalid';end if;
  perform pg_advisory_xact_lock(hashtextextended('tokenization:'||uid::text,0));
  select * into r from marketplace_private.tokenization_requests where user_id=uid and asset_type=typ and status='draft' order by created_at desc limit 1;
  if r.id is null then
   if (select count(*) from marketplace_private.tokenization_requests where user_id=uid and status not in('issued','rejected'))>=20 or (select count(*) from marketplace_private.tokenization_requests where user_id=uid and created_at>now()-interval '24 hours')>=10 then raise exception 'tokenization_limit';end if;
   insert into marketplace_private.tokenization_requests(user_id,asset_type,terms_version) values(uid,typ,'2026-09-24-v1') returning * into r;
  end if;
  return jsonb_build_object('request',marketplace_private.tokenization_view(r.id));
 end if;
 begin rid:=(p_payload->>'id')::uuid;v:=(p_payload->>'version')::integer;exception when others then raise exception 'tokenization_invalid';end;
 select * into r from marketplace_private.tokenization_requests where id=rid and user_id=uid for update;
 if r.id is null then raise exception 'tokenization_not_found';end if;
 if r.version is distinct from v then raise exception 'tokenization_conflict';end if;
 if p_action='accept' then
  if r.status<>'awaiting_acceptance' then raise exception 'tokenization_state';end if;
  if exists(select 1 from unnest(array['legal','financial','technical']) x where r.reviews->x->>'status' is distinct from 'approved') or not marketplace_private.tokenization_documents_ready(r.id,true) then raise exception 'tokenization_reviews';end if;
  if p_payload->'consent' is distinct from 'true'::jsonb or (p_payload->>'proposal_version')::integer is distinct from r.proposal_version or not marketplace_private.tokenization_proposal_valid(r.tokenomics) then raise exception 'tokenization_proposal';end if;
  update marketplace_private.tokenization_requests set status='issuance_pending',accepted_at=now(),accepted_proposal_version=proposal_version,accepted_tokenomics=tokenomics,accepted_fields=fields,
   accepted_documents=(select coalesce(jsonb_agg(to_jsonb(x)),'[]') from marketplace_private.tokenization_documents x where request_id=r.id and upload_state='complete'),version=version+1,updated_at=now() where id=r.id;
  insert into marketplace_private.tokenization_issuance_jobs(request_id,user_id,proposal_version,tokenomics) values(r.id,uid,r.proposal_version,r.tokenomics);
 elsif p_action in('save','submit','delete_document') then
  if r.status not in('draft','changes_requested') then raise exception 'tokenization_state';end if;
  if p_action='save' then
   typ:=p_payload->>'asset_type';
   if not marketplace_private.tokenization_fields_valid(typ,p_payload->'fields',true) then raise exception 'tokenization_fields';end if;
   if typ<>r.asset_type and exists(select 1 from marketplace_private.tokenization_documents where request_id=r.id and upload_state='complete') then raise exception 'tokenization_documents';end if;
   update marketplace_private.tokenization_requests set asset_type=typ,fields=p_payload->'fields',tokenomics=null,version=version+1,updated_at=now() where id=r.id;
  elsif p_action='submit' then
   if p_payload->'consent' is distinct from 'true'::jsonb then raise exception 'tokenization_terms';end if;
   if not marketplace_private.tokenization_fields_valid(r.asset_type,r.fields,false) then raise exception 'tokenization_fields';end if;
   if not marketplace_private.tokenization_documents_ready(r.id) then raise exception 'tokenization_documents';end if;
   update marketplace_private.tokenization_requests set status='submitted',submitted_at=now(),consented_at=now(),message='',tokenomics=null,
    reviews='{"legal":{"status":"pending","message":""},"financial":{"status":"pending","message":""},"technical":{"status":"pending","message":""}}',version=version+1,updated_at=now() where id=r.id;
   update marketplace_private.tokenization_documents set status='uploaded',message='',reviewed_at=null where request_id=r.id and upload_state='complete';
  else
   select * into d from marketplace_private.tokenization_documents where id=(p_payload->>'document_id')::uuid and request_id=r.id and upload_state='complete' for update;
   if d.id is null then raise exception 'tokenization_not_found';end if;path:=d.path;
   update marketplace_private.tokenization_documents set upload_state='deleted' where id=d.id;
   update marketplace_private.tokenization_requests set version=version+1,updated_at=now() where id=r.id;
  end if;
 else raise exception 'tokenization_invalid';end if;
 insert into marketplace_private.audit_log(actor,entity,record_id,operation) values(uid,'tokenization',r.id::text,p_action);
 return jsonb_build_object('request',marketplace_private.tokenization_view(r.id))||case when path is null then '{}'::jsonb else jsonb_build_object('deleted_path',path) end;
end$$;

create or replace function public.marketplace_tokenization_admin(p_action text,p_payload jsonb) returns jsonb language plpgsql security definer set search_path='' as $$
declare r marketplace_private.tokenization_requests;rid uuid;v integer;layer text;outcome text;msg text;d marketplace_private.tokenization_documents;t jsonb;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if p_payload is null or jsonb_typeof(p_payload)<>'object' or octet_length(p_payload::text)>64000 then raise exception 'tokenization_invalid';end if;
 begin rid:=(p_payload->>'id')::uuid;v:=(p_payload->>'version')::integer;exception when others then raise exception 'tokenization_invalid';end;
 select * into r from marketplace_private.tokenization_requests where id=rid for update;
 if r.id is null then raise exception 'tokenization_not_found';end if;
 if r.version is distinct from v then raise exception 'tokenization_conflict';end if;
 if r.status not in('submitted','under_review') and not(p_action in('request_changes','reject') and r.status='awaiting_acceptance') then raise exception 'tokenization_state';end if;
 msg:=trim(coalesce(p_payload->>'message',''));if not marketplace_private.tokenization_text_valid(msg) or length(msg)>2000 then raise exception 'tokenization_invalid';end if;
 if p_action='review' then
  layer:=p_payload->>'layer';outcome:=p_payload->>'status';
  if layer is null or layer not in('legal','financial','technical') or outcome is null or outcome not in('approved','changes_requested','rejected') or not marketplace_private.tokenization_text_valid(coalesce(p_payload->>'internal_note','')) or length(coalesce(p_payload->>'internal_note',''))>4000 or (outcome<>'approved' and length(msg)<5) then raise exception 'tokenization_invalid';end if;
  update marketplace_private.tokenization_requests set reviews=jsonb_set(reviews,array[layer],jsonb_build_object('status',outcome,'message',msg,'updated_at',now())),status='under_review' where id=r.id;
  if p_payload ? 'internal_note' then insert into marketplace_private.tokenization_internal_notes(request_id,layer,actor,note) values(r.id,layer,auth.uid(),coalesce(p_payload->>'internal_note',''));end if;
 elsif p_action='document_review' then
  outcome:=p_payload->>'status';if outcome is null or outcome not in('approved','changes_requested','rejected') or (outcome<>'approved' and length(msg)<5) then raise exception 'tokenization_invalid';end if;
  select * into d from marketplace_private.tokenization_documents where id=(p_payload->>'document_id')::uuid and request_id=r.id and upload_state='complete';
  if d.id is null then raise exception 'tokenization_not_found';end if;
  update marketplace_private.tokenization_documents set status=outcome,message=msg,reviewed_at=now() where id=d.id;
  update marketplace_private.tokenization_requests set status='under_review' where id=r.id;
 elsif p_action='propose' then
  if exists(select 1 from unnest(array['legal','financial','technical']) x where r.reviews->x->>'status' is distinct from 'approved') or not marketplace_private.tokenization_documents_ready(r.id,true) then raise exception 'tokenization_reviews';end if;
  t:=p_payload->'tokenomics';if not marketplace_private.tokenization_proposal_valid(t) then raise exception 'tokenization_proposal';end if;
  insert into marketplace_private.tokenization_proposals(request_id,version,tokenomics,actor) values(r.id,r.proposal_version+1,t,auth.uid());
  update marketplace_private.tokenization_requests set tokenomics=t,proposal_version=proposal_version+1,status='awaiting_acceptance',message='' where id=r.id;
 elsif p_action in('request_changes','reject') then
  if length(msg)<5 then raise exception 'tokenization_invalid';end if;
  update marketplace_private.tokenization_requests set status=case when p_action='reject' then 'rejected' else 'changes_requested' end,message=msg,tokenomics=null where id=r.id;
 else raise exception 'tokenization_invalid';end if;
 update marketplace_private.tokenization_requests set version=version+1,updated_at=now() where id=r.id;
 insert into marketplace_private.audit_log(actor,entity,record_id,operation) values(auth.uid(),'tokenization',r.id::text,p_action);
 return jsonb_build_object('request',marketplace_private.tokenization_view(r.id,true));
end$$;

-- Schema snapshot generated from the shared tokenization field metadata.
insert into marketplace_private.tokenization_asset_types(id,fields,document_slots,required_documents) values('solar_plant','[{"key":"title","type":"text","required":true,"maxLength":120},{"key":"owner_name","type":"text","required":true,"maxLength":160},{"key":"owner_type","type":"select","required":true,"maxLength":500,"options":["owner","company"]},{"key":"country","type":"text","required":true,"maxLength":80},{"key":"city","type":"text","required":true,"maxLength":100},{"key":"address","type":"textarea","required":true,"maxLength":600},{"key":"summary","type":"textarea","required":true,"maxLength":4000},{"key":"estimated_value_rial","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":24,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"capacity_mw","type":"number","required":true,"maxLength":256,"maxIntegerDigits":18,"maxFractionDigits":6,"min":"0","exclusiveMin":true},{"key":"land_area_m2","type":"number","required":true,"maxLength":256,"maxIntegerDigits":18,"maxFractionDigits":6,"min":"0","exclusiveMin":true},{"key":"project_stage","type":"select","required":true,"maxLength":500,"options":["planning","construction","operational"]},{"key":"grid_connection","type":"textarea","required":true,"maxLength":1200},{"key":"power_contract","type":"textarea","required":true,"maxLength":1200}]'::jsonb,ARRAY['ownership_evidence','owner_identity','valuation_report','representation_authority','energy_license','grid_documents','technical_plan','land_rights'],ARRAY['ownership_evidence','owner_identity','energy_license','technical_plan','land_rights']) on conflict(id) do update set fields=excluded.fields,document_slots=excluded.document_slots,required_documents=excluded.required_documents;
insert into marketplace_private.tokenization_asset_types(id,fields,document_slots,required_documents) values('real_estate','[{"key":"title","type":"text","required":true,"maxLength":120},{"key":"owner_name","type":"text","required":true,"maxLength":160},{"key":"owner_type","type":"select","required":true,"maxLength":500,"options":["owner","company"]},{"key":"country","type":"text","required":true,"maxLength":80},{"key":"city","type":"text","required":true,"maxLength":100},{"key":"address","type":"textarea","required":true,"maxLength":600},{"key":"summary","type":"textarea","required":true,"maxLength":4000},{"key":"estimated_value_rial","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":24,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"registration_number","type":"text","required":true,"maxLength":100},{"key":"built_area_m2","type":"number","required":true,"maxLength":256,"maxIntegerDigits":18,"maxFractionDigits":6,"min":"0","exclusiveMin":true},{"key":"units_count","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":7,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"construction_status","type":"select","required":true,"maxLength":500,"options":["planned","under_construction","completed"]},{"key":"encumbrances","type":"textarea","required":true,"maxLength":2000}]'::jsonb,ARRAY['ownership_evidence','owner_identity','valuation_report','representation_authority','property_title','building_permit','property_plan','lease_contracts'],ARRAY['ownership_evidence','owner_identity','property_title','property_plan']) on conflict(id) do update set fields=excluded.fields,document_slots=excluded.document_slots,required_documents=excluded.required_documents;
insert into marketplace_private.tokenization_asset_types(id,fields,document_slots,required_documents) values('land','[{"key":"title","type":"text","required":true,"maxLength":120},{"key":"owner_name","type":"text","required":true,"maxLength":160},{"key":"owner_type","type":"select","required":true,"maxLength":500,"options":["owner","company"]},{"key":"country","type":"text","required":true,"maxLength":80},{"key":"city","type":"text","required":true,"maxLength":100},{"key":"address","type":"textarea","required":true,"maxLength":600},{"key":"summary","type":"textarea","required":true,"maxLength":4000},{"key":"estimated_value_rial","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":24,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"registration_number","type":"text","required":true,"maxLength":100},{"key":"area_m2","type":"number","required":true,"maxLength":256,"maxIntegerDigits":18,"maxFractionDigits":6,"min":"0","exclusiveMin":true},{"key":"land_use","type":"text","required":true,"maxLength":300},{"key":"access_utilities","type":"textarea","required":false,"maxLength":1200},{"key":"encumbrances","type":"textarea","required":true,"maxLength":2000}]'::jsonb,ARRAY['ownership_evidence','owner_identity','valuation_report','representation_authority','land_title','cadastral_map','land_use_documents'],ARRAY['ownership_evidence','owner_identity','land_title','cadastral_map']) on conflict(id) do update set fields=excluded.fields,document_slots=excluded.document_slots,required_documents=excluded.required_documents;
insert into marketplace_private.tokenization_asset_types(id,fields,document_slots,required_documents) values('commercial_property','[{"key":"title","type":"text","required":true,"maxLength":120},{"key":"owner_name","type":"text","required":true,"maxLength":160},{"key":"owner_type","type":"select","required":true,"maxLength":500,"options":["owner","company"]},{"key":"country","type":"text","required":true,"maxLength":80},{"key":"city","type":"text","required":true,"maxLength":100},{"key":"address","type":"textarea","required":true,"maxLength":600},{"key":"summary","type":"textarea","required":true,"maxLength":4000},{"key":"estimated_value_rial","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":24,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"registration_number","type":"text","required":true,"maxLength":100},{"key":"rentable_area_m2","type":"number","required":true,"maxLength":256,"maxIntegerDigits":18,"maxFractionDigits":6,"min":"0","exclusiveMin":true},{"key":"units_count","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":7,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"occupancy_percent","type":"number","required":true,"maxLength":256,"maxIntegerDigits":18,"maxFractionDigits":2,"min":"0","max":"100","exclusiveMin":false},{"key":"lease_summary","type":"textarea","required":true,"maxLength":2000}]'::jsonb,ARRAY['ownership_evidence','owner_identity','valuation_report','representation_authority','commercial_title','operating_documents','lease_schedule'],ARRAY['ownership_evidence','owner_identity','commercial_title','lease_schedule']) on conflict(id) do update set fields=excluded.fields,document_slots=excluded.document_slots,required_documents=excluded.required_documents;
insert into marketplace_private.tokenization_asset_types(id,fields,document_slots,required_documents) values('agriculture','[{"key":"title","type":"text","required":true,"maxLength":120},{"key":"owner_name","type":"text","required":true,"maxLength":160},{"key":"owner_type","type":"select","required":true,"maxLength":500,"options":["owner","company"]},{"key":"country","type":"text","required":true,"maxLength":80},{"key":"city","type":"text","required":true,"maxLength":100},{"key":"address","type":"textarea","required":true,"maxLength":600},{"key":"summary","type":"textarea","required":true,"maxLength":4000},{"key":"estimated_value_rial","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":24,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"crop_type","type":"text","required":true,"maxLength":200},{"key":"area_hectares","type":"number","required":true,"maxLength":256,"maxIntegerDigits":18,"maxFractionDigits":6,"min":"0","exclusiveMin":true},{"key":"water_rights","type":"textarea","required":true,"maxLength":1500},{"key":"production_cycle","type":"text","required":true,"maxLength":300},{"key":"storage_transport","type":"textarea","required":false,"maxLength":1200}]'::jsonb,ARRAY['ownership_evidence','owner_identity','valuation_report','representation_authority','farm_rights','water_documents','production_plan'],ARRAY['ownership_evidence','owner_identity','farm_rights','production_plan']) on conflict(id) do update set fields=excluded.fields,document_slots=excluded.document_slots,required_documents=excluded.required_documents;
insert into marketplace_private.tokenization_asset_types(id,fields,document_slots,required_documents) values('machinery','[{"key":"title","type":"text","required":true,"maxLength":120},{"key":"owner_name","type":"text","required":true,"maxLength":160},{"key":"owner_type","type":"select","required":true,"maxLength":500,"options":["owner","company"]},{"key":"country","type":"text","required":true,"maxLength":80},{"key":"city","type":"text","required":true,"maxLength":100},{"key":"address","type":"textarea","required":true,"maxLength":600},{"key":"summary","type":"textarea","required":true,"maxLength":4000},{"key":"estimated_value_rial","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":24,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"manufacturer_model","type":"text","required":true,"maxLength":200},{"key":"serial_number","type":"text","required":true,"maxLength":160},{"key":"manufacture_year","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":4,"maxFractionDigits":0,"min":"1800","max":"2200","exclusiveMin":false},{"key":"condition","type":"textarea","required":true,"maxLength":1600},{"key":"quantity","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":9,"maxFractionDigits":0,"min":"0","exclusiveMin":true}]'::jsonb,ARRAY['ownership_evidence','owner_identity','valuation_report','representation_authority','equipment_invoice','technical_inspection','equipment_photos'],ARRAY['ownership_evidence','owner_identity','equipment_invoice','equipment_photos']) on conflict(id) do update set fields=excluded.fields,document_slots=excluded.document_slots,required_documents=excluded.required_documents;
insert into marketplace_private.tokenization_asset_types(id,fields,document_slots,required_documents) values('vehicles','[{"key":"title","type":"text","required":true,"maxLength":120},{"key":"owner_name","type":"text","required":true,"maxLength":160},{"key":"owner_type","type":"select","required":true,"maxLength":500,"options":["owner","company"]},{"key":"country","type":"text","required":true,"maxLength":80},{"key":"city","type":"text","required":true,"maxLength":100},{"key":"address","type":"textarea","required":true,"maxLength":600},{"key":"summary","type":"textarea","required":true,"maxLength":4000},{"key":"estimated_value_rial","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":24,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"vehicle_type","type":"text","required":true,"maxLength":150},{"key":"manufacturer_model","type":"text","required":true,"maxLength":200},{"key":"vehicle_identifiers","type":"textarea","required":true,"maxLength":2000},{"key":"vehicle_count","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":7,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"condition","type":"textarea","required":true,"maxLength":1600}]'::jsonb,ARRAY['ownership_evidence','owner_identity','valuation_report','representation_authority','vehicle_titles','insurance_documents','fleet_schedule'],ARRAY['ownership_evidence','owner_identity','vehicle_titles','fleet_schedule']) on conflict(id) do update set fields=excluded.fields,document_slots=excluded.document_slots,required_documents=excluded.required_documents;
insert into marketplace_private.tokenization_asset_types(id,fields,document_slots,required_documents) values('precious_metals','[{"key":"title","type":"text","required":true,"maxLength":120},{"key":"owner_name","type":"text","required":true,"maxLength":160},{"key":"owner_type","type":"select","required":true,"maxLength":500,"options":["owner","company"]},{"key":"country","type":"text","required":true,"maxLength":80},{"key":"city","type":"text","required":true,"maxLength":100},{"key":"address","type":"textarea","required":true,"maxLength":600},{"key":"summary","type":"textarea","required":true,"maxLength":4000},{"key":"estimated_value_rial","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":24,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"metal_type","type":"select","required":true,"maxLength":500,"options":["gold","silver","platinum","copper","other"]},{"key":"weight_kg","type":"number","required":true,"maxLength":256,"maxIntegerDigits":18,"maxFractionDigits":6,"min":"0","exclusiveMin":true},{"key":"purity","type":"text","required":true,"maxLength":120},{"key":"custodian","type":"textarea","required":true,"maxLength":1600},{"key":"delivery_terms","type":"textarea","required":true,"maxLength":2000}]'::jsonb,ARRAY['ownership_evidence','owner_identity','valuation_report','representation_authority','purchase_evidence','assay_certificate','custody_evidence'],ARRAY['ownership_evidence','owner_identity','purchase_evidence','assay_certificate','custody_evidence']) on conflict(id) do update set fields=excluded.fields,document_slots=excluded.document_slots,required_documents=excluded.required_documents;
insert into marketplace_private.tokenization_asset_types(id,fields,document_slots,required_documents) values('art','[{"key":"title","type":"text","required":true,"maxLength":120},{"key":"owner_name","type":"text","required":true,"maxLength":160},{"key":"owner_type","type":"select","required":true,"maxLength":500,"options":["owner","company"]},{"key":"country","type":"text","required":true,"maxLength":80},{"key":"city","type":"text","required":true,"maxLength":100},{"key":"address","type":"textarea","required":true,"maxLength":600},{"key":"summary","type":"textarea","required":true,"maxLength":4000},{"key":"estimated_value_rial","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":24,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"creator","type":"text","required":true,"maxLength":160},{"key":"work_details","type":"textarea","required":true,"maxLength":1800},{"key":"provenance","type":"textarea","required":true,"maxLength":2200},{"key":"condition","type":"textarea","required":true,"maxLength":1600},{"key":"rights_scope","type":"textarea","required":true,"maxLength":2200}]'::jsonb,ARRAY['ownership_evidence','owner_identity','valuation_report','representation_authority','provenance_documents','authenticity_documents','art_photos'],ARRAY['ownership_evidence','owner_identity','provenance_documents','art_photos']) on conflict(id) do update set fields=excluded.fields,document_slots=excluded.document_slots,required_documents=excluded.required_documents;
insert into marketplace_private.tokenization_asset_types(id,fields,document_slots,required_documents) values('oil_project','[{"key":"title","type":"text","required":true,"maxLength":120},{"key":"owner_name","type":"text","required":true,"maxLength":160},{"key":"owner_type","type":"select","required":true,"maxLength":500,"options":["owner","company"]},{"key":"country","type":"text","required":true,"maxLength":80},{"key":"city","type":"text","required":true,"maxLength":100},{"key":"address","type":"textarea","required":true,"maxLength":600},{"key":"summary","type":"textarea","required":true,"maxLength":4000},{"key":"estimated_value_rial","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":24,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"project_scope","type":"textarea","required":true,"maxLength":2000},{"key":"concession_rights","type":"textarea","required":true,"maxLength":2200},{"key":"capacity","type":"text","required":true,"maxLength":300},{"key":"project_stage","type":"select","required":true,"maxLength":500,"options":["planning","construction","operational"]},{"key":"offtake_status","type":"textarea","required":true,"maxLength":1800}]'::jsonb,ARRAY['ownership_evidence','owner_identity','valuation_report','representation_authority','project_rights','technical_report','environmental_documents','offtake_contracts'],ARRAY['ownership_evidence','owner_identity','project_rights','technical_report']) on conflict(id) do update set fields=excluded.fields,document_slots=excluded.document_slots,required_documents=excluded.required_documents;
insert into marketplace_private.tokenization_asset_types(id,fields,document_slots,required_documents) values('wind_farm','[{"key":"title","type":"text","required":true,"maxLength":120},{"key":"owner_name","type":"text","required":true,"maxLength":160},{"key":"owner_type","type":"select","required":true,"maxLength":500,"options":["owner","company"]},{"key":"country","type":"text","required":true,"maxLength":80},{"key":"city","type":"text","required":true,"maxLength":100},{"key":"address","type":"textarea","required":true,"maxLength":600},{"key":"summary","type":"textarea","required":true,"maxLength":4000},{"key":"estimated_value_rial","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":24,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"capacity_mw","type":"number","required":true,"maxLength":256,"maxIntegerDigits":18,"maxFractionDigits":6,"min":"0","exclusiveMin":true},{"key":"turbine_count","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":6,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"wind_resource","type":"textarea","required":true,"maxLength":1800},{"key":"land_rights","type":"textarea","required":true,"maxLength":1400},{"key":"power_contract","type":"textarea","required":true,"maxLength":1800}]'::jsonb,ARRAY['ownership_evidence','owner_identity','valuation_report','representation_authority','wind_study','energy_license','grid_documents'],ARRAY['ownership_evidence','owner_identity','wind_study']) on conflict(id) do update set fields=excluded.fields,document_slots=excluded.document_slots,required_documents=excluded.required_documents;
insert into marketplace_private.tokenization_asset_types(id,fields,document_slots,required_documents) values('infrastructure','[{"key":"title","type":"text","required":true,"maxLength":120},{"key":"owner_name","type":"text","required":true,"maxLength":160},{"key":"owner_type","type":"select","required":true,"maxLength":500,"options":["owner","company"]},{"key":"country","type":"text","required":true,"maxLength":80},{"key":"city","type":"text","required":true,"maxLength":100},{"key":"address","type":"textarea","required":true,"maxLength":600},{"key":"summary","type":"textarea","required":true,"maxLength":4000},{"key":"estimated_value_rial","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":24,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"infrastructure_type","type":"text","required":true,"maxLength":200},{"key":"operating_rights","type":"textarea","required":true,"maxLength":1800},{"key":"revenue_model","type":"textarea","required":true,"maxLength":2200},{"key":"safety_status","type":"textarea","required":true,"maxLength":1800}]'::jsonb,ARRAY['ownership_evidence','owner_identity','valuation_report','representation_authority','operating_contract','technical_plan','safety_documents'],ARRAY['ownership_evidence','owner_identity','operating_contract','technical_plan']) on conflict(id) do update set fields=excluded.fields,document_slots=excluded.document_slots,required_documents=excluded.required_documents;
insert into marketplace_private.tokenization_asset_types(id,fields,document_slots,required_documents) values('mining_project','[{"key":"title","type":"text","required":true,"maxLength":120},{"key":"owner_name","type":"text","required":true,"maxLength":160},{"key":"owner_type","type":"select","required":true,"maxLength":500,"options":["owner","company"]},{"key":"country","type":"text","required":true,"maxLength":80},{"key":"city","type":"text","required":true,"maxLength":100},{"key":"address","type":"textarea","required":true,"maxLength":600},{"key":"summary","type":"textarea","required":true,"maxLength":4000},{"key":"estimated_value_rial","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":24,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"mineral_type","type":"text","required":true,"maxLength":160},{"key":"permit_scope","type":"textarea","required":true,"maxLength":2000},{"key":"resource_estimate","type":"textarea","required":true,"maxLength":2000},{"key":"operating_stage","type":"textarea","required":true,"maxLength":1800}]'::jsonb,ARRAY['ownership_evidence','owner_identity','valuation_report','representation_authority','mining_rights','geological_report','environmental_documents'],ARRAY['ownership_evidence','owner_identity','mining_rights','geological_report']) on conflict(id) do update set fields=excluded.fields,document_slots=excluded.document_slots,required_documents=excluded.required_documents;
insert into marketplace_private.tokenization_asset_types(id,fields,document_slots,required_documents) values('startup','[{"key":"title","type":"text","required":true,"maxLength":120},{"key":"owner_name","type":"text","required":true,"maxLength":160},{"key":"owner_type","type":"select","required":true,"maxLength":500,"options":["owner","company"]},{"key":"country","type":"text","required":true,"maxLength":80},{"key":"city","type":"text","required":true,"maxLength":100},{"key":"address","type":"textarea","required":true,"maxLength":600},{"key":"summary","type":"textarea","required":true,"maxLength":4000},{"key":"estimated_value_rial","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":24,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"business_model","type":"textarea","required":true,"maxLength":2500},{"key":"incorporation_details","type":"textarea","required":true,"maxLength":1800},{"key":"ownership_structure","type":"textarea","required":true,"maxLength":2500},{"key":"traction","type":"textarea","required":true,"maxLength":2000},{"key":"funding_use","type":"textarea","required":true,"maxLength":2000}]'::jsonb,ARRAY['ownership_evidence','owner_identity','valuation_report','representation_authority','pitch_deck','corporate_documents','financial_statements'],ARRAY['ownership_evidence','owner_identity','pitch_deck','corporate_documents']) on conflict(id) do update set fields=excluded.fields,document_slots=excluded.document_slots,required_documents=excluded.required_documents;
insert into marketplace_private.tokenization_asset_types(id,fields,document_slots,required_documents) values('company_shares','[{"key":"title","type":"text","required":true,"maxLength":120},{"key":"owner_name","type":"text","required":true,"maxLength":160},{"key":"owner_type","type":"select","required":true,"maxLength":500,"options":["owner","company"]},{"key":"country","type":"text","required":true,"maxLength":80},{"key":"city","type":"text","required":true,"maxLength":100},{"key":"address","type":"textarea","required":true,"maxLength":600},{"key":"summary","type":"textarea","required":true,"maxLength":4000},{"key":"estimated_value_rial","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":24,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"registration_number","type":"text","required":true,"maxLength":120},{"key":"share_class","type":"text","required":true,"maxLength":300},{"key":"shares_count","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":18,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"transfer_restrictions","type":"textarea","required":true,"maxLength":2200},{"key":"ownership_structure","type":"textarea","required":true,"maxLength":2200}]'::jsonb,ARRAY['ownership_evidence','owner_identity','valuation_report','representation_authority','corporate_documents','share_register','financial_statements','board_authorization'],ARRAY['ownership_evidence','owner_identity','corporate_documents','share_register','financial_statements']) on conflict(id) do update set fields=excluded.fields,document_slots=excluded.document_slots,required_documents=excluded.required_documents;
insert into marketplace_private.tokenization_asset_types(id,fields,document_slots,required_documents) values('intellectual_property','[{"key":"title","type":"text","required":true,"maxLength":120},{"key":"owner_name","type":"text","required":true,"maxLength":160},{"key":"owner_type","type":"select","required":true,"maxLength":500,"options":["owner","company"]},{"key":"country","type":"text","required":true,"maxLength":80},{"key":"city","type":"text","required":true,"maxLength":100},{"key":"address","type":"textarea","required":true,"maxLength":600},{"key":"summary","type":"textarea","required":true,"maxLength":4000},{"key":"estimated_value_rial","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":24,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"right_type","type":"text","required":true,"maxLength":200},{"key":"right_identifier","type":"text","required":true,"maxLength":250},{"key":"territory","type":"text","required":true,"maxLength":300},{"key":"rights_scope","type":"textarea","required":true,"maxLength":2500},{"key":"license_obligations","type":"textarea","required":true,"maxLength":2000}]'::jsonb,ARRAY['ownership_evidence','owner_identity','valuation_report','representation_authority','ip_evidence','rights_chain','license_contracts'],ARRAY['ownership_evidence','owner_identity','ip_evidence','rights_chain']) on conflict(id) do update set fields=excluded.fields,document_slots=excluded.document_slots,required_documents=excluded.required_documents;
insert into marketplace_private.tokenization_asset_types(id,fields,document_slots,required_documents) values('patent','[{"key":"title","type":"text","required":true,"maxLength":120},{"key":"owner_name","type":"text","required":true,"maxLength":160},{"key":"owner_type","type":"select","required":true,"maxLength":500,"options":["owner","company"]},{"key":"country","type":"text","required":true,"maxLength":80},{"key":"city","type":"text","required":true,"maxLength":100},{"key":"address","type":"textarea","required":true,"maxLength":600},{"key":"summary","type":"textarea","required":true,"maxLength":4000},{"key":"estimated_value_rial","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":24,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"patent_number","type":"text","required":true,"maxLength":160},{"key":"patent_jurisdiction","type":"text","required":true,"maxLength":300},{"key":"filing_date","type":"date","required":true,"maxLength":500},{"key":"patent_status","type":"select","required":true,"maxLength":500,"options":["pending","granted","expired","other"]},{"key":"claims_scope","type":"textarea","required":true,"maxLength":2500}]'::jsonb,ARRAY['ownership_evidence','owner_identity','valuation_report','representation_authority','patent_record','claims_documents','assignments'],ARRAY['ownership_evidence','owner_identity','patent_record','claims_documents']) on conflict(id) do update set fields=excluded.fields,document_slots=excluded.document_slots,required_documents=excluded.required_documents;
insert into marketplace_private.tokenization_asset_types(id,fields,document_slots,required_documents) values('brand','[{"key":"title","type":"text","required":true,"maxLength":120},{"key":"owner_name","type":"text","required":true,"maxLength":160},{"key":"owner_type","type":"select","required":true,"maxLength":500,"options":["owner","company"]},{"key":"country","type":"text","required":true,"maxLength":80},{"key":"city","type":"text","required":true,"maxLength":100},{"key":"address","type":"textarea","required":true,"maxLength":600},{"key":"summary","type":"textarea","required":true,"maxLength":4000},{"key":"estimated_value_rial","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":24,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"brand_name","type":"text","required":true,"maxLength":160},{"key":"registration_details","type":"textarea","required":true,"maxLength":1600},{"key":"registered_classes","type":"text","required":true,"maxLength":600},{"key":"license_obligations","type":"textarea","required":true,"maxLength":2200}]'::jsonb,ARRAY['ownership_evidence','owner_identity','valuation_report','representation_authority','trademark_record','brand_usage','license_contracts'],ARRAY['ownership_evidence','owner_identity','trademark_record','brand_usage']) on conflict(id) do update set fields=excluded.fields,document_slots=excluded.document_slots,required_documents=excluded.required_documents;
insert into marketplace_private.tokenization_asset_types(id,fields,document_slots,required_documents) values('software','[{"key":"title","type":"text","required":true,"maxLength":120},{"key":"owner_name","type":"text","required":true,"maxLength":160},{"key":"owner_type","type":"select","required":true,"maxLength":500,"options":["owner","company"]},{"key":"country","type":"text","required":true,"maxLength":80},{"key":"city","type":"text","required":true,"maxLength":100},{"key":"address","type":"textarea","required":true,"maxLength":600},{"key":"summary","type":"textarea","required":true,"maxLength":4000},{"key":"estimated_value_rial","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":24,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"product_name","type":"text","required":true,"maxLength":160},{"key":"software_rights","type":"textarea","required":true,"maxLength":2200},{"key":"license_dependencies","type":"textarea","required":true,"maxLength":2200},{"key":"development_stage","type":"text","required":true,"maxLength":300},{"key":"revenue_model","type":"textarea","required":true,"maxLength":2200}]'::jsonb,ARRAY['ownership_evidence','owner_identity','valuation_report','representation_authority','software_ownership','technical_description','licenses','revenue_contracts'],ARRAY['ownership_evidence','owner_identity','software_ownership','technical_description','licenses']) on conflict(id) do update set fields=excluded.fields,document_slots=excluded.document_slots,required_documents=excluded.required_documents;
insert into marketplace_private.tokenization_asset_types(id,fields,document_slots,required_documents) values('royalties','[{"key":"title","type":"text","required":true,"maxLength":120},{"key":"owner_name","type":"text","required":true,"maxLength":160},{"key":"owner_type","type":"select","required":true,"maxLength":500,"options":["owner","company"]},{"key":"country","type":"text","required":true,"maxLength":80},{"key":"city","type":"text","required":true,"maxLength":100},{"key":"address","type":"textarea","required":true,"maxLength":600},{"key":"summary","type":"textarea","required":true,"maxLength":4000},{"key":"estimated_value_rial","type":"number","required":true,"maxLength":256,"integer":true,"maxIntegerDigits":24,"maxFractionDigits":0,"min":"0","exclusiveMin":true},{"key":"underlying_work","type":"text","required":true,"maxLength":250},{"key":"revenue_source","type":"textarea","required":true,"maxLength":2000},{"key":"revenue_share_percent","type":"number","required":true,"maxLength":256,"maxIntegerDigits":3,"maxFractionDigits":2,"min":"0","max":"100","exclusiveMin":true},{"key":"contract_term","type":"textarea","required":true,"maxLength":1800},{"key":"existing_obligations","type":"textarea","required":true,"maxLength":2000}]'::jsonb,ARRAY['ownership_evidence','owner_identity','valuation_report','representation_authority','royalty_contract','rights_chain','revenue_history'],ARRAY['ownership_evidence','owner_identity','royalty_contract','rights_chain']) on conflict(id) do update set fields=excluded.fields,document_slots=excluded.document_slots,required_documents=excluded.required_documents;

-- Explicit grants: no private tables or helper functions are callable by visitors.
do $$declare f record;begin
 for f in select n.nspname,p.proname,pg_get_function_identity_arguments(p.oid) args from pg_proc p join pg_namespace n on n.oid=p.pronamespace where (n.nspname='marketplace_private' and p.proname like 'tokenization_%') or (n.nspname='public' and p.proname like 'marketplace_tokenization_%') loop
  execute format('revoke all on function %I.%I(%s) from public,anon,authenticated',f.nspname,f.proname,f.args);
  if f.nspname='public' then
   if f.proname like 'marketplace_tokenization_issuance_%' then execute format('grant execute on function %I.%I(%s) to service_role',f.nspname,f.proname,f.args);
   else execute format('grant execute on function %I.%I(%s) to authenticated',f.nspname,f.proname,f.args);end if;
  end if;
 end loop;
end$$;
notify pgrst,'reload schema';
commit;