← back to Handbag Auth Nextjs

src/app/api/search/unified/route.ts

466 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'

interface SearchResult {
  type: 'listing' | 'auction' | 'arbitrage' | 'price_history'
  id: string | number
  title: string
  description?: string
  price?: number
  currency?: string
  url?: string
  brand?: string
  model?: string
  source: string
  relevance: number
  metadata: Record<string, any>
  created_at?: string
}

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

  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 query = searchParams.get('q')?.trim()
    const sources = searchParams.get('sources')?.split(',') || ['all']
    const limit = Math.min(parseInt(searchParams.get('limit') || '50'), 100)
    const minPrice = parseFloat(searchParams.get('minPrice') || '0')
    const maxPrice = parseFloat(searchParams.get('maxPrice') || '999999')

    if (!query || query.length < 2) {
      const response = NextResponse.json(
        { success: false, error: 'Search query must be at least 2 characters' },
        { status: 400 }
      )
      addSecurityHeaders(response)
      return response
    }

    const results: SearchResult[] = []

    // Search all sources in parallel
    const searchPromises: Promise<SearchResult[]>[] = []

    if (sources.includes('all') || sources.includes('listings')) {
      searchPromises.push(searchListings(query, limit, minPrice, maxPrice))
    }

    if (sources.includes('all') || sources.includes('auctions')) {
      searchPromises.push(searchAuctions(query, limit, minPrice, maxPrice))
    }

    if (sources.includes('all') || sources.includes('arbitrage')) {
      searchPromises.push(searchArbitrage(query, limit, minPrice, maxPrice))
    }

    if (sources.includes('all') || sources.includes('price_history')) {
      searchPromises.push(searchPriceHistory(query, limit))
    }

    const allResults = await Promise.all(searchPromises)

    // Flatten and combine results
    allResults.forEach(sourceResults => {
      results.push(...sourceResults)
    })

    // Sort by relevance (descending)
    results.sort((a, b) => b.relevance - a.relevance)

    // Limit total results
    const limitedResults = results.slice(0, limit)

    const response = NextResponse.json({
      success: true,
      query,
      total: results.length,
      returned: limitedResults.length,
      results: limitedResults,
      sources: sources,
      stats: {
        listings: results.filter(r => r.type === 'listing').length,
        auctions: results.filter(r => r.type === 'auction').length,
        arbitrage: results.filter(r => r.type === 'arbitrage').length,
        price_history: results.filter(r => r.type === 'price_history').length,
      }
    })

    addSecurityHeaders(response)
    return response

  } catch (error) {
    console.error('Unified search error:', error)
    const response = NextResponse.json(
      { success: false, error: 'Search failed' },
      { status: 500 }
    )
    addSecurityHeaders(response)
    return response
  }
}

// Search handbag listings
async function searchListings(
  query: string,
  limit: number,
  minPrice: number,
  maxPrice: number
): Promise<SearchResult[]> {
  const dbPath = path.join(process.cwd(), 'data', 'handbags.db')
  const db = new Database(dbPath, { readonly: true })

  try {
    const searchPattern = `%${query}%`

    const listings = db.prepare(`
      SELECT
        l.id,
        l.title,
        l.brand,
        l.model,
        l.price_usd,
        l.currency,
        l.condition,
        l.product_url,
        l.source,
        l.description,
        l.image_url,
        l.crawled_at,
        d.is_deal,
        d.deal_percentage,
        d.ai_notes
      FROM listings l
      LEFT JOIN deal_analysis d ON l.id = d.listing_id
      WHERE l.is_active = 1
        AND l.price_usd BETWEEN ? AND ?
        AND (
          LOWER(l.title) LIKE LOWER(?) OR
          LOWER(l.brand) LIKE LOWER(?) OR
          LOWER(l.model) LIKE LOWER(?) OR
          LOWER(l.description) LIKE LOWER(?) OR
          LOWER(l.condition) LIKE LOWER(?) OR
          LOWER(d.ai_notes) LIKE LOWER(?)
        )
      ORDER BY
        CASE
          WHEN LOWER(l.brand) = LOWER(?) THEN 3
          WHEN LOWER(l.title) LIKE LOWER(?) THEN 2
          ELSE 1
        END DESC,
        l.crawled_at DESC
      LIMIT ?
    `).all(
      minPrice, maxPrice,
      searchPattern, searchPattern, searchPattern, searchPattern, searchPattern, searchPattern,
      query, `%${query}%`,
      limit
    ) as any[]

    return listings.map(listing => ({
      type: 'listing' as const,
      id: listing.id,
      title: listing.title,
      description: listing.description,
      price: listing.price_usd,
      currency: listing.currency || 'USD',
      url: listing.product_url,
      brand: listing.brand,
      model: listing.model,
      source: `Listing - ${listing.source}`,
      relevance: calculateRelevance(query, listing.title, listing.brand, listing.model),
      created_at: listing.crawled_at,
      metadata: {
        condition: listing.condition,
        isDeal: Boolean(listing.is_deal),
        dealPercentage: listing.deal_percentage,
        aiNotes: listing.ai_notes,
        imageUrl: listing.image_url,
      }
    }))
  } finally {
    db.close()
  }
}

