← back to Handbag Auth Nextjs

auction-viewer/server-enhanced.js

776 lines

const express = require('express');
const Database = require('better-sqlite3');
const cors = require('cors');
const fs = require('fs');
const path = require('path');
const rateLimit = require('express-rate-limit');
const NodeCache = require('node-cache');
const Joi = require('joi');

const app = express();
const PORT = 7500;

// Cache with 5 minute TTL
const cache = new NodeCache({ stdTTL: 300, checkperiod: 60 });

// Structured logging
const logger = {
  info: (message, meta = {}) => {
    console.log(JSON.stringify({
      timestamp: new Date().toISOString(),
      level: 'INFO',
      message,
      ...meta
    }));
  },
  error: (message, error = {}, meta = {}) => {
    console.error(JSON.stringify({
      timestamp: new Date().toISOString(),
      level: 'ERROR',
      message,
      error: error.message || error,
      stack: error.stack,
      ...meta
    }));
  },
  warn: (message, meta = {}) => {
    console.warn(JSON.stringify({
      timestamp: new Date().toISOString(),
      level: 'WARN',
      message,
      ...meta
    }));
  }
};

// Metrics collection
const metrics = {
  apiCalls: 0,
  errors: 0,
  cacheHits: 0,
  cacheMisses: 0,
  avgResponseTime: 0,
  responseTimes: [],
  startTime: Date.now()
};

// Response time tracking middleware
app.use((req, res, next) => {
  const start = Date.now();
  res.on('finish', () => {
    const duration = Date.now() - start;
    metrics.responseTimes.push(duration);
    if (metrics.responseTimes.length > 100) metrics.responseTimes.shift();
    metrics.avgResponseTime = metrics.responseTimes.reduce((a, b) => a + b, 0) / metrics.responseTimes.length;
    metrics.apiCalls++;

    logger.info('API Request', {
      method: req.method,
      path: req.path,
      status: res.statusCode,
      duration: `${duration}ms`,
      ip: req.ip
    });
  });
  next();
});

// Rate limiting - 100 requests per 15 minutes per IP
const limiter = rateLimit({
  windowMs: 15 * 60 * 1000,
  max: 100,
  message: { success: false, error: 'Too many requests, please try again later.' },
  standardHeaders: true,
  legacyHeaders: false,
});

// Stricter rate limit for search endpoint
const searchLimiter = rateLimit({
  windowMs: 15 * 60 * 1000,
  max: 30,
  message: { success: false, error: 'Too many search requests, please try again later.' }
});

app.use(cors());
app.use(express.json());
app.use(express.static(__dirname));
app.use('/api/', limiter);

// Database paths
const DB_PATH = path.join(__dirname, '..', 'data', 'auction-history', 'auctions.db');
const CSV_PATH = path.join(__dirname, '..', 'data', 'auction-history', 'auction-history.csv');
const LOG_PATH = path.join(__dirname, '..', 'data', 'auction-history', 'scraper.log');
const METRICS_LOG_PATH = path.join(__dirname, '..', 'data', 'auction-history', 'metrics.log');

// Validation schemas
const querySchema = Joi.object({
  page: Joi.number().integer().min(1).default(1),
  limit: Joi.number().integer().min(1).max(100).default(50),
  sort: Joi.string().valid('price_asc', 'price_desc', 'date_asc', 'date_desc', 'savings_desc', 'savings_asc').default('date_desc'),
  brand: Joi.string().allow(''),
  auction_house: Joi.string().allow(''),
  min_price: Joi.number().min(0),
  max_price: Joi.number().min(0),
  min_savings: Joi.number().min(0)
});

const searchSchema = Joi.object({
  q: Joi.string().required().min(2).max(100),
  page: Joi.number().integer().min(1).default(1),
  limit: Joi.number().integer().min(1).max(50).default(20)
});

// Helper: Get database connection with error handling
function getDb(readonly = true) {
  try {
    return new Database(DB_PATH, { readonly });
  } catch (error) {
    logger.error('Database connection failed', error);
    throw new Error('Database unavailable');
  }
}

