SQL: get product stock of all product types in a single query
You have effectively have these 3 queries:

simple

SELECT p.product_id, SUM(s.qty) AS final_qty
FROM products AS p
JOIN product_stocks AS s
  ON p.product_id = s.product_id AND
     p.type = 'simple'
GROUP BY p.product_id


complex 1:

SELECT p.product_id, SUM(s.qty) AS final_qty
FROM products AS p
JOIN complex1_childs AS c
  ON p.product_id = c.parent_id AND
     p.type = 'complex_1'
JOIN products AS cp
  ON c.child_id = cp.product_id
JOIN product_stocks AS s
  ON cp.product_id = s.product_id
GROUP BY p.product_id


complex 2 is the same as above changing the table and the p.type join criteria.

You can UNION these together into a single query.