-- SBUT PAY 010. Run once after 009. Automatic ACCOUNT approval, NOT identity authenticity verification.
-- Owner requested 180-second admission. This enables the existing approved-wallet permissions.
-- No welcome reward, balance credit, merchant role or admin role is granted by this migration.
BEGIN;
CREATE TABLE IF NOT EXISTS wallet_private.auto_admissions (
 user_id uuid PRIMARY KEY REFERENCES public.sbut_users(id),
 submitted_at timestamptz NOT NULL,
 approved_at timestamptz NOT NULL DEFAULT now(),
 method text NOT NULL DEFAULT 'automatic_submission_checks_only'
);
ALTER TABLE wallet_private.auto_admissions ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON wallet_private.auto_admissions FROM PUBLIC,anon,authenticated;
CREATE OR REPLACE FUNCTION wallet_private.auto_admit(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 k.submitted_at<=now()-interval '180 seconds'
   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.auto_admit(uuid) FROM PUBLIC,anon,authenticated,service_role;
CREATE OR REPLACE FUNCTION public.sbut_review_status()
RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE result jsonb;
BEGIN
 PERFORM wallet_private.require_session();
 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();
 IF result IS NULL THEN RAISE EXCEPTION 'ACCOUNT_NOT_FOUND';END IF;
 RETURN result;
END $$;
REVOKE ALL ON FUNCTION public.sbut_review_status() FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.sbut_review_status() TO authenticated;
-- Re-running this installs/updates the SAME job, without duplicating it.
SELECT cron.schedule('sbut-auto-account-approval','* * * * *',
 'SELECT wallet_private.auto_admit(NULL::uuid);');
NOTIFY pgrst,'reload schema';
COMMIT;
SELECT true AS auto_approval_installed,180 AS minimum_wait_seconds;