-- 1) Extend match trigger to also fire on UPDATE OF mobile
CREATE OR REPLACE FUNCTION public.match_pending_referrals_on_signup()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_ref record;
BEGIN
  IF NEW.mobile IS NULL OR length(trim(NEW.mobile)) = 0 THEN
    RETURN NEW;
  END IF;

  -- Only act when mobile actually changed on UPDATE
  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;
    UPDATE public.pending_referrals
       SET status = 'matched', matched_customer_id = NEW.id, matched_at = now()
     WHERE id = v_ref.id;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_match_pending_referrals ON public.customers;
CREATE TRIGGER trg_match_pending_referrals
  BEFORE INSERT OR UPDATE OF mobile ON public.customers
  FOR EACH ROW EXECUTE FUNCTION public.match_pending_referrals_on_signup();

-- 2) Backfill: for any pending referral whose mobile is already a registered customer, link now
WITH matches AS (
  SELECT pr.id AS pr_id, pr.referrer_id, c.id AS cust_id, r.mobile AS referrer_mobile
    FROM public.pending_referrals pr
    JOIN public.customers c
      ON c.mobile = pr.mobile
     AND c.deleted_at IS NULL
     AND c.id <> pr.referrer_id
    JOIN public.customers r
      ON r.id = pr.referrer_id
   WHERE pr.status = 'pending'
)
UPDATE public.customers c
   SET reference_customer_id = m.referrer_id,
       reference_mobile = COALESCE(NULLIF(c.reference_mobile, ''), m.referrer_mobile),
       updated_at = now()
  FROM matches m
 WHERE c.id = m.cust_id
   AND c.reference_customer_id IS NULL;

UPDATE public.pending_referrals pr
   SET status = 'matched',
       matched_customer_id = c.id,
       matched_at = now()
  FROM public.customers c
 WHERE pr.status = 'pending'
   AND c.mobile = pr.mobile
   AND c.deleted_at IS NULL
   AND c.id <> pr.referrer_id;