Build Full-Text Search with PostgreSQL

// src/search/pg-search.ts — Full-text search with PostgreSQL tsvector
import { pool } from "../db";
import { Redis } from "ioredis";

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

interface SearchResult {
  id: string;
  title: string;
  description: string;
  category: string;
  price: number;
  imageUrl: string;
  rank: number;
  headline: string;           // highlighted search matches
}

interface SearchResponse {
  results: SearchResult[];
  total: number;
  facets: {
    categories: Array<{ name: string; count: number }>;
    priceRanges: Array<{ range: string; count: number }>;
  };
  query: string;
  took: number;
  suggestions: string[];
}

// Main search function
export async function search(
  query: string,
  options?: {
    category?: string;
    minPrice?: number;
    maxPrice?: number;
    sort?: "relevance" | "price_asc" | "price_desc" | "newest";
    page?: number;
    limit?: number;
  }
): Promise<SearchResponse> {
  const startTime = Date.now();
  const page = options?.page || 1;
  const limit = options?.limit || 20;
  const offset = (page - 1) * limit;

  // Check cache for common queries
  const cacheKey = `search:${JSON.stringify({ query, ...options })}`;
  const cached = await redis.get(cacheKey);
  if (cached) return { ...JSON.parse(cached), took: 0 };

  // Build the search query with ts_query
  // Handles: multi-word queries, prefix matching, and basic typo tolerance
  const tsQuery = buildTsQuery(query);

  // Filters
  const conditions: string[] = ["search_vector @@ to_tsquery('english', $1)"];
  const params: any[] = [tsQuery];
  let paramIndex = 2;

  if (options?.category) {
    conditions.push(`category = $${paramIndex++}`);
    params.push(options.category);
  }
  if (options?.minPrice !== undefined) {
    conditions.push(`price >= $${paramIndex++}`);
    params.push(options.minPrice);
  }
  if (options?.maxPrice !== undefined) {
    conditions.push(`price <= $${paramIndex++}`);
    params.push(options.maxPrice);
  }

  // Sort
  let orderBy = "ts_rank_cd(search_vector, to_tsquery('english', $1)) DESC";
  if (options?.sort === "price_asc") orderBy = "price ASC";
  if (options?.sort === "price_desc") orderBy = "price DESC";
  if (options?.sort === "newest") orderBy = "created_at DESC";

  const whereClause = conditions.join(" AND ");

  // Parallel: results + count + facets
  const [resultsQuery, countQuery, facetsQuery] = await Promise.all([
    // Results with highlighted excerpts
    pool.query(
      `SELECT id, title, description, category, price, image_url,
              ts_rank_cd(search_vector, to_tsquery('english', $1)) as rank,
              ts_headline('english', title || ' ' || description, to_tsquery('english', $1),
                'StartSel=<mark>, StopSel=</mark>, MaxWords=30, MinWords=15') as headline
       FROM products
       WHERE ${whereClause} AND active = true
       ORDER BY ${orderBy}
       LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`,
      [...params, limit, offset]
    ),

    // Total count
    pool.query(
      `SELECT COUNT(*) as total FROM products WHERE ${whereClause} AND active = true`,
      params
    ),

    // Facets (categories + price ranges)
    pool.query(
      `SELECT
         category, COUNT(*) as cat_count,
         CASE
           WHEN price < 25 THEN 'Under $25'
           WHEN price < 50 THEN '$25–$50'
           WHEN price < 100 THEN '$50–$100'
           WHEN price < 250 THEN '$100–$250'
           ELSE '$250+'
         END as price_range
       FROM products
       WHERE search_vector @@ to_tsquery('english', $1) AND active = true
       GROUP BY category, price_range`,
      [tsQuery]
    ),
  ]);

  // Aggregate facets
  const categoryFacets = new Map<string, number>();
  const priceFacets = new Map<string, number>();
  for (const row of facetsQuery.rows) {
    categoryFacets.set(row.category, (categoryFacets.get(row.category) || 0) + parseInt(row.cat_count));
    priceFacets.set(row.price_range, (priceFacets.get(row.price_range) || 0) + parseInt(row.cat_count));
  }

  // Get suggestions if few results
  let suggestions: string[] = [];
  if (resultsQuery.rows.length < 3) {
    suggestions = await getSuggestions(query);
  }

  const response: SearchResponse = {
    results: resultsQuery.rows.map((r) => ({
      id: r.id,
      title: r.title,
      description: r.description,
      category: r.category,
      price: parseFloat(r.price),
      imageUrl: r.image_url,
      rank: parseFloat(r.rank),
      headline: r.headline,
    })),
    total: parseInt(countQuery.rows[0].total),
    facets: {
      categories: Array.from(categoryFacets.entries())
        .map(([name, count]) => ({ name, count }))
        .sort((a, b) => b.count - a.count),
      priceRanges: Array.from(priceFacets.entries())
        .map(([range, count]) => ({ range, count }))
        .sort((a, b) => a.range.localeCompare(b.range)),
    },
    query,
    took: Date.now() - startTime,
    suggestions,
  };

  // Cache for 5 minutes
  await redis.setex(cacheKey, 300, JSON.stringify(response));

  // Track search analytics
  await trackSearch(query, response.total);

  return response;
}

// Build ts_query with prefix matching and OR fallback
function buildTsQuery(query: string): string {
  const words = query.trim().split(/\s+/).filter(Boolean);
  if (words.length === 0) return "";

  // Try AND first (all words must match), with prefix on last word (autocomplete)
  const andQuery = words.map((w, i) =>
    i === words.length - 1 ? `${w}:*` : w
  ).join(" & ");

  return andQuery;
}

// Autocomplete suggestions
export async function autocomplete(prefix: string): Promise<string[]> {
  if (prefix.length < 2) return [];

  const cacheKey = `autocomplete:${prefix.toLowerCase()}`;
  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);

  const { rows } = await pool.query(
    `SELECT DISTINCT title FROM products
     WHERE search_vector @@ to_tsquery('english', $1 || ':*')
       AND active = true
     ORDER BY title
     LIMIT 8`,
    [prefix.toLowerCase()]
  );

  const suggestions = rows.map((r) => r.title);
  await redis.setex(cacheKey, 600, JSON.stringify(suggestions));
  return suggestions;
}

// Did-you-mean suggestions using trigram similarity
async function getSuggestions(query: string): Promise<string[]> {
  const { rows } = await pool.query(
    `SELECT DISTINCT title, similarity(title, $1) as sim
     FROM products
     WHERE similarity(title, $1) > 0.2 AND active = true
     ORDER BY sim DESC
     LIMIT 3`,
    [query]
  );
  return rows.map((r) => r.title);
}

// Search analytics
async function trackSearch(query: string, resultCount: number): Promise<void> {
  await pool.query(
    `INSERT INTO search_analytics (query, result_count, searched_at)
     VALUES ($1, $2, NOW())`,
    [query.toLowerCase(), resultCount]
  );
}