-- Bale wallet invoices. Browser flags and pre-checkout approval are never settlement proof.
begin;
alter table marketplace_private.fiat_payment_intents drop constraint if exists fiat_payment_intents_provider_check;
alter table marketplace_private.fiat_payment_intents add constraint fiat_payment_intents_provider_check check(provider in('zibal','vandar','zarinpal','psp','bale'));

create table if not exists marketplace_private.bale_settings(
 id boolean primary key default true check(id),version bigint not null default 1 check(version>0),enabled boolean not null default false,test_mode boolean not null default true,
 bot_username text not null default '',bot_id text,bot_secret_id uuid,provider_secret_id uuid,webhook_secret_id uuid,
 ready boolean not null default false,verified_at timestamptz,last_error text default 'connection_unverified',
 updated_at timestamptz not null default clock_timestamp(),updated_by uuid references auth.users(id)
);
insert into marketplace_private.bale_settings(id) values(true) on conflict(id) do nothing;
create table if not exists marketplace_private.bale_payments(
 payment_id uuid primary key references marketplace_private.fiat_payment_intents(id) on delete restrict,
 start_token text not null unique default replace(gen_random_uuid()::text,'-',''),
 payload text not null unique default replace(gen_random_uuid()::text,'-','')||replace(gen_random_uuid()::text,'-',''),
 bot_username text not null,bot_id text not null,bot_secret_id uuid not null,provider_secret_id uuid not null,
 state text not null default 'ready' check(state in('ready','sending','sent','checkout','paid','test_paid','failed','rejected')),
 payer text,chat text,message_id text,authority text unique,
 created_at timestamptz not null default clock_timestamp(),expires_at timestamptz not null default clock_timestamp()+interval '15 minutes',
 claimed_at timestamptz,checkout_at timestamptz,settled_at timestamptz,last_inquiry_at timestamptz,
 check((payer is null)=(chat is null)),check(authority is null or payer is not null)
);
alter table marketplace_private.bale_payments add column if not exists last_inquiry_at timestamptz;
do $$declare t text;begin
 foreach t in array array['bale_settings','bale_payments'] 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 bale_history_retained on marketplace_private.%I',t);
  execute format('create trigger bale_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 bale_truncate_retained on marketplace_private.%I',t);
  execute format('create trigger bale_truncate_retained before truncate on marketplace_private.%I for each statement execute function marketplace_private.customer_record_retained()',t);
 end loop;
end$$;

create or replace function public.marketplace_bale_settings() returns jsonb language plpgsql stable security definer set search_path='' as $$
declare s marketplace_private.bale_settings;begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 select * into strict s from marketplace_private.bale_settings where id;
 return jsonb_build_object('version',s.version,'enabled',s.enabled,'test_mode',s.test_mode,'bot_username',s.bot_username,'has_bot_token',s.bot_secret_id is not null,'has_provider_token',s.provider_secret_id is not null,'webhook_configured',s.ready,'ready',s.ready,'verified_at',s.verified_at,'last_error',s.last_error,'updated_at',s.updated_at);
