TRUNCATE TABLE item_customer_behaivor_history;

INSERT INTO item_customer_behaivor_history 
    (customer_id, item_id, profit, profit_star, roi, roi_star, date)
WITH TotalesReales AS (
    SELECT 
        customer_id,
        item_id,
        SUM(profit) AS total_profit,
        SUM(profit / (roi / 100)) AS costo_base_total, 
        MAX(date) AS last_date
    FROM sales_behaivor_history
    GROUP BY customer_id, item_id
),
Averages AS (

    SELECT 
        customer_id,
        item_id,
        total_profit AS calculated_profit,

        CASE 
            WHEN (costo_base_total) != 0 
            THEN (total_profit / ABS(costo_base_total)) * 100 
            ELSE AVG_ROI_SIMPLE -- Fallback a promedio si el costo es cero
        END AS calculated_roi,
        last_date
    FROM (
        SELECT *, 
        (SELECT AVG(roi) FROM sales_behaivor_history s2 
         WHERE s2.customer_id = t.customer_id AND s2.item_id = t.item_id) as AVG_ROI_SIMPLE
        FROM TotalesReales t
    ) sub
),
Percentiles AS (

    SELECT 
        *,
        PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY calculated_profit) OVER () as p_profit_20,
        PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY calculated_profit) OVER () as p_profit_40,
        PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY calculated_profit) OVER () as p_profit_60,
        PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY calculated_profit) OVER () as p_profit_80,
        
        PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY calculated_roi) OVER () as p_roi_20,
        PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY calculated_roi) OVER () as p_roi_40,
        PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY calculated_roi) OVER () as p_roi_60,
        PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY calculated_roi) OVER () as p_roi_80
    FROM Averages
)
SELECT 
    customer_id,
    item_id,
    calculated_profit,
    CASE 
        WHEN calculated_profit <= p_profit_20 THEN 1
        WHEN calculated_profit <= p_profit_40 THEN 2
        WHEN calculated_profit <= p_profit_60 THEN 3
        WHEN calculated_profit <= p_profit_80 THEN 4
        ELSE 5 
    END AS profit_star,
    calculated_roi,
    CASE 
        WHEN calculated_roi <= p_roi_20 THEN 1
        WHEN calculated_roi <= p_roi_40 THEN 2
        WHEN calculated_roi <= p_roi_60 THEN 3
        WHEN calculated_roi <= p_roi_80 THEN 4
        ELSE 5 
    END AS roi_star,
    last_date
FROM Percentiles;