-- Persistent customer activity and protected document history. Reviews never reverse transactions or grant KYC.
begin;
create table if not exists marketplace_private.customer_activity(
 id bigint generated always as identity primary key,user_id uuid not null references auth.users(id) on delete cascade,
 category text not null,action text not null,entity text not null,entity_id text,
 before_data jsonb not null default '{}',after_data jsonb not null default '{}',changed_fields text[] not null default '{}',
 actor_id uuid,actor_kind text not null check(actor_kind in('customer','admin','system')),
 source text not null default 'database',source_key text unique,created_at timestamptz not null default now()
);
create table if not exists marketplace_private.customer_activity_rollout(id boolean primary key default true check(id),started_at timestamptz not null default now());
insert into marketplace_private.customer_activity_rollout(id) values(true) on conflict(id) do nothing;
create index if not exists customer_activity_owner_cursor on marketplace_private.customer_activity(user_id,id desc);
create table if not exists marketplace_private.customer_activity_reviews(
 activity_id bigint primary key references marketplace_private.customer_activity(id) on delete cascade,
 status text not null check(status in('approved','rejected')),version integer not null check(version>0),
 note text not null check(length(trim(note)) between 3 and 2000),reviewed_by uuid not null references auth.users(id),reviewed_at timestamptz not null default now()
);
create table if not exists marketplace_private.customer_activity_review_history(
 id bigint generated always as identity primary key,activity_id bigint not null references marketplace_private.customer_activity(id) on delete cascade,
 status text not null check(status in('approved','rejected')),version integer not null,note text not null,
 reviewed_by uuid not null references auth.users(id),reviewed_at timestamptz not null default now(),unique(activity_id,version)
);
create table if not exists marketplace_private.customer_document_registry(
 id uuid primary key,user_id uuid not null references auth.users(id) on delete cascade,
 source text not null check(source in('identity','tokenization','avatar')),request_id uuid,kind text not null,
 bucket text not null,path text not null,name text not null,mime text,size bigint,
 available boolean not null default true,created_at timestamptz not null default now(),removed_at timestamptz,
 unique(bucket,path)
);
do $$declare t text;begin
 foreach t in array array['customer_activity_rollout','customer_activity','customer_activity_reviews','customer_activity_review_history','customer_document_registry'] 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,service_role',t);
  execute format('grant select on marketplace_private.%I to service_role',t);
 end loop;
end$$;

