-- SBUT PAY v6. Choose ONE correct path and run ONCE.
begin;
-- SBUT PAY: run once in Supabase SQL Editor. All money is integer paisa.

create extension if not exists pgcrypto with schema extensions;
create schema if not exists wallet_private;
revoke all on schema wallet_private from public, anon, authenticated;
create table public.sbut_users(id uuid primary key references auth.users(id), username text unique not null check(username ~ '^[a-z0-9_]{3,24}$'), name text not null, phone text unique not null, kyc text not null default 'draft' check(kyc in ('draft','pending','approved','rejected')), role text not null default 'user' check(role in ('user','merchant')), balance bigint not null default 0 check(balance>=0), bonus_paid boolean not null default false, created_at timestamptz default now());
create table wallet_private.pins(user_id uuid primary key references public.sbut_users, hash text not null, failures int not null default 0, locked_until timestamptz);
create table public.sbut_kyc(user_id uuid primary key references public.sbut_users, nid text not null unique, dob date not null, front_path text not null, back_path text not null, selfie_path text not null, submitted_at timestamptz default now());
create table public.sbut_merchant_applications(user_id uuid primary key references public.sbut_users, business text not null, address text not null, status text not null default 'pending' check(status in ('pending','approved','rejected')), created_at timestamptz default now());
create table public.sbut_transactions(id uuid primary key default gen_random_uuid(), request_id uuid not null, sender uuid not null references public.sbut_users, recipient uuid not null references public.sbut_users, kind text not null check(kind in ('send','cashout','cashin','funding','bonus')), amount bigint not null check(amount>0), fee bigint not null check(fee>=0), commission bigint not null default 0 check(commission>=0), status text not null default 'completed', created_at timestamptz not null default now(), unique(sender,request_id));
create table wallet_private.treasury(id int primary key check(id=1), fees bigint not null default 0, promotion bigint not null default 0 check(promotion>=0));
insert into wallet_private.treasury(id) values(1);
alter table public.sbut_users enable row level security;
alter table public.sbut_kyc enable row level security;
alter table public.sbut_transactions enable row level security;
alter table public.sbut_merchant_applications enable row level security;
create policy self_read on public.sbut_users for select to authenticated using(id=(select auth.uid()));
create policy kyc_read on public.sbut_kyc for select to authenticated using(user_id=(select auth.uid()));
create policy application_read on public.sbut_merchant_applications for select to authenticated using(user_id=(select auth.uid()));
create policy ledger_read on public.sbut_transactions for select to authenticated using(sender=(select auth.uid()) or recipient=(select auth.uid()));
revoke all on public.sbut_users,public.sbut_kyc,public.sbut_transactions,public.sbut_merchant_applications from anon,authenticated;
grant select on public.sbut_users,public.sbut_kyc,public.sbut_transactions,public.sbut_merchant_applications to authenticated;
create function public.sbut_register(p_username text,p_name text) returns void language plpgsql security definer set search_path='' as $$
declare u uuid:=auth.uid(); ph text;
begin
 if u is null then raise exception 'AUTH_REQUIRED'; end if;
 select phone into ph from auth.users where id=u and phone_confirmed_at is not null;
 if ph is null then raise exception 'OTP_REQUIRED'; end if;
 insert into public.sbut_users(id,username,name,phone) values(u,lower(p_username),p_name,ph) on conflict(id) do nothing;
end $$;
create function public.sbut_submit_kyc(p_nid text,p_dob date,p_front text,p_back text,p_selfie text,p_pin text) returns void language plpgsql security definer set search_path='' as $$
declare u uuid:=auth.uid();
begin
 if u is null or coalesce(p_pin,'') !~ '^[0-9]{6}$' then raise exception 'INVALID_PIN'; end if;
 if not exists(select 1 from public.sbut_users where id=u and kyc in ('draft','rejected')) then raise exception 'KYC_STATE'; end if;
 if p_dob is null or p_nid is null or p_dob>current_date-interval '18 years' or p_dob<date '1900-01-01' or p_nid !~ '^([0-9]{10}|[0-9]{13}|[0-9]{17})$' then raise exception 'INVALID_IDENTITY'; end if;
 if not exists(select 1 from storage.objects where bucket_id='sbut-kyc' and name=p_front and owner_id=u::text) or not exists(select 1 from storage.objects where bucket_id='sbut-kyc' and name=p_back and owner_id=u::text) or not exists(select 1 from storage.objects where bucket_id='sbut-kyc' and name=p_selfie and owner_id=u::text) then raise exception 'UPLOAD_REQUIRED'; end if;
 insert into public.sbut_kyc values(u,p_nid,p_dob,p_front,p_back,p_selfie,now()) on conflict(user_id) do update set nid=excluded.nid,dob=excluded.dob,front_path=excluded.front_path,back_path=excluded.back_path,selfie_path=excluded.selfie_path,submitted_at=now();
 insert into wallet_private.pins(user_id,hash) values(u,extensions.crypt(p_pin,extensions.gen_salt('bf',12))) on conflict(user_id) do update set hash=excluded.hash,failures=0,locked_until=null;
 update public.sbut_users set kyc='pending' where id=u;
end $$;
create function public.sbut_transfer(p_recipient text,p_amount bigint,p_pin text,p_kind text,p_request uuid) returns jsonb language plpgsql security definer set search_path='' as $$
declare u uuid:=auth.uid(); v uuid; f bigint; c bigint:=0; b bigint; s text; r text; pinrow wallet_private.pins%rowtype; old public.sbut_transactions%rowtype;
begin
 if u is null then return jsonb_build_object('error','AUTH_REQUIRED'); end if;
 select * into pinrow from wallet_private.pins where user_id=u for update;
 if not found or pinrow.locked_until>now() then return jsonb_build_object('error','PIN_LOCKED'); end if;
 if coalesce(p_pin,'') !~ '^[0-9]{6}$' or extensions.crypt(p_pin,pinrow.hash) is distinct from pinrow.hash then
 update wallet_private.pins set failures=failures+1,locked_until=case when failures>=4 then now()+interval '15 minutes' else locked_until end where user_id=u;
 return jsonb_build_object('error','INVALID_PIN'); end if;
 update wallet_private.pins set failures=0,locked_until=null where user_id=u;
 select id into v from public.sbut_users where case
 when p_recipient ~ '^\+?[0-9]+$' then phone=case when regexp_replace(p_recipient,'[^0-9]','','g') ~ '^01[3-9][0-9]{8}$' then '88'||regexp_replace(p_recipient,'[^0-9]','','g') else regexp_replace(p_recipient,'[^0-9]','','g') end
 else username=lower(trim(leading '@' from p_recipient)) end;
 select * into old from public.sbut_transactions where sender=u and request_id=p_request;
 if found then
 if old.amount<>p_amount or old.kind<>p_kind then return jsonb_build_object('error','REQUEST_CONFLICT'); end if;
 if old.recipient is distinct from v then return jsonb_build_object('error','REQUEST_CONFLICT'); end if;
 return to_jsonb(old); end if;
 if p_amount is null or p_kind is null or p_request is null or p_amount<1000 or p_amount>1000000000000 or p_kind not in ('send','cashout','cashin') then return jsonb_build_object('error','INVALID_AMOUNT'); end if;

 if v is null or v=u then return jsonb_build_object('error','INVALID_RECIPIENT'); end if;
 perform id from public.sbut_users where id in(u,v) order by id for update;
 select balance,kyc,role into b,s,r from public.sbut_users where id=u;
 if s<>'approved' or not exists(select 1 from public.sbut_users where id=v and kyc='approved') then return jsonb_build_object('error','KYC_REQUIRED'); end if;
 f:=case when p_kind='send' then case when p_amount<10000 then 0 when p_amount<50000 then ceil(p_amount*0.015)::bigint else ceil(p_amount*0.02)::bigint end when p_kind='cashout' then case when p_amount<100000 then 600 else ceil(p_amount*0.012)::bigint end else 0 end;
 if p_kind='cashout' and not exists(select 1 from public.sbut_users where id=v and role='merchant') then return jsonb_build_object('error','MERCHANT_REQUIRED'); end if;
 if p_kind='cashin' and r<>'merchant' then return jsonb_build_object('error','MERCHANT_REQUIRED'); end if;
 if p_kind in ('cashout','cashin') then c:=case when p_amount<100000 then 400 else floor(p_amount*0.008)::bigint end; end if;
 if b<p_amount+f then return jsonb_build_object('error','INSUFFICIENT_BALANCE'); end if;
 perform id from wallet_private.treasury where id=1 for update;
 if (select fees+f-c from wallet_private.treasury where id=1)<0 then return jsonb_build_object('error','COMMISSION_POOL_EMPTY'); end if;
 update public.sbut_users set balance=balance-p_amount-f where id=u;
 update public.sbut_users set balance=balance+p_amount where id=v;
 if c>0 then update public.sbut_users set balance=balance+c where id=case when p_kind='cashin' then u else v end; end if;
 update wallet_private.treasury set fees=fees+f-c where id=1;
 insert into public.sbut_transactions(request_id,sender,recipient,kind,amount,fee,commission) values(p_request,u,v,p_kind,p_amount,f,c) returning * into old;
 return to_jsonb(old);
end $$;
create function public.sbut_apply_merchant(p_business text,p_address text) returns void language plpgsql security definer set search_path='' as $$
begin
 if auth.uid() is null or not exists(select 1 from public.sbut_users where id=auth.uid() and kyc='approved') then raise exception 'KYC_REQUIRED'; end if;
 insert into public.sbut_merchant_applications(user_id,business,address) values(auth.uid(),p_business,p_address);
end $$;
-- Admin-only: invoke from trusted SQL Editor after document review and deposit reconciliation.
create function wallet_private.approve_kyc(p_user uuid) returns void language plpgsql set search_path='' as $$
begin
 perform id from public.sbut_users where id=p_user and kyc='pending' for update;
 if not found then raise exception 'NOT_PENDING'; end if;
 update public.sbut_users set kyc='approved' where id=p_user;
 if not(select bonus_paid from public.sbut_users where id=p_user) then
 update wallet_private.treasury set promotion=promotion-10000 where id=1 and promotion>=10000;
 if found then update public.sbut_users set balance=balance+10000,bonus_paid=true where id=p_user; insert into public.sbut_transactions(request_id,sender,recipient,kind,amount,fee) values(gen_random_uuid(),p_user,p_user,'bonus',10000,0); end if;
 end if;
end $$;
create function wallet_private.fund_merchant(p_merchant uuid,p_gross bigint,p_reference uuid) returns void language plpgsql set search_path='' as $$
declare f bigint;
begin
 if p_gross<100000 or p_gross>99000000 then raise exception 'FUNDING_RANGE'; end if;
 perform id from public.sbut_users where id=p_merchant and role='merchant' and kyc='approved' for update;
 if not found then raise exception 'MERCHANT_REQUIRED'; end if;
 if exists(select 1 from public.sbut_transactions where sender=p_merchant and request_id=p_reference) then return; end if;
 f:=ceil(p_gross*case when p_gross<5000000 then .05 when p_gross<10000000 then .08 else .12 end)::bigint;
 update public.sbut_users set balance=balance+p_gross-f where id=p_merchant;
 update wallet_private.treasury set fees=fees+f where id=1;
 insert into public.sbut_transactions(request_id,sender,recipient,kind,amount,fee) values(p_reference,p_merchant,p_merchant,'funding',p_gross-f,f);
end $$;
revoke all on all functions in schema wallet_private from public,anon,authenticated;
revoke all on function public.sbut_register(text,text),public.sbut_submit_kyc(text,date,text,text,text,text),public.sbut_transfer(text,bigint,text,text,uuid),public.sbut_apply_merchant(text,text) from public,anon;
grant execute on function public.sbut_register(text,text),public.sbut_submit_kyc(text,date,text,text,text,text),public.sbut_transfer(text,bigint,text,text,uuid),public.sbut_apply_merchant(text,text) to authenticated;
insert into storage.buckets(id,name,public,file_size_limit,allowed_mime_types) values('sbut-kyc','sbut-kyc',false,5242880,array['image/jpeg','image/png','image/webp']);
create policy kyc_upload on storage.objects for insert to authenticated with check(bucket_id='sbut-kyc' and (storage.foldername(name))[1]=(select auth.uid())::text);
create policy kyc_own_read on storage.objects for select to authenticated using(bucket_id='sbut-kyc' and owner_id=(select auth.uid())::text);
alter publication supabase_realtime add table public.sbut_users,public.sbut_transactions;
create function public.sbut_status() returns jsonb language sql stable set search_path='' as $$ select jsonb_build_object('schema_version',2,'wallet_ready',true,'money_unit','paisa') $$;
revoke all on function public.sbut_status() from public;
grant execute on function public.sbut_status() to anon,authenticated;
create index sbut_tx_sender_created on public.sbut_transactions(sender,created_at desc);
create index sbut_tx_recipient_created on public.sbut_transactions(recipient,created_at desc);

-- SBUT PAY 003: run after 001 (or 001+002). No passwords or admin backdoor in this file.

alter table public.sbut_users add column if not exists frozen boolean not null default false;
alter table public.sbut_merchant_applications add column if not exists email text;
alter table public.sbut_merchant_applications add column if not exists reviewed_at timestamptz;
alter table public.sbut_transactions drop constraint if exists sbut_transactions_kind_check;
alter table public.sbut_transactions add constraint sbut_transactions_kind_check check(kind in ('send','cashout','cashin','funding','bonus','admin_credit','admin_debit'));
create table if not exists wallet_private.admins(user_id uuid primary key references auth.users,login_id text unique not null,kind text not null check(kind in ('founder','admin')));
create table if not exists wallet_private.sessions(id uuid primary key,user_id uuid not null references auth.users,revoked boolean not null default false,expires_at timestamptz not null default now()+interval '30 minutes');
create table if not exists public.sbut_consents(user_id uuid primary key references auth.users,version text not null,accepted_at timestamptz not null default now());
create table if not exists public.sbut_requests(id uuid primary key default gen_random_uuid(),requester uuid not null references public.sbut_users,payer uuid not null references public.sbut_users,amount bigint not null check(amount>=1000 and amount<=1000000000000),note text not null default '' check(length(note)<=200),status text not null default 'pending' check(status in ('pending','paid','declined','cancelled')),transaction_id uuid references public.sbut_transactions,created_at timestamptz not null default now(),check(requester<>payer));
create table if not exists wallet_private.confirmations(id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users,session_id uuid not null,recipient text not null,amount bigint not null,kind text not null,request_id uuid not null,money_request uuid references public.sbut_requests,ready_at timestamptz not null default now()+interval '5 seconds',expires_at timestamptz not null default now()+interval '2 minutes',transaction_id uuid references public.sbut_transactions,unique(user_id,request_id));
create table if not exists public.sbut_banners(id uuid primary key default gen_random_uuid(),title_en text not null,title_bn text not null,body_en text not null default '',body_bn text not null default '',active boolean not null default false,starts_at timestamptz not null default now(),ends_at timestamptz,created_at timestamptz not null default now());
create table if not exists public.sbut_admin_audit(id uuid primary key default gen_random_uuid(),actor uuid not null references auth.users,action text not null,target uuid,reference text,amount bigint,reason text not null,created_at timestamptz not null default now());
create table if not exists wallet_private.settlements(reference text primary key,target uuid not null references public.sbut_users,amount bigint not null,kind text not null,created_at timestamptz not null default now());
create or replace function wallet_private.require_session() returns void language plpgsql security definer set search_path='' as $$
declare sid uuid:=nullif(auth.jwt()->>'session_id','')::uuid; u uuid:=auth.uid();
begin
 if u is null or sid is null or not exists(select 1 from auth.sessions where id=sid and user_id=u) or not exists(select 1 from wallet_private.sessions where id=sid and user_id=u and not revoked and expires_at>now()) then raise exception 'SESSION_LOCKED'; end if;
 if exists(select 1 from public.sbut_users where id=u and frozen) then raise exception 'ACCOUNT_FROZEN'; end if;
end $$;
create or replace function public.sbut_open_session() returns void language plpgsql security definer set search_path='' as $$
declare sid uuid:=nullif(auth.jwt()->>'session_id','')::uuid; u uuid:=auth.uid();
begin
 if u is null or sid is null or not exists(select 1 from auth.sessions where id=sid and user_id=u) then raise exception 'AUTH_REQUIRED'; end if;
 if exists(select 1 from public.sbut_users where id=u and frozen) then raise exception 'ACCOUNT_FROZEN';end if;
 if exists(select 1 from wallet_private.sessions where id=sid and revoked) then raise exception 'SESSION_LOCKED'; end if;
 insert into wallet_private.sessions(id,user_id) values(sid,u) on conflict(id) do update set expires_at=now()+interval '30 minutes' where not wallet_private.sessions.revoked;
end $$;
create or replace function public.sbut_close_session() returns void language plpgsql security definer set search_path='' as $$
begin
 update wallet_private.sessions set revoked=true where id=nullif(auth.jwt()->>'session_id','')::uuid and user_id=auth.uid();
end $$;
create or replace function public.sbut_session_active() returns boolean language sql stable security definer set search_path='' as $$
 select auth.uid() is not null and exists(select 1 from wallet_private.sessions s join auth.sessions a on a.id=s.id and a.user_id=s.user_id where s.id=nullif(auth.jwt()->>'session_id','')::uuid and s.user_id=auth.uid() and not s.revoked and s.expires_at>now()) and not exists(select 1 from public.sbut_users where id=auth.uid() and frozen)
$$;
revoke all on function public.sbut_session_active() from public;
grant execute on function public.sbut_session_active() to anon,authenticated;
create or replace function public.sbut_is_admin() returns boolean language sql stable security definer set search_path='' as $$ select public.sbut_session_active() and exists(select 1 from wallet_private.admins where user_id=auth.uid()) $$;
create or replace function wallet_private.require_admin(p_founder boolean default false) returns void language plpgsql security definer set search_path='' as $$
begin
 perform wallet_private.require_session();
 if not exists(select 1 from wallet_private.admins where user_id=auth.uid() and (not p_founder or kind='founder')) then raise exception 'ADMIN_REQUIRED'; end if;
end $$;
create or replace function public.sbut_admin_identity(p_login_id text) returns jsonb language plpgsql security definer set search_path='' as $$
declare r wallet_private.admins%rowtype;
begin
 perform wallet_private.require_admin();select * into r from wallet_private.admins where user_id=auth.uid() and login_id=p_login_id;
 if not found then raise exception 'ADMIN_REQUIRED'; end if;
 return jsonb_build_object('kind',r.kind,'login_id',r.login_id);
end $$;
create or replace function public.sbut_accept_terms(p_version text) returns void language plpgsql security definer set search_path='' as $$
begin
 if auth.uid() is null or p_version<>'2026-10-26-v1' then raise exception 'CONSENT_REQUIRED'; end if;
 insert into public.sbut_consents(user_id,version) values(auth.uid(),p_version) on conflict(user_id) do update set version=excluded.version,accepted_at=now();
end $$;
create or replace function wallet_private.check_pin(p_pin text) returns text language plpgsql security definer set search_path='' as $$
declare r wallet_private.pins%rowtype;
begin
 select * into r from wallet_private.pins where user_id=auth.uid() for update;
 if not found or r.locked_until>now() then return 'PIN_LOCKED'; end if;
 if coalesce(p_pin,'') !~ '^[0-9]{6}$' or extensions.crypt(p_pin,r.hash) is distinct from r.hash then
 update wallet_private.pins set failures=failures+1,locked_until=case when failures>=4 then now()+interval '15 minutes' else locked_until end where user_id=auth.uid();return 'INVALID_PIN';end if;
 update wallet_private.pins set failures=0,locked_until=null where user_id=auth.uid();return null;
end $$;
create or replace function public.sbut_prepare(p_recipient text,p_amount bigint,p_pin text,p_kind text,p_request uuid,p_money_request uuid default null) returns jsonb language plpgsql security definer set search_path='' as $$
declare err text; c wallet_private.confirmations%rowtype; r public.sbut_requests%rowtype; recipient text:=p_recipient; amount bigint:=p_amount; kind text:=p_kind;
begin
 perform wallet_private.require_session();err:=wallet_private.check_pin(p_pin);if err is not null then return jsonb_build_object('error',err);end if;
 if p_money_request is not null then
 select * into r from public.sbut_requests where id=p_money_request and payer=auth.uid();
 if not found then return jsonb_build_object('error','REQUEST_UNAVAILABLE');end if;
 if r.status='paid' then return jsonb_build_object('completed',(select to_jsonb(t) from public.sbut_transactions t where id=r.transaction_id));end if;
 if r.status<>'pending' then return jsonb_build_object('error','REQUEST_UNAVAILABLE');end if;
 select '@'||username into recipient from public.sbut_users where id=r.requester;amount:=r.amount;kind:='send';
 end if;
 if amount is null or amount<1000 or amount>1000000000000 or kind is null or kind not in ('send','cashout','cashin') or p_request is null or coalesce(recipient,'')='' then return jsonb_build_object('error','INVALID_AMOUNT');end if;
 select * into c from wallet_private.confirmations where user_id=auth.uid() and request_id=p_request for update;
 if found then
 if c.recipient<>recipient or c.amount<>amount or c.kind<>kind or c.money_request is distinct from p_money_request then return jsonb_build_object('error','REQUEST_CONFLICT');end if;
 if c.transaction_id is not null then return jsonb_build_object('completed',(select to_jsonb(t) from public.sbut_transactions t where id=c.transaction_id));end if;

 -- Re-authorize same immutable intent after a reconnect, without generating a new debit reference.
 update wallet_private.confirmations set session_id=nullif(auth.jwt()->>'session_id','')::uuid,ready_at=now()+interval '5 seconds',expires_at=now()+interval '2 minutes' where id=c.id returning * into c;
 else
 insert into wallet_private.confirmations(user_id,session_id,recipient,amount,kind,request_id,money_request) values(auth.uid(),nullif(auth.jwt()->>'session_id','')::uuid,recipient,amount,kind,p_request,p_money_request) returning * into c;
 end if;
 return jsonb_build_object('challenge',c.id,'ready_at',c.ready_at,'expires_at',c.expires_at,'amount',c.amount,'recipient',c.recipient,'kind',c.kind);
end $$;
create or replace function public.sbut_commit(p_challenge uuid,p_pin text) returns jsonb language plpgsql security definer set search_path='' as $$
declare c wallet_private.confirmations%rowtype; r public.sbut_requests%rowtype; result jsonb;
begin
 perform wallet_private.require_session();
 perform user_id from wallet_private.pins where user_id=auth.uid() for update;
 select * into c from wallet_private.confirmations where id=p_challenge and user_id=auth.uid() and session_id=nullif(auth.jwt()->>'session_id','')::uuid for update;
 if not found then return jsonb_build_object('error','CONFIRMATION_REQUIRED');end if;
 if c.transaction_id is not null then return (select to_jsonb(t) from public.sbut_transactions t where id=c.transaction_id);end if;
 if now()<c.ready_at or now()>c.expires_at then return jsonb_build_object('error','HOLD_REQUIRED');end if;
 if c.money_request is not null then
 select * into r from public.sbut_requests where id=c.money_request for update;
 if r.status<>'pending' or r.payer<>auth.uid() then return jsonb_build_object('error','REQUEST_UNAVAILABLE');end if;
 end if;
 result:=public.sbut_transfer(c.recipient,c.amount,p_pin,c.kind,c.request_id);
 if result ? 'error' then return result;end if;
 update wallet_private.confirmations set transaction_id=(result->>'id')::uuid where id=c.id;
 if c.money_request is not null then update public.sbut_requests set status='paid',transaction_id=(result->>'id')::uuid where id=c.money_request;end if;
 return result;
end $$;
create or replace function public.sbut_create_request(p_payer text,p_amount bigint,p_note text) returns uuid language plpgsql security definer set search_path='' as $$
declare v uuid; rid uuid;
begin
 perform wallet_private.require_session();
 if not exists(select 1 from public.sbut_users where id=auth.uid() and kyc='approved') then raise exception 'KYC_REQUIRED';end if;
 if (select count(*) from public.sbut_requests where requester=auth.uid() and created_at>now()-interval '1 day')>=20 then raise exception 'REQUEST_LIMIT';end if;
 select id into v from public.sbut_users where (case when p_payer ~ '^\+?[0-9]+$' then phone=regexp_replace(p_payer,'[^0-9]','','g') else username=lower(trim(leading '@' from p_payer)) end) and kyc='approved' and not frozen;
 if v is null or v=auth.uid() then raise exception 'INVALID_RECIPIENT';end if;
 insert into public.sbut_requests(requester,payer,amount,note) values(auth.uid(),v,p_amount,left(coalesce(p_note,''),200)) returning id into rid;return rid;
end $$;
create or replace function public.sbut_request_action(p_id uuid,p_action text) returns void language plpgsql security definer set search_path='' as $$
begin
 perform wallet_private.require_session();
 update public.sbut_requests set status=p_action where id=p_id and status='pending' and ((p_action='declined' and payer=auth.uid()) or (p_action='cancelled' and requester=auth.uid()));
 if not found then raise exception 'REQUEST_UNAVAILABLE';end if;
end $$;
create or replace function public.sbut_apply_merchant_v3(p_business text,p_address text,p_email text) returns void language plpgsql security definer set search_path='' as $$
begin
 perform wallet_private.require_session();
 if not exists(select 1 from public.sbut_users where id=auth.uid() and kyc='approved') then raise exception 'KYC_REQUIRED';end if;
 if p_business is null or p_address is null or p_email is null or length(trim(p_business))<2 or length(trim(p_address))<5 or p_email !~ '^[^@[:space:]]+@[^@[:space:]]+\.[^@[:space:]]+$' then raise exception 'INVALID_APPLICATION';end if;
 insert into public.sbut_merchant_applications(user_id,business,address,email) values(auth.uid(),p_business,p_address,p_email) on conflict(user_id) do update set business=excluded.business,address=excluded.address,email=excluded.email,status='pending',created_at=now(),reviewed_at=null where sbut_merchant_applications.status='rejected';
end $$;
create or replace function public.sbut_admin_adjust(p_user uuid,p_amount bigint,p_action text,p_reference text,p_reason text) returns jsonb language plpgsql security definer set search_path='' as $$
declare result public.sbut_transactions%rowtype; delta bigint;
begin
 perform wallet_private.require_admin(true);
 if p_action is null or p_reference is null or p_reason is null or p_amount is null or p_amount<=0 or p_amount>1000000000000 or p_action not in ('credit','debit','merchant_funding') or length(trim(p_reference))<8 or length(trim(p_reason))<5 then raise exception 'INVALID_ADJUSTMENT';end if;
 perform id from public.sbut_users where id=p_user for update;if not found then raise exception 'USER_NOT_FOUND';end if;
 if exists(select 1 from wallet_private.settlements where reference=p_reference) then raise exception 'DUPLICATE_REFERENCE';end if;
 insert into wallet_private.settlements values(p_reference,p_user,p_amount,p_action,now());
 if p_action='merchant_funding' then
 perform wallet_private.fund_merchant(p_user,p_amount,gen_random_uuid());
 else
 delta:=case when p_action='credit' then p_amount else -p_amount end;
 update public.sbut_users set balance=balance+delta where id=p_user and balance+delta>=0;if not found then raise exception 'INSUFFICIENT_BALANCE';end if;
 insert into public.sbut_transactions(request_id,sender,recipient,kind,amount,fee) values(gen_random_uuid(),p_user,p_user,case when delta>0 then 'admin_credit' else 'admin_debit' end,p_amount,0) returning * into result;
 end if;
 insert into public.sbut_admin_audit(actor,action,target,reference,amount,reason) values(auth.uid(),p_action,p_user,p_reference,p_amount,p_reason);
 return jsonb_build_object('ok',true,'reference',p_reference);
end $$;
create or replace function public.sbut_admin_review(p_user uuid,p_action text,p_reason text) returns void language plpgsql security definer set search_path='' as $$
begin
 perform wallet_private.require_admin();if p_reason is null or length(trim(p_reason))<5 then raise exception 'REASON_REQUIRED';end if;
 if p_action='approve_kyc' then
 if not exists(select 1 from public.sbut_consents where user_id=p_user and version='2026-10-26-v1') then raise exception 'CONSENT_REQUIRED';end if;
 perform wallet_private.approve_kyc(p_user);
 elsif p_action='reject_kyc' then update public.sbut_users set kyc='rejected' where id=p_user and kyc='pending';
 elsif p_action='approve_merchant' then
 if not exists(select 1 from public.sbut_users where id=p_user and kyc='approved') then raise exception 'KYC_REQUIRED';end if;
 update public.sbut_merchant_applications set status='approved',reviewed_at=now() where user_id=p_user and status='pending';if not found then raise exception 'NO_APPLICATION';end if;
 update public.sbut_users set role='merchant' where id=p_user;
 elsif p_action='reject_merchant' then update public.sbut_merchant_applications set status='rejected',reviewed_at=now() where user_id=p_user and status='pending';
 elsif p_action='freeze' then update public.sbut_users set frozen=true where id=p_user;update wallet_private.sessions set revoked=true where user_id=p_user;
 elsif p_action='unfreeze' then update public.sbut_users set frozen=false where id=p_user;
 elsif p_action='remove_merchant' then update public.sbut_users set role='user' where id=p_user;
 else raise exception 'INVALID_ACTION';end if;
 insert into public.sbut_admin_audit(actor,action,target,reason) values(auth.uid(),p_action,p_user,p_reason);
end $$;
create or replace function public.sbut_admin_banner(p_id uuid,p_title_en text,p_title_bn text,p_body_en text,p_body_bn text,p_active boolean) returns uuid language plpgsql security definer set search_path='' as $$
declare bid uuid:=coalesce(p_id,gen_random_uuid());
begin
 perform wallet_private.require_admin();if length(p_title_en)>120 or length(p_title_bn)>120 then raise exception 'INVALID_BANNER';end if;
 insert into public.sbut_banners(id,title_en,title_bn,body_en,body_bn,active) values(bid,p_title_en,p_title_bn,p_body_en,p_body_bn,p_active) on conflict(id) do update set title_en=excluded.title_en,title_bn=excluded.title_bn,body_en=excluded.body_en,body_bn=excluded.body_bn,active=excluded.active;
 insert into public.sbut_admin_audit(actor,action,target,reason) values(auth.uid(),'banner_update',bid,'Banner status/content changed');return bid;
end $$;
create or replace function public.sbut_submit_kyc(p_nid text,p_dob date,p_front text,p_back text,p_selfie text,p_pin text) returns void language plpgsql security definer set search_path='' as $$
declare u uuid:=auth.uid();
begin
 if not exists(select 1 from public.sbut_consents where user_id=auth.uid() and version='2026-10-26-v1') then raise exception 'CONSENT_REQUIRED';end if;
 if u is null or coalesce(p_pin,'') !~ '^[0-9]{6}$' then raise exception 'INVALID_PIN'; end if;
 if not exists(select 1 from public.sbut_users where id=u and kyc in ('draft','rejected')) then raise exception 'KYC_STATE'; end if;
 if p_dob is null or p_nid is null or p_dob>current_date-interval '18 years' or p_dob<date '1900-01-01' or p_nid !~ '^([0-9]{10}|[0-9]{13}|[0-9]{17})$' then raise exception 'INVALID_IDENTITY'; end if;
 if not exists(select 1 from storage.objects where bucket_id='sbut-kyc' and name=p_front and owner_id=u::text) or not exists(select 1 from storage.objects where bucket_id='sbut-kyc' and name=p_back and owner_id=u::text) or not exists(select 1 from storage.objects where bucket_id='sbut-kyc' and name=p_selfie and owner_id=u::text) then raise exception 'UPLOAD_REQUIRED'; end if;
 insert into public.sbut_kyc values(u,p_nid,p_dob,p_front,p_back,p_selfie,now()) on conflict(user_id) do update set nid=excluded.nid,dob=excluded.dob,front_path=excluded.front_path,back_path=excluded.back_path,selfie_path=excluded.selfie_path,submitted_at=now();
 insert into wallet_private.pins(user_id,hash) values(u,extensions.crypt(p_pin,extensions.gen_salt('bf',12))) on conflict(user_id) do update set hash=excluded.hash,failures=0,locked_until=null;
 update public.sbut_users set kyc='pending' where id=u;
