`scripts/export-spreadsheet.js`

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

class SpreadsheetExporter {
  constructor() {
    this.db = new Database(process.env.DATABASE_PATH);
    this.outputDir = path.join(__dirname, '../exports');

    // Create exports directory if it doesn't exist
    if (!fs.existsSync(this.outputDir)) {
      fs.mkdirSync(this.outputDir, { recursive: true });
    }
  }

  async exportAllListings(filename = null) {
    const outputFile = filename || `all-listings-${Date.now()}.csv`;
    const outputPath = path.join(this.outputDir, outputFile);

    return new Promise((resolve, reject) => {
      this.db.getListings({}, (err, listings) => {
        if (err) {
          reject(err);
          return;
        }

        const fields = [
          { label: 'ID', value: 'id' },
          { label: 'Brand', value: 'brand' },
          { label: 'Title', value: 'title' },
          { label: 'Source', value: 'source' },
          { label: 'Listing Type', value: 'listing_type' },
          { label: 'Price (JPY)', value: 'price_jpy' },
          { label: 'Price (USD)', value: 'price_usd' },
          { label: 'US Market Avg', value: 'avg_us_price' },
          { label: 'Deal %', value: 'deal_percentage' },
          { label: 'Potential Profit', value: row => row.avg_us_price && row.price_usd ? (row.avg_us_price - row.price_usd).toFixed(2) : '' },
          { label: 'Is Deal', value: row => row.is_deal ? 'Yes' : 'No' },
          { label: 'Condition', value: 'condition' },
          { label: 'Confidence', value: row => row.confidence_score ? (row.confidence_score * 100).toFixed(0) + '%' : '' },
          { label: 'AI Notes', value: 'ai_notes' },
          { label: 'Product URL', value: 'product_url' },
          { label: 'Image URL', value: 'image_url' },
          { label: 'Crawled At', value: 'crawled_at' }
        ];

        try {
          const parser = new Parser({ fields });
          const csv = parser.parse(listings);

          fs.writeFileSync(outputPath, csv);
          console.log(`✓ Exported ${listings.length} listings to ${outputPath}`);
          resolve(outputPath);
        } catch (error) {
          reject(error);
        }
      });
    });
  }

  async exportDealsOnly(minDealPercentage = 20, filename = null) {
    const outputFile = filename || `deals-${minDealPercentage}pct-${Date.now()}.csv`;
    const outputPath = path.join(this.outputDir, outputFile);

    return new Promise((resolve, reject) => {
      this.db.getListings({ dealOnly: true, minDealPercentage, sort: 'deal' }, (err, listings) => {
        if (err) {
          reject(err);
          return;
        }

        const fields = [
          { label: 'Brand', value: 'brand' },
          { label: 'Title', value: 'title' },
          { label: 'Deal %', value: row => row.deal_percentage.toFixed(1) + '%' },
          { label: 'JP Price (¥)', value: row => '¥' + row.price_jpy.toLocaleString() },
          { label: 'USD Price', value: row => '$' + row.price_usd.toFixed(2) },
          { label: 'US Market', value: row => '$' + (row.avg_us_price || 0).toFixed(0) },
          { label: 'Profit Potential', value: row => '$' + (row.avg_us_price - row.price_usd).toFixed(0) },
          { label: 'Condition', value: 'condition' },
          { label: 'Type', value: 'listing_type' },
          { label: 'Source', value: 'source' },
          { label: 'Confidence', value: row => (row.confidence_score * 100).toFixed(0) + '%' },
          { label: 'AI Analysis', value: 'ai_notes' },
          { label: 'Link', value: 'product_url' }
        ];

        try {
          const parser = new Parser({ fields });
          const csv = parser.parse(listings);

          fs.writeFileSync(outputPath, csv);
          console.log(`✓ Exported ${listings.length} deals to ${outputPath}`);
          resolve(outputPath);
        } catch (error) {
          reject(error);
        }
      });
    });
  }