-- Explicit field allowlists keep credentials, challenges, provider payloads and file bytes outside the audit stream.
create or replace function marketplace_private.customer_activity_safe(p_entity text,p_row jsonb) returns jsonb language plpgsql stable security definer set search_path='' as $$
declare keys text[]; result jsonb; permitted jsonb;
begin
 keys:=case p_entity
 when 'customer_profiles' then array['username','first_name','last_name']
 when 'accounts' then array['first_name','last_name','birth_date','national_id_last4','national_id','verification_status','account_level','identity_verified_at','avatar_path']
 when 'preferences' then array['locale','currency','theme','hide_balances','hide_small_assets','email_notifications','confirm_orders','confirm_cancellations','reduced_motion']
 when 'bank_accounts' then array['bank_name','iban','card_last4','account_title','status','is_primary','ownership_checked_at','ownership_national_id','ownership_reference','card_link_status']
 when 'identity_requests' then array['status','first_name','last_name','phone','national_id','birth_certificate_number','father_name','address','postal_code','card_path','selfie_path','consent_at','submitted_at','reviewed_at','review_note','review_checks','verified_at','verification_type','review_stage','verification_level','final_approved_level','final_decided_by','final_decided_at','final_review_note']
 when 'identity_inquiries' then array['request_id','kind','bank_id','bank_iban','status','checked_at','error_code']
 when 'tokenization_requests' then array['asset_type','fields','status','terms_version','consented_at','submitted_at','accepted_at','reviews','message','tokenomics','proposal_version','accepted_proposal_version']
 when 'tokenization_documents' then array['request_id','slot','name','mime','size','path','upload_state','status','message','reviewed_at']
 when 'wallet_accounts' then array['network','address','label','wallet_type','verified_at','chain_id']
 when 'orders' then array['project_id','side','quantity','unit_price_rial','status','reference']
 when 'transactions' then array['reference','kind','asset_symbol','amount','status','network','tx_hash','completed_at']
 when 'asset_positions' then array['asset_symbol','project_id','quantity','locked_quantity','value_rial','value_usdt']
 when 'credit_positions' then array['kind','principal_rial','outstanding_rial','status','due_at']
 when 'staking_positions' then array['asset_symbol','quantity','accrued_reward','status','starts_at','ends_at']
 when 'notifications' then array['title','body','kind','severity','read_at','sent_by','sender_name','request_id']
 when 'price_alerts' then array['project_id','direction','target_price_rial','status','triggered_at']
 when 'referrals' then array['code_id','invitee_id','status','reward_rial']
 when 'referral_codes' then array['code','label','active']
 when 'customer_api_keys' then array['label','key_prefix','scopes','expires_at','revoked_at']
 when 'fiat_payment_intents' then array['amount_rial','provider','test_mode','status','transaction_id','credited_at']
 when 'fiat_deposit_ledger' then array['payment_id','amount_rial','transaction_id']
 when 'tokenization_issuance_jobs' then array['request_id','proposal_version','status','expected_chain_id','expected_contract','expected_recipient']
 when 'tokenization_allocation_ledger' then array['request_id','job_id','position_id','transaction_id','proposal_version','chain_id','contract_address','transaction_hash','log_index','quantity']
 when 'session_activity' then array['device_label','last_seen_at','revoked_at']
 when 'auth.users' then array['email','phone','email_confirmed_at','phone_confirmed_at']
 else array[]::text[] end;
 select coalesce(jsonb_object_agg(k,v),'{}') into result from jsonb_each(coalesce(p_row,'{}')) x(k,v) where k=any(keys);
 if p_entity='tokenization_requests' and result ? 'fields' then
  select coalesce(jsonb_object_agg(f.key,f.value),'{}') into permitted from jsonb_each(coalesce(result->'fields','{}')) f where exists(
   select 1 from marketplace_private.tokenization_asset_types a,jsonb_array_elements(a.fields) d where a.id=p_row->>'asset_type' and d->>'key'=f.key);
  result:=jsonb_set(result,'{fields}',permitted);
 end if;
 return result;
end$$;

create or replace function marketplace_private.customer_activity_capture() returns trigger language plpgsql security definer set search_path='' as $$
declare b jsonb:='{}';a jsonb:='{}';row_data jsonb;uid uuid;rid text;before_diff jsonb;after_diff jsonb;changed text[];cat text:=tg_argv[0];who uuid:=auth.uid();
begin
 if tg_op<>'INSERT' then b:=marketplace_private.customer_activity_safe(tg_table_name,to_jsonb(old));end if;
 if tg_op<>'DELETE' then a:=marketplace_private.customer_activity_safe(tg_table_name,to_jsonb(new));end if;
 row_data:=case when tg_op='DELETE' then to_jsonb(old) else to_jsonb(new) end;
 uid:=nullif(row_data->>'user_id','')::uuid;
 if uid is null and tg_table_name='identity_inquiries' then select user_id into uid from marketplace_private.identity_requests where id::text=row_data->>'request_id';end if;
 if uid is null and tg_table_name='tokenization_documents' then select user_id into uid from marketplace_private.tokenization_requests where id::text=row_data->>'request_id';end if;
 if uid is null or not exists(select 1 from auth.users where id=uid) then return null;end if;
 select coalesce(array_agg(k order by k),'{}'),coalesce(jsonb_object_agg(k,b->k) filter(where b ? k),'{}'),coalesce(jsonb_object_agg(k,a->k) filter(where a ? k),'{}')
 into changed,before_diff,after_diff from(select jsonb_object_keys(b||a) k)x where b->k is distinct from a->k;
 if cardinality(changed)=0 then return null;end if;
 rid:=coalesce(row_data->>'id',row_data->>'request_id',uid::text);
 insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,before_data,after_data,changed_fields,actor_id,actor_kind)
 values(uid,cat,lower(tg_op)||case tg_op when 'INSERT' then 'ed' when 'UPDATE' then 'd' else 'd' end,tg_table_name,rid,before_diff,after_diff,changed,who,case when who=uid then 'customer' when public.marketplace_role()='owner' then 'admin' else 'system' end);
 return null;