end $$;
create or replace function public.sbut_transfer(p_recipient text,p_amount bigint,p_pin text,p_kind text,p_request uuid) returns jsonb language plpgsql security definer set search_path='' as $$
declare u uuid:=auth.uid(); v uuid; f bigint; c bigint:=0; b bigint; s text; r text; pinrow wallet_private.pins%rowtype; old public.sbut_transactions%rowtype;
begin
 perform wallet_private.require_session();
 if u is null then return jsonb_build_object('error','AUTH_REQUIRED'); end if;
 select * into pinrow from wallet_private.pins where user_id=u for update;
 if not found or pinrow.locked_until>now() then return jsonb_build_object('error','PIN_LOCKED'); end if;
 if coalesce(p_pin,'') !~ '^[0-9]{6}$' or extensions.crypt(p_pin,pinrow.hash) is distinct from pinrow.hash then
 update wallet_private.pins set failures=failures+1,locked_until=case when failures>=4 then now()+interval '15 minutes' else locked_until end where user_id=u;
 return jsonb_build_object('error','INVALID_PIN'); end if;
 update wallet_private.pins set failures=0,locked_until=null where user_id=u;
 select id into v from public.sbut_users where case
 when p_recipient ~ '^\+?[0-9]+$' then phone=case when regexp_replace(p_recipient,'[^0-9]','','g') ~ '^01[3-9][0-9]{8}$' then '88'||regexp_replace(p_recipient,'[^0-9]','','g') else regexp_replace(p_recipient,'[^0-9]','','g') end
 else username=lower(trim(leading '@' from p_recipient)) end;
 select * into old from public.sbut_transactions where sender=u and request_id=p_request;
 if found then
 if old.amount<>p_amount or old.kind<>p_kind then return jsonb_build_object('error','REQUEST_CONFLICT'); end if;
 if old.recipient is distinct from v then return jsonb_build_object('error','REQUEST_CONFLICT'); end if;
 return to_jsonb(old); end if;
 if p_amount is null or p_kind is null or p_request is null or p_amount<1000 or p_amount>1000000000000 or p_kind not in ('send','cashout','cashin') then return jsonb_build_object('error','INVALID_AMOUNT'); end if;

 if v is null or v=u then return jsonb_build_object('error','INVALID_RECIPIENT'); end if;
 perform id from public.sbut_users where id in(u,v) order by id for update;
 select balance,kyc,role into b,s,r from public.sbut_users where id=u;
 if s<>'approved' or exists(select 1 from public.sbut_users where id=u and frozen) or not exists(select 1 from public.sbut_users where id=v and kyc='approved' and not frozen) then return jsonb_build_object('error','KYC_REQUIRED'); end if;
 f:=case when p_kind='send' then case when p_amount<10000 then 0 when p_amount<50000 then ceil(p_amount*0.015)::bigint else ceil(p_amount*0.02)::bigint end when p_kind='cashout' then case when p_amount<100000 then 600 else ceil(p_amount*0.012)::bigint end else 0 end;
 if p_kind='cashout' and not exists(select 1 from public.sbut_users where id=v and role='merchant') then return jsonb_build_object('error','MERCHANT_REQUIRED'); end if;
 if p_kind='cashin' and r<>'merchant' then return jsonb_build_object('error','MERCHANT_REQUIRED'); end if;
 if p_kind in ('cashout','cashin') then c:=case when p_amount<100000 then 400 else floor(p_amount*0.008)::bigint end; end if;
 if b<p_amount+f then return jsonb_build_object('error','INSUFFICIENT_BALANCE'); end if;
 perform id from wallet_private.treasury where id=1 for update;
 if (select fees+f-c from wallet_private.treasury where id=1)<0 then return jsonb_build_object('error','COMMISSION_POOL_EMPTY'); end if;
 update public.sbut_users set balance=balance-p_amount-f where id=u;
 update public.sbut_users set balance=balance+p_amount where id=v;
 if c>0 then update public.sbut_users set balance=balance+c where id=case when p_kind='cashin' then u else v end; end if;
 update wallet_private.treasury set fees=fees+f-c where id=1;
 insert into public.sbut_transactions(request_id,sender,recipient,kind,amount,fee,commission) values(p_request,u,v,p_kind,p_amount,f,c) returning * into old;
 return to_jsonb(old);
end $$;
drop policy if exists self_read on public.sbut_users;
create policy self_read on public.sbut_users for select to authenticated using(id=auth.uid() and public.sbut_session_active());
drop policy if exists kyc_read on public.sbut_kyc;
create policy kyc_read on public.sbut_kyc for select to authenticated using(user_id=auth.uid() and public.sbut_session_active());
drop policy if exists application_read on public.sbut_merchant_applications;
create policy application_read on public.sbut_merchant_applications for select to authenticated using(user_id=auth.uid() and public.sbut_session_active());
drop policy if exists ledger_read on public.sbut_transactions;
create policy ledger_read on public.sbut_transactions for select to authenticated using((sender=auth.uid() or recipient=auth.uid()) and public.sbut_session_active());
-- Table access is scoped by current server session and server-owned admin role.
alter table public.sbut_consents enable row level security;
alter table public.sbut_requests enable row level security;
alter table public.sbut_banners enable row level security;
alter table public.sbut_admin_audit enable row level security;
create policy consent_self on public.sbut_consents for select to authenticated using((user_id=auth.uid() and public.sbut_session_active()) or public.sbut_is_admin());
create policy request_parties on public.sbut_requests for select to authenticated using(((requester=auth.uid() or payer=auth.uid()) and public.sbut_session_active()) or public.sbut_is_admin());
create policy banner_active on public.sbut_banners for select to anon,authenticated using((active and starts_at<=now() and (ends_at is null or ends_at>now())) or public.sbut_is_admin());
create policy audit_admin on public.sbut_admin_audit for select to authenticated using(public.sbut_is_admin());
create policy users_admin_read on public.sbut_users for select to authenticated using(public.sbut_is_admin());
create policy kyc_admin_read on public.sbut_kyc for select to authenticated using(public.sbut_is_admin());
create policy applications_admin_read on public.sbut_merchant_applications for select to authenticated using(public.sbut_is_admin());
create policy transactions_admin_read on public.sbut_transactions for select to authenticated using(public.sbut_is_admin());
create policy kyc_admin_storage on storage.objects for select to authenticated using(bucket_id='sbut-kyc' and public.sbut_is_admin());
revoke all on public.sbut_consents,public.sbut_requests,public.sbut_banners,public.sbut_admin_audit from public,anon,authenticated;
grant select on public.sbut_consents,public.sbut_requests,public.sbut_admin_audit to authenticated;
grant select on public.sbut_banners to anon,authenticated;
-- Deny bypassing the new PIN + server delay confirmation flow.
revoke all on function public.sbut_transfer(text,bigint,text,text,uuid),public.sbut_apply_merchant(text,text) from public,anon,authenticated;
revoke all on all functions in schema wallet_private from public,anon,authenticated;
-- Explicitly limit public API execution.
revoke all on function public.sbut_open_session(),public.sbut_close_session(),public.sbut_is_admin(),public.sbut_admin_identity(text),public.sbut_accept_terms(text),public.sbut_prepare(text,bigint,text,text,uuid,uuid),public.sbut_commit(uuid,text),public.sbut_create_request(text,bigint,text),public.sbut_request_action(uuid,text),public.sbut_apply_merchant_v3(text,text,text),public.sbut_admin_adjust(uuid,bigint,text,text,text),public.sbut_admin_review(uuid,text,text),public.sbut_admin_banner(uuid,text,text,text,text,boolean) from public,anon;
grant execute on function public.sbut_open_session(),public.sbut_close_session(),public.sbut_is_admin(),public.sbut_admin_identity(text),public.sbut_accept_terms(text),public.sbut_prepare(text,bigint,text,text,uuid,uuid),public.sbut_commit(uuid,text),public.sbut_create_request(text,bigint,text),public.sbut_request_action(uuid,text),public.sbut_apply_merchant_v3(text,text,text),public.sbut_admin_adjust(uuid,bigint,text,text,text),public.sbut_admin_review(uuid,text,text),public.sbut_admin_banner(uuid,text,text,text,text,boolean) to authenticated;
grant execute on function public.sbut_is_admin() to anon;
create index if not exists sbut_requests_payer_date on public.sbut_requests(payer,created_at desc);
create index if not exists sbut_requests_requester_date on public.sbut_requests(requester,created_at desc);
do $$ begin
 if not exists(select 1 from pg_publication_tables where pubname='supabase_realtime' and schemaname='public' and tablename='sbut_requests') then alter publication supabase_realtime add table public.sbut_requests;end if;
 if not exists(select 1 from pg_publication_tables where pubname='supabase_realtime' and schemaname='public' and tablename='sbut_banners') then alter publication supabase_realtime add table public.sbut_banners;end if;
end $$;
create or replace function public.sbut_status() returns jsonb language sql stable set search_path='' as $$ select jsonb_build_object('schema_version',3,'wallet_ready',true,'money_unit','paisa','hold_seconds',5) $$;
create or replace function public.sbut_admin_role(p_user uuid,p_login_id text,p_role text) returns void language plpgsql security definer set search_path='' as $$
begin
 perform wallet_private.require_admin(true);
 if p_user=auth.uid() then raise exception 'CANNOT_CHANGE_SELF';end if;
 if p_role='remove' then delete from wallet_private.admins where user_id=p_user and kind='admin';
 elsif p_role='admin' and length(trim(p_login_id))>=4 then insert into wallet_private.admins(user_id,login_id,kind) values(p_user,p_login_id,'admin') on conflict(user_id) do update set login_id=excluded.login_id where admins.kind='admin';
 else raise exception 'INVALID_ROLE';end if;
 insert into public.sbut_admin_audit(actor,action,target,reason) values(auth.uid(),'admin_role_'||p_role,p_user,'Founder changed administrator access');
end $$;
revoke all on function public.sbut_admin_role(uuid,text,text) from public,anon;
grant execute on function public.sbut_admin_role(uuid,text,text) to authenticated;
-- Founder provisioning: requires an existing confirmed Auth user created in the Supabase dashboard.
-- The supplied password must be set there, never copied into this SQL or browser code.
insert into wallet_private.admins(user_id,login_id,kind) select id,'FOUNDER-TUTUL','founder' from auth.users where lower(email)='tutul@sbut.com' and email_confirmed_at is not null and not exists(select 1 from wallet_private.admins where kind='founder') on conflict do nothing;

create or replace function public.sbut_list_requests() returns jsonb language plpgsql security definer set search_path='' as $$
begin
 perform wallet_private.require_session();
 return coalesce((select jsonb_agg(to_jsonb(q)) from (select r.*,a.username as requester_handle,b.username as payer_handle from public.sbut_requests r join public.sbut_users a on a.id=r.requester join public.sbut_users b on b.id=r.payer where r.requester=auth.uid() or r.payer=auth.uid() order by r.created_at desc limit 100) q),'[]'::jsonb);
end $$;
revoke all on function public.sbut_list_requests() from public,anon;
grant execute on function public.sbut_list_requests() to authenticated;
drop policy if exists kyc_own_read on storage.objects;
create policy kyc_own_read on storage.objects for select to authenticated using(bucket_id='sbut-kyc' and owner_id=auth.uid()::text and public.sbut_session_active());
drop policy if exists kyc_upload on storage.objects;
create policy kyc_upload on storage.objects for insert to authenticated with check(bucket_id='sbut-kyc' and (storage.foldername(name))[1]=auth.uid()::text and public.sbut_session_active());

-- SBUT PAY 005. Install AFTER 003. Money unit = integer paisa.

alter table public.sbut_users add column if not exists security_hold boolean not null default false;
alter table public.sbut_transactions drop constraint if exists sbut_transactions_kind_check;
alter table public.sbut_transactions add constraint sbut_transactions_kind_check check(kind in ('send','cashout','cashin','funding','bonus','admin_credit','admin_debit','refund','recharge','commission_reversal'));
create table wallet_private.limit_rules(user_id uuid primary key references auth.users,per_transaction bigint not null check(per_transaction>=1000),daily bigint not null check(daily>=per_transaction),monthly bigint not null check(monthly>=daily),daily_count int not null check(daily_count between 1 and 10000));
create table wallet_private.defaults(id int primary key check(id=1),per_transaction bigint not null,daily bigint not null,monthly bigint not null,daily_count int not null,risk_amount bigint not null);
insert into wallet_private.defaults values(1,1000000000000,1000000000000,30000000000000,100,1000000);
create table public.sbut_notifications(id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users,kind text not null,data jsonb not null default '{}',read_at timestamptz,created_at timestamptz not null default now());
create index sbut_notification_user_date on public.sbut_notifications(user_id,created_at desc);
create table wallet_private.devices(user_id uuid not null references auth.users,device_id uuid not null,label text not null,first_seen timestamptz not null default now(),last_seen timestamptz not null default now(),primary key(user_id,device_id));
create table public.sbut_disputes(id uuid primary key default gen_random_uuid(),transaction_id uuid not null unique references public.sbut_transactions,complainant uuid not null references public.sbut_users,amount bigint not null check(amount>0),reason text not null check(length(reason) between 10 and 1000),status text not null default 'open' check(status in ('open','refunded','rejected')),resolution text,refund_transaction uuid references public.sbut_transactions,created_at timestamptz not null default now(),resolved_at timestamptz);
create table wallet_private.refunds(original_transaction uuid not null references public.sbut_transactions,refund_transaction uuid unique not null references public.sbut_transactions,amount bigint not null check(amount>0),primary key(original_transaction,refund_transaction));
create table wallet_private.commission_reversals(original_transaction uuid not null references public.sbut_transactions,refund_transaction uuid not null unique references public.sbut_transactions,reversal_transaction uuid not null unique references public.sbut_transactions,amount bigint not null check(amount>0));
create table public.sbut_cash_reports(user_id uuid not null references public.sbut_users,day date not null,cash_received bigint not null check(cash_received>=0),cash_paid bigint not null check(cash_paid>=0),note text not null default '',updated_at timestamptz not null default now(),primary key(user_id,day));
create table wallet_private.recovery_attempts(user_id uuid not null references auth.users,at timestamptz not null default now());
create index sbut_recovery_attempt_user_at on wallet_private.recovery_attempts(user_id,at);
create table wallet_private.recovery_tokens(id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users,session_id uuid not null,status text not null default 'authorized' check(status in ('authorized','prepared','completed')),new_pin_hash text,expires_at timestamptz not null default now()+interval '10 minutes',created_at timestamptz not null default now());
create table wallet_private.push_subscriptions(endpoint text primary key,user_id uuid not null references auth.users,subscription jsonb not null,created_at timestamptz not null default now());
create table wallet_private.push_outbox(notification_id uuid primary key references public.sbut_notifications,attempts int not null default 0,state text not null default 'pending' check(state in ('pending','leased','sent','failed')),available_at timestamptz not null default now(),lease_until timestamptz);
create table wallet_private.integrations(name text primary key,enabled boolean not null default false,public_config jsonb not null default '{}');
insert into wallet_private.integrations(name) values('push'),('recharge'),('kyc');
create or replace function wallet_private.check_operational_session() returns void language plpgsql security definer set search_path='' as $$
begin
 perform wallet_private.require_session();if exists(select 1 from public.sbut_users where id=auth.uid() and security_hold) then raise exception 'SECURITY_HOLD';end if;
end $$;
create or replace function wallet_private.enforce_limits() returns trigger language plpgsql security definer set search_path='' as $$
declare r record; spent_day numeric;spent_month numeric;n int;start_day timestamptz:=(date_trunc('day',now() at time zone 'Asia/Dhaka') at time zone 'Asia/Dhaka');start_month timestamptz:=(date_trunc('month',now() at time zone 'Asia/Dhaka') at time zone 'Asia/Dhaka');
begin
 if new.kind not in ('send','cashout','cashin','recharge') then return new;end if;
 perform id from public.sbut_users where id=new.sender for update;
 if not exists(select 1 from public.sbut_consents where user_id=new.sender and version='2026-10-26-v2') then raise exception 'CONSENT_REQUIRED';end if;
 if exists(select 1 from public.sbut_users where id=new.sender and (frozen or security_hold)) then raise exception 'ACCOUNT_RESTRICTED';end if;
 select coalesce(u.per_transaction,d.per_transaction) per_transaction,coalesce(u.daily,d.daily) daily,coalesce(u.monthly,d.monthly) monthly,coalesce(u.daily_count,d.daily_count) daily_count into r from wallet_private.defaults d left join wallet_private.limit_rules u on u.user_id=new.sender where d.id=1;
 select coalesce(sum(amount+fee) filter(where created_at>=start_day),0),coalesce(sum(amount+fee),0),count(*) filter(where created_at>=start_day) into spent_day,spent_month,n from public.sbut_transactions where sender=new.sender and kind in ('send','cashout','cashin','recharge') and status in ('completed','pending') and created_at>=start_month;
 if new.amount>r.per_transaction or spent_day+new.amount+new.fee>r.daily or spent_month+new.amount+new.fee>r.monthly or n>=r.daily_count then raise exception 'LIMIT_EXCEEDED';end if;
 return new;
end $$;
create trigger sbut_limit_guard before insert on public.sbut_transactions for each row execute function wallet_private.enforce_limits();
create or replace function public.sbut_limits() returns jsonb language plpgsql security definer set search_path='' as $$
declare result jsonb;
begin
 perform wallet_private.check_operational_session();select jsonb_build_object('per_transaction',coalesce(u.per_transaction,d.per_transaction),'daily',coalesce(u.daily,d.daily),'monthly',coalesce(u.monthly,d.monthly),'daily_count',coalesce(u.daily_count,d.daily_count)) into result from wallet_private.defaults d left join wallet_private.limit_rules u on u.user_id=auth.uid() where d.id=1;return result;
end $$;
create or replace function public.sbut_admin_limits(p_user uuid,p_transaction bigint,p_day bigint,p_month bigint,p_count int) returns void language plpgsql security definer set search_path='' as $$
begin
 perform wallet_private.require_admin(true);if p_transaction is null or p_day is null or p_month is null or p_count is null or p_transaction<1000 or p_day<p_transaction or p_month<p_day or p_count not between 1 and 10000 then raise exception 'INVALID_LIMITS';end if;
 if p_user is null then update wallet_private.defaults set per_transaction=p_transaction,daily=p_day,monthly=p_month,daily_count=p_count where id=1;
 else insert into wallet_private.limit_rules values(p_user,p_transaction,p_day,p_month,p_count) on conflict(user_id) do update set per_transaction=excluded.per_transaction,daily=excluded.daily,monthly=excluded.monthly,daily_count=excluded.daily_count;end if;
 insert into public.sbut_admin_audit(actor,action,target,reason) values(auth.uid(),'limits_updated',p_user,'Spending limits changed');
end $$;
create or replace function wallet_private.notify_transaction() returns trigger language plpgsql security definer set search_path='' as $$
begin
 if TG_OP='UPDATE' and new.status is not distinct from old.status then return new;end if;
 insert into public.sbut_notifications(user_id,kind,data) values(new.sender,case when TG_OP='UPDATE' then 'transaction_status' else 'transaction_sent' end,jsonb_build_object('transaction_id',new.id,'status',new.status));
 if new.recipient<>new.sender then insert into public.sbut_notifications(user_id,kind,data) values(new.recipient,case when TG_OP='UPDATE' then 'transaction_status' else 'transaction_received' end,jsonb_build_object('transaction_id',new.id,'status',new.status));end if;
 if TG_OP='INSERT' and new.kind in ('send','cashout','cashin','recharge') and new.amount>=(select risk_amount from wallet_private.defaults where id=1) then insert into public.sbut_notifications(user_id,kind,data) values(new.sender,'large_transfer',jsonb_build_object('transaction_id',new.id));end if;return new;
end $$;
create trigger sbut_transaction_notice after insert or update of status on public.sbut_transactions for each row execute function wallet_private.notify_transaction();
create or replace function wallet_private.notify_request() returns trigger language plpgsql security definer set search_path='' as $$
begin
 insert into public.sbut_notifications(user_id,kind,data) values(new.payer,case when TG_OP='INSERT' then 'request_received' else 'request_updated' end,jsonb_build_object('request_id',new.id,'status',new.status));return new;
end $$;
create trigger sbut_request_notice after insert or update of status on public.sbut_requests for each row execute function wallet_private.notify_request();
create or replace function public.sbut_device_seen(p_device uuid,p_label text) returns boolean language plpgsql security definer set search_path='' as $$
declare is_new boolean;
begin
 perform wallet_private.check_operational_session();if p_device is null then raise exception 'DEVICE_REQUIRED';end if;
 perform user_id from wallet_private.devices where user_id=auth.uid() and device_id=p_device;is_new:=not found;
 insert into wallet_private.devices(user_id,device_id,label) values(auth.uid(),p_device,left(coalesce(p_label,'Browser'),80)) on conflict(user_id,device_id) do update set last_seen=now();
 if is_new then insert into public.sbut_notifications(user_id,kind,data) values(auth.uid(),'new_device',jsonb_build_object('label',left(coalesce(p_label,'Browser'),80)));end if;return is_new;
end $$;
create or replace function public.sbut_read_notification(p_id uuid) returns void language plpgsql security definer set search_path='' as $$
begin perform wallet_private.require_session();update public.sbut_notifications set read_at=coalesce(read_at,now()) where user_id=auth.uid() and (p_id is null or id=p_id);end $$;
create or replace function public.sbut_open_dispute(p_transaction uuid,p_amount bigint,p_reason text) returns uuid language plpgsql security definer set search_path='' as $$
declare t public.sbut_transactions%rowtype;did uuid;
begin
 perform wallet_private.check_operational_session();select * into t from public.sbut_transactions where id=p_transaction and sender=auth.uid() and recipient<>sender and status='completed' and kind in ('send','cashout','cashin');
 if not found or p_amount is null or p_amount<=0 or p_amount>t.amount or p_reason is null or length(trim(p_reason)) not between 10 and 1000 then raise exception 'INVALID_DISPUTE';end if;
 insert into public.sbut_disputes(transaction_id,complainant,amount,reason) values(p_transaction,auth.uid(),p_amount,p_reason) returning id into did;
 insert into public.sbut_notifications(user_id,kind,data) values(auth.uid(),'dispute_opened',jsonb_build_object('dispute_id',did));return did;
end $$;
create or replace function public.sbut_admin_dispute(p_id uuid,p_decision text,p_reason text) returns jsonb language plpgsql security definer set search_path='' as $$
declare d public.sbut_disputes%rowtype;t public.sbut_transactions%rowtype;ref uuid;already numeric;claw bigint;beneficiary uuid;rev uuid;
begin
 perform wallet_private.require_admin(true);if p_reason is null or length(trim(p_reason))<5 or p_decision is null or p_decision not in ('refund','reject') then raise exception 'INVALID_DECISION';end if;
 select * into d from public.sbut_disputes where id=p_id for update;if not found then raise exception 'DISPUTE_NOT_FOUND';end if;
 if d.status<>'open' then return jsonb_build_object('status',d.status,'refund_transaction',d.refund_transaction);end if;
 if p_decision='refund' then
 select * into t from public.sbut_transactions where id=d.transaction_id for update;
 perform id from public.sbut_users where id in(t.sender,t.recipient) order by id for update;
 select coalesce(sum(amount),0) into already from wallet_private.refunds where original_transaction=t.id;
 if already+d.amount>t.amount then raise exception 'REFUND_LIMIT';end if;
 update public.sbut_users set balance=balance-d.amount where id=t.recipient and balance>=d.amount;if not found then raise exception 'REFUND_FUNDS_UNAVAILABLE';end if;
 update public.sbut_users set balance=balance+d.amount where id=t.sender;
 insert into public.sbut_transactions(request_id,sender,recipient,kind,amount,fee) values(gen_random_uuid(),t.recipient,t.sender,'refund',d.amount,0) returning id into ref;
 insert into wallet_private.refunds values(t.id,ref,d.amount);
 claw:=case when t.kind in ('cashin','cashout') then ceil(t.commission::numeric*d.amount/t.amount)::bigint else 0 end;
 if claw>0 then
 beneficiary:=case when t.kind='cashin' then t.sender else t.recipient end;
 update public.sbut_users set balance=balance-claw where id=beneficiary and balance>=claw;if not found then raise exception 'COMMISSION_RETURN_UNAVAILABLE';end if;
 update wallet_private.treasury set fees=fees+claw where id=1;
 insert into public.sbut_transactions(request_id,sender,recipient,kind,amount,fee) values(gen_random_uuid(),beneficiary,beneficiary,'commission_reversal',claw,0) returning id into rev;
 insert into wallet_private.commission_reversals values(t.id,ref,rev,claw);
 end if;

 update public.sbut_disputes set status='refunded',resolution=p_reason,refund_transaction=ref,resolved_at=now() where id=p_id;
 else update public.sbut_disputes set status='rejected',resolution=p_reason,resolved_at=now() where id=p_id;end if;
 insert into public.sbut_admin_audit(actor,action,target,reason,amount) values(auth.uid(),'dispute_'||p_decision,p_id,p_reason,d.amount);
 insert into public.sbut_notifications(user_id,kind,data) values(d.complainant,'dispute_resolved',jsonb_build_object('dispute_id',p_id,'decision',p_decision));return jsonb_build_object('status',case when ref is null then 'rejected' else 'refunded' end,'refund_transaction',ref);
end $$;
create or replace function wallet_private.transaction_delta(t public.sbut_transactions,u uuid) returns bigint language sql immutable set search_path='' as $$
 select case when t.sender=u and t.recipient=u and t.kind in ('funding','bonus','admin_credit') then t.amount when t.sender=u and t.recipient=u and t.kind in ('admin_debit','commission_reversal') then -t.amount else (case when t.recipient=u then t.amount else 0 end)-(case when t.sender=u then t.amount+t.fee else 0 end)+(case when t.kind='cashin' and t.sender=u or t.kind='cashout' and t.recipient=u then t.commission else 0 end) end
$$;
create or replace function public.sbut_merchant_report(p_day date,p_user uuid default null) returns jsonb language plpgsql security definer set search_path='' as $$
declare u uuid:=coalesce(p_user,auth.uid());start_at timestamptz;stop_at timestamptz;result jsonb;
begin
 perform wallet_private.require_session();if p_day is null or (u<>auth.uid() and not public.sbut_is_admin()) or not exists(select 1 from public.sbut_users where id=u and role='merchant') then raise exception 'MERCHANT_REQUIRED';end if;
 start_at:=p_day::timestamp at time zone 'Asia/Dhaka';stop_at:=(p_day+1)::timestamp at time zone 'Asia/Dhaka';
 select jsonb_build_object('day',p_day,'opening',coalesce(sum(wallet_private.transaction_delta(t,u)) filter(where created_at<start_at),0),'closing',coalesce(sum(wallet_private.transaction_delta(t,u)) filter(where created_at<stop_at),0),'cash_in',coalesce(sum(amount) filter(where kind='cashin' and sender=u and created_at>=start_at and created_at<stop_at),0),'cash_out',coalesce(sum(amount) filter(where kind='cashout' and recipient=u and created_at>=start_at and created_at<stop_at),0),'commission',coalesce(sum(commission) filter(where (kind='cashin' and sender=u or kind='cashout' and recipient=u) and created_at>=start_at and created_at<stop_at),0)-coalesce(sum(amount) filter(where kind='commission_reversal' and sender=u and created_at>=start_at and created_at<stop_at),0),'balance_now',(select balance from public.sbut_users where id=u),'ledger_total',coalesce(sum(wallet_private.transaction_delta(t,u)),0),'refunds_cash_in',(select coalesce(sum(r.amount),0) from wallet_private.refunds r join public.sbut_transactions original on original.id=r.original_transaction join public.sbut_transactions back on back.id=r.refund_transaction where original.kind='cashin' and original.sender=u and back.created_at>=start_at and back.created_at<stop_at),'refunds_cash_out',(select coalesce(sum(r.amount),0) from wallet_private.refunds r join public.sbut_transactions original on original.id=r.original_transaction join public.sbut_transactions back on back.id=r.refund_transaction where original.kind='cashout' and original.recipient=u and back.created_at>=start_at and back.created_at<stop_at),'actual',(select to_jsonb(c) from public.sbut_cash_reports c where user_id=u and day=p_day)) into result from public.sbut_transactions t where sender=u or recipient=u;return result;
end $$;
create or replace function public.sbut_cash_report(p_day date,p_received bigint,p_paid bigint,p_note text) returns void language plpgsql security definer set search_path='' as $$
begin
 perform wallet_private.check_operational_session();if p_day is null or p_day>(now() at time zone 'Asia/Dhaka')::date or p_received is null or p_paid is null or p_received<0 or p_paid<0 or not exists(select 1 from public.sbut_users where id=auth.uid() and role='merchant') then raise exception 'INVALID_REPORT';end if;
 insert into public.sbut_cash_reports values(auth.uid(),p_day,p_received,p_paid,left(coalesce(p_note,''),500),now()) on conflict(user_id,day) do update set cash_received=excluded.cash_received,cash_paid=excluded.cash_paid,note=excluded.note,updated_at=now();
end $$;
-- Recovering a PIN requires a fresh server-signed OTP authentication claim AND matching reviewed identity.
create or replace function public.sbut_recovery_authorize(p_nid text,p_dob date) returns jsonb language plpgsql security definer set search_path='' as $$
declare tok uuid;u uuid:=auth.uid();sid uuid:=nullif(auth.jwt()->>'session_id','')::uuid;
begin
 if u is null or sid is null or not exists(select 1 from auth.sessions where id=sid and user_id=u) or not exists(select 1 from jsonb_array_elements(coalesce(auth.jwt()->'amr','[]'::jsonb)) a where a->>'method'='otp' and (a->>'timestamp')::numeric>=extract(epoch from now()-interval '5 minutes') and (a->>'timestamp')::numeric<=extract(epoch from now()+interval '1 minute')) then return jsonb_build_object('error','FRESH_OTP_REQUIRED');end if;
 perform id from auth.users where id=u for update;
 if (select count(*) from wallet_private.recovery_attempts where user_id=u and at>now()-interval '1 hour')>=5 then return jsonb_build_object('error','RECOVERY_LOCKED');end if;
 insert into wallet_private.recovery_attempts(user_id) values(u);
 if not exists(select 1 from public.sbut_kyc k join public.sbut_users p on p.id=k.user_id where k.user_id=u and k.nid=p_nid and k.dob=p_dob and p.kyc='approved' and not p.frozen) then return jsonb_build_object('error','IDENTITY_MISMATCH');end if;
 select id into tok from wallet_private.recovery_tokens where user_id=u and status='prepared' order by created_at desc limit 1;
 if tok is not null then update wallet_private.recovery_tokens set session_id=sid,expires_at=now()+interval '10 minutes' where id=tok;return jsonb_build_object('token',tok,'resuming',true);end if;
 insert into wallet_private.recovery_tokens(user_id,session_id) values(u,sid) returning id into tok;return jsonb_build_object('token',tok);
end $$;
create or replace function public.sbut_recovery_begin(p_token uuid,p_user uuid,p_session uuid,p_pin text) returns jsonb language plpgsql security definer set search_path='' as $$
declare r wallet_private.recovery_tokens%rowtype;
begin
 perform id from auth.users where id=p_user for update;
 select * into r from wallet_private.recovery_tokens where id=p_token and user_id=p_user and session_id=p_session for update;
 if not found or coalesce(p_pin,'') !~ '^[0-9]{6}$' or r.expires_at<now() then raise exception 'INVALID_RECOVERY';end if;
 if r.status in ('prepared','completed') then
 if extensions.crypt(p_pin,r.new_pin_hash) is distinct from r.new_pin_hash then raise exception 'PIN_MISMATCH';end if;
 return jsonb_build_object('status',r.status);end if;
 if exists(select 1 from wallet_private.recovery_tokens where user_id=p_user and status='prepared' and id<>p_token) then raise exception 'RECOVERY_IN_PROGRESS';end if;
 update public.sbut_users set security_hold=true where id=p_user;
 update wallet_private.recovery_tokens set status='prepared',new_pin_hash=extensions.crypt(p_pin,extensions.gen_salt('bf',12)) where id=p_token;
 return jsonb_build_object('status','prepared');
end $$;
create or replace function public.sbut_recovery_finish(p_token uuid,p_user uuid,p_session uuid,p_pin text) returns void language plpgsql security definer set search_path='' as $$
declare r wallet_private.recovery_tokens%rowtype;
begin
 perform id from auth.users where id=p_user for update;
 select * into r from wallet_private.recovery_tokens where id=p_token and user_id=p_user and session_id=p_session for update;
 if not found or r.status not in ('prepared','completed') or extensions.crypt(p_pin,r.new_pin_hash) is distinct from r.new_pin_hash then raise exception 'INVALID_RECOVERY';end if;
 if r.status='completed' then return;end if;
 update wallet_private.pins set hash=r.new_pin_hash,failures=0,locked_until=null where user_id=p_user;if not found then raise exception 'PIN_RECORD_REQUIRED';end if;
 update public.sbut_users set security_hold=false where id=p_user;
 update wallet_private.sessions set revoked=true where user_id=p_user;
 update wallet_private.recovery_tokens set status='completed' where id=p_token;
 insert into public.sbut_notifications(user_id,kind) values(p_user,'pin_changed');