// Helper: Build WHERE clause from filters
function buildWhereClause(filters) {
  const conditions = [];
  const params = [];

  if (filters.brand) {
    conditions.push('LOWER(search_term) LIKE ?');
    params.push(`%${filters.brand.toLowerCase()}%`);
  }

  if (filters.auction_house) {
    conditions.push('LOWER(auction_house) LIKE ?');
    params.push(`%${filters.auction_house.toLowerCase()}%`);
  }

  if (filters.min_price !== undefined) {
    conditions.push('current_price >= ?');
    params.push(filters.min_price);
  }

  if (filters.max_price !== undefined) {
    conditions.push('current_price <= ?');
    params.push(filters.max_price);
  }

  if (filters.min_savings !== undefined) {
    conditions.push('(estimate_low - current_price) >= ?');
    params.push(filters.min_savings);
  }

  return {
    where: conditions.length > 0 ? 'WHERE ' + conditions.join(' AND ') : '',
    params
  };
}

// Helper: Get sort clause
function getSortClause(sort) {
  const sortMap = {
    'price_asc': 'current_price ASC',
    'price_desc': 'current_price DESC',
    'date_asc': 'created_at ASC',
    'date_desc': 'created_at DESC',
    'savings_desc': '(estimate_low - current_price) DESC',
    'savings_asc': '(estimate_low - current_price) ASC'
  };
  return sortMap[sort] || 'created_at DESC';
}

// API: Health check endpoint
app.get('/api/health', (req, res) => {
  const health = {
    status: 'healthy',
    timestamp: new Date().toISOString(),
    uptime: Math.floor((Date.now() - metrics.startTime) / 1000),
    database: {
      connected: fs.existsSync(DB_PATH),
      size: fs.existsSync(DB_PATH) ? fs.statSync(DB_PATH).size : 0
    },
    cache: {
      keys: cache.keys().length,
      hits: metrics.cacheHits,
      misses: metrics.cacheMisses,
      hitRate: metrics.cacheHits + metrics.cacheMisses > 0
        ? (metrics.cacheHits / (metrics.cacheHits + metrics.cacheMisses) * 100).toFixed(2) + '%'
        : 'N/A'
    },
    metrics: {
      totalRequests: metrics.apiCalls,
      errors: metrics.errors,
      avgResponseTime: Math.round(metrics.avgResponseTime) + 'ms'
    }
  };

  // Test database connection
  try {
    const db = getDb();
    const result = db.prepare('SELECT COUNT(*) as count FROM auctions').get();
    health.database.records = result.count;
    db.close();
  } catch (error) {
    health.status = 'degraded';
    health.database.error = error.message;
    logger.error('Health check database query failed', error);
  }

  res.json(health);
});

// API: Get auctions with pagination, filtering, and sorting
app.get('/api/auctions', (req, res) => {
  try {
    // Validate query parameters
    const { error, value } = querySchema.validate(req.query);
    if (error) {
      return res.status(400).json({
        success: false,
        error: 'Invalid query parameters',
        details: error.details[0].message
      });
    }

    const { page, limit, sort, ...filters } = value;
    const offset = (page - 1) * limit;

    // Generate cache key
    const cacheKey = `auctions:${JSON.stringify({ page, limit, sort, filters })}`;
    const cached = cache.get(cacheKey);

    if (cached) {
      metrics.cacheHits++;
      return res.json({ ...cached, cached: true });
    }

    metrics.cacheMisses++;

    const db = getDb();

    // Build query
    const { where, params } = buildWhereClause(filters);
    const orderBy = getSortClause(sort);

    // Get total count
    const countQuery = `SELECT COUNT(*) as count FROM auctions ${where}`;
    const totalResult = db.prepare(countQuery).get(...params);
    const total = totalResult.count;

    // Get paginated data with calculated savings
    const dataQuery = `
      SELECT *,
        CASE
          WHEN estimate_low > 0 AND current_price > 0
          THEN ((estimate_low - current_price) / estimate_low * 100)
          ELSE 0
        END as savings_percent,
        CASE
          WHEN estimate_low > 0 AND current_price > 0
          THEN (estimate_low - current_price)
          ELSE 0
        END as savings_amount
      FROM auctions
      ${where}
      ORDER BY ${orderBy}
      LIMIT ? OFFSET ?
    `;

    const auctions = db.prepare(dataQuery).all(...params, limit, offset);
    db.close();

    const response = {
      success: true,
      data: auctions,
      pagination: {
        page,
        limit,
        total,
        totalPages: Math.ceil(total / limit),
        hasMore: offset + auctions.length < total
      },
      filters: filters,
      sort
    };

    cache.set(cacheKey, response);
    res.json(response);

  } catch (error) {
    metrics.errors++;
    logger.error('Get auctions failed', error, { query: req.query });
    res.status(500).json({ success: false, error: error.message });
  }
});

