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