How to organize a warehouse accounting scheme with moving to stores for sale?

Question

MySQL

Imagine a situation.
There is a base with goods, there is a base of shops (customers). The goods can be moved to stores for sale and, depending on the situation, can either return to the warehouse or fix the sale with a subsequent increase in the store's debt.
The difficulty arose when designing the schematics, provided that the shipment of the same product (id) can be at different prices.
Example:
Movement through the warehouse is carried out in the table sale
id, warehouse id, product id, store id, quantity, amount
Product id-1 was in the database with a cost of 50 c.u.
It was shipped with record No. 1
id-1: 5 pcs * 50 c.u. = 250 c.u. from warehouse id-1, shop-1
After some time, the product became more expensive and there was a change in the database for the cost of goods with id-1, for example, it became 70.
And it ships to the same buyer
id-2: 3 pcs * 70 c.u. = 210 c.u. from warehouse id-1, store-1
We have a table with the movement of goods, a table with goods, and so on.
Let's try to ask simple questions:
1. How many items are in store id-1 ? easy, sum up by store id and product id.
2. How much is the product in store id-1? just as easy to summarize.
Now there are 2 operations that can be carried out with this base and the product:
1. Selling a part of the product by the store.
2. Return of unsold goods to the warehouse.
And here comes a dead end.
How do we process the actual sale of the item? We know that in store-1 there are 8 units of goods id-1 in the amount of 460 USD. That is, if the store sold 2 units of goods, then we must write the third line to the sale base, where the quantity will be -2, which will give us the actual balances of the goods in quantitative terms, and what is the cost in the line? Set an average cost? Then fractional numbers and rounding will begin.
Return of unsold goods - the same story. When returning, we must reduce not only the quantity of goods, but also indicate by what amount the store's commodity debt is reduced. In the "product" table, the price is current for new shipments. We are only interested in the prices at which we previously shipped to this store. Again, averages are obtained. There are 2 shipments of goods with the same id but different prices in different periods, and I have no idea how to organize their partial sale or partial return.
I will be glad for any hints.

Answer 1

The way out is quite obvious - to add the price field to the table where the presence in the warehouse is registered, and probably the date field or a link to the movement record, from which it will already be possible to get the date. When decommissioning, write off the goods by FIFO, starting from the earliest date.
Then the quantity will be in the context of the price on the date of shipment.