end$$;
create or replace function public.marketplace_bale_settings_update(p_version bigint,p_enabled boolean,p_test_mode boolean,p_bot_username text,p_bot_token text default null,p_provider_token text default null) returns jsonb language plpgsql security definer set search_path='' as $$
declare s marketplace_private.bale_settings;bot_secret uuid;provider_secret uuid;webhook_secret uuid;changed boolean;secret text;begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if p_enabled is null or p_test_mode is null or p_bot_username is null or (p_bot_username<>'' and p_bot_username!~'^[A-Za-z][A-Za-z0-9_]{2,63}$') then raise exception 'invalid_bale_settings';end if;
 foreach secret in array array[p_bot_token,p_provider_token] loop
  if secret is not null and (length(secret) not between 8 and 8192 or secret!~'^[!-~]+$') then raise exception 'invalid_api_key';end if;
 end loop;
 if p_provider_token like 'WALLET-TEST-%' and not p_test_mode then raise exception 'test_credentials_live_mode';end if;
 select * into strict s from marketplace_private.bale_settings where id for update;
 if p_version is distinct from s.version then raise exception 'settings_conflict';end if;
 changed:=s.bot_username is distinct from p_bot_username or p_bot_token is not null or p_provider_token is not null or s.test_mode is distinct from p_test_mode;
 -- A paid invoice may arrive after the launch link expires. Never abandon bound transactions.
 if changed and exists(select 1 from marketplace_private.bale_payments b join marketplace_private.fiat_payment_intents f on f.id=b.payment_id where b.state not in('paid','test_paid','failed','rejected') and (b.authority is not null or b.expires_at>clock_timestamp())) then raise exception 'payments_in_progress';end if;
 bot_secret:=s.bot_secret_id;provider_secret:=s.provider_secret_id;webhook_secret:=s.webhook_secret_id;
 if s.bot_username is distinct from p_bot_username then bot_secret:=null;provider_secret:=null;end if;
 -- Append new Vault secrets; do not overwrite credentials referenced by a historic payment.
 if p_bot_token is not null then select vault.create_secret(p_bot_token,'planck_bale_bot_'||gen_random_uuid()::text,'Bale bot credential') into bot_secret;end if;
 if p_provider_token is not null then select vault.create_secret(p_provider_token,'planck_bale_wallet_'||gen_random_uuid()::text,'Bale wallet payment credential') into provider_secret;end if;
 if webhook_secret is null and (bot_secret is not null or provider_secret is not null) then select vault.create_secret(replace(gen_random_uuid()::text,'-','')||replace(gen_random_uuid()::text,'-',''),'planck_bale_webhook_'||gen_random_uuid()::text,'Bale webhook path secret') into webhook_secret;end if;
 if p_enabled and (p_bot_username='' or bot_secret is null or provider_secret is null) then raise exception 'credentials_missing';end if;
 if not p_test_mode and provider_secret is not null then
  select decrypted_secret into secret from vault.decrypted_secrets where id=provider_secret;
  if secret like 'WALLET-TEST-%' then raise exception 'test_credentials_live_mode';end if;
 end if;
 update marketplace_private.bale_settings set version=version+1,enabled=p_enabled,test_mode=p_test_mode,bot_username=p_bot_username,bot_secret_id=bot_secret,provider_secret_id=provider_secret,webhook_secret_id=webhook_secret,
 ready=case when changed then false else ready end,bot_id=case when changed then null else bot_id end,verified_at=case when changed then null else verified_at end,last_error=case when changed then 'connection_unverified' else last_error end,updated_at=clock_timestamp(),updated_by=auth.uid() where id;
 insert into marketplace_private.audit_log(actor,entity,record_id,operation) values(auth.uid(),'bale_settings','bale','configure');
 return public.marketplace_bale_settings();
end$$;
create or replace function public.marketplace_bale_status() returns jsonb language plpgsql stable security definer set search_path='' as $$
declare s marketplace_private.bale_settings;begin
 if auth.uid() is null then raise exception 'not_authenticated' using errcode='42501';end if;
 select * into strict s from marketplace_private.bale_settings where id;
 return jsonb_build_object('enabled',s.enabled and s.ready and s.bot_secret_id is not null and s.provider_secret_id is not null,'ready',s.ready,'test_mode',s.test_mode,'provider','bale','bot_username',s.bot_username);
end$$;
create or replace function public.marketplace_bale_config(p_payment uuid default null) returns jsonb language plpgsql stable security definer set search_path='' as $$
declare s marketplace_private.bale_settings;b marketplace_private.bale_payments;f marketplace_private.fiat_payment_intents;bot_secret text;provider_secret text;hook_secret text;begin
 select * into strict s from marketplace_private.bale_settings where id;
 if p_payment is not null then
  select * into b from marketplace_private.bale_payments where payment_id=p_payment;
  select * into f from marketplace_private.fiat_payment_intents where id=p_payment;
  if b.payment_id is null then raise exception 'payment_not_found';end if;
 end if;
 select decrypted_secret into bot_secret from vault.decrypted_secrets where id=coalesce(b.bot_secret_id,s.bot_secret_id);
 select decrypted_secret into provider_secret from vault.decrypted_secrets where id=coalesce(b.provider_secret_id,s.provider_secret_id);
 select decrypted_secret into hook_secret from vault.decrypted_secrets where id=s.webhook_secret_id;
 return jsonb_build_object('version',s.version,'enabled',s.enabled,'ready',s.ready,'test_mode',coalesce(f.test_mode,s.test_mode),'bot_username',coalesce(b.bot_username,s.bot_username),'bot_id',coalesce(b.bot_id,s.bot_id),'bot_token',bot_secret,'provider_token',provider_secret,'webhook_secret',hook_secret);
