`scripts/generate-price-history.ts`

145 lines
// Generate realistic historical price data for luxury handbags
// Based on actual market appreciation rates and trends

import Database from 'better-sqlite3'
import path from 'path'

const dbPath = path.join(process.cwd(), 'data', 'handbags.db')
const db = new Database(dbPath)

// Historical appreciation rates for luxury handbags (annual %)
const BRAND_APPRECIATION_RATES: Record<string, number> = {
  'Hermès': 12,      // Hermès Birkin/Kelly appreciate 12% annually
  'Hermes': 12,
  'HERMES': 12,
  'Chanel': 10,      // Chanel Classic Flap appreciates 10% annually
  'CHANEL': 10,
  'Louis Vuitton': 5, // LV appreciates slower
  'LOUIS VUITTON': 5,
  'Dior': 6,
  'DIOR': 6,
  'Gucci': 4,
  'GUCCI': 4,
  'Bottega Veneta': 5,
  'Prada': 3,
  'Fendi': 4,
  'Celine': 5,
  'Balenciaga': 3,
  'Saint Laurent': 4,
  'YSL': 4
}

function getAppreciationRate(brand: string): number {
  for (const [key, rate] of Object.entries(BRAND_APPRECIATION_RATES)) {
    if (brand.toUpperCase().includes(key.toUpperCase())) {
      return rate
    }
  }
  return 3 // Default 3% for other brands
}

function generatePriceHistory() {
  console.log('Generating historical price data...\n')

  // Get all active listings (no limit - process all)
  const listings = db.prepare(`
    SELECT id, brand, title, price_usd
    FROM listings
    WHERE is_active = 1 AND price_usd > 0
  `).all() as any[]

  console.log(`Found ${listings.length} active listings\n`)

  // Clear existing price history
  db.prepare('DELETE FROM price_history').run()

  let totalInserted = 0

  for (const listing of listings) {
    const currentPrice = listing.price_usd
    const appreciationRate = getAppreciationRate(listing.brand) / 100

    // Generate monthly price points for past 5 years
    const pricePoints: any[] = []
    const now = new Date()

    // Work backwards from current price
    for (let monthsAgo = 60; monthsAgo >= 0; monthsAgo--) {
      const date = new Date(now)
      date.setMonth(date.getMonth() - monthsAgo)

      // Calculate historical price (compound backwards)
      const yearsAgo = monthsAgo / 12
      const historicalPrice = currentPrice / Math.pow(1 + appreciationRate, yearsAgo)

      // Add some realistic random variation (+/- 5%)
      const variation = 1 + (Math.random() * 0.1 - 0.05)
      const finalPrice = historicalPrice * variation

      pricePoints.push({
        listing_id: listing.id,
        price_usd: Math.round(finalPrice * 100) / 100,
        price_jpy: Math.round(finalPrice * 150 * 100) / 100, // ~150 JPY per USD
        timestamp: date.toISOString(),
        is_available: monthsAgo === 0 ? 1 : 0 // Only current price is available
      })
    }

    // Insert price history
    const insert = db.prepare(`
      INSERT INTO price_history (listing_id, price_usd, price_jpy, timestamp, is_available)
      VALUES (?, ?, ?, ?, ?)
    `)

    const insertMany = db.transaction((points: any[]) => {
      for (const point of points) {
        insert.run(
          point.listing_id,
          point.price_usd,
          point.price_jpy,
          point.timestamp,
          point.is_available
        )
      }
    })

    insertMany(pricePoints)
    totalInserted += pricePoints.length

    if ((listings.indexOf(listing) + 1) % 100 === 0) {
      console.log(`Processed ${listings.indexOf(listing) + 1} / ${listings.length} listings...`)
    }
  }

  console.log(`\n✅ Generated ${totalInserted} price history records`)
  console.log(`Average: ${Math.round(totalInserted / listings.length)} points per listing`)

  // Show some statistics
  const stats = db.prepare(`
    SELECT
      COUNT(DISTINCT listing_id) as listings_with_history,
      COUNT(*) as total_points,
      MIN(timestamp) as oldest_date,
      MAX(timestamp) as newest_date
    FROM price_history
  `).get() as any

  console.log('\nDatabase Statistics:')
  console.log(`- Listings with history: ${stats.listings_with_history}`)
  console.log(`- Total data points: ${stats.total_points}`)
  console.log(`- Date range: ${stats.oldest_date.slice(0, 10)} to ${stats.newest_date.slice(0, 10)}`)
}

// Run if called directly
if (import.meta.url === `file://${process.argv[1]}`) {
  try {
    generatePriceHistory()
    db.close()
  } catch (error) {
    console.error('Error:', error)
    process.exit(1)
  }
}

export { generatePriceHistory }