WITH inventory_by_product AS (
  SELECT
    ii.variant.product.id AS product_id,
    ii.variant.product.title AS product_title,
    ii.sku,
    ii.variant.title AS variant_title,
    CAST(ii.unitCost.amount AS DOUBLE) AS unit_cost,
    ii.unitCost.currencyCode AS currency,
    SUM(
      CASE WHEN q.name = 'available' THEN q.quantity ELSE 0 END
    ) AS available_qty,
    SUM(
      CASE WHEN q.name = 'committed' THEN q.quantity ELSE 0 END
    ) AS committed_qty,
    SUM(
      CASE WHEN q.name = 'incoming' THEN q.quantity ELSE 0 END
    ) AS incoming_qty,
    COUNT(DISTINCT il.location.id) AS location_count
  FROM
    nekt_raw.shopify_inventory_items ii
    CROSS JOIN UNNEST(ii.inventoryLevels) AS t(il)
    CROSS JOIN UNNEST(il.quantities) AS t2(q)
  WHERE
    ii.tracked = true
  GROUP BY
    ii.variant.product.id,
    ii.variant.product.title,
    ii.sku,
    ii.variant.title,
    ii.unitCost.amount,
    ii.unitCost.currencyCode
)
SELECT
  product_title,
  sku,
  variant_title,
  available_qty,
  committed_qty,
  incoming_qty,
  (available_qty + incoming_qty) AS total_incoming_available,
  location_count,
  ROUND(unit_cost * available_qty, 2) AS inventory_value,
  currency,
  CASE
    WHEN available_qty <= 0 THEN 'OUT_OF_STOCK'
    WHEN available_qty <= 5 THEN 'LOW_STOCK'
    WHEN available_qty <= 20 THEN 'NORMAL'
    WHEN available_qty > 100 THEN 'OVERSTOCK'
    ELSE 'HEALTHY'
  END AS stock_status
FROM
  inventory_by_product
ORDER BY
  available_qty ASC

WITH daily_sales AS (
  SELECT
    li.variant.product.id AS product_id,
    li.variant.product.title AS product_title,
    li.sku,
    SUM(li.quantity) AS units_sold,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(CAST(li.discountedTotalSet.shopMoney.amount AS DOUBLE)) AS revenue
  FROM
    nekt_raw.shopify_orders o
    CROSS JOIN UNNEST(o.lineItems) AS t(li)
  WHERE
    o.displayFinancialStatus IN ('PAID', 'PARTIALLY_REFUNDED')
    AND o.test = false
    AND o.processedAt >= CURRENT_DATE - INTERVAL '30' DAY
  GROUP BY
    li.variant.product.id,
    li.variant.product.title,
    li.sku
),
current_stock AS (
  SELECT
    ii.variant.product.id AS product_id,
    ii.sku,
    SUM(
      CASE WHEN q.name = 'available' THEN q.quantity ELSE 0 END
    ) AS available_qty
  FROM
    nekt_raw.shopify_inventory_items ii
    CROSS JOIN UNNEST(ii.inventoryLevels) AS t(il)
    CROSS JOIN UNNEST(il.quantities) AS t2(q)
  WHERE
    ii.tracked = true
  GROUP BY
    ii.variant.product.id,
    ii.sku
)
SELECT
  ds.product_title,
  ds.sku,
  ds.units_sold AS units_sold_30d,
  ds.order_count AS orders_30d,
  ROUND(ds.revenue, 2) AS revenue_30d,
  ROUND(ds.units_sold / 30.0, 2) AS daily_velocity,
  cs.available_qty,
  CASE
    WHEN ds.units_sold / 30.0 > 0
    THEN CAST(ROUND(cs.available_qty / (ds.units_sold / 30.0), 0) AS INTEGER)
    ELSE NULL
  END AS days_of_stock,
  CASE
    WHEN cs.available_qty <= 0 THEN 'STOCKOUT'
    WHEN ds.units_sold / 30.0 > 0 AND cs.available_qty / (ds.units_sold / 30.0) <= 7 THEN 'CRITICAL'
    WHEN ds.units_sold / 30.0 > 0 AND cs.available_qty / (ds.units_sold / 30.0) <= 14 THEN 'REORDER_NOW'
    WHEN ds.units_sold / 30.0 > 0 AND cs.available_qty / (ds.units_sold / 30.0) <= 30 THEN 'MONITOR'
    ELSE 'HEALTHY'
  END AS reorder_urgency
