-- Directory 007 must also work where optional temporary KYC migration 290006 was
-- never activated. Do not install that approval workflow or alter account records
-- merely to list customers. Missing temporary metadata means standard approval.
begin;
create or replace function marketplace_private.customer_directory_standard_level(p_id uuid)
returns text language plpgsql stable security definer set search_path='' as $$
declare earned text;
begin
 -- An expired temporary grant must not retain elevated access when the permanent
 -- evidence helper is unavailable. Dynamic execution avoids resolving an optional
 -- function when PostgreSQL plans the directory query.
 if p_id is null or to_regprocedure('marketplace_private.identity_standard_level(uuid)') is null then return 'basic';end if;
 execute 'select marketplace_private.identity_standard_level($1)' into earned using p_id;
 return case when earned in('primary','full') then earned else 'basic' end;
end$$;
revoke all on function marketplace_private.customer_directory_standard_level(uuid) from public,anon,authenticated,service_role;

create or replace function public.marketplace_admin_customer_directory(
 p_search text default '',p_status text default null,p_verification text default null,p_level text default null,
 p_from timestamptz default null,p_until timestamptz default null,p_sort text default 'newest',p_page integer default 1,p_limit integer default 20
) returns jsonb language plpgsql stable security definer set search_path='' as $$
declare q text:=marketplace_private.customer_directory_normalize(p_search);phone_q text;result jsonb;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if length(coalesce(p_search,''))>512 or length(q)>160 or coalesce(p_search,'')~'[[:cntrl:]]'
 or (p_status is not null and p_status not in('active','suspended','closed'))
 or (p_verification is not null and p_verification not in('unverified','pending','verified','rejected','temporary','expired'))
 or (p_level is not null and p_level not in('basic','primary','full'))
 or p_sort is null or p_sort not in('newest','oldest','name','last_login')
 or p_page is null or p_page not between 1 and 100000 or p_limit is null or p_limit not between 1 and 100
 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) then raise exception 'invalid_customer_filter';end if;
 if q~'^[+0-9 ().-]+$' then
  phone_q:=regexp_replace(q,'[^0-9]','','g');
  if phone_q~'^0098' then phone_q:=substring(phone_q from 3);elsif phone_q~'^09' then phone_q:='98'||substring(phone_q from 2);end if;
 end if;
 with source as (
  select p.user_id,p.username,coalesce(nullif(a.first_name,''),p.first_name) first_name,coalesce(nullif(a.last_name,''),p.last_name) last_name,
   nullif(u.email,'') email,nullif(u.raw_user_meta_data->>'contact_email','') contact_email,u.phone,p.created_at,u.last_sign_in_at,u.email_confirmed_at,u.phone_confirmed_at,
   coalesce(to_jsonb(a)->>'verification_mode','standard') verification_mode,to_jsonb(a)->>'temporary_level' temporary_level,(to_jsonb(a)->>'temporary_expires_at')::timestamptz temporary_expires_at,
   (to_jsonb(a)->>'verification_mode'='temporary' and (to_jsonb(a)->>'temporary_expires_at' is null or (to_jsonb(a)->>'temporary_expires_at')::timestamptz<=now())) expired_temporary,
   coalesce(a.verification_status,'not_started') stored_status,coalesce(a.account_level,'basic') stored_level,
   case when to_jsonb(a)->>'verification_mode'='temporary' and (to_jsonb(a)->>'temporary_expires_at' is null or (to_jsonb(a)->>'temporary_expires_at')::timestamptz<=now()) then marketplace_private.customer_directory_standard_level(r.id) else coalesce(a.account_level,'basic') end account_level,
   r.id request_id,r.status request_status,coalesce(u.raw_app_meta_data->>'phone_verification_mode'='sandbox',false) sandbox_registration,
   coalesce(x.status,'active') account_status,coalesce(x.trading_enabled,true) trading_enabled,coalesce(x.withdrawals_enabled,true) withdrawals_enabled
  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 left join public.marketplace_customer_access x on x.user_id=p.user_id
  left join lateral(select i.id,i.status from marketplace_private.identity_requests i where i.user_id=p.user_id order by i.created_at desc,i.id desc limit 1) r on true
 ), effective as (
  select s.*,case when expired_temporary then case when account_level='basic' then 'pending' else 'verified' end else stored_status end verification_status,
   marketplace_private.customer_directory_normalize(concat_ws(' ',first_name,last_name,username,email,contact_email,phone,user_id::text)) searchable,
   marketplace_private.customer_directory_normalize(concat_ws(' ',first_name,last_name,username)) sort_name
  from source s
 ), filtered as (
  select e.* from effective e where
   (q='' or strpos(searchable,q)>0 or (phone_q<>'' and strpos(regexp_replace(coalesce(phone,''),'[^0-9]','','g'),phone_q)>0))
   and (p_status is null or account_status=p_status) and (p_level is null or account_level=p_level)
   and (p_from is null or created_at>=p_from) and (p_until is null or created_at<p_until)
   and (p_verification is null
    or (p_verification='unverified' and verification_status in('not_started','unverified'))
    or (p_verification='pending' and verification_status='pending')
    or (p_verification='verified' and verification_status='verified')
    or (p_verification='rejected' and verification_status='rejected')
    or (p_verification='temporary' and verification_mode='temporary' and verification_status='verified' and not expired_temporary)
    or (p_verification='expired' and expired_temporary))
 ), ranked as (
  select f.*,row_number() over(order by
   case when p_sort='newest' then created_at end desc,
   case when p_sort='oldest' then created_at end asc,
   case when p_sort='name' then sort_name end asc,
   case when p_sort='last_login' then last_sign_in_at end desc nulls last,user_id asc) position
  from filtered f
 ), page_rows as (select * from ranked where position>(p_page-1)::bigint*p_limit and position<=p_page::bigint*p_limit)
 select jsonb_build_object('customers',coalesce((select jsonb_agg(to_jsonb(c)-array['stored_status','stored_level','expired_temporary','searchable','sort_name','position'] order by c.position) from page_rows c),'[]'::jsonb),
 'total',(select count(*) from filtered),'page',p_page,'page_size',p_limit) into result;
 return result;
end$$;
revoke all on function public.marketplace_admin_customer_directory(text,text,text,text,timestamptz,timestamptz,text,integer,integer) from public,anon;
grant execute on function public.marketplace_admin_customer_directory(text,text,text,text,timestamptz,timestamptz,text,integer,integer) to authenticated;
notify pgrst,'reload schema';
commit;