#!/usr/bin/env node
/**
 * scripts/flag-test-data.js
 *
 * One-shot migration: flag users / cases / takedowns as test data so
 * background workers + the admin dashboard can ignore them.
 *
 * Heuristics (any one triggers flagging):
 *   users:     email matches /@test\.local$/i   AND   role != 'superadmin'
 *              (the seeded superadmin is admin@yourdomain.com, NOT @test.local)
 *   cases:     victim_name matches /^(test|sample|demo|fake|sandbox)/i
 *   takedowns: url matches /(127\.0\.0\.1|localhost|0\.0\.0\.0|192\.168\.)/i
 *              OR domain matches /\.(test|example|invalid|localhost)$/i
 *              OR contact_email matches /@(test|example|invalid)\./
 *
 * Idempotent — re-running won't un-flag a row that was already flagged
 * by hand. To un-flag, do it via the admin UI (future) or:
 *   UPDATE users SET is_test_data=0 WHERE id=?;
 *
 * Usage:
 *   node scripts/flag-test-data.js            # report + flag
 *   node scripts/flag-test-data.js --dry-run  # report only
 *   node scripts/flag-test-data.js --reset    # clear all flags first
 */

'use strict';

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

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

const ARGS = new Set(process.argv.slice(2));
const DRY = ARGS.has('--dry-run');
const RESET = ARGS.has('--reset');

function countRows(sql, ...params) {
  return db.prepare(sql).get(...params).n;
}

if (RESET) {
  const n = countRows(`
    SELECT (SELECT COUNT(*) FROM users WHERE is_test_data=1)
         + (SELECT COUNT(*) FROM cases WHERE is_test_data=1)
         + (SELECT COUNT(*) FROM takedowns WHERE is_test_data=1) AS n
  `);
  console.log(`[reset] clearing ${n} existing flags`);
  if (!DRY) {
    db.exec(`UPDATE users SET is_test_data=0`);
    db.exec(`UPDATE cases SET is_test_data=0`);
    db.exec(`UPDATE takedowns SET is_test_data=0`);
  }
}

// ---- Heuristic patterns ----
const USER_PATTERNS = [
  // @test.local — the obvious one (test suite seeds users here)
  ["email LIKE '%@test.local'", /@test\.local$/i],
  // @ncii.test — the test suite's other common pattern
  ["email LIKE '%@ncii.test'",  /@ncii\.test$/i],
  // Disposable / fake email providers that shouldn't count as real users
  ["email LIKE '%@mailinator.%'",   /@mailinator\./i],
  ["email LIKE '%@10minutemail.%'", /@10minutemail\./i],
  ["email LIKE '%@guerrillamail.%'", /@guerrillamail\./i],
  ["email LIKE '%@tempmail.%'",     /@tempmail\./i],
  ["email LIKE '%@throwaway.%'",    /@throwaway\./i],
  // Any other TLD that looks like test/example: .test, .example, .invalid, .localhost
  ["email LIKE '%.test' OR email LIKE '%.example' OR email LIKE '%.invalid' OR email LIKE '%.localhost'",
    /\.(test|example|invalid|localhost)$/i],
];
const CASE_PATTERNS = [
  ["LOWER(victim_name) LIKE 'test %' OR LOWER(victim_name) LIKE 'sample %' OR LOWER(victim_name) LIKE 'demo %' OR LOWER(victim_name) LIKE 'fake %' OR LOWER(victim_name) LIKE 'sandbox %'",
    /^(test|sample|demo|fake|sandbox)\b/i],
  ["context = 'unit test' OR context LIKE '%test%'",
    /^unit test$/i],
];
const TAKEDOWN_PATTERNS = [
  ["url LIKE 'http://127.0.0.1%' OR url LIKE 'http://localhost%' OR url LIKE 'http://0.0.0.0%'",
    /https?:\/\/(127\.0\.0\.1|localhost|0\.0\.0\.0)/i],
  ["domain IN ('127.0.0.1', 'localhost', '0.0.0.0')",
    /^(127\.0\.0\.1|localhost|0\.0\.0\.0)$/i],
  ["contact_email LIKE '%@test.%' OR contact_email LIKE '%@example.%' OR contact_email LIKE '%@invalid.'",
    /@(test|example|invalid)\./i],
  // Some test takedowns used generated placeholder emails
  ["contact_email LIKE '%@test-platform.%'",
    /@test-platform\./i],
];

