INSERT INTO provider_behaivor_history_month 
    (provider_id, profit, roi, last_update)

-- se calculan los ganancias por item
WITH DetalleGanancias AS (
    SELECT 
        p.provider_id,
        p.id AS purchase_id,
        DATE_FORMAT(p.purchase_date, '%Y-%m-01') as mes,
        -- Costo base de la mercancía comprada
        p.general_total_price AS costo_factura_mercancia,
        -- Fórmula PHP: (Venta) - (Costo Unitario + Costo Adicional por Fila)
        SUM(
            (svi.qty * svi.price) - 
            (svi.qty * (pvi.unit_price + (pvi.cogs_row_amount / NULLIF(pvi.qty, 0))))
        ) AS ganancias_items
    FROM purchase p
    INNER JOIN purchase_vs_item pvi ON p.id = pvi.purchaseId
    INNER JOIN sale_vs_item svi ON pvi.id = svi.purchase_vs_itemId
    WHERE p.deleted = 0 
      AND pvi.deleted = 0 
      AND svi.deleted = 0
    GROUP BY p.provider_id, p.id, DATE_FORMAT(p.purchase_date, '%Y-%m-01')
),

-- se calculan los gastos que no se asignaron a ítems específicos
GastosGenerales AS (
    SELECT 
        purchaseId,
        SUM(amount) as total_gastos_extra
    FROM purchase_vs_expenses
    WHERE deleted = 0
    GROUP BY purchaseId
)

-- se calculan e insertan los datos
SELECT 
    d.provider_id,
    SUM(COALESCE(d.ganancias_items, 0) - COALESCE(g.total_gastos_extra, 0)) AS profit,
    CASE 
        WHEN SUM(d.costo_factura_mercancia + COALESCE(g.total_gastos_extra, 0)) > 0 
        THEN (SUM(COALESCE(d.ganancias_items, 0) - COALESCE(g.total_gastos_extra, 0)) / 
              SUM(d.costo_factura_mercancia + COALESCE(g.total_gastos_extra, 0))) * 100 
        ELSE 0 
    END AS roi,
    LAST_DAY(d.mes) AS last_update
FROM DetalleGanancias d
LEFT JOIN GastosGenerales g ON d.purchase_id = g.purchaseId
GROUP BY d.provider_id, d.mes;