`db/schema.js`

295 lines
const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const fs = require('fs');

class Database {
  constructor(dbPath = './data/handbags.db') {
    // Ensure data directory exists
    const dir = path.dirname(dbPath);
    if (!fs.existsSync(dir)) {
      fs.mkdirSync(dir, { recursive: true });
    }

    this.db = new sqlite3.Database(dbPath);
    this.init();
  }

  init() {
    this.db.serialize(() => {
      // Main listings table
      this.db.run(`
        CREATE TABLE IF NOT EXISTS listings (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          external_id TEXT UNIQUE,
          source TEXT NOT NULL,
          title TEXT NOT NULL,
          brand TEXT,
          model TEXT,
          price_jpy REAL NOT NULL,
          price_usd REAL,
          currency TEXT DEFAULT 'JPY',
          condition TEXT,
          image_url TEXT,
          thumbnail_url TEXT,
          product_url TEXT,
          listing_type TEXT CHECK(listing_type IN ('auction', 'buy', 'both')),
          auction_end_date TEXT,
          location TEXT,
          seller_name TEXT,
          description TEXT,
          crawled_at TEXT DEFAULT CURRENT_TIMESTAMP,
          is_active INTEGER DEFAULT 1,
          UNIQUE(external_id, source)
        )
      `);

      // US market price comparisons
      this.db.run(`
        CREATE TABLE IF NOT EXISTS us_comparisons (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          listing_id INTEGER,
          us_source TEXT NOT NULL,
          us_price_usd REAL,
          us_url TEXT,
          us_condition TEXT,
          comparison_date TEXT DEFAULT CURRENT_TIMESTAMP,
          FOREIGN KEY(listing_id) REFERENCES listings(id) ON DELETE CASCADE
        )
      `);

      // Deal analysis
      this.db.run(`
        CREATE TABLE IF NOT EXISTS deal_analysis (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          listing_id INTEGER UNIQUE,
          avg_us_price REAL,
          min_us_price REAL,
          max_us_price REAL,
          deal_percentage REAL,
          confidence_score REAL,
          is_deal INTEGER DEFAULT 0,
          analysis_date TEXT DEFAULT CURRENT_TIMESTAMP,
          ai_notes TEXT,
          FOREIGN KEY(listing_id) REFERENCES listings(id) ON DELETE CASCADE
        )
      `);

      // Crawl history
      this.db.run(`
        CREATE TABLE IF NOT EXISTS crawl_history (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          source TEXT NOT NULL,
          start_time TEXT DEFAULT CURRENT_TIMESTAMP,
          end_time TEXT,
          items_found INTEGER DEFAULT 0,
          items_added INTEGER DEFAULT 0,
          items_updated INTEGER DEFAULT 0,
          status TEXT CHECK(status IN ('running', 'completed', 'failed')),
          error_message TEXT
        )
      `);

      // Indexes for performance
      this.db.run(`CREATE INDEX IF NOT EXISTS idx_brand ON listings(brand)`);
      this.db.run(`CREATE INDEX IF NOT EXISTS idx_price ON listings(price_usd)`);
      this.db.run(`CREATE INDEX IF NOT EXISTS idx_listing_type ON listings(listing_type)`);
      this.db.run(`CREATE INDEX IF NOT EXISTS idx_crawled_at ON listings(crawled_at)`);
      this.db.run(`CREATE INDEX IF NOT EXISTS idx_deal_percentage ON deal_analysis(deal_percentage)`);
      this.db.run(`CREATE INDEX IF NOT EXISTS idx_is_deal ON deal_analysis(is_deal)`);
    });
  }

  // Check if a listing is new (not seen before)
  isNewListing(listing, callback) {
    const { external_id, source } = listing;

    const sql = `SELECT id FROM listings WHERE external_id = ? AND source = ?`;

    this.db.get(sql, [external_id, source], (err, row) => {
      if (err) {
        callback(err, false);
      } else {
        // If row exists, it's NOT new. If no row, it IS new.
        callback(null, !row);
      }
    });
  }

  // Insert or update listing
  upsertListing(listing, callback) {
    const {
      external_id, source, title, brand, model, price_jpy, price_usd, currency,
      condition, image_url, thumbnail_url, product_url, listing_type,
      auction_end_date, location, seller_name, description, is_new
    } = listing;

    const sql = `
      INSERT INTO listings (
        external_id, source, title, brand, model, price_jpy, price_usd, currency,
        condition, image_url, thumbnail_url, product_url, listing_type,
        auction_end_date, location, seller_name, description, is_new
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ON CONFLICT(external_id, source) DO UPDATE SET
        title=excluded.title,
        price_jpy=excluded.price_jpy,
        price_usd=excluded.price_usd,
        condition=excluded.condition,
        image_url=excluded.image_url,
        thumbnail_url=excluded.thumbnail_url,
        is_active=1,
        is_new=0,
        crawled_at=CURRENT_TIMESTAMP
    `;

    this.db.run(sql, [
      external_id, source, title, brand, model, price_jpy, price_usd, currency,
      condition, image_url, thumbnail_url, product_url, listing_type,
      auction_end_date, location, seller_name, description, is_new || 0
    ], callback);
  }