// Search auction data
async function searchAuctions(
  query: string,
  limit: number,
  minPrice: number,
  maxPrice: number
): Promise<SearchResult[]> {
  const dbPath = path.join(process.cwd(), 'data', 'auction-history', 'auctions.db')
  const db = new Database(dbPath, { readonly: true })

  try {
    const searchPattern = `%${query}%`

    const auctions = db.prepare(`
      SELECT
        id,
        auction_id,
        title,
        auction_house,
        current_price,
        estimate_low,
        estimate_high,
        end_date,
        lot_number,
        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
      FROM auctions
      WHERE current_price BETWEEN ? AND ?
        AND (
          LOWER(title) LIKE LOWER(?) OR
          LOWER(search_term) LIKE LOWER(?) OR
          LOWER(auction_house) LIKE LOWER(?)
        )
      ORDER BY created_at DESC
      LIMIT ?
    `).all(minPrice, maxPrice, searchPattern, searchPattern, searchPattern, limit) as any[]

    return auctions.map(auction => ({
      type: 'auction' as const,
      id: auction.auction_id || auction.id,
      title: auction.title,
      price: auction.current_price,
      currency: 'USD',
      url: auction.url,
      brand: auction.search_term,
      source: `Auction - ${auction.auction_house}`,
      relevance: calculateRelevance(query, auction.title, auction.search_term, auction.auction_house),
      created_at: auction.created_at,
      metadata: {
        auctionHouse: auction.auction_house,
        estimateLow: auction.estimate_low,
        estimateHigh: auction.estimate_high,
        endDate: auction.end_date,
        lotNumber: auction.lot_number,
        savingsPercent: auction.savings_percent,
      }
    }))
  } finally {
    db.close()
  }
}

// Search arbitrage opportunities
async function searchArbitrage(
  query: string,
  limit: number,
  minPrice: number,
  maxPrice: number
): Promise<SearchResult[]> {
  const dbPath = path.join(process.cwd(), 'data', 'arbitrage.db')
  const db = new Database(dbPath, { readonly: true })

  try {
    const searchPattern = `%${query}%`

    const opportunities = db.prepare(`
      SELECT
        id,
        brand,
        model,
        condition,
        japan_price_jpy,
        japan_source,
        japan_url,
        usa_price_usd,
        usa_source,
        usa_url,
        hk_price_hkd,
        hk_source,
        hk_url,
        total_cost_usd,
        potential_profit_usd,
        profit_margin_pct,
        cost_breakdown,
        created_at,
        is_active,
        verified
      FROM arbitrage_opportunities
      WHERE is_active = 1
        AND total_cost_usd BETWEEN ? AND ?
        AND (
          LOWER(brand) LIKE LOWER(?) OR
          LOWER(model) LIKE LOWER(?) OR
          LOWER(condition) LIKE LOWER(?)
        )
      ORDER BY profit_margin_pct DESC
      LIMIT ?
    `).all(minPrice, maxPrice, searchPattern, searchPattern, searchPattern, limit) as any[]

    return opportunities.map(opp => ({
      type: 'arbitrage' as const,
      id: opp.id,
      title: `${opp.brand} ${opp.model} - Arbitrage Opportunity`,
      description: `${opp.profit_margin_pct.toFixed(1)}% profit margin - Buy from ${opp.japan_source || opp.hk_source}, sell in USA`,
      price: opp.total_cost_usd,
      currency: 'USD',
      url: opp.japan_url || opp.hk_url,
      brand: opp.brand,
      model: opp.model,
      source: 'Arbitrage Opportunity',
      relevance: calculateRelevance(query, opp.brand, opp.model, opp.condition) + (opp.verified ? 10 : 0),
      created_at: opp.created_at,
      metadata: {
        condition: opp.condition,
        japanPriceJPY: opp.japan_price_jpy,
        japanSource: opp.japan_source,
        japanUrl: opp.japan_url,
        usaPriceUSD: opp.usa_price_usd,
        usaSource: opp.usa_source,
        usaUrl: opp.usa_url,
        hkPriceHKD: opp.hk_price_hkd,
        hkSource: opp.hk_source,
        hkUrl: opp.hk_url,
        totalCostUSD: opp.total_cost_usd,
        potentialProfitUSD: opp.potential_profit_usd,
        profitMarginPct: opp.profit_margin_pct,
        costBreakdown: opp.cost_breakdown,
        verified: Boolean(opp.verified),
      }
    }))
  } finally {
    db.close()
  }
}

