-- Time-limited managerial approval is separate from evidence-backed permanent KYC.
begin;
alter table marketplace_private.identity_requests add column if not exists verification_mode text not null default 'standard' check(verification_mode in('standard','temporary'));
alter table marketplace_private.identity_requests add column if not exists temporary_level text check(temporary_level in('primary','full'));
alter table marketplace_private.identity_requests add column if not exists temporary_approved_by uuid references auth.users(id) on delete restrict;
alter table marketplace_private.identity_requests add column if not exists temporary_approved_at timestamptz;
alter table marketplace_private.identity_requests add column if not exists temporary_expires_at timestamptz;
alter table marketplace_private.identity_requests add column if not exists temporary_reason text;
alter table marketplace_private.accounts add column if not exists verification_mode text not null default 'standard' check(verification_mode in('standard','temporary'));
alter table marketplace_private.accounts add column if not exists temporary_level text check(temporary_level in('primary','full'));
alter table marketplace_private.accounts add column if not exists temporary_expires_at timestamptz;
create table if not exists marketplace_private.identity_temporary_history(
 id bigint generated always as identity primary key,user_id uuid not null references auth.users(id) on delete restrict,
 request_id uuid not null references marketplace_private.identity_requests(id) on delete restrict,
 request_version integer not null,event text not null check(event in('granted','expired','revoked','standardized','invalidated')),
 before_data jsonb not null,after_data jsonb not null,reason text not null,actor_id uuid references auth.users(id) on delete restrict,
 responsibility_accepted boolean not null default false,created_at timestamptz not null default now(),unique(request_id,request_version)
);
alter table marketplace_private.identity_temporary_history enable row level security;
revoke all on marketplace_private.identity_temporary_history from public,anon,authenticated,service_role;
create or replace function marketplace_private.identity_temporary_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('pending','documents_approved','verified') and r.submitted_at is not null and r.consent_at is not null
 and u.phone=r.phone and u.phone_confirmed_at is not null
 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 r.id=(select id from marketplace_private.identity_requests where user_id=r.user_id order by created_at desc,id desc limit 1));
$$;
create or replace function marketplace_private.identity_standard_level(p_id uuid) returns text language sql stable security definer set search_path='' as $$
 select case when r.final_approved_level is not null and marketplace_private.identity_core_ready(r.id)
 then case when r.final_approved_level='full' and marketplace_private.identity_has_bank(r.id) then 'full' else 'primary' end else 'basic' end
 from marketplace_private.identity_requests r where r.id=p_id;
$$;
-- Temporary approvals never populate final_approved_level, review_checks or inquiry outcomes.
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;effective text;mode text;stage text;next_status text;temporary_active boolean;reviewed boolean;
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('pending','documents_approved','verified') then return;end if;
 earned:=marketplace_private.identity_standard_level(r.id);
 temporary_active:=r.verification_mode='temporary' and r.temporary_level in('primary','full') and r.temporary_approved_by is not null and r.temporary_approved_at is not null and r.temporary_expires_at>now() and marketplace_private.identity_temporary_ready(r.id);
 effective:=case when temporary_active and (r.temporary_level='full' or earned='basic') then r.temporary_level else earned end;
 mode:=case when temporary_active then 'temporary' else 'standard' end;
 reviewed:=r.review_checks->>'face_visible'='true' and r.review_checks->>'card_readable'='true' and r.review_checks->>'challenge_match'='true';
 next_status:=case when effective<>'basic' then 'verified' when coalesce(reviewed,false) then 'documents_approved' else 'pending' end;
 stage:=case when effective<>'basic' then 'final' when coalesce(reviewed,false) then 'inquiries' else 'documents' end;
 if r.status<>next_status or r.verification_level<>effective or r.review_stage<>stage or r.verification_mode<>mode or r.final_approved_level is distinct from nullif(earned,'basic') then
  update marketplace_private.identity_requests set status=next_status,verification_level=effective,review_stage=stage,verification_mode=mode,
   final_approved_level=nullif(earned,'basic'),verified_at=case when effective='basic' then null else coalesce(verified_at,now()) end,
   temporary_level=case when temporary_active then temporary_level end,temporary_approved_by=case when temporary_active then temporary_approved_by end,
   temporary_approved_at=case when temporary_active then temporary_approved_at end,temporary_expires_at=case when temporary_active then temporary_expires_at end,
   temporary_reason=case when temporary_active then temporary_reason end,updated_at=now(),version=version+1 where id=r.id;
 end if;
 update marketplace_private.accounts set first_name=case when effective='basic' then first_name else r.first_name end,last_name=case when effective='basic' then last_name else r.last_name end,
  national_id_last4=case when effective='basic' then national_id_last4 else right(r.national_id,4) end,
  verification_status=case when effective='basic' then 'pending' else 'verified' end,account_level=effective,verification_mode=mode,
  temporary_level=case when temporary_active then r.temporary_level end,temporary_expires_at=case when temporary_active then r.temporary_expires_at end,
  identity_verified_at=case when effective='basic' then null else coalesce(identity_verified_at,now()) end,updated_at=now()
 where user_id=r.user_id and (account_level<>effective or verification_status<>case when effective='basic' then 'pending' else 'verified' end or verification_mode<>mode or temporary_expires_at is distinct from case when temporary_active then r.temporary_expires_at end or temporary_level is distinct from case when temporary_active then r.temporary_level end);
 -- Only actual permanent evidence may verify a bank. A temporary full level does not verify bank ownership.
 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$$;
