101 lines
2.2 KiB
JavaScript
101 lines
2.2 KiB
JavaScript
'use strict';
|
|
|
|
const fs = require('fs');
|
|
const path = require('path');
|
|
const Database = require('better-sqlite3');
|
|
const conf = require('../conf/base');
|
|
|
|
// Ensure data directory exists
|
|
const dbDir = path.dirname(conf.dbPath);
|
|
if (!fs.existsSync(dbDir)) {
|
|
fs.mkdirSync(dbDir, { recursive: true });
|
|
}
|
|
|
|
const db = new Database(conf.dbPath);
|
|
db.pragma('journal_mode = WAL');
|
|
db.pragma('foreign_keys = ON');
|
|
|
|
// Initialize schema
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS panels (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
location TEXT,
|
|
model TEXT,
|
|
bus_rating TEXT,
|
|
notes TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS breakers (
|
|
id TEXT PRIMARY KEY,
|
|
panel_id TEXT NOT NULL,
|
|
slot INTEGER NOT NULL,
|
|
sub_slot TEXT,
|
|
label TEXT NOT NULL,
|
|
description TEXT,
|
|
amps INTEGER DEFAULT 20,
|
|
poles INTEGER DEFAULT 1,
|
|
voltage INTEGER DEFAULT 120,
|
|
color TEXT NOT NULL,
|
|
notes TEXT,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY(panel_id) REFERENCES panels(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS rooms (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
zone TEXT NOT NULL,
|
|
x REAL,
|
|
y REAL,
|
|
w REAL,
|
|
h REAL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS devices (
|
|
id TEXT PRIMARY KEY,
|
|
drawio_id TEXT NOT NULL,
|
|
type TEXT NOT NULL,
|
|
orig_x REAL NOT NULL,
|
|
orig_y REAL NOT NULL,
|
|
orig_w REAL NOT NULL,
|
|
orig_h REAL NOT NULL,
|
|
cx REAL NOT NULL,
|
|
cy REAL NOT NULL,
|
|
room TEXT,
|
|
zone TEXT,
|
|
breaker_id TEXT,
|
|
label TEXT,
|
|
notes TEXT,
|
|
color TEXT,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY(breaker_id) REFERENCES breakers(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS structures (
|
|
id TEXT PRIMARY KEY,
|
|
drawio_id TEXT NOT NULL,
|
|
type TEXT NOT NULL,
|
|
x REAL NOT NULL,
|
|
y REAL NOT NULL,
|
|
w REAL NOT NULL,
|
|
h REAL NOT NULL,
|
|
label TEXT,
|
|
style TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS edges (
|
|
id TEXT PRIMARY KEY,
|
|
drawio_id TEXT NOT NULL,
|
|
source TEXT,
|
|
target TEXT,
|
|
style TEXT
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_devices_breaker ON devices(breaker_id);
|
|
CREATE INDEX IF NOT EXISTS idx_devices_room ON devices(room);
|
|
CREATE INDEX IF NOT EXISTS idx_breakers_panel ON breakers(panel_id);
|
|
`);
|
|
|
|
module.exports = db;
|