← back to Vintage Wallpaper Archive

scripts/ingest.js

263 lines

#!/usr/bin/env node
'use strict';

// Ingest vintagewallpaperarchive.com (Shopify) into the local PG db.
// Internal only. No public site. No Shopify push. Images stay third-party-owned.

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

const DATABASE_URL = process.env.DATABASE_URL
  || 'postgresql:///vintage_wallpaper_archive?host=/tmp&user=stevestudio2';
const ORIGIN = process.env.SHOPIFY_ORIGIN || 'https://vintagewallpaperarchive.com';
const PAGE_LIMIT = 250;            // Shopify max
const REQUEST_GAP_MS = 1200;       // ~0.83 req/sec — under Shopify's 2/sec limit
const USER_AGENT =
  'Mozilla/5.0 (compatible; DW-archive-onboard/0.1; +mailto:steve@designerwallcoverings.com)';
const DRY_RUN = /^(1|true|yes)$/i.test(process.env.DRY_RUN || '');

const sleep = (ms) => new Promise((r) => setTimeout(r, ms));

function stripHtml(html) {
  if (!html) return null;
  return html
    .replace(/<[^>]+>/g, ' ')
    .replace(/&nbsp;/g, ' ')
    .replace(/&amp;/g, '&')
    .replace(/\s+/g, ' ')
    .trim();
}

function parseEra(text) {
  if (!text) return { era_year: null, era_decade: null };
  // "1940s", "1923", "early 1900s"
  const decadeMatch = text.match(/\b(18|19|20)(\d)0s\b/);
  if (decadeMatch) {
    const decade = parseInt(decadeMatch[1] + decadeMatch[2] + '0', 10);
    return { era_year: decade, era_decade: decade };
  }
  const yearMatch = text.match(/\b(18\d{2}|19\d{2}|20[0-2]\d)\b/);
  if (yearMatch) {
    const year = parseInt(yearMatch[1], 10);
    return { era_year: year, era_decade: Math.floor(year / 10) * 10 };
  }
  return { era_year: null, era_decade: null };
}

