src/settlementsApi.js

const { Router } = require('express');
const { db, newId, h, bad, conflict, notFound, scope, loadEvent, BET_TYPES } = require('./core.js');

const r = Router();

/** Did this bet win, given the declared frame? */
function isWinner(bet, order) {
  const sels = JSON.parse(bet.selections_json);
  switch (bet.bet_type) {
    case 'win':   return sels[0] === order[0];
    case 'place': return order.slice(0, 2).includes(sels[0]);
    case 'show':  return order.slice(0, 3).includes(sels[0]);
    default:      return sels.length === BET_TYPES[bet.bet_type].picks
                      && sels.every((s, i) => s === order[i]);   // exotics are strictly ordered
  }
}

/**
 * Work out every payout without writing anything. Used both by the preview
 * endpoint and by settle itself, so the number an operator approves is the
 * number that gets paid.
 */
function compute(eventId, order) {
  const bets = db.prepare(`SELECT * FROM bets WHERE event_id = ? AND status = 'open'`).all(eventId);

  // Exotic pools include every non-cancelled ticket of that type, winners and losers.
  const pools = {};
  for (const b of bets) {
    if (!BET_TYPES[b.bet_type].exotic) continue;
    pools[b.bet_type] ??= { gross: 0, winningStake: 0 };
    pools[b.bet_type].gross += b.stake;
  }
  for (const b of bets)
    if (BET_TYPES[b.bet_type].exotic && isWinner(b, order))
      pools[b.bet_type].winningStake += b.stake;

  const dividends = {};
  for (const [type, p] of Object.entries(pools)) {
    const net = p.gross * (1 - BET_TYPES[type].takeout);
    dividends[type] = {
      gross_pool: +p.gross.toFixed(2),
      takeout: BET_TYPES[type].takeout,
      net_pool: +net.toFixed(2),
      winning_stake: +p.winningStake.toFixed(2),
      dividend: p.winningStake ? +(net / p.winningStake).toFixed(4) : 0
    };
  }

  const results = bets.map(b => {
    const won = isWinner(b, order);
    if (!won) return { bet: b, status: 'lost', payout: 0, dividend: null };
    if (!BET_TYPES[b.bet_type].exotic)
      return { bet: b, status: 'won', payout: +(b.stake * b.struck_odds).toFixed(2), dividend: b.struck_odds };
    const d = dividends[b.bet_type].dividend;
    return { bet: b, status: 'won', payout: +(b.stake * d).toFixed(2), dividend: d };
  });

  const staked = bets.reduce((s, b) => s + b.stake, 0);
  const paid   = results.reduce((s, x) => s + x.payout, 0);

  return { results, dividends,
    total_staked: +staked.toFixed(2), total_paid: +paid.toFixed(2),
    net_position: +(staked - paid).toFixed(2),
    winning_bet_count: results.filter(x => x.status === 'won').length };
}

/* 1 ── POST /settlements/:id/result — declare the frame ─────────────────── */
r.post('/:id/result', h((req, res) => {
  const e = loadEvent(req.admin, req.params.id);
  if (!['closed', 'resulted'].includes(e.status))
    conflict(`A result can only be declared on a closed race (status: ${e.status}).`);

  const order = (req.body || {}).finishing_order;
  if (!Array.isArray(order) || order.length < 3)
    bad('finishing_order must list at least the first three past the post.');
  if (new Set(order).size !== order.length) bad('The same runner appears twice in the finishing order.');

  const runners = db.prepare(
    `SELECT id, is_scratched, horse_name FROM race_participants WHERE event_id = ? AND id IN (${order.map(() => '?').join(',')})`
  ).all(e.id, ...order);
  if (runners.length !== order.length) bad('A placed runner does not belong to this race.');
  const scr = runners.filter(p => p.is_scratched);
  if (scr.length) bad('A scratched runner cannot be placed.', { scratched: scr.map(p => p.horse_name) });

  db.exec('BEGIN');
  try {
    db.prepare('UPDATE race_participants SET finish_pos = NULL WHERE event_id = ?').run(e.id);
    order.forEach((pid, i) =>
      db.prepare('UPDATE race_participants SET finish_pos = ? WHERE id = ?').run(i + 1, pid));
    db.prepare(`UPDATE race_events SET status = 'resulted', meta_json = ?, updated_at = datetime('now') WHERE id = ?`)
      .run(JSON.stringify({ finishing_order: order }), e.id);
    db.exec('COMMIT');
  } catch (err) { db.exec('ROLLBACK'); throw err; }

  const preview = compute(e.id, order);
  res.json({ data: { event_id: e.id, status: 'resulted', finishing_order: order,
    preview: { total_staked: preview.total_staked, total_paid: preview.total_paid,
               net_position: preview.net_position, winning_bet_count: preview.winning_bet_count,
               dividends: preview.dividends } } });
}));

/* 2 ── GET /settlements/:id — preview before committing ─────────────────── */
r.get('/:id', h((req, res) => {
  const e = loadEvent(req.admin, req.params.id);
  const done = db.prepare('SELECT * FROM settlements WHERE event_id = ?').get(e.id);
  if (done) return res.json({ data: { event_id: e.id, finalised: true, ...done,
    finishing_order: JSON.parse(done.finishing_order_json || '[]'),
    dividends: JSON.parse(done.dividends_json) } });

  const order = JSON.parse(e.meta_json || '{}').finishing_order;
  if (!order) return res.json({ data: { event_id: e.id, finalised: false, message: 'No result declared yet.' } });

  const c = compute(e.id, order);
  res.json({ data: { event_id: e.id, finalised: false, finishing_order: order,
    total_staked: c.total_staked, total_paid: c.total_paid, net_position: c.net_position,
    winning_bet_count: c.winning_bet_count, dividends: c.dividends,
    bets: c.results.map(x => ({ bet_id: x.bet.id, punter_ref: x.bet.punter_ref,
      bet_type: x.bet.bet_type, stake: x.bet.stake, outcome: x.status,
      dividend: x.dividend, payout: x.payout })) } });
}));

