-- Demo money is isolated from authoritative positions. Real USDT requires server-verified chain evidence.
begin;
create or replace function marketplace_private.usdt_address_valid(p_network text,p_address text) returns boolean language plpgsql immutable set search_path='' as $$
declare alphabet constant text:='123456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz';acc numeric:=0;hex_value text:='';raw bytea;n integer;begin
 if p_address is null or p_network is null then return false;end if;
 if p_network='ethereum' then return p_address~'^0x[0-9a-f]{40}$' and p_address not in('0x0000000000000000000000000000000000000000','0xdac17f958d2ee523a2206206994597c13d831ec7');end if;
 if p_network<>'tron' or p_address!~'^T[1-9A-HJ-NP-Za-km-z]{33}$' or p_address='TR7NHqjeKQxGTCi8q8ZY4pL8otSzgjLj6t' then return false;end if;
 for n in 1..length(p_address) loop acc:=acc*58+strpos(alphabet,substr(p_address,n,1))-1;end loop;
 for n in 1..25 loop hex_value:=lpad(to_hex(mod(acc,256)::integer),2,'0')||hex_value;acc:=div(acc,256);end loop;
 if acc<>0 then return false;end if;
 raw:=decode(hex_value,'hex');
 return get_byte(raw,0)=65 and substring(raw from 2 for 20)<>decode(repeat('00',20),'hex')
 and substring(raw from 22 for 4)=substring(sha256(sha256(substring(raw from 1 for 21))) from 1 for 4);
end$$;
revoke all on function marketplace_private.usdt_address_valid(text,text) from public,anon,authenticated,service_role;
create table if not exists marketplace_private.demo_positions(
 user_id uuid not null references auth.users(id) on delete restrict,asset_symbol text not null check(asset_symbol in('IRR','USDT')),
 quantity numeric(30,6) not null default 0 check(quantity>=0),updated_at timestamptz not null default clock_timestamp(),primary key(user_id,asset_symbol),
 check((asset_symbol='IRR' and quantity=trunc(quantity) and quantity<=1000000000000) or (asset_symbol='USDT' and quantity<=100000000))
);
create table if not exists marketplace_private.demo_credits(
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id) on delete restrict,request_id uuid not null,
 currency text not null check(currency in('IRR','USDT')),amount numeric(30,6) not null check(amount>0),created_at timestamptz not null default clock_timestamp(),unique(user_id,request_id),
 check((currency='IRR' and amount=trunc(amount) and amount<=1000000000) or (currency='USDT' and amount<=100000))
);
create table if not exists marketplace_private.usdt_settings(
 network text primary key check(network in('ethereum','tron')),version bigint not null default 1 check(version>0),enabled boolean not null default false,
 address text not null default '',api_secret_id uuid,updated_at timestamptz not null default clock_timestamp(),updated_by uuid references auth.users(id),
 check(address='' or marketplace_private.usdt_address_valid(network,address)),
 check(not enabled or (address<>'' and api_secret_id is not null))
);
insert into marketplace_private.usdt_settings(network) values('ethereum'),('tron') on conflict(network) do nothing;
create table if not exists marketplace_private.usdt_intents(
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id) on delete restrict,request_id uuid not null,
 network text not null references marketplace_private.usdt_settings(network),address text not null,settings_version bigint not null,api_secret_id uuid not null,
 requested_micro bigint not null check(requested_micro between 1 and 100000000000),amount_micro bigint not null check(amount_micro>requested_micro and amount_micro<=requested_micro+999),
 status text not null default 'pending' check(status in('pending','credited')),created_at timestamptz not null default clock_timestamp(),expires_at timestamptz not null,
 candidate_tx_hash text,hash_version integer not null default 0 check(hash_version>=0),submitted_at timestamptz,last_checked_at timestamptz,last_verification_at timestamptz,
 tx_hash text,log_index integer,credited_at timestamptz,transaction_id uuid unique references marketplace_private.transactions(id) on delete restrict,
 unique(user_id,request_id),unique(network,address,amount_micro),check(expires_at=created_at+interval '1 hour'),
 check((status='credited')=(tx_hash is not null and log_index is not null and credited_at is not null and transaction_id is not null))
);
create index if not exists usdt_intents_owner_recent on marketplace_private.usdt_intents(user_id,created_at desc);
create table if not exists marketplace_private.usdt_deposit_ledger(
 id uuid primary key default gen_random_uuid(),intent_id uuid not null unique references marketplace_private.usdt_intents(id) on delete restrict,
 user_id uuid not null references auth.users(id) on delete restrict,position_id uuid not null references marketplace_private.asset_positions(id) on delete restrict,
 transaction_id uuid not null unique references marketplace_private.transactions(id) on delete restrict,network text not null check(network in('ethereum','tron')),
 address text not null,tx_hash text not null,log_index integer not null check(log_index>=0),block_number bigint not null check(block_number>=0),block_hash text not null,
 block_time timestamptz not null,amount_micro bigint not null check(amount_micro>0),created_at timestamptz not null default clock_timestamp(),unique(network,tx_hash,log_index)
);
do $$declare t text;begin
 foreach t in array array['demo_positions','demo_credits','usdt_settings','usdt_intents','usdt_deposit_ledger'] loop
  execute format('alter table marketplace_private.%I enable row level security',t);
  execute format('revoke all on marketplace_private.%I from public,anon,authenticated,service_role',t);
  execute format('drop trigger if exists funding_history_retained on marketplace_private.%I',t);
  execute format('create trigger funding_history_retained before delete on marketplace_private.%I for each row execute function marketplace_private.customer_record_retained()',t);
  execute format('drop trigger if exists funding_truncate_retained on marketplace_private.%I',t);
  execute format('create trigger funding_truncate_retained before truncate on marketplace_private.%I for each statement execute function marketplace_private.customer_record_retained()',t);
 end loop;
 foreach t in array array['demo_credits','usdt_deposit_ledger'] loop
  execute format('drop trigger if exists funding_history_immutable on marketplace_private.%I',t);
  execute format('create trigger funding_history_immutable before update on marketplace_private.%I for each row execute function marketplace_private.customer_record_retained()',t);
 end loop;
end$$;

create or replace function public.marketplace_demo_wallet() returns jsonb language plpgsql volatile security definer set search_path='' as $$
declare uid uuid:=auth.uid();begin
 perform marketplace_private.customer_access_assert(uid);
 return jsonb_build_object('enabled',true,
 'balance_rial',coalesce((select trim_scale(quantity)::text from marketplace_private.demo_positions where user_id=uid and asset_symbol='IRR'),'0'),
 'balance_usdt',coalesce((select trim_scale(quantity)::text from marketplace_private.demo_positions where user_id=uid and asset_symbol='USDT'),'0'),
 'history',coalesce((select jsonb_agg(jsonb_build_object('id',id,'currency',currency,'amount',trim_scale(amount)::text,'created_at',created_at) order by created_at desc,id desc) from (select * from marketplace_private.demo_credits where user_id=uid order by created_at desc,id desc limit 25) d),'[]'),
 'limits',jsonb_build_object('max_amount_toman','100000000','max_amount_usdt','100000'));
end$$;
create or replace function public.marketplace_demo_credit(p_request uuid,p_currency text,p_amount text) returns jsonb language plpgsql security definer set search_path='' as $$
declare uid uuid:=auth.uid();amount_value numeric;d marketplace_private.demo_credits;balance_value numeric;begin
 perform marketplace_private.customer_access_assert(uid);
 if p_request is null or p_currency is null or p_currency not in('IRR','USDT') or p_amount is null or length(p_amount)>32
 or p_amount!~'^(0|[1-9][0-9]*)(\.[0-9]{1,6})?$' then raise exception 'invalid_demo_amount';end if;
 amount_value:=p_amount::numeric;
 if amount_value<=0 or (p_currency='IRR' and (amount_value<>trunc(amount_value) or amount_value>1000000000)) or (p_currency='USDT' and amount_value>100000) then raise exception 'invalid_demo_amount';end if;
 perform pg_advisory_xact_lock(hashtextextended('demo-wallet:'||uid,0));
 select * into d from marketplace_private.demo_credits where user_id=uid and request_id=p_request;
 if d.id is not null then
  if d.currency<>p_currency or d.amount<>amount_value then raise exception 'demo_request_conflict';end if;
  return public.marketplace_demo_wallet()||jsonb_build_object('credited',true,'duplicate',true);
 end if;
 if (select count(*) from marketplace_private.demo_credits where user_id=uid and created_at>clock_timestamp()-interval '1 hour')>=20 then raise exception 'demo_rate_limit';end if;
 insert into marketplace_private.demo_positions(user_id,asset_symbol) values(uid,p_currency) on conflict(user_id,asset_symbol) do nothing;
 select quantity into balance_value from marketplace_private.demo_positions where user_id=uid and asset_symbol=p_currency for update;
 if balance_value+amount_value>(case when p_currency='IRR' then 1000000000000 else 100000000 end) then raise exception 'demo_balance_limit';end if;
 insert into marketplace_private.demo_credits(user_id,request_id,currency,amount) values(uid,p_request,p_currency,amount_value) returning * into d;
 update marketplace_private.demo_positions set quantity=quantity+amount_value,updated_at=clock_timestamp() where user_id=uid and asset_symbol=p_currency;
 insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,after_data,changed_fields,actor_id,actor_kind,source,source_key)
 values(uid,'wallet','demo_credit','demo_credits',d.id::text,jsonb_build_object('mode','demo','currency',p_currency,'amount',trim_scale(amount_value)::text,'withdrawable',false),array['amount'],uid,'customer','demo-wallet','demo-credit:'||d.id);
 insert into marketplace_private.notifications(user_id,event_key,title,body,kind,severity,action_url)
 values(uid,'demo-credit:'||d.id,'{"fa":"موجودی آزمایشی اضافه شد","en":"Demo balance added"}',
 jsonb_build_object('fa','این موجودی فقط برای آزمایش است و ارزش نقدی یا امکان برداشت ندارد.','en','This balance is for testing only and has no cash value or withdrawal option.'),'wallet','info','/wallet/deposit');
 insert into marketplace_private.admin_notifications(event_key,event_type,user_id,title,body,action_url)
 values('demo-credit:'||d.id,'demo_credit',uid,'{"fa":"شارژ موجودی آزمایشی کاربر"}',jsonb_build_object('fa','شارژ آزمایشی '||trim_scale(amount_value)::text||' '||p_currency||' ثبت شد؛ این مبلغ واقعی یا قابل برداشت نیست.'),'/customers/'||uid||'/activity?category=wallet');
 return public.marketplace_demo_wallet()||jsonb_build_object('credited',true,'duplicate',false);
