-- A customer-bound, read-only inquiry overview and explicit owner-only execution.
-- No adapter, credentials, approval level or provider contract is changed here.
begin;

create or replace function marketplace_private.customer_inquiry_adapter_supported(p_scope text,p_provider text,p_url text) returns boolean
language sql immutable set search_path='' as $$
 select coalesce(p_provider='شبانیک' and (
  (p_scope='identity' and p_url='https://shabanic.ir/api/v1/nid_inquiry') or
  (p_scope='mobile_owner' and p_url='https://shabanic.ir/api/v2/shahkar') or
  (p_scope='bank_owner' and p_url='https://shabanic.ir/api/v2/matchIbanByNationalcode')
 ),false)
$$;
revoke all on function marketplace_private.customer_inquiry_adapter_supported(text,text,text) from public,anon,authenticated;

-- Explicit columns only: no national ID, full IBAN, Vault identity, credential,
-- raw response, device information or idempotency fingerprint leaves this view.
-- New attempts use their immutable activity snapshot for provider labels. Older
-- attempts have no snapshot and retain the existing catalogue attribution.
create or replace view marketplace_private.customer_inquiry_history as
 select a.id,r.user_id,'identity'::text as source,r.id as request_id,i.id as inquiry_id,
 i.kind,right(i.bank_iban,4) as bank_iban_last4,s.id as service_id,coalesce(e.after_data->>'service_key',s.service_key) as service_key,
 coalesce(e.after_data->>'service_label',s.label) as service_label,coalesce(e.after_data->>'provider_name',s.provider_name) as provider_name,a.status,a.error_code,a.provider_reference,
 a.created_at,a.actor as actor_id
 from marketplace_private.inquiry_attempts a
 join marketplace_private.identity_inquiries i on i.id=a.inquiry_id
 join marketplace_private.identity_requests r on r.id=i.request_id
 join marketplace_private.inquiry_services s on s.id=a.service_id
 left join marketplace_private.customer_activity e on e.source_key='inquiry-attempt:identity:'||a.id::text;
-- Customer bank registration is an optional integration and was not enabled on
-- every deployment. Its absence must not block identity inquiry management.
do $$begin
 if to_regclass('marketplace_private.bank_registration_attempts') is not null then
  execute $view$
   create or replace view marketplace_private.customer_inquiry_history as
   select a.id,r.user_id,'identity'::text as source,r.id as request_id,i.id as inquiry_id,
    i.kind,right(i.bank_iban,4) as bank_iban_last4,s.id as service_id,coalesce(e.after_data->>'service_key',s.service_key) as service_key,
    coalesce(e.after_data->>'service_label',s.label) as service_label,coalesce(e.after_data->>'provider_name',s.provider_name) as provider_name,a.status,a.error_code,a.provider_reference,a.created_at,a.actor as actor_id
   from marketplace_private.inquiry_attempts a
   join marketplace_private.identity_inquiries i on i.id=a.inquiry_id
   join marketplace_private.identity_requests r on r.id=i.request_id
   join marketplace_private.inquiry_services s on s.id=a.service_id
   left join marketplace_private.customer_activity e on e.source_key='inquiry-attempt:identity:'||a.id::text
   union all
   select a.id,a.user_id,'bank_registration'::text,null::uuid,null::uuid,
    'bank_owner'::text,right(b.iban,4),s.id,coalesce(e.after_data->>'service_key',s.service_key),coalesce(e.after_data->>'service_label',s.label),coalesce(e.after_data->>'provider_name',s.provider_name),
    a.status,a.error_code,a.provider_reference,a.created_at,null::uuid
   from marketplace_private.bank_registration_attempts a
   join marketplace_private.inquiry_services s on s.id=a.service_id
   left join marketplace_private.bank_accounts b on b.id=a.bank_id and b.user_id=a.user_id
   left join marketplace_private.customer_activity e on e.source_key='inquiry-attempt:bank_registration:'||a.id::text
  $view$;
 end if;
end$$;
revoke all on marketplace_private.customer_inquiry_history from public,anon,authenticated,service_role;

