How did it come that SATA HDD use ATA while SATA CD drives use SCSI as protocol?
retrocomputing.stackexchange.com
retrocomputing.stackexchange.com
So with first "civilian" CD drives, appear few proprietary interfaces and some used sort of SCSI.
Than just made decision, to save resources, make hybrid, using ATA electronics, but partially SCSI logic/software.
I bought a used HP EliteDesk Mini G1 off eBay to replace a Raspberry Pi for a project. It requires a SATA m.2 which needs an AHCI controller on-board. Very difficult to find.
That doesn't sound right. AHCI is a software protocol for communication between the operating system and host bus adapters. If it's a M.2 SATA SSD, then the host system needs an AHCI controller or equivalent providing SATA ports (such as a SAS HBA, which provides SATA compatibility but is rarely wired up to a M.2 slot).
Or if it's a M.2 PCIe slot in a system that predates widespread NVMe support, then it needs a PCIe SSD that implements AHCI instead of NVMe, in which case there would be no SATA involved anywhere in the chain—just backwards compatibility with software expecting the usual SATA drive behind AHCI controller combination.