-- SBUT PAY: run once in Supabase SQL Editor. All money is integer paisa.
begin;
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);
commit;