← back to Watches

api/search-api.js

456 lines

/**
 * Comprehensive Search API for Omega Watches
 * Powered by SQLite FTS5 for lightning-fast deep search
 */

import express from 'express';
import WatchSearchDatabase from '../database/sqlite-fts5-setup.js';

const router = express.Router();
const searchDB = new WatchSearchDatabase();

// Initialize database connection
await searchDB.initialize();

/**
 * Main search endpoint - supports complex queries
 * GET /api/search/watches?q=moonwatch&collection=Speedmaster&minPrice=5000
 */
router.get('/watches', async (req, res) => {
  try {
    const {
      q = '',
      collection,
      minPrice,
      maxPrice,
      yearFrom,
      yearTo,
      sortBy = 'rank',
      limit = 50,
      offset = 0,
      complications,
      features,
      investmentGrade
    } = req.query;

    if (!q && !collection && !minPrice && !maxPrice && !yearFrom && !yearTo) {
      return res.status(400).json({ 
        error: 'Please provide at least one search parameter' 
      });
    }

    const filters = {
      collection,
      minPrice: minPrice ? parseFloat(minPrice) : undefined,
      maxPrice: maxPrice ? parseFloat(maxPrice) : undefined,
      yearFrom: yearFrom ? parseInt(yearFrom) : undefined,
      yearTo: yearTo ? parseInt(yearTo) : undefined
    };

    let results;
    
    if (q) {
      // Full-text search with filters
      results = await searchDB.search(q, {
        limit: parseInt(limit),
        offset: parseInt(offset),
        sortBy,
        filters
      });
    } else {
      // Advanced criteria search
      results = await searchDB.advancedSearch({
        complications,
        features,
        investmentGrade: investmentGrade ? investmentGrade.split(',') : undefined,
        ...filters
      });
    }

    // Add performance metrics
    res.json({
      success: true,
      ...results,
      searchTime: `${Date.now() - req.startTime}ms`
    });

  } catch (error) {
    console.error('Search error:', error);
    res.status(500).json({ 
      error: 'Search failed',
      message: error.message 
    });
  }
});

/**
 * Autocomplete/suggestions endpoint
 * 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 searchDB.searchSuggestions(q);
    
    res.json({
      success: true,
      suggestions,
      query: q
    });

  } catch (error) {
    console.error('Suggestions error:', error);
    res.status(500).json({ 
      error: 'Failed to get suggestions',
      message: error.message 
    });
  }
});

/**
 * Advanced multi-field search
 * POST /api/search/advanced
 */
router.post('/advanced', async (req, res) => {
  try {
    const criteria = req.body;
    
    const results = await searchDB.advancedSearch(criteria);
    
    res.json({
      success: true,
      results,
      criteria,
      total: results.length
    });

  } catch (error) {
    console.error('Advanced search error:', error);
    res.status(500).json({ 
      error: 'Advanced search failed',
      message: error.message 
    });
  }
});

/**
 * Search by specific fields
 * GET /api/search/by-field/:field/:value
 */
router.get('/by-field/:field/:value', async (req, res) => {
  try {
    const { field, value } = req.params;
    
    const allowedFields = [
      'collection', 'series', 'reference', 'caliber', 
      'dial_color', 'case_material', 'investment_grade'
    ];
    
    if (!allowedFields.includes(field)) {
      return res.status(400).json({ 
        error: `Invalid field. Allowed: ${allowedFields.join(', ')}` 
      });
    }

    const query = `${field}:"${value}"`;
    const results = await searchDB.search(query, { limit: 100 });
    
    res.json({
      success: true,
      field,
      value,
      ...results
    });

  } catch (error) {
    console.error('Field search error:', error);
    res.status(500).json({ 
      error: 'Field search failed',
      message: error.message 
    });
  }
});

/**
 * Search within price history
 * GET /api/search/price-range?min=1000&max=10000&year=1969
 */
router.get('/price-range', async (req, res) => {
  try {
    const { min, max, year } = req.query;
    
    let sql = `
      SELECT DISTINCT w.*, ph.year as price_year, ph.price
      FROM watches w
      JOIN price_history ph ON w.id = ph.watch_id
      WHERE 1=1
    `;
    const params = [];
    
    if (min) {
      sql += ' AND ph.price >= ?';
      params.push(parseFloat(min));
    }
    if (max) {
      sql += ' AND ph.price <= ?';
      params.push(parseFloat(max));
    }
    if (year) {
      sql += ' AND ph.year = ?';
      params.push(parseInt(year));
    }
    
    sql += ' ORDER BY ph.price ASC';
    
    const results = await searchDB.db.all(sql, params);
    
    res.json({
      success: true,
      results,
      filters: { min, max, year },
      total: results.length
    });

  } catch (error) {
    console.error('Price range search error:', error);
    res.status(500).json({ 
      error: 'Price range search failed',
      message: error.message 
    });
  }
});

/**
 * Search for investment opportunities
 * GET /api/search/investment?minAppreciation=5&grade=A
 */