end$$;

create or replace function public.marketplace_usdt_settings() returns jsonb language plpgsql stable 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;
 return (select jsonb_agg(jsonb_build_object('network',network,'version',version,'enabled',enabled,'address',address,'has_api_key',api_secret_id is not null,'updated_at',updated_at) order by network) from marketplace_private.usdt_settings);
end$$;
create or replace function public.marketplace_usdt_settings_update(p_network text,p_version bigint,p_enabled boolean,p_address text,p_api_key text default null) returns jsonb language plpgsql security definer set search_path='' as $$
declare s marketplace_private.usdt_settings;address_value text;secret_id uuid;begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if p_network is null or p_network not in('ethereum','tron') or p_enabled is null or p_address is null then raise exception 'invalid_usdt_settings';end if;
 address_value:=case when p_network='ethereum' then lower(p_address) else p_address end;
 if address_value<>'' and not marketplace_private.usdt_address_valid(p_network,address_value) then raise exception 'invalid_usdt_address';end if;
 -- Keys only: the verification adapter uses fixed Alchemy Ethereum-mainnet / TronGrid hosts.
 if p_api_key is not null and (length(p_api_key) not between 8 and 256 or p_api_key!~'^[A-Za-z0-9_-]+$') then raise exception 'invalid_api_key';end if;
 select * into strict s from marketplace_private.usdt_settings where network=p_network for update;
 if p_version is distinct from s.version then raise exception 'settings_conflict';end if;
 if s.enabled=p_enabled and s.address=address_value and p_api_key is null then return public.marketplace_usdt_settings();end if;
 if exists(select 1 from marketplace_private.usdt_intents where network=p_network and status='pending' and expires_at>clock_timestamp()) then raise exception 'usdt_intents_in_progress';end if;
 secret_id:=s.api_secret_id;
 if p_api_key is not null then select vault.create_secret(p_api_key,'planck_usdt_'||p_network||'_'||gen_random_uuid(),'USDT verification provider credential') into secret_id;end if;
 if p_enabled and (address_value='' or secret_id is null) then raise exception 'credentials_missing';end if;
 update marketplace_private.usdt_settings set version=version+1,enabled=p_enabled,address=address_value,api_secret_id=secret_id,updated_at=clock_timestamp(),updated_by=auth.uid() where network=p_network;
 insert into marketplace_private.audit_log(actor,entity,record_id,operation) values(auth.uid(),'usdt_settings',p_network,'configure');
 return public.marketplace_usdt_settings();
end$$;

create or replace function marketplace_private.usdt_intent_json(i marketplace_private.usdt_intents) returns jsonb language sql stable set search_path='' as $$
 select jsonb_build_object('id',i.id,'network',i.network,'address',i.address,'amount_micro',i.amount_micro::text,'requested_micro',i.requested_micro::text,
 'status',case when i.status='pending' and i.expires_at<=clock_timestamp() then 'expired' else i.status end,'created_at',i.created_at,'expires_at',i.expires_at,'candidate_tx_hash',i.candidate_tx_hash,'tx_hash',i.tx_hash,'log_index',i.log_index);
$$;
create or replace function public.marketplace_usdt_state() returns jsonb language plpgsql volatile security definer set search_path='' as $$
declare uid uuid:=auth.uid();begin
 perform marketplace_private.customer_access_assert(uid);
 return jsonb_build_object('networks',(select jsonb_agg(jsonb_build_object('network',network,'address',address,'enabled',enabled) order by network) from marketplace_private.usdt_settings),
 'intents',coalesce((select jsonb_agg(marketplace_private.usdt_intent_json(i) order by created_at desc,id desc) from (select * from marketplace_private.usdt_intents where user_id=uid order by created_at desc,id desc limit 25)i),'[]'));
end$$;
create or replace function public.marketplace_usdt_intent_create(p_request uuid,p_network text,p_amount_micro bigint) returns jsonb language plpgsql security definer set search_path='' as $$
declare uid uuid:=auth.uid();s marketplace_private.usdt_settings;i marketplace_private.usdt_intents;candidate bigint;created timestamptz:=clock_timestamp();begin
 perform marketplace_private.customer_access_assert(uid);
 if p_request is null or p_network is null or p_network not in('ethereum','tron') or p_amount_micro is null or p_amount_micro not between 1 and 100000000000 then raise exception 'invalid_usdt_amount';end if;
 perform pg_advisory_xact_lock(hashtextextended('usdt-user:'||uid,0));
 select * into i from marketplace_private.usdt_intents where user_id=uid and request_id=p_request;
 if i.id is not null then
  if i.network<>p_network or i.requested_micro<>p_amount_micro then raise exception 'usdt_request_conflict';end if;
  return marketplace_private.usdt_intent_json(i);
 end if;
 select * into strict s from marketplace_private.usdt_settings where network=p_network for share;
 if not s.enabled then raise exception 'usdt_network_unavailable';end if;
 if exists(select 1 from marketplace_private.usdt_intents where user_id=uid and network=p_network and status='pending' and expires_at>created) then raise exception 'usdt_intent_pending';end if;
 if (select count(*) from marketplace_private.usdt_intents where user_id=uid and created_at>created-interval '1 hour')>=10 then raise exception 'usdt_rate_limit';end if;
 perform pg_advisory_xact_lock(hashtextextended('usdt-destination:'||p_network||':'||s.address,0));
 -- Never reuse an allocated exact amount, even after expiry, so an old transfer cannot fund a new user.
 select p_amount_micro+n into candidate from generate_series(1,999)n
 where not exists(select 1 from marketplace_private.usdt_intents where network=p_network and address=s.address and amount_micro=p_amount_micro+n) order by random() limit 1;
 if candidate is null then raise exception 'usdt_amount_unavailable';end if;
 insert into marketplace_private.usdt_intents(user_id,request_id,network,address,settings_version,api_secret_id,requested_micro,amount_micro,created_at,expires_at)
 values(uid,p_request,p_network,s.address,s.version,s.api_secret_id,p_amount_micro,candidate,created,created+interval '1 hour') returning * into i;
 insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,after_data,changed_fields,actor_id,actor_kind,source,source_key)
 values(uid,'wallet','usdt_deposit_requested','usdt_intents',i.id::text,marketplace_private.usdt_intent_json(i),array['status'],uid,'customer','usdt-deposit','usdt-intent:'||i.id);
 return marketplace_private.usdt_intent_json(i);
end$$;
create or replace function public.marketplace_usdt_submit_hash(p_intent uuid,p_tx_hash text) returns jsonb language plpgsql security definer set search_path='' as $$
declare uid uuid:=auth.uid();i marketplace_private.usdt_intents;hash_value text:=lower(p_tx_hash);before_value text;begin
 perform marketplace_private.customer_access_assert(uid);
 perform pg_advisory_xact_lock(hashtextextended('usdt-hash:'||uid,0));
 select * into i from marketplace_private.usdt_intents where id=p_intent and user_id=uid for update;
 if i.id is null then raise exception 'usdt_intent_not_found';end if;
 if hash_value is null or not(case when i.network='ethereum' then hash_value~'^0x[0-9a-f]{64}$' else hash_value~'^[0-9a-f]{64}$' end) then raise exception 'invalid_usdt_hash';end if;
 if i.status='credited' then
  if i.tx_hash<>hash_value then raise exception 'usdt_receipt_conflict';end if;
  return marketplace_private.usdt_intent_json(i);
 end if;
 if i.candidate_tx_hash=hash_value then return marketplace_private.usdt_intent_json(i);end if;
 if i.expires_at<clock_timestamp()-interval '7 days' then raise exception 'usdt_intent_expired';end if;
 if (select count(*) from marketplace_private.customer_activity where user_id=uid and source='usdt-hash' and created_at>clock_timestamp()-interval '1 hour')>=30 then raise exception 'usdt_rate_limit';end if;
 before_value:=i.candidate_tx_hash;
 update marketplace_private.usdt_intents set candidate_tx_hash=hash_value,hash_version=hash_version+1,submitted_at=clock_timestamp() where id=i.id returning * into i;
 insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,before_data,after_data,changed_fields,actor_id,actor_kind,source,source_key)
 values(uid,'wallet','usdt_hash_submitted','usdt_intents',i.id::text,jsonb_build_object('candidate_tx_hash',before_value),jsonb_build_object('candidate_tx_hash',hash_value,'status','unverified'),array['candidate_tx_hash'],uid,'customer','usdt-hash','usdt-hash:'||i.id||':'||i.hash_version);
 return marketplace_private.usdt_intent_json(i);
end$$;
create or replace function public.marketplace_usdt_verify_context(p_user uuid,p_intent uuid) returns jsonb language plpgsql stable security definer set search_path='' as $$
declare i marketplace_private.usdt_intents;secret text;begin
 select * into i from marketplace_private.usdt_intents where id=p_intent and user_id=p_user;
 if i.id is null then raise exception 'usdt_intent_not_found';end if;
 select decrypted_secret into secret from vault.decrypted_secrets where id=i.api_secret_id;
 if secret is null then raise exception 'usdt_provider_unavailable';end if;
 return jsonb_build_object('intent',marketplace_private.usdt_intent_json(i),'api_key',secret);
end$$;
create or replace function public.marketplace_usdt_verify_claim(p_user uuid,p_intent uuid) returns boolean language plpgsql security definer set search_path='' as $$
declare i marketplace_private.usdt_intents;begin
 select * into i from marketplace_private.usdt_intents where id=p_intent and user_id=p_user for update;
 if i.id is null then raise exception 'usdt_intent_not_found';end if;
 if i.status='credited' or i.candidate_tx_hash is null or i.last_verification_at>clock_timestamp()-interval '10 seconds' then return false;end if;
 update marketplace_private.usdt_intents set last_verification_at=clock_timestamp() where id=i.id;
 return true;
end$$;

