A grocery data product
built for DAIS 2026

-- Gold: daily inventory risk + recommended action
CREATE OR REPLACE TABLE workspace.grocery_data_intelligence.gold_inventory_risk_daily AS
SELECT
  current_date()                              AS report_date,
  s.store_id, s.store_name, s.city, s.state,
  p.product_id, p.product_name, p.category,
  i.current_stock,
  COALESCE(d.avg_daily_demand, 0)             AS avg_daily_demand,
  i.days_until_expiry,
  ROUND(i.current_stock / NULLIF(d.avg_daily_demand, 0), 2) AS days_of_stock_remaining,
  CASE
    WHEN i.current_stock / NULLIF(d.avg_daily_demand, 0) < 2 THEN 'High'
    WHEN i.current_stock / NULLIF(d.avg_daily_demand, 0) <= 5 THEN 'Medium'
    ELSE 'Low'
  END                                         AS stockout_risk_level,
  CASE
    WHEN i.days_until_expiry IS NULL          THEN 'N/A'
    WHEN i.days_until_expiry <= 1             THEN 'High'
    WHEN i.days_until_expiry <= 3             THEN 'Medium'
    ELSE 'Low'
  END                                         AS waste_risk_level,
  CASE
    WHEN i.current_stock / NULLIF(d.avg_daily_demand, 0) < 2 THEN 'Reorder Now'
    WHEN i.days_until_expiry <= 1                            THEN 'Discount or Donate'
    WHEN i.days_until_expiry <= 3                            THEN 'Promote Soon'
    WHEN i.current_stock / NULLIF(d.avg_daily_demand, 0) <= 5 THEN 'Monitor Closely'
    ELSE 'No Action Needed'
  END                                         AS recommended_action,
  current_timestamp()                         AS gold_created_at
FROM workspace.grocery_data_intelligence.silver_inventory i
JOIN workspace.grocery_data_intelligence.silver_stores   s USING (store_id)
JOIN workspace.grocery_data_intelligence.silver_products p USING (product_id)
LEFT JOIN (
  SELECT store_id, product_id, AVG(quantity_sold) AS avg_daily_demand
  FROM workspace.grocery_data_intelligence.silver_sales
  WHERE sale_date >= current_date() - INTERVAL 14 DAYS
  GROUP BY store_id, product_id
) d USING (store_id, product_id);