← back to Handbag Auth Nextjs

auction-viewer/tests/e2e/system.test.js

388 lines

/**
 * End-to-End System Tests
 * Tests the entire workflow from database to API
 */

const Database = require('better-sqlite3');
const path = require('path');
const fs = require('fs');
const { TestDatabase } = require('../fixtures/test-database');
const { mockAuctions } = require('../fixtures/test-data');

describe('E2E System Tests', () => {
  let testDb;
  let testDbPath;
  const testDataDir = path.join(__dirname, '../fixtures/e2e-test-data');

  beforeAll(() => {
    // Create test data directory
    if (!fs.existsSync(testDataDir)) {
      fs.mkdirSync(testDataDir, { recursive: true });
    }

    // Initialize test database
    testDb = new TestDatabase(path.join(testDataDir, 'auctions.db'));
    testDb.initialize();
    testDbPath = testDb.getPath();
  });

  afterAll(() => {
    testDb.cleanup();
    // Cleanup test directory
    if (fs.existsSync(testDataDir)) {
      fs.rmSync(testDataDir, { recursive: true, force: true });
    }
  });

  describe('Complete Data Pipeline', () => {
    test('should handle full data workflow: insert -> query -> export', () => {
      // Step 1: Insert auction data
      testDb.seed(mockAuctions);

      // Step 2: Query data
      const db = new Database(testDbPath, { readonly: true });
      const auctions = db.prepare('SELECT * FROM auctions').all();

      expect(auctions.length).toBe(mockAuctions.length);

      // Step 3: Verify data integrity
      auctions.forEach((auction, index) => {
        expect(auction.title).toBeTruthy();
        expect(auction.auction_house).toBeTruthy();
        expect(typeof auction.current_price).toBe('number');
      });

      // Step 4: Export to CSV
      const csvPath = path.join(testDataDir, 'export.csv');
      const csvContent = auctions.map(a =>
        `${a.auction_id},${a.title},${a.current_price}`
      ).join('\n');

      fs.writeFileSync(csvPath, csvContent);

      expect(fs.existsSync(csvPath)).toBe(true);

      db.close();
    });

    test('should maintain data consistency across operations', () => {
      testDb.clear();
      testDb.seed(mockAuctions);

      const db = new Database(testDbPath, { readonly: true });

      // Multiple concurrent reads
      const results1 = db.prepare('SELECT COUNT(*) as count FROM auctions').get();
      const results2 = db.prepare('SELECT COUNT(*) as count FROM auctions').get();
      const results3 = db.prepare('SELECT COUNT(*) as count FROM auctions').get();

      expect(results1.count).toBe(results2.count);
      expect(results2.count).toBe(results3.count);

      db.close();
    });
  });

  describe('Search and Filter Workflow', () => {
    beforeEach(() => {
      testDb.clear();
      testDb.seed(mockAuctions);
    });

    test('should filter by brand and calculate savings', () => {
      const db = new Database(testDbPath, { readonly: true });

      // Search for specific brand
      const brand = 'Hermes Birkin';
      const results = db.prepare(`
        SELECT *,
          ((estimate_low - current_price) / estimate_low * 100) as savings_percent
        FROM auctions
        WHERE search_term = ?
        AND current_price > 0
        AND estimate_low > 0
      `).all(brand);

      expect(results.length).toBeGreaterThan(0);

      results.forEach(auction => {
        expect(auction.search_term).toBe(brand);
        expect(auction.savings_percent).toBeDefined();
      });

      db.close();
    });

    test('should find best deals across all brands', () => {
      const db = new Database(testDbPath, { readonly: true });

      const bestDeals = db.prepare(`
        SELECT *,
          ((estimate_low - current_price) / estimate_low * 100) as savings_percent,
          (estimate_low - current_price) as savings_amount
        FROM auctions
        WHERE current_price > 0 AND estimate_low > 0
        ORDER BY savings_percent DESC
        LIMIT 10
      `).all();

      expect(bestDeals.length).toBeGreaterThan(0);

      // Verify sorting
      for (let i = 0; i < bestDeals.length - 1; i++) {
        expect(bestDeals[i].savings_percent).toBeGreaterThanOrEqual(bestDeals[i + 1].savings_percent);
      }

      db.close();
    });

    test('should generate auction house statistics', () => {
      const db = new Database(testDbPath, { readonly: true });

      const stats = db.prepare(`
        SELECT
          auction_house,
          COUNT(*) as total_auctions,
          AVG(current_price) as avg_price,
          MIN(current_price) as min_price,
          MAX(current_price) as max_price
        FROM auctions
        WHERE current_price > 0
        GROUP BY auction_house
        ORDER BY total_auctions DESC
      `).all();

      expect(stats.length).toBeGreaterThan(0);

      stats.forEach(stat => {
        expect(stat.total_auctions).toBeGreaterThan(0);
        expect(stat.avg_price).toBeGreaterThan(0);
        expect(stat.max_price).toBeGreaterThanOrEqual(stat.min_price);
      });

      db.close();
    });
  });

  describe('Data Export and Reporting', () => {
    beforeEach(() => {
      testDb.clear();
      testDb.seed(mockAuctions);
    });

    test('should export full CSV report', () => {
      const db = new Database(testDbPath, { readonly: true });
      const auctions = db.prepare(`
        SELECT
          auction_id,
          title,
          auction_house,
          current_price,
          estimate_low,
          estimate_high,
          end_date,
          search_term
        FROM auctions
        ORDER BY created_at DESC
      `).all();

      const csvPath = path.join(testDataDir, 'full-report.csv');
      const csvLines = [
        'auction_id,title,auction_house,current_price,estimate_low,estimate_high,end_date,search_term'
      ];

      auctions.forEach(a => {
        csvLines.push(
          `${a.auction_id},"${a.title}",${a.auction_house},${a.current_price},${a.estimate_low},${a.estimate_high},${a.end_date},${a.search_term}`
        );
      });

      fs.writeFileSync(csvPath, csvLines.join('\n'));

      expect(fs.existsSync(csvPath)).toBe(true);

      const content = fs.readFileSync(csvPath, 'utf8');
      expect(content).toContain('auction_id');
      expect(content).toContain('title');

      db.close();
    });

    test('should generate daily summary report', () => {
      const db = new Database(testDbPath, { readonly: true });

      const summary = {
        total_auctions: db.prepare('SELECT COUNT(*) as count FROM auctions').get().count,
        total_value: db.prepare('SELECT SUM(current_price) as total FROM auctions WHERE current_price > 0').get().total,
        avg_price: db.prepare('SELECT AVG(current_price) as avg FROM auctions WHERE current_price > 0').get().avg,
        by_brand: db.prepare(`
          SELECT search_term, COUNT(*) as count
          FROM auctions
          GROUP BY search_term
          ORDER BY count DESC
        `).all(),
        by_auction_house: db.prepare(`
          SELECT auction_house, COUNT(*) as count
          FROM auctions
          GROUP BY auction_house
          ORDER BY count DESC
        `).all()
      };

      expect(summary.total_auctions).toBeGreaterThan(0);
      expect(summary.total_value).toBeGreaterThan(0);
      expect(summary.avg_price).toBeGreaterThan(0);
      expect(summary.by_brand.length).toBeGreaterThan(0);
      expect(summary.by_auction_house.length).toBeGreaterThan(0);

      // Save summary to JSON
      const summaryPath = path.join(testDataDir, 'summary.json');
      fs.writeFileSync(summaryPath, JSON.stringify(summary, null, 2));

      expect(fs.existsSync(summaryPath)).toBe(true);

      db.close();
    });
  });

  describe('System Health Checks', () => {
    test('should verify all system files exist', () => {
      const systemFiles = {
        database: testDbPath,
      };

      Object.entries(systemFiles).forEach(([name, filePath]) => {
        expect(fs.existsSync(filePath)).toBe(true);
      });
    });

    test('should verify database integrity', () => {
      const db = new Database(testDbPath, { readonly: true });

      // Check integrity
      const integrity = db.pragma('integrity_check');
      expect(integrity[0].integrity_check).toBe('ok');

      db.close();
    });

    test('should verify database size is reasonable', () => {
      const stats = fs.statSync(testDbPath);
      const sizeInMB = stats.size / (1024 * 1024);

      // Database should be less than 100MB for normal operation
      expect(sizeInMB).toBeLessThan(100);
    });

    test('should handle database backup and restore', () => {
      const backupPath = path.join(testDataDir, 'backup.db');

      // Backup
      fs.copyFileSync(testDbPath, backupPath);
      expect(fs.existsSync(backupPath)).toBe(true);

      // Verify backup is valid
      const backupDb = new Database(backupPath, { readonly: true });
      const count = backupDb.prepare('SELECT COUNT(*) as count FROM auctions').get().count;
      expect(count).toBeGreaterThan(0);
      backupDb.close();

      // Cleanup
      fs.unlinkSync(backupPath);
    });
  });

  describe('Error Recovery', () => {
    test('should handle corrupted query gracefully', () => {
      const db = new Database(testDbPath, { readonly: true });

      expect(() => {
        db.prepare('SELECT * FROM nonexistent_table').all();
      }).toThrow();

      // Database should still be usable
      const result = db.prepare('SELECT COUNT(*) as count FROM auctions').get();
      expect(result.count).toBeGreaterThanOrEqual(0);

      db.close();
    });

    test('should handle empty result sets', () => {
      testDb.clear();

      const db = new Database(testDbPath, { readonly: true });
      const results = db.prepare('SELECT * FROM auctions').all();

      expect(results).toEqual([]);
      expect(Array.isArray(results)).toBe(true);

      db.close();
    });

    test('should handle connection close and reopen', () => {
      let db = new Database(testDbPath, { readonly: true });
      const count1 = db.prepare('SELECT COUNT(*) as count FROM auctions').get().count;
      db.close();

      // Reopen
      db = new Database(testDbPath, { readonly: true });
      const count2 = db.prepare('SELECT COUNT(*) as count FROM auctions').get().count;
      db.close();

      expect(count1).toBe(count2);
    });
  });

  describe('Performance Under Load', () => {
    test('should handle rapid queries without degradation', () => {
      testDb.clear();
      testDb.seed(mockAuctions);

      const db = new Database(testDbPath, { readonly: true });
      const times = [];

      for (let i = 0; i < 100; i++) {
        const start = Date.now();
        db.prepare('SELECT * FROM auctions LIMIT 10').all();
        const duration = Date.now() - start;
        times.push(duration);
      }

      const avgTime = times.reduce((sum, t) => sum + t, 0) / times.length;

      // Average query time should be very fast
      expect(avgTime).toBeLessThan(10);

      db.close();
    });

    test('should handle complex analytical queries', () => {
      testDb.clear();
      testDb.seed(mockAuctions);

      const db = new Database(testDbPath, { readonly: true });

      const start = Date.now();
      const analysis = db.prepare(`
        SELECT
          search_term,
          COUNT(*) as total,
          AVG(current_price) as avg_price,
          AVG((estimate_low - current_price) / estimate_low * 100) as avg_savings,
          MIN(current_price) as min_price,
          MAX(current_price) as max_price
        FROM auctions
        WHERE current_price > 0 AND estimate_low > 0
        GROUP BY search_term
        ORDER BY avg_savings DESC
      `).all();
      const duration = Date.now() - start;

      expect(analysis.length).toBeGreaterThan(0);
      expect(duration).toBeLessThan(100);

      db.close();
    });
  });
});