end$$;
do $$declare entry text[];begin
 foreach entry slice 1 in array array[
 ['customer_profiles','profile'],['accounts','profile'],['preferences','preferences'],['bank_accounts','bank'],['identity_requests','identity'],['identity_inquiries','identity'],
 ['tokenization_requests','tokenization'],['tokenization_documents','documents'],['tokenization_issuance_jobs','tokenization'],['tokenization_allocation_ledger','tokenization'],
 ['wallet_accounts','wallet'],['asset_positions','wallet'],['transactions','wallet'],['orders','trade'],['credit_positions','credit'],['staking_positions','staking'],
 ['notifications','notifications'],['price_alerts','trade'],['referrals','referrals'],['referral_codes','referrals'],['customer_api_keys','security'],['session_activity','session'],['fiat_payment_intents','wallet'],['fiat_deposit_ledger','wallet']
 ] loop
  if to_regclass('marketplace_private.'||entry[1]) is not null then
   execute format('drop trigger if exists customer_activity_capture on marketplace_private.%I',entry[1]);
   execute format('create trigger customer_activity_capture after insert or update or delete on marketplace_private.%I for each row execute function marketplace_private.customer_activity_capture(%L)',entry[1],entry[2]);
  end if;
 end loop;
end$$;

-- Only successful persisted auth changes are captured; there is no callable login/logout event-forging RPC.
create or replace function marketplace_private.customer_auth_activity_capture() returns trigger language plpgsql security definer set search_path='' as $$
declare a jsonb:=to_jsonb(new);b jsonb:=to_jsonb(old);changed text[];safe_a jsonb;safe_b jsonb;
begin
 if not exists(select 1 from marketplace_private.customer_profiles where user_id=new.id) then return null;end if;
 safe_a:=marketplace_private.customer_activity_safe('auth.users',a);safe_b:=marketplace_private.customer_activity_safe('auth.users',b);
 select coalesce(array_agg(k order by k),'{}'),coalesce(jsonb_object_agg(k,safe_b->k),'{}'),coalesce(jsonb_object_agg(k,safe_a->k),'{}') into changed,safe_b,safe_a
 from(select jsonb_object_keys(safe_b||safe_a) k)x where safe_b->k is distinct from safe_a->k;
 if cardinality(changed)>0 then
  insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,before_data,after_data,changed_fields,actor_id,actor_kind)
  values(new.id,'profile','updated','auth.users',new.id::text,safe_b,safe_a,changed,auth.uid(),case when auth.uid()=new.id then 'customer' else 'system' end);
 end if;
 if a->>'encrypted_password' is distinct from b->>'encrypted_password' then
  insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,changed_fields,actor_id,actor_kind)
  values(new.id,'security','password_changed','auth.users',new.id::text,array['password'],auth.uid(),case when auth.uid()=new.id then 'customer' else 'system' end);
 end if;
 if a->>'last_sign_in_at' is not null and a->>'last_sign_in_at' is distinct from b->>'last_sign_in_at' then
  insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,actor_id,actor_kind,source_key)
  values(new.id,'session','login','auth.users',new.id::text,new.id,'customer','auth-login:'||new.id||':'||(a->>'last_sign_in_at')) on conflict(source_key) do nothing;
 end if;
 return null;
