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