← back to Handbag Auth Nextjs

src/app/api/top-deals/route.ts

285 lines

import { NextRequest, NextResponse } from 'next/server'
import Database from 'better-sqlite3'
import path from 'path'
import { rateLimit, getClientIp } from '@/lib/rateLimit'
import { addSecurityHeaders } from '@/lib/securityHeaders'
import { getKnownMuseumImage } from '@/lib/museumImages'

interface Deal {
  type: 'listing' | 'auction' | 'arbitrage'
  id: string | number
  title: string
  description?: string
  price: number
  originalPrice?: number
  currency: string
  savings: number
  savingsPercent: number
  url?: string
  imageUrl?: string
  brand?: string
  model?: string
  condition?: string
  source: string
  metadata: Record<string, any>
}

export async function GET(request: NextRequest) {
  // Apply rate limiting
  const clientIp = getClientIp(request)
  const rateLimitResult = rateLimit(clientIp, {
    windowMs: 60 * 1000,
    maxRequests: 60,
  })

  if (!rateLimitResult.allowed) {
    const response = NextResponse.json(
      { success: false, error: 'Rate limit exceeded' },
      { status: 429 }
    )
    response.headers.set('Retry-After', String(Math.ceil((rateLimitResult.resetTime - Date.now()) / 1000)))
    addSecurityHeaders(response)
    return response
  }

  try {
    const searchParams = request.nextUrl.searchParams
    const limit = Math.min(parseInt(searchParams.get('limit') || '10'), 50)

    const deals: Deal[] = []

    // Get deals from all sources in parallel
    const [listingDeals, auctionDeals, arbitrageDeals] = await Promise.all([
      getListingDeals(limit),
      getAuctionDeals(limit),
      getArbitrageDeals(limit)
    ])

    deals.push(...listingDeals, ...auctionDeals, ...arbitrageDeals)

    // Sort by savings percentage (descending)
    deals.sort((a, b) => b.savingsPercent - a.savingsPercent)

    // Take top N deals
    const topDeals = deals.slice(0, limit)

    const response = NextResponse.json({
      success: true,
      count: topDeals.length,
      deals: topDeals,
      stats: {
        listings: listingDeals.length,
        auctions: auctionDeals.length,
        arbitrage: arbitrageDeals.length,
        totalScanned: deals.length,
        avgSavings: topDeals.reduce((sum, d) => sum + d.savingsPercent, 0) / topDeals.length || 0
      }
    })

    addSecurityHeaders(response)
    return response

  } catch (error) {
    console.error('Top deals API error:', error)
    const response = NextResponse.json(
      { success: false, error: 'Failed to fetch top deals' },
      { status: 500 }
    )
    addSecurityHeaders(response)
    return response
  }
}

// Get top deals from listings
async function getListingDeals(limit: number): Promise<Deal[]> {
  const dbPath = path.join(process.cwd(), 'data', 'handbags.db')
  const db = new Database(dbPath, { readonly: true })

  try {
    const listings = db.prepare(`
      SELECT
        l.id,
        l.title,
        l.brand,
        l.model,
        l.price_usd,
        l.currency,
        l.condition,
        l.product_url,
        l.image_url,
        l.source,
        l.description,
        d.is_deal,
        d.deal_percentage,
        d.avg_us_price,
        d.ai_notes
      FROM listings l
      INNER JOIN deal_analysis d ON l.id = d.listing_id
      WHERE l.is_active = 1
        AND d.is_deal = 1
        AND d.deal_percentage > 10
        AND l.price_usd > 0
      ORDER BY d.deal_percentage DESC
      LIMIT ?
    `).all(limit) as any[]

    return listings.map(listing => ({
      type: 'listing' as const,
      id: listing.id,
      title: listing.title,
      description: listing.ai_notes || listing.description,
      price: listing.price_usd,
      originalPrice: listing.avg_us_price,
      currency: listing.currency || 'USD',
      savings: listing.avg_us_price - listing.price_usd,
      savingsPercent: listing.deal_percentage,
      url: listing.product_url,
      imageUrl: listing.image_url,
      brand: listing.brand,
      model: listing.model,
      condition: listing.condition,
      source: `Marketplace - ${listing.source}`,
      metadata: {
        isDeal: true,
        dealPercentage: listing.deal_percentage,
        estimatedValue: listing.avg_us_price
      }
    }))
  } finally {
    db.close()
  }
}

