CREATE COLLECTION products;
CREATE SEARCH INDEX ON products FIELDS title, description ANALYZER 'english' FUZZY true;
-- One vector index per embedding column on the same collection.
CREATE VECTOR INDEX idx_text  ON products (text_embedding)  METRIC cosine DIM 384;
CREATE VECTOR INDEX idx_image ON products (image_embedding) METRIC cosine DIM 512;

-- Hybrid search: BM25 keyword + vector semantic, fused with RRF
SELECT
  id,
  title,
  price,
  brand,
  rrf_score(
    vector_distance(text_embedding, $query_vec),
    bm25_score(description, $query_text)
  ) AS relevance
FROM products
WHERE in_stock     = true
  AND price        BETWEEN $min_price AND $max_price
  AND category_id  = ANY($category_ids)
ORDER BY relevance DESC
LIMIT 20;

-- Autocomplete: fuzzy title match, ranked by popularity
SELECT DISTINCT id, title
FROM products
WHERE text_match(title, $prefix, { fuzzy: true, distance: 1 })
ORDER BY popularity_score DESC
LIMIT 8;

-- Facet counts: products per brand in the current keyword result set
SELECT brand, count(*) AS count
FROM products
WHERE text_match(description, $query_text)
  AND in_stock = true
GROUP BY brand
ORDER BY count DESC
LIMIT 15;

-- "Shop similar items" — products whose image embedding is closest to the
-- viewed one, restricted to the same in-stock category.
SELECT id, title, price, image_url,
       image_embedding <=> $viewed_vec AS visual_distance
FROM products
WHERE category_id = $category_id
  AND in_stock = true
  AND id <> $viewed_product_id
ORDER BY image_embedding <=> $viewed_vec
LIMIT 12;

-- A batch job recomputes co-purchase counts and (re)writes the edge with the
-- current weight — DELETE then INSERT, since GRAPH INSERT EDGE is insert-only.
GRAPH DELETE EDGE IN 'products' FROM $product_a TO $product_b TYPE 'co_purchased';
GRAPH INSERT EDGE IN 'products' FROM $product_a TO $product_b TYPE 'co_purchased'
  PROPERTIES { weight: $copurchase_count };

-- "Customers also bought": direct co-purchase neighbours of a product,
-- ranked by co-purchase weight. Graph nodes are document rows, so their
-- catalog fields are available in RETURN.
MATCH (seed:Product)-[e:co_purchased]->(rec:Product)
WHERE seed.id = $product_id
  AND rec.in_stock = true
RETURN rec.id, rec.title, rec.price, e.weight AS affinity
ORDER BY e.weight DESC
LIMIT 8;

-- A shopper viewing their wishlist subscribes to back-in-stock events
LIVE SELECT id, title FROM products
WHERE id = ANY($wishlist_ids)
  AND in_stock = true;

-- When a back-ordered item is restocked, every subscriber gets the row
-- delivered immediately. Cancel with: CANCEL LIVE SELECT <subscription_id>;

CREATE COLLECTION orders (
  id          TEXT PRIMARY KEY,
  placed_at   TIMESTAMP TIME_KEY,
  status      TEXT
) WITH (engine='columnar');

-- Revenue by category, last 30 days
SELECT
  c.name                       AS category,
  count(DISTINCT o.id)         AS orders,
  sum(oi.quantity * oi.price)  AS revenue,
  avg(oi.quantity * oi.price)  AS avg_order_value
FROM orders o
JOIN order_items oi  ON oi.order_id    = o.id
JOIN products    p   ON p.id           = oi.product_id
JOIN categories  c   ON c.id           = p.category_id
WHERE o.placed_at >= now() - INTERVAL '30 days'
  AND o.status    = 'completed'
GROUP BY c.name
ORDER BY revenue DESC;

-- Conversion funnel: views → add-to-cart → purchase
SELECT
  time_bucket('1d', e.ts) AS day,
  count(CASE WHEN e.event = 'product_view' THEN 1 END) AS views,
  count(CASE WHEN e.event = 'add_to_cart'  THEN 1 END) AS add_to_cart,
  count(CASE WHEN e.event = 'purchase'     THEN 1 END) AS purchases,
  round(
    count(CASE WHEN e.event = 'purchase' THEN 1 END)::numeric
    / nullif(count(CASE WHEN e.event = 'product_view' THEN 1 END), 0) * 100,
    2
  ) AS conversion_pct
FROM events e
WHERE e.ts >= now() - INTERVAL '14 days'
GROUP BY day
ORDER BY day;