-- Caller must verify the exact canonical USDT transfer and chain finality directly with the fixed provider first.
create or replace function public.marketplace_usdt_credit(p_intent uuid,p_tx_hash text,p_log_index integer,p_block_number bigint,p_block_hash text,p_block_time timestamptz,p_amount_micro bigint,p_address text,p_network text) returns jsonb language plpgsql security definer set search_path='' as $$
declare i marketplace_private.usdt_intents;l marketplace_private.usdt_deposit_ledger;position marketplace_private.asset_positions;tx uuid;hash_value text:=lower(p_tx_hash);block_value text:=lower(p_block_hash);address_value text;quantity_value numeric;begin
 select * into i from marketplace_private.usdt_intents where id=p_intent for update;
 if i.id is null then raise exception 'usdt_intent_not_found';end if;
 address_value:=case when p_network='ethereum' then lower(p_address) else p_address end;
 if p_network is distinct from i.network or address_value is distinct from i.address or p_amount_micro is distinct from i.amount_micro
 or hash_value is null or block_value is null or not(case when i.network='ethereum' then hash_value~'^0x[0-9a-f]{64}$' and block_value~'^0x[0-9a-f]{64}$' else hash_value~'^[0-9a-f]{64}$' and block_value~'^[0-9a-f]{64}$' end)
 or p_log_index is null or p_log_index<0 or p_block_number is null or p_block_number<0 or p_block_time is null or not isfinite(p_block_time)
 or p_block_time<i.created_at or p_block_time>=i.expires_at or p_block_time>clock_timestamp() then raise exception 'usdt_verification_mismatch';end if;
 if i.status='credited' then
  select * into strict l from marketplace_private.usdt_deposit_ledger where intent_id=i.id;
  if l.tx_hash<>hash_value or l.log_index<>p_log_index or l.block_number<>p_block_number or l.block_hash<>block_value or l.block_time<>p_block_time then raise exception 'usdt_receipt_conflict';end if;
  return jsonb_build_object('ok',true,'duplicate',true,'status','credited','transaction_id',i.transaction_id,'intent',marketplace_private.usdt_intent_json(i));
 end if;
 perform pg_advisory_xact_lock(hashtextextended('usdt-event:'||p_network||':'||hash_value||':'||p_log_index,0));
 if exists(select 1 from marketplace_private.usdt_deposit_ledger where network=p_network and tx_hash=hash_value and log_index=p_log_index) then raise exception 'usdt_transfer_used';end if;
 -- Share the tokenization symbol lock; never combine an unrelated issued token named USDT with Tether.
 perform pg_advisory_xact_lock(hashtextextended('tokenization-symbol:'||i.user_id||':USDT',0));
 select * into position from marketplace_private.asset_positions where user_id=i.user_id and asset_symbol='USDT' for update;
 if position.id is not null and (position.project_id is not null or exists(select 1 from marketplace_private.tokenization_allocation_ledger where position_id=position.id)
 or (position.quantity<>0 and not exists(select 1 from marketplace_private.usdt_deposit_ledger where position_id=position.id))) then raise exception 'usdt_asset_conflict';end if;
 quantity_value:=i.amount_micro::numeric/1000000;
 if position.id is null then
  insert into marketplace_private.asset_positions(user_id,asset_symbol,asset_name,quantity,locked_quantity,value_usdt) values(i.user_id,'USDT','{"fa":"تتر","en":"Tether"}',0,0,0) returning * into position;
 end if;
 insert into marketplace_private.transactions(user_id,reference,kind,asset_symbol,amount,status,network,tx_hash,completed_at)
 values(i.user_id,'usdt-deposit:'||i.id,'deposit','USDT',quantity_value,'confirmed',i.network,hash_value,clock_timestamp()) returning id into tx;
 insert into marketplace_private.usdt_deposit_ledger(intent_id,user_id,position_id,transaction_id,network,address,tx_hash,log_index,block_number,block_hash,block_time,amount_micro)
 values(i.id,i.user_id,position.id,tx,i.network,i.address,hash_value,p_log_index,p_block_number,block_value,p_block_time,i.amount_micro);
 update marketplace_private.asset_positions set quantity=quantity+quantity_value,value_usdt=quantity+quantity_value,value_rial=null,updated_at=clock_timestamp() where id=position.id;
 update marketplace_private.usdt_intents set status='credited',tx_hash=hash_value,log_index=p_log_index,credited_at=clock_timestamp(),transaction_id=tx where id=i.id returning * into i;
 insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,after_data,changed_fields,actor_kind,source,source_key)
 values(i.user_id,'wallet','usdt_deposit_credited','usdt_deposit_ledger',i.id::text,jsonb_build_object('network',i.network,'amount_micro',i.amount_micro::text,'tx_hash',hash_value,'log_index',p_log_index,'transaction_id',tx),array['status'],'system','usdt-deposit','usdt-credit:'||i.id);
 insert into marketplace_private.notifications(user_id,event_key,title,body,kind,severity,action_url)
 values(i.user_id,'usdt-credit:'||i.id,'{"fa":"واریز تتر تأیید شد","en":"USDT deposit confirmed"}',jsonb_build_object('fa',trim_scale(quantity_value)::text||' تتر پس از تأیید انتقال شبکه به موجودی واقعی اضافه شد.','en',trim_scale(quantity_value)::text||' USDT was added to your real balance after chain verification.'),'wallet','success','/wallet/transactions');
 insert into marketplace_private.admin_notifications(event_key,event_type,user_id,title,body,action_url)
 values('usdt-credit:'||i.id,'usdt_deposit_credited',i.user_id,'{"fa":"واریز واقعی تتر تأیید شد"}',jsonb_build_object('fa',trim_scale(quantity_value)::text||' USDT · '||i.network||' · '||hash_value),'/customers/'||i.user_id||'/activity?category=wallet');
 return jsonb_build_object('ok',true,'duplicate',false,'status','credited','transaction_id',tx,'intent',marketplace_private.usdt_intent_json(i));
end$$;
create or replace function public.marketplace_usdt_pending(p_limit integer default 5) returns jsonb language plpgsql security definer set search_path='' as $$
declare result jsonb;begin
 if p_limit is null or p_limit not between 1 and 25 then raise exception 'invalid_usdt_limit';end if;
 -- Reserve the least recently checked rows. Invalid hashes cannot starve later valid deposits.
 with chosen as materialized(select id from marketplace_private.usdt_intents where status='pending' and candidate_tx_hash is not null and expires_at>=clock_timestamp()-interval '7 days' order by last_checked_at nulls first,submitted_at,id limit p_limit for update skip locked),
 checked as(update marketplace_private.usdt_intents i set last_checked_at=clock_timestamp() from chosen c where i.id=c.id returning i.user_id,i.id,i.submitted_at)
 select coalesce(jsonb_agg(jsonb_build_object('user_id',user_id,'id',id) order by submitted_at,id),'[]') into result from checked;
 return result;
end$$;

-- Preserve all prior review/title behavior while keeping funding receipts informational.
do $$declare definition text;begin
 select pg_get_functiondef('marketplace_private.customer_activity_reviewable(text,text,text)'::regprocedure) into definition;
 if position('demo_credits' in definition)=0 then
  definition:=replace(definition,'SELECT ','SELECT p_entity NOT IN (''demo_credits'',''usdt_intents'',''usdt_deposit_ledger'') AND ');
  definition:=replace(definition,'select ','select p_entity not in (''demo_credits'',''usdt_intents'',''usdt_deposit_ledger'') and ');
  if position('demo_credits' in definition)=0 then raise exception 'funding_activity_review_contract_missing';end if;
  execute definition;
 end if;
 select pg_get_functiondef('marketplace_private.customer_activity_title(text,text)'::regprocedure) into definition;
 if position('demo_credit' in definition)=0 then
  definition:=replace(definition,'case lower(p_action)','case lower(p_action) when ''demo_credit'' then ''شارژ موجودی آزمایشی غیرقابل برداشت'' when ''usdt_deposit_requested'' then ''درخواست واریز تتر'' when ''usdt_deposit_credited'' then ''تأیید واریز واقعی تتر'' when ''usdt_hash_submitted'' then ''ارسال شناسه تراکنش تتر برای بررسی''');
  if position('demo_credit' in definition)=0 then raise exception 'funding_activity_title_contract_missing';end if;
  execute definition;
 end if;
end$$;
do $$declare existing_check text;begin
 select pg_get_expr(conbin,conrelid) into existing_check from pg_constraint where conrelid='marketplace_private.admin_notifications'::regclass and conname='admin_notifications_event_type_check';
 if existing_check is null then raise exception 'funding_admin_notification_contract_missing';end if;
 if position('demo_credit' in existing_check)=0 or position('usdt_deposit_credited' in existing_check)=0 or position('funding_requested' in existing_check)=0 then
  alter table marketplace_private.admin_notifications drop constraint admin_notifications_event_type_check;
  execute format('alter table marketplace_private.admin_notifications add constraint admin_notifications_event_type_check check ((%s) or event_type in (''demo_credit'',''usdt_deposit_credited'',''funding_requested'',''funding_proof_verified'',''funding_approved'',''funding_rejected'',''funding_problem'',''funding_modes_changed''))',existing_check);
 end if;
