-- Deposits and wallet linking need an active customer account, not completed KYC.
-- Withdrawals/trades still require effective KYC. Existing bank ownership evidence,
-- provider proof, manual review, reservations and account restrictions are retained.
begin;

create or replace function marketplace_private.wallet_kyc_verified(p_user uuid)
returns boolean language sql stable security definer set search_path='' as $$
 select coalesce((select a.verification_status='verified' and a.account_level in('primary','full')
  and case coalesce(to_jsonb(a)->>'verification_mode','standard')
   when 'standard' then true
   when 'temporary' then to_jsonb(a)->>'temporary_level'=a.account_level
    and nullif(to_jsonb(a)->>'temporary_expires_at','')::timestamptz>statement_timestamp()
    and isfinite(nullif(to_jsonb(a)->>'temporary_expires_at','')::timestamptz)
   else false end
  from marketplace_private.accounts a where a.user_id=p_user),false);
$$;

-- Source-bank ownership is independent of the account's overall KYC decision.
-- Never accept an old "verified" label without matching ownership evidence.
create or replace function marketplace_private.wallet_bank_deposit_eligible(p_user uuid,p_bank uuid,p_card boolean default false)
returns boolean language sql stable security definer set search_path='' as $$
 select coalesce((select b.status='verified' and b.ownership_checked_at is not null
  and marketplace_private.national_id_valid(coalesce(nullif(to_jsonb(a)->>'national_id',''),i.national_id))
  and b.ownership_national_id=coalesce(nullif(to_jsonb(a)->>'national_id',''),i.national_id)
  and (not p_card or b.card_link_status='verified')
  from marketplace_private.bank_accounts b left join marketplace_private.accounts a on a.user_id=b.user_id
  left join lateral(select r.national_id from marketplace_private.identity_requests r where r.user_id=b.user_id order by r.created_at desc,r.id desc limit 1)i on true
  where b.id=p_bank and b.user_id=p_user),false);
$$;
revoke all on function marketplace_private.wallet_kyc_verified(uuid),marketplace_private.wallet_bank_deposit_eligible(uuid,uuid,boolean) from public,anon,authenticated,service_role;

-- Preserve the deployed access helper (including temporary approval expiry sync)
-- and function privileges while adding the outflow/trading rule.
do $$declare definition text;old_text text;begin
 select pg_get_functiondef('marketplace_private.customer_access_assert(uuid,text)'::regprocedure) into definition;
 if position('wallet_kyc_verified' in definition)=0 then
  old_text:=$old$if p_action='withdraw' and not a.withdrawals_enabled then raise exception 'withdrawals_disabled' using errcode='42501';end if;$old$;
  if position(old_text in definition)=0 then raise exception 'wallet_access_policy_definition_changed';end if;
  execute replace(definition,old_text,old_text||E'\n if p_action in(''trade'',''withdraw'') and not marketplace_private.wallet_kyc_verified(p_user) then raise exception ''wallet_identity_required'' using errcode=''42501'';end if;');
 end if;

 select pg_get_functiondef('public.marketplace_toman_deposit_state(text,integer)'::regprocedure) into definition;
 if position('wallet_bank_deposit_eligible' in definition)=0 then
  old_text:=$old$coalesce(b.status='verified' and b.ownership_checked_at is not null and b.ownership_national_id=i.national_id and marketplace_private.national_id_valid(i.national_id) and i.status='verified' and a.verification_status='verified',false)$old$;
  if position(old_text in definition)=0 then raise exception 'wallet_deposit_state_definition_changed';end if;
  execute replace(definition,old_text,'marketplace_private.wallet_bank_deposit_eligible(uid,b.id)');
 end if;

 select pg_get_functiondef('public.marketplace_toman_deposit_submit(uuid,integer,uuid,integer,uuid,text)'::regprocedure) into definition;
 if position('wallet_bank_deposit_eligible' in definition)=0 then
  old_text:=$old$select * into identity_row from marketplace_private.identity_requests where user_id=uid order by created_at desc,id desc limit 1 for share;
 if identity_row.status is distinct from 'verified' or not marketplace_private.national_id_valid(identity_row.national_id) or not exists(select 1 from marketplace_private.accounts where user_id=uid and verification_status='verified') then raise exception 'deposit_identity_required';end if;$old$;
  if position(old_text in definition)=0 then raise exception 'wallet_deposit_submit_definition_changed';end if;
  definition:=replace(definition,old_text,'perform 1 from marketplace_private.accounts where user_id=uid for share; -- Source identity cannot change during submission.');
  definition:=replace(definition,'identity_row marketplace_private.identity_requests;','');
  old_text:=$old$b.id is null or b.status is distinct from 'verified' or b.ownership_checked_at is null or b.ownership_national_id is distinct from identity_row.national_id or (r.method='card_transfer' and b.card_link_status is distinct from 'verified')$old$;
  if position(old_text in definition)=0 then raise exception 'wallet_deposit_bank_definition_changed';end if;
  execute replace(definition,old_text,$new$not marketplace_private.wallet_bank_deposit_eligible(uid,p_bank,r.method='card_transfer')$new$);
 end if;

 -- Funding migration wraps the original Bale creator; change its private body,
 -- retaining the public wrapper's test cap and pending-review response.
 select pg_get_functiondef('marketplace_private.funding_bale_create(uuid,uuid,bigint)'::regprocedure) into definition;
 old_text:=$old$if not exists(select 1 from marketplace_private.accounts where user_id=p_user and verification_status='verified') then raise exception 'deposit_identity_required';end if;$old$;
 if position(old_text in definition)>0 then
  execute replace(definition,old_text,'-- wallet_deposit_without_kyc: active-account and provider checks remain enforced.');
 elsif position('wallet_deposit_without_kyc' in definition)=0 then raise exception 'wallet_bale_create_definition_changed';end if;
end$$;

notify pgrst,'reload schema';
commit;