-- Complete fix for users table RLS policies
-- This resolves the infinite recursion issue by using auth.uid() directly

-- First, drop all existing policies on users table
DROP POLICY IF EXISTS "Users can view their own profile" ON users;
DROP POLICY IF EXISTS "Users can update their own profile" ON users;
DROP POLICY IF EXISTS "users_can_read_own" ON users;
DROP POLICY IF EXISTS "users_insert_own" ON users;
DROP POLICY IF EXISTS "users_update_own" ON users;
DROP POLICY IF EXISTS "Admins can view all users" ON users;
DROP POLICY IF EXISTS "Admins can update all users" ON users;

-- Ensure RLS is enabled
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Create simple, non-recursive policies
-- Users can always read their own profile (using auth.uid() directly, no subquery)
CREATE POLICY "users_select_own" ON users
  FOR SELECT
  USING (auth.uid() = id);

-- Users can insert their own profile
CREATE POLICY "users_insert_own_new" ON users
  FOR INSERT
  WITH CHECK (auth.uid() = id);

-- Users can update their own profile
CREATE POLICY "users_update_own_new" ON users
  FOR UPDATE
  USING (auth.uid() = id);

-- For admin access, we need a helper function that doesn't cause recursion
-- This function uses security definer to bypass RLS
CREATE OR REPLACE FUNCTION public.get_user_role(user_id uuid)
RETURNS text
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT role FROM users WHERE id = user_id;
$$;

-- Grant execute permission
GRANT EXECUTE ON FUNCTION public.get_user_role(uuid) TO authenticated;
GRANT EXECUTE ON FUNCTION public.get_user_role(uuid) TO anon;

-- Admin policy using the helper function (avoids infinite recursion)
CREATE POLICY "admins_select_all" ON users
  FOR SELECT
  USING (
    auth.uid() = id 
    OR public.get_user_role(auth.uid()) = 'admin'
  );

-- Admin update policy
CREATE POLICY "admins_update_all" ON users
  FOR UPDATE
  USING (
    auth.uid() = id 
    OR public.get_user_role(auth.uid()) = 'admin'
  );