end$$;
revoke all on function marketplace_private.usdt_intent_json(marketplace_private.usdt_intents) from public,anon,authenticated,service_role;
revoke all on function public.marketplace_demo_wallet(),public.marketplace_demo_credit(uuid,text,text),public.marketplace_usdt_settings(),public.marketplace_usdt_settings_update(text,bigint,boolean,text,text),public.marketplace_usdt_state(),public.marketplace_usdt_intent_create(uuid,text,bigint),public.marketplace_usdt_submit_hash(uuid,text),public.marketplace_usdt_verify_context(uuid,uuid),public.marketplace_usdt_verify_claim(uuid,uuid),public.marketplace_usdt_credit(uuid,text,integer,bigint,text,timestamptz,bigint,text,text),public.marketplace_usdt_pending(integer) from public,anon,authenticated,service_role;
grant execute on function public.marketplace_demo_wallet(),public.marketplace_demo_credit(uuid,text,text),public.marketplace_usdt_settings(),public.marketplace_usdt_settings_update(text,bigint,boolean,text,text),public.marketplace_usdt_state(),public.marketplace_usdt_intent_create(uuid,text,bigint),public.marketplace_usdt_submit_hash(uuid,text) to authenticated;
grant execute on function public.marketplace_usdt_verify_context(uuid,uuid),public.marketplace_usdt_verify_claim(uuid,uuid),public.marketplace_usdt_credit(uuid,text,integer,bigint,text,timestamptz,bigint,text,text),public.marketplace_usdt_pending(integer) to service_role;
comment on table marketplace_private.demo_positions is 'Separate nonwithdrawable test balances. Never used by real settlement or withdrawal.';
comment on table marketplace_private.demo_credits is 'Immutable demo funding history; no cash value.';
comment on table marketplace_private.usdt_intents is 'One-time exact-amount reservation, never reused. Destination and verifier credential are snapshotted. Credit requires a verified transfer mined within the one-hour window.';
comment on table marketplace_private.usdt_deposit_ledger is 'Immutable, service-verified real USDT credits. One credit per network transaction event.';
-- Every new funding request is reviewed before posting. Automatic review is configured on,
-- with a per-record manual override on by default. Proof and approval are separate records.
create table if not exists marketplace_private.funding_reviews(
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id) on delete restrict,
 kind text not null check(kind in('demo','usdt','bale','toman','withdrawal')),direction text not null check(direction in('deposit','withdrawal')),
 source_table text not null,source_id uuid not null,amount numeric(30,6) not null check(amount>0),currency text not null check(currency in('IRR','USDT')),
 status text not null default 'pending' check(status in('pending','approved','rejected','problem')),version integer not null default 1 check(version>0),
 automatic_enabled boolean not null default true,manual_required boolean not null default true,
 verification_status text not null default 'unverified' check(verification_status in('unverified','verified','not_required')),
 execution_status text not null default 'awaiting_review' check(execution_status in('awaiting_review','credited','reserved','awaiting_payout','released')),
 evidence jsonb not null default '{}',review_note text not null default '',reviewed_by uuid references auth.users(id),reviewed_at timestamptz,
 transaction_id uuid references marketplace_private.transactions(id),created_at timestamptz not null default clock_timestamp(),updated_at timestamptz not null default clock_timestamp(),
 unique(source_table,source_id),check(currency<>'IRR' or amount=trunc(amount))
);
create table if not exists marketplace_private.funding_review_history(
 id bigint generated always as identity primary key,review_id uuid not null references marketplace_private.funding_reviews(id) on delete restrict,
 version integer not null,action text not null,actor_id uuid,record jsonb not null,created_at timestamptz not null default clock_timestamp(),unique(review_id,version)
);
create table if not exists marketplace_private.funding_proofs(
 review_id uuid primary key references marketplace_private.funding_reviews(id) on delete restrict,proof_key text not null unique,evidence jsonb not null,
 verified_at timestamptz not null default clock_timestamp()
);
create table if not exists marketplace_private.funding_postings(
 review_id uuid primary key references marketplace_private.funding_reviews(id) on delete restrict,transaction_id uuid unique references marketplace_private.transactions(id),
 created_at timestamptz not null default clock_timestamp()
);
create table if not exists marketplace_private.demo_requests(
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id) on delete restrict,request_id uuid not null,
 currency text not null check(currency in('IRR','USDT')),amount numeric(30,6) not null check(amount>0),created_at timestamptz not null default clock_timestamp(),unique(user_id,request_id)
);
create table if not exists marketplace_private.withdrawal_requests(
 id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users(id) on delete restrict,request_id uuid not null,
 amount_rial bigint not null check(amount_rial between 10 and 1000000000000 and amount_rial%10=0),bank_id uuid not null references marketplace_private.bank_accounts(id),
 bank_snapshot jsonb not null,position_id uuid not null references marketplace_private.asset_positions(id),transaction_id uuid not null unique references marketplace_private.transactions(id),
 released boolean not null default false,created_at timestamptz not null default clock_timestamp(),unique(user_id,request_id)
);
create index if not exists funding_reviews_owner_recent on marketplace_private.funding_reviews(user_id,created_at desc);
create index if not exists funding_reviews_queue on marketplace_private.funding_reviews(status,created_at desc);
do $$declare t text;begin
 foreach t in array array['funding_reviews','funding_review_history','funding_proofs','funding_postings','demo_requests','withdrawal_requests'] loop
  execute format('alter table marketplace_private.%I enable row level security',t);execute format('revoke all on marketplace_private.%I from public,anon,authenticated,service_role',t);
  execute format('drop trigger if exists funding_history_retained on marketplace_private.%I',t);
  execute format('create trigger funding_history_retained before delete on marketplace_private.%I for each row execute function marketplace_private.customer_record_retained()',t);
  execute format('drop trigger if exists funding_truncate_retained on marketplace_private.%I',t);
  execute format('create trigger funding_truncate_retained before truncate on marketplace_private.%I for each statement execute function marketplace_private.customer_record_retained()',t);
 end loop;
 foreach t in array array['funding_review_history','funding_proofs','funding_postings','demo_requests'] loop
  execute format('drop trigger if exists funding_history_immutable on marketplace_private.%I',t);
  execute format('create trigger funding_history_immutable before update on marketplace_private.%I for each row execute function marketplace_private.customer_record_retained()',t);
 end loop;
end$$;
-- Keep the original atomic posting routines private. No service API can call them directly.
do $$declare definition text;begin
 if to_regprocedure('marketplace_private.funding_usdt_post(uuid,text,integer,bigint,text,timestamptz,bigint,text,text)') is null then
  select pg_get_functiondef('public.marketplace_usdt_credit(uuid,text,integer,bigint,text,timestamptz,bigint,text,text)'::regprocedure) into definition;
  execute replace(definition,'FUNCTION public.marketplace_usdt_credit(','FUNCTION marketplace_private.funding_usdt_post(');
 end if;
 if to_regprocedure('marketplace_private.funding_fiat_post(uuid,bigint,text,text)') is null then
  select pg_get_functiondef('public.marketplace_deposit_credit(uuid,bigint,text,text)'::regprocedure) into definition;
  execute replace(definition,'FUNCTION public.marketplace_deposit_credit(','FUNCTION marketplace_private.funding_fiat_post(');
 end if;
end$$;
create or replace function marketplace_private.funding_view(p_id uuid) returns jsonb language plpgsql stable security definer set search_path='' as $$
declare r marketplace_private.funding_reviews;label text;can_approve boolean;reason text;begin
 select * into r from marketplace_private.funding_reviews where id=p_id;if r.id is null then raise exception 'funding_not_found';end if;
 select coalesce(nullif(trim(first_name||' '||last_name),''),username,'کاربر پلانک') into label from marketplace_private.customer_profiles where user_id=r.user_id;
 can_approve:=r.execution_status not in('credited','released','awaiting_payout') and r.verification_status in('verified','not_required');
 reason:=case when r.execution_status='credited' then 'این مبلغ قبلاً ثبت شده است.' when r.execution_status='released' then 'رزرو برداشت آزاد شده است؛ درخواست تازه لازم است.' when r.execution_status='awaiting_payout' then 'این برداشت تأیید شده ولی هنوز پرداخت نشده است.' when r.verification_status='unverified' then 'مدرک پرداخت واقعی هنوز توسط سرویس معتبر تأیید نشده است.' end;
 return jsonb_build_object('id',r.id,'kind',r.kind,'test_mode',r.kind='demo' or coalesce((r.evidence->>'test_mode')::boolean,false),'direction',r.direction,'source_id',r.source_id,'user_id',r.user_id,'display_name',coalesce(label,'کاربر پلانک'),'amount',trim_scale(r.amount)::text,'currency',r.currency,
 'status',r.status,'version',r.version,'automatic_enabled',r.automatic_enabled,'manual_required',r.manual_required,'created_at',r.created_at,'updated_at',r.updated_at,'review_note',r.review_note,
 'verification_status',r.verification_status,'execution_status',r.execution_status,'can_approve',can_approve,'approval_block_reason',reason,'evidence',r.evidence,
 'checks',jsonb_build_array(jsonb_build_object('key','payment_evidence','label','تأیید مستندات و مبلغ','status',case when r.verification_status='unverified' then 'pending' else 'passed' end,'detail',case when r.kind='demo' then 'موجودی آزمایشی است؛ ارزش نقدی ندارد.' when r.direction='withdrawal' then 'موجودی واقعی برای این درخواست رزرو شده است؛ پرداخت اجرا نشده است.' when r.verification_status='verified' then 'رسید معتبر و مبلغ درخواست تطبیق یافته است.' else 'در انتظار استعلام یا رسید تأییدشده؛ تأیید مدیر جایگزین رسید نیست.' end),
 jsonb_build_object('key','manual_review','label','بررسی مدیر','status',case when r.status='approved' then 'passed' when r.status in('rejected','problem') then 'failed' else 'pending' end,'detail',case when r.manual_required then 'تأیید دستی مدیر الزامی است.' else 'بررسی خودکار فقط با مدرک معتبر مجاز است.' end),
 jsonb_build_object('key','execution','label','اجرای مالی','status',case when r.execution_status='credited' then 'passed' else 'pending' end,'detail',case when r.execution_status='credited' then 'موجودی ثبت شده است.' when r.execution_status='awaiting_payout' then 'مجوز برداشت صادر شده؛ وجه هنوز ارسال نشده است.' when r.execution_status='released' then 'رزرو آزاد شد؛ وجهی ارسال نشده است.' else 'عملیات نهایی مالی هنوز اجرا نشده است.' end)));
end$$;
create or replace function marketplace_private.funding_event(p_id uuid,p_action text) returns void language plpgsql security definer set search_path='' as $$
declare r marketplace_private.funding_reviews;heading text;body text;begin
 select * into strict r from marketplace_private.funding_reviews where id=p_id;
 heading:=case p_action when 'funding_requested' then 'درخواست مالی در انتظار بررسی' when 'funding_proof_verified' then 'مدرک پرداخت تأیید شد' when 'funding_approved' then 'درخواست مالی تأیید شد' when 'funding_rejected' then 'درخواست مالی رد شد' when 'funding_problem' then 'درخواست مالی نیاز به پیگیری دارد' else 'روش بررسی درخواست تغییر کرد' end;
 body:=case when r.kind='demo' or coalesce((r.evidence->>'test_mode')::boolean,false) then 'موجودی آزمایشی و غیرقابل برداشت. ' else '' end||
 case when r.execution_status='credited' then 'مبلغ به موجودی اضافه شد.' when r.execution_status='awaiting_payout' then 'برداشت تأیید شد ولی هنوز وجهی ارسال نشده است.' when r.execution_status='released' then 'رزرو برداشت آزاد شد و وجهی ارسال نشد.' when r.status='rejected' then 'درخواست رد شده و موجودی افزایش نیافته است.' when r.status='problem' then 'درخواست تا رفع مشکل متوقف است.' when r.verification_status='verified' then 'مدرک معتبر ثبت شد؛ درخواست در انتظار تأیید مالی است.' else 'درخواست ثبت شد و در انتظار بررسی است؛ پرداخت یا افزایش موجودی انجام نشده است.' end||case when r.review_note<>'' then ' توضیح مدیر: '||r.review_note else '' end;
 insert into marketplace_private.funding_review_history(review_id,version,action,actor_id,record) values(r.id,r.version,p_action,auth.uid(),marketplace_private.funding_view(r.id)) on conflict(review_id,version) do nothing;
 insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,after_data,changed_fields,actor_id,actor_kind,source,source_key)
 values(r.user_id,'wallet',p_action,'funding_reviews',r.id::text,jsonb_build_object('kind',r.kind,'amount',trim_scale(r.amount)::text,'currency',r.currency,'status',r.status,'verification_status',r.verification_status,'execution_status',r.execution_status,'automatic_enabled',r.automatic_enabled,'manual_required',r.manual_required,'review_note',r.review_note),array['status'],auth.uid(),case when auth.uid()=r.user_id then 'customer' when auth.uid() is not null then 'admin' else 'system' end,'funding-review','funding-review:'||r.id||':'||r.version) on conflict(source_key) do nothing;
 insert into marketplace_private.notifications(user_id,event_key,title,body,kind,severity,action_url) values(r.user_id,'funding-review:'||r.id||':'||r.version,jsonb_build_object('fa',heading),jsonb_build_object('fa',body),'wallet',case when r.status in('rejected','problem') then 'warning' when r.execution_status='credited' then 'success' else 'info' end,'/wallet/transactions') on conflict(user_id,event_key) where event_key is not null do nothing;
 insert into marketplace_private.admin_notifications(event_key,event_type,user_id,title,body,action_url)
 values('funding-review:'||r.id||':'||r.version,p_action,r.user_id,jsonb_build_object('fa',heading),jsonb_build_object('fa',body),'/?section=funding&request='||r.id) on conflict(event_key) do nothing;