end $$;
create or replace function public.sbut_subscribe_push(p_subscription jsonb) returns void language plpgsql security definer set search_path='' as $$
declare v_endpoint text:=p_subscription->>'endpoint';
begin
 perform wallet_private.check_operational_session();
 if v_endpoint is null or length(v_endpoint)>4096 or v_endpoint !~ '^https://(fcm\.googleapis\.com|([a-zA-Z0-9-]+\.)*push\.apple\.com|updates\.push\.services\.mozilla\.com|([a-zA-Z0-9-]+\.)*notify\.windows\.com)/' or coalesce(p_subscription#>>'{keys,p256dh}','') !~ '^[a-zA-Z0-9_-]{40,200}$' or coalesce(p_subscription#>>'{keys,auth}','') !~ '^[a-zA-Z0-9_-]{16,80}$' then raise exception 'INVALID_SUBSCRIPTION';end if;
 insert into wallet_private.push_subscriptions(endpoint,user_id,subscription) values(v_endpoint,auth.uid(),p_subscription) on conflict(endpoint) do update set subscription=excluded.subscription where push_subscriptions.user_id=auth.uid();
 if not found then raise exception 'SUBSCRIPTION_OWNER';end if;
end $$;
create or replace function public.sbut_unsubscribe_push(p_endpoint text) returns void language plpgsql security definer set search_path='' as $$
begin perform wallet_private.require_session();delete from wallet_private.push_subscriptions where user_id=auth.uid() and endpoint=p_endpoint;end $$;
create or replace function wallet_private.enqueue_push() returns trigger language plpgsql security definer set search_path='' as $$
begin insert into wallet_private.push_outbox(notification_id) values(new.id);return new;end $$;
create trigger sbut_enqueue_push after insert on public.sbut_notifications for each row execute function wallet_private.enqueue_push();
create or replace function public.sbut_claim_push(p_batch int default 20) returns jsonb language plpgsql security definer set search_path='' as $$
declare result jsonb;
begin
 update wallet_private.push_outbox set state='failed' where state='leased' and lease_until<now() and attempts>=5;
 with candidates as(select notification_id from wallet_private.push_outbox where attempts<5 and available_at<=now() and (state='pending' or state='leased' and lease_until<now()) order by available_at for update skip locked limit least(greatest(p_batch,1),50)),claimed as(update wallet_private.push_outbox o set state='leased',lease_until=now()+interval '2 minutes',attempts=attempts+1 from candidates c where c.notification_id=o.notification_id returning o.notification_id)
 select coalesce(jsonb_agg(jsonb_build_object('id',n.id,'subscriptions',(select coalesce(jsonb_agg(subscription),'[]'::jsonb) from wallet_private.push_subscriptions where user_id=n.user_id))),'[]'::jsonb) into result from claimed c join public.sbut_notifications n on n.id=c.notification_id;return result;
end $$;
create or replace function public.sbut_finish_push(p_id uuid,p_ok boolean,p_dead_endpoints text[] default '{}') returns void language plpgsql security definer set search_path='' as $$
begin
 delete from wallet_private.push_subscriptions s using public.sbut_notifications n where n.id=p_id and n.user_id=s.user_id and s.endpoint=any(p_dead_endpoints);
 update wallet_private.push_outbox set state=case when p_ok then 'sent' when attempts>=5 then 'failed' else 'pending' end,available_at=now()+interval '1 minute'*power(2,attempts),lease_until=null where notification_id=p_id;
end $$;
create or replace function public.sbut_operational_config() returns jsonb language sql stable security definer set search_path='' as $$
 select coalesce(jsonb_object_agg(name,jsonb_build_object('enabled',enabled,'config',case when name='push' then jsonb_build_object('vapid_public_key',public_config->>'vapid_public_key') else '{}'::jsonb end)),'{}'::jsonb) from wallet_private.integrations
$$;
-- RLS and least-privilege grants.
alter table public.sbut_notifications enable row level security;
alter table public.sbut_disputes enable row level security;
alter table public.sbut_cash_reports enable row level security;
create policy notifications_self on public.sbut_notifications for select to authenticated using(user_id=auth.uid() and public.sbut_session_active());
create policy security_alert_admin on public.sbut_notifications for select to authenticated using(public.sbut_is_admin() and kind in ('new_device','large_transfer','pin_changed'));
create policy dispute_parties on public.sbut_disputes for select to authenticated using((complainant=auth.uid() and public.sbut_session_active()) or public.sbut_is_admin());
create policy reports_owner on public.sbut_cash_reports for select to authenticated using((user_id=auth.uid() and public.sbut_session_active()) or public.sbut_is_admin());
revoke all on public.sbut_notifications,public.sbut_disputes,public.sbut_cash_reports from public,anon,authenticated;
grant select on public.sbut_notifications,public.sbut_disputes,public.sbut_cash_reports to authenticated;
revoke all on all functions in schema wallet_private from public,anon,authenticated;
revoke all on function public.sbut_limits(),public.sbut_admin_limits(uuid,bigint,bigint,bigint,int),public.sbut_device_seen(uuid,text),public.sbut_read_notification(uuid),public.sbut_open_dispute(uuid,bigint,text),public.sbut_admin_dispute(uuid,text,text),public.sbut_merchant_report(date,uuid),public.sbut_cash_report(date,bigint,bigint,text),public.sbut_recovery_authorize(text,date),public.sbut_recovery_begin(uuid,uuid,uuid,text),public.sbut_recovery_finish(uuid,uuid,uuid,text),public.sbut_subscribe_push(jsonb),public.sbut_unsubscribe_push(text),public.sbut_claim_push(int),public.sbut_finish_push(uuid,boolean,text[]),public.sbut_operational_config() from public,anon,authenticated;
grant execute on function public.sbut_limits(),public.sbut_device_seen(uuid,text),public.sbut_read_notification(uuid),public.sbut_open_dispute(uuid,bigint,text),public.sbut_merchant_report(date,uuid),public.sbut_cash_report(date,bigint,bigint,text),public.sbut_recovery_authorize(text,date),public.sbut_subscribe_push(jsonb),public.sbut_unsubscribe_push(text),public.sbut_operational_config() to authenticated;
grant execute on function public.sbut_admin_limits(uuid,bigint,bigint,bigint,int),public.sbut_admin_dispute(uuid,text,text) to authenticated;
grant execute on function public.sbut_recovery_begin(uuid,uuid,uuid,text),public.sbut_recovery_finish(uuid,uuid,uuid,text),public.sbut_claim_push(int),public.sbut_finish_push(uuid,boolean,text[]) to service_role;
-- An in-progress recovery blocks money movement through the core session guard.

create or replace function wallet_private.require_session() returns void language plpgsql security definer set search_path='' as $$
declare sid uuid:=nullif(auth.jwt()->>'session_id','')::uuid; u uuid:=auth.uid();
begin
 if u is null or sid is null or not exists(select 1 from auth.sessions where id=sid and user_id=u) or not exists(select 1 from wallet_private.sessions where id=sid and user_id=u and not revoked and expires_at>now()) then raise exception 'SESSION_LOCKED'; end if;
 if exists(select 1 from public.sbut_users where id=u and (frozen or security_hold)) then raise exception 'ACCOUNT_FROZEN'; end if;
end $$;
do $$ begin
 if not exists(select 1 from pg_publication_tables where pubname='supabase_realtime' and schemaname='public' and tablename='sbut_notifications') then alter publication supabase_realtime add table public.sbut_notifications;end if;
 if not exists(select 1 from pg_publication_tables where pubname='supabase_realtime' and schemaname='public' and tablename='sbut_disputes') then alter publication supabase_realtime add table public.sbut_disputes;end if;
end $$;
create or replace function public.sbut_status() returns jsonb language sql stable set search_path='' as $$ select jsonb_build_object('schema_version',4,'wallet_ready',true,'money_unit','paisa','hold_seconds',5) $$;

create or replace function public.sbut_confirm_identity_name(p_name text) returns void language plpgsql security definer set search_path='' as $$
begin
 perform wallet_private.require_session();if p_name is null or length(trim(p_name)) not between 2 and 150 then raise exception 'INVALID_NAME';end if;
 update public.sbut_users set name=trim(p_name) where id=auth.uid() and kyc in ('draft','rejected');if not found then raise exception 'KYC_STATE';end if;
end $$;
revoke all on function public.sbut_confirm_identity_name(text) from public,anon;
grant execute on function public.sbut_confirm_identity_name(text) to authenticated;

create or replace function public.sbut_submit_kyc(p_nid text,p_dob date,p_front text,p_back text,p_selfie text,p_pin text) returns void language plpgsql security definer set search_path='' as $$
declare u uuid:=auth.uid();
begin
 if not exists(select 1 from public.sbut_consents where user_id=auth.uid() and version='2026-10-26-v2') then raise exception 'CONSENT_REQUIRED';end if;
 if u is null or coalesce(p_pin,'') !~ '^[0-9]{6}$' then raise exception 'INVALID_PIN'; end if;
 if not exists(select 1 from public.sbut_users where id=u and kyc in ('draft','rejected')) then raise exception 'KYC_STATE'; end if;
 if p_dob is null or p_nid is null or p_dob>current_date-interval '18 years' or p_dob<date '1900-01-01' or p_nid !~ '^([0-9]{10}|[0-9]{13}|[0-9]{17})$' then raise exception 'INVALID_IDENTITY'; end if;
 if not exists(select 1 from storage.objects where bucket_id='sbut-kyc' and name=p_front and owner_id=u::text) or not exists(select 1 from storage.objects where bucket_id='sbut-kyc' and name=p_back and owner_id=u::text) or not exists(select 1 from storage.objects where bucket_id='sbut-kyc' and name=p_selfie and owner_id=u::text) then raise exception 'UPLOAD_REQUIRED'; end if;
 insert into public.sbut_kyc values(u,p_nid,p_dob,p_front,p_back,p_selfie,now()) on conflict(user_id) do update set nid=excluded.nid,dob=excluded.dob,front_path=excluded.front_path,back_path=excluded.back_path,selfie_path=excluded.selfie_path,submitted_at=now();
 insert into wallet_private.pins(user_id,hash) values(u,extensions.crypt(p_pin,extensions.gen_salt('bf',12))) on conflict(user_id) do update set hash=excluded.hash,failures=0,locked_until=null;
 update public.sbut_users set kyc='pending' where id=u;
end $$;
create or replace function public.sbut_accept_terms(p_version text) returns void language plpgsql security definer set search_path='' as $$
begin
 if auth.uid() is null or p_version<>'2026-10-26-v2' then raise exception 'CONSENT_REQUIRED'; end if;
 insert into public.sbut_consents(user_id,version) values(auth.uid(),p_version) on conflict(user_id) do update set version=excluded.version,accepted_at=now();
end $$;
create or replace function public.sbut_admin_review(p_user uuid,p_action text,p_reason text) returns void language plpgsql security definer set search_path='' as $$
begin
 perform wallet_private.require_admin();if p_reason is null or length(trim(p_reason))<5 then raise exception 'REASON_REQUIRED';end if;
 if p_action='approve_kyc' then
 if not exists(select 1 from public.sbut_consents where user_id=p_user and version='2026-10-26-v2') then raise exception 'CONSENT_REQUIRED';end if;
 perform wallet_private.approve_kyc(p_user);
 elsif p_action='reject_kyc' then update public.sbut_users set kyc='rejected' where id=p_user and kyc='pending';
 elsif p_action='approve_merchant' then
 if not exists(select 1 from public.sbut_users where id=p_user and kyc='approved') then raise exception 'KYC_REQUIRED';end if;
 update public.sbut_merchant_applications set status='approved',reviewed_at=now() where user_id=p_user and status='pending';if not found then raise exception 'NO_APPLICATION';end if;
 update public.sbut_users set role='merchant' where id=p_user;
 elsif p_action='reject_merchant' then update public.sbut_merchant_applications set status='rejected',reviewed_at=now() where user_id=p_user and status='pending';
 elsif p_action='freeze' then update public.sbut_users set frozen=true where id=p_user;update wallet_private.sessions set revoked=true where user_id=p_user;
 elsif p_action='unfreeze' then update public.sbut_users set frozen=false where id=p_user;
 elsif p_action='remove_merchant' then update public.sbut_users set role='user' where id=p_user;
 else raise exception 'INVALID_ACTION';end if;
 insert into public.sbut_admin_audit(actor,action,target,reason) values(auth.uid(),p_action,p_user,p_reason);
end $$;

create or replace function public.sbut_session_active() returns boolean language sql stable security definer set search_path='' as $$
 select auth.uid() is not null and exists(select 1 from wallet_private.sessions s join auth.sessions a on a.id=s.id and a.user_id=s.user_id where s.id=nullif(auth.jwt()->>'session_id','')::uuid and s.user_id=auth.uid() and not s.revoked and s.expires_at>now()) and not exists(select 1 from public.sbut_users where id=auth.uid() and (frozen or security_hold))
$$;

-- SBUT PAY 006, after 005. Provider disabled by default: no synthetic recharge success.

alter table wallet_private.confirmations add column metadata jsonb not null default '{}';
create table public.sbut_recharges(id uuid primary key default gen_random_uuid(),user_id uuid not null references public.sbut_users,reserve_user uuid not null references public.sbut_users,phone text not null,operator text not null,amount bigint not null check(amount>=1000),transaction_id uuid not null unique references public.sbut_transactions,status text not null default 'queued' check(status in ('queued','processing','unknown','completed','failed')),provider_reference text,refund_transaction uuid references public.sbut_transactions,created_at timestamptz not null default now(),updated_at timestamptz not null default now());
alter table public.sbut_recharges enable row level security;
create policy recharge_owner on public.sbut_recharges for select to authenticated using((user_id=auth.uid() and public.sbut_session_active()) or public.sbut_is_admin());
revoke all on public.sbut_recharges from public,anon,authenticated;grant select on public.sbut_recharges to authenticated;
create or replace function public.sbut_prepare_recharge(p_phone text,p_operator text,p_amount bigint,p_pin text,p_request uuid) returns jsonb language plpgsql security definer set search_path='' as $$
declare err text;c wallet_private.confirmations%rowtype;
begin
 perform wallet_private.check_operational_session();
 if not exists(select 1 from wallet_private.integrations where name='recharge' and enabled and public_config ? 'reserve_user') then return jsonb_build_object('error','PROVIDER_UNAVAILABLE');end if;
 if p_phone is null or p_phone !~ '^8801[3-9][0-9]{8}$' or p_operator is null or p_operator not in ('grameenphone','banglalink','robi','airtel','teletalk') or p_amount is null or p_amount<1000 or p_amount>1000000000000 or p_request is null then return jsonb_build_object('error','INVALID_RECHARGE');end if;
 err:=wallet_private.check_pin(p_pin);if err is not null then return jsonb_build_object('error',err);end if;
 select * into c from wallet_private.confirmations where user_id=auth.uid() and request_id=p_request for update;
 if found then
 if c.kind<>'recharge' or c.amount<>p_amount or c.recipient<>p_phone or c.metadata->>'operator'<>p_operator then return jsonb_build_object('error','REQUEST_CONFLICT');end if;
 if c.transaction_id is not null then return jsonb_build_object('completed',(select to_jsonb(t) from public.sbut_transactions t where id=c.transaction_id));end if;
 update wallet_private.confirmations set session_id=nullif(auth.jwt()->>'session_id','')::uuid,ready_at=now()+interval '5 seconds',expires_at=now()+interval '2 minutes' where id=c.id returning * into c;
 else insert into wallet_private.confirmations(user_id,session_id,recipient,amount,kind,request_id,metadata) values(auth.uid(),nullif(auth.jwt()->>'session_id','')::uuid,p_phone,p_amount,'recharge',p_request,jsonb_build_object('operator',p_operator)) returning * into c;end if;
 return jsonb_build_object('challenge',c.id,'amount',c.amount,'kind',c.kind,'recipient',c.recipient);
end $$;
create or replace function public.sbut_commit_recharge(p_challenge uuid,p_pin text) returns jsonb language plpgsql security definer set search_path='' as $$
declare c wallet_private.confirmations%rowtype;err text;v uuid;tx public.sbut_transactions%rowtype;
begin
 perform wallet_private.check_operational_session();perform user_id from wallet_private.pins where user_id=auth.uid() for update;
 select * into c from wallet_private.confirmations where id=p_challenge and user_id=auth.uid() and session_id=nullif(auth.jwt()->>'session_id','')::uuid and kind='recharge' for update;
 if not found then return jsonb_build_object('error','CONFIRMATION_REQUIRED');end if;
 if c.transaction_id is not null then return (select to_jsonb(t) from public.sbut_transactions t where id=c.transaction_id);end if;
 if now()<c.ready_at or now()>c.expires_at then return jsonb_build_object('error','HOLD_REQUIRED');end if;
 err:=wallet_private.check_pin(p_pin);if err is not null then return jsonb_build_object('error',err);end if;
 select (public_config->>'reserve_user')::uuid into v from wallet_private.integrations where name='recharge' and enabled;
 if v is null or v=auth.uid() then return jsonb_build_object('error','PROVIDER_UNAVAILABLE');end if;
 perform id from public.sbut_users where id in(v,auth.uid()) order by id for update;
 if not exists(select 1 from public.sbut_users where id=auth.uid() and kyc='approved' and not frozen and not security_hold) or not exists(select 1 from public.sbut_users where id=v and kyc='approved' and not frozen) then return jsonb_build_object('error','KYC_REQUIRED');end if;
 update public.sbut_users set balance=balance-c.amount where id=auth.uid() and balance>=c.amount;if not found then return jsonb_build_object('error','INSUFFICIENT_BALANCE');end if;
 update public.sbut_users set balance=balance+c.amount where id=v;
 insert into public.sbut_transactions(request_id,sender,recipient,kind,amount,fee,status) values(c.request_id,auth.uid(),v,'recharge',c.amount,0,'pending') returning * into tx;
 insert into public.sbut_recharges(user_id,reserve_user,phone,operator,amount,transaction_id) values(auth.uid(),v,c.recipient,c.metadata->>'operator',c.amount,tx.id);
 update wallet_private.confirmations set transaction_id=tx.id where id=c.id;return to_jsonb(tx);
end $$;
create or replace function wallet_private.protect_recharge_reserve() returns trigger language plpgsql security definer set search_path='' as $$
begin
 if new.balance<old.balance and new.balance<(select coalesce(sum(amount),0) from public.sbut_recharges where reserve_user=old.id and status in ('queued','processing','unknown')) then raise exception 'RESERVED_FUNDS';end if;return new;
end $$;
create trigger sbut_reserve_guard before update of balance on public.sbut_users for each row execute function wallet_private.protect_recharge_reserve();
create or replace function public.sbut_claim_recharges() returns jsonb language plpgsql security definer set search_path='' as $$
declare result jsonb;
begin
 with c as(select id from public.sbut_recharges where status='queued' order by created_at for update skip locked limit 10),u as(update public.sbut_recharges r set status='processing',updated_at=now() from c where r.id=c.id returning r.*) select coalesce(jsonb_agg(to_jsonb(u)),'[]'::jsonb) into result from u;return result;
end $$;
create or replace function public.sbut_finish_recharge(p_id uuid,p_status text,p_reference text) returns void language plpgsql security definer set search_path='' as $$
declare r public.sbut_recharges%rowtype;refund uuid;
begin
 if p_status is null or p_status not in ('completed','failed','unknown') then raise exception 'INVALID_STATUS';end if;
 select * into r from public.sbut_recharges where id=p_id for update;if not found then raise exception 'ORDER_NOT_FOUND';end if;
 if r.status in ('completed','failed') then return;end if;
 if p_status='completed' and coalesce(length(trim(p_reference)),0)<3 then raise exception 'PROVIDER_REFERENCE_REQUIRED';end if;
 perform id from public.sbut_users where id in(r.user_id,r.reserve_user) order by id for update;
 update public.sbut_recharges set status=p_status,provider_reference=p_reference,updated_at=now() where id=p_id;
 if p_status='failed' then
 update public.sbut_users set balance=balance-r.amount where id=r.reserve_user and balance>=r.amount;if not found then raise exception 'REFUND_FUNDS_UNAVAILABLE';end if;
 update public.sbut_users set balance=balance+r.amount where id=r.user_id;
 insert into public.sbut_transactions(request_id,sender,recipient,kind,amount,fee) values(gen_random_uuid(),r.reserve_user,r.user_id,'refund',r.amount,0) returning id into refund;
 insert into wallet_private.refunds values(r.transaction_id,refund,r.amount);
 update public.sbut_recharges set refund_transaction=refund where id=p_id;
 end if;
 update public.sbut_transactions set status=case when p_status='unknown' then 'pending' else p_status end where id=r.transaction_id;
end $$;
revoke all on function public.sbut_prepare_recharge(text,text,bigint,text,uuid),public.sbut_commit_recharge(uuid,text),public.sbut_claim_recharges(),public.sbut_finish_recharge(uuid,text,text) from public,anon,authenticated;
grant execute on function public.sbut_prepare_recharge(text,text,bigint,text,uuid),public.sbut_commit_recharge(uuid,text) to authenticated;
grant execute on function public.sbut_claim_recharges(),public.sbut_finish_recharge(uuid,text,text) to service_role;
revoke all on all functions in schema wallet_private from public,anon,authenticated;
do $$ begin
 if not exists(select 1 from pg_publication_tables where pubname='supabase_realtime' and schemaname='public' and tablename='sbut_recharges') then alter publication supabase_realtime add table public.sbut_recharges;end if;
end $$;

create or replace function public.sbut_admin_recharge(p_id uuid,p_status text,p_reference text,p_reason text) returns void language plpgsql security definer set search_path='' as $$
begin
 perform wallet_private.require_admin(true);
 if p_status is null or p_status not in ('completed','failed') or p_reference is null or length(trim(p_reference))<3 or p_reason is null or length(trim(p_reason))<10 then raise exception 'RECONCILIATION_REQUIRED';end if;
 perform public.sbut_finish_recharge(p_id,p_status,p_reference);
 insert into public.sbut_admin_audit(actor,action,target,reference,reason) values(auth.uid(),'recharge_reconcile_'||p_status,p_id,p_reference,p_reason);
end $$;
revoke all on function public.sbut_admin_recharge(uuid,text,text,text) from public,anon;
grant execute on function public.sbut_admin_recharge(uuid,text,text,text) to authenticated;

-- SBUT PAY 007. Run after 006. Username access and reviewed recovery/estate claims.

alter table public.sbut_users alter column phone drop not null;
alter table public.sbut_admin_audit alter column actor drop not null; -- backend completion has no impersonated admin actor
create table wallet_private.access_identity(user_id uuid primary key references public.sbut_users(id), username text unique not null, alias text unique not null, login_pin_hash text not null);
create table wallet_private.access_rate(key text primary key, window_at timestamptz not null default now(), attempts int not null default 1);
create table public.sbut_support_settings(id int primary key check(id=1), phone text not null default '', email text not null default '');
insert into public.sbut_support_settings(id) values(1);
alter table public.sbut_support_settings enable row level security;
revoke all on public.sbut_support_settings from anon,authenticated;
create table public.sbut_help_claims(
 id uuid primary key default gen_random_uuid(), target_handle text not null check(target_handle ~ '^[a-z0-9_]{3,24}$'),
 kind text not null check(kind in ('help','recovery','heir')), claimant_name text not null, contact text not null,
 details text not null, status text not null default 'submitted' check(status in ('submitted','reviewing','rejected','link_issued','recovered','settled','resolved')),
 evidence text[] not null default '{}', reason text, reviewed_by uuid references auth.users, target_user uuid references public.sbut_users,
 beneficiary uuid references public.sbut_users, settlement uuid references public.sbut_transactions,
 tracking_hash text not null, created_at timestamptz not null default now(),updated_at timestamptz not null default now()
);
alter table public.sbut_help_claims enable row level security;
revoke all on public.sbut_help_claims from anon,authenticated;
grant select on public.sbut_help_claims to authenticated;
create policy claim_admin_read on public.sbut_help_claims for select to authenticated using(public.sbut_is_admin());
create index sbut_claim_status on public.sbut_help_claims(status,created_at);
create table wallet_private.claim_links(id uuid primary key default gen_random_uuid(),claim_id uuid unique not null references public.sbut_help_claims,user_id uuid not null references public.sbut_users,token_hash text unique not null,expires_at timestamptz not null default now()+interval '15 minutes',state text not null default 'issued' check(state in ('issued','prepared','used')),pin_hash text,login_hash text);
insert into storage.buckets(id,name,public,file_size_limit,allowed_mime_types) values('sbut-claims','sbut-claims',false,5242880,array['image/jpeg','image/png','application/pdf']) on conflict(id) do nothing;
create policy claim_evidence_admin on storage.objects for select to authenticated using(bucket_id='sbut-claims' and public.sbut_is_admin());
create or replace function public.sbut_support_contact() returns jsonb language sql stable security definer set search_path='' as $$ select jsonb_build_object('phone',phone,'email',email) from public.sbut_support_settings where id=1 $$;
create or replace function public.sbut_admin_support(p_phone text,p_email text) returns void language plpgsql security definer set search_path='' as $$
begin
 perform wallet_private.require_admin(true);
 if coalesce(p_phone,'') !~ '^([+][0-9]{8,15})?$' or length(coalesce(p_email,''))>254 or (coalesce(p_email,'')<>'' and p_email !~ '^[^[:space:]@]+@[^[:space:]@]+[.][^[:space:]@]+$') then raise exception 'INVALID_CONTACT';end if;
 update public.sbut_support_settings set phone=coalesce(p_phone,''),email=coalesce(p_email,'') where id=1;
 insert into public.sbut_admin_audit(actor,action,reason) values(auth.uid(),'support_contact','Support channels updated');
end $$;
-- Durable gate returns false (does NOT throw and roll back the counter).
create or replace function public.sbut_access_gate(p_key text,p_limit int) returns boolean language plpgsql security definer set search_path='' as $$
declare n int;
begin
 if length(p_key)<>64 or p_limit not between 1 and 30 then return false;end if;
 insert into wallet_private.access_rate(key) values(p_key) on conflict(key) do update set attempts=case when wallet_private.access_rate.window_at<now()-interval '15 minutes' then 1 else wallet_private.access_rate.attempts+1 end,window_at=case when wallet_private.access_rate.window_at<now()-interval '15 minutes' then now() else wallet_private.access_rate.window_at end returning attempts into n;
 -- Keep hot-path cleanup bounded; schedule full retention cleanup separately.
 delete from wallet_private.access_rate where key in(select key from wallet_private.access_rate where window_at<now()-interval '1 day' limit 100);
 return n<=p_limit;
end $$;
create or replace function public.sbut_access_lookup(p_username text) returns jsonb language sql stable security definer set search_path='' as $$ select jsonb_build_object('id',user_id,'alias',alias) from wallet_private.access_identity where username=p_username $$;
create or replace function public.sbut_access_register(p_user uuid,p_username text,p_alias text,p_name text,p_pin text) returns void language plpgsql security definer set search_path='' as $$
begin
 if coalesce(p_pin,'') !~ '^[0-9]{6}$' or p_username !~ '^[a-z0-9_]{3,24}$' or length(trim(p_name)) not between 2 and 100 or p_alias<>p_username||'@identity.sbut.invalid' then raise exception 'INVALID_ACCOUNT';end if;
 insert into public.sbut_users(id,username,name,phone) values(p_user,p_username,trim(p_name),null);
 insert into wallet_private.access_identity values(p_user,p_username,p_alias,extensions.crypt(p_pin,extensions.gen_salt('bf',12))); 
end $$;
create or replace function public.sbut_claim_submit(p_handle text,p_kind text,p_name text,p_contact text,p_details text,p_hash text) returns uuid language plpgsql security definer set search_path='' as $$
declare c uuid;
begin
 if p_kind not in ('help','recovery','heir') or p_handle !~ '^[a-z0-9_]{3,24}$' or length(trim(p_name)) not between 2 and 100 or length(trim(p_contact)) not between 5 and 254 or length(trim(p_details)) not between 20 and 2000 or p_hash !~ '^[0-9a-f]{64}$' then raise exception 'INVALID_CLAIM';end if;
 -- Accept unknown handles too: intake must not disclose whether an account exists.
 insert into public.sbut_help_claims(target_handle,kind,claimant_name,contact,details,tracking_hash) values(p_handle,p_kind,trim(p_name),trim(p_contact),trim(p_details),p_hash) returning id into c;return c;
end $$;
create or replace function public.sbut_claim_evidence(p_id uuid,p_hash text,p_path text) returns void language plpgsql security definer set search_path='' as $$
begin
 perform id from public.sbut_help_claims where id=p_id and tracking_hash=p_hash and status in ('submitted','reviewing') and created_at>now()-interval '24 hours' for update;
 if not found or p_path not like p_id::text||'/%' or not exists(select 1 from storage.objects where bucket_id='sbut-claims' and name=p_path) then raise exception 'INVALID_CLAIM';end if;
 update public.sbut_help_claims set evidence=array_append(evidence,p_path) where id=p_id and cardinality(evidence)<5;if not found then raise exception 'EVIDENCE_LIMIT';end if;
end $$;
create or replace function public.sbut_claim_status(p_id uuid,p_hash text) returns jsonb language sql stable security definer set search_path='' as $$ select jsonb_build_object('status',status,'updated_at',updated_at) from public.sbut_help_claims where id=p_id and tracking_hash=p_hash $$;
create or replace function public.sbut_admin_claim(p_id uuid,p_action text,p_reason text,p_token_hash text default null) returns jsonb language plpgsql security definer set search_path='' as $$
declare c public.sbut_help_claims%rowtype;u uuid;
begin
 perform wallet_private.require_admin(true);
 if length(trim(coalesce(p_reason,'')))<10 or length(p_reason)>2000 then raise exception 'REASON_REQUIRED';end if;
 select * into c from public.sbut_help_claims where id=p_id for update;
 if not found or c.status in ('recovered','settled','rejected','resolved') then raise exception 'CASE_CLOSED';end if;
 select id into u from public.sbut_users where username=c.target_handle for update;
 if p_action='review' then update public.sbut_help_claims set status='reviewing' where id=p_id;
 elsif p_action='freeze' then
 if u is null then raise exception 'ACCOUNT_REQUIRED';end if;
 update public.sbut_users set frozen=true where id=u;update wallet_private.sessions set revoked=true where user_id=u;
 elsif p_action='resolve' and c.kind='help' then
 update public.sbut_help_claims set status='resolved' where id=p_id;
 elsif p_action='reject' then
 if exists(select 1 from wallet_private.claim_links where claim_id=p_id and state='prepared') then raise exception 'RECOVERY_IN_PROGRESS';end if;
 delete from wallet_private.claim_links where claim_id=p_id;update public.sbut_help_claims set status='rejected' where id=p_id;
 elsif p_action='issue_link' then
 if c.kind<>'recovery' or c.status<>'reviewing' or cardinality(c.evidence)=0 or u is null or p_token_hash is null or p_token_hash !~ '^[0-9a-f]{64}$' or not exists(select 1 from wallet_private.access_identity where user_id=u) then raise exception 'REVIEW_REQUIRED';end if;
 if exists(select 1 from wallet_private.claim_links where user_id=u and state='prepared' and expires_at>now()) then raise exception 'RECOVERY_IN_PROGRESS';end if;
 delete from wallet_private.claim_links where user_id=u and claim_id<>p_id and state<>'used';
 insert into wallet_private.claim_links(claim_id,user_id,token_hash) values(p_id,u,p_token_hash) on conflict(claim_id) do update set token_hash=excluded.token_hash,expires_at=now()+interval '15 minutes',state='issued',pin_hash=null,login_hash=null;
 update public.sbut_users set security_hold=true where id=u;update wallet_private.sessions set revoked=true where user_id=u;
 update public.sbut_help_claims set status='link_issued' where id=p_id;
 else raise exception 'INVALID_ACTION';end if;
 update public.sbut_help_claims set target_user=u,reason=p_reason,reviewed_by=auth.uid(),updated_at=now() where id=p_id;
 insert into public.sbut_admin_audit(actor,action,target,reference,reason) values(auth.uid(),'claim_'||p_action,u,p_id::text,p_reason);
 return jsonb_build_object('ok',true);
end $$;
-- Backend-only two-phase recovery: hold remains if Auth update or completion fails.
create or replace function public.sbut_claim_reset_begin(p_hash text,p_login text,p_pin text) returns jsonb language plpgsql security definer set search_path='' as $$
declare r wallet_private.claim_links%rowtype;
begin
 if coalesce(p_pin,'') !~ '^[0-9]{6}$' or coalesce(p_login,'') !~ '^[0-9]{6}$' or p_pin=p_login then raise exception 'INVALID_PIN';end if;
 select * into r from wallet_private.claim_links where token_hash=p_hash for update;
 if not found or r.state='used' or r.expires_at<=now() then raise exception 'INVALID_LINK';end if;
 if r.state='prepared' and (extensions.crypt(p_pin,r.pin_hash) is distinct from r.pin_hash or extensions.crypt(p_login,r.login_hash) is distinct from r.login_hash) then raise exception 'RECOVERY_RETRY_SAME_PIN';end if;
 update wallet_private.claim_links set state='prepared',pin_hash=coalesce(pin_hash,extensions.crypt(p_pin,extensions.gen_salt('bf',12))),login_hash=coalesce(login_hash,extensions.crypt(p_login,extensions.gen_salt('bf',12))) where id=r.id;
 update public.sbut_users set security_hold=true where id=r.user_id;
 return jsonb_build_object('id',r.user_id,'username',(select username from wallet_private.access_identity where user_id=r.user_id));
end $$;
create or replace function public.sbut_claim_reset_finish(p_hash text,p_login text,p_pin text) returns void language plpgsql security definer set search_path='' as $$
declare r wallet_private.claim_links%rowtype;
begin
 select * into r from wallet_private.claim_links where token_hash=p_hash for update;
 if not found or r.state<>'prepared' or extensions.crypt(p_pin,r.pin_hash) is distinct from r.pin_hash or extensions.crypt(p_login,r.login_hash) is distinct from r.login_hash then raise exception 'INVALID_RECOVERY';end if;
 insert into wallet_private.pins(user_id,hash) values(r.user_id,r.pin_hash) on conflict(user_id) do update set hash=excluded.hash,failures=0,locked_until=null;
 update wallet_private.access_identity set login_pin_hash=r.login_hash where user_id=r.user_id;
 update wallet_private.sessions set revoked=true where user_id=r.user_id;
 delete from auth.sessions where user_id=r.user_id;
 update public.sbut_users set security_hold=false where id=r.user_id;
 -- A separately frozen account stays frozen until a reviewed admin decision.
 update wallet_private.claim_links set state='used',pin_hash=null,login_hash=null where id=r.id;
 update public.sbut_help_claims set status='recovered',updated_at=now() where id=r.claim_id;
 insert into public.sbut_admin_audit(actor,action,target,reference,reason) values(null,'claim_reset_completed',r.user_id,r.claim_id::text,'One-use link reset completed; existing wallet sessions revoked');
 insert into public.sbut_notifications(user_id,kind) values(r.user_id,'pin_changed');
end $$;
alter table public.sbut_transactions drop constraint sbut_transactions_kind_check;
alter table public.sbut_transactions add constraint sbut_transactions_kind_check check(kind in ('send','cashout','cashin','funding','bonus','admin_credit','admin_debit','refund','recharge','commission_reversal','heir_settlement'));
create or replace function public.sbut_admin_heir_settle(p_id uuid,p_beneficiary uuid,p_amount bigint,p_reason text) returns uuid language plpgsql security definer set search_path='' as $$
declare c public.sbut_help_claims%rowtype;u uuid;t uuid;
begin
 perform wallet_private.require_admin(true);
 select * into c from public.sbut_help_claims where id=p_id for update;
 if not found then raise exception 'CASE_REQUIRED';end if;
 if c.status='settled' then return c.settlement;end if;
 if c.kind<>'heir' or c.status<>'reviewing' or cardinality(c.evidence)=0 or length(trim(coalesce(p_reason,'')))<20 or p_amount is null or p_amount<=0 then raise exception 'LEGAL_REVIEW_REQUIRED';end if;
 select id into u from public.sbut_users where username=c.target_handle;
 if u is null or u=p_beneficiary then raise exception 'INVALID_BENEFICIARY';end if;
 perform id from public.sbut_users where id in (u,p_beneficiary) order by id for update;
 if not exists(select 1 from public.sbut_users where id=u and frozen and balance>=p_amount) or not exists(select 1 from public.sbut_users where id=p_beneficiary and kyc='approved' and not frozen and not security_hold) then raise exception 'RESTRICTED_OR_INSUFFICIENT';end if;
 -- Explicit reviewed partial/final estate payment; account remains frozen after settlement.
 update public.sbut_users set balance=balance-p_amount,security_hold=true where id=u;
 update public.sbut_users set balance=balance+p_amount where id=p_beneficiary;
 update wallet_private.sessions set revoked=true where user_id=u;
 insert into public.sbut_transactions(request_id,sender,recipient,kind,amount,fee) values(p_id,u,p_beneficiary,'heir_settlement',p_amount,0) returning id into t;
 update public.sbut_help_claims set status='settled',reason=p_reason,target_user=u,beneficiary=p_beneficiary,settlement=t,reviewed_by=auth.uid(),updated_at=now() where id=p_id;
 insert into public.sbut_admin_audit(actor,action,target,reference,amount,reason) values(auth.uid(),'heir_settlement',u,p_id::text,p_amount,p_reason);return t;
end $$;
revoke all on function public.sbut_support_contact(),public.sbut_admin_support(text,text),public.sbut_access_gate(text,int),public.sbut_access_lookup(text),public.sbut_access_register(uuid,text,text,text,text),public.sbut_claim_submit(text,text,text,text,text,text),public.sbut_claim_evidence(uuid,text,text),public.sbut_claim_status(uuid,text),public.sbut_admin_claim(uuid,text,text,text),public.sbut_claim_reset_begin(text,text,text),public.sbut_claim_reset_finish(text,text,text),public.sbut_admin_heir_settle(uuid,uuid,bigint,text) from public,anon,authenticated;
grant execute on function public.sbut_support_contact() to anon,authenticated;
grant execute on function public.sbut_admin_support(text,text),public.sbut_admin_claim(uuid,text,text,text),public.sbut_admin_heir_settle(uuid,uuid,bigint,text) to authenticated;
grant execute on function public.sbut_access_gate(text,int),public.sbut_access_lookup(text),public.sbut_access_register(uuid,text,text,text,text),public.sbut_claim_submit(text,text,text,text,text,text),public.sbut_claim_evidence(uuid,text,text),public.sbut_claim_status(uuid,text),public.sbut_claim_reset_begin(text,text,text),public.sbut_claim_reset_finish(text,text,text) to service_role;
-- Prevent legacy OTP+same-PIN recovery from resetting new username identities.
revoke execute on function public.sbut_recovery_begin(uuid,uuid,uuid,text),public.sbut_recovery_finish(uuid,uuid,uuid,text) from service_role;
-- Preserve prior financial validation while limiting recipient resolution to handles.
alter function public.sbut_prepare(text,bigint,text,text,uuid,uuid) rename to sbut_prepare_pre007;
revoke all on function public.sbut_prepare_pre007(text,bigint,text,text,uuid,uuid) from public,anon,authenticated;
create function public.sbut_prepare(p_recipient text,p_amount bigint,p_pin text,p_kind text,p_request uuid,p_money_request uuid default null) returns jsonb language plpgsql security definer set search_path='' as $$
begin
 if p_recipient is null or lower(trim(leading '@' from p_recipient)) !~ '^[a-z0-9_]{3,24}$' then raise exception 'USERNAME_REQUIRED';end if;
 return public.sbut_prepare_pre007('@'||lower(trim(leading '@' from p_recipient)),p_amount,p_pin,p_kind,p_request,p_money_request);
end $$;
alter function public.sbut_create_request(text,bigint,text) rename to sbut_create_request_pre007;
revoke all on function public.sbut_create_request_pre007(text,bigint,text) from public,anon,authenticated;
create function public.sbut_create_request(p_payer text,p_amount bigint,p_note text) returns uuid language plpgsql security definer set search_path='' as $$
begin
 if p_payer is null or lower(trim(leading '@' from p_payer)) !~ '^[a-z0-9_]{3,24}$' then raise exception 'USERNAME_REQUIRED';end if;
 return public.sbut_create_request_pre007('@'||lower(trim(leading '@' from p_payer)),p_amount,p_note);
end $$;
alter function public.sbut_submit_kyc(text,date,text,text,text,text) rename to sbut_submit_kyc_pre007;
revoke all on function public.sbut_submit_kyc_pre007(text,date,text,text,text,text) from public,anon,authenticated;
create function public.sbut_submit_kyc(p_nid text,p_dob date,p_front text,p_back text,p_selfie text,p_pin text) returns void language plpgsql security definer set search_path='' as $$
begin
 if exists(select 1 from wallet_private.access_identity where user_id=auth.uid() and extensions.crypt(p_pin,login_pin_hash)=login_pin_hash) then raise exception 'PINS_MUST_DIFFER';end if;
 perform public.sbut_submit_kyc_pre007(p_nid,p_dob,p_front,p_back,p_selfie,p_pin);
end $$;
revoke all on function public.sbut_prepare(text,bigint,text,text,uuid,uuid),public.sbut_create_request(text,bigint,text),public.sbut_submit_kyc(text,date,text,text,text,text) from public,anon;
grant execute on function public.sbut_prepare(text,bigint,text,text,uuid,uuid),public.sbut_create_request(text,bigint,text),public.sbut_submit_kyc(text,date,text,text,text,text) to authenticated;

-- SBUT PAY 008: login Password (8-64 characters / at most 64 UTF-8 bytes), transaction PIN stays exactly six digits.
-- Run ONCE after SQL007. Existing six-digit login accounts may still sign in, but new registration/reset uses a password.

create or replace function public.sbut_access_register(p_user uuid,p_username text,p_alias text,p_name text,p_pin text) returns void language plpgsql security definer set search_path='' as $$
begin
 if p_pin is null or length(p_pin) not between 8 and 64 or octet_length(p_pin)>64 or p_pin ~ '[[:cntrl:]]' or p_username !~ '^[a-z0-9_]{3,24}$' or length(trim(p_name)) not between 2 and 100 or p_alias<>p_username||'@identity.sbut.invalid' then raise exception 'INVALID_ACCOUNT';end if;
 insert into public.sbut_users(id,username,name,phone) values(p_user,p_username,trim(p_name),null);
 insert into wallet_private.access_identity values(p_user,p_username,p_alias,extensions.crypt(p_pin,extensions.gen_salt('bf',12))); 
end $$;
create or replace function public.sbut_claim_reset_begin(p_hash text,p_login text,p_pin text) returns jsonb language plpgsql security definer set search_path='' as $$
declare r wallet_private.claim_links%rowtype;
begin
 if coalesce(p_pin,'') !~ '^[0-9]{6}$' or p_login is null or length(p_login) not between 8 and 64 or octet_length(p_login)>64 or p_login ~ '[[:cntrl:]]' or p_pin=p_login then raise exception 'INVALID_PIN';end if;
 select * into r from wallet_private.claim_links where token_hash=p_hash for update;
 if not found or r.state='used' or r.expires_at<=now() then raise exception 'INVALID_LINK';end if;
 if r.state='prepared' and (extensions.crypt(p_pin,r.pin_hash) is distinct from r.pin_hash or extensions.crypt(p_login,r.login_hash) is distinct from r.login_hash) then raise exception 'RECOVERY_RETRY_SAME_PIN';end if;
 update wallet_private.claim_links set state='prepared',pin_hash=coalesce(pin_hash,extensions.crypt(p_pin,extensions.gen_salt('bf',12))),login_hash=coalesce(login_hash,extensions.crypt(p_login,extensions.gen_salt('bf',12))) where id=r.id;
 update public.sbut_users set security_hold=true where id=r.user_id;
 return jsonb_build_object('id',r.user_id,'username',(select username from wallet_private.access_identity where user_id=r.user_id));
end $$;
create or replace function public.sbut_access_readiness() returns int language sql stable security definer set search_path='' as $$ select 8 $$;
revoke all on function public.sbut_access_readiness() from public,anon,authenticated;
grant execute on function public.sbut_access_readiness() to service_role;

commit;


-- SBUT PAY 009: additive KYC, private R2 media and 25-character order IDs.
-- Existing wallet: run after 008 and the registration/session repairs.
BEGIN;
ALTER TABLE public.sbut_users ADD COLUMN IF NOT EXISTS avatar_path text;
CREATE TABLE IF NOT EXISTS public.sbut_consents(user_id uuid PRIMARY KEY REFERENCES auth.users(id),version text NOT NULL,accepted_at timestamptz NOT NULL DEFAULT now());
ALTER TABLE public.sbut_consents ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON public.sbut_consents FROM PUBLIC,anon,authenticated;
CREATE TABLE IF NOT EXISTS wallet_private.pins(user_id uuid PRIMARY KEY REFERENCES public.sbut_users(id),hash text NOT NULL,failures int NOT NULL DEFAULT 0,locked_until timestamptz);
ALTER TABLE wallet_private.pins ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.pins FROM PUBLIC,anon,authenticated;
CREATE TABLE IF NOT EXISTS public.sbut_kyc(user_id uuid PRIMARY KEY REFERENCES public.sbut_users(id),nid text NOT NULL UNIQUE,dob date NOT NULL,front_path text NOT NULL,back_path text NOT NULL,selfie_path text NOT NULL,submitted_at timestamptz DEFAULT now());
ALTER TABLE public.sbut_kyc ENABLE ROW LEVEL SECURITY;
CREATE TABLE IF NOT EXISTS wallet_private.media_objects(object_key text PRIMARY KEY,user_id uuid NOT NULL REFERENCES public.sbut_users(id),kind text NOT NULL CHECK(kind IN ('front','back','selfie','profile')),mime text NOT NULL CHECK(mime IN ('image/jpeg','image/png','image/webp')),bytes int NOT NULL CHECK(bytes BETWEEN 8 AND 5242880),created_at timestamptz NOT NULL DEFAULT now());
ALTER TABLE wallet_private.media_objects ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.media_objects FROM PUBLIC,anon,authenticated;
CREATE OR REPLACE FUNCTION public.sbut_media_register(p_user uuid,p_key text,p_kind text,p_mime text,p_bytes int) RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN
 IF p_key IS NULL OR p_key NOT LIKE p_user::text||'/'||p_kind||'/%' OR p_kind NOT IN ('front','back','selfie','profile') THEN RAISE EXCEPTION 'INVALID_MEDIA';END IF;
 INSERT INTO wallet_private.media_objects(object_key,user_id,kind,mime,bytes) VALUES(p_key,p_user,p_kind,p_mime,p_bytes);
END $$;
CREATE OR REPLACE FUNCTION public.sbut_media_authorize(p_key text) RETURNS boolean LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path='' AS $$
BEGIN
 IF NOT public.sbut_session_active() THEN RETURN false;END IF;
 IF EXISTS(SELECT 1 FROM wallet_private.media_objects WHERE object_key=p_key AND user_id=auth.uid()) THEN RETURN true;END IF;
 IF to_regclass('wallet_private.admins') IS NOT NULL THEN
  RETURN EXISTS(SELECT 1 FROM wallet_private.media_objects WHERE object_key=p_key) AND EXISTS(SELECT 1 FROM wallet_private.admins WHERE user_id=auth.uid());
 END IF;
 RETURN false;
END $$;
CREATE OR REPLACE FUNCTION public.sbut_identity_ready() RETURNS jsonb LANGUAGE sql STABLE SECURITY DEFINER SET search_path='' AS $$
 SELECT jsonb_build_object('ready',public.sbut_session_active(),'version',9,'storage','r2','ocr','browser')
$$;
CREATE OR REPLACE FUNCTION public.sbut_accept_terms(p_version text) RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN
 PERFORM wallet_private.require_session();
 IF p_version IS DISTINCT FROM '2026-10-26-v2' THEN RAISE EXCEPTION 'CONSENT_REQUIRED';END IF;
 INSERT INTO public.sbut_consents(user_id,version) VALUES(auth.uid(),p_version) ON CONFLICT(user_id) DO UPDATE SET version=excluded.version,accepted_at=now();
END $$;
CREATE OR REPLACE FUNCTION public.sbut_confirm_identity_name(p_name text) RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN
 PERFORM wallet_private.require_session();
 IF p_name IS NULL OR length(trim(p_name)) NOT BETWEEN 2 AND 150 THEN RAISE EXCEPTION 'INVALID_NAME';END IF;
 UPDATE public.sbut_users SET name=trim(p_name) WHERE id=auth.uid() AND kyc IN ('draft','rejected');
 IF NOT FOUND THEN RAISE EXCEPTION 'KYC_STATE';END IF;
END $$;
CREATE OR REPLACE FUNCTION public.sbut_submit_identity(p_nid text,p_dob date,p_front text,p_back text,p_selfie text,p_pin text,p_name text) RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE u uuid:=auth.uid(); k text; v text;
BEGIN
 PERFORM wallet_private.require_session();
 PERFORM 1 FROM public.sbut_users WHERE id=u AND kyc IN ('draft','rejected') FOR UPDATE;
 IF NOT FOUND THEN RAISE EXCEPTION 'KYC_STATE';END IF;
 IF NOT EXISTS(SELECT 1 FROM public.sbut_consents WHERE user_id=u AND version='2026-10-26-v2') THEN RAISE EXCEPTION 'CONSENT_REQUIRED';END IF;
 IF p_name IS NULL OR length(trim(p_name)) NOT BETWEEN 2 AND 150 THEN RAISE EXCEPTION 'INVALID_NAME';END IF;
 IF p_pin IS NULL OR p_pin !~ '^[0-9]{6}$' THEN RAISE EXCEPTION 'INVALID_PIN';END IF;
 IF EXISTS(SELECT 1 FROM wallet_private.access_identity WHERE user_id=u AND extensions.crypt(p_pin,login_pin_hash)=login_pin_hash) THEN RAISE EXCEPTION 'PINS_MUST_DIFFER';END IF;
 IF p_nid IS NULL OR p_nid !~ '^([0-9]{10}|[0-9]{13}|[0-9]{17})$' OR p_dob IS NULL OR p_dob>current_date-interval '18 years' OR p_dob<date '1900-01-01' THEN RAISE EXCEPTION 'INVALID_IDENTITY';END IF;
 FOREACH k IN ARRAY ARRAY['front','back','selfie'] LOOP
  v:=CASE k WHEN 'front' THEN p_front WHEN 'back' THEN p_back ELSE p_selfie END;
  IF v IS NULL OR NOT EXISTS(SELECT 1 FROM wallet_private.media_objects WHERE object_key=v AND user_id=u AND kind=k) THEN RAISE EXCEPTION 'UPLOAD_REQUIRED';END IF;
 END LOOP;
 INSERT INTO public.sbut_kyc(user_id,nid,dob,front_path,back_path,selfie_path,submitted_at) VALUES(u,p_nid,p_dob,p_front,p_back,p_selfie,now()) ON CONFLICT(user_id) DO UPDATE SET nid=excluded.nid,dob=excluded.dob,front_path=excluded.front_path,back_path=excluded.back_path,selfie_path=excluded.selfie_path,submitted_at=now();
 INSERT INTO wallet_private.pins(user_id,hash) VALUES(u,extensions.crypt(p_pin,extensions.gen_salt('bf',12))) ON CONFLICT(user_id) DO UPDATE SET hash=excluded.hash,failures=0,locked_until=NULL;
 UPDATE public.sbut_users SET name=trim(p_name),kyc='pending' WHERE id=u;
END $$;
CREATE OR REPLACE FUNCTION public.sbut_set_profile_photo(p_key text) RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN
 PERFORM wallet_private.require_session();
 IF NOT EXISTS(SELECT 1 FROM wallet_private.media_objects WHERE object_key=p_key AND user_id=auth.uid() AND kind='profile') THEN RAISE EXCEPTION 'INVALID_MEDIA';END IF;
 UPDATE public.sbut_users SET avatar_path=p_key WHERE id=auth.uid();
END $$;
CREATE OR REPLACE FUNCTION public.sbut_identity_admin() RETURNS boolean LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path='' AS $$
BEGIN
 IF NOT public.sbut_session_active() OR to_regclass('wallet_private.admins') IS NULL THEN RETURN false;END IF;
 RETURN EXISTS(SELECT 1 FROM wallet_private.admins WHERE user_id=auth.uid());
END $$;
REVOKE ALL ON FUNCTION public.sbut_identity_admin() FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.sbut_identity_admin() TO authenticated;
DROP POLICY IF EXISTS identity9_kyc_read ON public.sbut_kyc;
CREATE POLICY identity9_kyc_read ON public.sbut_kyc FOR SELECT TO authenticated USING((user_id=auth.uid() AND public.sbut_session_active()) OR public.sbut_identity_admin());
DROP POLICY IF EXISTS identity9_consent_read ON public.sbut_consents;
CREATE POLICY identity9_consent_read ON public.sbut_consents FOR SELECT TO authenticated USING(user_id=auth.uid() AND public.sbut_session_active());
GRANT SELECT ON public.sbut_consents,public.sbut_kyc TO authenticated;
-- IDs are monotonic across ALL registered order types. Gaps are intentional.
-- A sequence + UNIQUE constraint guarantees uniqueness in this database.
CREATE SEQUENCE IF NOT EXISTS wallet_private.order_serial AS bigint NO CYCLE;
CREATE TABLE IF NOT EXISTS wallet_private.order_ids(source_kind text NOT NULL,source_id uuid NOT NULL,order_id text NOT NULL UNIQUE CHECK(order_id ~ '^SB[0-9]{23}$'),created_at timestamptz NOT NULL DEFAULT now(),PRIMARY KEY(source_kind,source_id));
ALTER TABLE wallet_private.order_ids ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.order_ids FROM PUBLIC,anon,authenticated;
REVOKE ALL ON SEQUENCE wallet_private.order_serial FROM PUBLIC,anon,authenticated;
CREATE OR REPLACE FUNCTION wallet_private.assign_order_id() RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN
 INSERT INTO wallet_private.order_ids(source_kind,source_id,order_id) VALUES(TG_TABLE_NAME,NEW.id,'SB'||lpad(nextval('wallet_private.order_serial')::text,23,'0')) ON CONFLICT(source_kind,source_id) DO NOTHING;
 RETURN NEW;
END $$;
DO $$
DECLARE t text;
BEGIN
 FOREACH t IN ARRAY ARRAY['sbut_transactions','sbut_recharges','sbut_requests'] LOOP
  IF to_regclass('public.'||t) IS NOT NULL THEN
   EXECUTE format('DROP TRIGGER IF EXISTS sbut_assign_order_id ON public.%I',t);
   EXECUTE format('CREATE TRIGGER sbut_assign_order_id AFTER INSERT ON public.%I FOR EACH ROW EXECUTE FUNCTION wallet_private.assign_order_id()',t);
   EXECUTE format('INSERT INTO wallet_private.order_ids(source_kind,source_id,order_id) SELECT %L,id,''SB''||lpad(nextval(''wallet_private.order_serial'')::text,23,''0'') FROM public.%I s WHERE NOT EXISTS(SELECT 1 FROM wallet_private.order_ids o WHERE o.source_kind=%L AND o.source_id=s.id) ORDER BY created_at,id ON CONFLICT(source_kind,source_id) DO NOTHING',t,t,t);
  END IF;
 END LOOP;
END $$;
CREATE OR REPLACE FUNCTION public.sbut_order_ids(p_ids uuid[]) RETURNS jsonb LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path='' AS $$
DECLARE r jsonb;
BEGIN
 PERFORM wallet_private.require_session();
 IF coalesce(cardinality(p_ids),0)>200 THEN RAISE EXCEPTION 'TOO_MANY';END IF;
 SELECT coalesce(jsonb_object_agg(o.source_id::text,o.order_id),'{}'::jsonb) INTO r FROM wallet_private.order_ids o JOIN public.sbut_transactions t ON o.source_kind='sbut_transactions' AND t.id=o.source_id WHERE o.source_id=ANY(p_ids) AND (t.sender=auth.uid() OR t.recipient=auth.uid());
 RETURN r;
END $$;
REVOKE ALL ON FUNCTION public.sbut_media_register(uuid,text,text,text,int),public.sbut_media_authorize(text),public.sbut_identity_ready(),public.sbut_submit_identity(text,date,text,text,text,text,text),public.sbut_set_profile_photo(text),public.sbut_order_ids(uuid[]),public.sbut_accept_terms(text),public.sbut_confirm_identity_name(text),wallet_private.assign_order_id() FROM PUBLIC,anon,authenticated;
GRANT EXECUTE ON FUNCTION public.sbut_media_register(uuid,text,text,text,int) TO service_role;
GRANT EXECUTE ON FUNCTION public.sbut_media_authorize(text),public.sbut_identity_ready(),public.sbut_submit_identity(text,date,text,text,text,text,text),public.sbut_set_profile_photo(text),public.sbut_order_ids(uuid[]),public.sbut_accept_terms(text),public.sbut_confirm_identity_name(text) TO authenticated;
NOTIFY pgrst,'reload schema';
COMMIT;
SELECT to_regprocedure('public.sbut_submit_identity(text,date,text,text,text,text,text)') IS NOT NULL AS kyc_ready,to_regclass('wallet_private.media_objects') IS NOT NULL AS media_ready,to_regclass('wallet_private.order_ids') IS NOT NULL AS orders_ready;


-- SBUT PAY 010. Run once after 009. Automatic ACCOUNT approval, NOT identity authenticity verification.
-- Owner requested 180-second admission. This enables the existing approved-wallet permissions.
-- No welcome reward, balance credit, merchant role or admin role is granted by this migration.
BEGIN;
CREATE TABLE IF NOT EXISTS wallet_private.auto_admissions (
 user_id uuid PRIMARY KEY REFERENCES public.sbut_users(id),
 submitted_at timestamptz NOT NULL,
 approved_at timestamptz NOT NULL DEFAULT now(),
 method text NOT NULL DEFAULT 'automatic_submission_checks_only'
);
ALTER TABLE wallet_private.auto_admissions ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.auto_admissions FROM PUBLIC,anon,authenticated;
CREATE OR REPLACE FUNCTION wallet_private.auto_admit(p_user uuid DEFAULT NULL)
RETURNS int LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE r record; n int:=0;
BEGIN
 FOR r IN
  SELECT u.id,k.submitted_at FROM public.sbut_users u
  JOIN public.sbut_kyc k ON k.user_id=u.id
  WHERE u.kyc='pending' AND NOT u.frozen
   AND (p_user IS NULL OR u.id=p_user)
   AND k.submitted_at<=now()-interval '180 seconds'
   AND EXISTS(SELECT 1 FROM wallet_private.pins p WHERE p.user_id=u.id)
   AND EXISTS(SELECT 1 FROM public.sbut_consents c WHERE c.user_id=u.id AND c.version='2026-10-26-v2')
   AND k.nid ~ '^([0-9]{10}|[0-9]{13}|[0-9]{17})$'
   AND k.dob<=current_date-interval '18 years' AND k.dob>=date '1900-01-01'
   AND EXISTS(SELECT 1 FROM wallet_private.media_objects m WHERE m.user_id=u.id AND m.kind='front' AND m.object_key=k.front_path)
   AND EXISTS(SELECT 1 FROM wallet_private.media_objects m WHERE m.user_id=u.id AND m.kind='back' AND m.object_key=k.back_path)
   AND EXISTS(SELECT 1 FROM wallet_private.media_objects m WHERE m.user_id=u.id AND m.kind='selfie' AND m.object_key=k.selfie_path)
  ORDER BY k.submitted_at LIMIT 100 FOR UPDATE OF u SKIP LOCKED
 LOOP
  UPDATE public.sbut_users SET kyc='approved' WHERE id=r.id AND kyc='pending';
  INSERT INTO wallet_private.auto_admissions(user_id,submitted_at)
   VALUES(r.id,r.submitted_at) ON CONFLICT(user_id) DO NOTHING;
  n:=n+1;
 END LOOP;
 RETURN n;
END $$;
REVOKE ALL ON FUNCTION wallet_private.auto_admit(uuid) FROM PUBLIC,anon,authenticated,service_role;
CREATE OR REPLACE FUNCTION public.sbut_review_status()
RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE result jsonb;
BEGIN
 PERFORM wallet_private.require_session();
 PERFORM wallet_private.auto_admit(auth.uid());
 SELECT jsonb_build_object('state',u.kyc,'frozen',u.frozen,
  'server_now',now(),'ready_at',k.submitted_at+interval '180 seconds',
  'automatic',EXISTS(SELECT 1 FROM wallet_private.auto_admissions a WHERE a.user_id=u.id))
 INTO result FROM public.sbut_users u LEFT JOIN public.sbut_kyc k ON k.user_id=u.id WHERE u.id=auth.uid();
 IF result IS NULL THEN RAISE EXCEPTION 'ACCOUNT_NOT_FOUND';END IF;
 RETURN result;
END $$;
REVOKE ALL ON FUNCTION public.sbut_review_status() FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.sbut_review_status() TO authenticated;
-- Re-running this installs/updates the SAME job, without duplicating it.
SELECT cron.schedule('sbut-auto-account-approval','* * * * *',
 'SELECT wallet_private.auto_admit(NULL::uuid);');
NOTIFY pgrst,'reload schema';
COMMIT;
SELECT true AS auto_approval_installed,180 AS minimum_wait_seconds;


-- SBUT PAY 011: one NID -> one application/account; resume the same provisional application.
-- Requires 009 and 010. Authentication record is provisional; account access requires approval.
BEGIN;
CREATE UNIQUE INDEX IF NOT EXISTS sbut_kyc_nid_single_account ON public.sbut_kyc(nid);
CREATE OR REPLACE FUNCTION public.sbut_submit_identity(p_nid text,p_dob date,p_front text,p_back text,p_selfie text,p_pin text,p_name text) RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE u uuid:=auth.uid(); k text; v text;
BEGIN
 PERFORM wallet_private.require_session();
 PERFORM 1 FROM public.sbut_users WHERE id=u AND kyc IN ('draft','rejected','pending') FOR UPDATE;
 IF NOT FOUND THEN RAISE EXCEPTION 'KYC_STATE';END IF;
 IF NOT EXISTS(SELECT 1 FROM public.sbut_consents WHERE user_id=u AND version='2026-10-26-v2') THEN RAISE EXCEPTION 'CONSENT_REQUIRED';END IF;
 IF p_name IS NULL OR length(trim(p_name)) NOT BETWEEN 2 AND 150 THEN RAISE EXCEPTION 'INVALID_NAME';END IF;
 IF p_pin IS NULL OR p_pin !~ '^[0-9]{6}$' THEN RAISE EXCEPTION 'INVALID_PIN';END IF;
 IF EXISTS(SELECT 1 FROM wallet_private.access_identity WHERE user_id=u AND extensions.crypt(p_pin,login_pin_hash)=login_pin_hash) THEN RAISE EXCEPTION 'PINS_MUST_DIFFER';END IF;
 IF p_nid IS NULL OR p_nid !~ '^([0-9]{10}|[0-9]{13}|[0-9]{17})$' OR p_dob IS NULL OR p_dob>current_date-interval '18 years' OR p_dob<date '1900-01-01' THEN RAISE EXCEPTION 'INVALID_IDENTITY';END IF;
 IF EXISTS(SELECT 1 FROM public.sbut_kyc WHERE nid=p_nid AND user_id<>u) THEN RAISE EXCEPTION 'NID_ALREADY_USED';END IF;
 FOREACH k IN ARRAY ARRAY['front','back','selfie'] LOOP
  v:=CASE k WHEN 'front' THEN p_front WHEN 'back' THEN p_back ELSE p_selfie END;
  IF v IS NULL OR NOT EXISTS(SELECT 1 FROM wallet_private.media_objects WHERE object_key=v AND user_id=u AND kind=k) THEN RAISE EXCEPTION 'UPLOAD_REQUIRED';END IF;
 END LOOP;
 INSERT INTO public.sbut_kyc(user_id,nid,dob,front_path,back_path,selfie_path,submitted_at) VALUES(u,p_nid,p_dob,p_front,p_back,p_selfie,now()) ON CONFLICT(user_id) DO UPDATE SET nid=excluded.nid,dob=excluded.dob,front_path=excluded.front_path,back_path=excluded.back_path,selfie_path=excluded.selfie_path,submitted_at=now();
 INSERT INTO wallet_private.pins(user_id,hash) VALUES(u,extensions.crypt(p_pin,extensions.gen_salt('bf',12))) ON CONFLICT(user_id) DO UPDATE SET hash=excluded.hash,failures=0,locked_until=NULL;
 UPDATE public.sbut_users SET name=trim(p_name),kyc='pending' WHERE id=u;
END $$;
CREATE OR REPLACE FUNCTION public.sbut_resume_application() RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE u uuid:=auth.uid(); state text; result jsonb;
BEGIN
 PERFORM wallet_private.require_session();
 SELECT kyc INTO state FROM public.sbut_users WHERE id=u AND NOT frozen FOR UPDATE;
 IF state IS NULL OR state NOT IN ('draft','pending','rejected') THEN RAISE EXCEPTION 'APPLICATION_NOT_EDITABLE';END IF;
 SELECT jsonb_build_object('name',a.name,'username',a.username,'nid',k.nid,'dob',k.dob,
  'front',k.front_path,'back',k.back_path,'selfie',k.selfie_path)
 INTO result FROM public.sbut_users a JOIN public.sbut_kyc k ON k.user_id=a.id WHERE a.id=u;
 -- Draft pauses the approval job until the user submits again. Row lock prevents approval/edit races.
 UPDATE public.sbut_users SET kyc='draft' WHERE id=u;
 RETURN result;
END $$;
REVOKE ALL ON FUNCTION public.sbut_resume_application() FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.sbut_resume_application() TO authenticated;
NOTIFY pgrst,'reload schema';
COMMIT;
SELECT true AS one_nid_one_account,true AS same_application_retry;


-- SBUT PAY 012: automatic account admission + separate manual document review queue.
-- Requires 009/010/011. This does NOT perform NID/face authenticity verification.
BEGIN;
CREATE TABLE IF NOT EXISTS wallet_private.setup12_once(key text PRIMARY KEY,done_at timestamptz DEFAULT now());
ALTER TABLE wallet_private.setup12_once ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.setup12_once FROM PUBLIC,anon,authenticated;
CREATE TABLE IF NOT EXISTS public.sbut_identity_reviews(
 user_id uuid PRIMARY KEY REFERENCES public.sbut_users(id),
 submitted_at timestamptz NOT NULL,
 status text NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','reviewed_approved','reviewed_rejected')),
 reviewed_at timestamptz,reviewer uuid REFERENCES auth.users(id),reason text
);
ALTER TABLE public.sbut_identity_reviews ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON public.sbut_identity_reviews FROM PUBLIC,anon,authenticated;
CREATE OR REPLACE FUNCTION wallet_private.queue_identity12() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN
 INSERT INTO public.sbut_identity_reviews(user_id,submitted_at) VALUES(NEW.user_id,NEW.submitted_at)
 ON CONFLICT(user_id) DO UPDATE SET submitted_at=excluded.submitted_at,status='pending',reviewed_at=NULL,reviewer=NULL,reason=NULL;
 RETURN NEW;
END $$;
REVOKE ALL ON FUNCTION wallet_private.queue_identity12() FROM PUBLIC,anon,authenticated;
DROP TRIGGER IF EXISTS queue_identity12 ON public.sbut_kyc;
CREATE TRIGGER queue_identity12 AFTER INSERT OR UPDATE ON public.sbut_kyc FOR EACH ROW EXECUTE FUNCTION wallet_private.queue_identity12();
INSERT INTO public.sbut_identity_reviews(user_id,submitted_at)
 SELECT user_id,submitted_at FROM public.sbut_kyc WHERE submitted_at IS NOT NULL ON CONFLICT(user_id) DO NOTHING;
CREATE OR REPLACE FUNCTION public.sbut_manual_review_queue(p_offset int DEFAULT 0) RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE result jsonb;
BEGIN
 PERFORM wallet_private.require_admin();
 SELECT coalesce(jsonb_agg(to_jsonb(r)),'[]'::jsonb) INTO result FROM(
 SELECT q.user_id,u.name,u.username,u.kyc,u.frozen,q.submitted_at
 FROM public.sbut_identity_reviews q JOIN public.sbut_users u ON u.id=q.user_id
 WHERE q.status='pending' ORDER BY q.submitted_at,q.user_id LIMIT 50 OFFSET greatest(0,p_offset)
 ) r;
 RETURN result;
END $$;
CREATE OR REPLACE FUNCTION public.sbut_manual_review_batch(p_users uuid[],p_decision text,p_reason text) RETURNS int
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE r record;n int:=0;
BEGIN
 PERFORM wallet_private.require_admin();
 IF cardinality(p_users) IS NULL OR cardinality(p_users) NOT BETWEEN 1 AND 50 OR p_decision NOT IN ('reviewed_approved','reviewed_rejected') OR p_reason IS NULL OR length(trim(p_reason))<5 THEN RAISE EXCEPTION 'INVALID_REVIEW';END IF;
 FOR r IN SELECT user_id FROM public.sbut_identity_reviews WHERE user_id=ANY(p_users) AND status='pending' ORDER BY user_id FOR UPDATE LOOP
  UPDATE public.sbut_identity_reviews SET status=p_decision,reviewed_at=now(),reviewer=auth.uid(),reason=trim(p_reason) WHERE user_id=r.user_id;
  IF p_decision='reviewed_rejected' THEN
   UPDATE public.sbut_users SET frozen=true WHERE id=r.user_id;
   UPDATE wallet_private.sessions SET revoked=true WHERE user_id=r.user_id;
  END IF;
  INSERT INTO public.sbut_admin_audit(actor,action,target,reason) VALUES(auth.uid(),p_decision,r.user_id,trim(p_reason));
  n:=n+1;
 END LOOP;
 RETURN n;
END $$;
REVOKE ALL ON FUNCTION public.sbut_manual_review_queue(int),public.sbut_manual_review_batch(uuid[],text,text) FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.sbut_manual_review_queue(int),public.sbut_manual_review_batch(uuid[],text,text) TO authenticated;
CREATE OR REPLACE FUNCTION wallet_private.admit_existing12(p_user uuid DEFAULT NULL)
RETURNS int LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE r record; n int:=0;
BEGIN
 FOR r IN
  SELECT u.id,k.submitted_at FROM public.sbut_users u
  JOIN public.sbut_kyc k ON k.user_id=u.id
  WHERE u.kyc='pending' AND NOT u.frozen
   AND (p_user IS NULL OR u.id=p_user)
   AND EXISTS(SELECT 1 FROM wallet_private.pins p WHERE p.user_id=u.id)
   AND EXISTS(SELECT 1 FROM public.sbut_consents c WHERE c.user_id=u.id AND c.version='2026-10-26-v2')
   AND k.nid ~ '^([0-9]{10}|[0-9]{13}|[0-9]{17})$'
   AND k.dob<=current_date-interval '18 years' AND k.dob>=date '1900-01-01'
   AND EXISTS(SELECT 1 FROM wallet_private.media_objects m WHERE m.user_id=u.id AND m.kind='front' AND m.object_key=k.front_path)
   AND EXISTS(SELECT 1 FROM wallet_private.media_objects m WHERE m.user_id=u.id AND m.kind='back' AND m.object_key=k.back_path)
   AND EXISTS(SELECT 1 FROM wallet_private.media_objects m WHERE m.user_id=u.id AND m.kind='selfie' AND m.object_key=k.selfie_path)
  ORDER BY k.submitted_at LIMIT 100 FOR UPDATE OF u SKIP LOCKED
 LOOP
  UPDATE public.sbut_users SET kyc='approved' WHERE id=r.id AND kyc='pending';
  INSERT INTO wallet_private.auto_admissions(user_id,submitted_at)
   VALUES(r.id,r.submitted_at) ON CONFLICT(user_id) DO NOTHING;
  n:=n+1;
 END LOOP;
 RETURN n;
END $$;

REVOKE ALL ON FUNCTION wallet_private.admit_existing12(uuid) FROM PUBLIC,anon,authenticated,service_role;
DO $$ BEGIN
 IF NOT EXISTS(SELECT 1 FROM wallet_private.setup12_once WHERE key='initial_pending_admission') THEN
  PERFORM wallet_private.admit_existing12(NULL::uuid);
  INSERT INTO wallet_private.setup12_once(key) VALUES('initial_pending_admission');
 END IF;
END $$;
DROP FUNCTION wallet_private.admit_existing12(uuid);
NOTIFY pgrst,'reload schema';
COMMIT;
SELECT u.username,u.kyc AS account_status,q.status AS manual_review
FROM public.sbut_users u JOIN public.sbut_identity_reviews q ON q.user_id=u.id
ORDER BY q.submitted_at DESC LIMIT 20;


-- SBUT PAY 013: repair ACCOUNT_RECORD / 42501 read privileges.
-- Run as database owner. Read-only grants; session + owner RLS stays required.
BEGIN;
GRANT USAGE ON SCHEMA public TO authenticated;
ALTER TABLE public.sbut_users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.sbut_transactions ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON public.sbut_users,public.sbut_transactions FROM anon;
GRANT SELECT ON public.sbut_users,public.sbut_transactions TO authenticated;
GRANT EXECUTE ON FUNCTION public.sbut_session_active() TO authenticated;
DO $$ BEGIN
 IF to_regprocedure('public.sbut_is_admin()') IS NOT NULL THEN
  GRANT EXECUTE ON FUNCTION public.sbut_is_admin() TO authenticated;
 END IF;
END $$;
DROP POLICY IF EXISTS self_read ON public.sbut_users;
CREATE POLICY self_read ON public.sbut_users FOR SELECT TO authenticated
 USING(id=auth.uid() AND public.sbut_session_active());
DROP POLICY IF EXISTS ledger_read ON public.sbut_transactions;
CREATE POLICY ledger_read ON public.sbut_transactions FOR SELECT TO authenticated
 USING((sender=auth.uid() OR recipient=auth.uid()) AND public.sbut_session_active());
NOTIFY pgrst,'reload schema';
COMMIT;
SELECT
 has_table_privilege('authenticated','public.sbut_users','SELECT') AS account_read,
 has_table_privilege('authenticated','public.sbut_transactions','SELECT') AS ledger_read,
 has_function_privilege('authenticated','public.sbut_session_active()','EXECUTE') AS session_check,
 (SELECT relrowsecurity FROM pg_class WHERE oid='public.sbut_users'::regclass) AS account_rls;


-- SBUT PAY 014: required signup email + username/email login + server-enforced wallet PIN gate.
-- Requires 009-013. Existing aliases and password HMAC inputs are preserved.
BEGIN;
CREATE TABLE IF NOT EXISTS wallet_private.login_emails(
 user_id uuid PRIMARY KEY REFERENCES public.sbut_users(id),
 email text NOT NULL UNIQUE CHECK(email=lower(trim(email))),
 email_verified boolean NOT NULL DEFAULT false,
 CHECK(length(email)<=254 AND email ~ '^[^[:space:]@]+@[^[:space:]@]+[.][^[:space:]@]+$')
);
ALTER TABLE wallet_private.login_emails ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.login_emails FROM PUBLIC,anon,authenticated;
CREATE OR REPLACE FUNCTION public.sbut_access_register_email(p_user uuid,p_username text,p_alias text,p_name text,p_pin text,p_email text)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN
 IF p_email IS NULL OR length(trim(p_email))>254 OR trim(p_email) !~ '^[^[:space:]@]+@[^[:space:]@]+[.][^[:space:]@]+$' THEN RAISE EXCEPTION 'INVALID_EMAIL';END IF;
 PERFORM public.sbut_access_register(p_user,p_username,p_alias,p_name,p_pin);
 INSERT INTO wallet_private.login_emails(user_id,email) VALUES(p_user,lower(trim(p_email)));
END $$;
CREATE OR REPLACE FUNCTION public.sbut_login_lookup(p_identifier text) RETURNS jsonb
LANGUAGE sql STABLE SECURITY DEFINER SET search_path='' AS $$
 SELECT jsonb_build_object('id',a.user_id,'username',a.username,'alias',a.alias)
 FROM wallet_private.access_identity a LEFT JOIN wallet_private.login_emails e ON e.user_id=a.user_id
 WHERE a.username=lower(trim(leading '@' from trim(p_identifier))) OR e.email=lower(trim(p_identifier)) LIMIT 1
$$;
REVOKE ALL ON FUNCTION public.sbut_access_register_email(uuid,text,text,text,text,text),public.sbut_login_lookup(text) FROM PUBLIC,anon,authenticated;
GRANT EXECUTE ON FUNCTION public.sbut_access_register_email(uuid,text,text,text,text,text),public.sbut_login_lookup(text) TO service_role;
ALTER TABLE wallet_private.sessions ADD COLUMN IF NOT EXISTS pin_verified_at timestamptz;
CREATE OR REPLACE FUNCTION wallet_private.access_session_active14() RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER SET search_path='' AS $$
 SELECT auth.uid() IS NOT NULL AND EXISTS(
 SELECT 1 FROM wallet_private.sessions s JOIN auth.sessions a ON a.id=s.id AND a.user_id=s.user_id
 WHERE s.id=nullif(auth.jwt()->>'session_id','')::uuid AND s.user_id=auth.uid() AND NOT s.revoked AND s.expires_at>now())
 AND NOT EXISTS(SELECT 1 FROM public.sbut_users WHERE id=auth.uid() AND frozen)
$$;
REVOKE ALL ON FUNCTION wallet_private.access_session_active14() FROM PUBLIC,anon,authenticated;
CREATE OR REPLACE FUNCTION public.sbut_session_active() RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER SET search_path='' AS $$
 SELECT wallet_private.access_session_active14() AND (
 NOT EXISTS(SELECT 1 FROM public.sbut_users WHERE id=auth.uid() AND kyc='approved')
 OR EXISTS(SELECT 1 FROM wallet_private.sessions WHERE id=nullif(auth.jwt()->>'session_id','')::uuid AND user_id=auth.uid() AND pin_verified_at IS NOT NULL))
$$;
CREATE OR REPLACE FUNCTION wallet_private.require_session() RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN
 IF NOT public.sbut_session_active() THEN RAISE EXCEPTION 'SESSION_LOCKED';END IF;
END $$;
CREATE OR REPLACE FUNCTION public.sbut_login_state() RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE r public.sbut_users%rowtype;
BEGIN
 IF NOT wallet_private.access_session_active14() THEN RAISE EXCEPTION 'SESSION_LOCKED';END IF;
 SELECT * INTO r FROM public.sbut_users WHERE id=auth.uid();
 IF NOT FOUND THEN RETURN jsonb_build_object('state','no_wallet','pin_required',false);END IF;
 RETURN jsonb_build_object('state',r.kyc,'name',r.name,'username',r.username,
 'pin_required',r.kyc='approved','pin_enrolled',EXISTS(SELECT 1 FROM wallet_private.pins WHERE user_id=r.id));
END $$;
CREATE OR REPLACE FUNCTION public.sbut_unlock_wallet(p_pin text) RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE err text;
BEGIN
 IF NOT wallet_private.access_session_active14() THEN RAISE EXCEPTION 'SESSION_LOCKED';END IF;
 IF NOT EXISTS(SELECT 1 FROM public.sbut_users WHERE id=auth.uid() AND kyc='approved' AND NOT frozen) THEN RAISE EXCEPTION 'APPROVAL_REQUIRED';END IF;
 err:=wallet_private.check_pin(p_pin);
 IF err IS NOT NULL THEN RETURN jsonb_build_object('ok',false,'error',err);END IF;
 UPDATE wallet_private.sessions SET pin_verified_at=now() WHERE id=nullif(auth.jwt()->>'session_id','')::uuid AND user_id=auth.uid() AND NOT revoked;
 RETURN jsonb_build_object('ok',true);
END $$;
-- Status remains readable with a provisional access session even if the cron has just approved it.
CREATE OR REPLACE FUNCTION public.sbut_review_status() RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE result jsonb;
BEGIN
 IF NOT wallet_private.access_session_active14() THEN RAISE EXCEPTION 'SESSION_LOCKED';END IF;
 PERFORM wallet_private.auto_admit(auth.uid());
 SELECT jsonb_build_object('state',u.kyc,'frozen',u.frozen,'server_now',now(),'ready_at',k.submitted_at+interval '180 seconds',
 'automatic',EXISTS(SELECT 1 FROM wallet_private.auto_admissions a WHERE a.user_id=u.id)) INTO result
 FROM public.sbut_users u LEFT JOIN public.sbut_kyc k ON k.user_id=u.id WHERE u.id=auth.uid();
 RETURN result;
END $$;
REVOKE ALL ON FUNCTION public.sbut_login_state(),public.sbut_unlock_wallet(text) FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.sbut_login_state(),public.sbut_unlock_wallet(text) TO authenticated;
REVOKE ALL ON FUNCTION wallet_private.require_session() FROM PUBLIC,anon,authenticated;
CREATE OR REPLACE FUNCTION public.sbut_access_readiness() RETURNS int LANGUAGE sql STABLE SECURITY DEFINER SET search_path='' AS $$ SELECT 14 $$;
NOTIFY pgrst,'reload schema';
COMMIT;
SELECT true AS email_login_ready,true AS server_pin_gate_ready;

-- PAYMENT FLOW UPDATE 016
-- SBUT PAY 016: repair operations endpoints + immutable payment orders, PIN, cancellable server hold.
-- Requires SQL 014. No funds are minted; rewards are funded from treasury.promotion.
BEGIN;
ALTER TABLE public.sbut_users ADD COLUMN IF NOT EXISTS security_hold boolean NOT NULL DEFAULT false;
ALTER TABLE public.sbut_users ADD COLUMN IF NOT EXISTS bonus_paid boolean NOT NULL DEFAULT false;
create or replace function public.sbut_is_admin() returns boolean language sql stable security definer set search_path='' as $$ select public.sbut_session_active() and exists(select 1 from wallet_private.admins where user_id=auth.uid()) $$;
create or replace function wallet_private.require_admin(p_founder boolean default false) returns void language plpgsql security definer set search_path='' as $$
begin
 perform wallet_private.require_session();
 if not exists(select 1 from wallet_private.admins where user_id=auth.uid() and (not p_founder or kind='founder')) then raise exception 'ADMIN_REQUIRED'; end if;
end $$;
create table if not exists public.sbut_requests(id uuid primary key default gen_random_uuid(),requester uuid not null references public.sbut_users,payer uuid not null references public.sbut_users,amount bigint not null check(amount>=1000 and amount<=1000000000000),note text not null default '' check(length(note)<=200),status text not null default 'pending' check(status in ('pending','paid','declined','cancelled')),transaction_id uuid references public.sbut_transactions,created_at timestamptz not null default now(),check(requester<>payer));
create table if not exists wallet_private.confirmations(id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users,session_id uuid not null,recipient text not null,amount bigint not null,kind text not null,request_id uuid not null,money_request uuid references public.sbut_requests,ready_at timestamptz not null default now()+interval '5 seconds',expires_at timestamptz not null default now()+interval '2 minutes',transaction_id uuid references public.sbut_transactions,unique(user_id,request_id));
ALTER TABLE wallet_private.confirmations ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}';
create table if not exists wallet_private.limit_rules(user_id uuid primary key references auth.users,per_transaction bigint not null check(per_transaction>=1000),daily bigint not null check(daily>=per_transaction),monthly bigint not null check(monthly>=daily),daily_count int not null check(daily_count between 1 and 10000));
create table if not exists wallet_private.defaults(id int primary key check(id=1),per_transaction bigint not null,daily bigint not null,monthly bigint not null,daily_count int not null,risk_amount bigint not null);
insert into wallet_private.defaults values(1,1000000000000,1000000000000,30000000000000,100,1000000) on conflict(id) do nothing;
create table if not exists public.sbut_notifications(id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users,kind text not null,data jsonb not null default '{}',read_at timestamptz,created_at timestamptz not null default now());
create table if not exists wallet_private.devices(user_id uuid not null references auth.users,device_id uuid not null,label text not null,first_seen timestamptz not null default now(),last_seen timestamptz not null default now(),primary key(user_id,device_id));
create table if not exists public.sbut_disputes(id uuid primary key default gen_random_uuid(),transaction_id uuid not null unique references public.sbut_transactions,complainant uuid not null references public.sbut_users,amount bigint not null check(amount>0),reason text not null check(length(reason) between 10 and 1000),status text not null default 'open' check(status in ('open','refunded','rejected')),resolution text,refund_transaction uuid references public.sbut_transactions,created_at timestamptz not null default now(),resolved_at timestamptz);
create table if not exists wallet_private.refunds(original_transaction uuid not null references public.sbut_transactions,refund_transaction uuid unique not null references public.sbut_transactions,amount bigint not null check(amount>0),primary key(original_transaction,refund_transaction));
create table if not exists wallet_private.commission_reversals(original_transaction uuid not null references public.sbut_transactions,refund_transaction uuid not null unique references public.sbut_transactions,reversal_transaction uuid not null unique references public.sbut_transactions,amount bigint not null check(amount>0));
create table if not exists public.sbut_cash_reports(user_id uuid not null references public.sbut_users,day date not null,cash_received bigint not null check(cash_received>=0),cash_paid bigint not null check(cash_paid>=0),note text not null default '',updated_at timestamptz not null default now(),primary key(user_id,day));
create table if not exists wallet_private.recovery_attempts(user_id uuid not null references auth.users,at timestamptz not null default now());
create table if not exists wallet_private.recovery_tokens(id uuid primary key default gen_random_uuid(),user_id uuid not null references auth.users,session_id uuid not null,status text not null default 'authorized' check(status in ('authorized','prepared','completed')),new_pin_hash text,expires_at timestamptz not null default now()+interval '10 minutes',created_at timestamptz not null default now());
create table if not exists wallet_private.push_subscriptions(endpoint text primary key,user_id uuid not null references auth.users,subscription jsonb not null,created_at timestamptz not null default now());
create table if not exists wallet_private.push_outbox(notification_id uuid primary key references public.sbut_notifications,attempts int not null default 0,state text not null default 'pending' check(state in ('pending','leased','sent','failed')),available_at timestamptz not null default now(),lease_until timestamptz);
create table if not exists wallet_private.integrations(name text primary key,enabled boolean not null default false,public_config jsonb not null default '{}');
insert into wallet_private.integrations(name) values('push'),('recharge'),('kyc') on conflict(name) do nothing;
ALTER TABLE public.sbut_transactions DROP CONSTRAINT IF EXISTS sbut_transactions_kind_check;
ALTER TABLE public.sbut_transactions ADD CONSTRAINT sbut_transactions_kind_check CHECK(kind IN ('send','cashout','cashin','funding','bonus','admin_credit','admin_debit','refund','recharge','commission_reversal'));
create or replace function wallet_private.check_operational_session() returns void language plpgsql security definer set search_path='' as $$
begin
 perform wallet_private.require_session();if exists(select 1 from public.sbut_users where id=auth.uid() and security_hold) then raise exception 'SECURITY_HOLD';end if;
