-- Read-only verification after 202609240001_asset_tokenization.sql.
-- Reads schema metadata and the static asset-type/bucket configuration only.
-- Does not read requests, documents, users, notes, balances or credentials.
with
expected_tables(name) as (values
 ('tokenization_asset_types'),('tokenization_requests'),('tokenization_documents'),
 ('tokenization_internal_notes'),('tokenization_proposals'),
 ('tokenization_issuance_jobs'),('tokenization_allocation_ledger')
),
table_checks as (
 select e.name,c.oid is not null as exists,
 coalesce(c.relrowsecurity,false) as rls_enabled,
 coalesce(not has_table_privilege('anon',c.oid,'SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER')
   and not has_table_privilege('authenticated',c.oid,'SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER'),false) as browser_table_access_denied
 from expected_tables e left join pg_namespace n on n.nspname='marketplace_private'
 left join pg_class c on c.relnamespace=n.oid and c.relname=e.name and c.relkind='r'
),
expected_rpcs(signature,authenticated_expected,service_only) as (values
 ('public.marketplace_tokenization_state(boolean)',true,false),
 ('public.marketplace_tokenization_customer(text,jsonb)',true,false),
 ('public.marketplace_tokenization_admin(text,jsonb)',true,false),
 ('public.marketplace_tokenization_upload_slot(uuid,integer,text,text,text,integer)',true,false),
 ('public.marketplace_tokenization_upload_complete(uuid,integer,uuid)',true,false),
 ('public.marketplace_tokenization_upload_abort(uuid,uuid)',true,false),
 ('public.marketplace_tokenization_document(uuid,uuid,boolean)',true,false),
 ('public.marketplace_tokenization_storage(text,text)',true,false),
 ('public.marketplace_tokenization_issuance_job(uuid)',false,true),
 ('public.marketplace_tokenization_issuance_bind(uuid,integer,text,text,text)',false,true),
 ('public.marketplace_tokenization_issuance_finalize(uuid,integer,jsonb)',false,true)
),
rpc_checks as (
 select e.signature,p.oid is not null as exists,
 coalesce(p.prosecdef,false) as security_definer,
 coalesce(has_function_privilege('authenticated',p.oid,'EXECUTE')=e.authenticated_expected
   and not has_function_privilege('anon',p.oid,'EXECUTE')
   and (not e.service_only or has_function_privilege('service_role',p.oid,'EXECUTE')),false) as grants_correct
 from expected_rpcs e left join pg_proc p on p.oid=to_regprocedure(e.signature)
)
select jsonb_build_object(
 'tables',(select jsonb_agg(to_jsonb(t) order by name) from table_checks t),
 'seven_tables_protected',(select count(*)=7 and bool_and(exists and rls_enabled and browser_table_access_denied) from table_checks),
 'asset_type_count',(select count(*) from marketplace_private.tokenization_asset_types),
 'twenty_asset_types',(select count(*)=20 from marketplace_private.tokenization_asset_types),
 'private_bucket_ready',exists(select 1 from storage.buckets where id='tokenization-documents' and public=false and file_size_limit=10485760
   and allowed_mime_types @> array['application/pdf','image/jpeg','image/png']::text[]
   and allowed_mime_types <@ array['application/pdf','image/jpeg','image/png']::text[]),
 'storage_policies_ready',(select count(*)=3 from pg_policies where schemaname='storage' and tablename='objects'
   and policyname in('tokenization_document_read','tokenization_document_insert','tokenization_document_delete') and roles=array['authenticated']::name[]),
 'rpcs',(select jsonb_agg(to_jsonb(r) order by signature) from rpc_checks r),
 'eleven_rpc_grants_correct',(select count(*)=11 and bool_and(exists and security_definer and grants_correct) from rpc_checks),
 'private_helpers_denied',not exists(select 1 from pg_proc p join pg_namespace n on n.oid=p.pronamespace
   where n.nspname='marketplace_private' and p.proname like 'tokenization_%'
   and (has_function_privilege('authenticated',p.oid,'EXECUTE') or has_function_privilege('anon',p.oid,'EXECUTE')))
) as tokenization_verification;