create or replace function marketplace_private.identity_temporary_sync(p_user uuid default null) returns void language plpgsql security definer set search_path='' as $$
declare rid uuid;begin
 for rid in select id from marketplace_private.identity_requests where verification_mode='temporary' and (p_user is null or user_id=p_user) order by id loop perform marketplace_private.identity_finalize(rid);end loop;
end$$;

create or replace function public.marketplace_identity_temporary_review(p_id uuid,p_version integer,p_level text,p_hours integer,p_reason text,p_accept_responsibility boolean) returns jsonb language plpgsql security definer set search_path='' as $$
declare r marketplace_private.identity_requests;earned text;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 p_hours is null or p_hours not in(24,72,168,720) or length(trim(coalesce(p_reason,''))) not between 10 and 1000 or p_accept_responsibility is distinct from true then raise exception 'invalid_temporary_review';end if;
 select * into r from marketplace_private.identity_requests where id=p_id for update;
 if r.id is null or r.version is distinct from p_version or r.status not in('pending','documents_approved','verified') then raise exception 'identity_conflict';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);
 -- Access synchronization can expire a preceding grant; never overwrite that newer version.
 select * into r from marketplace_private.identity_requests where id=p_id for update;
 if r.version is distinct from p_version then raise exception 'identity_conflict';end if;
 perform 1 from auth.users where id=r.user_id for update;
 if not marketplace_private.identity_temporary_ready(r.id) then raise exception 'temporary_evidence_required';end if;
 earned:=marketplace_private.identity_standard_level(r.id);
 if earned='full' or (earned='primary' and p_level='primary') then raise exception 'temporary_level_not_upgrade';end if;
 update marketplace_private.identity_requests set status='verified',verification_level=p_level,review_stage='final',verified_at=coalesce(verified_at,now()),verification_mode='temporary',temporary_level=p_level,temporary_approved_by=auth.uid(),temporary_approved_at=now(),temporary_expires_at=now()+make_interval(hours=>p_hours),temporary_reason=trim(p_reason),version=version+1,updated_at=now() where id=r.id;
 perform marketplace_private.identity_finalize(r.id);
 return public.marketplace_identity_admin(r.id);
