How do I return a calculated date based from another table?

See more:

SQL

MySQL

Hello everyone, for a few days I've been trying to make a query in a MYSQL database. But I'm not getting the result I need. I will try to explain in detail using an example of my structure. Consider the following tables.

There is a table that stores the registration of a customer, and another that stores the registration of compressors that this customer has:

CREATE TABLE customer
(
  id INT AUTO_INCREMENT,
  name VARCHAR(100),
  PRIMARY KEY(id)
);
INSERT INTO customer VALUES(NULL, 'Customer A');

CREATE TABLE customercompressor
(
  id INT AUTO_INCREMENT,
  customerid INT NOT NULL,
  name VARCHAR(50),
  PRIMARY KEY(id)
);
INSERT INTO customercompressor VALUES (NULL, 1, 'Compressor A');

Now, there is another table that stores the parts that make up the maintenance of each customer's compressor:

CREATE TABLE customercompressorpart
(
  id INT AUTO_INCREMENT,
  customercompressorid INT NOT NULL,
  name VARCHAR(50),
  hourcapacity INT NOT NULL,
  PRIMARY KEY(id)
);
INSERT INTO customercompressorpart VALUES (NULL, 1, 'Air Filter', 1000);
INSERT INTO customercompressorpart VALUES (NULL, 1, 'Oil Filter', 1000);
INSERT INTO customercompressorpart VALUES (NULL, 1, 'Oil', 1000);
INSERT INTO customercompressorpart VALUES (NULL, 1, 'Separator', 2000);
INSERT INTO customercompressorpart VALUES (NULL, 1, 'Sensor', 6000);

See that I created a client, a compressor for this client, and five parts for this compressor.
There is also a table where the evaluations of these compressors are stored, and another to store the parts that are replaced in them.

CREATE TABLE maintenance
(
  id INT AUTO_INCREMENT,
  maintenancedate DATE,
  customerid INT NOT NULL,
  customercompressorid INT NOT NULL,
  averageworkload DECIMAL(4, 2) NOT NULL,
  PRIMARY KEY(id)
);
INSERT INTO maintenance VALUES (NULL, '2022-01-01', 1, 1, 5.0);
INSERT INTO maintenance VALUES (NULL, '2022-02-01', 1, 1, 5.0);
INSERT INTO maintenance VALUES (NULL, '2022-03-01', 1, 1, 5.0);
INSERT INTO maintenance VALUES (NULL, '2022-04-01', 1, 1, 5.0);

CREATE TABLE maintenancepartreplace
(
  id INT AUTO_INCREMENT,
  maintenanceid INT NOT NULL,
  customercompressorpartid INT NOT NULL,
  PRIMARY KEY(id)
);
INSERT INTO maintenancepartreplace VALUES (NULL, 2, 1);
INSERT INTO maintenancepartreplace VALUES (NULL, 2, 2);
INSERT INTO maintenancepartreplace VALUES (NULL, 2, 3);
INSERT INTO maintenancepartreplace VALUES (NULL, 3, 4);

Above, I created four maintenances for the customer's compressor inserted before, and three replacements being air filter, oil and oil filter in the second evaluation and one more for the separator in the third evaluation.

Now I'm trying to create a query where I only inform the customer's compressor ID in the WHERE clause and it returns the name of each of the parts of this compressor, with the date of the next exchange, this date will be calculated by adding the value of (customercompressorpart .hourcapacity / maintenance.averageworkload) to maintenance.maintenancedate in days.

Notice that there are 5 parts registered for the compressor, but only 4 of them were replaced. So I need the query to check when the last time the part was changed to get the date of this evaluation to bring the date of the next exchange, but when there was no exchange, it should get the date of the first evaluation made.

I know it got a little complex, but I couldn't find another way to explain it. Please someone help me with this task.

Here is the link to the OneCompiler where this structure is already prepared.

What I have tried:

The closest i got was this, but i get the message that it's returning more than one line.

SELECT
	customercompressorpart.name,
    (
			SELECT
				maintenance.maintenancedate + INTERVAL (customercompressorpart.hourcapacity / maintenance.averageworkload) DAY
			FROM maintenancepartreplace
			JOIN maintenance ON maintenance.id = maintenancepartreplace.maintenanceid 
			JOIN customercompressorpart customercompressorpart2 ON customercompressorpart.id = maintenancepartreplace.customercompressorpartid     
			JOIN customercompressor customercompressor2 ON customercompressor2.id = maintenance.customercompressorid
			WHERE customercompressor2.id = customercompressor.id
		) AS nextchange
FROM customercompressorpart
LEFT JOIN customercompressor ON customercompressor.id = customercompressorpart.customercompressorid
LEFT JOIN maintenance ON maintenance.customercompressorid = customercompressor.id
LEFT JOIN maintenancepartreplace ON maintenancepartreplace.maintenanceid  = maintenance.id
WHERE 
	maintenance.maintenancedate = (SELECT MAX(maintenance2.maintenancedate) FROM maintenance maintenance2 WHERE maintenance2.customercompressorid = maintenance.customercompressorid) AND
	customercompressor.id = 1;