router.get('/investment', async (req, res) => {
  try {
    const { minAppreciation, grade, maxPrice } = req.query;
    
    let sql = `
      SELECT * FROM watches
      WHERE 1=1
    `;
    const params = [];
    
    if (minAppreciation) {
      sql += ' AND appreciation_rate >= ?';
      params.push(parseFloat(minAppreciation));
    }
    if (grade) {
      const grades = grade.split(',');
      sql += ` AND investment_grade IN (${grades.map(() => '?').join(',')})`;
      params.push(...grades);
    }
    if (maxPrice) {
      sql += ' AND avg_price <= ?';
      params.push(parseFloat(maxPrice));
    }
    
    sql += ' ORDER BY appreciation_rate DESC, investment_grade ASC';
    
    const results = await searchDB.db.all(sql, params);
    
    res.json({
      success: true,
      results,
      filters: { minAppreciation, grade, maxPrice },
      total: results.length
    });

  } catch (error) {
    console.error('Investment search error:', error);
    res.status(500).json({ 
      error: 'Investment search failed',
      message: error.message 
    });
  }
});

/**
 * Search by complications
 * GET /api/search/complications?types=Chronograph,Moonphase
 */
router.get('/complications', async (req, res) => {
  try {
    const { types } = req.query;
    
    if (!types) {
      return res.status(400).json({ error: 'Please provide complication types' });
    }
    
    const typeList = types.split(',');
    const conditions = typeList.map(() => 'complications LIKE ?');
    const params = typeList.map(type => `%${type.trim()}%`);
    
    const sql = `
      SELECT * FROM watches
      WHERE ${conditions.join(' OR ')}
      ORDER BY name
    `;
    
    const results = await searchDB.db.all(sql, params);
    
    res.json({
      success: true,
      results,
      complications: typeList,
      total: results.length
    });

  } catch (error) {
    console.error('Complications search error:', error);
    res.status(500).json({ 
      error: 'Complications search failed',
      message: error.message 
    });
  }
});

/**
 * Search by special features
 * GET /api/search/features?types=Co-Axial,Master Chronometer
 */
router.get('/features', async (req, res) => {
  try {
    const { types } = req.query;
    
    if (!types) {
      return res.status(400).json({ error: 'Please provide feature types' });
    }
    
    const typeList = types.split(',');
    const conditions = typeList.map(() => 'special_features LIKE ?');
    const params = typeList.map(type => `%${type.trim()}%`);
    
    const sql = `
      SELECT * FROM watches
      WHERE ${conditions.join(' OR ')}
      ORDER BY name
    `;
    
    const results = await searchDB.db.all(sql, params);
    
    res.json({
      success: true,
      results,
      features: typeList,
      total: results.length
    });

  } catch (error) {
    console.error('Features search error:', error);
    res.status(500).json({ 
      error: 'Features search failed',
      message: error.message 
    });
  }
});

/**
 * Popular searches analytics
 * GET /api/search/popular
 */
router.get('/popular', async (req, res) => {
  try {
    const { limit = 10 } = req.query;
    
    const popular = await searchDB.getPopularSearches(parseInt(limit));
    
    res.json({
      success: true,
      popular,
      period: 'last_7_days'
    });

  } catch (error) {
    console.error('Popular searches error:', error);
    res.status(500).json({ 
      error: 'Failed to get popular searches',
      message: error.message 
    });
  }
});

/**
 * Search collections
 * GET /api/search/collections
 */
router.get('/collections', async (req, res) => {
  try {
    const sql = `
      SELECT 
        collection,
        COUNT(*) as model_count,
        AVG(avg_price) as avg_collection_price,
        AVG(appreciation_rate) as avg_appreciation,
        MIN(year_introduced) as earliest_model,
        MAX(year_introduced) as latest_model
      FROM watches
      WHERE collection IS NOT NULL
      GROUP BY collection
      ORDER BY model_count DESC
    `;
    
    const collections = await searchDB.db.all(sql);
    
    res.json({
      success: true,
      collections,
      total: collections.length
    });

  } catch (error) {
    console.error('Collections search error:', error);
    res.status(500).json({ 
      error: 'Collections search failed',
      message: error.message 
    });
  }
});

/**
 * Fuzzy search with typo tolerance
 * GET /api/search/fuzzy?q=spemaster (will find Speedmaster)
 */
router.get('/fuzzy', async (req, res) => {
  try {
    const { q } = req.query;
    
    if (!q || q.length < 3) {
      return res.status(400).json({ error: 'Query too short for fuzzy search' });
    }
    
    // Use FTS5's built-in fuzzy matching
    const fuzzyQuery = q.split('').join('*') + '*';
    const results = await searchDB.search(fuzzyQuery, { limit: 20 });
    
    res.json({
      success: true,
      originalQuery: q,
      fuzzyQuery,
      ...results
    });

  } catch (error) {
    console.error('Fuzzy search error:', error);
    res.status(500).json({ 
      error: 'Fuzzy search failed',
      message: error.message 
    });
  }
});

// Middleware to track search timing
router.use((req, res, next) => {
  req.startTime = Date.now();
  next();
});

export default router;