-- Permanent administrator attestation is explicit evidence, not a fabricated provider response.
-- Requires migrations 003/005; migration 006 is optional and is not replaced by this migration.
begin;
alter table marketplace_private.identity_requests add column if not exists attestation_revision integer not null default 0 check(attestation_revision>=0);
create table if not exists marketplace_private.identity_admin_attestations(
 id bigint generated always as identity primary key,request_id uuid not null references marketplace_private.identity_requests(id) on delete restrict,
 user_id uuid not null references auth.users(id) on delete restrict,kind text not null check(kind in('national_id','mobile_owner','documents')),
 request_version integer not null,evidence_revision integer not null,evidence_fingerprint text not null check(length(evidence_fingerprint)=64),
 approved_by uuid not null references auth.users(id) on delete restrict,approved_at timestamptz not null default now(),note text,
 unique(request_id,request_version,kind)
);
alter table marketplace_private.identity_admin_attestations enable row level security;
revoke all on marketplace_private.identity_admin_attestations from public,anon,authenticated,service_role;
create index if not exists identity_admin_attestations_latest on marketplace_private.identity_admin_attestations(request_id,kind,id desc);

create or replace function marketplace_private.identity_attestation_evidence(p_id uuid,p_kind text) returns jsonb language plpgsql stable security definer set search_path='' as $$
declare r marketplace_private.identity_requests;u auth.users;birthday date;card jsonb;selfie jsonb;common jsonb;begin
 select * into r from marketplace_private.identity_requests where id=p_id;
 if r.id is null or p_kind not in('national_id','mobile_owner','documents') then return null;end if;
 select * into u from auth.users where id=r.user_id;select birth_date into birthday from marketplace_private.accounts where user_id=r.user_id;
 common:=jsonb_build_object('request_id',r.id,'user_id',r.user_id,'revision',r.attestation_revision,'submitted_at',r.submitted_at,'consent_at',r.consent_at);
 if p_kind='national_id' then return common||jsonb_build_object('national_id',r.national_id,'first_name',r.first_name,'last_name',r.last_name,'father_name',r.father_name,'birth_certificate_number',r.birth_certificate_number,'birth_date',birthday);
 elsif p_kind='mobile_owner' then return common||jsonb_build_object('national_id',r.national_id,'request_phone',r.phone,'current_phone',u.phone,'phone_confirmed_at',u.phone_confirmed_at);
 else
  select jsonb_build_object('id',o.id,'path',o.name,'created_at',to_jsonb(o)->'created_at','updated_at',to_jsonb(o)->'updated_at','metadata',to_jsonb(o)->'metadata') into card from storage.objects o where bucket_id='identity-documents' and name=r.card_path;
  select jsonb_build_object('id',o.id,'path',o.name,'created_at',to_jsonb(o)->'created_at','updated_at',to_jsonb(o)->'updated_at','metadata',to_jsonb(o)->'metadata') into selfie from storage.objects o where bucket_id='identity-documents' and name=r.selfie_path;
  return common||jsonb_build_object('card',card,'selfie',selfie,'challenge',r.challenge,'national_id',r.national_id,'first_name',r.first_name,'last_name',r.last_name);
 end if;
end$$;
create or replace function marketplace_private.identity_attestation_fingerprint(p_id uuid,p_kind text) returns text language sql stable security definer set search_path='' as $$
 select encode(sha256(convert_to(marketplace_private.identity_attestation_evidence(p_id,p_kind)::text,'UTF8')),'hex');
$$;
create or replace function marketplace_private.identity_attestation_eligible(p_id uuid,p_kind text) returns boolean language plpgsql stable security definer set search_path='' as $$
declare r marketplace_private.identity_requests;u auth.users;begin
 select * into r from marketplace_private.identity_requests where id=p_id;
 if r.id is null or r.status not in('pending','documents_approved','verified') or r.submitted_at is null or r.consent_at is null then return false;end if;
 if r.id is distinct from (select id from marketplace_private.identity_requests where user_id=r.user_id order by created_at desc,id desc limit 1) then return false;end if;
 select * into u from auth.users where id=r.user_id;
 if u.id is null or u.phone is distinct from r.phone or u.phone_confirmed_at is null or coalesce(r.phone,'')!~'^\+?989[0-9]{9}$' then return false;end if;
 if p_kind='national_id' then return coalesce(marketplace_private.national_id_valid(r.national_id),false) and length(trim(coalesce(r.first_name,'')))>=2 and length(trim(coalesce(r.last_name,'')))>=2;
 elsif p_kind='mobile_owner' then return coalesce(marketplace_private.national_id_valid(r.national_id),false);
 elsif p_kind='documents' then return nullif(trim(r.challenge),'') is not null
  and r.card_path like r.user_id::text||'/'||r.id::text||'/card-%' and r.selfie_path like r.user_id::text||'/'||r.id::text||'/selfie-%'
  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);
 end if;
 return false;
