begin;
-- Review selections are separate from the level earned by verified evidence.
alter table marketplace_private.identity_requests add column if not exists verification_type text not null default 'identity' check(verification_type in('identity','identity_bank'));
alter table marketplace_private.identity_requests add column if not exists review_stage text not null default 'documents' check(review_stage in('information','documents','inquiries','final'));
alter table marketplace_private.identity_requests add column if not exists verification_level text not null default 'basic' check(verification_level in('basic','primary','full'));
update marketplace_private.identity_requests set review_stage='inquiries' where status='documents_approved' and review_stage='documents';
update marketplace_private.identity_requests set review_stage='information' where status in('rejected','superseded') and verification_level='basic';
update marketplace_private.identity_requests set verification_level='primary',review_stage='final' where status='verified' and verification_level='basic';

create or replace function marketplace_private.identity_has_bank(p_id uuid) returns boolean language sql stable security definer set search_path='' as $$
 select exists(select 1 from marketplace_private.identity_requests r
 join marketplace_private.identity_inquiries i on i.request_id=r.id and i.kind='bank_owner' and i.status='passed'
 join marketplace_private.bank_accounts b on b.id=i.bank_id and b.iban=i.bank_iban and b.user_id=r.user_id
 where r.id=p_id and b.status<>'rejected');
$$;
revoke all on function marketplace_private.identity_has_bank(uuid) from public,anon,authenticated;

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;earned text;stage text;
begin
 select * into r from marketplace_private.identity_requests where id=p_id for update;
 if r.id is null or r.status not in('documents_approved','verified') 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 coalesce(r.review_checks->>'face_visible','')<>'true' or coalesce(r.review_checks->>'card_readable','')<>'true' or coalesce(r.review_checks->>'challenge_match','')<>'true' then return;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;
 earned:=case when r.verification_type='identity_bank' and marketplace_private.identity_has_bank(r.id) then 'full' else 'primary' end;
 stage:=case when r.verification_type='identity_bank' and earned<>'full' then 'inquiries' else 'final' end;
 if r.status<>'verified' or r.verification_level<>earned or r.review_stage<>stage then
  update marketplace_private.identity_requests set status='verified',verification_level=earned,review_stage=stage,verified_at=coalesce(verified_at,now()),updated_at=now(),version=version+1 where id=r.id;
 end if;
 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=earned,identity_verified_at=coalesce(identity_verified_at,now()),updated_at=now()
 where user_id=r.user_id and (verification_status<>'verified' or account_level<>earned);
 update marketplace_private.bank_accounts b set status='verified' where b.user_id=r.user_id and b.status not in('verified','rejected') 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);
 if r.status<>'verified' or r.verification_level<>earned then
  insert into marketplace_private.notifications(user_id,title,body,kind) values(r.user_id,'{"fa":"سطح تأیید حساب به‌روزرسانی شد"}',jsonb_build_object('fa',case earned when 'full' then 'احراز هویت، مالکیت موبایل و حساب بانکی تکمیل شد. سطح حساب شما کامل است.' else 'مدارک، هویت و مالکیت موبایل تأیید شده‌اند. سطح حساب شما اولیه است.' end),'account');
 end if;
end$$;
revoke all on function marketplace_private.identity_finalize(uuid) from public,anon,authenticated;

create or replace function marketplace_private.identity_review_stage_sync() returns trigger language plpgsql security definer set search_path='' as $$
begin
 if new.status in('rejected','superseded') then new.verification_level:='basic';new.review_stage:='information';
 elsif new.status='pending' and old.status='draft' then new.review_stage:='documents';
 elsif new.status='documents_approved' and old.status<>'documents_approved' then new.review_stage:='inquiries';end if;
 return new;
end$$;
revoke all on function marketplace_private.identity_review_stage_sync() from public,anon,authenticated;
drop trigger if exists identity_review_stage_sync on marketplace_private.identity_requests;
create trigger identity_review_stage_sync before update of status on marketplace_private.identity_requests for each row execute function marketplace_private.identity_review_stage_sync();

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,verification_type,review_stage,verification_level,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$$;

