Mysql Query Inventory Manufacture

I have project about inventory in manufacturing area, Where I have

Products Table

id (primary id),
product_name,
initial_qty,
type

purchase_order_details Table

id
, product_id (foreign_key)
 , quantity

work_order_details Table

id
, product_id (foreign_key)
, quantity

Assemblies Table

id (primary id)
, product_id (foreign_key)

boms Table

 id,
 assembly_id (foreign_key),
 product_id ( foreign_key),
 quantity

in this table I need to get a current inventory for products table quantity based on records where the formula is products.initial_qty + purchase_order_details.quantity - work_order_details quantity.

this formula is ok if the scenario for work order product_id is component (product type). if the scenario is work order is assembly (product type) that is relationship to assemblies table and boms table i get lost. this are my sample query. hope someone can help i stack already for almost 3 days.

SELECT 
    p.id,
    p.product_name,
    COALESCE(SUM(pod.quantity), 0) AS purchase_quantity,
    COALESCE(SUM(pod.quantity)  - SUM(b.quantity_boms), 0) AS final_quantity
FROM 
    products p
LEFT JOIN 
    purchase_order_details pod ON p.id = pod.product_id

LEFT JOIN 
    (SELECT a.product_id, SUM(b.quantity) AS quantity_boms
     FROM assemblies a
     LEFT JOIN boms b ON a.id = b.assembly_id 
     WHERE a.product_id IN (SELECT product_id FROM work_order_details)
     GROUP BY a.id) b ON p.id = b.product_id
GROUP BY 
    p.id, p.product_name;


SELECT 
    p.id,
    p.product_name,
    COALESCE(SUM(pod.quantity), 0) AS purchase_quantity,
    COALESCE(SUM(pod.quantity)  - SUM(b.quantity_boms), 0) AS final_quantity
FROM 
    products p
LEFT JOIN 
    purchase_order_details pod ON p.id = pod.product_id

LEFT JOIN 
    (SELECT b.product_id, b.quantity AS quantity_boms
     FROM assemblies a
     LEFT JOIN boms b ON a.id = b.assembly_id 
     WHERE a.product_id IN (SELECT product_id FROM work_order_details)
     GROUP BY b.product_id, b.quantity ) b ON p.id = b.product_id
GROUP BY 
    p.id, p.product_name;

I change the relationship. I add assembly_work_order table it records the work_order_order related that has assemblies. I add this sum of all product_id and include in the final_quantity. Now this is my final result

SELECT 
    p.id AS product_id,
    p.product_name,
    (p.quantity + COALESCE(po.total_quantity, 0) - COALESCE(wd.total_quantity, 0) - COALESCE(awo.total_quantity, 0)) AS current_inventory
FROM 
    Products p
LEFT JOIN
    (SELECT product_id, SUM(quantity) AS total_quantity FROM purchase_order_details GROUP BY product_id) po
ON 
    p.id = po.product_id
LEFT JOIN
    (SELECT product_id, SUM(quantity) AS total_quantity FROM assembly_work_orders GROUP BY product_id) awo
ON 
    p.id = awo.product_id
LEFT JOIN
    (SELECT product_id, SUM(quantity) AS total_quantity FROM work_order_details GROUP BY product_id) wd
ON 
    p.id = wd.product_id;