end $$;
create or replace function wallet_private.enforce_limits() returns trigger language plpgsql security definer set search_path='' as $$
declare r record; spent_day numeric;spent_month numeric;n int;start_day timestamptz:=(date_trunc('day',now() at time zone 'Asia/Dhaka') at time zone 'Asia/Dhaka');start_month timestamptz:=(date_trunc('month',now() at time zone 'Asia/Dhaka') at time zone 'Asia/Dhaka');
begin
 if new.kind not in ('send','cashout','cashin','recharge') then return new;end if;
 perform id from public.sbut_users where id=new.sender for update;
 if not exists(select 1 from public.sbut_consents where user_id=new.sender and version='2026-10-26-v2') then raise exception 'CONSENT_REQUIRED';end if;
 if exists(select 1 from public.sbut_users where id=new.sender and (frozen or security_hold)) then raise exception 'ACCOUNT_RESTRICTED';end if;
 select coalesce(u.per_transaction,d.per_transaction) per_transaction,coalesce(u.daily,d.daily) daily,coalesce(u.monthly,d.monthly) monthly,coalesce(u.daily_count,d.daily_count) daily_count into r from wallet_private.defaults d left join wallet_private.limit_rules u on u.user_id=new.sender where d.id=1;
 select coalesce(sum(amount+fee) filter(where created_at>=start_day),0),coalesce(sum(amount+fee),0),count(*) filter(where created_at>=start_day) into spent_day,spent_month,n from public.sbut_transactions where sender=new.sender and kind in ('send','cashout','cashin','recharge') and status in ('completed','pending') and created_at>=start_month;
 if new.amount>r.per_transaction or spent_day+new.amount+new.fee>r.daily or spent_month+new.amount+new.fee>r.monthly or n>=r.daily_count then raise exception 'LIMIT_EXCEEDED';end if;
 return new;