// Search price history
async function searchPriceHistory(
  query: string,
  limit: number
): Promise<SearchResult[]> {
  const dbPath = path.join(process.cwd(), 'data', 'handbags.db')
  const db = new Database(dbPath, { readonly: true })

  try {
    const searchPattern = `%${query}%`

    const priceHistory = db.prepare(`
      SELECT
        ph.id,
        ph.listing_id,
        ph.price_usd,
        ph.timestamp,
        l.title,
        l.brand,
        l.model,
        l.source,
        l.product_url
      FROM price_history ph
      INNER JOIN listings l ON ph.listing_id = l.id
      WHERE (
        LOWER(l.title) LIKE LOWER(?) OR
        LOWER(l.brand) LIKE LOWER(?) OR
        LOWER(l.model) LIKE LOWER(?)
      )
      ORDER BY ph.timestamp DESC
      LIMIT ?
    `).all(searchPattern, searchPattern, searchPattern, limit) as any[]

    // Group by listing to show price trends
    const groupedByListing = new Map<number, any[]>()
    priceHistory.forEach(ph => {
      if (!groupedByListing.has(ph.listing_id)) {
        groupedByListing.set(ph.listing_id, [])
      }
      groupedByListing.get(ph.listing_id)!.push(ph)
    })

    const results: SearchResult[] = []
    groupedByListing.forEach((history, listingId) => {
      const latest = history[0]
      const oldest = history[history.length - 1]
      const priceChange = latest.price_usd - oldest.price_usd
      const priceChangePercent = ((priceChange / oldest.price_usd) * 100).toFixed(1)

      results.push({
        type: 'price_history' as const,
        id: listingId,
        title: latest.title,
        description: `Price ${priceChange >= 0 ? 'increased' : 'decreased'} by ${Math.abs(parseFloat(priceChangePercent))}% (${history.length} price points)`,
        price: latest.price_usd,
        currency: 'USD',
        url: latest.product_url,
        brand: latest.brand,
        model: latest.model,
        source: `Price History - ${latest.source}`,
        relevance: calculateRelevance(query, latest.title, latest.brand, latest.model),
        created_at: latest.timestamp,
        metadata: {
          priceHistory: history.map(h => ({
            price: h.price_usd,
            date: h.timestamp
          })),
          oldestPrice: oldest.price_usd,
          latestPrice: latest.price_usd,
          priceChange,
          priceChangePercent: parseFloat(priceChangePercent),
          dataPoints: history.length,
        }
      })
    })

    return results
  } finally {
    db.close()
  }
}

// Calculate relevance score for ranking
function calculateRelevance(
  query: string,
  ...fields: (string | null | undefined)[]
): number {
  let score = 0
  const queryLower = query.toLowerCase()

  fields.forEach((field, index) => {
    if (!field) return

    const fieldLower = field.toLowerCase()

    // Exact match (highest score)
    if (fieldLower === queryLower) {
      score += 100 - (index * 10)
    }
    // Starts with query
    else if (fieldLower.startsWith(queryLower)) {
      score += 50 - (index * 5)
    }
    // Contains query
    else if (fieldLower.includes(queryLower)) {
      score += 25 - (index * 2)
    }
  })

  return score
}