WITH order_stats AS (
   SELECT
      status,
      COUNT(*) AS order_count,
      SUM(amounts.total / COALESCE(NULLIF(amounts.normalizer, 0), 1)) AS total_revenue,
      AVG(amounts.total / COALESCE(NULLIF(amounts.normalizer, 0), 1)) AS avg_order_value
   FROM
      `nekt_raw.magalu_orders`
   WHERE
      DATE(created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
   GROUP BY
      status
),
total AS (
   SELECT SUM(order_count) AS total_orders FROM order_stats
)
SELECT
   os.status,
   os.order_count,
   ROUND(SAFE_DIVIDE(os.order_count * 100.0, t.total_orders), 2) AS percentage,
   ROUND(os.total_revenue, 2) AS total_revenue,
   ROUND(os.avg_order_value, 2) AS avg_order_value
FROM
   order_stats os, total t
ORDER BY
   os.order_count DESC

WITH item_sales AS (
   SELECT
      i.info.sku AS sku,
      i.info.name AS product_name,
      SUM(i.quantity) AS total_quantity,
      SUM(i.amounts.total / COALESCE(NULLIF(i.amounts.normalizer, 0), 1)) AS total_revenue,
      COUNT(DISTINCT o.id) AS order_count
   FROM
      `nekt_raw.magalu_orders` o,
      UNNEST(o.deliveries) AS d,
      UNNEST(d.items) AS i
   WHERE
      o.status NOT IN ('cancelled')
      AND DATE(o.created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
   GROUP BY
      i.info.sku,
      i.info.name
)
SELECT
   sku,
   product_name,
   total_quantity,
   order_count,
   ROUND(total_revenue, 2) AS total_revenue,
   ROUND(SAFE_DIVIDE(total_revenue, total_quantity), 2) AS avg_unit_price
FROM
   item_sales
ORDER BY
   total_revenue DESC
LIMIT 20

WITH delivery_metrics AS (
   SELECT
      d.shipping.provider.name AS provider_name,
      d.status AS delivery_status,
      COUNT(*) AS delivery_count,
      AVG(d.shipping.deadline.value) AS avg_deadline_days,
      SUM(CASE WHEN d.status = 'delivered' THEN 1 ELSE 0 END) AS delivered_count,
      SUM(CASE WHEN d.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_count
   FROM
      `nekt_raw.magalu_deliveries` d
   WHERE
      DATE(d.purchased_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
   GROUP BY
      d.shipping.provider.name,
      d.status
)
SELECT
   provider_name,
   SUM(delivery_count) AS total_deliveries,
   SUM(delivered_count) AS total_delivered,
   SUM(cancelled_count) AS total_cancelled,
   ROUND(SAFE_DIVIDE(SUM(delivered_count) * 100.0, SUM(delivery_count)), 2) AS delivery_success_rate,
   ROUND(AVG(avg_deadline_days), 1) AS avg_deadline_days
FROM
   delivery_metrics
GROUP BY
   provider_name
ORDER BY
   total_deliveries DESC