-- Create a function to check if the current user (Clerk or Supabase) matches a given profile ID
CREATE OR REPLACE FUNCTION public.is_user_match(p_id uuid)
 RETURNS boolean
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
DECLARE
  v_clerk_id TEXT;
  v_supabase_id UUID;
BEGIN
  v_clerk_id := get_clerk_user_id();
  v_supabase_id := auth.uid();
  
  -- If both are null, not authenticated
  IF v_clerk_id IS NULL AND v_supabase_id IS NULL THEN
    RETURN FALSE;
  END IF;

  RETURN EXISTS (
    SELECT 1 FROM profiles 
    WHERE id = p_id 
    AND (
      (v_clerk_id IS NOT NULL AND clerk_user_id = v_clerk_id)
      OR 
      (v_supabase_id IS NOT NULL AND id = v_supabase_id)
    )
  );
END;
$function$;

-- Update provider_wallets policies
DROP POLICY IF EXISTS "Admins can view all wallets" ON provider_wallets;
DROP POLICY IF EXISTS "Providers can insert own wallet" ON provider_wallets;
DROP POLICY IF EXISTS "Providers can update own wallet" ON provider_wallets;
DROP POLICY IF EXISTS "Providers can view own wallet" ON provider_wallets;

-- New policies using the match function and is_admin()
CREATE POLICY "Admins can view all wallets" ON provider_wallets
  FOR SELECT TO public USING (is_admin());

CREATE POLICY "Providers can insert own wallet" ON provider_wallets
  FOR INSERT TO public WITH CHECK (is_user_match(provider_id));

CREATE POLICY "Providers can update own wallet" ON provider_wallets
  FOR UPDATE TO public USING (is_user_match(provider_id));

CREATE POLICY "Providers can view own wallet" ON provider_wallets
  FOR SELECT TO public USING (is_user_match(provider_id));
