-- Read-only, chronological customer history. Search the displayed title, never event payloads.
begin;
create index if not exists customer_activity_owner_chronological on marketplace_private.customer_activity(user_id,created_at desc,id desc);

create or replace function marketplace_private.customer_activity_title(p_action text,p_category text) returns text language sql immutable set search_path='' as $$
 select coalesce(case lower(p_action)
 when 'manually_attested' then 'تأیید دائمی سه بررسی هویتی توسط مدیر'
 when 'temporary_granted' then 'اعطای تأیید موقت احراز هویت'
 when 'temporary_standardized' then 'تبدیل احراز موقت به تأیید استاندارد'
 when 'temporary_expired' then 'پایان مهلت احراز موقت'
 when 'temporary_invalidated' then 'ابطال احراز موقت به دلیل تغییر مدارک یا اطلاعات'
 when 'temporary_revoked' then 'لغو تأیید موقت توسط مدیریت'
 when 'access_changed' then 'تغییر دسترسی حساب'
 when 'inquiry_recorded' then 'ثبت نتیجه استعلام'
 when 'avatar_updated' then 'تغییر تصویر پروفایل'
 when 'trade_buy' then 'خرید دارایی' when 'trade_sell' then 'فروش دارایی'
 when 'password_changed' then 'تغییر رمز عبور'
 when 'login' then 'ورود به حساب' when 'logout' then 'خروج از حساب'
 when 'profile' then 'تغییر اطلاعات پروفایل' when 'preferences' then 'تغییر تنظیمات'
 when 'bank_add' then 'ثبت حساب بانکی' when 'bank_remove' then 'حذف حساب بانکی' when 'bank_primary' then 'تغییر حساب بانکی اصلی'
 when 'notification_read' then 'خواندن پیام'
 when 'alert_add' then 'ثبت هشدار قیمت' when 'alert_remove' then 'حذف هشدار قیمت'
 when 'api_revoke' then 'لغو دسترسی کلید'
 when 'identity_submit' then 'ارسال درخواست احراز هویت' when 'identity_start' then 'شروع احراز هویت' when 'identity_save' then 'ذخیره اطلاعات احراز هویت' end,
 case lower(p_action) when 'inserted' then 'ثبت' when 'insert' then 'ثبت' when 'create' then 'ثبت' when 'created' then 'ثبت' when 'update' then 'تغییر' when 'updated' then 'تغییر' when 'delete' then 'حذف' when 'deleted' then 'حذف' when 'upload' then 'بارگذاری' when 'uploaded' then 'بارگذاری' when 'review' then 'بررسی' else 'رویداد' end||' '||
 case p_category when 'profile' then 'مشخصات حساب' when 'preferences' then 'تنظیمات' when 'identity' then 'احراز هویت' when 'documents' then 'مدارک و تصاویر' when 'bank' then 'حساب‌های بانکی' when 'wallet' then 'کیف پول' when 'trade' then 'خرید و فروش' when 'credit' then 'اعتبار و وام' when 'staking' then 'استیکینگ' when 'notifications' then 'صندوق پیام' when 'referrals' then 'دعوت‌ها' when 'security' then 'امنیت حساب' when 'tokenization' then 'توکنایزیشن' when 'session' then 'ورود و خروج' else 'حساب کاربر' end);
$$;
create or replace function marketplace_private.customer_activity_search_normalize(p_value text) returns text language sql immutable set search_path='' as $$
 select trim(regexp_replace(translate(lower(coalesce(p_value,'')),'يك'||chr(8204)||chr(8205)||chr(160),'یک   '),'[[:space:]]+',' ','g'));
$$;

