`api/search.js`

392 lines
const sqlite3 = require('sqlite3').verbose();
const path = require('path');

class SearchAPI {
  constructor(dbPath = '/root/Projects/handbag-authentication/data/handbags.db') {
    this.db = new sqlite3.Database(dbPath);
  }

  /**
   * Full-text search across all fields
   * @param {string} query - Search query
   * @param {object} options - Search options (limit, offset, filters)
   */
  fullTextSearch(query, options = {}) {
    return new Promise((resolve, reject) => {
      const limit = options.limit || 100;
      const offset = options.offset || 0;
      
      // Log search for analytics
      this.logSearch(query, 'full_text');
      
      const sql = `
        SELECT 
          l.*,
          snippet(listings_fts, 2, '<mark>', '</mark>', '...', 50) as title_snippet,
          snippet(listings_fts, 8, '<mark>', '</mark>', '...', 100) as description_snippet,
          rank
        FROM listings l
        JOIN listings_fts ON l.id = listings_fts.rowid
        WHERE listings_fts MATCH ?
        ORDER BY rank
        LIMIT ? OFFSET ?
      `;
      
      this.db.all(sql, [query, limit, offset], (err, rows) => {
        if (err) {
          reject(err);
        } else {
          this.db.get(
            'SELECT COUNT(*) as total FROM listings_fts WHERE listings_fts MATCH ?',
            [query],
            (countErr, countRow) => {
              resolve({
                results: rows,
                total: countErr ? 0 : countRow.total,
                query: query,
                limit: limit,
                offset: offset
              });
            }
          );
        }
      });
    });
  }

  /**
   * Advanced search with multiple filters
   */
  advancedSearch(params) {
    return new Promise((resolve, reject) => {
      let conditions = [];
      let values = [];
      
      // Text search
      if (params.text) {
        conditions.push(`(
          title LIKE ? OR 
          brand LIKE ? OR 
          model LIKE ? OR 
          description LIKE ? OR
          seller_name LIKE ? OR
          condition LIKE ?
        )`);
        const searchTerm = `%${params.text}%`;
        values.push(searchTerm, searchTerm, searchTerm, searchTerm, searchTerm, searchTerm);
      }
      
      // Brand filter
      if (params.brand) {
        conditions.push('LOWER(brand) = LOWER(?)');
        values.push(params.brand);
      }
      
      // Model filter
      if (params.model) {
        conditions.push('LOWER(model) = LOWER(?)');
        values.push(params.model);
      }
      
      // Price range
      if (params.minPrice) {
        conditions.push('price_usd >= ?');
        values.push(params.minPrice);
      }
      
      if (params.maxPrice) {
        conditions.push('price_usd <= ?');
        values.push(params.maxPrice);
      }
      
      // Condition filter
      if (params.condition) {
        conditions.push('condition LIKE ?');
        values.push(`%${params.condition}%`);
      }
      
      // Has image filter
      if (params.hasImage === true) {
        conditions.push('image_url IS NOT NULL');
      }
      
      // Source filter
      if (params.source) {
        conditions.push('source = ?');
        values.push(params.source);
      }
      
      // Deal only filter
      if (params.dealsOnly) {
        conditions.push(`
          EXISTS (
            SELECT 1 FROM deal_analysis da 
            WHERE da.listing_id = listings.id 
            AND da.is_deal = 1
          )
        `);
      }
      
      const whereClause = conditions.length > 0 
        ? 'WHERE ' + conditions.join(' AND ') 
        : '';
      
      // Sorting
      let orderBy = 'ORDER BY ';
      switch(params.sort) {
        case 'price_asc':
          orderBy += 'price_usd ASC';
          break;
        case 'price_desc':
          orderBy += 'price_usd DESC';
          break;
        case 'date_desc':
          orderBy += 'crawled_at DESC';
          break;
        case 'brand':
          orderBy += 'brand, model';
          break;
        default:
          orderBy += 'crawled_at DESC';
      }
      
      const limit = params.limit || 100;
      const offset = params.offset || 0;
      
      values.push(limit, offset);
      
      const sql = `
        SELECT * FROM listings
        ${whereClause}
        ${orderBy}
        LIMIT ? OFFSET ?
      `;
      
      // Log search
      this.logSearch(JSON.stringify(params), 'advanced');
      
      this.db.all(sql, values, (err, rows) => {
        if (err) {
          reject(err);
        } else {
          // Get total count
          const countSql = `SELECT COUNT(*) as total FROM listings ${whereClause}`;
          const countValues = values.slice(0, -2); // Remove limit and offset
          
          this.db.get(countSql, countValues, (countErr, countRow) => {
            resolve({
              results: rows,
              total: countErr ? 0 : countRow.total,
              filters: params,
              limit: limit,
              offset: offset
            });
          });
        }
      });
    });
  }

