wulf-pulse/scripts/reclassify-seubert-campaigns.ts

/**
 * reclassify-seubert-campaigns.ts
 *
 * One-off ops script (quick task 260717-a19): reclassifies the 6 Seubert
 * phishing tickets that were misrouted to UNWANTED before the
 * KNOWN_SIMULATION_SENDERS allowlist gained the 3 confirmed KnowBe4 domains
 * (customer-portal.info, cloud-service-care.com, bankonlinesupport.com) in
 * this same quick task. MUST run after that allowlist commit lands — it
 * depends on the corrected allowlist data path in classifyCampaign().
 *
 * For each of the 6 tickets:
 *   1. Looks up campaign_id LIVE via `reports.ticket_id` (never hardcoded).
 *   2. Reads the current ("before") verdict from `classifications`.
 *   3. Calls classifyCampaign(campaignId) — appends a new classifications
 *      row (D-02 append-only; the newest row becomes "current"). Never
 *      overwrites history.
 *   4. Prints ticketId, campaignId, before verdict -> after verdict,
 *      confidence.
 *   5. Prints a summary count of how many flipped to USER_AWARENESS.
 *
 * Two pairs of tickets may share a single campaign_id
 * (699421+699422, 699419+699433) — campaign_ids are deduped before calling
 * classifyCampaign so a shared campaign is never classified twice, while
 * still reporting the full per-ticket mapping.
 *
 * Usage:
 *   POSTGRES_HOST=localhost npx tsx scripts/reclassify-seubert-campaigns.ts
 */

import { config } from 'dotenv';
import { resolve } from 'path';

config({ path: resolve(__dirname, '../.env.local') });
config({ path: resolve(__dirname, '../.env') });

// When run from the host (not inside the docker network), POSTGRES_HOST is
// 'postgres' which won't resolve. Fall back to localhost.
if (process.env.POSTGRES_HOST === 'postgres') {
  process.env.POSTGRES_HOST = 'localhost';
}

import postgresClient from '../lib/services/postgres-client';
import { classifyCampaign } from '../lib/services/campaign-classifier';

const TICKET_IDS = [699419, 699421, 699422, 699433, 699435, 699456];

interface TicketMapping {
  ticketId: number;
  campaignId: string | null;
}

async function lookupCampaignId(ticketId: number): Promise<string | null> {
  const result = await postgresClient.query<{ campaign_id: string | null }>(
    `SELECT campaign_id::text AS campaign_id FROM reports WHERE ticket_id = $1`,
    [ticketId]
  );
  return result.rows[0]?.campaign_id ?? null;
}

async function lookupBeforeVerdict(campaignId: string): Promise<string | null> {
  const result = await postgresClient.query<{ verdict: string | null }>(
    `SELECT verdict FROM classifications WHERE campaign_id = $1 ORDER BY created_at DESC LIMIT 1`,
    [campaignId]
  );
  return result.rows[0]?.verdict ?? null;
}

async function main() {
  console.log('[reclassify-seubert-campaigns] starting for tickets:', TICKET_IDS.join(', '));
  console.log('');

  // Step 1: resolve campaign_id per ticket LIVE (no hardcoded UUIDs).
  const mappings: TicketMapping[] = [];
  for (const ticketId of TICKET_IDS) {
    const campaignId = await lookupCampaignId(ticketId);
    if (!campaignId) {
      console.warn(`[warn] ticket ${ticketId} — no campaign_id found (reports.campaign_id is null); skipping`);
    }
    mappings.push({ ticketId, campaignId });
  }

  // Step 2: read "before" verdict per unique campaign (dedupe before
  // classifying — shared campaigns must not be classified twice).
  const uniqueCampaignIds = [...new Set(mappings.map((m) => m.campaignId).filter((id): id is string => !!id))];
  const beforeByCampaign = new Map<string, string | null>();
  for (const campaignId of uniqueCampaignIds) {
    beforeByCampaign.set(campaignId, await lookupBeforeVerdict(campaignId));
  }

  // Step 3: classify each unique campaign exactly once.
  const afterByCampaign = new Map<string, { verdict: string; confidence: number }>();
  for (const campaignId of uniqueCampaignIds) {
    try {
      const result = await classifyCampaign(campaignId);
      afterByCampaign.set(campaignId, { verdict: result.verdict, confidence: result.confidence });
    } catch (err) {
      console.error(
        `[err] campaign ${campaignId} — classifyCampaign failed: ${err instanceof Error ? err.message : String(err)}`
      );
    }
  }

  // Step 4: print per-ticket before -> after lines.
  console.log('');
  console.log('Per-ticket results:');
  let flippedToUserAwareness = 0;
  for (const { ticketId, campaignId } of mappings) {
    if (!campaignId) {
      console.log(`  ticket ${ticketId}: SKIPPED (no campaign_id)`);
      continue;
    }
    const before = beforeByCampaign.get(campaignId) ?? '(none)';
    const after = afterByCampaign.get(campaignId);
    if (!after) {
      console.log(`  ticket ${ticketId} (campaign ${campaignId}): SKIPPED (classifyCampaign failed)`);
      continue;
    }
    console.log(
      `  ticket ${ticketId} (campaign ${campaignId}): ${before} -> ${after.verdict} (confidence ${after.confidence})`
    );
    if (after.verdict === 'USER_AWARENESS') {
      flippedToUserAwareness++;
    }
  }

  // Step 5: summary.
  console.log('');
  console.log(
    `[reclassify-seubert-campaigns] done. ${uniqueCampaignIds.length} unique campaign(s) reclassified across ${TICKET_IDS.length} tickets; ${flippedToUserAwareness} ticket(s) now USER_AWARENESS.`
  );

  // pg pool keeps the process alive otherwise.
  process.exit(0);
}

main().catch((err) => {
  console.error('[reclassify-seubert-campaigns] fatal:', err);
  process.exit(1);
});