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