end$$;
create or replace function marketplace_private.funding_add(p_user uuid,p_kind text,p_source text,p_source_id uuid,p_amount numeric,p_currency text,p_evidence jsonb default '{}',p_verification text default 'unverified',p_execution text default 'awaiting_review') returns uuid language plpgsql security definer set search_path='' as $$
declare rid uuid;begin
 insert into marketplace_private.funding_reviews(user_id,kind,direction,source_table,source_id,amount,currency,evidence,verification_status,execution_status)
 values(p_user,p_kind,case when p_kind='withdrawal' then 'withdrawal' else 'deposit' end,p_source,p_source_id,p_amount,p_currency,p_evidence,p_verification,p_execution) on conflict(source_table,source_id) do nothing returning id into rid;
 if rid is null then select id into strict rid from marketplace_private.funding_reviews where source_table=p_source and source_id=p_source_id;else perform marketplace_private.funding_event(rid,'funding_requested');end if;
 return rid;
end$$;
create or replace function marketplace_private.funding_source_capture() returns trigger language plpgsql security definer set search_path='' as $$
begin
 if tg_table_name='usdt_intents' then
  perform marketplace_private.funding_add(new.user_id,'usdt','usdt_intents',new.id,new.amount_micro::numeric/1000000,'USDT',jsonb_build_object('network',new.network,'address',new.address,'requested_micro',new.requested_micro::text,'amount_micro',new.amount_micro::text));
 elsif tg_table_name='fiat_payment_intents' then
  perform marketplace_private.funding_add(new.user_id,case when new.provider='bale' then 'bale' else 'toman' end,'fiat_payment_intents',new.id,new.amount_rial,'IRR',jsonb_build_object('provider',new.provider,'test_mode',new.test_mode));
 elsif tg_table_name='toman_deposit_requests' and new.status='submitted' then
  perform marketplace_private.funding_add(new.user_id,'toman','toman_deposit_requests',new.id,new.amount_rial,'IRR',jsonb_build_object('method',new.method,'transfer_reference',new.transfer_reference,'bank_name',new.bank_snapshot->>'bank_name','iban_last4',new.bank_snapshot->>'iban_last4','destination_id',new.destination_id));
 end if;return new;
end$$;
drop trigger if exists funding_source_capture on marketplace_private.usdt_intents;
create trigger funding_source_capture after insert on marketplace_private.usdt_intents for each row execute function marketplace_private.funding_source_capture();
drop trigger if exists funding_source_capture on marketplace_private.fiat_payment_intents;
create trigger funding_source_capture after insert on marketplace_private.fiat_payment_intents for each row execute function marketplace_private.funding_source_capture();
do $$begin
 if to_regclass('marketplace_private.toman_deposit_requests') is not null then
  execute 'drop trigger if exists funding_source_capture on marketplace_private.toman_deposit_requests';
  execute 'create trigger funding_source_capture after insert or update on marketplace_private.toman_deposit_requests for each row execute function marketplace_private.funding_source_capture()';
 end if;
end$$;

create or replace function marketplace_private.funding_approve(p_id uuid,p_note text default '') returns jsonb language plpgsql security definer set search_path='' as $$
declare r marketplace_private.funding_reviews;e jsonb;receipt jsonb;tx uuid;position uuid;w marketplace_private.withdrawal_requests;begin
 select * into r from marketplace_private.funding_reviews where id=p_id for update;if r.id is null then raise exception 'funding_not_found';end if;
 if r.execution_status in('credited','awaiting_payout') then return marketplace_private.funding_view(r.id);end if;
 if r.execution_status='released' then raise exception 'withdrawal_released';end if;
 if r.verification_status not in('verified','not_required') then raise exception 'funding_proof_required';end if;
 if r.kind='demo' or (r.source_table='fiat_payment_intents' and r.evidence->>'test_mode'='true') then
  perform pg_advisory_xact_lock(hashtextextended('demo-wallet:'||r.user_id,0));
  insert into marketplace_private.demo_positions(user_id,asset_symbol) values(r.user_id,r.currency) on conflict(user_id,asset_symbol) do nothing;
  perform 1 from marketplace_private.demo_positions where user_id=r.user_id and asset_symbol=r.currency for update;
  if (r.currency='IRR' and r.amount>1000000000) or (r.currency='USDT' and r.amount>100000) or exists(select 1 from marketplace_private.demo_positions where user_id=r.user_id and asset_symbol=r.currency and quantity+r.amount>case when r.currency='IRR' then 1000000000000 else 100000000 end) then raise exception 'demo_balance_limit';end if;
  insert into marketplace_private.demo_credits(user_id,request_id,currency,amount) values(r.user_id,r.id,r.currency,r.amount);
  update marketplace_private.demo_positions set quantity=quantity+r.amount,updated_at=clock_timestamp() where user_id=r.user_id and asset_symbol=r.currency;
 elsif r.kind='usdt' then
  select evidence into strict e from marketplace_private.funding_proofs where review_id=r.id;
  receipt:=marketplace_private.funding_usdt_post(r.source_id,e->>'tx_hash',(e->>'log_index')::integer,(e->>'block_number')::bigint,e->>'block_hash',(e->>'block_time')::timestamptz,(e->>'amount_micro')::bigint,e->>'address',e->>'network');tx:=(receipt->>'transaction_id')::uuid;
 elsif r.source_table='fiat_payment_intents' then
  select evidence into strict e from marketplace_private.funding_proofs where review_id=r.id;
  receipt:=marketplace_private.funding_fiat_post(r.source_id,r.amount::bigint,e->>'authority',e->>'provider_reference');tx:=(receipt->>'transaction_id')::uuid;
 elsif r.source_table='toman_deposit_requests' then
  if not exists(select 1 from marketplace_private.funding_proofs where review_id=r.id) then raise exception 'funding_proof_required';end if;
  perform marketplace_private.ensure_cash_position(r.user_id);
  insert into marketplace_private.transactions(user_id,reference,kind,asset_symbol,amount,status,completed_at) values(r.user_id,'bank-deposit:'||r.source_id,'deposit','IRR',r.amount,'confirmed',clock_timestamp()) returning id into tx;
  update marketplace_private.asset_positions set quantity=quantity+r.amount,value_rial=quantity+r.amount,value_usdt=null,updated_at=clock_timestamp() where user_id=r.user_id and asset_symbol='IRR' and project_id is null;
 elsif r.kind='withdrawal' then
  perform marketplace_private.customer_access_assert(r.user_id,'withdraw');
  select * into strict w from marketplace_private.withdrawal_requests where id=r.source_id for update;
  if w.released then raise exception 'withdrawal_released';end if;
  tx:=w.transaction_id;
 else raise exception 'funding_source_unavailable';end if;
 insert into marketplace_private.funding_postings(review_id,transaction_id) values(r.id,tx);
 update marketplace_private.funding_reviews set status='approved',execution_status=case when kind='withdrawal' then 'awaiting_payout' else 'credited' end,transaction_id=tx,review_note=p_note,reviewed_by=auth.uid(),reviewed_at=clock_timestamp(),version=version+1,updated_at=clock_timestamp() where id=r.id;
 perform marketplace_private.funding_event(r.id,'funding_approved');return marketplace_private.funding_view(r.id);
end$$;
create or replace function marketplace_private.funding_accept_proof(p_id uuid,p_key text,p_evidence jsonb) returns boolean language plpgsql security definer set search_path='' as $$
declare r marketplace_private.funding_reviews;oldproof marketplace_private.funding_proofs;begin
 select * into strict r from marketplace_private.funding_reviews where id=p_id for update;
 select * into oldproof from marketplace_private.funding_proofs where review_id=r.id;
 if oldproof.review_id is not null then
  if oldproof.proof_key<>p_key or oldproof.evidence<>p_evidence then raise exception 'funding_proof_conflict';end if;return true;
 end if;
 begin
  insert into marketplace_private.funding_proofs(review_id,proof_key,evidence) values(r.id,p_key,p_evidence);
 exception when unique_violation then raise exception 'funding_proof_used';end;
 update marketplace_private.funding_reviews set verification_status='verified',evidence=evidence||p_evidence,version=version+1,updated_at=clock_timestamp() where id=r.id;
 perform marketplace_private.funding_event(r.id,'funding_proof_verified');
 if r.automatic_enabled and not r.manual_required and r.status='pending' then perform marketplace_private.funding_approve(r.id,'تأیید خودکار پس از تطبیق مدرک معتبر');end if;
 return false;
end$$;

create or replace function public.marketplace_funding_reviews(p_limit integer default 50) returns jsonb language plpgsql stable 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_limit is null or p_limit not between 1 and 100 then raise exception 'invalid_funding_limit';end if;
 return jsonb_build_object('items',coalesce((select jsonb_agg(marketplace_private.funding_view(id) order by created_at desc,id desc) from (select id,created_at from marketplace_private.funding_reviews order by created_at desc,id desc limit p_limit)r),'[]'));
