
-- 1. Fix storage policies: restrict writes to staff only
DROP POLICY IF EXISTS "Authenticated users can upload product images" ON storage.objects;
DROP POLICY IF EXISTS "Authenticated users can update product images" ON storage.objects;
DROP POLICY IF EXISTS "Authenticated users can delete product images" ON storage.objects;

CREATE POLICY "Staff can upload product images"
  ON storage.objects FOR INSERT TO authenticated
  WITH CHECK (bucket_id = 'product-images' AND public.is_staff(auth.uid()));

CREATE POLICY "Staff can update product images"
  ON storage.objects FOR UPDATE TO authenticated
  USING (bucket_id = 'product-images' AND public.is_staff(auth.uid()));

CREATE POLICY "Staff can delete product images"
  ON storage.objects FOR DELETE TO authenticated
  USING (bucket_id = 'product-images' AND public.is_staff(auth.uid()));

-- 2. Tighten SELECT on sensitive tables to staff only
DROP POLICY IF EXISTS "Authenticated users can view active products" ON public.products;
CREATE POLICY "Staff can view products"
  ON public.products FOR SELECT TO authenticated
  USING (public.is_staff(auth.uid()));

DROP POLICY IF EXISTS "Authenticated users can view customers" ON public.customers;
CREATE POLICY "Staff can view customers"
  ON public.customers FOR SELECT TO authenticated
  USING (public.is_staff(auth.uid()));

DROP POLICY IF EXISTS "Authenticated users can view customer transactions" ON public.customer_transactions;
CREATE POLICY "Staff can view customer transactions"
  ON public.customer_transactions FOR SELECT TO authenticated
  USING (public.is_staff(auth.uid()));

DROP POLICY IF EXISTS "Authenticated users can view stock adjustments" ON public.stock_adjustments;
CREATE POLICY "Staff can view stock adjustments"
  ON public.stock_adjustments FOR SELECT TO authenticated
  USING (public.is_staff(auth.uid()));

-- 3. Restrict SECURITY DEFINER functions: revoke from public, grant to authenticated only
REVOKE EXECUTE ON FUNCTION public.is_staff(uuid) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.is_staff(uuid) TO authenticated;

REVOKE EXECUTE ON FUNCTION public.has_role(uuid, app_role) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.has_role(uuid, app_role) TO authenticated;

REVOKE EXECUTE ON FUNCTION public.handle_new_user() FROM PUBLIC;