end $$;
create or replace function public.sbut_limits() returns jsonb language plpgsql security definer set search_path='' as $$
declare result jsonb;
begin
 perform wallet_private.check_operational_session();select jsonb_build_object('per_transaction',coalesce(u.per_transaction,d.per_transaction),'daily',coalesce(u.daily,d.daily),'monthly',coalesce(u.monthly,d.monthly),'daily_count',coalesce(u.daily_count,d.daily_count)) into result from wallet_private.defaults d left join wallet_private.limit_rules u on u.user_id=auth.uid() where d.id=1;return result;
end $$;
create or replace function wallet_private.notify_transaction() returns trigger language plpgsql security definer set search_path='' as $$
begin
 if TG_OP='UPDATE' and new.status is not distinct from old.status then return new;end if;
 insert into public.sbut_notifications(user_id,kind,data) values(new.sender,case when TG_OP='UPDATE' then 'transaction_status' else 'transaction_sent' end,jsonb_build_object('transaction_id',new.id,'status',new.status));
 if new.recipient<>new.sender then insert into public.sbut_notifications(user_id,kind,data) values(new.recipient,case when TG_OP='UPDATE' then 'transaction_status' else 'transaction_received' end,jsonb_build_object('transaction_id',new.id,'status',new.status));end if;
 if TG_OP='INSERT' and new.kind in ('send','cashout','cashin','recharge') and new.amount>=(select risk_amount from wallet_private.defaults where id=1) then insert into public.sbut_notifications(user_id,kind,data) values(new.sender,'large_transfer',jsonb_build_object('transaction_id',new.id));end if;return new;
end $$;
create or replace function wallet_private.notify_request() returns trigger language plpgsql security definer set search_path='' as $$
begin
 insert into public.sbut_notifications(user_id,kind,data) values(new.payer,case when TG_OP='INSERT' then 'request_received' else 'request_updated' end,jsonb_build_object('request_id',new.id,'status',new.status));return new;
end $$;
create or replace function public.sbut_device_seen(p_device uuid,p_label text) returns boolean language plpgsql security definer set search_path='' as $$
declare is_new boolean;
begin
 perform wallet_private.check_operational_session();if p_device is null then raise exception 'DEVICE_REQUIRED';end if;
 perform user_id from wallet_private.devices where user_id=auth.uid() and device_id=p_device;is_new:=not found;
 insert into wallet_private.devices(user_id,device_id,label) values(auth.uid(),p_device,left(coalesce(p_label,'Browser'),80)) on conflict(user_id,device_id) do update set last_seen=now();
 if is_new then insert into public.sbut_notifications(user_id,kind,data) values(auth.uid(),'new_device',jsonb_build_object('label',left(coalesce(p_label,'Browser'),80)));end if;return is_new;
end $$;
create or replace function public.sbut_read_notification(p_id uuid) returns void language plpgsql security definer set search_path='' as $$
begin perform wallet_private.require_session();update public.sbut_notifications set read_at=coalesce(read_at,now()) where user_id=auth.uid() and (p_id is null or id=p_id);end $$;
create or replace function public.sbut_operational_config() returns jsonb language sql stable security definer set search_path='' as $$
 select coalesce(jsonb_object_agg(name,jsonb_build_object('enabled',enabled,'config',case when name='push' then jsonb_build_object('vapid_public_key',public_config->>'vapid_public_key') else '{}'::jsonb end)),'{}'::jsonb) from wallet_private.integrations
$$;
create or replace function public.sbut_subscribe_push(p_subscription jsonb) returns void language plpgsql security definer set search_path='' as $$
declare v_endpoint text:=p_subscription->>'endpoint';
begin
 perform wallet_private.check_operational_session();
 if v_endpoint is null or length(v_endpoint)>4096 or v_endpoint !~ '^https://(fcm\.googleapis\.com|([a-zA-Z0-9-]+\.)*push\.apple\.com|updates\.push\.services\.mozilla\.com|([a-zA-Z0-9-]+\.)*notify\.windows\.com)/' or coalesce(p_subscription#>>'{keys,p256dh}','') !~ '^[a-zA-Z0-9_-]{40,200}$' or coalesce(p_subscription#>>'{keys,auth}','') !~ '^[a-zA-Z0-9_-]{16,80}$' then raise exception 'INVALID_SUBSCRIPTION';end if;
 insert into wallet_private.push_subscriptions(endpoint,user_id,subscription) values(v_endpoint,auth.uid(),p_subscription) on conflict(endpoint) do update set subscription=excluded.subscription where push_subscriptions.user_id=auth.uid();
 if not found then raise exception 'SUBSCRIPTION_OWNER';end if;
end $$;
create or replace function public.sbut_unsubscribe_push(p_endpoint text) returns void language plpgsql security definer set search_path='' as $$
begin perform wallet_private.require_session();delete from wallet_private.push_subscriptions where user_id=auth.uid() and endpoint=p_endpoint;end $$;
create or replace function wallet_private.enqueue_push() returns trigger language plpgsql security definer set search_path='' as $$
begin insert into wallet_private.push_outbox(notification_id) values(new.id);return new;end $$;
DROP TRIGGER IF EXISTS sbut_limit_guard ON public.sbut_transactions;
CREATE TRIGGER sbut_limit_guard before insert on public.sbut_transactions for each row execute function wallet_private.enforce_limits();
DROP TRIGGER IF EXISTS sbut_transaction_notice ON public.sbut_transactions;
CREATE TRIGGER sbut_transaction_notice after insert or update of status on public.sbut_transactions for each row execute function wallet_private.notify_transaction();
DROP TRIGGER IF EXISTS sbut_request_notice ON public.sbut_requests;
CREATE TRIGGER sbut_request_notice after insert or update of status on public.sbut_requests for each row execute function wallet_private.notify_request();
DROP TRIGGER IF EXISTS sbut_enqueue_push ON public.sbut_notifications;
CREATE TRIGGER sbut_enqueue_push after insert on public.sbut_notifications for each row execute function wallet_private.enqueue_push();
create table if not exists public.sbut_recharges(id uuid primary key default gen_random_uuid(),user_id uuid not null references public.sbut_users,reserve_user uuid not null references public.sbut_users,phone text not null,operator text not null,amount bigint not null check(amount>=1000),transaction_id uuid not null unique references public.sbut_transactions,status text not null default 'queued' check(status in ('queued','processing','unknown','completed','failed')),provider_reference text,refund_transaction uuid references public.sbut_transactions,created_at timestamptz not null default now(),updated_at timestamptz not null default now());
create or replace function public.sbut_prepare_recharge(p_phone text,p_operator text,p_amount bigint,p_pin text,p_request uuid) returns jsonb language plpgsql security definer set search_path='' as $$
declare err text;c wallet_private.confirmations%rowtype;
begin
 perform wallet_private.check_operational_session();
 if not exists(select 1 from wallet_private.integrations where name='recharge' and enabled and public_config ? 'reserve_user') then return jsonb_build_object('error','PROVIDER_UNAVAILABLE');end if;
 if p_phone is null or p_phone !~ '^8801[3-9][0-9]{8}$' or p_operator is null or p_operator not in ('grameenphone','banglalink','robi','airtel','teletalk') or p_amount is null or p_amount<1000 or p_amount>1000000000000 or p_request is null then return jsonb_build_object('error','INVALID_RECHARGE');end if;
 err:=wallet_private.check_pin(p_pin);if err is not null then return jsonb_build_object('error',err);end if;
 select * into c from wallet_private.confirmations where user_id=auth.uid() and request_id=p_request for update;
 if found then
 if c.kind<>'recharge' or c.amount<>p_amount or c.recipient<>p_phone or c.metadata->>'operator'<>p_operator then return jsonb_build_object('error','REQUEST_CONFLICT');end if;
 if c.transaction_id is not null then return jsonb_build_object('completed',(select to_jsonb(t) from public.sbut_transactions t where id=c.transaction_id));end if;
 update wallet_private.confirmations set session_id=nullif(auth.jwt()->>'session_id','')::uuid,ready_at=now()+interval '5 seconds',expires_at=now()+interval '2 minutes' where id=c.id returning * into c;
 else insert into wallet_private.confirmations(user_id,session_id,recipient,amount,kind,request_id,metadata) values(auth.uid(),nullif(auth.jwt()->>'session_id','')::uuid,p_phone,p_amount,'recharge',p_request,jsonb_build_object('operator',p_operator)) returning * into c;end if;
 return jsonb_build_object('challenge',c.id,'amount',c.amount,'kind',c.kind,'recipient',c.recipient);
end $$;
create or replace function public.sbut_commit_recharge(p_challenge uuid,p_pin text) returns jsonb language plpgsql security definer set search_path='' as $$
declare c wallet_private.confirmations%rowtype;err text;v uuid;tx public.sbut_transactions%rowtype;
begin
 perform wallet_private.check_operational_session();perform user_id from wallet_private.pins where user_id=auth.uid() for update;
 select * into c from wallet_private.confirmations where id=p_challenge and user_id=auth.uid() and session_id=nullif(auth.jwt()->>'session_id','')::uuid and kind='recharge' for update;
 if not found then return jsonb_build_object('error','CONFIRMATION_REQUIRED');end if;
 if c.transaction_id is not null then return (select to_jsonb(t) from public.sbut_transactions t where id=c.transaction_id);end if;
 if now()<c.ready_at or now()>c.expires_at then return jsonb_build_object('error','HOLD_REQUIRED');end if;
 err:=wallet_private.check_pin(p_pin);if err is not null then return jsonb_build_object('error',err);end if;
 select (public_config->>'reserve_user')::uuid into v from wallet_private.integrations where name='recharge' and enabled;
 if v is null or v=auth.uid() then return jsonb_build_object('error','PROVIDER_UNAVAILABLE');end if;
 perform id from public.sbut_users where id in(v,auth.uid()) order by id for update;
 if not exists(select 1 from public.sbut_users where id=auth.uid() and kyc='approved' and not frozen and not security_hold) or not exists(select 1 from public.sbut_users where id=v and kyc='approved' and not frozen) then return jsonb_build_object('error','KYC_REQUIRED');end if;
 update public.sbut_users set balance=balance-c.amount where id=auth.uid() and balance>=c.amount;if not found then return jsonb_build_object('error','INSUFFICIENT_BALANCE');end if;
 update public.sbut_users set balance=balance+c.amount where id=v;
 insert into public.sbut_transactions(request_id,sender,recipient,kind,amount,fee,status) values(c.request_id,auth.uid(),v,'recharge',c.amount,0,'pending') returning * into tx;
 insert into public.sbut_recharges(user_id,reserve_user,phone,operator,amount,transaction_id) values(auth.uid(),v,c.recipient,c.metadata->>'operator',c.amount,tx.id);
 update wallet_private.confirmations set transaction_id=tx.id where id=c.id;return to_jsonb(tx);
end $$;
create or replace function wallet_private.protect_recharge_reserve() returns trigger language plpgsql security definer set search_path='' as $$
begin
 if new.balance<old.balance and new.balance<(select coalesce(sum(amount),0) from public.sbut_recharges where reserve_user=old.id and status in ('queued','processing','unknown')) then raise exception 'RESERVED_FUNDS';end if;return new;
end $$;
create or replace function public.sbut_claim_recharges() returns jsonb language plpgsql security definer set search_path='' as $$
declare result jsonb;
begin
 with c as(select id from public.sbut_recharges where status='queued' order by created_at for update skip locked limit 10),u as(update public.sbut_recharges r set status='processing',updated_at=now() from c where r.id=c.id returning r.*) select coalesce(jsonb_agg(to_jsonb(u)),'[]'::jsonb) into result from u;return result;
end $$;
create or replace function public.sbut_finish_recharge(p_id uuid,p_status text,p_reference text) returns void language plpgsql security definer set search_path='' as $$
declare r public.sbut_recharges%rowtype;refund uuid;
begin
 if p_status is null or p_status not in ('completed','failed','unknown') then raise exception 'INVALID_STATUS';end if;
 select * into r from public.sbut_recharges where id=p_id for update;if not found then raise exception 'ORDER_NOT_FOUND';end if;
 if r.status in ('completed','failed') then return;end if;
 if p_status='completed' and coalesce(length(trim(p_reference)),0)<3 then raise exception 'PROVIDER_REFERENCE_REQUIRED';end if;
 perform id from public.sbut_users where id in(r.user_id,r.reserve_user) order by id for update;
 update public.sbut_recharges set status=p_status,provider_reference=p_reference,updated_at=now() where id=p_id;
 if p_status='failed' then
 update public.sbut_users set balance=balance-r.amount where id=r.reserve_user and balance>=r.amount;if not found then raise exception 'REFUND_FUNDS_UNAVAILABLE';end if;
 update public.sbut_users set balance=balance+r.amount where id=r.user_id;
 insert into public.sbut_transactions(request_id,sender,recipient,kind,amount,fee) values(gen_random_uuid(),r.reserve_user,r.user_id,'refund',r.amount,0) returning id into refund;
 insert into wallet_private.refunds values(r.transaction_id,refund,r.amount);
 update public.sbut_recharges set refund_transaction=refund where id=p_id;
 end if;
 update public.sbut_transactions set status=case when p_status='unknown' then 'pending' else p_status end where id=r.transaction_id;
end $$;
DROP TRIGGER IF EXISTS sbut_reserve_guard ON public.sbut_users;
CREATE TRIGGER sbut_reserve_guard BEFORE UPDATE OF balance ON public.sbut_users FOR EACH ROW EXECUTE FUNCTION wallet_private.protect_recharge_reserve();
create or replace function wallet_private.transfer16(p_recipient text,p_amount bigint,p_pin text,p_kind text,p_request uuid) returns jsonb language plpgsql security definer set search_path='' as $$
declare u uuid:=auth.uid(); v uuid; f bigint; c bigint:=0; b bigint; s text; r text; pinrow wallet_private.pins%rowtype; old public.sbut_transactions%rowtype;
begin
 perform wallet_private.check_operational_session();
 if u is null then return jsonb_build_object('error','AUTH_REQUIRED'); end if;
 select * into pinrow from wallet_private.pins where user_id=u for update;
 if not found or pinrow.locked_until>now() then return jsonb_build_object('error','PIN_LOCKED'); end if;
 if coalesce(p_pin,'') !~ '^[0-9]{6}$' or extensions.crypt(p_pin,pinrow.hash) is distinct from pinrow.hash then
 update wallet_private.pins set failures=failures+1,locked_until=case when failures>=4 then now()+interval '15 minutes' else locked_until end where user_id=u;
 return jsonb_build_object('error','INVALID_PIN'); end if;
 update wallet_private.pins set failures=0,locked_until=null where user_id=u;
 select id into v from public.sbut_users where case
 when p_recipient ~ '^\+?[0-9]+$' then phone=case when regexp_replace(p_recipient,'[^0-9]','','g') ~ '^01[3-9][0-9]{8}$' then '88'||regexp_replace(p_recipient,'[^0-9]','','g') else regexp_replace(p_recipient,'[^0-9]','','g') end
 else username=lower(trim(leading '@' from p_recipient)) end;
 select * into old from public.sbut_transactions where sender=u and request_id=p_request;
 if found then
 if old.amount<>p_amount or old.kind<>p_kind then return jsonb_build_object('error','REQUEST_CONFLICT'); end if;
 if old.recipient is distinct from v then return jsonb_build_object('error','REQUEST_CONFLICT'); end if;
 return to_jsonb(old); end if;
 if p_amount is null or p_kind is null or p_request is null or p_amount<1000 or p_amount>1000000000000 or p_kind not in ('send','cashout','cashin') then return jsonb_build_object('error','INVALID_AMOUNT'); end if;

 if v is null or v=u then return jsonb_build_object('error','INVALID_RECIPIENT'); end if;
 perform id from public.sbut_users where id in(u,v) order by id for update;
 select balance,kyc,role into b,s,r from public.sbut_users where id=u;
 if s<>'approved' or exists(select 1 from public.sbut_users where id=u and frozen) or not exists(select 1 from public.sbut_users where id=v and kyc='approved' and not frozen) then return jsonb_build_object('error','KYC_REQUIRED'); end if;
 f:=case when p_kind='send' then case when p_amount<10000 then 0 when p_amount<50000 then ceil(p_amount*0.015)::bigint else ceil(p_amount*0.02)::bigint end when p_kind='cashout' then case when p_amount<100000 then 600 else ceil(p_amount*0.012)::bigint end else 0 end;
 if p_kind='cashout' and not exists(select 1 from public.sbut_users where id=v and role='merchant') then return jsonb_build_object('error','MERCHANT_REQUIRED'); end if;
 if p_kind='cashin' and r<>'merchant' then return jsonb_build_object('error','MERCHANT_REQUIRED'); end if;
 if p_kind in ('cashout','cashin') then c:=case when p_amount<100000 then 400 else floor(p_amount*0.008)::bigint end; end if;
 if b<p_amount+f then return jsonb_build_object('error','INSUFFICIENT_BALANCE'); end if;
 perform id from wallet_private.treasury where id=1 for update;
 if (select fees+f-c from wallet_private.treasury where id=1)<0 then return jsonb_build_object('error','COMMISSION_POOL_EMPTY'); end if;
 update public.sbut_users set balance=balance-p_amount-f where id=u;
 update public.sbut_users set balance=balance+p_amount where id=v;
 if c>0 then update public.sbut_users set balance=balance+c where id=case when p_kind='cashin' then u else v end; end if;
 update wallet_private.treasury set fees=fees+f-c where id=1;
 insert into public.sbut_transactions(request_id,sender,recipient,kind,amount,fee,commission) values(p_request,u,v,p_kind,p_amount,f,c) returning * into old;
 return to_jsonb(old);
end $$;
CREATE TABLE IF NOT EXISTS wallet_private.payment_orders16(
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),user_id uuid NOT NULL REFERENCES public.sbut_users(id),
 request_id uuid NOT NULL,order_id text NOT NULL UNIQUE CHECK(order_id ~ '^SB[0-9]{23}$'),
 kind text NOT NULL CHECK(kind IN ('send','cashout','cashin','recharge')),
 recipient text NOT NULL,recipient_id uuid,recipient_name text NOT NULL,amount bigint NOT NULL CHECK(amount>=1000),
 fee bigint NOT NULL CHECK(fee>=0),commission bigint NOT NULL DEFAULT 0,operator text,money_request uuid,
 state text NOT NULL DEFAULT 'quoted' CHECK(state IN ('quoted','authorized','holding','completed')),
 session_id uuid,hold_id uuid,ready_at timestamptz,expires_at timestamptz NOT NULL DEFAULT now()+interval '10 minutes',
 challenge_id uuid,transaction_id uuid,created_at timestamptz NOT NULL DEFAULT now(),UNIQUE(user_id,request_id)
);
ALTER TABLE wallet_private.payment_orders16 ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.payment_orders16 FROM PUBLIC,anon,authenticated;
CREATE OR REPLACE FUNCTION wallet_private.order_summary16(p_id uuid) RETURNS jsonb
LANGUAGE sql STABLE SECURITY DEFINER SET search_path='' AS $$
 SELECT jsonb_build_object('id',o.id,'order_id',o.order_id,'request_id',o.request_id,'money_request',o.money_request,'kind',o.kind,
 'recipient',o.recipient,'recipient_name',o.recipient_name,'amount',o.amount,'fee',o.fee,'commission',o.commission,
 'total_debit',o.amount+o.fee,'balance',u.balance,'remaining_balance',CASE WHEN o.state='completed' THEN u.balance ELSE u.balance-o.amount-o.fee+CASE WHEN o.kind='cashin' THEN o.commission ELSE 0 END END,
 'created_at',o.created_at,'server_now',now(),'state',o.state,'operator',o.operator,'transaction_id',o.transaction_id)
 FROM wallet_private.payment_orders16 o JOIN public.sbut_users u ON u.id=o.user_id WHERE o.id=p_id AND o.user_id=auth.uid()