end$$;
drop trigger if exists customer_auth_activity_capture on auth.users;
create trigger customer_auth_activity_capture after update on auth.users for each row execute function marketplace_private.customer_auth_activity_capture();
create or replace function marketplace_private.customer_auth_logout_capture() returns trigger language plpgsql security definer set search_path='' as $$
declare row_data jsonb:=to_jsonb(new);payload jsonb:=to_jsonb(new)->'payload';uid uuid;
begin
 if payload->>'action' is distinct from 'logout' or coalesce(payload->>'actor_id','') !~ '^[0-9a-fA-F-]{36}$' then return null;end if;
 begin uid:=(payload->>'actor_id')::uuid;exception when invalid_text_representation then return null;end;
 if not exists(select 1 from marketplace_private.customer_profiles where user_id=uid) then return null;end if;
 insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,actor_id,actor_kind,source_key)
 values(uid,'session','logout','auth.audit_log_entries',row_data->>'id',uid,'customer','auth-logout:'||(row_data->>'id')) on conflict(source_key) do nothing;
 return null;
end$$;
do $$begin
 if to_regclass('auth.audit_log_entries') is not null then
  execute 'drop trigger if exists customer_auth_logout_capture on auth.audit_log_entries';
  execute 'create trigger customer_auth_logout_capture after insert on auth.audit_log_entries for each row execute function marketplace_private.customer_auth_logout_capture()';
 end if;
end$$;

-- Historical uploads are metadata only. Replacement/deletion keeps an audit record, never a downloadable deleted file.
create or replace function marketplace_private.customer_document_register(p_object jsonb,p_removed boolean default false) returns void language plpgsql security definer set search_path='' as $$
declare uid uuid;req uuid;src text;kind text;label text;bucket text:=p_object->>'bucket_id';document_path text:=p_object->>'name';oid uuid:=(p_object->>'id')::uuid;mime text;size_value bigint;
begin
 if bucket='identity-documents' and document_path ~ '^[0-9a-f-]{36}/[0-9a-f-]{36}/(card|selfie)-[0-9a-f-]{36}\.(jpg|png|webp)$' then
  select user_id,id into uid,req from marketplace_private.identity_requests where id::text=split_part(document_path,'/',2) and user_id::text=split_part(document_path,'/',1);
  src:='identity';kind:=split_part(split_part(document_path,'/',3),'-',1);label:=case kind when 'card' then 'کارت ملی' else 'تصویر چهره' end;
 elsif bucket='tokenization-documents' then
  select r.user_id,r.id,d.slot,d.name,d.mime,d.size into uid,req,kind,label,mime,size_value from marketplace_private.tokenization_documents d join marketplace_private.tokenization_requests r on r.id=d.request_id where d.path=document_path;
  src:='tokenization';
 elsif bucket='customer-avatars' and document_path ~ '^[0-9a-f-]{36}/[0-9a-f-]{36}\.(jpg|png|webp)$' then
  select user_id into uid from marketplace_private.customer_profiles where user_id::text=split_part(document_path,'/',1);
  src:='avatar';kind:='avatar';label:='تصویر پروفایل';
 else return;end if;
 if uid is null or not exists(select 1 from auth.users where id=uid) then return;end if;
 -- Storage moves retain their object id; each historical path keeps its own document record.
 if exists(select 1 from marketplace_private.customer_document_registry where id=oid and (customer_document_registry.bucket<>(p_object->>'bucket_id') or customer_document_registry.path<>document_path)) then oid:=gen_random_uuid();end if;
 mime:=coalesce(mime,p_object->'metadata'->>'mimetype',case lower(right(document_path,4)) when '.png' then 'image/png' when 'webp' then 'image/webp' when '.jpg' then 'image/jpeg' end);
 if size_value is null and coalesce(p_object->'metadata'->>'size','')~'^[0-9]{1,15}$' then size_value:=(p_object->'metadata'->>'size')::bigint;end if;
 insert into marketplace_private.customer_document_registry(id,user_id,source,request_id,kind,bucket,path,name,mime,size,available,created_at,removed_at)
 values(oid,uid,src,req,kind,bucket,document_path,label,mime,size_value,not p_removed,coalesce(nullif(p_object->>'created_at','')::timestamptz,now()),case when p_removed then now() end)
 on conflict on constraint customer_document_registry_bucket_path_key do update set available=excluded.available,removed_at=excluded.removed_at,mime=coalesce(excluded.mime,customer_document_registry.mime),size=coalesce(excluded.size,customer_document_registry.size);
