BizGaze_Remote/server/scripts/migrate-bizgaze-only.js

#!/usr/bin/env node
// One-time PRODUCTION migration for "BizGaze-only logins".
//
// Deletes the in-app (pre-BizGaze) local accounts. Combined with the BizGaze-only login
// change, every user then signs in through BizGaze and is provisioned into the same
// tenant — which restores the admin's "see all sessions" report.
//
// A "pre-BizGaze" account = a user with NO 'sso_user_created' audit entry for its email
// (i.e. created locally via register/console, not provisioned by a BizGaze login).
//
// SAFE BY DEFAULT: dry-run unless you pass --apply. BACK UP THE DB FIRST.
//   Dry run : node scripts/migrate-bizgaze-only.js
//   Apply   : node scripts/migrate-bizgaze-only.js --apply
// Honors DB_PATH (same env var the server uses).

const db = require('../db');
const APPLY = process.argv.includes('--apply');

const tableExists = (name) => !!db.prepare("SELECT 1 FROM sqlite_master WHERE type='table' AND name=?").get(name);

const ssoEmails = new Set(
  db.prepare("SELECT DISTINCT lower(user_email) AS e FROM audit_log WHERE action='sso_user_created' AND user_email IS NOT NULL")
    .all().map((r) => r.e),
);
const users = db.prepare('SELECT id,email,name,role,team_id,active FROM users').all();
const keep = users.filter((u) => ssoEmails.has(String(u.email).toLowerCase()));
const remove = users.filter((u) => !ssoEmails.has(String(u.email).toLowerCase()));

console.log('=== Teams ===');
for (const t of db.prepare('SELECT id,name FROM teams').all()) {
  const uc = db.prepare('SELECT COUNT(*) AS c FROM users WHERE team_id=?').get(t.id).c;
  console.log(`  ${t.id}  ${t.name}  (${uc} users)`);
}
console.log('\n=== Users ===');
console.log(`  total: ${users.length}  |  BizGaze-provisioned (keep): ${keep.length}  |  local pre-BizGaze (delete): ${remove.length}`);
console.log('\n  KEEP (already BizGaze-provisioned):');
keep.forEach((u) => console.log(`    ${u.email}  [${u.role}]  team ${u.team_id}`));
console.log('\n  DELETE (local / pre-BizGaze):');
remove.forEach((u) => console.log(`    ${u.email}  [${u.role}]  team ${u.team_id}`));

if (!remove.length) { console.log('\nNothing to delete. Done.'); process.exit(0); }

if (!APPLY) {
  console.log('\nDRY RUN — no changes made. Re-run with --apply to delete the local accounts above.');
  console.log('After deletion, those users sign in via BizGaze and are recreated automatically.');
  process.exit(0);
}

const delAuth = db.prepare('DELETE FROM sessions_auth WHERE user_id=?');
const delRefresh = tableExists('refresh_tokens') ? db.prepare('DELETE FROM refresh_tokens WHERE user_id=?') : null;
const delUser = db.prepare('DELETE FROM users WHERE id=?');
let deleted = 0;
db.exec('BEGIN');
try {
  for (const u of remove) {
    delAuth.run(u.id);          // clear active sessions (FK) — also logs them out
    if (delRefresh) delRefresh.run(u.id);
    delUser.run(u.id);
    deleted++;
  }
  db.exec('COMMIT');
} catch (e) {
  db.exec('ROLLBACK');
  console.error('FAILED — rolled back, no changes applied:', e.message);
  process.exit(1);
}
console.log(`\nDONE. Deleted ${deleted} local account(s). They are recreated via BizGaze on next sign-in.`);