#!/usr/bin/env node
/**
 * scripts/purge-test-data.js
 *
 * One-shot: DELETE every row flagged as test data, plus the rows
 * that reference them. Idempotent — safe to re-run.
 *
 * What gets removed (when is_test_data=1):
 *   - users (cascades to cases, takedowns, takedown_jobs,
 *     sessions, api_tokens, user_2fa, user_known_ips, alerts,
 *     audit_log, findings, link_health_checks, monitored_urls,
 *     takedown_notes, trusted_devices — all the FKs)
 *   - cases (cascades to takedowns, findings, etc.)
 *   - takedowns (cascades to link_health_checks, takedown_jobs,
 *     takedown_notes, trusted_devices if user_id references a
 *     test user)
 *   - alerts that reference a test takedown/case (the test-data
 *     spam from 127.0.0.1 takedowns, api_error noise, etc.)
 *   - audit_log rows for test users
 *   - findings attached to test cases
 *   - api_tokens, sessions, login_attempts for test users
 *   - case_discovery_scans, email_digest_subscriptions, etc.
 *
 * Why we don't just `DELETE FROM users WHERE is_test_data=1`:
 *   - Some FKs are NOT cascade (alerts.dedupe_key is a string, not
 *     a rowid; audit_log.actor_id is a soft pointer; some ref_url
 *     values point to "takedown:N"). We clean those up explicitly.
 *
 * After the deletes, VACUUMs the database to reclaim disk space.
 *
 * Usage:
 *   node scripts/purge-test-data.js --dry-run
 *   node scripts/purge-test-data.js            # actually delete
 *   node scripts/purge-test-data.js --no-vacuum
 */

'use strict';

const path = require('path');
process.env.DB_PATH = process.env.DB_PATH || path.join(__dirname, '..', 'data', 'ncii.sqlite');

const { db } = require('../config/db');

const ARGS = new Set(process.argv.slice(2));
const DRY = ARGS.has('--dry-run');
const NO_VACUUM = ARGS.has('--no-vacuum');

function n(sql, ...p) { return db.prepare(sql).get(...p).n; }
function run(sql, ...p) { return db.prepare(sql).run(...p).changes; }

const before = {
  users: n('SELECT COUNT(*) AS n FROM users'),
  cases: n('SELECT COUNT(*) AS n FROM cases'),
  takedowns: n('SELECT COUNT(*) AS n FROM takedowns'),
  findings: n('SELECT COUNT(*) AS n FROM findings'),
  alerts: n('SELECT COUNT(*) AS n FROM alerts'),
  audit: n('SELECT COUNT(*) AS n FROM audit_log'),
  health: n('SELECT COUNT(*) AS n FROM link_health_checks'),
  jobs: n('SELECT COUNT(*) AS n FROM takedown_jobs'),
  scans: n('SELECT COUNT(*) AS n FROM case_discovery_scans'),
  notes: n('SELECT COUNT(*) AS n FROM takedown_notes'),
  trusted: n('SELECT COUNT(*) AS n FROM trusted_devices'),
  tokens: n('SELECT COUNT(*) AS n FROM api_tokens'),
  attempts: n('SELECT COUNT(*) AS n FROM login_attempts'),
  ips: n('SELECT COUNT(*) AS n FROM user_known_ips'),
  digests: n('SELECT COUNT(*) AS n FROM email_digest_subscriptions'),
  monitors: n('SELECT COUNT(*) AS n FROM monitored_urls'),
};
// Note: sessions live in data/sessions.sqlite (separate file managed
// by connect-sqlite3). That file is also excluded from the deploy
// zip, so it doesn't ship with test data anyway. Skip it here.
console.log('\n=== Before ===');
for (const [k, v] of Object.entries(before)) console.log(`  ${k.padEnd(10)} ${v}`);

if (DRY) { console.log('\n(dry-run — no changes)'); process.exit(0); }

