
CREATE OR REPLACE FUNCTION public.verify_referral_autobinding()
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  a_id uuid;
  b_id uuid;
  c_id uuid;
  b_ref_after uuid;
  d_ref_after uuid;
  test1_pass boolean;
  test2_pass boolean;
  suffix text := substr(replace(gen_random_uuid()::text,'-',''),1,8);
  mob_a text;
  mob_b text;
  mob_c text;
BEGIN
  -- Admin gate
  IF NOT public.has_role(auth.uid(), 'admin'::app_role) THEN
    RAISE EXCEPTION 'Only admins can run referral verification';
  END IF;

  mob_a := '0199' || suffix;
  mob_b := '0188' || suffix;
  mob_c := '0177' || suffix;

  -- Run inside a sub-block we can rollback via exception
  BEGIN
    -- 1) A = referrer (already registered)
    INSERT INTO public.customers(name, name_bn, mobile)
      VALUES ('TEST_A_'||suffix, 'টেস্ট_এ', mob_a)
      RETURNING id INTO a_id;

    -- 2) B = added by A with reference_mobile = C's mobile (C not yet registered → orphan)
    INSERT INTO public.customers(name, name_bn, mobile, reference_mobile)
      VALUES ('TEST_B_'||suffix, 'টেস্ট_বি', mob_b, mob_c)
      RETURNING id INTO b_id;

    -- 3) C registers (B was already waiting). The INSERT trigger should auto-link B → C.
    INSERT INTO public.customers(name, name_bn, mobile)
      VALUES ('TEST_C_'||suffix, 'টেস্ট_সি', mob_c)
      RETURNING id INTO c_id;

    SELECT reference_customer_id INTO b_ref_after FROM public.customers WHERE id = b_id;
    test1_pass := (b_ref_after = c_id);

    -- 4) Existing-customer sync test: set B.reference_mobile = A.mobile → BEFORE trigger should populate reference_customer_id = A.id immediately
    UPDATE public.customers SET reference_mobile = mob_a WHERE id = b_id;
    SELECT reference_customer_id INTO d_ref_after FROM public.customers WHERE id = b_id;
    test2_pass := (d_ref_after = a_id);

    -- Force rollback so no test data remains
    RAISE EXCEPTION 'ROLLBACK_TEST_DATA';

  EXCEPTION WHEN OTHERS THEN
    IF SQLERRM <> 'ROLLBACK_TEST_DATA' THEN
      RETURN jsonb_build_object(
        'ok', false,
        'error', SQLERRM,
        'test1_auto_link_on_register', COALESCE(test1_pass, false),
        'test2_sync_on_reference_set', COALESCE(test2_pass, false)
      );
    END IF;
  END;

  RETURN jsonb_build_object(
    'ok', COALESCE(test1_pass,false) AND COALESCE(test2_pass,false),
    'test1_auto_link_on_register', jsonb_build_object(
      'passed', COALESCE(test1_pass,false),
      'description', 'B was waiting with reference_mobile=C; after C registers, B.reference_customer_id auto-set to C.id',
      'expected_id', c_id,
      'actual_id', b_ref_after
    ),
    'test2_sync_on_reference_set', jsonb_build_object(
      'passed', COALESCE(test2_pass,false),
      'description', 'Setting reference_mobile on an existing customer to an already-registered mobile syncs reference_customer_id instantly',
      'expected_id', a_id,
      'actual_id', d_ref_after
    ),
    'cleanup', 'All test rows rolled back automatically'
  );
END;
$$;

REVOKE ALL ON FUNCTION public.verify_referral_autobinding() FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.verify_referral_autobinding() TO authenticated;
