← back to Handbag Auth Nextjs

auction-viewer/server-optimized.js

1536 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';

// ============================================
// PERFORMANCE: In-Memory Query Cache
// ============================================
class QueryCache {
  constructor(maxSize = 100, ttl = 300000) { // 5 minutes default TTL
    this.cache = new Map();
    this.maxSize = maxSize;
    this.ttl = ttl;
  }

  get(key) {
    const item = this.cache.get(key);
    if (!item) return null;

    if (Date.now() > item.expires) {
      this.cache.delete(key);
      return null;
    }

    item.hits++;
    return item.value;
  }

  set(key, value, customTtl) {
    if (this.cache.size >= this.maxSize) {
      // Remove oldest entry
      const firstKey = this.cache.keys().next().value;
      this.cache.delete(firstKey);
    }

    this.cache.set(key, {
      value,
      expires: Date.now() + (customTtl || this.ttl),
      hits: 0,
      created: Date.now()
    });
  }

  invalidate(pattern) {
    if (!pattern) {
      this.cache.clear();
      return;
    }

    const regex = new RegExp(pattern);
    for (const key of this.cache.keys()) {
      if (regex.test(key)) {
        this.cache.delete(key);
      }
    }
  }

  stats() {
    let totalHits = 0;
    for (const item of this.cache.values()) {
      totalHits += item.hits;
    }

    return {
      size: this.cache.size,
      maxSize: this.maxSize,
      totalHits,
      hitRate: totalHits / this.cache.size || 0
    };
  }
}

const queryCache = new QueryCache(100, 300000); // 100 items, 5 min TTL

// ============================================
// 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-optimized' },
  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
    })
  ]
});

if (NODE_ENV !== 'production') {
  logger.add(new winston.transports.Console({
    format: winston.format.simple()
  }));
}

// ============================================
// SECURITY MIDDLEWARE
// ============================================
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
}));

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

app.use(cors({
  origin: function(origin, callback) {
    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
}));

app.use(express.json({ limit: '10mb' }));
app.use(express.urlencoded({ extended: true, limit: '10mb' }));

// High compression level
app.use(compression({
  level: 9,
  threshold: 0,
  filter: (req, res) => {
    if (req.headers['x-no-compression']) {
      return false;
    }
    return compression.filter(req, res);
  }
}));

// ============================================
// RATE LIMITING
// ============================================
const generalLimiter = rateLimit({
  windowMs: parseInt(process.env.API_RATE_WINDOW_MS) || 15 * 60 * 1000,
  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,
  max: parseInt(process.env.STRICT_RATE_LIMIT) || 20,
  skipSuccessfulRequests: false
});

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

app.use('/api/', generalLimiter);

// Request logging with performance metrics
app.use((req, res, next) => {
  const start = Date.now();

  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;

    // Log slow queries
    if (duration > 1000) {
      logger.warn('Slow request', {
        method: req.method,
        path: req.path,
        duration,
        status: res.statusCode
      });
    }

    if (res.statusCode >= 400) {
      logger.warn('Request failed', {
        method: req.method,
        path: req.path,
        status: res.statusCode,
        duration,
        ip: req.ip
      });
    }
  });

  next();
});

app.use(express.static(__dirname, {
  dotfiles: 'deny',
  index: false,
  setHeaders: (res, path) => {
    res.setHeader('X-Content-Type-Options', 'nosniff');
    res.setHeader('Cache-Control', 'public, max-age=3600');
  }
}));

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

// ============================================
// DATABASE CONFIGURATION
// ============================================
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');

const isPathSafe = (filePath, basePath) => {
  const resolvedPath = path.resolve(filePath);
  const resolvedBase = path.resolve(basePath);
  return resolvedPath.startsWith(resolvedBase);
};

// Database connection pool
let dbPool = null;

const getDbConnection = () => {
  if (!dbPool) {
    dbPool = new Database(DB_PATH, {
      readonly: true,
      fileMustExist: true,
      timeout: 5000
    });

    // Enable performance optimizations
    dbPool.pragma('journal_mode = WAL');
    dbPool.pragma('synchronous = NORMAL');
    dbPool.pragma('cache_size = -64000'); // 64MB cache
    dbPool.pragma('temp_store = MEMORY');
    dbPool.pragma('mmap_size = 30000000000');
  }
  return dbPool;
};

const executeQuery = (queryFn, errorMessage = 'Database query failed', useCache = false, cacheKey = null) => {
  try {
    // Check cache first
    if (useCache && cacheKey) {
      const cached = queryCache.get(cacheKey);
      if (cached) {
        return cached;
      }
    }

    const db = getDbConnection();
    const result = queryFn(db);

    // Cache the result
    if (useCache && cacheKey) {
      queryCache.set(cacheKey, result);
    }

    return result;
  } catch (error) {
    logger.error(errorMessage, { error: error.message });
    throw new Error(NODE_ENV === 'production' ? errorMessage : error.message);
  }
};

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