end$$;
create or replace function marketplace_private.customer_document_capture() returns trigger language plpgsql security definer set search_path='' as $$
declare row_data jsonb:=case when tg_op='DELETE' then to_jsonb(old) else to_jsonb(new) end;d marketplace_private.customer_document_registry;event_data jsonb;
begin
 if tg_op='UPDATE' and to_jsonb(old)->'metadata' is not distinct from row_data->'metadata' and to_jsonb(old)->>'name' is not distinct from row_data->>'name' then return null;end if;
 if tg_op='UPDATE' and (to_jsonb(old)->>'name' is distinct from row_data->>'name' or to_jsonb(old)->>'bucket_id' is distinct from row_data->>'bucket_id') then
  perform marketplace_private.customer_document_register(to_jsonb(old),true);
 end if;
 perform marketplace_private.customer_document_register(row_data,tg_op='DELETE');
 select * into d from marketplace_private.customer_document_registry where bucket=row_data->>'bucket_id' and path=row_data->>'name';
 if d.id is null then return null;end if;
 event_data:=jsonb_build_object('document_id','storage:'||d.id,'source',d.source,'request_id',d.request_id,'kind',d.kind,'name',d.name,'mime',d.mime,'size',d.size,'available',d.available);
 insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,before_data,after_data,changed_fields,actor_id,actor_kind)
 values(d.user_id,'documents',case when tg_op='DELETE' then 'deleted' when tg_op='INSERT' then 'uploaded' else 'updated' end,'storage.objects',d.id::text,case when tg_op='DELETE' then event_data||'{"available":true}'::jsonb else '{}' end,event_data,array['document'],auth.uid(),case when auth.uid()=d.user_id then 'customer' when public.marketplace_role()='owner' then 'admin' else 'system' end);
 return null;
end$$;
drop trigger if exists customer_document_capture on storage.objects;
create trigger customer_document_capture after insert or update or delete on storage.objects for each row execute function marketplace_private.customer_document_capture();
do $$declare r record;begin
 for r in select to_jsonb(o) as data from storage.objects o where bucket_id in('identity-documents','tokenization-documents','customer-avatars') loop
  perform marketplace_private.customer_document_register(r.data,false);
 end loop;
end$$;
-- Old dashboard events keep their original timestamps and explicitly lack before/after evidence.
insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,actor_id,actor_kind,source,source_key,created_at)
 select user_id,case when action like '%bank%' then 'bank' when action like '%identity%' then 'identity' when action like '%notification%' then 'notifications' when action like '%wallet%' then 'wallet' when action like '%order%' then 'trade' when action like '%preference%' then 'preferences' else 'profile' end,action,'dashboard_audit',entity_id::text,user_id,'customer','legacy','dashboard-audit:'||id,created_at from marketplace_private.dashboard_audit where created_at <= (select started_at from marketplace_private.customer_activity_rollout where id)
 on conflict(source_key) do nothing;

create or replace function marketplace_private.customer_activity_reviewable(p_entity text,p_action text,p_actor_kind text) returns boolean language sql immutable set search_path='' as $$
 select coalesce(p_actor_kind<>'admin',false) and not(p_entity='notifications' and p_action='inserted');
