Blame for server/src/db.js

import Database from 'better-sqlite3';
import bcrypt from 'bcryptjs';
import fs from 'node:fs';
import path from 'node:path';

const DATA_DIR = process.env.DATA_DIR || path.join(process.cwd(), 'data');
fs.mkdirSync(DATA_DIR, { recursive: true });

const db = new Database(path.join(DATA_DIR, 'dispatch.db'));
db.pragma('journal_mode = WAL');

db.exec(`
CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'dispatcher',
  created_at INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS talkgroups (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT UNIQUE NOT NULL,
  color TEXT NOT NULL DEFAULT '#3b82f6'
);
CREATE TABLE IF NOT EXISTS members (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  callsign TEXT,
  device_id TEXT UNIQUE,
  group_id INTEGER REFERENCES talkgroups(id),
  status TEXT NOT NULL DEFAULT 'offline',
  battery INTEGER,
  lat REAL,
  lng REAL,
  last_seen INTEGER
);
CREATE TABLE IF NOT EXISTS gps_pings (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  member_id INTEGER NOT NULL REFERENCES members(id),
  lat REAL NOT NULL,
  lng REAL NOT NULL,
  speed REAL,
  ts INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS sos_alerts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  member_id INTEGER NOT NULL REFERENCES members(id),
  lat REAL,
  lng REAL,
  state TEXT NOT NULL DEFAULT 'open',
  ack_by TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER
);
CREATE TABLE IF NOT EXISTS call_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  group_id INTEGER NOT NULL,
  member_id INTEGER NOT NULL,
  kind TEXT NOT NULL,
  ts INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_pings_member_ts ON gps_pings(member_id, ts);
CREATE INDEX IF NOT EXISTS idx_sos_state ON sos_alerts(state);
`);

if (!db.prepare('SELECT 1 FROM users WHERE username = ?').get('admin')) {
  db.prepare('INSERT INTO users (username, password_hash, role, created_at) VALUES (?, ?, ?, ?)')
    .run('admin', bcrypt.hashSync(process.env.ADMIN_PASSWORD || 'admin', 10), 'admin', Date.now());
  console.log('[db] default dispatcher created: admin / admin (change it)');
}

if (db.prepare('SELECT COUNT(*) AS n FROM talkgroups').get().n === 0) {
  const ins = db.prepare('INSERT INTO talkgroups (name, color) VALUES (?, ?)');
  ins.run('Alpha', '#3b82f6');
  ins.run('Bravo', '#f59e0b');
  ins.run('Echo', '#10b981');
  console.log('[db] seeded default talkgroups: Alpha, Bravo, Echo');
}

export default db;