Just like in the title of the question: Why would changing "RAID" to "AHCI" as SATA mode operation make Windows 10 fail to boot?
Changing the mode changes how the controller appears to the OS and hence changes what drivers are needed to access it. Logically it is similar to removing your storage controller and fitting a different one, even though the physical controller is the same.
Any modern OS at some point switches from an environment where the hardware is largely controlled by the BIOS/UEFI to a mode where the hardware is controlled by the OS. Before this switch the OS can use the BIOS/UEFI to load data from the boot device, but after the switch the OS must use it's own drivers.
During normal operation windows uses "plug and play" to decide what drivers to load, but this doesn't work during the early boot process. So the drivers for the storage controller are loaded through other less-intelligent means. On a normal boot these mechanisms only load the relevant drivers for the storage controller your particular system has (or had before you went and changed it).
So when you change the SATA mode Windows does not load the correct driver for, fails to find the drive and fails to boot.