`push-shopify.js`

219 lines
#!/usr/bin/env node
/**
 * Atomic 50 Ceilings — Shopify push (EXECUTE, Steve-approved 2026-07-13)
 *
 * 1. Create the 56 to-create rows (exclude LBI-BOYD resource + the 2 already-live)
 *    as DRAFT, quote-only / sample-only:
 *      - ONE variant: {DW_SKU}-Sample, $4.25, no inventory tracking, requires_shipping
 *      - NO sellable retail variant, NO product price (quote-only line)
 *      - product_type from DB (Tin Ceiling Tile / Tin Ceiling Molding / Accessory / Tool)
 *      - vendor "Atomic 50 Ceilings", DWJT- SKU, tin-ceiling metafields
 *      - image = Squarespace-CDN swatch, tags from ai_tags + type/vendor
 *    Write shopify_product_id + on_shopify=true back into atomic50_catalog per row.
 *
 * 2. Reconcile the 2 already-live products (AT50-04 pid 7799402823731,
 *    AT50-41 pid 7799395778611): product_type Wallcovering -> Tin Ceiling Tile,
 *    title -> new convention (drop banned word "Wallcovering"), align tags.
 *    Keep current status (stay active — do NOT flip to draft).
 *
 * PG-first (mirror) then Shopify (authoritative) per DW canonical write order.
 * Cost: Shopify Admin API = $0 (rate-limit throttled). $0 (local) DB writes.
 */
const { Client } = require('pg');
const fs = require('fs');

const SHOP = 'designer-laboratory-sandbox.myshopify.com';
const API = '2024-10';
const DRY = process.argv.includes('--dry-run');

