SELECT
    o.order_num,
    o.order_date,
    buyer.name                                              AS buyer,
    -- выручка: сумма (кол-во × цена продажи) по всем позициям
    ROUND(SUM(oi.quantity * oi.price), 2)                  AS total_revenue,
    -- себестоимость: сумма по всем позициям × все ингредиенты
    ROUND(SUM(
        sm.quantity / s.yield_qty * oi.quantity * pr.price
    ), 2)                                                   AS total_cost,
    -- маржа
    ROUND(
        SUM(oi.quantity * oi.price) -
        SUM(sm.quantity / s.yield_qty * oi.quantity * pr.price),
    2)                                                      AS margin

FROM orders           o
JOIN counterparties   buyer   ON buyer.id    = o.buyer_id
JOIN order_items      oi      ON oi.order_id = o.id
JOIN items            p       ON p.id        = oi.item_id
JOIN specifications   s       ON s.product_id = p.id
JOIN specification_materials sm ON sm.specification_id = s.id
LEFT JOIN prices      pr      ON pr.item_id  = sm.material_id
    AND pr.effective_date = (
        SELECT MAX(effective_date)
        FROM prices
        WHERE item_id = sm.material_id
    )

WHERE o.id = 1   -- ← подставь нужный order_id
GROUP BY o.id, o.order_num, o.order_date, buyer.name;
------------
SELECT
    o.id                                            AS order_id,
    o.order_num,
    o.order_date,
    buyer.name                                      AS buyer,
    seller.name                                     AS seller,
    p.name                                          AS product,
    oi.quantity                                     AS qty_ordered,

    -- цена продажи (выручка по позиции)
    oi.price                                        AS sale_price,
    ROUND(oi.quantity * oi.price, 2)                AS revenue_line,

    -- себестоимость: (кол-во пицц / выход закладки) * расход ингредиента * цена ингредиента
    m.name                                          AS ingredient,
    u.name                                          AS unit,
    sm.quantity                                     AS ingredient_per_batch,
    s.yield_qty,
    ROUND(sm.quantity / s.yield_qty * oi.quantity, 4)
                                                    AS ingredient_total_qty,
    pr.price                                        AS ingredient_price,
    ROUND(sm.quantity / s.yield_qty * oi.quantity * pr.price, 2)
                                                    AS ingredient_cost

FROM orders           o
JOIN counterparties   buyer   ON buyer.id   = o.buyer_id
JOIN counterparties   seller  ON seller.id  = o.seller_id
JOIN order_items      oi      ON oi.order_id = o.id
JOIN items            p       ON p.id        = oi.item_id
-- берём рецептуру для этого продукта
JOIN specifications   s       ON s.product_id = p.id
JOIN specification_materials sm ON sm.specification_id = s.id
JOIN items            m       ON m.id = sm.material_id
JOIN units_of_measure u       ON u.id = m.unit_id
-- актуальная цена ингредиента
LEFT JOIN prices      pr      ON pr.item_id = m.id
    AND pr.effective_date = (
        SELECT MAX(effective_date)
        FROM prices
        WHERE item_id = m.id
    )

WHERE o.id = 1   -- ← подставь нужный order_id
ORDER BY p.name, m.name;