$$;
create or replace function marketplace_private.customer_activity_json(p_id bigint) returns jsonb language sql stable security definer set search_path='' as $$
 select jsonb_build_object('id',e.id::text,'user_id',e.user_id,'category',e.category,'action',e.action,'entity',e.entity,'entity_id',e.entity_id,'before',e.before_data,'after',e.after_data,'changed_fields',e.changed_fields,'created_at',e.created_at,'actor_id',e.actor_id,'actor_kind',e.actor_kind,'source',e.source,
 'reviewable',marketplace_private.customer_activity_reviewable(e.entity,e.action,e.actor_kind),'review_status',coalesce(r.status,case when marketplace_private.customer_activity_reviewable(e.entity,e.action,e.actor_kind) then 'pending' else 'recorded' end),'review_version',coalesce(r.version,0),'review_note',r.note,'reviewed_by',r.reviewed_by,'reviewed_at',r.reviewed_at,'review_history',coalesce((select jsonb_agg(jsonb_build_object('status',h.status,'version',h.version,'note',h.note,'reviewed_by',h.reviewed_by,'reviewed_at',h.reviewed_at) order by h.version) from marketplace_private.customer_activity_review_history h where h.activity_id=e.id),'[]'))
 from marketplace_private.customer_activity e left join marketplace_private.customer_activity_reviews r on r.activity_id=e.id where e.id=p_id;
$$;
create or replace function public.marketplace_admin_customer_activity(p_user uuid,p_before bigint default null,p_limit integer default 50,p_category text default null,p_status text default null) returns jsonb language plpgsql stable security definer set search_path='' as $$
declare items jsonb;last_id bigint;total bigint;has_more boolean;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if not exists(select 1 from marketplace_private.customer_profiles where user_id=p_user) then raise exception 'customer_not_found';end if;
 if p_limit is null or p_limit not between 1 and 100 or p_before<=0 or (p_category is not null and p_category not in('profile','preferences','identity','documents','bank','wallet','trade','credit','staking','notifications','referrals','security','tokenization','session')) or(p_status is not null and p_status not in('pending','approved','rejected','recorded')) then raise exception 'invalid_activity_filter';end if;
 select count(*) into total from marketplace_private.customer_activity e left join marketplace_private.customer_activity_reviews r on r.activity_id=e.id where e.user_id=p_user and(p_category is null or e.category=p_category) and(p_status is null or coalesce(r.status,case when marketplace_private.customer_activity_reviewable(e.entity,e.action,e.actor_kind) then 'pending' else 'recorded' end)=p_status);
 select coalesce(jsonb_agg(marketplace_private.customer_activity_json(id) order by id desc),'[]'),min(id) into items,last_id from(
  select e.id from marketplace_private.customer_activity e left join marketplace_private.customer_activity_reviews r on r.activity_id=e.id where e.user_id=p_user and(p_before is null or e.id<p_before) and(p_category is null or e.category=p_category) and(p_status is null or coalesce(r.status,case when marketplace_private.customer_activity_reviewable(e.entity,e.action,e.actor_kind) then 'pending' else 'recorded' end)=p_status) order by e.id desc limit p_limit)x;
 select exists(select 1 from marketplace_private.customer_activity e left join marketplace_private.customer_activity_reviews r on r.activity_id=e.id where e.user_id=p_user and e.id<last_id and(p_category is null or e.category=p_category) and(p_status is null or coalesce(r.status,case when marketplace_private.customer_activity_reviewable(e.entity,e.action,e.actor_kind) then 'pending' else 'recorded' end)=p_status)) into has_more;
 return jsonb_build_object('items',items,'total',total,'next_cursor',case when has_more then last_id::text end);
