calc FIFO based inventory valuation with MySQL 8.0

Question

nice day for all. I spent more than three days of searching and testing but nothing helped me. let us assume I have many way to enter items of product to storages, So I created 'view' to show me the product transactions "in" like this:

product_id	storage_id	type	unit_cost	quantity	date
82	1	in	1	10	2022-04-25 17:04:13
82	1	in	2	10	2022-04-25 17:04:51
82	1	in	3	10	2022-04-25 17:07:13
82	16	in	2	10	2022-04-25 17:10:51
82	16	in	3	10	2022-04-25 17:12:13

also I sold items by orders (order and order items tables view)

product_id	storage_id	quantity	date
82	1	15	2022-04-25 17:06:27
82	1	7	2022-04-25 17:08:24
82	2	5	2022-04-25 17:20:13

so from these tables and views, is there any query to calc my inventory valuation (cost of goods didn't sold) based on FIFO and LIFO that query should show data as follow(FIFO):

product_id	storage_id	remaining_quantity	total_cost
82	1	8	24
82	16	15	40

(LIFO):

product_id	storage_id	remaining_quantity	total_cost
82	1	8	14
82	16	15	35