// Generated, do not edit. sha256:bbb7036ed8755194a6ae32362bf5faec1d944b2733c93e94b0d21804b434b99e
import { createHash } from 'node:crypto';

import * as db from '../db.js';
import { CONFIG } from '../../shared/config.js';
import { fail } from '../../shared/app-error.js';
import { listOpenValuationDiffs } from './valuations.js';
import { listPriceGaps, getPricePoint } from './price-history.js';
import { trackUpdateMany } from './change-log.js';
import { positionsByAsset } from './positions.js';
import { daysBetween } from './_shared.js';
import { healthChecks } from '../registers.mjs';

function rij(row, counts = {}) {
  return { row, counts };
}

function dubbeleEvents() {
  return db.all(`SELECT Event.assetId, Asset.symbol, Event.type, Event.occurredAt,
                        Event.productId, MetalProduct.name AS product_name, COUNT(*) AS n
                 FROM Event
                 JOIN Asset ON Asset.id = Event.assetId
                 LEFT JOIN MetalProduct ON MetalProduct.id = Event.productId
                 WHERE Event.type <> 'reservation'
                 GROUP BY Event.assetId, Event.type, Event.occurredAt, Event.amount, Event.productId
                 HAVING COUNT(*) > 1
                 ORDER BY n DESC, Event.occurredAt DESC`)
    .map(r => rij({ assetId: r.assetId, symbol: r.symbol, type: r.type, occurredAt: r.occurredAt,
      productId: r.productId, product_name: r.product_name }, { keer: r.n }));
}

function assetsZonderBalans() {
  return db.all(`SELECT Asset.id AS assetId, Asset.symbol, COUNT(Event.id) AS events
                 FROM Asset
                 JOIN Event ON Event.assetId = Asset.id AND Event.type <> 'reservation'
                 WHERE NOT EXISTS (SELECT 1 FROM Valuation WHERE Valuation.assetId = Asset.id)
                 GROUP BY Asset.id, Asset.symbol
                 ORDER BY events DESC`)
    .map(r => rij({ assetId: r.assetId, symbol: r.symbol }, { boekingen: r.events }));
}

function onverzoendeBalansen() {
  const symbolen = new Map(db.all(`SELECT id, symbol FROM Asset`).map(a => [a.id, a.symbol]));
  return listOpenValuationDiffs()
    .map(v => rij({
      assetId: v.assetId, symbol: symbolen.get(v.assetId) ?? null,
      accountId: v.accountId, valuedOn: v.valuedOn,
    }));
}

const LAATSTE_KOERS = `(SELECT MAX(observedAt) FROM PriceHistory WHERE PriceHistory.assetId = Asset.id)`;
function achterlopendeKoersen() {
  return db.all(`SELECT Asset.id AS assetId, Asset.symbol,
                        MAX(Event.occurredAt) AS occurredAt,
                        ${LAATSTE_KOERS} AS observedAt
                 FROM Asset
                 JOIN Event ON Event.assetId = Asset.id AND Event.type <> 'reservation'
                 GROUP BY Asset.id, Asset.symbol
                 HAVING ${LAATSTE_KOERS} IS NULL OR ${LAATSTE_KOERS} < MAX(Event.occurredAt)
                 ORDER BY Asset.symbol`)
    .map(r => rij(r));
}

const EERSTE_KOERS = `(SELECT MIN(observedAt) FROM PriceHistory WHERE PriceHistory.assetId = Asset.id)`;
function koersenBeginnenTeLaat() {
  const maandDag = `${String(CONFIG.PEILDATUM.MONTH).padStart(2, '0')}-${String(CONFIG.PEILDATUM.DAY).padStart(2, '0')}`;
  const EERSTE_PEILDATUM = `(CAST(strftime('%Y', MIN(Event.occurredAt)) AS INTEGER) + 1) || '-${maandDag}'`;
  return db.all(`SELECT Asset.id AS assetId, Asset.symbol,
                        MIN(Event.occurredAt) AS occurredAt,
                        ${EERSTE_KOERS} AS observedAt
                 FROM Asset
                 JOIN Event ON Event.assetId = Asset.id AND Event.type <> 'reservation'
                 GROUP BY Asset.id, Asset.symbol
                 HAVING ${EERSTE_KOERS} IS NOT NULL
                    AND substr(${EERSTE_KOERS}, 1, 10) > ${EERSTE_PEILDATUM}
                 ORDER BY Asset.symbol`)
    .map(r => rij(r));
}

