AtreidesAssetsFree/server/data/metals.js

// Generated, do not edit. sha256:8eb5f3330fcdfdbe9052fd4560a6ccd48f5f3aa7ef341ffd35df63e368fe3a71
import * as db from '../db.js';
import { toSearchName } from '../../shared/search-name.js';
import { ENUMS } from '../../shared/config.js';
import { assertEnum } from './_shared.js';
import { fail } from '../../shared/app-error.js';
import { trackDelete, trackUpdate } from './change-log.js';

export function hasMetalAssets() {
  return db.one(`SELECT 1 AS x FROM Asset WHERE kind = 'metal' LIMIT 1`) != null;
}
export function listMetalAssets() {
  return db.all(`SELECT * FROM Asset WHERE kind = 'metal' ORDER BY symbol`);
}
export function listMetalProducts() {
  return db.all(`SELECT mp.*, a.symbol AS asset_symbol, a.name AS asset_name
                 FROM MetalProduct mp JOIN Asset a ON a.id = mp.assetId
                 ORDER BY a.symbol, mp.form, mp.fineGrams`);
}
export function listMetalProductsForAsset(assetId) {
  return db.all(`SELECT * FROM MetalProduct WHERE assetId = ? ORDER BY form, fineGrams`, [assetId]);
}
function assertMetalProduct({ assetId, name, form, fineGrams }) {
  assertEnum('form', ENUMS.METAL_FORM, form);
  if (!Number.isInteger(assetId)) fail('metalProducts.assetRequired', null, 'Pick a valid metal asset.');
  if (!String(name || '').trim()) fail('metalProducts.nameRequired', null, 'The name is required.');
  if (!Number.isInteger(fineGrams) || fineGrams <= 0) fail('metalProducts.fineGramsPositive', null, 'The fine weight has to be greater than 0.');
  const a = db.one(`SELECT kind FROM Asset WHERE id = ?`, [assetId]);
  if (!a || a.kind !== 'metal') fail('metalProducts.notMetal', null, 'The chosen asset is not a metal.');
}
export async function createMetalProduct(p) {
  assertMetalProduct(p);
  db.run(`INSERT INTO MetalProduct (assetId, name, searchName, form, fineGrams) VALUES (?,?,?,?,?)`,
    [p.assetId, p.name.trim(), toSearchName(p.name), p.form, p.fineGrams]);
  const id = db.lastId();
  await db.persist();
  return id;
}
export async function updateMetalProduct(id, p) {
  assertMetalProduct(p);
  trackUpdate('MetalProduct', id, () =>
    db.run(`UPDATE MetalProduct SET assetId = ?, name = ?, searchName = ?, form = ?, fineGrams = ? WHERE id = ?`,
      [p.assetId, p.name.trim(), toSearchName(p.name), p.form, p.fineGrams, id]));
  await db.persist();
}
export async function deleteMetalProduct(id) {
  const used = db.one(`SELECT COUNT(*) AS n FROM Event WHERE productId = ?`, [id]).n;
  if (used > 0) fail('metalProducts.inUse', { n: used }, `Product is used by ${used} event(s); remove those first.`);
  trackDelete('MetalProduct', id, () => db.run(`DELETE FROM MetalProduct WHERE id = ?`, [id]));
  await db.persist();
}

export function metalHoldings({ ownOnly = false } = {}) {
  return db.all(`
    SELECT mp.id, mp.name, mp.form, mp.fineGrams, mp.assetId,
           a.symbol AS asset_symbol, a.name AS asset_name,
           COALESCE(SUM(CASE
             WHEN e.type = 'buy'  AND e.toIn   THEN e.productCount
             WHEN e.type = 'sell' AND e.fromIn THEN -e.productCount
             -- Only a transfer between two ACCOUNTS of which one is own: a transfer out of the
             -- administration counts no pieces in either mode, so the two modes agree without a scope.
             WHEN e.type = 'transfer' AND ?1 = 1 AND e.toAccountId IS NOT NULL AND e.fromAccountId IS NOT NULL THEN
                  (CASE WHEN e.toIn   AND NOT e.fromIn THEN e.productCount ELSE 0 END)
                - (CASE WHEN e.fromIn AND NOT e.toIn   THEN e.productCount ELSE 0 END)
             ELSE 0 END), 0) AS net_count,
           COALESCE(SUM(CASE WHEN e.type = 'buy' AND e.toIn THEN e.productCount ELSE 0 END), 0) AS buy_count,
           COALESCE(SUM(CASE WHEN e.type = 'buy' AND e.toIn AND e.priceOrigin IS NOT 'none' THEN 1 ELSE 0 END), 0) AS buy_events,
           COALESCE(SUM(CASE WHEN e.type = 'buy' AND e.toIn AND e.priceOrigin IS NOT 'none' THEN COALESCE(e.fiatFee,0) ELSE 0 END), 0) AS buy_premium,
           COALESCE(SUM(CASE WHEN e.type = 'buy' AND e.toIn AND e.priceOrigin IS NOT 'none' THEN e.amount ELSE 0 END), 0) AS buy_amount,
           COALESCE(SUM(CASE WHEN e.type = 'buy' AND e.toIn AND e.priceOrigin IS NOT 'none' THEN e.amount * COALESCE(e.fiatPrice,0) ELSE 0 END), 0) AS buy_spot_raw,
           COALESCE(SUM(CASE WHEN e.priceOrigin IS 'none' THEN
             (CASE WHEN e.type = 'buy' AND e.toIn THEN e.amount WHEN e.type = 'sell' AND e.fromIn THEN -e.amount ELSE 0 END)
             ELSE 0 END), 0) AS gift_amount
    FROM MetalProduct mp
    JOIN Asset a ON a.id = mp.assetId
    LEFT JOIN (
      SELECT Event.*,
             (?1 = 0 OR toAccount.wealthScope   IS NULL) AS toIn,
             (?1 = 0 OR fromAccount.wealthScope IS NULL) AS fromIn
        FROM Event
        LEFT JOIN Account toAccount   ON toAccount.id   = Event.toAccountId
        LEFT JOIN Account fromAccount ON fromAccount.id = Event.fromAccountId
       WHERE Event.productCount IS NOT NULL
    ) e ON e.productId = mp.id
    GROUP BY mp.id
    HAVING net_count > 0
    ORDER BY a.symbol, mp.form, mp.fineGrams`, [ownOnly ? 1 : 0]);
}