#!/usr/bin/env node
/**
 * ADR-0009 Phase 2: Test-Data Cleanup — DRY-RUN manifest generator.
 *
 * Default mode: DRY-RUN. No DELETE, DROP, or UPDATE is issued.
 * Writes a snapshot directory scripts/snapshots/cleanup-<ts>/ containing CSVs
 * of every row that WOULD be deleted, plus a MANIFEST.md summary.
 *
 * Usage:
 *   node scripts/cleanup-test-data.js                   # dry-run (default)
 *   node scripts/cleanup-test-data.js --execute         # destructive — NOT in Phase 2
 *
 * Safety guards (see ADR-0009 §6 Constraints):
 *   1. Refuses to run --execute without --target=prod AND --owner-totp=XXXXXX.
 *   2. Refuses to run --execute if POSTGRES_HOST is not localhost (running
 *      the script against a remote PROD DB is not permitted — must be on the
 *      host, where localhost==PROD).
 *   3. The allow-list (scripts/cleanup-allowlist.json) MUST have
 *      owner_signed: true for --execute to proceed.
 *
 * Allow-list semantics:
 *   - Any id present in allowlist.users[].id is NEVER deleted.
 *   - Any id in allowlist.spaces[].id is NEVER deleted.
 *   - Any id in allowlist.tables[].id is NEVER deleted (and its table_rows kept).
 *   - False positive in allow-list is safe; false negative risks deleting
 *     real user data. When in doubt, include.
 */

import fs from 'node:fs';
import path from 'node:path';
import pg from 'pg';
import dotenv from 'dotenv';

dotenv.config({ path: path.resolve(process.cwd(), '.env') });

// ---------- argv ----------
const argv = process.argv.slice(2);
const hasFlag = (f) => argv.includes(f);
const getFlag = (f) => {
  const p = argv.find((a) => a.startsWith(f + '='));
  return p ? p.slice(f.length + 1) : null;
};
const EXECUTE = hasFlag('--execute');
const ROLLBACK = hasFlag('--rollback'); // Phase 3 safety: run deletes, capture counts, then ROLLBACK.
const TARGET = getFlag('--target');
const OWNER_TOTP = getFlag('--owner-totp');

// ---------- safety ----------
const POSTGRES_HOST = process.env.POSTGRES_HOST || 'localhost';

if (EXECUTE) {
  if (!['prod', 'dev'].includes(TARGET)) {
    console.error('[FATAL] --execute requires --target=prod or --target=dev (safety gate).');
    process.exit(2);
  }
  if (TARGET === 'prod' && !OWNER_TOTP) {
    console.error('[FATAL] --execute --target=prod requires --owner-totp=XXXXXX (owner signs off at runtime).');
    process.exit(2);
  }
  if (!['localhost', '127.0.0.1', ''].includes(POSTGRES_HOST)) {
    console.error(`[FATAL] Refusing to --execute against remote host ${POSTGRES_HOST}.`);
    console.error(`        For --target=dev run on DEV server with POSTGRES_HOST=localhost (DEV-local godcrm_prod copy).`);
    console.error(`        For --target=prod run on PROD server (.205) where localhost==PROD.`);
    process.exit(2);
  }
  // ADR-0009 §9: PROD gate lifted 2026-04-22 after DEV Phase 3 succeeded and
  // owner signed off at runtime (owner_signed=true, owner-totp provided, MANIFEST approved).
  // Safety: allow-list + localhost + owner_signed still enforced further down.
}

// ---------- allow-list ----------
const ALLOWLIST_PATH = path.resolve(process.cwd(), 'scripts/cleanup-allowlist.json');
if (!fs.existsSync(ALLOWLIST_PATH)) {
  console.error(`[FATAL] Missing ${ALLOWLIST_PATH}. Generate it first (see ADR-0009 §9 Phase 2).`);
  process.exit(2);
}
const allowlist = JSON.parse(fs.readFileSync(ALLOWLIST_PATH, 'utf-8'));
const ALLOW_USER_IDS = new Set(allowlist.users.map((u) => u.id));
const ALLOW_SPACE_IDS = new Set(allowlist.spaces.map((s) => s.id));
const ALLOW_TABLE_IDS = new Set(allowlist.tables.map((t) => t.id));

// ---------- db ----------
const pool = new pg.Pool({
  host: POSTGRES_HOST,
  port: Number(process.env.POSTGRES_PORT || 5432),
  database: process.env.POSTGRES_DB || 'godcrm_prod',
  user: process.env.POSTGRES_USER || 'godcrm',
  password: process.env.POSTGRES_PASSWORD,
  max: 4,
});

async function q(sql, params = []) {
  const r = await pool.query(sql, params);
  return r.rows;
}

// ---------- test signatures (ADR-0009 §4.1) ----------
// Users: email matches any of these patterns OR name matches.
// NOTE: regex semantics match PostgreSQL `~*` (case-insensitive regex).
const TEST_EMAIL_RX = '@test\\.com|@example\\.com|^test-';
const TEST_NAME_RX = '^test-.*-[0-9]{10,}$|^tables-test-';

// Agents/service users are ALWAYS protected regardless of signature match.
// This is enforced by pre-populating the allow-list with every agent/service user.

