begin;
-- Source contracts: shabanic.ir/nid_api_doc (v1) and shabanic.ir/api_doc (v2).
-- Keep this migration independent of the pending customer-bank registration migration.
alter table marketplace_private.identity_requests add column if not exists birth_date date;

create or replace function marketplace_private.identity_jalali(d date) returns text
language plpgsql immutable strict set search_path='' as $$
declare gy integer:=extract(year from d)::int;gm integer:=extract(month from d)::int;gd integer:=extract(day from d)::int;
 days integer;jdays integer;jy integer;jm integer;jd integer;cum integer[]:=array[0,31,59,90,120,151,181,212,243,273,304,334];y integer:=gy-1600;
begin
 days:=365*y+(y+3)/4-(y+99)/100+(y+399)/400+cum[gm]+gd-1;
 if gm>2 and (gy%4=0 and (gy%100<>0 or gy%400=0)) then days:=days+1;end if;
 jdays:=days-79;jy:=979+33*(jdays/12053);jdays:=jdays%12053;
 jy:=jy+4*(jdays/1461);jdays:=jdays%1461;
 if jdays>=366 then jy:=jy+(jdays-1)/365;jdays:=(jdays-1)%365;end if;
 if jdays<186 then jm:=1+jdays/31;jd:=1+jdays%31;else jm:=7+(jdays-186)/30;jd:=1+(jdays-186)%30;end if;
 return jy::text||'/'||lpad(jm::text,2,'0')||'/'||lpad(jd::text,2,'0');
end$$;
create or replace function marketplace_private.identity_normalize(v text) returns text
language sql immutable set search_path='' as $$
 select lower(regexp_replace(translate(coalesce(v,''),'يىك۰۱۲۳۴۵۶۷۸۹٠١٢٣٤٥٦٧٨٩','ییک01234567890123456789'),'[[:space:]‌]+','','g'))
$$;
revoke all on function marketplace_private.identity_jalali(date),marketplace_private.identity_normalize(text) from public,anon,authenticated;

-- Run after the existing bank adapter trigger, without changing its contracts.
create or replace function marketplace_private.inquiry_identity_adapter_config() returns trigger
language plpgsql security definer set search_path='' as $$
begin
 if new.scope in('identity','mobile_owner') then
  if new.provider_name='شبانیک' and (
   (new.scope='identity' and new.base_url='https://shabanic.ir/api/v1/nid_inquiry') or
   (new.scope='mobile_owner' and new.base_url='https://shabanic.ir/api/v2/shahkar')) then
   new.adapter_status:=case when new.vault_secret_id is not null then 'ready' else 'awaiting_docs' end;
  else new.adapter_status:='awaiting_docs';end if;
 end if;
 return new;
end$$;
revoke all on function marketplace_private.inquiry_identity_adapter_config() from public,anon,authenticated;
drop trigger if exists inquiry_identity_adapter_config on marketplace_private.inquiry_services;
create trigger inquiry_identity_adapter_config before insert or update of scope,provider_name,base_url,vault_secret_id on marketplace_private.inquiry_services
for each row execute function marketplace_private.inquiry_identity_adapter_config();
-- The owner supplied the nid contract for the saved, still-unbound registry key.
-- Bind only that catalogue entry; never move credentials from another destination.
update marketplace_private.inquiry_services set provider_name='شبانیک',base_url='https://shabanic.ir/api/v1/nid_inquiry',
 version=version+1,updated_at=now()
where service_key='national_identity' and scope='identity' and provider_name='' and base_url='';
update marketplace_private.inquiry_services set provider_name='شبانیک',
 base_url=case when service_key='national_identity' then 'https://shabanic.ir/api/v1/nid_inquiry' else 'https://shabanic.ir/api/v2/shahkar' end,
 version=version+1,updated_at=now()
where service_key in('national_identity','mobile_ownership') and base_url='' and provider_name in('','شبانیک') and vault_secret_id is null;
-- Refresh readiness only for exact, already configured provider contracts; never reuse another key.
update marketplace_private.inquiry_services set base_url=base_url
where provider_name='شبانیک' and ((scope='identity' and base_url='https://shabanic.ir/api/v1/nid_inquiry') or (scope='mobile_owner' and base_url='https://shabanic.ir/api/v2/shahkar'));