create or replace function public.marketplace_admin_customer_activity_search(
 p_user uuid,p_before text default null,p_limit integer default 25,p_query text default null,
 p_from timestamptz default null,p_until timestamptz default null,p_category text default null,p_status text default null
) returns jsonb language plpgsql stable security definer set search_path='' as $$
declare cursor_data jsonb;cursor_at timestamptz;cursor_id bigint;query_text text;items jsonb;total bigint;next_cursor text;cursor_valid 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 char_length(coalesce(p_query,''))>160
 or (p_from is not null and not isfinite(p_from)) or (p_until is not null and not isfinite(p_until)) or (p_from is not null and p_until is not null and p_from>=p_until)
 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;
 query_text:=marketplace_private.customer_activity_search_normalize(p_query);
 if p_before is not null then
  begin
   if octet_length(p_before)>256 then raise exception 'invalid_activity_cursor';end if;
   cursor_data:=p_before::jsonb;
   if jsonb_typeof(cursor_data)<>'object' or not(cursor_data ?& array['created_at','id']) or (select count(*) from jsonb_object_keys(cursor_data))<>2 or (select count(*) from json_each(p_before::json))<>2
   or jsonb_typeof(cursor_data->'created_at')<>'string' or jsonb_typeof(cursor_data->'id')<>'string'
   or cursor_data->>'created_at' !~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}\.[0-9]{6}Z$'
   or cursor_data->>'id' !~ '^[1-9][0-9]{0,18}$' then raise exception 'invalid_activity_cursor';end if;
   cursor_at:=(cursor_data->>'created_at')::timestamptz;cursor_id:=(cursor_data->>'id')::bigint;
   if not isfinite(cursor_at) or to_char(cursor_at at time zone 'UTC','YYYY-MM-DD"T"HH24:MI:SS.US"Z"')<>cursor_data->>'created_at' then raise exception 'invalid_activity_cursor';end if;
  exception when others then raise exception 'invalid_activity_cursor';end;
 end if;
 with filtered as materialized (
  select e.id,e.created_at,marketplace_private.customer_activity_title(e.action,e.category) title
  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)
   and (p_from is null or e.created_at>=p_from) and (p_until is null or e.created_at<p_until)
   and (query_text='' or position(query_text in marketplace_private.customer_activity_search_normalize(marketplace_private.customer_activity_title(e.action,e.category)))>0)
 ), page as (
  select * from filtered where p_before is null or (created_at,id)<(cursor_at,cursor_id) order by created_at desc,id desc limit p_limit+1
 ), shown as (select * from page order by created_at desc,id desc limit p_limit)
 select (select count(*) from filtered),
  (select coalesce(jsonb_agg(marketplace_private.customer_activity_json(id)||jsonb_build_object('title',title) order by created_at desc,id desc),'[]') from shown),
  case when (select count(*) from page)>p_limit then (select jsonb_build_object('created_at',to_char(created_at at time zone 'UTC','YYYY-MM-DD"T"HH24:MI:SS.US"Z"'),'id',id::text)::text from shown order by created_at,id limit 1) end,
  p_before is null or exists(select 1 from filtered where id=cursor_id and created_at=cursor_at)
 into total,items,next_cursor,cursor_valid;
 if not cursor_valid then raise exception 'invalid_activity_cursor';end if;
 return jsonb_build_object('items',items,'total',total,'next_cursor',next_cursor);
end$$;
revoke all on function marketplace_private.customer_activity_title(text,text),marketplace_private.customer_activity_search_normalize(text) from public,anon,authenticated,service_role;
revoke all on function public.marketplace_admin_customer_activity_search(uuid,text,integer,text,timestamptz,timestamptz,text,text) from public,anon,authenticated,service_role;
grant execute on function public.marketplace_admin_customer_activity_search(uuid,text,integer,text,timestamptz,timestamptz,text,text) to authenticated;

-- Preserve whichever profile implementation is installed, including optional temporary-KYC wrappers.
do $$declare f record;definition text;matched boolean:=false;old_call text:='timeline:=public.marketplace_admin_customer_activity(p_user);';new_call text:='timeline:=public.marketplace_admin_customer_activity_search(p_user,null,5);';begin
 for f in select p.oid from pg_proc p join pg_namespace n on n.oid=p.pronamespace
 where (n.nspname='public' and p.proname='marketplace_admin_customer_profile') or (n.nspname='marketplace_private' and p.proname='temporary_base_marketplace_admin_customer_profile') loop
  definition:=pg_get_functiondef(f.oid);
  if position(old_call in definition)>0 then execute replace(definition,old_call,new_call);matched:=true;
  elsif position(new_call in definition)>0 then matched:=true;end if;
 end loop;
 if not matched then raise exception 'customer_activity_profile_contract_missing';end if;
end$$;
notify pgrst,'reload schema';
commit;