-- Fix infinite recursion in users table RLS policy
-- The issue is that the policy references the users table itself, causing a loop

-- Drop existing problematic policies
DROP POLICY IF EXISTS "Users can view own profile" ON users;
DROP POLICY IF EXISTS "Users can update own profile" ON users;
DROP POLICY IF EXISTS "Admins can view all users" ON users;
DROP POLICY IF EXISTS "Admins can update all users" ON users;
DROP POLICY IF EXISTS "users_select_own" ON users;
DROP POLICY IF EXISTS "users_update_own" ON users;
DROP POLICY IF EXISTS "users_select_policy" ON users;
DROP POLICY IF EXISTS "users_update_policy" ON users;

-- Create new policies that use auth.uid() directly without recursion
-- Users can view their own profile
CREATE POLICY "users_select_own" ON users
  FOR SELECT
  USING (auth.uid() = id);

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

-- Allow insert for new user registration
CREATE POLICY "users_insert_own" ON users
  FOR INSERT
  WITH CHECK (auth.uid() = id);

-- For admin access, we use a security definer function to avoid recursion
CREATE OR REPLACE FUNCTION is_admin()
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM users 
    WHERE id = auth.uid() 
    AND role = 'admin'
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Admins can view all users (using security definer function)
CREATE POLICY "admins_select_all" ON users
  FOR SELECT
  USING (
    auth.uid() = id 
    OR 
    (SELECT role FROM users WHERE id = auth.uid()) = 'admin'
  );

-- Actually, the above still causes recursion. Let's use a different approach.
-- Drop and recreate with a simpler approach

DROP POLICY IF EXISTS "users_select_own" ON users;
DROP POLICY IF EXISTS "admins_select_all" ON users;

-- Simple policy: users can only see their own row
-- Admin functionality will use service role key (admin client) to bypass RLS
CREATE POLICY "users_can_read_own" ON users
  FOR SELECT
  USING (auth.uid() = id);
