-- SBUT PAY 011: one NID -> one application/account; resume the same provisional application.
-- Requires 009 and 010. Authentication record is provisional; account access requires approval.
BEGIN;
CREATE UNIQUE INDEX IF NOT EXISTS sbut_kyc_nid_single_account ON public.sbut_kyc(nid);
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','pending') 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;
 IF EXISTS(SELECT 1 FROM public.sbut_kyc WHERE nid=p_nid AND user_id<>u) THEN RAISE EXCEPTION 'NID_ALREADY_USED';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_resume_application() RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER SET search_path='' AS $$
DECLARE u uuid:=auth.uid(); state text; result jsonb;
BEGIN
 PERFORM wallet_private.require_session();
 SELECT kyc INTO state FROM public.sbut_users WHERE id=u AND NOT frozen FOR UPDATE;
 IF state IS NULL OR state NOT IN ('draft','pending','rejected') THEN RAISE EXCEPTION 'APPLICATION_NOT_EDITABLE';END IF;
 SELECT jsonb_build_object('name',a.name,'username',a.username,'nid',k.nid,'dob',k.dob,
  'front',k.front_path,'back',k.back_path,'selfie',k.selfie_path)
 INTO result FROM public.sbut_users a JOIN public.sbut_kyc k ON k.user_id=a.id WHERE a.id=u;
 -- Draft pauses the approval job until the user submits again. Row lock prevents approval/edit races.
 UPDATE public.sbut_users SET kyc='draft' WHERE id=u;
 RETURN result;
END $$;
REVOKE ALL ON FUNCTION public.sbut_resume_application() FROM PUBLIC,anon;
GRANT EXECUTE ON FUNCTION public.sbut_resume_application() TO authenticated;
NOTIFY pgrst,'reload schema';
COMMIT;
SELECT true AS one_nid_one_account,true AS same_application_retry;