FROM
  daily_sales ds
  LEFT JOIN current_stock cs ON ds.product_id = cs.product_id AND ds.sku = cs.sku
ORDER BY
  days_of_stock ASC NULLS LAST

WITH location_inventory AS (
  SELECT
    il.location.id AS location_id,
    il.location.name AS location_name,
    ii.variant.product.title AS product_title,
    ii.sku,
    SUM(
      CASE WHEN q.name = 'available' THEN q.quantity ELSE 0 END
    ) AS available_qty,
    SUM(
      CASE WHEN q.name = 'committed' THEN q.quantity ELSE 0 END
    ) AS committed_qty,
    CAST(ii.unitCost.amount AS DOUBLE) AS unit_cost
  FROM
    nekt_raw.shopify_inventory_items ii
    CROSS JOIN UNNEST(ii.inventoryLevels) AS t(il)
    CROSS JOIN UNNEST(il.quantities) AS t2(q)
  WHERE
    ii.tracked = true
  GROUP BY
    il.location.id,
    il.location.name,
    ii.variant.product.title,
    ii.sku,
    ii.unitCost.amount
)
SELECT
  location_name,
  COUNT(DISTINCT sku) AS unique_skus,
  SUM(available_qty) AS total_available,
  SUM(committed_qty) AS total_committed,
  ROUND(SUM(unit_cost * available_qty), 2) AS total_inventory_value,
  SUM(CASE WHEN available_qty <= 0 THEN 1 ELSE 0 END) AS out_of_stock_skus,
  SUM(CASE WHEN available_qty BETWEEN 1 AND 5 THEN 1 ELSE 0 END) AS low_stock_skus,
  ROUND(
    SUM(CASE WHEN available_qty <= 0 THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(DISTINCT sku), 0),
    1
  ) AS stockout_rate_pct
FROM
  location_inventory
GROUP BY
  location_name
ORDER BY
  total_inventory_value DESC