// API: Search auctions with full-text search
app.get('/api/search', searchLimiter, (req, res) => {
  try {
    const { error, value } = searchSchema.validate(req.query);
    if (error) {
      return res.status(400).json({
        success: false,
        error: 'Invalid search parameters',
        details: error.details[0].message
      });
    }

    const { q, page, limit } = value;
    const offset = (page - 1) * limit;

    const cacheKey = `search:${q}:${page}:${limit}`;
    const cached = cache.get(cacheKey);

    if (cached) {
      metrics.cacheHits++;
      return res.json({ ...cached, cached: true });
    }

    metrics.cacheMisses++;

    const db = getDb();

    // Search in title, search_term, auction_house
    const searchPattern = `%${q}%`;
    const countQuery = `
      SELECT COUNT(*) as count FROM auctions
      WHERE LOWER(title) LIKE LOWER(?)
         OR LOWER(search_term) LIKE LOWER(?)
         OR LOWER(auction_house) LIKE LOWER(?)
    `;
    const totalResult = db.prepare(countQuery).get(searchPattern, searchPattern, searchPattern);
    const total = totalResult.count;

    const dataQuery = `
      SELECT *,
        CASE
          WHEN estimate_low > 0 AND current_price > 0
          THEN ((estimate_low - current_price) / estimate_low * 100)
          ELSE 0
        END as savings_percent,
        CASE
          WHEN estimate_low > 0 AND current_price > 0
          THEN (estimate_low - current_price)
          ELSE 0
        END as savings_amount
      FROM auctions
      WHERE LOWER(title) LIKE LOWER(?)
         OR LOWER(search_term) LIKE LOWER(?)
         OR LOWER(auction_house) LIKE LOWER(?)
      ORDER BY created_at DESC
      LIMIT ? OFFSET ?
    `;

    const results = db.prepare(dataQuery).all(searchPattern, searchPattern, searchPattern, limit, offset);
    db.close();

    const response = {
      success: true,
      query: q,
      data: results,
      pagination: {
        page,
        limit,
        total,
        totalPages: Math.ceil(total / limit)
      }
    };

    cache.set(cacheKey, response);
    res.json(response);

  } catch (error) {
    metrics.errors++;
    logger.error('Search failed', error, { query: req.query });
    res.status(500).json({ success: false, error: error.message });
  }
});

// API: Get brand analytics
app.get('/api/brands', (req, res) => {
  try {
    const cacheKey = 'brands:analytics';
    const cached = cache.get(cacheKey);

    if (cached) {
      metrics.cacheHits++;
      return res.json({ ...cached, cached: true });
    }

    metrics.cacheMisses++;

    const db = getDb();

    const brands = db.prepare(`
      SELECT
        search_term as brand,
        COUNT(*) as total_auctions,
        AVG(current_price) as avg_price,
        MIN(current_price) as min_price,
        MAX(current_price) as max_price,
        AVG(CASE
          WHEN estimate_low > 0 AND current_price > 0
          THEN ((estimate_low - current_price) / estimate_low * 100)
          ELSE 0
        END) as avg_savings_percent,
        SUM(CASE WHEN current_price > 0 THEN 1 ELSE 0 END) as with_price
      FROM auctions
      GROUP BY search_term
      ORDER BY total_auctions DESC
    `).all();

    db.close();

    const response = {
      success: true,
      data: brands,
      total_brands: brands.length
    };

    cache.set(cacheKey, response);
    res.json(response);

  } catch (error) {
    metrics.errors++;
    logger.error('Get brands failed', error);
    res.status(500).json({ success: false, error: error.message });
  }
});

// API: Get trending auctions (highest savings)
app.get('/api/trending', (req, res) => {
  try {
    const limit = parseInt(req.query.limit) || 20;

    const cacheKey = `trending:${limit}`;
    const cached = cache.get(cacheKey);

    if (cached) {
      metrics.cacheHits++;
      return res.json({ ...cached, cached: true });
    }

    metrics.cacheMisses++;

    const db = getDb();

    const trending = db.prepare(`
      SELECT *,
        CASE
          WHEN estimate_low > 0 AND current_price > 0
          THEN ((estimate_low - current_price) / estimate_low * 100)
          ELSE 0
        END as savings_percent,
        CASE
          WHEN estimate_low > 0 AND current_price > 0
          THEN (estimate_low - current_price)
          ELSE 0
        END as savings_amount
      FROM auctions
      WHERE current_price > 0 AND estimate_low > 0
      ORDER BY savings_percent DESC
      LIMIT ?
    `).all(limit);

    db.close();

    const response = {
      success: true,
      data: trending,
      count: trending.length
    };

    cache.set(cacheKey, response);
    res.json(response);

  } catch (error) {
    metrics.errors++;
    logger.error('Get trending failed', error);
    res.status(500).json({ success: false, error: error.message });
  }
});

