-- Read-only owner/MFA control centre. Refreshing never reviews requests, reads notifications,
-- runs billable inquiries, changes customer levels, or contacts external providers.
begin;
create index if not exists customer_activity_global_chronological on marketplace_private.customer_activity(created_at desc,id desc);
create index if not exists admin_audit_global_chronological on marketplace_private.audit_log(occurred_at desc,id desc);

create or replace function public.marketplace_admin_control_dashboard() returns jsonb
language plpgsql stable security definer set search_path='' as $$
declare metrics jsonb;activities jsonb;requests jsonb;logs jsonb;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 select jsonb_build_object(
  'total_customers',(select count(*) from marketplace_private.customer_profiles p where not exists(select 1 from marketplace_private.members m where m.user_id=p.user_id)),
  'pending_identity',(select count(*) from marketplace_private.identity_requests where status in('pending','documents_approved')),
  'pending_tokenization',(select count(*) from marketplace_private.tokenization_requests where status in('submitted','under_review','issuance_pending')),
  -- This is the number of submitted deposit reports, not a financial settlement count.
  'pending_deposits',(select count(*) from marketplace_private.toman_deposit_requests where status='submitted'),
  'unread_notifications',(select count(*) from marketplace_private.admin_notifications n where not exists(select 1 from marketplace_private.admin_notification_reads r where r.notification_id=n.id and r.admin_id=auth.uid())),
  'active_projects',(select count(*) from public.projects where status='published')
 ) into metrics;
 select coalesce(jsonb_agg(jsonb_build_object(
  'id',e.id::text,'user_id',e.user_id,'user_label',coalesce(nullif(trim(p.first_name||' '||p.last_name),''),p.username,'کاربر'),
  'title',marketplace_private.customer_activity_title(e.action,e.category),'category',e.category,
  'actor_kind',e.actor_kind,'occurred_at',e.created_at
 ) order by e.created_at desc,e.id desc),'[]'::jsonb) into activities
 from (select * from marketplace_private.customer_activity order by created_at desc,id desc limit 5) e
 left join marketplace_private.customer_profiles p on p.user_id=e.user_id;
 select coalesce(jsonb_agg(jsonb_build_object(
  'id',r.id,'kind',r.kind,'user_id',r.user_id,'user_label',coalesce(nullif(trim(p.first_name||' '||p.last_name),''),p.username,'کاربر'),
  'title',r.title,'status',r.status,'created_at',r.created_at
 ) order by r.created_at desc,r.kind,r.id desc),'[]'::jsonb) into requests
 from (
  select * from (
   (select id,'identity'::text kind,user_id,'احراز هویت'::text title,status,coalesce(submitted_at,created_at) created_at from marketplace_private.identity_requests where status not in('draft','superseded') order by coalesce(submitted_at,created_at) desc,id desc limit 5)
   union all
   (select id,'tokenization',user_id,coalesce(nullif(fields->>'title',''),'درخواست توکنایزیشن'),status,coalesce(submitted_at,created_at) from marketplace_private.tokenization_requests where status<>'draft' order by coalesce(submitted_at,created_at) desc,id desc limit 5)
   union all
   (select id,'deposit',user_id,'گزارش واریز تومان',status,submitted_at from marketplace_private.toman_deposit_requests where status='submitted' order by submitted_at desc,id desc limit 5)
  ) combined order by created_at desc,kind,id desc limit 5
 ) r left join marketplace_private.customer_profiles p on p.user_id=r.user_id;
 select coalesce(jsonb_agg(jsonb_build_object('id',id::text,'actor',actor,'entity',entity,'operation',operation,'record_id',record_id,'occurred_at',occurred_at) order by occurred_at desc,id desc),'[]'::jsonb) into logs
 from (select id,actor,entity,operation,record_id,occurred_at from marketplace_private.audit_log order by occurred_at desc,id desc limit 5) recent;
 return jsonb_build_object('metrics',metrics,'recent_activities',activities,'recent_requests',requests,'system_logs',logs);
end$$;
revoke all on function public.marketplace_admin_control_dashboard() from public,anon,service_role;
grant execute on function public.marketplace_admin_control_dashboard() to authenticated;
notify pgrst,'reload schema';
commit;