556 lines
19 KiB
JavaScript
556 lines
19 KiB
JavaScript
const Database = require('better-sqlite3');
|
|
const path = require('path');
|
|
const bcrypt = require('bcryptjs');
|
|
|
|
const DB_PATH = process.env.DB_PATH || path.join(__dirname, 'data', 'consulting.db');
|
|
|
|
let db;
|
|
|
|
function getDb() {
|
|
if (!db) {
|
|
const fs = require('fs');
|
|
const dir = path.dirname(DB_PATH);
|
|
if (!fs.existsSync(dir)) fs.mkdirSync(dir, { recursive: true });
|
|
|
|
db = new Database(DB_PATH);
|
|
db.pragma('journal_mode = WAL');
|
|
db.pragma('foreign_keys = ON');
|
|
initSchema();
|
|
migrateSchema();
|
|
seedDefaults();
|
|
}
|
|
return db;
|
|
}
|
|
|
|
function migrateSchema() {
|
|
const existingColumns = db.prepare("PRAGMA table_info('users')").all().map(r => r.name);
|
|
const newCols = { email: "TEXT DEFAULT ''", role: "TEXT DEFAULT 'admin'", reset_token: "TEXT DEFAULT ''", reset_expires: "TEXT DEFAULT ''" };
|
|
for (const [col, def] of Object.entries(newCols)) {
|
|
if (!existingColumns.includes(col)) {
|
|
db.exec(`ALTER TABLE users ADD COLUMN ${col} ${def}`);
|
|
}
|
|
}
|
|
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS ai_providers (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
provider_type TEXT NOT NULL DEFAULT 'openrouter',
|
|
api_key TEXT DEFAULT '',
|
|
base_url TEXT DEFAULT '',
|
|
default_model TEXT DEFAULT '',
|
|
is_active INTEGER DEFAULT 1,
|
|
is_default INTEGER DEFAULT 0,
|
|
extra_headers TEXT DEFAULT '{}',
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS integrations (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT DEFAULT '',
|
|
api_key TEXT DEFAULT '',
|
|
webhook_url TEXT DEFAULT '',
|
|
config TEXT DEFAULT '{}',
|
|
status TEXT DEFAULT 'inactive',
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS reports (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
report_type TEXT DEFAULT 'client_summary',
|
|
client_id INTEGER REFERENCES clients(id) ON DELETE SET NULL,
|
|
project_id INTEGER,
|
|
config TEXT DEFAULT '{}',
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
client_id INTEGER REFERENCES clients(id) ON DELETE SET NULL,
|
|
start_date TEXT,
|
|
end_date TEXT,
|
|
status TEXT DEFAULT 'planning',
|
|
priority TEXT DEFAULT 'medium',
|
|
budget REAL,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
`);
|
|
|
|
const diagramCols = db.prepare("PRAGMA table_info('data_diagrams')").all().map(r => r.name);
|
|
if (!diagramCols.includes('engagement_id')) {
|
|
db.exec('ALTER TABLE data_diagrams ADD COLUMN engagement_id INTEGER REFERENCES engagements(id) ON DELETE SET NULL');
|
|
}
|
|
if (!diagramCols.includes('rack_id')) {
|
|
db.exec('ALTER TABLE data_diagrams ADD COLUMN rack_id INTEGER REFERENCES racks(id) ON DELETE SET NULL');
|
|
}
|
|
|
|
const tblRow = db.prepare("SELECT sql FROM sqlite_master WHERE type='table' AND name='data_diagrams'").get();
|
|
const tblSql = tblRow ? tblRow.sql : '';
|
|
if (tblSql && tblSql.includes('CHECK') && !tblSql.includes('application_flow')) {
|
|
const cols = db.prepare("PRAGMA table_info('data_diagrams')").all();
|
|
const colNames = cols.map(c => c.name);
|
|
const selectCols = colNames.join(', ');
|
|
db.exec(`
|
|
CREATE TABLE data_diagrams_v2 (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
client_id INTEGER NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
diagram_type TEXT DEFAULT 'data_architecture',
|
|
nodes TEXT NOT NULL DEFAULT '[]',
|
|
edges TEXT NOT NULL DEFAULT '[]',
|
|
engagement_id INTEGER REFERENCES engagements(id) ON DELETE SET NULL,
|
|
rack_id INTEGER REFERENCES racks(id) ON DELETE SET NULL,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
INSERT INTO data_diagrams_v2 (${selectCols}) SELECT ${selectCols} FROM data_diagrams;
|
|
DROP TABLE data_diagrams;
|
|
ALTER TABLE data_diagrams_v2 RENAME TO data_diagrams;
|
|
`);
|
|
}
|
|
}
|
|
|
|
function initSchema() {
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT UNIQUE NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
email TEXT DEFAULT '',
|
|
role TEXT DEFAULT 'admin' CHECK(role IN ('admin','consultant','viewer')),
|
|
reset_token TEXT DEFAULT '',
|
|
reset_expires TEXT DEFAULT '',
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS clients (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
industry TEXT,
|
|
website TEXT,
|
|
contact_name TEXT,
|
|
contact_email TEXT,
|
|
contact_phone TEXT,
|
|
status TEXT DEFAULT 'lead' CHECK(status IN ('lead','active','paused','archived')),
|
|
source TEXT,
|
|
notes TEXT,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS engagements (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
client_id INTEGER NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
type TEXT NOT NULL CHECK(type IN ('assessment','architecture','implementation','monitoring','consulting','training')),
|
|
status TEXT DEFAULT 'planned' CHECK(status IN ('planned','in_progress','completed','on_hold')),
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
start_date TEXT,
|
|
end_date TEXT,
|
|
outcome TEXT,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS assessments (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
client_id INTEGER NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
engagement_id INTEGER REFERENCES engagements(id) ON DELETE SET NULL,
|
|
answers TEXT NOT NULL,
|
|
report TEXT NOT NULL,
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS tasks (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
engagement_id INTEGER NOT NULL REFERENCES engagements(id) ON DELETE CASCADE,
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
status TEXT DEFAULT 'todo' CHECK(status IN ('todo','in_progress','done','cancelled')),
|
|
priority TEXT DEFAULT 'medium' CHECK(priority IN ('low','medium','high','critical')),
|
|
due_date TEXT,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS advice_log (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
client_id INTEGER NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
advice_type TEXT NOT NULL,
|
|
advice_text TEXT NOT NULL,
|
|
priority TEXT DEFAULT 'medium',
|
|
dismissed INTEGER DEFAULT 0,
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS racks (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
client_id INTEGER NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
location TEXT,
|
|
datacenter TEXT,
|
|
total_units INTEGER DEFAULT 42,
|
|
notes TEXT,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS rack_devices (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
rack_id INTEGER NOT NULL REFERENCES racks(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
device_type TEXT DEFAULT 'server',
|
|
model TEXT,
|
|
manufacturer TEXT,
|
|
position_u INTEGER NOT NULL,
|
|
height_u INTEGER DEFAULT 1,
|
|
specs TEXT,
|
|
mgmt_ip TEXT,
|
|
notes TEXT,
|
|
serial TEXT DEFAULT '',
|
|
asset_tag TEXT DEFAULT '',
|
|
status TEXT DEFAULT 'active',
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS network_devices (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
client_id INTEGER NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
device_type TEXT NOT NULL CHECK(device_type IN ('switch','router','firewall','loadbalancer','ap','other')),
|
|
model TEXT,
|
|
manufacturer TEXT,
|
|
ip_address TEXT,
|
|
mgmt_ip TEXT,
|
|
ports_count INTEGER DEFAULT 24,
|
|
os_version TEXT,
|
|
notes TEXT,
|
|
status TEXT DEFAULT 'active' CHECK(status IN ('active','standby','decommissioned')),
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS network_connections (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
client_id INTEGER NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
name TEXT,
|
|
from_type TEXT NOT NULL CHECK(from_type IN ('rack_device','network_device','external')),
|
|
from_id INTEGER,
|
|
from_port TEXT,
|
|
to_type TEXT NOT NULL CHECK(to_type IN ('rack_device','network_device','external')),
|
|
to_id INTEGER,
|
|
to_port TEXT,
|
|
media_type TEXT DEFAULT 'copper' CHECK(media_type IN ('copper','sfp','sfp+','qsfp','dac','fiber')),
|
|
speed TEXT DEFAULT '1GbE',
|
|
vlan TEXT,
|
|
status TEXT DEFAULT 'active' CHECK(status IN ('active','inactive','planned')),
|
|
notes TEXT,
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS data_diagrams (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
client_id INTEGER NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
diagram_type TEXT DEFAULT 'data_architecture' CHECK(diagram_type IN ('data_architecture','network_topology','system_architecture')),
|
|
nodes TEXT NOT NULL DEFAULT '[]',
|
|
edges TEXT NOT NULL DEFAULT '[]',
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS client_notes (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
client_id INTEGER NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
content TEXT NOT NULL,
|
|
created_by TEXT DEFAULT '',
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS app_settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL,
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS client_files (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
client_id INTEGER NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
original_name TEXT NOT NULL,
|
|
stored_name TEXT NOT NULL,
|
|
mime_type TEXT DEFAULT '',
|
|
file_size INTEGER DEFAULT 0,
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS audit_log (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER,
|
|
username TEXT DEFAULT '',
|
|
action TEXT NOT NULL,
|
|
entity_type TEXT NOT NULL,
|
|
entity_id INTEGER,
|
|
details TEXT DEFAULT '',
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS time_entries (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
client_id INTEGER REFERENCES clients(id) ON DELETE SET NULL,
|
|
engagement_id INTEGER REFERENCES engagements(id) ON DELETE SET NULL,
|
|
date TEXT NOT NULL,
|
|
hours REAL NOT NULL,
|
|
description TEXT DEFAULT '',
|
|
billable INTEGER DEFAULT 1,
|
|
hourly_rate REAL DEFAULT 150.0,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS invoices (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
client_id INTEGER NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
number TEXT UNIQUE NOT NULL,
|
|
status TEXT DEFAULT 'draft' CHECK(status IN ('draft','sent','paid','overdue','cancelled')),
|
|
date TEXT NOT NULL,
|
|
due_date TEXT NOT NULL,
|
|
subtotal REAL DEFAULT 0,
|
|
tax REAL DEFAULT 0,
|
|
total REAL DEFAULT 0,
|
|
notes TEXT DEFAULT '',
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS invoice_items (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
invoice_id INTEGER NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
|
|
description TEXT NOT NULL,
|
|
quantity REAL DEFAULT 1,
|
|
unit_price REAL DEFAULT 0,
|
|
total REAL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS notifications (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER,
|
|
type TEXT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
message TEXT DEFAULT '',
|
|
channel TEXT DEFAULT 'in-app' CHECK(channel IN ('in-app','email','slack','webhook')),
|
|
read INTEGER DEFAULT 0,
|
|
sent INTEGER DEFAULT 0,
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS calendar_events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER,
|
|
client_id INTEGER REFERENCES clients(id) ON DELETE SET NULL,
|
|
engagement_id INTEGER REFERENCES engagements(id) ON DELETE SET NULL,
|
|
title TEXT NOT NULL,
|
|
description TEXT DEFAULT '',
|
|
event_type TEXT DEFAULT 'meeting' CHECK(event_type IN ('meeting','call','deadline','workshop','review','other')),
|
|
start_time TEXT NOT NULL,
|
|
end_time TEXT NOT NULL,
|
|
all_day INTEGER DEFAULT 0,
|
|
color TEXT DEFAULT '#58a6ff',
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_settings (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL UNIQUE,
|
|
notify_email INTEGER DEFAULT 1,
|
|
notify_slack INTEGER DEFAULT 0,
|
|
notify_webhook INTEGER DEFAULT 0,
|
|
webhook_url TEXT DEFAULT '',
|
|
slack_webhook TEXT DEFAULT '',
|
|
language TEXT DEFAULT 'nl'
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS email_accounts (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
email TEXT NOT NULL,
|
|
name TEXT DEFAULT '',
|
|
imap_host TEXT NOT NULL,
|
|
imap_port INTEGER DEFAULT 993,
|
|
imap_secure INTEGER DEFAULT 1,
|
|
imap_user TEXT NOT NULL,
|
|
imap_pass TEXT NOT NULL,
|
|
smtp_host TEXT NOT NULL,
|
|
smtp_port INTEGER DEFAULT 587,
|
|
smtp_secure INTEGER DEFAULT 0,
|
|
smtp_user TEXT DEFAULT '',
|
|
smtp_pass TEXT DEFAULT '',
|
|
active INTEGER DEFAULT 1,
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS email_messages (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
account_id INTEGER NOT NULL REFERENCES email_accounts(id) ON DELETE CASCADE,
|
|
uid INTEGER NOT NULL,
|
|
folder TEXT DEFAULT 'INBOX',
|
|
subject TEXT DEFAULT '',
|
|
from_name TEXT DEFAULT '',
|
|
from_addr TEXT DEFAULT '',
|
|
to_addr TEXT DEFAULT '',
|
|
cc TEXT DEFAULT '',
|
|
bcc TEXT DEFAULT '',
|
|
date TEXT DEFAULT '',
|
|
body_text TEXT DEFAULT '',
|
|
body_html TEXT DEFAULT '',
|
|
attachments INTEGER DEFAULT 0,
|
|
seen INTEGER DEFAULT 0,
|
|
flagged INTEGER DEFAULT 0,
|
|
replied INTEGER DEFAULT 0,
|
|
size INTEGER DEFAULT 0,
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS datacenters (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
address TEXT DEFAULT '',
|
|
city TEXT DEFAULT '',
|
|
region TEXT DEFAULT '',
|
|
postal_code TEXT DEFAULT '',
|
|
phone TEXT DEFAULT '',
|
|
website TEXT DEFAULT '',
|
|
type TEXT DEFAULT 'colo',
|
|
operator TEXT DEFAULT '',
|
|
tier TEXT DEFAULT 'III',
|
|
power_mw REAL DEFAULT 0,
|
|
size_sqm INTEGER DEFAULT 0,
|
|
network_vendors TEXT DEFAULT '',
|
|
server_vendors TEXT DEFAULT '',
|
|
storage_vendors TEXT DEFAULT '',
|
|
certifications TEXT DEFAULT '',
|
|
notes TEXT DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS tech_companies (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
industry TEXT DEFAULT '',
|
|
city TEXT DEFAULT '',
|
|
address TEXT DEFAULT '',
|
|
postal_code TEXT DEFAULT '',
|
|
phone TEXT DEFAULT '',
|
|
email TEXT DEFAULT '',
|
|
website TEXT DEFAULT '',
|
|
linkedin TEXT DEFAULT '',
|
|
employees INTEGER DEFAULT 0,
|
|
revenue_range TEXT DEFAULT '',
|
|
data_warehouse TEXT DEFAULT '',
|
|
bi_tools TEXT DEFAULT '',
|
|
cloud_provider TEXT DEFAULT '',
|
|
data_lake TEXT DEFAULT '',
|
|
ai_ml TEXT DEFAULT '',
|
|
database_vendor TEXT DEFAULT '',
|
|
lead_score INTEGER DEFAULT 0,
|
|
lead_status TEXT DEFAULT 'new',
|
|
notes TEXT DEFAULT '',
|
|
source TEXT DEFAULT ''
|
|
);
|
|
`);
|
|
}
|
|
|
|
function seedDefaults() {
|
|
const existing = db.prepare('SELECT COUNT(*) as cnt FROM users').get();
|
|
if (existing.cnt === 0) {
|
|
const pw = process.env.ADMIN_PASSWORD || 'admin';
|
|
const hash = bcrypt.hashSync(pw, 10);
|
|
const result = db.prepare('INSERT INTO users (username, password_hash, role) VALUES (?, ?, ?)').run('admin', hash, 'admin');
|
|
db.prepare('INSERT INTO user_settings (user_id, language) VALUES (?, ?)').run(result.lastInsertRowid, 'nl');
|
|
console.log(`Default user created: admin / ${pw}`);
|
|
}
|
|
}
|
|
|
|
const DEFAULTS = {
|
|
brand_name: 'Mek-Tech Consulting',
|
|
language: 'nl',
|
|
theme: 'mek-neon-blue',
|
|
default_page: '/',
|
|
auto_refresh: '0',
|
|
items_per_page: '50',
|
|
debug_mode: '0',
|
|
icon_colors: JSON.stringify({
|
|
'layout-dashboard': '#58a6ff',
|
|
users: '#3fb950',
|
|
briefcase: '#d29922',
|
|
server: '#bc8cff',
|
|
'check-circle': '#3fb950',
|
|
lightbulb: '#d29922',
|
|
clock: '#79c0ff',
|
|
eye: '#f778ba',
|
|
phone: '#3fb950',
|
|
'file-text': '#f778ba',
|
|
'edit-3': '#79c0ff',
|
|
folder: '#d29922',
|
|
'clipboard-list': '#bc8cff'
|
|
}),
|
|
theme_colors: '{}',
|
|
showcase_url: 'http://192.168.1.247:3010'
|
|
};
|
|
|
|
function getSettings() {
|
|
const d = getDb();
|
|
const rows = d.prepare('SELECT key, value FROM app_settings').all();
|
|
const s = { ...DEFAULTS };
|
|
// Ensure defaults exist in DB
|
|
const upsert = d.prepare(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, datetime('now'))
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`);
|
|
for (const [k, v] of Object.entries(DEFAULTS)) {
|
|
const row = rows.find(r => r.key === k);
|
|
if (row) {
|
|
s[k] = row.value;
|
|
} else {
|
|
upsert.run(k, v);
|
|
}
|
|
}
|
|
// Also store icon_colors as parsed object
|
|
if (typeof s.icon_colors === 'string') {
|
|
try { s.icon_colors = JSON.parse(s.icon_colors); } catch (e) { s.icon_colors = JSON.parse(DEFAULTS.icon_colors); }
|
|
}
|
|
if (typeof s.theme_colors === 'string') {
|
|
try { s.theme_colors = JSON.parse(s.theme_colors || '{}'); } catch (e) { s.theme_colors = {}; }
|
|
}
|
|
if (!s.theme_colors || typeof s.theme_colors !== 'object') s.theme_colors = {};
|
|
return s;
|
|
}
|
|
|
|
function updateSetting(key, value) {
|
|
const d = getDb();
|
|
if (typeof value === 'object') value = JSON.stringify(value);
|
|
d.prepare(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, datetime('now'))
|
|
ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`).run(key, value);
|
|
}
|
|
|
|
function logAudit(userId, username, action, entityType, entityId, details) {
|
|
const d = getDb();
|
|
d.prepare('INSERT INTO audit_log (user_id, username, action, entity_type, entity_id, details) VALUES (?, ?, ?, ?, ?, ?)')
|
|
.run(userId || null, username || '', action, entityType, entityId || null, details || '');
|
|
}
|
|
|
|
function getUserSettings(userId) {
|
|
const d = getDb();
|
|
let s = d.prepare('SELECT * FROM user_settings WHERE user_id = ?').get(userId);
|
|
if (!s) {
|
|
d.prepare('INSERT INTO user_settings (user_id) VALUES (?)').run(userId);
|
|
s = d.prepare('SELECT * FROM user_settings WHERE user_id = ?').get(userId);
|
|
}
|
|
return s;
|
|
}
|
|
|
|
module.exports = { getDb, getSettings, updateSetting, logAudit, getUserSettings, DEFAULTS };
|