← back to Handbag Auth Nextjs

auction-viewer/server-secured.js

1107 lines

const express = require('express');
const Database = require('better-sqlite3');
const cors = require('cors');
const helmet = require('helmet');
const rateLimit = require('express-rate-limit');
const { body, query, validationResult } = require('express-validator');
const compression = require('compression');
const winston = require('winston');
const fs = require('fs');
const path = require('path');
const crypto = require('crypto');
const { exec } = require('child_process');
const util = require('util');
require('dotenv').config();

const execPromise = util.promisify(exec);

const app = express();
const PORT = process.env.PORT || 7500;
const NODE_ENV = process.env.NODE_ENV || 'production';

// Security: Logging Configuration
const logger = winston.createLogger({
  level: process.env.LOG_LEVEL || 'info',
  format: winston.format.combine(
    winston.format.timestamp(),
    winston.format.errors({ stack: true }),
    winston.format.json()
  ),
  defaultMeta: { service: 'auction-viewer' },
  transports: [
    new winston.transports.File({
      filename: path.join(__dirname, 'logs', 'error.log'),
      level: 'error',
      maxsize: parseInt(process.env.LOG_ROTATION_SIZE) || 10485760,
      maxFiles: process.env.LOG_RETENTION_DAYS || 14
    }),
    new winston.transports.File({
      filename: path.join(__dirname, 'logs', 'security.log'),
      level: 'warn',
      maxsize: parseInt(process.env.LOG_ROTATION_SIZE) || 10485760,
      maxFiles: process.env.LOG_RETENTION_DAYS || 14
    }),
    new winston.transports.File({
      filename: path.join(__dirname, 'logs', 'combined.log'),
      maxsize: parseInt(process.env.LOG_ROTATION_SIZE) || 10485760,
      maxFiles: process.env.LOG_RETENTION_DAYS || 14
    })
  ]
});

// Add console logging in development
if (NODE_ENV !== 'production') {
  logger.add(new winston.transports.Console({
    format: winston.format.simple()
  }));
}

// Security: Helmet configuration with custom CSP
app.use(helmet({
  contentSecurityPolicy: {
    directives: {
      defaultSrc: ["'self'"],
      styleSrc: ["'self'", "'unsafe-inline'", "https://cdnjs.cloudflare.com", "https://fonts.googleapis.com"],
      scriptSrc: ["'self'", "'unsafe-inline'", "https://cdn.jsdelivr.net", "https://cdnjs.cloudflare.com"],
      fontSrc: ["'self'", "https://cdnjs.cloudflare.com", "https://fonts.gstatic.com"],
      imgSrc: ["'self'", "data:", "https:"],
      connectSrc: ["'self'"],
      objectSrc: ["'none'"],
      mediaSrc: ["'none'"],
      frameSrc: ["'none'"]
    }
  },
  hsts: {
    maxAge: parseInt(process.env.HSTS_MAX_AGE) || 31536000,
    includeSubDomains: true,
    preload: true
  },
  noSniff: true,
  xssFilter: true,
  referrerPolicy: { policy: 'strict-origin-when-cross-origin' },
  permittedCrossDomainPolicies: false
}));

// Security: CORS configuration
const allowedOrigins = process.env.ALLOWED_ORIGINS
  ? process.env.ALLOWED_ORIGINS.split(',')
  : ['http://45.61.58.125:7500'];

app.use(cors({
  origin: function(origin, callback) {
    // Allow requests with no origin (like mobile apps or curl requests)
    if (!origin) return callback(null, true);

    if (allowedOrigins.indexOf(origin) !== -1) {
      callback(null, true);
    } else {
      logger.warn('CORS violation attempt', { origin, ip: origin });
      callback(new Error('Not allowed by CORS'));
    }
  },
  credentials: true,
  optionsSuccessStatus: 200
}));

// Security: Body parsing with size limits
app.use(express.json({ limit: '10mb' }));
app.use(express.urlencoded({ extended: true, limit: '10mb' }));

// Performance: Compression
app.use(compression());

// Security: Rate limiting configurations
const generalLimiter = rateLimit({
  windowMs: parseInt(process.env.API_RATE_WINDOW_MS) || 15 * 60 * 1000, // 15 minutes
  max: parseInt(process.env.API_RATE_LIMIT) || 100,
  message: 'Too many requests from this IP, please try again later.',
  standardHeaders: true,
  legacyHeaders: false,
  handler: (req, res) => {
    logger.warn('Rate limit exceeded', {
      ip: req.ip,
      endpoint: req.path,
      method: req.method
    });
    res.status(429).json({
      success: false,
      error: 'Too many requests from this IP, please try again later.'
    });
  }
});

