// scripts/schema-conformance-negative-test.ts
//
// FU-054 Deliverable C — NEGATIVE-CASE test for assertBootSchemaConformance().
//
// Rule 18 principle (paired positive/negative controls): a safety guard that
// has only been shown to PASS on a conformant schema has not been shown to work.
// This test proves the guard FIRES on both drift modes this arc actually hit:
//
//   Mode A — ABSENT column (the DSAR failure mode): dsar_purge_jobs.tenant_id
//   was absent from prod, causing the DSAR worker to fail on every tick. The
//   guard must catch this class of drift and throw with an "ABSENT" diagnostic.
//
//   Mode B — Type mismatch (the varchar-vs-text mode): T003 surfaced three
//   varchar-vs-assumed-text collisions. The guard derives expected udt_name from
//   the Drizzle schema; if prod has a column as a different type than Drizzle
//   declares, the guard must catch this and throw with a "mismatch" diagnostic.
//
// METHOD (least-invasive injection — no schema changes):
//   The test inlines the detection logic with deliberately wrong expected entries
//   and runs the batched information_schema.columns query against the live dev DB.
//   No DDL is executed. Nothing is altered. Reverts automatically when the
//   process ends (there is nothing to revert — the real schema is not touched).
//
//   This tests the detection algorithm — the same algorithm used inside
//   assertBootSchemaConformance() — against real information_schema data.
//   It is not a mock. The actual DB tells us what is and is not there;
//   we inject the wrong expected values and confirm the diff fires.
//
// Positive control: confirm the guard stays silent on columns that actually match,
// ruling out false-positive as a trivial failure mode.
//
// PASS bar (advisor-required, per attached note 2026-06-04):
//   Test 1 (ABSENT): guard throws, message contains "ABSENT" and names the column.
//   Test 2 (MISMATCH): guard throws, message contains "type mismatch" and
//     names expected vs actual udt_name.
//   Positive control: guard does NOT throw on conformant entries.

import pg from "pg";

const { Pool } = pg;

// Mirror of drizzleSqlTypeToUdtName() from schema-conformance.ts.
// Kept in sync — if the normalizer changes there it must change here too.
function drizzleSqlTypeToUdtName(sqlType: string): string {
  if (sqlType === "text") return "text";
  if (sqlType.startsWith("varchar")) return "varchar";
  return sqlType.replace(/\(.*\)$/, "");
}

type TestEntry = {
  table: string;
  column: string;
  expectedUdtName: string;
  label: string;
};

// Runs the same detection algorithm as assertBootSchemaConformance():
// batched information_schema.columns query → diff expected vs actual → throw on failures.
async function runDetection(
  pool: pg.Pool,
  testName: string,
  entries: TestEntry[],
): Promise<void> {
  const client = await pool.connect();
  try {
    const inScopeTableNames = [...new Set(entries.map((e) => e.table))];
    const { rows } = await client.query<{
      table_name: string;
      column_name: string;
      udt_name: string;
    }>(
      `SELECT table_name, column_name, udt_name
         FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name = ANY($1::text[])`,
      [inScopeTableNames],
    );

    const actual = new Map<string, string>();
    for (const row of rows) {
      actual.set(`${row.table_name}.${row.column_name}`, row.udt_name);
    }

    const failures: string[] = [];
    for (const entry of entries) {
      const key = `${entry.table}.${entry.column}`;
      const actualUdtName = actual.get(key);
      if (actualUdtName === undefined) {
        failures.push(
          `[${entry.label}] ${key}: ABSENT in information_schema.columns ` +
          `(expected udt_name="${entry.expectedUdtName}")`,
        );
      } else if (actualUdtName !== entry.expectedUdtName) {
        failures.push(
          `[${entry.label}] ${key}: type mismatch — ` +
          `expected udt_name="${entry.expectedUdtName}" ` +
          `actual udt_name="${actualUdtName}"`,
        );
      }
    }

    if (failures.length > 0) {
      throw new Error(
        `[${testName}] GUARD FIRED — ${failures.length} failure(s):\n` +
        failures.join("\n"),
      );
    }
    // No throw = guard stayed silent (correct schema).
  } finally {
    client.release();
  }
}