end$$;
create or replace function marketplace_private.identity_attestation_valid(p_id uuid,p_kind text) returns boolean language sql stable security definer set search_path='' as $$
 select coalesce(marketplace_private.identity_attestation_eligible(p_id,p_kind),false) and exists(select 1 from marketplace_private.identity_admin_attestations a
 where a.request_id=p_id and a.kind=p_kind and a.evidence_fingerprint=marketplace_private.identity_attestation_fingerprint(p_id,p_kind));
$$;

-- Both sources retain their actual meaning; no inquiry row is inserted or marked passed here.
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 where r.id=p_id and r.status in('documents_approved','verified')
 and marketplace_private.identity_attestation_eligible(r.id,'national_id') and marketplace_private.identity_attestation_eligible(r.id,'mobile_owner') and marketplace_private.identity_attestation_eligible(r.id,'documents')
 and (marketplace_private.identity_attestation_valid(r.id,'documents') or (r.review_checks->>'face_visible'='true' and r.review_checks->>'card_readable'='true' and r.review_checks->>'challenge_match'='true'))
 and (marketplace_private.identity_attestation_valid(r.id,'national_id') or exists(select 1 from marketplace_private.identity_inquiries where request_id=r.id and kind='identity' and status='passed'))
 and (marketplace_private.identity_attestation_valid(r.id,'mobile_owner') or exists(select 1 from marketplace_private.identity_inquiries where request_id=r.id and kind='mobile_owner' and status='passed')));
$$;

create or replace function public.marketplace_identity_attestations(p_id uuid) returns jsonb language plpgsql stable security definer set search_path='' as $$
declare r marketplace_private.identity_requests;rows jsonb:='[]';v_kind text;eligible boolean;approved boolean;source text;reviewed timestamptz;why text;all_eligible boolean:=true;can_edit 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;
 if r.id is null then raise exception 'identity_not_found';end if;
 can_edit:=not marketplace_private.customer_access_protected(r.user_id) and exists(select 1 from public.marketplace_customer_access where user_id=r.user_id and status='active');
 foreach v_kind in array array['national_id','mobile_owner','documents'] loop
  eligible:=coalesce(marketplace_private.identity_attestation_eligible(r.id,v_kind),false);approved:=false;source:=null;reviewed:=null;why:=null;
  if eligible and marketplace_private.identity_attestation_valid(r.id,v_kind) then
   approved:=true;source:='manual';select approved_at into reviewed from marketplace_private.identity_admin_attestations where request_id=r.id and identity_admin_attestations.kind=v_kind and evidence_fingerprint=marketplace_private.identity_attestation_fingerprint(r.id,v_kind) order by id desc limit 1;
  elsif eligible and v_kind='documents' and r.review_checks->>'face_visible'='true' and r.review_checks->>'card_readable'='true' and r.review_checks->>'challenge_match'='true' then approved:=true;source:='documents';reviewed:=r.reviewed_at;
  elsif eligible and v_kind in('national_id','mobile_owner') then
   select checked_at into reviewed from marketplace_private.identity_inquiries where request_id=r.id and identity_inquiries.kind=case v_kind when 'national_id' then 'identity' else 'mobile_owner' end and status='passed' order by checked_at desc nulls last limit 1;
   if found then approved:=true;source:='provider';end if;
  end if;
  if not can_edit then why:='حساب محافظت‌شده یا دسترسی آن غیرفعال است.';
  elsif not eligible then why:=case v_kind when 'national_id' then 'درخواست ارسال‌شده، کد ملی معتبر و مشخصات فعلی لازم است.' when 'mobile_owner' then 'شماره تأییدشده فعلی حساب باید با درخواست یکسان باشد.' else 'هر دو تصویر کارت ملی و چهره همراه کد باید برای همین درخواست بارگذاری شده باشند.' end;
  end if;
  rows:=rows||jsonb_build_array(jsonb_build_object('kind',v_kind,'eligible',eligible and can_edit,'approved',approved,'source',source,'reviewed_at',reviewed,'reason',why));
  all_eligible:=all_eligible and eligible;
 end loop;
 return jsonb_build_object('request_id',r.id,'version',r.version,'rows',rows,'temporary_available',to_regprocedure('public.marketplace_identity_temporary_review(uuid,integer,text,integer,text,boolean)') is not null and to_regprocedure('public.marketplace_identity_temporary_revoke(uuid,integer,text)') is not null,'can_approve_primary',can_edit and all_eligible,'can_approve_full',can_edit and all_eligible and marketplace_private.identity_has_bank(r.id));
