`lib/portfolio.ts`

40 lines
// lib/portfolio.ts
import { getDb } from './db';

export function getUserPortfolio(userId: number) {
  const db = getDb();

  const rows = db.prepare(`
    WITH latest_prices AS (
      SELECT wp.wine_id,
             wp.price AS current_price
      FROM wine_prices wp
      JOIN (
        SELECT wine_id, MAX(collected_at) AS max_ts
        FROM wine_prices
        GROUP BY wine_id
      ) latest ON latest.wine_id = wp.wine_id
              AND latest.max_ts = wp.collected_at
    )
    SELECT
      uwh.id,
      w.id      AS wine_id,
      w.name,
      w.varietal,
      w.region,
      w.country,
      uwh.units,
      uwh.cost_basis,
      lp.current_price,
      (uwh.units * lp.current_price)       AS market_value,
      (uwh.cost_basis * uwh.units)         AS cost_total,
      ((lp.current_price - uwh.cost_basis) / uwh.cost_basis) * 100.0 AS pct_change
    FROM user_wine_holdings uwh
    JOIN wines w       ON w.id = uwh.wine_id
    JOIN latest_prices lp ON lp.wine_id = uwh.wine_id
    WHERE uwh.user_id = ?
    ORDER BY pct_change DESC
  `).all(userId);

  return rows;
}