end$$;
create or replace function public.marketplace_admin_customer_activity_review(p_user uuid,p_id bigint,p_version integer,p_decision text,p_note text) returns jsonb language plpgsql security definer set search_path='' as $$
declare e marketplace_private.customer_activity;r marketplace_private.customer_activity_reviews;state text;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if p_decision is null or p_decision not in('approve','reject') or p_version is null or p_version<0 or length(trim(coalesce(p_note,''))) not between 3 and 2000 then raise exception 'invalid_activity_review';end if;
 select * into e from marketplace_private.customer_activity where id=p_id and user_id=p_user for update;
 if e.id is null then raise exception 'activity_not_found';end if;
 if not marketplace_private.customer_activity_reviewable(e.entity,e.action,e.actor_kind) then raise exception 'activity_not_reviewable';end if;
 select * into r from marketplace_private.customer_activity_reviews where activity_id=e.id;
 if coalesce(r.version,0)<>p_version then raise exception 'activity_version_conflict';end if;
 state:=case p_decision when 'approve' then 'approved' else 'rejected' end;
 insert into marketplace_private.customer_activity_reviews(activity_id,status,version,note,reviewed_by) values(e.id,state,p_version+1,trim(p_note),auth.uid())
 on conflict(activity_id) do update set status=excluded.status,version=excluded.version,note=excluded.note,reviewed_by=excluded.reviewed_by,reviewed_at=now();
 insert into marketplace_private.customer_activity_review_history(activity_id,status,version,note,reviewed_by) values(e.id,state,p_version+1,trim(p_note),auth.uid());
 insert into marketplace_private.audit_log(actor,entity,record_id,operation) values(auth.uid(),'customer_activity',e.id::text,'review_'||state);
 perform marketplace_private.customer_notify(e.user_id,'activity-review:'||e.id||':'||(p_version+1),case p_decision when 'approve' then 'فعالیت شما بررسی و تأیید شد' else 'فعالیت شما نیاز به بررسی دارد' end,trim(p_note),case p_decision when 'approve' then 'success' else 'warning' end,null,auth.uid(),'تیم پشتیبانی پلانک');
 return jsonb_build_object('activity',marketplace_private.customer_activity_json(e.id));
end$$;

-- Owner/MFA document access is limited to files with evidence of ownership; no caller-provided path is accepted.
create or replace function marketplace_private.admin_customer_document_access(p_bucket text,p_path text) returns boolean language sql stable security definer set search_path='' as $$
 select public.marketplace_role()='owner' and exists(select 1 from marketplace_private.customer_document_registry where bucket=p_bucket and path=p_path and available);
$$;
drop policy if exists admin_customer_documents_read on storage.objects;
create policy admin_customer_documents_read on storage.objects for select to authenticated using(marketplace_private.admin_customer_document_access(bucket_id,name));
create or replace function public.marketplace_admin_customer_document(p_user uuid,p_document text) returns jsonb language plpgsql security definer set search_path='' as $$
declare d marketplace_private.customer_document_registry;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 select * into d from marketplace_private.customer_document_registry where user_id=p_user and 'storage:'||id=p_document and available;
 if d.id is null or not exists(select 1 from storage.objects where bucket_id=d.bucket and name=d.path) then raise exception 'document_not_found';end if;
 insert into marketplace_private.audit_log(actor,entity,record_id,operation) values(auth.uid(),'customer_document',d.id::text,'read');
 return jsonb_build_object('bucket',d.bucket,'path',d.path,'name',d.name,'mime',d.mime,'size',d.size);