// API: Get best value auctions (enhanced)
app.get('/api/best-values', (req, res) => {
  try {
    const sortBy = req.query.sort || 'percent';
    const limit = parseInt(req.query.limit) || 100;

    const cacheKey = `best-values:${sortBy}:${limit}`;
    const cached = cache.get(cacheKey);

    if (cached) {
      metrics.cacheHits++;
      return res.json({ ...cached, cached: true });
    }

    metrics.cacheMisses++;

    const db = getDb();
    const orderBy = sortBy === 'dollar' ? 'savings_amount' : 'savings_percent';

    const auctions = db.prepare(`
      SELECT *,
        CASE
          WHEN estimate_low > 0 AND current_price > 0
          THEN ((estimate_low - current_price) / estimate_low * 100)
          ELSE 0
        END as savings_percent,
        CASE
          WHEN estimate_low > 0 AND current_price > 0
          THEN (estimate_low - current_price)
          ELSE 0
        END as savings_amount
      FROM auctions
      WHERE current_price > 0 AND estimate_low > 0
      ORDER BY ${orderBy} DESC
      LIMIT ?
    `).all(limit);

    db.close();

    const response = {
      success: true,
      count: auctions.length,
      data: auctions,
      sortedBy: sortBy
    };

    cache.set(cacheKey, response);
    res.json(response);

  } catch (error) {
    metrics.errors++;
    logger.error('Get best values failed', error);
    res.status(500).json({ success: false, error: error.message });
  }
});

// API: Get statistics (enhanced with caching)
app.get('/api/stats', (req, res) => {
  try {
    const cacheKey = 'stats:overview';
    const cached = cache.get(cacheKey);

    if (cached) {
      metrics.cacheHits++;
      return res.json({ ...cached, cached: true });
    }

    metrics.cacheMisses++;

    const db = getDb();

    const total = db.prepare('SELECT COUNT(*) as count FROM auctions').get();
    const byBrand = db.prepare(`
      SELECT search_term, COUNT(*) as count
      FROM auctions
      GROUP BY search_term
      ORDER BY count DESC
    `).all();

    const byAuctionHouse = db.prepare(`
      SELECT auction_house, COUNT(*) as count
      FROM auctions
      GROUP BY auction_house
      ORDER BY count DESC
    `).all();

    const avgPrice = db.prepare(`
      SELECT AVG(current_price) as avg_price
      FROM auctions
      WHERE current_price > 0
    `).get();

    const priceRange = db.prepare(`
      SELECT
        MIN(current_price) as min_price,
        MAX(current_price) as max_price
      FROM auctions
      WHERE current_price > 0
    `).get();

    // Additional stats
    const recentActivity = db.prepare(`
      SELECT DATE(created_at) as date, COUNT(*) as count
      FROM auctions
      WHERE created_at >= DATE('now', '-7 days')
      GROUP BY DATE(created_at)
      ORDER BY date DESC
    `).all();

    db.close();

    const response = {
      success: true,
      stats: {
        total: total.count,
        byBrand,
        byAuctionHouse,
        avgPrice: avgPrice.avg_price || 0,
        priceRange: priceRange || { min_price: 0, max_price: 0 },
        recentActivity
      }
    };

    cache.set(cacheKey, response);
    res.json(response);

  } catch (error) {
    metrics.errors++;
    logger.error('Get stats failed', error);
    res.status(500).json({ success: false, error: error.message });
  }
});

