CREATE TABLE public.customer_segment_logs (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  customer_id UUID NOT NULL,
  old_segment TEXT,
  new_segment TEXT NOT NULL,
  reason TEXT NOT NULL DEFAULT 'auto',
  changed_by UUID,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

CREATE INDEX idx_customer_segment_logs_customer ON public.customer_segment_logs(customer_id, created_at DESC);

ALTER TABLE public.customer_segment_logs ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Staff can view customer segment logs"
  ON public.customer_segment_logs FOR SELECT
  TO authenticated
  USING (is_staff(auth.uid()));

CREATE POLICY "Staff can insert customer segment logs"
  ON public.customer_segment_logs FOR INSERT
  TO authenticated
  WITH CHECK (is_staff(auth.uid()));

CREATE POLICY "Admins can delete customer segment logs"
  ON public.customer_segment_logs FOR DELETE
  TO authenticated
  USING (has_role(auth.uid(), 'admin'::app_role));

CREATE TRIGGER trg_customer_segment_logs_updated_at
  BEFORE UPDATE ON public.customer_segment_logs
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();