// Run inside a transaction. If anything throws, the whole thing rolls back.
const tx = db.transaction(() => {
  // 1. Collect the IDs we need to clean up.
  const testUserIds     = db.prepare('SELECT id FROM users WHERE is_test_data=1').all().map(r => r.id);
  const testCaseIds     = db.prepare('SELECT id FROM cases WHERE is_test_data=1').all().map(r => r.id);
  const testTakedownIds = db.prepare('SELECT id FROM takedowns WHERE is_test_data=1').all().map(r => r.id);
  console.log(`\nIdentified: ${testUserIds.length} test users, ${testCaseIds.length} test cases, ${testTakedownIds.length} test takedowns`);

  // 2. Alerts pointing at test takedowns/cases/users (string ref_url).
  //    ref_url format: "takedown:N", "case:N", "user:N".
  let alertsDeleted = 0;
  if (testTakedownIds.length) {
    const placeholders = testTakedownIds.map(() => '?').join(',');
    alertsDeleted += run(`DELETE FROM alerts WHERE ref_url IN (${testTakedownIds.map(id => 'takedown:' + id).map(s => '?').join(',')})`, ...testTakedownIds.map(id => 'takedown:' + id));
  }
  if (testCaseIds.length) {
    alertsDeleted += run(`DELETE FROM alerts WHERE ref_url IN (${testCaseIds.map(id => 'case:' + id).map(s => '?').join(',')})`, ...testCaseIds.map(id => 'case:' + id));
  }
  if (testUserIds.length) {
    alertsDeleted += run(`DELETE FROM alerts WHERE actor_id IS NOT NULL AND actor_id IN (${testUserIds.map(() => '?').join(',')})`, ...testUserIds);
  }
  console.log(`  alerts (test ref):    -${alertsDeleted}`);

  // 3. The 4 linkHealth records for the 127.0.0.1 test takedowns. The
  //    CASCADE on link_health_checks.takedown_id handles these when
  //    we delete the takedowns below — but we also have orphans in
  //    case the test takedown was already gone. Clean them first.
  //    (No-op if cascades already did it.)
  const orphansHealth = testTakedownIds.length
    ? run(`DELETE FROM link_health_checks WHERE takedown_id IN (${testTakedownIds.map(() => '?').join(',')})`, ...testTakedownIds)
    : 0;
  console.log(`  link_health_checks:  -${orphansHealth}`);

  // 4. Takedown_jobs, case_discovery_scans, takedown_notes,
  //    monitored_urls — all FK to test users/cases/takedowns. CASCADE
  //    handles most. Manual for the ones without CASCADE.
  //    (Skipped — the takedown/cases/user deletes below cascade
  //    automatically for the tables that have ON DELETE CASCADE.)

  // 5. Now delete the test takedowns themselves. CASCADE removes
  //    link_health_checks, takedown_jobs, takedown_notes.
  const tds = testTakedownIds.length
    ? run(`DELETE FROM takedowns WHERE id IN (${testTakedownIds.map(() => '?').join(',')})`, ...testTakedownIds)
    : 0;
  console.log(`  takedowns (forced):   -${tds}`);

  // 6. Findings attached to test cases. CASCADE handles.
  // (No-op via the cases delete below.)

  // 7. Delete test cases. CASCADE removes findings, takedowns (the
  //    few leftovers), case_discovery_scans, email_digest_subscriptions.
  const cs = testCaseIds.length
    ? run(`DELETE FROM cases WHERE id IN (${testCaseIds.map(() => '?').join(',')})`, ...testCaseIds)
    : 0;
  console.log(`  cases (forced):       -${cs}`);

  // 8. Delete test users. CASCADE removes takedowns, sessions, api_tokens,
  //    user_2fa, user_known_ips, login_attempts, trusted_devices,
  //    takedown_notes, audit_log? (no — actor_id is soft; we handled
  //    that above by deleting alerts with actor_id in testUserIds).
  //    But the audit_log rows for these users are still there. Clean them.
  let auditDeleted = 0;
  if (testUserIds.length) {
    auditDeleted += run(`DELETE FROM audit_log WHERE actor_id IN (${testUserIds.map(() => '?').join(',')})`, ...testUserIds);
  }
  console.log(`  audit_log (by actor): -${auditDeleted}`);

  const us = testUserIds.length
    ? run(`DELETE FROM users WHERE id IN (${testUserIds.map(() => '?').join(',')})`, ...testUserIds)
    : 0;
  console.log(`  users (forced):       -${us}`);

  // 9. Belt-and-suspenders: any audit_log rows whose actor_id was a
  //    test user but somehow survived (e.g. NULL actor_id was set
  //    before the user was deleted).
  if (testUserIds.length) {
    const moreAudit = run(`DELETE FROM audit_log WHERE actor_id IN (${testUserIds.map(() => '?').join(',')})`, ...testUserIds);
    if (moreAudit) console.log(`  audit_log (cleanup):  -${moreAudit}`);
  }

  return { alertsDeleted, orphansHealth, tds, cs, auditDeleted, us };
});

const result = tx();
console.log('\n=== Deleted ===');
console.log(`  alerts:        -${result.alertsDeleted}`);
console.log(`  takedowns:     -${result.tds}`);
console.log(`  cases:         -${result.cs}`);
console.log(`  audit_log:     -${result.auditDeleted}`);
console.log(`  users:         -${result.us}`);

if (!NO_VACUUM) {
  console.log('\nVACUUMing the database to reclaim space...');
  db.exec('VACUUM');
  console.log('Done.');
}

const after = {
  users: n('SELECT COUNT(*) AS n FROM users'),
  cases: n('SELECT COUNT(*) AS n FROM cases'),
  takedowns: n('SELECT COUNT(*) AS n FROM takedowns'),
  findings: n('SELECT COUNT(*) AS n FROM findings'),
  alerts: n('SELECT COUNT(*) AS n FROM alerts'),
  audit: n('SELECT COUNT(*) AS n FROM audit_log'),
  health: n('SELECT COUNT(*) AS n FROM link_health_checks'),
  jobs: n('SELECT COUNT(*) AS n FROM takedown_jobs'),
  scans: n('SELECT COUNT(*) AS n FROM case_discovery_scans'),
  notes: n('SELECT COUNT(*) AS n FROM takedown_notes'),
  trusted: n('SELECT COUNT(*) AS n FROM trusted_devices'),
  tokens: n('SELECT COUNT(*) AS n FROM api_tokens'),
  attempts: n('SELECT COUNT(*) AS n FROM login_attempts'),
  ips: n('SELECT COUNT(*) AS n FROM user_known_ips'),
  digests: n('SELECT COUNT(*) AS n FROM email_digest_subscriptions'),
  monitors: n('SELECT COUNT(*) AS n FROM monitored_urls'),
};
console.log('\n=== After ===');
for (const [k, v] of Object.entries(after)) console.log(`  ${k.padEnd(10)} ${v}`);

console.log('\n=== Diffs ===');
for (const [k, v] of before) {
  const d = after[k] !== undefined ? after[k] - v : 0;
  if (d !== 0) console.log(`  ${k.padEnd(10)} ${d > 0 ? '+' : ''}${d}`);
}