end$$;
create or replace function public.marketplace_bale_connection_record(p_version bigint,p_bot_id text,p_bot_username text,p_ready boolean,p_error_code text) returns jsonb language plpgsql security definer set search_path='' as $$
declare s marketplace_private.bale_settings;begin
 select * into strict s from marketplace_private.bale_settings where id for update;
 if p_version is distinct from s.version then raise exception 'settings_conflict';end if;
 if p_ready is null or (p_ready and (p_bot_id is null or p_bot_id!~'^[1-9][0-9]{0,24}$' or p_bot_username is distinct from s.bot_username or s.bot_secret_id is null or s.provider_secret_id is null or s.webhook_secret_id is null)) then raise exception 'bot_mismatch';end if;
 if not p_ready and (p_error_code is null or p_error_code not in('provider_auth','provider_unavailable','provider_rate_limit','provider_response_invalid','bot_mismatch','webhook_mismatch','webhook_setup_failed','credentials_missing','connection_unverified')) then raise exception 'invalid_error_code';end if;
 update marketplace_private.bale_settings set ready=p_ready,bot_id=case when p_ready then p_bot_id else bot_id end,verified_at=case when p_ready then clock_timestamp() else null end,last_error=case when p_ready then null else p_error_code end where id;
 return jsonb_build_object('ready',p_ready,'version',s.version);
end$$;
-- No secret arguments or caller-chosen merchant are accepted by this initiation API.
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 s marketplace_private.bale_settings;b marketplace_private.bale_payments;f marketplace_private.fiat_payment_intents;created jsonb;begin
 perform marketplace_private.customer_access_assert(p_user);
 if p_request is null or p_amount_rial is null or p_amount_rial not between 10 and 9999999999990 or p_amount_rial%10<>0 then raise exception 'invalid_deposit_amount';end if;
 if not exists(select 1 from marketplace_private.customer_profiles where user_id=p_user) or exists(select 1 from marketplace_private.members where user_id=p_user) then raise exception 'not_authorized' using errcode='42501';end if;
 if not exists(select 1 from marketplace_private.accounts where user_id=p_user and verification_status='verified') then raise exception 'deposit_identity_required';end if;
 -- All code paths lock settings before the user/payment; configuration cannot race an invoice snapshot.
 select * into strict s from marketplace_private.bale_settings where id for share;
 perform pg_advisory_xact_lock(hashtextextended(p_user::text,9048));
 select * into f from marketplace_private.fiat_payment_intents where user_id=p_user and request_id=p_request;
 if f.id is not null then
  select * into b from marketplace_private.bale_payments where payment_id=f.id;
  if f.amount_rial is distinct from p_amount_rial or f.provider<>'bale' or b.payment_id is null then raise exception 'payment_request_conflict';end if;
 else
  if not s.enabled or not s.ready or s.bot_id is null or s.bot_secret_id is null or s.provider_secret_id is null then raise exception 'gateway_unavailable';end if;
  created:=public.marketplace_deposit_create(p_user,p_request,p_amount_rial,'bale',s.bot_id,s.test_mode);
  select * into strict f from marketplace_private.fiat_payment_intents where id=(created->>'id')::uuid;
  insert into marketplace_private.bale_payments(payment_id,bot_username,bot_id,bot_secret_id,provider_secret_id) values(f.id,s.bot_username,s.bot_id,s.bot_secret_id,s.provider_secret_id) returning * into b;
 end if;
 return jsonb_build_object('payment_id',f.id,'id',f.id,'start_token',b.start_token,'bot_username',b.bot_username,'expires_at',b.expires_at,'amount_rial',f.amount_rial::text,'test_mode',f.test_mode,'status',case when b.state='test_paid' then 'test_paid' when b.authority is null and b.expires_at<=now() then 'cancelled' else f.status end);