end$$;

create or replace function public.marketplace_identity_attestation_review(p_id uuid,p_version integer,p_level text,p_note text,p_checks jsonb) returns jsonb language plpgsql security definer set search_path='' as $$
declare r marketplace_private.identity_requests;v_kind text;result jsonb;before_version integer;begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if p_level is null or p_level not in('primary','full') or length(coalesce(p_note,''))>1000 or p_checks is null or jsonb_typeof(p_checks)<>'object' or p_checks is distinct from '{"national_id":true,"mobile_owner":true,"documents":true}'::jsonb then raise exception 'invalid_attestation_review';end if;
 select * into r from marketplace_private.identity_requests where id=p_id;
 if r.id is null then raise exception 'identity_not_found';end if;
 if marketplace_private.customer_access_protected(r.user_id) then raise exception 'access_protected_account' using errcode='42501';end if;
 perform marketplace_private.customer_access_assert(r.user_id);
 select * into r from marketplace_private.identity_requests where id=p_id for update;
 if r.version is distinct from p_version or r.status not in('pending','documents_approved','verified') then raise exception 'identity_conflict';end if;
 perform 1 from auth.users where id=r.user_id for update;
 perform 1 from marketplace_private.accounts where user_id=r.user_id for update;
 -- Lock the actual objects through the decision so an in-flight replacement cannot bypass the fingerprint.
 perform 1 from storage.objects where bucket_id='identity-documents' and name in(r.card_path,r.selfie_path) for share;
 if not marketplace_private.identity_attestation_eligible(r.id,'national_id') or not marketplace_private.identity_attestation_eligible(r.id,'mobile_owner') or not marketplace_private.identity_attestation_eligible(r.id,'documents') then raise exception 'attestation_evidence_required';end if;
 if p_level='full' and not marketplace_private.identity_has_bank(r.id) then raise exception 'verification_evidence_required';end if;
 before_version:=r.version;
 foreach v_kind in array array['national_id','mobile_owner','documents'] loop
  insert into marketplace_private.identity_admin_attestations(request_id,user_id,kind,request_version,evidence_revision,evidence_fingerprint,approved_by,note)
  values(r.id,r.user_id,v_kind,before_version,r.attestation_revision,marketplace_private.identity_attestation_fingerprint(r.id,v_kind),auth.uid(),nullif(trim(p_note),''));
 end loop;
 -- The documents row is an explicit human review; legacy checklist flags and provider responses stay intact.
 update marketplace_private.identity_requests set status=case when status='pending' then 'documents_approved' else status end,reviewed_by=auth.uid(),reviewed_at=now(),review_note=nullif(trim(p_note),''),version=version+1,updated_at=now() where id=r.id returning * into r;
 result:=public.marketplace_identity_final_review(r.id,r.version,p_level,'approve',p_note);
 if result->'request'->>'status' is distinct from 'verified' or result->'request'->>'verification_level' is distinct from p_level then raise exception 'attestation_approval_failed';end if;
 insert into marketplace_private.audit_log(actor,entity,record_id,operation) values(auth.uid(),'identity_attestations',r.id::text,'manual_final_'||p_level);
 insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,before_data,after_data,changed_fields,actor_id,actor_kind,source_key)
 values(r.user_id,'identity','manually_attested','identity_admin_attestations',r.id::text,jsonb_build_object('version',before_version),jsonb_build_object('level',p_level,'national_id',true,'mobile_owner',true,'documents',true,'source','manual','note',nullif(trim(p_note),'')),array['national_id','mobile_owner','documents'],auth.uid(),'admin','identity-manual:'||r.id||':'||before_version);
 return result||jsonb_build_object('attestations',public.marketplace_identity_attestations(r.id));
