← back to Handbag Authentication

api/database-export.js

183 lines

const Database = require('../db/schema');
const { Parser } = require('json2csv');
const fs = require('fs');
const path = require('path');

/**
 * Database Export API
 * Export all listings, sold comps, price history for external analysis
 */
class DatabaseExporter {
  constructor() {
    this.db = new Database(process.env.DATABASE_PATH);
  }

  /**
   * Export all listings with full analysis to CSV
   */
  async exportListingsCSV() {
    return new Promise((resolve, reject) => {
      this.db.db.all(`
        SELECT
          l.id,
          l.external_id,
          l.brand,
          l.model,
          l.title,
          l.size,
          l.color,
          l.material,
          l.condition,
          l.price_jpy,
          l.price_usd,
          l.listing_type,
          l.source,
          l.product_url,
          l.image_url,
          l.auction_end_date,
          l.crawled_at,
          da.avg_us_price,
          da.retail_msrp,
          da.retail_year,
          da.appreciation_percent,
          da.deal_percentage,
          da.is_deal,
          da.confidence_score,
          da.ai_notes
        FROM listings l
        LEFT JOIN deal_analysis da ON l.id = da.listing_id
        WHERE l.is_active = 1
        ORDER BY da.deal_percentage DESC
      `, (err, rows) => {
        if (err) return reject(err);

        try {
          const parser = new Parser();
          const csv = parser.parse(rows);
          resolve(csv);
        } catch (error) {
          reject(error);
        }
      });
    });
  }

  /**
   * Export brand-specific data
   */
  async exportBrandData(brand) {
    return new Promise((resolve, reject) => {
      this.db.db.all(`
        SELECT
          l.*,
          da.avg_us_price,
          da.retail_msrp,
          da.deal_percentage,
          da.appreciation_percent
        FROM listings l
        LEFT JOIN deal_analysis da ON l.id = da.listing_id
        WHERE l.brand LIKE ? AND l.is_active = 1
        ORDER BY l.price_usd ASC
      `, [`%${brand}%`], (err, rows) => {
        if (err) return reject(err);
        resolve(rows);
      });
    });
  }

  /**
   * Export all Hermès bags with retail price tracking
   */
  async exportHermesDatabase() {
    const data = await this.exportBrandData('Hermes');

    // Enrich with retail price data
    const RetailPriceDatabase = require('./retail-price-database');
    const retailDB = new RetailPriceDatabase();

    const enriched = data.map(item => {
      const retailInfo = retailDB.findRetailPrice(item.title, 'Hermes');
      return {
        ...item,
        matched_model: retailInfo ? retailInfo.modelName : null,
        msrp_lookup: retailInfo ? retailInfo.msrp : null,
        msrp_year: retailInfo ? retailInfo.year : null,
        appreciation_vs_retail: retailInfo && item.avg_us_price
          ? (((item.avg_us_price - retailInfo.msrp) / retailInfo.msrp) * 100).toFixed(1) + '%'
          : null
      };
    });

    return enriched;
  }

  /**
   * Export price history time series for a brand
   */
  async exportPriceHistory(brand, model = null) {
    const query = model
      ? `SELECT * FROM listings WHERE brand LIKE ? AND model LIKE ? ORDER BY crawled_at ASC`
      : `SELECT * FROM listings WHERE brand LIKE ? ORDER BY crawled_at ASC`;

    return new Promise((resolve, reject) => {
      const params = model ? [`%${brand}%`, `%${model}%`] : [`%${brand}%`];

      this.db.db.all(query, params, (err, rows) => {
        if (err) return reject(err);
        resolve(rows);
      });
    });
  }

  /**
   * Generate summary stats by brand
   */
  async getBrandStats() {
    return new Promise((resolve, reject) => {
      this.db.db.all(`
        SELECT
          l.brand,
          COUNT(l.id) as total_listings,
          AVG(l.price_usd) as avg_price_usd,
          MIN(l.price_usd) as min_price_usd,
          MAX(l.price_usd) as max_price_usd,
          AVG(da.deal_percentage) as avg_deal_percent,
          COUNT(CASE WHEN da.is_deal = 1 THEN 1 END) as deal_count,
          AVG(da.appreciation_percent) as avg_appreciation
        FROM listings l
        LEFT JOIN deal_analysis da ON l.id = da.listing_id
        WHERE l.is_active = 1
        GROUP BY l.brand
        ORDER BY avg_appreciation DESC
      `, (err, rows) => {
        if (err) return reject(err);
        resolve(rows);
      });
    });
  }

  /**
   * Save export to file
   */
  async saveExportToFile(filename, data) {
    const exportDir = path.join(__dirname, '../exports');
    if (!fs.existsSync(exportDir)) {
      fs.mkdirSync(exportDir, { recursive: true });
    }

    const filepath = path.join(exportDir, filename);

    if (filename.endsWith('.csv')) {
      const parser = new Parser();
      const csv = parser.parse(data);
      fs.writeFileSync(filepath, csv);
    } else if (filename.endsWith('.json')) {
      fs.writeFileSync(filepath, JSON.stringify(data, null, 2));
    }

    return filepath;
  }
}

module.exports = DatabaseExporter;