`lib/portfolio.js`

160 lines
// portfolio.js — shared helpers for portfolio_holdings.
//
// The enriched-list query joins each holding to its canonical asset, brand,
// family, and the latest valuation_snapshot via a LATERAL subquery (mirrors
// lib/valuation.js listAssets()), then computes unrealized_pl and pl_pct in
// JS so callers (API + console) get identical shapes.

const { pool } = require('./db');

const EMAIL_RE = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;

function isValidEmail(s) {
  return typeof s === 'string' && EMAIL_RE.test(s.trim());
}

async function findAssetIdBySlug(slug) {
  if (typeof slug !== 'string' || !slug.trim()) return null;
  const r = await pool.query(
    `SELECT id FROM canonical_assets WHERE slug = $1`,
    [slug.trim()]
  );
  return r.rows[0] ? r.rows[0].id : null;
}

async function listHoldings(ownerEmail) {
  const r = await pool.query(
    `SELECT ph.id,
            ph.owner_email,
            ph.canonical_asset_id,
            ph.acquired_at,
            ph.acquisition_basis,
            ph.notes,
            ph.created_at,
            ph.updated_at,
            ca.slug    AS asset_slug,
            mf.name    AS family,
            mf.slug    AS family_slug,
            b.name     AS brand,
            b.slug     AS brand_slug,
            ca.size    AS size,
            ca.material AS material,
            ca.color   AS color,
            vs.q10     AS q10,
            vs.q50     AS q50,
            vs.q90     AS q90,
            vs.as_of   AS snapshot_as_of
       FROM portfolio_holdings ph
       JOIN canonical_assets ca ON ca.id = ph.canonical_asset_id
       JOIN model_families mf   ON mf.id = ca.model_family_id
       JOIN brands b            ON b.id  = mf.brand_id
  LEFT JOIN LATERAL (
            SELECT q10, q50, q90, as_of
              FROM valuation_snapshots vs
             WHERE vs.canonical_asset_id = ca.id
             ORDER BY vs.as_of DESC
             LIMIT 1
       ) vs ON true
      WHERE ph.owner_email = $1
      ORDER BY ph.acquired_at DESC NULLS LAST, ph.created_at DESC`,
    [ownerEmail]
  );
  return r.rows.map(enrich);
}

function enrich(row) {
  const basis = numOrNull(row.acquisition_basis);
  const q50 = numOrNull(row.q50);
  let unrealized_pl = null;
  let pl_pct = null;
  if (basis !== null && q50 !== null) {
    unrealized_pl = +(q50 - basis).toFixed(2);
    if (basis !== 0) pl_pct = +((q50 - basis) / basis).toFixed(4);
  }
  let days_held = null;
  if (row.acquired_at) {
    const acq = new Date(row.acquired_at);
    if (!isNaN(acq.getTime())) {
      days_held = Math.max(0, Math.floor((Date.now() - acq.getTime()) / 86400000));
    }
  }
  return {
    ...row,
    acquisition_basis: basis,
    q10: numOrNull(row.q10),
    q50,
    q90: numOrNull(row.q90),
    unrealized_pl,
    pl_pct,
    days_held
  };
}

function numOrNull(v) {
  if (v === null || v === undefined) return null;
  const n = typeof v === 'string' ? parseFloat(v) : v;
  return Number.isFinite(n) ? n : null;
}

async function insertHolding({ owner_email, asset_slug, acquired_at, acquisition_basis, notes }) {
  const assetId = await findAssetIdBySlug(asset_slug);
  if (!assetId) {
    const err = new Error('invalid_asset_slug');
    err.code = 'invalid_asset_slug';
    throw err;
  }
  const r = await pool.query(
    `INSERT INTO portfolio_holdings (owner_email, canonical_asset_id, acquired_at, acquisition_basis, notes)
     VALUES ($1, $2, $3, $4, $5)
     RETURNING *`,
    [
      owner_email.trim(),
      assetId,
      acquired_at || null,
      acquisition_basis === undefined || acquisition_basis === null || acquisition_basis === '' ? null : acquisition_basis,
      notes || null
    ]
  );
  return r.rows[0];
}

async function patchHolding(id, fields) {
  const allowed = ['acquired_at', 'acquisition_basis', 'notes'];
  const sets = [];
  const vals = [];
  let i = 1;
  for (const key of allowed) {
    if (Object.prototype.hasOwnProperty.call(fields, key)) {
      sets.push(`${key} = $${i++}`);
      vals.push(fields[key] === '' ? null : fields[key]);
    }
  }
  if (!sets.length) return { error: 'no_fields_to_update' };
  sets.push(`updated_at = now()`);
  vals.push(id);
  const r = await pool.query(
    `UPDATE portfolio_holdings SET ${sets.join(', ')} WHERE id = $${i} RETURNING *`,
    vals
  );
  if (!r.rows[0]) return { error: 'not_found' };
  return { row: r.rows[0] };
}

async function deleteHolding(id) {
  const r = await pool.query(
    `DELETE FROM portfolio_holdings WHERE id = $1 RETURNING id`,
    [id]
  );
  return r.rowCount > 0;
}

module.exports = {
  EMAIL_RE,
  isValidEmail,
  findAssetIdBySlug,
  listHoldings,
  insertHolding,
  patchHolding,
  deleteHolding
};