begin;
-- Evidence completion and the administrator's final decision are separate steps.
alter table marketplace_private.identity_requests add column if not exists final_approved_level text check(final_approved_level in('primary','full'));
alter table marketplace_private.identity_requests add column if not exists final_decided_by uuid references auth.users(id);
alter table marketplace_private.identity_requests add column if not exists final_decided_at timestamptz;
alter table marketplace_private.identity_requests add column if not exists final_review_note text;
-- Preserve approvals that predate the explicit final-review workflow.
update marketplace_private.identity_requests set final_approved_level=verification_level,final_decided_by=reviewed_by,final_decided_at=coalesce(verified_at,reviewed_at,updated_at)
where status='verified' and verification_level in('primary','full') and final_decided_at is null;

create or replace function marketplace_private.identity_core_ready(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 auth.users u on u.id=r.user_id
 where r.id=p_id and r.status in('documents_approved','verified') and u.phone=r.phone and u.phone_confirmed_at is not null
 and r.review_checks->>'face_visible'='true' and r.review_checks->>'card_readable'='true' and r.review_checks->>'challenge_match'='true'
 and exists(select 1 from storage.objects where bucket_id='identity-documents' and name=r.card_path)
 and exists(select 1 from storage.objects where bucket_id='identity-documents' and name=r.selfie_path)
 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'));
$$;
revoke all on function marketplace_private.identity_core_ready(uuid) from public,anon,authenticated;

-- This routine can retain or revoke a grant; it cannot create an administrator's decision.
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;earned text;stage text;next_status 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;
 earned:='basic';
 if r.final_approved_level is not null and marketplace_private.identity_core_ready(r.id) then
  earned:=case when r.final_approved_level='full' and marketplace_private.identity_has_bank(r.id) then 'full' else 'primary' end;
 end if;
 next_status:=case when earned='basic' then 'documents_approved' else 'verified' end;
 stage:=case when earned='basic' or (r.verification_type='identity_bank' and earned<>'full') then 'inquiries' else 'final' end;
 if r.status<>next_status or r.verification_level<>earned or r.review_stage<>stage or (earned='basic' and r.final_approved_level is not null) then
  update marketplace_private.identity_requests set status=next_status,verification_level=earned,review_stage=stage,
   final_approved_level=case when earned='basic' then null else earned end,
   verified_at=case when earned='basic' then null else coalesce(verified_at,now()) end,updated_at=now(),version=version+1 where id=r.id;
 end if;
 update marketplace_private.accounts set first_name=case when earned='basic' then first_name else r.first_name end,last_name=case when earned='basic' then last_name else r.last_name end,
  national_id_last4=case when earned='basic' then national_id_last4 else right(r.national_id,4) end,
  verification_status=case when earned='basic' then 'pending' else 'verified' end,account_level=earned,
  identity_verified_at=case when earned='basic' then null else coalesce(identity_verified_at,now()) end,updated_at=now()
 where user_id=r.user_id and (account_level<>earned or verification_status<>case when earned='basic' then 'pending' else 'verified' end);
 if earned<>'basic' then
  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);
 end if;
end$$;
revoke all on function marketplace_private.identity_finalize(uuid) from public,anon,authenticated;

create or replace function public.marketplace_identity_final_review(p_id uuid,p_version integer,p_level text,p_decision text,p_note text) 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','verified') or r.version is distinct from p_version then raise exception 'identity_conflict';end if;
 if p_level is null or p_level not in('primary','full') or p_decision is null or p_decision not in('approve','reject') or length(coalesce(p_note,''))>1000 then raise exception 'invalid_final_review';end if;
 if p_decision='reject' and length(trim(coalesce(p_note,'')))<5 then raise exception 'rejection_reason_required';end if;
 if p_decision='approve' then
  -- The owner chooses the granted level, never the inquiry outcome.
  perform 1 from auth.users where id=r.user_id for update;
  if not marketplace_private.identity_core_ready(r.id) or (p_level='full' and not marketplace_private.identity_has_bank(r.id)) then raise exception 'verification_evidence_required';end if;
  update marketplace_private.identity_requests set final_approved_level=p_level,final_decided_by=auth.uid(),final_decided_at=now(),final_review_note=nullif(trim(p_note),''),
   verification_type=case when p_level='full' then 'identity_bank' else 'identity' end,review_note=nullif(trim(p_note),''),version=version+1,updated_at=now() where id=r.id;
  perform marketplace_private.identity_finalize(r.id);
 else
  update marketplace_private.identity_requests set status='rejected',final_approved_level=null,final_decided_by=auth.uid(),final_decided_at=now(),final_review_note=trim(p_note),review_note=trim(p_note),
   reviewed_by=auth.uid(),reviewed_at=now(),verified_at=null,version=version+1,updated_at=now() where id=r.id;
  update marketplace_private.accounts set verification_status='rejected',account_level='basic',identity_verified_at=null,updated_at=now() where user_id=r.user_id;
 end if;
 insert into marketplace_private.audit_log(actor,entity,record_id,operation) values(auth.uid(),'identity',r.id::text,'final_'||p_decision||'_'||p_level);
 return public.marketplace_identity_admin(r.id);
end$$;
revoke all on function public.marketplace_identity_final_review(uuid,integer,text,text,text) from public,anon;
grant execute on function public.marketplace_identity_final_review(uuid,integer,text,text,text) to authenticated;