// API: Get scraper status
app.get('/api/scraper/status', (req, res) => {
  try {
    const db = getDb();

    // Get last scrape time
    const lastAuction = db.prepare(`
      SELECT created_at, search_term
      FROM auctions
      ORDER BY created_at DESC
      LIMIT 1
    `).get();

    // Get scrapes per day for last 7 days
    const dailyStats = db.prepare(`
      SELECT DATE(created_at) as date, COUNT(*) as count
      FROM auctions
      WHERE created_at >= DATE('now', '-7 days')
      GROUP BY DATE(created_at)
      ORDER BY date DESC
    `).all();

    db.close();

    // Read last 20 lines of scraper log
    let recentLogs = [];
    if (fs.existsSync(LOG_PATH)) {
      const logContent = fs.readFileSync(LOG_PATH, 'utf8');
      recentLogs = logContent.split('\n').filter(l => l.trim()).slice(-20);
    }

    const status = {
      success: true,
      lastScrape: lastAuction ? lastAuction.created_at : 'Never',
      lastBrand: lastAuction ? lastAuction.search_term : 'N/A',
      dailyStats,
      recentLogs,
      logFile: LOG_PATH,
      nextScheduledRun: '06:00 UTC daily'
    };

    res.json(status);

  } catch (error) {
    metrics.errors++;
    logger.error('Get scraper status failed', error);
    res.status(500).json({ success: false, error: error.message });
  }
});

// API: Get metrics
app.get('/api/metrics', (req, res) => {
  res.json({
    success: true,
    metrics: {
      ...metrics,
      uptime: Math.floor((Date.now() - metrics.startTime) / 1000),
      memory: process.memoryUsage(),
      cacheStats: cache.getStats()
    }
  });
});

// API: Clear cache (admin endpoint)
app.post('/api/cache/clear', (req, res) => {
  const clearedKeys = cache.keys().length;
  cache.flushAll();
  logger.info('Cache cleared', { clearedKeys });
  res.json({ success: true, message: `Cleared ${clearedKeys} cache entries` });
});

// API: Get recent logs
app.get('/api/logs', (req, res) => {
  try {
    const lines = parseInt(req.query.lines) || 100;
    const logs = fs.readFileSync(LOG_PATH, 'utf8').split('\n').slice(-lines).reverse();
    res.json({ success: true, logs, count: logs.length });
  } catch (error) {
    logger.error('Get logs failed', error);
    res.json({ success: false, error: error.message, logs: [] });
  }
});

// API: Get CSV data
app.get('/api/csv', (req, res) => {
  try {
    const csvData = fs.readFileSync(CSV_PATH, 'utf8');
    res.json({ success: true, data: csvData });
  } catch (error) {
    logger.error('Get CSV failed', error);
    res.json({ success: false, error: error.message, data: '' });
  }
});

// API: Download CSV
app.get('/api/download-csv', (req, res) => {
  try {
    res.download(CSV_PATH, 'auction-history.csv');
  } catch (error) {
    logger.error('Download CSV failed', error);
    res.status(500).json({ error: error.message });
  }
});

// API: System status (backward compatible)
app.get('/api/status', (req, res) => {
  const status = {
    database: fs.existsSync(DB_PATH),
    csv: fs.existsSync(CSV_PATH),
    logs: fs.existsSync(LOG_PATH),
    dbSize: fs.existsSync(DB_PATH) ? fs.statSync(DB_PATH).size : 0,
    csvSize: fs.existsSync(CSV_PATH) ? fs.statSync(CSV_PATH).size : 0,
    logSize: fs.existsSync(LOG_PATH) ? fs.statSync(LOG_PATH).size : 0,
  };
  res.json({ success: true, status });
});

// Log metrics periodically
setInterval(() => {
  const metricLog = {
    timestamp: new Date().toISOString(),
    ...metrics,
    uptime: Math.floor((Date.now() - metrics.startTime) / 1000),
    cacheStats: cache.getStats()
  };

  fs.appendFileSync(METRICS_LOG_PATH, JSON.stringify(metricLog) + '\n');
}, 60000); // Every minute

// Graceful shutdown
process.on('SIGTERM', () => {
  logger.info('SIGTERM received, shutting down gracefully');
  process.exit(0);
});

process.on('SIGINT', () => {
  logger.info('SIGINT received, shutting down gracefully');
  process.exit(0);
});

app.listen(PORT, '0.0.0.0', () => {
  logger.info('Server started', {
    port: PORT,
    url: `http://45.61.58.125:${PORT}`,
    environment: process.env.NODE_ENV || 'development'
  });
  console.log(`Auction Data API running on http://45.61.58.125:${PORT}`);
  console.log(`Dashboard: http://45.61.58.125:${PORT}`);
  console.log(`Health Check: http://45.61.58.125:${PORT}/api/health`);
});