INSERT INTO item_behaivor_history_month 
    (item_id, profit, inversion, speed, friction, roi, perishable, last_update)
WITH VentasMensuales AS (
    -- obtenemos las ventas por mes
    SELECT 
        pvi.item_id,
        svi.purchase_vs_itemId,
        DATE_FORMAT(svi.date_registered, '%Y-%m-01') as mes,
        SUM(svi.total) as ingresos_totales,
        SUM(svi.qty) as unidades_vendidas,
        COUNT(DISTINCT svi.saleId) as transacciones_ventas,
        MAX(svi.date_registered) as fecha_ultima_venta,
        SUM(svi.qty * pvi.unit_price) as costo_total_vendido
    FROM sale_vs_item svi
    INNER JOIN purchase_vs_item pvi ON svi.purchase_vs_itemId = pvi.id
    WHERE svi.deleted = 0
    GROUP BY pvi.item_id, svi.purchase_vs_itemId, DATE_FORMAT(svi.date_registered, '%Y-%m-01')
),
ComprasMensuales AS (
    -- obtenemos compras por mes
    SELECT 
        id as purchase_item_id,
        item_id,
	qty as unidades_compradas,
        date_registered as fecha_compra,
        total_price as inversion_item
    FROM purchase_vs_item
    WHERE deleted = 0
),
PerdidasMensuales AS (
    -- calculo de perdidas
    SELECT 
        purchase_vs_itemId,
        SUM(lost_qty) as total_lost_qty
    FROM purchase_vs_lost
    WHERE deleted = 0
    GROUP BY purchase_vs_itemId
)
-- cosolidacion y insercion de datos
SELECT 
    v.item_id AS item_name,
    SUM(v.ingresos_totales - v.costo_total_vendido) AS profit,
    SUM(DISTINCT c.inversion_item) AS inversion,
    AVG(DATEDIFF(v.fecha_ultima_venta, c.fecha_compra)) AS speed_days,
    AVG(v.transacciones_ventas) AS friction_transactions,
    CASE 
        WHEN SUM(c.inversion_item) > 0 
        THEN (SUM(v.ingresos_totales - v.costo_total_vendido) / SUM(c.inversion_item)) * 100 
        ELSE 0 
    END AS roi,
    CASE 
        WHEN SUM(c.unidades_compradas) > 0 
        THEN SUM(COALESCE(l.total_lost_qty, 0)) / SUM(c.unidades_compradas)
        ELSE 0 
    END AS perishable_rate,
    LAST_DAY(v.mes) AS last_update
FROM VentasMensuales v
INNER JOIN ComprasMensuales c ON v.purchase_vs_itemId = c.purchase_item_id
LEFT JOIN PerdidasMensuales l ON c.purchase_item_id = l.purchase_vs_itemId
GROUP BY v.item_id, v.mes;