create or replace function public.marketplace_admin_customer_inquiries(p_user uuid) returns jsonb
language plpgsql stable security definer set search_path='' as $$
declare r marketplace_private.identity_requests;birthday date;overview jsonb;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if p_user is null or not exists(select 1 from marketplace_private.customer_profiles where user_id=p_user) then raise exception 'customer_not_found';end if;
 select * into r from marketplace_private.identity_requests where user_id=p_user order by created_at desc,id desc limit 1;
 select birth_date into birthday from marketplace_private.accounts where user_id=p_user;
 select jsonb_build_object(
  'user_id',p_user,'server_time',now(),'birth_date',birthday,
  'request',case when r.id is null then null else jsonb_build_object(
   'id',r.id,'status',r.status,'version',r.version,'consent_at',r.consent_at,'submitted_at',r.submitted_at,
   'verification_type',r.verification_type,'verification_level',r.verification_level,'review_stage',r.review_stage,
   'phone_current',exists(select 1 from auth.users u where u.id=p_user and u.phone=r.phone and u.phone_confirmed_at is not null),
   'national_id_valid',coalesce(marketplace_private.national_id_valid(r.national_id),false),
   'mobile_format_valid',coalesce(r.phone ~ '^\+?989[0-9]{9}$',false),
   'card_uploaded',exists(select 1 from storage.objects where bucket_id='identity-documents' and name=r.card_path),
   'selfie_uploaded',exists(select 1 from storage.objects where bucket_id='identity-documents' and name=r.selfie_path)
  ) end,
  'checks',coalesce((select jsonb_agg(jsonb_build_object(
   'id',i.id,'request_id',i.request_id,'kind',i.kind,'status',i.status,'service_id',i.service_id,
   'provider_reference',i.provider_reference,'checked_at',i.checked_at,'error_code',i.error_code,
   'bank_id',i.bank_id,'bank_iban_last4',right(i.bank_iban,4),
   'bank_current',case when i.kind='bank_owner' then exists(select 1 from marketplace_private.bank_accounts b where b.id=i.bank_id and b.user_id=p_user and b.iban=i.bank_iban and b.status<>'rejected') else true end,
   'attempt_count_24h',(select count(*) from marketplace_private.inquiry_attempts a where a.inquiry_id=i.id and a.created_at>now()-interval '24 hours'),
   'retry_after',(select max(a.created_at)+interval '60 seconds' from marketplace_private.inquiry_attempts a where a.inquiry_id=i.id and a.created_at>now()-interval '60 seconds')
  ) order by i.kind,i.created_at,i.id) from marketplace_private.identity_inquiries i where i.request_id=r.id),'[]'::jsonb),
  'services',coalesce((select jsonb_agg(jsonb_build_object(
   'id',s.id,'service_key',s.service_key,'label',s.label,'scope',s.scope,'provider_name',s.provider_name,
   'base_url',s.base_url,'has_key',s.vault_secret_id is not null,'adapter_status',s.adapter_status,
   'version',s.version,'updated_at',s.updated_at,
   'supported_adapter',marketplace_private.customer_inquiry_adapter_supported(s.scope,s.provider_name,s.base_url)
  ) order by s.service_key) from marketplace_private.inquiry_services s),'[]'::jsonb),
  'banks',coalesce((select jsonb_agg(jsonb_build_object('id',b.id,'bank_name',b.bank_name,'iban_last4',right(b.iban,4),'status',b.status,'is_primary',b.is_primary) order by b.is_primary desc,b.created_at,b.id) from marketplace_private.bank_accounts b where b.user_id=p_user),'[]'::jsonb),
  'attempts',coalesce((select jsonb_agg(to_jsonb(h)-'user_id' order by h.created_at desc,h.id desc,h.source) from(
   select * from marketplace_private.customer_inquiry_history where user_id=p_user order by created_at desc,id desc,source limit 100
  )h),'[]'::jsonb),
  'attempts_total',(select count(*) from marketplace_private.customer_inquiry_history where user_id=p_user)
 ) into overview;
 return overview;
end$$;

