`lib/wines.ts`

92 lines
// lib/wines.ts
import { getDb } from './db';

export type Wine = {
  id: number;
  name: string;
  varietal: string | null;
  region: string | null;
  country: string | null;
  vintage: number | null;
  image_url: string | null;
  average_price: number | null;
  critic_scores: any | null;
};

export function searchWines(query?: string, limit = 50): Wine[] {
  const db = getDb();

  if (!query) {
    return db.prepare(`
      SELECT id, name, varietal, region, country, vintage, image_url, average_price, critic_scores
      FROM wines
      ORDER BY created_at DESC
      LIMIT ?
    `).all(limit).map((row: any) => ({
      ...row,
      critic_scores: row.critic_scores ? JSON.parse(row.critic_scores) : null
    }));
  }

  return db.prepare(`
    SELECT id, name, varietal, region, country, vintage, image_url, average_price, critic_scores
    FROM wines
    WHERE name     LIKE '%' || @q || '%'
       OR varietal LIKE '%' || @q || '%'
       OR region   LIKE '%' || @q || '%'
       OR country  LIKE '%' || @q || '%'
    ORDER BY created_at DESC
    LIMIT @limit
  `).all({ q: query, limit }).map((row: any) => ({
    ...row,
    critic_scores: row.critic_scores ? JSON.parse(row.critic_scores) : null
  }));
}

export function getWineById(id: number) {
  const db = getDb();
  const wine = db.prepare(`
    SELECT *
    FROM wines
    WHERE id = ?
  `).get(id);

  if (!wine) return null;

  const latestPrice = db.prepare(`
    SELECT price, vendor, collected_at
    FROM wine_prices
    WHERE wine_id = ?
    ORDER BY collected_at DESC
    LIMIT 1
  `).get(id);

  const ratingInfo = db.prepare(`
    SELECT AVG(rating) AS avg_rating, COUNT(*) AS count_rating
    FROM wine_ratings
    WHERE wine_id = ?
  `).get(id);

  return {
    ...wine,
    critic_scores: (wine as any).critic_scores ? JSON.parse((wine as any).critic_scores) : null,
    latestPrice,
    ratingInfo
  };
}

export type WinePricePoint = { collected_at: string; price: number };

export function getWinePriceHistory(id: number, days = 365): WinePricePoint[] {
  const db = getDb();
  return db.prepare(`
    SELECT collected_at, price
    FROM wine_prices
    WHERE wine_id = ?
      AND collected_at >= datetime('now', '-' || @days || ' days')
    ORDER BY collected_at ASC
  `).all(id, { days }).map((row: any) => ({
    collected_at: row.collected_at,
    price: row.price
  }));
}