begin;
alter table marketplace_private.accounts add column if not exists identity_verified_at timestamptz;
create table if not exists marketplace_private.identity_requests(
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id),
 status text not null default 'draft' check(status in('draft','pending','documents_approved','verified','rejected','superseded')),
 phone text not null,first_name text,last_name text,national_id text,birth_certificate_number text,father_name text,address text,postal_code text,
 challenge text not null default ('PLK-'||upper(substr(replace(gen_random_uuid()::text,'-',''),1,8))),challenge_expires_at timestamptz not null default now()+interval '24 hours',
 card_path text,selfie_path text,upload_attempts integer not null default 0 check(upload_attempts between 0 and 12),
 version integer not null default 1,consent_at timestamptz,submitted_at timestamptz,reviewed_at timestamptz,reviewed_by uuid references auth.users(id),review_note text,
 review_checks jsonb not null default '{}',verified_at timestamptz,created_at timestamptz not null default now(),updated_at timestamptz not null default now()
);
alter table marketplace_private.identity_requests enable row level security;
revoke all on marketplace_private.identity_requests from public,anon,authenticated;
grant all on marketplace_private.identity_requests to service_role;
create unique index if not exists identity_one_active on marketplace_private.identity_requests(user_id) where status in('draft','pending','documents_approved');
create unique index if not exists identity_verified_national_unique on marketplace_private.identity_requests(national_id) where status='verified';
create index if not exists identity_queue on marketplace_private.identity_requests(status,created_at desc);

create table if not exists marketplace_private.inquiry_services(
 id uuid primary key default gen_random_uuid(),service_key text unique not null check(service_key~'^[a-z][a-z0-9_]{2,59}$'),
 label text not null check(length(label) between 2 and 100),scope text not null check(scope in('identity','mobile_owner','bank_owner','other')),
 provider_name text not null default '' check(length(provider_name)<=100),base_url text not null default '' check(base_url='' or (base_url~'^https://[^/@?#]+(/[^?#]*)?$' and length(base_url)<=500)),
 vault_secret_id uuid,adapter_status text not null default 'awaiting_docs' check(adapter_status in('awaiting_docs','ready','paused')),
 version integer not null default 1,updated_at timestamptz not null default now(),updated_by uuid references auth.users(id)
);
alter table marketplace_private.inquiry_services enable row level security;
revoke all on marketplace_private.inquiry_services from public,anon,authenticated;
grant all on marketplace_private.inquiry_services to service_role;
insert into marketplace_private.inquiry_services(service_key,label,scope) values
 ('national_identity','تطبیق کد ملی و مشخصات','identity'),('mobile_ownership','مالکیت موبایل و کد ملی','mobile_owner'),('bank_ownership','مالکیت حساب بانکی','bank_owner') on conflict(service_key) do nothing;
create table if not exists marketplace_private.identity_inquiries(
 id uuid primary key default gen_random_uuid(),request_id uuid not null references marketplace_private.identity_requests(id),
 kind text not null check(kind in('identity','mobile_owner','bank_owner')),bank_id uuid references marketplace_private.bank_accounts(id) on delete set null,
 bank_iban text,status text not null default 'not_connected' check(status in('not_connected','pending','passed','failed','error')),
 service_id uuid references marketplace_private.inquiry_services(id),provider_reference text,checked_at timestamptz,created_at timestamptz not null default now()
);
alter table marketplace_private.identity_inquiries enable row level security;
revoke all on marketplace_private.identity_inquiries from public,anon,authenticated;
grant all on marketplace_private.identity_inquiries to service_role;
create unique index if not exists identity_inquiry_once on marketplace_private.identity_inquiries(request_id,kind,coalesce(bank_iban,''));

create or replace function marketplace_private.national_id_valid(v text) returns boolean language plpgsql immutable set search_path='' as $$
declare s integer:=0;r integer;i integer;
begin
 if v is null or v!~'^[0-9]{10}$' or v=repeat(substr(v,1,1),10) then return false;end if;
 for i in 1..9 loop s:=s+substr(v,i,1)::integer*(11-i);end loop;r:=s%11;
 return substr(v,10,1)::integer=case when r<2 then r else 11-r end;
