begin;
create extension if not exists http with schema extensions;

alter table marketplace_private.identity_inquiries add column if not exists error_code text;
create table if not exists marketplace_private.inquiry_attempts(
 id uuid primary key default gen_random_uuid(),
 inquiry_id uuid not null references marketplace_private.identity_inquiries(id),
 service_id uuid not null references marketplace_private.inquiry_services(id),
 actor uuid not null references auth.users(id),
 status text not null check(status in('passed','failed','error')),
 error_code text,provider_reference text,created_at timestamptz not null default now()
);
alter table marketplace_private.inquiry_attempts enable row level security;
revoke all on marketplace_private.inquiry_attempts from public,anon,authenticated;
grant all on marketplace_private.inquiry_attempts to service_role;
create index if not exists inquiry_attempt_recent on marketplace_private.inquiry_attempts(inquiry_id,created_at desc);

-- Only fill the untouched service: never send a previous provider's key to Shabanic.
update marketplace_private.inquiry_services
set label='تطبیق شبا با کد ملی',provider_name='شبانیک',base_url='https://shabanic.ir/api/v2/matchIbanByNationalcode',version=version+1,updated_at=now()
where service_key='bank_ownership' and provider_name='' and base_url='' and vault_secret_id is null;

create or replace function marketplace_private.inquiry_adapter_config() returns trigger language plpgsql security definer set search_path='' as $$
begin
 if new.scope='bank_owner' and new.provider_name='شبانیک' and new.base_url='https://shabanic.ir/api/v2/matchIbanByNationalcode' 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;
 return new;
end$$;
revoke all on function marketplace_private.inquiry_adapter_config() from public,anon,authenticated;
drop trigger if exists inquiry_adapter_config on marketplace_private.inquiry_services;
create trigger inquiry_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_adapter_config();

-- The owner saved the supplied IBAN-service token before sending these docs,
-- using the service description as provider and leaving destination unset.
update marketplace_private.inquiry_services
set scope='bank_owner',label='تطبیق شبا با کد ملی',provider_name='شبانیک',base_url='https://shabanic.ir/api/v2/matchIbanByNationalcode',version=version+1,updated_at=now()
where service_key='bank_ownership' and scope='other' and provider_name='تطبیق شماره شبا با کد ملی' and base_url='' and version=2;

create or replace function public.marketplace_inquiry_configure(p_key text,p_label text,p_scope text,p_provider text,p_url text,p_secret text,p_version integer) returns jsonb language plpgsql security definer set search_path='' as $$
declare s marketplace_private.inquiry_services;secret_id uuid;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 perform pg_advisory_xact_lock(hashtextextended('inquiry:'||p_key,0));
 select * into s from marketplace_private.inquiry_services where service_key=p_key for update;
 if (s.id is not null and s.version is distinct from p_version) or (s.id is null and p_version is distinct from 0) then raise exception 'settings_conflict';end if;
 if p_secret is not null and (length(p_secret) not between 8 and 8192 or p_secret~'[\r\n]') then raise exception 'invalid_api_key';end if;
 secret_id:=s.vault_secret_id;
 -- Changing destinations without supplying a new key disconnects the old credential.
 if s.provider_name is distinct from coalesce(p_provider,'') or s.base_url is distinct from coalesce(p_url,'') or s.scope is distinct from p_scope then secret_id:=null;end if;
 if p_secret is not null then
  if to_regprocedure('vault.create_secret(text,text,text,uuid)') is null and to_regprocedure('vault.create_secret(text,text,text)') is null then raise exception 'vault_unavailable';end if;
  if secret_id is null then execute 'select vault.create_secret($1,$2,$3)' into secret_id using p_secret,'planck_inquiry_'||p_key||'_'||gen_random_uuid()::text,'Planck inquiry API credential';
  else execute 'select vault.update_secret($1,$2)' using secret_id,p_secret;end if;
 end if;
 insert into marketplace_private.inquiry_services(service_key,label,scope,provider_name,base_url,vault_secret_id,updated_by) values(p_key,p_label,p_scope,coalesce(p_provider,''),coalesce(p_url,''),secret_id,auth.uid())
 on conflict(service_key) do update set label=excluded.label,scope=excluded.scope,provider_name=excluded.provider_name,base_url=excluded.base_url,vault_secret_id=excluded.vault_secret_id,adapter_status='awaiting_docs',version=inquiry_services.version+1,updated_at=now(),updated_by=auth.uid();
 insert into marketplace_private.audit_log(actor,entity,record_id,operation) values(auth.uid(),'inquiry_settings',p_key,'configure');
 return public.marketplace_inquiry_settings();