// ============================================
// ENHANCED API ENDPOINTS WITH FULL FEATURES
// ============================================

/**
 * GET /api/v1/auctions
 * Enhanced with: pagination, filtering, sorting, full-text search
 * Query params:
 * - limit: number of results (1-1000, default 50)
 * - offset: pagination offset (default 0)
 * - page: page number (alternative to offset)
 * - brand: filter by brand/search_term
 * - auction_house: filter by auction house
 * - min_price: minimum current price
 * - max_price: maximum current price
 * - start_date: filter by created_at >= date
 * - end_date: filter by created_at <= date
 * - search: full-text search across title, brand, auction house
 * - sort: field to sort by (price, date, savings, deal_score)
 * - order: asc or desc (default desc)
 */
app.get('/api/v1/auctions',
  [
    query('limit').optional().isInt({ min: 1, max: 1000 }).toInt(),
    query('offset').optional().isInt({ min: 0 }).toInt(),
    query('page').optional().isInt({ min: 1 }).toInt(),
    query('brand').optional().trim().escape(),
    query('auction_house').optional().trim().escape(),
    query('min_price').optional().isFloat({ min: 0 }).toFloat(),
    query('max_price').optional().isFloat({ min: 0 }).toFloat(),
    query('start_date').optional().isISO8601(),
    query('end_date').optional().isISO8601(),
    query('search').optional().trim(),
    query('sort').optional().isIn(['price', 'date', 'savings', 'deal_score', 'relevance']),
    query('order').optional().isIn(['asc', 'desc']),
    handleValidationErrors
  ],
  (req, res) => {
    try {
      const limit = req.query.limit || 50;
      const page = req.query.page || 1;
      const offset = req.query.offset || (page - 1) * limit;
      const brand = req.query.brand;
      const auctionHouse = req.query.auction_house;
      const minPrice = req.query.min_price;
      const maxPrice = req.query.max_price;
      const startDate = req.query.start_date;
      const endDate = req.query.end_date;
      const search = req.query.search;
      const sort = req.query.sort || 'date';
      const order = req.query.order || 'desc';

      // Build cache key
      const cacheKey = `auctions:${JSON.stringify(req.query)}`;

      const result = executeQuery(db => {
        let whereClauses = [];
        let params = [];

        // Full-text search
        if (search) {
          whereClauses.push(`id IN (SELECT rowid FROM auctions_fts WHERE auctions_fts MATCH ?)`);
          params.push(search);
        }

        // Filters
        if (brand) {
          whereClauses.push('search_term = ?');
          params.push(brand);
        }

        if (auctionHouse) {
          whereClauses.push('auction_house = ?');
          params.push(auctionHouse);
        }

        if (minPrice !== undefined) {
          whereClauses.push('current_price >= ?');
          params.push(minPrice);
        }

        if (maxPrice !== undefined) {
          whereClauses.push('current_price <= ?');
          params.push(maxPrice);
        }

        if (startDate) {
          whereClauses.push('created_at >= ?');
          params.push(startDate);
        }

        if (endDate) {
          whereClauses.push('created_at <= ?');
          params.push(endDate);
        }

        // Build ORDER BY clause
        let orderByClause;
        switch (sort) {
          case 'price':
            orderByClause = `current_price ${order.toUpperCase()}`;
            break;
          case 'savings':
            orderByClause = `(estimate_low - current_price) ${order.toUpperCase()}`;
            break;
          case 'deal_score':
            orderByClause = `
              CASE
                WHEN estimate_low > 0 AND current_price > 0
                THEN ((estimate_low - current_price) / estimate_low * 100)
                ELSE 0
              END ${order.toUpperCase()}`;
            break;
          case 'relevance':
            orderByClause = search ? 'rank' : `created_at ${order.toUpperCase()}`;
            break;
          default:
            orderByClause = `created_at ${order.toUpperCase()}`;
        }

        // Build main query
        const whereClause = whereClauses.length > 0 ? `WHERE ${whereClauses.join(' AND ')}` : '';

        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
          ${whereClause}
          ORDER BY ${orderByClause}
          LIMIT ? OFFSET ?
        `;

        const countQuery = `
          SELECT COUNT(*) as total
          FROM auctions
          ${whereClause}
        `;

        params.push(limit, offset);
        const data = db.prepare(dataQuery).all(...params);

        params.pop(); // Remove limit
        params.pop(); // Remove offset
        const totalResult = db.prepare(countQuery).get(...params);

        return {
          data,
          total: totalResult.total,
          limit,
          offset,
          page,
          totalPages: Math.ceil(totalResult.total / limit)
        };
      }, 'Failed to fetch auctions', true, cacheKey);

      res.setHeader('Cache-Control', 'public, max-age=60'); // 1 minute cache
      res.json({
        success: true,
        count: result.data.length,
        ...result,
        filters: {
          brand,
          auction_house: auctionHouse,
          min_price: minPrice,
          max_price: maxPrice,
          start_date: startDate,
          end_date: endDate,
          search
        },
        sort: { field: sort, order }
      });
    } catch (error) {
      res.status(500).json({
        success: false,
        error: error.message,
        data: []
      });
    }
  }
);

// Legacy endpoint for backward compatibility
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: []
      });
    }
  }
);

/**
 * GET /api/v1/auctions/:id
 * Get single auction by ID
 */
app.get('/api/v1/auctions/:id',
  [
    query('id').optional().isInt().toInt(),
    handleValidationErrors
  ],
  (req, res) => {
    try {
      const id = req.params.id;
      const cacheKey = `auction:${id}`;

      const auction = executeQuery(db => {
        return 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 id = ?
        `).get(id);
      }, 'Failed to fetch auction', true, cacheKey);

      if (!auction) {
        return res.status(404).json({
          success: false,
          error: 'Auction not found'
        });
      }

      res.setHeader('Cache-Control', 'public, max-age=3600'); // 1 hour
      res.json({
        success: true,
        data: auction
      });
    } catch (error) {
      res.status(500).json({
        success: false,
        error: error.message
      });
    }
  }
);

