← back to Handbag Authentication

api/price-history.js

186 lines

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

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

  // Get recent price history
  getRecentHistory(limit = 100, brand = null, retailer = null) {
    return new Promise((resolve, reject) => {
      let query = `
        SELECT 
          retailer,
          brand,
          item_name,
          model,
          condition,
          price_usd,
          first_seen_date,
          last_updated,
          days_on_market,
          price_change,
          product_url,
          image_url,
          crawl_date
        FROM price_history
        WHERE 1=1
      `;
      
      const params = [];
      
      if (brand) {
        query += ` AND LOWER(brand) = LOWER(?)`;
        params.push(brand);
      }
      
      if (retailer) {
        query += ` AND LOWER(retailer) = LOWER(?)`;
        params.push(retailer);
      }
      
      query += ` ORDER BY last_updated DESC LIMIT ?`;
      params.push(limit);
      
      this.db.all(query, params, (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows);
        }
      });
    });
  }

  // Get price trends for a specific brand/model
  getPriceTrends(brand, model = null, days = 30) {
    return new Promise((resolve, reject) => {
      let query = `
        SELECT 
          DATE(crawl_date) as date,
          AVG(price_usd) as avg_price,
          MIN(price_usd) as min_price,
          MAX(price_usd) as max_price,
          COUNT(*) as item_count
        FROM price_history
        WHERE LOWER(brand) = LOWER(?)
          AND crawl_date >= date('now', '-' || ? || ' days')
      `;
      
      const params = [brand, days];
      
      if (model) {
        query += ` AND LOWER(model) = LOWER(?)`;
        params.push(model);
      }
      
      query += ` GROUP BY DATE(crawl_date) ORDER BY date`;
      
      this.db.all(query, params, (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows);
        }
      });
    });
  }

  // Get items with significant price changes
  getPriceChanges(threshold = 100) {
    return new Promise((resolve, reject) => {
      const query = `
        SELECT 
          retailer,
          brand,
          item_name,
          model,
          price_usd,
          price_change,
          ROUND((price_change / (price_usd - price_change)) * 100, 2) as change_percentage,
          first_seen_date,
          last_updated,
          days_on_market,
          product_url
        FROM price_history
        WHERE ABS(price_change) >= ?
          AND last_updated >= datetime('now', '-7 days')
        ORDER BY ABS(price_change) DESC
        LIMIT 50
      `;
      
      this.db.all(query, [threshold], (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows);
        }
      });
    });
  }

  // Get new listings (first seen today)
  getNewListings() {
    return new Promise((resolve, reject) => {
      const query = `
        SELECT 
          retailer,
          brand,
          item_name,
          model,
          condition,
          price_usd,
          first_seen_date,
          last_updated,
          product_url,
          image_url
        FROM price_history
        WHERE first_seen_date = date('now')
        ORDER BY last_updated DESC
      `;
      
      this.db.all(query, (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows);
        }
      });
    });
  }

  // Get summary statistics
  getSummaryStats() {
    return new Promise((resolve, reject) => {
      const query = `
        SELECT 
          COUNT(DISTINCT external_id) as total_items,
          COUNT(DISTINCT CASE WHEN first_seen_date = date('now') THEN external_id END) as new_today,
          COUNT(DISTINCT CASE WHEN ABS(price_change) > 0 THEN external_id END) as items_with_changes,
          AVG(days_on_market) as avg_days_on_market,
          MIN(last_updated) as oldest_update,
          MAX(last_updated) as latest_update,
          COUNT(DISTINCT retailer) as retailer_count,
          COUNT(DISTINCT brand) as brand_count
        FROM price_history
        WHERE crawl_date >= date('now', '-30 days')
      `;
      
      this.db.get(query, (err, row) => {
        if (err) {
          reject(err);
        } else {
          resolve(row);
        }
      });
    });
  }

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

module.exports = PriceHistoryAPI;