end$$;

-- No caller-supplied URL, identity, credentials or result. All inputs come from locked records.
create or replace function public.marketplace_bank_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;
 bank marketplace_private.bank_accounts;response extensions.http_response;payload jsonb;secret text;
 outcome text:='error';failure text;reference text;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('bank_inquiry:'||p_inquiry::text,0)) then raise exception 'inquiry_busy';end if;
 select * into i from marketplace_private.identity_inquiries where id=p_inquiry;
 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<>'bank_owner' or r.status not in('pending','documents_approved','verified') or r.consent_at is null then raise exception 'inquiry_not_pending';end if;
 select * into bank from marketplace_private.bank_accounts where id=i.bank_id and user_id=r.user_id and iban=i.bank_iban for update;
 if bank.id is null then raise exception 'bank_changed';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) or i.bank_iban !~ '^IR[0-9]{24}$' then raise exception 'invalid_identity_fields';end if;
 -- A completed match for this exact bank and identity is not billed again.
 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<>'bank_owner' or s.provider_name<>'شبانیک' or s.base_url<>'https://shabanic.ir/api/v2/matchIbanByNationalcode' or s.adapter_status<>'ready' or s.vault_secret_id is null 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;
 begin
  select * into response from extensions.http((
   'POST','https://shabanic.ir/api/v2/matchIbanByNationalcode',
   array[('Authorization','Bearer '||secret)::extensions.http_header,('Accept','application/json')::extensions.http_header],
   'application/json',jsonb_build_object('iban',i.bank_iban,'nationalCode',r.national_id)::text
  )::extensions.http_request);
  -- pgsql-http defaults to a five-second timeout and does not follow redirects.
  -- TLS verification stays enabled; no privileged curl option override is needed.
  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;
   if payload->'success'='false'::jsonb then failure:='provider_rejected';end if;
   if payload->'success'='true'::jsonb and payload->'statusCode'='200'::jsonb
     and (not(payload?'status') or payload->>'status'='DONE')
     and jsonb_typeof(payload#>'{result,isMatched}')='boolean'
     and jsonb_typeof(payload->'trackId')='string' and (payload->>'trackId')~'^shabanic-[A-Za-z0-9_.:-]{1,180}$' then
    outcome:=case when payload#>'{result,isMatched}'='true'::jsonb then 'passed' else 'failed' end;
    reference:=payload->>'trackId';failure:=null;
   end if;
  end if;
 exception when invalid_text_representation then failure:='provider_response_invalid';
 when others then failure:='provider_unavailable';
 end;
 secret:=null;payload:=null;response:=null;
 -- Only the validated outcome/reference is persisted, never raw provider text or secrets.
 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;
 if outcome='failed' then update marketplace_private.bank_accounts set status='rejected' where id=bank.id;end if;
 if outcome='error' then update marketplace_private.bank_accounts set status='pending' where id=bank.id;end if;
 if outcome='passed' and r.status<>'verified' then update marketplace_private.bank_accounts set status='pending' where id=bank.id;end if;
 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(),'bank_inquiry',i.id::text,outcome);
 return public.marketplace_identity_admin(r.id);
end$$;
revoke all on function public.marketplace_bank_inquiry_run(uuid,uuid) from public,anon;
grant execute on function public.marketplace_bank_inquiry_run(uuid,uuid) to authenticated;

create or replace function public.marketplace_identity_admin(p_id uuid default null) returns jsonb language plpgsql security definer set search_path='' as $$
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if p_id is null then return jsonb_build_object('requests',coalesce((select jsonb_agg(to_jsonb(q)) from(select id,status,first_name,last_name,right(national_id,4) national_id_last4,created_at,submitted_at,version from marketplace_private.identity_requests where status<>'draft' order by created_at desc limit 100)q),'[]'));end if;
 insert into marketplace_private.audit_log(actor,entity,record_id,operation) values(auth.uid(),'identity',p_id::text,'view_documents');
 return jsonb_build_object('request',(select to_jsonb(r)-'upload_attempts' from marketplace_private.identity_requests r where id=p_id),
 'checks',coalesce((select jsonb_agg((to_jsonb(i)-'bank_iban')||jsonb_build_object('bank_iban_last4',right(i.bank_iban,4))) from marketplace_private.identity_inquiries i where request_id=p_id),'[]'));
end$$;
notify pgrst,'reload schema';
commit;