`db/add-new-tracking.js`

63 lines
#!/usr/bin/env node

/**
 * Add "is_new" tracking column to handbags database
 */

const sqlite3 = require('sqlite3').verbose();
const path = require('path');

const dbPath = path.join(__dirname, '..', 'data', 'handbags.db');
const db = new sqlite3.Database(dbPath);

console.log('Adding new item tracking to handbags database...\n');

db.serialize(() => {
  // Add is_new column to listings table
  db.run(`
    ALTER TABLE listings
    ADD COLUMN is_new INTEGER DEFAULT 1
  `, (err) => {
    if (err && !err.message.includes('duplicate column name')) {
      console.error('Error adding is_new column:', err);
    } else {
      console.log('✓ Added is_new column to listings table');
    }
  });

  // Add first_seen_at column to track when first discovered
  db.run(`
    ALTER TABLE listings
    ADD COLUMN first_seen_at TEXT DEFAULT CURRENT_TIMESTAMP
  `, (err) => {
    if (err && !err.message.includes('duplicate column name')) {
      console.error('Error adding first_seen_at column:', err);
    } else {
      console.log('✓ Added first_seen_at column to listings table');
    }
  });

  // Create index for quick new item queries
  db.run(`
    CREATE INDEX IF NOT EXISTS idx_is_new ON listings(is_new)
  `, (err) => {
    if (err) {
      console.error('Error creating index:', err);
    } else {
      console.log('✓ Created index on is_new column');
    }
  });

  // Get count of existing listings (these will be marked as NOT new)
  db.get(`SELECT COUNT(*) as count FROM listings`, (err, row) => {
    if (err) {
      console.error('Error counting listings:', err);
    } else {
      console.log(`\n✓ Database updated! ${row.count} existing listings will be marked as seen.`);
      console.log('  Future crawls will mark new items with is_new=1');
    }

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