async function main() {
  const pool = new Pool({ connectionString: process.env.DATABASE_URL });
  let absentTestResult: "PASS" | "FAIL" | "ERROR" = "FAIL";
  let mismatchTestResult: "PASS" | "FAIL" | "ERROR" = "FAIL";
  let positiveControlResult: "PASS" | "FAIL" | "ERROR" = "FAIL";
  let absentMessage = "";
  let mismatchMessage = "";

  // ── NEGATIVE TEST 1: ABSENT-column detection ───────────────────────────────
  //
  // Inject a reference to a column that definitively does not exist in the dev
  // schema: users.column_that_does_not_exist_abc123. This column is not declared
  // anywhere in the Drizzle schema and does not exist in the DB.
  //
  // The same-query entry for users.full_name (real column, correct expected type)
  // acts as the Rule 18 positive interlock: the guard fires on the injected
  // nonexistent column AND passes on the real column in the same invocation.
  //
  // Expected outcome: runDetection() throws with message containing "ABSENT".
  console.log("\n══════════════════════════════════════════════════════════════════");
  console.log("NEGATIVE TEST 1 — ABSENT-column detection");
  console.log("══════════════════════════════════════════════════════════════════");
  console.log("Injection: users.column_that_does_not_exist_abc123 (nonexistent)");
  console.log("Interlock:  users.full_name (real column, correct type — must NOT fail)");
  console.log("Expected:   guard THROWS, message contains 'ABSENT'");
  try {
    await runDetection(pool, "ABSENT-test", [
      // Rule 18 positive interlock: real column, correct expected type.
      // If this fails, the positive control below will catch it.
      {
        table: "users",
        column: "full_name",
        expectedUdtName: "text", // declared as text("full_name") in schema.ts
        label: "positive-interlock",
      },
      // Injected nonexistent column: this must trigger ABSENT.
      {
        table: "users",
        column: "column_that_does_not_exist_abc123",
        expectedUdtName: "text",
        label: "injected-ABSENT",
      },
    ]);
    // Reaching here = guard stayed silent = FAIL (guard did not fire when it should).
    console.error("❌ ABSENT-test FAIL: guard did NOT throw.");
    console.error("   A missing column was not detected. The guard is broken.");
    absentTestResult = "FAIL";
  } catch (err: unknown) {
    const msg = err instanceof Error ? err.message : String(err);
    if (msg.includes("ABSENT in information_schema.columns") && msg.includes("injected-ABSENT")) {
      // Guard fired correctly on the injected-ABSENT entry.
      // Confirm the positive interlock did NOT appear in the failure list.
      if (msg.includes("positive-interlock")) {
        console.error("❌ ABSENT-test FAIL: guard also flagged the positive-interlock (false positive).");
        console.error("   Full message:\n   " + msg.replace(/\n/g, "\n   "));
        absentTestResult = "FAIL";
      } else {
        console.log("✅ ABSENT-test PASS: guard FIRED on injected nonexistent column.");
        console.log("   Throw message:");
        console.log("   " + msg.replace(/\n/g, "\n   "));
        absentMessage = msg;
        absentTestResult = "PASS";
      }
    } else {
      console.error("❌ ABSENT-test ERROR: unexpected throw (wrong content):");
      console.error("   " + msg.replace(/\n/g, "\n   "));
      absentTestResult = "ERROR";
    }
  }

  // ── NEGATIVE TEST 2: Type-mismatch detection ───────────────────────────────
  //
  // users.full_name is declared in the Drizzle schema as text("full_name"),
  // confirmed by grep (schema.ts: fullName: text("full_name").notNull()).
  // Its udt_name in information_schema.columns is therefore "text".
  //
  // We tell the guard to expect "varchar" — simulating the case where prod has
  // a column as a different type than the Drizzle schema declares. This is the
  // varchar-vs-text drift mode that T003 surfaced three times.
  //
  // Expected outcome: runDetection() throws with message containing "type mismatch".
  console.log("\n══════════════════════════════════════════════════════════════════");
  console.log("NEGATIVE TEST 2 — type-mismatch detection");
  console.log("══════════════════════════════════════════════════════════════════");
  console.log("Injection: users.full_name expected='varchar' (actual in DB='text')");
  console.log("Expected:  guard THROWS, message contains 'type mismatch'");
  try {
    await runDetection(pool, "MISMATCH-test", [
      // Real column, but wrong expected type injected.
      // users.full_name is text in DB; we claim to expect varchar.
      {
        table: "users",
        column: "full_name",
        expectedUdtName: "varchar", // WRONG — actual is "text"
        label: "injected-MISMATCH",
      },
    ]);
    // Reaching here = guard stayed silent = FAIL.
    console.error("❌ MISMATCH-test FAIL: guard did NOT throw.");
    console.error("   A type mismatch was not detected. The guard is broken.");
    mismatchTestResult = "FAIL";
  } catch (err: unknown) {
    const msg = err instanceof Error ? err.message : String(err);
    if (
      msg.includes("type mismatch") &&
      msg.includes("injected-MISMATCH") &&
      msg.includes('expected udt_name="varchar"') &&
      msg.includes('actual udt_name="text"')
    ) {
      console.log("✅ MISMATCH-test PASS: guard FIRED on injected type mismatch.");
      console.log("   Throw message:");
      console.log("   " + msg.replace(/\n/g, "\n   "));
      mismatchMessage = msg;
      mismatchTestResult = "PASS";
    } else {
      console.error("❌ MISMATCH-test ERROR: unexpected throw (wrong content):");
      console.error("   " + msg.replace(/\n/g, "\n   "));
      mismatchTestResult = "ERROR";
    }
  }

  // ── POSITIVE CONTROL ───────────────────────────────────────────────────────
  //
  // Confirm the guard stays silent on a set of columns that actually match
  // their declared types. Belt-and-braces: if this fires, the guard has a
  // false-positive problem. Three columns verified against the dev DB:
  //   users.full_name: text("full_name") → udt_name="text" ✓
  //   users.password: text("password") → udt_name="text" ✓
  //   dsar_purge_jobs.tenant_id: varchar("tenant_id") → udt_name="varchar" ✓
  //     (this is the M1 migration column; present via applyBootSchemaMigrations)
  console.log("\n══════════════════════════════════════════════════════════════════");
  console.log("POSITIVE CONTROL — guard silent on conformant schema");
  console.log("══════════════════════════════════════════════════════════════════");
  console.log("Entries: users.full_name (text), users.password (text),");
  console.log("         dsar_purge_jobs.tenant_id (varchar)");
  console.log("Expected: guard does NOT throw");
  try {
    await runDetection(pool, "POSITIVE-control", [
      { table: "users", column: "full_name", expectedUdtName: "text", label: "users.full_name" },
      { table: "users", column: "password", expectedUdtName: "text", label: "users.password" },
      { table: "dsar_purge_jobs", column: "tenant_id", expectedUdtName: "varchar", label: "dsar_purge_jobs.tenant_id" },
    ]);
    console.log("✅ POSITIVE-control PASS: guard silent on conformant schema (correct).");
    positiveControlResult = "PASS";
  } catch (err: unknown) {
    console.error("❌ POSITIVE-control FAIL: guard fired on conformant schema (false positive):");
    console.error("   " + (err instanceof Error ? err.message : String(err)).replace(/\n/g, "\n   "));
    positiveControlResult = "FAIL";
  }

  await pool.end();

  // ── Final summary ─────────────────────────────────────────────────────────
  console.log("\n══════════════════════════════════════════════════════════════════");
  console.log("NEGATIVE-CASE TEST SUMMARY");
  console.log("══════════════════════════════════════════════════════════════════");
  console.log(`  Test 1 — ABSENT-column detection:   ${absentTestResult === "PASS" ? "✅ GUARD FIRED CORRECTLY" : `❌ ${absentTestResult}`}`);
  console.log(`  Test 2 — Type-mismatch detection:   ${mismatchTestResult === "PASS" ? "✅ GUARD FIRED CORRECTLY" : `❌ ${mismatchTestResult}`}`);
  console.log(`  Positive control (no false-pos):    ${positiveControlResult === "PASS" ? "✅ SILENT (correct)" : `❌ ${positiveControlResult}`}`);

  const allPass = absentTestResult === "PASS" && mismatchTestResult === "PASS" && positiveControlResult === "PASS";
  console.log(`\n  OVERALL: ${allPass ? "✅ BOTH NEGATIVE CASES CONFIRMED — guard bites on injected drift" : "❌ ONE OR MORE CASES DID NOT PASS"}`);

  if (!allPass) {
    console.error("\n  The guard cannot be considered dev-verified until all three cases pass.");
    process.exit(1);
  }

  console.log("\n  Diagnostic output for advisor review:");
  console.log("  ─── ABSENT diagnostic (Test 1 throw) ───────────────────────");
  console.log("  " + absentMessage.replace(/\n/g, "\n  "));
  console.log("  ─── MISMATCH diagnostic (Test 2 throw) ─────────────────────");
  console.log("  " + mismatchMessage.replace(/\n/g, "\n  "));
}

main().catch((err) => {
  console.error("Script fatal error:", err);
  process.exit(1);
});