
-- 1. Fix agent_test_results: remove permissive {public} policies
DROP POLICY IF EXISTS "Service role full access on agent_test_results" ON public.agent_test_results;
DROP POLICY IF EXISTS "Anonymous read access on agent_test_results" ON public.agent_test_results;

-- Allow service_role full access (bypasses RLS anyway, but explicit)
CREATE POLICY "Service role manages agent_test_results"
ON public.agent_test_results
FOR ALL
TO service_role
USING (true)
WITH CHECK (true);

-- Allow admins to insert/update/delete via authenticated context
CREATE POLICY "Admins can modify agent_test_results"
ON public.agent_test_results
FOR ALL
TO authenticated
USING (public.is_admin(auth.uid()))
WITH CHECK (public.is_admin(auth.uid()));

-- 2. Fix admin_profiles UPDATE to require admin role
DROP POLICY IF EXISTS "Admins can update their own profile" ON public.admin_profiles;
CREATE POLICY "Admins can update their own profile"
ON public.admin_profiles
FOR UPDATE
USING (auth.uid() = user_id AND public.is_admin(auth.uid()))
WITH CHECK (auth.uid() = user_id AND public.is_admin(auth.uid()));

-- 3. Fix broken group_buy_orders SELECT policy (self-referential join bug)
DROP POLICY IF EXISTS "Users can view group orders they participate in" ON public.group_buy_orders;
CREATE POLICY "Users can view group orders they participate in"
ON public.group_buy_orders
FOR SELECT
USING (
  leader_user_id = auth.uid()
  OR EXISTS (
    SELECT 1 FROM public.group_buy_members
    WHERE group_buy_members.group_order_id = group_buy_orders.id
      AND group_buy_members.user_id = auth.uid()
  )
);

-- 4. Restrict realtime broadcast subscriptions for business_alerts to admins
ALTER TABLE IF EXISTS realtime.messages ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Admins receive business_alerts realtime" ON realtime.messages;
CREATE POLICY "Admins receive business_alerts realtime"
ON realtime.messages
FOR SELECT
TO authenticated
USING (
  realtime.topic() <> 'business_alerts'
  OR public.is_admin(auth.uid())
);
