D1 FTS5 全文搜索：50 万行数据仅需 47ms —— 无需外部服务

-- 创建 FTS5 虚拟表
CREATE VIRTUAL TABLE products_fts USING fts5(
  name,
  description,
  category,
  content='products',
  content_rowid='id'
);

-- 从 products 表填充数据
INSERT INTO products_fts(rowid, name, description, category)
SELECT id, name, description, category FROM products;

-- 使用触发器保持同步
CREATE TRIGGER products_ai AFTER INSERT ON products BEGIN
  INSERT INTO products_fts(rowid, name, description, category)
  VALUES (new.id, new.name, new.description, new.category);
END;

CREATE TRIGGER products_ad AFTER DELETE ON products BEGIN
  INSERT INTO products_fts.products_fts VALUES('delete', old.id, old.name, old.description, old.category);
END;

CREATE TRIGGER products_au AFTER UPDATE ON products BEGIN
  INSERT INTO products_fts.products_fts VALUES('delete', old.id, old.name, old.description, old.category);
  INSERT INTO products_fts(rowid, name, description, category)
  VALUES (new.id, new.name, new.description, new.category);
END;

-- products 表（你的实际数据表）
CREATE TABLE IF NOT EXISTS products (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  category TEXT,
  price INTEGER,
  created_at INTEGER
);

-- FTS5 虚拟表
CREATE VIRTUAL TABLE products_fts USING fts5(
  name,
  description,
  category,
  content='products',
  content_rowid='id',
  tokenize='porter unicode61'
);

-- migrations/0003_fts5_search.sql
CREATE VIRTUAL TABLE IF NOT EXISTS products_fts USING fts5(
  name, description, category,
  content='products', content_rowid='id',
  tokenize='porter unicode61'
);

CREATE TRIGGER IF NOT EXISTS products_fts_insert
AFTER INSERT ON products BEGIN
  INSERT INTO products_fts(rowid, name, description, category)
  VALUES (new.id, new.name, new.description, new.category);
END;

SELECT
  p.*,
  bm25(products_fts) AS relevance_score
FROM products_fts
JOIN products p ON products_fts.rowid = p.id
WHERE products_fts MATCH :query
ORDER BY relevance_score
LIMIT 20;

// TanStack Ship 搜索服务
export async function autocomplete(
  env: Env,
  prefix: string,
  limit = 8
): Promise<Product[]> {
  // FTS5 前缀匹配："rou*" 会匹配 "router"、"routing"
  const query = `${prefix}*`;

  const results = await env.DB.prepare(`
    SELECT p.id, p.name, p.category, p.price,
           bm25(products_fts) as relevance
    FROM products_fts fts
    JOIN products p ON fts.rowid = p.id
    WHERE products_fts MATCH ?
    ORDER BY relevance
    LIMIT ?
  `).bind(query, limit).all();

  return results.results as Product[];
}

export async function searchWithFilters(
  env: Env,
  options: {
    query: string;
    category?: string;
    minPrice?: number;
    maxPrice?: number;
    limit?: number;
    offset?: number;
  }
): Promise<{ results: Product[]; total: number }> {
  let sql = `
    SELECT p.*, bm25(products_fts) as relevance
    FROM products_fts fts
    JOIN products p ON fts.rowid = p.id
    WHERE products_fts MATCH ?
  `;
  const params: (string | number)[] = [`${options.query}*`];

  if (options.category) {
    sql += ` AND p.category = ?`;
    params.push(options.category);
  }
  if (options.minPrice !== undefined) {
    sql += ` AND p.price >= ?`;
    params.push(options.minPrice);
  }
  if (options.maxPrice !== undefined) {
    sql += ` AND p.price <= ?`;
    params.push(options.maxPrice);
  }

  // 获取总数
  const countSql = sql.replace('SELECT p.*, bm25(products_fts) as relevance', 'SELECT COUNT(*) as count');
  const { count } = await env.DB.prepare(countSql).bind(...params).first<{ count: number }>();

  // 添加分页
  sql += ` ORDER BY relevance LIMIT ? OFFSET ?`;
  params.push(options.limit ?? 20, options.offset ?? 0);

  const results = await env.DB.prepare(sql).bind(...params).all();
  return { results: results.results as Product[], total: count };
}

INSERT INTO products_fts(products_fts) VALUES('rebuild');

CREATE VIRTUAL TABLE products_fts USING fts5(
  name, description,
  tokenize='unicode61 remove_diacritics 2'
);

if (prefix.length < 3) {
  // 对短前缀退回使用 LIKE 查询（虽然较慢但能保证可用）
  return env.DB.prepare(`
    SELECT * FROM products
    WHERE name LIKE ? LIMIT 8
  `).bind(`%${prefix}%`).all();
}