function gatenInKoershistorie() {
  const perAsset = new Map();
  for (const g of listPriceGaps({ head: false, tail: false, limit: null }).rows) {
    const a = perAsset.get(g.assetId) || { assetId: g.assetId, symbol: g.symbol, gaten: 0, grootste: 0 };
    a.gaten++;
    a.grootste = Math.max(a.grootste, g.days);
    perAsset.set(g.assetId, a);
  }
  return [...perAsset.values()].sort((x, y) => y.grootste - x.grootste)
    .map(r => rij({ assetId: r.assetId, symbol: r.symbol }, { gaten: r.gaten, grootsteGatInDagen: r.grootste }));
}

function koersGestopt() {
  const vandaag = new Date().toISOString().slice(0, 10);
  const uit = [];
  for (const a of positionsByAsset()) {
    const grens = CONFIG.PRICE_HISTORY.CARRY_FORWARD_DAYS[a.kind];
    if (!(a.net > 0) || grens == null) continue;
    const laatste = db.one(`SELECT MAX(observedAt) AS observedAt FROM PriceHistory WHERE assetId = ? AND fiatPrice > 0`, [a.id]).observedAt;
    if (!laatste) continue;
    const dagen = daysBetween(laatste.slice(0, 10), vandaag);
    if (dagen > grens) uit.push({ dagen, r: rij({ assetId: a.id, symbol: a.symbol, observedAt: laatste }, { dagenZonderKoers: dagen }) });
  }
  return uit.sort((x, y) => y.dagen - x.dagen).map(x => x.r);
}

function verplaatsingZonderTegenkant() {
  return db.all(`SELECT Event.id AS eventId, Event.assetId, Asset.symbol, Event.occurredAt,
                        Event.fromAccountId, vanaf.name AS from_name,
                        Event.toAccountId, naar.name AS to_name
                 FROM Event
                 JOIN Asset ON Asset.id = Event.assetId
                 LEFT JOIN Account vanaf ON vanaf.id = Event.fromAccountId
                 LEFT JOIN Account naar  ON naar.id  = Event.toAccountId
                 WHERE Event.type = 'transfer'
                   AND (Event.fromAccountId IS NULL OR Event.toAccountId IS NULL)
                   AND (Event.externalAddress IS NULL OR Event.externalAddress = '')
                   AND Event.priceOrigin IS NOT 'none'
                 ORDER BY Event.occurredAt DESC`)
    .map(r => rij(r));
}

function kostenZonderEffect() {
  return db.all(`SELECT Event.id AS eventId, Event.assetId, Asset.symbol, Event.type, Event.occurredAt,
                        Event.source
                 FROM Event
                 JOIN Asset ON Asset.id = Event.assetId
                 WHERE (Event.type = 'interest' AND Event.fiatFee > 0)
                    OR (Event.type IN ('buy','interest') AND Event.unitFee > 0)
                 ORDER BY Event.occurredAt DESC`)
    .map(r => rij(r));
}

function ongekoppeldeBewegingen() {
  return db.all(`SELECT WalletTransaction.id AS walletTransactionId, WalletTransaction.accountId,
                        Account.name AS account_name,
                        WalletTransaction.direction, WalletTransaction.blockTime,
                        WalletTransaction.createdAt
                 FROM WalletTransaction
                 LEFT JOIN Account ON Account.id = WalletTransaction.accountId
                 -- Lightning only from the node, the same rule as syncTxProposals (proposals.js): an NWC
                 -- payment without its own event is not unlinked, it hangs on its LND twin; on an account
                 -- without a LND source the NWC wallet counts on its own.
                 WHERE (WalletTransaction.layer = 'onchain'
                        OR EXISTS (SELECT 1 FROM WalletSource
                                   WHERE WalletSource.id = WalletTransaction.sourceId
                                     AND (WalletSource.kind = 'lnd'
                                          OR (WalletSource.kind = 'nwc' AND NOT EXISTS (
                                                SELECT 1 FROM WalletSource node
                                                WHERE node.accountId = WalletTransaction.accountId
                                                  AND node.kind = 'lnd')))))
                   AND NOT EXISTS (SELECT 1 FROM WalletTransactionEventLink
                                   WHERE WalletTransactionEventLink.walletTransactionId = WalletTransaction.id)
                   AND NOT EXISTS (SELECT 1 FROM ReconcileProposal
                                   WHERE ReconcileProposal.walletTransactionId = WalletTransaction.id
                                     AND ReconcileProposal.status = 'pending')
                 ORDER BY WalletTransaction.blockTime DESC`)
    .map(r => rij(r));
}