end$$;

create or replace function public.marketplace_bale_invoice_claim(p_start_token text,p_payer text,p_chat text) returns jsonb language plpgsql security definer set search_path='' as $$
declare b marketplace_private.bale_payments;f marketplace_private.fiat_payment_intents;should_send boolean;begin
 if p_start_token is null or length(p_start_token)<>32 or p_payer is null or p_payer!~'^[1-9][0-9]{0,24}$' or p_chat is distinct from p_payer then raise exception 'invalid_bale_start';end if;
 select * into b from marketplace_private.bale_payments where start_token=p_start_token for update;
 if b.payment_id is null then raise exception 'payment_not_found';end if;
 select * into strict f from marketplace_private.fiat_payment_intents where id=b.payment_id for update;
 if b.expires_at<=clock_timestamp() or b.state not in('ready','sending','sent','checkout') or f.status not in('created','pending') then raise exception 'payment_link_expired';end if;
 perform marketplace_private.customer_access_assert(f.user_id);
 if not exists(select 1 from marketplace_private.bale_settings where id and enabled and ready) then raise exception 'gateway_unavailable';end if;
 if b.payer is not null and (b.payer<>p_payer or b.chat<>p_chat) then raise exception 'payment_payer_conflict';end if;
 should_send:=b.state='ready';
 if should_send then update marketplace_private.bale_payments set payer=p_payer,chat=p_chat,state='sending',claimed_at=clock_timestamp() where payment_id=b.payment_id;end if;
 return jsonb_build_object('should_send',should_send,'payment_id',b.payment_id,'payload',b.payload,'amount_rial',f.amount_rial::text,'title','شارژ کیف پول پلانک','expires_at',b.expires_at,'test_mode',f.test_mode,'message_id',b.message_id,'chat',p_chat,'payer',p_payer);
end$$;
create or replace function public.marketplace_bale_invoice_sent(p_payment uuid,p_message_id text) returns void language plpgsql security definer set search_path='' as $$
declare b marketplace_private.bale_payments;begin
 select * into b from marketplace_private.bale_payments where payment_id=p_payment for update;
 if b.payment_id is null or b.claimed_at is null or p_message_id is null or length(p_message_id) not between 1 and 100 then raise exception 'invalid_invoice_ack';end if;
 if b.message_id is not null and b.message_id<>p_message_id then raise exception 'invoice_message_conflict';end if;
 update marketplace_private.bale_payments set message_id=p_message_id,state=case when state='sending' then 'sent' else state end where payment_id=p_payment;