$$;
CREATE OR REPLACE FUNCTION public.sbut_quote16(p_recipient text,p_amount bigint,p_kind text,p_request uuid,p_operator text DEFAULT NULL,p_money_request uuid DEFAULT NULL)
RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE v public.sbut_users%rowtype; me public.sbut_users%rowtype;o wallet_private.payment_orders16%rowtype;r public.sbut_requests%rowtype;f bigint:=0;c bigint:=0;handle text:=p_recipient;n bigint:=p_amount;kind text:=p_kind; reserve_id uuid;
BEGIN
 PERFORM wallet_private.check_operational_session();
 SELECT * INTO me FROM public.sbut_users WHERE id=auth.uid();
 IF me.kyc IS DISTINCT FROM 'approved' THEN RETURN jsonb_build_object('error','KYC_REQUIRED');END IF;
 IF p_request IS NULL OR kind IS NULL OR kind NOT IN ('send','cashout','cashin','recharge') THEN RETURN jsonb_build_object('error','INVALID_AMOUNT');END IF;
 IF p_money_request IS NOT NULL THEN
  SELECT * INTO r FROM public.sbut_requests WHERE id=p_money_request AND payer=me.id AND status='pending';
  IF NOT FOUND THEN RETURN jsonb_build_object('error','REQUEST_UNAVAILABLE');END IF;
  SELECT '@'||username INTO handle FROM public.sbut_users WHERE id=r.requester;n:=r.amount;kind:='send';
 END IF;
 IF n IS NULL OR n<1000 OR n>1000000000000 THEN RETURN jsonb_build_object('error','INVALID_AMOUNT');END IF;
 IF kind='recharge' THEN
  IF handle IS NULL OR handle !~ '^8801[3-9][0-9]{8}$' OR p_operator IS NULL OR p_operator NOT IN ('grameenphone','banglalink','robi','airtel','teletalk') THEN RETURN jsonb_build_object('error','INVALID_RECHARGE');END IF;
  SELECT (public_config->>'reserve_user')::uuid INTO reserve_id FROM wallet_private.integrations WHERE name='recharge' AND enabled AND public_config ? 'reserve_user';
  IF reserve_id IS NULL THEN RETURN jsonb_build_object('error','PROVIDER_UNAVAILABLE');END IF;
 ELSE
  IF handle IS NULL OR lower(trim(leading '@' from trim(handle))) !~ '^[a-z0-9_]{3,24}$' THEN RETURN jsonb_build_object('error','INVALID_RECIPIENT');END IF;
  SELECT * INTO v FROM public.sbut_users WHERE username=lower(trim(leading '@' from trim(handle))) AND kyc='approved' AND NOT frozen;
  IF NOT FOUND OR v.id=me.id THEN RETURN jsonb_build_object('error','INVALID_RECIPIENT');END IF;
  handle:='@'||v.username;
  IF (kind='cashout' AND v.role<>'merchant') OR (kind='cashin' AND me.role<>'merchant') THEN RETURN jsonb_build_object('error','MERCHANT_REQUIRED');END IF;
 END IF;
 f:=CASE WHEN kind='send' THEN CASE WHEN n<10000 THEN 0 WHEN n<50000 THEN ceil(n*0.015)::bigint ELSE ceil(n*0.02)::bigint END WHEN kind='cashout' THEN CASE WHEN n<100000 THEN 600 ELSE ceil(n*0.012)::bigint END ELSE 0 END;
 IF kind IN ('cashin','cashout') THEN c:=CASE WHEN n<100000 THEN 400 ELSE floor(n*0.008)::bigint END;END IF;
 SELECT * INTO o FROM wallet_private.payment_orders16 WHERE user_id=me.id AND request_id=p_request;
 IF FOUND THEN
  IF o.recipient<>handle OR o.amount<>n OR o.kind<>kind OR o.operator IS DISTINCT FROM p_operator OR o.money_request IS DISTINCT FROM p_money_request THEN RETURN jsonb_build_object('error','REQUEST_CONFLICT');END IF;
  RETURN wallet_private.order_summary16(o.id);
 END IF;
 IF EXISTS(SELECT 1 FROM public.sbut_transactions WHERE sender=me.id AND request_id=p_request) THEN RETURN jsonb_build_object('error','REQUEST_CONFLICT');END IF;
 IF (SELECT count(*) FROM wallet_private.payment_orders16 WHERE user_id=me.id AND created_at>now()-interval '15 minutes')>=60 THEN RETURN jsonb_build_object('error','TRY_LATER');END IF;
 INSERT INTO wallet_private.payment_orders16(user_id,request_id,order_id,kind,recipient,recipient_id,recipient_name,amount,fee,commission,operator,money_request)
 VALUES(me.id,p_request,'SB'||lpad(nextval('wallet_private.order_serial')::text,23,'0'),kind,handle,CASE WHEN kind='recharge' THEN reserve_id ELSE v.id END,CASE WHEN kind='recharge' THEN handle ELSE v.name END,n,f,c,p_operator,p_money_request) RETURNING * INTO o;
 RETURN wallet_private.order_summary16(o.id);
END $$;
CREATE OR REPLACE FUNCTION public.sbut_authorize_order16(p_order uuid,p_pin text) RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE o wallet_private.payment_orders16%rowtype;err text;r jsonb;
BEGIN
 PERFORM wallet_private.check_operational_session();
 SELECT * INTO o FROM wallet_private.payment_orders16 WHERE id=p_order AND user_id=auth.uid() FOR UPDATE;
 IF NOT FOUND THEN RETURN jsonb_build_object('error','ORDER_NOT_FOUND');END IF;
 IF o.state='completed' THEN RETURN wallet_private.order_summary16(o.id);END IF;
 IF o.expires_at<=now() THEN RETURN jsonb_build_object('error','ORDER_EXPIRED');END IF;
 err:=wallet_private.check_pin(p_pin);IF err IS NOT NULL THEN RETURN jsonb_build_object('error',err);END IF;
 IF (SELECT balance FROM public.sbut_users WHERE id=auth.uid())<o.amount+o.fee THEN RETURN jsonb_build_object('error','INSUFFICIENT_BALANCE');END IF;
 IF o.kind='recharge' THEN
  r:=public.sbut_prepare_recharge(o.recipient,o.operator,o.amount,p_pin,o.request_id);
  IF r ? 'error' THEN RETURN r;END IF;
  UPDATE wallet_private.payment_orders16 SET challenge_id=(r->>'challenge')::uuid WHERE id=o.id;
 END IF;
 UPDATE wallet_private.payment_orders16 SET state='authorized',session_id=nullif(auth.jwt()->>'session_id','')::uuid,hold_id=NULL,ready_at=NULL WHERE id=o.id;
 RETURN wallet_private.order_summary16(o.id);
END $$;
CREATE OR REPLACE FUNCTION public.sbut_begin_hold16(p_order uuid) RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE o wallet_private.payment_orders16%rowtype;h uuid:=gen_random_uuid();
BEGIN
 PERFORM wallet_private.check_operational_session();
 SELECT * INTO o FROM wallet_private.payment_orders16 WHERE id=p_order AND user_id=auth.uid() AND session_id=nullif(auth.jwt()->>'session_id','')::uuid FOR UPDATE;
 IF NOT FOUND OR o.state NOT IN ('authorized','holding') OR o.expires_at<=now() THEN RETURN jsonb_build_object('error','CONFIRMATION_REQUIRED');END IF;
 UPDATE wallet_private.payment_orders16 SET state='holding',hold_id=h,ready_at=now()+interval '5 seconds' WHERE id=o.id;
 IF o.kind='recharge' THEN UPDATE wallet_private.confirmations SET ready_at=now()+interval '5 seconds',expires_at=now()+interval '2 minutes' WHERE id=o.challenge_id;END IF;
 RETURN wallet_private.order_summary16(o.id)||jsonb_build_object('hold_id',h);
END $$;
CREATE OR REPLACE FUNCTION public.sbut_cancel_hold16(p_order uuid,p_hold uuid) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN
 PERFORM wallet_private.require_session();
 UPDATE wallet_private.payment_orders16 SET state='quoted',hold_id=NULL,ready_at=NULL WHERE id=p_order AND user_id=auth.uid() AND hold_id=p_hold AND state='holding';
END $$;
CREATE OR REPLACE FUNCTION public.sbut_commit_order16(p_order uuid,p_hold uuid,p_pin text) RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE o wallet_private.payment_orders16%rowtype;r jsonb;mr public.sbut_requests%rowtype;
BEGIN
 PERFORM wallet_private.check_operational_session();
 SELECT * INTO o FROM wallet_private.payment_orders16 WHERE id=p_order AND user_id=auth.uid() FOR UPDATE;
 IF NOT FOUND THEN RETURN jsonb_build_object('error','ORDER_NOT_FOUND');END IF;
 IF o.state='completed' THEN RETURN jsonb_build_object('transaction',(SELECT to_jsonb(t) FROM public.sbut_transactions t WHERE t.id=o.transaction_id),'order_id',o.order_id);END IF;
 IF o.session_id IS DISTINCT FROM nullif(auth.jwt()->>'session_id','')::uuid OR o.state<>'holding' OR o.hold_id IS DISTINCT FROM p_hold OR o.ready_at IS NULL OR now()<o.ready_at OR now()>o.expires_at THEN RETURN jsonb_build_object('error','HOLD_REQUIRED');END IF;
 IF o.money_request IS NOT NULL THEN
  SELECT * INTO mr FROM public.sbut_requests WHERE id=o.money_request AND payer=auth.uid() AND status='pending' FOR UPDATE;
  IF NOT FOUND OR mr.amount<>o.amount OR mr.requester<>o.recipient_id THEN RETURN jsonb_build_object('error','REQUEST_UNAVAILABLE');END IF;
 END IF;
 IF o.kind<>'recharge' AND NOT EXISTS(SELECT 1 FROM public.sbut_users WHERE id=o.recipient_id AND '@'||username=o.recipient AND kyc='approved' AND NOT frozen) THEN RETURN jsonb_build_object('error','RECIPIENT_CHANGED');END IF;
 IF o.kind='recharge' THEN r:=public.sbut_commit_recharge(o.challenge_id,p_pin);
 ELSE r:=wallet_private.transfer16(o.recipient,o.amount,p_pin,o.kind,o.request_id);END IF;
 IF r ? 'error' THEN RETURN r;END IF;
 IF (r->>'recipient')::uuid IS DISTINCT FROM o.recipient_id THEN RAISE EXCEPTION 'RECIPIENT_CHANGED';END IF;
 UPDATE wallet_private.payment_orders16 SET state='completed',transaction_id=(r->>'id')::uuid,hold_id=NULL WHERE id=o.id;
 IF o.money_request IS NOT NULL THEN UPDATE public.sbut_requests SET status='paid',transaction_id=(r->>'id')::uuid WHERE id=o.money_request;END IF;
 RETURN jsonb_build_object('transaction',r,'order_id',o.order_id);
END $$;
-- The reserved pre-confirmation Order ID becomes the SAME ledger/receipt Order ID.
CREATE OR REPLACE FUNCTION wallet_private.assign_order_id() RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE ref text;
BEGIN
 IF TG_TABLE_NAME='sbut_transactions' THEN SELECT order_id INTO ref FROM wallet_private.payment_orders16 WHERE user_id=NEW.sender AND request_id=NEW.request_id;END IF;
 INSERT INTO wallet_private.order_ids(source_kind,source_id,order_id)
 VALUES(TG_TABLE_NAME,NEW.id,coalesce(ref,'SB'||lpad(nextval('wallet_private.order_serial')::text,23,'0'))) ON CONFLICT(source_kind,source_id) DO NOTHING;
 RETURN NEW;
END $$;
CREATE OR REPLACE FUNCTION public.sbut_order_status16(p_order uuid) RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN
 PERFORM wallet_private.require_session();RETURN wallet_private.order_summary16(p_order)||jsonb_build_object('transaction',(SELECT to_jsonb(t) FROM public.sbut_transactions t JOIN wallet_private.payment_orders16 o ON o.transaction_id=t.id WHERE o.id=p_order AND o.user_id=auth.uid()));
END $$;
CREATE OR REPLACE FUNCTION public.sbut_welcome_reward16(p_claim boolean DEFAULT false) RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE u public.sbut_users%rowtype;tx uuid;
BEGIN
 PERFORM wallet_private.check_operational_session();
 SELECT * INTO u FROM public.sbut_users WHERE id=auth.uid() FOR UPDATE;
 IF u.bonus_paid THEN RETURN jsonb_build_object('status','paid','amount',10000);END IF;
 IF u.kyc<>'approved' OR NOT EXISTS(SELECT 1 FROM public.sbut_kyc WHERE user_id=u.id) THEN RETURN jsonb_build_object('status','not_eligible','amount',10000);END IF;
 IF NOT p_claim THEN RETURN jsonb_build_object('status','eligible','amount',10000);END IF;
 UPDATE wallet_private.treasury SET promotion=promotion-10000 WHERE id=1 AND promotion>=10000;
 IF NOT FOUND THEN RETURN jsonb_build_object('status','waiting_for_funds','amount',10000);END IF;
 UPDATE public.sbut_users SET balance=balance+10000,bonus_paid=true WHERE id=u.id;
 INSERT INTO public.sbut_transactions(request_id,sender,recipient,kind,amount,fee) VALUES(gen_random_uuid(),u.id,u.id,'bonus',10000,0) RETURNING id INTO tx;
 RETURN jsonb_build_object('status','paid','amount',10000,'transaction_id',tx);
END $$;
CREATE TABLE IF NOT EXISTS wallet_private.campaign_funding16(reference text PRIMARY KEY,user_id uuid NOT NULL,amount bigint NOT NULL CHECK(amount>0),transaction_id uuid NOT NULL);
ALTER TABLE wallet_private.campaign_funding16 ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.campaign_funding16 FROM PUBLIC,anon,authenticated;
CREATE OR REPLACE FUNCTION public.sbut_campaign_admin16() RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN
 PERFORM wallet_private.require_admin(true);
 RETURN jsonb_build_object('promotion',(SELECT promotion FROM wallet_private.treasury WHERE id=1),'balance',(SELECT balance FROM public.sbut_users WHERE id=auth.uid()),'waiting',(SELECT count(*) FROM public.sbut_users WHERE kyc='approved' AND NOT bonus_paid));
END $$;
CREATE OR REPLACE FUNCTION public.sbut_fund_campaign16(p_amount bigint,p_pin text,p_reference text,p_reason text) RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE err text;r wallet_private.campaign_funding16%rowtype;tx uuid;
BEGIN
 PERFORM wallet_private.require_admin(true);
 IF p_amount IS NULL OR p_amount<10000 OR p_amount>1000000000000 OR length(trim(coalesce(p_reference,'')))<5 OR length(trim(coalesce(p_reason,'')))<5 THEN RAISE EXCEPTION 'INVALID_FUNDING';END IF;
 err:=wallet_private.check_pin(p_pin);IF err IS NOT NULL THEN RETURN jsonb_build_object('error',err);END IF;
 PERFORM id FROM public.sbut_users WHERE id=auth.uid() AND kyc='approved' FOR UPDATE;
 IF NOT FOUND THEN RETURN jsonb_build_object('error','FUNDED_WALLET_REQUIRED');END IF;
 SELECT * INTO r FROM wallet_private.campaign_funding16 WHERE reference=trim(p_reference);
 IF FOUND THEN
  IF r.user_id<>auth.uid() OR r.amount<>p_amount THEN RETURN jsonb_build_object('error','REQUEST_CONFLICT');END IF;
  RETURN jsonb_build_object('ok',true,'transaction_id',r.transaction_id);
 END IF;
 UPDATE public.sbut_users SET balance=balance-p_amount WHERE id=auth.uid() AND balance>=p_amount;
 IF NOT FOUND THEN RETURN jsonb_build_object('error','INSUFFICIENT_BALANCE');END IF;
 UPDATE wallet_private.treasury SET promotion=promotion+p_amount WHERE id=1;
 INSERT INTO public.sbut_transactions(request_id,sender,recipient,kind,amount,fee) VALUES(gen_random_uuid(),auth.uid(),auth.uid(),'admin_debit',p_amount,0) RETURNING id INTO tx;
 INSERT INTO wallet_private.campaign_funding16 VALUES(trim(p_reference),auth.uid(),p_amount,tx);
 INSERT INTO public.sbut_admin_audit(actor,action,target,reference,amount,reason) VALUES(auth.uid(),'promotion_budget_funded',auth.uid(),trim(p_reference),p_amount,trim(p_reason));
 RETURN jsonb_build_object('ok',true,'transaction_id',tx);
END $$;
ALTER TABLE wallet_private.limit_rules ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.limit_rules FROM PUBLIC,anon,authenticated;
ALTER TABLE wallet_private.defaults ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.defaults FROM PUBLIC,anon,authenticated;
ALTER TABLE wallet_private.devices ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.devices FROM PUBLIC,anon,authenticated;
ALTER TABLE wallet_private.refunds ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.refunds FROM PUBLIC,anon,authenticated;
ALTER TABLE wallet_private.commission_reversals ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.commission_reversals FROM PUBLIC,anon,authenticated;
ALTER TABLE wallet_private.recovery_attempts ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.recovery_attempts FROM PUBLIC,anon,authenticated;
ALTER TABLE wallet_private.recovery_tokens ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.recovery_tokens FROM PUBLIC,anon,authenticated;
ALTER TABLE wallet_private.push_subscriptions ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.push_subscriptions FROM PUBLIC,anon,authenticated;
ALTER TABLE wallet_private.push_outbox ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.push_outbox FROM PUBLIC,anon,authenticated;
ALTER TABLE wallet_private.integrations ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.integrations FROM PUBLIC,anon,authenticated;
ALTER TABLE wallet_private.confirmations ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.confirmations FROM PUBLIC,anon,authenticated;
ALTER TABLE public.sbut_requests ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS request_parties ON public.sbut_requests;
CREATE POLICY request_parties ON public.sbut_requests FOR SELECT TO authenticated USING(((requester=auth.uid() OR payer=auth.uid()) AND public.sbut_session_active()) OR public.sbut_is_admin());
REVOKE ALL ON public.sbut_requests FROM PUBLIC,anon,authenticated;
GRANT SELECT ON public.sbut_requests TO authenticated;
ALTER TABLE public.sbut_notifications ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS notifications_self ON public.sbut_notifications;
CREATE POLICY notifications_self ON public.sbut_notifications FOR SELECT TO authenticated USING(user_id=auth.uid() AND public.sbut_session_active());
REVOKE ALL ON public.sbut_notifications FROM PUBLIC,anon,authenticated;
GRANT SELECT ON public.sbut_notifications TO authenticated;
ALTER TABLE public.sbut_disputes ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS dispute_parties ON public.sbut_disputes;
CREATE POLICY dispute_parties ON public.sbut_disputes FOR SELECT TO authenticated USING((complainant=auth.uid() AND public.sbut_session_active()) OR public.sbut_is_admin());
REVOKE ALL ON public.sbut_disputes FROM PUBLIC,anon,authenticated;
GRANT SELECT ON public.sbut_disputes TO authenticated;
ALTER TABLE public.sbut_cash_reports ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS reports_owner ON public.sbut_cash_reports;
CREATE POLICY reports_owner ON public.sbut_cash_reports FOR SELECT TO authenticated USING((user_id=auth.uid() AND public.sbut_session_active()) OR public.sbut_is_admin());
REVOKE ALL ON public.sbut_cash_reports FROM PUBLIC,anon,authenticated;
GRANT SELECT ON public.sbut_cash_reports TO authenticated;
ALTER TABLE public.sbut_recharges ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS recharge_owner ON public.sbut_recharges;
CREATE POLICY recharge_owner ON public.sbut_recharges FOR SELECT TO authenticated USING((user_id=auth.uid() AND public.sbut_session_active()) OR public.sbut_is_admin());
REVOKE ALL ON public.sbut_recharges FROM PUBLIC,anon,authenticated;
GRANT SELECT ON public.sbut_recharges TO authenticated;
DO $$ DECLARE r record;BEGIN
 FOR r IN SELECT p.oid::regprocedure AS signature,n.nspname FROM pg_proc p JOIN pg_namespace n ON n.oid=p.pronamespace
 WHERE (n.nspname='wallet_private' AND p.proname IN ('transfer16','order_summary16','assign_order_id','check_operational_session','enforce_limits','notify_transaction','notify_request','enqueue_push','protect_recharge_reserve','require_admin'))
 OR (n.nspname='public' AND p.proname IN ('sbut_quote16','sbut_authorize_order16','sbut_begin_hold16','sbut_cancel_hold16','sbut_commit_order16','sbut_order_status16','sbut_welcome_reward16','sbut_campaign_admin16','sbut_fund_campaign16','sbut_limits','sbut_device_seen','sbut_read_notification','sbut_operational_config','sbut_subscribe_push','sbut_unsubscribe_push','sbut_prepare_recharge','sbut_commit_recharge','sbut_is_admin'))
 LOOP
  EXECUTE format('REVOKE ALL ON FUNCTION %s FROM PUBLIC,anon,authenticated',r.signature);
  IF r.nspname='public' THEN EXECUTE format('GRANT EXECUTE ON FUNCTION %s TO authenticated',r.signature);END IF;
 END LOOP;
END $$;
REVOKE ALL ON FUNCTION public.sbut_claim_recharges(),public.sbut_finish_recharge(uuid,text,text) FROM PUBLIC,anon,authenticated;
GRANT EXECUTE ON FUNCTION public.sbut_claim_recharges(),public.sbut_finish_recharge(uuid,text,text) TO service_role;
REVOKE ALL ON FUNCTION public.sbut_transfer(text,bigint,text,text,uuid) FROM PUBLIC,anon,authenticated;
NOTIFY pgrst,'reload schema';
-- Old client money paths are retired. SQL016 calls the recharge engine internally as owner.
DO $legacy16$ 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='public' AND p.proname IN ('sbut_prepare','sbut_prepare_pre007','sbut_commit','sbut_prepare_recharge','sbut_commit_recharge')
 LOOP EXECUTE format('REVOKE ALL ON FUNCTION %s FROM PUBLIC,anon,authenticated',f.signature); END LOOP;
END $legacy16$;
COMMIT;
SELECT true AS payment_flow_ready,true AS notifications_limits_repaired;

-- ADMIN ACCESS UPDATE 018
-- SBUT PAY 018: Admin access repair. Run as the owner of the SBUT PAY database.
-- Requires SQL014 and SQL016. No password change and no reward/money credit.
-- Owner-authorized first Founder setup targets @tutul001 only. Existing different Founder is preserved.
BEGIN;
CREATE TABLE IF NOT EXISTS wallet_private.admins(user_id uuid PRIMARY KEY REFERENCES auth.users,login_id text UNIQUE NOT NULL,kind text NOT NULL CHECK(kind IN ('founder','admin')));
ALTER TABLE wallet_private.admins ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.admins FROM PUBLIC,anon,authenticated;
create or replace function public.sbut_is_admin() returns boolean language sql stable security definer set search_path='' as $$ select public.sbut_session_active() and exists(select 1 from wallet_private.admins where user_id=auth.uid()) $$;
create or replace function wallet_private.require_admin(p_founder boolean default false) returns void language plpgsql security definer set search_path='' as $$
begin
 perform wallet_private.require_session();
 if not exists(select 1 from wallet_private.admins where user_id=auth.uid() and (not p_founder or kind='founder')) then raise exception 'ADMIN_REQUIRED'; end if;
end $$;
create or replace function public.sbut_admin_identity(p_login_id text) returns jsonb language plpgsql security definer set search_path='' as $$
declare r wallet_private.admins%rowtype;
begin
 perform wallet_private.require_admin();select * into r from wallet_private.admins where user_id=auth.uid() and login_id=p_login_id;
 if not found then raise exception 'ADMIN_REQUIRED'; end if;
 return jsonb_build_object('kind',r.kind,'login_id',r.login_id);
end $$;
CREATE OR REPLACE FUNCTION public.sbut_admin_entry18() RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE r wallet_private.admins%rowtype;
BEGIN
 IF NOT wallet_private.access_session_active14() THEN RAISE EXCEPTION 'SESSION_LOCKED';END IF;
 SELECT * INTO r FROM wallet_private.admins WHERE user_id=auth.uid();
 IF NOT FOUND THEN RETURN jsonb_build_object('can_access',false,'error','ADMIN_NOT_PROVISIONED');END IF;
 RETURN jsonb_build_object('can_access',true,'login_id',r.login_id,'kind',r.kind,'pin_required',NOT public.sbut_session_active());
END $$;
REVOKE ALL ON FUNCTION public.sbut_admin_entry18(),public.sbut_admin_identity(text),public.sbut_is_admin() FROM PUBLIC,anon,authenticated;
GRANT EXECUTE ON FUNCTION public.sbut_admin_entry18(),public.sbut_admin_identity(text),public.sbut_is_admin() TO authenticated;
GRANT EXECUTE ON FUNCTION public.sbut_is_admin() TO anon;
REVOKE ALL ON FUNCTION wallet_private.require_admin(boolean) FROM PUBLIC,anon,authenticated;
GRANT USAGE ON SCHEMA public TO authenticated;
ALTER TABLE public.sbut_users ENABLE ROW LEVEL SECURITY;
GRANT SELECT ON public.sbut_users TO authenticated;
DROP POLICY IF EXISTS users_admin_read ON public.sbut_users;
CREATE POLICY users_admin_read ON public.sbut_users FOR SELECT TO authenticated USING(public.sbut_is_admin());
ALTER TABLE public.sbut_kyc ENABLE ROW LEVEL SECURITY;
GRANT SELECT ON public.sbut_kyc TO authenticated;
DROP POLICY IF EXISTS kyc_admin_read ON public.sbut_kyc;
CREATE POLICY kyc_admin_read ON public.sbut_kyc FOR SELECT TO authenticated USING(public.sbut_is_admin());
ALTER TABLE public.sbut_transactions ENABLE ROW LEVEL SECURITY;
GRANT SELECT ON public.sbut_transactions TO authenticated;
DROP POLICY IF EXISTS transactions_admin_read ON public.sbut_transactions;
CREATE POLICY transactions_admin_read ON public.sbut_transactions FOR SELECT TO authenticated USING(public.sbut_is_admin());
ALTER TABLE public.sbut_merchant_applications ENABLE ROW LEVEL SECURITY;
GRANT SELECT ON public.sbut_merchant_applications TO authenticated;
DROP POLICY IF EXISTS applications_admin_read ON public.sbut_merchant_applications;
CREATE POLICY applications_admin_read ON public.sbut_merchant_applications FOR SELECT TO authenticated USING(public.sbut_is_admin());
ALTER TABLE public.sbut_banners ENABLE ROW LEVEL SECURITY;
GRANT SELECT ON public.sbut_banners TO authenticated;
DROP POLICY IF EXISTS banners_admin18 ON public.sbut_banners;
CREATE POLICY banners_admin18 ON public.sbut_banners FOR SELECT TO authenticated USING(public.sbut_is_admin());
ALTER TABLE public.sbut_admin_audit ENABLE ROW LEVEL SECURITY;
GRANT SELECT ON public.sbut_admin_audit TO authenticated;
DROP POLICY IF EXISTS audit_admin ON public.sbut_admin_audit;
CREATE POLICY audit_admin ON public.sbut_admin_audit FOR SELECT TO authenticated USING(public.sbut_is_admin());
DO $owner_setup18$
DECLARE target_username text:='tutul001';target_id uuid;
BEGIN
 LOCK TABLE wallet_private.admins IN SHARE ROW EXCLUSIVE MODE;
 SELECT u.id INTO target_id FROM public.sbut_users u JOIN auth.users a ON a.id=u.id WHERE u.username=target_username AND u.kyc='approved' AND NOT u.frozen;
 IF target_id IS NULL OR EXISTS(SELECT 1 FROM wallet_private.admins WHERE kind='founder') THEN RETURN;END IF;
 IF EXISTS(SELECT 1 FROM wallet_private.admins WHERE user_id=target_id) THEN
  UPDATE wallet_private.admins SET kind='founder' WHERE user_id=target_id;
 ELSIF NOT EXISTS(SELECT 1 FROM wallet_private.admins WHERE login_id='FOUNDER-TUTUL') THEN
  INSERT INTO wallet_private.admins(user_id,login_id,kind) VALUES(target_id,'FOUNDER-TUTUL','founder');
 ELSE RETURN;END IF;
 INSERT INTO public.sbut_admin_audit(actor,action,target,reference,reason)
 VALUES(auth.uid(),'first_founder_provisioned',target_id,'owner-sql018','Database owner authorized first Founder for @tutul001');