function dekkingZonderAsset() {
  return db.all(`SELECT ProviderCoverage.providerId, ProviderCoverage.symbol
                 FROM ProviderCoverage
                 WHERE NOT EXISTS (SELECT 1 FROM Asset WHERE Asset.symbol = ProviderCoverage.symbol)
                 ORDER BY ProviderCoverage.symbol`)
    .map(r => rij(r));
}

function koersRijen() {
  return db.all(`SELECT PriceHistory.id AS priceHistoryId, PriceHistory.assetId, Asset.symbol, Asset.kind,
                        PriceHistory.providerId, Provider.name AS provider_name, PriceHistory.resolution,
                        PriceHistory.observedAt, PriceHistory.fiatPrice
                   FROM PriceHistory
                   JOIN Asset ON Asset.id = PriceHistory.assetId
                   JOIN Provider ON Provider.id = PriceHistory.providerId
                  ORDER BY PriceHistory.assetId, PriceHistory.observedAt, PriceHistory.id`);
}
const koersRij = (r, counts) => rij({ priceHistoryId: r.priceHistoryId, assetId: r.assetId, symbol: r.symbol,
  observedAt: r.observedAt, providerId: r.providerId, provider_name: r.provider_name }, counts);
const factor = x => Math.round(x * 10) / 10;

function reeksenPerAsset() {
  const m = new Map();
  for (const r of koersRijen()) {
    if (!(r.fiatPrice > 0)) continue;
    if (!m.has(r.assetId)) m.set(r.assetId, []);
    m.get(r.assetId).push(r);
  }
  return m;
}
const isOunceJump = ratio => {
  const oz = CONFIG.METALS.UNITS.ozt, tol = CONFIG.HYGIENE.TROY_OUNCE_TOLERANCE;
  const bij = (x, doel) => x >= doel * (1 - tol) && x <= doel * (1 + tol);
  return bij(ratio, oz) || bij(ratio, 1 / oz);
};

function eenheidsverwisseling() {
  const uit = [];
  for (const reeks of reeksenPerAsset().values()) {
    for (let i = 1; i < reeks.length; i++) {
      const ratio = reeks[i].fiatPrice / reeks[i - 1].fiatPrice;
      if (isOunceJump(ratio)) uit.push(koersRij(reeks[i], { factor: factor(ratio >= 1 ? ratio : 1 / ratio) }));
    }
  }
  return uit;
}

function uitschieters() {
  const f = CONFIG.HYGIENE.OUTLIER_FACTOR;
  const uit = [];
  for (const reeks of reeksenPerAsset().values()) {
    for (let i = 1; i < reeks.length - 1; i++) {
      const voor = reeks[i].fiatPrice / reeks[i - 1].fiatPrice, na = reeks[i].fiatPrice / reeks[i + 1].fiatPrice;
      if (isOunceJump(voor) || isOunceJump(na)) continue;
      const omhoog = voor >= f && na >= f, omlaag = voor <= 1 / f && na <= 1 / f;
      if (omhoog || omlaag) uit.push(koersRij(reeks[i], { factor: factor(omhoog ? Math.min(voor, na) : 1 / Math.max(voor, na)) }));
    }
  }
  return uit;
}

function bronnenOneens() {
  const grens = CONFIG.HYGIENE.SOURCES_DISAGREE_SHARE;
  const perDag = new Map();
  for (const r of koersRijen()) {
    if (r.resolution !== 'day' || !(r.fiatPrice > 0)) continue;
    const sleutel = `${r.assetId}|${r.observedAt.slice(0, 10)}`;
    if (!perDag.has(sleutel)) perDag.set(sleutel, []);
    perDag.get(sleutel).push(r);
  }
  const uit = [];
  for (const rijen of perDag.values()) {
    if (new Set(rijen.map(r => r.providerId)).size < 2) continue;
    const laag = rijen.reduce((a, b) => (b.fiatPrice < a.fiatPrice ? b : a));
    const hoog = rijen.reduce((a, b) => (b.fiatPrice > a.fiatPrice ? b : a));
    const spreiding = hoog.fiatPrice / laag.fiatPrice - 1;
    if (spreiding > grens && laag.providerId !== hoog.providerId) {
      uit.push(rij({ assetId: laag.assetId, symbol: laag.symbol, observedAt: laag.observedAt,
        low_provider_name: laag.provider_name, high_provider_name: hoog.provider_name },
      { verschilProcent: Math.round(spreiding * 1000) / 10 }));
    }
  }
  return uit;
}