  /**
   * Search by image URL or similar items
   */
  searchByImage(imageUrl, listingId) {
    return new Promise((resolve, reject) => {
      // If listing ID provided, find similar items
      if (listingId) {
        const sql = `
          SELECT 
            l2.*,
            COUNT(*) as similarity_score
          FROM listings l1
          JOIN listings l2 ON l1.id != l2.id
          WHERE l1.id = ?
            AND (
              l2.brand = l1.brand OR
              l2.model = l1.model OR
              ABS(l2.price_usd - l1.price_usd) / l1.price_usd < 0.2
            )
          GROUP BY l2.id
          ORDER BY similarity_score DESC
          LIMIT 20
        `;
        
        this.db.all(sql, [listingId], (err, rows) => {
          if (err) {
            reject(err);
          } else {
            resolve({
              similar_items: rows,
              base_listing_id: listingId
            });
          }
        });
      } else {
        // Search by direct image URL match
        const sql = `
          SELECT * FROM listings
          WHERE image_url = ? OR thumbnail_url = ?
          LIMIT 10
        `;
        
        this.db.all(sql, [imageUrl, imageUrl], (err, rows) => {
          if (err) {
            reject(err);
          } else {
            resolve({
              results: rows,
              search_image: imageUrl
            });
          }
        });
      }
    });
  }

  /**
   * Get search suggestions based on partial input
   */
  getSuggestions(query, limit = 10) {
    return new Promise((resolve, reject) => {
      const sql = `
        SELECT DISTINCT 
          brand || ' ' || COALESCE(model, '') as suggestion,
          brand,
          model,
          COUNT(*) as item_count
        FROM listings
        WHERE brand LIKE ? OR model LIKE ? OR title LIKE ?
        GROUP BY brand, model
        ORDER BY item_count DESC
        LIMIT ?
      `;
      
      const searchTerm = `%${query}%`;
      
      this.db.all(sql, [searchTerm, searchTerm, searchTerm, limit], (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows);
        }
      });
    });
  }

  /**
   * Get popular searches
   */
  getPopularSearches(days = 7, limit = 20) {
    return new Promise((resolve, reject) => {
      const sql = `
        SELECT 
          search_query,
          COUNT(*) as search_count,
          MAX(searched_at) as last_searched
        FROM search_history
        WHERE searched_at >= datetime('now', '-' || ? || ' days')
        GROUP BY search_query
        ORDER BY search_count DESC
        LIMIT ?
      `;
      
      this.db.all(sql, [days, limit], (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows);
        }
      });
    });
  }

  /**
   * Search within specific fields
   */
  searchByField(field, value, exactMatch = false) {
    return new Promise((resolve, reject) => {
      const validFields = ['brand', 'model', 'condition', 'seller_name', 'source', 'location'];
      
      if (!validFields.includes(field)) {
        reject(new Error('Invalid search field'));
        return;
      }
      
      let sql, searchValue;
      
      if (exactMatch) {
        sql = `SELECT * FROM listings WHERE LOWER(${field}) = LOWER(?) ORDER BY crawled_at DESC LIMIT 100`;
        searchValue = value;
      } else {
        sql = `SELECT * FROM listings WHERE ${field} LIKE ? ORDER BY crawled_at DESC LIMIT 100`;
        searchValue = `%${value}%`;
      }
      
      this.db.all(sql, [searchValue], (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve({
            results: rows,
            field: field,
            value: value,
            exact_match: exactMatch
          });
        }
      });
    });
  }

  /**
   * Get all unique values for a field (for filters)
   */
  getFieldValues(field) {
    return new Promise((resolve, reject) => {
      const validFields = ['brand', 'model', 'condition', 'source', 'location', 'seller_name'];
      
      if (!validFields.includes(field)) {
        reject(new Error('Invalid field'));
        return;
      }
      
      const sql = `
        SELECT 
          ${field} as value,
          COUNT(*) as count
        FROM listings
        WHERE ${field} IS NOT NULL
        GROUP BY ${field}
        ORDER BY count DESC
      `;
      
      this.db.all(sql, [], (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows);
        }
      });
    });
  }

  /**
   * Log search query for analytics
   */
  logSearch(query, searchType) {
    const sql = `
      INSERT INTO search_history (search_query, search_type)
      VALUES (?, ?)
    `;
    
    this.db.run(sql, [query, searchType]);
  }

  /**
   * Close database connection
   */
  close() {
    this.db.close();
  }
}

module.exports = SearchAPI;