-- USDT withdrawals reserve real balances and enter the shared review queue.
-- Approval is an authorization only: this migration has no signing, broadcast,
-- debit or "paid" operation. Settlement needs independently verified chain evidence.
begin;
create table if not exists marketplace_private.crypto_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,
 network text not null check(network in('ethereum','tron')),
 address text not null,
 amount_micro bigint not null check(amount_micro between 1 and 100000000000),
 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),
 check(marketplace_private.usdt_address_valid(network,address))
);
alter table marketplace_private.crypto_withdrawal_requests enable row level security;
revoke all on marketplace_private.crypto_withdrawal_requests from public,anon,authenticated,service_role;
create index if not exists crypto_withdrawal_owner_recent on marketplace_private.crypto_withdrawal_requests(user_id,created_at desc);
drop trigger if exists funding_history_retained on marketplace_private.crypto_withdrawal_requests;
create trigger funding_history_retained before delete on marketplace_private.crypto_withdrawal_requests for each row execute function marketplace_private.customer_record_retained();
drop trigger if exists funding_truncate_retained on marketplace_private.crypto_withdrawal_requests;
create trigger funding_truncate_retained before truncate on marketplace_private.crypto_withdrawal_requests for each statement execute function marketplace_private.customer_record_retained();

create or replace function public.marketplace_crypto_withdrawal_request(p_request uuid,p_network text,p_address text,p_amount_micro bigint) returns jsonb language plpgsql security definer set search_path='' as $$
declare uid uuid:=auth.uid();w marketplace_private.crypto_withdrawal_requests;position marketplace_private.asset_positions;tx uuid;rid uuid;address_value text;amount_value numeric;
begin
 perform marketplace_private.customer_access_assert(uid,'withdraw');
 -- The explicit check also keeps this operation closed if a later access helper changes.
 if not marketplace_private.wallet_kyc_verified(uid) then raise exception 'wallet_identity_required' using errcode='42501';end if;
 address_value:=case when p_network='ethereum' then lower(trim(p_address)) else trim(p_address) end;
 if p_request is null or p_amount_micro is null or p_amount_micro not between 1 and 100000000000 or not marketplace_private.usdt_address_valid(p_network,address_value) then raise exception 'invalid_withdrawal';end if;
 amount_value:=p_amount_micro::numeric/1000000;
 perform pg_advisory_xact_lock(hashtextextended('crypto-withdrawal:'||uid,0));
 select * into w from marketplace_private.crypto_withdrawal_requests where user_id=uid and request_id=p_request;
 if w.id is not null then
  if w.amount_micro<>p_amount_micro or w.network<>p_network or w.address<>address_value then raise exception 'withdrawal_request_conflict';end if;
  select id into strict rid from marketplace_private.funding_reviews where source_table='crypto_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.crypto_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 position from marketplace_private.asset_positions where user_id=uid and asset_symbol='USDT' and project_id is null for update;
 if position.id is null or position.quantity-position.locked_quantity<amount_value then raise exception 'withdrawal_insufficient_balance';end if;
 insert into marketplace_private.transactions(user_id,reference,kind,asset_symbol,amount,status)
 values(uid,'crypto-withdrawal:'||uid||':'||p_request,'withdrawal','USDT',amount_value,'pending') returning id into tx;
 update marketplace_private.asset_positions set locked_quantity=locked_quantity+amount_value,updated_at=clock_timestamp() where id=position.id;
 insert into marketplace_private.crypto_withdrawal_requests(user_id,request_id,network,address,amount_micro,position_id,transaction_id)
 values(uid,p_request,p_network,address_value,p_amount_micro,position.id,tx) returning * into w;
 rid:=marketplace_private.funding_add(uid,'withdrawal','crypto_withdrawal_requests',w.id,amount_value,'USDT',jsonb_build_object('network',p_network,'address',address_value,'amount_micro',p_amount_micro::text,'asset','USDT','payout_sent',false,'manual_settlement_required',true),'not_required','reserved');
 return marketplace_private.funding_view(rid)-'user_id'-'display_name';
