Blame for server/src/routes.js

import { Router } from 'express';
import db from './db.js';

const router = Router();

const MEMBER_SQL = `SELECT m.*, g.name AS group_name, g.color AS group_color
  FROM members m LEFT JOIN talkgroups g ON g.id = m.group_id`;
const SOS_SQL = `SELECT a.*, m.name AS member_name FROM sos_alerts a
  JOIN members m ON m.id = a.member_id`;

const hub = (req) => req.app.get('hub');

router.get('/groups', (req, res) => {
  res.json(db.prepare('SELECT * FROM talkgroups ORDER BY id').all());
});

router.post('/groups', (req, res) => {
  const { name, color = '#3b82f6' } = req.body || {};
  if (!name) return res.status(400).json({ error: 'name required' });
  try {
    const info = db.prepare('INSERT INTO talkgroups (name, color) VALUES (?, ?)').run(name, color);
    hub(req)?.broadcastGroups();
    res.json(db.prepare('SELECT * FROM talkgroups WHERE id = ?').get(info.lastInsertRowid));
  } catch {
    res.status(409).json({ error: 'group name already exists' });
  }
});

router.get('/members', (req, res) => {
  res.json(db.prepare(`${MEMBER_SQL} ORDER BY m.id`).all());
});

router.post('/members', (req, res) => {
  const { name, callsign, device_id, group_id } = req.body || {};
  if (!name) return res.status(400).json({ error: 'name required' });
  if (!device_id) return res.status(400).json({ error: 'device_id required' });
  try {
    const info = db.prepare('INSERT INTO members (name, callsign, device_id, group_id) VALUES (?, ?, ?, ?)')
      .run(name, callsign || null, device_id, group_id ?? null);
    hub(req)?.broadcastMembers();
    res.json(db.prepare(`${MEMBER_SQL} WHERE m.id = ?`).get(info.lastInsertRowid));
  } catch {
    res.status(409).json({ error: 'device_id already exists' });
  }
});

router.put('/members/:id', (req, res) => {
  const m = db.prepare('SELECT * FROM members WHERE id = ?').get(req.params.id);
  if (!m) return res.status(404).json({ error: 'member not found' });
  const { name = m.name, callsign = m.callsign, group_id = m.group_id } = req.body || {};
  db.prepare('UPDATE members SET name = ?, callsign = ?, group_id = ? WHERE id = ?')
    .run(name, callsign, group_id, m.id);
  hub(req)?.broadcastMembers();
  res.json(db.prepare(`${MEMBER_SQL} WHERE m.id = ?`).get(m.id));
});

router.delete('/members/:id', (req, res) => {
  const m = db.prepare('SELECT id FROM members WHERE id = ?').get(req.params.id);
  if (!m) return res.status(404).json({ error: 'member not found' });
  // remove history first: FK references would otherwise make this fail
  db.prepare('DELETE FROM gps_pings WHERE member_id = ?').run(m.id);
  db.prepare('DELETE FROM sos_alerts WHERE member_id = ?').run(m.id);
  db.prepare('DELETE FROM call_log WHERE member_id = ?').run(m.id);
  db.prepare('DELETE FROM members WHERE id = ?').run(m.id);
  hub(req)?.broadcastMembers();
  res.json({ ok: true });
});

router.get('/sos', (req, res) => {
  const rows = req.query.state
    ? db.prepare(`${SOS_SQL} WHERE a.state = ? ORDER BY a.created_at DESC LIMIT 200`).all(req.query.state)
    : db.prepare(`${SOS_SQL} ORDER BY a.created_at DESC LIMIT 200`).all();
  res.json(rows);
});

router.post('/sos/:id/ack', (req, res) => {
  const info = db.prepare(`UPDATE sos_alerts SET state = 'ack', ack_by = ?, updated_at = ? WHERE id = ? AND state = 'open'`)
    .run(req.user.username, Date.now(), req.params.id);
  if (!info.changes) return res.status(409).json({ error: 'alert not open' });
  const row = db.prepare(`${SOS_SQL} WHERE a.id = ?`).get(req.params.id);
  hub(req)?.broadcast({ type: 'sos_update', alert: row });
  res.json(row);
});

router.post('/sos/:id/resolve', (req, res) => {
  const info = db.prepare(`UPDATE sos_alerts SET state = 'resolved', ack_by = COALESCE(ack_by, ?), updated_at = ? WHERE id = ? AND state != 'resolved'`)
    .run(req.user.username, Date.now(), req.params.id);
  if (!info.changes) return res.status(409).json({ error: 'alert already resolved' });
  const row = db.prepare(`${SOS_SQL} WHERE a.id = ?`).get(req.params.id);
  hub(req)?.broadcast({ type: 'sos_update', alert: row });
  res.json(row);
});

router.get('/members/:id/trail', (req, res) => {
  const since = Number(req.query.since) || 0;
  res.json(db.prepare('SELECT lat, lng, speed, ts FROM gps_pings WHERE member_id = ? AND ts > ? ORDER BY ts LIMIT 5000')
    .all(req.params.id, since));
});

router.get('/calls', (req, res) => {
  res.json(db.prepare(`SELECT c.*, m.name AS member_name FROM call_log c
    LEFT JOIN members m ON m.id = c.member_id ORDER BY c.ts DESC LIMIT 100`).all());
});

export default router;