end$$;
create or replace function public.marketplace_bale_precheckout(p_payload text,p_transaction text,p_payer text,p_amount_rial bigint,p_currency text) returns jsonb language plpgsql security definer set search_path='' as $$
declare b marketplace_private.bale_payments;f marketplace_private.fiat_payment_intents;begin
 select * into b from marketplace_private.bale_payments where payload=p_payload for update;
 if b.payment_id is null then raise exception 'payment_not_found';end if;
 select * into strict f from marketplace_private.fiat_payment_intents where id=b.payment_id for update;
 if p_transaction is null or length(p_transaction) not between 1 and 150 or p_currency is distinct from 'IRR' or p_amount_rial is distinct from f.amount_rial or p_payer is distinct from b.payer or b.payer is null then raise exception 'payment_verification_mismatch';end if;
 if b.authority is not null and b.authority<>p_transaction then raise exception 'payment_authority_conflict';end if;
 if b.state='checkout' and b.authority=p_transaction then return jsonb_build_object('ok',true,'payment_id',b.payment_id,'duplicate',true);end if;
 if b.expires_at<=clock_timestamp() or b.state not in('sending','sent') then raise exception 'payment_link_expired';end if;
 perform marketplace_private.customer_access_assert(f.user_id);
 if not exists(select 1 from marketplace_private.bale_settings where id and enabled and ready) then raise exception 'gateway_unavailable';end if;
 perform public.marketplace_deposit_bind(b.payment_id,p_transaction);
 update marketplace_private.bale_payments set authority=p_transaction,state='checkout',checkout_at=clock_timestamp() where payment_id=b.payment_id;
 return jsonb_build_object('ok',true,'payment_id',b.payment_id,'duplicate',false);
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 b marketplace_private.bale_payments;f marketplace_private.fiat_payment_intents;begin
 select * into b from marketplace_private.bale_payments where payload=p_payload;
 if b.payment_id is null then raise exception 'payment_not_found';end if;
 select * into strict f from marketplace_private.fiat_payment_intents where id=b.payment_id;
 return jsonb_build_object('payment_id',b.payment_id,'payload',b.payload,'authority',b.authority,'payer',b.payer,'amount_rial',f.amount_rial::text,'test_mode',f.test_mode,'status',case when b.state='test_paid' then 'test_paid' when b.authority is null and b.expires_at<=now() then 'cancelled' else f.status end,'state',b.state,'created_at',f.created_at,'expires_at',b.expires_at,'credited_at',f.credited_at);
end$$;
-- Service only. The caller has already authenticated the marketplace customer.
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;begin
 select b.payload into payload_value from marketplace_private.bale_payments b join marketplace_private.fiat_payment_intents f on f.id=b.payment_id where b.payment_id=p_payment and f.user_id=p_user;
 if payload_value is null then raise exception 'payment_not_found';end if;
 return 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);
end$$;
-- Bounded recovery queue for an owner-triggered Edge reconciliation run.
-- This is service-only and read-only; each provider inquiry still reserves its own rate-limited slot.
create or replace function public.marketplace_bale_pending_payments(p_limit integer default 10) returns jsonb language plpgsql stable security definer set search_path='' as $$
begin
 if p_limit is null or p_limit not between 1 and 10 then raise exception 'invalid_payment_limit';end if;
 return coalesce((select jsonb_agg(public.marketplace_bale_payment_lookup(q.payload) order by q.inquiry_order,q.payment_id) from(
  select b.payment_id,b.payload,coalesce(b.last_inquiry_at,b.created_at) as inquiry_order
  from marketplace_private.bale_payments b join marketplace_private.fiat_payment_intents f on f.id=b.payment_id
  where b.state='checkout' and b.authority is not null and f.status='pending' and f.provider='bale'
  order by coalesce(b.last_inquiry_at,b.created_at),b.payment_id limit p_limit
 )q),'[]'::jsonb);
end$$;

-- Reserve a customer-triggered provider inquiry atomically before making the network request.
-- Successful-payment webhooks deliberately bypass this limiter so settlement is never delayed by polling.
create or replace function public.marketplace_bale_reconcile_claim(p_payment uuid) returns boolean language plpgsql security definer set search_path='' as $$
declare b marketplace_private.bale_payments;checked_at timestamptz:=clock_timestamp();begin
 select * into b from marketplace_private.bale_payments where payment_id=p_payment for update;
 if b.payment_id is null then raise exception 'payment_not_found';end if;
 if b.authority is null or b.state<>'checkout' or (b.last_inquiry_at is not null and b.last_inquiry_at>checked_at-interval '10 seconds') then return false;end if;
 update marketplace_private.bale_payments set last_inquiry_at=checked_at where payment_id=p_payment;
 return true;
end$$;

-- Keep existing access checks and append only safe provider/test metadata for history reconnection.
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 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
  from marketplace_private.fiat_payment_intents f left join marketplace_private.bale_payments b on b.payment_id=f.id where f.user_id=uid order by f.created_at desc limit 100
 )h),'[]'::jsonb);