const strictLimiter = rateLimit({
  windowMs: 5 * 60 * 1000, // 5 minutes
  max: parseInt(process.env.STRICT_RATE_LIMIT) || 20,
  skipSuccessfulRequests: false
});

const downloadLimiter = rateLimit({
  windowMs: 60 * 60 * 1000, // 1 hour
  max: 10,
  message: 'Download limit exceeded. Please try again later.'
});

// Apply general rate limiting to all routes
app.use('/api/', generalLimiter);

// Security: Request logging middleware
app.use((req, res, next) => {
  const start = Date.now();

  // Log security-relevant requests
  if (req.method !== 'GET' || req.path.includes('download') || req.path.includes('logs')) {
    logger.info('Request', {
      method: req.method,
      path: req.path,
      ip: req.ip,
      userAgent: req.get('user-agent')
    });
  }

  res.on('finish', () => {
    const duration = Date.now() - start;
    if (res.statusCode >= 400) {
      logger.warn('Request failed', {
        method: req.method,
        path: req.path,
        status: res.statusCode,
        duration,
        ip: req.ip
      });
    }
  });

  next();
});

// Security: Static files with path validation
app.use(express.static(__dirname, {
  dotfiles: 'deny',
  index: false,
  setHeaders: (res, path) => {
    // Additional security headers for static files
    res.setHeader('X-Content-Type-Options', 'nosniff');
    res.setHeader('Cache-Control', 'public, max-age=3600');
  }
}));

// Serve index.html for root route
app.get('/', (req, res) => {
  res.sendFile(path.join(__dirname, 'index.html'));
});

// Database paths with validation
const DB_PATH = process.env.DB_PATH || path.join(__dirname, '..', 'data', 'auction-history', 'auctions.db');
const CSV_PATH = process.env.CSV_PATH || path.join(__dirname, '..', 'data', 'auction-history', 'auction-history.csv');
const LOG_PATH = process.env.LOG_PATH || path.join(__dirname, '..', 'data', 'auction-history', 'scraper.log');
const ANALYTICS_SCRIPT = path.join(__dirname, 'analytics-engine.py');

// Security: Path traversal prevention
const isPathSafe = (filePath, basePath) => {
  const resolvedPath = path.resolve(filePath);
  const resolvedBase = path.resolve(basePath);
  return resolvedPath.startsWith(resolvedBase);
};

// Security: Database helper with prepared statements
const executeQuery = (queryFn, errorMessage = 'Database query failed') => {
  try {
    const db = new Database(DB_PATH, {
      readonly: true,
      fileMustExist: true,
      timeout: 5000
    });
    const result = queryFn(db);
    db.close();
    return result;
  } catch (error) {
    logger.error(errorMessage, { error: error.message });
    throw new Error(NODE_ENV === 'production' ? errorMessage : error.message);
  }
};

// Security: Input validation middleware
const handleValidationErrors = (req, res, next) => {
  const errors = validationResult(req);
  if (!errors.isEmpty()) {
    logger.warn('Validation failed', {
      errors: errors.array(),
      endpoint: req.path,
      ip: req.ip
    });
    return res.status(400).json({
      success: false,
      error: 'Invalid input parameters',
      details: NODE_ENV === 'production' ? undefined : errors.array()
    });
  }
  next();
};

// API: Get all auctions with pagination
app.get('/api/auctions',
  [
    query('limit').optional().isInt({ min: 1, max: 1000 }).toInt(),
    query('offset').optional().isInt({ min: 0 }).toInt(),
    handleValidationErrors
  ],
  (req, res) => {
    try {
      const limit = req.query.limit || 1000;
      const offset = req.query.offset || 0;

      const auctions = executeQuery(db => {
        const stmt = db.prepare(`
          SELECT * FROM auctions
          ORDER BY created_at DESC
          LIMIT ? OFFSET ?
        `);
        return stmt.all(limit, offset);
      }, 'Failed to fetch auctions');

      res.json({
        success: true,
        count: auctions.length,
        limit,
        offset,
        data: auctions
      });
    } catch (error) {
      res.status(500).json({
        success: false,
        error: error.message,
        data: []
      });
    }
  }
);

