
-- 1) Trigger function: when a new customer is inserted, link any pending referrals pointing to this mobile
CREATE OR REPLACE FUNCTION public.auto_link_pending_referrals()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  IF NEW.mobile IS NOT NULL AND length(trim(NEW.mobile)) > 0 THEN
    UPDATE public.customers
       SET reference_customer_id = NEW.id,
           updated_at = now()
     WHERE reference_mobile = NEW.mobile
       AND reference_customer_id IS NULL
       AND id <> NEW.id;
  END IF;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_auto_link_pending_referrals ON public.customers;
CREATE TRIGGER trg_auto_link_pending_referrals
AFTER INSERT ON public.customers
FOR EACH ROW
EXECUTE FUNCTION public.auto_link_pending_referrals();

-- 2) Trigger function: when reference_mobile is set/changed on a customer, sync reference_customer_id immediately
CREATE OR REPLACE FUNCTION public.sync_reference_customer_id()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  found_id uuid;
BEGIN
  -- Only act when reference_mobile actually changed (or on insert)
  IF TG_OP = 'INSERT' OR NEW.reference_mobile IS DISTINCT FROM OLD.reference_mobile THEN
    IF NEW.reference_mobile IS NULL OR length(trim(NEW.reference_mobile)) = 0 THEN
      NEW.reference_customer_id := NULL;
    ELSE
      SELECT id INTO found_id
        FROM public.customers
       WHERE mobile = NEW.reference_mobile
         AND id <> NEW.id
       LIMIT 1;
      NEW.reference_customer_id := found_id; -- NULL if not yet registered (orphan, will auto-link later)
    END IF;
  END IF;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_sync_reference_customer_id ON public.customers;
CREATE TRIGGER trg_sync_reference_customer_id
BEFORE INSERT OR UPDATE OF reference_mobile ON public.customers
FOR EACH ROW
EXECUTE FUNCTION public.sync_reference_customer_id();

-- 3) One-shot backfill: link any existing orphan referrals where the referenced mobile is now a registered customer
UPDATE public.customers c
   SET reference_customer_id = r.id,
       updated_at = now()
  FROM public.customers r
 WHERE c.reference_customer_id IS NULL
   AND c.reference_mobile IS NOT NULL
   AND length(trim(c.reference_mobile)) > 0
   AND r.mobile = c.reference_mobile
   AND r.id <> c.id;

-- 4) Helpful index for fast lookup on referral matching
CREATE INDEX IF NOT EXISTS idx_customers_reference_mobile
  ON public.customers (reference_mobile)
  WHERE reference_mobile IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_customers_mobile
  ON public.customers (mobile);