-- A successful identity check is tied to the birthday used for that request.
create or replace function marketplace_private.identity_birth_date_lock() returns trigger
language plpgsql security definer set search_path='' as $$
begin
 if new.birth_date is distinct from old.birth_date and exists(
  select 1 from marketplace_private.identity_requests r join marketplace_private.identity_inquiries i on i.request_id=r.id
  where r.user_id=new.user_id and r.status in('pending','documents_approved','verified') and i.kind='identity' and i.status='passed'
 ) then raise exception 'birth_date_locked';end if;
 return new;
end$$;
revoke all on function marketplace_private.identity_birth_date_lock() from public,anon,authenticated;
drop trigger if exists identity_birth_date_lock on marketplace_private.accounts;
create trigger identity_birth_date_lock before update of birth_date on marketplace_private.accounts for each row execute function marketplace_private.identity_birth_date_lock();

create or replace function public.marketplace_identity_inquiry_run(p_inquiry uuid,p_service uuid) returns jsonb
language plpgsql security definer set search_path='' as $$
declare i marketplace_private.identity_inquiries;r marketplace_private.identity_requests;s marketplace_private.inquiry_services;
 response extensions.http_response;payload jsonb;result jsonb;secret text;headers extensions.http_header[];request_body jsonb;
 birthday date;jalali text;mobile text;reference text;failure text;outcome text:='error';attempt uuid:=gen_random_uuid();
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if not pg_try_advisory_xact_lock(hashtextextended('inquiry:'||coalesce(p_inquiry::text,''),0)) then raise exception 'inquiry_busy';end if;
 select * into i from marketplace_private.identity_inquiries where id=p_inquiry;
 if i.kind='bank_owner' then return public.marketplace_bank_inquiry_run(p_inquiry,p_service);end if;
 select * into r from marketplace_private.identity_requests where id=i.request_id for update;
 select * into i from marketplace_private.identity_inquiries where id=p_inquiry for update;
 if i.id is null or i.kind not in('identity','mobile_owner') or r.status not in('pending','documents_approved','verified') or r.consent_at is null then raise exception 'inquiry_not_pending';end if;
 if not exists(select 1 from auth.users where id=r.user_id and phone=r.phone and phone_confirmed_at is not null) then raise exception 'phone_changed';end if;
 if not marketplace_private.national_id_valid(r.national_id) then raise exception 'invalid_identity_fields';end if;
 if i.status='passed' then return public.marketplace_identity_admin(r.id);end if;
 select * into s from marketplace_private.inquiry_services where id=p_service for share;
 if s.id is null or s.scope<>i.kind or s.provider_name<>'شبانیک' or s.adapter_status<>'ready' or s.vault_secret_id is null
 or (i.kind='identity' and s.base_url<>'https://shabanic.ir/api/v1/nid_inquiry')
 or (i.kind='mobile_owner' and s.base_url<>'https://shabanic.ir/api/v2/shahkar') then raise exception 'adapter_not_ready';end if;
 if exists(select 1 from marketplace_private.inquiry_attempts where inquiry_id=i.id and created_at>now()-interval '60 seconds') then raise exception 'inquiry_retry_later';end if;
 if (select count(*) from marketplace_private.inquiry_attempts where inquiry_id=i.id and created_at>now()-interval '24 hours')>=5 then raise exception 'inquiry_daily_limit';end if;
 select decrypted_secret into secret from vault.decrypted_secrets where id=s.vault_secret_id;
 if secret is null or length(secret) not between 8 and 8192 or secret~'[\r\n]' then raise exception 'invalid_api_key';end if;
 if i.kind='identity' then
  select birth_date into birthday from marketplace_private.accounts where user_id=r.user_id for update;
  if birthday is null or birthday<date '1900-01-01' or birthday>(now() at time zone 'Asia/Tehran')::date then raise exception 'birth_date_required';end if;
  jalali:=marketplace_private.identity_jalali(birthday);
  update marketplace_private.identity_requests set birth_date=birthday,version=version+1,updated_at=now() where id=r.id and birth_date is distinct from birthday;
  request_body:=jsonb_build_object('nationalCode',r.national_id,'birthDate',jalali);
  headers:=array[('SHABANIC-API-KEY',secret)::extensions.http_header,('Accept','application/json')::extensions.http_header];
 else
  mobile:=regexp_replace(r.phone,'^\+?98','0');
  if mobile!~'^09[0-9]{9}$' then raise exception 'verified_mobile_required';end if;
  request_body:=jsonb_build_object('national_code',r.national_id,'mobile',mobile);
  headers:=array[('Authorization','Bearer '||secret)::extensions.http_header,('Accept','application/json')::extensions.http_header];
 end if;
 begin
  select * into response from extensions.http(('POST',s.base_url,headers,'application/json',request_body::text)::extensions.http_request);
  failure:=case when response.status in(401,403) then 'provider_auth' when response.status=429 then 'provider_rate_limit' when response.status<>200 then 'provider_unavailable' else 'provider_response_invalid' end;
  if response.status=200 and length(response.content)<=65536 then
   payload:=response.content::jsonb;result:=payload->'result';
   if payload->'success'='false'::jsonb or payload->>'status'='FAILED' then failure:='provider_rejected';end if;
   if payload->>'status'='DONE' and jsonb_typeof(payload->'trackId')='string'
    and payload->>'trackId' ~ '^[A-Za-z0-9][A-Za-z0-9_.:-]{7,199}$' then
    if i.kind='mobile_owner' and payload->'success'='true'::jsonb and jsonb_typeof(result->'isMatched')='boolean' then
     outcome:=case when result->'isMatched'='true'::jsonb then 'passed' else 'failed' end;
     reference:=payload->>'trackId';failure:=null;
    elsif i.kind='identity' and (not(payload?'success') or payload->'success'='true'::jsonb)
     and jsonb_typeof(result)='object'
     and not exists(select 1 from unnest(array['nationalCode','firstName','lastName','fatherName','birthDate','idNo','deathStatus']) as f(key)
      where jsonb_typeof(result->f.key) is distinct from 'string' or length(trim(result->>f.key)) not between 1 and 100)
     and result->>'nationalCode' ~ '^[0-9۰-۹٠-٩]{10}$'
     and result->>'idNo' ~ '^[0-9۰-۹٠-٩]{1,10}$'
     and marketplace_private.identity_normalize(result->>'birthDate') ~ '^[0-9]{4}/[0-9]{2}/[0-9]{2}$'
     and marketplace_private.identity_normalize(result->>'deathStatus') in('زنده','فوتشده','فوت') then
     outcome:=case when marketplace_private.identity_normalize(result->>'nationalCode')=r.national_id
      and marketplace_private.identity_normalize(result->>'firstName')=marketplace_private.identity_normalize(r.first_name)
      and marketplace_private.identity_normalize(result->>'lastName')=marketplace_private.identity_normalize(r.last_name)
      and marketplace_private.identity_normalize(result->>'fatherName')=marketplace_private.identity_normalize(r.father_name)
      and marketplace_private.identity_normalize(result->>'birthDate')=jalali
      and marketplace_private.identity_normalize(result->>'idNo')::numeric=r.birth_certificate_number::numeric
      and marketplace_private.identity_normalize(result->>'deathStatus')='زنده' then 'passed' else 'failed' end;
     reference:=payload->>'trackId';failure:=null;
    end if;
   end if;
  end if;
 exception when invalid_text_representation then failure:='provider_response_invalid';
 when others then failure:='provider_unavailable';
 end;
 secret:=null;headers:=null;request_body:=null;payload:=null;result:=null;response:=null;
 perform public.marketplace_identity_inquiry_result(i.id,s.id,outcome,coalesce(reference,'local-error-'||attempt::text));
 update marketplace_private.identity_inquiries set error_code=failure where id=i.id;
 insert into marketplace_private.inquiry_attempts(id,inquiry_id,service_id,actor,status,error_code,provider_reference) values(attempt,i.id,s.id,auth.uid(),outcome,failure,reference);
 insert into marketplace_private.audit_log(actor,entity,record_id,operation) values(auth.uid(),'identity_inquiry',i.id::text,outcome);
 return public.marketplace_identity_admin(r.id);
end$$;
revoke all on function public.marketplace_identity_inquiry_run(uuid,uuid) from public,anon;
grant execute on function public.marketplace_identity_inquiry_run(uuid,uuid) to authenticated;
notify pgrst,'reload schema';
commit;