nuxt-app/server/service/search.ts

import { dbGlobal } from "drizzle-pkg/lib/db";
import { items, itemTags, tags } from "drizzle-pkg/lib/schema/collection";
import { eq, inArray, sql } from "drizzle-orm";

export async function fullTextSearch(
  query: string,
  userId: number,
  limit = 20
) {
  // Sanitize input
  const sanitized = query.replace(/['"*^~]/g, " ").trim();
  if (!sanitized) return [];

  // Build FTS5 query with prefix matching
  const terms = sanitized.split(/\s+/).filter(Boolean);
  const ftsQuery = terms.map((t) => `"${t}"*`).join(" AND ");

  const rows = await dbGlobal.all<{
    id: number;
    title: string;
    description: string | null;
    type: string;
    highlight: string;
  }>(
    sql`
      SELECT
        i.id,
        i.title,
        i.description,
        i.type,
        i.rating,
        i.starred,
        i.created_at,
        i.cover_url,
        i.source_host,
        i.url,
        snippet(items_fts, 1, '<mark>', '</mark>', '...', 32) AS highlight
      FROM items_fts
      JOIN items i ON i.id = items_fts.rowid
      WHERE items_fts MATCH ${ftsQuery}
        AND i.user_id = ${userId}
        AND i.is_archived = 0
      ORDER BY rank
      LIMIT ${limit}
    `
  );

  // Load tags for results
  const itemIds = rows.map((r) => r.id);
  let tagMap: Record<number, { id: number; name: string; color: string }[]> = {};
  if (itemIds.length > 0) {
    const tagRows = await dbGlobal
      .select({ itemId: itemTags.itemId, id: tags.id, name: tags.name, color: tags.color })
      .from(itemTags)
      .innerJoin(tags, eq(itemTags.tagId, tags.id))
      .where(inArray(itemTags.itemId, itemIds));
    for (const t of tagRows) {
      if (!tagMap[t.itemId]) tagMap[t.itemId] = [];
      tagMap[t.itemId].push({ id: t.id, name: t.name, color: t.color });
    }
  }

  return rows.map((r) => ({ ...r, tags: tagMap[r.id] || [] }));
}

export async function suggestSearch(query: string, userId: number, limit = 5) {
  const sanitized = query.replace(/['"*^~]/g, " ").trim();
  if (!sanitized) return [];

  const terms = sanitized.split(/\s+/).filter(Boolean);
  const ftsQuery = terms.map((t) => `"${t}"*`).join(" AND ");

  const rows = await dbGlobal.all<{ suggestion: string }>(
    sql`
      SELECT DISTINCT i.title AS suggestion
      FROM items_fts
      JOIN items i ON i.id = items_fts.rowid
      WHERE items_fts MATCH ${ftsQuery}
        AND i.user_id = ${userId}
      ORDER BY rank
      LIMIT ${limit}
    `
  );

  return rows.map((r) => r.suggestion);
}