create or replace function public.marketplace_admin_customer_inquiry_run(p_user uuid,p_inquiry uuid,p_service uuid,p_version integer) returns jsonb
language plpgsql security definer set search_path='' as $$
declare r marketplace_private.identity_requests;i marketplace_private.identity_inquiries;s marketplace_private.inquiry_services;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if p_user is null or not exists(select 1 from marketplace_private.customer_profiles where user_id=p_user) then raise exception 'customer_not_found';end if;
 -- The same lock as starting a new identity request prevents it from superseding
 -- the reviewed request between the customer/version check and adapter execution.
 perform pg_advisory_xact_lock(hashtextextended('identity:'||p_user::text,0));
 select * into r from marketplace_private.identity_requests where user_id=p_user order by created_at desc,id desc limit 1 for update;
 if r.id is null then raise exception 'inquiry_not_pending';end if;
 if r.version is distinct from p_version then raise exception 'identity_conflict';end if;
 select * into i from marketplace_private.identity_inquiries where id=p_inquiry and request_id=r.id;
 if i.id is null then raise exception 'inquiry_customer_mismatch';end if;
 select * into s from marketplace_private.inquiry_services where id=p_service for share;
 if s.id is null or s.scope is distinct from i.kind or not marketplace_private.customer_inquiry_adapter_supported(s.scope,s.provider_name,s.base_url) then raise exception 'adapter_not_ready';end if;
 -- Existing adapters continue enforcing consent, current phone/bank evidence,
 -- Vault readiness, provider destination, cooldown, daily limit and result validation.
 -- No caller-provided national ID, IBAN, URL, provider result or approval is accepted.
 if i.kind='bank_owner' then
  -- A prior passed check cannot stand in for a bank that was subsequently rejected.
  perform 1 from marketplace_private.bank_accounts b where b.id=i.bank_id and b.user_id=p_user and b.iban=i.bank_iban and b.status<>'rejected' for update;
  if not found then raise exception 'bank_changed';end if;
  perform public.marketplace_bank_inquiry_run(i.id,s.id);
 elsif i.kind in('identity','mobile_owner') then perform public.marketplace_identity_inquiry_run(i.id,s.id);
 else raise exception 'adapter_not_ready';end if;
 return public.marketplace_admin_customer_inquiries(p_user);
end$$;

-- Every actual attempt is visible even when a repeated provider error does not
-- change the inquiry status. These are informational events, never KYC approvals.
create or replace function marketplace_private.customer_inquiry_attempt_activity() returns trigger
language plpgsql security definer set search_path='' as $$
declare uid uuid;meta jsonb;who uuid;record_source text;
begin
 select user_id,to_jsonb(h)-'user_id'-'actor_id'-'created_at',actor_id,source into uid,meta,who,record_source
 from marketplace_private.customer_inquiry_history h where h.id=new.id and h.source=case when tg_table_name='bank_registration_attempts' then 'bank_registration' else 'identity' end;
 if uid is null then return null;end if;
 insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,after_data,changed_fields,actor_id,actor_kind,source_key)
 values(uid,'identity','inquiry_recorded',tg_table_name,new.id::text,meta,array['status','error_code','provider_reference'],who,case when record_source='identity' then 'admin' else 'system' end,'inquiry-attempt:'||record_source||':'||new.id::text)
 on conflict(source_key) do nothing;
 return null;
end$$;
revoke all on function marketplace_private.customer_inquiry_attempt_activity() from public,anon,authenticated;
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')
 and not(p_entity in('inquiry_attempts','bank_registration_attempts') and p_action='inquiry_recorded');
$$;
drop trigger if exists customer_inquiry_attempt_activity on marketplace_private.inquiry_attempts;
create trigger customer_inquiry_attempt_activity after insert on marketplace_private.inquiry_attempts for each row execute function marketplace_private.customer_inquiry_attempt_activity();
do $$begin
 if to_regclass('marketplace_private.bank_registration_attempts') is not null then
  execute 'drop trigger if exists customer_inquiry_attempt_activity on marketplace_private.bank_registration_attempts';
  execute 'create trigger customer_inquiry_attempt_activity after insert on marketplace_private.bank_registration_attempts for each row execute function marketplace_private.customer_inquiry_attempt_activity()';
 end if;
end$$;

revoke all on function public.marketplace_admin_customer_inquiries(uuid),public.marketplace_admin_customer_inquiry_run(uuid,uuid,uuid,integer) from public,anon;
grant execute on function public.marketplace_admin_customer_inquiries(uuid),public.marketplace_admin_customer_inquiry_run(uuid,uuid,uuid,integer) to authenticated;
notify pgrst,'reload schema';
commit;