/* 3 ── POST /settlements/:id/settle — move the money ────────────────────── */
r.post('/:id/settle', h((req, res) => {
  const e = loadEvent(req.admin, req.params.id);
  if (db.prepare('SELECT 1 FROM settlements WHERE event_id = ?').get(e.id))
    conflict('This race has already been settled.');
  if (e.status !== 'resulted') conflict(`Declare a result before settling (status: ${e.status}).`);

  const order = JSON.parse(e.meta_json || '{}').finishing_order;
  if (!order) conflict('No finishing order on this race.');

  const c = compute(e.id, order);

  // Payouts and the settlement record commit together or not at all. A partial
  // write here would leave punters paid with no audit row, or an audit row
  // claiming payouts that never landed.
  db.exec('BEGIN');
  try {
    const upd = db.prepare(
      `UPDATE bets SET status = ?, dividend = ?, payout = ?, settled_at = datetime('now') WHERE id = ?`);
    for (const x of c.results) upd.run(x.status, x.dividend, x.payout, x.bet.id);

    db.prepare(`INSERT INTO settlements
        (id, event_id, owner_admin_id, finishing_order_json, outcome, total_staked, total_paid,
         net_position, winning_bet_count, dividends_json, settled_by)
        VALUES (?,?,?,?, 'settled', ?,?,?,?,?,?)`)
      .run(newId('stl'), e.id, e.owner_admin_id, JSON.stringify(order),
           c.total_staked, c.total_paid, c.net_position, c.winning_bet_count,
           JSON.stringify(c.dividends), req.admin.id);

    db.prepare(`UPDATE race_events SET status = 'settled', updated_at = datetime('now') WHERE id = ?`).run(e.id);
    db.exec('COMMIT');
  } catch (err) { db.exec('ROLLBACK'); throw err; }

  res.json({ data: { event_id: e.id, status: 'settled', finishing_order: order,
    total_staked: c.total_staked, total_paid: c.total_paid, net_position: c.net_position,
    winning_bet_count: c.winning_bet_count, dividends: c.dividends } });
}));

/* 4 ── POST /settlements/:id/void — refund everything ───────────────────── */
r.post('/:id/void', h((req, res) => {
  const e = loadEvent(req.admin, req.params.id);
  if (['settled', 'void'].includes(e.status)) conflict(`This race is already ${e.status}.`);

  const reason = ((req.body || {}).reason || '').trim();
  if (!reason) bad('reason is required when voiding a race.');

  const open = db.prepare(`SELECT id, stake FROM bets WHERE event_id = ? AND status = 'open'`).all(e.id);
  const refunded = open.reduce((s, b) => s + b.stake, 0);

  db.exec('BEGIN');
  try {
    db.prepare(`UPDATE bets SET status = 'voided', payout = stake, dividend = NULL,
                settled_at = datetime('now') WHERE event_id = ? AND status = 'open'`).run(e.id);

    db.prepare(`INSERT INTO settlements
        (id, event_id, owner_admin_id, finishing_order_json, outcome, total_staked, total_paid,
         net_position, winning_bet_count, dividends_json, settled_by, reason)
        VALUES (?,?,?, NULL, 'void', ?,?, 0, 0, '{}', ?, ?)`)
      .run(newId('stl'), e.id, e.owner_admin_id, refunded, refunded, req.admin.id, reason);

    db.prepare(`UPDATE race_events SET status = 'void', void_reason = ?, updated_at = datetime('now') WHERE id = ?`)
      .run(reason, e.id);
    db.exec('COMMIT');
  } catch (err) { db.exec('ROLLBACK'); throw err; }

  res.json({ data: { event_id: e.id, status: 'void', reason,
    bets_refunded: open.length, total_refunded: +refunded.toFixed(2) } });
}));

/* 5 ── GET /settlements — the closed-out ledger ─────────────────────────── */
r.get('/', h((req, res) => {
  const s = scope(req.admin, 's');
  const where = [s.sql], params = [...s.params];
  if (req.query.outcome) { where.push('s.outcome = ?'); params.push(req.query.outcome); }

  const rows = db.prepare(`
    SELECT s.*, e.name AS event_name, e.venue, a.name AS settled_by_name
    FROM settlements s
    JOIN race_events e ON e.id = s.event_id
    JOIN admins a ON a.id = s.settled_by
    WHERE ${where.join(' AND ')}
    ORDER BY s.created_at DESC`).all(...params);

  res.json({
    data: rows.map(x => ({ id: x.id, event_id: x.event_id, event_name: x.event_name, venue: x.venue,
      outcome: x.outcome, finishing_order: JSON.parse(x.finishing_order_json || 'null'),
      total_staked: x.total_staked, total_paid: x.total_paid, net_position: x.net_position,
      winning_bet_count: x.winning_bet_count, dividends: JSON.parse(x.dividends_json),
      settled_by: x.settled_by_name, reason: x.reason, created_at: x.created_at })),
    meta: { net_across_races: +rows.reduce((s2, x) => s2 + x.net_position, 0).toFixed(2) }
  });
}));

module.exports = r;