-- limpieza de la tabla resumen antes de actualizar
TRUNCATE TABLE item_behaivor;

-- se prepara los campos para insertar los datos
INSERT INTO item_behaivor (item_id, profit, profit_star, roi, roi_star, speed, speed_star, friction, friction_star, perishable, perishable_star, inversion, last_update)

-- se calculan los avg (profit) por item_id
WITH BaseAverages AS (
    SELECT 
        item_id,
        AVG(profit) AS avg_profit,
        AVG(roi) AS avg_roi,
        AVG(speed) AS avg_speed,
        AVG(friction) AS avg_bulk,
	AVG(perishable) AS avg_perishable,
        AVG(inversion) AS avg_inversion,
        MAX(last_update) AS max_date
    FROM item_behaivor_history_month
    GROUP BY item_id
),

-- se calculan los percentiles
Percentiles AS (
    SELECT 
        *,
        -- percentil para profit
        PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY avg_profit) OVER () as p_profit_20,
        PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY avg_profit) OVER () as p_profit_40,
        PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY avg_profit) OVER () as p_profit_60,
        PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY avg_profit) OVER () as p_profit_80,
        
        -- Percentiles para ROI
        PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY avg_roi) OVER () as p_roi_20,
        PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY avg_roi) OVER () as p_roi_40,
        PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY avg_roi) OVER () as p_roi_60,
        PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY avg_roi) OVER () as p_roi_80,

        -- Percentiles para Speed
        PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY avg_speed) OVER () as p_speed_20,
        PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY avg_speed) OVER () as p_speed_40,
        PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY avg_speed) OVER () as p_speed_60,
        PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY avg_speed) OVER () as p_speed_80,

        -- Percentiles para Bulk (Friction)
        PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY avg_bulk) OVER () as p_bulk_20,
        PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY avg_bulk) OVER () as p_bulk_40,
        PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY avg_bulk) OVER () as p_bulk_60,
        PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY avg_bulk) OVER () as p_bulk_80,

	-- Percentiles para Perishable
        PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY avg_perishable) OVER () as p_perishable_20,
        PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY avg_perishable) OVER () as p_perishable_40,
        PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY avg_perishable) OVER () as p_perishable_60,
        PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY avg_perishable) OVER () as p_perishable_80
    FROM BaseAverages
)
SELECT 
    item_id,
    avg_profit,
    CASE 
        WHEN avg_profit <= p_profit_20 THEN 1
        WHEN avg_profit <= p_profit_40 THEN 2
        WHEN avg_profit <= p_profit_60 THEN 3
        WHEN avg_profit <= p_profit_80 THEN 4
        ELSE 5 
    END AS profit_star,
    avg_roi,
    CASE 
        WHEN avg_roi <= p_roi_20 THEN 1
        WHEN avg_roi <= p_roi_40 THEN 2
        WHEN avg_roi <= p_roi_60 THEN 3
        WHEN avg_roi <= p_roi_80 THEN 4
        ELSE 5 
    END AS roi_star,
    avg_speed,
    CASE 
        WHEN avg_speed <= p_speed_20 THEN 5
        WHEN avg_speed <= p_speed_40 THEN 4
        WHEN avg_speed <= p_speed_60 THEN 3
        WHEN avg_speed <= p_speed_80 THEN 2
        ELSE 1
    END AS speed_star,
    avg_bulk,
    CASE 
        WHEN avg_bulk <= p_bulk_20 THEN 5
        WHEN avg_bulk <= p_bulk_40 THEN 4
        WHEN avg_bulk <= p_bulk_60 THEN 3
        WHEN avg_bulk <= p_bulk_80 THEN 2
        ELSE 1
    END AS bulk_star,
    avg_perishable,
    CASE 
        WHEN avg_perishable <= p_perishable_20 THEN 5
        WHEN avg_perishable <= p_perishable_40 THEN 4
        WHEN avg_perishable <= p_perishable_60 THEN 3
        WHEN avg_perishable <= p_perishable_80 THEN 2
        ELSE 1
    END AS perishable_star,
    avg_inversion,
    max_date
FROM Percentiles;