#!/usr/bin/env npx tsx
/**
 * T004b — AC-7 nullable-NULL conditional UPDATE test
 * Proves that the Phase-1 conditional UPDATE predicate
 *   col IS NOT DISTINCT FROM $snap_val
 * correctly handles a nullable PII column that IS NULL — i.e., NULL IS NOT DISTINCT FROM NULL
 * evaluates to TRUE, so a row with a NULL PII column is correctly matched and migrated
 * (not false-aborted with rowCount=0).
 *
 * Context: ALL Phase-1 PII columns are NOT NULL in the current schema (confirmed by
 * information_schema.columns query 2026-06-06). The IS NOT DISTINCT FROM predicate is a
 * defensive/future-proof choice. This test exercises the SQL predicate directly using a
 * temporary table with a nullable column to prove the behaviour is correct and the
 * predicate cannot false-abort on a NULL value.
 *
 * Two cases tested:
 *   AC-7a: NULL snapshot value → IS NOT DISTINCT FROM NULL → rowCount=1 (correct match, migrates)
 *   AC-7b: non-NULL snapshot value with IS NOT DISTINCT FROM → rowCount=1 (baseline, no regression)
 *   AC-7c: NULL snapshot vs non-NULL DB value → rowCount=0 (correctly does NOT match — not a false-pass)
 */

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

const PLATFORM_CTX: PiiContext = { type: "platform" };

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-ac7] ══════════════════════════════════════════════");
  console.log("[t004b-ac7] AC-7 nullable-NULL conditional UPDATE test");
  console.log("[t004b-ac7] ══════════════════════════════════════════════");

  try {
    // Create a temporary table with a nullable text column to test the predicate
    await pool.query(`
      CREATE TEMP TABLE t004b_ac7_test (
        id   text PRIMARY KEY,
        pii  text   -- nullable — the AC-7 case
      )
    `);
    console.log(`[t004b-ac7] Temp table created`);

    // ─── AC-7a: NULL snapshot value — should match (rowCount=1) ───────────
    await pool.query(`INSERT INTO t004b_ac7_test VALUES ('row-null', NULL)`);

    // Snapshot value is NULL (as it would be in the migration for a NULL PII column)
    const snapValNull: string | null = null;
    // When PII column is NULL, migration skips encryption (null pass-through)
    const ciphertextNull: string | null = null;

    const updNull = await pool.query(
      `UPDATE t004b_ac7_test SET pii = $1 WHERE id = $2 AND pii IS NOT DISTINCT FROM $3`,
      [ciphertextNull, "row-null", snapValNull]
    );

    if ((updNull.rowCount ?? 0) === 1) {
      console.log(`[t004b-ac7] AC-7a PASS — NULL snap IS NOT DISTINCT FROM NULL: rowCount=1 (correct match, no false-abort)`);
    } else {
      throw new Error(
        `AC-7a FAIL — rowCount=${updNull.rowCount}: NULL IS NOT DISTINCT FROM NULL should yield rowCount=1. ` +
        `Predicate is false-aborting on NULL PII column — IS NOT DISTINCT FROM is broken.`
      );
    }

    // ─── AC-7b: non-NULL snapshot value — baseline correct match ──────────
    await pool.query(`INSERT INTO t004b_ac7_test VALUES ('row-nonnull', 'plaintext-value')`);

    const snapValNonNull = "plaintext-value";
    const ciphertextNonNull = encryptPii(snapValNonNull, PLATFORM_CTX);

    const updNonNull = await pool.query(
      `UPDATE t004b_ac7_test SET pii = $1 WHERE id = $2 AND pii IS NOT DISTINCT FROM $3`,
      [ciphertextNonNull, "row-nonnull", snapValNonNull]
    );

    if ((updNonNull.rowCount ?? 0) === 1) {
      // Verify the ciphertext decrypts correctly
      const readBack = await pool.query(
        `SELECT pii FROM t004b_ac7_test WHERE id = 'row-nonnull'`
      );
      const dbVal = readBack.rows[0].pii as string;
      const decrypted = decryptPii(dbVal, PLATFORM_CTX);
      if (decrypted !== snapValNonNull) {
        throw new Error(`AC-7b FAIL — decrypted value !== snapshot value`);
      }
      console.log(`[t004b-ac7] AC-7b PASS — non-NULL snap IS NOT DISTINCT FROM non-NULL: rowCount=1, decrypt verified`);
    } else {
      throw new Error(`AC-7b FAIL — rowCount=${updNonNull.rowCount}: non-NULL baseline failed`);
    }

    // ─── AC-7c: NULL snapshot vs non-NULL DB value — should NOT match ──────
    // This proves IS NOT DISTINCT FROM doesn't collapse distinctions: NULL ≠ non-NULL.
    // (Prevents a false-positive where we might accidentally overwrite a non-NULL value
    //  because a stale NULL snapshot matched a non-NULL DB value.)
    await pool.query(`INSERT INTO t004b_ac7_test VALUES ('row-mismatch', 'non-null-value')`);

    const updMismatch = await pool.query(
      `UPDATE t004b_ac7_test SET pii = $1 WHERE id = $2 AND pii IS NOT DISTINCT FROM $3`,
      [null, "row-mismatch", null] // snapshot says NULL, DB has non-NULL → must not match
    );

    if ((updMismatch.rowCount ?? 0) === 0) {
      console.log(`[t004b-ac7] AC-7c PASS — NULL snap vs non-NULL DB: rowCount=0 (correctly does NOT match — stale guard works for NULL/non-NULL mismatch)`);
    } else {
      throw new Error(
        `AC-7c FAIL — rowCount=${updMismatch.rowCount}: NULL snap matched non-NULL DB value. ` +
        `IS NOT DISTINCT FROM is incorrectly treating NULL as matching non-NULL.`
      );
    }

    console.log(`[t004b-ac7] ══════════════════════════════════════════════`);
    console.log(`[t004b-ac7] RESULT: 3/3 PASS — IS NOT DISTINCT FROM predicate is correct for all NULL cases`);
    console.log(`[t004b-ac7]   AC-7a: NULL snap → matches NULL DB column (no false-abort) ✓`);
    console.log(`[t004b-ac7]   AC-7b: non-NULL snap → matches non-NULL DB column, decrypt verified ✓`);
    console.log(`[t004b-ac7]   AC-7c: NULL snap vs non-NULL DB → does NOT match (stale guard intact) ✓`);
    console.log(`[t004b-ac7] Schema note: all Phase-1 PII columns confirmed NOT NULL in DB`);
    console.log(`[t004b-ac7]   (information_schema.columns, 2026-06-06). AC-7a is a defensive`);
    console.log(`[t004b-ac7]   code path for future schema changes — predicate proven correct.`);
    console.log(`[t004b-ac7] ══════════════════════════════════════════════`);

  } finally {
    // Temp tables are session-scoped and dropped automatically on connection close,
    // but explicit DROP is good practice.
    await pool.query(`DROP TABLE IF EXISTS t004b_ac7_test`).catch(() => {});
    await pool.end();
  }
}

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