// ---------- snapshot dir ----------
const ts = new Date().toISOString().replace(/[-:]/g, '').replace(/\..+/, '').replace('T', '-');
const snapDir = path.resolve(process.cwd(), `scripts/snapshots/cleanup-${ts}`);
fs.mkdirSync(snapDir, { recursive: true });
console.log(`[ok] snapshot dir: ${snapDir}`);

function writeCsv(filename, columns, rows) {
  const csvEsc = (v) => {
    if (v === null || v === undefined) return '';
    const s = String(v);
    if (s.includes(',') || s.includes('"') || s.includes('\n')) {
      return '"' + s.replace(/"/g, '""') + '"';
    }
    return s;
  };
  const header = columns.join(',');
  const body = rows.map((r) => columns.map((c) => csvEsc(r[c])).join(',')).join('\n');
  const content = rows.length ? header + '\n' + body + '\n' : header + '\n';
  fs.writeFileSync(path.join(snapDir, filename), content);
  return rows.length;
}

// ---------- main ----------
async function main() {
  console.log(`\n=== ADR-0009 Phase 2 cleanup DRY-RUN ===`);
  console.log(`allow-list: ${ALLOW_USER_IDS.size} users, ${ALLOW_SPACE_IDS.size} spaces, ${ALLOW_TABLE_IDS.size} tables`);
  console.log(`owner_signed: ${allowlist.owner_signed}`);

  // --- 1. Candidate users ------------------------------------------------
  // A user is a DELETE candidate if:
  //   (a) email OR name matches test-signature regex, AND
  //   (b) id is NOT in allow-list
  // Agents/service are automatically in allow-list, so they are filtered out by (b).
  const userRows = await q(`
    SELECT id, email, name, user_type, role, status, created_at
    FROM users
    WHERE (email ~* $1 OR name ~* $2)
  `, [TEST_EMAIL_RX, TEST_NAME_RX]);

  const deleteUsers = userRows.filter((u) => !ALLOW_USER_IDS.has(u.id));
  const skippedUsers = userRows.filter((u) => ALLOW_USER_IDS.has(u.id));
  const deleteUserIds = new Set(deleteUsers.map((u) => u.id));

  writeCsv('users.csv',
    ['id', 'email', 'name', 'user_type', 'role', 'status', 'created_at'],
    deleteUsers,
  );
  console.log(`[users] candidates=${deleteUsers.length}, skipped-by-allowlist=${skippedUsers.length}`);

  // --- 2. Candidate spaces ----------------------------------------------
  // Spaces owned by a candidate user, MINUS allow-list spaces.
  let deleteSpaces = [];
  let deleteSpaceIds = new Set();
  if (deleteUserIds.size > 0) {
    const rows = await q(
      `SELECT id, owner_id, name, type, created_at
         FROM spaces
        WHERE owner_id = ANY($1::int[])`,
      [[...deleteUserIds]],
    );
    deleteSpaces = rows.filter((s) => !ALLOW_SPACE_IDS.has(s.id));
    deleteSpaceIds = new Set(deleteSpaces.map((s) => s.id));
  }
  writeCsv('spaces.csv',
    ['id', 'owner_id', 'name', 'type', 'created_at'],
    deleteSpaces,
  );
  console.log(`[spaces] candidates=${deleteSpaces.length}`);

  // --- 3. Candidate projects --------------------------------------------
  // A project is a candidate if its space is a candidate OR its owner is a candidate user.
  let deleteProjects = [];
  let deleteProjectIds = new Set();
  const projCond = [];
  const projParams = [];
  if (deleteSpaceIds.size > 0) {
    projCond.push(`space_id = ANY($${projParams.length + 1}::int[])`);
    projParams.push([...deleteSpaceIds]);
  }
  if (deleteUserIds.size > 0) {
    projCond.push(`owner_id = ANY($${projParams.length + 1}::int[])`);
    projParams.push([...deleteUserIds]);
  }
  if (projCond.length) {
    deleteProjects = await q(
      `SELECT id, name, space_id, owner_id, type, created_at
         FROM projects
        WHERE ${projCond.join(' OR ')}`,
      projParams,
    );
    deleteProjectIds = new Set(deleteProjects.map((p) => p.id));
  }
  writeCsv('projects.csv',
    ['id', 'name', 'space_id', 'owner_id', 'type', 'created_at'],
    deleteProjects,
  );
  console.log(`[projects] candidates=${deleteProjects.length}`);

  // --- 4. Candidate universal_tables ------------------------------------
  // A table is a candidate if its project is a candidate OR its created_by is a candidate user.
  // allow-list tables always kept.
  let deleteTables = [];
  let deleteTableIds = new Set();
  const tblCond = [];
  const tblParams = [];
  if (deleteProjectIds.size > 0) {
    tblCond.push(`project_id = ANY($${tblParams.length + 1}::int[])`);
    tblParams.push([...deleteProjectIds]);
  }
  if (deleteUserIds.size > 0) {
    tblCond.push(`created_by = ANY($${tblParams.length + 1}::int[])`);
    tblParams.push([...deleteUserIds]);
  }
  if (tblCond.length) {
    const rows = await q(
      `SELECT id, name, project_id, created_by, created_at
         FROM universal_tables
        WHERE ${tblCond.join(' OR ')}`,
      tblParams,
    );
    deleteTables = rows.filter((t) => !ALLOW_TABLE_IDS.has(t.id));
    deleteTableIds = new Set(deleteTables.map((t) => t.id));
  }
  writeCsv('universal_tables.csv',
    ['id', 'name', 'project_id', 'created_by', 'created_at'],
    deleteTables,
  );
  console.log(`[universal_tables] candidates=${deleteTables.length}`);

  // Check for physical per-table PG tables (table_<N>). None exist in current
  // schema, but keep the check so Phase 3 rescan catches any that appear.
  const physicalTables = deleteTableIds.size
    ? await q(
        `SELECT table_name
           FROM information_schema.tables
          WHERE table_schema='public'
            AND table_name ~ '^table_[0-9]+$'
            AND (regexp_replace(table_name,'^table_',''))::int = ANY($1::int[])`,
        [[...deleteTableIds]],
      )
    : [];
  writeCsv('physical_tables_to_drop.csv',
    ['table_name'],
    physicalTables,
  );
  console.log(`[physical table_<N>] to drop: ${physicalTables.length}`);

  // --- 5. table_rows summary (per-table counts) -------------------------
  let rowSummary = [];
  let rowTotal = 0;
  if (deleteTableIds.size > 0) {
    rowSummary = await q(
      `SELECT table_id, COUNT(*) AS row_count
         FROM table_rows
        WHERE table_id = ANY($1::int[])
        GROUP BY table_id
        ORDER BY row_count DESC`,
      [[...deleteTableIds]],
    );
    rowTotal = rowSummary.reduce((s, r) => s + Number(r.row_count), 0);
  }
  writeCsv('table_rows_summary.csv',
    ['table_id', 'row_count'],
    rowSummary,
  );
  console.log(`[table_rows] candidate rows across ${rowSummary.length} tables: ${rowTotal}`);

  // --- 6. Dependent FK-by-convention tables -----------------------------
  // For each table that references users or spaces by convention, capture
  // rows that would orphan. See FK inventory in ADR-0009 §11 Appendix B.
  const dep = [];

  // spaces-referencing
  const spaceChildren = [
    { table: 'calendar_events', cols: ['id', 'space_id', 'created_by', 'title', 'start_at'], where: 'space_id' },
    { table: 'modules',         cols: ['id', 'space_id', 'name'],                             where: 'space_id' },
    { table: 'widget_library',  cols: ['id', 'space_id', 'name'],                             where: 'space_id' },
    { table: 'fitness_workouts', cols: ['id', 'space_id'],                                    where: 'space_id' },
    { table: 'fitness_workout_sets', cols: ['id', 'space_id'],                                where: 'space_id' },
    { table: 'fitness_exercises',    cols: ['id', 'space_id'],                                where: 'space_id' },
    { table: 'wellness_profiles', cols: ['id', 'space_id'],                                   where: 'space_id' },
    { table: 'wellness_points',   cols: ['id', 'space_id'],                                   where: 'space_id' },
    { table: 'wellness_levels',   cols: ['id', 'space_id'],                                   where: 'space_id' },
    { table: 'wellness_streaks',  cols: ['id', 'space_id'],                                   where: 'space_id' },
    { table: 'wellness_vitals',   cols: ['id', 'space_id'],                                   where: 'space_id' },
    { table: 'wellness_user_achievements', cols: ['id', 'space_id'],                          where: 'space_id' },
    { table: 'labs',              cols: ['id', 'space_id', 'name'],                           where: 'space_id' },
    { table: 'schema_layouts',    cols: ['id', 'space_id'],                                   where: 'space_id' },
    { table: 'space_invitations', cols: ['id', 'space_id'],                                   where: 'space_id' },
    { table: 'conversations',     cols: ['id', 'space_id', 'created_by'],                     where: 'space_id' },
    { table: 'files',             cols: ['id', 'space_id', 'project_id', 'table_id'],         where: 'space_id' },
    { table: 'dashboards',        cols: ['id', 'space_id', 'project_id', 'user_id'],          where: 'space_id' },
    { table: 'user_access_permissions', cols: ['id','user_id','space_id','project_id','table_id'], where: 'space_id' },
  ];

  // users-referencing
  const userChildren = [
    { table: 'agent_jobs',            cols: ['id', 'agent_user_id', 'status'],                where: 'agent_user_id' },
    { table: 'terminal_sessions',     cols: ['id', 'user_id'],                                where: 'user_id' },
    { table: 'terminal_commands',     cols: ['id'],                                           where: 'user_id', skipIfNoCol: true },
    { table: 'tool_approval_rules',   cols: ['id', 'created_by'],                             where: 'created_by' },
    { table: 'api_keys',              cols: ['id', 'user_id', 'project_id'],                  where: 'user_id' },
    { table: 'audit_log',             cols: ['id', 'user_id'],                                where: 'user_id' },
    { table: 'chat_participants',     cols: ['user_id', 'conversation_id'],                   where: 'user_id', noId: true },
    { table: 'conversation_participants', cols: ['user_id', 'conversation_id'],               where: 'user_id', noId: true },
    { table: 'message_reactions',     cols: ['id', 'user_id'],                                where: 'user_id' },
    { table: 'monitoring_runs',       cols: ['id', 'user_id'],                                where: 'user_id' },
    { table: 'monitoring_threads',    cols: ['id', 'user_id'],                                where: 'user_id' },
    { table: 'oidc_access_tokens',    cols: ['id', 'user_id'],                                where: 'user_id' },
    { table: 'oidc_auth_codes',       cols: ['id', 'user_id'],                                where: 'user_id' },
    { table: 'user_settings',         cols: ['user_id'],                                      where: 'user_id', noId: true },
    { table: 'user_widget_favorites', cols: ['user_id', 'widget_id'],                         where: 'user_id', noId: true },
    { table: 'user_widget_history',   cols: ['user_id', 'widget_id'],                         where: 'user_id', noId: true },
    { table: 'wa_auth_tokens',        cols: ['id', 'user_id'],                                where: 'user_id' },
    { table: 'wa_presence',           cols: ['user_id'],                                      where: 'user_id', noId: true },
  ];

  // For robustness: look up actual columns on the target table and only select
  // columns from `cols` that actually exist. If the target WHERE column is
  // absent, skip the table with a note.
  const runChild = async ({ table, cols, where }, idSet, label) => {
    // First: does table even exist?
    const tableCheck = await q(
      `SELECT 1 FROM information_schema.tables WHERE table_schema='public' AND table_name=$1`,
      [table],
    );
    if (!tableCheck.length) {
      writeCsv(`${table}.csv`, cols, []);
      dep.push({ table, parent: label, count: 0, note: `table not found — skipped` });
      return;
    }
    const colRows = await q(
      `SELECT column_name, data_type FROM information_schema.columns WHERE table_schema='public' AND table_name=$1`,
      [table],
    );
    const actualCols = new Set(colRows.map((r) => r.column_name));
    if (!actualCols.has(where)) {
      writeCsv(`${table}.csv`, cols, []);
      dep.push({ table, parent: label, count: 0, note: `column ${where} not found — skipped` });
      return;
    }
    if (idSet.size === 0) {
      writeCsv(`${table}.csv`, cols, []);
      dep.push({ table, parent: label, count: 0 });
      return;
    }
    const selectCols = cols.filter((c) => actualCols.has(c));
    if (selectCols.length === 0) selectCols.push(where);
    // Guard against text-typed id columns (monitoring_runs.user_id is text, not int).
    const whereDt = colRows.find((r) => r.column_name === where)?.data_type;
    const isIntCol = /^(integer|bigint|smallint)$/i.test(whereDt || '');
    const castClause = isIntCol
      ? `${where} = ANY($1::int[])`
      : `${where} = ANY($1::text[])`;
    const param = isIntCol ? [...idSet] : [...idSet].map(String);
    const rows = await q(
      `SELECT ${selectCols.join(', ')} FROM ${table} WHERE ${castClause}`,
      [param],
    );
    writeCsv(`${table}.csv`, selectCols, rows);
    const note = cols.filter((c) => !actualCols.has(c));
    dep.push({ table, parent: label, count: rows.length, note: note.length ? `missing cols: ${note.join(',')}` : '' });
  };

  console.log('\n[dependents: spaces →]');
  for (const sc of spaceChildren) {
    await runChild(sc, deleteSpaceIds, 'spaces');
    const d = dep[dep.length - 1];
    if (d.count > 0) console.log(`  ${d.table}: ${d.count}${d.note ? ' (' + d.note + ')' : ''}`);
  }
  console.log('\n[dependents: users →]');
  for (const uc of userChildren) {
    await runChild(uc, deleteUserIds, 'users');
    const d = dep[dep.length - 1];
    if (d.count > 0) console.log(`  ${d.table}: ${d.count}${d.note ? ' (' + d.note + ')' : ''}`);
  }

  // --- 6b. "Orphan-suspect" users NOT covered by test signature ----------
  // The ADR signature does not match certain test-ish patterns that still
  // exist in PROD (e.g. register-<timestamp>@hltrn.cc stress-test registrations).
  // These are NOT candidates for deletion in Phase 2, but the owner should
  // inspect them before Phase 3 and decide whether to extend the regex.
  const orphanSuspects = await q(`
    SELECT id, email, name, user_type, created_at,
      (SELECT COUNT(*) FROM spaces s WHERE s.owner_id=u.id) AS spaces_owned,
      (SELECT COUNT(*) FROM projects p WHERE p.owner_id=u.id) AS projects_owned
    FROM users u
    WHERE NOT (email ~* $1 OR name ~* $2)
      AND (email ~ '^register-[0-9]{10,}@hltrn\\.cc$'
           OR name = 'New User'
           OR email ~* '\\+test[0-9]*@'
           OR email ~* 'qa[0-9]+@'
          )
    ORDER BY id
  `, [TEST_EMAIL_RX, TEST_NAME_RX]);
  writeCsv('orphan_suspects.csv',
    ['id', 'email', 'name', 'user_type', 'created_at', 'spaces_owned', 'projects_owned'],
    orphanSuspects,
  );

  // --- 7. Shape-unexpected warnings -------------------------------------
  // A "test" user should have empty or near-empty downstream footprint.
  // Surface any candidate user with > 10 calendar_events or > 10 audit_log rows.
  const weird = await q(
    `SELECT u.id, u.email, u.name,
            (SELECT COUNT(*) FROM audit_log a WHERE a.user_id=u.id) AS audits,
            (SELECT COUNT(*) FROM dashboards d WHERE d.user_id=u.id) AS dashboards,
            (SELECT COUNT(*) FROM spaces s WHERE s.owner_id=u.id) AS spaces_owned
       FROM users u
      WHERE u.id = ANY($1::int[])
      ORDER BY (
         (SELECT COUNT(*) FROM audit_log a WHERE a.user_id=u.id) +
         (SELECT COUNT(*) FROM dashboards d WHERE d.user_id=u.id)
      ) DESC
      LIMIT 20`,
    [[...deleteUserIds]],
  );
  writeCsv('weird_candidates.csv',
    ['id', 'email', 'name', 'audits', 'dashboards', 'spaces_owned'],
    weird,
  );

  // --- 8. pg_total_relation_size estimate -------------------------------
  const sizeRows = await q(`
    SELECT 'users' AS t, pg_total_relation_size('public.users') AS bytes UNION ALL
    SELECT 'spaces', pg_total_relation_size('public.spaces') UNION ALL
    SELECT 'projects', pg_total_relation_size('public.projects') UNION ALL
    SELECT 'universal_tables', pg_total_relation_size('public.universal_tables') UNION ALL
    SELECT 'table_rows', pg_total_relation_size('public.table_rows');
  `);
  const sizeByTbl = Object.fromEntries(sizeRows.map((r) => [r.t, Number(r.bytes)]));
  const totalSize = sizeRows.reduce((s, r) => s + Number(r.bytes), 0);

  // Very rough free estimate: fraction of test rows out of total rows × bytes.
  const totalUsers = Number((await q(`SELECT COUNT(*) AS c FROM users`))[0].c);
  const totalSpaces = Number((await q(`SELECT COUNT(*) AS c FROM spaces`))[0].c);
  const totalProjects = Number((await q(`SELECT COUNT(*) AS c FROM projects`))[0].c);
  const totalTables = Number((await q(`SELECT COUNT(*) AS c FROM universal_tables`))[0].c);
  const totalRows = Number((await q(`SELECT COUNT(*) AS c FROM table_rows`))[0].c);

  const est = (frac, bytes) => Math.round(frac * bytes);
  const freedEst =
    est(deleteUsers.length / Math.max(totalUsers, 1), sizeByTbl.users) +
    est(deleteSpaces.length / Math.max(totalSpaces, 1), sizeByTbl.spaces) +
    est(deleteProjects.length / Math.max(totalProjects, 1), sizeByTbl.projects) +
    est(deleteTables.length / Math.max(totalTables, 1), sizeByTbl.universal_tables) +
    est(rowTotal / Math.max(totalRows, 1), sizeByTbl.table_rows);
  const fmtMB = (b) => (b / 1024 / 1024).toFixed(2) + ' MB';

  // --- 9. MANIFEST ------------------------------------------------------
  // Topological delete order — mirrors what Phase 3 --execute must do.
  const deleteOrder = [
    '1. table_rows  WHERE table_id IN (candidate universal_tables)',
    '2. DROP TABLE table_<N>  (physical per-table PG tables for candidate universal_tables) — N/A, none exist',
    '3. universal_tables  (candidate set)',
    '4. all FK-by-convention children referencing candidate projects',
    '5. projects  (candidate set)',
    '6. all FK-by-convention children referencing candidate spaces (modules/widget_library/fitness_*/wellness_*/calendar_events/labs/schema_layouts/space_invitations/dashboards/files/conversations/user_access_permissions)',
    '7. spaces  (candidate set)',
    '8. all FK-by-convention children referencing candidate users (agent_jobs/terminal_*/tool_approval_rules/wa_*/audit_log/monitoring_*/oidc_*/api_keys/user_settings/user_widget_*/chat_participants/conversation_participants/message_reactions)',
    '9. users  (candidate set)',
  ];

  const manifest = [];
  manifest.push(`# ADR-0009 Phase 2 cleanup DRY-RUN manifest`);
  manifest.push(``);
  manifest.push(`- Generated: ${new Date().toISOString()}`);
  manifest.push(`- Mode: DRY-RUN (no writes)`);
  manifest.push(`- Target DB: ${POSTGRES_HOST}/${process.env.POSTGRES_DB || 'godcrm_prod'}`);
  manifest.push(`- Allow-list file: scripts/cleanup-allowlist.json (owner_signed=${allowlist.owner_signed})`);
  manifest.push(`- Allow-list size: ${ALLOW_USER_IDS.size} users, ${ALLOW_SPACE_IDS.size} spaces, ${ALLOW_TABLE_IDS.size} tables`);
  manifest.push(``);
  manifest.push(`## Test signatures`);
  manifest.push(`- email regex: \`${TEST_EMAIL_RX}\``);
  manifest.push(`- name regex:  \`${TEST_NAME_RX}\``);
  manifest.push(`- Agents (user_type='agent') and services (user_type='service') are unconditionally allow-listed regardless of regex match.`);
  manifest.push(``);
  manifest.push(`## Candidate summary`);
  manifest.push(`| Entity | Total in DB | To delete | To keep |`);
  manifest.push(`|---|---:|---:|---:|`);
  manifest.push(`| users | ${totalUsers} | ${deleteUsers.length} | ${totalUsers - deleteUsers.length} |`);
  manifest.push(`| spaces | ${totalSpaces} | ${deleteSpaces.length} | ${totalSpaces - deleteSpaces.length} |`);
  manifest.push(`| projects | ${totalProjects} | ${deleteProjects.length} | ${totalProjects - deleteProjects.length} |`);
  manifest.push(`| universal_tables | ${totalTables} | ${deleteTables.length} | ${totalTables - deleteTables.length} |`);
  manifest.push(`| table_rows | ${totalRows} | ${rowTotal} | ${totalRows - rowTotal} |`);
  manifest.push(`| physical table_<N> | (n/a) | ${physicalTables.length} | n/a |`);
  manifest.push(``);
  manifest.push(`## ADR Appendix A comparison`);
  manifest.push(`ADR predicted: 5878 users to delete, 5328 spaces to delete.`);
  manifest.push(`Actual: ${deleteUsers.length} users, ${deleteSpaces.length} spaces.`);
  const udelta = Math.abs(deleteUsers.length - 5878) / 5878;
  const sdelta = Math.abs(deleteSpaces.length - 5328) / 5328;
  manifest.push(`Delta: users ${(udelta * 100).toFixed(1)}%, spaces ${(sdelta * 100).toFixed(1)}%.`);
  if (udelta > 0.1 || sdelta > 0.1) {
    manifest.push(`**WARNING:** delta > 10% — regex may be wrong or allow-list may have too many/few entries.`);
  } else {
    manifest.push(`Delta within 10% — signature matches ADR audit.`);
  }
  manifest.push(``);
  manifest.push(`## Dependent FK-by-convention child counts`);
  manifest.push(`| Table | Parent | Candidate rows | Note |`);
  manifest.push(`|---|---|---:|---|`);
  for (const d of dep) {
    manifest.push(`| ${d.table} | ${d.parent} | ${d.count} | ${d.note || ''} |`);
  }
  manifest.push(``);
  manifest.push(`## Biggest individual deletes (by table_rows count)`);
  manifest.push(`| universal_table_id | rows |`);
  manifest.push(`|---|---:|`);
  for (const r of rowSummary.slice(0, 10)) {
    manifest.push(`| ${r.table_id} | ${r.row_count} |`);
  }
  manifest.push(``);
  manifest.push(`## Orphan-suspect users (NOT covered by current signature)`);
  manifest.push(`These users look test-ish (\`register-<ts>@hltrn.cc\`, name = "New User", etc.) but are NOT covered by the ADR regex, so they will NOT be deleted in the current plan. Owner should decide before Phase 3 whether to extend the regex. See orphan_suspects.csv.`);
  manifest.push(`Total: ${orphanSuspects.length}`);
  manifest.push(`| id | email | name | spaces_owned | projects_owned |`);
  manifest.push(`|---|---|---|---:|---:|`);
  for (const o of orphanSuspects.slice(0, 10)) {
    manifest.push(`| ${o.id} | ${o.email} | ${o.name} | ${o.spaces_owned} | ${o.projects_owned} |`);
  }
  manifest.push(``);
  manifest.push(`## Shape-unexpected warnings`);
  manifest.push(`Candidate users with non-trivial downstream activity (see weird_candidates.csv):`);
  manifest.push(`| id | email | audits | dashboards | spaces_owned |`);
  manifest.push(`|---|---|---:|---:|---:|`);
  for (const w of weird.slice(0, 10)) {
    manifest.push(`| ${w.id} | ${w.email} | ${w.audits} | ${w.dashboards} | ${w.spaces_owned} |`);
  }
  manifest.push(``);
  manifest.push(`## Estimated bytes freed`);
  manifest.push(`Rough proportional estimate (rows_to_delete / total_rows × table_size):`);
  manifest.push(`- users:            ${fmtMB(est(deleteUsers.length / Math.max(totalUsers, 1), sizeByTbl.users))} (of ${fmtMB(sizeByTbl.users)})`);
  manifest.push(`- spaces:           ${fmtMB(est(deleteSpaces.length / Math.max(totalSpaces, 1), sizeByTbl.spaces))} (of ${fmtMB(sizeByTbl.spaces)})`);
  manifest.push(`- projects:         ${fmtMB(est(deleteProjects.length / Math.max(totalProjects, 1), sizeByTbl.projects))} (of ${fmtMB(sizeByTbl.projects)})`);
  manifest.push(`- universal_tables: ${fmtMB(est(deleteTables.length / Math.max(totalTables, 1), sizeByTbl.universal_tables))} (of ${fmtMB(sizeByTbl.universal_tables)})`);
  manifest.push(`- table_rows:       ${fmtMB(est(rowTotal / Math.max(totalRows, 1), sizeByTbl.table_rows))} (of ${fmtMB(sizeByTbl.table_rows)})`);
  manifest.push(`- **TOTAL estimate freed:** ~${fmtMB(freedEst)} (of DB main-table footprint ${fmtMB(totalSize)})`);
  manifest.push(`(Does not include per-index bloat or dependent child tables; actual free may be higher after VACUUM FULL.)`);
  manifest.push(``);
  manifest.push(`## Topological delete order (Phase 3 --execute)`);
  for (const step of deleteOrder) manifest.push(`- ${step}`);
  manifest.push(``);
  manifest.push(`All wrapped in a single \`BEGIN; ... COMMIT;\` transaction; any error triggers \`ROLLBACK\`.`);
  manifest.push(``);
  manifest.push(`## Files in this snapshot`);
  for (const f of fs.readdirSync(snapDir).sort()) {
    manifest.push(`- ${f}`);
  }

  fs.writeFileSync(path.join(snapDir, 'MANIFEST.md'), manifest.join('\n') + '\n');
  console.log(`\n[ok] manifest: ${path.join(snapDir, 'MANIFEST.md')}`);

  if (!EXECUTE) {
    console.log(`\nDRY-RUN COMPLETE — no rows modified`);
    return;
  }

  // ============================================================
  // Phase 3 destructive path (--execute --target=dev|prod)
  // ============================================================
  console.log(`\n=== PHASE 3 DESTRUCTIVE EXECUTION ===`);
  console.log(`Target: ${TARGET}  Mode: ${ROLLBACK ? 'BEGIN; ... ROLLBACK (dry-transaction)' : 'BEGIN; ... COMMIT (destructive)'}`);
  if (!allowlist.owner_signed) {
    console.error('[FATAL] owner_signed=false — set to true in cleanup-allowlist.json after MANIFEST review.');
    process.exit(2);
  }

  const client = await pool.connect();
  const counts = {};
  let committed = false;
  try {
    await client.query('BEGIN');

    const candidateUsers = [...deleteUserIds];
    const candidateSpaces = [...deleteSpaceIds];
    const candidateProjects = [...deleteProjectIds];
    const candidateTables = [...deleteTableIds];

    // Pre-compute conversation IDs belonging to candidate spaces — their children
    // CASCADE on spaces delete, but agent_jobs.conversation_id is FK=NO ACTION
    // and must be nulled/cleared first.
    let candidateConvIds = [];
    if (candidateSpaces.length) {
      const r = await client.query(
        `SELECT id FROM conversations WHERE space_id = ANY($1::int[])`,
        [candidateSpaces],
      );
      candidateConvIds = r.rows.map((x) => x.id);
    }
    console.log(`  conversations in candidate spaces (will cascade on space delete): ${candidateConvIds.length}`);

    // Helper: run DELETE, capture rowCount.
    const del = async (label, sql, params) => {
      const r = await client.query(sql, params);
      counts[label] = r.rowCount;
      console.log(`  [-] ${label}: ${r.rowCount}`);
      return r.rowCount;
    };

    // --- Step A: break NO ACTION FKs referencing candidate users / conversations
    //     (agent_jobs, terminal_*, tool_approval_rules, wa_*)
    if (candidateUsers.length || candidateConvIds.length) {
      const conds = [];
      const params = [];
      if (candidateUsers.length) {
        conds.push(`agent_user_id = ANY($${params.length + 1}::int[])`);
        params.push(candidateUsers);
        conds.push(`trigger_user_id = ANY($${params.length + 1}::int[])`);
        params.push(candidateUsers);
      }
      if (candidateConvIds.length) {
        conds.push(`conversation_id = ANY($${params.length + 1}::int[])`);
        params.push(candidateConvIds);
      }
      await del('agent_jobs', `DELETE FROM agent_jobs WHERE ${conds.join(' OR ')}`, params);
    }

    if (candidateUsers.length) {
      await del('terminal_commands', `DELETE FROM terminal_commands WHERE approved_by = ANY($1::int[])`, [candidateUsers]);
      await del('terminal_sessions', `DELETE FROM terminal_sessions WHERE user_id = ANY($1::int[])`, [candidateUsers]);
      await del('tool_approval_rules', `DELETE FROM tool_approval_rules WHERE created_by = ANY($1::int[])`, [candidateUsers]);
      await del('wa_auth_tokens', `DELETE FROM wa_auth_tokens WHERE user_id = ANY($1::int[])`, [candidateUsers]);
      await del('wa_presence', `DELETE FROM wa_presence WHERE user_id = ANY($1::int[])`, [candidateUsers]);
    }

    // --- Step B: drop per-table rows + universal_tables + projects
    if (candidateTables.length) {
      await del('table_rows', `DELETE FROM table_rows WHERE table_id = ANY($1::int[])`, [candidateTables]);
      await del('universal_tables', `DELETE FROM universal_tables WHERE id = ANY($1::int[])`, [candidateTables]);
    }
    if (candidateProjects.length) {
      await del('projects', `DELETE FROM projects WHERE id = ANY($1::int[])`, [candidateProjects]);
    }

    // --- Step C: space-children (NO ACTION FKs must delete first)
    if (candidateSpaces.length) {
      await del('calendar_events',   `DELETE FROM calendar_events   WHERE space_id = ANY($1::int[])`, [candidateSpaces]);
      await del('modules',           `DELETE FROM modules           WHERE space_id = ANY($1::int[])`, [candidateSpaces]);
      await del('widget_library',    `DELETE FROM widget_library    WHERE space_id = ANY($1::int[])`, [candidateSpaces]);
      // fitness_workout_sets is a VIEW joining fitness_workouts + fitness_sets.
      // fitness_sets CASCADES via workout_id → fitness_workouts FK (confdeltype=c).
      // Delete order: fitness_workouts (→ cascades fitness_sets) → fitness_exercises.
      await del('fitness_workouts',  `DELETE FROM fitness_workouts  WHERE space_id = ANY($1::int[])`, [candidateSpaces]);
      await del('fitness_exercises', `DELETE FROM fitness_exercises WHERE space_id = ANY($1::int[])`, [candidateSpaces]);
      await del('wellness_levels',   `DELETE FROM wellness_levels   WHERE space_id = ANY($1::int[])`, [candidateSpaces]);
      await del('wellness_points',   `DELETE FROM wellness_points   WHERE space_id = ANY($1::int[])`, [candidateSpaces]);
      await del('wellness_profiles', `DELETE FROM wellness_profiles WHERE space_id = ANY($1::int[])`, [candidateSpaces]);
      await del('wellness_streaks',  `DELETE FROM wellness_streaks  WHERE space_id = ANY($1::int[])`, [candidateSpaces]);
      await del('wellness_vitals',   `DELETE FROM wellness_vitals   WHERE space_id = ANY($1::int[])`, [candidateSpaces]);
      await del('wellness_user_achievements', `DELETE FROM wellness_user_achievements WHERE space_id = ANY($1::int[])`, [candidateSpaces]);
      await del('labs',              `DELETE FROM labs              WHERE space_id = ANY($1::int[])`, [candidateSpaces]);
      await del('schema_layouts',    `DELETE FROM schema_layouts    WHERE space_id = ANY($1::int[])`, [candidateSpaces]);
      await del('files',             `DELETE FROM files             WHERE space_id = ANY($1::int[])`, [candidateSpaces]);
      await del('dashboards',        `DELETE FROM dashboards        WHERE space_id = ANY($1::int[])`, [candidateSpaces]);
      await del('user_access_permissions_by_space', `DELETE FROM user_access_permissions WHERE space_id = ANY($1::int[])`, [candidateSpaces]);
      // conversations + space_invitations CASCADE from spaces delete.
      await del('spaces', `DELETE FROM spaces WHERE id = ANY($1::int[])`, [candidateSpaces]);
    }

    // --- Step D: users (+ CASCADE children)
    if (candidateUsers.length) {
      await del('user_access_permissions_by_user', `DELETE FROM user_access_permissions WHERE user_id = ANY($1::int[])`, [candidateUsers]);
      await del('user_settings',    `DELETE FROM user_settings    WHERE user_id = ANY($1::int[])`, [candidateUsers]);
      await del('audit_log',        `DELETE FROM audit_log        WHERE user_id = ANY($1::int[])`, [candidateUsers]);
      // conversation_participants, message_reactions, oidc_*, scheduled_messages,
      // user_widget_favorites, user_widget_history — all CASCADE on user delete.
      // conversations.created_by / messages.sender_id — SET NULL.
      await del('users', `DELETE FROM users WHERE id = ANY($1::int[])`, [candidateUsers]);
    }

    // --- Verify remaining row totals (in-transaction) ---
    const verify = await client.query(`
      SELECT
        (SELECT COUNT(*) FROM users) AS users_remaining,
        (SELECT COUNT(*) FROM spaces) AS spaces_remaining,
        (SELECT COUNT(*) FROM projects) AS projects_remaining,
        (SELECT COUNT(*) FROM universal_tables) AS tables_remaining,
        (SELECT COUNT(*) FROM table_rows) AS rows_remaining
    `);
    console.log('\n[post-delete row totals, pre-commit]');
    console.log(verify.rows[0]);

    if (ROLLBACK) {
      await client.query('ROLLBACK');
      console.log('\n[ok] --rollback — transaction rolled back. No rows persisted.');
    } else {
      await client.query('COMMIT');
      committed = true;
      console.log('\n[ok] COMMIT — cleanup persisted.');
    }

    // Write execution report
    const reportPath = path.join(snapDir, committed ? 'EXECUTION_REPORT.md' : 'ROLLBACK_REPORT.md');
    const report = [
      `# ADR-0009 Phase 3 execution report`,
      ``,
      `- Timestamp: ${new Date().toISOString()}`,
      `- Target: ${TARGET}`,
      `- Mode: ${ROLLBACK ? 'ROLLBACK (dry-transaction)' : 'COMMIT (destructive)'}`,
      `- DB: ${POSTGRES_HOST}/${process.env.POSTGRES_DB || 'godcrm_prod'}`,
      ``,
      `## Delete counts per table`,
      `| Table | rowCount |`,
      `|---|---:|`,
      ...Object.entries(counts).map(([k, v]) => `| ${k} | ${v} |`),
      ``,
      `## Post-delete totals (in-transaction)`,
      `| metric | value |`,
      `|---|---:|`,
      ...Object.entries(verify.rows[0]).map(([k, v]) => `| ${k} | ${v} |`),
      ``,
    ].join('\n');
    fs.writeFileSync(reportPath, report);
    console.log(`[ok] report: ${reportPath}`);
  } catch (err) {
    try { await client.query('ROLLBACK'); } catch {}
    console.error('\n[FATAL] destructive step failed — ROLLBACK issued:');
    console.error(err);
    throw err;
  } finally {
    client.release();
  }
}

main()
  .then(() => pool.end().then(() => process.exit(0)))
  .catch((err) => {
    console.error(err);
    pool.end().finally(() => process.exit(1));
  });