end$$;
create or replace function public.marketplace_admin_customer_profile(p_user uuid) returns jsonb language plpgsql stable security definer set search_path='' as $$
declare customer jsonb;account jsonb;prefs jsonb;docs jsonb;timeline jsonb;req uuid;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 select jsonb_build_object('user_id',p.user_id,'username',p.username,'first_name',coalesce(a.first_name,p.first_name),'last_name',coalesce(a.last_name,p.last_name),'email',u.email,'phone',u.phone,'created_at',p.created_at,'email_confirmed_at',u.email_confirmed_at,'phone_confirmed_at',u.phone_confirmed_at,'sandbox_registration',coalesce(u.raw_app_meta_data->>'phone_verification_mode'='sandbox',false)) into customer from marketplace_private.customer_profiles p join auth.users u on u.id=p.user_id left join marketplace_private.accounts a on a.user_id=p.user_id where p.user_id=p_user;
 if customer is null then raise exception 'customer_not_found';end if;
 select marketplace_private.customer_activity_safe('accounts',to_jsonb(a))||jsonb_build_object('user_id',a.user_id,'created_at',a.created_at,'updated_at',a.updated_at) into account from marketplace_private.accounts a where user_id=p_user;
 select marketplace_private.customer_activity_safe('preferences',to_jsonb(p)) into prefs from marketplace_private.preferences p where user_id=p_user;
 select id into req from marketplace_private.identity_requests where user_id=p_user order by created_at desc,id desc limit 1;
 select coalesce(jsonb_agg(jsonb_build_object('id','storage:'||d.id,'source',d.source,'request_id',d.request_id,'kind',d.kind,'name',d.name,'mime',d.mime,'size',d.size,'created_at',d.created_at,'available',d.available and exists(select 1 from storage.objects o where o.bucket_id=d.bucket and o.name=d.path),'status',case when not d.available then 'deleted' when d.source='identity' and d.path is distinct from case d.kind when 'card' then i.card_path when 'selfie' then i.selfie_path end then 'superseded' when d.source='tokenization' and t.upload_state='deleted' then 'superseded' else coalesce(t.status,i.status,'uploaded') end) order by d.created_at desc,d.id),'[]') into docs from marketplace_private.customer_document_registry d left join marketplace_private.tokenization_documents t on d.source='tokenization' and t.path=d.path left join marketplace_private.identity_requests i on d.source='identity' and i.id=d.request_id where d.user_id=p_user;
 timeline:=public.marketplace_admin_customer_activity(p_user);
 return jsonb_build_object('customer',customer,'account',account,'preferences',prefs,'identity_request_id',req,
 'identity_requests',coalesce((select jsonb_agg(jsonb_build_object('id',r.id,'status',r.status,'created_at',r.created_at,'updated_at',r.updated_at,'submitted_at',r.submitted_at,'verification_level',r.verification_level,'review_stage',r.review_stage,'review_note',r.review_note,'version',r.version) order by r.created_at desc,r.id desc) from marketplace_private.identity_requests r where r.user_id=p_user),'[]'),
 'bank_accounts',coalesce((select jsonb_agg(marketplace_private.customer_activity_safe('bank_accounts',to_jsonb(b))||jsonb_build_object('id',b.id,'created_at',b.created_at) order by b.created_at desc,b.id) from marketplace_private.bank_accounts b where b.user_id=p_user),'[]'),
 'wallet_accounts',coalesce((select jsonb_agg(marketplace_private.customer_activity_safe('wallet_accounts',to_jsonb(w))||jsonb_build_object('id',w.id,'created_at',w.created_at) order by w.created_at desc,w.id) from marketplace_private.wallet_accounts w where w.user_id=p_user),'[]'),
 'documents',docs,'activity',timeline,'summary',jsonb_build_object('activities',timeline->'total','pending_reviews',(select count(*) from marketplace_private.customer_activity e where e.user_id=p_user and marketplace_private.customer_activity_reviewable(e.entity,e.action,e.actor_kind) and not exists(select 1 from marketplace_private.customer_activity_reviews r where r.activity_id=e.id)),'documents',jsonb_array_length(docs)));
end$$;

do $$declare f record;begin
 for f in select p.oid::regprocedure as signature from pg_proc p join pg_namespace n on n.oid=p.pronamespace where n.nspname='marketplace_private' and p.proname in('customer_activity_safe','customer_activity_capture','customer_auth_activity_capture','customer_auth_logout_capture','customer_document_register','customer_document_capture','customer_activity_json','customer_activity_reviewable','admin_customer_document_access') loop
  execute format('revoke all on function %s from public,anon,authenticated',f.signature);
 end loop;
end$$;
grant execute on function marketplace_private.admin_customer_document_access(text,text) to authenticated;
revoke all on function public.marketplace_admin_customer_activity(uuid,bigint,integer,text,text),public.marketplace_admin_customer_activity_review(uuid,bigint,integer,text,text),public.marketplace_admin_customer_document(uuid,text),public.marketplace_admin_customer_profile(uuid) from public,anon;
grant execute on function public.marketplace_admin_customer_activity(uuid,bigint,integer,text,text),public.marketplace_admin_customer_activity_review(uuid,bigint,integer,text,text),public.marketplace_admin_customer_document(uuid,text),public.marketplace_admin_customer_profile(uuid) to authenticated;
notify pgrst,'reload schema';
commit;