WITH product_sales_90d AS (
  SELECT
    li.variant.product.id AS product_id,
    li.variant.product.title AS product_title,
    li.sku,
    SUM(li.quantity) AS units_sold_90d,
    SUM(CAST(li.discountedTotalSet.shopMoney.amount AS DOUBLE)) AS revenue_90d,
    MIN(o.processedAt) AS first_sale,
    MAX(o.processedAt) AS last_sale
  FROM
    nekt_raw.shopify_orders o
    CROSS JOIN UNNEST(o.lineItems) AS t(li)
  WHERE
    o.displayFinancialStatus IN ('PAID', 'PARTIALLY_REFUNDED')
    AND o.test = false
    AND o.processedAt >= CURRENT_DATE - INTERVAL '90' DAY
  GROUP BY
    li.variant.product.id,
    li.variant.product.title,
    li.sku
),
current_stock AS (
  SELECT
    ii.variant.product.id AS product_id,
    ii.sku,
    SUM(
      CASE WHEN q.name = 'available' THEN q.quantity ELSE 0 END
    ) AS available_qty,
    CAST(ii.unitCost.amount AS DOUBLE) AS unit_cost
  FROM
    nekt_raw.shopify_inventory_items ii
    CROSS JOIN UNNEST(ii.inventoryLevels) AS t(il)
    CROSS JOIN UNNEST(il.quantities) AS t2(q)
  WHERE
    ii.tracked = true
  GROUP BY
    ii.variant.product.id,
    ii.sku,
    ii.unitCost.amount
)
SELECT
  COALESCE(cs.product_id, ps.product_id) AS product_id,
  COALESCE(ps.product_title, 'Unknown') AS product_title,
  COALESCE(cs.sku, ps.sku) AS sku,
  COALESCE(ps.units_sold_90d, 0) AS units_sold_90d,
  ROUND(COALESCE(ps.revenue_90d, 0), 2) AS revenue_90d,
  cs.available_qty,
  ROUND(cs.unit_cost * cs.available_qty, 2) AS stock_value_at_cost,
  CASE
    WHEN COALESCE(ps.units_sold_90d, 0) = 0 AND cs.available_qty > 0 THEN 'DEAD_STOCK'
    WHEN COALESCE(ps.units_sold_90d, 0) > 0 AND cs.available_qty > 0
      THEN ROUND(ps.units_sold_90d * 4.0 / cs.available_qty, 2)
    ELSE NULL
  END AS annual_turnover_rate,
  CASE
    WHEN COALESCE(ps.units_sold_90d, 0) = 0 AND cs.available_qty > 0 THEN 'Dead Stock'
    WHEN COALESCE(ps.units_sold_90d, 0) > 0 AND cs.available_qty > 0
      AND (ps.units_sold_90d * 4.0 / cs.available_qty) < 2 THEN 'Slow Mover'
    WHEN COALESCE(ps.units_sold_90d, 0) > 0 AND cs.available_qty > 0
      AND (ps.units_sold_90d * 4.0 / cs.available_qty) >= 2
      AND (ps.units_sold_90d * 4.0 / cs.available_qty) < 6 THEN 'Moderate'
    WHEN COALESCE(ps.units_sold_90d, 0) > 0 AND cs.available_qty > 0
      AND (ps.units_sold_90d * 4.0 / cs.available_qty) >= 6 THEN 'Fast Mover'
    ELSE 'No Stock'
  END AS turnover_category
FROM
  current_stock cs
  LEFT JOIN product_sales_90d ps ON cs.product_id = ps.product_id AND cs.sku = ps.sku
WHERE
  cs.available_qty > 0
ORDER BY
  annual_turnover_rate ASC NULLS FIRST

WITH abandoned_analysis AS (
  SELECT
    ac.id,
    ac.createdAt,
    CAST(ac.totalPriceSet.presentmentMoney.amount AS DOUBLE) AS cart_value,
    ac.totalPriceSet.presentmentMoney.currencyCode AS currency,
    ac.customer.email AS customer_email,
    ac.completedAt,
    CASE WHEN ac.completedAt IS NOT NULL THEN 'RECOVERED' ELSE 'ABANDONED' END AS status,
    CARDINALITY(ac.lineItems) AS item_count
  FROM
    nekt_raw.shopify_abandoned_checkouts ac
  WHERE
    ac.createdAt >= CURRENT_DATE - INTERVAL '30' DAY
)
SELECT
  DATE(createdAt) AS abandon_date,
  COUNT(*) AS total_abandoned,
  COUNT(CASE WHEN status = 'RECOVERED' THEN 1 END) AS recovered,
  ROUND(COUNT(CASE WHEN status = 'RECOVERED' THEN 1 END) * 100.0 / COUNT(*), 1) AS recovery_rate_pct,
  ROUND(SUM(cart_value), 2) AS total_cart_value,
  ROUND(SUM(CASE WHEN status = 'ABANDONED' THEN cart_value ELSE 0 END), 2) AS lost_revenue,
  ROUND(SUM(CASE WHEN status = 'RECOVERED' THEN cart_value ELSE 0 END), 2) AS recovered_revenue,
  ROUND(AVG(cart_value), 2) AS avg_cart_value,
  ROUND(AVG(item_count), 1) AS avg_items_per_cart
FROM
  abandoned_analysis
GROUP BY
  DATE(createdAt)
ORDER BY
  abandon_date DESC