end$$;
create or replace function public.marketplace_funding_review(p_id uuid) returns jsonb language plpgsql stable 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;return marketplace_private.funding_view(p_id);end$$;
create or replace function public.marketplace_funding_history(p_limit integer default 50) returns jsonb language plpgsql volatile security definer set search_path='' as $$
declare uid uuid:=auth.uid();begin
 perform marketplace_private.customer_access_assert(uid);
 if p_limit is null or p_limit not between 1 and 100 then raise exception 'invalid_funding_limit';end if;
 return jsonb_build_object('items',coalesce((select jsonb_agg(marketplace_private.funding_view(id)-'user_id'-'display_name' order by created_at desc,id desc) from (select id,created_at from marketplace_private.funding_reviews where user_id=uid order by created_at desc,id desc limit p_limit)r),'[]'));
end$$;
create or replace function public.marketplace_funding_review_update(p_id uuid,p_version integer,p_action text,p_note text default '',p_automatic_enabled boolean default null,p_manual_required boolean default null) returns jsonb language plpgsql security definer set search_path='' as $$
declare r marketplace_private.funding_reviews;w marketplace_private.withdrawal_requests;begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if p_action is null or p_action not in('approve','reject','problem','modes') or p_note is null or length(trim(p_note))>2000 or (p_action<>'modes' and length(trim(p_note))<3) then raise exception 'invalid_funding_review';end if;
 select * into r from marketplace_private.funding_reviews where id=p_id for update;if r.id is null then raise exception 'funding_not_found';end if;
 if p_version is distinct from r.version then raise exception 'funding_version_conflict';end if;
 if p_action='modes' then
  if p_automatic_enabled is null or p_manual_required is null then raise exception 'invalid_funding_review';end if;
  if r.execution_status in('credited','released','awaiting_payout') then raise exception 'funding_already_processed';end if;
  update marketplace_private.funding_reviews set automatic_enabled=p_automatic_enabled,manual_required=p_manual_required,review_note=trim(p_note),version=version+1,updated_at=clock_timestamp() where id=r.id;
  perform marketplace_private.funding_event(r.id,'funding_modes_changed');
  if p_automatic_enabled and not p_manual_required and r.status='pending' and r.verification_status in('verified','not_required') then return marketplace_private.funding_approve(r.id,'تأیید خودکار پس از تغییر روش بررسی');end if;
 elsif p_action='approve' then return marketplace_private.funding_approve(r.id,trim(p_note));
 else
  if r.execution_status='credited' then raise exception 'funding_already_processed';end if;
  -- An authorized withdrawal can still be cancelled and released, but cannot be
  -- moved into a problem state which would leave its payout authorization ambiguous.
  if p_action='problem' and r.execution_status='awaiting_payout' then raise exception 'funding_already_processed';end if;
  if r.kind='withdrawal' and p_action='reject' and r.execution_status<>'released' then
   select * into strict w from marketplace_private.withdrawal_requests where id=r.source_id for update;
   if not w.released then
    update marketplace_private.asset_positions set locked_quantity=locked_quantity-w.amount_rial,updated_at=clock_timestamp() where id=w.position_id;
    update marketplace_private.withdrawal_requests set released=true where id=w.id;
    update marketplace_private.transactions set status='rejected' where id=w.transaction_id;
   end if;
  end if;
  update marketplace_private.funding_reviews set status=case when p_action='reject' then 'rejected' else 'problem' end,execution_status=case when kind='withdrawal' and p_action='reject' then 'released' else execution_status end,
   review_note=trim(p_note),reviewed_by=auth.uid(),reviewed_at=clock_timestamp(),version=version+1,updated_at=clock_timestamp() where id=r.id;
  perform marketplace_private.funding_event(r.id,case when p_action='reject' then 'funding_rejected' else 'funding_problem' end);
 end if;
 return marketplace_private.funding_view(r.id);
end$$;

create or replace function public.marketplace_demo_wallet() returns jsonb language plpgsql volatile security definer set search_path='' as $$
declare uid uuid:=auth.uid();begin
 perform marketplace_private.customer_access_assert(uid);
 return jsonb_build_object('enabled',true,'balance_rial',coalesce((select trim_scale(quantity)::text from marketplace_private.demo_positions where user_id=uid and asset_symbol='IRR'),'0'),'balance_usdt',coalesce((select trim_scale(quantity)::text from marketplace_private.demo_positions where user_id=uid and asset_symbol='USDT'),'0'),
 'history',coalesce((select jsonb_agg(jsonb_build_object('id',d.id,'currency',d.currency,'amount',trim_scale(d.amount)::text,'created_at',d.created_at,'status',r.status,'review_id',r.id,'review_note',r.review_note) order by d.created_at desc,d.id desc) from (select * from marketplace_private.demo_requests where user_id=uid order by created_at desc,id desc limit 25)d join marketplace_private.funding_reviews r on r.source_table='demo_requests' and r.source_id=d.id),'[]'),
 'limits',jsonb_build_object('max_amount_toman','100000000','max_amount_usdt','100000'));
end$$;
create or replace function public.marketplace_demo_credit(p_request uuid,p_currency text,p_amount text) returns jsonb language plpgsql security definer set search_path='' as $$
declare uid uuid:=auth.uid();amount_value numeric;d marketplace_private.demo_requests;r marketplace_private.funding_reviews;rid uuid;balance_value numeric;duplicate_value boolean:=false;begin
 perform marketplace_private.customer_access_assert(uid);
 if p_request is null or p_currency is null or p_currency not in('IRR','USDT') or p_amount is null or length(p_amount)>32 or p_amount!~'^(0|[1-9][0-9]*)(\.[0-9]{1,6})?$' then raise exception 'invalid_demo_amount';end if;
 amount_value:=p_amount::numeric;
 if amount_value<=0 or (p_currency='IRR' and (amount_value<>trunc(amount_value) or amount_value>1000000000)) or (p_currency='USDT' and amount_value>100000) then raise exception 'invalid_demo_amount';end if;
 perform pg_advisory_xact_lock(hashtextextended('demo-wallet:'||uid,0));
 select * into d from marketplace_private.demo_requests where user_id=uid and request_id=p_request;
 if d.id is not null then
  if d.currency<>p_currency or d.amount<>amount_value then raise exception 'demo_request_conflict';end if;duplicate_value:=true;
 else
  if (select count(*) from marketplace_private.demo_requests where user_id=uid and created_at>clock_timestamp()-interval '1 hour')>=20 then raise exception 'demo_rate_limit';end if;
  select coalesce(sum(quantity),0)+(select coalesce(sum(amount),0) from marketplace_private.funding_reviews where user_id=uid and currency=p_currency and kind='demo' and status in('pending','problem')) into balance_value from marketplace_private.demo_positions where user_id=uid and asset_symbol=p_currency;
  if balance_value+amount_value>(case when p_currency='IRR' then 1000000000000 else 100000000 end) then raise exception 'demo_balance_limit';end if;
  insert into marketplace_private.demo_requests(user_id,request_id,currency,amount) values(uid,p_request,p_currency,amount_value) returning * into d;
  perform marketplace_private.funding_add(uid,'demo','demo_requests',d.id,amount_value,p_currency,'{"test_mode":true}','not_required');
 end if;
 select * into strict r from marketplace_private.funding_reviews where source_table='demo_requests' and source_id=d.id;
 return public.marketplace_demo_wallet()||jsonb_build_object('credited',r.execution_status='credited','pending',r.status='pending','status',r.status,'review_id',r.id,'duplicate',duplicate_value);
end$$;

create or replace function marketplace_private.usdt_intent_json(i marketplace_private.usdt_intents) returns jsonb language sql stable set search_path='' as $$
 select jsonb_build_object('id',i.id,'network',i.network,'address',i.address,'amount_micro',i.amount_micro::text,'requested_micro',i.requested_micro::text,
 'status',case when i.status='credited' then 'credited' when p.review_id is not null then 'pending' when i.expires_at<=clock_timestamp() then 'expired' else 'pending' end,'created_at',i.created_at,'expires_at',i.expires_at,'candidate_tx_hash',i.candidate_tx_hash,'tx_hash',i.tx_hash,'log_index',i.log_index,
 'verification_status',case when p.review_id is not null then 'verified' else 'unverified' end,'chain_verified_at',p.verified_at,'review_required',coalesce(r.status<>'approved',true),'review_decision',r.status,'review_id',r.id,'review_note',coalesce(r.review_note,''))
 from (select 1)x left join marketplace_private.funding_reviews r on r.source_table='usdt_intents' and r.source_id=i.id left join marketplace_private.funding_proofs p on p.review_id=r.id;
$$;
create or replace function public.marketplace_usdt_record_proof(p_intent uuid,p_tx_hash text,p_log_index integer,p_block_number bigint,p_block_hash text,p_block_time timestamptz,p_amount_micro bigint,p_address text,p_network text) returns jsonb language plpgsql security definer set search_path='' as $$
declare i marketplace_private.usdt_intents;r marketplace_private.funding_reviews;e jsonb;duplicate_value boolean;hash_value text:=lower(p_tx_hash);block_value text:=lower(p_block_hash);address_value text;begin
 select * into r from marketplace_private.funding_reviews where source_table='usdt_intents' and source_id=p_intent for update;if r.id is null then raise exception 'usdt_intent_not_found';end if;
 select * into strict i from marketplace_private.usdt_intents where id=p_intent for update;address_value:=case when p_network='ethereum' then lower(p_address) else p_address end;
 if p_network is distinct from i.network or address_value is distinct from i.address or p_amount_micro is distinct from i.amount_micro or hash_value is null or block_value is null
 or not(case when i.network='ethereum' then hash_value~'^0x[0-9a-f]{64}$' and block_value~'^0x[0-9a-f]{64}$' else hash_value~'^[0-9a-f]{64}$' and block_value~'^[0-9a-f]{64}$' end)
 or p_log_index is null or p_log_index<0 or p_block_number is null or p_block_number<0 or p_block_time is null or not isfinite(p_block_time) or p_block_time<i.created_at or p_block_time>=i.expires_at or p_block_time>clock_timestamp() then raise exception 'usdt_verification_mismatch';end if;
 if exists(select 1 from marketplace_private.funding_proofs where proof_key='usdt:'||p_network||':'||hash_value||':'||p_log_index and review_id<>r.id) then raise exception 'usdt_transfer_used';end if;
 e:=jsonb_build_object('network',p_network,'address',address_value,'tx_hash',hash_value,'log_index',p_log_index,'block_number',p_block_number,'block_hash',block_value,'block_time',p_block_time,'amount_micro',p_amount_micro::text);
 duplicate_value:=marketplace_private.funding_accept_proof(r.id,'usdt:'||p_network||':'||hash_value||':'||p_log_index,e);
 select * into strict i from marketplace_private.usdt_intents where id=p_intent;
 return jsonb_build_object('ok',true,'duplicate',duplicate_value,'status',case when i.status='credited' then 'credited' else 'pending' end,'verificationStatus','verified','reviewRequired',i.status<>'credited','intent',marketplace_private.usdt_intent_json(i));