// ---- token (parse .env directly; do NOT shell-source — unquoted-value bug) ----
function readToken() {
  const env = fs.readFileSync('/Users/macstudio3/Projects/secrets-manager/.env', 'utf8');
  for (const line of env.split('\n')) {
    const m = line.match(/^SHOPIFY_ADMIN_TOKEN=(.*)$/);
    if (m) return m[1].trim().replace(/^["']|["']$/g, '');
  }
  throw new Error('SHOPIFY_ADMIN_TOKEN not found');
}
const TOKEN = readToken();

const BAN = /wallpaper|wallcovering/i;
const sleep = (ms) => new Promise((r) => setTimeout(r, ms));

async function shopify(method, path, body) {
  const url = `https://${SHOP}/admin/api/${API}/${path}`;
  const res = await fetch(url, {
    method,
    headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' },
    body: body ? JSON.stringify(body) : undefined,
  });
  const txt = await res.text();
  let json;
  try { json = txt ? JSON.parse(txt) : {}; } catch { json = { _raw: txt }; }
  // respect Shopify's leaky-bucket call limit
  const call = res.headers.get('x-shopify-shop-api-call-limit');
  if (call) {
    const [used, cap] = call.split('/').map(Number);
    if (used / cap > 0.7) await sleep(600);
  }
  return { ok: res.ok, status: res.status, json };
}

// ---- title / tags builders ----
function titleFor(row) {
  const sku = (row.mfr_sku || '').toUpperCase();       // AT50-35 (never lowercased At50-)
  const color = row.color_name || 'Unfinished';
  const t = `${sku} ${color} | Atomic 50`;
  if (BAN.test(t)) throw new Error(`banned word in title: ${t}`);
  return t;
}
function typeTagFor(pt) {
  // customer-facing tag mirror of product_type
  return pt; // "Tin Ceiling Tile" / "Tin Ceiling Molding" / "Accessory" / "Tool"
}
function tagsFor(row) {
  const set = new Set();
  set.add('Atomic 50');
  set.add('Pressed Tin');
  set.add(typeTagFor(row.product_type));
  set.add('Quote-Only');
  const at = row.ai_tags || [];
  for (const t of at) if (t && typeof t === 'string') set.add(t);
  const st = row.ai_styles || [];
  for (const t of st) if (t && typeof t === 'string') set.add(t);
  const pa = row.ai_patterns || [];
  for (const t of pa) if (t && typeof t === 'string') set.add(t);
  // strip any banned tags
  return [...set].filter((t) => !BAN.test(t)).join(', ');
}
function metafieldsFor(row) {
  // Tin-ceiling spec fields go in the `tin` namespace (no store definitions there),
  // NOT `custom` — the store already defines custom.material=multi_line and
  // custom.finish=product_reference, which reject our single-line text values.
  const mf = [];
  const push = (ns, key, value) =>
    value && mf.push({ namespace: ns, key, type: 'single_line_text_field', value: String(value) });
  push('tin', 'material', row.material);
  push('tin', 'finish', row.finish);
  push('tin', 'application', row.application);
  push('tin', 'fire_rating', row.fire_rating);
  push('tin', 'width', row.width);
  mf.push({ namespace: 'sec', key: 'mfr_sku', type: 'single_line_text_field', value: row.mfr_sku });
  mf.push({ namespace: 'sec', key: 'dw_sku', type: 'single_line_text_field', value: row.dw_sku });
  return mf;
}
function bodyFor(row) {
  const b = (row.body_html && row.body_html.trim()) || (row.ai_description && row.ai_description.trim()) || '';
  if (BAN.test(b)) throw new Error(`banned word in body for ${row.mfr_sku}`);
  return b;
}

function buildCreatePayload(row) {
  const p = {
    title: titleFor(row),
    status: 'draft',
    vendor: 'Atomic 50 Ceilings',
    product_type: row.product_type,
    tags: tagsFor(row),
    body_html: bodyFor(row),
    variants: [
      {
        option1: 'Sample',
        sku: `${row.dw_sku}-Sample`,
        price: '4.25',
        inventory_management: null,
        requires_shipping: true,
        taxable: true,
      },
    ],
    metafields: metafieldsFor(row),
  };
  if (row.image_url && /^https:\/\//.test(row.image_url)) p.images = [{ src: row.image_url }];
  return { product: p };
}

async function main() {
  const c = new Client({ host: '/tmp', database: 'dw_unified' });
  await c.connect();

  // rows to create: not LBI-BOYD, not already-live
  const { rows: toCreate } = await c.query(`
    SELECT mfr_sku, dw_sku, pattern_name, color_name, product_type, width, width_inches,
           material, finish, application, fire_rating, image_url,
           ai_tags, ai_styles, ai_patterns, body_html, ai_description
    FROM atomic50_catalog
    WHERE mfr_sku <> 'LBI-BOYD' AND shopify_product_id IS NULL
    ORDER BY product_type, mfr_sku`);

  console.log(`[create] ${toCreate.length} rows to create as DRAFT (dry-run=${DRY})`);

  const results = { created: [], failed: [], reconciled: [], reconcileFailed: [] };

  for (const row of toCreate) {
    let payload;
    try { payload = buildCreatePayload(row); }
    catch (e) { console.error(`  ! build failed ${row.mfr_sku}: ${e.message}`); results.failed.push({ mfr_sku: row.mfr_sku, error: e.message }); continue; }

    if (DRY) { console.log(`  [dry] would create ${row.mfr_sku} "${payload.product.title}" (${row.product_type})`); results.created.push({ mfr_sku: row.mfr_sku, dry: true }); continue; }

    const r = await shopify('POST', 'products.json', payload);
    if (!r.ok || !r.json.product) {
      console.error(`  ! create failed ${row.mfr_sku}: ${r.status} ${JSON.stringify(r.json.errors || r.json)}`);
      results.failed.push({ mfr_sku: row.mfr_sku, status: r.status, error: r.json.errors || r.json });
      await sleep(300);
      continue;
    }
    const pid = String(r.json.product.id);
    // PG mirror write-back (immediate local reads)
    await c.query(
      `UPDATE atomic50_catalog SET shopify_product_id=$1, on_shopify=true, updated_at=now() WHERE mfr_sku=$2`,
      [pid, row.mfr_sku]
    );
    console.log(`  + ${row.mfr_sku} -> pid ${pid} "${payload.product.title}"`);
    results.created.push({ mfr_sku: row.mfr_sku, pid, title: payload.product.title });
    await sleep(250); // pacing (well under 1k/day; leaky bucket handled above)
  }

  // ---- reconcile the 2 already-live ----
  const live = [
    { mfr_sku: 'AT50-04', pid: '7799402823731', color: 'Unfinished' },
    { mfr_sku: 'AT50-41', pid: '7799395778611', color: 'Unfinished' },
  ];
  for (const L of live) {
    const { rows } = await c.query(`SELECT * FROM atomic50_catalog WHERE mfr_sku=$1`, [L.mfr_sku]);
    const row = rows[0];
    const newTitle = titleFor(row);       // AT50-04 Unfinished | Atomic 50
    const newTags = tagsFor(row);         // tile tag set, no banned words
    const update = {
      product: {
        id: Number(L.pid),
        title: newTitle,
        product_type: 'Tin Ceiling Tile',
        tags: newTags,
        // status intentionally omitted -> keep current (active)
      },
    };
    if (DRY) { console.log(`  [dry] would reconcile ${L.mfr_sku} -> "${newTitle}" / Tin Ceiling Tile`); results.reconciled.push({ mfr_sku: L.mfr_sku, dry: true }); continue; }
    const r = await shopify('PUT', `products/${L.pid}.json`, update);
    if (!r.ok || !r.json.product) {
      console.error(`  ! reconcile failed ${L.mfr_sku}: ${r.status} ${JSON.stringify(r.json.errors || r.json)}`);
      results.reconcileFailed.push({ mfr_sku: L.mfr_sku, status: r.status, error: r.json.errors || r.json });
      continue;
    }
    await c.query(
      `UPDATE atomic50_catalog SET on_shopify=true, updated_at=now() WHERE mfr_sku=$1`,
      [L.mfr_sku]
    );
    console.log(`  ~ reconciled ${L.mfr_sku} pid ${L.pid} -> "${newTitle}" / Tin Ceiling Tile / status kept ${r.json.product.status}`);
    results.reconciled.push({ mfr_sku: L.mfr_sku, pid: L.pid, title: newTitle, status: r.json.product.status });
    await sleep(250);
  }

  await c.end();
  fs.writeFileSync('/Users/macstudio3/Projects/atomic50-onboard/data/shopify-push-result.json', JSON.stringify(results, null, 2));
  console.log(`\nDONE created=${results.created.length} failed=${results.failed.length} reconciled=${results.reconciled.length} reconcileFailed=${results.reconcileFailed.length}`);
  console.log('audit -> /Users/macstudio3/Projects/atomic50-onboard/data/shopify-push-result.json');
}

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