`auction-viewer/tests/unit/database.test.js`

338 lines
/**
 * Unit Tests - Database Operations
 */

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

describe('Database Operations', () => {
  let testDb;

  beforeEach(() => {
    testDb = new TestDatabase();
    testDb.initialize();
  });

  afterEach(() => {
    testDb.cleanup();
  });

  describe('Database Initialization', () => {
    test('should create database file', () => {
      const fs = require('fs');
      expect(fs.existsSync(testDb.getPath())).toBe(true);
    });

    test('should create auctions table', () => {
      const tables = testDb.db.prepare(
        "SELECT name FROM sqlite_master WHERE type='table' AND name='auctions'"
      ).all();

      expect(tables).toHaveLength(1);
      expect(tables[0].name).toBe('auctions');
    });

    test('should have correct schema', () => {
      const schema = testDb.db.prepare("PRAGMA table_info(auctions)").all();
      const columnNames = schema.map(col => col.name);

      expect(columnNames).toContain('id');
      expect(columnNames).toContain('auction_id');
      expect(columnNames).toContain('title');
      expect(columnNames).toContain('auction_house');
      expect(columnNames).toContain('current_price');
      expect(columnNames).toContain('estimate_low');
      expect(columnNames).toContain('estimate_high');
      expect(columnNames).toContain('created_at');
    });
  });

  describe('Data Insertion', () => {
    test('should insert single auction', () => {
      testDb.seed([mockAuctions[0]]);
      const count = testDb.getCount();
      expect(count).toBe(1);
    });

    test('should insert multiple auctions', () => {
      testDb.seed(mockAuctions);
      const count = testDb.getCount();
      expect(count).toBe(mockAuctions.length);
    });

    test('should enforce unique auction_id constraint', () => {
      testDb.seed([mockAuctions[0]]);

      expect(() => {
        testDb.seed([mockAuctions[0]]);
      }).toThrow();
    });

    test('should handle large dataset insertion', () => {
      const largeDataset = generateLargeDataset(1000);
      testDb.seed(largeDataset);

      const count = testDb.getCount();
      expect(count).toBe(1000);
    });

    test('should set created_at timestamp automatically', () => {
      testDb.seed([mockAuctions[0]]);
      const auction = testDb.getAllAuctions()[0];

      expect(auction.created_at).toBeTruthy();
      expect(new Date(auction.created_at)).toBeInstanceOf(Date);
    });
  });

  describe('Data Retrieval', () => {
    beforeEach(() => {
      testDb.seed(mockAuctions);
    });

    test('should retrieve all auctions', () => {
      const auctions = testDb.getAllAuctions();
      expect(auctions).toHaveLength(mockAuctions.length);
    });

    test('should order by created_at DESC', () => {
      const auctions = testDb.getAllAuctions();

      for (let i = 0; i < auctions.length - 1; i++) {
        const current = new Date(auctions[i].created_at);
        const next = new Date(auctions[i + 1].created_at);
        expect(current >= next).toBe(true);
      }
    });

    test('should retrieve auctions with valid prices', () => {
      const validAuctions = testDb.db.prepare(`
        SELECT * FROM auctions
        WHERE current_price > 0 AND estimate_low > 0
      `).all();

      validAuctions.forEach(auction => {
        expect(auction.current_price).toBeGreaterThan(0);
        expect(auction.estimate_low).toBeGreaterThan(0);
      });
    });

    test('should group by search_term', () => {
      const grouped = testDb.db.prepare(`
        SELECT search_term, COUNT(*) as count
        FROM auctions
        GROUP BY search_term
      `).all();

      expect(grouped.length).toBeGreaterThan(0);
      grouped.forEach(group => {
        expect(group.count).toBeGreaterThan(0);
      });
    });
  });

  describe('Savings Calculations', () => {
    beforeEach(() => {
      testDb.seed(mockAuctions);
    });

    test('should calculate savings percentage correctly', () => {
      const results = testDb.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
        FROM auctions
        WHERE current_price > 0 AND estimate_low > 0
      `).all();

      results.forEach(auction => {
        const expectedSavings = ((auction.estimate_low - auction.current_price) / auction.estimate_low) * 100;
        expect(Math.abs(auction.savings_percent - expectedSavings)).toBeLessThan(0.01);
      });
    });

    test('should calculate savings amount correctly', () => {
      const results = testDb.db.prepare(`
        SELECT *,
          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
      `).all();

      results.forEach(auction => {
        const expectedSavings = auction.estimate_low - auction.current_price;
        expect(auction.savings_amount).toBe(expectedSavings);
      });
    });

    test('should handle zero prices in savings calculation', () => {
      const zeroPrice = {
        ...mockAuctions[0],
        auction_id: 'zero-test-' + Date.now(),
        current_price: 0
      };

      testDb.seed([zeroPrice]);

      const results = testDb.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
        FROM auctions
        WHERE auction_id = ?
      `).get(zeroPrice.auction_id);

      expect(results.savings_percent).toBe(0);
    });
  });

  describe('Filtering and Sorting', () => {
    beforeEach(() => {
      testDb.seed(mockAuctions);
    });

    test('should filter by auction house', () => {
      const christies = testDb.db.prepare(`
        SELECT * FROM auctions WHERE auction_house = ?
      `).all("Christie's");

      christies.forEach(auction => {
        expect(auction.auction_house).toBe("Christie's");
      });
    });

    test('should sort by savings percentage DESC', () => {
      const sorted = testDb.db.prepare(`
        SELECT *,
          ((estimate_low - current_price) / estimate_low * 100) as savings_percent
        FROM auctions
        WHERE current_price > 0 AND estimate_low > 0
        ORDER BY savings_percent DESC
      `).all();

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

    test('should sort by savings amount DESC', () => {
      const sorted = testDb.db.prepare(`
        SELECT *,
          (estimate_low - current_price) as savings_amount
        FROM auctions
        WHERE current_price > 0 AND estimate_low > 0
        ORDER BY savings_amount DESC
      `).all();

      for (let i = 0; i < sorted.length - 1; i++) {
        expect(sorted[i].savings_amount).toBeGreaterThanOrEqual(sorted[i + 1].savings_amount);
      }
    });

    test('should limit results', () => {
      const limited = testDb.db.prepare(`
        SELECT * FROM auctions LIMIT 3
      `).all();

      expect(limited.length).toBe(3);
    });
  });

  describe('Statistics', () => {
    beforeEach(() => {
      testDb.seed(mockAuctions);
    });

    test('should calculate total count', () => {
      const result = testDb.db.prepare('SELECT COUNT(*) as count FROM auctions').get();
      expect(result.count).toBe(mockAuctions.length);
    });

    test('should calculate average price', () => {
      const result = testDb.db.prepare(`
        SELECT AVG(current_price) as avg_price
        FROM auctions
        WHERE current_price > 0
      `).get();

      expect(result.avg_price).toBeGreaterThan(0);
    });

    test('should find min and max prices', () => {
      const result = testDb.db.prepare(`
        SELECT
          MIN(current_price) as min_price,
          MAX(current_price) as max_price
        FROM auctions
        WHERE current_price > 0
      `).get();

      expect(result.min_price).toBeGreaterThan(0);
      expect(result.max_price).toBeGreaterThan(result.min_price);
    });

    test('should count by brand', () => {
      const results = testDb.db.prepare(`
        SELECT search_term, COUNT(*) as count
        FROM auctions
        GROUP BY search_term
        ORDER BY count DESC
      `).all();

      expect(results.length).toBeGreaterThan(0);
      results.forEach(row => {
        expect(row.count).toBeGreaterThan(0);
      });
    });
  });

  describe('Performance', () => {
    test('should handle 1000+ records efficiently', () => {
      const largeDataset = generateLargeDataset(1000);

      const startTime = Date.now();
      testDb.seed(largeDataset);
      const insertTime = Date.now() - startTime;

      expect(insertTime).toBeLessThan(5000); // Should complete within 5 seconds

      const queryStart = Date.now();
      const results = testDb.getAllAuctions();
      const queryTime = Date.now() - queryStart;

      expect(results.length).toBe(1000);
      expect(queryTime).toBeLessThan(1000); // Query should be fast
    });

    test('should perform complex queries efficiently', () => {
      testDb.seed(generateLargeDataset(500));

      const startTime = Date.now();
      const results = testDb.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 100
      `).all();
      const queryTime = Date.now() - startTime;

      expect(results.length).toBe(100);
      expect(queryTime).toBeLessThan(500); // Should be very fast
    });
  });
});