function parseWidthAndRepeat(text) {
  if (!text) return { width: null, repeat: null, match: null };
  const widthMatch = text.match(/(\d+(?:\.\d+)?)\s*(?:inches|inch|in\.|")\s*(?:wide|width)/i)
    || text.match(/pattern is\s*(\d+(?:\.\d+)?)\s*inches\s*wide/i);
  const repeatMatch = text.match(/repeat\s*is\s*(\d+(?:\.\d+)?)\s*inches/i)
    || text.match(/(\d+(?:\.\d+)?)\s*(?:inch|in\.|")\s*repeat/i);
  let match = null;
  if (/straight\s*across/i.test(text)) match = 'straight across';
  else if (/half\s*drop/i.test(text)) match = 'half drop';
  else if (/random/i.test(text) && /match/i.test(text)) match = 'random';
  return {
    width: widthMatch ? parseFloat(widthMatch[1]) : null,
    repeat: repeatMatch ? parseFloat(repeatMatch[1]) : null,
    match,
  };
}

async function fetchPage(page) {
  const url = `${ORIGIN}/products.json?limit=${PAGE_LIMIT}&page=${page}`;
  const r = await fetch(url, { headers: { 'User-Agent': USER_AGENT, accept: 'application/json' } });
  if (!r.ok) throw new Error(`HTTP ${r.status} on page ${page}`);
  const j = await r.json();
  return j.products || [];
}

async function upsertProduct(client, p, runId) {
  const bodyText = stripHtml(p.body_html);
  const era = parseEra(`${p.title || ''} ${bodyText || ''} ${(p.tags || []).join(' ')}`);
  const wr = parseWidthAndRepeat(bodyText);
  const tags = typeof p.tags === 'string'
    ? p.tags.split(',').map((s) => s.trim()).filter(Boolean)
    : (Array.isArray(p.tags) ? p.tags : []);

  await client.query(
    `INSERT INTO products (
       shopify_product_id, handle, title, vendor, product_type,
       body_html, body_text, tags, options,
       published_at, created_at_remote, updated_at_remote,
       pattern_width_inches, pattern_repeat_inches, era_year, era_decade, match_type,
       source, source_url, owned_by_steve, image_license, ok_for_commercial_use,
       raw_payload, ingest_run_id
     ) VALUES (
       $1,$2,$3,$4,$5, $6,$7,$8,$9, $10,$11,$12, $13,$14,$15,$16,$17,
       $18,$19,$20,$21,$22, $23,$24
     )
     ON CONFLICT (shopify_product_id) DO UPDATE SET
       handle = EXCLUDED.handle,
       title = EXCLUDED.title,
       vendor = EXCLUDED.vendor,
       product_type = EXCLUDED.product_type,
       body_html = EXCLUDED.body_html,
       body_text = EXCLUDED.body_text,
       tags = EXCLUDED.tags,
       options = EXCLUDED.options,
       published_at = EXCLUDED.published_at,
       updated_at_remote = EXCLUDED.updated_at_remote,
       pattern_width_inches = EXCLUDED.pattern_width_inches,
       pattern_repeat_inches = EXCLUDED.pattern_repeat_inches,
       era_year = EXCLUDED.era_year,
       era_decade = EXCLUDED.era_decade,
       match_type = EXCLUDED.match_type,
       raw_payload = EXCLUDED.raw_payload,
       ingest_run_id = EXCLUDED.ingest_run_id,
       ingested_at = NOW()`,
    [
      p.id, p.handle, p.title, p.vendor, p.product_type,
      p.body_html, bodyText, tags, JSON.stringify(p.options || []),
      p.published_at, p.created_at, p.updated_at,
      wr.width, wr.repeat, era.era_year, era.era_decade, wr.match,
      'vintage-wallpaper-archive-shopify-public',
      `${ORIGIN}/products/${p.handle}`,
      false,                                    // owned_by_steve
      'unknown_third_party',                    // image_license
      false,                                    // ok_for_commercial_use
      JSON.stringify(p),
      runId,
    ]
  );
}

async function upsertVariants(client, p) {
  for (const v of (p.variants || [])) {
    await client.query(
      `INSERT INTO variants (
         shopify_variant_id, shopify_product_id, sku, title, position,
         price, compare_at_price, available, taxable, requires_shipping,
         option1, option2, option3, grams,
         created_at_remote, updated_at_remote
       ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16)
       ON CONFLICT (shopify_variant_id) DO UPDATE SET
         sku = EXCLUDED.sku,
         title = EXCLUDED.title,
         price = EXCLUDED.price,
         compare_at_price = EXCLUDED.compare_at_price,
         available = EXCLUDED.available,
         option1 = EXCLUDED.option1,
         option2 = EXCLUDED.option2,
         option3 = EXCLUDED.option3,
         updated_at_remote = EXCLUDED.updated_at_remote,
         ingested_at = NOW()`,
      [
        v.id, p.id, v.sku, v.title, v.position,
        v.price ? parseFloat(v.price) : null,
        v.compare_at_price ? parseFloat(v.compare_at_price) : null,
        v.available, v.taxable, v.requires_shipping,
        v.option1, v.option2, v.option3, v.grams,
        v.created_at, v.updated_at,
      ]
    );
  }
}

async function upsertImages(client, p) {
  for (const img of (p.images || [])) {
    await client.query(
      `INSERT INTO images (
         shopify_image_id, shopify_product_id, position, src, alt,
         width, height, variant_ids, created_at_remote, updated_at_remote
       ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)
       ON CONFLICT (shopify_image_id) DO UPDATE SET
         position = EXCLUDED.position,
         src = EXCLUDED.src,
         alt = EXCLUDED.alt,
         width = EXCLUDED.width,
         height = EXCLUDED.height,
         variant_ids = EXCLUDED.variant_ids,
         updated_at_remote = EXCLUDED.updated_at_remote,
         ingested_at = NOW()`,
      [
        img.id, p.id, img.position, img.src, img.alt,
        img.width, img.height, img.variant_ids || [],
        img.created_at, img.updated_at,
      ]
    );
  }
}

async function main() {
  const runId = crypto.randomBytes(6).toString('hex');
  console.log(`[ingest] run_id=${runId} dry=${DRY_RUN} origin=${ORIGIN}`);

  const client = new Client({ connectionString: DATABASE_URL });
  await client.connect();

  let pagesFetched = 0;
  let productsSeen = 0;
  let productsUpserted = 0;
  let variantsUpserted = 0;
  let imagesUpserted = 0;
  let errors = 0;

  if (!DRY_RUN) {
    await client.query(
      'INSERT INTO ingest_runs (run_id, notes) VALUES ($1, $2)',
      [runId, 'vintagewallpaperarchive.com /products.json paginate']
    );
  }

  try {
    for (let page = 1; page <= 50; page++) {
      const products = await fetchPage(page);
      pagesFetched++;
      if (products.length === 0) {
        console.log(`[ingest] page ${page} empty — done`);
        break;
      }
      console.log(`[ingest] page ${page}: ${products.length} products`);
      for (const p of products) {
        productsSeen++;
        if (DRY_RUN) {
          console.log(`  - ${p.handle}  "${p.title}"  variants=${p.variants?.length || 0} images=${p.images?.length || 0}`);
          continue;
        }
        try {
          await client.query('BEGIN');
          await upsertProduct(client, p, runId);
          await upsertVariants(client, p);
          await upsertImages(client, p);
          await client.query('COMMIT');
          productsUpserted++;
          variantsUpserted += (p.variants || []).length;
          imagesUpserted += (p.images || []).length;
        } catch (err) {
          await client.query('ROLLBACK').catch(() => {});
          errors++;
          console.error(`[ingest] ERROR on ${p.handle}: ${err.message}`);
        }
      }
      await sleep(REQUEST_GAP_MS);
    }
  } finally {
    if (!DRY_RUN) {
      await client.query(
        `UPDATE ingest_runs SET
           finished_at = NOW(),
           pages_fetched = $2,
           products_seen = $3,
           products_upserted = $4,
           variants_upserted = $5,
           images_upserted = $6,
           errors = $7
         WHERE run_id = $1`,
        [runId, pagesFetched, productsSeen, productsUpserted, variantsUpserted, imagesUpserted, errors]
      );
    }
    await client.end();
  }

  console.log(`[ingest] DONE pages=${pagesFetched} seen=${productsSeen} upserted=${productsUpserted} variants=${variantsUpserted} images=${imagesUpserted} errors=${errors}`);
}

main().catch((e) => {
  console.error('[ingest] FATAL', e);
  process.exit(1);
});