end$$;
create or replace function public.marketplace_identity_temporary_revoke(p_id uuid,p_version integer,p_reason 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;
 if length(trim(coalesce(p_reason,''))) not between 10 and 1000 then raise exception 'invalid_temporary_review';end if;
 select * into r from marketplace_private.identity_requests where id=p_id for update;
 if r.id is null or r.version is distinct from p_version or r.verification_mode<>'temporary' then raise exception 'identity_conflict';end if;
 if marketplace_private.customer_access_protected(r.user_id) then raise exception 'access_protected_account' using errcode='42501';end if;
 update marketplace_private.identity_requests set verification_mode='standard',temporary_level=null,temporary_approved_by=null,temporary_approved_at=null,temporary_expires_at=null,temporary_reason=null,review_note=trim(p_reason),version=version+1,updated_at=now() where id=r.id;
 perform marketplace_private.identity_finalize(r.id);
 return public.marketplace_identity_admin(r.id);
end$$;

-- The administrator must be able to complete genuine document/evidence review while a temporary grant is active.
do $$declare definition text;begin
 definition:=pg_get_functiondef('public.marketplace_identity_review(uuid,integer,text,text,jsonb)'::regprocedure);
 if position($x$r.verification_mode='temporary'$x$ in definition)=0 then definition:=replace(definition,$x$r.status not in('pending','documents_approved')$x$,$x$(r.status not in('pending','documents_approved') and not(r.status='verified' and r.verification_mode='temporary'))$x$);end if;
 execute definition;
end$$;

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 status='verified',verification_level=p_level,review_stage='final',verified_at=coalesce(verified_at,now()),verification_mode='standard',temporary_level=null,temporary_approved_by=null,temporary_approved_at=null,temporary_expires_at=null,temporary_reason=null,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 verification_mode='standard',temporary_level=null,temporary_approved_by=null,temporary_approved_at=null,temporary_expires_at=null,temporary_reason=null,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;


create or replace function marketplace_private.identity_review_stage_sync() returns trigger language plpgsql security definer set search_path='' as $$
declare earned text;reviewed boolean;
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;new.verification_mode:='standard';new.temporary_level:=null;new.temporary_approved_by:=null;new.temporary_approved_at:=null;new.temporary_expires_at:=null;new.temporary_reason:=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;
 if old.verification_mode='temporary' and new.verification_mode='standard' and new.status not in('rejected','superseded') and new.final_decided_at is not distinct from old.final_decided_at then
  earned:=marketplace_private.identity_standard_level(old.id);
  reviewed:=new.review_checks->>'face_visible'='true' and new.review_checks->>'card_readable'='true' and new.review_checks->>'challenge_match'='true';
  new.verification_level:=earned;new.status:=case when earned<>'basic' then 'verified' when coalesce(reviewed,false) then 'documents_approved' else 'pending' end;
  new.review_stage:=case when earned<>'basic' then 'final' when coalesce(reviewed,false) then 'inquiries' else 'documents' end;
  new.verified_at:=case when earned='basic' then null else new.verified_at end;
 end if;
 return new;
end$$;


drop trigger if exists identity_review_stage_sync on marketplace_private.identity_requests;
create trigger identity_review_stage_sync before update of status,verification_mode on marketplace_private.identity_requests for each row execute function marketplace_private.identity_review_stage_sync();

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.verification_mode='temporary' then
  if old.verification_mode='temporary' and old.temporary_approved_at is not distinct from new.temporary_approved_at and old.temporary_expires_at is not distinct from new.temporary_expires_at and old.temporary_level is not distinct from new.temporary_level then return new;end if;
  heading:='تأیید موقت حساب با مسئولیت مدیر';level:='warning';
  message:='سطح موقت حساب شما '||case new.temporary_level when 'full' then 'کامل' else 'اولیه' end||' است و تا '||to_char(new.temporary_expires_at at time zone 'Asia/Tehran','YYYY-MM-DD HH24:MI')||' به وقت تهران اعتبار دارد. این تأیید به معنی تکمیل یا موفق بودن استعلام‌های هویت، موبایل یا بانک نیست.'||E'\nدلیل مدیر: '||coalesce(new.temporary_reason,'');
 elsif old.verification_mode='temporary' and new.verification_mode='standard' then
  heading:=case when new.final_decided_at is distinct from old.final_decided_at and new.final_approved_level is not null then 'تأیید موقت به تأیید استاندارد تبدیل شد' when old.temporary_expires_at<=now() then 'مهلت تأیید موقت حساب پایان یافت' else 'تأیید موقت حساب پایان یافت' end;level:='info';
  message:='تأیید موقت دیگر فعال نیست. سطح جاری حساب بر اساس تأیید استاندارد معتبر و وضعیت فعلی مدارک تعیین می‌شود. وضعیت استعلام‌ها در پرونده قابل مشاهده است.';
  if new.review_note is distinct from old.review_note and nullif(trim(new.review_note),'') is not null then message:=message||E'\nپیام مدیر: '||new.review_note;end if;
 elsif 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$$;

-- All changes, including automatic expiry, have immutable history distinct from inquiry evidence.
create or replace function marketplace_private.identity_temporary_audit() returns trigger language plpgsql security definer set search_path='' as $$
declare action text;reason text;actor uuid;before_value jsonb;after_value jsonb;begin
 if new.verification_mode='temporary' and (old.verification_mode<>'temporary' or old.temporary_approved_at is distinct from new.temporary_approved_at or old.temporary_expires_at is distinct from new.temporary_expires_at or old.temporary_level is distinct from new.temporary_level) then
  action:='granted';reason:=new.temporary_reason;actor:=new.temporary_approved_by;
 elsif old.verification_mode='temporary' and new.verification_mode='standard' then
  action:=case when new.final_decided_at is distinct from old.final_decided_at and new.final_approved_level is not null then 'standardized' when old.temporary_expires_at<=now() then 'expired' when not marketplace_private.identity_temporary_ready(new.id) and new.status<>'rejected' then 'invalidated' else 'revoked' end;
  actor:=case when action in('expired','invalidated') then null else auth.uid() end;
  reason:=case when action='expired' then 'مهلت تأیید موقت پایان یافت.' when action='invalidated' then 'اعتبار مدارک یا اطلاعات لازم برای تأیید موقت تغییر کرد.' when new.review_note is distinct from old.review_note then coalesce(new.review_note,'تأیید موقت پایان یافت.') else 'تأیید موقت پایان یافت.' end;
 else return null;end if;
 before_value:=jsonb_build_object('verification_mode',old.verification_mode,'verification_level',old.verification_level,'temporary_level',old.temporary_level,'temporary_expires_at',old.temporary_expires_at,'final_approved_level',old.final_approved_level);
 after_value:=jsonb_build_object('verification_mode',new.verification_mode,'verification_level',new.verification_level,'temporary_level',new.temporary_level,'temporary_expires_at',new.temporary_expires_at,'final_approved_level',new.final_approved_level);
 insert into marketplace_private.identity_temporary_history(user_id,request_id,request_version,event,before_data,after_data,reason,actor_id,responsibility_accepted) values(new.user_id,new.id,new.version,action,before_value,after_value,reason,actor,action='granted');
 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(new.user_id,'identity','temporary_'||action,'identity_temporary_reviews',new.id::text,before_value,after_value,array['verification_mode','temporary_level','temporary_expires_at'],actor,case when actor is null then 'system' else 'admin' end,'identity-temporary:'||new.id||':'||new.version);
 insert into marketplace_private.audit_log(actor,entity,record_id,operation) values(actor,'identity_temporary',new.id::text,action);
 return null;
end$$;
drop trigger if exists identity_temporary_audit on marketplace_private.identity_requests;
create trigger identity_temporary_audit after update on marketplace_private.identity_requests for each row execute function marketplace_private.identity_temporary_audit();
create or replace function marketplace_private.customer_activity_reviewable(p_entity text,p_action text,p_actor_kind text) returns boolean language sql immutable set search_path='' as $$
 select coalesce(p_actor_kind<>'admin',false) and not(p_entity='notifications' and p_action='inserted') and p_entity<>'identity_temporary_reviews';
$$;
-- Preserve the existing safe-field allowlists, adding only the new explicit status metadata.
do $$declare definition text;begin
 definition:=pg_get_functiondef('marketplace_private.customer_activity_safe(text,jsonb)'::regprocedure);
 definition:=replace(definition,$x$'identity_verified_at','avatar_path']$x$,$x$'identity_verified_at','avatar_path','verification_mode','temporary_level','temporary_expires_at']$x$);
 definition:=replace(definition,$x$'final_decided_at','final_review_note']$x$,$x$'final_decided_at','final_review_note','verification_mode','temporary_level','temporary_approved_by','temporary_approved_at','temporary_expires_at','temporary_reason']$x$);
 execute definition;
