-- Sale status transition rules (admin-configurable)
CREATE TABLE public.sale_status_transitions (
  from_status text PRIMARY KEY,
  allowed_to text[] NOT NULL DEFAULT '{}',
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by uuid
);

ALTER TABLE public.sale_status_transitions ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Staff can view sale status transitions"
ON public.sale_status_transitions FOR SELECT
TO authenticated
USING (is_staff(auth.uid()));

CREATE POLICY "Admins can insert sale status transitions"
ON public.sale_status_transitions FOR INSERT
TO authenticated
WITH CHECK (has_role(auth.uid(), 'admin'::app_role));

CREATE POLICY "Admins can update sale status transitions"
ON public.sale_status_transitions FOR UPDATE
TO authenticated
USING (has_role(auth.uid(), 'admin'::app_role));

CREATE POLICY "Admins can delete sale status transitions"
ON public.sale_status_transitions FOR DELETE
TO authenticated
USING (has_role(auth.uid(), 'admin'::app_role));

-- Seed with current defaults
INSERT INTO public.sale_status_transitions (from_status, allowed_to) VALUES
  ('pending',            ARRAY['confirmed','cancelled']),
  ('confirmed',          ARRAY['processing','cancelled','pending']),
  ('processing',         ARRAY['ready_for_delivery','completed','cancelled']),
  ('ready_for_delivery', ARRAY['delivered','partial_delivered','cancelled']),
  ('completed',          ARRAY['delivered','partial_delivered','returned']),
  ('partial_delivered',  ARRAY['delivered','returned']),
  ('delivered',          ARRAY['returned']),
  ('returned',           ARRAY[]::text[]),
  ('cancelled',          ARRAY[]::text[])
ON CONFLICT (from_status) DO NOTHING;