INSERT INTO sales_behaivor_history 
    (purchase_id, sale_id, customer_id, item_id, profit, roi, date)
SELECT 
    pvi.purchaseId AS purchase_id,
    s.id AS sale_id,
    s.customer_id,
    pvi.item_id,

    (svi.total - (svi.qty * (pvi.unit_price + (pvi.cogs_row_amount / NULLIF(pvi.qty, 0))))) AS profit,
    

    CASE 
        WHEN (svi.qty * (pvi.unit_price + (pvi.cogs_row_amount / NULLIF(pvi.qty, 0)))) > 0 
        THEN ((svi.total - (svi.qty * (pvi.unit_price + (pvi.cogs_row_amount / NULLIF(pvi.qty, 0))))) / 
              (svi.qty * (pvi.unit_price + (pvi.cogs_row_amount / NULLIF(pvi.qty, 0))))) * 100
        ELSE 0 
    END AS roi,
    
    s.sale_date AS date
FROM sale_vs_item svi
INNER JOIN sale s ON svi.saleId = s.id
INNER JOIN purchase_vs_item pvi ON svi.purchase_vs_itemId = pvi.id
WHERE s.deleted = 0 
  AND svi.deleted = 0
  AND pvi.deleted = 0;