← back to Watches

database/sqlite-fts5-setup.js

150 lines

/**
 * Simplified SQLite FTS5 for Omega Watches
 * Deep search with maximum performance
 */

import sqlite3 from 'sqlite3';
import { open } from 'sqlite';
import fs from 'fs/promises';
import path from 'path';
import { fileURLToPath } from 'url';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

async function setupDatabase() {
  // Open database
  const db = await open({
    filename: path.join(__dirname, 'watches-search.db'),
    driver: sqlite3.Database
  });

  await db.exec('PRAGMA journal_mode = WAL');
  
  // Drop existing tables
  await db.exec('DROP TABLE IF EXISTS watches');
  await db.exec('DROP TABLE IF EXISTS watches_fts');
  await db.exec('DROP TABLE IF EXISTS price_history');
  
  // Create main watches table
  await db.exec(`
    CREATE TABLE watches (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      collection TEXT,
      reference TEXT,
      year_introduced INTEGER,
      description TEXT,
      min_price REAL,
      max_price REAL,
      avg_price REAL,
      appreciation_rate REAL
    )
  `);

  // Create FTS5 table - simpler structure
  await db.exec(`
    CREATE VIRTUAL TABLE watches_fts USING fts5(
      id UNINDEXED,
      name,
      collection,
      reference,
      description,
      search_text,
      tokenize = 'porter unicode61'
    )
  `);

  // Create price history
  await db.exec(`
    CREATE TABLE price_history (
      id INTEGER PRIMARY KEY,
      watch_id TEXT,
      year INTEGER,
      price REAL,
      condition TEXT
    )
  `);

  // Create indexes
  await db.exec('CREATE INDEX idx_watches_collection ON watches(collection)');
  await db.exec('CREATE INDEX idx_watches_price ON watches(min_price, max_price)');
  await db.exec('CREATE INDEX idx_price_history_watch ON price_history(watch_id)');

  console.log('✅ Database structure created');

  // Load and import data
  const jsonPath = path.join(__dirname, '..', 'data', 'omega-watches.json');
  const data = JSON.parse(await fs.readFile(jsonPath, 'utf-8'));
  
  let imported = 0;
  
  for (const [watchId, watch] of Object.entries(data.watches)) {
    // Calculate prices
    const prices = watch.priceHistory?.map(p => p.price) || [];
    const minPrice = prices.length > 0 ? Math.min(...prices) : 0;
    const maxPrice = prices.length > 0 ? Math.max(...prices) : 0;
    const avgPrice = prices.length > 0 ? prices.reduce((a, b) => a + b, 0) / prices.length : 0;
    
    // Calculate appreciation
    let appreciationRate = 0;
    if (watch.priceHistory && watch.priceHistory.length >= 2) {
      const oldPrice = watch.priceHistory[0].price;
      const newPrice = watch.priceHistory[watch.priceHistory.length - 1].price;
      appreciationRate = ((newPrice - oldPrice) / oldPrice) * 100;
    }
    
    // Insert into main table
    await db.run(`
      INSERT INTO watches (id, name, collection, reference, year_introduced, 
                          description, min_price, max_price, avg_price, appreciation_rate)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `, [watchId, watch.name, watch.collection, watch.reference, watch.year_introduced,
        watch.description, minPrice, maxPrice, avgPrice, appreciationRate]);
    
    // Create search text
    const searchText = [
      watch.name,
      watch.collection,
      watch.reference,
      watch.description
    ].filter(Boolean).join(' ');
    
    // Insert into FTS table
    await db.run(`
      INSERT INTO watches_fts (id, name, collection, reference, description, search_text)
      VALUES (?, ?, ?, ?, ?, ?)
    `, [watchId, watch.name, watch.collection, watch.reference, watch.description, searchText]);
    
    // Insert price history
    if (watch.priceHistory) {
      for (const price of watch.priceHistory) {
        await db.run(`
          INSERT INTO price_history (watch_id, year, price, condition)
          VALUES (?, ?, ?, ?)
        `, [watchId, price.year, price.price, price.condition || 'new']);
      }
    }
    
    imported++;
  }

  console.log(`✅ Imported ${imported} watches`);
  
  // Test search
  const testResults = await db.all(`
    SELECT w.*, highlight(watches_fts, 1, '<b>', '</b>') as highlighted
    FROM watches w
    JOIN watches_fts f ON f.id = w.id
    WHERE watches_fts MATCH 'speedmaster'
    LIMIT 5
  `);
  
  console.log(`✅ Test search found ${testResults.length} results for 'speedmaster'`);
  
  await db.close();
  console.log('✅ Database setup complete!');
}

// Run setup
setupDatabase().catch(console.error);