`scripts/dedup-products.js`

98 lines
#!/usr/bin/env node
// TK-10112 — De-duplicate re-ingest artifacts. The same real product lands more
// than once when a feed lists it under sibling storefronts (Wayfair / Joss &
// Main / Perigold all share the wfcdn asset) or when an ingest re-ran before the
// (network, external_id) key existed. Two rows are the SAME product iff either:
//   (A) they share the exact same non-empty image_url, OR
//   (B) they share lower(title) AND advertiser AND the same non-empty image_url.
// For each duplicate group we KEEP the lowest id (earliest ingested) and DELETE
// the rest.
//
// Care taken NOT to over-delete across genuinely different products:
//   - image_url match requires a NON-EMPTY, identical URL (a shared CDN asset is
//     the same product photo — a reliable identity signal here).
//   - The title+advertiser rule is IMAGE-GUARDED. This catalog is full of
//     generic Wayfair titles ("Upholstered Headboard" has 68 DISTINCT products,
//     62 distinct images, 68 distinct external_ids) — collapsing purely on
//     lower(title)+advertiser would delete ~1,600 real, different products. A
//     genuine re-ingest artifact under the same title also carries the same
//     image_url, so we require the image to match too. (Verified: 0 duplicate
//     (network, external_id) pairs exist — the unique constraint holds — so the
//     only real dups are same-photo rows, which image_url identity catches.)
// clicks.product_id is ON DELETE SET NULL and rooms.wall_paint_id likewise, so
// deleting a dup never orphan-breaks a FK.
//
// Usage: node scripts/dedup-products.js [--dry-run]

try { require('dotenv').config(); } catch (_) { /* env from shell */ }
const db = require('../lib/db');

const DRY_RUN = process.argv.includes('--dry-run');

// --- union-find over the two identity relations ---------------------------
// Both rules are equivalence relations; a product can be reachable through a
// CHAIN (row X shares an image with Y, Y shares title+advertiser with Z). We
// union all such rows into one component and keep the global MIN id per
// component, deleting every other member. This is transitively correct and can
// never leave a survivor-less identity or delete two unrelated products.
class UF {
  constructor() { this.p = new Map(); }
  find(x) {
    if (!this.p.has(x)) { this.p.set(x, x); return x; }
    let r = x;
    while (this.p.get(r) !== r) r = this.p.get(r);
    while (this.p.get(x) !== r) { const n = this.p.get(x); this.p.set(x, r); x = n; }
    return r;
  }
  union(a, b) { const ra = this.find(a), rb = this.find(b); if (ra !== rb) this.p.set(Math.max(ra, rb), Math.min(ra, rb)); }
}

async function main() {
  await db.query('SELECT 1');

  const uf = new UF();

  // Rule A: same non-empty image_url → union all ids sharing that URL.
  const { rows: aGroups } = await db.query(`
    SELECT array_agg(id ORDER BY id) AS ids
    FROM products
    WHERE image_url IS NOT NULL AND image_url <> ''
    GROUP BY image_url HAVING count(*) > 1
  `);
  let aExtra = 0;
  for (const g of aGroups) { const ids = g.ids.map(Number); aExtra += ids.length - 1; for (let i = 1; i < ids.length; i++) uf.union(ids[0], ids[i]); }

  // Rule B: same lower(title)+advertiser AND same non-empty image_url → union.
  // Image-guarded so distinct generic-titled products are never collapsed.
  const { rows: bGroups } = await db.query(`
    SELECT array_agg(id ORDER BY id) AS ids
    FROM products
    WHERE title IS NOT NULL AND title <> ''
      AND image_url IS NOT NULL AND image_url <> ''
    GROUP BY lower(title), advertiser, image_url HAVING count(*) > 1
  `);
  let bExtra = 0;
  for (const g of bGroups) { const ids = g.ids.map(Number); bExtra += ids.length - 1; for (let i = 1; i < ids.length; i++) uf.union(ids[0], ids[i]); }

  // Every id that isn't its own component root is a duplicate → delete it.
  const delIds = [];
  for (const id of uf.p.keys()) { if (uf.find(id) !== id) delIds.push(id); }

  console.log(`[dedup] image_url dup rows (extra): ${aExtra}, title+advertiser dup rows (extra): ${bExtra}`);
  console.log(`[dedup] total distinct rows to remove (chained): ${delIds.length}`);

  if (!delIds.length) { console.log('[dedup] nothing to remove.'); await db.pool.end(); return; }

  if (DRY_RUN) {
    console.log('[dedup] DRY-RUN — no rows deleted. Sample ids:', delIds.slice(0, 20).join(', '));
  } else {
    const before = (await db.query('SELECT count(*)::int n FROM products')).rows[0].n;
    const res = await db.query('DELETE FROM products WHERE id = ANY($1::bigint[])', [delIds]);
    const after = (await db.query('SELECT count(*)::int n FROM products')).rows[0].n;
    console.log(`[dedup] deleted ${res.rowCount} rows. products: ${before} -> ${after}`);
  }
  await db.pool.end();
}

main().catch((e) => { console.error(e); process.exit(1); });