function flagRows(table, patterns, label) {
  let totalFlagged = 0;
  const detail = [];
  for (const [where, _re] of patterns) {
    const cnt = countRows(`SELECT COUNT(*) AS n FROM ${table} WHERE is_test_data=0 AND (${where})`);
    if (cnt > 0) {
      totalFlagged += cnt;
      detail.push({ where, count: cnt });
      if (!DRY) {
        db.exec(`UPDATE ${table} SET is_test_data=1 WHERE is_test_data=0 AND (${where})`);
      }
    }
  }
  return { totalFlagged, detail, label };
}

console.log('\n=== Flagging test data ===\n');
const r1 = flagRows('users',     USER_PATTERNS,     'users');
const r2 = flagRows('cases',     CASE_PATTERNS,     'cases');
const r3 = flagRows('takedowns', TAKEDOWN_PATTERNS, 'takedowns');

function report(r) {
  console.log(`${r.label}: ${r.totalFlagged} flagged`);
  for (const d of r.detail) console.log(`  ${d.count.toString().padStart(4)} ← ${d.where.slice(0, 80)}`);
}
report(r1);
report(r2);
report(r3);

// Cross-propagate: any takedown whose case is test → also flag the takedown.
// And any case whose user is test → also flag the case.
console.log('\n=== Cross-propagating flags (case→takedown, user→case) ===\n');
function crossPropagate() {
  const tdsViaCase = countRows(`
    SELECT COUNT(*) AS n FROM takedowns t
      JOIN cases c ON c.id = t.case_id
     WHERE t.is_test_data=0 AND c.is_test_data=1
  `);
  const casesViaUser = countRows(`
    SELECT COUNT(*) AS n FROM cases c
      JOIN users u ON u.id = c.user_id
     WHERE c.is_test_data=0 AND u.is_test_data=1
  `);
  const tdsViaUser = countRows(`
    SELECT COUNT(*) AS n FROM takedowns t
      JOIN users u ON u.id = t.user_id
     WHERE t.is_test_data=0 AND u.is_test_data=1
  `);
  if (tdsViaCase + casesViaUser + tdsViaUser > 0) {
    console.log(`  ${tdsViaCase} takedowns via test case, ${casesViaUser} cases via test user, ${tdsViaUser} takedowns via test user`);
    if (!DRY) {
      db.exec(`UPDATE takedowns SET is_test_data=1 WHERE is_test_data=0 AND case_id IN (SELECT id FROM cases WHERE is_test_data=1)`);
      db.exec(`UPDATE cases SET is_test_data=1 WHERE is_test_data=0 AND user_id IN (SELECT id FROM users WHERE is_test_data=1)`);
      db.exec(`UPDATE takedowns SET is_test_data=1 WHERE is_test_data=0 AND user_id IN (SELECT id FROM users WHERE is_test_data=1)`);
    }
  } else {
    console.log('  no orphans — every test takedown/case is already flagged');
  }
}
crossPropagate();

// Final summary
const final = {
  testUsers:     countRows('SELECT COUNT(*) AS n FROM users     WHERE is_test_data=1'),
  realUsers:     countRows('SELECT COUNT(*) AS n FROM users     WHERE is_test_data=0'),
  testCases:     countRows('SELECT COUNT(*) AS n FROM cases     WHERE is_test_data=1'),
  realCases:     countRows('SELECT COUNT(*) AS n FROM cases     WHERE is_test_data=0'),
  testTakedowns: countRows('SELECT COUNT(*) AS n FROM takedowns WHERE is_test_data=1'),
  realTakedowns: countRows('SELECT COUNT(*) AS n FROM takedowns WHERE is_test_data=0'),
};
console.log('\n=== Final state ===');
console.log(`  users:     ${final.testUsers} test, ${final.realUsers} real`);
console.log(`  cases:     ${final.testCases} test, ${final.realCases} real`);
console.log(`  takedowns: ${final.testTakedowns} test, ${final.realTakedowns} real`);
console.log(DRY ? '\n(dry-run — no changes written)' : '\nDone.');