END $owner_setup18$;
NOTIFY pgrst,'reload schema';
COMMIT;
-- Inspect these results. If another Founder already exists, its access was NOT reassigned.
SELECT a.login_id,a.kind,u.username,coalesce(e.email,au.email) AS login_email
FROM wallet_private.admins a JOIN auth.users au ON au.id=a.user_id
LEFT JOIN public.sbut_users u ON u.id=a.user_id LEFT JOIN wallet_private.login_emails e ON e.user_id=a.user_id
WHERE a.kind='founder';
SELECT CASE WHEN EXISTS(SELECT 1 FROM wallet_private.admins a JOIN public.sbut_users u ON u.id=a.user_id WHERE a.kind='founder' AND u.username='tutul001') THEN 'tutul001_founder_ready'
 WHEN EXISTS(SELECT 1 FROM wallet_private.admins WHERE kind='founder') THEN 'existing_founder_preserved_use_account_shown_above'
 ELSE 'approved_tutul001_account_required_or_login_id_already_used' END AS founder_setup_status;

-- ADMIN FINANCE UPDATE 019
-- SBUT PAY019: Founder money controls, merchant workflow and consolidated records.
-- Requires SQL018/016. Owner-run repair only. No automatic money credits.
BEGIN;
create table if not exists public.sbut_help_claims(
 id uuid primary key default gen_random_uuid(), target_handle text not null check(target_handle ~ '^[a-z0-9_]{3,24}$'),
 kind text not null check(kind in ('help','recovery','heir')), claimant_name text not null, contact text not null,
 details text not null, status text not null default 'submitted' check(status in ('submitted','reviewing','rejected','link_issued','recovered','settled','resolved')),
 evidence text[] not null default '{}', reason text, reviewed_by uuid references auth.users, target_user uuid references public.sbut_users,
 beneficiary uuid references public.sbut_users, settlement uuid references public.sbut_transactions,
 tracking_hash text not null, created_at timestamptz not null default now(),updated_at timestamptz not null default now()
);
ALTER TABLE public.sbut_help_claims ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON public.sbut_help_claims FROM PUBLIC,anon,authenticated;
GRANT SELECT ON public.sbut_help_claims TO authenticated;
DROP POLICY IF EXISTS claim_admin_read ON public.sbut_help_claims;
CREATE POLICY claim_admin_read ON public.sbut_help_claims FOR SELECT TO authenticated USING(public.sbut_is_admin());
create or replace function wallet_private.fund_merchant(p_merchant uuid,p_gross bigint,p_reference uuid) returns void language plpgsql set search_path='' as $$
declare f bigint;
begin
 if p_gross<100000 or p_gross>99000000 then raise exception 'FUNDING_RANGE'; end if;
 perform id from public.sbut_users where id=p_merchant and role='merchant' and kyc='approved' for update;
 if not found then raise exception 'MERCHANT_REQUIRED'; end if;
 if exists(select 1 from public.sbut_transactions where sender=p_merchant and request_id=p_reference) then return; end if;
 f:=ceil(p_gross*case when p_gross<5000000 then .05 when p_gross<10000000 then .08 else .12 end)::bigint;
 update public.sbut_users set balance=balance+p_gross-f where id=p_merchant;
 update wallet_private.treasury set fees=fees+f where id=1;
 insert into public.sbut_transactions(request_id,sender,recipient,kind,amount,fee) values(p_reference,p_merchant,p_merchant,'funding',p_gross-f,f);
end $$;
create or replace function public.sbut_admin_adjust(p_user uuid,p_amount bigint,p_action text,p_reference text,p_reason text) returns jsonb language plpgsql security definer set search_path='' as $$
declare result public.sbut_transactions%rowtype; delta bigint; funding_request uuid:=gen_random_uuid();
begin
 perform wallet_private.require_admin(true);
 if p_action is null or p_reference is null or p_reason is null or p_amount is null or p_amount<=0 or p_amount>1000000000000 or p_action not in ('credit','debit','merchant_funding') or length(trim(p_reference))<8 or length(trim(p_reason))<5 then raise exception 'INVALID_ADJUSTMENT';end if;
 perform id from public.sbut_users where id=p_user for update;if not found then raise exception 'USER_NOT_FOUND';end if;
 if exists(select 1 from wallet_private.settlements where reference=p_reference) then raise exception 'DUPLICATE_REFERENCE';end if;
 insert into wallet_private.settlements values(p_reference,p_user,p_amount,p_action,now());
 if p_action='merchant_funding' then
 perform wallet_private.fund_merchant(p_user,p_amount,funding_request);
 select * into result from public.sbut_transactions where sender=p_user and request_id=funding_request;
 else
 delta:=case when p_action='credit' then p_amount else -p_amount end;
 update public.sbut_users set balance=balance+delta where id=p_user and balance+delta>=0;if not found then raise exception 'INSUFFICIENT_BALANCE';end if;
 insert into public.sbut_transactions(request_id,sender,recipient,kind,amount,fee) values(gen_random_uuid(),p_user,p_user,case when delta>0 then 'admin_credit' else 'admin_debit' end,p_amount,0) returning * into result;
 end if;
 insert into public.sbut_admin_audit(actor,action,target,reference,amount,reason) values(auth.uid(),p_action,p_user,p_reference,p_amount,p_reason);
 return jsonb_build_object('ok',true,'reference',p_reference,'transaction_id',result.id,'order_id',(select order_id from wallet_private.order_ids where source_kind='sbut_transactions' and source_id=result.id));
end $$;
create or replace function public.sbut_apply_merchant_v3(p_business text,p_address text,p_email text) returns void language plpgsql security definer set search_path='' as $$
begin
 perform wallet_private.require_session();
 if not exists(select 1 from public.sbut_users where id=auth.uid() and kyc='approved') then raise exception 'KYC_REQUIRED';end if;
 if p_business is null or p_address is null or p_email is null or length(trim(p_business))<2 or length(trim(p_address))<5 or p_email !~ '^[^@[:space:]]+@[^@[:space:]]+\.[^@[:space:]]+$' then raise exception 'INVALID_APPLICATION';end if;
 insert into public.sbut_merchant_applications(user_id,business,address,email) values(auth.uid(),p_business,p_address,p_email) on conflict(user_id) do update set business=excluded.business,address=excluded.address,email=excluded.email,status='pending',created_at=now(),reviewed_at=null where sbut_merchant_applications.status='rejected';
end $$;
create or replace function wallet_private.transaction_delta(t public.sbut_transactions,u uuid) returns bigint language sql immutable set search_path='' as $$
 select case when t.sender=u and t.recipient=u and t.kind in ('funding','bonus','admin_credit') then t.amount when t.sender=u and t.recipient=u and t.kind in ('admin_debit','commission_reversal') then -t.amount else (case when t.recipient=u then t.amount else 0 end)-(case when t.sender=u then t.amount+t.fee else 0 end)+(case when t.kind='cashin' and t.sender=u or t.kind='cashout' and t.recipient=u then t.commission else 0 end) end
$$;
CREATE TABLE IF NOT EXISTS wallet_private.money_controls19(
 reference text PRIMARY KEY,actor uuid NOT NULL REFERENCES auth.users,target uuid REFERENCES public.sbut_users,
 action text NOT NULL,amount bigint NOT NULL CHECK(amount>0),reason text NOT NULL,result jsonb NOT NULL,created_at timestamptz NOT NULL DEFAULT now());
ALTER TABLE wallet_private.money_controls19 ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.money_controls19 FROM PUBLIC,anon,authenticated;
CREATE OR REPLACE FUNCTION public.sbut_admin_money19(p_user uuid,p_amount bigint,p_action text,p_reference text,p_reason text,p_confirmed boolean) RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE r wallet_private.money_controls19%rowtype;result jsonb;ref text:=trim(p_reference);before_balance bigint;
BEGIN
 PERFORM wallet_private.require_admin(true);
 IF p_confirmed IS DISTINCT FROM true OR p_amount IS NULL OR p_amount<1 OR p_amount>1000000000000 OR p_action IS NULL OR p_action NOT IN ('credit','debit','merchant_funding') OR length(coalesce(ref,''))<8 OR length(coalesce(trim(p_reason),''))<5 THEN RAISE EXCEPTION 'INVALID_ADJUSTMENT';END IF;
 PERFORM pg_advisory_xact_lock(hashtextextended(ref,19));
 SELECT * INTO r FROM wallet_private.money_controls19 WHERE reference=ref;
 IF FOUND THEN
  IF r.actor<>auth.uid() OR r.target IS DISTINCT FROM p_user OR r.action<>p_action OR r.amount<>p_amount THEN RAISE EXCEPTION 'REFERENCE_CONFLICT';END IF;
  RETURN r.result;
 END IF;
 IF NOT EXISTS(SELECT 1 FROM public.sbut_users WHERE id=p_user AND kyc='approved') THEN RAISE EXCEPTION 'ACCOUNT_NOT_READY';END IF;
 SELECT balance INTO before_balance FROM public.sbut_users WHERE id=p_user FOR UPDATE;
 result:=public.sbut_admin_adjust(p_user,p_amount,p_action,ref,p_reason);
 result:=result||jsonb_build_object('balance',(SELECT balance FROM public.sbut_users WHERE id=p_user),'before_balance',before_balance,'net_change',(SELECT balance-before_balance FROM public.sbut_users WHERE id=p_user));
 INSERT INTO wallet_private.money_controls19 VALUES(ref,auth.uid(),p_user,p_action,p_amount,trim(p_reason),result,now());RETURN result;
END $$;
CREATE OR REPLACE FUNCTION public.sbut_merchant_review19(p_user uuid,p_action text,p_reason text) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN
 PERFORM wallet_private.require_admin();
 IF length(coalesce(trim(p_reason),''))<5 THEN RAISE EXCEPTION 'REASON_REQUIRED';END IF;
 PERFORM id FROM public.sbut_users WHERE id=p_user FOR UPDATE;IF NOT FOUND THEN RAISE EXCEPTION 'USER_NOT_FOUND';END IF;
 IF p_action='approve' THEN
  IF NOT EXISTS(SELECT 1 FROM public.sbut_users WHERE id=p_user AND kyc='approved' AND NOT frozen) THEN RAISE EXCEPTION 'KYC_REQUIRED';END IF;
  UPDATE public.sbut_merchant_applications SET status='approved',reviewed_at=now() WHERE user_id=p_user AND status='pending';IF NOT FOUND THEN RAISE EXCEPTION 'NO_PENDING_APPLICATION';END IF;
  UPDATE public.sbut_users SET role='merchant' WHERE id=p_user;
 ELSIF p_action='reject' THEN
  UPDATE public.sbut_merchant_applications SET status='rejected',reviewed_at=now() WHERE user_id=p_user AND status='pending';IF NOT FOUND THEN RAISE EXCEPTION 'NO_PENDING_APPLICATION';END IF;
 ELSIF p_action='remove' THEN UPDATE public.sbut_users SET role='user' WHERE id=p_user;
 ELSE RAISE EXCEPTION 'INVALID_ACTION';END IF;
 INSERT INTO public.sbut_admin_audit(actor,action,target,reason) VALUES(auth.uid(),'merchant_'||p_action,p_user,trim(p_reason));
END $$;
CREATE OR REPLACE FUNCTION public.sbut_campaign_deposit19(p_amount bigint,p_reference text,p_reason text,p_confirmed boolean) RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE r wallet_private.money_controls19%rowtype;ref text:=trim(p_reference);result jsonb;
BEGIN
 PERFORM wallet_private.require_admin(true);
 IF p_confirmed IS DISTINCT FROM true OR p_amount IS NULL OR p_amount<10000 OR p_amount>1000000000000 OR length(coalesce(ref,''))<8 OR length(coalesce(trim(p_reason),''))<5 THEN RAISE EXCEPTION 'VERIFIED_DEPOSIT_REQUIRED';END IF;
 PERFORM pg_advisory_xact_lock(hashtextextended(ref,19));
 SELECT * INTO r FROM wallet_private.money_controls19 WHERE reference=ref;
 IF FOUND THEN
  IF r.actor<>auth.uid() OR r.action<>'campaign_deposit' OR r.amount<>p_amount THEN RAISE EXCEPTION 'REFERENCE_CONFLICT';END IF;RETURN r.result;
 END IF;
 IF EXISTS(SELECT 1 FROM wallet_private.settlements WHERE reference=ref) OR EXISTS(SELECT 1 FROM wallet_private.campaign_funding16 WHERE reference=ref) THEN RAISE EXCEPTION 'REFERENCE_CONFLICT';END IF;
 UPDATE wallet_private.treasury SET promotion=promotion+p_amount WHERE id=1;
 IF NOT FOUND THEN RAISE EXCEPTION 'TREASURY_REQUIRED';END IF;
 result:=jsonb_build_object('ok',true,'reference',ref,'amount',p_amount,'order_id','SB'||lpad(nextval('wallet_private.order_serial')::text,23,'0'));
 INSERT INTO wallet_private.money_controls19 VALUES(ref,auth.uid(),NULL,'campaign_deposit',p_amount,trim(p_reason),result,now());
 INSERT INTO public.sbut_admin_audit(actor,action,reference,amount,reason) VALUES(auth.uid(),'campaign_external_deposit',ref,p_amount,trim(p_reason));RETURN result;
END $$;
CREATE OR REPLACE FUNCTION public.sbut_accounts19(p_search text DEFAULT '',p_role text DEFAULT 'all',p_page integer DEFAULT 0) RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE result jsonb;
BEGIN
 PERFORM wallet_private.require_admin();
 IF p_page IS NULL OR p_page<0 OR p_page>100000 OR p_role IS NULL OR p_role NOT IN ('all','user','merchant') OR length(coalesce(p_search,''))>100 THEN RAISE EXCEPTION 'INVALID_FILTER';END IF;
 SELECT jsonb_build_object('total',(SELECT count(*) FROM public.sbut_users WHERE (p_role='all' OR role=p_role) AND (coalesce(p_search,'')='' OR strpos(lower(username||' '||name),lower(p_search))>0)),
 'accounts',coalesce(jsonb_agg(to_jsonb(q)),'[]'::jsonb)) INTO result FROM (
  SELECT u.id,u.username,u.name,u.role,u.kyc,u.frozen,u.balance,u.created_at,
  coalesce((SELECT sum(wallet_private.transaction_delta(t,u.id)) FROM public.sbut_transactions t WHERE t.sender=u.id OR t.recipient=u.id),0) AS ledger_balance
  FROM public.sbut_users u WHERE (p_role='all' OR u.role=p_role) AND (coalesce(p_search,'')='' OR strpos(lower(u.username||' '||u.name),lower(p_search))>0)
  ORDER BY u.created_at DESC,u.id LIMIT 25 OFFSET p_page*25
 )q;RETURN result;
END $$;
CREATE OR REPLACE FUNCTION public.sbut_accounting19(p_user uuid DEFAULT NULL,p_page integer DEFAULT 0) RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN
 PERFORM wallet_private.require_admin();
 IF p_page IS NULL OR p_page<0 OR p_page>100000 THEN RAISE EXCEPTION 'INVALID_PAGE';END IF;
 RETURN jsonb_build_object(
 'totals',(SELECT jsonb_build_object('users',count(*) FILTER(WHERE role='user'),'merchants',count(*) FILTER(WHERE role='merchant'),'balances',coalesce(sum(balance),0)) FROM public.sbut_users),
 'treasury',(SELECT jsonb_build_object('fees',fees,'promotion',promotion) FROM wallet_private.treasury WHERE id=1),
 'pending_recharge',(SELECT coalesce(sum(amount),0) FROM public.sbut_recharges WHERE status IN ('queued','processing','unknown')),
 'by_kind',(SELECT coalesce(jsonb_agg(to_jsonb(k)),'[]'::jsonb) FROM (SELECT kind,status,count(*) AS count,sum(amount) AS amount,sum(fee) AS fee,sum(commission) AS commission FROM public.sbut_transactions WHERE p_user IS NULL OR sender=p_user OR recipient=p_user GROUP BY kind,status ORDER BY kind,status)k),
 'transaction_count',(SELECT count(*) FROM public.sbut_transactions WHERE p_user IS NULL OR sender=p_user OR recipient=p_user),
 'transactions',(SELECT coalesce(jsonb_agg(to_jsonb(x)),'[]'::jsonb) FROM (
 SELECT t.*,s.username AS sender_handle,r.username AS recipient_handle,o.order_id FROM public.sbut_transactions t
 JOIN public.sbut_users s ON s.id=t.sender JOIN public.sbut_users r ON r.id=t.recipient
 LEFT JOIN wallet_private.order_ids o ON o.source_kind='sbut_transactions' AND o.source_id=t.id
 WHERE p_user IS NULL OR t.sender=p_user OR t.recipient=p_user ORDER BY t.created_at DESC,t.id LIMIT 50 OFFSET p_page*50)x),
 'controls',(SELECT coalesce(jsonb_agg(to_jsonb(x)),'[]'::jsonb) FROM (SELECT reference,actor,target,action,amount,reason,created_at,result->>'order_id' AS order_id FROM wallet_private.money_controls19 WHERE p_user IS NULL OR target=p_user ORDER BY created_at DESC LIMIT 50 OFFSET p_page*50)x));
END $$;
DO $$ DECLARE r record;BEGIN
 FOR r IN SELECT p.oid::regprocedure signature,n.nspname FROM pg_proc p JOIN pg_namespace n ON n.oid=p.pronamespace
 WHERE (n.nspname='public' AND p.proname IN ('sbut_admin_money19','sbut_merchant_review19','sbut_campaign_deposit19','sbut_accounts19','sbut_accounting19','sbut_admin_adjust','sbut_apply_merchant_v3')) OR (n.nspname='wallet_private' AND p.proname IN ('fund_merchant','transaction_delta'))
 LOOP EXECUTE format('REVOKE ALL ON FUNCTION %s FROM PUBLIC,anon,authenticated',r.signature);IF r.nspname='public' THEN EXECUTE format('GRANT EXECUTE ON FUNCTION %s TO authenticated',r.signature);END IF;END LOOP;
END $$;
REVOKE ALL ON FUNCTION public.sbut_admin_adjust(uuid,bigint,text,text,text) FROM PUBLIC,anon,authenticated;
NOTIFY pgrst,'reload schema';COMMIT;
SELECT true AS admin_money_merchant_accounts_ready;

-- WALLET IDENTITY AND EXPERIENCE UPDATE 021
-- SBUT PAY021: adult registration, reviewed identity gates, restartable holds and named own history.
BEGIN;
CREATE TABLE IF NOT EXISTS wallet_private.registration_age21(user_id uuid PRIMARY KEY REFERENCES auth.users,dob date NOT NULL,created_at timestamptz NOT NULL DEFAULT now());
ALTER TABLE wallet_private.registration_age21 ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.registration_age21 FROM PUBLIC,anon,authenticated,service_role;
CREATE OR REPLACE FUNCTION public.sbut_register21(p_user uuid,p_username text,p_alias text,p_name text,p_pin text,p_email text,p_dob date) RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN
 IF p_dob IS NULL OR p_dob>(now() AT TIME ZONE 'Asia/Dhaka')::date-interval '18 years' OR p_dob<date '1900-01-01' THEN RAISE EXCEPTION 'AGE_18_REQUIRED';END IF;
 INSERT INTO wallet_private.registration_age21(user_id,dob) VALUES(p_user,p_dob);
 PERFORM public.sbut_access_register_email(p_user,p_username,p_alias,p_name,p_pin,p_email);
END $$;
CREATE OR REPLACE FUNCTION wallet_private.registration_guard21() RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN
 IF NOT EXISTS(SELECT 1 FROM wallet_private.registration_age21 WHERE user_id=NEW.id AND dob<=(now() AT TIME ZONE 'Asia/Dhaka')::date-interval '18 years') THEN RAISE EXCEPTION 'AGE_18_REQUIRED';END IF;RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS registration_guard21 ON public.sbut_users;
CREATE TRIGGER registration_guard21 BEFORE INSERT ON public.sbut_users FOR EACH ROW EXECUTE FUNCTION wallet_private.registration_guard21();
CREATE OR REPLACE FUNCTION wallet_private.identity_ready21(p_user uuid) RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path='' AS $$
 SELECT EXISTS(SELECT 1 FROM public.sbut_users u JOIN public.sbut_kyc k ON k.user_id=u.id JOIN public.sbut_identity_reviews r ON r.user_id=u.id
 WHERE u.id=p_user AND u.kyc='approved' AND NOT u.frozen AND NOT u.security_hold AND length(trim(u.name))>=2
 AND k.dob<=(now() AT TIME ZONE 'Asia/Dhaka')::date-interval '18 years' AND k.dob>=date '1900-01-01' AND k.nid ~ '^([0-9]{10}|[0-9]{13}|[0-9]{17})$'
 AND r.status='reviewed_approved' AND r.submitted_at=k.submitted_at
 AND EXISTS(SELECT 1 FROM wallet_private.login_emails WHERE user_id=u.id)
 AND EXISTS(SELECT 1 FROM wallet_private.pins WHERE user_id=u.id)
 AND EXISTS(SELECT 1 FROM wallet_private.media_objects WHERE user_id=u.id AND kind='front' AND object_key=k.front_path)
 AND EXISTS(SELECT 1 FROM wallet_private.media_objects WHERE user_id=u.id AND kind='back' AND object_key=k.back_path)
 AND EXISTS(SELECT 1 FROM wallet_private.media_objects WHERE user_id=u.id AND kind='selfie' AND object_key=k.selfie_path));
$$;
CREATE OR REPLACE FUNCTION wallet_private.require_verified21() RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN PERFORM wallet_private.check_operational_session();IF NOT wallet_private.identity_ready21(auth.uid()) THEN RAISE EXCEPTION 'IDENTITY_REVIEW_REQUIRED';END IF;END $$;
CREATE OR REPLACE FUNCTION wallet_private.identity_guard21() RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN
 IF NEW.dob IS NULL OR NEW.dob>(now() AT TIME ZONE 'Asia/Dhaka')::date-interval '18 years' OR NEW.dob<date '1900-01-01' THEN RAISE EXCEPTION 'AGE_18_REQUIRED';END IF;
 IF EXISTS(SELECT 1 FROM wallet_private.registration_age21 WHERE user_id=NEW.user_id AND dob<>NEW.dob) THEN RAISE EXCEPTION 'DOB_MISMATCH';END IF;RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS identity_guard21 ON public.sbut_kyc;
CREATE TRIGGER identity_guard21 BEFORE INSERT OR UPDATE ON public.sbut_kyc FOR EACH ROW EXECUTE FUNCTION wallet_private.identity_guard21();
CREATE OR REPLACE FUNCTION wallet_private.money_identity21() RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN
 IF NEW.kind IN ('send','cashin','cashout','recharge') THEN
  IF NOT wallet_private.identity_ready21(NEW.sender) OR (NEW.kind<>'recharge' AND NOT wallet_private.identity_ready21(NEW.recipient)) THEN RAISE EXCEPTION 'IDENTITY_REVIEW_REQUIRED';END IF;
 END IF;RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS money_identity21 ON public.sbut_transactions;
CREATE TRIGGER money_identity21 BEFORE INSERT ON public.sbut_transactions FOR EACH ROW EXECUTE FUNCTION wallet_private.money_identity21();
CREATE OR REPLACE FUNCTION public.sbut_profile_status21() RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN PERFORM wallet_private.require_session();RETURN jsonb_build_object('ready',wallet_private.identity_ready21(auth.uid()),'owner',EXISTS(SELECT 1 FROM wallet_private.admins WHERE user_id=auth.uid() AND kind='founder'),'review',(SELECT status FROM public.sbut_identity_reviews WHERE user_id=auth.uid()));END $$;
CREATE OR REPLACE FUNCTION public.sbut_quote16(p_recipient text,p_amount bigint,p_kind text,p_request uuid,p_operator text DEFAULT NULL,p_money_request uuid DEFAULT NULL)
RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE v public.sbut_users%rowtype; me public.sbut_users%rowtype;o wallet_private.payment_orders16%rowtype;r public.sbut_requests%rowtype;f bigint:=0;c bigint:=0;handle text:=p_recipient;n bigint:=p_amount;kind text:=p_kind; reserve_id uuid;
BEGIN
 PERFORM wallet_private.require_verified21();
 SELECT * INTO me FROM public.sbut_users WHERE id=auth.uid();
 IF me.kyc IS DISTINCT FROM 'approved' THEN RETURN jsonb_build_object('error','KYC_REQUIRED');END IF;
 IF p_request IS NULL OR kind IS NULL OR kind NOT IN ('send','cashout','cashin','recharge') THEN RETURN jsonb_build_object('error','INVALID_AMOUNT');END IF;
 IF p_money_request IS NOT NULL THEN
  SELECT * INTO r FROM public.sbut_requests WHERE id=p_money_request AND payer=me.id AND status='pending';
  IF NOT FOUND THEN RETURN jsonb_build_object('error','REQUEST_UNAVAILABLE');END IF;
  SELECT '@'||username INTO handle FROM public.sbut_users WHERE id=r.requester;n:=r.amount;kind:='send';
 END IF;
 IF n IS NULL OR n<1000 OR n>1000000000000 THEN RETURN jsonb_build_object('error','INVALID_AMOUNT');END IF;
 IF kind='recharge' THEN
  IF handle IS NULL OR handle !~ '^8801[3-9][0-9]{8}$' OR p_operator IS NULL OR p_operator NOT IN ('grameenphone','banglalink','robi','airtel','teletalk') THEN RETURN jsonb_build_object('error','INVALID_RECHARGE');END IF;
  SELECT (public_config->>'reserve_user')::uuid INTO reserve_id FROM wallet_private.integrations WHERE name='recharge' AND enabled AND public_config ? 'reserve_user';
  IF reserve_id IS NULL THEN RETURN jsonb_build_object('error','PROVIDER_UNAVAILABLE');END IF;
 ELSE
  IF handle IS NULL OR lower(trim(leading '@' from trim(handle))) !~ '^[a-z0-9_]{3,24}$' THEN RETURN jsonb_build_object('error','INVALID_RECIPIENT');END IF;
  SELECT * INTO v FROM public.sbut_users WHERE username=lower(trim(leading '@' from trim(handle))) AND kyc='approved' AND NOT frozen;
  IF NOT FOUND OR v.id=me.id THEN RETURN jsonb_build_object('error','INVALID_RECIPIENT');END IF;
  IF NOT wallet_private.identity_ready21(v.id) THEN RAISE EXCEPTION 'RECIPIENT_IDENTITY_REQUIRED';END IF;
  handle:='@'||v.username;
  IF (kind='cashout' AND v.role<>'merchant') OR (kind='cashin' AND me.role<>'merchant') THEN RETURN jsonb_build_object('error','MERCHANT_REQUIRED');END IF;
 END IF;
 f:=CASE WHEN kind='send' THEN CASE WHEN n<10000 THEN 0 WHEN n<50000 THEN ceil(n*0.015)::bigint ELSE ceil(n*0.02)::bigint END WHEN kind='cashout' THEN CASE WHEN n<100000 THEN 600 ELSE ceil(n*0.012)::bigint END ELSE 0 END;
 IF kind IN ('cashin','cashout') THEN c:=CASE WHEN n<100000 THEN 400 ELSE floor(n*0.008)::bigint END;END IF;
 SELECT * INTO o FROM wallet_private.payment_orders16 WHERE user_id=me.id AND request_id=p_request;
 IF FOUND THEN
  IF o.recipient<>handle OR o.amount<>n OR o.kind<>kind OR o.operator IS DISTINCT FROM p_operator OR o.money_request IS DISTINCT FROM p_money_request THEN RETURN jsonb_build_object('error','REQUEST_CONFLICT');END IF;
  RETURN wallet_private.order_summary16(o.id);
 END IF;
 IF EXISTS(SELECT 1 FROM public.sbut_transactions WHERE sender=me.id AND request_id=p_request) THEN RETURN jsonb_build_object('error','REQUEST_CONFLICT');END IF;
 IF (SELECT count(*) FROM wallet_private.payment_orders16 WHERE user_id=me.id AND created_at>now()-interval '15 minutes')>=60 THEN RETURN jsonb_build_object('error','TRY_LATER');END IF;
 INSERT INTO wallet_private.payment_orders16(user_id,request_id,order_id,kind,recipient,recipient_id,recipient_name,amount,fee,commission,operator,money_request)
 VALUES(me.id,p_request,'SB'||lpad(nextval('wallet_private.order_serial')::text,23,'0'),kind,handle,CASE WHEN kind='recharge' THEN reserve_id ELSE v.id END,CASE WHEN kind='recharge' THEN handle ELSE v.name END,n,f,c,p_operator,p_money_request) RETURNING * INTO o;
 RETURN wallet_private.order_summary16(o.id);
END $$;
CREATE OR REPLACE FUNCTION public.sbut_welcome_reward16(p_claim boolean DEFAULT false) RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE u public.sbut_users%rowtype;tx uuid;
BEGIN
 PERFORM wallet_private.require_verified21();
 SELECT * INTO u FROM public.sbut_users WHERE id=auth.uid() FOR UPDATE;
 IF u.bonus_paid THEN RETURN jsonb_build_object('status','paid','amount',10000);END IF;
 IF u.kyc<>'approved' OR NOT EXISTS(SELECT 1 FROM public.sbut_kyc WHERE user_id=u.id) THEN RETURN jsonb_build_object('status','not_eligible','amount',10000);END IF;
 IF NOT p_claim THEN RETURN jsonb_build_object('status','eligible','amount',10000);END IF;
 UPDATE wallet_private.treasury SET promotion=promotion-10000 WHERE id=1 AND promotion>=10000;
 IF NOT FOUND THEN RETURN jsonb_build_object('status','waiting_for_funds','amount',10000);END IF;
 UPDATE public.sbut_users SET balance=balance+10000,bonus_paid=true WHERE id=u.id;
 INSERT INTO public.sbut_transactions(request_id,sender,recipient,kind,amount,fee) VALUES(gen_random_uuid(),u.id,u.id,'bonus',10000,0) RETURNING id INTO tx;
 RETURN jsonb_build_object('status','paid','amount',10000,'transaction_id',tx);
END $$;
CREATE OR REPLACE FUNCTION public.sbut_cancel_hold16(p_order uuid,p_hold uuid) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN
 PERFORM wallet_private.require_session();
 UPDATE wallet_private.payment_orders16 SET state='authorized',hold_id=NULL,ready_at=NULL WHERE id=p_order AND user_id=auth.uid() AND hold_id=p_hold AND state='holding';
END $$;
create or replace function public.sbut_apply_merchant_v3(p_business text,p_address text,p_email text) returns void language plpgsql security definer set search_path='' as $$
begin
 perform wallet_private.require_verified21();
 if not exists(select 1 from public.sbut_users where id=auth.uid() and kyc='approved') then raise exception 'KYC_REQUIRED';end if;
 if p_business is null or p_address is null or p_email is null or length(trim(p_business))<2 or length(trim(p_address))<5 or p_email !~ '^[^@[:space:]]+@[^@[:space:]]+\.[^@[:space:]]+$' then raise exception 'INVALID_APPLICATION';end if;
 insert into public.sbut_merchant_applications(user_id,business,address,email) values(auth.uid(),p_business,p_address,p_email) on conflict(user_id) do update set business=excluded.business,address=excluded.address,email=excluded.email,status='pending',created_at=now(),reviewed_at=null where sbut_merchant_applications.status='rejected';
end $$;
CREATE OR REPLACE FUNCTION public.sbut_submit_identity(p_nid text,p_dob date,p_front text,p_back text,p_selfie text,p_pin text,p_name text) RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE u uuid:=auth.uid(); k text; v text;
BEGIN
 PERFORM wallet_private.require_session();
 UPDATE public.sbut_users SET kyc='draft' WHERE id=u AND kyc='approved' AND NOT wallet_private.identity_ready21(u);
 PERFORM 1 FROM public.sbut_users WHERE id=u AND kyc IN ('draft','rejected','pending') FOR UPDATE;
 IF NOT FOUND THEN RAISE EXCEPTION 'KYC_STATE';END IF;
 IF NOT EXISTS(SELECT 1 FROM public.sbut_consents WHERE user_id=u AND version='2026-10-26-v2') THEN RAISE EXCEPTION 'CONSENT_REQUIRED';END IF;
 IF p_name IS NULL OR length(trim(p_name)) NOT BETWEEN 2 AND 150 THEN RAISE EXCEPTION 'INVALID_NAME';END IF;
 IF p_pin IS NULL OR p_pin !~ '^[0-9]{6}$' THEN RAISE EXCEPTION 'INVALID_PIN';END IF;
 IF EXISTS(SELECT 1 FROM wallet_private.access_identity WHERE user_id=u AND extensions.crypt(p_pin,login_pin_hash)=login_pin_hash) THEN RAISE EXCEPTION 'PINS_MUST_DIFFER';END IF;
 IF p_nid IS NULL OR p_nid !~ '^([0-9]{10}|[0-9]{13}|[0-9]{17})$' OR p_dob IS NULL OR p_dob>current_date-interval '18 years' OR p_dob<date '1900-01-01' THEN RAISE EXCEPTION 'INVALID_IDENTITY';END IF;
 IF EXISTS(SELECT 1 FROM public.sbut_kyc WHERE nid=p_nid AND user_id<>u) THEN RAISE EXCEPTION 'NID_ALREADY_USED';END IF;
 FOREACH k IN ARRAY ARRAY['front','back','selfie'] LOOP
  v:=CASE k WHEN 'front' THEN p_front WHEN 'back' THEN p_back ELSE p_selfie END;
  IF v IS NULL OR NOT EXISTS(SELECT 1 FROM wallet_private.media_objects WHERE object_key=v AND user_id=u AND kind=k) THEN RAISE EXCEPTION 'UPLOAD_REQUIRED';END IF;
 END LOOP;
 INSERT INTO public.sbut_kyc(user_id,nid,dob,front_path,back_path,selfie_path,submitted_at) VALUES(u,p_nid,p_dob,p_front,p_back,p_selfie,now()) ON CONFLICT(user_id) DO UPDATE SET nid=excluded.nid,dob=excluded.dob,front_path=excluded.front_path,back_path=excluded.back_path,selfie_path=excluded.selfie_path,submitted_at=now();
 INSERT INTO wallet_private.pins(user_id,hash) VALUES(u,extensions.crypt(p_pin,extensions.gen_salt('bf',12))) ON CONFLICT(user_id) DO UPDATE SET hash=excluded.hash,failures=0,locked_until=NULL;
 UPDATE public.sbut_users SET name=trim(p_name),kyc='pending' WHERE id=u;