/**
 * GET /api/v1/search
 * Full-text search endpoint
 */
app.get('/api/v1/search',
  [
    query('q').notEmpty().trim(),
    query('limit').optional().isInt({ min: 1, max: 100 }).toInt(),
    handleValidationErrors
  ],
  (req, res) => {
    try {
      const searchQuery = req.query.q;
      const limit = req.query.limit || 50;
      const cacheKey = `search:${searchQuery}:${limit}`;

      const results = executeQuery(db => {
        return db.prepare(`
          SELECT
            a.*,
            CASE
              WHEN a.estimate_low > 0 AND a.current_price > 0
              THEN ((a.estimate_low - a.current_price) / a.estimate_low * 100)
              ELSE 0
            END as savings_percent
          FROM auctions a
          WHERE a.id IN (
            SELECT rowid FROM auctions_fts
            WHERE auctions_fts MATCH ?
          )
          ORDER BY a.created_at DESC
          LIMIT ?
        `).all(searchQuery, limit);
      }, 'Search failed', true, cacheKey);

      res.setHeader('Cache-Control', 'public, max-age=300');
      res.json({
        success: true,
        query: searchQuery,
        count: results.length,
        data: results
      });
    } catch (error) {
      res.status(500).json({
        success: false,
        error: 'Search failed',
        data: []
      });
    }
  }
);

// Best values endpoint (maintained for compatibility)
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;
      const cacheKey = `best-values:${sortBy}:${limit}`;

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

        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', true, cacheKey);

      res.setHeader('Cache-Control', 'public, max-age=300');
      res.json({
        success: true,
        count: auctions.length,
        data: auctions,
        sortedBy: sortBy
      });
    } catch (error) {
      res.status(500).json({
        success: false,
        error: error.message,
        data: []
      });
    }
  }
);

// Statistics endpoint with caching
app.get('/api/stats', (req, res) => {
  try {
    const cacheKey = 'stats:all';

    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', true, cacheKey);

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

// Logs endpoint
app.get('/api/logs',
  strictLimiter,
  [
    query('lines').optional().isInt({ min: 1, max: 500 }).toInt(),
    handleValidationErrors
  ],
  (req, res) => {
    try {
      if (!isPathSafe(LOG_PATH, path.dirname(LOG_PATH))) {
        throw new Error('Invalid log path');
      }

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

      const stats = fs.statSync(LOG_PATH);

      if (stats.size > 50 * 1024 * 1024) {
        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: []
      });
    }
  }
);

// CSV endpoints
app.get('/api/csv', strictLimiter, (req, res) => {
  try {
    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);

    if (stats.size > 10 * 1024 * 1024) {
      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: ''
    });
  }
});

app.get('/api/download-csv', downloadLimiter, (req, res) => {
  try {
    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);

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

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

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

    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'
    });
  }
});

// Status and health endpoints
app.get('/api/status', (req, res) => {
  try {
    const cacheStats = queryCache.stats();

    const status = {
      service: 'auction-viewer-optimized',
      version: '2.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,
      cache: cacheStats,
      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'
    });
  }
});

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

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

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;
  }
}

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: []
      });
    }
  }
);