// API: Get best value auctions with strict validation
app.get('/api/best-values',
  strictLimiter,
  [
    query('sort').optional().isIn(['percent', 'dollar']).trim(),
    query('limit').optional().isInt({ min: 1, max: 100 }).toInt(),
    handleValidationErrors
  ],
  (req, res) => {
    try {
      const sortBy = req.query.sort || 'percent';
      const limit = req.query.limit || 100;

      // Use parameterized query
      const auctions = executeQuery(db => {
        const orderBy = sortBy === 'dollar' ? 'savings_amount' : 'savings_percent';

        // Validate orderBy to prevent SQL injection
        if (!['savings_amount', 'savings_percent'].includes(orderBy)) {
          throw new Error('Invalid sort parameter');
        }

        const stmt = 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 ?
        `);
        return stmt.all(limit);
      }, 'Failed to fetch best value auctions');

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

// API: Get statistics with caching headers
app.get('/api/stats', (req, res) => {
  try {
    const stats = executeQuery(db => {
      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
        LIMIT 20
      `).all();

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

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

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

      return {
        total: total.count,
        byBrand,
        byAuctionHouse,
        avgPrice: avgPrice.avg_price || 0,
        priceRange: priceRange || { min_price: 0, max_price: 0 }
      };
    }, 'Failed to fetch statistics');

    res.setHeader('Cache-Control', 'public, max-age=300'); // Cache for 5 minutes
    res.json({
      success: true,
      stats
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
});

// API: Get recent logs with strict access control
app.get('/api/logs',
  strictLimiter,
  [
    query('lines').optional().isInt({ min: 1, max: 500 }).toInt(),
    handleValidationErrors
  ],
  (req, res) => {
    try {
      // Validate path to prevent directory traversal
      if (!isPathSafe(LOG_PATH, path.dirname(LOG_PATH))) {
        throw new Error('Invalid log path');
      }

      // Check file exists and is readable
      if (!fs.existsSync(LOG_PATH)) {
        return res.json({
          success: false,
          error: 'Log file not found',
          logs: []
        });
      }

      const stats = fs.statSync(LOG_PATH);

      // Limit file size to prevent memory issues
      if (stats.size > 50 * 1024 * 1024) { // 50MB limit
        logger.warn('Log file too large', { size: stats.size });
        return res.json({
          success: false,
          error: 'Log file too large',
          logs: []
        });
      }

      const lines = req.query.lines || 100;
      const logs = fs.readFileSync(LOG_PATH, 'utf8')
        .split('\n')
        .filter(line => line.trim())
        .slice(-lines)
        .reverse();

      logger.info('Logs accessed', {
        ip: req.ip,
        lines: logs.length
      });

      res.json({
        success: true,
        logs
      });
    } catch (error) {
      logger.error('Failed to read logs', { error: error.message });
      res.status(500).json({
        success: false,
        error: 'Failed to read logs',
        logs: []
      });
    }
  }
);

// API: Get CSV data with validation
app.get('/api/csv',
  strictLimiter,
  (req, res) => {
    try {
      // Validate path
      if (!isPathSafe(CSV_PATH, path.dirname(CSV_PATH))) {
        throw new Error('Invalid CSV path');
      }

      if (!fs.existsSync(CSV_PATH)) {
        return res.json({
          success: false,
          error: 'CSV file not found',
          data: ''
        });
      }

      const stats = fs.statSync(CSV_PATH);

      // Limit file size
      if (stats.size > 10 * 1024 * 1024) { // 10MB limit
        return res.json({
          success: false,
          error: 'CSV file too large',
          data: ''
        });
      }

      const csvData = fs.readFileSync(CSV_PATH, 'utf8');

      res.setHeader('Cache-Control', 'public, max-age=300');
      res.json({
        success: true,
        data: csvData
      });
    } catch (error) {
      logger.error('Failed to read CSV', { error: error.message });
      res.status(500).json({
        success: false,
        error: 'Failed to read CSV',
        data: ''
      });
    }
  }
);

// API: Download CSV with rate limiting
app.get('/api/download-csv',
  downloadLimiter,
  (req, res) => {
    try {
      // Validate path
      if (!isPathSafe(CSV_PATH, path.dirname(CSV_PATH))) {
        throw new Error('Invalid CSV path');
      }

      if (!fs.existsSync(CSV_PATH)) {
        return res.status(404).json({
          success: false,
          error: 'CSV file not found'
        });
      }

      const stats = fs.statSync(CSV_PATH);

      // Check file size
      if (stats.size > 50 * 1024 * 1024) { // 50MB limit
        return res.status(413).json({
          success: false,
          error: 'File too large for download'
        });
      }

      logger.info('CSV downloaded', {
        ip: req.ip,
        size: stats.size
      });

      // Set secure headers for download
      res.setHeader('Content-Type', 'text/csv');
      res.setHeader('Content-Disposition', 'attachment; filename="auction-history.csv"');
      res.setHeader('X-Content-Type-Options', 'nosniff');

      // Stream file instead of loading into memory
      const stream = fs.createReadStream(CSV_PATH);
      stream.pipe(res);

      stream.on('error', (error) => {
        logger.error('Download stream error', { error: error.message });
        if (!res.headersSent) {
          res.status(500).json({
            success: false,
            error: 'Download failed'
          });
        }
      });
    } catch (error) {
      logger.error('Download failed', { error: error.message });
      res.status(500).json({
        success: false,
        error: 'Download failed'
      });
    }
  }
);

// API: System status with health check
app.get('/api/status', (req, res) => {
  try {
    const status = {
      service: 'auction-viewer',
      version: '1.0.0',
      environment: NODE_ENV,
      uptime: process.uptime(),
      memory: process.memoryUsage(),
      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,
      timestamp: new Date().toISOString()
    };

    res.setHeader('Cache-Control', 'no-cache');
    res.json({
      success: true,
      status
    });
  } catch (error) {
    logger.error('Status check failed', { error: error.message });
    res.status(500).json({
      success: false,
      error: 'Status check failed'
    });
  }
});

// API: Health check endpoint
app.get('/health', (req, res) => {
  res.status(200).json({
    status: 'healthy',
    timestamp: new Date().toISOString()
  });
});

// ============================================
// 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, { timeout: 30000 });

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

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

// API: Get top deals by Deal Score algorithm
app.get('/api/insights/deal-scores',
  [
    query('limit').optional().isInt({ min: 1, max: 100 }).toInt(),
    handleValidationErrors
  ],
  async (req, res) => {
    try {
      const limit = req.query.limit || 50;
      const deals = await runAnalytics('deal-scores', [limit]);

      res.setHeader('Cache-Control', 'public, max-age=300');
      res.json({
        success: true,
        count: deals.length,
        data: deals,
        generated_at: new Date().toISOString()
      });
    } catch (error) {
      res.status(500).json({
        success: false,
        error: 'Failed to calculate deal scores',
        data: []
      });
    }
  }
);

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

    res.setHeader('Cache-Control', 'public, max-age=600'); // 10 minutes
    res.json({
      success: true,
      data: trends,
      generated_at: new Date().toISOString()
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: 'Failed to generate trends',
      data: {}
    });
  }
});

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

    res.setHeader('Cache-Control', 'public, max-age=600');
    res.json({
      success: true,
      count: anomalies.length,
      data: anomalies,
      generated_at: new Date().toISOString()
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: 'Failed to detect anomalies',
      data: []
    });
  }
});