// Get top deals from auctions
async function getAuctionDeals(limit: number): Promise<Deal[]> {
  const dbPath = path.join(process.cwd(), 'data', 'auction-history', 'auctions.db')
  const db = new Database(dbPath, { readonly: true })

  try {
    const auctions = db.prepare(`
      SELECT
        id,
        auction_id,
        title,
        auction_house,
        current_price,
        estimate_low,
        estimate_high,
        end_date,
        url,
        search_term,
        created_at,
        CASE
          WHEN estimate_low > 0 AND current_price > 0
          THEN ((estimate_low - current_price) / estimate_low * 100)
          ELSE 0
        END as savings_percent,
        CASE
          WHEN estimate_low > 0 AND current_price > 0
          THEN (estimate_low - current_price)
          ELSE 0
        END as savings_amount
      FROM auctions
      WHERE current_price > 0
        AND estimate_low > 0
        AND savings_percent > 20
      ORDER BY savings_percent DESC
      LIMIT ?
    `).all(limit) as any[]

    return auctions.map(auction => {
      // Use search_term which contains brand+model (e.g., "Chanel Boy")
      // This directly maps to our museum image keys
      const museumImage = getKnownMuseumImage(auction.search_term || auction.title, '')

      return {
        type: 'auction' as const,
        id: auction.auction_id || auction.id,
        title: auction.title,
        description: `${auction.auction_house} auction ending ${new Date(auction.end_date).toLocaleDateString()}`,
        price: auction.current_price,
        originalPrice: auction.estimate_low,
        currency: 'USD',
        savings: auction.savings_amount,
        savingsPercent: auction.savings_percent,
        url: auction.url,
        imageUrl: museumImage || undefined, // Real museum images from Met/Smithsonian (convert null to undefined)
        brand: auction.search_term,
        source: `Auction - ${auction.auction_house}`,
        metadata: {
          auctionHouse: auction.auction_house,
          estimateLow: auction.estimate_low,
          estimateHigh: auction.estimate_high,
          endDate: auction.end_date
        }
      }
    })
  } finally {
    db.close()
  }
}

// Get top arbitrage opportunities
async function getArbitrageDeals(limit: number): Promise<Deal[]> {
  const dbPath = path.join(process.cwd(), 'data', 'arbitrage.db')
  const db = new Database(dbPath, { readonly: true })

  try {
    const opportunities = db.prepare(`
      SELECT
        id,
        brand,
        model,
        condition,
        japan_price_jpy,
        japan_source,
        japan_url,
        usa_price_usd,
        hk_price_hkd,
        total_cost_usd,
        potential_profit_usd,
        profit_margin_pct,
        created_at,
        verified
      FROM arbitrage_opportunities
      WHERE is_active = 1
        AND profit_margin_pct > 15
        AND total_cost_usd > 0
      ORDER BY profit_margin_pct DESC
      LIMIT ?
    `).all(limit) as any[]

    return opportunities.map(opp => {
      // Get real museum image for this brand/model
      const museumImage = getKnownMuseumImage(opp.brand, opp.model)

      return {
        type: 'arbitrage' as const,
        id: opp.id,
        title: `${opp.brand} ${opp.model}`,
        description: `Buy in ${opp.japan_source || 'Japan'}, sell in USA - ${opp.profit_margin_pct.toFixed(1)}% profit margin`,
        price: opp.total_cost_usd,
        originalPrice: opp.usa_price_usd,
        currency: 'USD',
        savings: opp.potential_profit_usd,
        savingsPercent: opp.profit_margin_pct,
        url: opp.japan_url,
        imageUrl: museumImage || undefined, // Real museum images from Met/Smithsonian (convert null to undefined)
        brand: opp.brand,
        model: opp.model,
        condition: opp.condition,
        source: 'Arbitrage Opportunity',
        metadata: {
          japanPrice: opp.japan_price_jpy,
          usaPrice: opp.usa_price_usd,
          potentialProfit: opp.potential_profit_usd,
          profitMargin: opp.profit_margin_pct,
          verified: Boolean(opp.verified)
        }
      }
    })
  } finally {
    db.close()
  }
}