`auction-viewer/migrations.js`

326 lines
const Database = require('better-sqlite3');
const fs = require('fs');
const path = require('path');

const DB_PATH = path.join(__dirname, '..', 'data', 'auction-history', 'auctions.db');
const MIGRATIONS_DIR = path.join(__dirname, 'migrations');

// Ensure migrations directory exists
if (!fs.existsSync(MIGRATIONS_DIR)) {
  fs.mkdirSync(MIGRATIONS_DIR, { recursive: true });
}

class MigrationManager {
  constructor(dbPath) {
    this.dbPath = dbPath;
    this.db = null;
  }

  connect() {
    this.db = new Database(this.dbPath);
    this.db.pragma('journal_mode = WAL');
    this.initMigrationsTable();
  }

  close() {
    if (this.db) {
      this.db.close();
      this.db = null;
    }
  }

  initMigrationsTable() {
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS migrations (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT UNIQUE NOT NULL,
        applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);
  }

  getAppliedMigrations() {
    const migrations = this.db.prepare('SELECT name FROM migrations ORDER BY id').all();
    return migrations.map(m => m.name);
  }

  applyMigration(name, sql) {
    console.log(`Applying migration: ${name}`);

    const transaction = this.db.transaction(() => {
      this.db.exec(sql);
      this.db.prepare('INSERT INTO migrations (name) VALUES (?)').run(name);
    });

    try {
      transaction();
      console.log(`✓ Migration applied: ${name}`);
      return true;
    } catch (error) {
      console.error(`✗ Migration failed: ${name}`, error);
      throw error;
    }
  }

  runMigrations() {
    console.log('Running database migrations...\n');

    const migrations = [
      {
        name: '001_add_indexes',
        sql: `
          -- Add indexes for common queries
          CREATE INDEX IF NOT EXISTS idx_auctions_search_term ON auctions(search_term);
          CREATE INDEX IF NOT EXISTS idx_auctions_auction_house ON auctions(auction_house);
          CREATE INDEX IF NOT EXISTS idx_auctions_current_price ON auctions(current_price);
          CREATE INDEX IF NOT EXISTS idx_auctions_created_at ON auctions(created_at);
          CREATE INDEX IF NOT EXISTS idx_auctions_estimate_low ON auctions(estimate_low);

          -- Composite index for common filtering combinations
          CREATE INDEX IF NOT EXISTS idx_auctions_brand_price ON auctions(search_term, current_price);
          CREATE INDEX IF NOT EXISTS idx_auctions_price_estimate ON auctions(current_price, estimate_low);
        `
      },
      {
        name: '002_add_constraints',
        sql: `
          -- Create new table with constraints
          CREATE TABLE IF NOT EXISTS auctions_new (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            auction_id TEXT UNIQUE NOT NULL,
            title TEXT NOT NULL,
            auction_house TEXT,
            current_price REAL CHECK(current_price >= 0),
            estimate_low REAL CHECK(estimate_low >= 0),
            estimate_high REAL CHECK(estimate_high >= 0),
            end_date TEXT,
            lot_number TEXT,
            url TEXT,
            search_term TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
          );

          -- Copy data if old table exists
          INSERT OR IGNORE INTO auctions_new (
            id, auction_id, title, auction_house, current_price,
            estimate_low, estimate_high, end_date, lot_number,
            url, search_term, created_at
          )
          SELECT
            id, auction_id, title, auction_house, current_price,
            estimate_low, estimate_high, end_date, lot_number,
            url, search_term, created_at
          FROM auctions
          WHERE auction_id IS NOT NULL AND title IS NOT NULL AND search_term IS NOT NULL;

          -- Drop old table and rename
          DROP TABLE IF EXISTS auctions_old;
          ALTER TABLE auctions RENAME TO auctions_old;
          ALTER TABLE auctions_new RENAME TO auctions;
          DROP TABLE IF EXISTS auctions_old;
        `
      },
      {
        name: '003_add_archive_table',
        sql: `
          -- Create archive table for old auctions
          CREATE TABLE IF NOT EXISTS auctions_archive (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            auction_id TEXT UNIQUE,
            title TEXT,
            auction_house TEXT,
            current_price REAL,
            estimate_low REAL,
            estimate_high REAL,
            end_date TEXT,
            lot_number TEXT,
            url TEXT,
            search_term TEXT,
            created_at TIMESTAMP,
            archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
          );

          -- Add index to archive
          CREATE INDEX IF NOT EXISTS idx_archive_created_at ON auctions_archive(created_at);
          CREATE INDEX IF NOT EXISTS idx_archive_archived_at ON auctions_archive(archived_at);
        `
      },
      {
        name: '004_add_scraper_runs_table',
        sql: `
          -- Track scraper execution
          CREATE TABLE IF NOT EXISTS scraper_runs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            completed_at TIMESTAMP,
            status TEXT CHECK(status IN ('running', 'success', 'failed')),
            items_found INTEGER DEFAULT 0,
            items_saved INTEGER DEFAULT 0,
            error_message TEXT,
            brands_searched INTEGER DEFAULT 0
          );

          CREATE INDEX IF NOT EXISTS idx_scraper_runs_started ON scraper_runs(started_at);
          CREATE INDEX IF NOT EXISTS idx_scraper_runs_status ON scraper_runs(status);
        `
      },
      {
        name: '005_add_fts_search',
        sql: `
          -- Full-text search virtual table
          CREATE VIRTUAL TABLE IF NOT EXISTS auctions_fts USING fts5(
            title,
            auction_house,
            search_term,
            content='auctions',
            content_rowid='id'
          );

          -- Populate FTS table
          INSERT OR IGNORE INTO auctions_fts(rowid, title, auction_house, search_term)
          SELECT id, title, auction_house, search_term FROM auctions;

          -- Triggers to keep FTS in sync
          CREATE TRIGGER IF NOT EXISTS auctions_ai AFTER INSERT ON auctions BEGIN
            INSERT INTO auctions_fts(rowid, title, auction_house, search_term)
            VALUES (new.id, new.title, new.auction_house, new.search_term);
          END;

          CREATE TRIGGER IF NOT EXISTS auctions_ad AFTER DELETE ON auctions BEGIN
            DELETE FROM auctions_fts WHERE rowid = old.id;
          END;

          CREATE TRIGGER IF NOT EXISTS auctions_au AFTER UPDATE ON auctions BEGIN
            UPDATE auctions_fts SET
              title = new.title,
              auction_house = new.auction_house,
              search_term = new.search_term
            WHERE rowid = new.id;
          END;
        `
      }
    ];

    const appliedMigrations = this.getAppliedMigrations();

    let appliedCount = 0;
    for (const migration of migrations) {
      if (!appliedMigrations.includes(migration.name)) {
        try {
          this.applyMigration(migration.name, migration.sql);
          appliedCount++;
        } catch (error) {
          console.error(`Failed to apply migration ${migration.name}:`, error);
          throw error;
        }
      } else {
        console.log(`⊘ Skipping already applied: ${migration.name}`);
      }
    }

    console.log(`\nMigrations complete: ${appliedCount} applied, ${migrations.length - appliedCount} skipped`);
  }

