
-- BEFORE trigger: only set reference fields on NEW row, do NOT touch pending_referrals
CREATE OR REPLACE FUNCTION public.match_pending_referrals_on_signup()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $function$
DECLARE
  v_ref record;
BEGIN
  IF NEW.mobile IS NULL OR length(trim(NEW.mobile)) = 0 THEN
    RETURN NEW;
  END IF;

  IF TG_OP = 'UPDATE' AND NEW.mobile IS NOT DISTINCT FROM OLD.mobile THEN
    RETURN NEW;
  END IF;

  SELECT * INTO v_ref
    FROM public.pending_referrals
   WHERE mobile = NEW.mobile AND status = 'pending'
   ORDER BY created_at ASC
   LIMIT 1;

  IF v_ref.id IS NOT NULL AND v_ref.referrer_id <> NEW.id THEN
    IF NEW.reference_customer_id IS NULL THEN
      NEW.reference_customer_id := v_ref.referrer_id;
      NEW.reference_mobile := (SELECT mobile FROM public.customers WHERE id = v_ref.referrer_id);
    END IF;
  END IF;

  RETURN NEW;
END;
$function$;

-- AFTER trigger function: now safely update pending_referrals since customer exists
CREATE OR REPLACE FUNCTION public.match_pending_referrals_after()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $function$
BEGIN
  IF NEW.mobile IS NULL OR length(trim(NEW.mobile)) = 0 THEN
    RETURN NEW;
  END IF;

  IF TG_OP = 'UPDATE' AND NEW.mobile IS NOT DISTINCT FROM OLD.mobile THEN
    RETURN NEW;
  END IF;

  UPDATE public.pending_referrals
     SET status = 'matched',
         matched_customer_id = NEW.id,
         matched_at = now()
   WHERE mobile = NEW.mobile
     AND status = 'pending'
     AND referrer_id <> NEW.id;

  RETURN NEW;
END;
$function$;

DROP TRIGGER IF EXISTS trg_match_pending_referrals_after ON public.customers;
CREATE TRIGGER trg_match_pending_referrals_after
AFTER INSERT OR UPDATE OF mobile ON public.customers
FOR EACH ROW EXECUTE FUNCTION public.match_pending_referrals_after();