function koersNietPositief() {
  return koersRijen().filter(r => r.fiatPrice <= 0).map(r => koersRij(r, {}));
}

function koersInToekomst() {
  const nu = new Date().toISOString();
  return koersRijen().filter(r => r.observedAt > nu).map(r => koersRij(r, {}));
}

function boekingenMetDagkoers({ origin } = {}) {
  const rijen = db.all(`SELECT Event.id AS eventId, Event.assetId, Asset.symbol, Event.type, Event.occurredAt,
                               Event.priceOrigin, Event.fiatPrice
                          FROM Event
                          JOIN Asset ON Asset.id = Event.assetId
                         WHERE Event.type IN ('buy','sell') AND Event.fiatPrice > 0
                           AND Event.priceOrigin IS NOT 'none'
                           AND (?1 = 0 OR Event.priceOrigin IS NULL)
                         ORDER BY Event.occurredAt DESC, Event.id DESC`, [origin === 'unknown' ? 1 : 0]);
  const uit = [];
  for (const r of rijen) {
    const dag = getPricePoint(r.assetId, String(r.occurredAt).slice(0, 10));
    if (dag && dag.fiatPrice > 0) uit.push({ ...r, ratio: r.fiatPrice / dag.fiatPrice });
  }
  return uit;
}
const boekingRij = (r, counts) => rij({ eventId: r.eventId, assetId: r.assetId, symbol: r.symbol, type: r.type,
  occurredAt: r.occurredAt, priceOrigin: r.priceOrigin }, counts);

function boekingsprijsWijktAf() {
  const grens = CONFIG.HYGIENE.BOOKING_PRICE_DEVIATION;
  const uit = [];
  for (const r of boekingenMetDagkoers()) {
    if (isOunceJump(r.ratio)) uit.push(boekingRij(r, { factor: factor(r.ratio >= 1 ? r.ratio : 1 / r.ratio) }));
    else if (Math.abs(r.ratio - 1) > grens) uit.push(boekingRij(r, { afwijkingProcent: Math.round((r.ratio - 1) * 1000) / 10 }));
  }
  return uit;
}

const SAME_PRICE_SHARE = 0.0005;
const afleidbaar = () => boekingenMetDagkoers({ origin: 'unknown' })
  .filter(r => Math.abs(r.ratio - 1) <= SAME_PRICE_SHARE);
function herkomstAfleidbaar() {
  return afleidbaar().map(r => boekingRij(r, {}));
}

export async function fillDerivablePriceOrigins() {
  const ids = afleidbaar().map(r => r.eventId);
  if (!ids.length) return 0;
  trackUpdateMany('Event', ids, () => db.run(
    `UPDATE Event SET priceOrigin = 'market_day'
      WHERE priceOrigin IS NULL AND id IN (${ids.map(() => '?').join(',')})`, ids));
  await db.persist();
  return ids.length;
}

