INSERT INTO purchase_behaivor_history (purchase_id, provider_id, item_id, inversion, profit, roi, date)
WITH GastosPorFactura AS (

    SELECT 
        purchaseId,
        SUM(amount) as total_gastos_generales
    FROM purchase_vs_expenses
    WHERE deleted = 0
    GROUP BY purchaseId
),
VentaDetalle AS (

    SELECT 
        svi.purchase_vs_itemId,
        SUM(svi.total) AS total_venta,
        SUM(svi.qty * (pvi.unit_price + (pvi.cogs_row_amount / NULLIF(pvi.qty, 0)))) AS total_costo_venta
    FROM sale_vs_item svi
    INNER JOIN purchase_vs_item pvi ON svi.purchase_vs_itemId = pvi.id
    WHERE svi.deleted = 0
    GROUP BY svi.purchase_vs_itemId
),
PerdidaDetalle AS (
    -- 3. Valor de las mermas
    SELECT 
        purchase_vs_itemId,
        SUM(lost_qty * lost_price) AS valor_perdida
    FROM purchase_vs_lost
    WHERE deleted = 0
    GROUP BY purchase_vs_itemId
)
-- 4. Consolidación Final con Prorrateo
SELECT 
    p.id AS purchase_id,
    p.provider_id,
    pvi.item_id,
    
    -- Inversion Base (Costo Item + Gasto Directo de Fila)
    (pvi.qty * (pvi.unit_price + (pvi.cogs_row_amount / NULLIF(pvi.qty, 0)))) AS inversion_base,
    
    -- Profit: Venta - Costo Vendido - Perdidas - (Gastos Generales Proporcionales)
    (
        COALESCE(v.total_venta, 0) - 
        COALESCE(v.total_costo_venta, 0) - 
        COALESCE(l.valor_perdida, 0) - 
        -- Cálculo de Prorrateo: (Gasto Total Factura) * (Valor de este Item / Valor Total Factura)
        (COALESCE(g.total_gastos_generales, 0) * (pvi.total_price / NULLIF(p.general_total_price, 0)))
    ) AS profit_neto,
    
    -- ROI Neto: (Profit Neto / Inversion Base) * 100
    CASE 
        WHEN (pvi.qty * pvi.unit_price) > 0 
        THEN (
            (COALESCE(v.total_venta, 0) - COALESCE(v.total_costo_venta, 0) - COALESCE(l.valor_perdida, 0) - 
            (COALESCE(g.total_gastos_generales, 0) * (pvi.total_price / NULLIF(p.general_total_price, 0)))) 
            / (pvi.qty * (pvi.unit_price + (pvi.cogs_row_amount / NULLIF(pvi.qty, 0))))
        ) * 100
        ELSE 0 
    END AS roi,
    
    p.purchase_date AS date
FROM purchase p
INNER JOIN purchase_vs_item pvi ON p.id = pvi.purchaseId
LEFT JOIN GastosPorFactura g ON p.id = g.purchaseId
LEFT JOIN VentaDetalle v ON pvi.id = v.purchase_vs_itemId
LEFT JOIN PerdidaDetalle l ON pvi.id = l.purchase_vs_itemId
WHERE p.deleted = 0 AND pvi.deleted = 0;