end$$;
create or replace function public.marketplace_usdt_credit(p_intent uuid,p_tx_hash text,p_log_index integer,p_block_number bigint,p_block_hash text,p_block_time timestamptz,p_amount_micro bigint,p_address text,p_network text) returns jsonb language plpgsql security definer set search_path='' as $$
begin return public.marketplace_usdt_record_proof(p_intent,p_tx_hash,p_log_index,p_block_number,p_block_hash,p_block_time,p_amount_micro,p_address,p_network);end$$;
-- Refuse candidate changes after a proof has been bound. Preserve same-hash retries.
create or replace function marketplace_private.funding_usdt_hash_guard() returns trigger language plpgsql security definer set search_path='' as $$
declare proof_hash text;begin
 if new.candidate_tx_hash is distinct from old.candidate_tx_hash then
  select p.evidence->>'tx_hash' into proof_hash from marketplace_private.funding_proofs p join marketplace_private.funding_reviews r on r.id=p.review_id where r.source_table='usdt_intents' and r.source_id=old.id;
  if proof_hash is not null and new.candidate_tx_hash is distinct from proof_hash then raise exception 'funding_proof_conflict';end if;
 end if;return new;
end$$;
drop trigger if exists funding_usdt_hash_guard on marketplace_private.usdt_intents;
create trigger funding_usdt_hash_guard before update on marketplace_private.usdt_intents for each row execute function marketplace_private.funding_usdt_hash_guard();
create or replace function public.marketplace_usdt_verify_claim(p_user uuid,p_intent uuid) returns boolean language plpgsql security definer set search_path='' as $$
declare i marketplace_private.usdt_intents;begin
 select * into i from marketplace_private.usdt_intents where id=p_intent and user_id=p_user for update;if i.id is null then raise exception 'usdt_intent_not_found';end if;
 if i.status='credited' or i.candidate_tx_hash is null or i.last_verification_at>clock_timestamp()-interval '10 seconds' or exists(select 1 from marketplace_private.funding_proofs p join marketplace_private.funding_reviews r on r.id=p.review_id where r.source_table='usdt_intents' and r.source_id=i.id) then return false;end if;
 update marketplace_private.usdt_intents set last_verification_at=clock_timestamp() where id=i.id;return true;
end$$;
create or replace function public.marketplace_usdt_pending(p_limit integer default 5) returns jsonb language plpgsql security definer set search_path='' as $$
declare result jsonb;begin
 if p_limit is null or p_limit not between 1 and 25 then raise exception 'invalid_usdt_limit';end if;
 with chosen as materialized(select i.id from marketplace_private.usdt_intents i where i.status='pending' and i.candidate_tx_hash is not null and i.expires_at>=clock_timestamp()-interval '7 days' and not exists(select 1 from marketplace_private.funding_proofs p join marketplace_private.funding_reviews r on r.id=p.review_id where r.source_table='usdt_intents' and r.source_id=i.id) order by i.last_checked_at nulls first,i.submitted_at,i.id limit p_limit for update of i skip locked),
 checked as(update marketplace_private.usdt_intents i set last_checked_at=clock_timestamp() from chosen c where i.id=c.id returning i.user_id,i.id,i.submitted_at)
 select coalesce(jsonb_agg(jsonb_build_object('user_id',user_id,'id',id) order by submitted_at,id),'[]') into result from checked;return result;
end$$;

create or replace function public.marketplace_deposit_credit(p_payment uuid,p_verified_amount_rial bigint,p_authority text,p_reference text) returns jsonb language plpgsql security definer set search_path='' as $$
declare f marketplace_private.fiat_payment_intents;r marketplace_private.funding_reviews;duplicate_value boolean;begin
 select * into f from marketplace_private.fiat_payment_intents where id=p_payment;
 if f.id is null or f.test_mode or f.amount_rial is distinct from p_verified_amount_rial or f.authority is null or f.authority is distinct from p_authority or p_reference is null or length(p_reference) not between 1 and 200 or f.status not in('pending','credited') then raise exception 'payment_verification_mismatch';end if;
 select * into r from marketplace_private.funding_reviews where source_table='fiat_payment_intents' and source_id=f.id;
 -- Historical credited receipts remain idempotent; never recreate their credit as a new pending request.
 if r.id is null and f.status='credited' then return marketplace_private.funding_fiat_post(p_payment,p_verified_amount_rial,p_authority,p_reference)||jsonb_build_object('status','credited','review_id',null,'review_required',false);end if;
 if r.id is null then raise exception 'funding_not_found';end if;
 duplicate_value:=marketplace_private.funding_accept_proof(r.id,'fiat:'||f.provider||':'||f.merchant_account||':'||p_reference,jsonb_build_object('provider',f.provider,'authority',p_authority,'provider_reference',p_reference,'amount_rial',p_verified_amount_rial::text));
 select * into strict r from marketplace_private.funding_reviews where id=r.id;
 return jsonb_build_object('ok',true,'duplicate',duplicate_value,'status',case when r.execution_status='credited' then 'credited' else 'pending_review' end,'review_id',r.id,'review_required',r.execution_status<>'credited','transaction_id',r.transaction_id);
end$$;
create or replace function public.marketplace_funding_bank_proof(p_review uuid,p_reference text,p_amount_rial bigint,p_destination uuid) returns jsonb language plpgsql security definer set search_path='' as $$
declare r marketplace_private.funding_reviews;duplicate_value boolean;begin
 select * into r from marketplace_private.funding_reviews where id=p_review for update;
 if r.id is null or r.source_table<>'toman_deposit_requests' then raise exception 'funding_not_found';end if;
 if p_amount_rial is distinct from r.amount or p_destination::text is distinct from r.evidence->>'destination_id' or p_reference is null or p_reference!~'^[A-Za-z0-9_-]{3,100}$' then raise exception 'funding_proof_mismatch';end if;
 -- The bank reconciler supplies a globally unique bank-event reference, including its provider namespace.
 -- Destination methods may share the same company account, so destination ID cannot scope receipt reuse.
 duplicate_value:=marketplace_private.funding_accept_proof(r.id,'bank:'||p_reference,jsonb_build_object('provider','verified_bank_reconciliation','provider_reference',p_reference,'amount_rial',p_amount_rial::text,'destination_id',p_destination));
 return marketplace_private.funding_view(r.id)||jsonb_build_object('duplicate',duplicate_value);
end$$;
create or replace function marketplace_private.funding_bale_test_proof() returns trigger language plpgsql security definer set search_path='' as $$
declare f marketplace_private.fiat_payment_intents;rid uuid;begin
 if new.state='test_paid' and old.state is distinct from new.state then
  select * into strict f from marketplace_private.fiat_payment_intents where id=new.payment_id;
  if not f.test_mode then raise exception 'payment_verification_mismatch';end if;
  select id into strict rid from marketplace_private.funding_reviews where source_table='fiat_payment_intents' and source_id=f.id;
  perform marketplace_private.funding_accept_proof(rid,'bale-test:'||new.authority,jsonb_build_object('provider','bale','test_mode',true,'provider_reference','bale:'||new.authority,'amount_rial',f.amount_rial::text));
 end if;return new;
end$$;
drop trigger if exists funding_bale_test_proof on marketplace_private.bale_payments;
create trigger funding_bale_test_proof after update on marketplace_private.bale_payments for each row execute function marketplace_private.funding_bale_test_proof();
-- Adapt existing provider RPCs without changing their verification contracts.
do $$declare definition text;begin
 select pg_get_functiondef('public.marketplace_bale_payment_settle(text,text,text,text,bigint,text,text)'::regprocedure) into definition;
 definition:=replace(definition,$x$return receipt||jsonb_build_object('status','credited');$x$,$x$return receipt;$x$);
 definition:=replace(definition,$x$return jsonb_build_object('ok',true,'duplicate',true,'status','test_paid');$x$,$x$return public.marketplace_bale_payment_status(f.id,f.user_id)||jsonb_build_object('ok',true,'duplicate',true);$x$);
 definition:=replace(definition,$x$return jsonb_build_object('ok',true,'duplicate',false,'status','test_paid');$x$,$x$return public.marketplace_bale_payment_status(f.id,f.user_id)||jsonb_build_object('ok',true,'duplicate',false);$x$);
 execute definition;
end$$;
create or replace function public.marketplace_bale_payment_status(p_payment uuid,p_user uuid) returns jsonb language plpgsql stable security definer set search_path='' as $$
declare payload_value text;r marketplace_private.funding_reviews;value jsonb;f marketplace_private.fiat_payment_intents;begin
 select bp.payload into payload_value from marketplace_private.bale_payments bp join marketplace_private.fiat_payment_intents fp on fp.id=bp.payment_id where bp.payment_id=p_payment and fp.user_id=p_user;
 if payload_value is null then raise exception 'payment_not_found';end if;
 value:=public.marketplace_bale_payment_lookup(payload_value)||(select jsonb_build_object('id',p_payment,'start_token',start_token,'bot_username',bot_username) from marketplace_private.bale_payments where payment_id=p_payment);
 select * into r from marketplace_private.funding_reviews where source_table='fiat_payment_intents' and source_id=p_payment;
 if r.id is not null then
  select * into strict f from marketplace_private.fiat_payment_intents where id=p_payment;
  value:=value||jsonb_build_object('review_id',r.id,'review_required',r.status<>'approved','review_decision',r.status);
  if r.verification_status='verified' then value:=value||jsonb_build_object('status',case when r.execution_status='credited' then case when f.test_mode then 'test_paid' else 'credited' end else 'pending_review' end);end if;
 end if;return value;
end$$;

