custom_data_insert_example.js

'use strict';

/**
 * upsert_custom_table.js
 *
 * Connects to Postgres and:
 * 1) Ensures schema "custom" exists
 * 2) Ensures table "custom.dx_user_profiles" exists (creates if not)
 * 3) Inserts (upserts) a small set of example rows
 *
 * Connection:
 * - Uses DATABASE_URL from env (preferred)
 * - Falls back to HARDCODED_DB_URL if set
 * - Normalizes postgres:// → postgresql://
 */

const { Pool } = require('pg');
require('dotenv').config();

// Optional hardcoded connection string
// Example: 'postgresql://user:pass@host:5432/dbname'
const HARDCODED_DB_URL = ''; // set if needed

// Normalize postgres:// → postgresql://
const normalizePostgresURL = (url) =>
  url && url.startsWith('postgres://') ? url.replace('postgres://', 'postgresql://') : url;

// Choose connection string: env > hardcoded
let dbUrl = process.env.DATABASE_URL
  ? normalizePostgresURL(process.env.DATABASE_URL)
  : normalizePostgresURL(HARDCODED_DB_URL);

if (!dbUrl) {
  console.error('Missing DATABASE_URL environment variable or HARDCODED_DB_URL');
  process.exit(1);
}

// Database connection pool
const pool = new Pool({
  connectionString: dbUrl,
  ssl: { rejectUnauthorized: false },
  application_name: 'dx-custom-table-upsert-example',
});

// Table definition (5–7 columns; using 7 here)
const SCHEMA_NAME = 'custom';
const TABLE_NAME = 'dx_user_profiles';
const FULL_TABLE = `${SCHEMA_NAME}.${TABLE_NAME}`;

// Existence check (information_schema)
const TABLE_EXISTS_SQL = `
SELECT 1
FROM information_schema.tables
WHERE table_schema = $1
  AND table_name = $2
LIMIT 1;
`;

// Create schema + table if missing
// Notes:
// - email is unique so we can upsert on it
// - timestamps are useful and common in DX-style custom tables
const CREATE_SCHEMA_SQL = `CREATE SCHEMA IF NOT EXISTS ${SCHEMA_NAME};`;

const CREATE_TABLE_SQL = `
CREATE TABLE IF NOT EXISTS ${FULL_TABLE} (
  id               BIGSERIAL PRIMARY KEY,
  email            TEXT NOT NULL UNIQUE,
  full_name        TEXT,
  team_name        TEXT,
  start_date       DATE,
  is_active        BOOLEAN NOT NULL DEFAULT true,
  tags             TEXT,
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Optional: help lookups by team
CREATE INDEX IF NOT EXISTS ${TABLE_NAME}_team_name_idx
  ON ${FULL_TABLE} (team_name);

-- Optional: help filtering by start_date
CREATE INDEX IF NOT EXISTS ${TABLE_NAME}_start_date_idx
  ON ${FULL_TABLE} (start_date);
`;

// Upsert SQL
const UPSERT_SQL = `
INSERT INTO ${FULL_TABLE} (
  email,
  full_name,
  team_name,
  start_date,
  is_active,
  tags,
  updated_at
) VALUES (
  $1, $2, $3, $4, $5, $6, NOW()
)
ON CONFLICT (email) DO UPDATE SET
  full_name  = EXCLUDED.full_name,
  team_name  = EXCLUDED.team_name,
  start_date = EXCLUDED.start_date,
  is_active  = EXCLUDED.is_active,
  tags       = EXCLUDED.tags,
  updated_at = NOW();
`;

// Example rows to insert (replace with your real dataset later)
const EXAMPLE_ROWS = [
  {
    email: 'alex@example.com',
    full_name: 'Alex Rivera',
    team_name: 'Platform',
    start_date: '2024-01-15',
    is_active: true,
    tags: 'location: remote, level: senior',
  },
  {
    email: 'bri@example.com',
    full_name: 'Bri Chen',
    team_name: 'Data',
    start_date: '2023-09-01',
    is_active: true,
    tags: 'location: nyc, focus: analytics',
  },
  {
    email: 'casey@example.com',
    full_name: 'Casey Patel',
    team_name: 'Infra',
    start_date: '2022-05-20',
    is_active: false,
    tags: 'location: sf, note: alumni',
  },
];

async function ensureTableExists(client) {
  await client.query(CREATE_SCHEMA_SQL);

  const existsRes = await client.query(TABLE_EXISTS_SQL, [SCHEMA_NAME, TABLE_NAME]);
  const exists = existsRes.rowCount > 0;

  if (!exists) {
    console.log(`Table ${FULL_TABLE} does not exist. Creating...`);
  } else {
    console.log(`Table ${FULL_TABLE} exists. Continuing...`);
  }

  await client.query(CREATE_TABLE_SQL);
}

async function upsertRows(client, rows) {
  let upserted = 0;

  for (const r of rows) {
    const params = [
      r.email,
      r.full_name ?? null,
      r.team_name ?? null,
      r.start_date ?? null,
      typeof r.is_active === 'boolean' ? r.is_active : true,
      r.tags ?? null,
    ];

    await client.query(UPSERT_SQL, params);
    upserted += 1;

    if (upserted % 500 === 0) {
      console.log(`Upserted ${upserted} rows...`);
    }
  }

  return upserted;
}

async function main() {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    await ensureTableExists(client);

    const count = await upsertRows(client, EXAMPLE_ROWS);

    await client.query('COMMIT');
    console.log(`Done. Upserted ${count} rows into ${FULL_TABLE}.`);
  } catch (err) {
    try {
      await client.query('ROLLBACK');
    } catch (rollbackErr) {
      console.error('Rollback failed:', rollbackErr);
    }
    console.error('Script failed:', err);
    process.exitCode = 1;
  } finally {
    client.release();
    await pool.end();
  }
}

main();