generate_identity_inserts_active_only.js

const fs = require("fs");
const csv = require("fast-csv");
const { Client } = require("pg");

const inputFile = process.argv[2] || "combined_dx_jira_matches.csv";
const outputFile = "new_identity_inserts.sql";

const DX_DB_CONNECTION =
  "postgres://uw1e3sby6trmysstv:8nq4f6zzkwcSmbVE@integrated-practice-solus.6bfe0e091eb03829ab9e.db.getdx.net/client";

const normalizePostgresURL = (url) =>
  url.startsWith("postgres://") ? url.replace("postgres://", "postgresql://") : url;

const client = new Client({
  connectionString: normalizePostgresURL(DX_DB_CONNECTION),
  ssl: { rejectUnauthorized: false },
});

async function getExistingIdentities() {
  const result = await client.query("SELECT dx_user_id, source_id FROM identities WHERE source = 'jira';");
  return new Set(result.rows.map((r) => `${r.dx_user_id}-${r.source_id}`));
}

async function getActiveDxUserIds() {
  const result = await client.query("SELECT id FROM dx_users WHERE deleted_at IS NULL;");
  return new Set(result.rows.map((r) => String(r.id)));
}

async function generateInserts() {
  await client.connect();
  const existingIdentities = await getExistingIdentities();
  const activeDxUsers = await getActiveDxUserIds();

  const newInserts = [];
  const totalRecords = [];

  fs.createReadStream(inputFile)
    .pipe(csv.parse({ headers: true }))
    .on("data", (row) => {
      const dxId = row["dx_user_id"];
      const jiraId = row["jira_user_id"];
      const key = `${dxId}-${jiraId}`;
      if (dxId && jiraId && activeDxUsers.has(dxId)) {
        totalRecords.push(key);
        if (!existingIdentities.has(key)) {
          const now = new Date().toISOString().replace("T", " ").slice(0, 19);
          const sql = `INSERT INTO identities (dx_user_id, source_id, source, row_created_at, row_updated_at) VALUES (${dxId}, ${jiraId}, 'jira', '${now}', '${now}');`;
          newInserts.push(sql);
        }
      }
    })
    .on("end", () => {
      fs.writeFileSync(outputFile, ["BEGIN;", ...newInserts, "COMMIT;"].join("\n"));
      console.log("✅ Insert script written to", outputFile);
      console.log(`📊 Total records reviewed: ${totalRecords.length}`);
      console.log(`➕ New inserts generated: ${newInserts.length}`);
      client.end();
    });
}

generateInserts();