end$$;
revoke all on function marketplace_private.national_id_valid(text) from public,anon,authenticated;

create or replace function public.marketplace_identity_state() returns jsonb language plpgsql stable security definer set search_path='' as $$
declare uid uuid:=auth.uid();r marketplace_private.identity_requests;
begin
 if uid is null then raise exception 'not_authenticated' using errcode='42501';end if;
 select * into r from marketplace_private.identity_requests where user_id=uid order by created_at desc,id desc limit 1;
 return jsonb_build_object('request',case when r.id is null then null else (to_jsonb(r)-'user_id'-'reviewed_by'-'review_checks'-'upload_attempts')||jsonb_build_object('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,'kind',i.kind,'status',i.status,'checked_at',i.checked_at,'bank_id',i.bank_id)) from marketplace_private.identity_inquiries i where request_id=r.id),'[]'));
end$$;
create or replace function public.marketplace_identity_start() returns jsonb language plpgsql security definer set search_path='' as $$
declare uid uuid:=auth.uid();mobile text;r marketplace_private.identity_requests;
begin
 if uid is null then raise exception 'not_authenticated' using errcode='42501';end if;
 perform pg_advisory_xact_lock(hashtextextended('identity:'||uid::text,0));
 select phone into mobile from auth.users where id=uid and phone_confirmed_at is not null;
 if mobile is null or mobile!~'^\+?989[0-9]{9}$' then raise exception 'verified_mobile_required';end if;
 select * into r from marketplace_private.identity_requests where user_id=uid and status in('draft','pending','documents_approved','verified') order by created_at desc limit 1 for update;
 if r.id is not null and r.phone=mobile then
  if r.status<>'draft' or r.challenge_expires_at>now() then return public.marketplace_identity_state();end if;
 end if;
 if r.id is not null then update marketplace_private.identity_requests set status='superseded',updated_at=now(),version=version+1 where id=r.id;end if;
 if (select count(*) from marketplace_private.identity_requests where user_id=uid and created_at>now()-interval '24 hours')>=5 then raise exception 'identity_rate_limit';end if;
 insert into marketplace_private.identity_requests(user_id,phone,first_name,last_name) select uid,mobile,first_name,last_name from marketplace_private.accounts where user_id=uid;
 if not found then insert into marketplace_private.identity_requests(user_id,phone) values(uid,mobile);end if;
 return public.marketplace_identity_state();
end$$;
create or replace function public.marketplace_identity_save(p_id uuid,p_version integer,p_fields jsonb) returns jsonb language plpgsql security definer set search_path='' as $$
declare r marketplace_private.identity_requests;
begin
 select * into r from marketplace_private.identity_requests where id=p_id and user_id=auth.uid() for update;
 if r.id is null or r.status<>'draft' or r.version is distinct from p_version then raise exception 'identity_conflict';end if;
 if r.challenge_expires_at<=now() then raise exception 'challenge_expired';end if;
 if not marketplace_private.national_id_valid(p_fields->>'national_id')
 or coalesce(p_fields->>'birth_certificate_number','')!~'^[0-9]{1,10}$'
 or coalesce(p_fields->>'postal_code','')!~'^[0-9]{10}$'
 or length(trim(coalesce(p_fields->>'first_name',''))) not between 2 and 80
 or length(trim(coalesce(p_fields->>'last_name',''))) not between 2 and 80
 or length(trim(coalesce(p_fields->>'father_name',''))) not between 2 and 80
 or length(trim(coalesce(p_fields->>'address',''))) not between 15 and 1000 then raise exception 'invalid_identity_fields';end if;
 update marketplace_private.identity_requests set first_name=trim(p_fields->>'first_name'),last_name=trim(p_fields->>'last_name'),national_id=p_fields->>'national_id',birth_certificate_number=p_fields->>'birth_certificate_number',father_name=trim(p_fields->>'father_name'),address=trim(p_fields->>'address'),postal_code=p_fields->>'postal_code',version=version+1,updated_at=now() where id=r.id;
 return public.marketplace_identity_state();
end$$;
create or replace function public.marketplace_identity_upload_slot(p_id uuid,p_kind text,p_extension text) returns jsonb language plpgsql security definer set search_path='' as $$
declare r marketplace_private.identity_requests;path text;previous text;
begin
 select * into r from marketplace_private.identity_requests where id=p_id and user_id=auth.uid() for update;
 if r.id is null or r.status<>'draft' then raise exception 'identity_conflict';end if;
 if r.challenge_expires_at<=now() then raise exception 'challenge_expired';end if;
 if r.upload_attempts>=12 then raise exception 'upload_limit';end if;
 if p_kind not in('card','selfie') or p_kind is null or p_extension not in('jpg','png','webp') or p_extension is null then raise exception 'invalid_document';end if;
 path:=r.user_id::text||'/'||r.id::text||'/'||p_kind||'-'||gen_random_uuid()::text||'.'||p_extension;
 previous:=case p_kind when 'card' then r.card_path else r.selfie_path end;
 update marketplace_private.identity_requests set card_path=case when p_kind='card' then path else card_path end,selfie_path=case when p_kind='selfie' then path else selfie_path end,upload_attempts=upload_attempts+1,version=version+1,updated_at=now() where id=r.id;
 return jsonb_build_object('path',path,'previous_path',previous);
end$$;

insert into storage.buckets(id,name,public,file_size_limit,allowed_mime_types) values('identity-documents','identity-documents',false,5242880,array['image/jpeg','image/png','image/webp']) on conflict(id) do update set public=false,file_size_limit=excluded.file_size_limit,allowed_mime_types=excluded.allowed_mime_types;
create or replace function marketplace_private.identity_file_access(path text,operation text) returns boolean language plpgsql stable security definer set search_path='' as $$
declare r marketplace_private.identity_requests;
begin
 if path!~'^[0-9a-f-]{36}/[0-9a-f-]{36}/(card|selfie)-[0-9a-f-]{36}\.(jpg|png|webp)$' then return false;end if;
 select * into r from marketplace_private.identity_requests where id::text=split_part(path,'/',2) and user_id::text=split_part(path,'/',1);
 if r.id is null then return false;end if;
 if operation='read' then return (r.user_id=auth.uid() and (r.status='draft' or path in(r.card_path,r.selfie_path))) or (public.marketplace_role()='owner' and path in(r.card_path,r.selfie_path));end if;
 if r.user_id<>auth.uid() or auth.uid() is null or r.status<>'draft' then return false;end if;
 if operation='insert' then return r.challenge_expires_at>now() and path in(r.card_path,r.selfie_path);end if;
 if operation='delete' then return path not in(coalesce(r.card_path,''),coalesce(r.selfie_path,''));end if;
 return false;
end$$;
revoke all on function marketplace_private.identity_file_access(text,text) from public,anon;
grant usage on schema marketplace_private to authenticated;
grant execute on function marketplace_private.identity_file_access(text,text) to authenticated;
drop policy if exists identity_documents_read on storage.objects;
create policy identity_documents_read on storage.objects for select to authenticated using(bucket_id='identity-documents' and marketplace_private.identity_file_access(name,'read'));
drop policy if exists identity_documents_insert on storage.objects;
create policy identity_documents_insert on storage.objects for insert to authenticated with check(bucket_id='identity-documents' and marketplace_private.identity_file_access(name,'insert'));
drop policy if exists identity_documents_delete on storage.objects;
create policy identity_documents_delete on storage.objects for delete to authenticated using(bucket_id='identity-documents' and marketplace_private.identity_file_access(name,'delete'));

create or replace function public.marketplace_identity_submit(p_id uuid,p_version integer,p_consent boolean) returns jsonb language plpgsql security definer set search_path='' as $$
declare r marketplace_private.identity_requests;mobile text;
begin
 select * into r from marketplace_private.identity_requests where id=p_id and user_id=auth.uid() for update;
 if r.id is null then raise exception 'identity_conflict';end if;
 if r.status in('pending','documents_approved','verified') then return public.marketplace_identity_state();end if;
 if r.status<>'draft' or r.version is distinct from p_version then raise exception 'identity_conflict';end if;
 select phone into mobile from auth.users where id=r.user_id and phone_confirmed_at is not null for update;
 if mobile is distinct from r.phone then raise exception 'verified_mobile_required';end if;
 if p_consent is distinct from true or r.challenge_expires_at<=now() then raise exception 'challenge_or_consent_required';end if;
 if not marketplace_private.national_id_valid(r.national_id) or r.address is null then raise exception 'invalid_identity_fields';end if;
 if not exists(select 1 from storage.objects where bucket_id='identity-documents' and name=r.card_path) or not exists(select 1 from storage.objects where bucket_id='identity-documents' and name=r.selfie_path) then raise exception 'identity_photos_required';end if;
 update marketplace_private.identity_requests set status='pending',submitted_at=now(),consent_at=now(),version=version+1,updated_at=now() where id=r.id;
 insert into marketplace_private.accounts(user_id,first_name,last_name,verification_status) values(r.user_id,r.first_name,r.last_name,'pending') on conflict(user_id) do update set verification_status='pending',updated_at=now();
 insert into marketplace_private.identity_inquiries(request_id,kind) values(r.id,'identity'),(r.id,'mobile_owner');
 insert into marketplace_private.identity_inquiries(request_id,kind,bank_id,bank_iban) select r.id,'bank_owner',id,iban from marketplace_private.bank_accounts where user_id=r.user_id;
 insert into marketplace_private.dashboard_audit(user_id,action) values(r.user_id,'identity_submitted');
 return public.marketplace_identity_state();
end$$;

create or replace function marketplace_private.identity_finalize(p_id uuid) returns void language plpgsql security definer set search_path='' as $$
declare r marketplace_private.identity_requests;mobile text;
begin
 select * into r from marketplace_private.identity_requests where id=p_id for update;
 if r.status<>'documents_approved' then return;end if;
 select phone into mobile from auth.users where id=r.user_id and phone_confirmed_at is not null for update;
 if mobile is distinct from r.phone then raise exception 'phone_changed';end if;
 if not exists(select 1 from marketplace_private.identity_inquiries where request_id=r.id and kind='identity' and status='passed') or not exists(select 1 from marketplace_private.identity_inquiries where request_id=r.id and kind='mobile_owner' and status='passed') then return;end if;
 update marketplace_private.identity_requests set status='verified',verified_at=now(),updated_at=now(),version=version+1 where id=r.id;
 update marketplace_private.accounts set first_name=r.first_name,last_name=r.last_name,national_id_last4=right(r.national_id,4),verification_status='verified',account_level='primary',identity_verified_at=now(),updated_at=now() where user_id=r.user_id;
 update marketplace_private.bank_accounts b set status='verified' where b.user_id=r.user_id and exists(select 1 from marketplace_private.identity_inquiries i where i.request_id=r.id and i.kind='bank_owner' and i.status='passed' and i.bank_id=b.id and i.bank_iban=b.iban);
 insert into marketplace_private.notifications(user_id,title,body,kind) values(r.user_id,'{"fa":"احراز هویت اولیه تأیید شد"}','{"fa":"مدارک شما و استعلام تطبیق هویت و مالکیت موبایل تأیید شده است."}','account');
end$$;
revoke all on function marketplace_private.identity_finalize(uuid) from public,anon,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') from marketplace_private.identity_inquiries i where request_id=p_id),'[]'));
end$$;
create or replace function public.marketplace_identity_review(p_id uuid,p_version integer,p_decision text,p_note text,p_checks jsonb) returns jsonb language plpgsql security definer set search_path='' as $$
declare r marketplace_private.identity_requests;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 select * into r from marketplace_private.identity_requests where id=p_id for update;
 if r.id is null or r.status not in('pending','documents_approved') or r.version is distinct from p_version then raise exception 'identity_conflict';end if;
 if p_decision is null or p_decision not in('approve','reject') or length(coalesce(p_note,''))>1000 then raise exception 'invalid_review';end if;
 if p_decision='approve' and (coalesce(p_checks->>'face_visible','')<>'true' or coalesce(p_checks->>'card_readable','')<>'true' or coalesce(p_checks->>'challenge_match','')<>'true') then raise exception 'review_checks_required';end if;
 if p_decision='reject' and length(trim(coalesce(p_note,'')))<5 then raise exception 'rejection_reason_required';end if;
 update marketplace_private.identity_requests set status=case when p_decision='approve' then 'documents_approved' else 'rejected' end,review_note=nullif(trim(p_note),''),review_checks=jsonb_build_object('face_visible',p_checks->>'face_visible'='true','card_readable',p_checks->>'card_readable'='true','challenge_match',p_checks->>'challenge_match'='true'),reviewed_by=auth.uid(),reviewed_at=now(),updated_at=now(),version=version+1 where id=r.id;
 if p_decision='reject' then update marketplace_private.accounts set verification_status='rejected',account_level='basic',identity_verified_at=null,updated_at=now() where user_id=r.user_id;else perform marketplace_private.identity_finalize(r.id);end if;
 insert into marketplace_private.audit_log(actor,entity,record_id,operation) values(auth.uid(),'identity',r.id::text,p_decision||'_documents');
 insert into marketplace_private.notifications(user_id,title,body,kind) values(r.user_id,'{"fa":"نتیجه بررسی مدارک احراز هویت"}',jsonb_build_object('fa',case when p_decision='reject' then 'مدارک نیاز به اصلاح دارند. جزئیات را در بخش احراز هویت ببینید.' else 'مدارک تأیید شدند. تکمیل احراز اولیه به نتیجه استعلام‌ها وابسته است.' end),'account');
 return public.marketplace_identity_admin(p_id);
end$$;
create or replace function public.marketplace_identity_document(p_id uuid,p_kind text) returns jsonb language plpgsql security definer set search_path='' as $$
declare r marketplace_private.identity_requests;path text;
begin
 select * into r from marketplace_private.identity_requests where id=p_id;
 if r.id is null or (r.user_id is distinct from auth.uid() and public.marketplace_role() is distinct from 'owner') then raise exception 'not_authorized' using errcode='42501';end if;
 if p_kind is null or p_kind not in('card','selfie') then raise exception 'invalid_document';end if;
 path:=case p_kind when 'card' then r.card_path else r.selfie_path end;
 if r.user_id<>auth.uid() then insert into marketplace_private.audit_log(actor,entity,record_id,operation) values(auth.uid(),'identity',r.id::text,'read_'||p_kind);end if;
 return jsonb_build_object('path',path);
end$$;

-- Provider results are accepted only from a deployed, trusted server adapter.
create or replace function public.marketplace_identity_inquiry_result(p_inquiry uuid,p_service uuid,p_status text,p_reference text) returns void language plpgsql security definer set search_path='' as $$
declare i marketplace_private.identity_inquiries;r marketplace_private.identity_requests;
begin
 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 r.status not in('pending','documents_approved','verified') then raise exception 'inquiry_not_pending';end if;
 if not exists(select 1 from marketplace_private.inquiry_services where id=p_service and scope=i.kind and adapter_status='ready' and vault_secret_id is not null) then raise exception 'adapter_not_ready';end if;
 if p_status is null or p_status not in('passed','failed','error') or p_reference is null or length(p_reference) not between 1 and 200 then raise exception 'invalid_inquiry_result';end if;
 if i.status='passed' then if i.provider_reference=p_reference and i.service_id=p_service and p_status='passed' then return;else raise exception 'inquiry_result_conflict';end if;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;
 update marketplace_private.identity_inquiries set status=p_status,service_id=p_service,provider_reference=p_reference,checked_at=now() where id=i.id;
 if i.kind='bank_owner' and p_status='passed' and exists(select 1 from marketplace_private.identity_requests where id=r.id and status='verified') then
  update marketplace_private.bank_accounts set status='verified' where id=i.bank_id and user_id=r.user_id and iban=i.bank_iban;
 end if;
 perform marketplace_private.identity_finalize(r.id);
end$$;
revoke all on function public.marketplace_identity_inquiry_result(uuid,uuid,text,text) from public,anon,authenticated;
grant execute on function public.marketplace_identity_inquiry_result(uuid,uuid,text,text) to service_role;

create or replace function public.marketplace_inquiry_settings() returns jsonb language plpgsql stable 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;
 return jsonb_build_object('services',coalesce((select jsonb_agg(jsonb_build_object('id',id,'service_key',service_key,'label',label,'scope',scope,'provider_name',provider_name,'base_url',base_url,'has_key',vault_secret_id is not null,'adapter_status',adapter_status,'version',version,'updated_at',updated_at) order by created.service_key) from marketplace_private.inquiry_services created),'[]'));
end$$;
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 then raise exception 'invalid_api_key';end if;
 secret_id:=s.vault_secret_id;
 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,'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$$;

create or replace function marketplace_private.identity_bank_added() returns trigger language plpgsql security definer set search_path='' as $$
declare rid uuid;
begin
 select id into rid from marketplace_private.identity_requests where user_id=new.user_id and status in('pending','documents_approved','verified') order by created_at desc limit 1;
 if rid is not null then insert into marketplace_private.identity_inquiries(request_id,kind,bank_id,bank_iban) values(rid,'bank_owner',new.id,new.iban) on conflict(request_id,kind,(coalesce(bank_iban,''))) do update set bank_id=excluded.bank_id,status='not_connected',service_id=null,provider_reference=null,checked_at=null;end if;
 return new;
end$$;
revoke all on function marketplace_private.identity_bank_added() from public,anon,authenticated;
drop trigger if exists marketplace_identity_bank_added on marketplace_private.bank_accounts;
create trigger marketplace_identity_bank_added after insert on marketplace_private.bank_accounts for each row execute function marketplace_private.identity_bank_added();

create or replace function marketplace_private.identity_phone_changed() returns trigger language plpgsql security definer set search_path='' as $$
begin
 if new.phone is distinct from old.phone or (new.phone_confirmed_at is null and old.phone_confirmed_at is not null) then
  update marketplace_private.identity_requests set status='superseded',version=version+1,updated_at=now() where user_id=new.id and status in('draft','pending','documents_approved','verified');
  update marketplace_private.accounts set verification_status='not_started',account_level='basic',identity_verified_at=null,updated_at=now() where user_id=new.id;
  update marketplace_private.bank_accounts set status='pending' where user_id=new.id and status='verified';
 end if;return new;
end$$;
revoke all on function marketplace_private.identity_phone_changed() from public,anon,authenticated;
drop trigger if exists marketplace_identity_phone_changed on auth.users;
create trigger marketplace_identity_phone_changed after update of phone,phone_confirmed_at on auth.users for each row execute function marketplace_private.identity_phone_changed();

revoke all on function public.marketplace_identity_state(),public.marketplace_identity_start(),public.marketplace_identity_save(uuid,integer,jsonb),public.marketplace_identity_upload_slot(uuid,text,text),public.marketplace_identity_submit(uuid,integer,boolean),public.marketplace_identity_admin(uuid),public.marketplace_identity_review(uuid,integer,text,text,jsonb),public.marketplace_identity_document(uuid,text),public.marketplace_inquiry_settings(),public.marketplace_inquiry_configure(text,text,text,text,text,text,integer) from public,anon;
grant execute on function public.marketplace_identity_state(),public.marketplace_identity_start(),public.marketplace_identity_save(uuid,integer,jsonb),public.marketplace_identity_upload_slot(uuid,text,text),public.marketplace_identity_submit(uuid,integer,boolean),public.marketplace_identity_admin(uuid),public.marketplace_identity_review(uuid,integer,text,text,jsonb),public.marketplace_identity_document(uuid,text),public.marketplace_inquiry_settings(),public.marketplace_inquiry_configure(text,text,text,text,text,text,integer) to authenticated;
notify pgrst,'reload schema';
commit;