end$$;
revoke all on function public.marketplace_deposit_history() from public,anon,service_role;
grant execute on function public.marketplace_deposit_history() to authenticated;
-- Edge must call inquireTransaction first; these arguments are exclusively provider-verified values.
create or replace function public.marketplace_bale_payment_settle(p_payload text,p_transaction text,p_reference text,p_payer text,p_amount_rial bigint,p_currency text,p_verified_status text) returns jsonb language plpgsql security definer set search_path='' as $$
declare b marketplace_private.bale_payments;f marketplace_private.fiat_payment_intents;receipt jsonb;begin
 select * into b from marketplace_private.bale_payments where payload=p_payload for update;
 if b.payment_id is null then raise exception 'payment_not_found';end if;
 select * into strict f from marketplace_private.fiat_payment_intents where id=b.payment_id for update;
 if p_verified_status is distinct from 'paid' or p_currency is distinct from 'IRR' or p_amount_rial is distinct from f.amount_rial or p_payer is distinct from b.payer or b.payer is null or b.authority is null or p_transaction is distinct from b.authority or p_reference is distinct from 'bale:'||b.authority or f.provider<>'bale' or f.merchant_account<>b.bot_id then raise exception 'payment_verification_mismatch';end if;
 if b.state not in('checkout','paid','test_paid') then raise exception 'payment_not_pending';end if;
 -- No customer-access or expiry gate here: a verified payment must settle even after suspension.
 if f.test_mode then
  if b.state='test_paid' then return jsonb_build_object('ok',true,'duplicate',true,'status','test_paid');end if;
  update marketplace_private.fiat_payment_intents set status='cancelled' where id=f.id;
  update marketplace_private.bale_payments set state='test_paid',settled_at=clock_timestamp() where payment_id=f.id;
  return jsonb_build_object('ok',true,'duplicate',false,'status','test_paid');
 end if;
 receipt:=public.marketplace_deposit_credit(f.id,p_amount_rial,p_transaction,p_reference);
 update marketplace_private.bale_payments set state='paid',settled_at=coalesce(settled_at,clock_timestamp()) where payment_id=f.id;
 return receipt||jsonb_build_object('status','credited');
end$$;
create or replace function public.marketplace_bale_payment_result(p_payload text,p_transaction text,p_payer text,p_amount_rial bigint,p_verified_status text) returns jsonb language plpgsql security definer set search_path='' as $$
declare b marketplace_private.bale_payments;f marketplace_private.fiat_payment_intents;begin
 select * into b from marketplace_private.bale_payments where payload=p_payload for update;
 if b.payment_id is null then raise exception 'payment_not_found';end if;
 select * into strict f from marketplace_private.fiat_payment_intents where id=b.payment_id for update;
 if p_verified_status is null or p_verified_status not in('failed','rejected') or b.authority is null or p_transaction is distinct from b.authority or p_payer is distinct from b.payer or p_amount_rial is distinct from f.amount_rial then raise exception 'payment_verification_mismatch';end if;
 if b.state in('paid','test_paid') then return jsonb_build_object('status',case when b.state='paid' then 'credited' else 'test_paid' end);end if;
 update marketplace_private.fiat_payment_intents set status='failed' where id=f.id and status='pending';
 update marketplace_private.bale_payments set state=p_verified_status,settled_at=coalesce(settled_at,clock_timestamp()) where payment_id=f.id;
 return jsonb_build_object('status','failed');
end$$;

-- State history contains no launch token, invoice payload, payer ID, chat ID or credential.
create or replace function marketplace_private.bale_activity_capture() returns trigger language plpgsql security definer set search_path='' as $$
declare uid uuid;begin
 if tg_op='UPDATE' and old.state=new.state then return new;end if;
 select user_id into strict uid from marketplace_private.fiat_payment_intents where id=new.payment_id;
 insert into marketplace_private.customer_activity(user_id,category,action,entity,entity_id,before_data,after_data,changed_fields,actor_kind,source_key)
 values(uid,'wallet','bale_'||new.state,'bale_payments',new.payment_id::text,case when tg_op='UPDATE' then jsonb_build_object('status',old.state) else '{}'::jsonb end,jsonb_build_object('status',new.state,'provider','bale','expires_at',new.expires_at),array['status'],'system','bale-state:'||new.payment_id||':'||new.state) on conflict(source_key) do nothing;
 return new;
