register_activation_hook doesn’t fire at all

The register_activation_hook as the first parameter takes:

$file
(string) (required) Path to the main plugin file inside the wp-content/plugins directory. A full path will work.

So using __FILE__ for the tag works only when you do it in the main plugin file i.e. plugins/my-super-plugin/my-super-plugin.php.