end$$;
create or replace function marketplace_private.identity_account_mode_sync() returns trigger language plpgsql security definer set search_path='' as $$
begin
 if new.verification_status<>'verified' or new.account_level='basic' then new.verification_mode:='standard';new.temporary_level:=null;new.temporary_expires_at:=null;end if;
 return new;
end$$;
drop trigger if exists identity_account_mode_sync on marketplace_private.accounts;
create trigger identity_account_mode_sync before insert or update on marketplace_private.accounts for each row execute function marketplace_private.identity_account_mode_sync();

-- Expiry is enforced on access/read/use, not by a browser timer or a scheduled worker.
-- The public migration-005 customer wrappers remain intact.
create or replace function marketplace_private.customer_access_assert(p_user uuid,p_action text default 'login') returns void language plpgsql security definer set search_path='' as $$
declare a public.marketplace_customer_access;begin
 if p_user is null then raise exception 'not_authenticated' using errcode='42501';end if;
 select * into a from public.marketplace_customer_access where user_id=p_user for share;
 if a.user_id is null then raise exception 'not_authenticated' using errcode='42501';end if;
 if a.status='closed' then raise exception 'account_closed' using errcode='42501';end if;
 if a.status='suspended' then raise exception 'account_suspended' using errcode='42501';end if;
 perform marketplace_private.identity_temporary_sync(p_user);
 if p_action='trade' and not a.trading_enabled then raise exception 'trading_disabled' using errcode='42501';end if;
 if p_action='withdraw' and not a.withdrawals_enabled then raise exception 'withdrawals_disabled' using errcode='42501';end if;
