#!/usr/bin/env npx tsx
/**
 * T004b — Stale-overwrite guard dev test
 * Proves the Phase-1 conditional UPDATE (IS NOT DISTINCT FROM) correctly catches
 * a row that was modified between Phase 0 (snapshot) and Phase 1 (UPDATE).
 *
 * This is Finding-3 (lost-update protection) empirically exercised.
 *
 * Test table: vendor_risk_register (contact_email — single plaintext PII col)
 * The test inserts a sentinel row, takes a snapshot value, modifies the row to
 * simulate a concurrent app write, then runs the exact conditional UPDATE the
 * migration script uses — verifying rowCount === 0 (abort fires).
 */

import pg from "pg";
import {
  encryptPii,
  type PiiContext,
} from "../server/lib/pii-encryption";

const PLATFORM_CTX: PiiContext = { type: "platform" };
const SENTINEL_EMAIL = "stale-overwrite-test@t004b-test.invalid";
const CONCURRENT_EMAIL = "stale-overwrite-CHANGED@t004b-test.invalid";

async function main(): Promise<void> {
  if (!process.env.DATABASE_URL) throw new Error("DATABASE_URL not set");

  const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

  console.log("[t004b-stale] ══════════════════════════════════════════════");
  console.log("[t004b-stale] Stale-overwrite guard test — Finding-3 proof");
  console.log("[t004b-stale] ══════════════════════════════════════════════");

  let rowId: string | null = null;

  try {
    // ─── Step 1: Insert sentinel row (plaintext contact_email) ─────────────
    // vendor_risk_register required NOT NULL without defaults: vendor_key, vendor_name, category, contact_email
    // All other NOT NULL cols have DB defaults (risk_score=0, risk_tier='LOW', certifications_json='[]', notes='', active=1, added_at=now())
    const insertRes = await pool.query(
      `INSERT INTO vendor_risk_register
         (vendor_key, vendor_name, category, contact_email)
       VALUES ($1, $2, $3, $4)
       RETURNING id`,
      [
        "t004b-stale-test-vendor-key",
        "T004b-StaleTest-Vendor",
        "test",
        SENTINEL_EMAIL,
      ]
    );
    rowId = insertRes.rows[0].id as string;
    console.log(`[t004b-stale] Step 1 PASS — inserted sentinel row id=${rowId}`);
    console.log(`[t004b-stale]             contact_email=[SENTINEL — not logged]`);

    // ─── Step 2: Phase-0 snapshot (read plaintext value) ───────────────────
    const snapRes = await pool.query(
      `SELECT contact_email FROM vendor_risk_register WHERE id = $1`,
      [rowId]
    );
    const snapshotValue: string = snapRes.rows[0].contact_email as string;
    // Verify snapshot matches what we inserted (sanity check)
    if (snapshotValue !== SENTINEL_EMAIL) {
      throw new Error(`Step 2 FAIL — snapshot value does not match inserted value`);
    }
    console.log(`[t004b-stale] Step 2 PASS — Phase-0 snapshot captured (value verified, not logged)`);

    // ─── Step 3: Simulate concurrent app write (change the row) ────────────
    // This is what a concurrent application write between Phase 0 and Phase 1 looks like.
    await pool.query(
      `UPDATE vendor_risk_register SET contact_email = $1 WHERE id = $2`,
      [CONCURRENT_EMAIL, rowId]
    );
    console.log(`[t004b-stale] Step 3 PASS — row modified (concurrent write simulated)`);

    // ─── Step 4: Run Phase-1 conditional UPDATE using SNAPSHOT value in WHERE
    // This is the exact SQL the migration script would issue.
    // Snapshot value is now STALE (row has a different value).
    // Expected: rowCount === 0 (WHERE predicate does not match — stale-overwrite caught).
    const ciphertext = encryptPii(snapshotValue, PLATFORM_CTX);
    const updateRes = await pool.query(
      `UPDATE vendor_risk_register
         SET contact_email = $2
       WHERE id = $1
         AND contact_email IS NOT DISTINCT FROM $3`,
      [rowId, ciphertext, snapshotValue]
    );

    const rowCount = updateRes.rowCount ?? 0;
    if (rowCount === 0) {
      console.log(`[t004b-stale] Step 4 PASS — rowCount=0: stale-overwrite guard FIRED correctly`);
      console.log(`[t004b-stale]             (WHERE predicate did not match changed row — abort would trigger)`);
    } else {
      throw new Error(
        `Step 4 FAIL — rowCount=${rowCount}: stale-overwrite guard DID NOT FIRE (expected 0). ` +
        `The conditional UPDATE overwrote the concurrent change — lost-update protection is BROKEN.`
      );
    }

    // ─── Step 5: Verify the concurrent value is still in place (not overwritten)
    const verifyRes = await pool.query(
      `SELECT contact_email FROM vendor_risk_register WHERE id = $1`,
      [rowId]
    );
    const currentValue: string = verifyRes.rows[0].contact_email as string;
    if (currentValue !== CONCURRENT_EMAIL) {
      throw new Error(
        `Step 5 FAIL — row value after abort does not equal the concurrent value. ` +
        `Expected CONCURRENT_EMAIL, got something else. This means the failed UPDATE still modified the row.`
      );
    }
    console.log(`[t004b-stale] Step 5 PASS — row retains concurrent value (not overwritten by failed migration UPDATE)`);

    console.log(`[t004b-stale] ══════════════════════════════════════════════`);
    console.log(`[t004b-stale] RESULT: PASS — stale-overwrite guard is empirically proven`);
    console.log(`[t004b-stale]   Finding-3 fix verified: concurrent write caught, row not overwritten`);
    console.log(`[t004b-stale] ══════════════════════════════════════════════`);

  } finally {
    // ─── Cleanup: delete sentinel row ──────────────────────────────────────
    if (rowId) {
      await pool.query(`DELETE FROM vendor_risk_register WHERE id = $1`, [rowId]);
      console.log(`[t004b-stale] Cleanup DONE — sentinel row deleted (id=${rowId})`);
    }
    await pool.end();
  }
}

main().catch((err: Error) => {
  console.error(`[t004b-stale] FATAL: ${err.message}`);
  process.exit(1);
});