  // Archive old auctions (older than 1 year)
  archiveOldAuctions(daysOld = 365) {
    console.log(`Archiving auctions older than ${daysOld} days...`);

    const result = this.db.prepare(`
      INSERT INTO auctions_archive
      SELECT *, CURRENT_TIMESTAMP as archived_at
      FROM auctions
      WHERE created_at < DATE('now', '-${daysOld} days')
    `).run();

    if (result.changes > 0) {
      this.db.prepare(`
        DELETE FROM auctions
        WHERE created_at < DATE('now', '-${daysOld} days')
      `).run();

      console.log(`✓ Archived ${result.changes} old auctions`);
    } else {
      console.log('No auctions to archive');
    }

    return result.changes;
  }

  // Optimize database
  optimize() {
    console.log('Optimizing database...');
    this.db.pragma('optimize');
    this.db.exec('VACUUM');
    console.log('✓ Database optimized');
  }

  // Get migration status
  getStatus() {
    const appliedMigrations = this.getAppliedMigrations();
    const tables = this.db.prepare(`
      SELECT name FROM sqlite_master WHERE type='table' ORDER BY name
    `).all();

    const indexes = this.db.prepare(`
      SELECT name, tbl_name FROM sqlite_master WHERE type='index' ORDER BY name
    `).all();

    return {
      appliedMigrations,
      tables: tables.map(t => t.name),
      indexes: indexes.map(i => ({ name: i.name, table: i.tbl_name })),
      databaseSize: fs.statSync(this.dbPath).size
    };
  }
}

// CLI usage
if (require.main === module) {
  const manager = new MigrationManager(DB_PATH);
  manager.connect();

  const command = process.argv[2] || 'migrate';

  try {
    switch (command) {
      case 'migrate':
        manager.runMigrations();
        break;

      case 'status':
        const status = manager.getStatus();
        console.log('\nDatabase Status:');
        console.log('================');
        console.log(`Applied Migrations: ${status.appliedMigrations.length}`);
        status.appliedMigrations.forEach(m => console.log(`  - ${m}`));
        console.log(`\nTables: ${status.tables.length}`);
        status.tables.forEach(t => console.log(`  - ${t}`));
        console.log(`\nIndexes: ${status.indexes.length}`);
        status.indexes.forEach(i => console.log(`  - ${i.name} (on ${i.table})`));
        console.log(`\nDatabase Size: ${(status.databaseSize / 1024).toFixed(2)} KB`);
        break;

      case 'archive':
        const days = parseInt(process.argv[3]) || 365;
        manager.archiveOldAuctions(days);
        break;

      case 'optimize':
        manager.optimize();
        break;

      default:
        console.log('Unknown command:', command);
        console.log('Usage: node migrations.js [migrate|status|archive|optimize]');
    }
  } catch (error) {
    console.error('Error:', error);
    process.exit(1);
  } finally {
    manager.close();
  }
}

module.exports = MigrationManager;