-- 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.
begin;
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;