app.get('/api/insights/trends', async (req, res) => {
  try {
    const trends = await runAnalytics('trends');

    res.setHeader('Cache-Control', 'public, max-age=600');
    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: {}
    });
  }
});

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: []
    });
  }
});

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

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: []
      });
    }
  }
);

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

app.get('/api/insights/brand/:brand',
  [
    query('brand').optional().trim().escape(),
    handleValidationErrors
  ],
  (req, res) => {
    try {
      const brand = req.params.brand;
      const cacheKey = `brand:${brand}`;

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

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

        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', true, cacheKey);

      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'
      });
    }
  }
);

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 cacheKey = `time-series:${brand}:${interval}`;

      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', true, cacheKey);

      res.setHeader('Cache-Control', 'public, max-age=600');
      res.json({
        success: true,
        interval: interval,
        brand: brand || 'all',
        data: data.reverse(),
        generated_at: new Date().toISOString()
      });
    } catch (error) {
      res.status(500).json({
        success: false,
        error: 'Failed to fetch time series',
        data: []
      });
    }
  }
);

app.get('/api/insights/compare-brands', (req, res) => {
  try {
    const cacheKey = 'compare-brands';

    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', true, cacheKey);

    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: []
    });
  }
});

app.get('/api/insights/velocity', (req, res) => {
  try {
    const cacheKey = 'velocity';

    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', true, cacheKey);

    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: []
    });
  }
});

// Cache management endpoint (admin only in production)
app.post('/api/cache/invalidate',
  strictLimiter,
  [
    body('pattern').optional().trim(),
    handleValidationErrors
  ],
  (req, res) => {
    try {
      const pattern = req.body.pattern;
      queryCache.invalidate(pattern);

      logger.info('Cache invalidated', { pattern, ip: req.ip });

      res.json({
        success: true,
        message: pattern ? `Cache invalidated for pattern: ${pattern}` : 'All cache cleared',
        stats: queryCache.stats()
      });
    } catch (error) {
      res.status(500).json({
        success: false,
        error: 'Cache invalidation failed'
      });
    }
  }
);

// 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'
  });
});

// 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
  });

  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');
    if (dbPool) {
      dbPool.close();
    }
    process.exit(0);
  });

  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 v2.0                 ║
║        OPTIMIZED + SECURED + ANALYTICS                ║
╠═══════════════════════════════════════════════════════╣
║  🚀 Performance Optimizations:                        ║
║     ✓ Query Result Caching (In-Memory)               ║
║     ✓ Database Connection Pooling                    ║
║     ✓ SQLite WAL Mode + Optimizations                ║
║     ✓ Response Compression (Level 9)                 ║
║     ✓ Prepared Statement Reuse                       ║
║                                                       ║
║  🔒 Security Features:                                ║
║     ✓ Helmet.js (CSP, HSTS, XSS Protection)          ║
║     ✓ Rate Limiting (API Protection)                  ║
║     ✓ Input Validation (express-validator)           ║
║     ✓ SQL Injection Protection                       ║
║     ✓ Path Traversal Prevention                       ║
║                                                       ║
║  🌐 Server: http://45.61.58.125:${PORT}                 ║
║                                                       ║
║  📈 Enhanced API v1 Endpoints:                        ║
║     • GET  /api/v1/auctions                          ║
║       ├─ Pagination: ?limit=50&page=2                ║
║       ├─ Filters: ?brand=Hermes&min_price=1000       ║
║       ├─ Search: ?search=birkin+leather              ║
║       └─ Sort: ?sort=price&order=desc                ║
║                                                       ║
║     • GET  /api/v1/auctions/:id                      ║
║     • GET  /api/v1/search?q=query                    ║
║     • POST /api/cache/invalidate                     ║
║                                                       ║
║  🧠 Analytics Endpoints:                              ║
║     • GET  /api/insights/deal-scores                 ║
║     • GET  /api/insights/trends                      ║
║     • GET  /api/insights/anomalies                   ║
║     • GET  /api/insights/market                      ║
║     • GET  /api/insights/recommendations             ║
║     • GET  /api/insights/brand/:brand                ║
║     • GET  /api/insights/time-series                 ║
║     • GET  /api/insights/compare-brands              ║
║     • GET  /api/insights/velocity                    ║
║                                                       ║
║  📊 Legacy Endpoints (Backward Compatible):           ║
║     • GET  /api/auctions                             ║
║     • GET  /api/best-values                          ║
║     • GET  /api/stats                                ║
║     • GET  /api/status                               ║
║     • GET  /health                                   ║
╚═══════════════════════════════════════════════════════╝
  `);
});

module.exports = app;