← back to Handbag Auth Nextjs

auction-viewer/server.js

696 lines

const express = require('express');
const Database = require('better-sqlite3');
const cors = require('cors');
const fs = require('fs');
const path = require('path');
const { exec } = require('child_process');
const util = require('util');

const execPromise = util.promisify(exec);

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

app.use(cors());
app.use(express.json());
app.use(express.static(__dirname));

// 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 ANALYTICS_SCRIPT = path.join(__dirname, 'analytics-engine.py');

// API: Get all auctions
app.get('/api/auctions', (req, res) => {
  try {
    const db = new Database(DB_PATH, { readonly: true });
    const auctions = db.prepare('SELECT * FROM auctions ORDER BY created_at DESC LIMIT 1000').all();
    db.close();
    res.json({ success: true, count: auctions.length, data: auctions });
  } catch (error) {
    res.json({ success: false, error: error.message, data: [] });
  }
});

// API: Get best value auctions
app.get('/api/best-values', (req, res) => {
  try {
    const db = new Database(DB_PATH, { readonly: true });
    const sortBy = req.query.sort || 'percent'; // 'percent' or 'dollar'

    // Get auctions with complete price data
    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 100
    `).all();

    db.close();

    res.json({ success: true, count: auctions.length, data: auctions, sortedBy: sortBy });
  } catch (error) {
    res.json({ success: false, error: error.message, data: [] });
  }
});

// API: Get statistics
app.get('/api/stats', (req, res) => {
  try {
    const db = new Database(DB_PATH, { readonly: true });

    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();

    db.close();

    res.json({
      success: true,
      stats: {
        total: total.count,
        byBrand,
        byAuctionHouse,
        avgPrice: avgPrice.avg_price || 0,
        priceRange: priceRange || { min_price: 0, max_price: 0 }
      }
    });
  } catch (error) {
    res.json({ success: false, error: error.message });
  }
});

// API: Get recent logs
app.get('/api/logs', (req, res) => {
  try {
    const logs = fs.readFileSync(LOG_PATH, 'utf8').split('\n').slice(-100).reverse();
    res.json({ success: true, logs });
  } catch (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) {
    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) {
    res.status(500).json({ error: error.message });
  }
});

// API: System status
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 });
});

// ============================================
// ANALYTICS API ENDPOINTS
// ============================================

// Helper function to run Python analytics script
async function runAnalytics(command, args = []) {
  try {
    const argsStr = args.join(' ');
    const cmd = `python3 "${ANALYTICS_SCRIPT}" "${DB_PATH}" ${command} ${argsStr}`;
    const { stdout, stderr } = await execPromise(cmd);

    if (stderr && !stdout) {
      throw new Error(stderr);
    }

    return JSON.parse(stdout);
  } catch (error) {
    console.error('Analytics error:', error);
    throw error;
  }
}

// API: Get top deals by Deal Score algorithm
app.get('/api/insights/deal-scores', async (req, res) => {
  try {
    const limit = parseInt(req.query.limit) || 50;
    const deals = await runAnalytics('deal-scores', [limit]);

    res.json({
      success: true,
      count: deals.length,
      data: deals,
      generated_at: new Date().toISOString()
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message,
      data: []
    });
  }
});

// API: Get price trends by brand
app.get('/api/insights/trends', async (req, res) => {
  try {
    const trends = await runAnalytics('trends');

    res.json({
      success: true,
      data: trends,
      generated_at: new Date().toISOString()
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message,
      data: {}
    });
  }
});

// API: Get price anomalies (outliers)
app.get('/api/insights/anomalies', async (req, res) => {
  try {
    const anomalies = await runAnalytics('anomalies');

    res.json({
      success: true,
      count: anomalies.length,
      data: anomalies,
      generated_at: new Date().toISOString()
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message,
      data: []
    });
  }
});

// API: Predict final price for an auction
app.get('/api/insights/predict/:auction_id', async (req, res) => {
  try {
    const auctionId = req.params.auction_id;
    const prediction = await runAnalytics('predict', [auctionId]);

    res.json({
      success: true,
      data: prediction,
      generated_at: new Date().toISOString()
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message,
      data: null
    });
  }
});

// API: Get personalized recommendations
app.get('/api/insights/recommendations', async (req, res) => {
  try {
    const budget = req.query.budget ? parseFloat(req.query.budget) : null;
    const args = budget ? [budget] : [];

    const recommendations = await runAnalytics('recommendations', args);

    res.json({
      success: true,
      count: recommendations.length,
      data: recommendations,
      filters: {
        budget: budget || 'none'
      },
      generated_at: new Date().toISOString()
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message,
      data: []
    });
  }
});

// API: Get comprehensive market insights
app.get('/api/insights/market', async (req, res) => {
  try {
    const insights = await runAnalytics('market-insights');

    res.json({
      success: true,
      data: insights,
      generated_at: new Date().toISOString()
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message,
      data: null
    });
  }
});

// API: Get brand-specific analytics
app.get('/api/insights/brand/:brand', async (req, res) => {
  try {
    const brand = req.params.brand;
    const db = new Database(DB_PATH, { readonly: true });

    // Get brand statistics
    const stats = db.prepare(`
      SELECT
        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_pct
      FROM auctions
      WHERE search_term = ? AND current_price > 0
    `).get(brand);

    // Get recent auctions
    const recent = db.prepare(`
      SELECT * FROM auctions
      WHERE search_term = ?
      ORDER BY created_at DESC
      LIMIT 20
    `).all(brand);

    // Get price history over time
    const history = db.prepare(`
      SELECT
        strftime('%Y-%m', end_date) as month,
        AVG(current_price) as avg_price,
        COUNT(*) as count
      FROM auctions
      WHERE search_term = ? AND current_price > 0 AND end_date IS NOT NULL
      GROUP BY month
      ORDER BY month DESC
      LIMIT 12
    `).all(brand);

    db.close();

    res.json({
      success: true,
      brand: brand,
      statistics: stats,
      recent_auctions: recent,
      price_history: history,
      generated_at: new Date().toISOString()
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
});

// API: Get time-series data for charts
app.get('/api/insights/time-series', (req, res) => {
  try {
    const db = new Database(DB_PATH, { readonly: true });
    const brand = req.query.brand;
    const interval = req.query.interval || 'month'; // day, week, month

    let dateFormat;
    switch(interval) {
      case 'day':
        dateFormat = '%Y-%m-%d';
        break;
      case 'week':
        dateFormat = '%Y-W%W';
        break;
      default:
        dateFormat = '%Y-%m';
    }

    let query = `
      SELECT
        strftime('${dateFormat}', end_date) as period,
        COUNT(*) as auction_count,
        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_pct
      FROM auctions
      WHERE current_price > 0 AND end_date IS NOT NULL
    `;

    if (brand) {
      query += ` AND search_term = '${brand}'`;
    }

    query += `
      GROUP BY period
      ORDER BY period DESC
      LIMIT 50
    `;

    const data = db.prepare(query).all();
    db.close();

    res.json({
      success: true,
      interval: interval,
      brand: brand || 'all',
      data: data.reverse(), // Chronological order for charts
      generated_at: new Date().toISOString()
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message,
      data: []
    });
  }
});

// API: Get comparative brand analysis
app.get('/api/insights/compare-brands', (req, res) => {
  try {
    const db = new Database(DB_PATH, { readonly: true });

    const comparison = db.prepare(`
      SELECT
        search_term as brand,
        COUNT(*) as auction_count,
        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_pct,
        SUM(CASE WHEN current_price < estimate_low THEN 1 ELSE 0 END) as deals_count
      FROM auctions
      WHERE current_price > 0
      GROUP BY search_term
      HAVING COUNT(*) >= 5
      ORDER BY avg_savings_pct DESC
    `).all();

    db.close();

    res.json({
      success: true,
      count: comparison.length,
      data: comparison,
      generated_at: new Date().toISOString()
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message,
      data: []
    });
  }
});

// API: Get auction velocity metrics
app.get('/api/insights/velocity', (req, res) => {
  try {
    const db = new Database(DB_PATH, { readonly: true });

    // Calculate auction velocity (auctions ending soon)
    const velocity = db.prepare(`
      SELECT
        search_term as brand,
        COUNT(*) as total,
        SUM(CASE
          WHEN julianday(end_date) - julianday('now') <= 1 THEN 1
          ELSE 0
        END) as ending_today,
        SUM(CASE
          WHEN julianday(end_date) - julianday('now') <= 3 THEN 1
          ELSE 0
        END) as ending_3days,
        SUM(CASE
          WHEN julianday(end_date) - julianday('now') <= 7 THEN 1
          ELSE 0
        END) as ending_week,
        AVG(current_price) as avg_price
      FROM auctions
      WHERE end_date IS NOT NULL AND current_price > 0
      GROUP BY search_term
      ORDER BY ending_today DESC, ending_3days DESC
    `).all();

    db.close();

    res.json({
      success: true,
      data: velocity,
      generated_at: new Date().toISOString()
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message,
      data: []
    });
  }
});

// ============================================
// HEALTH CHECK & MONITORING ENDPOINTS
// ============================================

// Metrics storage
const metrics = {
  requestCount: 0,
  errorCount: 0,
  startTime: Date.now(),
  lastRequest: null,
  responseTimeHistory: []
};

// Request tracking middleware
app.use((req, res, next) => {
  const startTime = Date.now();
  metrics.requestCount++;
  metrics.lastRequest = new Date().toISOString();

  res.on('finish', () => {
    const duration = Date.now() - startTime;
    metrics.responseTimeHistory.push(duration);
    if (metrics.responseTimeHistory.length > 100) {
      metrics.responseTimeHistory.shift();
    }
    if (res.statusCode >= 500) {
      metrics.errorCount++;
    }
  });

  next();
});

// API: Health check endpoint
app.get('/health', (req, res) => {
  try {
    // Check database connectivity
    const db = new Database(DB_PATH, { readonly: true });
    const result = db.prepare('SELECT COUNT(*) as count FROM auctions').get();
    db.close();

    const uptime = Math.floor((Date.now() - metrics.startTime) / 1000);
    const avgResponseTime = metrics.responseTimeHistory.length > 0
      ? metrics.responseTimeHistory.reduce((a, b) => a + b, 0) / metrics.responseTimeHistory.length
      : 0;

    res.json({
      status: 'healthy',
      timestamp: new Date().toISOString(),
      uptime: uptime,
      database: {
        connected: true,
        recordCount: result.count
      },
      server: {
        port: PORT,
        nodeVersion: process.version,
        pid: process.pid,
        memory: process.memoryUsage(),
        cpu: process.cpuUsage()
      },
      metrics: {
        requestCount: metrics.requestCount,
        errorCount: metrics.errorCount,
        errorRate: metrics.requestCount > 0 ? (metrics.errorCount / metrics.requestCount * 100).toFixed(2) + '%' : '0%',
        avgResponseTime: Math.round(avgResponseTime) + 'ms',
        lastRequest: metrics.lastRequest
      }
    });
  } catch (error) {
    res.status(503).json({
      status: 'unhealthy',
      error: error.message,
      timestamp: new Date().toISOString()
    });
  }
});

// API: Readiness check endpoint
app.get('/ready', (req, res) => {
  try {
    // Check if database is accessible
    const db = new Database(DB_PATH, { readonly: true });
    db.prepare('SELECT 1').get();
    db.close();

    // Check if critical files exist
    const filesExist = fs.existsSync(DB_PATH) &&
                       fs.existsSync(CSV_PATH) &&
                       fs.existsSync(LOG_PATH);

    if (filesExist) {
      res.status(200).json({
        status: 'ready',
        timestamp: new Date().toISOString()
      });
    } else {
      throw new Error('Critical files missing');
    }
  } catch (error) {
    res.status(503).json({
      status: 'not ready',
      error: error.message,
      timestamp: new Date().toISOString()
    });
  }
});

// API: Metrics endpoint (Prometheus-style)
app.get('/metrics', (req, res) => {
  const uptime = Math.floor((Date.now() - metrics.startTime) / 1000);
  const avgResponseTime = metrics.responseTimeHistory.length > 0
    ? metrics.responseTimeHistory.reduce((a, b) => a + b, 0) / metrics.responseTimeHistory.length
    : 0;

  const memUsage = process.memoryUsage();

  res.set('Content-Type', 'text/plain');
  res.send(`# HELP auction_viewer_requests_total Total number of HTTP requests
# TYPE auction_viewer_requests_total counter
auction_viewer_requests_total ${metrics.requestCount}

# HELP auction_viewer_errors_total Total number of HTTP errors
# TYPE auction_viewer_errors_total counter
auction_viewer_errors_total ${metrics.errorCount}

# HELP auction_viewer_uptime_seconds Server uptime in seconds
# TYPE auction_viewer_uptime_seconds gauge
auction_viewer_uptime_seconds ${uptime}

# HELP auction_viewer_response_time_ms Average response time in milliseconds
# TYPE auction_viewer_response_time_ms gauge
auction_viewer_response_time_ms ${avgResponseTime.toFixed(2)}

# HELP auction_viewer_memory_bytes Memory usage in bytes
# TYPE auction_viewer_memory_bytes gauge
auction_viewer_memory_rss_bytes ${memUsage.rss}
auction_viewer_memory_heap_total_bytes ${memUsage.heapTotal}
auction_viewer_memory_heap_used_bytes ${memUsage.heapUsed}
auction_viewer_memory_external_bytes ${memUsage.external}

# HELP auction_viewer_process_info Process information
# TYPE auction_viewer_process_info gauge
auction_viewer_process_pid ${process.pid}
`);
});

// Graceful shutdown handler
process.on('SIGTERM', () => {
  console.log('SIGTERM received, shutting down gracefully...');
  server.close(() => {
    console.log('Server closed. Exiting process.');
    process.exit(0);
  });
});

process.on('SIGINT', () => {
  console.log('SIGINT received, shutting down gracefully...');
  server.close(() => {
    console.log('Server closed. Exiting process.');
    process.exit(0);
  });
});

const server = app.listen(PORT, '0.0.0.0', () => {
  console.log(`🎯 Auction Data Viewer running on http://45.61.58.125:${PORT}`);
  console.log(`📊 Dashboard: http://45.61.58.125:${PORT}`);
  console.log(`🔌 API: http://45.61.58.125:${PORT}/api/auctions`);
  console.log(`📈 Analytics: http://45.61.58.125:${PORT}/api/insights/*`);
  console.log(`❤️  Health: http://45.61.58.125:${PORT}/health`);
  console.log(`✅ Readiness: http://45.61.58.125:${PORT}/ready`);
  console.log(`📊 Metrics: http://45.61.58.125:${PORT}/metrics`);
});