const CHECKS = Object.freeze([
  { kind: 'duplicate_events', severity: 'warn', run: dubbeleEvents, countsFrom: ['id'] },
  { kind: 'assets_without_balance', severity: 'info', run: assetsZonderBalans, countsFrom: ['id'] },
  { kind: 'unreconciled_valuations', severity: 'warn', run: onverzoendeBalansen, countsFrom: [] },
  { kind: 'stale_prices', severity: 'warn', run: achterlopendeKoersen, countsFrom: [] },
  { kind: 'late_price_history', severity: 'warn', run: koersenBeginnenTeLaat, countsFrom: [] },
  { kind: 'price_gaps', severity: 'info', run: gatenInKoershistorie, countsFrom: ['observedAt'] },
  { kind: 'price_stopped', severity: 'warn', run: koersGestopt, countsFrom: ['observedAt'] },
  { kind: 'transfer_without_counterpart', severity: 'warn', run: verplaatsingZonderTegenkant, countsFrom: [] },
  { kind: 'fee_without_effect', severity: 'info', run: kostenZonderEffect, countsFrom: [] },
  { kind: 'unlinked_transactions', severity: 'warn', run: ongekoppeldeBewegingen, countsFrom: [] },
  { kind: 'coverage_without_asset', severity: 'info', run: dekkingZonderAsset, countsFrom: [] },
  { kind: 'price_unit_swap', severity: 'warn', run: eenheidsverwisseling, countsFrom: ['fiatPrice'] },
  { kind: 'price_outlier', severity: 'warn', run: uitschieters, countsFrom: ['fiatPrice'] },
  { kind: 'price_sources_disagree', severity: 'info', run: bronnenOneens, countsFrom: ['fiatPrice'] },
  { kind: 'price_not_positive', severity: 'warn', run: koersNietPositief, countsFrom: [] },
  { kind: 'price_in_future', severity: 'warn', run: koersInToekomst, countsFrom: [] },
  { kind: 'booking_price_deviates', severity: 'warn', run: boekingsprijsWijktAf, countsFrom: ['fiatPrice'] },
  { kind: 'price_origin_derivable', severity: 'info', run: herkomstAfleidbaar, countsFrom: ['fiatPrice'] },
]);

export function hygieneKinds() {
  return checks().map(c => ({ kind: c.kind, severity: c.severity }));
}

function checks() {
  const extra = healthChecks.list().map(e => ({ kind: e.id, severity: e.severity, run: e.run, countsFrom: e.countsFrom }));
  const dubbel = extra.find(e => CHECKS.some(c => c.kind === e.kind));
  if (dubbel) throw new Error(`health check '${dubbel.kind}' is registered and also a core check`);
  return [...CHECKS, ...extra];
}

export function hygieneFingerprint(kind, row = {}, counts = {}) {
  const vast = obj => JSON.stringify(Object.keys(obj).sort().map(k => [k, obj[k]]));
  return createHash('sha256').update(`${kind}|${vast(row)}|${vast(counts)}`).digest('hex').slice(0, 32);
}

export function listHygieneAcceptances() {
  return db.all(`SELECT id, kind, fingerprint, summary, note, createdAt
                   FROM HygieneAcceptance ORDER BY createdAt DESC, id DESC`);
}

export async function acceptHygieneFinding({ kind, row = {}, counts = {}, summary, note = null } = {}) {
  if (!checks().some(c => c.kind === kind)) {
    fail('health.acceptUnknownKind', {}, `Unknown hygiene finding kind '${kind}'.`);
  }
  if (!summary || !String(summary).trim()) {
    fail('health.acceptNeedsSummary', {}, 'A hygiene acceptance needs a readable summary.');
  }
  const fingerprint = hygieneFingerprint(kind, row, counts);
  db.run(`INSERT INTO HygieneAcceptance (kind, fingerprint, summary, note, createdAt)
          VALUES (?,?,?,?,?)
          ON CONFLICT(fingerprint) DO UPDATE SET
            summary = excluded.summary, note = excluded.note, createdAt = excluded.createdAt`,
    [kind, fingerprint, String(summary).trim(), note ? String(note).trim() : null, new Date().toISOString()]);
  await db.persist();
  return fingerprint;
}

export async function unacceptHygieneFinding(id) {
  db.run(`DELETE FROM HygieneAcceptance WHERE id = ?`, [id]);
  await db.persist();
}

export function hygieneReport({ kinds = null } = {}) {
  const alle = checks();
  const gekozen = kinds ? alle.filter(c => kinds.includes(c.kind)) : alle;
  const grens = CONFIG.HYGIENE.MAX_ROWS_PER_FINDING;
  const aanvaard = new Set(db.all(`SELECT fingerprint FROM HygieneAcceptance`).map(r => r.fingerprint));
  const bevindingen = gekozen.map(c => {
    const alles = aanvaard.size
      ? c.run().filter(r => !aanvaard.has(hygieneFingerprint(c.kind, r.row, r.counts)))
      : c.run();
    return {
      kind: c.kind, severity: c.severity, countsFrom: c.countsFrom,
      total: alles.length,
      rows: alles.length > grens ? alles.slice(0, grens) : alles,
    };
  });
  return {
    total: bevindingen.reduce((n, b) => n + b.total, 0),
    findings: bevindingen,
  };
}