← back to Watches

api/search-simple.js

200 lines

/**
 * Simplified Search API for Omega Watches
 * Using SQLite FTS5 for fast, deep search
 */

import express from 'express';
import sqlite3 from 'sqlite3';
import { open } from 'sqlite';
import path from 'path';
import { fileURLToPath } from 'url';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

const router = express.Router();

// Open database connection
let db;
(async () => {
  db = await open({
    filename: path.join(__dirname, '..', 'database', 'watches-search.db'),
    driver: sqlite3.Database
  });
  console.log('✅ Search database connected');
})();

/**
 * Main search endpoint
 * GET /api/search/watches?q=moonwatch
 */
router.get('/watches', async (req, res) => {
  try {
    const { q = '', limit = 50, offset = 0 } = req.query;
    
    if (!q) {
      return res.status(400).json({ error: 'Query parameter q is required' });
    }
    
    // Search using FTS5
    const results = await db.all(`
      SELECT 
        w.*,
        highlight(watches_fts, 1, '<mark>', '</mark>') as highlighted_name,
        snippet(watches_fts, -1, '<b>', '</b>', '...', 64) as snippet
      FROM watches w
      JOIN watches_fts f ON f.id = w.id
      WHERE watches_fts MATCH ?
      ORDER BY rank
      LIMIT ? OFFSET ?
    `, [q, parseInt(limit), parseInt(offset)]);
    
    // Get total count
    const { total } = await db.get(`
      SELECT COUNT(*) as total
      FROM watches_fts
      WHERE watches_fts MATCH ?
    `, [q]);
    
    res.json({
      success: true,
      query: q,
      total,
      results,
      limit: parseInt(limit),
      offset: parseInt(offset)
    });
    
  } catch (error) {
    console.error('Search error:', error);
    res.status(500).json({ 
      error: 'Search failed', 
      message: error.message 
    });
  }
});

/**
 * Search by collection
 * GET /api/search/collection/Speedmaster
 */
router.get('/collection/:name', async (req, res) => {
  try {
    const { name } = req.params;
    
    const results = await db.all(`
      SELECT * FROM watches
      WHERE collection = ?
      ORDER BY year_introduced DESC
    `, [name]);
    
    res.json({
      success: true,
      collection: name,
      total: results.length,
      results
    });
    
  } catch (error) {
    console.error('Collection search error:', error);
    res.status(500).json({ 
      error: 'Collection search failed', 
      message: error.message 
    });
  }
});

/**
 * Search by price range
 * GET /api/search/price?min=1000&max=10000
 */
router.get('/price', async (req, res) => {
  try {
    const { min = 0, max = 999999 } = req.query;
    
    const results = await db.all(`
      SELECT * FROM watches
      WHERE avg_price BETWEEN ? AND ?
      ORDER BY avg_price ASC
    `, [parseFloat(min), parseFloat(max)]);
    
    res.json({
      success: true,
      priceRange: { min: parseFloat(min), max: parseFloat(max) },
      total: results.length,
      results
    });
    
  } catch (error) {
    console.error('Price search error:', error);
    res.status(500).json({ 
      error: 'Price search failed', 
      message: error.message 
    });
  }
});

/**
 * Top appreciating watches
 * GET /api/search/appreciation?limit=10
 */
router.get('/appreciation', async (req, res) => {
  try {
    const { limit = 10 } = req.query;
    
    const results = await db.all(`
      SELECT * FROM watches
      WHERE appreciation_rate > 0
      ORDER BY appreciation_rate DESC
      LIMIT ?
    `, [parseInt(limit)]);
    
    res.json({
      success: true,
      total: results.length,
      results
    });
    
  } catch (error) {
    console.error('Appreciation search error:', error);
    res.status(500).json({ 
      error: 'Appreciation search failed', 
      message: error.message 
    });
  }
});

/**
 * Search suggestions
 * GET /api/search/suggestions?q=speed
 */
router.get('/suggestions', async (req, res) => {
  try {
    const { q } = req.query;
    
    if (!q || q.length < 2) {
      return res.json({ suggestions: [] });
    }
    
    const suggestions = await db.all(`
      SELECT DISTINCT name, collection
      FROM watches
      WHERE name LIKE ? OR collection LIKE ?
      LIMIT 10
    `, [`%${q}%`, `%${q}%`]);
    
    res.json({
      success: true,
      suggestions
    });
    
  } catch (error) {
    console.error('Suggestions error:', error);
    res.status(500).json({ 
      error: 'Suggestions failed', 
      message: error.message 
    });
  }
});

export default router;