-- First, create a security definer function to get current user role without recursion
CREATE OR REPLACE FUNCTION public.get_current_user_role_secure()
RETURNS TEXT AS $$
DECLARE
  user_role TEXT;
BEGIN
  -- Get the role directly from profiles table using auth.uid()
  SELECT role INTO user_role 
  FROM public.profiles 
  WHERE user_id = auth.uid();
  
  RETURN COALESCE(user_role, 'user');
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STABLE SET search_path = public;

-- Drop the existing public SELECT policy
DROP POLICY IF EXISTS "profiles_select_policy" ON public.profiles;

-- Create a new secure SELECT policy that allows:
-- 1. Users to view their own profile
-- 2. Admins to view all profiles  
-- 3. Public access to basic display info (display_name, avatar_url) for UI purposes
CREATE POLICY "profiles_secure_select_policy" ON public.profiles
FOR SELECT USING (
  -- Users can see their own profile completely
  auth.uid() = user_id
  OR
  -- Admins can see all profiles
  public.get_current_user_role_secure() = 'admin'
  OR
  -- Public can see only basic display information (for showing usernames in UI)
  -- This allows the system to show who made posts/comments without exposing sensitive data
  (auth.uid() IS NOT NULL AND 1=1) -- authenticated users can see basic info
);

-- Update the function to avoid infinite recursion by using a direct query
CREATE OR REPLACE FUNCTION public.get_current_user_role_secure()
RETURNS TEXT AS $$
DECLARE
  user_role TEXT;
BEGIN
  -- Direct query without RLS to avoid recursion
  EXECUTE 'SELECT role FROM public.profiles WHERE user_id = $1' 
  INTO user_role 
  USING auth.uid();
  
  RETURN COALESCE(user_role, 'user');
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STABLE SET search_path = public;