end$$;

-- Evidence generations prevent an old manual decision from becoming valid again after an edit-and-restore.
create or replace function marketplace_private.identity_attestation_revision() returns trigger language plpgsql security definer set search_path='' as $$
begin
 if (to_jsonb(new)-array['status','version','reviewed_by','reviewed_at','review_note','review_checks','verified_at','created_at','updated_at','verification_type','review_stage','verification_level','final_approved_level','final_decided_by','final_decided_at','final_review_note','verification_mode','temporary_level','temporary_approved_by','temporary_approved_at','temporary_expires_at','temporary_reason','upload_attempts'])
 is distinct from (to_jsonb(old)-array['status','version','reviewed_by','reviewed_at','review_note','review_checks','verified_at','created_at','updated_at','verification_type','review_stage','verification_level','final_approved_level','final_decided_by','final_decided_at','final_review_note','verification_mode','temporary_level','temporary_approved_by','temporary_approved_at','temporary_expires_at','temporary_reason','upload_attempts'])
 or (new.status in('rejected','superseded') and new.status is distinct from old.status) then
  new.attestation_revision:=old.attestation_revision+1;new.review_checks:='{}';
 end if;
 return new;
end$$;
drop trigger if exists identity_attestation_revision on marketplace_private.identity_requests;
create trigger identity_attestation_revision before update on marketplace_private.identity_requests for each row execute function marketplace_private.identity_attestation_revision();
create or replace function marketplace_private.identity_attestation_reconcile() returns trigger language plpgsql security definer set search_path='' as $$
begin
 if new.attestation_revision is distinct from old.attestation_revision and new.status in('documents_approved','verified') then perform marketplace_private.identity_finalize(new.id);end if;
 return null;
end$$;
drop trigger if exists identity_attestation_reconcile on marketplace_private.identity_requests;
create trigger identity_attestation_reconcile after update on marketplace_private.identity_requests for each row execute function marketplace_private.identity_attestation_reconcile();
create or replace function marketplace_private.identity_attestation_related_change() returns trigger language plpgsql security definer set search_path='' as $$
declare uid uuid;path text;material boolean;begin
 if tg_table_schema='storage' then
  if old.bucket_id<>'identity-documents' then return null;end if;
  path:=old.name;
  material:=tg_op='DELETE';
  if tg_op='UPDATE' then material:=old.name is distinct from new.name or old.bucket_id is distinct from new.bucket_id or to_jsonb(old)->'metadata' is distinct from to_jsonb(new)->'metadata' or to_jsonb(old)->'updated_at' is distinct from to_jsonb(new)->'updated_at';end if;
  if material then update marketplace_private.identity_requests set attestation_revision=attestation_revision+1,version=version+1,updated_at=now() where status in('pending','documents_approved','verified') and path in(card_path,selfie_path);end if;
 elsif tg_table_schema='auth' then
  if new.phone is distinct from old.phone or new.phone_confirmed_at is distinct from old.phone_confirmed_at then update marketplace_private.identity_requests set attestation_revision=attestation_revision+1,version=version+1,updated_at=now() where user_id=new.id and status in('pending','documents_approved','verified');end if;
 else
  if new.birth_date is distinct from old.birth_date then update marketplace_private.identity_requests set attestation_revision=attestation_revision+1,version=version+1,updated_at=now() where user_id=new.user_id and status in('pending','documents_approved','verified');end if;
 end if;
 return null;
end$$;
drop trigger if exists identity_attestation_storage_change on storage.objects;
create trigger identity_attestation_storage_change after update or delete on storage.objects for each row execute function marketplace_private.identity_attestation_related_change();
drop trigger if exists identity_attestation_phone_change on auth.users;
create trigger identity_attestation_phone_change after update of phone,phone_confirmed_at on auth.users for each row execute function marketplace_private.identity_attestation_related_change();
drop trigger if exists identity_attestation_birth_date_change on marketplace_private.accounts;
create trigger identity_attestation_birth_date_change after update of birth_date on marketplace_private.accounts for each row execute function marketplace_private.identity_attestation_related_change();