end$$;
drop trigger if exists bale_activity_capture on marketplace_private.bale_payments;
create trigger bale_activity_capture after insert or update on marketplace_private.bale_payments for each row execute function marketplace_private.bale_activity_capture();
revoke all on function marketplace_private.bale_activity_capture() from public,anon,authenticated,service_role;
do $$declare definition text;begin
 select pg_get_functiondef('marketplace_private.customer_activity_safe(text,jsonb)'::regprocedure) into definition;
 definition:=replace(definition,$x$array['amount_rial','provider','test_mode','status','transaction_id','credited_at']$x$,$x$array['amount_rial','provider','test_mode','status','transaction_id','credited_at','provider_reference']$x$);
 execute definition;
end$$;

-- Existing fiat intent/ledger triggers already append safe activity; never make settlement admin-reviewable.
do $$declare definition text;begin
 select pg_get_functiondef('marketplace_private.customer_activity_reviewable(text,text,text)'::regprocedure) into definition;
 if position('fiat_deposit_ledger' in definition)=0 then
  definition:=replace(definition,$x$coalesce(p_actor_kind <> 'admin'::text, false)$x$,$x$coalesce(p_actor_kind <> 'admin'::text, false) and p_entity not in ('fiat_payment_intents','fiat_deposit_ledger','bale_payments')$x$);
  definition:=replace(definition,$x$coalesce(p_actor_kind<>'admin',false)$x$,$x$coalesce(p_actor_kind<>'admin',false) and p_entity not in ('fiat_payment_intents','fiat_deposit_ledger','bale_payments')$x$);
  if position('fiat_deposit_ledger' in definition)=0 then raise exception 'bale_activity_review_contract_missing';end if;
  execute definition;
 end if;
end$$;
-- Keep all existing admin notification kinds when adding the informational gateway receipt.
alter table marketplace_private.admin_notifications drop constraint if exists admin_notifications_event_type_check;
alter table marketplace_private.admin_notifications add constraint admin_notifications_event_type_check check(event_type in('customer_registered','identity_submitted','toman_deposit_submitted','bale_deposit_credited'));
create or replace function marketplace_private.bale_admin_receipt() returns trigger language plpgsql security definer set search_path='' as $$
begin
 if new.provider='bale' and new.status='credited' and old.status<>'credited' then
  insert into marketplace_private.admin_notifications(event_key,event_type,user_id,title,body,action_url)
  values('bale-deposit:'||new.id,'bale_deposit_credited',new.user_id,'{"fa":"واریز بله به کیف پول تأیید شد"}',jsonb_build_object('fa','واریز '||translate(to_char(new.amount_rial/10,'FM999,999,999,999,990'),'0123456789,','۰۱۲۳۴۵۶۷۸۹٬')||' تومان پس از استعلام تراکنش بله ثبت و به کیف پول اضافه شد.'),'/customers/'||new.user_id||'/activity?category=wallet') on conflict(event_key) do nothing;
 end if;
 return new;
end$$;
drop trigger if exists bale_admin_receipt on marketplace_private.fiat_payment_intents;
create trigger bale_admin_receipt after update on marketplace_private.fiat_payment_intents for each row execute function marketplace_private.bale_admin_receipt();
revoke all on function marketplace_private.bale_admin_receipt() from public,anon,authenticated,service_role;
do $$declare f record;begin
 for f in select p.oid::regprocedure signature,p.proname name from pg_proc p join pg_namespace n on n.oid=p.pronamespace where n.nspname='public' and p.proname like 'marketplace_bale_%' loop
  execute format('revoke all on function %s from public,anon,authenticated,service_role',f.signature);
  if f.name in('marketplace_bale_settings','marketplace_bale_settings_update','marketplace_bale_status') then execute format('grant execute on function %s to authenticated',f.signature);
  else execute format('grant execute on function %s to service_role',f.signature);end if;
 end loop;
end$$;
notify pgrst,'reload schema';
commit;