Build a Product Recommendation Engine

// src/recommendations/engine.ts — Product recommendations without ML infrastructure
import { pool } from "../db";
import { Redis } from "ioredis";

const redis = new Redis(process.env.REDIS_URL!);

interface Recommendation {
  productId: string;
  name: string;
  price: number;
  imageUrl: string;
  score: number;
  reason: string;              // "Customers also bought", "Similar to items you viewed"
}

// "Customers who bought X also bought Y" (co-purchase analysis)
export async function getAlsoBought(productId: string, limit: number = 8): Promise<Recommendation[]> {
  const cacheKey = `recs:alsobought:${productId}`;
  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);

  // Find products frequently purchased together
  const { rows } = await pool.query(
    `SELECT p.id, p.name, p.price, p.image_url,
            COUNT(*) as co_purchase_count,
            COUNT(*)::float / (SELECT COUNT(DISTINCT order_id) FROM order_items WHERE product_id = $1) as score
     FROM order_items oi1
     JOIN order_items oi2 ON oi1.order_id = oi2.order_id AND oi1.product_id != oi2.product_id
     JOIN products p ON oi2.product_id = p.id
     WHERE oi1.product_id = $1 AND p.active = true
     GROUP BY p.id, p.name, p.price, p.image_url
     HAVING COUNT(*) >= 3
     ORDER BY co_purchase_count DESC
     LIMIT $2`,
    [productId, limit]
  );

  const recs = rows.map((r) => ({
    productId: r.id, name: r.name, price: parseFloat(r.price),
    imageUrl: r.image_url, score: parseFloat(r.score),
    reason: "Customers also bought",
  }));

  await redis.setex(cacheKey, 3600, JSON.stringify(recs)); // cache 1 hour
  return recs;
}

// "Recommended for you" (based on viewing/purchase history)
export async function getPersonalized(userId: string, limit: number = 12): Promise<Recommendation[]> {
  const cacheKey = `recs:personal:${userId}`;
  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);

  // Get user's recent activity
  const { rows: recentViews } = await pool.query(
    `SELECT product_id FROM product_views
     WHERE user_id = $1
     ORDER BY viewed_at DESC LIMIT 20`,
    [userId]
  );

  const { rows: recentPurchases } = await pool.query(
    `SELECT oi.product_id FROM order_items oi
     JOIN orders o ON oi.order_id = o.id
     WHERE o.customer_id = $1
     ORDER BY o.created_at DESC LIMIT 20`,
    [userId]
  );

  const viewedIds = recentViews.map((r) => r.product_id);
  const purchasedIds = recentPurchases.map((r) => r.product_id);
  const allInteracted = [...new Set([...viewedIds, ...purchasedIds])];

  if (allInteracted.length === 0) {
    return getTrending(limit);
  }

  // Find products similar to what user interacted with (category + tag overlap)
  const { rows } = await pool.query(
    `SELECT p.id, p.name, p.price, p.image_url,
            COUNT(DISTINCT shared.category) + COUNT(DISTINCT shared.tag) as similarity_score
     FROM products p
     JOIN (
       SELECT DISTINCT category FROM products WHERE id = ANY($1)
       UNION
       SELECT DISTINCT unnest(tags) as category FROM products WHERE id = ANY($1)
     ) shared ON (p.category = shared.category OR shared.category = ANY(p.tags))
     WHERE p.id != ALL($1) AND p.active = true AND p.id != ALL($2)
     GROUP BY p.id, p.name, p.price, p.image_url
     ORDER BY similarity_score DESC, p.rating DESC
     LIMIT $3`,
    [allInteracted, purchasedIds, limit]
  );

  // Also add co-purchase recommendations from purchased items
  const coPurchase: Recommendation[] = [];
  for (const pid of purchasedIds.slice(0, 3)) {
    const recs = await getAlsoBought(pid, 3);
    coPurchase.push(...recs.filter((r) => !purchasedIds.includes(r.productId)));
  }

  // Merge and deduplicate
  const seen = new Set<string>();
  const recs: Recommendation[] = [];

  for (const r of rows) {
    if (!seen.has(r.id)) {
      seen.add(r.id);
      recs.push({
        productId: r.id, name: r.name, price: parseFloat(r.price),
        imageUrl: r.image_url, score: parseInt(r.similarity_score),
        reason: "Recommended for you",
      });
    }
  }

  for (const r of coPurchase) {
    if (!seen.has(r.productId) && recs.length < limit) {
      seen.add(r.productId);
      recs.push(r);
    }
  }

  await redis.setex(cacheKey, 1800, JSON.stringify(recs.slice(0, limit)));
  return recs.slice(0, limit);
}

// "Similar products" (content-based)
export async function getSimilar(productId: string, limit: number = 6): Promise<Recommendation[]> {
  const cacheKey = `recs:similar:${productId}`;
  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);

  const { rows: [product] } = await pool.query(
    "SELECT category, tags, price FROM products WHERE id = $1", [productId]
  );
  if (!product) return [];

  // Find products with same category + similar tags + similar price range
  const priceMin = product.price * 0.5;
  const priceMax = product.price * 2.0;

  const { rows } = await pool.query(
    `SELECT p.id, p.name, p.price, p.image_url,
            (CASE WHEN p.category = $2 THEN 3 ELSE 0 END) +
            (SELECT COUNT(*) FROM unnest(p.tags) t WHERE t = ANY($3)) +
            (CASE WHEN p.price BETWEEN $4 AND $5 THEN 1 ELSE 0 END) as score
     FROM products p
     WHERE p.id != $1 AND p.active = true
     ORDER BY score DESC, p.rating DESC
     LIMIT $6`,
    [productId, product.category, product.tags, priceMin, priceMax, limit]
  );

  const recs = rows.map((r) => ({
    productId: r.id, name: r.name, price: parseFloat(r.price),
    imageUrl: r.image_url, score: parseInt(r.score),
    reason: "Similar products",
  }));

  await redis.setex(cacheKey, 3600, JSON.stringify(recs));
  return recs;
}

// Trending products (fallback for new users)
async function getTrending(limit: number): Promise<Recommendation[]> {
  const cached = await redis.get("recs:trending");
  if (cached) return JSON.parse(cached);

  const { rows } = await pool.query(
    `SELECT p.id, p.name, p.price, p.image_url, COUNT(oi.id) as order_count
     FROM products p
     JOIN order_items oi ON p.id = oi.product_id
     JOIN orders o ON oi.order_id = o.id
     WHERE o.created_at > NOW() - interval '7 days' AND p.active = true
     GROUP BY p.id, p.name, p.price, p.image_url
     ORDER BY order_count DESC
     LIMIT $1`,
    [limit]
  );

  const recs = rows.map((r) => ({
    productId: r.id, name: r.name, price: parseFloat(r.price),
    imageUrl: r.image_url, score: parseInt(r.order_count),
    reason: "Trending this week",
  }));

  await redis.setex("recs:trending", 1800, JSON.stringify(recs));
  return recs;
}

// Track product view (for personalization)
export async function trackView(userId: string, productId: string): Promise<void> {
  await pool.query(
    "INSERT INTO product_views (user_id, product_id, viewed_at) VALUES ($1, $2, NOW())",
    [userId, productId]
  );
  await redis.del(`recs:personal:${userId}`);
}