  // Get all active listings with deal info
  getListings(filters = {}, callback) {
    let sql = `
      SELECT
        l.*,
        d.deal_percentage,
        d.avg_us_price,
        d.is_deal,
        d.confidence_score,
        d.ai_notes
      FROM listings l
      LEFT JOIN deal_analysis d ON l.id = d.listing_id
      WHERE l.is_active = 1
    `;
    const params = [];

    if (filters.brand) {
      sql += ` AND LOWER(l.brand) LIKE LOWER(?)`;
      params.push(`%${filters.brand}%`);
    }

    if (filters.minPrice) {
      sql += ` AND l.price_usd >= ?`;
      params.push(filters.minPrice);
    }

    if (filters.maxPrice) {
      sql += ` AND l.price_usd <= ?`;
      params.push(filters.maxPrice);
    }

    if (filters.listing_type) {
      sql += ` AND l.listing_type = ?`;
      params.push(filters.listing_type);
    }

    if (filters.dealOnly) {
      sql += ` AND d.is_deal = 1`;
    }

    if (filters.minDealPercentage) {
      sql += ` AND d.deal_percentage >= ?`;
      params.push(filters.minDealPercentage);
    }

    if (filters.newOnly) {
      sql += ` AND l.is_new = 1`;
    }

    // Sorting
    const sortMap = {
      'deal': 'd.deal_percentage DESC',
      'price_low': 'l.price_usd ASC',
      'price_high': 'l.price_usd DESC',
      'date': 'l.crawled_at DESC',
      'new': 'l.is_new DESC, l.crawled_at DESC',
      'type': 'l.listing_type ASC'
    };

    sql += ` ORDER BY ${sortMap[filters.sort] || 'd.deal_percentage DESC'}`;

    if (filters.limit) {
      sql += ` LIMIT ?`;
      params.push(filters.limit);
    }

    this.db.all(sql, params, callback);
  }

  // Save deal analysis
  saveDealAnalysis(analysis, callback) {
    const {
      listing_id, avg_us_price, min_us_price, max_us_price,
      deal_percentage, confidence_score, is_deal, ai_notes
    } = analysis;

    const sql = `
      INSERT INTO deal_analysis (
        listing_id, avg_us_price, min_us_price, max_us_price,
        deal_percentage, confidence_score, is_deal, ai_notes
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
      ON CONFLICT(listing_id) DO UPDATE SET
        avg_us_price=excluded.avg_us_price,
        min_us_price=excluded.min_us_price,
        max_us_price=excluded.max_us_price,
        deal_percentage=excluded.deal_percentage,
        confidence_score=excluded.confidence_score,
        is_deal=excluded.is_deal,
        ai_notes=excluded.ai_notes,
        analysis_date=CURRENT_TIMESTAMP
    `;

    this.db.run(sql, [
      listing_id, avg_us_price, min_us_price, max_us_price,
      deal_percentage, confidence_score, is_deal, ai_notes
    ], callback);
  }

  // Save US comparison
  saveUSComparison(comparison, callback) {
    const { listing_id, us_source, us_price_usd, us_url, us_condition } = comparison;

    const sql = `
      INSERT INTO us_comparisons (listing_id, us_source, us_price_usd, us_url, us_condition)
      VALUES (?, ?, ?, ?, ?)
    `;

    this.db.run(sql, [listing_id, us_source, us_price_usd, us_url, us_condition], callback);
  }

  // Start crawl session
  startCrawl(source, callback) {
    const sql = `INSERT INTO crawl_history (source, status) VALUES (?, 'running')`;
    this.db.run(sql, [source], function(err) {
      callback(err, this.lastID);
    });
  }

  // End crawl session
  endCrawl(crawl_id, stats, callback) {
    const sql = `
      UPDATE crawl_history
      SET end_time=CURRENT_TIMESTAMP,
          items_found=?,
          items_added=?,
          items_updated=?,
          status=?
      WHERE id=?
    `;
    this.db.run(sql, [
      stats.items_found,
      stats.items_added,
      stats.items_updated,
      stats.status,
      crawl_id
    ], callback);
  }

  close() {
    this.db.close();
  }
}

module.exports = Database;