-- Migration: Update orders RLS policies to allow company-wide select and update access

-- 1. Drop existing policies
DROP POLICY IF EXISTS "Users can view their own orders" ON public.orders;
DROP POLICY IF EXISTS "Users can update their own orders" ON public.orders;

-- 2. Create updated SELECT policy
CREATE POLICY "Users can view their own orders" ON public.orders FOR SELECT USING (
  (SELECT role FROM public.users WHERE id = auth.uid()) = 'admin'
  OR (
    EXISTS (
      SELECT 1 FROM public.users u1
      JOIN public.users u2 ON u1.company_id = u2.company_id
      WHERE u1.id = orders.user_id AND u2.id = auth.uid() AND u1.company_id IS NOT NULL
    )
  )
  OR auth.uid() = user_id
);

-- 3. Create updated UPDATE policy
CREATE POLICY "Users can update their own orders" ON public.orders FOR UPDATE USING (
  (SELECT role FROM public.users WHERE id = auth.uid()) = 'admin'
  OR (
    EXISTS (
      SELECT 1 FROM public.users u1
      JOIN public.users u2 ON u1.company_id = u2.company_id
      WHERE u1.id = orders.user_id AND u2.id = auth.uid() AND u1.company_id IS NOT NULL
    )
  )
  OR auth.uid() = user_id
);

-- Reload Schema Cache
NOTIFY pgrst, 'reload schema';