end$$;

-- Preserve established deposit and bank-withdrawal behavior, including later fixes
-- already present when this migration is installed. Backups are private and revoked.
do $$declare definition text;begin
 if to_regprocedure('marketplace_private.funding_approve_before_crypto(uuid,text)') is null then
  select pg_get_functiondef('marketplace_private.funding_approve(uuid,text)'::regprocedure) into definition;
  execute replace(definition,'FUNCTION marketplace_private.funding_approve(','FUNCTION marketplace_private.funding_approve_before_crypto(');
 end if;
 if to_regprocedure('marketplace_private.funding_review_update_before_crypto(uuid,integer,text,text,boolean,boolean)') is null then
  select pg_get_functiondef('public.marketplace_funding_review_update(uuid,integer,text,text,boolean,boolean)'::regprocedure) into definition;
  execute replace(definition,'FUNCTION public.marketplace_funding_review_update(','FUNCTION marketplace_private.funding_review_update_before_crypto(');
 end if;
 if to_regprocedure('marketplace_private.funding_view_before_crypto(uuid)') is null then
  select pg_get_functiondef('marketplace_private.funding_view(uuid)'::regprocedure) into definition;
  execute replace(definition,'FUNCTION marketplace_private.funding_view(','FUNCTION marketplace_private.funding_view_before_crypto(');
 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 result jsonb;r marketplace_private.funding_reviews;access_ready boolean;kyc_ready boolean;reservation_ready boolean;block_reason text;begin
 result:=marketplace_private.funding_view_before_crypto(p_id);
 select * into strict r from marketplace_private.funding_reviews where id=p_id;
 if r.source_table='crypto_withdrawal_requests' then
  access_ready:=exists(select 1 from public.marketplace_customer_access a where a.user_id=r.user_id and a.status='active' and a.withdrawals_enabled);
  kyc_ready:=marketplace_private.wallet_kyc_verified(r.user_id);
  reservation_ready:=exists(select 1 from marketplace_private.crypto_withdrawal_requests w join marketplace_private.asset_positions p on p.id=w.position_id
   where w.id=r.source_id and w.user_id=r.user_id and not w.released and p.user_id=r.user_id and p.asset_symbol='USDT' and p.project_id is null and p.locked_quantity>=w.amount_micro::numeric/1000000 and r.amount=w.amount_micro::numeric/1000000);
  block_reason:=case when r.execution_status<>'reserved' then result->>'approval_block_reason'
   when not access_ready then 'حساب فعال نیست یا امکان برداشت آن غیرفعال شده است.'
   when not kyc_ready then 'احراز هویت کاربر کامل نیست یا اعتبار تأیید موقت پایان یافته است.'
   when not reservation_ready then 'رزرو موجودی واقعی برای این درخواست معتبر نیست.'
   when not r.manual_required then 'برداشت کریپتو باید با تأیید دستی بررسی شود.'
   when r.kind<>'withdrawal' or r.currency<>'USDT' or r.verification_status not in('verified','not_required') then 'اطلاعات درخواست برای تأیید برداشت معتبر نیست.' end;
  result:=result||jsonb_build_object('can_approve',r.execution_status='reserved' and access_ready and kyc_ready and reservation_ready and r.manual_required and r.kind='withdrawal' and r.currency='USDT' and r.verification_status in('verified','not_required'),'approval_block_reason',block_reason);
  result:=jsonb_set(result,'{checks}',(result->'checks')||jsonb_build_array(
   jsonb_build_object('key','crypto_destination','label','آدرس والت مقصد','status','passed','detail',coalesce(r.evidence->>'network','')||' · '||coalesce(r.evidence->>'address','')),
   jsonb_build_object('key','crypto_access','label','احراز هویت و امکان برداشت','status',case when access_ready and kyc_ready then 'passed' else 'failed' end,'detail',case when access_ready and kyc_ready then 'احراز هویت معتبر است و امکان برداشت فعال است.' else coalesce(block_reason,'دسترسی برداشت باید دوباره بررسی شود.') end),
   jsonb_build_object('key','crypto_settlement','label','انتقال روی شبکه','status','pending','detail','این درخواست تنها مبلغ را رزرو می‌کند. انتقال کریپتو هنوز انجام نشده و تأیید مدیر به معنی انتقال نیست.')));
 end if;return result;
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;w marketplace_private.crypto_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.source_table<>'crypto_withdrawal_requests' then return marketplace_private.funding_approve_before_crypto(p_id,p_note);end if;
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if r.execution_status='awaiting_payout' then return marketplace_private.funding_view(r.id);end if;
 if r.execution_status<>'reserved' or r.kind<>'withdrawal' or r.currency<>'USDT' or not r.manual_required then raise exception 'funding_already_processed';end if;
 perform marketplace_private.customer_access_assert(r.user_id,'withdraw');
 if not marketplace_private.wallet_kyc_verified(r.user_id) then raise exception 'wallet_identity_required' using errcode='42501';end if;
 select * into strict w from marketplace_private.crypto_withdrawal_requests where id=r.source_id for update;
 if w.released then raise exception 'withdrawal_released';end if;
 perform 1 from marketplace_private.asset_positions where id=w.position_id and user_id=r.user_id and asset_symbol='USDT' and project_id is null and locked_quantity>=w.amount_micro::numeric/1000000 for update;
 if not found then raise exception 'withdrawal_insufficient_balance';end if;
 insert into marketplace_private.funding_postings(review_id,transaction_id) values(r.id,w.transaction_id);
 update marketplace_private.funding_reviews set status='approved',execution_status='awaiting_payout',transaction_id=w.transaction_id,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,'funding_approved');return marketplace_private.funding_view(r.id);
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.crypto_withdrawal_requests;begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 select * into r from marketplace_private.funding_reviews where id=p_id for update;
 if r.id is null then raise exception 'funding_not_found';end if;
 if r.source_table<>'crypto_withdrawal_requests' then return marketplace_private.funding_review_update_before_crypto(p_id,p_version,p_action,p_note,p_automatic_enabled,p_manual_required);end if;
 if p_version is distinct from r.version then raise exception 'funding_version_conflict';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;
 if p_action='approve' then return marketplace_private.funding_approve(r.id,trim(p_note));end if;
 if p_action='modes' then
  if p_manual_required is distinct from true or p_automatic_enabled is null then raise exception 'invalid_funding_review';end if;
  if r.execution_status<>'reserved' then raise exception 'funding_already_processed';end if;
  update marketplace_private.funding_reviews set automatic_enabled=p_automatic_enabled,manual_required=true,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');
 else
  if r.execution_status='released' or (p_action='problem' and r.execution_status='awaiting_payout') then raise exception 'funding_already_processed';end if;
  if p_action='reject' then
   select * into strict w from marketplace_private.crypto_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_micro::numeric/1000000,updated_at=clock_timestamp() where id=w.position_id and locked_quantity>=w.amount_micro::numeric/1000000;
    if not found then raise exception 'withdrawal_insufficient_balance';end if;
    update marketplace_private.crypto_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 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$$;

revoke all on function marketplace_private.funding_approve_before_crypto(uuid,text),marketplace_private.funding_review_update_before_crypto(uuid,integer,text,text,boolean,boolean),marketplace_private.funding_view_before_crypto(uuid),marketplace_private.funding_approve(uuid,text),marketplace_private.funding_view(uuid) from public,anon,authenticated,service_role;
revoke all on function public.marketplace_crypto_withdrawal_request(uuid,text,text,bigint),public.marketplace_funding_review_update(uuid,integer,text,text,boolean,boolean) from public,anon,authenticated,service_role;
grant execute on function public.marketplace_crypto_withdrawal_request(uuid,text,text,bigint),public.marketplace_funding_review_update(uuid,integer,text,text,boolean,boolean) to authenticated;
comment on table marketplace_private.crypto_withdrawal_requests is 'Immutable destination snapshot and real USDT reservation. Approval never implies on-chain settlement. No client-accessible signing or payout function.';
notify pgrst,'reload schema';
commit;