  async exportByBrand(brand, filename = null) {
    const outputFile = filename || `${brand.toLowerCase().replace(/\s+/g, '-')}-${Date.now()}.csv`;
    const outputPath = path.join(this.outputDir, outputFile);

    return new Promise((resolve, reject) => {
      this.db.getListings({ brand }, (err, listings) => {
        if (err) {
          reject(err);
          return;
        }

        const fields = [
          { label: 'Title', value: 'title' },
          { label: 'Price (USD)', value: row => '$' + row.price_usd.toFixed(2) },
          { label: 'Deal %', value: row => row.deal_percentage ? row.deal_percentage.toFixed(1) + '%' : 'N/A' },
          { label: 'Condition', value: 'condition' },
          { label: 'Source', value: 'source' },
          { label: 'Link', value: 'product_url' }
        ];

        try {
          const parser = new Parser({ fields });
          const csv = parser.parse(listings);

          fs.writeFileSync(outputPath, csv);
          console.log(`✓ Exported ${listings.length} ${brand} listings to ${outputPath}`);
          resolve(outputPath);
        } catch (error) {
          reject(error);
        }
      });
    });
  }

  async exportDailySummary() {
    const outputFile = `daily-summary-${new Date().toISOString().split('T')[0]}.csv`;
    const outputPath = path.join(this.outputDir, outputFile);

    // Get top deals from today
    const sql = `
      SELECT
        l.brand,
        l.title,
        l.price_usd,
        d.avg_us_price,
        d.deal_percentage,
        l.condition,
        l.source,
        l.product_url
      FROM listings l
      JOIN deal_analysis d ON l.id = d.listing_id
      WHERE d.is_deal = 1
        AND DATE(l.crawled_at) = DATE('now')
      ORDER BY d.deal_percentage DESC
      LIMIT 100
    `;

    return new Promise((resolve, reject) => {
      this.db.db.all(sql, (err, listings) => {
        if (err) {
          reject(err);
          return;
        }

        if (listings.length === 0) {
          console.log('No new deals today');
          resolve(null);
          return;
        }

        const fields = [
          { label: 'Brand', value: 'brand' },
          { label: 'Title', value: 'title' },
          { label: 'Price', value: row => '$' + row.price_usd.toFixed(2) },
          { label: 'Market Value', value: row => '$' + row.avg_us_price.toFixed(0) },
          { label: 'Savings', value: row => row.deal_percentage.toFixed(1) + '%' },
          { label: 'Profit', value: row => '$' + (row.avg_us_price - row.price_usd).toFixed(0) },
          { label: 'Condition', value: 'condition' },
          { label: 'Source', value: 'source' },
          { label: 'URL', value: 'product_url' }
        ];

        try {
          const parser = new Parser({ fields });
          const csv = parser.parse(listings);

          fs.writeFileSync(outputPath, csv);
          console.log(`✓ Exported ${listings.length} today's deals to ${outputPath}`);
          resolve(outputPath);
        } catch (error) {
          reject(error);
        }
      });
    });
  }

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

// CLI interface
if (require.main === module) {
  const exporter = new SpreadsheetExporter();
  const command = process.argv[2] || 'deals';

  (async () => {
    try {
      switch (command) {
        case 'all':
          await exporter.exportAllListings();
          break;

        case 'deals':
          const minDeal = parseInt(process.argv[3]) || 20;
          await exporter.exportDealsOnly(minDeal);
          break;

        case 'brand':
          const brand = process.argv[3];
          if (!brand) {
            console.error('Please specify a brand: node export-spreadsheet.js brand "Gucci"');
            process.exit(1);
          }
          await exporter.exportByBrand(brand);
          break;

        case 'daily':
          await exporter.exportDailySummary();
          break;

        default:
          console.log('Usage:');
          console.log('  node export-spreadsheet.js all                  - Export all listings');
          console.log('  node export-spreadsheet.js deals [minDeal]      - Export deals (default 20%)');
          console.log('  node export-spreadsheet.js brand "Brand Name"   - Export by brand');
          console.log('  node export-spreadsheet.js daily                - Export today\'s deals');
      }

      exporter.close();
      process.exit(0);
    } catch (error) {
      console.error('Export error:', error);
      exporter.close();
      process.exit(1);
    }
  })();
}

module.exports = SpreadsheetExporter;