-- se prepara los campos para insertar los datos
INSERT INTO customer_behaivor_history_month 
    (customer_id, profit, roi, last_update)

-- se calculan los ganancias por venta realizadas
WITH GananciasPorVenta AS (
    SELECT 
        c.id AS customer_id,
        s.id AS sale_id,
        DATE_FORMAT(s.sale_date, '%Y-%m-01') as mes,
        -- Ganancia: (Precio Venta * Qty) - (Costo Compra + Gasto Fila)
        SUM(
            (svi.qty * svi.price) - 
            (svi.qty * (pvi.unit_price + (pvi.cogs_row_amount / NULLIF(pvi.qty, 0))))
        ) AS profit_venta,
        -- Costo total de lo vendido en esta factura (para calcular ROI)
        SUM(
            (svi.qty * pvi.unit_price) + (svi.qty * (pvi.cogs_row_amount / NULLIF(pvi.qty, 0)))
        ) AS costo_venta
    FROM sale s
    INNER JOIN customer c ON s.customer_id = c.id
    INNER JOIN sale_vs_item svi ON s.id = svi.saleId
    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
    GROUP BY c.id, s.id, DATE_FORMAT(s.sale_date, '%Y-%m-01')
)

-- se calculan e insertan los datos
SELECT 
    customer_id,
    SUM(profit_venta) AS profit,
    -- ROI = (Ganancia Total / Costo Total) * 100
    CASE 
        WHEN SUM(costo_venta) > 0 
        THEN (SUM(profit_venta) / SUM(costo_venta)) * 100 
        ELSE 0 
    END AS roi,
    LAST_DAY(mes) AS last_update
FROM GananciasPorVenta
GROUP BY customer_id, mes;