-- Every lookup and initiation retry carries the same held/settled projection.
do $$declare definition text;begin
 if to_regprocedure('marketplace_private.funding_bale_lookup(text)') is null then
  select pg_get_functiondef('public.marketplace_bale_payment_lookup(text)'::regprocedure) into definition;
  execute replace(definition,'public.marketplace_bale_payment_lookup','marketplace_private.funding_bale_lookup');
 end if;
 if to_regprocedure('marketplace_private.funding_bale_create(uuid,uuid,bigint)') is null then
  select pg_get_functiondef('public.marketplace_bale_payment_create(uuid,uuid,bigint)'::regprocedure) into definition;
  execute replace(definition,'public.marketplace_bale_payment_create','marketplace_private.funding_bale_create');
 end if;
 select pg_get_functiondef('public.marketplace_bale_payment_result(text,text,text,bigint,text)'::regprocedure) into definition;
 definition:=replace(definition,$x$return jsonb_build_object('status',case when b.state='paid' then 'credited' else 'test_paid' end);$x$,$x$return public.marketplace_bale_payment_status(f.id,f.user_id);$x$);execute definition;
end$$;
create or replace function public.marketplace_bale_payment_lookup(p_payload text) returns jsonb language plpgsql stable security definer set search_path='' as $$
declare value jsonb;r marketplace_private.funding_reviews;begin
 value:=marketplace_private.funding_bale_lookup(p_payload);
 select * into r from marketplace_private.funding_reviews where source_table='fiat_payment_intents' and source_id=(value->>'payment_id')::uuid;
 if r.id is not null then
  value:=value||jsonb_build_object('review_id',r.id,'review_required',r.status<>'approved','review_decision',r.status);
  if r.verification_status='verified' then value:=value||jsonb_build_object('status',case when r.execution_status='credited' then case when (value->>'test_mode')::boolean then 'test_paid' else 'credited' end else 'pending_review' end);end if;
 end if;return value;
end$$;
create or replace function public.marketplace_bale_payment_create(p_user uuid,p_request uuid,p_amount_rial bigint) returns jsonb language plpgsql security definer set search_path='' as $$
declare value jsonb;begin
 -- Match the immutable demo ledger's per-credit cap before creating a sandbox invoice.
 -- Keep the settings share lock through original creation so mode changes cannot race this check.
 perform 1 from marketplace_private.bale_settings where id for share;
 if p_amount_rial>1000000000 and exists(select 1 from marketplace_private.bale_settings where id and test_mode)
 and not exists(select 1 from marketplace_private.fiat_payment_intents where user_id=p_user and request_id=p_request) then raise exception 'test_deposit_limit';end if;
 value:=marketplace_private.funding_bale_create(p_user,p_request,p_amount_rial);
 return value||public.marketplace_bale_payment_status((value->>'payment_id')::uuid,p_user);
end$$;
create or replace function public.marketplace_deposit_history() returns jsonb language plpgsql security definer set search_path='' as $$
declare uid uuid:=auth.uid();begin
 perform marketplace_private.customer_access_assert(uid);
 return coalesce((select jsonb_agg(to_jsonb(h) order by h.created_at desc) from(
  select f.id,f.amount_rial::text,case when r.verification_status='verified' then case when r.execution_status='credited' then case when f.test_mode then 'test_paid' else 'credited' end else 'pending_review' end when b.state='test_paid' then 'test_paid' when b.authority is null and b.expires_at<=now() then 'cancelled' else f.status end as status,
  f.created_at,f.credited_at,case when f.status='credited' then 'deposit:'||f.id::text else null end as reference,f.provider,f.test_mode,r.id review_id,r.status review_decision,(r.status<>'approved') review_required
  from marketplace_private.fiat_payment_intents f left join marketplace_private.bale_payments b on b.payment_id=f.id left join marketplace_private.funding_reviews r on r.source_table='fiat_payment_intents' and r.source_id=f.id where f.user_id=uid order by f.created_at desc limit 100
 )h),'[]'::jsonb);
end$$;

create or replace function public.marketplace_withdrawal_request(p_request uuid,p_amount_rial bigint,p_bank uuid) returns jsonb language plpgsql security definer set search_path='' as $$
declare uid uuid:=auth.uid();w marketplace_private.withdrawal_requests;b marketplace_private.bank_accounts;position marketplace_private.asset_positions;tx uuid;rid uuid;begin
 perform marketplace_private.customer_access_assert(uid,'withdraw');
 if p_request is null or p_bank is null or p_amount_rial is null or p_amount_rial not between 10 and 1000000000000 or p_amount_rial%10<>0 then raise exception 'invalid_withdrawal';end if;
 perform pg_advisory_xact_lock(hashtextextended('withdrawal:'||uid,0));
 select * into w from marketplace_private.withdrawal_requests where user_id=uid and request_id=p_request;
 if w.id is not null then
  if w.amount_rial<>p_amount_rial or w.bank_id<>p_bank then raise exception 'withdrawal_request_conflict';end if;
  select id into strict rid from marketplace_private.funding_reviews where source_table='withdrawal_requests' and source_id=w.id;return marketplace_private.funding_view(rid)-'user_id'-'display_name';
 end if;
 if (select count(*) from marketplace_private.withdrawal_requests where user_id=uid and created_at>clock_timestamp()-interval '1 hour')>=10 then raise exception 'withdrawal_rate_limit';end if;
 select * into b from marketplace_private.bank_accounts where id=p_bank and user_id=uid for share;
 if b.id is null or b.status<>'verified' or not exists(select 1 from marketplace_private.accounts where user_id=uid and verification_status='verified') then raise exception 'withdrawal_verified_bank_required';end if;
 -- Legacy verified labels without ownership evidence are insufficient for a payout destination.
 if to_jsonb(b)->>'ownership_checked_at' is null or not exists(select 1 from (select * from marketplace_private.identity_requests where user_id=uid order by created_at desc,id desc limit 1)i where i.status='verified' and i.national_id=to_jsonb(b)->>'ownership_national_id' and marketplace_private.national_id_valid(i.national_id)) then raise exception 'withdrawal_verified_bank_required';end if;
 perform marketplace_private.ensure_cash_position(uid);
 select * into position from marketplace_private.asset_positions where user_id=uid and asset_symbol='IRR' and project_id is null for update;
 if position.quantity-position.locked_quantity<p_amount_rial then raise exception 'withdrawal_insufficient_balance';end if;
 insert into marketplace_private.transactions(user_id,reference,kind,asset_symbol,amount,status) values(uid,'withdrawal:'||uid||':'||p_request,'withdrawal','IRR',p_amount_rial,'pending') returning id into tx;
 update marketplace_private.asset_positions set locked_quantity=locked_quantity+p_amount_rial,updated_at=clock_timestamp() where id=position.id;
 insert into marketplace_private.withdrawal_requests(user_id,request_id,amount_rial,bank_id,bank_snapshot,position_id,transaction_id) values(uid,p_request,p_amount_rial,b.id,jsonb_build_object('iban',b.iban,'bank_name',b.bank_name),position.id,tx) returning * into w;
 rid:=marketplace_private.funding_add(uid,'withdrawal','withdrawal_requests',w.id,p_amount_rial,'IRR',jsonb_build_object('bank_name',b.bank_name,'iban_last4',right(b.iban,4),'amount_rial',p_amount_rial::text,'payout_sent',false),'not_required','reserved');
 return marketplace_private.funding_view(rid)-'user_id'-'display_name';
end$$;

-- Add safe review titles and exclude financial decisions from generic profile approval.
do $$declare definition text;begin
 select pg_get_functiondef('marketplace_private.customer_activity_reviewable(text,text,text)'::regprocedure) into definition;
 if position('funding_reviews' in definition)=0 then definition:=replace(definition,'select ','select p_entity not in (''funding_reviews'',''funding_review_history'') and ');definition:=replace(definition,'SELECT ','SELECT p_entity NOT IN (''funding_reviews'',''funding_review_history'') AND ');execute definition;end if;
 select pg_get_functiondef('marketplace_private.customer_activity_title(text,text)'::regprocedure) into definition;
 if position('funding_requested' in definition)=0 then definition:=replace(definition,'case lower(p_action)','case lower(p_action) when ''funding_requested'' then ''ثبت درخواست مالی در انتظار بررسی'' when ''funding_approved'' then ''تأیید درخواست مالی'' when ''funding_rejected'' then ''رد درخواست مالی'' when ''funding_problem'' then ''ثبت مشکل درخواست مالی'' when ''funding_proof_verified'' then ''تأیید مدرک پرداخت'' when ''funding_modes_changed'' then ''تغییر روش بررسی مالی''');execute definition;end if;
end$$;
-- Existing unsettled requests also become reviewable; no historical credit is recreated.
do $$declare i record;begin
 for i in select * from marketplace_private.fiat_payment_intents where status in('created','pending') loop perform marketplace_private.funding_add(i.user_id,case when i.provider='bale' then 'bale' else 'toman' end,'fiat_payment_intents',i.id,i.amount_rial,'IRR',jsonb_build_object('provider',i.provider,'test_mode',i.test_mode));end loop;
 for i in select * from marketplace_private.usdt_intents where status='pending' loop perform marketplace_private.funding_add(i.user_id,'usdt','usdt_intents',i.id,i.amount_micro::numeric/1000000,'USDT',jsonb_build_object('network',i.network,'address',i.address,'requested_micro',i.requested_micro::text,'amount_micro',i.amount_micro::text));end loop;
 if to_regclass('marketplace_private.toman_deposit_requests') is not null then
  for i in execute 'select * from marketplace_private.toman_deposit_requests where status=''submitted''' loop perform marketplace_private.funding_add(i.user_id,'toman','toman_deposit_requests',i.id,i.amount_rial,'IRR',jsonb_build_object('method',i.method,'transfer_reference',i.transfer_reference,'bank_name',i.bank_snapshot->>'bank_name','iban_last4',i.bank_snapshot->>'iban_last4','destination_id',i.destination_id));end loop;
 end if;
end$$;
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 'funding_%' 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_funding_reviews(integer),public.marketplace_funding_review(uuid),public.marketplace_funding_history(integer),public.marketplace_funding_review_update(uuid,integer,text,text,boolean,boolean),public.marketplace_withdrawal_request(uuid,bigint,uuid),public.marketplace_funding_bank_proof(uuid,text,bigint,uuid),public.marketplace_usdt_record_proof(uuid,text,integer,bigint,text,timestamptz,bigint,text,text) from public,anon,authenticated,service_role;
grant execute on function public.marketplace_funding_reviews(integer),public.marketplace_funding_review(uuid),public.marketplace_funding_history(integer),public.marketplace_funding_review_update(uuid,integer,text,text,boolean,boolean),public.marketplace_withdrawal_request(uuid,bigint,uuid) to authenticated;
grant execute on function public.marketplace_funding_bank_proof(uuid,text,bigint,uuid),public.marketplace_usdt_record_proof(uuid,text,integer,bigint,text,timestamptz,bigint,text,text) to service_role;
notify pgrst,'reload schema';
commit;