`migrate-supabase-to-postgres.js`

65 lines
#!/usr/bin/env node
require('dotenv').config({ path: '/root/DW-Agents/claudette-agent/.env' });
const fs = require('fs');
const { Pool } = require('pg');

const SRC = '/root/Projects/Designer-Wallcoverings/DW-Websites/glassbeadedwallpaper/glass-bead-products.json';
const data = JSON.parse(fs.readFileSync(SRC, 'utf8'));
console.log(`loaded ${data.length} records`);

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

(async () => {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS glass_bead_products (
      id            TEXT PRIMARY KEY,
      title         TEXT,
      handle        TEXT,
      description   TEXT,
      vendor        TEXT,
      product_type  TEXT,
      tags          TEXT[],
      primary_color TEXT,
      category      TEXT,
      skus          TEXT[],
      primary_sku   TEXT,
      price         NUMERIC(10,2),
      image         TEXT,
      image_alt     TEXT,
      product_url   TEXT,
      status        TEXT,
      fire_rated    BOOLEAN,
      created_at    TIMESTAMPTZ,
      updated_at    TIMESTAMPTZ DEFAULT NOW()
    );
    CREATE INDEX IF NOT EXISTS glass_bead_products_color_idx ON glass_bead_products(primary_color);
    CREATE INDEX IF NOT EXISTS glass_bead_products_category_idx ON glass_bead_products(category);
  `);

  const cols = ['id','title','handle','description','vendor','product_type','tags','primary_color','category','skus','primary_sku','price','image','image_alt','product_url','status','fire_rated','created_at'];
  const ph = cols.map((_, i) => `$${i + 1}`).join(',');
  const updates = cols.filter(c => c !== 'id').map(c => `${c}=EXCLUDED.${c}`).join(',');
  const sql = `INSERT INTO glass_bead_products (${cols.join(',')}) VALUES (${ph})
               ON CONFLICT (id) DO UPDATE SET ${updates}, updated_at=NOW()`;

  let n = 0;
  for (const r of data) {
    const vals = [
      r.id, r.title, r.handle, r.description, r.vendor, r.productType,
      Array.isArray(r.tags) ? r.tags : null,
      r.primaryColor, r.category,
      Array.isArray(r.skus) ? r.skus : null,
      r.primarySku,
      r.price != null ? Number(r.price) : null,
      r.image, r.imageAlt, r.productUrl, r.status,
      typeof r.fireRated === 'boolean' ? r.fireRated : null,
      r.createdAt || null
    ];
    await pool.query(sql, vals);
    if (++n % 100 === 0) process.stdout.write(`. ${n}\n`);
  }
  const { rows } = await pool.query(`SELECT COUNT(*) total, COUNT(image) with_image FROM glass_bead_products`);
  console.log('done:', rows[0]);
  await pool.end();
})().catch(e => { console.error(e); process.exit(1); });