-- Any evidence revocation removes its effective grant. Restored evidence requires a new final decision.
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
  perform marketplace_private.identity_finalize(case when tg_op='DELETE' then old.request_id else new.request_id end);
 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$$;
drop trigger if exists identity_deleted_evidence_level on marketplace_private.identity_inquiries;
create trigger identity_deleted_evidence_level after delete on marketplace_private.identity_inquiries for each row execute function marketplace_private.identity_bank_level_sync();

-- Keep internal reviewer identifiers private in the customer's view.
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'-'final_decided_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 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';new.final_approved_level:=null;new.verified_at:=null;
 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$$;

create or replace function marketplace_private.identity_photo_deleted() returns trigger language plpgsql security definer set search_path='' as $$
declare rid uuid;
begin
 if old.bucket_id='identity-documents' then
  for rid in select id from marketplace_private.identity_requests where status='verified' and old.name in(card_path,selfie_path) loop perform marketplace_private.identity_finalize(rid);end loop;
 end if;
 return null;
end$$;
revoke all on function marketplace_private.identity_photo_deleted() from public,anon,authenticated;
drop trigger if exists identity_deleted_photo_level on storage.objects;
create trigger identity_deleted_photo_level after delete on storage.objects for each row execute function marketplace_private.identity_photo_deleted();

create or replace function marketplace_private.identity_notification_trigger()
returns trigger language plpgsql security definer set search_path='' as $$
declare heading text;message text;level text:='info';event text;
begin
 event:='identity:'||new.id||':'||new.version||':'||new.status;
 if new.status='pending' and old.status='draft' then
  heading:='درخواست احراز هویت ارسال شد';message:='اطلاعات و مدارک شما برای بررسی به تیم احراز هویت ارسال شد. نتیجه و هرگونه درخواست اصلاح در همین صندوق نمایش داده می‌شود.';
  insert into marketplace_private.admin_notifications(event_key,event_type,user_id,request_id,title,body,action_url)
  values(event,'identity_submitted',new.user_id,new.id,'{"fa":"درخواست احراز هویت جدید"}',jsonb_build_object('fa',trim(coalesce(new.first_name,'')||' '||coalesce(new.last_name,''))||' مدارک احراز هویت را ارسال کرده است.'),'/?section=identity&request='||new.id) on conflict(event_key) do nothing;
 elsif new.status='documents_approved' and (old.status is distinct from new.status or old.reviewed_at is distinct from new.reviewed_at) then
  if old.status='verified' then heading:='احراز هویت نیاز به بررسی دوباره دارد';level:='warning';message:='اعتبار یکی از مدارک یا استعلام‌های لازم تغییر کرده است. سطح حساب تا بررسی و تأیید نهایی دوباره مدیر، پایه است.';
  else heading:='مدارک احراز هویت تأیید شد';level:='success';message:='مدارک شما توسط کارشناس تأیید شد. پس از تکمیل استعلام‌ها، مدیر سطح نهایی حساب را بررسی و تأیید می‌کند.';end if;
  if nullif(trim(new.review_note),'') is not null then message:=message||E'\n\nپیام کارشناس: '||new.review_note;end if;
 elsif new.status='rejected' and (old.status is distinct from new.status or old.reviewed_at is distinct from new.reviewed_at) then
  heading:='مدارک احراز هویت نیاز به اصلاح دارد';level:='warning';message:=coalesce(nullif(trim(new.review_note),''),'لطفاً اطلاعات و مدارک خود را در بخش احراز هویت بررسی و دوباره ارسال کنید.');
 elsif new.status='verified' and (old.status is distinct from new.status or old.verification_level is distinct from new.verification_level or old.final_decided_at is distinct from new.final_decided_at) then
  heading:='سطح تأیید حساب به‌روزرسانی شد';level:=case when old.verification_level='full' and new.verification_level<>'full' then 'warning' else 'success' end;
  message:=case when new.verification_level='full' then 'احراز هویت، مالکیت موبایل و حساب بانکی تکمیل شد. سطح حساب شما کامل است.' when old.verification_level='full' then 'سطح حساب شما اولیه است. برای بازگشت به سطح کامل، تأیید مالکیت حساب بانکی و تأیید نهایی مدیر را تکمیل کنید.' else 'مدارک، هویت و مالکیت موبایل تأیید شده‌اند. سطح حساب شما اولیه است.' end;
  if nullif(trim(new.final_review_note),'') is not null then message:=message||E'\n\nپیام مدیر: '||new.final_review_note;end if;
 elsif new.status='superseded' and old.status not in('draft','superseded') then
  heading:='احراز هویت نیاز به بررسی دوباره دارد';level:='warning';message:='شرایط حساب یا شماره موبایل تغییر کرده است. برای ادامه، وضعیت احراز هویت خود را بررسی کنید.';
 elsif new.status=old.status and (new.verification_type is distinct from old.verification_type or new.review_stage is distinct from old.review_stage) then
  heading:='وضعیت بررسی احراز هویت به‌روزرسانی شد';message:='تیم احراز هویت مرحله بررسی پرونده را به‌روزرسانی کرده است. وضعیت فعلی و استعلام‌ها را در بخش احراز هویت ببینید.';
 end if;
 if heading is not null then perform marketplace_private.customer_notify(new.user_id,event,heading,message,level,new.id,auth.uid(),'تیم احراز هویت پلانک');end if;
 return new;
end$$;

notify pgrst,'reload schema';
commit;