3m-portal/lib/db.js

const Database = require('better-sqlite3');
const path     = require('path');
const fs       = require('fs');

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

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

// ── Schema migrations ─────────────────────────────────────────
db.exec(`
CREATE TABLE IF NOT EXISTS users (
    id                   INTEGER PRIMARY KEY AUTOINCREMENT,
    email                TEXT    NOT NULL UNIQUE COLLATE NOCASE,
    one2edit_username    TEXT    NOT NULL,
    password_hash        TEXT,
    role                 TEXT    NOT NULL DEFAULT 'user'
                             CHECK(role IN ('admin','user')),
    is_active            INTEGER NOT NULL DEFAULT 1,
    must_change_password INTEGER NOT NULL DEFAULT 0,
    created_at           INTEGER NOT NULL,
    created_by           INTEGER REFERENCES users(id),
    last_login_at        INTEGER
);

CREATE TABLE IF NOT EXISTS sessions (
    id                             TEXT    PRIMARY KEY,
    user_id                        INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    one2edit_extern_session_id     TEXT,
    one2edit_user_id               TEXT,
    expires_at                     INTEGER NOT NULL,
    created_at                     INTEGER NOT NULL,
    last_seen_at                   INTEGER NOT NULL,
    user_agent                     TEXT,
    ip                             TEXT
);
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_sessions_user    ON sessions(user_id);

CREATE TABLE IF NOT EXISTS tokens (
    token      TEXT    PRIMARY KEY,
    user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    purpose    TEXT    NOT NULL CHECK(purpose IN ('invite','reset')),
    expires_at INTEGER NOT NULL,
    used_at    INTEGER
);
CREATE INDEX IF NOT EXISTS idx_tokens_user ON tokens(user_id);

CREATE TABLE IF NOT EXISTS audit_log (
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    actor_user_id  INTEGER REFERENCES users(id),
    action         TEXT    NOT NULL,
    target_user_id INTEGER REFERENCES users(id),
    metadata       TEXT,
    ip             TEXT,
    created_at     INTEGER NOT NULL
);
`);

// ── Prepared statements ───────────────────────────────────────
const stmts = {
    getUserByEmail:  db.prepare('SELECT * FROM users WHERE email = ? COLLATE NOCASE'),
    getUserById:     db.prepare('SELECT * FROM users WHERE id = ?'),
    listUsers:       db.prepare('SELECT id, email, one2edit_username, role, is_active, must_change_password, created_at, last_login_at, (password_hash IS NOT NULL) AS has_password FROM users ORDER BY id'),
    adminCount:      db.prepare("SELECT COUNT(*) AS c FROM users WHERE role = 'admin' AND is_active = 1"),
    createUser:      db.prepare(`
        INSERT INTO users (email, one2edit_username, password_hash, role, is_active, must_change_password, created_at, created_by)
        VALUES (@email, @one2editUsername, @passwordHash, @role, 1, @mustChangePassword, @createdAt, @createdBy)
    `),
    updateUser:      db.prepare(`
        UPDATE users
        SET role              = COALESCE(@role, role),
            is_active         = COALESCE(@isActive, is_active),
            one2edit_username = COALESCE(@one2editUsername, one2edit_username)
        WHERE id = @id
    `),
    setPassword:     db.prepare('UPDATE users SET password_hash = @hash, must_change_password = @mustChange WHERE id = @id'),
    setLastLogin:    db.prepare('UPDATE users SET last_login_at = ? WHERE id = ?'),
};

// ── Public API ────────────────────────────────────────────────
function getUserByEmail(email)  { return stmts.getUserByEmail.get(email); }
function getUserById(id)        { return stmts.getUserById.get(id); }
function listUsers()            { return stmts.listUsers.all(); }
function adminCount()           { return stmts.adminCount.get().c; }

function createUser({ email, one2editUsername, passwordHash = null, role = 'user', mustChangePassword = 0, createdBy = null }) {
    const info = stmts.createUser.run({ email, one2editUsername, passwordHash, role, mustChangePassword, createdAt: Date.now(), createdBy });
    return stmts.getUserById.get(info.lastInsertRowid);
}

function updateUser(id, { role, isActive, one2editUsername } = {}) {
    stmts.updateUser.run({ id, role: role ?? null, isActive: isActive ?? null, one2editUsername: one2editUsername ?? null });
    return stmts.getUserById.get(id);
}

function setPassword(id, hash, mustChange = 0) {
    stmts.setPassword.run({ hash, mustChange, id });
}

function setLastLogin(id) {
    stmts.setLastLogin.run(Date.now(), id);
}

// Supports two formats:
//   INITIAL_ADMINS = JSON array: [{"email":"...","one2editUsername":"...","password":"..."},...]
//   Legacy single:  INITIAL_ADMIN_EMAIL + INITIAL_ADMIN_ONE2EDIT_USERNAME + INITIAL_ADMIN_PASSWORD
function bootstrapInitialAdmin() {
    let admins = [];

    if (process.env.INITIAL_ADMINS) {
        try { admins = JSON.parse(process.env.INITIAL_ADMINS); } catch { /* ignore bad JSON */ }
    } else if (process.env.INITIAL_ADMIN_EMAIL) {
        admins = [{
            email:            process.env.INITIAL_ADMIN_EMAIL,
            one2editUsername: process.env.INITIAL_ADMIN_ONE2EDIT_USERNAME || '',
            password:         process.env.INITIAL_ADMIN_PASSWORD || '',
        }];
    }

    const { hash } = require('./password');
    for (const a of admins) {
        if (!a.email || !a.one2editUsername || !a.password) continue;
        if (getUserByEmail(a.email)) continue;
        createUser({ email: a.email, one2editUsername: a.one2editUsername, passwordHash: hash(a.password), role: 'admin', mustChangePassword: 1 });
        console.log(`[bootstrap] created initial admin: ${a.email}`);
    }
}

module.exports = { db, getUserByEmail, getUserById, listUsers, createUser, updateUser, setPassword, setLastLogin, adminCount, bootstrapInitialAdmin };