CREATE TABLE IF NOT EXISTS public.pending_referrals (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  referrer_id uuid NOT NULL REFERENCES public.customers(id) ON DELETE CASCADE,
  mobile text NOT NULL,
  note text,
  status text NOT NULL DEFAULT 'pending',
  matched_customer_id uuid REFERENCES public.customers(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  matched_at timestamptz,
  UNIQUE (referrer_id, mobile)
);

CREATE INDEX IF NOT EXISTS idx_pending_referrals_mobile ON public.pending_referrals(mobile) WHERE status = 'pending';

ALTER TABLE public.pending_referrals ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Staff full access pending_referrals"
  ON public.pending_referrals FOR ALL
  USING (public.is_staff(auth.uid()))
  WITH CHECK (public.is_staff(auth.uid()));

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;

  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 ON public.customers
  FOR EACH ROW EXECUTE FUNCTION public.match_pending_referrals_on_signup();