END $$;
CREATE TABLE IF NOT EXISTS wallet_private.parties21(transaction_id uuid PRIMARY KEY REFERENCES public.sbut_transactions,sender_name text,sender_handle text,recipient_name text,recipient_handle text);
ALTER TABLE wallet_private.parties21 ENABLE ROW LEVEL SECURITY;REVOKE ALL ON wallet_private.parties21 FROM PUBLIC,anon,authenticated;
CREATE OR REPLACE FUNCTION wallet_private.snapshot_parties21() RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN INSERT INTO wallet_private.parties21 SELECT NEW.id,s.name,s.username,r.name,r.username FROM public.sbut_users s,public.sbut_users r WHERE s.id=NEW.sender AND r.id=NEW.recipient;RETURN NEW;END $$;
DROP TRIGGER IF EXISTS snapshot_parties21 ON public.sbut_transactions;
CREATE TRIGGER snapshot_parties21 AFTER INSERT ON public.sbut_transactions FOR EACH ROW EXECUTE FUNCTION wallet_private.snapshot_parties21();
CREATE OR REPLACE FUNCTION public.sbut_history21() RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN PERFORM wallet_private.require_session();RETURN (SELECT coalesce(jsonb_agg(to_jsonb(x)),'[]'::jsonb) FROM (
 SELECT t.*,coalesce(p.sender_name,s.name) AS sender_name,coalesce(p.sender_handle,s.username) AS sender_handle,coalesce(p.recipient_name,r.name) AS recipient_name,coalesce(p.recipient_handle,r.username) AS recipient_handle,o.order_id
 FROM public.sbut_transactions t JOIN public.sbut_users s ON s.id=t.sender JOIN public.sbut_users r ON r.id=t.recipient LEFT JOIN wallet_private.parties21 p ON p.transaction_id=t.id LEFT JOIN wallet_private.order_ids o ON o.source_kind='sbut_transactions' AND o.source_id=t.id
 WHERE t.sender=auth.uid() OR t.recipient=auth.uid() ORDER BY t.created_at DESC,t.id LIMIT 100)x);END $$;
DO $$ DECLARE r record;BEGIN FOR r IN SELECT p.oid::regprocedure signature,n.nspname,p.proname FROM pg_proc p JOIN pg_namespace n ON n.oid=p.pronamespace WHERE (n.nspname='wallet_private' AND p.proname IN ('registration_guard21','identity_ready21','require_verified21','identity_guard21','money_identity21','snapshot_parties21')) OR (n.nspname='public' AND p.proname IN ('sbut_register21','sbut_profile_status21','sbut_history21','sbut_quote16','sbut_welcome_reward16','sbut_cancel_hold16','sbut_apply_merchant_v3','sbut_submit_identity')) LOOP
 EXECUTE format('REVOKE ALL ON FUNCTION %s FROM PUBLIC,anon,authenticated,service_role',r.signature);
 IF r.nspname='public' THEN EXECUTE format('GRANT EXECUTE ON FUNCTION %s TO %I',r.signature,CASE WHEN r.proname='sbut_register21' THEN 'service_role' ELSE 'authenticated' END);END IF;END LOOP;END $$;
REVOKE ALL ON FUNCTION public.sbut_access_register(uuid,text,text,text,text),public.sbut_access_register_email(uuid,text,text,text,text,text) FROM service_role;
NOTIFY pgrst,'reload schema';COMMIT;

-- USER VERIFICATION UPDATE 022
-- SBUT PAY022: explicit reviewed decisions, safe application resume and paid-reward visibility.
BEGIN;
CREATE TABLE IF NOT EXISTS wallet_private.verification_hold22(user_id uuid PRIMARY KEY REFERENCES public.sbut_users(id),reason text NOT NULL,actor uuid REFERENCES auth.users(id),created_at timestamptz NOT NULL DEFAULT now());
ALTER TABLE wallet_private.verification_hold22 ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.verification_hold22 FROM PUBLIC,anon,authenticated,service_role;
CREATE OR REPLACE FUNCTION wallet_private.identity_ready21(p_user uuid) RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path='' AS $$
 SELECT EXISTS(SELECT 1 FROM public.sbut_users u JOIN public.sbut_kyc k ON k.user_id=u.id JOIN public.sbut_identity_reviews r ON r.user_id=u.id
 WHERE u.id=p_user AND NOT EXISTS(SELECT 1 FROM wallet_private.verification_hold22 h WHERE h.user_id=u.id) AND u.kyc='approved' AND NOT u.frozen AND NOT u.security_hold AND length(trim(u.name))>=2
 AND k.dob<=(now() AT TIME ZONE 'Asia/Dhaka')::date-interval '18 years' AND k.dob>=date '1900-01-01' AND k.nid ~ '^([0-9]{10}|[0-9]{13}|[0-9]{17})$'
 AND r.status='reviewed_approved' AND r.submitted_at=k.submitted_at
 AND EXISTS(SELECT 1 FROM wallet_private.login_emails WHERE user_id=u.id)
 AND EXISTS(SELECT 1 FROM wallet_private.pins WHERE user_id=u.id)
 AND EXISTS(SELECT 1 FROM wallet_private.media_objects WHERE user_id=u.id AND kind='front' AND object_key=k.front_path)
 AND EXISTS(SELECT 1 FROM wallet_private.media_objects WHERE user_id=u.id AND kind='back' AND object_key=k.back_path)
 AND EXISTS(SELECT 1 FROM wallet_private.media_objects WHERE user_id=u.id AND kind='selfie' AND object_key=k.selfie_path));
$$;
CREATE OR REPLACE FUNCTION public.sbut_verification_queue22(p_offset int DEFAULT 0) RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE result jsonb;BEGIN
 PERFORM wallet_private.require_admin();
 SELECT coalesce(jsonb_agg(to_jsonb(x)),'[]'::jsonb) INTO result FROM (
 SELECT u.id,u.name,u.username,u.kyc,u.frozen,k.submitted_at,
 CASE WHEN h.user_id IS NOT NULL THEN 'hold' ELSE coalesce(r.status,'not_submitted') END AS review,
 wallet_private.identity_ready21(u.id) AS ready
 FROM public.sbut_users u LEFT JOIN public.sbut_kyc k ON k.user_id=u.id LEFT JOIN public.sbut_identity_reviews r ON r.user_id=u.id LEFT JOIN wallet_private.verification_hold22 h ON h.user_id=u.id
 ORDER BY CASE WHEN r.status='reviewed_approved' THEN 1 ELSE 0 END,u.created_at,u.id LIMIT 50 OFFSET greatest(0,p_offset)) x;RETURN result;END $$;
CREATE OR REPLACE FUNCTION public.sbut_verification_decide22(p_user uuid,p_decision text,p_reason text,p_submitted timestamptz) RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE k public.sbut_kyc%rowtype;BEGIN
 PERFORM wallet_private.require_admin();
 IF p_decision IS NULL OR p_decision NOT IN ('approve','reject','hold') OR p_reason IS NULL OR length(trim(p_reason)) NOT BETWEEN 5 AND 1000 THEN RAISE EXCEPTION 'INVALID_REVIEW';END IF;
 PERFORM 1 FROM public.sbut_users WHERE id=p_user FOR UPDATE;IF NOT FOUND THEN RAISE EXCEPTION 'ACCOUNT_NOT_FOUND';END IF;
 SELECT * INTO k FROM public.sbut_kyc WHERE user_id=p_user FOR UPDATE;
 IF NOT FOUND THEN RAISE EXCEPTION 'IDENTITY_NOT_SUBMITTED';END IF;
 IF p_submitted IS NULL OR k.submitted_at IS DISTINCT FROM p_submitted THEN RAISE EXCEPTION 'APPLICATION_CHANGED_REFRESH';END IF;
 INSERT INTO public.sbut_identity_reviews(user_id,submitted_at,status,reviewed_at,reviewer,reason) VALUES(p_user,k.submitted_at,CASE WHEN p_decision='approve' THEN 'reviewed_approved' WHEN p_decision='reject' THEN 'reviewed_rejected' ELSE 'pending' END,now(),auth.uid(),trim(p_reason)) ON CONFLICT(user_id) DO UPDATE SET submitted_at=excluded.submitted_at,status=excluded.status,reviewed_at=excluded.reviewed_at,reviewer=excluded.reviewer,reason=excluded.reason;
 UPDATE public.sbut_users SET kyc=CASE WHEN p_decision='approve' THEN 'approved' WHEN p_decision='reject' THEN 'rejected' ELSE 'pending' END WHERE id=p_user;
 IF p_decision<>'hold' THEN DELETE FROM wallet_private.verification_hold22 WHERE user_id=p_user;END IF;
 IF p_decision='approve' AND NOT wallet_private.identity_ready21(p_user) THEN RAISE EXCEPTION 'IDENTITY_INCOMPLETE_OR_RESTRICTED';END IF;
 IF p_decision='hold' THEN INSERT INTO wallet_private.verification_hold22(user_id,reason,actor) VALUES(p_user,trim(p_reason),auth.uid()) ON CONFLICT(user_id) DO UPDATE SET reason=excluded.reason,actor=excluded.actor,created_at=now();ELSE DELETE FROM wallet_private.verification_hold22 WHERE user_id=p_user;END IF;
 INSERT INTO public.sbut_admin_audit(actor,action,target,reason) VALUES(auth.uid(),'verification_'||p_decision,p_user,trim(p_reason));
 RETURN jsonb_build_object('decision',p_decision,'ready',wallet_private.identity_ready21(p_user));END $$;
CREATE OR REPLACE FUNCTION public.sbut_profile_status21() RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN PERFORM wallet_private.require_session();RETURN jsonb_build_object('ready',wallet_private.identity_ready21(auth.uid()),'claimed',coalesce((SELECT bonus_paid FROM public.sbut_users WHERE id=auth.uid()),false),'owner',EXISTS(SELECT 1 FROM wallet_private.admins WHERE user_id=auth.uid() AND kind='founder'),'review',CASE WHEN EXISTS(SELECT 1 FROM wallet_private.verification_hold22 WHERE user_id=auth.uid()) THEN 'hold' ELSE (SELECT status FROM public.sbut_identity_reviews WHERE user_id=auth.uid()) END);END $$;
CREATE OR REPLACE FUNCTION public.sbut_resume_application() RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE u uuid:=auth.uid(); state text; result jsonb;
BEGIN
 PERFORM wallet_private.require_session();
 SELECT kyc INTO state FROM public.sbut_users WHERE id=u AND NOT frozen FOR UPDATE;
 IF state IS NULL OR (state NOT IN ('draft','pending','rejected') AND NOT (state='approved' AND NOT wallet_private.identity_ready21(u))) THEN RAISE EXCEPTION 'APPLICATION_NOT_EDITABLE';END IF;
 SELECT jsonb_build_object('name',a.name,'username',a.username,'nid',k.nid,'dob',k.dob,
  'front',k.front_path,'back',k.back_path,'selfie',k.selfie_path)
 INTO result FROM public.sbut_users a JOIN public.sbut_kyc k ON k.user_id=a.id WHERE a.id=u;
 -- Draft pauses the approval job until the user submits again. Row lock prevents approval/edit races.
 UPDATE public.sbut_users SET kyc='draft' WHERE id=u;
 RETURN result;
END $$;
CREATE OR REPLACE FUNCTION public.sbut_welcome_reward16(p_claim boolean DEFAULT false) RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE u public.sbut_users%rowtype;tx uuid;
BEGIN
 PERFORM wallet_private.require_session();
 SELECT * INTO u FROM public.sbut_users WHERE id=auth.uid() FOR UPDATE;
 IF u.bonus_paid THEN RETURN jsonb_build_object('status','paid','amount',10000);END IF;
 PERFORM wallet_private.require_verified21();
 IF u.kyc<>'approved' OR NOT EXISTS(SELECT 1 FROM public.sbut_kyc WHERE user_id=u.id) THEN RETURN jsonb_build_object('status','not_eligible','amount',10000);END IF;
 IF NOT p_claim THEN RETURN jsonb_build_object('status','eligible','amount',10000);END IF;
 UPDATE wallet_private.treasury SET promotion=promotion-10000 WHERE id=1 AND promotion>=10000;
 IF NOT FOUND THEN RETURN jsonb_build_object('status','waiting_for_funds','amount',10000);END IF;
 UPDATE public.sbut_users SET balance=balance+10000,bonus_paid=true WHERE id=u.id;
 INSERT INTO public.sbut_transactions(request_id,sender,recipient,kind,amount,fee) VALUES(gen_random_uuid(),u.id,u.id,'bonus',10000,0) RETURNING id INTO tx;
 RETURN jsonb_build_object('status','paid','amount',10000,'transaction_id',tx);
END $$;
REVOKE ALL ON FUNCTION public.sbut_verification_queue22(int),public.sbut_verification_decide22(uuid,text,text,timestamptz) FROM PUBLIC,anon,service_role;
GRANT EXECUTE ON FUNCTION public.sbut_verification_queue22(int),public.sbut_verification_decide22(uuid,text,text,timestamptz) TO authenticated;
NOTIFY pgrst,'reload schema';
COMMIT;

-- FOUNDER ADMISSION UPDATE 023
-- SBUT PAY023: explicit Founder admission without uploaded documents; never fabricates KYC.
BEGIN;
CREATE TABLE IF NOT EXISTS wallet_private.admission23(user_id uuid PRIMARY KEY REFERENCES public.sbut_users(id),status text NOT NULL CHECK(status IN ('active','hold','rejected')),adult_attested boolean NOT NULL,actor uuid NOT NULL REFERENCES auth.users(id),reason text NOT NULL,updated_at timestamptz NOT NULL DEFAULT now());
ALTER TABLE wallet_private.admission23 ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.admission23 FROM PUBLIC,anon,authenticated,service_role;
CREATE OR REPLACE FUNCTION wallet_private.identity_ready21(p_user uuid) RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path='' AS $$
 SELECT EXISTS(SELECT 1 FROM public.sbut_users u JOIN public.sbut_kyc k ON k.user_id=u.id JOIN public.sbut_identity_reviews r ON r.user_id=u.id
 WHERE u.id=p_user AND NOT EXISTS(SELECT 1 FROM wallet_private.verification_hold22 h WHERE h.user_id=u.id) AND u.kyc='approved' AND NOT u.frozen AND NOT u.security_hold AND length(trim(u.name))>=2
 AND k.dob<=(now() AT TIME ZONE 'Asia/Dhaka')::date-interval '18 years' AND k.dob>=date '1900-01-01' AND k.nid ~ '^([0-9]{10}|[0-9]{13}|[0-9]{17})$'
 AND r.status='reviewed_approved' AND r.submitted_at=k.submitted_at
 AND EXISTS(SELECT 1 FROM wallet_private.login_emails WHERE user_id=u.id)
 AND EXISTS(SELECT 1 FROM wallet_private.pins WHERE user_id=u.id)
 AND EXISTS(SELECT 1 FROM wallet_private.media_objects WHERE user_id=u.id AND kind='front' AND object_key=k.front_path)
 AND EXISTS(SELECT 1 FROM wallet_private.media_objects WHERE user_id=u.id AND kind='back' AND object_key=k.back_path)
 AND EXISTS(SELECT 1 FROM wallet_private.media_objects WHERE user_id=u.id AND kind='selfie' AND object_key=k.selfie_path)) OR EXISTS(SELECT 1 FROM public.sbut_users u JOIN wallet_private.admission23 a ON a.user_id=u.id
 WHERE u.id=p_user AND a.status='active' AND a.adult_attested AND u.kyc='approved' AND NOT u.frozen AND NOT u.security_hold
 AND length(trim(u.name))>=2 AND NOT EXISTS(SELECT 1 FROM wallet_private.verification_hold22 WHERE user_id=u.id)
 AND NOT EXISTS(SELECT 1 FROM wallet_private.registration_age21 WHERE user_id=u.id AND dob>(now() AT TIME ZONE 'Asia/Dhaka')::date-interval '18 years')
 AND NOT EXISTS(SELECT 1 FROM public.sbut_kyc WHERE user_id=u.id AND dob>(now() AT TIME ZONE 'Asia/Dhaka')::date-interval '18 years')
 AND EXISTS(SELECT 1 FROM wallet_private.login_emails WHERE user_id=u.id)
 AND EXISTS(SELECT 1 FROM wallet_private.pins WHERE user_id=u.id));
$$;
CREATE OR REPLACE FUNCTION wallet_private.verification_issues23(p_user uuid) RETURNS jsonb LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path='' AS $$
DECLARE result jsonb:='[]'::jsonb;u public.sbut_users%rowtype;k public.sbut_kyc%rowtype;BEGIN
 SELECT * INTO u FROM public.sbut_users WHERE id=p_user;SELECT * INTO k FROM public.sbut_kyc WHERE user_id=p_user;
 IF u.frozen THEN result:=result||'"ACCOUNT_FROZEN"'::jsonb;END IF;
 IF u.security_hold THEN result:=result||'"SECURITY_HOLD"'::jsonb;END IF;
 IF NOT EXISTS(SELECT 1 FROM wallet_private.login_emails WHERE user_id=p_user) THEN result:=result||'"LOGIN_EMAIL_MISSING"'::jsonb;END IF;
 IF NOT EXISTS(SELECT 1 FROM wallet_private.pins WHERE user_id=p_user) THEN result:=result||'"TRANSACTION_PIN_MISSING"'::jsonb;END IF;
 IF k.user_id IS NULL THEN result:=result||'"IDENTITY_NOT_SUBMITTED"'::jsonb;ELSE
 IF k.nid IS NULL OR k.nid !~ '^([0-9]{10}|[0-9]{13}|[0-9]{17})$' THEN result:=result||'"NID_INVALID"'::jsonb;END IF;
 IF k.dob IS NULL OR k.dob<date '1900-01-01' OR k.dob>(now() AT TIME ZONE 'Asia/Dhaka')::date-interval '18 years' THEN result:=result||'"ADULT_DOB_REQUIRED"'::jsonb;END IF;
 IF NOT EXISTS(SELECT 1 FROM wallet_private.media_objects WHERE user_id=p_user AND kind='front' AND object_key=k.front_path) THEN result:=result||'"FRONT_MEDIA_RECORD_MISSING"'::jsonb;END IF;
 IF NOT EXISTS(SELECT 1 FROM wallet_private.media_objects WHERE user_id=p_user AND kind='back' AND object_key=k.back_path) THEN result:=result||'"BACK_MEDIA_RECORD_MISSING"'::jsonb;END IF;
 IF NOT EXISTS(SELECT 1 FROM wallet_private.media_objects WHERE user_id=p_user AND kind='selfie' AND object_key=k.selfie_path) THEN result:=result||'"SELFIE_MEDIA_RECORD_MISSING"'::jsonb;END IF;
 END IF;RETURN result;END $$;
CREATE OR REPLACE FUNCTION public.sbut_admission23(p_user uuid,p_decision text,p_reason text,p_adult boolean DEFAULT false) RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN
 PERFORM wallet_private.require_admin(true);
 IF p_decision IS NULL OR p_decision NOT IN ('approve','reject','hold') OR p_reason IS NULL OR length(trim(p_reason)) NOT BETWEEN 5 AND 1000 THEN RAISE EXCEPTION 'INVALID_REVIEW';END IF;
 PERFORM 1 FROM public.sbut_users WHERE id=p_user FOR UPDATE;IF NOT FOUND THEN RAISE EXCEPTION 'ACCOUNT_NOT_FOUND';END IF;
 IF p_decision='approve' THEN
 IF p_adult IS DISTINCT FROM true THEN RAISE EXCEPTION 'ADULT_ATTESTATION_REQUIRED';END IF;
 IF EXISTS(SELECT 1 FROM public.sbut_users WHERE id=p_user AND (frozen OR security_hold)) THEN RAISE EXCEPTION 'ACCOUNT_RESTRICTED';END IF;
 IF EXISTS(SELECT 1 FROM wallet_private.registration_age21 WHERE user_id=p_user AND dob>(now() AT TIME ZONE 'Asia/Dhaka')::date-interval '18 years') OR EXISTS(SELECT 1 FROM public.sbut_kyc WHERE user_id=p_user AND dob>(now() AT TIME ZONE 'Asia/Dhaka')::date-interval '18 years') THEN RAISE EXCEPTION 'AGE_18_REQUIRED';END IF;
 IF NOT EXISTS(SELECT 1 FROM wallet_private.login_emails WHERE user_id=p_user) THEN RAISE EXCEPTION 'LOGIN_EMAIL_MISSING';END IF;
 END IF;
 INSERT INTO wallet_private.admission23(user_id,status,adult_attested,actor,reason) VALUES(p_user,CASE p_decision WHEN 'approve' THEN 'active' WHEN 'hold' THEN 'hold' ELSE 'rejected' END,p_adult,auth.uid(),trim(p_reason)) ON CONFLICT(user_id) DO UPDATE SET status=excluded.status,adult_attested=excluded.adult_attested,actor=excluded.actor,reason=excluded.reason,updated_at=now();
 UPDATE public.sbut_users SET kyc=CASE p_decision WHEN 'approve' THEN 'approved' WHEN 'reject' THEN 'rejected' ELSE 'pending' END WHERE id=p_user;
 IF p_decision='hold' THEN INSERT INTO wallet_private.verification_hold22(user_id,reason,actor) VALUES(p_user,trim(p_reason),auth.uid()) ON CONFLICT(user_id) DO UPDATE SET reason=excluded.reason,actor=excluded.actor,created_at=now();ELSE DELETE FROM wallet_private.verification_hold22 WHERE user_id=p_user;END IF;
 -- A rejection/hold must not leave a former document-review approval active.
 IF p_decision<>'approve' THEN UPDATE public.sbut_identity_reviews SET status=CASE p_decision WHEN 'reject' THEN 'reviewed_rejected' ELSE 'pending' END,reviewed_at=now(),reviewer=auth.uid(),reason=trim(p_reason) WHERE user_id=p_user;END IF;
 INSERT INTO public.sbut_admin_audit(actor,action,target,reason) VALUES(auth.uid(),'founder_admission_'||p_decision,p_user,trim(p_reason));
 RETURN jsonb_build_object('decision',p_decision,'ready',wallet_private.identity_ready21(p_user),'mode','admin_approved');END $$;
CREATE OR REPLACE FUNCTION public.sbut_verification_decide22(p_user uuid,p_decision text,p_reason text,p_submitted timestamptz) RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE k public.sbut_kyc%rowtype;BEGIN
 PERFORM wallet_private.require_admin();
 IF p_decision IS NULL OR p_decision NOT IN ('approve','reject','hold') OR p_reason IS NULL OR length(trim(p_reason)) NOT BETWEEN 5 AND 1000 THEN RAISE EXCEPTION 'INVALID_REVIEW';END IF;
 PERFORM 1 FROM public.sbut_users WHERE id=p_user FOR UPDATE;IF NOT FOUND THEN RAISE EXCEPTION 'ACCOUNT_NOT_FOUND';END IF;
 SELECT * INTO k FROM public.sbut_kyc WHERE user_id=p_user FOR UPDATE;
 IF NOT FOUND THEN RAISE EXCEPTION 'IDENTITY_NOT_SUBMITTED';END IF;
 IF p_submitted IS NULL OR k.submitted_at IS DISTINCT FROM p_submitted THEN RAISE EXCEPTION 'APPLICATION_CHANGED_REFRESH';END IF;
 DELETE FROM wallet_private.admission23 WHERE user_id=p_user;
 INSERT INTO public.sbut_identity_reviews(user_id,submitted_at,status,reviewed_at,reviewer,reason) VALUES(p_user,k.submitted_at,CASE WHEN p_decision='approve' THEN 'reviewed_approved' WHEN p_decision='reject' THEN 'reviewed_rejected' ELSE 'pending' END,now(),auth.uid(),trim(p_reason)) ON CONFLICT(user_id) DO UPDATE SET submitted_at=excluded.submitted_at,status=excluded.status,reviewed_at=excluded.reviewed_at,reviewer=excluded.reviewer,reason=excluded.reason;
 UPDATE public.sbut_users SET kyc=CASE WHEN p_decision='approve' THEN 'approved' WHEN p_decision='reject' THEN 'rejected' ELSE 'pending' END WHERE id=p_user;
 IF p_decision<>'hold' THEN DELETE FROM wallet_private.verification_hold22 WHERE user_id=p_user;END IF;
 IF p_decision='approve' AND NOT wallet_private.identity_ready21(p_user) THEN RAISE EXCEPTION 'IDENTITY_INCOMPLETE_OR_RESTRICTED';END IF;
 IF p_decision='hold' THEN INSERT INTO wallet_private.verification_hold22(user_id,reason,actor) VALUES(p_user,trim(p_reason),auth.uid()) ON CONFLICT(user_id) DO UPDATE SET reason=excluded.reason,actor=excluded.actor,created_at=now();ELSE DELETE FROM wallet_private.verification_hold22 WHERE user_id=p_user;END IF;
 INSERT INTO public.sbut_admin_audit(actor,action,target,reason) VALUES(auth.uid(),'verification_'||p_decision,p_user,trim(p_reason));
 RETURN jsonb_build_object('decision',p_decision,'ready',wallet_private.identity_ready21(p_user));END $$;
CREATE OR REPLACE FUNCTION public.sbut_verification_queue22(p_offset int DEFAULT 0) RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE result jsonb;BEGIN
 PERFORM wallet_private.require_admin();
 SELECT coalesce(jsonb_agg(to_jsonb(x)),'[]'::jsonb) INTO result FROM (
 SELECT u.id,u.name,u.username,u.kyc,u.frozen,k.submitted_at,
 CASE WHEN h.user_id IS NOT NULL THEN 'hold' WHEN a.status='active' THEN 'admin_approved' WHEN a.status='rejected' THEN 'rejected' ELSE coalesce(r.status,'not_submitted') END AS review,
 wallet_private.identity_ready21(u.id) AS ready,wallet_private.verification_issues23(u.id) AS issues
 FROM public.sbut_users u LEFT JOIN wallet_private.admission23 a ON a.user_id=u.id LEFT JOIN public.sbut_kyc k ON k.user_id=u.id LEFT JOIN public.sbut_identity_reviews r ON r.user_id=u.id LEFT JOIN wallet_private.verification_hold22 h ON h.user_id=u.id
 ORDER BY CASE WHEN r.status='reviewed_approved' THEN 1 ELSE 0 END,u.created_at,u.id LIMIT 50 OFFSET greatest(0,p_offset)) x;RETURN result;END $$;
CREATE OR REPLACE FUNCTION public.sbut_profile_status21() RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN PERFORM wallet_private.require_session();RETURN jsonb_build_object('ready',wallet_private.identity_ready21(auth.uid()),'claimed',coalesce((SELECT bonus_paid FROM public.sbut_users WHERE id=auth.uid()),false),'owner',EXISTS(SELECT 1 FROM wallet_private.admins WHERE user_id=auth.uid() AND kind='founder'),'review',CASE WHEN EXISTS(SELECT 1 FROM wallet_private.verification_hold22 WHERE user_id=auth.uid()) THEN 'hold' WHEN EXISTS(SELECT 1 FROM wallet_private.admission23 WHERE user_id=auth.uid() AND status='active') THEN 'admin_approved' ELSE (SELECT status FROM public.sbut_identity_reviews WHERE user_id=auth.uid()) END);END $$;
CREATE OR REPLACE FUNCTION public.sbut_welcome_reward16(p_claim boolean DEFAULT false) RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE u public.sbut_users%rowtype;tx uuid;
BEGIN
 PERFORM wallet_private.require_session();
 SELECT * INTO u FROM public.sbut_users WHERE id=auth.uid() FOR UPDATE;
 IF u.bonus_paid THEN RETURN jsonb_build_object('status','paid','amount',10000);END IF;
 PERFORM wallet_private.require_verified21();
 IF u.kyc<>'approved' THEN RETURN jsonb_build_object('status','not_eligible','amount',10000);END IF;
 IF NOT p_claim THEN RETURN jsonb_build_object('status','eligible','amount',10000);END IF;
 UPDATE wallet_private.treasury SET promotion=promotion-10000 WHERE id=1 AND promotion>=10000;
 IF NOT FOUND THEN RETURN jsonb_build_object('status','waiting_for_funds','amount',10000);END IF;
 UPDATE public.sbut_users SET balance=balance+10000,bonus_paid=true WHERE id=u.id;
 INSERT INTO public.sbut_transactions(request_id,sender,recipient,kind,amount,fee) VALUES(gen_random_uuid(),u.id,u.id,'bonus',10000,0) RETURNING id INTO tx;
 RETURN jsonb_build_object('status','paid','amount',10000,'transaction_id',tx);
END $$;
REVOKE ALL ON FUNCTION wallet_private.verification_issues23(uuid),public.sbut_admission23(uuid,text,text,boolean) FROM PUBLIC,anon,authenticated,service_role;
GRANT EXECUTE ON FUNCTION public.sbut_admission23(uuid,text,text,boolean) TO authenticated;
CREATE OR REPLACE FUNCTION public.sbut_first_pin23(p_pin text) RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
BEGIN
 IF NOT wallet_private.access_session_active14() THEN RAISE EXCEPTION 'SESSION_LOCKED';END IF;
 IF p_pin IS NULL OR p_pin !~ '^[0-9]{6}$' THEN RAISE EXCEPTION 'INVALID_PIN';END IF;
 PERFORM 1 FROM public.sbut_users WHERE id=auth.uid() AND kyc='approved' AND NOT frozen AND NOT security_hold FOR UPDATE;
 IF NOT FOUND OR NOT EXISTS(SELECT 1 FROM wallet_private.admission23 WHERE user_id=auth.uid() AND status='active' AND adult_attested) THEN RAISE EXCEPTION 'ADMIN_APPROVAL_REQUIRED';END IF;
 IF EXISTS(SELECT 1 FROM wallet_private.pins WHERE user_id=auth.uid()) THEN RAISE EXCEPTION 'PIN_ALREADY_SET';END IF;
 INSERT INTO wallet_private.pins(user_id,hash) VALUES(auth.uid(),extensions.crypt(p_pin,extensions.gen_salt('bf',12)));
END $$;
REVOKE ALL ON FUNCTION public.sbut_first_pin23(text) FROM PUBLIC,anon,service_role;
GRANT EXECUTE ON FUNCTION public.sbut_first_pin23(text) TO authenticated;
NOTIFY pgrst,'reload schema';
COMMIT;


-- 024 Account Information Admin Visibility
-- SBUT PAY Account Information + Admin read access (safe to run more than once)
create table if not exists public.sbut_account_information (
 user_id uuid primary key references public.sbut_users(id) on delete cascade,
 email text, address text, district text, postcode text, occupation text,
 created_at timestamptz not null default now(), updated_at timestamptz not null default now()
);
alter table public.sbut_account_information enable row level security;
grant select, insert on public.sbut_account_information to authenticated;
grant select on public.sbut_account_information to authenticated;
drop policy if exists account_information_select_self_or_admin on public.sbut_account_information;
create policy account_information_select_self_or_admin on public.sbut_account_information for select to authenticated using ((user_id=auth.uid() and public.sbut_session_active()) or public.sbut_is_admin());
drop policy if exists account_information_insert_self on public.sbut_account_information;
create policy account_information_insert_self on public.sbut_account_information for insert to authenticated with check (user_id=auth.uid() and public.sbut_session_active());
drop policy if exists account_information_update_locked on public.sbut_account_information;
create policy account_information_update_locked on public.sbut_account_information for update to authenticated using (false) with check (false);
create or replace function public.sbut_account_information_lock() returns trigger language plpgsql security definer set search_path='' as $$
begin
 if row(new.user_id,new.email,new.address,new.district,new.postcode,new.occupation,new.created_at) is distinct from row(old.user_id,old.email,old.address,old.district,old.postcode,old.occupation,old.created_at) then
  raise exception 'Account information is locked after saving. Contact SMS Help Line.';
 end if;
 new.updated_at=now(); return new;
end $$;
drop trigger if exists sbut_account_information_lock_trg on public.sbut_account_information;
create trigger sbut_account_information_lock_trg before update on public.sbut_account_information for each row execute function public.sbut_account_information_lock();
revoke all on function public.sbut_account_information_lock() from public, anon, authenticated;