= Прогноза за потрошувачка на состојки врз основа на продажби =

== Опис ==

Извештајот предвидува колку од секоја состојка ќе биде потрошена во следните 30 дена, врз основа на просечната дневна потрошувачка во последните 90 дена. Користи три нивоа на вгнездување: (1) продажби по производ по ден, (2) агрегација по состојка преку рецепт, (3) споредба со моменталната залиха за да се најдат состојки што ќе треба наскоро да се нарачаат.

== SQL решение ==

{{{
CREATE OR REPLACE FUNCTION project.get_ingredient_consumption_forecast()
RETURNS TABLE (
    ingredient_id INT,
    ingredient_name TEXT,
    avg_daily_usage NUMERIC,
    forecast_30d NUMERIC,
    current_stock NUMERIC,
    days_until_empty NUMERIC,
    action TEXT
)
LANGUAGE sql
AS $$
    WITH daily_product_sales AS (
        SELECT 
            oi.product_id,
            DATE(o.created_at) AS sale_date,
            SUM(oi.quantity) AS qty_sold
        FROM project.order_item oi
        JOIN project.orders o ON o.order_id = oi.order_id
        WHERE o.status = 'ПЛАТЕНА'
          AND o.created_at >= NOW() - INTERVAL '90 days'
        GROUP BY oi.product_id, DATE(o.created_at)
    ),
    ingredient_usage AS (
        SELECT 
            ri.ingredient_id AS ing_id,
            SUM(dps.qty_sold * ri.quantity_needed)::numeric AS total_used
        FROM daily_product_sales dps
        JOIN project.recipe r ON r.product_id = dps.product_id
        JOIN project.recipe_item ri ON ri.recipe_id = r.recipe_id
        GROUP BY ri.ingredient_id
    ),
    stock AS (
        SELECT 
            ii.ingredient_id AS ing_id,
            COALESCE(SUM(ii.quantity_change), 0)::numeric AS current_stock
        FROM project.ingredient_inventory ii
        GROUP BY ii.ingredient_id
    )
    SELECT 
        i.ingredient_id,
        i.name::TEXT,
        ROUND(iu.total_used / 90.0, 3),
        ROUND((iu.total_used / 90.0) * 30, 2),
        COALESCE(s.current_stock, 0),
        CASE 
            WHEN (iu.total_used / 90.0) > 0 
            THEN ROUND(COALESCE(s.current_stock, 0) / (iu.total_used / 90.0), 1)
            ELSE NULL
        END,
        CASE 
            WHEN COALESCE(s.current_stock, 0) < (iu.total_used / 90.0) * 30 
                 AND i.min_stock > COALESCE(s.current_stock, 0) THEN 'ИТНО НАРАЧАЈ'
            WHEN COALESCE(s.current_stock, 0) < (iu.total_used / 90.0) * 30 THEN 'НАРАЧАЈ'
            ELSE 'ОК'
        END::TEXT
    FROM project.ingredient i
    JOIN ingredient_usage iu ON iu.ing_id = i.ingredient_id
    LEFT JOIN stock s ON s.ing_id = i.ingredient_id
    WHERE i.active = TRUE
    ORDER BY 6 ASC NULLS LAST;
$$;
}}}

== Релациона алгебра ==

{{{
OI(order_id, product_id, quantity, unit_price)
O(order_id, status, created_at)
R(recipe_id, product_id)
RI(recipe_id, ingredient_id, quantity_needed)
I(ingredient_id, name, min_stock, active)
II(id, ingredient_id, quantity_change)

J1 ← OI ⨝ OI.order_id = O.order_id O
F1 ← σ status='ПЛАТЕНА' ∧ created_at ≥ NOW() - INTERVAL '90 days' (J1)

G1 ← γ product_id, sale_date; SUM(quantity) → qty_sold (F1)

J2 ← G1 ⨝ G1.product_id = R.product_id R
J3 ← J2 ⨝ R.recipe_id = RI.recipe_id RI
G2 ← γ ingredient_id; SUM(qty_sold * quantity_needed) → total_used (J3)

G3 ← γ ingredient_id; COALESCE(SUM(quantity_change), 0) → current_stock (II)

J4 ← I ⨝ G2 ⨝ G3
F2 ← σ active=TRUE (J4)

R ← π ingredient_id, ingredient_name, avg_daily_usage, forecast_30d,
      current_stock, days_until_empty, action (F2)

R_final ← τ days_until_empty ASC (R)
}}}