BizGaze_Remote/server/repos.js

// Data-access layer (Phase 1).
// All SQL lives here, never in route/signaling handlers. This decouples the rest of
// the app from SQLite so the store can later move to Postgres without touching callers.
//
// TENANT ABSTRACTION: a "tenant" currently maps 1:1 to a team (column `team_id`).
// Repo signatures take `tenantId` so that when the tenant is later elevated to a
// first-class Organization (Phase 3), callers and the API/auth built on top stay unchanged.
const db = require('./db');
const A = require('./auth');
const now = () => Date.now();

const teams = {
  first: () => db.prepare('SELECT * FROM teams LIMIT 1').get(),
  byId: (id) => db.prepare('SELECT * FROM teams WHERE id=?').get(id),
  create: (name) => {
    const id = A.id();
    db.prepare('INSERT INTO teams (id,name,created_at) VALUES (?,?,?)').run(id, name, now());
    return db.prepare('SELECT * FROM teams WHERE id=?').get(id);
  },
};

const users = {
  anyExists: () => !!db.prepare('SELECT 1 FROM users LIMIT 1').get(),
  byId: (id) => db.prepare('SELECT * FROM users WHERE id=?').get(id),
  byEmail: (email) => db.prepare('SELECT * FROM users WHERE email=? COLLATE NOCASE').get(email),
  emailExists: (email) => !!db.prepare('SELECT 1 FROM users WHERE email=? COLLATE NOCASE').get(email),
  listByTenant: (tenantId) =>
    db.prepare('SELECT id,email,name,role,active,created_at FROM users WHERE team_id=?').all(tenantId),
  inTenant: (id, tenantId) =>
    db.prepare('SELECT * FROM users WHERE id=? AND team_id=?').get(id, tenantId),
  create: ({ tenantId, email, hash, salt, role, name, mfaSecret }) => {
    const id = A.id();
    db.prepare(`INSERT INTO users (id,team_id,email,pw_hash,pw_salt,role,name,mfa_secret,mfa_enabled,created_at)
                VALUES (?,?,?,?,?,?,?,?,0,?)`)
      .run(id, tenantId, email, hash, salt, role, name || null, mfaSecret, now());
    return id;
  },
  enableMfa: (id) => db.prepare('UPDATE users SET mfa_enabled=1 WHERE id=?').run(id),
  setName: (id, name) => db.prepare('UPDATE users SET name=? WHERE id=?').run(name, id),
  setRole: (id, role) => db.prepare('UPDATE users SET role=? WHERE id=?').run(role, id),
  setPassword: (id, hash, salt) => db.prepare('UPDATE users SET pw_hash=?, pw_salt=? WHERE id=?').run(hash, salt, id),
  setActive: (id, active) => db.prepare('UPDATE users SET active=? WHERE id=?').run(active ? 1 : 0, id),
  remove: (id) => db.prepare('DELETE FROM users WHERE id=?').run(id),
};

const authSessions = {
  byToken: (token) => db.prepare('SELECT * FROM sessions_auth WHERE token=?').get(token),
  create: ({ token, userId, mfaPassed, ttl }) =>
    db.prepare('INSERT INTO sessions_auth (token,user_id,mfa_passed,created_at,expires_at) VALUES (?,?,?,?,?)')
      .run(token, userId, mfaPassed ? 1 : 0, now(), now() + ttl),
  markMfaPassed: (token) => db.prepare('UPDATE sessions_auth SET mfa_passed=1 WHERE token=?').run(token),
  deleteByToken: (token) => db.prepare('DELETE FROM sessions_auth WHERE token=?').run(token),
  deleteByUser: (userId) => db.prepare('DELETE FROM sessions_auth WHERE user_id=?').run(userId),
};

const machines = {
  byEnrollToken: (t) => db.prepare('SELECT * FROM machines WHERE enroll_token=?').get(t),
  inTenant: (id, tenantId) => db.prepare('SELECT * FROM machines WHERE id=? AND team_id=?').get(id, tenantId),
  listByTenant: (tenantId) =>
    db.prepare('SELECT id,name,unattended,last_seen FROM machines WHERE team_id=?').all(tenantId),
  create: ({ tenantId, name, enrollToken, unattended }) => {
    const id = A.id();
    db.prepare('INSERT INTO machines (id,team_id,name,enroll_token,unattended,created_at) VALUES (?,?,?,?,?,?)')
      .run(id, tenantId, name, enrollToken, unattended ? 1 : 0, now());
    return id;
  },
  touch: (id) => db.prepare('UPDATE machines SET last_seen=? WHERE id=?').run(now(), id),
};

const audit = {
  add: (e) =>
    db.prepare(`INSERT INTO audit_log (team_id,user_id,user_email,machine_id,machine_name,action,detail,at)
                VALUES (@team_id,@user_id,@user_email,@machine_id,@machine_name,@action,@detail,@at)`)
      .run({
        team_id: e.team_id, user_id: e.user_id || null, user_email: e.user_email || null,
        machine_id: e.machine_id || null, machine_name: e.machine_name || null,
        action: e.action, detail: e.detail || null, at: now(),
      }),
  listByTenant: (tenantId) =>
    db.prepare("SELECT * FROM audit_log WHERE team_id=? OR team_id='adhoc' ORDER BY at DESC LIMIT 200").all(tenantId),
};

const sessionsLog = {
  byId: (id) => db.prepare('SELECT * FROM sessions_log WHERE id=?').get(id),
  byIdInTenant: (id, tenantId) => db.prepare('SELECT * FROM sessions_log WHERE id=? AND team_id=?').get(id, tenantId),
  create: ({ id, tenantId, agentEmail, agentName, ticket }) =>
    db.prepare('INSERT INTO sessions_log (id,team_id,agent_email,agent_name,ticket,started_at) VALUES (?,?,?,?,?,?)')
      .run(id, tenantId, agentEmail, agentName, ticket || null, now()),
  end: (id) => db.prepare('UPDATE sessions_log SET ended_at=? WHERE id=? AND ended_at IS NULL').run(now(), id),
  setRecording: (id, fname) => db.prepare('UPDATE sessions_log SET recording=? WHERE id=?').run(fname, id),
  setTranscript: (id, fname) => db.prepare('UPDATE sessions_log SET transcript=? WHERE id=?').run(fname, id),
  // Role-scoping is the caller's job: pass agentEmail to restrict to one agent (non-admins).
  report: ({ tenantId, agentEmail, from, to }) => {
    let sql = 'SELECT * FROM sessions_log WHERE team_id=?';
    const args = [tenantId];
    if (agentEmail) { sql += ' AND agent_email=?'; args.push(agentEmail); }
    if (from) { sql += ' AND started_at>=?'; args.push(from); }
    if (to) { sql += ' AND started_at<=?'; args.push(to); }
    sql += ' ORDER BY started_at DESC LIMIT 500';
    return db.prepare(sql).all(...args);
  },
};

module.exports = { teams, users, authSessions, machines, audit, sessionsLog };