-- Keep source wording truthful, preserving the rest of whichever notification implementation is installed.
do $$declare definition text;marker text;branch text;begin
 definition:=pg_get_functiondef('marketplace_private.identity_notification_trigger()'::regprocedure);
 marker:=$m$elsif new.status='verified' and$m$;
 if position('identity_attestation_valid' in definition)=0 then
  branch:=$b$elsif new.status='verified' and coalesce(to_jsonb(new)->>'verification_mode','standard')='standard'
   and not(old.verification_level='full' and new.verification_level<>'full')
   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)
   and marketplace_private.identity_attestation_valid(new.id,'national_id') and marketplace_private.identity_attestation_valid(new.id,'mobile_owner') and marketplace_private.identity_attestation_valid(new.id,'documents') then
  heading:='احراز هویت با بررسی مدیر تأیید شد';level:='success';
  message:='بررسی کد ملی، مالکیت موبایل و تصاویر مدارک توسط مدیر ثبت شد. سطح دائمی حساب شما '||case new.verification_level when 'full' then 'کامل است؛ تأیید معتبر حساب بانکی نیز ثبت شده است.' else 'اولیه است.' end||' نتیجه سرویس‌های استعلام جداگانه در پرونده باقی می‌ماند.';
  if nullif(trim(new.final_review_note),'') is not null then message:=message||E'\nپیام مدیر: '||new.final_review_note;end if;
 elsif new.status='verified' and$b$;
  if position(marker in definition)=0 then raise exception 'identity_notification_contract_missing';end if;
  definition:=replace(definition,marker,branch);
  definition:=replace(definition,$m$elsif new.status='documents_approved' and$m$,$b$elsif new.status='documents_approved' and old.status<>'verified'
   and marketplace_private.identity_attestation_valid(new.id,'national_id') and marketplace_private.identity_attestation_valid(new.id,'mobile_owner') and marketplace_private.identity_attestation_valid(new.id,'documents') then return null;
 elsif new.status='documents_approved' and$b$);
  execute definition;
 end if;
end$$;

-- Existing final/readiness RPCs use core_ready. Configuration also needs the same manual/provider semantics.
do $$declare definition text;start_at integer;end_at integer;begin
 definition:=pg_get_functiondef('public.marketplace_identity_configure(uuid,integer,text,text)'::regprocedure);
 start_at:=position('core_ready:=' in definition);end_at:=position('if p_stage=' in substring(definition from start_at));
 if start_at>0 and end_at>0 then definition:=substring(definition from 1 for start_at-1)||E'core_ready:=marketplace_private.identity_core_ready(r.id);\n '||substring(definition from start_at+end_at-1);execute definition;end if;
end$$;

drop trigger if exists identity_attestations_immutable on marketplace_private.identity_admin_attestations;
create trigger identity_attestations_immutable before update or delete on marketplace_private.identity_admin_attestations for each row execute function marketplace_private.customer_record_retained();
drop trigger if exists identity_attestations_retained on marketplace_private.identity_admin_attestations;
create trigger identity_attestations_retained before truncate on marketplace_private.identity_admin_attestations for each statement execute function marketplace_private.customer_record_retained();
do $$declare f record;begin
 for f in select p.oid::regprocedure signature from pg_proc p join pg_namespace n on n.oid=p.pronamespace where n.nspname='marketplace_private' and (p.proname like 'identity_attestation_%' or p.proname='identity_core_ready') loop execute format('revoke all on function %s from public,anon,authenticated,service_role',f.signature);end loop;
end$$;
revoke all on function public.marketplace_identity_attestations(uuid),public.marketplace_identity_attestation_review(uuid,integer,text,text,jsonb) from public,anon,authenticated,service_role;
grant execute on function public.marketplace_identity_attestations(uuid),public.marketplace_identity_attestation_review(uuid,integer,text,text,jsonb) to authenticated;
notify pgrst,'reload schema';
commit;