end$$;
create or replace function public.marketplace_my_access() returns jsonb language plpgsql security definer set search_path='' as $$
declare result jsonb;begin
 if auth.uid() is null then raise exception 'not_authenticated' using errcode='42501';end if;
 perform marketplace_private.identity_temporary_sync(auth.uid());
 result:=marketplace_private.customer_access_json(auth.uid());
 if result is null then raise exception 'not_authenticated' using errcode='42501';end if;
 return result;
end$$;
-- Keep reviewer identity private in the customer's view, including the temporary approver.
create or replace function marketplace_private.access_base_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'-'temporary_approved_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$$;

-- Administrator views synchronize the selected account (or the queue) before returning persisted levels.
do $$declare f record;private_name text;definition text;arg_values text;subject text;body text;begin
 for f in select p.oid,p.proname,p.pronargs,pg_get_function_arguments(p.oid) args,pg_get_function_identity_arguments(p.oid) identity_args,pg_get_function_result(p.oid) result_type
 from pg_proc p join pg_namespace n on n.oid=p.pronamespace where n.nspname='public' and p.proname=any(array['marketplace_identity_admin','marketplace_admin_customer_profile','marketplace_admin_customers','marketplace_admin_customer_inquiries']) loop
  private_name:='temporary_base_'||f.proname;
  if not exists(select 1 from pg_proc p join pg_namespace n on n.oid=p.pronamespace where n.nspname='marketplace_private' and p.proname=private_name) then
   definition:=pg_get_functiondef(f.oid);definition:=replace(definition,format('FUNCTION public.%s(',f.proname),format('FUNCTION marketplace_private.%s(',private_name));execute definition;
  end if;
  execute format('revoke all on function marketplace_private.%I(%s) from public,anon,authenticated,service_role',private_name,f.identity_args);
  select coalesce(string_agg(format('$%s',i),','),'') into arg_values from generate_series(1,f.pronargs) i;
  subject:=case when f.proname='marketplace_admin_customers' then 'null' when f.proname='marketplace_identity_admin' then '(select user_id from marketplace_private.identity_requests where id=$1)' else '$1' end;
  body:=format('begin if public.marketplace_role() is distinct from ''owner'' then raise exception ''not_authorized'' using errcode=''42501'';end if;perform marketplace_private.identity_temporary_sync(%s);return marketplace_private.%I(%s);end',subject,private_name,arg_values);
  execute format('create or replace function public.%I(%s) returns %s language plpgsql security definer set search_path='''' as %L',f.proname,f.args,f.result_type,body);
 end loop;
end$$;
-- Explicit list projections must carry the same scoped metadata as full detail responses.
do $$declare definition text;begin
 definition:=pg_get_functiondef('marketplace_private.temporary_base_marketplace_admin_customers(text)'::regprocedure);
 if position($x$coalesce(a.verification_mode,'standard') verification_mode$x$ in definition)=0 then
  definition:=replace(definition,$x$coalesce(a.verification_status,'not_started') verification_status,$x$,$x$coalesce(a.verification_status,'not_started') verification_status,coalesce(a.verification_mode,'standard') verification_mode,a.temporary_level,a.temporary_expires_at,$x$);execute definition;
 end if;
 definition:=pg_get_functiondef('marketplace_private.temporary_base_marketplace_identity_admin(uuid)'::regprocedure);
 definition:=replace(definition,$x$verification_type,review_stage,verification_level,first_name$x$,$x$verification_type,review_stage,verification_level,verification_mode,temporary_level,temporary_expires_at,first_name$x$);execute definition;
 definition:=pg_get_functiondef('marketplace_private.temporary_base_marketplace_admin_customer_profile(uuid)'::regprocedure);
 if position($x$'verification_mode',r.verification_mode$x$ in definition)=0 then
  definition:=replace(definition,$x$'verification_level',r.verification_level,$x$,$x$'verification_level',r.verification_level,'verification_mode',r.verification_mode,'temporary_level',r.temporary_level,'temporary_expires_at',r.temporary_expires_at,$x$);execute definition;
 end if;
end$$;
-- A service settlement may consult KYC for a pre-existing order without writing that order again.
do $$declare definition text;begin
 if to_regprocedure('public.marketplace_referral_settle(uuid)') is not null then
  definition:=pg_get_functiondef('public.marketplace_referral_settle(uuid)'::regprocedure);
  if position('identity_temporary_sync' in definition)=0 then
   definition:=replace(definition,E'begin\n',E'begin\n perform marketplace_private.identity_temporary_sync((select user_id from marketplace_private.orders where id=p_order));\n');execute definition;
  end if;
 end if;
end$$;

drop trigger if exists customer_record_retained on marketplace_private.identity_temporary_history;
create trigger customer_record_retained before delete on marketplace_private.identity_temporary_history for each row execute function marketplace_private.customer_record_retained();
drop trigger if exists customer_history_immutable on marketplace_private.identity_temporary_history;
create trigger customer_history_immutable before update on marketplace_private.identity_temporary_history for each row execute function marketplace_private.customer_record_retained();
drop trigger if exists customer_record_truncate_retained on marketplace_private.identity_temporary_history;
create trigger customer_record_truncate_retained before truncate on marketplace_private.identity_temporary_history for each statement execute function marketplace_private.customer_record_retained();
-- Private functions are never application-side approval or expiry bypasses.
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_temporary_%' or p.proname in('identity_standard_level','identity_finalize','identity_account_mode_sync','customer_access_assert','customer_activity_reviewable','customer_activity_safe','access_base_marketplace_identity_state','identity_review_stage_sync','identity_notification_trigger')) 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_temporary_review(uuid,integer,text,integer,text,boolean),public.marketplace_identity_temporary_revoke(uuid,integer,text) from public,anon,authenticated,service_role;
grant execute on function public.marketplace_identity_temporary_review(uuid,integer,text,integer,text,boolean),public.marketplace_identity_temporary_revoke(uuid,integer,text) to authenticated;
notify pgrst,'reload schema';
commit;