-- Migration: Add company_id to public.orders and update RLS policies

-- 1. Add company_id column to orders table
ALTER TABLE public.orders
  ADD COLUMN IF NOT EXISTS company_id UUID REFERENCES public.companies(id) ON DELETE SET NULL;

-- 2. Backfill company_id from users for existing orders
UPDATE public.orders o
SET company_id = (SELECT company_id FROM public.users u WHERE u.id = o.user_id)
WHERE o.company_id IS NULL AND o.user_id IS NOT NULL;

-- 3. Recreate RLS policies for public.orders to use orders.company_id
DROP POLICY IF EXISTS "Users can view their own orders" ON public.orders;
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 company_id = (SELECT company_id FROM public.users WHERE id = auth.uid())
  OR auth.uid() = user_id
);

DROP POLICY IF EXISTS "Users can update their own orders" ON public.orders;
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 company_id = (SELECT company_id FROM public.users WHERE id = auth.uid())
  OR auth.uid() = user_id
);

-- Reload schema cache
NOTIFY pgrst, 'reload schema';
