← back to Watches
api/advanced-search.js
547 lines
/**
* Advanced Search & Sort API for Omega Watches
* Comprehensive filtering, sorting, and faceted search with SQLite FTS5
*/
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();
// Database connection
let db;
(async () => {
db = await open({
filename: path.join(__dirname, '..', 'database', 'watches-search.db'),
driver: sqlite3.Database
});
console.log('✅ Advanced search database connected');
})();
/**
* Advanced search with multiple filters and sort options
* GET /api/advanced-search?q=speedmaster&collection=Speedmaster&minPrice=1000&maxPrice=10000&sort=price_asc&facets=true
*/
router.get('/', async (req, res) => {
try {
const {
// Search parameters
q, // Full-text search query
// Filter parameters
collection, // Filter by collection
reference, // Filter by reference number
yearFrom, // Minimum year introduced
yearTo, // Maximum year introduced
minPrice, // Minimum average price
maxPrice, // Maximum average price
minAppreciation, // Minimum appreciation rate
// Sort parameters
sort = 'relevance', // Sort by: relevance, price_asc, price_desc, year_asc, year_desc, appreciation_desc, name_asc
// Pagination
limit = 50,
offset = 0,
// Options
facets = false, // Include facet counts
highlight = true, // Include highlighted matches
snippet = true // Include text snippets
} = req.query;
// Build the main query
let sql = `
SELECT DISTINCT
w.*
`;
// Add FTS5 fields if doing text search
if (q) {
sql += `,
${highlight === 'true' ? "highlight(watches_fts, 1, '<mark>', '</mark>') as highlighted_name" : "'' as highlighted_name"},
${snippet === 'true' ? "snippet(watches_fts, -1, '<b>', '</b>', '...', 64) as text_snippet" : "'' as text_snippet"},
rank as relevance_score
`;
}
sql += `
FROM watches w
`;
// Join FTS table if searching
if (q) {
sql += `
JOIN watches_fts f ON f.id = w.id
`;
}
// Build WHERE clause
const conditions = [];
const params = [];
if (q) {
conditions.push('watches_fts MATCH ?');
params.push(q);
}
if (collection) {
conditions.push('w.collection = ?');
params.push(collection);
}
if (reference) {
conditions.push('w.reference LIKE ?');
params.push(`%${reference}%`);
}
if (yearFrom) {
conditions.push('w.year_introduced >= ?');
params.push(parseInt(yearFrom));
}
if (yearTo) {
conditions.push('w.year_introduced <= ?');
params.push(parseInt(yearTo));
}
if (minPrice) {
conditions.push('w.avg_price >= ?');
params.push(parseFloat(minPrice));
}
if (maxPrice) {
conditions.push('w.avg_price <= ?');
params.push(parseFloat(maxPrice));
}
if (minAppreciation) {
conditions.push('w.appreciation_rate >= ?');
params.push(parseFloat(minAppreciation));
}
if (conditions.length > 0) {
sql += ' WHERE ' + conditions.join(' AND ');
}
// Add ORDER BY clause based on sort parameter
switch (sort) {
case 'price_asc':
sql += ' ORDER BY w.avg_price ASC NULLS LAST';
break;
case 'price_desc':
sql += ' ORDER BY w.avg_price DESC NULLS LAST';
break;
case 'year_asc':
sql += ' ORDER BY w.year_introduced ASC NULLS LAST';
break;
case 'year_desc':
sql += ' ORDER BY w.year_introduced DESC NULLS LAST';
break;
case 'appreciation_desc':
sql += ' ORDER BY w.appreciation_rate DESC NULLS LAST';
break;
case 'name_asc':
sql += ' ORDER BY w.name ASC';
break;
case 'name_desc':
sql += ' ORDER BY w.name DESC';
break;
case 'relevance':
default:
if (q) {
sql += ' ORDER BY rank'; // FTS5 relevance ranking
} else {
sql += ' ORDER BY w.name ASC';
}
}
// Add pagination
sql += ' LIMIT ? OFFSET ?';
params.push(parseInt(limit), parseInt(offset));
// Execute main query
const results = await db.all(sql, params);
// Get total count for pagination
let countSql = `
SELECT COUNT(DISTINCT w.id) as total
FROM watches w
`;
if (q) {
countSql += ' JOIN watches_fts f ON f.id = w.id';
}
if (conditions.length > 0) {
countSql += ' WHERE ' + conditions.join(' AND ');
}
const { total } = await db.get(countSql, params.slice(0, -2)); // Remove limit/offset params
// Build response
const response = {
success: true,
query: q || null,
filters: {
collection,
reference,
yearFrom: yearFrom ? parseInt(yearFrom) : null,
yearTo: yearTo ? parseInt(yearTo) : null,
minPrice: minPrice ? parseFloat(minPrice) : null,
maxPrice: maxPrice ? parseFloat(maxPrice) : null,
minAppreciation: minAppreciation ? parseFloat(minAppreciation) : null
},
sort,
total,
limit: parseInt(limit),
offset: parseInt(offset),
results
};
// Add facets if requested
if (facets === 'true') {
response.facets = await getFacets(conditions, params.slice(0, -2));
}
res.json(response);
} catch (error) {
console.error('Advanced search error:', error);
res.status(500).json({
error: 'Advanced search failed',
message: error.message
});
}
});
/**
* Search by multiple fields with OR logic
* POST /api/advanced-search/multi-field
* Body: { fields: { name: "speedmaster", description: "chronograph", collection: "racing" } }
*/
router.post('/multi-field', async (req, res) => {
try {
const { fields, matchAll = false, sort = 'relevance' } = req.body;
if (!fields || Object.keys(fields).length === 0) {
return res.status(400).json({ error: 'No search fields provided' });
}
// Build search query for each field
const searchTerms = [];
const params = [];
for (const [field, value] of Object.entries(fields)) {
if (value) {
searchTerms.push(`${field}:${value}`);
}
}
const searchQuery = matchAll
? searchTerms.join(' AND ')
: searchTerms.join(' OR ');
// Execute FTS5 search
const results = await db.all(`
SELECT
w.*,
highlight(watches_fts, -1, '<mark>', '</mark>') as highlighted,
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 100
`, [searchQuery]);
res.json({
success: true,
searchFields: fields,
matchAll,
total: results.length,
results
});
} catch (error) {
console.error('Multi-field search error:', error);
res.status(500).json({
error: 'Multi-field search failed',
message: error.message
});
}
});
/**
* Get facet counts for filtering
*/
async function getFacets(baseConditions, baseParams) {
const facets = {};
// Collection facets
let collectionSql = `
SELECT collection, COUNT(*) as count
FROM watches w
`;
if (baseConditions.length > 0) {
const nonCollectionConditions = baseConditions.filter(c => !c.includes('w.collection'));
if (nonCollectionConditions.length > 0) {
collectionSql += ' WHERE ' + nonCollectionConditions.join(' AND ');
}
}
collectionSql += ' GROUP BY collection ORDER BY count DESC';
const collectionFacets = await db.all(collectionSql,
baseParams.filter((_, i) => !baseConditions[i]?.includes('w.collection')));
facets.collections = collectionFacets;
// Price range facets
const priceRanges = [
{ label: 'Under $1,000', min: 0, max: 1000 },
{ label: '$1,000 - $5,000', min: 1000, max: 5000 },
{ label: '$5,000 - $10,000', min: 5000, max: 10000 },
{ label: '$10,000 - $25,000', min: 10000, max: 25000 },
{ label: 'Over $25,000', min: 25000, max: 999999999 }
];
const priceFacets = [];
for (const range of priceRanges) {
let priceSql = `
SELECT COUNT(*) as count
FROM watches w
WHERE w.avg_price >= ? AND w.avg_price < ?
`;
const nonPriceConditions = baseConditions.filter(c =>
!c.includes('w.avg_price') && !c.includes('w.min_price') && !c.includes('w.max_price'));
if (nonPriceConditions.length > 0) {
priceSql = `
SELECT COUNT(*) as count
FROM watches w
WHERE w.avg_price >= ? AND w.avg_price < ?
AND ${nonPriceConditions.join(' AND ')}
`;
}
const priceParams = [range.min, range.max];
const filteredBaseParams = baseParams.filter((_, i) =>
!baseConditions[i]?.includes('w.avg_price') &&
!baseConditions[i]?.includes('w.min_price') &&
!baseConditions[i]?.includes('w.max_price'));
const { count } = await db.get(priceSql, [...priceParams, ...filteredBaseParams]);
priceFacets.push({ ...range, count });
}
facets.priceRanges = priceFacets;
// Year facets
const yearFacets = await db.all(`
SELECT
CASE
WHEN year_introduced < 1960 THEN 'Pre-1960'
WHEN year_introduced BETWEEN 1960 AND 1969 THEN '1960s'
WHEN year_introduced BETWEEN 1970 AND 1979 THEN '1970s'
WHEN year_introduced BETWEEN 1980 AND 1989 THEN '1980s'
WHEN year_introduced BETWEEN 1990 AND 1999 THEN '1990s'
WHEN year_introduced BETWEEN 2000 AND 2009 THEN '2000s'
WHEN year_introduced BETWEEN 2010 AND 2019 THEN '2010s'
ELSE '2020s'
END as decade,
COUNT(*) as count
FROM watches
WHERE year_introduced IS NOT NULL
GROUP BY decade
ORDER BY decade
`);
facets.decades = yearFacets;
return facets;
}
/**
* Smart search with typo tolerance and suggestions
* GET /api/advanced-search/smart?q=spedmaster
*/
router.get('/smart', async (req, res) => {
try {
const { q } = req.query;
if (!q || q.length < 3) {
return res.status(400).json({ error: 'Query too short' });
}
// First try exact match
let results = await db.all(`
SELECT w.*,
highlight(watches_fts, 1, '<mark>', '</mark>') as highlighted_name
FROM watches w
JOIN watches_fts f ON f.id = w.id
WHERE watches_fts MATCH ?
LIMIT 10
`, [q]);
// If no results, try fuzzy search
if (results.length === 0) {
// Add wildcards for fuzzy matching
const fuzzyQuery = q.split(' ').map(word => word + '*').join(' ');
results = await db.all(`
SELECT w.*,
highlight(watches_fts, 1, '<mark>', '</mark>') as highlighted_name
FROM watches w
JOIN watches_fts f ON f.id = w.id
WHERE watches_fts MATCH ?
LIMIT 10
`, [fuzzyQuery]);
}
// Get suggestions for alternative searches
const suggestions = await db.all(`
SELECT DISTINCT name, collection
FROM watches
WHERE name LIKE ? OR collection LIKE ?
LIMIT 5
`, [`%${q}%`, `%${q}%`]);
res.json({
success: true,
query: q,
total: results.length,
results,
suggestions: suggestions.map(s => ({
text: s.name,
collection: s.collection
}))
});
} catch (error) {
console.error('Smart search error:', error);
res.status(500).json({
error: 'Smart search failed',
message: error.message
});
}
});
/**
* Aggregate data for analytics
* GET /api/advanced-search/analytics
*/
router.get('/analytics', async (req, res) => {
try {
// Get various analytics
const [
totalWatches,
avgPrice,
priceRange,
topAppreciating,
collectionStats,
yearStats
] = await Promise.all([
// Total watches
db.get('SELECT COUNT(*) as count FROM watches'),
// Average price
db.get('SELECT AVG(avg_price) as avg FROM watches WHERE avg_price > 0'),
// Price range
db.get('SELECT MIN(min_price) as min, MAX(max_price) as max FROM watches'),
// Top appreciating
db.all(`
SELECT name, appreciation_rate
FROM watches
WHERE appreciation_rate > 0
ORDER BY appreciation_rate DESC
LIMIT 5
`),
// Collection statistics
db.all(`
SELECT
collection,
COUNT(*) as count,
AVG(avg_price) as avg_price,
AVG(appreciation_rate) as avg_appreciation
FROM watches
GROUP BY collection
ORDER BY count DESC
`),
// Year distribution
db.all(`
SELECT
year_introduced,
COUNT(*) as count
FROM watches
WHERE year_introduced IS NOT NULL
GROUP BY year_introduced
ORDER BY year_introduced
`)
]);
res.json({
success: true,
analytics: {
totalWatches: totalWatches.count,
averagePrice: avgPrice.avg,
priceRange: {
min: priceRange.min,
max: priceRange.max
},
topAppreciating,
collectionStats,
yearDistribution: yearStats
}
});
} catch (error) {
console.error('Analytics error:', error);
res.status(500).json({
error: 'Analytics failed',
message: error.message
});
}
});
/**
* Saved searches functionality
* POST /api/advanced-search/save
*/
router.post('/save', async (req, res) => {
try {
const { name, query, filters, userId } = req.body;
// In production, this would save to a database
// For now, we'll just return success
res.json({
success: true,
savedSearch: {
id: Date.now().toString(),
name,
query,
filters,
userId,
createdAt: new Date().toISOString()
}
});
} catch (error) {
console.error('Save search error:', error);
res.status(500).json({
error: 'Failed to save search',
message: error.message
});
}
});
export default router;