-- Migration: Fix Users RLS Recursion and Admin Lockout
-- Purpose: Implement a security definer function to check admin status, avoiding infinite recursion on public.users.

-- 1. Create helper function to check admin role bypassing RLS (SECURITY DEFINER)
CREATE OR REPLACE FUNCTION public.is_admin(user_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM public.users
        WHERE id = user_id AND role = 'admin'
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 2. Drop the recursive policy on public.users
DROP POLICY IF EXISTS select_all_users_for_admin ON public.users;

-- 3. Re-create the policy using the non-recursive helper function
CREATE POLICY select_all_users_for_admin ON public.users
    FOR SELECT
    TO authenticated
    USING (public.is_admin(auth.uid()));