-- A separate RPC keeps old review callers compatible, without overloaded signatures.
create or replace function public.marketplace_identity_configure(p_id uuid,p_version integer,p_type text,p_stage text) returns jsonb language plpgsql security definer set search_path='' as $$
declare r marketplace_private.identity_requests;core_ready boolean;
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','verified') or r.version is distinct from p_version then raise exception 'identity_conflict';end if;
 if p_type is null or p_type not in('identity','identity_bank') or p_stage is null or p_stage not in('information','documents','inquiries','final') then raise exception 'invalid_review_configuration';end if;
 if p_stage in('inquiries','final') and r.status='pending' then raise exception 'review_stage_requires_documents';end if;
 -- Once evidence is accepted, a stage selection cannot roll it back or invent it.
 if r.status in('documents_approved','verified') and p_stage in('information','documents') then raise exception 'invalid_review_configuration';end if;
 core_ready:=r.status in('documents_approved','verified')
  and coalesce(r.review_checks->>'face_visible','')='true' and coalesce(r.review_checks->>'card_readable','')='true' and coalesce(r.review_checks->>'challenge_match','')='true'
  and exists(select 1 from marketplace_private.identity_inquiries where request_id=r.id and kind='identity' and status='passed')
  and exists(select 1 from marketplace_private.identity_inquiries where request_id=r.id and kind='mobile_owner' and status='passed');
 if p_stage='final' and (not core_ready or (p_type='identity_bank' and not marketplace_private.identity_has_bank(r.id))) then raise exception 'verification_evidence_required';end if;
 update marketplace_private.identity_requests set verification_type=p_type,review_stage=p_stage,version=version+1,updated_at=now() where id=r.id;
 perform marketplace_private.identity_finalize(r.id);
 insert into marketplace_private.audit_log(actor,entity,record_id,operation) values(auth.uid(),'identity',r.id::text,'configure_'||p_type||'_'||p_stage);
 return public.marketplace_identity_admin(r.id);
end$$;
revoke all on function public.marketplace_identity_configure(uuid,integer,text,text) from public,anon;
grant execute on function public.marketplace_identity_configure(uuid,integer,text,text) to authenticated;

-- Bank deletion/replacement or newly returned bank evidence must update the effective level.
create or replace function marketplace_private.identity_bank_level_sync() returns trigger language plpgsql security definer set search_path='' as $$
declare rid uuid;uid uuid;
begin
 if tg_table_name='identity_inquiries' then
  if new.kind='bank_owner' then perform marketplace_private.identity_finalize(new.request_id);end if;
 else
  uid:=case when tg_op='DELETE' then old.user_id else new.user_id end;
  for rid in select id from marketplace_private.identity_requests where user_id=uid and status='verified' loop perform marketplace_private.identity_finalize(rid);end loop;
 end if;
 return null;
end$$;
revoke all on function marketplace_private.identity_bank_level_sync() from public,anon,authenticated;
drop trigger if exists identity_bank_evidence_level on marketplace_private.identity_inquiries;
create trigger identity_bank_evidence_level after insert or update of status,bank_id,bank_iban on marketplace_private.identity_inquiries for each row execute function marketplace_private.identity_bank_level_sync();
drop trigger if exists identity_bank_deleted_level on marketplace_private.bank_accounts;
create trigger identity_bank_deleted_level after delete on marketplace_private.bank_accounts for each row execute function marketplace_private.identity_bank_level_sync();
drop trigger if exists identity_bank_changed_level on marketplace_private.bank_accounts;
create trigger identity_bank_changed_level after update of iban,status on marketplace_private.bank_accounts for each row when(old.iban is distinct from new.iban or old.status is distinct from new.status) execute function marketplace_private.identity_bank_level_sync();
notify pgrst,'reload schema';
commit;