-- Index for fast "latest segment per customer" lookups
CREATE INDEX IF NOT EXISTS idx_customer_segment_logs_customer_created
  ON public.customer_segment_logs (customer_id, created_at DESC);

-- Trigger function: skip insert if latest log's new_segment matches incoming new_segment
CREATE OR REPLACE FUNCTION public.prevent_duplicate_segment_log()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_last_segment text;
BEGIN
  SELECT new_segment INTO v_last_segment
  FROM public.customer_segment_logs
  WHERE customer_id = NEW.customer_id
  ORDER BY created_at DESC
  LIMIT 1;

  -- If latest stored segment is identical to the incoming new_segment, skip insert
  IF v_last_segment IS NOT NULL AND v_last_segment = NEW.new_segment THEN
    RETURN NULL;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_prevent_duplicate_segment_log ON public.customer_segment_logs;
CREATE TRIGGER trg_prevent_duplicate_segment_log
BEFORE INSERT ON public.customer_segment_logs
FOR EACH ROW
EXECUTE FUNCTION public.prevent_duplicate_segment_log();