// API: Predict final price for an auction
app.get('/api/insights/predict/:auction_id',
  [
    query('auction_id').optional().isAlphanumeric(),
    handleValidationErrors
  ],
  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: 'Failed to predict price',
        data: null
      });
    }
  }
);

// API: Get personalized recommendations
app.get('/api/insights/recommendations',
  [
    query('budget').optional().isFloat({ min: 0, max: 1000000 }).toFloat(),
    handleValidationErrors
  ],
  async (req, res) => {
    try {
      const budget = req.query.budget || null;
      const args = budget ? [budget] : [];

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

      res.setHeader('Cache-Control', 'public, max-age=300');
      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: 'Failed to generate recommendations',
        data: []
      });
    }
  }
);

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

    res.setHeader('Cache-Control', 'public, max-age=600');
    res.json({
      success: true,
      data: insights,
      generated_at: new Date().toISOString()
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: 'Failed to generate market insights',
      data: null
    });
  }
});

// API: Get brand-specific analytics
app.get('/api/insights/brand/:brand',
  [
    query('brand').optional().trim().escape(),
    handleValidationErrors
  ],
  (req, res) => {
    try {
      const brand = req.params.brand;

      const result = executeQuery(db => {
        // 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);

        return {
          statistics: stats,
          recent_auctions: recent,
          price_history: history
        };
      }, 'Failed to fetch brand analytics');

      res.setHeader('Cache-Control', 'public, max-age=300');
      res.json({
        success: true,
        brand: brand,
        ...result,
        generated_at: new Date().toISOString()
      });
    } catch (error) {
      res.status(500).json({
        success: false,
        error: 'Failed to fetch brand analytics'
      });
    }
  }
);

