Spaces:
Paused
Paused
| 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; |