// API: Get time-series data for charts
app.get('/api/insights/time-series',
  [
    query('brand').optional().trim().escape(),
    query('interval').optional().isIn(['day', 'week', 'month']),
    handleValidationErrors
  ],
  (req, res) => {
    try {
      const brand = req.query.brand;
      const interval = req.query.interval || 'month';

      const data = executeQuery(db => {
        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
        `;

        const params = [];
        if (brand) {
          query += ` AND search_term = ?`;
          params.push(brand);
        }

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

        const stmt = db.prepare(query);
        return params.length ? stmt.all(...params) : stmt.all();
      }, 'Failed to fetch time series');

      res.setHeader('Cache-Control', 'public, max-age=600');
      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: 'Failed to fetch time series',
        data: []
      });
    }
  }
);

// API: Get comparative brand analysis
app.get('/api/insights/compare-brands', (req, res) => {
  try {
    const comparison = executeQuery(db => {
      return 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();
    }, 'Failed to compare brands');

    res.setHeader('Cache-Control', 'public, max-age=600');
    res.json({
      success: true,
      count: comparison.length,
      data: comparison,
      generated_at: new Date().toISOString()
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: 'Failed to compare brands',
      data: []
    });
  }
});

// API: Get auction velocity metrics
app.get('/api/insights/velocity', (req, res) => {
  try {
    const velocity = executeQuery(db => {
      return 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();
    }, 'Failed to calculate velocity');

    res.setHeader('Cache-Control', 'public, max-age=300');
    res.json({
      success: true,
      data: velocity,
      generated_at: new Date().toISOString()
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: 'Failed to calculate velocity',
      data: []
    });
  }
});

// Security: 404 handler
app.use((req, res) => {
  logger.warn('404 Not Found', {
    path: req.path,
    method: req.method,
    ip: req.ip
  });
  res.status(404).json({
    success: false,
    error: 'Endpoint not found'
  });
});

// Security: Global error handler
app.use((err, req, res, next) => {
  logger.error('Unhandled error', {
    error: err.message,
    stack: NODE_ENV === 'production' ? undefined : err.stack,
    path: req.path,
    method: req.method,
    ip: req.ip
  });

  // Don't leak error details in production
  const message = NODE_ENV === 'production'
    ? 'Internal server error'
    : err.message;

  res.status(err.status || 500).json({
    success: false,
    error: message
  });
});

// Graceful shutdown
const gracefulShutdown = () => {
  logger.info('Received shutdown signal, closing server gracefully...');

  server.close(() => {
    logger.info('Server closed');
    process.exit(0);
  });

  // Force close after 30 seconds
  setTimeout(() => {
    logger.error('Could not close connections in time, forcefully shutting down');
    process.exit(1);
  }, 30000);
};

process.on('SIGTERM', gracefulShutdown);
process.on('SIGINT', gracefulShutdown);

// Start server
const server = app.listen(PORT, '0.0.0.0', () => {
  logger.info('Server started', {
    port: PORT,
    environment: NODE_ENV,
    url: `http://45.61.58.125:${PORT}`
  });

  console.log(`
╔═══════════════════════════════════════════════════════╗
║                LUXVAULT AUCTION VIEWER                 ║
║            SECURED + ANALYTICS ENABLED                 ║
╠═══════════════════════════════════════════════════════╣
║  🔒 Security Features:                                 ║
║     ✓ Helmet.js (CSP, HSTS, XSS Protection)          ║
║     ✓ Rate Limiting (API Protection)                  ║
║     ✓ Input Validation (express-validator)           ║
║     ✓ SQL Injection Protection (Prepared Statements)  ║
║     ✓ Path Traversal Prevention                       ║
║     ✓ CORS Restrictions                              ║
║     ✓ Security Logging (Winston)                     ║
║     ✓ Error Sanitization                             ║
║                                                       ║
║  🌐 Server: http://45.61.58.125:${PORT}                 ║
║  📊 Dashboard: http://45.61.58.125:${PORT}              ║
║  🔌 API Base: http://45.61.58.125:${PORT}/api           ║
║                                                       ║
║  📈 Core Endpoints:                                   ║
║     • GET  /api/auctions     (Rate limited)          ║
║     • GET  /api/best-values  (Strict limit)          ║
║     • GET  /api/stats        (Cached)                ║
║     • GET  /api/status       (Health check)          ║
║     • GET  /health          (Monitoring)             ║
║                                                       ║
║  🧠 Analytics Endpoints (NEW):                        ║
║     • GET  /api/insights/deal-scores                 ║
║     • GET  /api/insights/trends                      ║
║     • GET  /api/insights/anomalies                   ║
║     • GET  /api/insights/market                      ║
║     • GET  /api/insights/recommendations             ║
║     • GET  /api/insights/predict/:id                 ║
║     • GET  /api/insights/brand/:brand                ║
║     • GET  /api/insights/time-series                 ║
║     • GET  /api/insights/compare-brands              ║
║     • GET  /api/insights/velocity                    ║
╚═══════════════════════════════════════════════════════╝
  `);
});

module.exports = app;